using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Estsh.Web.Util;
using System.Data;
using Estsh.Web.Dal;
using System.Collections;
using System.IO;
using NPOI.HSSF.UserModel;
using Com.Estsh.MES.App;
using System.Text;
using Aspose.Cells;
using DbCommon;

/***************************************************************************************************
 * 
 * 作者：王勇
 * 
 * *************************************************************************************************/
namespace Estsh.Core.Services
{
    /// <summary>
    /// 
    /// </summary>
    public class VendorOrderPrintService
    {
        private VendorOrderPrintDal dal = new VendorOrderPrintDal(RemotingProxyProvider._remotingProxy);
        private int PRE_PAGE_ITEMS = 6;

        public Hashtable getStockVendorOrderPrintByPage(Pager pager, String direction, string str)
        {
            Hashtable result = new Hashtable();
            result = dal.getStockVendorOrderPrintByPage(pager.pageSize, pager.pageNo, str);
            return result;
        }

        public Hashtable getStockOrderDetailByPage(Pager pager, String direction, string str)
        {
            Hashtable result = new Hashtable();
            result = dal.getStockOrderDetailByPage(pager.pageSize, pager.pageNo, str);
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public bool changeOrderStatus(string PoNo_Data, string PartNo_Data, string TIME, string ztxm, string evrtp)
        {
            Hashtable result = new Hashtable();

            bool boolResult = dal.changeOrderStatus(ztxm);

            TIME = TIME.Substring(0, TIME.Length - 1);
            TIME = Convert.ToDateTime(TIME).ToString("yyyyMMdd");

            bool selectResult = dal.selectPrintState(PoNo_Data, PartNo_Data, TIME, evrtp);
            if (selectResult == false)
            {
                dal.updateDspoSetStaflg(PoNo_Data, PartNo_Data, TIME, evrtp);
            }

            return boolResult;
        }


        public bool changeOrderStatus(string EBELN)
        {
            Hashtable result = new Hashtable();

            bool boolResult = dal.updateDspoSetStaflg(EBELN);

            return boolResult;
        }
        /// <summary>
        /// 获取工厂
        /// </summary>
        /// <returns></returns>
        public ArrayList GetFactory()
        {
            Hashtable result = new Hashtable();
            DataTable dt = dal.GetFactory();
            return DataTypeConvert.NewObject.DataTableToArrayList(dt);
        }

        /// <summary>
        /// 获取供应商
        /// </summary>
        /// <returns></returns>
        public ArrayList GetVendor()
        {
            Hashtable result = new Hashtable();
            DataTable dt = dal.GetVendor();
            return DataTypeConvert.NewObject.DataTableToArrayList(dt);
        }

        public DataTable updatePrintData(string ebeln)
        {
            DataTable dt = new DataTable();
            dt = this.dal.updatePrintData(ebeln);

            return dt;
        }


        /// <summary>
        /// 订单确认
        /// </summary>
        /// <returns></returns>
        public bool dingDanQueRen(string ebeln)
        {
            Hashtable result = new Hashtable();
            bool bool_1 = dal.dingDanQueRen(ebeln);
            return bool_1;
        }

        /// <summary>
        /// 发运确认
        /// </summary>
        /// <returns></returns>
        public bool shipQueRen(string ebeln)
        {
            Hashtable result = new Hashtable();
            bool bool_1 = dal.shipQueRen(ebeln);
            return bool_1;
        }

        public Hashtable exportBarcodeInfo(string excelTemplate, string folder, string weekNo)
        {
            Hashtable result = new Hashtable();
            //判断条码是否生成
            //if (!dal.IsBuildCode(weekNo))
            //{
            //    result.Add("message", weekNo + ":该订单的条码未生成，请先生成条码！");
            //    result.Add("flag", "error");
            //    return result;
            //}

            DataTable dt = dal.GetCartonNo(weekNo);

            //if (dt == null || dt.Rows.Count < 1)
            //{
            //    result.Add("message", "没有需要导出的条码数据，请确认！");
            //    result.Add("flag", "error");
            //    return result;
            //}

            ////判断条码是否已导出
            //if (dal.GetDspoYN(weekNo))
            //{
            //    //未导出,第一次打导出标志
            //    dal.UpdateData(weekNo);
            //}
            string saveFolder = folder;
            //if (!Directory.Exists(saveFolder))
            //{
            //    Directory.CreateDirectory(saveFolder);
            //}

            string str = Export(excelTemplate, saveFolder, weekNo);
            if (!(string.IsNullOrEmpty(str)))
            {
                String zipFile = folder + weekNo + "-" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".zip";
                result.Add("fileName", str);
                result.Add("message", "导出数据成功！");
                result.Add("flag", "OK");
                return result;
            }
            else
            {
                result.Add("message", "导出数据异常，请重试！");
                result.Add("flag", "error");
                return result;
            }
        }

        public string Export(string excelTemplate, string saveFolder, String weekNo)
        {
            DataTable dt = dal.GetCartonNo(weekNo);

            string str = "";

            try
            {
                //创建数据集，用来存放选中的数据
                DataTable dtRePrintSome = new DataTable("dtData");
                dtRePrintSome.Columns.Add("customer", Type.GetType("System.String"));   //客户名称
                dtRePrintSome.Columns.Add("VendorName", Type.GetType("System.String"));  //供应商名称 
                dtRePrintSome.Columns.Add("ZTXM", Type.GetType("System.String"));        //箱条码
                dtRePrintSome.Columns.Add("MATNR", Type.GetType("System.String"));       //零件号
                dtRePrintSome.Columns.Add("MAKTX", Type.GetType("System.String"));        //零件描述
                dtRePrintSome.Columns.Add("ZDEV_NUM", Type.GetType("System.String"));    //数量
                dtRePrintSome.Columns.Add("ModelName", Type.GetType("System.String"));   //项目名称
                dtRePrintSome.Columns.Add("ProdDate", Type.GetType("System.String"));   //生产日期
                dtRePrintSome.Columns.Add("LotNo", Type.GetType("System.String"));      //送货批次
                dtRePrintSome.Columns.Add("SEQ", Type.GetType("System.String"));      //标签序号
                dtRePrintSome.Columns.Add("subEBELN", Type.GetType("System.String"));   //订单号

                string factoryName = dal.GetFactoryName();//获取客户名称


                //初始化获取第一个单号
                string _orderNo = dt.Rows[0]["order_no"].ToString();
                string _vendorNname = dt.Rows[0]["vendor_name"].ToString();
                string _vendor_code = dt.Rows[0]["vendor_code"].ToString();
                int _CodeCount = 0;

                int j = 0;

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (i == 0)
                    {
                        _CodeCount = dal.GetWmsCodeCount(_orderNo, dt.Rows[i]["part_no"].ToString(), dt.Rows[i]["evrtp"].ToString());
                        j = 0;
                    }
                    else
                    {
                        if (_orderNo != dt.Rows[i]["order_no"].ToString() || dt.Rows[i]["part_no"].ToString() != dt.Rows[i - 1]["part_no"].ToString() || dt.Rows[i]["evrtp"].ToString() != dt.Rows[i - 1]["evrtp"].ToString())
                        {
                            _CodeCount = dal.GetWmsCodeCount(dt.Rows[i]["order_no"].ToString(), dt.Rows[i]["part_no"].ToString(), dt.Rows[i]["evrtp"].ToString());
                            j = 0;
                        }
                    }


                    if (_orderNo == dt.Rows[i]["order_no"].ToString()) //判断是否与上条数据为同一单号
                    {
                        string orderNo = _orderNo + "_" + dt.Rows[j]["version"].ToString();
                        j++;
                        string seq = j.ToString() + "/" + _CodeCount.ToString();
                        //将选中的数据存放到数据集中
                        dtRePrintSome.Rows.Add(new Object[] { factoryName, dt.Rows[i]["vendor_name"].ToString(), 
                                                dt.Rows[i]["carton_no"].ToString(), dt.Rows[i]["part_no"].ToString(),
                                                dt.Rows[i]["part_spec"].ToString(), Convert.ToDecimal(dt.Rows[i]["zdev_num"].ToString()).ToString ("0.###"),
                                                dt.Rows[i]["option3"].ToString(),string.Empty,dt.Rows[i]["zdate"].ToString().Replace("-",""), 
                                                seq, orderNo});
                    }

                    else //新的单号，导出前table，重新赋值
                    {
                        //导出前table
                        string fileName = saveFolder + _orderNo + "-" + _vendor_code + "-" + _vendorNname + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx";
                        ExportSave(dtRePrintSome, fileName, dtRePrintSome.Rows.Count, _orderNo, excelTemplate);

                        //重新初始化
                        _orderNo = dt.Rows[i]["order_no"].ToString();
                        _vendorNname = dt.Rows[i]["vendor_name"].ToString();
                        _vendor_code = dt.Rows[i]["vendor_code"].ToString();
                        _CodeCount = dal.GetWmsCodeCount(dt.Rows[i]["order_no"].ToString(), dt.Rows[i]["part_no"].ToString(), dt.Rows[i]["evrtp"].ToString());
                        j = 1;

                        //删除所有行
                        dtRePrintSome.Rows.Clear();

                        string orderNo = _orderNo + "_" + dt.Rows[j]["version"].ToString();

                        string seq = j.ToString() + "/" + _CodeCount.ToString();
                        //将选中的数据存放到数据集中
                        dtRePrintSome.Rows.Add(new Object[] { factoryName, dt.Rows[i]["vendor_name"].ToString(), 
                                                dt.Rows[i]["carton_no"].ToString(), dt.Rows[i]["part_no"].ToString(),
                                                dt.Rows[i]["part_spec"].ToString(), Convert.ToDecimal(dt.Rows[i]["zdev_num"].ToString()).ToString ("0.###"),
                                                dt.Rows[i]["option3"].ToString(),string.Empty,dt.Rows[i]["zdate"].ToString().Replace("-",""), 
                                                seq, orderNo});
                    }

                    //判断是否为最后一条数据
                    if (i == dt.Rows.Count - 1)
                    {
                        //导出当前table
                        string strName = _orderNo + "-" + _vendor_code + "-" + _vendorNname + "-" + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx";
                        string fileName = saveFolder + strName;
                        ExportSave(dtRePrintSome, fileName, dtRePrintSome.Rows.Count, _orderNo, excelTemplate);
                        str = strName;
                    }

                }
                return str;
            }
            catch (System.Exception ex)
            {
                //return false;
                return "";
            }
        }


        public void ExportSave(DataTable table, string FileName, int jCount, string orderNo, string excelTemplate)
        {
            int sheetIndex = 0;
            int cellIndex = 0;
            WorkbookDesigner designer = new WorkbookDesigner();
            designer.Open(excelTemplate);

            //获取汇总明细
            //DataSet ds = dal.GetExpInfo(orderNo);

            //DataTable dtHead = ds.Tables[2];


            //designer.SetDataSource("recymd", ds.Tables[0].Rows[0]["recymd"] is DBNull ? "" : ds.Tables[0].Rows[0]["recymd"].ToString());
            //designer.SetDataSource("vendorCode", ds.Tables[0].Rows[0]["lifnr"] is DBNull ? "" : ds.Tables[0].Rows[0]["lifnr"].ToString());
            //designer.SetDataSource("vendorSale", ds.Tables[0].Rows[0]["vendor_sale"] is DBNull ? "" : ds.Tables[0].Rows[0]["vendor_sale"].ToString());
            //designer.SetDataSource("vendorTel", ds.Tables[0].Rows[0]["vendor_tel"] is DBNull ? "" : ds.Tables[0].Rows[0]["vendor_tel"].ToString());
            //designer.SetDataSource("vendorFax", ds.Tables[0].Rows[0]["vendor_fax"] is DBNull ? "" : ds.Tables[0].Rows[0]["vendor_fax"].ToString());
            //designer.SetDataSource("warehouseDesc", ds.Tables[0].Rows[0]["lgort"] is DBNull ? "" : ds.Tables[0].Rows[0]["lgort"].ToString());
            //designer.SetDataSource("emp_name", ds.Tables[0].Rows[0]["emp_name"] is DBNull ? "" : ds.Tables[0].Rows[0]["emp_name"].ToString());

            ////填写订单号
            //designer.Workbook.Worksheets[0].Cells[4, 10].PutValue(orderNo);

            //int BeginRow = 24;


            ////填写时间
            //DataTable dtDate = dal.GetStartDate(orderNo);

            //for (int i = 0; i < dtDate.Rows.Count; i++)
            //{
            //    dtHead.Columns.Add(new DataColumn(dtDate.Rows[i]["InDate"].ToString(), typeof(string)));
            //    designer.Workbook.Worksheets[0].Cells[23, 2 + i].PutValue(dtDate.Rows[i]["InDate"].ToString());
            //}

            ////插入行
            //if (dtHead.Rows.Count > 1)
            //{
            //    designer.Workbook.Worksheets[0].Cells.InsertRows(BeginRow, dtHead.Rows.Count - 1);
            //}

            ////填写零件号
            //for (int i = 0; i < dtHead.Rows.Count; i++)
            //{
            //    designer.Workbook.Worksheets[0].Cells[i + BeginRow, 0].PutValue(dtHead.Rows[i]["matnr"].ToString());
            //    designer.Workbook.Worksheets[0].Cells[i + BeginRow, 1].PutValue(dtHead.Rows[i]["part_spec"].ToString());
            //}

            ////填写数量
            //DataTable dtDetail = ds.Tables[1];

            //for (int i = 0; i < dtDetail.Rows.Count; i++)
            //{

            //    for (int q = 0; q < dtHead.Rows.Count; q++)
            //    {
            //        if (dtDetail.Rows[i]["PartNo"].ToString() == dtHead.Rows[q]["matnr"].ToString())
            //        {
            //            for (int j = 0; j < dtHead.Columns.Count; j++)
            //            {
            //                if (dtDetail.Rows[i]["InDate"].ToString() == dtHead.Columns[j].ColumnName.ToString())
            //                {
            //                    decimal dQty = Convert.ToDecimal(dtDetail.Rows[i]["Qty"].ToString());
            //                    int sQty = Convert.ToInt32(dQty);
            //                    designer.Workbook.Worksheets[0].Cells[BeginRow + q, j].PutValue(sQty.ToString());
            //                }
            //            }
            //        }
            //    }
            //}

            //designer.Process(0, true);


            //sheet2写入条码明细
            DataTable dtCode = dal.GetCodeDetail(orderNo);
            for (int i = 0; i < dtCode.Rows.Count; i++)
            {
                designer.Workbook.Worksheets[0].Cells[i + 1, 0].PutValue(dtCode.Rows[i]["factory_name"].ToString());
                designer.Workbook.Worksheets[0].Cells[i + 1, 1].PutValue(dtCode.Rows[i]["vendor_name"].ToString());
                designer.Workbook.Worksheets[0].Cells[i + 1, 2].PutValue(dtCode.Rows[i]["ZTXM"].ToString());
                designer.Workbook.Worksheets[0].Cells[i + 1, 3].PutValue(dtCode.Rows[i]["MATNR"].ToString());
                designer.Workbook.Worksheets[0].Cells[i + 1, 4].PutValue(dtCode.Rows[i]["part_spec"].ToString());
                designer.Workbook.Worksheets[0].Cells[i + 1, 5].PutValue(dtCode.Rows[i]["ZDEV_NUM"].ToString());
                designer.Workbook.Worksheets[0].Cells[i + 1, 6].PutValue(dtCode.Rows[i]["option3"].ToString());
                designer.Workbook.Worksheets[0].Cells[i + 1, 7].PutValue(dtCode.Rows[i]["ZDATE"].ToString());
                designer.Workbook.Worksheets[0].Cells[i + 1, 8].PutValue(dtCode.Rows[i]["seq"].ToString());
                designer.Workbook.Worksheets[0].Cells[i + 1, 9].PutValue(dtCode.Rows[i]["order_no"].ToString());
            }
            designer.Process(0, true);

            //写入箱条码
            for (int rowIndex = 0; rowIndex < jCount; rowIndex++)
            {
                cellIndex = (rowIndex % PRE_PAGE_ITEMS) + 1;
                sheetIndex = (rowIndex / PRE_PAGE_ITEMS) + 1;


                if (0 == rowIndex % PRE_PAGE_ITEMS)
                {
                    designer.Workbook.Worksheets.AddCopy(sheetIndex);
                }

                designer.SetDataSource("barcode" + cellIndex, "*" + table.Rows[rowIndex]["ZTXM"].ToString() + "*");
                designer.SetDataSource("supplier" + cellIndex, table.Rows[rowIndex]["VendorName"].ToString());
                designer.SetDataSource("customer" + cellIndex, table.Rows[rowIndex]["customer"].ToString());
                designer.SetDataSource("partNo" + cellIndex, table.Rows[rowIndex]["MATNR"].ToString());
                designer.SetDataSource("partSpec" + cellIndex, table.Rows[rowIndex]["MAKTX"].ToString());
                designer.SetDataSource("qty" + cellIndex, table.Rows[rowIndex]["ZDEV_NUM"].ToString());
                designer.SetDataSource("lotNo" + cellIndex, table.Rows[rowIndex]["LotNo"].ToString());
                designer.SetDataSource("model" + cellIndex, table.Rows[rowIndex]["ModelName"].ToString());
                designer.SetDataSource("serialNo" + cellIndex, table.Rows[rowIndex]["SEQ"].ToString());
                designer.SetDataSource("prodDate" + cellIndex, "" + table.Rows[rowIndex]["ProdDate"].ToString() + "");
                designer.SetDataSource("poNo" + cellIndex, table.Rows[rowIndex]["subEBELN"].ToString());

                designer.Process(sheetIndex, true);
                designer.ClearDataSource();
            }

            designer.Save(FileName, FileFormatType.Excel2007Xlsx);

        }


    }
}