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

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


namespace Estsh.Core.Web.Controllers
{
    public class YYEDIQueryController : Controller
    {
        //
        YYEDIQueryService Service = new YYEDIQueryService();

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

        public string _where = "";
        public string SetWhere()
        {
            int condition = 0;
            _where = "";

            DateTime startDate = Request.RequestContext.HttpContext.Timestamp.AddDays(-1);
            DateTime endDate = Request.RequestContext.HttpContext.Timestamp;

            string starttime = startDate.ToString("yyyy-MM-dd HH:mm:ss");

            string endtime = endDate.ToString("yyyy-MM-dd HH:mm:ss");

            if (!string.IsNullOrEmpty(Request["txtfactory"]))
            {
                condition++;
                _where += " and a.plantId= '" + Request["txtfactory"].ToString().Trim() + "'";
            }

            if (!string.IsNullOrEmpty(Request["txtVIN"]))
            {
                condition++;
                _where += " and b.vin= '" + Request["txtVIN"].ToString().Trim() + "'";
            }
            if (!string.IsNullOrEmpty(Request["txtCSN"]))
            {
                condition++;
                _where += " and b.csn= '" + Request["txtCSN"].ToString().Trim() + "'";
            }

            if (!string.IsNullOrEmpty(Request["txtStatus"]))
            {
                condition++;
                _where += " and b.status= '" + Request["txtStatus"].ToString().Trim() + "'";
            }

            if (!string.IsNullOrEmpty(Request["txtEnabled"]))
            {
                condition++;
                _where += " and b.enabled= '" + Request["txtEnabled"].ToString().Trim() + "'";
            }
            if (!string.IsNullOrEmpty(Request["txtType"]))
            {
                condition++;
                _where += " and b.edi_type= '" + Request["txtType"].ToString().Trim() + "'";
            }
            if (!string.IsNullOrEmpty(Request["txtIsProduction"]))
            {
                condition++;
                _where += " and b.is_production= '" + Request["txtIsProduction"].ToString().Trim() + "'";
            }
            if (!string.IsNullOrEmpty(Request["txtIsShipping"]))
            {
                condition++;
                _where += " and b.is_shipping= '" + Request["txtIsShipping"].ToString().Trim() + "'";
            }
            if (!string.IsNullOrEmpty(Request["txtStartTime"])&& !string.IsNullOrEmpty(Request["txtEndTime"]))
            {
                condition++;
                _where += " and a.firstTime >= '" + Request["txtStartTime"].Trim() + "'";
                _where += " and a.firstTime <= '" + Request["txtEndTime"].Trim() + "'";
            }

            if (condition == 0)
            {
                _where += " and a.firstTime >= '" + starttime + "'";
                _where += " and a.firstTime <= '" + endtime + "'"; 
            }

            return _where;
        }
        /// <summary>
        /// 汇总
        /// </summary>
        /// <param name="pager"></param>
        /// <returns></returns>
        public ActionResult GetYYEDIData(Pager pager)
        {
            _where = SetWhere();
            int totalCount = 0;
            DataTable dtQuery = Service.GetYYEDIData(_where, pager, ref totalCount);
            Hashtable result = new Hashtable();
            result.Add("rows", DataTypeConvert.NewObject.DataTableToArrayList(dtQuery));
            result.Add("pager.totalRows", totalCount);
            return Json(result);
        }

      

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="pager"></param>
        /// <param name="a"></param>
        /// <param name="sort"></param>
        /// <param name="direction"></param>
        /// <param name="isPage"></param>
        /// <returns></returns>
        public ActionResult exportData(Pager pager, int a, String sort, String direction, String isPage, string txtfactory, string txtStartTime, string txtEndTime, string txtVIN, string txtCSN, string txtStatus, string txtType, string txtIsProduction, string txtIsShipping, string txtEnabled)
        {
            string _where2 = "";
            _where2 = SetWhere();

          
            int totalCount = 0;
            DataTable dataHt = Service.GetYYEDIData(_where2, pager, ref totalCount);

            HSSFWorkbook workbook = new HSSFWorkbook();
            Stream outputStream = Response.OutputStream;
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("EDI信息");
            try
            {
                if (workbook != null)
                {
                    HSSFRow headRow = (HSSFRow)sheet.CreateRow(0);
                    headRow.CreateCell(0).SetCellValue("vin");
                    headRow.CreateCell(1).SetCellValue("客户零件号");
                    headRow.CreateCell(2).SetCellValue("CSN");
                    headRow.CreateCell(3).SetCellValue("配置名称");
                    headRow.CreateCell(4).SetCellValue("配置描述");
                    headRow.CreateCell(5).SetCellValue("零件号");
                    headRow.CreateCell(6).SetCellValue("零件描述");
                    headRow.CreateCell(7).SetCellValue("单号");
                    headRow.CreateCell(8).SetCellValue("工厂");
                    headRow.CreateCell(9).SetCellValue("箱零件号");
                    headRow.CreateCell(10).SetCellValue("箱零件号名称");
                    headRow.CreateCell(11).SetCellValue("第一个过点时间");
                    headRow.CreateCell(12).SetCellValue("期望到达时间");
                    headRow.CreateCell(13).SetCellValue("供应发运时间");
                    headRow.CreateCell(14).SetCellValue("建议到达时间");
                    headRow.CreateCell(15).SetCellValue("EDI类型");
                    headRow.CreateCell(16).SetCellValue("是否生产");
                    headRow.CreateCell(17).SetCellValue("是否发运");
                    headRow.CreateCell(18).SetCellValue("备注");
                }

                for (int i = 0; i < dataHt.Rows.Count; i++)
                {
                    int row = i + 1;
                    HSSFRow dataRow = (HSSFRow)sheet.CreateRow(row);

                    dataRow.CreateCell(0).SetCellValue(dataHt.Rows[i]["vin"].ToString());
                    dataRow.CreateCell(1).SetCellValue(dataHt.Rows[i]["materialId"].ToString());
                    dataRow.CreateCell(2).SetCellValue(dataHt.Rows[i]["csn"].ToString());
                    dataRow.CreateCell(3).SetCellValue(dataHt.Rows[i]["model_name"].ToString());
                    dataRow.CreateCell(4).SetCellValue(dataHt.Rows[i]["model_desc"].ToString());
                    dataRow.CreateCell(5).SetCellValue(dataHt.Rows[i]["part_no"].ToString());
                    dataRow.CreateCell(6).SetCellValue(dataHt.Rows[i]["part_spec"].ToString());
                    dataRow.CreateCell(7).SetCellValue(dataHt.Rows[i]["billid"].ToString());
                    dataRow.CreateCell(8).SetCellValue(dataHt.Rows[i]["plantId"].ToString());
                    dataRow.CreateCell(9).SetCellValue(dataHt.Rows[i]["boxParts"].ToString());
                    dataRow.CreateCell(10).SetCellValue(dataHt.Rows[i]["boxPartsName"].ToString());
                    dataRow.CreateCell(11).SetCellValue(dataHt.Rows[i]["firstTime"].ToString());
                    dataRow.CreateCell(12).SetCellValue(dataHt.Rows[i]["expectedArrivalTime"].ToString());
                    dataRow.CreateCell(13).SetCellValue(dataHt.Rows[i]["supplierConfirmTime"].ToString());
                    dataRow.CreateCell(14).SetCellValue(dataHt.Rows[i]["estimatedArrivalTime"].ToString());
                    dataRow.CreateCell(15).SetCellValue(dataHt.Rows[i]["edi_type"].ToString());
                    dataRow.CreateCell(16).SetCellValue(dataHt.Rows[i]["is_production"].ToString());
                    dataRow.CreateCell(17).SetCellValue(dataHt.Rows[i]["is_shipping"].ToString());
                    dataRow.CreateCell(18).SetCellValue(dataHt.Rows[i]["remark"].ToString());
                  
                }

                Response.Clear();
                workbook.Write(outputStream);

                Response.Buffer = true;
                Response.AppendHeader("Content-Disposition", "attachment;filename=EDI信息_" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") + ".xls");
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.ContentType = "application/vnd.ms-excel";
                Response.Flush();

            }
            catch (Exception e)
            {
            }
            finally
            {
                workbook = null;
            }

            return null;
        }
    }
}