using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using GThinking.N_MISPRO;
using GThinking.N_MISPRO.Data;
using GThinking.N_MODEL.Page;
using GThinking.N_WORKFLOW;
using GThinking.N_MODEL.Dialog;
using GThinking.N_PUBLIC;
using GThinking.N_MISPRO.Utility;
using GThinking.N_CONTROL;
using System.IO;
using System.Data.OleDb;
using System.Data.Common;
using System.Collections.Generic;

namespace GThinking.N_XLXSBJ
{
    public partial class CLYL_DRYLInfo : SingleCardBase
    {
        CLYL_DRYL2 entity = new CLYL_DRYL2();

        protected void Page_Load(object sender, EventArgs e)
        {
            this.UMID = 100860014;
            this.UMName = "线缆销售报价材料用量";
            base.CurMaster = Master;
            base.CurEntity = entity;
            this.PageHeight = 180;
            this.PageWidth = 420;
            Master.EnableAttachBtn = false;
            Master.EnableCancelBtn = false;
            Master.EnableCopyBtn = false;
            Master.EnableDeleteBtn = false;
            Master.EnableEditBtn = false;
            Master.EnableInsertBtn = false;
            Master.EnablePrintBtn = false;
            Master.EnableRefreshBtn = false;
            Master.EnableSaveBtn = false;
            fupdPost.FileSaveType = gtAsyncFileUpload.DFileSaveType.SaveToPath;
            fupdPost.Filter = "XLS,XLSX";

            if (!IsPostBack)
            {
                TComboBox.SetComboBox("DBERP", cbxGCID, "select GC_GCID,GC_GCMC from GC where 1=1 and ISNULL(GC_TYBZ,' ')<>'Y' order by GC_GCID ", false);
                if (cbxGCID.ItemsCount == 1)
                    cbxGCID.SelectedIndex = 0;
            }
            base.InitPage();
        }

        #region 控件事件

        protected void btnQD_Click(object sender, EventArgs e)
        {
            if (cbxGCID.SelectedValue.Trim().Equals(""))
            {
                ShowMessage("请选择工厂！");
                return;
            }
            if (fupdPost.FilePath.Equals(""))
            {
                ShowMessage("请选择需要导入的文件！");
                return;
            }

            string sAnswer = "";
            Confirm.Register("IsQD", "是否需要用最新材料物料用量更新各线缆产品的材料用量？");
            if (Confirm.GetValue("IsQD").Trim().Equals(""))
            {
                Confirm.Ask("IsQD");
                return;
            }
            else if (Confirm.GetValue("IsQD").Trim().Equals("N"))
            {
                //表示进行了提示并选择了取消，直接返回或其他处理
                Confirm.Clear();
                sAnswer = "N";
            }
            else if (Confirm.GetValue("IsQD").Trim().Equals("Y"))
            {
                //表示进行了提示并选择了确认
                Confirm.Clear();
                sAnswer = "Y";
            }

            TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
            TSQL sSQL = new TSQL(dberp);
            try
            {
                lblTitle.Text = "正在导入数据，请稍后...";
                dberp.BeginTrans();
                DataSet aDataExcel = new DataSet();
                ReadWJ(fupdPost.FilePath, ref aDataExcel);
                ExcelColumnValid(aDataExcel);
                InsertXLXSCLWLYL(dberp, sSQL, aDataExcel);
                if (sAnswer.Equals("Y"))
                {
                    UpdateXLXSCLQD(dberp, sSQL);
                }
                dberp.CommitTrans();
                if (sAnswer.Equals("Y"))
                    ShowMessage("完成材料物料用量导入，并同步更新了各线缆产品的材料用量！");
                else
                    ShowMessage("完成材料物料用量导入！");
                CloseWindow();
            }
            catch (Exception ex)
            {
                lblTitle.Text = "";
                dberp.RollbackTrans();
                ShowMessage(ex.Message);
            }
        }

        protected void btnQX_Click(object sender, EventArgs e)
        {
            CloseWindow();
        }

        #endregion

        #region 自定义事件

        private void UpdateXLXSCLQD(TDatabase dberp, TSQL sSQL)
        {
            sSQL.Clear();
            sSQL.Add("update XLXSCLQD set ");
            for (int i = 1; i < 80; i++)
            {
                sSQL.Add(string.Format("XLXSCLQD_SL{0}=SL{0},", i.ToString().PadLeft(2, '0')));
            }
            sSQL.Add("XLXSCLQD_SL80=SL80 ");
            sSQL.Add(" from (");

            sSQL.Add("select XLXSCLWLYL_CPWLID as WLID, ");
            for (int i = 1; i < 80; i++)
            {
                sSQL.Add(string.Format("sum(case when XLXSBJCLWL_BJCLID='{0}' then XLXSCLWLYL_SL else 0.0 end) as SL{1}, ", "BJCL" + i.ToString().PadLeft(2, '0') + "0000", i.ToString().PadLeft(2, '0')));
            }
            sSQL.Add("sum(case when XLXSBJCLWL_BJCLID='BJCL800000' then XLXSCLWLYL_SL else 0.0 end) as SL80 ");
            sSQL.Add(" from XLXSCLWLYL,XLXSBJCLWL ");
            sSQL.Add(" where XLXSCLWLYL_GCID=:pGCID ");
            sSQL.Add("   and XLXSCLWLYL_WLID=XLXSBJCLWL_WLID ");
            sSQL.Add("group by XLXSCLWLYL_CPWLID");
            sSQL.Add(") a ");
            sSQL.Add("where XLXSCLQD_GCID=:pGCID and a.WLID=XLXSCLQD_WLID ");
            sSQL.ParamByName("pGCID").Value = cbxGCID.SelectedValue.Trim();
            dberp.ExecSQL(sSQL);
        }

        private void InsertXLXSCLWLYL(TDatabase dberp, TSQL sSQL, DataSet ds)
        {
            int KeyRun = 200;
            int tmpRun = 1;//循环KeyRun次执行1次SQL
            TSQL sqlDel = new TSQL(dberp);
            string strDel = "";
            string strIns = "";
            string sWHR = tSession.UserName;
            string sWHRID = tSession.UserId;
            string sWHSJ = DateTime.Now.ToString();

            foreach (DataRow Row in ds.Tables[0].Rows)
            {
                string sCPWLID = Row[0].ToString().Trim();
                string sWLID = Row[2].ToString().Trim();
                double fSL = CMISproConvert.StrToFloat(Row[4].ToString(), 0);
                string sSL = "0";
                if (fSL.ToString().ToUpper().Contains("E"))//科学记数法
                {
                    sSL = fSL.ToString("0.000000");//显示6位小数
                }
                else
                    sSL = fSL.ToString();
                

                if (!sCPWLID.Equals("") && !sWLID.Equals(""))
                {
                    if (strDel.Trim().Equals(""))
                        strDel = string.Format("(XLXSCLWLYL_CPWLID='{0}' and XLXSCLWLYL_WLID='{1}')", sCPWLID, sWLID);
                    else
                        strDel += string.Format(" or (XLXSCLWLYL_CPWLID='{0}' and XLXSCLWLYL_WLID='{1}')", sCPWLID, sWLID);

                    if (strIns.Trim().Equals(""))
                        strIns = string.Format("select '{0}' as GC,'{1}' as CPWL,'{2}' as WL,{3} as SL,'{4}' as WHR,'{5}' as WHRID,'{6}' as WHSJ ",
                            cbxGCID.SelectedValue.Trim(), sCPWLID, sWLID, sSL, sWHR, sWHRID, sWHSJ);
                    else
                    {
                        strIns += "\r\n" + " union all " + "\r\n";
                        strIns += string.Format("select '{0}' as GC,'{1}' as CPWL,'{2}' as WL,{3} as SL,'{4}' as WHR,'{5}' as WHRID,'{6}' as WHSJ ",
                            cbxGCID.SelectedValue.Trim(), sCPWLID, sWLID, sSL, sWHR, sWHRID, sWHSJ);
                    }
                }

                if (tmpRun >= KeyRun && (tmpRun % KeyRun == 0))
                {
                    if (!strDel.Trim().Equals(""))
                    {
                        sqlDel.Clear();
                        sqlDel.Add("delete from XLXSCLWLYL where XLXSCLWLYL_GCID=:pGCID ");
                        sqlDel.Add(" and (" + strDel + ")");
                        sqlDel.ParamByName("pGCID").Value = cbxGCID.SelectedValue.Trim();
                        dberp.ExecSQL(sqlDel);
                        strDel = "";
                    }

                    if (!strIns.Trim().Equals(""))
                    {
                        sSQL.Clear();
                        sSQL.Add("insert into XLXSCLWLYL(XLXSCLWLYL_GCID,XLXSCLWLYL_CPWLID,XLXSCLWLYL_WLID,XLXSCLWLYL_SL,XLXSCLWLYL_WHR,XLXSCLWLYL_WHRID,XLXSCLWLYL_WHSJ) ");
                        sSQL.Add(strIns);
                        dberp.ExecSQL(sSQL);
                        strIns = "";
                    }
                }

                tmpRun++;
            }

            if (!strDel.Trim().Equals(""))
            {
                sqlDel.Clear();
                sqlDel.Add("delete from XLXSCLWLYL where XLXSCLWLYL_GCID=:pGCID ");
                sqlDel.Add(" and (" + strDel + ")");
                sqlDel.ParamByName("pGCID").Value = cbxGCID.SelectedValue.Trim();
                dberp.ExecSQL(sqlDel);
                strDel = "";
            }

            if (!strIns.Trim().Equals(""))
            {
                sSQL.Clear();
                sSQL.Add("insert into XLXSCLWLYL(XLXSCLWLYL_GCID,XLXSCLWLYL_CPWLID,XLXSCLWLYL_WLID,XLXSCLWLYL_SL,XLXSCLWLYL_WHR,XLXSCLWLYL_WHRID,XLXSCLWLYL_WHSJ) ");
                sSQL.Add(strIns);
                dberp.ExecSQL(sSQL);
                strIns = "";
            }
        }

        private void ExcelColumnValid(DataSet ds)
        {
            Dictionary<string, string> ColumnNames = new Dictionary<string, string>();
            ColumnNames.Add("0", "产品物料编码");
            ColumnNames.Add("1", "产品物料信息");
            ColumnNames.Add("2", "材料物料编码");
            ColumnNames.Add("3", "材料物料信息");
            ColumnNames.Add("4", "材料物料用量");
            ColumnNames.Add("5", "报价材料");

            for (int i = 0; i < 5; i++)
            {
                try
                {
                    if (!ds.Tables[0].Columns[i].ColumnName.Trim().Equals(ColumnNames[i.ToString()].Trim()))
                    {
                        throw new Exception("导入文件的内容格式不正确，请按记录导出的excel格式调整导入文件！");
                    }
                }
                catch
                {
                    throw new Exception("导入文件的内容格式不正确，请按记录导出的excel格式调整导入文件！");
                }
            }
        }

        private void ReadWJ(string sPath, ref DataSet aDataExcel)
        {
            string sMessage = "";
            aDataExcel = TExcelData.ReadExcel(sPath.Trim(), "", "", false, ref sMessage);
            if (!sMessage.Trim().Equals(""))
            {
                throw new Exception("文件读取失败！");
            }
        }

        private string sGetCellTest(DataRow Row, int ColumnIndex)
        {
            return Row[ColumnIndex].ToString().Trim().Replace("\r\n", "").Replace("\n", "");
        }

        #endregion
    }
}