Estsh-Yiri/Estsh.Core.Web/Controllers/OutGoingQueryController.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Collections;
using Estsh.Web.Service;
using Estsh.Web.Util;
using NPOI.HSSF.UserModel;
using System.IO;
using System.Data;

namespace Estsh.Core.Web.Controllers
{
    public class OutGoingQueryController : Controller
    {
        OutGoingQueryService service = new OutGoingQueryService();
        //
        // GET: /OutGoingQuery/

        public ActionResult Index()
        {
            return View();
        }

        public ActionResult GetCountType(Pager pager, string startTime, string endTime)
        {
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                return null;
            }
            Hashtable result = new Hashtable();
            DataTable dt = service.GetCountType(ref pager, startTime, endTime);
            ArrayList arrList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
            result.Add("rows", arrList);
            result.Add("pager.totalRows", pager.totalRows);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        public ActionResult GetCountModel(Pager pager, string startTime, string endTime)
        {
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                return null;
            }
            Hashtable result = new Hashtable();
            DataTable dt = service.GetCountModel(ref pager, startTime, endTime);
            ArrayList arrList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
            result.Add("rows", arrList);
            result.Add("pager.totalRows", pager.totalRows);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        public ActionResult GetCountByModel(Pager pager, string startTime, string endTime)
        {
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                return null;
            }
            Hashtable result = new Hashtable();
            DataTable dt = service.GetCountByModel(ref pager, startTime, endTime);
            ArrayList arrList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
            result.Add("rows", arrList);
            result.Add("pager.totalRows", pager.totalRows);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        public ActionResult GetCountByPN(Pager pager, string startTime, string endTime)
        {
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                return null;
            }
            Hashtable result = new Hashtable();
            DataTable dt = service.GetCountByPN(ref pager, startTime, endTime);
            ArrayList arrList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
            result.Add("rows", arrList);
            result.Add("pager.totalRows", pager.totalRows);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        public ActionResult GetOutGoingDeail(Pager pager, string startTime, string endTime)
        {
            if (string.IsNullOrEmpty(startTime) && string.IsNullOrEmpty(endTime))
            {
                return null;
            }
            Hashtable result = new Hashtable();
            DataTable dt = service.GetOutGoingDeail(ref pager, startTime, endTime);
            ArrayList arrList = DataTypeConvert.NewObject.DataTableToArrayList(dt);
            result.Add("rows", arrList);
            result.Add("pager.totalRows", pager.totalRows);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        public ActionResult ExportData(string startTime, string endTime)
        {
            Pager pager = new Pager();
            pager.pageNo = 1;
            pager.pageSize = int.MaxValue;
            DataTable countType = service.GetCountType(ref pager, startTime, endTime);
            DataTable countModel = service.GetCountModel(ref pager, startTime, endTime);
            DataTable countByModel = service.GetCountByModel(ref pager, startTime, endTime);
            DataTable countByPN = service.GetCountByPN(ref pager, startTime, endTime);
            DataTable outGoingDeail = service.GetOutGoingDeail(ref pager, startTime, endTime);

            string AbsolutePath = Request.UrlReferrer.AbsolutePath;
            string url = AbsolutePath.Remove(0, Request.ApplicationPath.Length);

            GridColumnService colService = new GridColumnService();
            Models.SysWebGridColumn[] countTypeGridItems = colService.GetColumnByUrl(url, "汇总车型");//汇总车型GtidColumn
            Models.SysWebGridColumn[] countModelGridItems = colService.GetColumnByUrl(url, "汇总配置");//汇总配置GtidColumn
            Models.SysWebGridColumn[] countByModelGridItems = colService.GetColumnByUrl(url, "按配置汇总总成");//按配置汇总总成GtidColumn
            Models.SysWebGridColumn[] countByPNGridItems = colService.GetColumnByUrl(url, "按零件号汇总总成");//按零件号汇总总成GtidColumn
            Models.SysWebGridColumn[] outGoingDeailGridItems = colService.GetColumnByUrl(url, "发运明细");//按零件号汇总总成GtidColumn
            if (countType.Rows.Count >= 65535)
            {
                Response.Write("<script type='text/javascript'>alert('【汇总车型】数据行数大于65535！');</script>");
                return null;
            }
            HSSFWorkbook workbook = NPOIExcelTools.DataTableToWorkbook(countTypeGridItems, countType, "汇总车型");//将汇总表转换为Workbook
            if (countModel.Rows.Count >= 65535)
            {
                Response.Write("<script type='text/javascript'>alert('【汇总配置】数据行数大于65535！');</script>");
                return null;
            }
            NPOIExcelTools.AddSheet(workbook, countModelGridItems, countModel, "汇总配置");
            if (countByModel.Rows.Count >= 65535)
            {
                Response.Write("<script type='text/javascript'>alert('【按配置汇总总成】数据行数大于65535！');</script>");
                return null;
            }
            NPOIExcelTools.AddSheet(workbook, countByModelGridItems, countByModel, "按配置汇总总成");
            if (countByPN.Rows.Count >= 65535)
            {
                Response.Write("<script type='text/javascript'>alert('【按零件号汇总总成】数据行数大于65535！');</script>");
                return null;
            }
            NPOIExcelTools.AddSheet(workbook, countByPNGridItems, countByPN, "按零件号汇总总成");
            if (outGoingDeail.Rows.Count >= 65535)
            {
                Response.Write("<script type='text/javascript'>alert('【发运明细】数据行数大于65535！');</script>");
                return null;
            }
            NPOIExcelTools.AddSheet(workbook, outGoingDeailGridItems, outGoingDeail, "发运明细");

            Stream outputStream = Response.OutputStream;
            Response.Clear();
            workbook.Write(outputStream);

            Response.Buffer = true;
            Response.AppendHeader("Content-Disposition", "attachment;filename= 上托盘扫描查询.xls");
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/vnd.ms-excel";
            Response.Flush();
            return null;
        }

    }
}