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

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



namespace Estsh.Core.Web.Controllers
{
    public class ProcessOutputQueryController : Controller
    {
        ProcessOutputQueryService Service = new ProcessOutputQueryService();
        //
        // GET: /ProcessOutputQuery/

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

        public string SetWhere(string pdlineName,string ProcessName,string ShiftName,string PartNo,
                            string StartTime,string cmbStart,string EndTime,string cmbEnd)
        {
            string _where = "";

            if (!string.IsNullOrEmpty(StartTime) && !string.IsNullOrEmpty(cmbStart)
                && !string.IsNullOrEmpty(EndTime) && !string.IsNullOrEmpty(cmbEnd))
            {
                _where = string.Format(" and a.create_ymd BETWEEN '{0}' AND '{1}' AND a.work_hour BETWEEN '{2}' AND '{3}' ",
                    StartTime,EndTime, cmbStart, cmbEnd);
            }

            string pdlineStr = string.Empty;
            if (!string.IsNullOrEmpty(pdlineName))
            {
                string[] pdLineArr = pdlineName.Split(',');
                foreach (string str in pdLineArr)
                {
                    pdlineStr += " '" + str + "' ,";
                }
                pdlineStr = pdlineStr.Substring(0, pdlineStr.Length - 2);
                _where += string.Format(" and c.pdline_name in ({0})", pdlineStr);
            }

            if (!string.IsNullOrEmpty(ProcessName))
            {
                string processStr = "";
                string[] processArr = ProcessName.Split(',');
                foreach (string str in processArr)
                {
                    processStr += "'" + str + "' ,";
                }
                processStr = processStr.Substring(0, processStr.Length - 2);
                _where += string.Format(" and e.process_name  in ({0})", processStr);
            }
            if (!string.IsNullOrEmpty(ShiftName))
            {
                string ShiftStr = "";
                string[] ShiftArr = ShiftName.Split(',');
                foreach (string str in ShiftArr)
                {
                    ShiftStr += "'" + str + "' ,";
                }
                ShiftStr = ShiftStr.Substring(0, ShiftStr.Length - 2);
                _where += string.Format(" and f.shift_name  in ({0})", ShiftStr);
            }
            if (!string.IsNullOrEmpty(PartNo) && (!PartNo.Equals("请选择")))
            {
                string PartNoStr = "";
                string[] PartNoArr = PartNo.Split(',');
                foreach (string str in PartNoArr)
                {
                    PartNoStr += "'" + str + "' ,";
                }
                PartNoStr = PartNoStr.Substring(0, PartNoStr.Length - 2);
                _where += string.Format(" and b.part_no  in ({0})", PartNoStr);
            }

            return _where;
        }


        public ActionResult GetAll(string PdlineName, string ProcessName, string ShiftName,
            string PartNo, string StartTime, string CmbStart, string EndTime, string CmbEnd, Pager pager)
        {
            if (string.IsNullOrEmpty(StartTime) || string.IsNullOrEmpty(EndTime))
                return null;
            string where = SetWhere(PdlineName, ProcessName, ShiftName, PartNo, StartTime, CmbStart, EndTime,CmbEnd);
            int totalCount = 0;
            DataTable dtQuery = Service.GetAll(where, pager, ref totalCount);
            Hashtable result = new Hashtable();
            result.Add("rows", DataTypeConvert.NewObject.DataTableToArrayList(dtQuery));
            result.Add("pager.totalRows", totalCount);
            return Json(result);

        }



        public ActionResult GetPdlineName()
        {
            Hashtable ht = new Hashtable();
            ArrayList PdlineName = Service.GetPdlineName();
            ht.Add("list", PdlineName);
            return Json(ht, JsonRequestBehavior.AllowGet);
        }

        public ActionResult GetProcessName()
        {
            Hashtable ht = new Hashtable();
            ArrayList ProcessName = Service.GetProcessName();
            ht.Add("list", ProcessName);
            return Json(ht, JsonRequestBehavior.AllowGet);
        }

        public ActionResult GetShiftName()
        {
            Hashtable ht = new Hashtable();
            ArrayList ShiftName = Service.GetShiftName();
            ht.Add("list", ShiftName);
            return Json(ht, JsonRequestBehavior.AllowGet);
        }



        [HttpPost, ActionName("GetPartInfo")]
        public ActionResult GetPartInfo(Pager pager)
        {
            Hashtable ht = new Hashtable();
            int totalCount = 0;

            ht.Add("pager.pageNo", pager.pageNo);


            DataTable PartNo = Service.GetPartInfo(pager,ref totalCount);

            ht.Add("rows", DataTypeConvert.NewObject.DataTableToArrayList(PartNo));
            ht.Add("pager.totalRows", totalCount);
            return Json(ht, JsonRequestBehavior.AllowGet);
        }

        public ActionResult exportData(Pager pager, int a, string PdlineName, string ProcessName, string ShiftName,
            string PartNo, string StartTime, string CmbStart, string EndTime, string CmbEnd)
        {
            int totalCount = 0;
            


            string where = SetWhere(PdlineName, ProcessName, ShiftName, PartNo, StartTime, CmbStart, EndTime, CmbEnd);
            DataTable dtQuery = Service.GetAll(where, pager, ref totalCount);
            Stream outputStream = Response.OutputStream;
            string AbsolutePath = Request.UrlReferrer.AbsolutePath;
            string url = AbsolutePath.Remove(0, Request.ApplicationPath.Length);
            GridColumnService colService = new GridColumnService();
            Models.SysWebGridColumn[] detailGridItems = colService.GetColumnByUrl(url, "明细");//查询明细GtidColumn
            HSSFWorkbook workbook = NPOIExcelTools.DataTableToWorkbook(detailGridItems, dtQuery, "明细信息");//将汇总表转换为Workbook
            //NPOIExcelTools.AddSheet(workbook, detailGridItems, dtQuery, "明细信息");//添加明细Sheet
            Response.Clear();
            workbook.Write(outputStream);

            Response.Buffer = true;
            if (a == 1)
            {
                if (Request.Browser.Type.ToUpper().IndexOf("IE") >= 0)
                {
                    Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("制程产出统计_当前页_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls", System.Text.Encoding.UTF8));
                }
                else
                {
                    Response.AppendHeader("Content-Disposition", "attachment;filename=制程产出统计_当前页_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls");
                }
            }
            else
            {
                if (Request.Browser.Type.ToUpper().IndexOf("IE") >= 0)
                {
                    Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("制程产出统计_所有页_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls", System.Text.Encoding.UTF8));
                }
                else
                {
                    Response.AppendHeader("Content-Disposition", "attachment;filename=制程产出统计_所有页_" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls");
                }
            }
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/vnd.ms-excel";
            Response.Flush();
            return null;


        }


    }
}