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 YYEDIProdOrderController : Controller
    {
        //
        YYEDIProdOrderService Service = new YYEDIProdOrderService();

        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 g.plantId= '" + Request["txtfactory"].ToString().Trim() + "'";
            }

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

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

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

                //_where += " and ediTime >= '" + Request["txtStartTime"].Trim() + "'";
                //_where += " and ediTime <= '" + Request["txtEndTime"].Trim() + "'";
            }

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

            return _where;
        }
        /// <summary>
        /// 汇总
        /// </summary>
        /// <param name="pager"></param>
        /// <returns></returns>
        public ActionResult GetYYEDIProdData(Pager pager)
        {
            _where = SetWhere();
            int totalCount = 0;
            DataTable dtQuery = Service.GetYYEDIProdData(_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 txtEnabled)
        {
            string _where2 = "";
            _where2 = SetWhere();

          
            int totalCount = 0;
            DataTable dataHt = Service.GetYYEDIProdData(_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("客户工厂");
                    headRow.CreateCell(1).SetCellValue("CSN号");
                    headRow.CreateCell(2).SetCellValue("生成订单号");
                    headRow.CreateCell(3).SetCellValue("座椅条码");
                    headRow.CreateCell(4).SetCellValue("配置名称");
                    headRow.CreateCell(5).SetCellValue("零件号");
                    headRow.CreateCell(6).SetCellValue("客户零件号");
                    headRow.CreateCell(7).SetCellValue("零件描述");
                    headRow.CreateCell(8).SetCellValue("EDI排序单号");
                    headRow.CreateCell(9).SetCellValue("VIN号");
                    headRow.CreateCell(10).SetCellValue("EDI类型");
                    headRow.CreateCell(11).SetCellValue("是否生产");
                    headRow.CreateCell(12).SetCellValue("是否发运");
                    headRow.CreateCell(13).SetCellValue("状态");
                    headRow.CreateCell(14).SetCellValue("生产标识");
                    headRow.CreateCell(15).SetCellValue("座椅状态");
                    headRow.CreateCell(16).SetCellValue("EDI获取时间");
                    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]["plantId"].ToString());
                    dataRow.CreateCell(1).SetCellValue(dataHt.Rows[i]["csn"].ToString());
                    dataRow.CreateCell(2).SetCellValue(dataHt.Rows[i]["order_no"].ToString());
                    dataRow.CreateCell(3).SetCellValue(dataHt.Rows[i]["serial_number"].ToString());
                    dataRow.CreateCell(4).SetCellValue(dataHt.Rows[i]["model_name"].ToString());
                    dataRow.CreateCell(5).SetCellValue(dataHt.Rows[i]["part_no"].ToString());
                    dataRow.CreateCell(6).SetCellValue(dataHt.Rows[i]["part_no_3c"].ToString());
                    dataRow.CreateCell(7).SetCellValue(dataHt.Rows[i]["part_spec"].ToString());
                    dataRow.CreateCell(8).SetCellValue(dataHt.Rows[i]["billId"].ToString());
                    dataRow.CreateCell(9).SetCellValue(dataHt.Rows[i]["vin"].ToString());
                    dataRow.CreateCell(10).SetCellValue(dataHt.Rows[i]["edi_type"].ToString());
                    dataRow.CreateCell(11).SetCellValue(dataHt.Rows[i]["is_production"].ToString());
                    dataRow.CreateCell(12).SetCellValue(dataHt.Rows[i]["is_shipping"].ToString());
                    dataRow.CreateCell(13).SetCellValue(dataHt.Rows[i]["status"].ToString());
                    dataRow.CreateCell(14).SetCellValue(dataHt.Rows[i]["edi_enabled"].ToString());
                    dataRow.CreateCell(15).SetCellValue(dataHt.Rows[i]["flag"].ToString());
                    dataRow.CreateCell(16).SetCellValue(dataHt.Rows[i]["ediTime"].ToString());
                    dataRow.CreateCell(17).SetCellValue(dataHt.Rows[i]["prodTime"].ToString());
                    dataRow.CreateCell(18).SetCellValue(dataHt.Rows[i]["appoint_pdline"].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;
        }
    }
}