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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Estsh.BLL;
using Estsh.Web.Util;
using System.Collections;
using System.Data;
using NPOI.HSSF.UserModel;
using System.IO;
using Estsh.Web.Service;
/***************************************************************************************************
 * 
 * 作者：何存根
 * 创建时间：2014.06.17
 * 描述：报表模块条码上下线查询Controller
 * 
 * *************************************************************************************************/
namespace Estsh.Core.Web.Controllers
{
    public class OutPDLineQueryController : Controller
    {
        //
        // GET: /OutPDLineQuery/
        OutPDLineQueryService service = new OutPDLineQueryService();
        public ActionResult Index()
        {
            return View();
        }

        /// <summary>
        /// 产线
        /// </summary>
        /// <returns>数据集</returns>
        public ActionResult GetPDLineName()
        {
            Hashtable resault = new Hashtable();
            ArrayList list = service.GetPDLineName();
            resault.Add("list", list);
            return Json(resault, JsonRequestBehavior.AllowGet);
        }

        public string sortStr;
        public string GetWhereStr()
        {
            string _whereStr = string.Empty;
            string pdLine = string.Empty;
            string outPDLine = string.Empty;

            if (!string.IsNullOrEmpty(Request["cmbOutPDLine"]))
            {
                outPDLine = Request["cmbOutPDLine"].ToString();
            }

            if (!string.IsNullOrEmpty(Request["cbPDLine"]))
            {
                _whereStr += " and d.pdline_id=" + Request["cbPDLine"].ToString();
            }

            if (!string.IsNullOrEmpty(Request["txtStartTime"]) &&
                !string.IsNullOrEmpty(Request["txtEndTime"]))
            {
                DateTime startPrintTime = Convert.ToDateTime(Request["txtStartTime"]);
                DateTime endPrintTime = Convert.ToDateTime(Request["txtEndTime"]);
                if (outPDLine == "下线")
                {
                    _whereStr += " and a.out_pdline_ymd+' '+a.out_pdline_hms >='" + startPrintTime.ToString("yyyy-MM-dd HH:mm:ss")
                        + "' and a.out_pdline_ymd + ' ' + a.out_pdline_hms <='" + endPrintTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
                }
                if (outPDLine == "上线")
                {
                    _whereStr += "and a.in_pdline_ymd+' '+a.in_pdline_hms >='" + startPrintTime.ToString("yyyy-MM-dd HH:mm:ss")
                        + "' and a.in_pdline_ymd + ' ' + a.in_pdline_hms <='" + endPrintTime.ToString("yyyy-MM-dd HH:mm:ss") + "'";
                }
            }

            return _whereStr;
        }

        /// <summary>
        /// 根据用户选择的条件查找汇总数据
        /// </summary>
        /// <param name="wheres">筛选条件</param>
        /// <returns>获取汇总表</returns>
        public ActionResult GetSummary(Pager pager)
        {
            if (string.IsNullOrEmpty(Request["cmbOutPDLine"]) && string.IsNullOrEmpty(Request["cbPDLine"]) && string.IsNullOrEmpty(Request["txtStartTime"]) && string.IsNullOrEmpty(Request["txtEndTime"]))
            {
                return null;
            }
            string wheres = GetWhereStr();
            int totalCount = 0;
            DataTable dataHt = service.GetSummary(wheres, pager, ref totalCount);
            Hashtable result = new Hashtable();
            result.Add("rows", DataTypeConvert.NewObject.DataTableToArrayList(dataHt));
            result.Add("pager.totalRows", totalCount);
            return Json(result);
        }

        /// <summary>
        /// 根据用户选择的条件查找明细数据
        /// </summary>
        /// <param name="wheres">筛选条件</param>
        /// <param name="sortStr">排序条件</param>
        /// <returns>获取明细表</returns>
        public ActionResult GetAll(Pager pager)
        {
            if (string.IsNullOrEmpty(Request["cmbOutPDLine"]) && string.IsNullOrEmpty(Request["cbPDLine"]) && string.IsNullOrEmpty(Request["txtStartTime"]) && string.IsNullOrEmpty(Request["txtEndTime"]))
            {
                return null;
            }
            string wheres = GetWhereStr();
            int totalCount = 0;
            DataTable dataHt = service.GetAll(wheres, pager, ref totalCount);
            Hashtable result = new Hashtable();
            result.Add("rows", DataTypeConvert.NewObject.DataTableToArrayList(dataHt));
            result.Add("pager.totalRows", totalCount);
            return Json(result);
        }

        /// <summary>
        /// 导出全部
        /// </summary>
        /// <returns></returns>
        public ActionResult exportData(Pager pager, Pager pager1)
        {
            // 如果没有数据就直接返回
            Hashtable resault = new Hashtable();
            string wheres = GetWhereStr();
            int totalCount = 0;
            DataTable _dt = service.GetSummary(wheres, pager, ref totalCount);//获得汇总信息
            DataTable _dtDetail = service.GetAll(wheres, pager1, 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[] sumGridItems = colService.GetColumnByUrl(url, "汇总");//查询汇总GtidColumn
            Models.SysWebGridColumn[] detailGridItems = colService.GetColumnByUrl(url, "明细");//查询明细GtidColumn
            HSSFWorkbook workbook = NPOIExcelTools.DataTableToWorkbook(sumGridItems, _dt, "汇总信息");//将汇总表转换为Workbook
            NPOIExcelTools.AddSheet(workbook, detailGridItems, _dtDetail, "明细信息");//添加明细Sheet
            Response.Clear();
            workbook.Write(outputStream);

            Response.Buffer = true;
            if (Request.Browser.Type.ToUpper().IndexOf("IE") >= 0)
            {
                Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("条码上下线.xls", System.Text.Encoding.UTF8));
            }
            else
            {
                Response.AppendHeader("Content-Disposition", "attachment;filename=条码上下线.xls");
            }
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.ContentType = "application/vnd.ms-excel";
            Response.Flush();
            return null;
        }
    }
}