using System;
using System.Data;
using System.Configuration;
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.Data;
using GThinking.N_MISPRO;
using System.Collections.Generic;
using GThinking.N_MISPRO.Utility;

namespace GThinking.U20261363_N_XLXSBJ
{
    public class XLWBBJDUtils
    {
        public static string sTSDYDJIDList = ",AC10KV,";//字母统一大写
        //报价产品解析时，进行电压等级替换
        public static Dictionary<string, string> GetDYDJTH()
        {
            Dictionary<string, string> dic = new Dictionary<string, string>();
            TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
            TSQL SQL = new TSQL(dberp);
            SQL.Clear();
            SQL.Add("select XLWBBJDDYDZB_WBDYDJID,XLWBBJDDYDZB_DYDJID from XLWBBJDDYDZB order by LEN(XLWBBJDDYDZB_WBDYDJID) DESC ");
            DataSet dsXLDYDJ = dberp.OpenDataSet(SQL);
            if (dsXLDYDJ.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < dsXLDYDJ.Tables[0].Rows.Count; i++)
                {
                    dic.Add(dsXLDYDJ.Tables[0].Rows[i]["XLWBBJDDYDZB_WBDYDJID"].ToString().ToUpper().Trim(), dsXLDYDJ.Tables[0].Rows[i]["XLWBBJDDYDZB_DYDJID"].ToString().Trim());
                }
            }

            return dic;
        }

        /// <summary>
        /// 统一使用年度+6位流水号编码
        /// </summary>
        /// <param name="sExcel"></param>
        /// <returns></returns>
        public static string GetNewWBBJDID()
        {
            return CMISproDataBase.GetYWBHByN("DBERP", "XLWBBJDML", "XLWBBJDML_WBBJDID", 6);
        }

        private static string[] GetWBBJDDRYZPZList()
        {
            string sYZList = XLBJDUtils.GetXLXSBJPZXX(100860043).XLXSBJPZ_WBBJDDRYZ.Trim();
            if (sYZList.Equals(""))
                return new string[0];
            return sYZList.Split(',');
        }

        public static string[] GetWBBJDDRYZList()
        {
            return GetWBBJDDRYZPZList();
        }

        /// <summary>
        /// 报价单ID为空时不加载因子；不为空时返回配置因子与报价分类因子的交集（保持配置顺序）
        /// </summary>
        public static string[] GetWBBJDDRYZList(string sBJDID)
        {
            if (sBJDID.Trim().Equals(""))
                return new string[0];
            string[] arrPZ = GetWBBJDDRYZPZList();
            if (arrPZ.Length == 0)
                return new string[0];
            string sBJFLID = XLBJDUtils.GetBJFLXXByBJDID(sBJDID).XLXSBJFL_BJFLID.Trim();
            if (sBJFLID.Equals(""))
                return new string[0];
            Dictionary<string, bool> dicBJFLYZ = GetBJFLYZDict(sBJFLID);
            if (dicBJFLYZ.Count == 0)
                return new string[0];
            List<string> arrResult = new List<string>();
            foreach (string sYZ in arrPZ)
            {
                string sYZID = sYZ.Trim();
                if (!sYZID.Equals("") && dicBJFLYZ.ContainsKey(sYZID))
                    arrResult.Add(sYZID);
            }
            return arrResult.ToArray();
        }

        private static Dictionary<string, bool> GetBJFLYZDict(string sBJFLID)
        {
            Dictionary<string, bool> dic = new Dictionary<string, bool>();
            TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
            TSQL SQL = new TSQL(dberp);
            SQL.Add("select XLXSBJFLYZ_YZID from XLXSBJFLYZ where XLXSBJFLYZ_BJFLID=:pBJFLID");
            SQL.ParamByName("pBJFLID").Value = sBJFLID.Trim();
            DataSet ds = dberp.OpenDataSet(SQL);
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                string sYZID = ds.Tables[0].Rows[i]["XLXSBJFLYZ_YZID"].ToString().Trim();
                if (!sYZID.Equals("") && !dic.ContainsKey(sYZID))
                    dic.Add(sYZID, true);
            }
            return dic;
        }

        /// <summary>
        /// 因子栏目在XLWBBJDLM_ZDLMLX中的标记，使用小写字母a-z（最多20个）
        /// </summary>
        public static string GetWBBJDYZZLMKey(int iIndex)
        {
            return ((char)('a' + iIndex)).ToString();
        }

        public static bool IsWBBJDYZZLM(string sKey)
        {
            return sKey.Length == 1 && sKey[0] >= 'a' && sKey[0] <= 'z';
        }

        public static int GetWBBJDYZZIndexByLMKey(string sKey)
        {
            if (!IsWBBJDYZZLM(sKey))
                return -1;
            return sKey[0] - 'a';
        }

        public static bool IsWBBJDMXYZZField(string sField)
        {
            if (!sField.StartsWith("XLWBBJDMXYZZ_YZZ") || sField.Length != 18)
                return false;
            int iIdx = 0;
            return int.TryParse(sField.Substring(16), out iIdx) && iIdx >= 1 && iIdx <= 20;
        }

        public static Dictionary<string, string> GetWBBJDYZZGDLM()
        {
            return GetWBBJDYZZGDLM("");
        }

        /// <summary>
        /// 报价单ID为空时不加载因子栏目；不为空时加载配置因子与报价分类因子的交集
        /// </summary>
        public static Dictionary<string, string> GetWBBJDYZZGDLM(string sBJDID)
        {
            Dictionary<string, string> dic = new Dictionary<string, string>();
            if (sBJDID.Trim().Equals(""))
                return dic;
            string[] arrPZ = GetWBBJDDRYZPZList();
            if (arrPZ.Length == 0)
                return dic;
            string sBJFLID = XLBJDUtils.GetBJFLXXByBJDID(sBJDID).XLXSBJFL_BJFLID.Trim();
            if (sBJFLID.Equals(""))
                return dic;
            Dictionary<string, bool> dicBJFLYZ = GetBJFLYZDict(sBJFLID);
            if (dicBJFLYZ.Count == 0)
                return dic;

            List<string> arrIntersect = new List<string>();
            for (int i = 0; i < arrPZ.Length && i < 20; i++)
            {
                string sYZID = arrPZ[i].Trim();
                if (!sYZID.Equals("") && dicBJFLYZ.ContainsKey(sYZID))
                    arrIntersect.Add(sYZID);
            }
            if (arrIntersect.Count == 0)
                return dic;

            TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
            TSQL SQL = new TSQL(dberp);
            SQL.Add("select XLXSTZYZ_YZID, XLXSTZYZ_YZMC from XLXSTZYZ ");
            SQL.Add(" where XLXSTZYZ_MXBH='BJD' and " + CMISproDataBase.GetWhereEqual("XLXSTZYZ_YZID", string.Join(",", arrIntersect.ToArray())));
            SQL.Add(" order by XLXSTZYZ_YZID ");
            DataSet ds = dberp.OpenDataSet(SQL);
            Dictionary<string, string> dicYZMC = new Dictionary<string, string>();
            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                dicYZMC.Add(ds.Tables[0].Rows[i]["XLXSTZYZ_YZID"].ToString().Trim(), ds.Tables[0].Rows[i]["XLXSTZYZ_YZMC"].ToString().Trim());
            }
            for (int i = 0; i < arrPZ.Length && i < 20; i++)
            {
                string sYZID = arrPZ[i].Trim();
                if (!sYZID.Equals("") && dicBJFLYZ.ContainsKey(sYZID) && dicYZMC.ContainsKey(sYZID))
                    dic.Add(GetWBBJDYZZLMKey(i), dicYZMC[sYZID]);
            }
            return dic;
        }

        public static void InsXLWBBJDMXYZZ(TDatabase dberp, TSQL SQL, string sWBBJDID, string sXLBJDID)
        {
            string[] arrYZ = GetWBBJDDRYZList();
            if (arrYZ.Length == 0)
                return;
            int iYZCount = arrYZ.Length > 20 ? 20 : arrYZ.Length;

            Dictionary<int, string> dicLMIndex = new Dictionary<int, string>();
            SQL.Clear();
            SQL.Add("select XLWBBJDLM_ZDLMLX, XLWBBJDLM_LMBH from XLWBBJDLM ");
            SQL.Add(" where XLWBBJDLM_WBBJDID=:pWBBJDID and XLWBBJDLM_BJDID=:pBJDID ");
            SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
            SQL.ParamByName("pBJDID").Value = sXLBJDID;
            DataSet dsLM = dberp.OpenDataSet(SQL);
            bool bHasYZLM = false;
            for (int i = 0; i < dsLM.Tables[0].Rows.Count; i++)
            {
                string sZDLMLX = dsLM.Tables[0].Rows[i]["XLWBBJDLM_ZDLMLX"].ToString().Trim();
                if (IsWBBJDYZZLM(sZDLMLX))
                {
                    int iIdx = GetWBBJDYZZIndexByLMKey(sZDLMLX);
                    if (iIdx >= 0 && iIdx < iYZCount)
                    {
                        dicLMIndex[iIdx] = dsLM.Tables[0].Rows[i]["XLWBBJDLM_LMBH"].ToString().Trim();
                        bHasYZLM = true;
                    }
                }
            }
            if (!bHasYZLM)
                return;

            SQL.Clear();
            SQL.Add("delete from XLWBBJDMXYZZ where XLWBBJDMXYZZ_WBBJDID=:pWBBJDID and XLWBBJDMXYZZ_BJDID=:pBJDID");
            SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
            SQL.ParamByName("pBJDID").Value = sXLBJDID;
            dberp.ExecSQL(SQL);

            SQL.Clear();
            SQL.Add("select XLWBBJDNR_DRHH");
            for (int i = 0; i < iYZCount; i++)
            {
                if (dicLMIndex.ContainsKey(i))
                    SQL.Add(string.Format(",isnull(rtrim(XLWBBJDNR_LMNR{0}),0.0) as YZZ{1}", dicLMIndex[i], (i + 1).ToString().PadLeft(2, '0')));
                else
                    SQL.Add(string.Format(",0.0 as YZZ{0}", (i + 1).ToString().PadLeft(2, '0')));
            }
            SQL.Add(" from XLWBBJDNR where XLWBBJDNR_WBBJDID=:pWBBJDID and XLWBBJDNR_BJDID=:pBJDID order by XLWBBJDNR_DRHH");
            SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
            SQL.ParamByName("pBJDID").Value = sXLBJDID;
            DataSet dsNR = dberp.OpenDataSet(SQL);

            SQL.Clear();
            SQL.Add("insert into XLWBBJDMXYZZ(XLWBBJDMXYZZ_WBBJDID,XLWBBJDMXYZZ_DRHH,XLWBBJDMXYZZ_BJDID");
            for (int i = 1; i <= 20; i++)
                SQL.Add(string.Format(",XLWBBJDMXYZZ_YZZ{0}", i.ToString().PadLeft(2, '0')));
            SQL.Add(") values(:pWBBJDID,:pDRHH,:pBJDID");
            for (int i = 1; i <= 20; i++)
                SQL.Add(string.Format(",:pYZZ{0}", i.ToString().PadLeft(2, '0')));
            SQL.Add(")");

            for (int i = 0; i < dsNR.Tables[0].Rows.Count; i++)
            {
                SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
                SQL.ParamByName("pDRHH").Value = dsNR.Tables[0].Rows[i]["XLWBBJDNR_DRHH"];
                SQL.ParamByName("pBJDID").Value = sXLBJDID;
                for (int j = 1; j <= 20; j++)
                {
                    string sCol = "YZZ" + j.ToString().PadLeft(2, '0');
                    if (j <= iYZCount && dsNR.Tables[0].Columns.Contains(sCol))
                        SQL.ParamByName("pYZZ" + j.ToString().PadLeft(2, '0')).Value = CMISproConvert.StrToFloat(dsNR.Tables[0].Rows[i][sCol].ToString().Trim());
                    else
                        SQL.ParamByName("pYZZ" + j.ToString().PadLeft(2, '0')).Value = 0.0;
                }
                dberp.ExecSQL(SQL);
            }
        }

        public static void CopyWBBJDYZZToBJDYZZ0(TDatabase dberp, TSQL SQL, string sBJDID, string sWBBJDID, string sSELBJDID)
        {
            if (sBJDID.Trim().Equals(""))
                return;
            string[] arrPZ = GetWBBJDDRYZPZList();
            if (arrPZ.Length == 0)
                return;
            string[] arrYZ = GetWBBJDDRYZList(sBJDID);
            if (arrYZ.Length == 0)
                return;
            int iYZCount = arrPZ.Length > 20 ? 20 : arrPZ.Length;

            Dictionary<string, bool> dicIntersect = new Dictionary<string, bool>();
            foreach (string sYZ in arrYZ)
            {
                string sYZID = sYZ.Trim();
                if (!sYZID.Equals("") && !dicIntersect.ContainsKey(sYZID))
                    dicIntersect.Add(sYZID, true);
            }

            SQL.Clear();
            SQL.Add("select XLWBBJDMXYZZ_WBBJDID,XLWBBJDMXYZZ_DRHH,XLWBBJDMXYZZ_BJDID ");
            SQL.Add("  ,XLWBBJDMXYZZ_YZZ01,XLWBBJDMXYZZ_YZZ02,XLWBBJDMXYZZ_YZZ03,XLWBBJDMXYZZ_YZZ04,XLWBBJDMXYZZ_YZZ05 ");
            SQL.Add("  ,XLWBBJDMXYZZ_YZZ06,XLWBBJDMXYZZ_YZZ07,XLWBBJDMXYZZ_YZZ08,XLWBBJDMXYZZ_YZZ09,XLWBBJDMXYZZ_YZZ10 ");
            SQL.Add("  ,XLWBBJDMXYZZ_YZZ11,XLWBBJDMXYZZ_YZZ12,XLWBBJDMXYZZ_YZZ13,XLWBBJDMXYZZ_YZZ14,XLWBBJDMXYZZ_YZZ15 ");
            SQL.Add("  ,XLWBBJDMXYZZ_YZZ16,XLWBBJDMXYZZ_YZZ17,XLWBBJDMXYZZ_YZZ18,XLWBBJDMXYZZ_YZZ19,XLWBBJDMXYZZ_YZZ20 ");
            SQL.Add("  ,XLXSBJDMX_XH as BJMXH from XLWBBJDMXYZZ ");
            SQL.Add(" left join XLXSBJDMX on XLXSBJDMX_WBBJDID=XLWBBJDMXYZZ_WBBJDID ");
            SQL.Add("   and XLXSBJDMX_DRHH=XLWBBJDMXYZZ_DRHH and XLXSBJDMX_BJDID=:pBJDID ");
            SQL.Add(" where XLWBBJDMXYZZ_WBBJDID=:pWBBJDID and XLWBBJDMXYZZ_BJDID=:pSELBJDID ");
            SQL.Add(" order by XLWBBJDMXYZZ_DRHH ");
            SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
            SQL.ParamByName("pSELBJDID").Value = sSELBJDID;
            SQL.ParamByName("pBJDID").Value = sBJDID;
            DataSet ds = dberp.OpenDataSet(SQL);
            if (ds.Tables[0].Rows.Count == 0)
                return;

            for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
            {
                int iXH = CMISproConvert.StrToInt(ds.Tables[0].Rows[i]["BJMXH"].ToString().Trim());
                if (iXH <= 0)
                    continue;
                SQL.Clear();
                SQL.Add("update XLXSBJDYZZ0 set ");
                bool bFirst = true;
                for (int j = 0; j < iYZCount; j++)
                {
                    string sYZID = arrPZ[j].Trim();
                    if (!dicIntersect.ContainsKey(sYZID))
                        continue;
                    string sCol = string.Format("XLWBBJDMXYZZ_YZZ{0}", (j + 1).ToString().PadLeft(2, '0'));
                    string sYZZField = string.Format("XLXSBJDYZZ0_YZZ{0}", sYZID.Replace("BJ", ""));
                    double fVal = CMISproConvert.StrToFloat(ds.Tables[0].Rows[i][sCol].ToString().Trim());
                    if (bFirst)
                    {
                        SQL.Add(string.Format("{0}={1}", sYZZField, fVal));
                        bFirst = false;
                    }
                    else
                        SQL.Add(string.Format(",{0}={1}", sYZZField, fVal));
                }
                if (bFirst)
                    continue;
                SQL.Add(" where XLXSBJDYZZ0_BJDID=:pBJDID and XLXSBJDYZZ0_XH=:pXH ");
                SQL.ParamByName("pBJDID").Value = sBJDID;
                SQL.ParamByName("pXH").Value = iXH;
                dberp.ExecSQL(SQL);
            }
        }

        public static bool DeleteXLWBBJD(string sWBBJDID, string sXLBJDID, out string sErrorMsg)
        {
            sErrorMsg = "";
            TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
            TSQL SQL = new TSQL(dberp);
            try
            {
                dberp.BeginTrans();
                SQL.Clear();
                SQL.Add("delete from XLWBBJDML ");
                SQL.Add("  where XLWBBJDML_WBBJDID=:pWBBJDID and XLWBBJDML_BJDID=:pBJDID ");
                SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
                SQL.ParamByName("pBJDID").Value = sXLBJDID;
                dberp.ExecSQL(SQL);

                SQL.Clear();
                SQL.Add("delete from XLWBBJDLM ");
                SQL.Add("  where XLWBBJDLM_WBBJDID=:pWBBJDID and XLWBBJDLM_BJDID=:pBJDID ");
                SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
                SQL.ParamByName("pBJDID").Value = sXLBJDID;
                dberp.ExecSQL(SQL);

                SQL.Clear();
                SQL.Add("delete from XLWBBJDNR ");
                SQL.Add("  where XLWBBJDNR_WBBJDID=:pWBBJDID and XLWBBJDNR_BJDID=:pBJDID ");
                SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
                SQL.ParamByName("pBJDID").Value = sXLBJDID;
                dberp.ExecSQL(SQL);

                SQL.Clear();
                SQL.Add("delete from XLWBBJDMX ");
                SQL.Add("  where XLWBBJDMX_WBBJDID=:pWBBJDID and XLWBBJDMX_BJDID=:pBJDID ");
                SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
                SQL.ParamByName("pBJDID").Value = sXLBJDID;
                dberp.ExecSQL(SQL);

                SQL.Clear();
                SQL.Add("delete from XLWBBJDMXYZZ ");
                SQL.Add("  where XLWBBJDMXYZZ_WBBJDID=:pWBBJDID and XLWBBJDMXYZZ_BJDID=:pBJDID ");
                SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
                SQL.ParamByName("pBJDID").Value = sXLBJDID;
                dberp.ExecSQL(SQL);
                dberp.CommitTrans();
                return true;
            }
            catch (Exception ex)
            {
                sErrorMsg = "删除失败！" + ex;
                dberp.RollbackTrans();
                return false;
            }
        }

        public static void InsXLWBBJDMX(TDatabase dberp, TSQL SQL, string sWBBJDID, string sXLBJDID, CMISproSession tSession, ref string sErrMsg)
        {
            XLXSBJPZXX pzxx = XLBJDUtils.GetXLXSBJPZXX(100860000);
            XLXSBJPZXX pzxx43 = XLBJDUtils.GetXLXSBJPZXX(100860043);

            string sKHMCPPFS = "";
            if (pzxx.XLXSBJPZ_KHJSLY.Equals("KHXX") && pzxx.XLXSBJPZ_XZKHYQ.Equals("Q"))
            {
                sKHMCPPFS = "1";
            }
            else if (pzxx.XLXSBJPZ_KHJSLY.Equals("KH") && pzxx.XLXSBJPZ_XZKHYQ.Equals("Q"))
            {
                sKHMCPPFS = "2";
            }
            string sBJSJLY = "";
            string sXLJGU007 = tSession.GetOption(102579000, "XLJGU007");
            string sBJU028 = tSession.GetOption(100860043, "BJU028");
            string sDefaultXLBZID = XLBJDUtils.GetDefaultXLBZID();
            if (sDefaultXLBZID.Equals(""))
                sDefaultXLBZID = " ";
            Dictionary<string, string> dicXLBZID = new Dictionary<string, string>();
            dicXLBZID.Clear();
            SQL.Clear();
            SQL.Add("select XLBZ_XLBZID,XLBZ_XLBZLXMC from XLBZ where 1 = 1 ");
            DataSet dsXLBZID = dberp.OpenDataSet(SQL);
            if (dsXLBZID.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < dsXLBZID.Tables[0].Rows.Count; i++)
                {
                    dicXLBZID.Add(dsXLBZID.Tables[0].Rows[i]["XLBZ_XLBZID"].ToString().Trim(), dsXLBZID.Tables[0].Rows[i]["XLBZ_XLBZLXMC"].ToString().Trim());
                }
            }

            Dictionary<string, string> dicXLYS = new Dictionary<string, string>();
            dicXLYS.Clear();
            SQL.Clear();
            SQL.Add("select XLYSB_XLYSID,XLYSB_XLYSMC from XLYSB where 1=1");
            DataSet dsXLYS = dberp.OpenDataSet(SQL);
            if (dsXLYS.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < dsXLYS.Tables[0].Rows.Count; i++)
                {
                    dicXLYS.Add(dsXLYS.Tables[0].Rows[i]["XLYSB_XLYSID"].ToString().Trim(), dsXLYS.Tables[0].Rows[i]["XLYSB_XLYSMC"].ToString().Trim());
                }
            }

            int iNRHHS = 0;
            int iNRHHZ = 0;
            string sKHMC = "";
            Dictionary<string, string> dicLM = new Dictionary<string, string>();
            dicLM.Clear();
            SQL.Clear();
            SQL.Add(" select XLWBBJDLM_ZDLMLX,XLWBBJDLM_LMBH,XLWBBJDML_NRHHS,XLWBBJDML_NRHHZ,XLWBBJDML_SJLY,XLXSBJD_KH  ");
            SQL.Add("  from XLWBBJDLM ");
            SQL.Add(" left join XLWBBJDML on XLWBBJDML_WBBJDID = XLWBBJDLM_WBBJDID and  XLWBBJDML_BJDID = XLWBBJDLM_BJDID ");
            SQL.Add(" left join XLXSBJD on XLWBBJDLM_BJDID = XLXSBJD_BJDID ");
            SQL.Add(" where 1=1 ");
            SQL.Add("   and XLWBBJDLM_WBBJDID=:pWBBJDID and XLWBBJDLM_BJDID=:pXLBJDID ");
            SQL.Add(" order by XLWBBJDLM_ZDLMLX ");
            SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
            SQL.ParamByName("pXLBJDID").Value = sXLBJDID;
            DataSet dsLM = dberp.OpenDataSet(SQL);
            if (dsLM.Tables[0].Rows.Count > 0)
            {
                iNRHHS = CMISproConvert.StrToInt(dsLM.Tables[0].Rows[0]["XLWBBJDML_NRHHS"].ToString());
                iNRHHZ = CMISproConvert.StrToInt(dsLM.Tables[0].Rows[0]["XLWBBJDML_NRHHZ"].ToString());
                sBJSJLY = dsLM.Tables[0].Rows[0]["XLWBBJDML_SJLY"].ToString();
                sKHMC = dsLM.Tables[0].Rows[0]["XLXSBJD_KH"].ToString();
                for (int i = 0; i < dsLM.Tables[0].Rows.Count; i++)
                {
                    if (!dsLM.Tables[0].Rows[i]["XLWBBJDLM_ZDLMLX"].ToString().Trim().Equals(""))
                    {
                        dicLM.Add(dsLM.Tables[0].Rows[i]["XLWBBJDLM_ZDLMLX"].ToString().Trim(), dsLM.Tables[0].Rows[i]["XLWBBJDLM_LMBH"].ToString().Trim());
                    }
                }
            }
            //查询客户ID
            string sKHID = "";
            SQL.Clear();
            if (sKHMCPPFS.Equals("1"))
            {
                SQL.Add(" select KHXX_YKHID AS KHID  ");
                SQL.Add("  from KHXX ");
                SQL.Add("  where KHXX_MC=:pKHMC ");
                SQL.ParamByName("pKHMC").Value = sKHMC;
            }
            else
            {
                SQL.Add(" select KH_KHID AS KHID  ");
                SQL.Add("  from KH ");
                SQL.Add("  where KH_MC=:pKHMC ");
                SQL.ParamByName("pKHMC").Value = sKHMC;
            }
            DataSet dsKH = dberp.OpenDataSet(SQL);
            if (dsKH.Tables[0].Rows.Count > 0)
                sKHID = dsKH.Tables[0].Rows[0][0].ToString().Trim();
            if (dicLM.Count == 0)
            {
                sErrMsg = "请先指定报价单栏目！";
                return;
            }

            try
            {
                SQL.Clear();
                SQL.Add("delete from XLWBBJDMX where 1=1 ");
                SQL.Add("      and XLWBBJDMX_WBBJDID=:pWBBJDID and XLWBBJDMX_BJDID=:pXLBJDID ");
                SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
                SQL.ParamByName("pXLBJDID").Value = sXLBJDID;
                dberp.ExecSQL(SQL);

                SQL.Clear();
                SQL.Add("delete from XLWBBJDMXYZZ where 1=1 ");
                SQL.Add("      and XLWBBJDMXYZZ_WBBJDID=:pWBBJDID and XLWBBJDMXYZZ_BJDID=:pXLBJDID ");
                SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
                SQL.ParamByName("pXLBJDID").Value = sXLBJDID;
                dberp.ExecSQL(SQL);

                SQL.Clear();
                SQL.Add("insert into XLWBBJDMX(XLWBBJDMX_BJDID,XLWBBJDMX_WBBJDID,XLWBBJDMX_DRHH,");
                if (dicLM.ContainsKey("4"))
                {
                    SQL.Add("XLWBBJDMX_JLDW, ");
                }
                if (dicLM.ContainsKey("5"))
                {
                    SQL.Add("XLWBBJDMX_BJSL, ");
                }
                if (dicLM.ContainsKey("6"))
                {
                    SQL.Add("XLWBBJDMX_CPBJ, ");
                }
                if (dicLM.ContainsKey("7"))
                {
                    SQL.Add("XLWBBJDMX_BZ, ");
                }
                if (dicLM.ContainsKey("A"))
                {
                    SQL.Add("XLWBBJDMX_XLYSID, ");
                }
                if (dicLM.ContainsKey("B"))
                {
                    SQL.Add("XLWBBJDMX_YXJG, ");
                }
                if (dicLM.ContainsKey("C"))
                {
                    SQL.Add("XLWBBJDMX_YZ, ");
                }
                if (dicLM.ContainsKey("D"))
                {
                    SQL.Add("XLWBBJDMX_YSCP, ");
                }
                if (dicLM.ContainsKey("E"))
                {
                    SQL.Add("XLWBBJDMX_FBH, ");
                }
                if (dicLM.ContainsKey("F"))
                {
                    SQL.Add("XLWBBJDMX_WGCPBZ, ");
                }
                if (dicLM.ContainsKey("G"))
                {
                    SQL.Add("XLWBBJDMX_CPBJXJ, ");
                }
                if (dicLM.ContainsKey("H"))
                {
                    SQL.Add("XLWBBJDMX_BJJEXJ, ");
                }
                if (dicLM.ContainsKey("I"))
                {
                    SQL.Add("XLWBBJDMX_ZHCPMLMC, ");
                }
                if (dicLM.ContainsKey("J"))
                {
                    SQL.Add("XLWBBJDMX_CPMLMC, ");
                }
                if (dicLM.ContainsKey("K"))
                {
                    SQL.Add("XLWBBJDMX_PWQDID, ");
                }
                if (dicLM.ContainsKey("L"))
                {
                    SQL.Add("XLWBBJDMX_PWQDWLMC, ");
                }
                if (dicLM.ContainsKey("M"))
                {
                    SQL.Add("XLWBBJDMX_PWQDMS, ");
                }
                if (dicLM.ContainsKey("N"))
                {
                    SQL.Add("XLWBBJDMX_GWWLBM, ");
                }
                if (dicLM.ContainsKey("O"))
                {
                    SQL.Add("XLWBBJDMX_JMBGS, ");
                }
                if (dicLM.ContainsKey("P"))
                {
                    SQL.Add("XLWBBJDMX_LSJGBBJ, ");
                }
                //报价产品、报价图片、销售合同一致
                if (sBJSJLY.Equals("BJCP") || sBJSJLY.Equals("BJTP") || sBJSJLY.Equals("XSHT"))
                {
                    SQL.Add(" XLWBBJDMX_DYDJID,XLWBBJDMX_XLBZID,");
                    SQL.Add(" XLWBBJDMX_XLXHID,XLWBBJDMX_XLGGID,XLWBBJDMX_BJCP,");
                }
                else if (sBJSJLY.Equals("BJWL"))//BJWL|报价物料
                {
                    //必须项
                    SQL.Add("XLWBBJDMX_BJCP,XLWBBJDMX_WLID,XLWBBJDMX_XLXHID,XLWBBJDMX_XLGGID,XLWBBJDMX_XLBZID,XLWBBJDMX_DYDJID,XLWBBJDMX_XLYSID, ");
                }
                else if (sBJSJLY.Equals("KHWL"))//KHWL|客户物料
                {
                    //必须项
                    SQL.Add("XLWBBJDMX_BJCP,XLWBBJDMX_KHWLID,XLWBBJDMX_WLID,XLWBBJDMX_XLXHID,XLWBBJDMX_XLGGID,XLWBBJDMX_XLBZID,XLWBBJDMX_DYDJID,XLWBBJDMX_XLYSID, ");
                }

                SQL.Add(" XLWBBJDMX_WHR,XLWBBJDMX_WHRID,XLWBBJDMX_WHSJ) ");

                SQL.Add("select XLWBBJDNR_BJDID,XLWBBJDNR_WBBJDID,XLWBBJDNR_DRHH,");
                if (dicLM.ContainsKey("4"))//4|计量单位
                {
                    SQL.Add(string.Format("case when isnull(XLWBBJDNR_LMNR{0},' ')=' ' then ' ' when XLWBBJDNR_LMNR{0} in ('km','KM','Km','kM') then 'Km' when XLWBBJDNR_LMNR{0} in ('m','M') then 'M' when XLWBBJDNR_LMNR{0} in ('kg','KG','Kg','kG') then 'Kg' when XLWBBJDNR_LMNR{0} in ('t','T') then 'T' else XLWBBJDNR_LMNR{0} end as XLWBBJDMX_JLDW,", dicLM["4"]));
                }
                if (dicLM.ContainsKey("5"))//5|报价数量
                {
                    SQL.Add(string.Format("isnull(rtrim(XLWBBJDNR_LMNR{0}),0.0) as XLWBBJDMX_BJSL,", dicLM["5"]));
                }
                if (dicLM.ContainsKey("6"))//6|报价
                {
                    SQL.Add(string.Format("isnull(rtrim(XLWBBJDNR_LMNR{0}),0.0) as XLWBBJDMX_CPBJ,", dicLM["6"]));
                }
                if (dicLM.ContainsKey("7"))//7|备注
                {
                    SQL.Add(string.Format("XLWBBJDNR_LMNR{0} as XLWBBJDMX_BZ,", dicLM["7"]));
                }
                if (dicLM.ContainsKey("A"))//A|颜色
                {
                    //SQL.Add("case when isnull(XLYSB_XLYSID,' ')<>' ' then XLYSB_XLYSID else ' ' end as XLWBBJDMX_XLYSID,");
                    if (pzxx43.XLXSBJPZ_WBBJDJXYS.Equals("Y"))
                    {
                        SQL.Add(" case when isnull(d.XLYSB_XLYSID,' ')<>' ' then d.XLYSB_XLYSID when isnull(e.XLYSB_XLYSID,' ')<>' ' then e.XLYSB_XLYSID when isnull(f.XLYSB_XLYSID,' ')<>' ' then f.XLYSB_XLYSID ");
                        SQL.Add(" when isnull(g.XLYSB_XLYSID,' ')<>' ' then g.XLYSB_XLYSID when isnull(h.XLYSB_XLYSID,' ')<>' ' then h.XLYSB_XLYSID when isnull(i.XLYSB_XLYSID,' ')<>' ' then i.XLYSB_XLYSID else ' ' end as XLWBBJDMX_XLYSID,");
                    }
                    else
                        SQL.Add("case when isnull(d.XLYSB_XLYSID,' ')<>' ' then d.XLYSB_XLYSID when isnull(e.XLYSB_XLYSID,' ')<>' ' then e.XLYSB_XLYSID when isnull(f.XLYSB_XLYSID,' ')<>' ' then f.XLYSB_XLYSID  else ' ' end as XLWBBJDMX_XLYSID,");

                }
                if (dicLM.ContainsKey("B"))//B|意向价
                {
                    SQL.Add(string.Format("isnull(rtrim(XLWBBJDNR_LMNR{0}),0.0) as XLWBBJDMX_YXJG,", dicLM["B"]));
                }
                if (dicLM.ContainsKey("C"))//C|印字
                {
                    SQL.Add(string.Format("XLWBBJDNR_LMNR{0} as XLWBBJDMX_YZ,", dicLM["C"]));
                }
                if (dicLM.ContainsKey("D"))//D|原始产品
                {
                    SQL.Add(string.Format("XLWBBJDNR_LMNR{0} as XLWBBJDMX_YSCP,", dicLM["D"]));
                }
                if (dicLM.ContainsKey("E"))//E|包号
                {
                    SQL.Add(string.Format("XLWBBJDNR_LMNR{0} as XLWBBJDMX_FBH,", dicLM["E"]));
                }
                if (dicLM.ContainsKey("F"))//F|外购
                {
                    SQL.Add(string.Format("case XLWBBJDNR_LMNR{0} when '外购' then 'Y' when '是' then 'Y' when 'Y' then 'Y' else 'N' end as XLWBBJDMX_WGCPBZ,", dicLM["F"]));
                }
                if (dicLM.ContainsKey("G"))//G|产品报价限价
                {
                    SQL.Add(string.Format("isnull(rtrim(XLWBBJDNR_LMNR{0}),0.0) as XLWBBJDMX_CPBJXJ,", dicLM["G"]));
                }
                if (dicLM.ContainsKey("H"))//H|报价金额限价
                {
                    SQL.Add(string.Format("isnull(rtrim(XLWBBJDNR_LMNR{0}),0.0) as XLWBBJDMX_BJJEXJ,", dicLM["H"]));
                }
                if (dicLM.ContainsKey("I"))
                {
                    SQL.Add(string.Format("XLWBBJDNR_LMNR{0} as XLWBBJDMX_ZHCPMLMC,", dicLM["I"]));
                }
                if (dicLM.ContainsKey("J"))
                {
                    SQL.Add(string.Format("XLWBBJDNR_LMNR{0} as XLWBBJDMX_CPMLMC,", dicLM["J"]));
                }
                if (dicLM.ContainsKey("K"))
                {
                    SQL.Add(string.Format("XLWBBJDNR_LMNR{0} as XLWBBJDMX_PWQDID,", dicLM["K"]));
                }
                if (dicLM.ContainsKey("L"))
                {
                    SQL.Add(string.Format("XLWBBJDNR_LMNR{0} as XLWBBJDMX_PWQDWLMC,", dicLM["L"]));
                }
                if (dicLM.ContainsKey("M"))
                {
                    SQL.Add(string.Format("XLWBBJDNR_LMNR{0} as XLWBBJDMX_PWQDMS,", dicLM["M"]));
                }
                if (dicLM.ContainsKey("N"))
                {
                    SQL.Add(string.Format("XLWBBJDNR_LMNR{0} as XLWBBJDMX_GWWLBM,", dicLM["N"]));
                }
                if (dicLM.ContainsKey("O"))
                {
                    SQL.Add(string.Format("XLWBBJDNR_LMNR{0} as XLWBBJDMX_JMBGS,", dicLM["O"]));
                }
                if (dicLM.ContainsKey("P"))
                {
                    SQL.Add(string.Format("isnull(rtrim(XLWBBJDNR_LMNR{0}),0.0) as XLWBBJDMX_LSJGBBJ,", dicLM["P"]));
                }
                //报价产品、报价图片、销售合同一致
                if (sBJSJLY.Equals("BJCP") || sBJSJLY.Equals("BJTP") || sBJSJLY.Equals("XSHT"))
                {
                    if (dicLM.ContainsKey("2"))//2|电压等级
                    {
                        SQL.Add(string.Format("replace(replace(isnull(XLWBBJDNR_LMNR{0},' '),'K','k'),'v','V') as XLWBBJDMX_DYDJID,", dicLM["2"]));
                    }
                    else
                    {
                        SQL.Add("' ' as XLWBBJDMX_DYDJID, ");
                    }
                    if (dicLM.ContainsKey("3"))//3|标准
                    {
                        //SQL.Add(string.Format("case when isnull(XLBZ_XLBZID,' ')<>' ' then XLBZ_XLBZID else '{0}' end as XLWBBJDMX_XLBZID,", (sDefaultXLBZID.Equals("FirstBZ") ? "" : sDefaultXLBZID)));
                        SQL.Add(string.Format("case when isnull(a.XLBZ_XLBZID,' ')<>' ' then a.XLBZ_XLBZID when isnull(b.XLBZ_XLBZID,' ')<>' ' then b.XLBZ_XLBZID when isnull(c.XLBZ_XLBZID,' ')<>' ' then c.XLBZ_XLBZID else '{0}' end as XLWBBJDMX_XLBZID,", (sDefaultXLBZID.Equals("FirstBZ") ? " " : sDefaultXLBZID)));
                    }
                    else
                    {
                        SQL.Add(string.Format("'{0}' as XLWBBJDMX_XLBZID, ", (sDefaultXLBZID.Equals("FirstBZ") ? " " : sDefaultXLBZID)));
                    }
                    if (dicLM.ContainsKey("8"))//8|型号
                    {
                        SQL.Add(string.Format("upper(XLWBBJDNR_LMNR{0}) as XLWBBJDMX_XLXHID,", dicLM["8"]));//型号控制大写
                    }
                    else
                    {
                        SQL.Add("' ' as XLWBBJDMX_XLXHID, ");
                    }
                    if (dicLM.ContainsKey("9"))//9|规格
                    {
                        SQL.Add(string.Format("XLWBBJDNR_LMNR{0} as XLWBBJDMX_XLGGID,", dicLM["9"]));
                    }
                    else
                    {
                        SQL.Add("' ' as XLWBBJDMX_XLGGID, ");
                    }
                    if (dicLM.ContainsKey("1"))//1|报价产品
                    {
                        SQL.Add(string.Format("XLWBBJDNR_LMNR{0} as XLWBBJDMX_BJCP,", dicLM["1"]));
                    }
                    else
                    {
                        SQL.Add("' ' as XLWBBJDMX_BJCP, ");
                    }
                }
                else if (sBJSJLY.Equals("BJWL"))//BJWL|报价物料
                {
                    SQL.Add(" case when (ISNULL(XLXLB_DYDJID,' ') = ' ' or ISNULL(XLXLB_DYDJID,' ') = '--') then rtrim(case when isnull(XSBJXHTH_BJXLXHID,' ') = ' ' then XLXLB_XLXHID else XSBJXHTH_BJXLXHID end)||' '||rtrim(XLXLB_XLGGID) else rtrim(XLXLB_XLXHID)||'-'||rtrim(XLXLB_DYDJID)||' '||rtrim(XLXLB_XLGGID) end as XLWBBJDMX_BJCP,");

                    SQL.Add("XLWBBJDNR_LMNR01 as XLWBBJDMX_WLID, ");
                    SQL.Add("isnull(case when isnull(XSBJXHTH_BJXLXHID,' ') = ' ' then XLXLB_XLXHID else XSBJXHTH_BJXLXHID end,' ') as XLWBBJDMX_XLXHID, ");
                    //A|× B|*
                    if (sXLJGU007.Equals("B"))
                    {
                        SQL.Add("replace(isnull(XLXLB_XLGGID,' '),'×','*') as XLWBBJDMX_XLGGID, ");
                    }
                    else
                    {
                        SQL.Add("replace(isnull(XLXLB_XLGGID,' '),'*','×') as XLWBBJDMX_XLGGID, ");
                    }
                    SQL.Add("isnull(XLXLB_XLBZID,' ') as XLWBBJDMX_XLBZID, ");
                    SQL.Add("isnull(XLXLB_DYDJID,' ') as XLWBBJDMX_DYDJID, ");
                    SQL.Add("XLXLB_XLYSID as XLWBBJDMX_XLYSID, ");
                }
                else if (sBJSJLY.Equals("KHWL"))//KHWL|客户物料
                {
                    SQL.Add(" case when (ISNULL(XLXLB_DYDJID,' ') = ' ' or ISNULL(XLXLB_DYDJID,' ') = '--') then rtrim(case when isnull(XSBJXHTH_BJXLXHID,' ') = ' ' then XLXLB_XLXHID else XSBJXHTH_BJXLXHID end)||' '||rtrim(XLXLB_XLGGID) else rtrim(XLXLB_XLXHID)||'-'||rtrim(XLXLB_DYDJID)||' '||rtrim(XLXLB_XLGGID) end as XLWBBJDMX_BJCP,");

                    SQL.Add(string.Format(" XLWBBJDNR_LMNR{0},XLXLB_WLID AS XLWBBJDMX_WLID, ", dicLM["Z"]));
                    SQL.Add("isnull(case when isnull(XSBJXHTH_BJXLXHID,' ') = ' ' then XLXLB_XLXHID else XSBJXHTH_BJXLXHID end,' ') as XLWBBJDMX_XLXHID, ");
                    //A|× B|*
                    if (sXLJGU007.Equals("B"))
                    {
                        SQL.Add("replace(isnull(XLXLB_XLGGID,' '),'×','*') as XLWBBJDMX_XLGGID, ");
                    }
                    else
                    {
                        SQL.Add("replace(isnull(XLXLB_XLGGID,' '),'*','×') as XLWBBJDMX_XLGGID, ");
                    }
                    SQL.Add("isnull(XLXLB_XLBZID,' ') as XLWBBJDMX_XLBZID, ");
                    SQL.Add("isnull(XLXLB_DYDJID,' ') as XLWBBJDMX_DYDJID, ");
                    SQL.Add("XLXLB_XLYSID as XLWBBJDMX_XLYSID, ");
                }

                SQL.Add(" :XLWBBJDMX_WHR,:XLWBBJDMX_WHRID,:XLWBBJDMX_WHSJ ");
                SQL.Add(" from XLWBBJDNR");
                if (dicLM.ContainsKey("3"))
                {
                    //SQL.Add(string.Format(" left join XLBZ on (XLBZ_XLBZID=XLWBBJDNR_LMNR{0} or XLBZ_XLBZLXMC=XLWBBJDNR_LMNR{0} or rtrim(XLBZ_XLBZID)||'|'||rtrim(XLBZ_XLBZLXMC)=XLWBBJDNR_LMNR{0})", dicLM["3"]));
                    SQL.Add(string.Format(" left join XLBZ a on a.XLBZ_XLBZID=XLWBBJDNR_LMNR{0}", dicLM["3"]));
                    SQL.Add(string.Format(" left join XLBZ b on b.XLBZ_XLBZLXMC=XLWBBJDNR_LMNR{0}", dicLM["3"]));
                    SQL.Add(string.Format(" left join XLBZ c on rtrim(c.XLBZ_XLBZID)||'|'||rtrim(c.XLBZ_XLBZLXMC)=XLWBBJDNR_LMNR{0}", dicLM["3"]));
                }
                if (dicLM.ContainsKey("A"))
                {
                    //SQL.Add(string.Format(" left join XLYSB on (XLYSB_XLYSID=XLWBBJDNR_LMNR{0} or XLYSB_XLYSMC=XLWBBJDNR_LMNR{0} or rtrim(XLYSB_XLYSID)||'|'||rtrim(XLYSB_XLYSMC)=XLWBBJDNR_LMNR{0})", dicLM["A"]));
                    SQL.Add(string.Format(" left join XLYSB d on d.XLYSB_XLYSID=XLWBBJDNR_LMNR{0}", dicLM["A"]));
                    SQL.Add(string.Format(" left join XLYSB e on e.XLYSB_XLYSMC=XLWBBJDNR_LMNR{0}", dicLM["A"]));
                    SQL.Add(string.Format(" left join XLYSB f on rtrim(f.XLYSB_XLYSID)||'|'||rtrim(f.XLYSB_XLYSMC)=XLWBBJDNR_LMNR{0}", dicLM["A"]));
                    if (pzxx43.XLXSBJPZ_WBBJDJXYS.Equals("Y"))
                    {
                        SQL.Add(string.Format(" left join (select replace(XLYSB_XLYSMC,'色','') as YSMC,XLYSB_XLYSID from XLYSB where XLYSB_XLYSMC like '%色%') g on g.YSMC=XLWBBJDNR_LMNR{0}", dicLM["A"]));
                        SQL.Add(string.Format(" left join (select '双' as YSMC,XLYSB_XLYSID from XLYSB where XLYSB_XLYSMC = '绿黄' ) h on h.YSMC = replace(XLWBBJDNR_LMNR{0},'色','') ", dicLM["A"]));
                        SQL.Add(string.Format(" left join (select '双' as YSMC,XLYSB_XLYSID from XLYSB where XLYSB_XLYSMC = '黄绿' ) i on i.YSMC = replace(XLWBBJDNR_LMNR{0},'色','') ", dicLM["A"]));
                    }
                }
                if (sBJSJLY.Equals("BJWL"))//BJWL|报价物料
                {
                    SQL.Add("   left join XLXLB on XLXLB_WLID=XLWBBJDNR_LMNR01 ");
                    SQL.Add("   left join XSBJXHTH on XSBJXHTH_XLXHID=XLXLB_XLXHID ");
                }
                else if (sBJSJLY.Equals("KHWL"))//KHWL|客户物料
                {
                    SQL.Add(string.Format("    left join KHWL ON KHWL_KHID=:pKHID and KHWL_KHWLID=upper(XLWBBJDNR_LMNR{0}) ", dicLM["Z"]));
                    SQL.Add("    left join XLXLB ON XLXLB_WLID=KHWL_WLID ");
                    SQL.Add("    left join XSBJXHTH on XSBJXHTH_XLXHID=XLXLB_XLXHID ");
                    SQL.ParamByName("pKHID").Value = sKHID;
                }
                SQL.Add("      where XLWBBJDNR_WBBJDID=:pWBBJDID and XLWBBJDNR_BJDID=:pXLBJDID ");
                SQL.Add("        and XLWBBJDNR_DRHH>=:pNRHHS and XLWBBJDNR_DRHH<=:pNRHHZ");
                SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
                SQL.ParamByName("pXLBJDID").Value = sXLBJDID;
                SQL.ParamByName("pNRHHS").Value = iNRHHS;
                SQL.ParamByName("pNRHHZ").Value = iNRHHZ;
                SQL.ParamByName("XLWBBJDMX_WHR").Value = tSession.UserName;
                SQL.ParamByName("XLWBBJDMX_WHRID").Value = tSession.UserId;
                SQL.ParamByName("XLWBBJDMX_WHSJ").Value = DateTime.Now;
                dberp.ExecSQL(SQL);

                if (sBJU028 == "Y")
                {
                    SQL.Clear();
                    SQL.Add("   update XLWBBJDMX set XLWBBJDMX_XLBZID=''    where 1=1 ");
                    SQL.Add("      and XLWBBJDMX_WBBJDID=:pWBBJDID and XLWBBJDMX_BJDID=:pXLBJDID ");
                    SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
                    SQL.ParamByName("pXLBJDID").Value = sXLBJDID;
                    dberp.ExecSQL(SQL);
                }

                // XLWBBJDMX导入后，统一同步规格替换表
                XLBJDUtils.SyncXLXSBJXLGGTHB_ByXLWBBJDMX_Batch(dberp, tSession, sWBBJDID);


                //BJCP|报价产品、BJTP|报价图片
                //BJWL|报价物料、XSHT|销售合同
                //SQL.Clear();
                //SQL.Add("select XLWBBJDNR_WBBJDID,XLWBBJDNR_BJDID,XLWBBJDNR_DRHH,   ");
                //foreach (KeyValuePair<string, string> kvp in dicLM)
                //{
                //    SQL.Add(String.Format(" XLWBBJDNR_LMNR{0}, ", kvp.Value));
                //}
                //if (sBJSJLY.Equals("BJWL"))
                //{
                //    SQL.Add("   XLWBBJDNR_LMNR01,XLXLB_XLXHID,XLXLB_XLGGID, ");
                //    SQL.Add("   XLXLB_XLBZID,XLXLB_DYDJID,XLXLB_XLYSID, ");
                //}
                //SQL.Add(" 1 as px  from XLWBBJDNR ");
                //if (sBJSJLY.Equals("BJWL"))
                //{
                //    SQL.Add("   left join XLXLB on XLXLB_WLID=XLWBBJDNR_LMNR01 ");
                //}
                //SQL.Add("      where XLWBBJDNR_WBBJDID=:pWBBJDID and XLWBBJDNR_BJDID=:pXLBJDID ");
                //SQL.Add("        and XLWBBJDNR_DRHH>=:pNRHHS and XLWBBJDNR_DRHH<=:pNRHHZ");
                //SQL.Add("      order by XLWBBJDNR_DRHH");
                //SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
                //SQL.ParamByName("pXLBJDID").Value = sXLBJDID;
                //SQL.ParamByName("pNRHHS").Value = iNRHHS;
                //SQL.ParamByName("pNRHHZ").Value = iNRHHZ;
                //DataSet dsNR = dberp.OpenDataSet(SQL);
                //for (int i = 0; i < dsNR.Tables[0].Rows.Count; i++)
                //{
                //    SQL.Clear();
                //    SQL.Add("insert into XLWBBJDMX( ");
                //    SQL.Add("               XLWBBJDMX_BJDID,XLWBBJDMX_WBBJDID,XLWBBJDMX_DRHH, ");
                //    //单独处理字段
                //    GetSQLAdd(SQL, dicLM, sBJSJLY);
                //    SQL.Add("               XLWBBJDMX_WHR,XLWBBJDMX_WHRID,XLWBBJDMX_WHSJ ");
                //    SQL.Add("         ) ");
                //    SQL.Add("            values(");
                //    SQL.Add("               :XLWBBJDMX_BJDID,:XLWBBJDMX_WBBJDID,:XLWBBJDMX_DRHH, ");
                //    //与函数GetSQLAdd相互对应
                //    GetParamNameAdd(SQL, dicLM, dsNR, i, sBJSJLY, sDefaultXLBZID, dicXLBZID, dicXLYS, tSession, sXLJGU007);
                //    SQL.Add("               :XLWBBJDMX_WHR,:XLWBBJDMX_WHRID,:XLWBBJDMX_WHSJ ");
                //    SQL.Add("         ) ");
                //    SQL.ParamByName("XLWBBJDMX_BJDID").Value = dsNR.Tables[0].Rows[i]["XLWBBJDNR_BJDID"].ToString().Trim();
                //    SQL.ParamByName("XLWBBJDMX_WBBJDID").Value = dsNR.Tables[0].Rows[i]["XLWBBJDNR_WBBJDID"].ToString().Trim();
                //    SQL.ParamByName("XLWBBJDMX_DRHH").Value = dsNR.Tables[0].Rows[i]["XLWBBJDNR_DRHH"].ToString().Trim();

                //    SQL.ParamByName("XLWBBJDMX_WHR").Value = tSession.UserName;
                //    SQL.ParamByName("XLWBBJDMX_WHRID").Value = tSession.UserId;
                //    SQL.ParamByName("XLWBBJDMX_WHSJ").Value = DateTime.Now;
                //    string ss = SQL.Text;
                //    dberp.ExecSQL(SQL);
                //}
                InsXLWBBJDMXYZZ(dberp, SQL, sWBBJDID, sXLBJDID);
            }
            catch (Exception ex)
            {
                sErrMsg = ex.ToString().Trim();
            }

        }

        public static void UpdateCPMLMC(TDatabase dberp, TSQL SQL, string sWBBJDID, string sXLBJDID, string sDRHHList, int VnewID)
        {
            SQL.Clear();
            SQL.Add(" merge into XLWBBJDMX tar ");
            SQL.Add(" using (  ");
            SQL.Add("    SELECT  XLWBBJDMX_WBBJDID,XLWBBJDMX_BJDID, XLWBBJDMX_DRHH,");
            SQL.Add("           isnull(CPMLMC,' ') as CPMLMC,isnull(ZHCPMLMC,' ') as ZHCPMLMC    ");
            SQL.Add("       from XLWBBJDMX   ");
            SQL.Add("               left join XLXSBJXHML A on A.XLXSBJXHML_XLXHID = XLWBBJDMX_XLXHID and A.XLXSBJXHML_DYDJID = isnull(XLWBBJDMX_DYDJID, ' ')   ");
            SQL.Add("               left join XLXSBJXHML B on B.XLXSBJXHML_XLXHID = XLWBBJDMX_XLXHID and B.XLXSBJXHML_DYDJID = ' '    ");
            SQL.Add(@" left join 
                        (select DY.XLXSCPML_CPMLID as CPMLID ,DY.XLXSCPML_CPMLMC as CPMLMC ,min(ZH.XLXSCPML_CPMLMC) as ZHCPMLMC
	                        from XLXSCPML DY 
	                        left join  XLXSCPML ZH  on   ','||rtrim(ZH.XLXSCPML_CPMLIDS)||',' like '%,'||rtrim(DY.XLXSCPML_CPMLID)||',%' 
	                        group by DY.XLXSCPML_CPMLID,DY.XLXSCPML_CPMLMC
                        ) CPML on CPML.CPMLID = case when ISNULL(A.XLXSBJXHML_CPMLID,' ') = ' ' then ISNULL(B.XLXSBJXHML_CPMLID,' ') else ISNULL(A.XLXSBJXHML_CPMLID,' ') end ");
            SQL.Add("       where  1=1  ");
            SQL.Add("       and XLWBBJDMX_BJDID = :pBJDID  ");
            SQL.Add("       and XLWBBJDMX_WBBJDID = :pWBBJDID  ");
            if (!sDRHHList.Equals(""))
            {   //研发自测：王威 2025/11/02 21:35:46
                SQL.Add(" and exists (select 1  from V_XLWBBJDMX where V_XLWBBJDMX_DRHH=XLWBBJDMX_DRHH and V_XLWBBJDMX_ID=:pNewID)");
                SQL.ParamByName("pNewID").Value = VnewID;
            }
            SQL.Add(" ) sor ");
            SQL.Add(" on( ");
            SQL.Add("  1=1   ");
            SQL.Add("  and sor.XLWBBJDMX_BJDID = tar.XLWBBJDMX_BJDID   ");
            SQL.Add("  and sor.XLWBBJDMX_WBBJDID = tar.XLWBBJDMX_WBBJDID   ");
            SQL.Add("  and sor.XLWBBJDMX_DRHH = tar.XLWBBJDMX_DRHH   ");
            SQL.Add("  and (isnull(tar.XLWBBJDMX_CPMLMC, ' ')= ' ' and isnull(tar.XLWBBJDMX_ZHCPMLMC, ' ')= ' ' )  ");
            if (!sDRHHList.Equals(""))
            {   //研发自测：王威 2025/11/02 21:35:46
                SQL.Add(" and exists (select 1  from V_XLWBBJDMX where V_XLWBBJDMX_DRHH=sor.XLWBBJDMX_DRHH and V_XLWBBJDMX_ID=:pNewID)");
                SQL.ParamByName("pNewID").Value = VnewID;
            }
            SQL.Add(" ) ");
            SQL.Add(" when matched then update set  ");
            SQL.Add(" XLWBBJDMX_CPMLMC = sor.CPMLMC, ");
            SQL.Add(" XLWBBJDMX_ZHCPMLMC = sor.ZHCPMLMC ");
            SQL.ParamByName("pBJDID").Value = sXLBJDID;
            SQL.ParamByName("pWBBJDID").Value = sWBBJDID;

            dberp.ExecSQL(SQL);
        }

        //private static void GetSQLAdd(TSQL SQL, Dictionary<string, string> dicLM, string sBJSJLY)
        //{
        //    ////1|报价产品、2|电压等级、3|标准、4|计量单位、5|报价数量、6|报价、7|备注、8|型号、9|规格、A|颜色
        //    #region 通用格式 与GetParamNameAdd一致
        //    SQL.Add("XLWBBJDMX_DYDJID, ");
        //    SQL.Add("XLWBBJDMX_XLBZID, ");
        //    if (dicLM.ContainsKey("4"))
        //    {
        //        SQL.Add("XLWBBJDMX_JLDW, ");
        //    }
        //    if (dicLM.ContainsKey("5"))
        //    {
        //        SQL.Add("XLWBBJDMX_BJSL, ");
        //    }
        //    if (dicLM.ContainsKey("6"))
        //    {
        //        SQL.Add("XLWBBJDMX_CPBJ, ");
        //    }
        //    if (dicLM.ContainsKey("7"))
        //    {
        //        SQL.Add("XLWBBJDMX_BZ, ");
        //    }
        //    if (dicLM.ContainsKey("A"))
        //    {
        //        SQL.Add("XLWBBJDMX_XLYSID, ");
        //    }
        //    #endregion
        //    if (sBJSJLY.Equals("BJCP")
        //        || sBJSJLY.Equals("BJTP")
        //        || sBJSJLY.Equals("XSHT"))
        //    {
        //        SQL.Add("XLWBBJDMX_XLXHID, ");
        //        SQL.Add("XLWBBJDMX_XLGGID, ");
        //        SQL.Add("XLWBBJDMX_BJCP, ");

        //    }
        //    else if (sBJSJLY.Equals("BJWL"))
        //    {
        //        //必须项
        //        SQL.Add("XLWBBJDMX_BJCP, ");
        //        SQL.Add("XLWBBJDMX_WLID, ");
        //        SQL.Add("XLWBBJDMX_XLXHID, ");
        //        SQL.Add("XLWBBJDMX_XLGGID, ");
        //        SQL.Add("XLWBBJDMX_XLBZID, ");
        //        SQL.Add("XLWBBJDMX_DYDJID, ");
        //        SQL.Add("XLWBBJDMX_XLYSID, ");

        //    }
        //}


        //private static void GetParamNameAdd(TSQL SQL, Dictionary<string, string> dicLM, DataSet dsNR, int i, string sBJSJLY,
        //            string sDefaultXLBZID, Dictionary<string, string> dicXLBZID, Dictionary<string, string> dicXLYS, CMISproSession tSession, string sXLJGU007)
        //{
        //    ////1|报价产品、2|电压等级、3|标准、4|计量单位、5|报价数量、6|报价、7|备注、8|型号、9|规格、A|颜色
        //    string sXLXHID = "";
        //    string sXLGGID = "";
        //    string sDYDJID = "";
        //    string sXLYSID = "";
        //    string sBJCP = "";

        //    #region 通用格式
        //    if (dicLM.ContainsKey("2"))
        //    {
        //        SQL.Add(":XLWBBJDMX_DYDJID, ");
        //        if (dsNR.Tables[0].Rows[i][String.Format("XLWBBJDNR_LMNR{0}", dicLM["2"])].ToString().Trim().Equals(""))
        //        {
        //            SQL.ParamByName("XLWBBJDMX_DYDJID").Value = " ";
        //        }
        //        else
        //        {
        //            sDYDJID = XLBJDUtils.GetStaDYDJID(dsNR.Tables[0].Rows[i][String.Format("XLWBBJDNR_LMNR{0}", dicLM["2"])].ToString().Trim());
        //            SQL.ParamByName("XLWBBJDMX_DYDJID").Value = sDYDJID;
        //        }
        //    }
        //    else
        //    {
        //        SQL.Add(":XLWBBJDMX_DYDJID, ");
        //        SQL.ParamByName("XLWBBJDMX_DYDJID").Value = " ";
        //    }

        //    if (dicLM.ContainsKey("3"))
        //    {
        //        SQL.Add(":XLWBBJDMX_XLBZID, ");
        //        SQL.ParamByName("XLWBBJDMX_XLBZID").Value = XLBJDUtils.GetBZXLZBZID(
        //            dsNR.Tables[0].Rows[i][String.Format("XLWBBJDNR_LMNR{0}", dicLM["3"])].ToString().Trim(),
        //            sDefaultXLBZID,
        //            dicXLBZID);
        //    }
        //    else
        //    {
        //        SQL.Add(":XLWBBJDMX_XLBZID, ");
        //        SQL.ParamByName("XLWBBJDMX_XLBZID").Value = "";
        //    }

        //    if (dicLM.ContainsKey("4"))
        //    {
        //        SQL.Add(":XLWBBJDMX_JLDW, ");
        //        if (dsNR.Tables[0].Rows[i][String.Format("XLWBBJDNR_LMNR{0}", dicLM["4"])].ToString().Trim().Equals(""))
        //        {
        //            SQL.ParamByName("XLWBBJDMX_JLDW").Value = tSession.GetOption(100860000, "BJU003");
        //        }
        //        else
        //        {
        //            SQL.ParamByName("XLWBBJDMX_JLDW").Value = dsNR.Tables[0].Rows[i][String.Format("XLWBBJDNR_LMNR{0}", dicLM["4"])].ToString().Trim();
        //        }
        //    }
        //    if (dicLM.ContainsKey("5"))
        //    {
        //        SQL.Add(":XLWBBJDMX_BJSL, ");
        //        SQL.ParamByName("XLWBBJDMX_BJSL").Value = CMISproConvert.StrToFloat(dsNR.Tables[0].Rows[i][String.Format("XLWBBJDNR_LMNR{0}", dicLM["5"])].ToString().Trim());

        //    }
        //    if (dicLM.ContainsKey("6"))
        //    {
        //        SQL.Add(":XLWBBJDMX_CPBJ, ");
        //        SQL.ParamByName("XLWBBJDMX_CPBJ").Value = CMISproConvert.StrToFloat(dsNR.Tables[0].Rows[i][String.Format("XLWBBJDNR_LMNR{0}", dicLM["6"])].ToString().Trim());


        //    }
        //    if (dicLM.ContainsKey("7"))
        //    {
        //        SQL.Add(":XLWBBJDMX_BZ, ");
        //        SQL.ParamByName("XLWBBJDMX_BZ").Value = dsNR.Tables[0].Rows[i][String.Format("XLWBBJDNR_LMNR{0}", dicLM["7"])].ToString().Trim();

        //    }
        //    if (dicLM.ContainsKey("A"))
        //    {
        //        SQL.Add(":XLWBBJDMX_XLYSID, ");
        //        if (dsNR.Tables[0].Rows[i][String.Format("XLWBBJDNR_LMNR{0}", dicLM["A"])].ToString().Trim().Equals(""))
        //        {
        //            SQL.ParamByName("XLWBBJDMX_XLYSID").Value = " ";
        //        }
        //        else
        //        {
        //            sXLYSID = XLBJDUtils.GetBZXLYSID(dsNR.Tables[0].Rows[i][String.Format("XLWBBJDNR_LMNR{0}", dicLM["A"])].ToString().Trim(), dicXLYS);
        //            SQL.ParamByName("XLWBBJDMX_XLYSID").Value = sXLYSID;
        //        }

        //    }
        //    #endregion
        //    //报价产品、报价图片、销售合同一致
        //    if (sBJSJLY.Equals("BJCP")
        //        || sBJSJLY.Equals("BJTP")
        //        || sBJSJLY.Equals("XSHT"))
        //    {
        //        if (dicLM.ContainsKey("8"))
        //        {
        //            sXLXHID = dsNR.Tables[0].Rows[i][String.Format("XLWBBJDNR_LMNR{0}", dicLM["8"])].ToString().Trim();
        //            SQL.Add(":XLWBBJDMX_XLXHID, ");
        //            SQL.ParamByName("XLWBBJDMX_XLXHID").Value = sXLXHID.ToUpper();//型号控制大写
        //        }
        //        else
        //        {
        //            SQL.Add(":XLWBBJDMX_XLXHID, ");
        //            SQL.ParamByName("XLWBBJDMX_XLXHID").Value = "";
        //        }
        //        if (dicLM.ContainsKey("9"))
        //        {
        //            sXLGGID = dsNR.Tables[0].Rows[i][String.Format("XLWBBJDNR_LMNR{0}", dicLM["9"])].ToString().Trim();
        //            SQL.Add(":XLWBBJDMX_XLGGID, ");
        //            SQL.ParamByName("XLWBBJDMX_XLGGID").Value = sXLGGID;
        //        }
        //        else
        //        {
        //            SQL.Add(":XLWBBJDMX_XLGGID, ");
        //            SQL.ParamByName("XLWBBJDMX_XLGGID").Value = "";
        //        }
        //        if (dicLM.ContainsKey("1"))
        //        {
        //            SQL.Add(" :XLWBBJDMX_BJCP, ");
        //            sBJCP = dsNR.Tables[0].Rows[i][String.Format("XLWBBJDNR_LMNR{0}", dicLM["1"])].ToString().Trim();
        //            SQL.ParamByName("XLWBBJDMX_BJCP").Value = sBJCP;
        //        }
        //        else
        //        {
        //            SQL.Add(" :XLWBBJDMX_BJCP, ");
        //            SQL.ParamByName("XLWBBJDMX_BJCP").Value = "";
        //        }
        //    }
        //    else if (sBJSJLY.Equals("BJWL"))
        //    {
        //        sXLXHID = dsNR.Tables[0].Rows[i][String.Format("XLXLB_XLXHID")].ToString().Trim();
        //        sXLGGID = dsNR.Tables[0].Rows[i][String.Format("XLXLB_XLGGID")].ToString().Trim();
        //        sDYDJID = dsNR.Tables[0].Rows[i][String.Format("XLXLB_DYDJID")].ToString().Trim();

        //        SQL.Add(":XLWBBJDMX_BJCP, ");
        //        SQL.ParamByName("XLWBBJDMX_BJCP").Value = XLBJDUtils.GroupBJCPXX(sXLXHID, sXLGGID, sDYDJID);

        //        SQL.Add(":XLWBBJDMX_WLID, ");
        //        SQL.ParamByName("XLWBBJDMX_WLID").Value = dsNR.Tables[0].Rows[i][String.Format("XLWBBJDNR_LMNR01")].ToString().Trim();


        //        SQL.Add(":XLWBBJDMX_XLXHID, ");
        //        SQL.ParamByName("XLWBBJDMX_XLXHID").Value = sXLXHID.ToUpper();//型号控制大写

        //        SQL.Add(":XLWBBJDMX_XLGGID, ");
        //        SQL.ParamByName("XLWBBJDMX_XLGGID").Value = XLBJDUtils.GetStaXLGGID(sXLGGID, sXLJGU007);

        //        SQL.Add(":XLWBBJDMX_XLBZID, ");
        //        SQL.ParamByName("XLWBBJDMX_XLBZID").Value = dsNR.Tables[0].Rows[i][String.Format("XLXLB_XLBZID")].ToString().Trim();

        //        SQL.Add(":XLWBBJDMX_DYDJID, ");
        //        SQL.ParamByName("XLWBBJDMX_DYDJID").Value = sDYDJID;

        //        SQL.Add(":XLWBBJDMX_XLYSID, ");
        //        SQL.ParamByName("XLWBBJDMX_XLYSID").Value = dsNR.Tables[0].Rows[i][String.Format("XLXLB_XLYSID")].ToString().Trim();
        //    }

        //}


        //更新标准字段
        public static void ConvertWBBJDMX_XLBZID(TDatabase dberp, TSQL SQL, string sWBBJDID, string sXLBJDID, string sDateTime, string sGH)
        {
            string sDefaultXLBZID = XLBJDUtils.GetDefaultXLBZID();

            Dictionary<string, string> dicDRMXBZ = new Dictionary<string, string>();
            dicDRMXBZ.Clear();
            SQL.Clear();
            SQL.Add("select XLBZ_XLBZID,XLBZ_XLBZLXMC from XLBZ where 1 = 1 ");
            DataSet dsXLBZ = dberp.OpenDataSet(SQL);
            if (dsXLBZ.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < dsXLBZ.Tables[0].Rows.Count; i++)
                {
                    dicDRMXBZ.Add(dsXLBZ.Tables[0].Rows[i]["XLBZ_XLBZID"].ToString().Trim(), dsXLBZ.Tables[0].Rows[i]["XLBZ_XLBZLXMC"].ToString().Trim());
                }
            }

            string sTemp = "";
            SQL.Clear();
            SQL.Add(" select XLWBBJDMX_BJDID,XLWBBJDMX_WBBJDID,XLWBBJDMX_DRHH,  ");
            SQL.Add("            XLWBBJDMX_BJCP,XLWBBJDMX_DYDJID,XLWBBJDMX_XLBZID,XLWBBJDMX_JLDW,XLWBBJDMX_BJSL,XLWBBJDMX_CPBJ,XLWBBJDMX_BZ,XLWBBJDMX_XLXHID,XLWBBJDMX_XLGGID,  ");
            SQL.Add("            XLWBBJDMX_WHR,XLWBBJDMX_WHRID,XLWBBJDMX_WHSJ ");
            SQL.Add("   from  XLWBBJDMX ");
            SQL.Add("      where XLWBBJDMX_WBBJDID=:pWBBJDID and XLWBBJDMX_BJDID=:pBJDID ");
            SQL.Add("   and  XLWBBJDMX_WHRID =:pXLWBBJDMX_WHRID and XLWBBJDMX_WHSJ=:pXLWBBJDMX_WHSJ ");
            SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
            SQL.ParamByName("pBJDID").Value = sXLBJDID;
            SQL.ParamByName("pXLWBBJDMX_WHRID").Value = sGH;
            SQL.ParamByName("pXLWBBJDMX_WHSJ").Value = CMISproConvert.StrToDateTime(sDateTime);
            DataSet ds = dberp.OpenDataSet(SQL);
            if (ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    if (!ds.Tables[0].Rows[i]["XLWBBJDMX_XLBZID"].ToString().Trim().Equals(""))
                    {
                        sTemp = XLBJDUtils.GetBZXLZBZID(ds.Tables[0].Rows[i]["XLWBBJDMX_XLBZID"].ToString().Trim(), sDefaultXLBZID, dicDRMXBZ);
                        SQL.Clear();
                        SQL.Add("update XLWBBJDMX set XLWBBJDMX_XLBZID = :pXLWBBJDMX_XLBZID ");
                        SQL.Add("   where 1= 1  ");
                        SQL.Add("      and XLWBBJDMX_WBBJDID=:pWBBJDID and XLWBBJDMX_BJDID=:pBJDID ");
                        SQL.Add("      and XLWBBJDMX_DRHH=:pDRHH ");
                        SQL.ParamByName("pXLWBBJDMX_XLBZID").Value = sTemp.Equals("") ? " " : sTemp;
                        SQL.ParamByName("pWBBJDID").Value = sWBBJDID;
                        SQL.ParamByName("pBJDID").Value = sXLBJDID;
                        SQL.ParamByName("pDRHH").Value = ds.Tables[0].Rows[i]["XLWBBJDMX_DRHH"].ToString().Trim();
                        dberp.ExecSQL(SQL);
                    }
                }
            }
        }

        //获取同一个目录分组号下的外部报价单ID
        public static string GetWBBJDListByMLFZH(string sBJDID, string sMLFZH)
        {
            string sWBBJDIDList = string.Empty;
            TDatabase dberp = TContextDatabases.GetDatabase("DBERP");
            TSQL SQL = new TSQL(dberp);
            SQL.Clear();
            SQL.Add(" select XLWBBJDML_WBBJDID from XLWBBJDML where XLWBBJDML_BJDID=:pBJDID  and XLWBBJDML_MLFZH=:pMLFZH  order by XLWBBJDML_WBBJDID ");
            SQL.ParamByName("pBJDID").Value = sBJDID;
            SQL.ParamByName("pMLFZH").Value = sMLFZH;
            DataSet ds = dberp.OpenDataSet(SQL);
            if (ds.Tables[0].Rows.Count > 0)
            {
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                    sWBBJDIDList += ds.Tables[0].Rows[i][0].ToString().Trim() + ",";
            }
            return sWBBJDIDList.TrimEnd(',');

        }

        //去除EXCEL导入最后的无效空行
        public static void DeleteInValidEmptyRow(ref DataSet aDataExcel)
        {
            int dsCount = aDataExcel.Tables[0].Rows.Count;
            for (int r = dsCount - 1; r >= 0; r--)
            {
                bool bEmptyRow = true;
                for (int j = 0; j < aDataExcel.Tables[0].Columns.Count; j++)
                {
                    if (!aDataExcel.Tables[0].Rows[r][j].ToString().Trim().Equals(""))
                    {
                        bEmptyRow = false;
                        break;
                    }
                }
                if (bEmptyRow)
                {
                    aDataExcel.Tables[0].Rows.RemoveAt(r);
                }
                else
                {
                    break;
                }
            }
        }


    }
}