test/MESEAI/EAItc/clsERP.cs

using iMESCIO.SCI.SMES.Request;
using iMESCIOC.Request.EAP;
using iMESCIOC.Request.WMS_Request;
using iMESCore.Base;
using iMESCore.DataBase;
using iMESCore.DataBase;
using iMESCore.Settings;
using iMESCore.Settings;
using kcOE;
using kcSYS;
using Microsoft.VisualBasic;
using Microsoft.VisualBasic.ApplicationServices;
using Microsoft.VisualBasic.CompilerServices;
using Org.BouncyCastle.Asn1.X9;
using Org.BouncyCastle.Crypto;
using Org.BouncyCastle.Crypto.Engines;
using Org.BouncyCastle.Crypto.Generators;
using Org.BouncyCastle.Crypto.Parameters;
using Org.BouncyCastle.Math;
using Org.BouncyCastle.Math.EC;
using Org.BouncyCastle.Security;
using Org.BouncyCastle.Utilities.Encoders;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Diagnostics;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Xml;
using static iMESCore.Base.iMESComXML;
using static iMESCore.Base.iMESConst;
using static iMESCore.DataBase.iMESSql;

namespace tcEAI_C
{
    public class clsERP
    {
        #region --- Combine (Exception, Message) ---

        public static bool combineException(ref List<ArrayList> strException, string code = null, string sysmsg = null, string mesmsg = null, string stack = null)
        {
            bool result = false;
            try
            {
                var al = new ArrayList();
                al.Add(code);
                al.Add(sysmsg);
                al.Add(mesmsg);
                al.Add(stack);
                strException.Add(al);
                result = true;
            }
            catch (Exception ex)
            {
                result = false;
            }

            return default(bool);

        }

        public static bool combineMessage(ref List<ArrayList> strMessage, string mcode = null, string mtype = null, string mmsg = null)
        {
            bool result = false;
            try
            {
                var al = new ArrayList();
                al.Add(mcode);
                al.Add(mtype);
                al.Add(mmsg);
                strMessage.Add(al);
                result = true;
            }
            catch (Exception ex)
            {
                result = false;
            }

            return default(bool);
        }

        public static bool combineFileMessage(ref List<ArrayList> aryFileMessage, string mmsg)
        {
            bool result = false;

            try
            {
                var al = new ArrayList();
                al.Add(mmsg);
                aryFileMessage.Add(al);
                result = true;
            }
            catch (Exception ex)
            {
                result = false;
            }

            return result;
        }
        #endregion


        #region "add by 14166  for 加密接口"

        /// <summary>
        /// 检查用户
        /// </summary>
        /// <param name="UserNo"></param>
        /// <param name="UserPassword"></param>
        /// <param name="strMessage"></param>
        /// <param name="strException"></param>
        /// <returns></returns>
        public static string funCheckUser(string UserNo, string UserPassword)
        {
            string funCheckUserRet = default(string);
            funCheckUserRet = "fail";

            AppSettings objSetting = new AppSettings();

            DbDataReader drTemp = null;
            IDbConnection cnnTemp = null;
            string strSQL = "";

            var objSYS = new kcSYS.clsSYSFunction();

            string PasswordDecoding = "";//接口传入密码
            string sysPassword = "";//MES系统绑定DS密码

            try
            {

                string strDataBaseType = objSetting.GetDataBaseType();
                string strConnectionString = objSetting.GetConnectionString(strDataBaseType);
                cnnTemp = CreateConnection(strConnectionString); // Create connection

                strSQL = " select PASSWORD from tblusruserbasis where userno='" + UserNo + "' ";
                drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                if (drTemp.Read())
                {
                    sysPassword = drTemp["PASSWORD"].ToString();
                }
                else
                {
                    throw new iMESException.MESException("0000-202001", "[%UserNo%] : " + "DS");
                }
                drTemp.Close();



                sysPassword = objSYS.PasswordDecoding(sysPassword);


                //SM2解密
                // 生成密钥对

                string privateKey = "Ja4UIUJz7XRNDhIiuWXwL78qd1Pc7SC0/Z9LzyF4SL8=";
                //string publicKey = "BGe1BZDFN+NhCQtc2qlVk8nUlXrIwcyjT3mMt7Xx3BkDNBGBQjFPV0+h3/cGUYXo2TFI1SShS7hWl9zi6SxUHvg=";

                //解密
                PasswordDecoding = Decrypt(UserPassword, privateKey);


                if (sysPassword != PasswordDecoding)
                {
                    throw new iMESException.MESException("", "用户：" + UserNo + "权限校验失败");
                }

                //if (strException.Count > 0)
                //    goto exitfun;


                funCheckUserRet = "success";


                //exitfun:
                ;

            }
            catch (Exception ex)
            {
                throw;
                //clsERP.combineException(ref strException, sysmsg: ex.Message, mesmsg: "[%CheckUserFail%]", stack: iMESCore.Base.iMESComXML.CInput(ex.StackTrace));
            }

            finally
            {
                CloseConnection(cnnTemp);
            }
            return funCheckUserRet;


        }



        /// <summary>
        /// 生成 SM2 密钥对，密钥对使用 Base64 进行编码
        /// </summary>
        /// <param name="privateKey"></param>
        /// <param name="publicKey"></param>
        public static void GenerateSM2KeyPair(out string privateKey, out string publicKey)
        {
            // 获取 SM2 曲线参数
            X9ECParameters curve = ECNamedCurveTable.GetByName("sm2p256v1");
            KeyGenerationParameters parameters = new ECKeyGenerationParameters(new ECDomainParameters(curve), new SecureRandom());

            // 创建 SM2 密钥对生成器
            ECKeyPairGenerator generator = new ECKeyPairGenerator();
            generator.Init(parameters);

            // 创建密钥对
            var keyPair = generator.GenerateKeyPair();

            // 私钥
            ECPrivateKeyParameters privateKeyParameters = (ECPrivateKeyParameters)keyPair.Private;
            privateKey = Base64.ToBase64String(privateKeyParameters.D.ToByteArrayUnsigned());

            // 公钥
            ECPublicKeyParameters publicKeyParameters = (ECPublicKeyParameters)keyPair.Public;
            publicKey = Base64.ToBase64String(publicKeyParameters.Q.GetEncoded());
        }


        /// <summary>
        /// SM2公钥加密
        /// </summary>
        /// <param name="strs"></param>
        /// <param name="publicKey"></param>
        /// <returns></returns>
        public static string Encrypt(string strs, string publicKey)
        {
            //获取SM2曲线参数
            X9ECParameters curve = ECNamedCurveTable.GetByName("sm2p256v1");

            ECPoint q = curve.Curve.DecodePoint(Convert.FromBase64String(publicKey));
            ECDomainParameters domainParameters = new ECDomainParameters(curve);
            ECPublicKeyParameters publicKeyParameters = new ECPublicKeyParameters("EC", q, domainParameters);

            //创建SM2加密器
            SM2Engine engine = new SM2Engine();
            engine.Init(true, new ParametersWithRandom(publicKeyParameters, new SecureRandom()));

            //把待加密的字符串转换成字节数组
            byte[] bytes = Encoding.UTF8.GetBytes(strs);

            //执行加密
            byte[] result = engine.ProcessBlock(bytes, 0, bytes.Length);

            //将加密结果转换成Base64字符串，并返回
            return Convert.ToBase64String(result);
        }

        /// <summary>
        /// SM2私钥解密
        /// </summary>
        /// <param name="strs"></param>
        /// <param name="privateKey"></param>
        /// <returns></returns>
        public static string Decrypt(string strs, string privateKey)
        {

            try
            {
                //获取SM2曲线参数
                X9ECParameters curve = ECNamedCurveTable.GetByName("sm2p256v1");

                ECDomainParameters domainParameters = new ECDomainParameters(curve);
                BigInteger bigInteger = new BigInteger(1, Convert.FromBase64String(privateKey));
                ECPrivateKeyParameters privateKeyParameters = new ECPrivateKeyParameters(bigInteger, domainParameters);

                //创建SM2解密器
                SM2Engine engine = new SM2Engine();
                engine.Init(false, privateKeyParameters);

                //把待解密的Base64字符串转换成字节数组
                byte[] bytes = Convert.FromBase64String(strs);

                //执行解密
                byte[] result = engine.ProcessBlock(bytes, 0, bytes.Length);

                //将解密结果转换成字符串，并返回
                return Encoding.UTF8.GetString(result);
            }
            catch (FormatException)
            {
                throw new ArgumentException("Base64格式无效", nameof(strs));
            }
            catch (Exception ex)
            {
                // 其他所有异常包装后重新抛出
                throw new InvalidOperationException($"SM2解密失败: {ex.Message}", ex);
            }

            //try
            //{
            //    // 获取SM2曲线参数
            //    var curve = ECNamedCurveTable.GetByName("sm2p256v1");
            //    var domainParameters = new ECDomainParameters(curve);

            //    var privateKeyBytes = Convert.FromBase64String(_privateKey);
            //    var bigInteger = new BigInteger(1, privateKeyBytes);
            //    var privateKeyParameters = new ECPrivateKeyParameters(bigInteger, domainParameters);

            //    // 创建SM2解密器
            //    using var engine = new SM2Engine();
            //    engine.Init(false, privateKeyParameters);

            //    // 解密
            //    var encryptedBytes = Convert.FromBase64String(encryptedText);
            //    var resultBytes = engine.ProcessBlock(encryptedBytes, 0, encryptedBytes.Length);

            //    return Encoding.UTF8.GetString(resultBytes);
            //}
            //catch (FormatException)
            //{
            //    throw new ArgumentException("Base64格式无效", nameof(encryptedText));
            //}
            //catch (Exception ex)
            //{
            //    // 记录日志
            //    LogError($"SM2解密失败: {ex.Message}");
            //    throw new CryptographicException("解密过程中发生错误", ex);
            //}


        }







        #endregion

        public static bool addTransactionLog_ErrorCode(string TransactionID, string ModuleID, string FunctionID, string ComputerName, string CurUserNo, string SendTime, string Result, string KeyValue, List<ArrayList> strMessage, List<ArrayList> strException, string InXml, string OutXml, string ExceptionFun, int KeepDays, int KeepXML, string ErrorCode = "0000-999999", string LogClass = null)
        {
            bool r = false;
            var CollectionSQL = new Collection();

            try
            {
                AppSettings objSetting = new AppSettings();
                // 2017/12/06 OwenLiu, mantis:0042964 修正ERP交易紀錄刪除舊資料因時間不同步造成的問題
                var datEventTime = DateTime.Now;
                string strDataBaseType = objSetting.GetDataBaseType();
                string strConnectionString = objSetting.GetConnectionString(strDataBaseType);

                List<List<clsDbParameter>> parameters = new List<List<clsDbParameter>>();
                List<clsDbParameter> pars1 = new List<clsDbParameter>();
                List<clsDbParameter> pars2 = new List<clsDbParameter>();

                parameters.Add(pars1);

                // Transaction(ID)
                string g = Guid.NewGuid().ToString();
                // 2022/12/2,Ning,122356: 台基---erp交易记录无法查询
                g = g.Replace(Constants.vbNullChar, "");


                if (TransactionID == null)
                    pars1.Add(new clsDbParameter("TransactionID", DBNull.Value));
                else
                    pars1.Add(new clsDbParameter("TransactionID", TransactionID));
                if (ModuleID == null)
                    pars1.Add(new clsDbParameter("ModuleID", DBNull.Value));
                else
                    pars1.Add(new clsDbParameter("ModuleID", ModuleID));
                if (FunctionID == null)
                    pars1.Add(new clsDbParameter("FunctionID", DBNull.Value));
                else
                    pars1.Add(new clsDbParameter("FunctionID", FunctionID));
                if (ComputerName == null)
                    pars1.Add(new clsDbParameter("ComputerName", DBNull.Value));
                else
                    pars1.Add(new clsDbParameter("ComputerName", ComputerName));
                if (CurUserNo == null)
                    pars1.Add(new clsDbParameter("CurUserNo", DBNull.Value));
                else
                    pars1.Add(new clsDbParameter("CurUserNo", CurUserNo));
                if (SendTime == null || !Information.IsDate(SendTime))
                    pars1.Add(new clsDbParameter("SendTime", DBNull.Value));
                else
                    pars1.Add(new clsDbParameter("SendTime", Convert.ToDateTime(SendTime)));
                if (Result == null)
                    pars1.Add(new clsDbParameter("Result", DBNull.Value));
                else
                    pars1.Add(new clsDbParameter("Result", Result));
                if (KeyValue == null)
                    pars1.Add(new clsDbParameter("KeyValue", DBNull.Value));
                else
                    pars1.Add(new clsDbParameter("KeyValue", KeyValue));

                // 2017/12/06 OwenLiu, mantis:0042964 修正ERP交易紀錄刪除舊資料因時間不同步造成的問題
                pars1.Add(new clsDbParameter("CreateDate", datEventTime));

                if (LogClass == null)
                    pars1.Add(new clsDbParameter("LogClass", DBNull.Value));
                else
                    pars1.Add(new clsDbParameter("LogClass", LogClass));
                if (Result != null && Result.ToUpper() == "SUCCESS")
                    pars1.Add(new clsDbParameter("ErrorCode", DBNull.Value));
                else
                    pars1.Add(new clsDbParameter("ErrorCode", ErrorCode));
                if (Result != null && Result.ToUpper() == "SUCCESS")
                    pars1.Add(new clsDbParameter("ErrorType", DBNull.Value));
                else if (ErrorCode == "0000-999999")
                    pars1.Add(new clsDbParameter("ErrorType", 2));
                else
                    pars1.Add(new clsDbParameter("ErrorType", 1));

                CollectionSQL.Add("insert into tblERPTransactionLog(id,transactionid,moduleid,functionid,computername,curuserno,sendtime,result,keyvalue,createdate,logclass,errorcode,errortype) " +
                "values('" + g + "',:TransactionID, :ModuleID, :FunctionID, :ComputerName, :CurUserNo, :SendTime, :Result, :KeyValue, :CreateDate, :LogClass, :ErrorCode, :ErrorType)");

                // ----- Transaction XML Log 組參數-----
                if (KeepXML != 0)
                {
                    parameters.Add(pars2);

                    if (strMessage.Count == 0)
                    {
                        pars2.Add(new clsDbParameter("Message", DBNull.Value));
                    }
                    else
                    {
                        // mcode, mtype, mmsg
                        string str = string.Empty;
                        foreach (ArrayList al in strMessage)
                        {
                            // 2018/05/10 OwenLiu, 調整TransactionLog針對 ArrayList處理邏輯，以便與中台EAI共用
                            if (!string.IsNullOrEmpty(str))
                                str += Convert.ToString(ControlChars.Cr);
                            if (!string.IsNullOrEmpty(Convert.ToString(al[0])))
                            {
                                str = str + "mcode: " + al[0] + ";".ToString();
                            }
                            if (!string.IsNullOrEmpty(Convert.ToString(al[0])))
                            {
                                str = str + "mtype: " + al[1] + ";".ToString();
                            }
                            // str += "mmsg: " & al.Item(2)
                            if (!string.IsNullOrEmpty(Convert.ToString(al[0])))
                            {
                                str = str + "mmsg: " + al[2] + ";".ToString();
                            }
                        }
                        pars2.Add(new clsDbParameter("Message", str));
                    }

                    if (strException.Count == 0)
                    {
                        pars2.Add(new clsDbParameter("Exception", DBNull.Value));
                    }
                    else
                    {
                        // code, sysmsg, mesmsg, stack
                        string str = string.Empty;
                        foreach (ArrayList al in strException)
                        {
                            // 2018/05/10 OwenLiu, 調整TransactionLog針對 ArrayList處理邏輯，以便與中台EAI共用
                            if (!string.IsNullOrEmpty(str))
                                str += Convert.ToString(ControlChars.Cr);
                            if (!string.IsNullOrEmpty(Convert.ToString(al[0])))
                            {
                                str = str + "code: " + al[0] + ";".ToString();
                            }
                            if (!string.IsNullOrEmpty(Convert.ToString(al[1])))
                            {
                                str = str + "sysmsg: " + al[1] + ";".ToString();
                            }
                            if (!string.IsNullOrEmpty(Convert.ToString(al[2])))
                            {
                                str = str + "mesmsg: " + al[2].ToString().Replace("[%", "").Replace("%]", "") + ";";
                            }
                            if (!string.IsNullOrEmpty(Convert.ToString(al[3])))
                            {
                                str = str + "stack: " + al[3].ToString();
                            }
                        }
                        pars2.Add(new clsDbParameter("Exception", str));
                    }

                    if (InXml == null)
                        pars2.Add(new clsDbParameter("InXml", DBNull.Value));
                    else
                        pars2.Add(new clsDbParameter("InXml", InXml));
                    if (OutXml == null)
                        pars2.Add(new clsDbParameter("OutXml", DBNull.Value));
                    else
                        pars2.Add(new clsDbParameter("OutXml", OutXml));
                    if (ExceptionFun == null)
                        pars2.Add(new clsDbParameter("ExceptionFun", DBNull.Value));
                    else
                        pars2.Add(new clsDbParameter("ExceptionFun", ExceptionFun));

                    // -----執行Transaction XML Log-----
                    CollectionSQL.Add("insert into tblERPTransactionXMLLog(id,messagexml,exceptionxml,inxml,outxml,exceptionfun) values('" + g +
                    "',:Message, :Exception, :InXml, :OutXml, :ExceptionFun)");
                }
                ExecuteSQLNoneQuery_UPD_Parameter(strDataBaseType, strConnectionString, CollectionSQL, parameters);

                r = true;
            }

            catch (Exception ex)
            {
                r = false;
                throw;
            }

            return r;
        }


        #region-----------add by 14166 for 新增人员接口-----------
        /// <summary>
        /// (ref strMessage, ref strException, UserNo, UserName, UserLevel, Creator, CreateDate, Description, IssueState, ChangeDate, ResetPassword, UserType, UserDP, UserEmail);
        /// </summary>
        /// <param name="aryMessage"></param>
        /// <param name="aryException"></param>
        /// <param name="UserNo"></param>
        /// <param name="UserName"></param>
        /// <param name="UserLever"></param>
        /// <param name="UserDP"></param>
        /// <param name="UserEmail"></param>
        /// <returns></returns>
        public static bool funAddUser(ref List<ArrayList> aryMessage, ref List<ArrayList> aryException, string UserNo = defString, string UserName = defString, string UserLever = defString, string Creator = defString, string CreateDate = defString, string Description = defString, string IssueState = defString, string ChangeDate = defString, string ResetPassword = defString, string UserType = defString, string UserDP = defString, string UserEmail = defString, string Password = defString)
        {
            bool result = false;
            DataRow dr;
            int i, j;
            string strSQLAddField, strSQLAddValue;
            Collection MTLCollection = new Collection();
            Collection CollectionSQL = new Collection();


            string strSQL = "";

            IDbConnection cnnTemp = null;
            DbDataReader drTemp;
            AppSettings objSetting = new AppSettings();
            string strDataBaseType = objSetting.GetDataBaseType();
            string strConnectionString = objSetting.GetConnectionString(strDataBaseType);

            bool blnAdd = false; ;
            string PasswordDecoding = "";
            try
            {
                try
                {
                    // //Create connection
                    cnnTemp = CreateConnection(strConnectionString);

                    //默认用户密码123
                    using (var objsys = new clsSYSFunction())
                    {
                        PasswordDecoding = objsys.PasswordEncoding(Password);
                    }

                    try
                    {
                        strSQL = " SELECT * FROM TBLUSRUSERBASIS WHERE USERNO='" + UserNo + "' ";
                        drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                        if (drTemp.Read())
                        {
                            throw new iMESException.MESException("0000-003000", "[%UserNo : " + UserNo + "人员已存在!%]");
                        }
                        else
                        {
                            blnAdd = true;
                        }
                        drTemp.Close();

                        // ----- Exception => Fail -----
                        if (aryException.Count > 0)
                            goto exitfun;

                        // Add 
                        if (blnAdd)
                        {
                            strSQLAddField = " INSERT INTO TBLUSRUSERBASIS (USERNO,USERNAME,USERLEVEL,PASSWORD,DEPARTMENTNO,EMAILADDRESS,DESCRIPTION,USERTYPE,ISSUESTATE,RESETPASSWORD,CREATOR,CREATEDATE,CHANGEDATE";
                            strSQLAddValue = " Values ('" + UserNo + "','" + UserName + "','" + UserLever + "','" + PasswordDecoding + "','" + UserDP + "','" + UserEmail + "','" + Description + "','" + UserType + "','" + IssueState + "','" + ResetPassword + "','" + Creator + "',To_Date('" + Strings.Format(Conversions.ToDate(CreateDate), "yyyy/MM/dd H:mm:ss") + "','YYYY/MM/DD HH24:MI:SS'),To_Date('" + Strings.Format(Conversions.ToDate(ChangeDate), "yyyy/MM/dd H:mm:ss") + "','YYYY/MM/DD HH24:MI:SS')";

                            // 當沒有額外的欄位時,直接給定strSQL
                            strSQL = strSQLAddField + ")" + strSQLAddValue + ")";

                            CollectionSQL.Add(strSQL);

                        }
                        else
                        {
                            // Edit
                            //strSQL = " UPDATE TBLUSRUSERBASIS set USERNAME='" + UserName + "',USERLEVEL='" + UserLever + "',DEPARTMENTNO='" + UserDP + "',EMAILADDRESS='" + UserEmail + "' WHERE USERNO='" + UserNo + "' ";
                            strSQL = " UPDATE TBLUSRUSERBASIS set USERNAME='" + UserName + "',USERLEVEL='" + UserLever + "' WHERE USERNO='" + UserNo + "' ";
                            CollectionSQL.Add(strSQL);

                        }
                        // 呼叫執行SQL指令 
                        ExecuteSQLNoneQuery_UPD(strDataBaseType, strConnectionString, CollectionSQL);
                        result = true;

                        //clsERP.combineMessage(ref aryMessage, null, null, "[%AddUserSuccess%]");
                    }
                    catch (Exception ex)
                    {
                        // trans.Rollback()
                        clsERP.combineException(ref aryException, null, ex.Message, "[%AddUserFail%]", CInput(ex.StackTrace));
                    }
                }
                catch (Exception ex)
                {
                    clsERP.combineException(ref aryException, null, ex.Message, "[%AddUserFail%]", CInput(ex.StackTrace));
                }

            exitfun:
                ;
            }
            catch (Exception ex)
            {
                clsERP.combineException(ref aryException, null, ex.Message, "[%AddUserFail%]", CInput(ex.StackTrace));
            }
            finally
            {
                CloseConnection(cnnTemp);
            }


            return result;
        }


        public static bool funDelUser(ref List<ArrayList> aryMessage, ref List<ArrayList> aryException, string UserNo = defString, string UserName = defString, string UserLever = defString, string Creator = defString, string CreateDate = defString, string Description = defString, string IssueState = defString, string ChangeDate = defString, string ResetPassword = defString, string UserType = defString, string UserDP = defString, string UserEmail = defString, string Password = defString)
        {
            bool result = false;

            Collection CollectionSQL = new Collection();


            string strSQL = "";

            IDbConnection cnnTemp = null;
            DbDataReader drTemp;
            AppSettings objSetting = new AppSettings();
            string strDataBaseType = objSetting.GetDataBaseType();
            string strConnectionString = objSetting.GetConnectionString(strDataBaseType);


            try
            {
                try
                {
                    // //Create connection
                    cnnTemp = CreateConnection(strConnectionString);

                    ////默认用户密码123
                    //using (var objsys = new clsSYSFunction())
                    //{
                    //    PasswordDecoding = objsys.PasswordEncoding(Password);
                    //}

                    try
                    {
                        strSQL = " SELECT * FROM TBLUSRUSERBASIS WHERE USERNO='" + UserNo + "' ";
                        drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                        if (!drTemp.Read())
                        {
                            throw new iMESException.MESException("0000-003000", "[%UserNo : " + UserNo + "人员不存在!%]");
                        }
                        drTemp.Close();

                        // ----- Exception => Fail -----
                        if (aryException.Count > 0)
                            goto exitfun;


                        strSQL = "DELETE FROM TBLUSRUSERBASIS WHERE USERNO='" + UserNo + "'  ";
                        CollectionSQL.Add(strSQL);

                        // 呼叫執行SQL指令 
                        ExecuteSQLNoneQuery_UPD(strDataBaseType, strConnectionString, CollectionSQL);
                        result = true;

                    }
                    catch (Exception ex)
                    {
                        // trans.Rollback()
                        clsERP.combineException(ref aryException, null, ex.Message, "[%DelUserFail%]", CInput(ex.StackTrace));
                    }
                }
                catch (Exception ex)
                {
                    clsERP.combineException(ref aryException, null, ex.Message, "[%DelUserFail%]", CInput(ex.StackTrace));
                }

            exitfun:
                ;
            }
            catch (Exception ex)
            {
                clsERP.combineException(ref aryException, null, ex.Message, "[%DelUserFail%]", CInput(ex.StackTrace));
            }
            finally
            {
                CloseConnection(cnnTemp);
            }


            return result;
        }


        #endregion



        #region 辅料调拨接口

        public static bool funAddMaterial_WIP(string SubInventoryTransferNo, string Description, string Creator, int Status, DateTime CreateDate = default(DateTime), DataTable dtMTL = null, List<ArrayList> aryMessage = null, List<ArrayList> aryException = null, List<ArrayList> aryAlert = null)
        {
            bool AddMaterial_WIPRet = default(bool);

            bool r = false;
            string strSQL = default(string);
            IDbConnection cnnTemp = null;
            DbDataReader drTemp;

            AppSettings objSetting = new AppSettings();
            var CollectionSQL = new Collection();

            try
            {
                string strDataBaseType = objSetting.GetDataBaseType();
                string strConnectionString = objSetting.GetConnectionString(strDataBaseType);
                cnnTemp = CreateConnection(strConnectionString);

                try
                {
                    if (dtMTL.Rows.Count > 0)
                    {
                        //status：0为发料，1为退料；
                        if (Status == 0)
                        {
                            foreach (DataRow row in dtMTL.Rows)
                            {
                                //若是同仓库编号、同物料编号、同物料批号，则数量进行叠加，若是其中一项不一样，则进行新增；
                                strSQL = " select * from TBLINVWIPINVENTORY_RAW where INVENTORYNO = '" + row["ToInventoryNo"] + "' and MATERIALNO = '" + row["MaterialNo"] + "' and MATERIALLOTNO = '" + row["MaterialLotNo"] + "' ";
                                drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                                if (drTemp.Read())
                                {
                                    //叠加
                                    strSQL = " update TBLINVWIPINVENTORY_RAW set QTY = QTY + " + Convert.ToInt32(row["TransferQty"]) + " where INVENTORYNO = '" + row["ToInventoryNo"] + "' and MATERIALNO = '" + row["MaterialNo"] + "' and MATERIALLOTNO = '" + row["MaterialLotNo"] + "' ";
                                    CollectionSQL.Add(strSQL);
                                }
                                else
                                {
                                    //TBLMTLMATERIALBASIS
                                    strSQL = " select * from TBLMTLMATERIALBASIS where  MATERIALNO = '" + row["MaterialNo"] + "' ";
                                    drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                                    if (drTemp.Read())
                                    {
                                        //新增
                                        strSQL = " insert into TBLINVWIPINVENTORY_RAW(INVENTORYNO,MATERIALNO,MATERIALLOTNO,UNITNO,QTY,INPUTDATE,LOCATORNO,MATERIALTYPE) ";
                                        strSQL += " values('" + row["ToInventoryNo"] + "','" + row["MaterialNo"] + "','" + row["MaterialLotNo"] + "','" + row["MaterialUnitNo"] + "'," + Convert.ToInt32(row["TransferQty"]) + ",To_Date('" + Strings.Format(CreateDate, "yyyy/MM/dd H:mm:ss") + "','YYYY/MM/DD HH24:MI:SS'),'N/A','" + drTemp["MATERIALTYPE"] + "') ";
                                        CollectionSQL.Add(strSQL);
                                    }
                                    else
                                    {
                                        combineException(ref aryException, mesmsg: "[%物料基本资料无相关信息%]");
                                    }

                                }

                                //记录调拨以及取消调拨记录
                                strSQL = " insert into TBLINVWIPINVENTORY_RAWLOG(subinventorytransferno,materialno,materiallotno,transferqty,materialunitno,frominventoryno,fromlocatorno,toinventoryno,tolocatorno,description,creator,createdate,Status) ";
                                strSQL += " values('" + SubInventoryTransferNo + "','" + row["MaterialNo"] + "','" + row["MaterialLotNo"] + "'," + Convert.ToInt32(row["TransferQty"]) + ",'" + row["MaterialUnitNo"] + "','" + row["FromInventoryNo"] + "','" + row["FromLocatorNo"] + "','" + row["ToInventoryNo"] + "','" + row["ToLocatorNo"] + "','" + Description + "','" + Creator + "',To_Date('" + Strings.Format(CreateDate, "yyyy/MM/dd H:mm:ss") + "','YYYY/MM/DD HH24:MI:SS')," + Status + ") ";
                                CollectionSQL.Add(strSQL);
                            }
                        }
                        else if (Status == 1)
                        {
                            foreach (DataRow row in dtMTL.Rows)
                            {
                                //取消调拨：则直接扣减数量即可，若是扣减数量 > 库存数，则直接回复失败：库存不足；
                                strSQL = " select * from TBLINVWIPINVENTORY_RAW where INVENTORYNO = '" + row["FromInventoryNo"] + "' and MATERIALNO = '" + row["MaterialNo"] + "' and MATERIALLOTNO = '" + row["MaterialLotNo"] + "' ";
                                drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                                if (drTemp.Read())
                                {
                                    if (Convert.ToInt32(row["TransferQty"]) > Convert.ToInt32(drTemp["QTY"]))
                                    {
                                        combineException(ref aryException, mesmsg: "[%库存不足%]");
                                    }
                                    else
                                    {
                                        //扣减
                                        strSQL = " update TBLINVWIPINVENTORY_RAW set QTY = QTY - " + Convert.ToInt32(row["TransferQty"]) + " where INVENTORYNO = '" + row["FromInventoryNo"] + "' and MATERIALNO = '" + row["MaterialNo"] + "' and MATERIALLOTNO = '" + row["MaterialLotNo"] + "' ";
                                        CollectionSQL.Add(strSQL);

                                        //记录调拨以及取消调拨记录
                                        strSQL = " insert into TBLINVWIPINVENTORY_RAWLOG(subinventorytransferno,materialno,materiallotno,transferqty,materialunitno,frominventoryno,fromlocatorno,toinventoryno,tolocatorno,description,creator,createdate,Status) ";
                                        strSQL += " values('" + SubInventoryTransferNo + "','" + row["MaterialNo"] + "','" + row["MaterialLotNo"] + "'," + Convert.ToInt32(row["TransferQty"]) + ",'" + row["MaterialUnitNo"] + "','" + row["FromInventoryNo"] + "','" + row["FromLocatorNo"] + "','" + row["ToInventoryNo"] + "','" + row["ToLocatorNo"] + "','" + Description + "','" + Creator + "',To_Date('" + Strings.Format(CreateDate, "yyyy/MM/dd H:mm:ss") + "','YYYY/MM/DD HH24:MI:SS')," + Status + ") ";
                                        CollectionSQL.Add(strSQL);

                                    }

                                }

                            }
                        }
                    }
                    else
                    {
                        combineException(ref aryException, mesmsg: "[%无调拨信息%]");
                    }

                    if (aryException.Count > 0)
                        goto exitfun;

                    ExecuteSQLNoneQuery_UPD(objSetting.GetDataBaseType(), objSetting.GetConnectionString(), CollectionSQL);
                    r = true;
                    combineMessage(ref aryMessage, mmsg: "[%AddMaterial_WIPSuccess%]");
                }

                catch (Exception ex)
                {
                    combineException(ref aryException, sysmsg: ex.Message, mesmsg: "[%AddMaterial_WIPFail%]", stack: iMESCore.Base.iMESComXML.CInput(ex.StackTrace));

                }

            exitfun:
                ;
            }
            catch (Exception ex)
            {
                combineException(ref aryException, sysmsg: ex.Message, mesmsg: "[%AddMaterial_WIPFail%]", stack: iMESCore.Base.iMESComXML.CInput(ex.StackTrace));
            }
            finally
            {
                CloseConnection(cnnTemp);
                AddMaterial_WIPRet = r;
            }

            return AddMaterial_WIPRet;
        }

        #endregion

        #region MES箱号变更
        #endregion

        #region 湿敏接口
        #endregion

        #region MES退料
        #endregion

        #region  "WMS发料回传前,先回传发料明细关联reel信息,datahub调用MES服务,推送发料明细数据至MES;"
        public static bool funAddMaterial_WIP(string factory, DataTable dtReel)
        {
            bool funAddMaterial_WIP = default(bool);
            bool r = false;
            string strSQL = default(string);
            IDbConnection cnnTemp = null;
            DbDataReader drTemp;

            AppSettings objSetting = new AppSettings();
            var CollectionSQL = new Collection();

            string strDataBaseType = objSetting.GetDataBaseType();
            string strConnectionString = objSetting.GetConnectionString(strDataBaseType);
            cnnTemp = CreateConnection(strConnectionString);

            DataRow[] dataRows = null;
            try
            {

                if (dtReel.Rows.Count == 0)
                {
                    throw new Exception("无明细数据！");
                }

                //先取出dtReel所有的MATERIALNO字段，拼接成IN语句
                string strMaterialNo = "";
                for (int i = 0; i <= dtReel.Rows.Count - 1; i++)
                {
                    if (strMaterialNo == "")
                    {
                        strMaterialNo = "'" + dtReel.Rows[i]["MCODE"] + "'";
                    }
                    else
                    {
                        strMaterialNo = strMaterialNo + ",'" + dtReel.Rows[i]["MCODE"] + "'";
                    }
                }

                strSQL = "SELECT MATERIALNO,MATERIALTYPE,UNITNO FROM TBLMTLMATERIALBASIS  WHERE MATERIALNO IN (" + strMaterialNo + ") AND ISSUESTATE = 2";
                DataTable dtMaterial = new DataTable("TBLMTLMATERIALBASIS");
                ExecuteSQLQuery_Adapter(strSQL, dtMaterial, cnnTemp);
                if (dtMaterial.Rows.Count == 0)
                {
                    throw new Exception("MES无物料信息！");
                }


                for (int i = 0; i <= dtReel.Rows.Count - 1; i++)
                {

                    //物料单位
                    string strUnitNo = "";

                    dataRows = dtMaterial.Select("MATERIALNO = '" + dtReel.Rows[i]["MCODE"] + "'");
                    if (dataRows.Length > 0)
                    {
                        strUnitNo = dataRows[0]["UNITNO"].ToString();
                    }
                    else
                    {
                        throw new Exception($"{dtReel.Rows[i]["MCODE"]}找不到对应的物料信息！");
                    }

                    //数量
                    decimal QTY = 0;
                    if (dtReel.Rows[i]["QTY"].ToString() != "")
                    {
                        QTY = Convert.ToDecimal(dtReel.Rows[i]["QTY"]);
                    }

                    //物料类别 MATERIALTYPE 来着dtMaterial
                    string strMaterialType = "";
                    if (dataRows.Length > 0)
                    {
                        strMaterialType = dataRows[0]["MATERIALTYPE"].ToString();
                    }

                    //输入时间 INPUTDATE 来着于 dtReel的MTIME，时间格式，orcal
                    DateTime InputDate = DateTime.Now;
                    if (dtReel.Rows[i]["MTIME"].ToString() != "")
                    {
                        //InputDate = Convert.ToDateTime(dtReel.Rows[i]["MDATE"]);
                        InputDate = DateTime.ParseExact(dtReel.Rows[i]["MDATE"].ToString(), "yyyyMMdd", CultureInfo.InvariantCulture);
                    }

                    ////制造日期 MANUFACTUREDATE 来着于 dtReel的VENDERDATE，时间格式
                    DateTime ManufactureDate = ConvertYearWeekToMonday(dtReel.Rows[i]["VENDERDATE"].ToString());
                    //if (dtReel.Rows[i]["VENDERDATE"].ToString() != "")
                    //{
                    //    ManufactureDate = Convert.ToDateTime(dtReel.Rows[i]["VENDERDATE"]);
                    //}


                    //超限日期  EXDATE
                    DateTime EXDATE = DateTime.Now;
                    if (dtReel.Rows[i]["EXDATE"].ToString() != "")
                    {
                        EXDATE = DateTime.ParseExact(dtReel.Rows[i]["EXDATE"].ToString(), "yyyyMMdd", CultureInfo.InvariantCulture);
                    }
                    string MONO = "";
                    strSQL = $"SELECT * from TBLCUSMATERIALSUMDETAIL where MATERIALINNO = '{dtReel.Rows[i]["RECEIPTNO"].ToString()}'";
                    drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                    if (drTemp.Read())
                    { 
                        MONO = drTemp["MONO"].ToString();
                    }
                    drTemp.Close();

                    ////插入 TBLINVWIPINVENTORY_RAW
                    //strSQL = @"INSERT INTO TBLINVWIPINVENTORY_RAW(INVENTORYNO, MATERIALNO, MATERIALLOTNO, UNITNO, QTY, MATERIALTYPE, INPUTDATE,MANUFACTUREDATE,RECEIPTNO,REELID,EXDATE,DATECODE,LOTCODE,VENDORCODE,VENDORITEMCODE,MANUFACTURER,SNSCALE) ";
                    //strSQL += " VALUES('" + dtReel.Rows[i]["STORAGECODE"].ToString() + "','" + dtReel.Rows[i]["MCODE"].ToString() + "','" + dtReel.Rows[i]["REELID"].ToString() + "', '" + strUnitNo + "'," + QTY + ",'" + strMaterialType + "','" + dtReel.Rows[i]["MTIME"].ToString() + 
                    //    "','"+ dtReel.Rows[i]["VENDERDATE"] + "','" + dtReel.Rows[i]["RECEIPTNO"].ToString() + "' ,'" + dtReel.Rows[i]["REELID"].ToString() + "',To_Date('" + Strings.Format(EXDATE, "yyyy/MM/dd HH:mm:ss") + $"','YYYY/MM/DD HH24:MI:SS'),'{dtReel.Rows[i]["DATECODE"].ToString()}','{dtReel.Rows[i]["VENDERLOTNO"].ToString()}','{dtReel.Rows[i]["VENDORCODE"].ToString()}','{dtReel.Rows[i]["VENDORITEMCODE"].ToString()}','{dtReel.Rows[i]["MANUFACTURER"].ToString()}','{dtReel.Rows[i]["SNSCALE"].ToString()}')";

                    //CollectionSQL.Add(strSQL);

                    ////插入 TBLINVWIPINVENTORY_RAWLOG
                    //strSQL = @"INSERT INTO TBLINVWIPINVENTORY_RAWLOG(INVENTORYNO, MATERIALNO, MATERIALLOTNO, UNITNO, QTY, MATERIALTYPE, INPUTDATE,MANUFACTUREDATE,RECEIPTNO,REELID,EXDATE,EVENTTIME,DATECODE,LOTCODE,VENDORCODE,VENDORITEMCODE,MANUFACTURER,SNSCALE) ";
                    //strSQL += " VALUES('" + dtReel.Rows[i]["STORAGECODE"].ToString() + "','" + dtReel.Rows[i]["MCODE"].ToString() + "','" + dtReel.Rows[i]["REELID"].ToString() + "', '" + strUnitNo + "'," + QTY + ",'" + strMaterialType + "','"+ dtReel.Rows[i]["MTIME"].ToString() + 
                    //    "','"+ dtReel.Rows[i]["VENDERDATE"] + "','" + dtReel.Rows[i]["RECEIPTNO"].ToString() + "' ,'" + dtReel.Rows[i]["REELID"].ToString() + "',To_Date('" + Strings.Format(EXDATE, "yyyy/MM/dd HH:mm:ss") + "','YYYY/MM/DD HH24:MI:SS'),To_Date('" + Strings.Format(DateTime.Now, "yyyy/MM/dd HH:mm:ss") + $"','YYYY/MM/DD HH24:MI:SS'),'{dtReel.Rows[i]["DATECODE"].ToString()}','{dtReel.Rows[i]["VENDERLOTNO"].ToString()}','{dtReel.Rows[i]["VENDORCODE"].ToString()}','{dtReel.Rows[i]["VENDORITEMCODE"].ToString()}','{dtReel.Rows[i]["MANUFACTURER"].ToString()}','{dtReel.Rows[i]["SNSCALE"].ToString()}')";

                    //CollectionSQL.Add(strSQL);

                    //插入 TBLINVWIPINVENTORY_RAW
                    strSQL = @"INSERT INTO TBLINVWIPINVENTORY_RAW(INVENTORYNO, MATERIALNO, MATERIALLOTNO, UNITNO, QTY, MATERIALTYPE, INPUTDATE,MANUFACTUREDATE,RECEIPTNO,REELID,EXDATE,DATECODE,LOTCODE,VENDORCODE,VENDORITEMCODE,MANUFACTURER,SNSCALE,VENDERLOTNO,EXPIREDATE,MONO) ";
                    strSQL += " VALUES('" + dtReel.Rows[i]["STORAGECODE"].ToString() + "','" + dtReel.Rows[i]["MCODE"].ToString() + "','" + dtReel.Rows[i]["REELID"].ToString() + "', '" + strUnitNo + "'," + QTY + ",'" + strMaterialType + "',To_Date('" + Strings.Format(InputDate, "yyyy/MM/dd HH:mm:ss") + "','YYYY/MM/DD HH24:MI:SS')" +
                        ",To_Date('" + Strings.Format(ManufactureDate, "yyyy/MM/dd HH:mm:ss") + "','YYYY/MM/DD HH24:MI:SS'),'" + dtReel.Rows[i]["RECEIPTNO"].ToString() + "' ,'" + dtReel.Rows[i]["REELID"].ToString() + 
                        "',To_Date('" + Strings.Format(EXDATE, "yyyy/MM/dd HH:mm:ss") + $"','YYYY/MM/DD HH24:MI:SS'),'{dtReel.Rows[i]["DATECODE"].ToString()}','{dtReel.Rows[i]["VENDERLOTNO"].ToString()}','{dtReel.Rows[i]["VENDORCODE"].ToString()}','{dtReel.Rows[i]["VENDORITEMCODE"].ToString()}','{dtReel.Rows[i]["MANUFACTURER"].ToString()}','{dtReel.Rows[i]["SNSCALE"].ToString()}','{dtReel.Rows[i]["VENDERLOTNO"].ToString()}',To_Date('" + Strings.Format(EXDATE, "yyyy/MM/dd HH:mm:ss") + "','YYYY/MM/DD HH24:MI:SS'),'"+MONO+"')";

                    CollectionSQL.Add(strSQL);

                    //插入 TBLINVWIPINVENTORY_RAWLOG
                    strSQL = @"INSERT INTO TBLINVWIPINVENTORY_RAWLOG(INVENTORYNO, MATERIALNO, MATERIALLOTNO, UNITNO, QTY, MATERIALTYPE, INPUTDATE,MANUFACTUREDATE,RECEIPTNO,REELID,EXDATE,EVENTTIME,DATECODE,LOTCODE,VENDORCODE,VENDORITEMCODE,MANUFACTURER,SNSCALE,VENDERLOTNO,EXPIREDATE,MONO) ";
                    strSQL += " VALUES('" + dtReel.Rows[i]["STORAGECODE"].ToString() + "','" + dtReel.Rows[i]["MCODE"].ToString() + "','" + dtReel.Rows[i]["REELID"].ToString() + "', '" + strUnitNo + "'," + QTY + ",'" + strMaterialType + "',To_Date('" + Strings.Format(InputDate, "yyyy/MM/dd HH:mm:ss") + "','YYYY/MM/DD HH24:MI:SS')" +
                        ",To_Date('" + Strings.Format(ManufactureDate, "yyyy/MM/dd HH:mm:ss") + "','YYYY/MM/DD HH24:MI:SS'),'" + dtReel.Rows[i]["RECEIPTNO"].ToString() + "' ,'" + dtReel.Rows[i]["REELID"].ToString() + 
                        "',To_Date('" + Strings.Format(EXDATE, "yyyy/MM/dd HH:mm:ss") + "','YYYY/MM/DD HH24:MI:SS'),To_Date('" + Strings.Format(DateTime.Now, "yyyy/MM/dd HH:mm:ss") + $"','YYYY/MM/DD HH24:MI:SS'),'{dtReel.Rows[i]["DATECODE"].ToString()}','{dtReel.Rows[i]["VENDERLOTNO"].ToString()}','{dtReel.Rows[i]["VENDORCODE"].ToString()}','{dtReel.Rows[i]["VENDORITEMCODE"].ToString()}','{dtReel.Rows[i]["MANUFACTURER"].ToString()}','{dtReel.Rows[i]["SNSCALE"].ToString()}','{dtReel.Rows[i]["VENDERLOTNO"].ToString()}',To_Date('" + Strings.Format(EXDATE, "yyyy/MM/dd HH:mm:ss") + "','YYYY/MM/DD HH24:MI:SS'),'"+MONO+"')";

                    CollectionSQL.Add(strSQL);



                }

                if (CollectionSQL.Count > 0)
                {
                    ExecuteSQLNoneQuery_UPD(objSetting.GetDataBaseType(), objSetting.GetConnectionString(), CollectionSQL);
                }



            exitfun:
                ;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConnection(cnnTemp);
            }
            return funAddMaterial_WIP;

        }

        public static DateTime ConvertYearWeekToMonday(string yearWeek)
        {
            if (string.IsNullOrEmpty(yearWeek) || yearWeek.Length != 4)
                throw new ArgumentException("输入格式必须为4位数字，前2位为年份，后2位为周数");

            int year = int.Parse(yearWeek.Substring(0, 2)) + 2000;  // 26 -> 2026
            int week = int.Parse(yearWeek.Substring(2, 2));          // 21 -> 21

            return GetMondayOfWeek(year, week);
        }

        /// <summary>
        /// 获取指定年份第几周的周一日期
        /// </summary>
        public static DateTime GetMondayOfWeek(int year, int week)
        {
            // 1. 计算该年1月1日
            DateTime jan1 = new DateTime(year, 1, 1);

            // 2. 计算1月1日所在周的周一
            int daysOffset = ((int)jan1.DayOfWeek + 6) % 7;  // 周一=0, 周日=6
            DateTime firstMonday = jan1.AddDays(-daysOffset);

            // 3. 计算目标周周一（第1周从第一个周一开始）
            DateTime targetMonday = firstMonday.AddDays((week - 1) * 7);

            return targetMonday;
        }
        #endregion


        #region "SPC 接收数据"

        //计量
        public static bool funGetJLSPCData(ref List<ArrayList> aryMessage, ref List<ArrayList> aryException, string SPCSerial_QMS, string MaterialNo, string MTLVendor, string Employee, string LotNo, string OPNo, string QCItemNo, string DC, string PartNo, List<decimal> listTestValue)
        {
            bool funGetJLSPCData = default(bool);
            //bool r = false;
            //string strSQL = default(string);
            //IDbConnection cnnTemp = null;
            //DbDataReader drTemp;

            AppSettings objSetting = new AppSettings();


            //string strDataBaseType = objSetting.GetDataBaseType();
            //string strConnectionString = objSetting.GetConnectionString(strDataBaseType);
            //cnnTemp = CreateConnection(strConnectionString);

            DataSet dsData = new DataSet();



            DataRow addrow;
            DataRow addrow2;
            string strReturn = "";

            int intSerial;

            try
            {
                if (dsData.Tables.Contains("SPCData"))
                {
                    dsData.Tables.Remove("SPCData");
                }

                dsData.Tables.Add("SPCData");
                dsData.Tables["SPCData"].Columns.Add("Execute", System.Type.GetType("System.String"));
                dsData.Tables["SPCData"].Columns.Add("Result", System.Type.GetType("System.String"));
                dsData.Tables["SPCData"].Columns.Add("LotNo", System.Type.GetType("System.String"));
                dsData.Tables["SPCData"].Columns.Add("QCItemNo", System.Type.GetType("System.String"));
                dsData.Tables["SPCData"].Columns.Add("Employee", System.Type.GetType("System.String"));
                dsData.Tables["SPCData"].Columns.Add("OP", System.Type.GetType("System.String"));
                dsData.Tables["SPCData"].Columns.Add("OpNo", System.Type.GetType("System.String"));
                dsData.Tables["SPCData"].Columns.Add("ComponentNo", System.Type.GetType("System.String"));
                dsData.Tables["SPCData"].Columns.Add("LotSerial", System.Type.GetType("System.String"));
                dsData.Tables["SPCData"].Columns.Add("intResult", System.Type.GetType("System.String"));


                dsData.Tables["SPCData"].Columns.Add("SPCSerial_QMS", System.Type.GetType("System.String"));
                dsData.Tables["SPCData"].Columns.Add("Material", System.Type.GetType("System.String"));
                dsData.Tables["SPCData"].Columns.Add("MTLVendor", System.Type.GetType("System.String"));
                dsData.Tables["SPCData"].Columns.Add("DC", System.Type.GetType("System.String"));
                dsData.Tables["SPCData"].Columns.Add("PartNo", System.Type.GetType("System.String"));




                if (dsData.Tables.Contains("TestValue"))
                {
                    dsData.Tables.Clear();
                }
                else
                {
                    dsData.Tables.Add("TestValue");
                    dsData.Tables["TestValue"].Columns.Add("LotNo", System.Type.GetType("System.String"));
                    dsData.Tables["TestValue"].Columns.Add("QCItemNo", System.Type.GetType("System.String"));
                    dsData.Tables["TestValue"].Columns.Add("SerialNo", System.Type.GetType("System.Int32"));
                    dsData.Tables["TestValue"].Columns.Add("TestValue", System.Type.GetType("System.Double"));
                    dsData.Tables["TestValue"].Columns.Add("MEMO", System.Type.GetType("System.String"));
                    dsData.Tables["TestValue"].Columns.Add("ComponentNo", System.Type.GetType("System.String"));
                    dsData.Tables["TestValue"].Columns.Add("LotSerial", System.Type.GetType("System.String"));
                }

                addrow = dsData.Tables["SPCData"].NewRow();
                addrow["Execute"] = "True";
                addrow["LotNo"] = LotNo;
                addrow["QCItemNo"] = QCItemNo;
                addrow["Employee"] = Employee;
                addrow["OP"] = OPNo;
                addrow["OpNo"] = OPNo;
                addrow["LotSerial"] = 0;
                //addrow["PartNo"] = PartNo;
                addrow["SPCSerial_QMS"] = SPCSerial_QMS;
                addrow["Material"] = MaterialNo;
                addrow["MTLVendor"] = MTLVendor;
                addrow["DC"] = DC;
                addrow["PartNo"] = PartNo;
                dsData.Tables["SPCData"].Rows.Add(addrow);

                intSerial = 0;
                foreach (decimal TestValue in listTestValue)
                {


                    intSerial += 1;
                    addrow2 = dsData.Tables["TestValue"].NewRow();
                    addrow2["LotNo"] = LotNo;
                    addrow2["QCItemNo"] = QCItemNo;
                    addrow2["SerialNo"] = intSerial;
                    addrow2["TestValue"] = TestValue;
                    addrow2["LotSerial"] = 0;
                    addrow2["ComponentNo"] = "";

                    dsData.Tables["TestValue"].Rows.Add(addrow2);


                }

                string LotSerialTmp = string.Empty;
                string LogGroupSerialTmp = string.Empty;
                string LotStampTmp = string.Empty;
                string ProductNoTmp = string.Empty;
                string CustomerNoTmp = "N/A";

                var drDataTmp = dsData.Tables["SPCData"].Rows[0];
                var dvValue = default(DataView);
                dvValue = dsData.Tables["TestValue"].DefaultView;

                bool blnChkOnly = false;
                bool blnAttachChart = true;

                //tcSPC_IC.clsSPC objtc_SPC = new tcSPC_IC.clsSPC();
                //objtc_SPC.funAutoBRSPCContorl_Var(ref LotSerialTmp, ref LogGroupSerialTmp, ref LotStampTmp, ref ProductNoTmp, ref CustomerNoTmp, ref drDataTmp, dvValue, blnChkOnly: blnChkOnly, blnAttachChart: blnAttachChart);
                strReturn = funAutoBRSPCContorl_Var_M(ref LotSerialTmp, ref LogGroupSerialTmp, ref LotStampTmp, ref ProductNoTmp, ref CustomerNoTmp, ref drDataTmp, dvValue, blnChkOnly: blnChkOnly, blnAttachChart: blnAttachChart);

                if (!strReturn.ToLower().Contains("success"))
                {
                    throw new Exception(strReturn);
                }





            exitfun:
                ;
            }
            catch (Exception ex)
            {
                // 一行调用，拿到完整异常字符串
                string errorMsg = GetExceptionFullMessage(ex);
                throw new Exception(errorMsg);
            }
            finally
            {
                dsData?.Dispose();
                //CloseConnection(cnnTemp);
            }
            return funGetJLSPCData;

        }

        //计数 计数不需要开立异常单或者任何法则检查，只需要做数据收集（多计数品管项目）
        public static bool funGetJSSPCData(ref List<ArrayList> aryMessage, ref List<ArrayList> aryException, string SPCSerial_QMS, string MaterialNo, string MTLVendor, string Employee, string LotNo, string ProDuct, string EquipMent, string DC, List<QCItemDetail> listQCItemDetail)
        {
            bool funGetJSSPCData = default(bool);
            //bool r = false;
            //string strSQL = default(string);
            IDbConnection cnnTemp = null;
            //DbDataReader drTemp;

            AppSettings objSetting = new AppSettings();


            string strDataBaseType = objSetting.GetDataBaseType();
            string strConnectionString = objSetting.GetConnectionString(strDataBaseType);
            cnnTemp = CreateConnection(strConnectionString);

            string SpcSerial = string.Empty;
            Collection CollectionSQL = new Collection();
            Collection CollectionSQL_Rows = new Collection();
            DateTime nowTime = DateTime.Now;
            var oSQLContent = new Dictionary<uint, iMESCore.DataBase._SQLContent>();
            uint intSQLIndex = 1U;
            kcSPC.clsSPC objSPC = new kcSPC.clsSPC();

            try
            {

                foreach (var item in listQCItemDetail)
                {
                    //这里的回写序号，防止多人操作时序号被占用，导致数据录不进，且序号不要求连续
                    SpcSerial = objSPC.InitSPCSerial(ref cnnTemp);

                    if (string.IsNullOrEmpty(SpcSerial))
                    {
                        throw new Exception("[%SpcSerial生成失败%]");
                    }

                    //参考兆驰

                    //计量计数项目基础资料
                    funCreateSPCATTBASIS(ref aryMessage, ref aryException, ref CollectionSQL,
                        SpcSerial, item.QCITEMNO, LotNo, item.SAMPLEQTY.ToString(), item.GOODQTY.ToString(), item.DefectQty.ToString(),
                        "0", item.RESULT, SPCSerial_QMS, MaterialNo, MTLVendor,
                        Employee, ProDuct, EquipMent, DC, nowTime.ToString("yyyy/MM/dd H:mm:ss"));

                    //TBLSPCATTDETAILLOG 计数线上检验明细资料历史纪录
                    //不良原因填QMS，顾问固定的
                    funCreateSPCATTDETAIL(ref aryMessage, ref aryException, ref CollectionSQL,
                        SpcSerial, item.QCITEMNO, "QMS", item.DefectQty.ToString(), nowTime.ToString("yyyy/MM/dd H:mm:ss"));

                    //TBLSPCATTDETAILLOGSECTION  计数线上检验区域明细资料历史纪录档
                    //funCreateSPCATTSECTION(ref aryMessage, ref aryException, ref CollectionSQL, 
                    //    SpcSerial,"", "QMS","2", item.DefectQty.ToString());

                }



                //执行数据库
                if (CollectionSQL.Count > 0)
                {
                    for (int k = 1, loopTo10 = CollectionSQL.Count; k <= loopTo10; k++)
                        CollectionSQL_Rows.Add(-1);
                    funAddSQLContent(oSQLContent, ref intSQLIndex, CollectionSQL, CollectionSQL_Rows);
                    ExecuteSQLNoneQuery_ArrayBinding(strDataBaseType, strConnectionString, oSQLContent);
                }





            exitfun:
                ;
            }
            catch (Exception ex)
            {
                // 一行调用，拿到完整异常字符串
                string errorMsg = GetExceptionFullMessage(ex);
                throw new Exception(errorMsg);
            }
            finally
            {
                if (objSPC != null)
                {
                    objSPC.Dispose();
                }

                CloseConnection(cnnTemp);
            }
            return funGetJSSPCData;

        }


        // 2020/11/25 Grace 增加傳入前端畫面輸入的要因, 若有傳, 直接使用
        public static string funAutoBRSPCContorl_Var_M(ref string LotSerial, ref string LogGroupSerial, ref string LotStamp, ref string ProductNo, ref string CustomerNo,
            ref DataRow drData, DataView dvValue = null,
            string MClassNo = defString, bool blnChkOnly = true, bool blnAttachChart = false,
            Collection CollectionSQL = null,
            DataTable dtQCItemFactorAll = null, string UserNo = defString, string ShiftNo = defString, bool BrControl = false, bool EAPControl = false, bool blnReleaseLog = true, bool blnHoldLot = true)
        {
            string funAutoBRSPCContorl_VarRet = default(string);

            // 此 Function 將自動判別檢測數據_計量
            // 傳入值: drData (一次傳入一組檢驗資料,內有LotNo,OpNo,QCItemNo,PratNo,Employee）TestValue 等 
            // 傳回值funAutoBRSPCContorl_Var: success(成功), QCItemNo(找不到品管項目編號), LotNo(找不到批號)... 
            // 傳回值: drData(檢測結果) 

            IDbConnection cnnTemp = null;
            // 'Dim cmmTemp As OleDb.OleDbCommand
            DbDataReader drTemp = null;
            string strSQL;

            var kcQC = new kcQC.clsQCBasis();
            var kcWIP = new kcWIP.clsLot();
            var kcSPC = new kcSPC.clsSPC();
            var udSPC = new udSPC.clsSPC();
            var udWIP = new udWIP.clsWIP();

            // Table
            DataSet dsTemp;
            var dtQCItemBasis = default(DataTable);
            var dtQCItemFactor = default(DataTable);
            DataTable dtLotData;
            var dtWIPFactor = default(DataTable);
            DataTable dtQCItem;     // 主要是儲存Check後的結果
            DataRow drAdd;
            DataRow[] drSel;

            // 傳入的資料
            var LotNo = default(string);
            var OpNo = default(string);
            var QCItemNo = default(string);
            var QCItemName = default(string);
            var Employee = default(string);

            int i, j;
            var ERFAction = default(string);
            var ERFMsg = default(string);
            var strSPCSerial = new string[2];


            int intResult;
            int intERFSource;
            string LinkName;
            // Dim LotSerial As String
            // Dim LogGroupSerial As String
            string ExecuteSPCSerial;
            string PopulationQty = "";
            // Dim ProductNo As String
            // Dim CustomerNo As String = "N/A" '開異常單時 customerNo不能 Null
            // Dim LotStamp As String
            bool MailNotice = false;
            string[] aryAdditionalSQL;

            string[] aryUpdateSQLReplace; // Added by Neil 2008/05/19
            bool blnCheckOK = true;  // Added by Neil 2008/05/19 作為各項檢查的指標，任何一項檢查不過則為False
            var strCheckNotOKMessage = default(string);  // Added by Neil 2008/05/19 
            DataSet dsChartData = null; // Added by Neil 2008/05/20 存放管制圖資料

            AppSettings objSetting = new AppSettings();
            string strDataBaseType = objSetting.GetDataBaseType();
            string strConnectionString = objSetting.GetConnectionString(strDataBaseType);


            tcSPC_IC.clsSPC objtc_SPC = new tcSPC_IC.clsSPC();

            try
            {

                funAutoBRSPCContorl_VarRet = "error";   // Initial

                // =====  整理傳入的資料 =========
                if (drData.Table.Columns.Contains("LotNo"))
                {
                    LotNo = drData["LotNo"] + "".ToString();
                }
                if (drData.Table.Columns.Contains("QCItemNo"))
                {
                    QCItemNo = (drData["QCItemNo"] + "").ToString().ToUpper();     // 請注意一定要轉大寫
                }
                if (drData.Table.Columns.Contains("OpNo"))
                {
                    OpNo = drData["OpNo"] + "".ToString();
                }
                if (drData.Table.Columns.Contains("Employee"))
                {
                    Employee = drData["Employee"] + "".ToString();
                }

                DataTable dtTestValue;

                if (dvValue != null)
                {
                    dtTestValue = dvValue.Table.Clone();
                    var loopTo = dvValue.Count - 1;
                    for (i = 0; i <= loopTo; i++)
                        dtTestValue.ImportRow(dvValue[i].Row);
                }
                else
                {
                    dtTestValue = new DataTable("TestValue");
                    dtTestValue.Columns.Add("QCItemNo", Type.GetType("System.String"));
                    dtTestValue.Columns.Add("SerialNo", Type.GetType("System.Int32"));
                    dtTestValue.Columns.Add("TestValue", Type.GetType("System.Double"));
                    dtTestValue.Columns.Add("MEMO", Type.GetType("System.String"));
                    dtTestValue.Columns.Add("ComponentNo", Type.GetType("System.String"));

                    for (j = 0; j <= 19; j++)
                    {
                        if (drData.Table.Columns.Contains("S" + Strings.Format(j + 1, "00")) && !(drData["S" + Strings.Format(j + 1, "00")] is DBNull) && !string.IsNullOrEmpty(drData["S" + Strings.Format(j + 1, "00")].ToString()))
                        {
                            drAdd = dtTestValue.NewRow();
                            drAdd["QCItemNo"] = QCItemNo;
                            drAdd["SerialNo"] = j + 1;
                            drAdd["TestValue"] = drData["S" + Strings.Format(j + 1, "00")];
                            dtTestValue.Rows.Add(drAdd);
                        }
                        else
                        {
                            break;
                        }
                    }

                }

                if (dtTestValue.Rows.Count == 0)
                {
                    strCheckNotOKMessage = "No Data";
                    // Exit Function
                    blnCheckOK = false; // Added by Neil 2008/05/19
                }

                // =====  檢查資料是否正確 =========
                // 1.取出品管項目
                if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作 
                {

                    dtQCItemBasis = kcQC.ShowQCItemForVariables(QCItemNo, IssueState: 2).Tables[0];

                    if (dtQCItemBasis == null || dtQCItemBasis.Rows.Count == 0)
                    {
                        strCheckNotOKMessage = "QCItemNo Error";
                        // Exit Function
                        blnCheckOK = false; // Added by Neil 2008/05/19
                    }

                }


                // 2 取出Lot資料
                if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作
                {

                    dtLotData = kcWIP.ShowLotBasisJoinState(LotNo).Tables[0];
                    if (dtLotData == null || dtLotData.Rows.Count == 0)
                    {
                        if (Convert.ToBoolean(Operators.ConditionalCompareObjectEqual(dtQCItemBasis.Rows[0]["LotIsValid"], 1, false)))     // 必須檢查批號有效,但找不到批號資料
                        {
                            strCheckNotOKMessage = "LotData Error";
                            // Exit Function
                            blnCheckOK = false; // Added by Neil 2008/05/19
                        }
                    }
                    else
                    {
                        PopulationQty = dtLotData.Rows[0]["CurQty"].ToString();
                        LotSerial = dtLotData.Rows[0]["LotSerial"] + "".ToString();
                        LogGroupSerial = dtLotData.Rows[0]["LogGroupSerial"] + "".ToString();
                        LotStamp = dtLotData.Rows[0]["LotStamp"] + "".ToString();
                        ProductNo = dtLotData.Rows[0]["ProductNo"] + "".ToString();
                        CustomerNo = dtLotData.Rows[0]["CustomerNo"] + "".ToString();
                        OpNo = dtLotData.Rows[0]["OpNo"] + "".ToString();
                    }

                }


                // =====  整理要因資料 =========
                // 3.1 取出 QCItemFactor
                if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作
                {
                    if (!(dtQCItemFactorAll == null))
                    {
                        dtQCItemFactor = dtQCItemFactorAll;
                    }
                    else
                    {
                        dtQCItemFactor = kcQC.ShowQCItemFactor(QCItemNo).Tables[0];
                    }

                    dtQCItemFactor = kcQC.ShowQCItemFactor(QCItemNo).Tables[0];
                    if (dtQCItemFactor == null || dtQCItemFactor.Rows.Count == 0)
                    {
                        strCheckNotOKMessage = "QCItemFactor Error";
                        // Exit Function
                        blnCheckOK = false; // Added by Neil 2008/05/19
                    }

                }


                // 3.2 取得檢驗批的要因資料
                if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作
                {
                    dtWIPFactor = udSPC.LoadLotFactorValue(LotNo).Tables[0];
                    foreach (DataColumn dcFactor in drData.Table.Columns)
                    {
                        if (dcFactor.ColumnName.ToUpper() == "OPNO")
                        {
                            drSel = dtWIPFactor.Select("ParameterNo = 'OP'");

                            //0144256: [607]SC107匯入報錯 19120
                            if (drSel != null && drSel.Length > 0)
                            {
                                drSel[0]["Value"] = drData[dcFactor];
                            }

                        }

                        drSel = dtWIPFactor.Select("ParameterNo = '" + dcFactor.ColumnName + "'");
                        if (drSel.Length > 0)
                        {
                            drSel[0]["Value"] = drData[dcFactor];
                        }
                    }
                }


                // 3.3.將取得的要因值填入品管項目要因,取不到值的則填入N/A
                if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作 
                {
                    dtQCItemFactor.Columns.Add("FactorValue", Type.GetType("System.String"));
                    var loopTo1 = dtQCItemFactor.Rows.Count - 1;
                    for (i = 0; i <= loopTo1; i++)
                    {
                        drSel = dtWIPFactor.Select("ParameterNo = '" + dtQCItemFactor.Rows[i]["FactorNo"] + "'".ToString());
                        if (drSel.Length > 0)
                        {
                            dtQCItemFactor.Rows[i]["FactorValue"] = drSel[0]["Value"];
                        }
                        else if (drData.Table.Columns.Contains(Convert.ToString(dtQCItemFactor.Rows[i]["FactorNo"])) && !(drData[dtQCItemFactor.Rows[i]["FactorNo"].ToString()] is DBNull) && Convert.ToBoolean(Operators.ConditionalCompareObjectNotEqual(drData[dtQCItemFactor.Rows[i]["FactorNo"].ToString()], "", false)))
                        {
                            dtQCItemFactor.Rows[i]["FactorValue"] = drData[dtQCItemFactor.Rows[i]["FactorNo"].ToString()];                  // 從傳入的drData 中取得要因
                        }
                        else
                        {
                            dtQCItemFactor.Rows[i]["FactorValue"] = "N/A";
                        }
                    }

                    //0190824: 【澳洋】SPC接口 Edison 251023 補上SC101取得自定義要因值的做法
                    var dtTempSPCFactorData = new DataTable();
                    var dtValueList = new DataTable("ConditionValueList");
                    dtValueList.Columns.Add("InspLotNo", typeof(string));
                    var drValueList = dtValueList.NewRow();
                    drValueList["InspLotNo"] = LotNo;
                    dtValueList.Rows.Add(drValueList);
                    foreach (DataRow drw in dtQCItemFactor.Rows)
                    {
                        dtValueList.Columns.Add(Convert.ToString(drw["FactorNo"]), typeof(string));
                        dtValueList.Rows[0][drw["FactorNo"].ToString()] = drw["FactorValue"].ToString();
                    }

                    using (var objSys = new kcSYS.clsSYSMesParameter())
                    {
                        // 以SQLCMDMap方式取要因值
                        IDbConnection argcnnTemp = null;
                        objSys.LoadMesParameterValueBySQLCMD(ref dtValueList, cnnTemp: argcnnTemp);
                    }

                    if (dtValueList.Rows.Count > 0)
                    {
                        foreach (DataRow drw in dtQCItemFactor.Rows)
                        {
                            // 填入要因預設值
                            drw["FactorValue"] = dtValueList.Rows[0][drw["FactorNo"].ToString()].ToString().Trim();

                            if (dtQCItemFactorAll == null)
                                continue;
                            var drSel2 = dtQCItemFactorAll.Select("ParameterNo = '" + drw["FactorNo"].ToString() + "'");
                            // 填入要因預設值
                            if (drSel2.Length > 0)
                            {
                                foreach (DataRow dr in drSel2)
                                {
                                    dr["FactorValue"] = dtValueList.Rows[0][drw["FactorNo"].ToString()].ToString().Trim();
                                }
                            }

                        }
                    }

                    // 取得暫存要因資料
                    strSQL = " Select * From tblWIPTemp_SPCFactorData Where LotNo = '" + LotNo + "'  And OPNO = '" + OpNo + "' And LotSerial = '" + LotSerial + "'";

                    dtTempSPCFactorData = new DataTable("WIPTemp_SPCFactorData");
                    using (IDbConnection cnnQ = CreateConnection(strConnectionString))
                    {
                        ExecuteSQLQuery_Adapter(SQLCommandTuning(strDataBaseType, ref strSQL), dtTempSPCFactorData, connection: cnnQ);

                        if (dtTempSPCFactorData.Rows.Count > 0)
                        {
                            foreach (DataRow drw in dtQCItemFactor.Rows)
                            {
                                var drtTempSPCFactorData = dtTempSPCFactorData.Select(
                                    "FactorNo = '" + drw["FactorNo"].ToString() +
                                    "' And QCItemNo ='" + drw["QCItemNo"].ToString() + "'");

                                // 填入要因預設值
                                if (drtTempSPCFactorData.Length > 0)
                                {
                                    drw["FactorValue"] = drtTempSPCFactorData[0]["FactorValue"];

                                    if (dtQCItemFactorAll == null)
                                        continue;

                                    var drQCItemFactorAll = dtQCItemFactorAll.Select(
                                        "ParameterNo = '" + drw["FactorNo"].ToString() +
                                        "' And QCItemNo ='" + drw["QCItemNo"].ToString() + "'");

                                    // 填入要因預設值
                                    if (drQCItemFactorAll.Length > 0)
                                    {
                                        foreach (DataRow dr in drQCItemFactorAll)
                                        {
                                            dr["FactorValue"] = drtTempSPCFactorData[0]["FactorValue"];
                                        }
                                    }
                                }
                            }
                        }
                    }
                }

                // =====  檢查此筆檢測資料是否已經存過 =========
                var LatestLotsMappingCreateDateTmp = defDateTime;
                if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作
                {
                    if (blnChkOnly == true)
                    {
                        if (kcSPC.ShowVarFactorLog(ref LatestLotsMappingCreateDateTmp, QCItemNo, LotNo).Tables["SPCVarFactorLog"].Rows.Count > 0)
                        {
                            strCheckNotOKMessage = "Already Exist";
                            // Exit Function
                            blnCheckOK = false; // Added by Neil 2008/05/19
                        }
                    }
                }

                // =====  Check 規格與管制法則 =========
                var FactorSerial = default(string);
                string strChkTemp;

                var dtViolation = new DataTable("Violation");    // 執行 funChkQCChartValue 後回傳結果

                if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作
                {

                    dtViolation.Columns.Add("QCItemNo", Type.GetType("System.String"));
                    dtViolation.Columns.Add("SPCSerial", Type.GetType("System.String"));
                    dtViolation.Columns.Add("ComponentNo", Type.GetType("System.String"));
                    dtViolation.Columns.Add("SPCID", Type.GetType("System.String"));
                    dtViolation.Columns.Add("SerialNo", Type.GetType("System.String"));            // Raw ID , 非Raw=-1
                    dtViolation.Columns.Add("ViolationID", Type.GetType("System.String"));   // 違反類型：WE001, WE002, ..,WE008

                    if (blnAttachChart == true) // 若blnAttachChart = True 則建立dsXlsChartData基本架構，待經過funChkQCChartValue後取得資料，後續畫管制圖用
                    {

                        dsChartData = new DataSet();
                        DataRow tmprow;

                        dsChartData.Tables.Add("ChartInfo");
                        dsChartData.Tables["ChartInfo"].Columns.Add("SpecLineType");
                        dsChartData.Tables["ChartInfo"].Columns.Add("SampleSize");
                        dsChartData.Tables["ChartInfo"].Columns.Add("TUCL");
                        dsChartData.Tables["ChartInfo"].Columns.Add("TLCL");
                        dsChartData.Tables["ChartInfo"].Columns.Add("TCL");
                        dsChartData.Tables["ChartInfo"].Columns.Add("TUS");
                        dsChartData.Tables["ChartInfo"].Columns.Add("TLS");
                        dsChartData.Tables["ChartInfo"].Columns.Add("XUCL");
                        dsChartData.Tables["ChartInfo"].Columns.Add("XLCL");
                        dsChartData.Tables["ChartInfo"].Columns.Add("XCL");
                        dsChartData.Tables["ChartInfo"].Columns.Add("XUS");
                        dsChartData.Tables["ChartInfo"].Columns.Add("XLS");
                        dsChartData.Tables["ChartInfo"].Columns.Add("GUCL");
                        dsChartData.Tables["ChartInfo"].Columns.Add("GLCL");
                        dsChartData.Tables["ChartInfo"].Columns.Add("GCL");
                        dsChartData.Tables["ChartInfo"].Columns.Add("GUS");
                        dsChartData.Tables["ChartInfo"].Columns.Add("GLS");
                        dsChartData.Tables["ChartInfo"].Rows.Add(dsChartData.Tables["ChartInfo"].NewRow());

                        // dsChartData.Tables.Add("TrendData")
                        // dsChartData.Tables.Add("XbarData")
                        // dsChartData.Tables.Add("GraphData")

                    }

                    string argEnterpriseNo = "";
                    strChkTemp = kcSPC.funChkQCChartValue(LotNo, QCItemNo, Employee,
                        dtQCItemFactor, dtTestValue, ref FactorSerial, ref dtViolation,
                        ref ERFAction, ref ERFMsg, dsChartData,
                        EnterpriseNo: argEnterpriseNo);
                    if (Convert.ToBoolean(Strings.InStr(strChkTemp, "fail")))    // 無管制圖或檢查過程錯誤
                    {
                        strCheckNotOKMessage = "ChkQCChartValue" + strChkTemp;
                        // Exit Function
                        blnCheckOK = false; // Added by Neil 2008/05/19
                    }
                }


                // 若純檢查,只要做到這裡為止   'Added by Yun 2007/11/2
                if (blnChkOnly == true)
                {
                    funAutoBRSPCContorl_VarRet = strCheckNotOKMessage; // last modify by Neil 2008/06/03
                    return funAutoBRSPCContorl_VarRet;
                }

                // Added by Neil 2008/05/19
                if (blnCheckOK == false)
                {
                    drData["Execute"] = strCheckNotOKMessage;   // Add Yun 2017/11/28   將Check 結果傳出

                    // 檢查沒過回寫狀態
                    if (drData.Table.Columns.Contains("UpdateSQL") && !(drData["UpdateSQL"] is DBNull))
                    {
                        if (drData.Table.Columns.Contains("UpdateSQLReplace") && !(drData["UpdateSQLReplace"] is DBNull))
                        {
                            var CollectSQL = new Collection();
                            // drData("Execute") = strCheckNotOKMessage    'Marked Yun 2017/11/28 移到上面
                            objtc_SPC.CollectUpdateSQL(ref drData, ref CollectSQL);
                            if (CollectSQL.Count != 0)
                            {
                                kcSPC.ExecuteSQL(CollectSQL);
                            }
                        }
                    }

                    funAutoBRSPCContorl_VarRet = strCheckNotOKMessage;
                    return funAutoBRSPCContorl_VarRet;

                }


                // 備用
                DataTable dtComponent;
                dtComponent = new DataTable("Component");
                dtComponent.Columns.Add("SPCSerial", Type.GetType("System.String"));
                dtComponent.Columns.Add("QCItemNo", Type.GetType("System.String"));
                dtComponent.Columns.Add("ComponentNo", Type.GetType("System.String"));
                dtComponent.Columns.Add("CompResult", Type.GetType("System.String"));


                // =====  整理生產批(各品管項目綜合)結果,並存入資料庫 =========
                if (Strings.InStr(ERFAction, "Hold") > 0)
                {
                    LinkName = "Hold";
                    intResult = 1;
                }
                else if (Strings.InStr(ERFAction, "Alarm") > 0)
                {
                    LinkName = "Alarm";
                    intResult = 4;
                }
                else if (Strings.InStr(ERFAction, "Alert") > 0)
                {
                    LinkName = "Alert";
                    intResult = 4;
                }
                else if (Strings.InStr(ERFAction, "Wait") > 0)
                {
                    LinkName = "Wait";
                    intResult = 2;
                }
                else if (Strings.InStr(ERFAction, "Warning") > 0)
                {
                    LinkName = "Warning";
                    intResult = 3;
                }
                else
                {
                    LinkName = "Pass";
                    intResult = 0;
                }
                drData["Result"] = LinkName;   // 回傳出去給人看的所以用字串
                drData["intResult"] = intResult;

                // Add QCItem Test Value
                dtQCItem = new DataTable("QCItem");
                dtQCItem.Columns.Add("QCItemNo", Type.GetType("System.String"));
                dtQCItem.Columns.Add("FactorSerial", Type.GetType("System.String"));
                dtQCItem.Columns.Add("RetestLotNo", Type.GetType("System.String"));
                dtQCItem.Columns.Add("ERFMsg", Type.GetType("System.String"));
                dtQCItem.Columns.Add("Result", Type.GetType("System.String"));     // 紀錄結果 0,1,2,....
                dtQCItem.Columns.Add("SPCSerial", Type.GetType("System.String"));  // 在UpdVar_TestValue中會將SPCSerial 填入  

                drAdd = dtQCItem.NewRow();
                drAdd["QCItemNo"] = QCItemNo;
                drAdd["FactorSerial"] = FactorSerial;
                drAdd["RetestLotNo"] = "";
                drAdd["ERFMsg"] = ERFMsg;
                drAdd["Result"] = intResult.ToString();
                dtQCItem.Rows.Add(drAdd);

                //EAP过站组件信息
                if (EAPControl)
                {
                    DataView dataView = dtTestValue.DefaultView;
                    DataTable QcItemNoDistinct = dataView.ToTable(true, "ComponentNo", "QCItemNo");
                    DataRow[] drSelCom;
                    // 將資料存入暫存Table
                    for (int q = 0; q < QcItemNoDistinct.Rows.Count; q++)
                    {
                        var drcomData = dtComponent.NewRow();
                        drcomData["QCItemNo"] = QCItemNo;
                        if (QcItemNoDistinct.Rows[q]["QCItemNo"].ToString() == QCItemNo)
                        {
                            drcomData["ComponentNo"] = QcItemNoDistinct.Rows[q]["ComponentNo"].ToString();
                        }

                        if (dtViolation.Rows.Count > 0)
                        {
                            drSelCom = dtViolation.Select("ComponentNo='" + QcItemNoDistinct.Rows[q]["ComponentNo"].ToString() + "'");
                            if (drSelCom.Length > 0)
                            {
                                drcomData["CompResult"] = "1";
                            }
                            else
                            {
                                drcomData["CompResult"] = "0";
                            }
                        }
                        else
                        {
                            drcomData["CompResult"] = "0";
                        }
                        dtComponent.Rows.Add(drcomData);
                    }

                }

                var colSQL = new Collection();
                string strGSPCSerialTmp = defString;
                kcSPC.UpdVar_TestValue(ref strGSPCSerialTmp, dtQCItemFactor, dtQCItem, dtTestValue, LotNo, LotSerial, Employee, dtComponent: dtComponent, colSQL: colSQL, LogGroupSerial: LogGroupSerial, OPNo: OpNo);

                // 取得 SPCSerial
                strSPCSerial[0] = dtQCItem.Rows[0]["SPCSerial"].ToString();
                if (drData.Table.Columns.Contains("SPCSerial"))
                {
                    drData["SPCSerial"] = dtQCItem.Rows[0]["SPCSerial"];
                }

                // 2020/01/20 ,Yenru Add ERFMSG
                if (drData.Table.Columns.Contains("ERFMsg"))
                {
                    drData["ERFMsg"] = dtQCItem.Rows[0]["ERFMsg"];
                }
                //回写法则编号
                string ruleno = "";
                if (drData.Table.Columns.Contains("RuleNo"))
                {
                    if (dtViolation.Rows.Count > 0)
                    {
                        for (int r = 0; r < dtViolation.Rows.Count; r++)
                        {
                            if (dtViolation.Rows[r]["QCItemNo"].ToString() == QCItemNo && dtViolation.Rows[r]["ComponentNo"].ToString() == drData["ComponentNo"].ToString())
                            {
                                ruleno = dtViolation.Rows[r]["ViolationID"].ToString();
                            }
                        }
                    }
                    drData["RuleNo"] = ruleno;
                }

                // Add SPC待檢清單 
                kcSPC.UpdSpcLotQCList(LotNo, OpNo, 1, dtQCItem, strSPCSerial, colSQL);
                //争对EAP处理
                if (EAPControl)
                {
                    //对生产批的处理
                    if (BrControl || intResult != 0)
                    {
                        // WIP的BR_SPCControl
                        DataTable dtSamplePara = new DataTable("SamplePara");
                        dtSamplePara.Columns.Add("ParameterNo", Type.GetType("System.String"));
                        dtSamplePara.Columns.Add("ParameterValue", Type.GetType("System.String"));
                        kcWIP.BR_SPCControl(long.Parse(LotStamp), LotNo, ref LinkName, QCItemNo, UserNo, ShiftNo, colSQL, dtSamplePara, strMsg: ERFMsg, blnFirstLotOf2ndInsp: false, blnReleaseLog: blnReleaseLog);
                        funAutoBRSPCContorl_VarRet = LinkName;
                        // 紀錄Cont_QCItem
                        var loopTo13 = dtQCItem.Rows.Count - 1;
                        string[] comp_SPCSerial;
                        string[] comp_ERFMsg;
                        for (i = 0; i <= loopTo13; i++)
                        {
                            comp_SPCSerial = strSPCSerial[i].Split(';');   // 當計算方式為 ByComponent時會用;隔開
                            var loopTo14 = comp_SPCSerial.Length - 1;
                            for (int c = 0; c <= loopTo14; c++)
                                kcSPC.AddWIPQCItemContent(LotNo, LotSerial, OpNo, QCItemNo, Convert.ToString(dtQCItem.Rows[i]["QCItemNo"]), 1, comp_SPCSerial[c], UserNo, LogGroupSerial, colSQL);
                        }
                    }
                }

                // Add Limit
                dsTemp = kcSPC.ShowQCChartDetail(FactorSerial);
                if (!(dsTemp.Tables["QCChartDetail"] == null) && dsTemp.Tables["QCChartDetail"].Rows.Count > 0)
                {
                    dsTemp.Tables["QCChartDetail"].Columns.Add("SPCSerial", Type.GetType("System.String"));
                    var loopTo2 = dsTemp.Tables["QCChartDetail"].Rows.Count - 1;
                    for (i = 0; i <= loopTo2; i++)
                        dsTemp.Tables["QCChartDetail"].Rows[i]["SPCSerial"] = dtQCItem.Rows[0]["SPCSerial"];
                    kcSPC.AddSPCVarLimitContent(dsTemp.Tables["QCChartDetail"], colSQL);
                }

                // Add Violation
                if (!(dtViolation == null) && dtViolation.Rows.Count > 0)
                {
                    var loopTo3 = dtViolation.Rows.Count - 1;
                    if (EAPControl && Convert.ToInt32(dtQCItemBasis.Rows[0]["CalType"]) == 1)
                    {
                        DataTable dtComSpcserial = dtComponent;
                        for (i = 0; i <= loopTo3; i++)
                        {
                            for (int s = 0; s < dtComSpcserial.Rows.Count; s++)
                            {
                                if (dtViolation.Rows[i]["QCItemNo"].ToString() == dtComSpcserial.Rows[s]["QCItemNo"].ToString() && dtViolation.Rows[i]["ComponentNo"].ToString() == dtComSpcserial.Rows[s]["ComponentNo"].ToString())
                                {
                                    dtViolation.Rows[i]["SPCSerial"] = dtComSpcserial.Rows[s]["SPCSerial"];
                                }
                            }
                        }
                    }
                    else
                    {
                        for (i = 0; i <= loopTo3; i++)
                            dtViolation.Rows[i]["SPCSerial"] = dtQCItem.Rows[0]["SPCSerial"];
                    }
                    kcSPC.AddSPCViolationContent(dtViolation, colSQL);
                }

                //0173294: [6.0.7]SDD240326_01-04 / Execute_InLineSPC_Judge 將異常單訊息上提到新增ERFCrerateData之前 Edison 241126
                var strMsg = default(string);
                // ↓===================================================================================================================
                if (Convert.ToBoolean(Operators.ConditionalCompareObjectEqual(dtQCItem.Rows[0]["Result"], 4, false)) || Convert.ToBoolean(Operators.ConditionalCompareObjectEqual(dtQCItem.Rows[0]["Result"], 1, false)))
                {
                    strMsg = "Auto SPC " + "[" + QCItemNo + "]" + Strings.Replace(dtQCItem.Rows[0]["ERFMsg"].ToString(), "$", "");
                }

                string strSPCSerialPlitByDot = string.Join(",",
                                dtViolation.AsEnumerable()
                                           .Select(row => row.Field<string>("SPCSerial"))
                                           .Distinct());
                if (!blnHoldLot && strMsg != default(string))
                    kcWIP.AddERFCreateData(LotNo, OpNo, "1", 0, strMsg, PopulationQty, UserNo, strSPCSerialPlitByDot, Conversions.ToInteger(dtQCItemBasis.Rows[0]["LotIsValid"]), 1, LotSerial: LotSerial, colSQL: colSQL);

                // 額外需執行的指令
                if (drData.Table.Columns.Contains("AdditionalSQL") && !(drData["AdditionalSQL"] is DBNull))
                {
                    aryAdditionalSQL = Strings.Split(drData["AdditionalSQL"].ToString(), "|");
                    var loopTo4 = aryAdditionalSQL.Length - 1;
                    for (i = 0; i <= loopTo4; i++)
                        colSQL.Add(aryAdditionalSQL[i]);
                }

                // 額外需執行的指令 Added by Neil 2008/05/19
                if (drData.Table.Columns.Contains("UpdateSQL") && !(drData["UpdateSQL"] is DBNull))
                {
                    objtc_SPC.CollectUpdateSQL(ref drData, ref colSQL);
                }


                // 呼叫執行SQL指令
                if (CollectionSQL == null)
                {
                    // Call ExecuteSQLNoneQuery_UPD(DataBaseType, strConnectionString, CollectionSQL)

                    // 執行彙總的 SQL 指令
                    kcSPC.ExecuteSQL(colSQL);
                    drData["Execute"] = "success";   // 表示SPC資料已儲存成功
                }
                else
                {
                    // 2020/11/23 Grace 不執行指令,將指令傳出
                    var loopTo5 = colSQL.Count;
                    for (i = 1; i <= loopTo5; i++)
                        CollectionSQL.Add(colSQL[i]);
                }



                // ↑===================================================================================================================


                // 開立異常單
                string ERFNo;

                intERFSource = 1;

                if (Convert.ToBoolean(Operators.ConditionalCompareObjectEqual(dtQCItem.Rows[0]["Result"], 1, false)) && blnAttachChart == true)
                {

                    if (MClassNo == defString)
                    {

                        // 取得MClassNo (取不到則不開 ）===============================================
                        cnnTemp = CreateConnection(strConnectionString);
                        strSQL = "Select MClassNo, QCItemName From tblQCItemForVariables Where QCItemNo = '" + QCItemNo + "'";

                        // 'cmmTemp = New OleDb.OleDbCommand(strSQL, cnnTemp) 'Select data
                        drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                        if (drTemp.Read())
                        {
                            MClassNo = drTemp["MClassNo"] + "".ToString();
                            QCItemName = drTemp["QCItemName"] + "".ToString();
                            if (!string.IsNullOrEmpty(MClassNo))                       // Added by Yun 2007/12/19
                            {
                                ERFNo = udWIP.InitERFNo(InitNo: "SPC");
                                kcWIP.UpdERF_Create(1, -1, LotNo, intERFSource, ERFNo, 0, MClassNo, strMsg, Employee, CreateERF: true, OPNo: OpNo, CustomerNo: CustomerNo, ProductNo: ProductNo, SPCSerial: strSPCSerial[0], QCItemType: 1, MailNotice: false);
                                // 建立異常單附件並發送 Added by Neil 2008/05/21============================================================================
                                // Dim dtHoldReason As New DataTable("HoldReason")
                                var AttachFileContent = default(byte[]); // Added by Neil 2008/05/21 mail附件
                                if (objtc_SPC.funCreateAttachFile(ERFNo + ".pdf", QCItemNo, LotNo, ERFNo, ProductNo, CustomerNo, OpNo, MClassNo, Strings.Replace(Convert.ToString(dtQCItem.Rows[0]["ERFMsg"]), "$", ""), Employee, dsChartData, drData, dtTestValue, dtQCItemFactor, ref AttachFileContent, true, QCItemName) == true)
                                {
                                    kcSPC.SendAlertMail(OpNo, MClassNo, QCItemNo, "1", "[ERF Create Notice]: ERFNo:" + ERFNo + " LotNo:" + LotNo, "ERFNo:" + ERFNo, ERFNo + ".pdf", AttachFileContent);
                                }
                            }
                        }
                        drTemp.Close();
                        CloseConnection(cnnTemp);

                    }

                }

                // =====  發Mail =========
                // ↓===================================================================================================================
                if (Convert.ToBoolean(Operators.ConditionalCompareObjectEqual(dtQCItem.Rows[0]["Result"], 4, false)))      // Alert 發 mail
                {
                    // Last Modify:Neil 2008/05/22
                    var AttachAlertFileContent = default(byte[]); // Added by Neil 2008/05/21 mail附件
                    if (objtc_SPC.funCreateAttachFile("Alert.pdf", QCItemNo, LotNo, "", ProductNo, CustomerNo, OpNo, MClassNo, Strings.Replace(Convert.ToString(dtQCItem.Rows[0]["ERFMsg"]), "$", ""), Employee, dsChartData, drData, dtTestValue, dtQCItemFactor, ref AttachAlertFileContent, false, QCItemName) == true)
                    {
                        kcSPC.SendAlertMail(OpNo, MClassNo, QCItemNo, "1", "[Alert Mail]: QCItemNo:" + QCItemNo, "QCItemNo:" + QCItemNo, "Alert.pdf", AttachAlertFileContent);
                    }
                }
                // ↑===================================================================================================================
                if (EAPControl)
                {
                    if (!BrControl)
                    {
                        if (intResult == 0)
                        {
                            funAutoBRSPCContorl_VarRet = "success";   // 表示 Function 執行成功
                        }
                    }
                }
                else
                {
                    funAutoBRSPCContorl_VarRet = "success";   // 表示 Function 執行成功
                }

            }


            catch (Exception e1)
            {
                funAutoBRSPCContorl_VarRet = "fail";
                // 發生錯誤時丟回本身的Function Name及系統錯誤訊息
                throw;
            } // New Exception("kcSPC.funBRSPConlinetrans: " & e1.Message)

            finally
            {
                if (kcWIP != null)
                    kcWIP = null;
                if (kcQC != null)
                    kcQC = null;
                if (kcSPC != null)
                    kcSPC = null;
                if (objtc_SPC != null)
                    objtc_SPC = null;

                if (cnnTemp != null)
                    CloseConnection(cnnTemp);
            }

            return funAutoBRSPCContorl_VarRet;


        }




        /// <summary>
        /// 计数标准-标版备用
        /// </summary>
        /// <param name="LotSerial"></param>
        /// <param name="LogGroupSerial"></param>
        /// <param name="LotStamp"></param>
        /// <param name="ProductNo"></param>
        /// <param name="CustomerNo"></param>
        /// <param name="drData"></param>
        /// <param name="dvError"></param>
        /// <param name="MClassNo"></param>
        /// <param name="blnChkOnly"></param>
        /// <param name="blnCreateERF"></param>
        /// <param name="blnChkAttRule"></param>
        /// <param name="CollectionSQL"></param>
        /// <param name="AQLRejectNum"></param>
        /// <param name="LotRuleNo"></param>
        /// <param name="dtQCItemFactorAll"></param>
        /// <param name="UserNo"></param>
        /// <param name="ShiftNo"></param>
        /// <param name="BrControl"></param>
        /// <param name="EAPControl"></param>
        /// <param name="dtAttDetailLogComponent"></param>
        /// <param name="blnReleaseLog"></param>
        /// <param name="blnHoldLot"></param>
        /// <returns></returns>
        public string funAutoBRSPCContorl_Att(ref string LotSerial, ref string LogGroupSerial, ref string LotStamp, ref string ProductNo, ref string CustomerNo,
          ref DataRow drData, DataView dvError = null,
          string MClassNo = defString, bool blnChkOnly = true, bool blnCreateERF = true,
          bool blnChkAttRule = true, Collection CollectionSQL = null,
          long AQLRejectNum = defInteger, string LotRuleNo = defString,
          DataTable dtQCItemFactorAll = null, string UserNo = defString, string ShiftNo = defString, bool BrControl = false, bool EAPControl = false, DataTable dtAttDetailLogComponent = null, bool blnReleaseLog = true, bool blnHoldLot = true)
        {
            string funAutoBRSPCContorl_AttRet = default(string);


            // 此 Function 將自動判別檢測數據_計數
            // 傳入值: drData (一次傳入一組檢驗資料,內有LotNo,OpNo,QCItemNo,PratNo,Employee）等 
            // 傳入 : dtError (不良原因)
            // 傳回值funAutoBRSPCContorl_Att: success(成功), QCItemNo(找不到品管項目編號), LotNo(找不到批號)... 
            // 傳回值: drData(檢測結果) 

            IDbConnection cnnTemp = null;
            // 'Dim cmmTemp As OleDb.OleDbCommand
            DbDataReader drTemp = null;
            string strSQL;

            var kcQC = new kcQC.clsQCBasis();
            var kcWIP = new kcWIP.clsLot();
            var kcSPC = new kcSPC.clsSPC();
            var udSPC = new udSPC.clsSPC();
            var udWIP = new udWIP.clsWIP();

            // Table
            DataSet dsTemp;
            var dtQCItemBasis = default(DataTable);
            var dtQCItemFactor = default(DataTable);
            var dtFactorOnline = default(DataTable);
            DataTable dtLotData;
            var dtWIPFactor = default(DataTable);
            DataTable dtQCItem;     // 主要是儲存Check後的結果
            var dtError = default(DataTable);
            DataRow drAdd;
            DataRow[] drSel;

            // 傳入的資料
            var LotNo = default(string);
            var OpNo = default(string);
            var QCItemNo = default(string);
            string QCItemName;
            var Employee = default(string);
            decimal SampleQty = default(decimal), ScrapQty = default(decimal), DefectQty = default(decimal), GoodQty = default(decimal);

            int i, j;
            string ERFAction;
            var ERFMsg = default(string);
            var strSPCSerial = new string[2];


            var intResult = default(int);
            int intERFSource;
            var LinkName = default(string);
            // Dim LotSerial As String
            // Dim LogGroupSerial As String
            string ExecuteSPCSerial;
            string PopulationQty = "";
            // Dim ProductNo As String
            // Dim CustomerNo As String = "N/A" '開異常單時 customerNo不能 Null
            // Dim LotStamp As String
            bool MailNotice = false;
            string[] aryAdditionalSQL;
            int LimitLotNum;   // 計數品管項目的最大顯示批數

            string[] aryUpdateSQLReplace; // Added by Neil 2008/05/22
            bool blnCheckOK = true;  // Added by Neil 2008/05/22 作為各項檢查的指標，任何一項檢查不過則為False
            var strCheckNotOKMessage = default(string);  // Added by Neil 2008/05/22 

            tcSPC_IC.clsSPC objtc_SPC = new tcSPC_IC.clsSPC();
            AppSettings objSetting = new AppSettings();
            string strDataBaseType = objSetting.GetDataBaseType();
            string strConnectionString = objSetting.GetConnectionString(strDataBaseType);

            try
            {
                funAutoBRSPCContorl_AttRet = "error";   // Initial

                // =====  整理傳入的資料 =========
                if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作 
                {
                    if (drData.Table.Columns.Contains("LotNo"))
                    {
                        LotNo = drData["LotNo"] + "".ToString();
                    }
                    else
                    {
                        strCheckNotOKMessage = "No LotNo";
                        // Exit Function
                        blnCheckOK = false;
                    } // Added by Neil 2008/05/22
                }

                if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作 
                {
                    if (drData.Table.Columns.Contains("QCItemNo"))
                    {
                        QCItemNo = drData["QCItemNo"] + "".ToString().ToUpper();      // 請注意一定要轉大寫
                    }
                    else
                    {
                        strCheckNotOKMessage = "No QCItemNo";
                        // Exit Function
                        blnCheckOK = false;
                    } // Added by Neil 2008/05/22
                }

                if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作 
                {
                    if (drData.Table.Columns.Contains("OpNo"))
                    {
                        OpNo = drData["OpNo"] + "".ToString();
                    }

                    if (drData.Table.Columns.Contains("Employee"))
                    {
                        Employee = drData["Employee"] + "".ToString();
                    }

                    if (drData.Table.Columns.Contains("Product"))
                    {
                        ProductNo = drData["Product"] + "".ToString();
                    }
                    if (drData.Table.Columns.Contains("SampleQty"))
                    {
                        SampleQty = Convert.ToDecimal(drData["SampleQty"]);
                    }
                    else
                    {
                        strCheckNotOKMessage = "No SampleQty";
                        // Exit Function
                        blnCheckOK = false;
                    } // Added by Neil 2008/05/22
                }

                if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作 
                {
                    if (drData.Table.Columns.Contains("ScrapQty"))
                    {
                        ScrapQty = Convert.ToDecimal(drData["ScrapQty"]);
                    }
                    else
                    {
                        strCheckNotOKMessage = "No ScrapQty";
                        // Exit Function
                        blnCheckOK = false;
                    } // Added by Neil 2008/05/22
                }

                if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作 
                {
                    if (drData.Table.Columns.Contains("DefectQty"))
                    {
                        DefectQty = Convert.ToDecimal(drData["DefectQty"]);
                    }
                    else
                    {
                        DefectQty = 0m;
                    }
                    if (drData.Table.Columns.Contains("GoodQty"))
                    {
                        GoodQty = Convert.ToDecimal(drData["GoodQty"]);
                    }
                    else
                    {
                        strCheckNotOKMessage = "No GoodQty";
                        // Exit Function
                        blnCheckOK = false;
                    } // Added by Neil 2008/05/22
                }

                if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作 
                {
                    // 不良原因
                    if (dvError != null)
                    {
                        dtError = dvError.Table.Clone();
                        var loopTo = dvError.Count - 1;
                        for (i = 0; i <= loopTo; i++)
                            dtError.ImportRow(dvError[i].Row);
                        if (funChkReasonsExist(dtError, ref ScrapQty, ref DefectQty, ref GoodQty, ref SampleQty) == false)
                        {
                            strCheckNotOKMessage = "ReasonNo is not exist";
                            blnCheckOK = false;
                        }
                    }


                    // =====  檢查資料是否正確 =========
                    // 1.取出品管項目
                    dtQCItemBasis = kcQC.ShowQCItemForAttributes(QCItemNo, IssueState: 2).Tables[0];

                    if (dtQCItemBasis == null || dtQCItemBasis.Rows.Count == 0)
                    {
                        strCheckNotOKMessage = "QCItemNo Error";
                        // Exit Function
                        blnCheckOK = false; // Added by Neil 2008/05/22
                    }
                }


                if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作 
                {
                    // 2 取出Lot資料
                    dtLotData = kcWIP.ShowLotBasisJoinState(LotNo).Tables[0];
                    if (dtLotData == null || dtLotData.Rows.Count == 0)
                    {
                        if (Convert.ToBoolean(Operators.ConditionalCompareObjectEqual(dtQCItemBasis.Rows[0]["LotIsValid"], 1, false)))     // 必須檢查批號有效,但找不到批號資料
                        {
                            strCheckNotOKMessage = "LotData Error";
                            // Exit Function
                            blnCheckOK = false; // Added by Neil 2008/05/22
                        }
                    }
                    else
                    {
                        PopulationQty = dtLotData.Rows[0]["CurQty"].ToString();
                        LotSerial = dtLotData.Rows[0]["LotSerial"] + "".ToString();
                        LogGroupSerial = dtLotData.Rows[0]["LogGroupSerial"] + "".ToString();
                        LotStamp = dtLotData.Rows[0]["LotStamp"] + "".ToString();
                        ProductNo = dtLotData.Rows[0]["ProductNo"] + "".ToString();
                        CustomerNo = dtLotData.Rows[0]["CustomerNo"] + "".ToString();
                        OpNo = dtLotData.Rows[0]["OpNo"] + "".ToString();
                    }
                }

                // =====  整理要因資料 =========
                if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作 
                {
                    // 3.1 取出 QCItemFactor
                    if (!(dtQCItemFactorAll == null))
                    {
                        dtQCItemFactor = dtQCItemFactorAll;
                    }
                    else
                    {
                        dtQCItemFactor = kcQC.ShowQCItemFactor(QCItemNo).Tables[0];
                    }

                    if (dtQCItemFactor == null || dtQCItemFactor.Rows.Count == 0)
                    {
                        strCheckNotOKMessage = "QCItemFactor Error";
                        // Exit Function
                        blnCheckOK = false; // Added by Neil 2008/05/22
                    }
                }


                // 3.2 取得檢驗批的要因資料
                if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作 
                {
                    dtWIPFactor = udSPC.LoadLotFactorValue(LotNo).Tables[0];
                }


                // 3.3.將取得的要因值填入品管項目要因,取不到值的則填入N/A
                if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作 
                {
                    dtQCItemFactor.Columns.Add("FactorValue", Type.GetType("System.String"));
                    dtFactorOnline = dtQCItemFactor.Clone();    // 複製Table Schema
                    var loopTo1 = dtQCItemFactor.Rows.Count - 1;
                    for (i = 0; i <= loopTo1; i++)
                    {
                        drSel = dtWIPFactor.Select("ParameterNo = '" + dtQCItemFactor.Rows[i]["FactorNo"] + "'".ToString());
                        if (drSel.Length > 0)
                        {
                            dtQCItemFactor.Rows[i]["FactorValue"] = drSel[0]["Value"];
                        }
                        else if (drData.Table.Columns.Contains(Convert.ToString(dtQCItemFactor.Rows[i]["FactorNo"])) && !(drData[dtQCItemFactor.Rows[i]["FactorNo"].ToString()] is DBNull) && Convert.ToBoolean(Operators.ConditionalCompareObjectNotEqual(drData[dtQCItemFactor.Rows[i]["FactorNo"].ToString()], "", false)))
                        {
                            dtQCItemFactor.Rows[i]["FactorValue"] = drData[dtQCItemFactor.Rows[i]["FactorNo"].ToString()];                  // 從傳入的drData 中取得要因
                        }
                        else
                        {
                            dtQCItemFactor.Rows[i]["FactorValue"] = "N/A";
                        }

                        // 取出線上管制要因
                        if (Convert.ToBoolean(Operators.ConditionalCompareObjectEqual(dtQCItemFactor.Rows[i]["FactorOnline"], "Y", false)))
                        {
                            dtFactorOnline.ImportRow(dtQCItemFactor.Rows[i]);
                        }
                    }
                }


                // '=====  檢查此筆檢測資料是否已經存過 =========
                // If blnChkOnly = True Then
                // If kcSPC.ShowVarFactorLog(, , QCItemNo, LotNo).Tables("SPCVarFactorLog").Rows.Count > 0 Then
                // funAutoBRSPCContorl_Var = "Already Exist"
                // Exit Function
                // End If
                // End If


                // 若純檢查,只要做到這裡為止   
                if (blnChkOnly == true)
                {
                    funAutoBRSPCContorl_AttRet = "";
                    return funAutoBRSPCContorl_AttRet;
                }

                // '=====  Check 規格與管制法則 =========
                var dtViolation = new DataTable("Violation");    // 執行 funChkAttRule 後回傳結果
                if (blnChkAttRule == true)
                {
                    if (blnCheckOK == true) // 前項檢查通過才繼續下面的動作 
                    {
                        dtViolation.Columns.Add("SPCSerial", Type.GetType("System.String"));
                        dtViolation.Columns.Add("ViolationID", Type.GetType("System.String"));   // 違反類型：WE001, WE002, ..,WE008
                        dtViolation.Columns.Add("PChartFlag", Type.GetType("System.Int16"));   // 0:否 1:是

                        if (!(dtQCItemBasis.Rows[0]["LimitLotNum"] is DBNull))
                        {
                            LimitLotNum = Conversions.ToInteger(dtQCItemBasis.Rows[0]["LimitLotNum"]);
                        }
                        else
                        {
                            LimitLotNum = defInteger;
                        }

                        // 檢查法則(取出LinkeName,ERFMsg,intResult,dtViolation)
                        if (Convert.ToBoolean(objtc_SPC.funChkAttRule(QCItemNo, dtError, Convert.ToString(dtQCItemBasis.Rows[0]["RuleNo"] +
                            ""), (long)Math.Round(SampleQty), ref LinkName, ref ERFMsg, ref intResult, ref dtViolation, ScrapQty / SampleQty, dtFactorOnline, LimitLotNum, AQLRejectNum, LotRuleNo)) == false)
                        {
                            strCheckNotOKMessage = "Check Rule Error";
                            // Exit Function
                            blnCheckOK = false; // Added by Neil 2008/05/22
                        }
                    }
                }

                // Added by Neil 2008/05/19
                if (blnCheckOK == false)
                {
                    drData["Execute"] = strCheckNotOKMessage;    // 從下面搬移過來 Yun 2009/6/4 

                    // 檢查沒過回寫狀態
                    if (drData.Table.Columns.Contains("UpdateSQL") && !(drData["UpdateSQL"] is DBNull))
                    {
                        if (drData.Table.Columns.Contains("UpdateSQLReplace") && !(drData["UpdateSQLReplace"] is DBNull))
                        {
                            var CollectSQL = new Collection();
                            // drData("Execute") = strCheckNotOKMessage   '搬移  Yun 2009/6/4 
                            objtc_SPC.CollectUpdateSQL(ref drData, ref CollectSQL);
                            if (CollectSQL.Count != 0)
                            {
                                kcSPC.ExecuteSQL(CollectSQL);
                            }
                        }
                    }

                    funAutoBRSPCContorl_AttRet = strCheckNotOKMessage;
                    return funAutoBRSPCContorl_AttRet;

                }

                drData["Result"] = LinkName;   // 回傳出去給人看的所以用字串
                drData["intResult"] = intResult;

                // Add QCItem Test Value
                dtQCItem = new DataTable("QCItem");
                dtQCItem.Columns.Add("QCItemNo", Type.GetType("System.String"));
                dtQCItem.Columns.Add("QCAttSPSerial", Type.GetType("System.String"));
                dtQCItem.Columns.Add("SampleQty", Type.GetType("System.Int32"));
                dtQCItem.Columns.Add("GoodQty", Type.GetType("System.Int32"));
                dtQCItem.Columns.Add("ScrapQty", Type.GetType("System.Int32"));
                dtQCItem.Columns.Add("DefectQty", Type.GetType("System.Int32"));
                dtQCItem.Columns.Add("Result", Type.GetType("System.Int32"));
                dtQCItem.Columns.Add("ERFMsg", Type.GetType("System.String"));
                dtQCItem.Columns.Add("SPCSerial", Type.GetType("System.String"));    // 產生SPCSerial後可存在此欄位

                if (blnChkAttRule == true)
                {
                    drAdd = dtQCItem.NewRow();
                    drAdd["QCItemNo"] = QCItemNo;
                    drAdd["QCAttSPSerial"] = "";
                    drAdd["SampleQty"] = SampleQty;
                    drAdd["GoodQty"] = GoodQty;
                    drAdd["ScrapQty"] = ScrapQty;
                    drAdd["DefectQty"] = DefectQty;
                    drAdd["Result"] = intResult.ToString();
                    drAdd["ERFMsg"] = ERFMsg;
                    drAdd["SPCSerial"] = "";
                    if (MClassNo != defString)
                    {
                        drAdd["MClassNo"] = MClassNo;
                    }
                    dtQCItem.Rows.Add(drAdd);

                    // 2020/01/20 ,Yenru Add ERFMSG
                    if (drData.Table.Columns.Contains("ERFMsg"))
                    {
                        drData["ERFMsg"] = dtQCItem.Rows[0]["ERFMsg"];
                    }
                    //回写法则编号
                    string ruleno = "";
                    if (drData.Table.Columns.Contains("RuleNo"))
                    {
                        drData["RuleNo"] = dtQCItemBasis.Rows[0]["RuleNo"];
                    }
                }
                else
                {
                    drAdd = dtQCItem.NewRow();
                    drAdd["QCItemNo"] = drData["QCItemNo"];
                    drAdd["QCAttSPSerial"] = "";
                    drAdd["SampleQty"] = drData["SampleQty"];
                    drAdd["GoodQty"] = drData["GoodQty"];
                    drAdd["ScrapQty"] = drData["ScrapQty"];
                    drAdd["DefectQty"] = 0;
                    drAdd["Result"] = drData["DefaultResult"];
                    drAdd["ERFMsg"] = "";
                    drAdd["SPCSerial"] = "";
                    if (MClassNo != defString)
                    {
                        drAdd["MClassNo"] = MClassNo;
                    }
                    dtQCItem.Rows.Add(drAdd);
                }

                // 收集要執行的指令
                var colSQL = new Collection();
                string strReturnValue;

                // Update SPC Log
                if (!dtQCItem.Columns.Contains("Rank"))
                {
                    dtQCItem.Columns.Add("Rank", Type.GetType("System.String"));      // 2010/06/28 Lai
                }
                if (!dtQCItem.Columns.Contains("RankDetail"))
                {
                    dtQCItem.Columns.Add("RankDetail", Type.GetType("System.String")); // 2010/06/28 Lai
                }

                strReturnValue = kcSPC.UpdAtt_TestValue(dtQCItemFactor, dtQCItem, dtError, LotNo, LotSerial, Creator: Employee, colSQL: colSQL, LogGroupSerial: LogGroupSerial);

                // 取得 SPCSerial
                strSPCSerial = Strings.Split(strReturnValue, "|");
                if (drData.Table.Columns.Contains("SPCSerial"))
                {
                    drData["SPCSerial"] = strSPCSerial[0];
                }

                // Add SPC待檢清單 
                kcSPC.UpdSpcLotQCList(LotNo, OpNo, 0, dtQCItem, strSPCSerial, colSQL);

                //争对EAP处理
                if (EAPControl)
                {
                    //对生产批的处理
                    if (BrControl || intResult != 0)
                    {
                        // WIP的BR_SPCControl
                        DataTable dtSamplePara = new DataTable("SamplePara");
                        dtSamplePara.Columns.Add("ParameterNo", Type.GetType("System.String"));
                        dtSamplePara.Columns.Add("ParameterValue", Type.GetType("System.String"));
                        kcWIP.BR_SPCControl(long.Parse(LotStamp), LotNo, ref LinkName, QCItemNo, UserNo, ShiftNo, colSQL, dtSamplePara, strMsg: ERFMsg, blnFirstLotOf2ndInsp: false, blnReleaseLog: blnReleaseLog);
                        funAutoBRSPCContorl_AttRet = LinkName;
                        // 紀錄Cont_QCItem
                        var loopTo13 = dtQCItem.Rows.Count - 1;
                        string[] comp_SPCSerial;
                        string[] comp_ERFMsg;
                        for (i = 0; i <= loopTo13; i++)
                        {
                            comp_SPCSerial = strSPCSerial[i].Split(';');   // 當計算方式為 ByComponent時會用;隔開
                            var loopTo14 = comp_SPCSerial.Length - 1;
                            for (int c = 0; c <= loopTo14; c++)
                                kcSPC.AddWIPQCItemContent(LotNo, LotSerial, OpNo, QCItemNo, Convert.ToString(dtQCItem.Rows[i]["QCItemNo"]), 1, comp_SPCSerial[c], UserNo, LogGroupSerial, colSQL);
                        }
                    }
                }

                string strPChcartViolationID;
                // Add Att Violation
                if (!(dtViolation == null) && dtViolation.Rows.Count > 0)
                {
                    strPChcartViolationID = "";
                    var loopTo2 = dtViolation.Rows.Count - 1;
                    for (i = 0; i <= loopTo2; i++)
                    {
                        dtViolation.Rows[i]["SPCSerial"] = dtQCItem.Rows[0]["SPCSerial"];
                        if (Convert.ToBoolean(Operators.ConditionalCompareObjectEqual(dtViolation.Rows[i]["PChartFlag"], 1, false)))
                        {
                            strPChcartViolationID = strPChcartViolationID + (string.IsNullOrEmpty(strPChcartViolationID) ? "" : "|") +
                                dtViolation.Rows[i]["ViolationID"];
                        }
                    }
                    kcSPC.AddSPCAttViolationContent(dtViolation, colSQL);
                    kcSPC.AddSPCPChartFailLog(strSPCSerial[0], QCItemNo, (ScrapQty / SampleQty).ToString(), strPChcartViolationID, ProductNo, OpNo);   // P-Chart 不良紀錄
                }

                //0173294: [6.0.7]SDD240326_01-04 / Execute_InLineSPC_Judge 將異常單訊息上提到新增ERFCrerateData之前 Edison 241126

                var strMsg = default(string);
                if (Convert.ToBoolean(Operators.ConditionalCompareObjectEqual(dtQCItem.Rows[0]["Result"], 4, false)) || Convert.ToBoolean(Operators.ConditionalCompareObjectEqual(dtQCItem.Rows[0]["Result"], 3, false)))      // Alert 或 Hold 要發 mail (4=Alert,計數的Result,與計量不同)
                {
                    strMsg = "Auto SPC " + "[" + QCItemNo + "]" + Strings.Replace(dtQCItem.Rows[0]["ERFMsg"].ToString(), "$", "");
                    // Call kcSPC.SendAlertMail(OpNo, MClassNo, QCItemNo, "0", "[Alert Mail]:" & QCItemNo, strMsg)
                }

                string strSPCSerialPlitByDot = string.Join(",",
                                dtViolation.AsEnumerable()
                                           .Select(row => row.Field<string>("SPCSerial"))
                                           .Distinct());
                if (!blnHoldLot && strMsg != default(string))
                    kcWIP.AddERFCreateData(LotNo, OpNo, "1", 0, strMsg, PopulationQty, UserNo, strSPCSerialPlitByDot, Conversions.ToInteger(dtQCItemBasis.Rows[0]["LotIsValid"]), 1, LotSerial: LotSerial, colSQL: colSQL);

                if (!(dtAttDetailLogComponent == null))
                {
                    var loopTo14 = dtAttDetailLogComponent.Rows.Count - 1;
                    for (i = 0; i <= loopTo14; i++)
                        kcSPC.AddSPCAttDetailLogComponent(strSPCSerial[0], dtAttDetailLogComponent.Rows[i]["ComponentNo"].ToString(), dtAttDetailLogComponent.Rows[i]["ErrorNo"].ToString(), Convert.ToDecimal(dtAttDetailLogComponent.Rows[i]["ErrorQty"]), dtAttDetailLogComponent.Rows[i]["Area"].ToString(), dtAttDetailLogComponent.Rows[i]["Memo"].ToString(), colSQL);
                }

                // 額外需執行的指令
                if (drData.Table.Columns.Contains("AdditionalSQL") && Operators.ConditionalCompareObjectNotEqual(drData["AdditionalSQL"] + "", "", false))
                {
                    aryAdditionalSQL = Strings.Split(drData["AdditionalSQL"].ToString(), "|");
                    var loopTo3 = aryAdditionalSQL.Length - 1;
                    for (i = 0; i <= loopTo3; i++)
                        colSQL.Add(aryAdditionalSQL[i]);
                }

                // 額外需執行的指令 Added by Neil 2008/05/22
                if (drData.Table.Columns.Contains("UpdateSQL") && !(drData["UpdateSQL"] is DBNull))
                {
                    objtc_SPC.CollectUpdateSQL(ref drData, ref colSQL);
                }


                // 呼叫執行SQL指令
                if (CollectionSQL == null)
                {
                    // Call ExecuteSQLNoneQuery_UPD(DataBaseType, strConnectionString, CollectionSQL)
                    // 執行彙總的 SQL 指令
                    kcSPC.ExecuteSQL(colSQL);
                    drData["Execute"] = "success";   // 表示SPC資料已儲存成功
                }
                else
                {
                    // 2020/11/23 Grace 不執行指令,將指令傳出
                    var loopTo4 = colSQL.Count;
                    for (i = 1; i <= loopTo4; i++)
                        CollectionSQL.Add(colSQL[i]);
                }

                if (blnCreateERF == true)
                {
                    // =====  發Mail =========
                    // //Create connection
                    //var strMsg = default(string);

                    //// ↓===================================================================================================================
                    //if (Convert.ToBoolean(Operators.ConditionalCompareObjectEqual(dtQCItem.Rows[0]["Result"], 4, false)) || Convert.ToBoolean(Operators.ConditionalCompareObjectEqual(dtQCItem.Rows[0]["Result"], 3, false)))      // Alert 或 Hold 要發 mail (4=Alert,計數的Result,與計量不同)
                    //{
                    //    strMsg = "Auto SPC " + "[" + QCItemNo + "]" + Strings.Replace(dtQCItem.Rows[0]["ERFMsg"].ToString(), "$", "");
                    //    // Call kcSPC.SendAlertMail(OpNo, MClassNo, QCItemNo, "0", "[Alert Mail]:" & QCItemNo, strMsg)
                    //}
                    // ↑===================================================================================================================


                    // 開立異常單
                    string ERFNo;
                    string OPName = defString; // added by Neil 2008/07/29

                    intERFSource = 1;

                    if (Convert.ToBoolean(Operators.ConditionalCompareObjectEqual(dtQCItem.Rows[0]["Result"], 3, false)))    // Hold
                    {

                        if (MClassNo == defString)
                        {
                            // 取得MClassNo (取不到則不開) ========================================================   'update by Yun 2007/12/19
                            cnnTemp = CreateConnection(strConnectionString);
                            strSQL = "Select MClassNo, QCItemName From tblQCItemForAttributes Where  QCItemNo = '" + QCItemNo + "'";

                            // cmmTemp = New OleDb.OleDbCommand(strSQL, cnnTemp) 'Select data
                            drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                            if (drTemp.Read())
                            {
                                MClassNo = drTemp["MClassNo"] + "".ToString();
                                QCItemName = drTemp["QCItemName"] + "".ToString();
                                if (!string.IsNullOrEmpty(MClassNo))                         // Added by Yun 2007/12/19
                                {
                                    ERFNo = udWIP.InitERFNo(InitNo: "SPC");
                                    kcWIP.UpdERF_Create(1, Conversions.ToLong(LotStamp), LotNo, intERFSource, ERFNo, 0, MClassNo, strMsg, Employee, CreateERF: true, OPNo: OpNo, CustomerNo: CustomerNo, ProductNo: ProductNo, SPCSerial: strSPCSerial[0], QCItemType: 0);
                                }
                            }
                            drTemp.Close();
                            CloseConnection(cnnTemp);
                        }
                    }
                }


                if (EAPControl)
                {
                    if (!BrControl)
                    {
                        if (intResult == 0)
                        {
                            funAutoBRSPCContorl_AttRet = "success";   // 表示 Function 執行成功
                        }
                    }
                }
                else
                {
                    funAutoBRSPCContorl_AttRet = "success";   // 表示 Function 執行成功
                }
            }


            catch (Exception e1)
            {
                funAutoBRSPCContorl_AttRet = "fail";
                // 發生錯誤時丟回本身的Function Name及系統錯誤訊息
                throw;
            } // New Exception("kcSPC.funBRSPConlinetrans: " & e1.Message)

            finally
            {
                if (kcWIP != null)
                    kcWIP = null;
                if (kcQC != null)
                    kcQC = null;
                if (kcSPC != null)
                    kcSPC = null;

                if (objtc_SPC != null)
                    objtc_SPC = null;

                if (cnnTemp != null)
                    CloseConnection(cnnTemp);
            }

            return funAutoBRSPCContorl_AttRet;


        }

        // Added by Neil 2008/07/29 檢查原因是否存在並更新ScrapQty,DefectQty,GoodQty資料
        private bool funChkReasonsExist(DataTable dtError, ref decimal ScrapQty, ref decimal DefectQty, ref decimal GoodQty, ref decimal SampleQty)
        {
            bool funChkReasonsExistRet = default(bool);

            IDbConnection cnnTemp = null;
            // Dim cmmTemp As OleDb.OleDbCommand
            DbDataReader drTemp = null;

            var ScrapCount = default(decimal);
            var DefectCount = default(decimal);

            int inti;

            AppSettings objSetting = new AppSettings();
            string strDataBaseType = objSetting.GetDataBaseType();
            string strConnectionString = objSetting.GetConnectionString(strDataBaseType);
            string strSQL = "";

            try
            {

                // //Create connection
                cnnTemp = CreateConnection(strConnectionString);
                // cmmTemp = New OleDb.OleDbCommand
                // cmmTemp.Connection = cnnTemp

                funChkReasonsExistRet = true;

                var loopTo = dtError.Rows.Count - 1;
                for (inti = 0; inti <= loopTo; inti++)
                {
                    // 檢查資料在 TBL_QUICS_SPCDETAILLOG 中是否存在
                    strSQL = "SELECT REASONTYPE FROM TBLQCREASONBASIS " + "WHERE " + "REASONNO = '" +
                        dtError.Rows[inti]["ErrorNo"] + "' AND ISSUESTATE = 2";

                    // cmmTemp.CommandText = strSQL
                    drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);

                    if (drTemp.Read() == false) // 若不存在則結束
                    {
                        funChkReasonsExistRet = false;
                        return funChkReasonsExistRet;
                    }
                    else
                    {

                        switch (drTemp["ReasonType"].ToString())
                        {
                            case "0": // Scrap
                                {
                                    ScrapCount = (decimal)((double)ScrapCount + Conversion.Val(dtError.Rows[inti]["ErrorQty"]));
                                    break;
                                }
                            case "1": // Defect
                                {
                                    DefectCount = (decimal)((double)DefectCount + Conversion.Val(dtError.Rows[inti]["ErrorQty"]));
                                    break;
                                }
                        }
                    }
                    drTemp.Close();
                }

                ScrapQty = ScrapCount;
                DefectQty = DefectCount;
                GoodQty = SampleQty - (ScrapQty + DefectQty);
            }

            catch (Exception ex)
            {
                funChkReasonsExistRet = false;
                throw;
            }
            finally
            {
                if (!(cnnTemp == null))
                    CloseConnection(cnnTemp);

            }

            return funChkReasonsExistRet;

        }


        //建立计数检验数据收集接口
        public static bool funCreateSPCATTBASIS(ref List<ArrayList> aryMessage, ref List<ArrayList> aryException, ref Collection CollectionSQL, string SpcSerial, string QCItemNo, string LotNo, string SampleQty, string GoodQty, string ScrapQty, string DefectQty, string Result, string SPCSerial_QMS, string MaterialNo, string MTLVendor, string Employee, string ProDuct, string EquipMent, string DC, string CreateDate)
        {
            bool result = false;
            //int i;
            string strSQL = "";
            string strSQL_Value = "";
            //IDbConnection cnnTemp = null;
            //DbDataReader drTemp = null;
            //Collection CollectionSQL = new Collection();

            //AppSettings objSetting = new AppSettings();
            //string strDataBaseType = objSetting.GetDataBaseType();
            //string strConnectionString = objSetting.GetConnectionString(strDataBaseType);
            decimal decResult = 0;
            try
            {
                //cnnTemp = CreateConnection(strConnectionString);

                //因为前面先锁住序号，所有这里不需要再次检查

                ////先检查是否已经存在相同数据
                //strSQL = $@"Select * From tblSPCAttBasisLog Where SpcSerial ='{SpcSerial}'";
                //drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                //if (drTemp.Read())
                //{
                //    //combineException(ref aryException, null, null, "[%已经存在对应的计数检验数据!%]");
                //    //return result;
                //    throw new Exception("已经存在对应的计数检验数据!");
                //}
                //drTemp.Dispose();

                //主档
                strSQL = "Insert Into tblSPCAttBasisLog(SpcSerial,QCItemNo,LotNo,CreateDate,Creator,Product,Equipment,SPCSerial_QMS,MATERIAL,MTLVendor,EMPLOYEE,DC";
                strSQL_Value = $@" Values('{SpcSerial}','{QCItemNo}','{LotNo}',To_Date('{Strings.Format(Convert.ToDateTime(CreateDate), "yyyy/MM/dd H:mm:ss")}','YYYY/MM/DD HH24:MI:SS'),'QMS','{ProDuct}',
'{EquipMent}','{SPCSerial_QMS}','{MaterialNo}','{MTLVendor}','{Employee}','{DC}'";

                if (SampleQty != "")
                {
                    strSQL += ",SampleQty";
                    strSQL_Value += $@",{SampleQty}";
                }
                if (GoodQty != "")
                {
                    strSQL += ",GoodQty";
                    strSQL_Value += $@",{GoodQty}";
                }
                if (ScrapQty != "")
                {
                    strSQL += ",ScrapQty";
                    strSQL_Value += $@",{ScrapQty}";
                }
                if (DefectQty != "")
                {
                    strSQL += ",DefectQty";
                    strSQL_Value += $@",{DefectQty}";
                }
                if (Result != "")
                {
                    if (Result.ToUpper() == "PASS")
                    {
                        //成功
                        decResult = 0;
                    }
                    else
                    {
                        //失败 //先默认4
                        decResult = 4;
                    }


                    strSQL += ",Result";
                    strSQL_Value += $@",{decResult}";
                }

                strSQL += ")";
                strSQL_Value += ")";

                CollectionSQL.Add(strSQL + strSQL_Value);

                //ExecuteSQLNoneQuery_UPD(strDataBaseType, strConnectionString, CollectionSQL);
                result = true;

            }
            catch (Exception ex)
            {
                //combineException(ref aryException, null, ex.Message, "[%新增计数检验数据失败%]", ex.StackTrace);
                throw;
            }
            finally
            {
                //if (drTemp != null && !drTemp.IsClosed)
                //{
                //    drTemp.Close();
                //}
                //CloseConnection(cnnTemp);
            }

            return result;
        }
        //建立计数检验数据明细记录接口
        public static bool funCreateSPCATTDETAIL(ref List<ArrayList> aryMessage, ref List<ArrayList> aryException, ref Collection CollectionSQL, string SpcSerial, string QCItemNo, string ErrorNo, string strErrorQty, string CreateDate)
        {
            bool result = false;
            string strSQL = "";
            string strSQL_Value = "";
            //IDbConnection cnnTemp = null;
            //DbDataReader drTemp = null;
            //Collection CollectionSQL = new Collection();

            //AppSettings objSetting = new AppSettings();
            //string strDataBaseType = objSetting.GetDataBaseType();
            //string strConnectionString = objSetting.GetConnectionString(strDataBaseType);
            decimal ErrorQty = 0;

            try
            {
                //cnnTemp = CreateConnection(strConnectionString);

                //因为前面先锁住序号，所有这里不需要再次检查

                ////先检查是否已经存在相同数据
                //strSQL = $@"Select * From tblSPCAttDetailLog Where SpcSerial ='{SpcSerial}'";
                //drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                //if (drTemp.Read())
                //{
                //    combineException(ref aryException, null, null, "[%已经存在对应的计数检验数据!%]");
                //    return result;
                //}
                //drTemp.Dispose();

                if (!decimal.TryParse(strErrorQty, out ErrorQty))
                {
                    throw new Exception("不良数量格式错误!");
                }

                //主档

                strSQL = "Insert Into tblSPCAttDetailLog(SpcSerial,QCItemNo,ErrorNo,ErrorQty,CreateDate";
                strSQL_Value = $@" Values('{SpcSerial}','{QCItemNo}','{ErrorNo}',{ErrorQty},To_Date('{Strings.Format(Convert.ToDateTime(CreateDate), "yyyy/MM/dd H:mm:ss")}','YYYY/MM/DD HH24:MI:SS')";

                strSQL += ")";
                strSQL_Value += ")";

                CollectionSQL.Add(strSQL + strSQL_Value);



                //ExecuteSQLNoneQuery_UPD(strDataBaseType, strConnectionString, CollectionSQL);
                result = true;

            }
            catch (Exception ex)
            {
                //combineException(ref aryException, null, ex.Message, "[%新增计数检验数据失败%]", ex.StackTrace);
                throw;
            }
            finally
            {
                //if (drTemp != null && !drTemp.IsClosed)
                //{
                //    drTemp.Close();
                //}
                //CloseConnection(cnnTemp);
            }

            return result;
        }
        //建立计数检验数据区域明细接口
        public static bool funCreateSPCATTSECTION(ref List<ArrayList> aryMessage, ref List<ArrayList> aryException, ref Collection CollectionSQL, string SpcSerial, string SectionNo, string ErrorNo, string Area, string strErrorQty)
        {
            bool result = false;
            //int i;
            string strSQL = "";
            string strSQL_Value = "";
            //IDbConnection cnnTemp = null;
            //DbDataReader drTemp = null;
            //Collection CollectionSQL = new Collection();

            //AppSettings objSetting = new AppSettings();
            //string strDataBaseType = objSetting.GetDataBaseType();
            //string strConnectionString = objSetting.GetConnectionString(strDataBaseType);
            decimal ErrorQty = 0;
            try
            {
                //cnnTemp = CreateConnection(strConnectionString);

                //因为前面先锁住序号，所有这里不需要再次检查

                ////先检查是否已经存在相同数据
                //strSQL = $@"Select * From tblSPCAttDetailLogSection Where SpcSerial ='{SpcSerial}'";
                //drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                //if (drTemp.Read())
                //{
                //    combineException(ref aryException, null, null, "[%已经存在对应的计数检验数据区域明细!%]");
                //    return result;
                //}
                //drTemp.Dispose();




                //主档

                strSQL = "Insert Into tblSPCAttDetailLogSection(SpcSerial,SectionNo,ErrorNo,Area";
                strSQL_Value = $@" Values('{SpcSerial}','{SectionNo}','{ErrorNo}','{Area}'";

                if (strErrorQty != "")
                {
                    if (!decimal.TryParse(strErrorQty, out ErrorQty))
                    {
                        throw new Exception("不良数量格式错误!");
                    }

                    strSQL += ",ErrorQty";
                    strSQL_Value += $@",{ErrorQty}";
                }

                strSQL += ")";
                strSQL_Value += ")";

                CollectionSQL.Add(strSQL + strSQL_Value);



                ////sql集合一起执行
                //ExecuteSQLNoneQuery_UPD(strDataBaseType, strConnectionString, CollectionSQL);

                ////成功后回写最大码
                //funGetSPCSerialNo(true);

                result = true;

            }
            catch (Exception ex)
            {
                //combineException(ref aryException, null, ex.Message, "[%新增计数检验数据失败%]", ex.StackTrace);
                throw;
            }
            finally
            {
                //if (drTemp != null && !drTemp.IsClosed)
                //{
                //    drTemp.Close();
                //}
                //CloseConnection(cnnTemp);
            }

            return result;
        }

        public static string funGetSPCSerialNo(bool ReserveSerialNo)
        {
            string funExeSerialCreate_SerialRet = default(string);

            DbDataReader drTemp = null;
            var colSQL = new Collection();
            var colSQL_Rows = new Collection();
            var arySerial = new ArrayList();

            string SerialType = "SPCAPI";
            string SerialString = "SPCSERIAL";
            int SerialLength = 4;
            int DecimalType = 1;
            long SerialCount = 1;
            int PadLeft = defInteger;
            string strSQL = "";
            IDbConnection cnnTemp = null;
            AppSettings objSetting = new AppSettings();
            string strDataBaseType = objSetting.GetDataBaseType();
            string strConnectionString = objSetting.GetConnectionString(strDataBaseType);


            try
            {

                cnnTemp = CreateConnection(strConnectionString);

                // 取出最大碼.
                bool blnInsert;
                long lngMaxSerialNo; // 起始碼.
                long lngSerialNo_Last; // 最大一個序號.
                var strSerialNo_Last_nn = default(string); // 轉換成10,16,36表示式.

                strSQL = "Select MaxSerialNo From tblSYSSerialCreateMax " + " Where SerialType='" + SerialType + "'" + " And SerialString='" + SerialString + "'" + " And SerialLength=" + SerialLength + " And DecimalType=" + DecimalType + "";
                // cmmTemp = New OleDb.OleDbCommand(strSQL, cnnTemp) 
                drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                if (drTemp.Read())
                {
                    blnInsert = false;
                    lngMaxSerialNo = Conversions.ToLong(drTemp["MaxSerialNo"]);
                }
                else
                {
                    blnInsert = true;
                    lngMaxSerialNo = 0L;
                }
                drTemp.Close();

                // 最大的序號
                lngSerialNo_Last = lngMaxSerialNo + SerialCount;

                if (DecimalType == 1)
                {
                    strSerialNo_Last_nn = lngSerialNo_Last.ToString();
                }
                else if (DecimalType == 2)
                {
                    strSerialNo_Last_nn = Tran10_16(lngSerialNo_Last);
                }
                else if (DecimalType == 3)
                {
                    strSerialNo_Last_nn = Tran10_36(lngSerialNo_Last);
                }

                // 86774: [6.0.5] 序號規則新增34進位序號
                // ===================================== 86774 Start =====================================
                else if (DecimalType == 4)
                {
                    strSerialNo_Last_nn = Tran10_34(lngSerialNo_Last);
                    // ===================================== 86774  End  =====================================

                }

                // 最大碼是否超出序號長度.
                if (strSerialNo_Last_nn != null && strSerialNo_Last_nn.Length > SerialLength)
                {
                    throw new iMESException.MESException("9P12-200028", "[%SerialString%]: " + SerialString + Constants.vbNewLine + "[%SerialLength%]: " + SerialLength + Constants.vbNewLine + "[%SerialNo%]: " + strSerialNo_Last_nn);
                }

                if (ReserveSerialNo == true)
                {
                    // 回填最大碼(利用MaxSerialNo來確保資料的一致性及使用的序號為唯一)
                    if (blnInsert)
                    {
                        strSQL = "Insert into tblSYSSerialCreateMax " + " (SerialType,SerialString,SerialLength,DecimalType,MaxSerialNo) " + " Values('" + SerialType + "','" + SerialString + "'," + SerialLength + "," + DecimalType + "," + lngSerialNo_Last + ")";
                        colSQL.Add(strSQL);
                        colSQL_Rows.Add(1);
                    }
                    else
                    {
                        strSQL = "Update tblSYSSerialCreateMax Set MaxSerialNo=" + lngSerialNo_Last + " Where SerialType='" + SerialType + "'" + " And SerialString='" + SerialString + "'" + " And SerialLength=" + SerialLength + " And DecimalType=" + DecimalType + " And MaxSerialNo=" + lngMaxSerialNo;
                        colSQL.Add(strSQL);
                        colSQL_Rows.Add(1);
                    }

                    ExecuteSQLNoneQuery_ChkRowCount(Convert.ToString(strDataBaseType), strConnectionString, colSQL, colSQL_Rows);
                }

                for (int i = 1, loopTo = (int)SerialCount; i <= loopTo; i++)
                {
                    if (PadLeft == 0)
                    {
                        if (DecimalType == 1)
                        {
                            arySerial.Add((lngMaxSerialNo + i).ToString());
                        }
                        else if (DecimalType == 2)
                        {
                            arySerial.Add(Tran10_16(lngMaxSerialNo + i).ToString());
                        }
                        else if (DecimalType == 3)
                        {
                            arySerial.Add(Tran10_36(lngMaxSerialNo + i).ToString());
                        }

                        // 86774: [6.0.5] 序號規則新增34進位序號
                        // ===================================== 86774 Start =====================================
                        else if (DecimalType == 4)
                        {
                            arySerial.Add(Tran10_34(lngMaxSerialNo + i).ToString());
                            // ===================================== 86774  End  =====================================
                        }
                    }
                    else if (DecimalType == 1)
                    {
                        arySerial.Add((lngMaxSerialNo + i).ToString().PadLeft(SerialLength, '0'));
                    }
                    else if (DecimalType == 2)
                    {
                        arySerial.Add(Tran10_16(lngMaxSerialNo + i).ToString().PadLeft(SerialLength, '0'));
                    }
                    else if (DecimalType == 3)
                    {
                        arySerial.Add(Tran10_36(lngMaxSerialNo + i).ToString().PadLeft(SerialLength, '0'));
                    }

                    // 86774: [6.0.5] 序號規則新增34進位序號
                    // ===================================== 86774 Start =====================================
                    else if (DecimalType == 4)
                    {
                        arySerial.Add(Tran10_34(lngMaxSerialNo + i).ToString().PadLeft(SerialLength, '0'));
                        // ===================================== 86774  End  =====================================
                    }
                }

                // 回傳最大碼
                if (arySerial.Count > 0)
                {
                    funExeSerialCreate_SerialRet = arySerial[0].ToString();
                }
                else
                {
                    funExeSerialCreate_SerialRet = null;
                }
            }

            catch (Exception e1)
            {
                funExeSerialCreate_SerialRet = null;
                throw;
            }
            finally
            {
                CloseConnection(cnnTemp);
            }

            return funExeSerialCreate_SerialRet;

        }

        /// <summary>
        /// 拼接异常信息：Message + StackTrace，用于日志/输出
        /// </summary>
        public static string GetExceptionFullMessage(Exception ex)
        {
            if (ex == null)
                return "无异常信息";

            return $"异常信息：{ex.Message}       堆栈信息：{ex.StackTrace}";
        }
        #endregion

        #region "工单砍单、取消卡控接口"
        public static bool funCheckMONoCanEditCancel_M(ref List<ArrayList> aryMessage, ref List<ArrayList> aryException, ref decimal InputQty, ref decimal OutputQty, ref string CurrentStatus, string MONo, decimal decQuantity, int intStatus)
        {
            bool funCheckMONoCanEditCancel_M = default(bool);
            //bool r = false;
            string strSQL = default(string);
            IDbConnection cnnTemp = null;
            DbDataReader drTemp = null;

            AppSettings objSetting = new AppSettings();


            string strDataBaseType = objSetting.GetDataBaseType();
            string strConnectionString = objSetting.GetConnectionString(strDataBaseType);
            cnnTemp = CreateConnection(strConnectionString);

            DataSet dsData = new DataSet();



            string strReturn = "";

            var ReleaseQty = default(decimal);

            kcWIP.clsLot objLot = new kcWIP.clsLot();
            DataRow drOE = null;
            try
            {

                //标准基础检查
                using (var objOE = new clsOEBasis())
                {

                    drOE = objOE.GetMOBasis(MONo.ToUpper());
                    if (drOE["MONo"] is DBNull == true)
                    {
                        //clsERP.combineException(aryException, mesmsg: "[%MONo%]：" + drOE["MONo"] + " [%MONoNotExist%]");
                        //break;

                        //throw new Exception("工单号不存在");

                        clsERP.combineMessage(ref aryMessage, mmsg: "[%工单号不存在!%]");
                        return false;
                    }

                    // 如果MES這邊是不使用IssueState = -1, 也要可以改,以EPR那邊為主,
                    if (!(drOE["MOState"] is DBNull))
                    {
                        if (Convert.ToBoolean(Operators.ConditionalCompareObjectEqual(drOE["MOState"], 99, false)))
                        {
                            //clsERP.combineException(aryException, mesmsg: "[%MONo%]：" + drOE["MONo"] + " [%MONoClosed%]");
                            //break;

                            //throw new Exception("工单状态已经结案!");

                            clsERP.combineMessage(ref aryMessage, mmsg: "[%工单状态已经结案!%]");
                            return false;
                        }


                    }

                    // ----- 3. 檢查MO剩餘數量 -----
                    if (!(drOE["MOQty"] is DBNull))
                    {
                        ReleaseQty = Convert.ToDecimal(Operators.SubtractObject(drOE["MOQty"], drOE["UnReleaseLotQty"]));
                        if (decQuantity > ReleaseQty && intStatus == 0)
                        {
                            //clsERP.combineException(aryException, mesmsg: "[%MONo%]：" +
                            //    drOE["MONo"] + " [%MOQtyLessThenReleaseLotQty%]");
                            //break;

                            //throw new Exception("砍单数量小于现在工单未下线数量!");

                            clsERP.combineMessage(ref aryMessage, mmsg: "[%砍单数量大于现在工单未下线数量/工单数量!%]");
                            return false;
                        }
                    }

                }

                //如当前设置A站点前可以砍单/取消，可以手动变更为B站点前可以砍单/取消；在作业站属性中配置标识:Delflag（只针对线上的生产批）
                //找到目前线上的生产批

                strSQL = $"SELECT A.BASELOTNO,B.STATUS,B.LotStamp FROM TBLWIPLOTBASIS A INNER JOIN  TBLWIPLOTSTATE B ON A.BASELOTNO = B.LOTNO WHERE A.MONO='{MONo}' AND B.STATUS IN (0,1,2,3,4,5) AND A.LotState = '1'";
                ExecuteSQLQuery_Adapter(strSQL, dsData, "TBLWIPLOTBASIS", cnnTemp);

                if (dsData.Tables["TBLWIPLOTBASIS"].Rows.Count > 0)
                {
                    //有线上生产批
                    //检查线上生产批是否过了有 【在作业站属性中配置标识:Delflag】

                    //拼接成IN语句 
                    string strLotNo = "";
                    foreach (DataRow dr in dsData.Tables["TBLWIPLOTBASIS"].Rows)
                    {
                        strLotNo += "'" + dr["BASELOTNO"] + "',";
                    }
                    strLotNo = strLotNo.Substring(0, strLotNo.Length - 1);

                    strSQL = $@"SELECT 1 
FROM DUAL 
WHERE EXISTS (
    SELECT 1
    FROM TBLOPPROPERTY A
    INNER JOIN TBLOPBASIS B 
        ON A.OPNO = B.OPNO AND B.ISSUESTATE = 2
    INNER JOIN TBLWIPLOTLOG C 
        ON C.OPNO = B.OPNO AND C.STATUS = 1
    WHERE A.PROPERTYNO = 'Delflag' 
      AND A.ISSUESTATE = 2 
      AND C.LOTNO IN ({strLotNo})
)";
                    drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                    if (drTemp.Read())
                    {
                        //存在，说明过了特殊站点，不能变更
                        clsERP.combineMessage(ref aryMessage, mmsg: "[%线上生产批已经过了带有Delflag站点，不能变更!%]");
                        return false;
                    }
                    else
                    {
                        //不存在，说明没有过特殊站点，可以变更

                        //可以变更，先将线上的生产批全部wait,顾问说一搬流程就是 一个批,这里直接调用标准wait
                        foreach (DataRow dr in dsData.Tables["TBLWIPLOTBASIS"].Rows)
                        {
                            //如果是 STATUS 字段是 3或者4则，则不wait
                            if (Convert.ToInt32(dr["STATUS"]) == 3 || Convert.ToInt32(dr["STATUS"]) == 4)
                            {
                                continue;
                            }
                            else
                            {
                                int intLotStamp = dr["LotStamp"].ToString().Trim() == "" ? 0 : Convert.ToInt32(dr["LotStamp"]);

                                //wait
                                objLot.SetLotStateToWait(LotStamp: intLotStamp, dr["BASELOTNO"].ToString(),
                                    WaitType: 2, UserNo: "ERP", WaitSource: 0, WaitDescription: "砍单接口立即暂停所有批次");
                            }
                        }



                    }
                    drTemp.Close();
                }


                //若是成功,返回 工单投入、产出数量 、工单状态工单状态
                if (drOE != null)
                {
                    //工单投入(InputQty) = 工单数量(MOQTY) - 未下线数量(UNRELEASELOTQTY)
                    if (!(drOE["MOQty"] is DBNull) && !(drOE["UnReleaseLotQty"] is DBNull))
                    {
                        //使用三元运算符转换工单数量和未下线数量，防止出现空值报错
                        decimal MOQty = Convert.ToDecimal(drOE["MOQty"] is DBNull ? 0 : drOE["MOQty"]);
                        decimal UnReleaseLotQty = Convert.ToDecimal(drOE["UnReleaseLotQty"] is DBNull ? 0 : drOE["UnReleaseLotQty"]);
                        InputQty = MOQty - UnReleaseLotQty;

                    }

                    //产出数量 OnputQty MOFINISHQTY
                    decimal MOFINISHQTY = Convert.ToDecimal(drOE["MOFINISHQTY"] is DBNull ? 0 : drOE["MOFINISHQTY"]);
                    OutputQty = MOFINISHQTY;

                    //工单状态 根据 MOSTATE判断，赋值给CurrentStatus

                    // 工单状态
                    //0:未确认
                    //1:确认中
                    //2:已确认
                    //3:生产中
                    //4:已在客托仓或退货仓
                    //99:结案

                    int MOSTATE = Convert.ToInt32(drOE["MOSTATE"] is DBNull ? 0 : drOE["MOSTATE"]);
                    switch (MOSTATE)
                    {
                        case 0:
                            CurrentStatus = "未确认";
                            break;
                        case 1:
                            CurrentStatus = "确认中";
                            break;
                        case 2:
                            CurrentStatus = "已确认";
                            break;
                        case 3:
                            CurrentStatus = "生产中";
                            break;
                        case 4:
                            CurrentStatus = "已在客托仓或退货仓";
                            break;
                        case 99:
                            CurrentStatus = "结案";
                            break;
                        default:
                            CurrentStatus = "未知状态";
                            break;
                    }

                }


                funCheckMONoCanEditCancel_M = true;
            exitfun:
                ;
            }
            catch (Exception ex)
            {
                // 一行调用，拿到完整异常字符串
                string errorMsg = GetExceptionFullMessage(ex);
                throw new Exception(errorMsg);
            }
            finally
            {
                if (objLot != null)
                {
                    objLot.Dispose();
                }


                drTemp?.Close();

                dsData?.Dispose();
                CloseConnection(cnnTemp);
            }
            return funCheckMONoCanEditCancel_M;

        }







        #endregion

        #region"Mapping反转"
        public static bool funGetPrdMapping(ref List<ArrayList> aryMessage, ref List<ArrayList> aryException, string Productid, string Eqp_id, ref int REVERSALMETHOD)
        {
            bool funGetPrdMappingref = false;
            string strSQL = default(string);
            IDbConnection cnnTemp = null;
            DbDataReader drTemp = null;

            AppSettings objSetting = new AppSettings();


            string strDataBaseType = objSetting.GetDataBaseType();
            string strConnectionString = objSetting.GetConnectionString(strDataBaseType);
            cnnTemp = CreateConnection(strConnectionString);


            DataSet dsData = new DataSet();




            try
            {
                strSQL = "select d.* from TBLWIPPRDSTATE_LQ a inner join TBLWIPLOTSTATE b on a.LotNo=b.LotNo  inner join TBLWIPLOTBASIS c on b.LotNo=c.BaseLotNo inner join TBLPRDMAPPING_LQ d on d.OPNO=b.OPNO and d.ProductNo=c.ProductNo where a.productid ='" + Productid + "'and a.state = '1'";
                drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                if (drTemp.Read())
                {
                    REVERSALMETHOD = Convert.ToInt16(drTemp["REVERSALMETHOD"].ToString());
                    funGetPrdMappingref = true;
                }
                else
                {
                    throw new Exception("产品作业站未配置反转方式!");
                }
                drTemp.Close();
            }
            catch (Exception ex)
            {
                // 一行调用，拿到完整异常字符串
                string errorMsg = GetExceptionFullMessage(ex);
                throw new Exception(errorMsg);
            }
            finally
            {
                dsData?.Dispose();
                CloseConnection(cnnTemp);
            }
            return funGetPrdMappingref;

        }

        public static bool funGetMaterialhold(ref List<ArrayList> aryMessage, ref List<ArrayList> aryException, string source, string materialno, string materiallotno, string holdstate, string creator, string date, string reasonno)
        {
            bool funGetPrdMappingref = false;
            string strSQL = default(string);
            IDbConnection cnnTemp = null;
            DbDataReader drTemp = null;

            AppSettings objSetting = new AppSettings();

            Collection CollectionSQL = new Collection();
            string strDataBaseType = objSetting.GetDataBaseType();
            string strConnectionString = objSetting.GetConnectionString(strDataBaseType);
            cnnTemp = CreateConnection(strConnectionString);


            DataSet dsData = new DataSet();

            
            string MaterialFrom;

            try
            {
                if (materiallotno == "*")
                {
                    materiallotno = "ALL";
                }
                if (holdstate == "0")
                {

                    if (source == "0")
                    {
                        //工单
                        MaterialFrom = "工单";
                        strSQL = "select * from TBLOEMOMATERIALSTATE WHERE  MaterialNo='" + materialno + "' AND QTY>0";
                        if (!string.IsNullOrWhiteSpace(materiallotno)  && !materiallotno.Equals("ALL", StringComparison.OrdinalIgnoreCase) && !materiallotno.Equals("*", StringComparison.OrdinalIgnoreCase))
                        {
                            strSQL += " AND MaterialLotNo ='" + materiallotno + "'";
                           
                        }
                    }
                    else
                    {
                        MaterialFrom = "虚拟库";
                        strSQL = "select * from TBLINVWIPINVENTORY_RAW WHERE  MaterialNo='" + materialno + "' AND QTY>0";
                        if (!string.IsNullOrWhiteSpace(materiallotno) && !materiallotno.Equals("ALL", StringComparison.OrdinalIgnoreCase) && !materiallotno.Equals("*", StringComparison.OrdinalIgnoreCase))
                        {
                            strSQL += " AND MaterialLotNo ='" + materiallotno + "'";

                        }
                    }
                    drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                    if (drTemp.Read())
                    {
                        
                        strSQL = "INSERT INTO TBLCUSHOLDMATERIALSTATE ( MATERIALFROM ,  MATERIALNO ,  MATERIALLOTNO ,  REASONNO ,  CREATOR  ) VALUES ('" + MaterialFrom + "', '" + materialno + "', '" + materiallotno + "', '" + reasonno + "', '" + creator + "')";
                        CollectionSQL.Add(strSQL);
                        strSQL = "INSERT INTO TBLCUSHOLDMATERIALLOG (  MATERIALFROM ,  MATERIALNO ,  MATERIALLOTNO ,  HOLDSTATE ,  REASONNO ,  CREATOR) VALUES ('" + MaterialFrom + "', '" + materialno + "', '" + materiallotno + "',1, '" + reasonno + "', '" + creator + "')";
                        CollectionSQL.Add(strSQL);
                    }
                    else
                    {
                        throw new Exception("物料不存在或数量不足!");
                        
                    }
                    drTemp.Close();
                }
                else
                {
                    DataTable dtdata = new DataTable();
                    if (source == "0")
                    {
                        //工单
                        MaterialFrom = "工单";
                    }
                    else
                    {
                        MaterialFrom = "虚拟库";
                    }
                        strSQL = "select * from TBLCUSHOLDMATERIALSTATE where  1=1  ";
                    if (materiallotno != "*")
                    {
                        strSQL += " AND MaterialNo='" + materialno + "'";
                    }
                    if (MaterialFrom != "*")
                    {
                        strSQL += " AND MaterialFrom='" + MaterialFrom + "'";
                    }
                    if ( reasonno != "ERPhold")
                    {
                        strSQL += " AND REASONNO='" + reasonno + "'";
                    }
                    if (materiallotno != "*")
                    {
                        strSQL += " AND MaterialLotNo='" + materiallotno + "'";
                    }
                    else
                    {
                        strSQL += " AND MaterialLotNo='ALL'";
                    }
                    ExecuteSQLQuery_Adapter(strSQL, dtdata, cnnTemp);

                    if (dtdata.Rows.Count == 0)
                    {
                        throw new Exception("物料未被Hold!");
                    }
                    foreach (DataRow dr in dtdata.Rows)
                    {
                        strSQL = "delete from TBLCUSHOLDMATERIALSTATE where MaterialNo='" + dr["MATERIALNO"] + "' AND MaterialFrom='" + dr["MaterialFrom"] + "' AND REASONNO='" + dr["REASONNO"] + "' AND MaterialLotNo='" + dr["MaterialLotNo"] + "'   AND Creator='" + dr["Creator"] + "'";
                        CollectionSQL.Add(strSQL);
                        strSQL = "INSERT INTO TBLCUSHOLDMATERIALLOG (  MATERIALFROM ,  MATERIALNO ,  MATERIALLOTNO ,  HOLDSTATE ,  REASONNO ,  CREATOR) VALUES ('" + dr["MaterialFrom"] + "', '" + dr["MATERIALNO"] + "', '" + dr["MaterialLotNo"] + "',0, '" + dr["REASONNO"] + "', '" + creator + "')";
                        CollectionSQL.Add(strSQL);
                    }
                   
                }
                ExecuteSQLNoneQuery_UPD(strDataBaseType, strConnectionString, CollectionSQL);
                funGetPrdMappingref=true;
            }
            catch (Exception ex)
            {
                // 一行调用，拿到完整异常字符串
                string errorMsg = GetExceptionFullMessage(ex);
                throw new Exception(errorMsg);
            }
            finally
            {
                dsData?.Dispose();
                CloseConnection(cnnTemp);
            }
            return funGetPrdMappingref;

        }

        #endregion

        #region"钢片绑定"
        public static bool funSteelBind(ref List<ArrayList> aryMessage, ref List<ArrayList> aryException,
            string equipmentno, string steelno, string carrierno, ref string lotno)
        {
            bool result = false;
            IDbConnection cnnTemp = null;
            DataSet dsTemp = new DataSet();
            AppSettings objSetting = new AppSettings();
            try
            {
                string strDataBaseType = objSetting.GetDataBaseType();
                string strConnectionString = objSetting.GetConnectionString(strDataBaseType);

                // 1. 根据设备编号查询 TBLWIPTEMP_Equipment 获取 LotNo
                cnnTemp = CreateConnection(strConnectionString);
                string strSQL;
                lotno = carrierno;

                // 2. 检查钢片是否已被绑定（被其他批次占用）
                dsTemp.Tables.Clear();
                strSQL = string.Format("SELECT STEELNO, LOTNO, STEELSTATE FROM TBLCUSSTEELSTATE WHERE STEELNO = '{0}'", steelno);
                ExecuteSQLQuery_Adapter(strSQL, dsTemp, "tblSteel", cnnTemp);

                if (dsTemp.Tables["tblSteel"].Rows.Count > 0)
                {
                    string existLotNo = dsTemp.Tables["tblSteel"].Rows[0]["LOTNO"].ToString();
                    if (!string.IsNullOrEmpty(existLotNo) && existLotNo != lotno)
                    {
                        throw new Exception("钢片已被绑定：" + steelno);
                    }
                }

                // 3. 检查该批次是否已绑定其他钢片，有则先解绑
                dsTemp.Tables.Clear();
                strSQL = string.Format("SELECT STEELNO FROM TBLCUSSTEELSTATE WHERE LOTNO = '{0}' AND STEELSTATE = '1' AND STEELNO != '{1}'", lotno, steelno);
                ExecuteSQLQuery_Adapter(strSQL, dsTemp, "tblOldSteel", cnnTemp);

                var CollectionSQL = new Collection();
                foreach (DataRow dr in dsTemp.Tables["tblOldSteel"].Rows)
                {
                    string oldSteelNo = dr["STEELNO"].ToString();
                    CollectionSQL.Add(string.Format("UPDATE TBLCUSSTEELSTATE SET LOTNO = '', STEELSTATE = '0' WHERE STEELNO = '{0}'", oldSteelNo));
                    CollectionSQL.Add(string.Format("INSERT INTO TBLCUSSTEELLOG(STEELNO, LOTNO, STEELSTATE, CREATOR, CREATDATE) VALUES('{0}', '{1}', '0', 'DS', SYSDATE)", oldSteelNo, lotno));
                }

                // 4. 绑定新钢片
                CollectionSQL.Add(string.Format("UPDATE TBLCUSSTEELSTATE SET LOTNO = '{0}', STEELSTATE = '1' WHERE STEELNO = '{1}'", lotno, steelno));
                CollectionSQL.Add(string.Format("INSERT INTO TBLCUSSTEELLOG(STEELNO, LOTNO, STEELSTATE, CREATOR, CREATDATE) VALUES('{0}', '{1}', '1', 'DS', SYSDATE)", steelno, lotno));

                ExecuteSQLNoneQuery_UPD(strDataBaseType, strConnectionString, CollectionSQL);

                result = true;
            }
            catch (Exception ex)
            {
                string errorMsg = GetExceptionFullMessage(ex);
                throw new Exception(errorMsg);
            }
            finally
            {
                CloseConnection(cnnTemp);
                dsTemp?.Dispose();
            }

            return result;
        }
        #endregion

        #region" 产品BOM信息接口"
        // OPNO,NODENO,STDQTY,DECREASERATE,SUBMTL
        public static bool GetProdBOMInfo(ref List<ArrayList> aryMessage, ref List<ArrayList> aryException, string PRODUCTNO = defString,
            string PRODUCTVERSION = defString, BomRequest bomRequest = null)
        {
            //                tcEAI_C.clsERP.AddUser(ref strMessage, ref strException, PRODUCTNO, PRODUCTNAME, PRODUCTVERSION, CREATOR, CREATEDATE, dtDPPara);
            bool result = false;

            //  int i, j;
            string strSQLAddField, strSQLAddValue;
            Collection MTLCollection = new Collection();
            Collection CollectionSQL = new Collection();


            string strSQL = "";

            IDbConnection cnnTemp = null;
            DbDataReader drTemp, drTemp1;
            AppSettings objSetting = new AppSettings();
            string strDataBaseType = objSetting.GetDataBaseType();
            string strConnectionString = objSetting.GetConnectionString(strDataBaseType);

            bool blnAdd = false; ;

            try
            {
                try
                {
                    // //Create connection
                    cnnTemp = CreateConnection(strConnectionString);

                    try
                    {

                        //add by 14166 for 产品已核准 则不允许进行参数收集
                        strSQL = " SELECT * FROM TBLPRDPRODUCTBASIS WHERE PRODUCTNO='" + PRODUCTNO + "' And PRODUCTVERSION='" + PRODUCTVERSION + "' ";
                        drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                        if (drTemp.Read())
                        {
                            //if (Convert.ToInt32(drTemp["ISSUESTATE"]) == 2)
                            //{
                            //    clsERP.combineException(ref aryException, null, null, "[%产品编号:" + PRODUCTNO + " ,产品版本:" + PRODUCTVERSION + " 产品已核准,不允许产品Bom信息收集！%]");
                            //    // goto exitfun;
                            //}

                        }
                        else
                        {
                            //手动增加一个产品主档
                            strSQL = "INSERT INTO TBLPRDPRODUCTBASIS (PRODUCTNO,PRODUCTVERSION,PRODUCTNAME,PRODUCTTYPE,ISSUESTATE,\r\n  CREATOR,CREATEDATE,DESCRIPTION,CURVERSION, UNITNO, UNITTYPE, SPECNO,CARTONQTY,PALLETQTY,BOXQTY,LOTSTDQTY,  CUSTOMERNO ,  DEVICENO ,  ECNCONTROL   ,  BATCHECNLOCK ,  DATASTAMP   ,  UNITID ,  PANLEID ) VALUES ('"+ PRODUCTNO +"', '"+ PRODUCTVERSION +"', '"+ PRODUCTNO +"', 'null', '0', 'ERP',sysdate, 'ERP新增', '1', '', '', 'N/A', '0', '0', '0', '0','N/A', 'N/A', 'N', '0', '0', '','')";
                            CollectionSQL.Add(strSQL);
                            //   clsERP.combineException(ref aryException, null, null, "[%产品编号:" + PRODUCTNO + " ,产品版本:" + PRODUCTVERSION + " 产品不存在,不允许产品Bom信息收集！%]");
                            //goto exitfun;
                        }
                        drTemp.Close();



                        // ----- Exception => Fail -----
                     
                        foreach (Bomlist dr in bomRequest.Bomlist)
                        {
                            int NodeType = 0, PUTINPLACETYPE = 0;
                            string UnitNo = defString, NODEVERSION = defString;


                            strSQL = "delete from TBLPRDBOM where ProductNo='" + PRODUCTNO + "' and PRODUCTVERSION='" + PRODUCTVERSION + "' and NodeNo='" + dr.MaterialNo.ToString() + "' and OPNO='" + dr.OpNo.ToString() + "'";
                            CollectionSQL.Add(strSQL);
                            strSQL = "delete from TBLPRDBOMMATERIALGROUP where ProductNo='" + PRODUCTNO + "' and PRODUCTVERSION='" + PRODUCTVERSION + "' and NodeNo='" + dr.MaterialNo.ToString() + "' and OPNO='" + dr.OpNo.ToString() + "'";
                            CollectionSQL.Add(strSQL);

                            strSQL = " SELECT * FROM TBLPRDPRODUCTBASIS WHERE PRODUCTNO='" + dr.MaterialNo.ToString() + "'";
                            drTemp = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                            if (drTemp.Read())
                            {
                                //说明是半成品物料
                                NodeType = 1;
                                PUTINPLACETYPE = 4;
                                UnitNo = drTemp["UnitNo"].ToString();
                                NODEVERSION = "*";
                            }
                            else
                            {
                                //再检查是不是物料
                                strSQL = " SELECT * FROM TBLMTLMATERIALBASIS WHERE MATERIALNO='" + dr.MaterialNo.ToString() + "'";
                                drTemp1 = ExecuteSQLQuery_Reader(strSQL, cnnTemp);
                                if (drTemp1.Read())
                                {
                                    NodeType = 0;
                                    PUTINPLACETYPE = 2;
                                    UnitNo = drTemp1["UnitNo"].ToString();
                                    NODEVERSION = "N/A";
                                }
                                else
                                {
                                    throw new Exception("[%NODENO:" + dr.MaterialNo.ToString() + " 既不是半成品也不是物料,信息错误！%]");
                                
                                    //goto exitfun;
                                }
                                drTemp1.Close();
                            }
                            drTemp.Close();
                            strSQL = "INSERT INTO TBLPRDBOM  (PRODUCTNO, PRODUCTVERSION, NODENO, NODETYPE, STDQTY, UNITNO, DECREASERATE, OPNO, SPECIFIED, CREATOR, CREATEDATE, NODEVERSION, PUTINPLACETYPE,AB,PositionCode)  " +
                                    "VALUES('" + PRODUCTNO + "', '" + PRODUCTVERSION + "', '" + dr.MaterialNo.ToString() + "', '" + NodeType + "', '" + dr.StdQty.ToString() + "', '" + UnitNo + "', '" + dr.DecreaseRate.ToString() + "', '" + dr.OpNo.ToString() + "', '0', 'ERP', sysdate, '" + NODEVERSION + "', '" + PUTINPLACETYPE + "', '" + dr.AB.ToString() + "', '" + dr.PositionCode.ToString() + "')";
                            CollectionSQL.Add(strSQL);


                            if (!string.IsNullOrEmpty(dr.SubstituteMaterialNo.ToString()) && NodeType == 0)
                            {
                                string[] SUBMTLS = dr.SubstituteMaterialNo.ToString().Split(',');
                                for (int i = 0; i < SUBMTLS.Length; i++)
                                {
                                    strSQL = "INSERT INTO TBLPRDBOMMATERIALGROUP (PRODUCTNO, PRODUCTVERSION, NODENO, OPNO, MATERIALNO) VALUES('" + PRODUCTNO + "', '" + PRODUCTVERSION + "', '" + dr.MaterialNo + "','" + dr.OpNo.ToString() + "','" + SUBMTLS[i].ToString() + "') ";
                                    CollectionSQL.Add(strSQL);

                                }

                            }
                        }


                        // 呼叫執行SQL指令 
                        ExecuteSQLNoneQuery_UPD(strDataBaseType, strConnectionString, CollectionSQL);
                        result = true;

                       
                    }
                    catch (Exception ex)
                    {
                        // trans.Rollback()
                        string errorMsg = GetExceptionFullMessage(ex);
                        throw new Exception(errorMsg);
                    }
                }
                catch (Exception ex)
                {
                    string errorMsg = GetExceptionFullMessage(ex);
                    throw new Exception(errorMsg);
                }

          
            }
            catch (Exception ex)
            {
                string errorMsg = GetExceptionFullMessage(ex);
                throw new Exception(errorMsg);
            }
            finally
            {
                CloseConnection(cnnTemp);
            }


            return result;
        }

        #endregion


        #region
        public static bool CheckReelQty(ref List<ArrayList> aryMessage, ref List<ArrayList> aryException, string reelId, string storageCode, ref string mcode,
    ref string qty, ref string vendordate, ref string vendorcode, ref string manufacturer, ref string vendorlotno, ref string snscale, ref bool everExisted)
        {
            bool CheckReelQty = default(bool);
            string strSQL = "";

            IDbConnection cnnTemp = null;
            DbDataReader drTemp, drTemp1;
            AppSettings objSetting = new AppSettings();
            string strDataBaseType = objSetting.GetDataBaseType();
            string strConnectionString = objSetting.GetConnectionString(strDataBaseType);
            try
            {
                // //Create connection
                cnnTemp = CreateConnection(strConnectionString);
                DataTable dtMaterial = new DataTable();
                //供应商生产日期取自制造日期栏，依WMS送入格式回传YYYYMMDD字串
                //供应商编码、生产厂家、供应商批次、09条码四栏同表取数，栏位由WMS进料写入
                strSQL = $"select MATERIALNO,QTY,To_Char(MANUFACTUREDATE,'YYYYMMDD') as VENDORDATE,VENDORCODE,MANUFACTURER,VENDERLOTNO,SNSCALE from TBLINVWIPINVENTORY_RAW where MATERIALLOTNO = '{reelId}'";
                //仓位有传则以仓别+料卷定位，未传则只以料卷号定位
                if (storageCode != "")
                {
                    strSQL += $" and INVENTORYNO = '{storageCode}'";
                }
                ExecuteSQLQuery_Adapter(strSQL, dtMaterial, cnnTemp);
                if (dtMaterial.Rows.Count > 0)
                {
                    mcode = dtMaterial.Rows[0]["MATERIALNO"].ToString();
                    qty = dtMaterial.Rows[0]["QTY"].ToString();
                    vendordate = dtMaterial.Rows[0]["VENDORDATE"].ToString();
                    //栏位未写入时为NULL，ToString後即空字串，与既有回传空值行为一致
                    vendorcode = dtMaterial.Rows[0]["VENDORCODE"].ToString();
                    manufacturer = dtMaterial.Rows[0]["MANUFACTURER"].ToString();
                    vendorlotno = dtMaterial.Rows[0]["VENDERLOTNO"].ToString();
                    snscale = dtMaterial.Rows[0]["SNSCALE"].ToString();
                    everExisted = true;
                    CheckReelQty = true;
                    goto exitfun;
                }

                //线边仓查无现存量时，再查料卷日志判断该料卷是否曾经存在，
                //用以区分「已扣光」与「卷号根本不存在」两种情形
                DataTable dtLog = new DataTable();
                strSQL = $"select count(1) as CNT from TBLINVWIPINVENTORY_RAWLOG where MATERIALLOTNO = '{reelId}'";
                ExecuteSQLQuery_Adapter(strSQL, dtLog, cnnTemp);
                if (dtLog.Rows.Count > 0 && Convert.ToDecimal(dtLog.Rows[0]["CNT"]) > 0)
                {
                    everExisted = true;
                }

            exitfun:
                ;
            }
            catch (Exception ex)
            {
                string errorMsg = GetExceptionFullMessage(ex);
                throw new Exception(errorMsg);
            }
            finally
            {
                CloseConnection(cnnTemp);
            }


            return CheckReelQty;
        }


        #endregion


        #region MES退料
        /// <summary>
        /// WMS退料：依REELINFO逐笔扣减线边仓料卷数量，数量不够则清空、不报错
        /// </summary>
        /// <param name="mvdCode">退料单号</param>
        /// <param name="moCode">工单号（仅落客製退料日志备查）</param>
        /// <param name="fromStorage">供应仓，对应线边仓的INVENTORYNO</param>
        /// <param name="toStorage">收货仓，仅记录不入账</param>
        /// <param name="dtReelInfo">料卷明细：REELID／MCODE／QTY</param>
        public static bool ReelReturn(ref List<ArrayList> aryMessage, ref List<ArrayList> aryException,
            string mvdCode, string moCode, string fromStorage, string toStorage,
            string userCode, string mDate, string mTime, DataTable dtReelInfo)
        {
            bool ReelReturn = default(bool);
            string strSQL = default(string);
            IDbConnection cnnTemp = null;

            AppSettings objSetting = new AppSettings();
            var CollectionSQL = new Collection();

            string strDataBaseType = objSetting.GetDataBaseType();
            string strConnectionString = objSetting.GetConnectionString(strDataBaseType);
            cnnTemp = CreateConnection(strConnectionString);

            //同一料卷可能在REELINFO内出现多笔，须逐笔累加扣减。
            //因所有SQL累积到最后才一次提交，若每笔都回资料库取现存量，
            //第二笔会取到未扣减前的数量而少扣，故在内存累计剩余量。
            var dicLeftQty = new Dictionary<string, decimal>();
            //以料卷为单位做扣减与快照时，保持 REELINFO 的出现顺序
            var lstKeyOrder = new List<string>();
            var dicMCode = new Dictionary<string, string>();
            var dicReelId = new Dictionary<string, string>();

            try
            {
                if (dtReelInfo.Rows.Count == 0)
                {
                    throw new Exception("无明细数据！");
                }

                //操作日期与时间为数字栏，未传则存空值
                string strMDate = mDate.Trim() == "" ? "null" : mDate.Trim();
                string strMTime = mTime.Trim() == "" ? "null" : mTime.Trim();

                for (int i = 0; i <= dtReelInfo.Rows.Count - 1; i++)
                {
                    string strReelId = dtReelInfo.Rows[i]["REELID"].ToString();
                    string strMCode = dtReelInfo.Rows[i]["MCODE"].ToString();
                    string strKey = fromStorage + "|" + strMCode + "|" + strReelId;

                    //请求扣减量
                    decimal REQQTY = 0;
                    if (dtReelInfo.Rows[i]["QTY"].ToString() != "")
                    {
                        REQQTY = Convert.ToDecimal(dtReelInfo.Rows[i]["QTY"]);
                    }
                    if (REQQTY <= 0)
                    {
                        throw new Exception($"{strReelId}扣减数量不能为0或者负数！");
                    }

                    //现存量：以仓别＋物料＋料卷三段键定位
                    decimal CURQTY = 0;
                    if (dicLeftQty.ContainsKey(strKey))
                    {
                        CURQTY = dicLeftQty[strKey];
                    }
                    else
                    {
                        DataTable dtRaw = new DataTable();
                        strSQL = $"select QTY from TBLINVWIPINVENTORY_RAW where INVENTORYNO = '{fromStorage}' and MATERIALNO = '{strMCode}' and MATERIALLOTNO = '{strReelId}'";
                        ExecuteSQLQuery_Adapter(strSQL, dtRaw, cnnTemp);
                        if (dtRaw.Rows.Count == 0)
                        {
                            throw new Exception($"{strReelId}在仓位{fromStorage}找不到对应的料卷！");
                        }
                        if (dtRaw.Rows[0]["QTY"].ToString() != "")
                        {
                            CURQTY = Convert.ToDecimal(dtRaw.Rows[0]["QTY"]);
                        }
                    }

                    //数量不够则清空、不用报错：实扣量最多只扣到现存量，
                    //不套用标准「保留负数」的写法，短扣差额由客製退料日志的请求量与实扣量留证
                    decimal REALQTY = REQQTY;
                    if (REQQTY > CURQTY)
                    {
                        REALQTY = CURQTY;
                    }
                    decimal LEFTQTY = CURQTY - REALQTY;
                    dicLeftQty[strKey] = LEFTQTY;
                    if (!lstKeyOrder.Contains(strKey))
                    {
                        lstKeyOrder.Add(strKey);
                        dicMCode[strKey] = strMCode;
                        dicReelId[strKey] = strReelId;
                    }

                    //① 写客製退料日志：逐笔记录请求量与实扣量，供短扣对帐；收货仓仅记录不入账
                    strSQL = @"INSERT INTO TBLINVREELRETURNLOG_LQ(MVDCODE, MOCODE, REELID, MCODE, FROMSTORAGE, TOSTORAGE, REQQTY, REALQTY, USERCODE, MDATE, MTIME, EVENTTIME) ";
                    strSQL += $" VALUES('{mvdCode}','{moCode}','{strReelId}','{strMCode}','{fromStorage}','{toStorage}',{REQQTY},{REALQTY},'{userCode}',{strMDate},{strMTime},To_Date('" + Strings.Format(DateTime.Now, "yyyy/MM/dd HH:mm:ss") + "','YYYY/MM/DD HH24:MI:SS'))";
                    CollectionSQL.Add(strSQL);
                }

                //料卷日志与扣减以「料卷」为单位各做一次，不随 REELINFO 逐笔做：
                //TBLINVWIPINVENTORY_RAWLOG 的主键是 (INVENTORYNO, MATERIALNO, MATERIALLOTNO, EVENTTIME)，
                //EVENTTIME 只到秒，同一料卷若在同批内写入多列快照必然撞主键 ORA-00001
                //（规格 2.2 请求样例的 REELINFO 即同一 REELID 连续两笔）。
                //且标准的 RAWLOG 语义本就是「变更后快照」＝最终状态一列，故此处只留最终一列。
                foreach (string strKey in lstKeyOrder)
                {
                    string strMCode = dicMCode[strKey];
                    string strReelId = dicReelId[strKey];
                    decimal LEFTQTY = dicLeftQty[strKey];

                    //② 扣减线边仓现存量（累加扣减后的最终数量）
                    strSQL = $"UPDATE TBLINVWIPINVENTORY_RAW SET QTY = {LEFTQTY} WHERE INVENTORYNO = '{fromStorage}' AND MATERIALNO = '{strMCode}' AND MATERIALLOTNO = '{strReelId}'";
                    CollectionSQL.Add(strSQL);

                    //③ 写料卷日志（扣减后整列快照），必须排在删除资料列之前，否则删除后即无从取数
                    strSQL = @"INSERT INTO TBLINVWIPINVENTORY_RAWLOG(INVENTORYNO, MATERIALNO, MATERIALLOTNO, UNITNO, QTY, MATERIALTYPE, INPUTDATE,MANUFACTUREDATE,RECEIPTNO,REELID,EXDATE,EVENTTIME,DATECODE) ";
                    strSQL += " SELECT INVENTORYNO, MATERIALNO, MATERIALLOTNO, UNITNO, QTY, MATERIALTYPE, INPUTDATE,MANUFACTUREDATE,RECEIPTNO,REELID,EXDATE,To_Date('" + Strings.Format(DateTime.Now, "yyyy/MM/dd HH:mm:ss") + "','YYYY/MM/DD HH24:MI:SS'),DATECODE ";
                    strSQL += $" FROM TBLINVWIPINVENTORY_RAW WHERE INVENTORYNO = '{fromStorage}' AND MATERIALNO = '{strMCode}' AND MATERIALLOTNO = '{strReelId}'";
                    CollectionSQL.Add(strSQL);

                    //④ 扣光则删除资料列
                    if (LEFTQTY <= 0)
                    {
                        strSQL = $"DELETE FROM TBLINVWIPINVENTORY_RAW WHERE INVENTORYNO = '{fromStorage}' AND MATERIALNO = '{strMCode}' AND MATERIALLOTNO = '{strReelId}' AND QTY = 0";
                        CollectionSQL.Add(strSQL);
                    }
                }

                if (CollectionSQL.Count > 0)
                {
                    ExecuteSQLNoneQuery_UPD(objSetting.GetDataBaseType(), objSetting.GetConnectionString(), CollectionSQL);
                }

                ReelReturn = true;

            exitfun:
                ;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                CloseConnection(cnnTemp);
            }
            return ReelReturn;

        }
        #endregion
    }
}