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


namespace Estsh.Core.Web.Controllers
{
    public class XTTestDataController : Controller
    {
        XTTestDataService service = new XTTestDataService();
        //
        // GET: /Menu/
        public ActionResult Index()
        {
            return View();
        }


        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["txtStartTime"]) || !string.IsNullOrEmpty(Request["txtEndTime"]))
            {
                condition++;
                _where += "  and CONVERT(NVARCHAR(20), 测试完成日期) + ' '+ CONVERT(NVARCHAR(20), 测试完成时间) BETWEEN '" + Request["txtStartTime"].Trim() + "' and '" + Request["txtEndTime"].Trim() + "' ";
            }

            if (!string.IsNullOrEmpty(Request["txtSN"]))
            {
                condition++;
                _where += " and CONVERT(NVARCHAR(50),座椅条形码) like '%" + Request["txtSN"].ToString().Trim() + "%' ";
            }


            if (!string.IsNullOrEmpty(Request["txtResult"]))
            {
                condition++;
                _where += " and  CONVERT(NVARCHAR(20),[测试结果(P/F)]) = '" + Request["txtResult"].ToString().Trim() + "' ";
            }

            if (condition == 0)
            {

                _where += " and CONVERT(NVARCHAR(20), 测试完成日期) + ' '+ CONVERT(NVARCHAR(20), 测试完成时间) BETWEEN '" + starttime + "' and '" + endtime + "' ";
            }
            return _where;
        }

       
        /// <summary>
        /// 电检主表
        /// </summary>
        /// <param name="pager"></param>
        /// <returns></returns>
        public ActionResult XTGetDjc(Pager pager)
        {
            _where = SetWhere();
            int totalCount = 0;
            DataTable dataHt = service.XTGetDjc(_where, 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)
        {
            #region 查询电检汇总数据

            _where = SetWhere();

            DataTable sumList = service.GetSumDjc(_where);

            #endregion

            HSSFWorkbook workbook = new HSSFWorkbook();
            Stream outputStream = Response.OutputStream;
            HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("电检数据");
         
            try
            {
                if (workbook != null)
                {
                    HSSFRow headRow = (HSSFRow)sheet.CreateRow(0);
                    headRow.CreateCell(0).SetCellValue("座椅条形码");
                    headRow.CreateCell(1).SetCellValue("设备名称");
                    headRow.CreateCell(2).SetCellValue("产品类型");
                    headRow.CreateCell(3).SetCellValue("测试结果");
                    headRow.CreateCell(4).SetCellValue("所用时间");
                    headRow.CreateCell(5).SetCellValue("测试完成日期");
                    headRow.CreateCell(6).SetCellValue("测试完成时间");
                    headRow.CreateCell(7).SetCellValue("模块版本CAN");
                    headRow.CreateCell(8).SetCellValue("模块版本CAN结果");

                    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("安全带卡扣插入电阻");
                    headRow.CreateCell(16).SetCellValue("安全带卡扣插入电阻结果");

                    headRow.CreateCell(17).SetCellValue("安全带卡扣插入释放电阻");
                    headRow.CreateCell(18).SetCellValue("安全带卡扣插入释放电阻结果");
                    headRow.CreateCell(19).SetCellValue("SBR无加载电阻");
                    headRow.CreateCell(20).SetCellValue("SBR无加载电阻结果");
                    headRow.CreateCell(21).SetCellValue("SBR加载电阻");
                    headRow.CreateCell(22).SetCellValue("SBR加载电阻结果");
                    headRow.CreateCell(23).SetCellValue("SBR加载释放电阻");
                    headRow.CreateCell(24).SetCellValue("SBR加载释放电阻结果");
                    headRow.CreateCell(25).SetCellValue("左座椅加热CAN电流");

                    headRow.CreateCell(26).SetCellValue("左座椅加热CAN电流结果");
                    headRow.CreateCell(27).SetCellValue("左座椅对右加热CAN电流");
                    headRow.CreateCell(28).SetCellValue("左座椅对右加热CAN电流结果");
                    headRow.CreateCell(29).SetCellValue("右座椅加热CAN电流");
                    headRow.CreateCell(30).SetCellValue("右座椅加热CAN电流结果");
                    headRow.CreateCell(31).SetCellValue("座椅前移电流");
                    headRow.CreateCell(32).SetCellValue("座椅前移电流结果");
                    headRow.CreateCell(33).SetCellValue("座椅后移电流");

                    headRow.CreateCell(34).SetCellValue("座椅后移电流结果");
                    headRow.CreateCell(35).SetCellValue("靠背向前电流");
                    headRow.CreateCell(36).SetCellValue("靠背向前电流结果");
                    headRow.CreateCell(37).SetCellValue("靠背向后电流");
                    headRow.CreateCell(38).SetCellValue("靠背向后电流结果");
                    headRow.CreateCell(39).SetCellValue("前端下降电流");
                    headRow.CreateCell(40).SetCellValue("前端下降电流结果");
                    headRow.CreateCell(41).SetCellValue("后端上升电流");
                    headRow.CreateCell(42).SetCellValue("后端上升电流结果");
                    headRow.CreateCell(43).SetCellValue("后端下降电流");
                    headRow.CreateCell(44).SetCellValue("后端下降电流结果");
                    
                }

                for (int i = 0; i < sumList.Rows.Count; i++)
                {
                    int row = i + 1;
                    HSSFRow dataRow = (HSSFRow)sheet.CreateRow(row);
                    dataRow.CreateCell(0).SetCellValue(sumList.Rows[i]["sn"].ToString());
                    dataRow.CreateCell(1).SetCellValue(sumList.Rows[i]["shebei"].ToString());
                    dataRow.CreateCell(2).SetCellValue(sumList.Rows[i]["part_type"].ToString());
                    dataRow.CreateCell(3).SetCellValue(sumList.Rows[i]["result_test"].ToString());
                    dataRow.CreateCell(4).SetCellValue(sumList.Rows[i]["used_time"].ToString());
                    dataRow.CreateCell(5).SetCellValue(sumList.Rows[i]["test_ymd"].ToString());
                    dataRow.CreateCell(6).SetCellValue(sumList.Rows[i]["test_hms"].ToString());
                    dataRow.CreateCell(7).SetCellValue(sumList.Rows[i]["mkbb_CAN"].ToString());

                    dataRow.CreateCell(8).SetCellValue(sumList.Rows[i]["mkbb_CAN_result"].ToString());
                    dataRow.CreateCell(9).SetCellValue(sumList.Rows[i]["qndz"].ToString());
                    dataRow.CreateCell(10).SetCellValue(sumList.Rows[i]["qndz_result"].ToString());
                    dataRow.CreateCell(11).SetCellValue(sumList.Rows[i]["qndddz"].ToString());
                    dataRow.CreateCell(12).SetCellValue(sumList.Rows[i]["qndddz_result"].ToString());
                    dataRow.CreateCell(13).SetCellValue(sumList.Rows[i]["aqdkwcrdz"].ToString());
                    dataRow.CreateCell(14).SetCellValue(sumList.Rows[i]["aqdkwcrdz_result"].ToString());
                    dataRow.CreateCell(15).SetCellValue(sumList.Rows[i]["aqdkcrdz"].ToString());

                    dataRow.CreateCell(16).SetCellValue(sumList.Rows[i]["aqdkcrdz_result"].ToString());
                    dataRow.CreateCell(17).SetCellValue(sumList.Rows[i]["aqdkcrsfdz"].ToString());
                    dataRow.CreateCell(18).SetCellValue(sumList.Rows[i]["aqdkcrsfdz_result"].ToString());
                    dataRow.CreateCell(19).SetCellValue(sumList.Rows[i]["sbrwjzdz"].ToString());
                    dataRow.CreateCell(20).SetCellValue(sumList.Rows[i]["sbrwjzdz_result"].ToString());
                    dataRow.CreateCell(21).SetCellValue(sumList.Rows[i]["sbrjzdz"].ToString());
                    dataRow.CreateCell(22).SetCellValue(sumList.Rows[i]["sbrjzdz_result"].ToString());
                    dataRow.CreateCell(23).SetCellValue(sumList.Rows[i]["sbrjzsfdz"].ToString());
                    dataRow.CreateCell(24).SetCellValue(sumList.Rows[i]["sbrjzsfdz_result"].ToString());

                    dataRow.CreateCell(25).SetCellValue(sumList.Rows[i]["zzyjrcandl"].ToString());
                    dataRow.CreateCell(26).SetCellValue(sumList.Rows[i]["zzyjrcandl_result"].ToString());
                    dataRow.CreateCell(27).SetCellValue(sumList.Rows[i]["zzydyjrcandl"].ToString());
                    dataRow.CreateCell(28).SetCellValue(sumList.Rows[i]["zzydyjrcandl_result"].ToString());
                    dataRow.CreateCell(29).SetCellValue(sumList.Rows[i]["yzyjrcandl"].ToString());
                    dataRow.CreateCell(30).SetCellValue(sumList.Rows[i]["yzyjrcandl_result"].ToString());
                    dataRow.CreateCell(31).SetCellValue(sumList.Rows[i]["zyqydl"].ToString());
                    dataRow.CreateCell(32).SetCellValue(sumList.Rows[i]["zyqydl_result"].ToString());
                    dataRow.CreateCell(33).SetCellValue(sumList.Rows[i]["zyhydl"].ToString());
                    dataRow.CreateCell(34).SetCellValue(sumList.Rows[i]["zyhydl_result"].ToString());

                    dataRow.CreateCell(35).SetCellValue(sumList.Rows[i]["kbxqdl"].ToString());
                    dataRow.CreateCell(36).SetCellValue(sumList.Rows[i]["kbxqdl_result"].ToString());
                    dataRow.CreateCell(37).SetCellValue(sumList.Rows[i]["kbxhdl"].ToString());
                    dataRow.CreateCell(38).SetCellValue(sumList.Rows[i]["kbxhdl_result"].ToString());
                    dataRow.CreateCell(39).SetCellValue(sumList.Rows[i]["qdxjdl"].ToString());
                    dataRow.CreateCell(40).SetCellValue(sumList.Rows[i]["qdxjdl_result"].ToString());
                    dataRow.CreateCell(41).SetCellValue(sumList.Rows[i]["hdssdl"].ToString());
                    dataRow.CreateCell(42).SetCellValue(sumList.Rows[i]["hdssdl_result"].ToString());
                    dataRow.CreateCell(43).SetCellValue(sumList.Rows[i]["hdxjdl"].ToString());
                    dataRow.CreateCell(44).SetCellValue(sumList.Rows[i]["hdxjdl_result"].ToString());
                }

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

                Response.Buffer = true;
                Response.AppendHeader("Content-Disposition", "attachment;filename=电检测信息" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
                Response.ContentEncoding = System.Text.Encoding.UTF8;
                Response.ContentType = "application/vnd.ms-excel";
                Response.Flush();
            }
            catch (Exception ex)
            { }
            return null;
        }








    }
}