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

using System;
using System.Collections.Generic;
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 SGAJISOrderQueryController : Controller
    {
        private SGAJISOrderQueryService service = new SGAJISOrderQueryService();
        //
        // GET: /SGAJISOrderQuery/

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


        /// <summary>
        /// 回的车型汇总
        /// </summary>
        /// <param name="SGAJISOrder_noStart"></param>
        /// <param name="SGAJISorder_noEnd"></param>
        /// <param name="dateStart"></param>
        /// <param name="dateEnd"></param>
        /// <returns></returns>
        public ActionResult GetModelSummary(string SGAJISOrder_noStart, string SGAJISorder_noEnd)
        {
            DateTime dateStart = DateTime.MinValue;
            DateTime dateEnd = DateTime.MaxValue;
            string chkUseTime = Request["chkUseTime"];
            if (string.IsNullOrEmpty(chkUseTime) == false)
            {
                dateStart = Convert.ToDateTime(Request["dateStart"]);
                dateEnd = Convert.ToDateTime(Request["dateEnd"]);
            }
            if (string.IsNullOrEmpty(SGAJISOrder_noStart) && string.IsNullOrEmpty(SGAJISorder_noEnd))
            {
                return null;
            }
            DataTable dt = service.GetModelSummary(SGAJISOrder_noStart, SGAJISorder_noEnd, dateStart, dateEnd);
            Hashtable result = new Hashtable();
            result.Add("rows", DataTypeConvert.NewObject.DataTableToArrayList(dt));
            //result.Add("pager.totalRows", pager.totalRows);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        /// <summary>
        /// 获得配置汇总
        /// </summary>
        /// <param name="SGAJISOrder_noStart"></param>
        /// <param name="SGAJISorder_noEnd"></param>
        /// <param name="dateStart"></param>
        /// <param name="dateEnd"></param>
        /// <returns></returns>
        public ActionResult GetConfigurationSummary(string SGAJISOrder_noStart, string SGAJISorder_noEnd)
        {
            DateTime dateStart = DateTime.MinValue;
            DateTime dateEnd = DateTime.MaxValue;
            string chkUseTime = Request["chkUseTime"];
            if (string.IsNullOrEmpty(chkUseTime) == false)
            {
                dateStart = Convert.ToDateTime(Request["dateStart"]);
                dateEnd = Convert.ToDateTime(Request["dateEnd"]);
            }

            if (string.IsNullOrEmpty(SGAJISOrder_noStart) && string.IsNullOrEmpty(SGAJISorder_noEnd))
            {
                return null;
            }
            DataTable dt = service.GetConfigurationSummary(SGAJISOrder_noStart, SGAJISorder_noEnd, dateStart, dateEnd);
            Hashtable result = new Hashtable();
            result.Add("rows", DataTypeConvert.NewObject.DataTableToArrayList(dt));
            //result.Add("pager.totalRows", pager.totalRows);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        /// <summary>
        /// 获得车号汇总
        /// </summary>
        /// <param name="page"></param>
        /// <param name="SGAJISOrder_noStart"></param>
        /// <param name="SGAJISorder_noEnd"></param>
        /// <param name="dateStart"></param>
        /// <param name="dateEnd"></param>
        /// <returns></returns>
        public ActionResult GetCarNoSummary(Pager page, string SGAJISOrder_noStart, string SGAJISorder_noEnd)
        {
            DateTime dateStart = DateTime.MinValue;
            DateTime dateEnd = DateTime.MaxValue;
            string chkUseTime = Request["chkUseTime"];
            if (string.IsNullOrEmpty(chkUseTime) == false)
            {
                dateStart = Convert.ToDateTime(Request["dateStart"]);
                dateEnd = Convert.ToDateTime(Request["dateEnd"]);
            }

            if (string.IsNullOrEmpty(SGAJISOrder_noStart) && string.IsNullOrEmpty(SGAJISorder_noEnd))
            {
                return null;
            }
            DataTable dt = service.GetCarNoSummary(ref page, SGAJISOrder_noStart, SGAJISorder_noEnd, dateStart, dateEnd);
            Hashtable result = new Hashtable();
            result.Add("rows", DataTypeConvert.NewObject.DataTableToArrayList(dt));
            result.Add("pager.totalRows", page.totalRows);
            return Json(result, JsonRequestBehavior.AllowGet);
        }
        /// <summary>
        /// 获得零件号统计
        /// </summary>
        /// <param name="page"></param>
        /// <param name="SGAJISOrder_noStart"></param>
        /// <param name="SGAJISorder_noEnd"></param>
        /// <param name="dateStart"></param>
        /// <param name="dateEnd"></param>
        /// <returns></returns>
        public ActionResult GetPartNoCount(Pager page, string SGAJISOrder_noStart, string SGAJISorder_noEnd)
        {
            DateTime dateStart = DateTime.MinValue;
            DateTime dateEnd = DateTime.MaxValue;
            string chkUseTime = Request["chkUseTime"];
            if (string.IsNullOrEmpty(chkUseTime) == false)
            {
                dateStart = Convert.ToDateTime(Request["dateStart"]);
                dateEnd = Convert.ToDateTime(Request["dateEnd"]);
            }
            if (string.IsNullOrEmpty(SGAJISOrder_noStart) && string.IsNullOrEmpty(SGAJISorder_noEnd))
            {
                return null;
            }
            DataTable dt = service.GetPartNoCount(ref page, SGAJISOrder_noStart, SGAJISorder_noEnd, dateStart, dateEnd);
            Hashtable result = new Hashtable();
            result.Add("rows", DataTypeConvert.NewObject.DataTableToArrayList(dt));
            result.Add("pager.totalRows", page.totalRows);
            return Json(result, JsonRequestBehavior.AllowGet);
        }
        /// <summary>
        /// 获得零件号汇总
        /// </summary>
        /// <param name="page"></param>
        /// <param name="SGAJISOrder_noStart"></param>
        /// <param name="SGAJISorder_noEnd"></param>
        /// <param name="dateStart"></param>
        /// <param name="dateEnd"></param>
        /// <returns></returns>
        public ActionResult GetPartNoSummary(Pager page, string SGAJISOrder_noStart, string SGAJISorder_noEnd)
        {
            DateTime dateStart = DateTime.MinValue;
            DateTime dateEnd = DateTime.MaxValue;
            string chkUseTime = Request["chkUseTime"];
            if (string.IsNullOrEmpty(chkUseTime) == false)
            {
                dateStart = Convert.ToDateTime(Request["dateStart"]);
                dateEnd = Convert.ToDateTime(Request["dateEnd"]);
            }
            if (string.IsNullOrEmpty(SGAJISOrder_noStart) && string.IsNullOrEmpty(SGAJISorder_noEnd))
            {
                return null;
            }
            DataTable dt = service.GetPartNoSummary(ref page, SGAJISOrder_noStart, SGAJISorder_noEnd, dateStart, dateEnd);
            Hashtable result = new Hashtable();
            result.Add("rows", DataTypeConvert.NewObject.DataTableToArrayList(dt));
            result.Add("pager.totalRows", page.totalRows);
            return Json(result, JsonRequestBehavior.AllowGet);
        }

        public ActionResult ExportData(string SGAJISOrder_noStart, string SGAJISorder_noEnd)
        {
            DateTime dateStart = DateTime.MinValue;
            DateTime dateEnd = DateTime.MaxValue;
            string chkUseTime = Request["chkUseTime"];
            if (string.IsNullOrEmpty(chkUseTime) == false)
            {
                dateStart = Convert.ToDateTime(Request["dateStart"]);
                dateEnd = Convert.ToDateTime(Request["dateEnd"]);
            }
            if (string.IsNullOrEmpty(SGAJISOrder_noStart) && string.IsNullOrEmpty(SGAJISorder_noEnd))
            {
                return null;
            }
            Pager page = new Pager();
            page.pageNo = 1;
            page.pageSize = int.MaxValue;
            string AbsolutePath = Request.UrlReferrer.AbsolutePath;
            string url = AbsolutePath.Remove(0, Request.ApplicationPath.Length);

            GridColumnService colService = new GridColumnService();
            Models.SysWebGridColumn[] modelSummaryGridItems = colService.GetColumnByUrl(url, "车型汇总");
            Models.SysWebGridColumn[] configurationSummaryGridItems = colService.GetColumnByUrl(url, "配置汇总");
            Models.SysWebGridColumn[] carNoSummaryGridItems = colService.GetColumnByUrl(url, "车号统计");
            Models.SysWebGridColumn[] partNoCountGridItems = colService.GetColumnByUrl(url, "零件号统计");
            Models.SysWebGridColumn[] partNoSummaryGridItems = colService.GetColumnByUrl(url, "零件号汇总");

            DataTable modelSummary = service.GetModelSummary(SGAJISOrder_noStart, SGAJISorder_noEnd, dateStart, dateEnd);
            DataTable configurationSummary = service.GetConfigurationSummary(SGAJISOrder_noStart, SGAJISorder_noEnd, dateStart, dateEnd);
            DataTable carNoSummary = service.GetCarNoSummary(ref page, SGAJISOrder_noStart, SGAJISorder_noEnd, dateStart, dateEnd);
            DataTable partNoCount = service.GetPartNoCount(ref page, SGAJISOrder_noStart, SGAJISorder_noEnd, dateStart, dateEnd);
            DataTable partNoSummary = service.GetPartNoSummary(ref page, SGAJISOrder_noStart, SGAJISorder_noEnd, dateStart, dateEnd);


            HSSFWorkbook workbook = NPOIExcelTools.DataTableToWorkbook(modelSummaryGridItems, modelSummary, "车型汇总");//将汇总表转换为Workbook
            NPOIExcelTools.AddSheet(workbook, configurationSummaryGridItems, configurationSummary, "配置汇总");
            NPOIExcelTools.AddSheet(workbook, carNoSummaryGridItems, carNoSummary, "车号统计");
            NPOIExcelTools.AddSheet(workbook, partNoCountGridItems, partNoCount, "零件号统计");
            NPOIExcelTools.AddSheet(workbook, partNoSummaryGridItems, partNoSummary, "零件号汇总");

            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;
        }
    }
}