即时库存

yhj

2025-08-15 b3a5b4165d585463af639c35931d6c2962a795a0

 JF_DataexchangeServer/DataexchangeServer/HandleData/AE_MES_ERP.cs

@@ -86,6 +86,7 @@
                //即时库存
                case "TB_ERPTOMES_JSKC1":
                    result = TB_ERPTOMES_JSKC1(inputds, operatype, ref msg);
                  //  result = TB_ERPTOMES_JSKC(inputds, operatype, ref msg);
                    break;

                //9.ERPBOMA(同步推送)
@@ -1509,13 +1510,22 @@
                            {

                                // 检查下游单据 WOMDAA 是否存在影响同步的数据
                                string sqlCheck = string.Format(@"SELECT COUNT(*) FROM WOMDAA WHERE DAA014 = '{0}' AND DAA011 > 0", dr["TLA001"]);
                                /* string sqlCheck2 = string.Format(@"SELECT COUNT(*) FROM WOMDAA WHERE DAA014 = '{0}' AND FSTATUS = 1", dr["TLA001"]);
                                 int downstreamCount2 = Convert.ToInt32(_sqlHelper.GetSingle(sqlCheck2));
 */

                                //任务数只能变大不能变小
                                string sqlCheck = string.Format(@"SELECT nvl(CAA012,0) FROM womcaa WHERE caa001 = '{0}' ", dr["TLA001"]);
                                int downstreamCount = Convert.ToInt32(_sqlHelper.GetSingle(sqlCheck));

                                // 如果存在下游单据且数量 > 0，则返回失败
                                if (downstreamCount > 0)
                                string sqlgds = string.Format(@"SELECT sum(daa008) FROM WOMDAA WHERE DAA014 = '{0}' AND FSTATUS = 1", dr["TLA001"]);
                                int gds = Convert.ToInt32(_sqlHelper.GetSingle(sqlgds));
                                
                                int newTaskCount = Convert.ToInt32(dr["TLA012"]);
                                // 如果已排产工单数大于新工单数，则返回失败,且排除未同步过的
                                if (gds > newTaskCount && downstreamCount > 0 && gds != 0)
                                {
                                    ErrorMsg = string.Format("存在下游单据，无法同步数据: 主表记录 TLA001 = {0}", dr["TLA001"]);
                                    ErrorMsg = string.Format("存在已排工单数大于任务总数，无法同步数据: 主表记录 TLA001 = {0}", dr["TLA001"]);
                                    return false;
                                }
                                //2025-01-22 修改单据状态审核/反审核
@@ -1584,6 +1594,7 @@
                        }
                        if (dt.TableName == "ERPWWTLA")
                        {
                            
                            #region 投料单主表的处理                           
                            foreach (DataRow dr in dt.Rows)
                            {
@@ -1617,7 +1628,7 @@
                                     }*/
                                   string SQLF = string.Format(@"DELETE WOMCAA WHERE CAA001='{0}'", dr["WWTLA001"]);
                                    int B = _sqlHelper.ExecuteNonQuery(SQLF);
                                    string SQLD = string.Format(@"DELETE WOMCAB WHERE CAB001='{0}' and WWLL_TYPE=1", dr["ERPID"]);
                                    string SQLD = string.Format(@"DELETE WOMCAB WHERE CAB001='{0}' and WWLL_TYPE is not null", dr["ERPID"]);
                                    int D = _sqlHelper.ExecuteNonQuery(SQLD);

                                    sql = string.Format(@" INSERT INTO WOMCAA(ID,ERPID,CAA001,CAA002,CAA003,CAA004,CAA005,CAA006,CAA007,CAA008,CAA009,CAA010,CAA011,CAA012,CAA013,CAA014,CAA015,CAA016,CAA018,CAA019,CAA020,CAA021,FSTATUS,CAA022,GYS_NO,XS_NO,CG_NO,EBELN_LINE,tsbs,FSTATES,TYPEA)
@@ -1627,7 +1638,7 @@
                                {
                                   string SQLF = string.Format(@"DELETE WOMCAA WHERE CAA001='{0}'", dr["WWTLA001"]);
                                    int B = _sqlHelper.ExecuteNonQuery(SQLF);
                                    string SQLD = string.Format(@"DELETE WOMCAB WHERE CAB001='{0}' and WWLL_TYPE=1", dr["ERPID"]);
                                    string SQLD = string.Format(@"DELETE WOMCAB WHERE CAB001='{0}' and WWLL_TYPE  is not null", dr["ERPID"]);
                                    int D = _sqlHelper.ExecuteNonQuery(SQLD);
                                }
                            }
@@ -2125,13 +2136,44 @@
                                    SQLStringList.Add(sqlcr);
                                }
                            }

                            

                           // string sqlcr = string.Format(@" insert into 
                              

                        }
                    }

                        if ( dr["JA003"].ToString().Equals("反结案 ", StringComparison.OrdinalIgnoreCase))
                        {
                            string daa014 = dr["JA001"].ToString();
                            string sqlja = string.Format(@"update womdaa set daa018='反完工',WORK_BY='',DAA017='' where daa018='完工' and WORK_BY='ERP结案' and daa014='" + daa014 + "'");
                            SQLStringList.Add(sqlja);
                            //查询要插入工单信息
                            string SqlStr = string.Format(@"Select a.daa001,j.caa015,s.ITEM_NO,s.ITEM_NAME,a.daa029,s.ITEM_MODEL,nvl(max(lv.SQE_NUM),0),s.id
from womdaa a left join GD_LVL lv on a.daa001=lv.DAA001
         left join womcaa j
on A.DAA021 = j.CAA020 and j.CAA001 = A.DAA014 left join v_mes_items s on a.daa002=s.id  WHERE a.daa018 ='完工' and  WORK_BY='ERP结案' and 
        a.daa014 = '" + daa014 + @"'
group by a.daa001, j.caa015, s.ITEM_NO, s.ITEM_NAME, a.daa029, s.ITEM_MODEL, s.id ");
                            DataTable tbQueue = _sqlHelper.ExecuteDataTable(SqlStr);
                            //有数据
                            if (tbQueue.Rows.Count > 0)
                            {
                                foreach (DataRow item in tbQueue.Rows)
                                {
                                    var daa001 = item[0];
                                    /* var daa001 = item[1];
                                     var daa001 = item[2];
                                     var daa001 = item[3];
                                     var daa001 = item[4];
                                     var daa001 = item[5];*/
                                    //insert
                                    string sqlcr = string.Format(@" insert into GD_LVL(id, caa015, daa001, item_no, item_id, item_name, item_model, xt, xg_state, sg_time, xg_user, sqe_num) values (
                            F_GETSEQNEXTVALUE('GD_LVL'), '{0}','{1}','{2}','{3}','{4}','{5}' ,'{6}','反完工',sysdate,'ERP反结案','{7}') ", item[1], item[0], item[2], item[7], item[3], item[5], item[4], item[6]);
                                    SQLStringList.Add(sqlcr);
                                }
                            }
                        }


                        }
                    return _sqlHelper.ExecuteSqlTran(SQLStringList);
                    //插入前先删除重复键
                    // DeleteDataBeforeInsert("COMMAA", "ERPID", string.Join("','", IDList), "");
@@ -2384,7 +2426,9 @@
                                DataRow dr = (DataRow)obj2;
                                if (dr["FDOCUMENTSTATUS"].ToString() == "C")
                                {
                                    string sqlT = string.Format("SELECT count(*) cou FROM MES_ROH_IN WHERE EBELN='{0}'", dr["CGA001"]);

                               
                                        string sqlT = string.Format("SELECT count(*) cou FROM MES_ROH_IN WHERE EBELN='{0}'", dr["CGA001"]);
                                    int c = Convert.ToInt32(this._sqlHelper.GetSingle(sqlT));
                                    bool flag4 = c != 0;
                                    if (flag4)
@@ -2393,8 +2437,7 @@
                                        {
                                            string SQLF = string.Format("DELETE FROM MES_ROH_IN WHERE EBELN='{0}'", dr["CGA001"]);
                                            int B = this._sqlHelper.ExecuteNonQuery(SQLF);
                                            string SQLD = string.Format("DELETE FROM  MES_ROH_IN_DATA WHERE EBELN='{0}'", dr["CGA001"]);
                                            int D = this._sqlHelper.ExecuteNonQuery(SQLD);
                                          
                                        }
                                        catch (Exception)
                                        {
@@ -2462,12 +2505,42 @@
                                foreach (object obj3 in dt.Rows)
                                {
                                    DataRow dr2 = (DataRow)obj3;
                                    string erpid = dr2["ERPID"].ToString();
                                    //普通料
                                    string urgencyStr = this._sqlHelper.GetSingle(string.Format(
                                        "SELECT URGENCY FROM MES_ROH_IN_DATA WHERE ERPID='{0}'", erpid))?.ToString();
                                  //采购要求供应商送货时间
                                    string cgb024Str = this._sqlHelper.GetSingle(string.Format(
                                        "SELECT CGB024 FROM MES_ROH_IN_DATA WHERE ERPID='{0}'", erpid))?.ToString();
                                   //供应商承诺交货时间
                                    string gys_jhtimeStr = this._sqlHelper.GetSingle(string.Format(
                                        "SELECT GYS_JHTIME FROM MES_ROH_IN_DATA WHERE ERPID='{0}'", erpid))?.ToString();
                                    //跟进备注
                                    string gbbz = this._sqlHelper.GetSingle(string.Format(
                                        "SELECT GBBZ FROM MES_ROH_IN_DATA WHERE ERPID='{0}'", erpid))?.ToString() ?? "";

                                    string cgb = this._sqlHelper.GetSingle(string.Format("SELECT CGB FROM MES_ROH_IN_DATA WHERE ERPID='{0}'", erpid))?.ToString() ?? "";

                                    string cgb025Str = this._sqlHelper.GetSingle(string.Format("SELECT CGB025 FROM MES_ROH_IN_DATA WHERE ERPID='{0}'", erpid))?.ToString();
                                    string cgb026 = this._sqlHelper.GetSingle(string.Format("SELECT CGB026 FROM MES_ROH_IN_DATA WHERE ERPID='{0}'", erpid))?.ToString() ?? "";
                                    string cgb028Str = this._sqlHelper.GetSingle(string.Format("SELECT CGB028 FROM MES_ROH_IN_DATA WHERE ERPID='{0}'", erpid))?.ToString();


                                    // 转换类型
                                    string urgency = string.IsNullOrWhiteSpace(urgencyStr) ? "0" : urgencyStr; // number 不加引号
                                    string cgb024 = string.IsNullOrWhiteSpace(cgb024Str) ? "NULL" : $"TO_DATE('{Convert.ToDateTime(cgb024Str):yyyy-MM-dd HH:mm:ss}', 'yyyy-MM-dd HH24:MI:SS')";
                                    string gys_jhtime = string.IsNullOrWhiteSpace(gys_jhtimeStr) ? "NULL" : $"TO_DATE('{Convert.ToDateTime(gys_jhtimeStr):yyyy-MM-dd HH:mm:ss}', 'yyyy-MM-dd HH24:MI:SS')";
                                    string cgb025 = string.IsNullOrWhiteSpace(cgb025Str) ? "0" : cgb025Str;
                                    string cgb028 = string.IsNullOrWhiteSpace(cgb028Str) ? "NULL" : $"TO_DATE('{Convert.ToDateTime(cgb028Str):yyyy-MM-dd HH:mm:ss}', 'yyyy-MM-dd HH24:MI:SS')";
                                    string SQLD = string.Format("DELETE FROM  MES_ROH_IN_DATA WHERE ERPID='{0}'", erpid);
                                    int D = this._sqlHelper.ExecuteNonQuery(SQLD);
                                 
                                    // 判断 CGB019 是否等于 MENGE，若是则 CGB025 为 1，否则为 0
                                  int cgb025Value = (dr2["CGB008"].ToString() == dr2["CGB014"].ToString()) ? 1 : 0;
                                    int cgb025Value = (dr2["CGB008"].ToString() == dr2["CGB014"].ToString()) ? 1 : 0;
                                  //  int cgb025Value2 = dr2["CGB008"] == dr2["CGB014"] ? 1 : 0;

                                    string sql2 = string.Format(" INSERT INTO MES_ROH_IN_DATA(ID,ERPID,EBELN,CGB002,MATNR,CGB004,CGB005,CGB006,CGB007,MENGE,CGB009,CGB010,CGB011,CGB012,CGB013,CGB014,CGB015,CGB016,CGB018,CGB020,CGB021,CGB022,CGB023,CGB027,CGB029,cgb024,TB_TIME,CGB019,FMRPCLOSESTATUS,FMRPTERMINATESTATUS,cgb025) " +
                                        "VALUES (SEQ_BASEINFO_ID.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','18','{19}','{20}','{21}','{22}','{23}',TIMESTAMP '{24}',sysdate,'{25}','{26}','{27}','{28}') ", new object[]
                                    string sql2 = string.Format(" INSERT INTO MES_ROH_IN_DATA(ID,ERPID,EBELN,CGB002,MATNR,CGB004,CGB005,CGB006,CGB007,MENGE,CGB009,CGB010,CGB011,CGB012,CGB013,CGB014,CGB015,CGB016,CGB018,CGB020,CGB021,CGB022,CGB023,CGB027,CGB029,cgb024,TB_TIME,CGB019,FMRPCLOSESTATUS,FMRPTERMINATESTATUS,URGENCY, GYS_JHTIME, GBBZ, CGB, CGB025, CGB026, CGB028) " +
                                        "VALUES (SEQ_BASEINFO_ID.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','18','{19}','{20}','{21}','{22}','{23}',TIMESTAMP '{24}',sysdate,'{25}','{26}','{27}','{28}',{29},'{30}','{31}','{32}','{33}',{34}) ", new object[]
                                    {
                                        dr2["ERPID"],
                                        dr2["CGB001"],
@@ -2497,7 +2570,8 @@
                                         dr2["CGB014"],
                                         dr2["FMRPCloseStatus"],
                                         dr2["FMRPTerminateStatus"],
                                         cgb025Value
                                       urgency, gys_jhtime, gbbz.Replace("'", "''"), cgb.Replace("'", "''"),
                                    cgb025, cgb026.Replace("'", "''"), cgb028

                                    });
                                    SQLStringListT.Add(sql2);
@@ -2943,6 +3017,24 @@
                                    DataRow dr = (DataRow)obj2;
                                    //2025-01-22 修改审核字段
                                    string FDOCUMENTSTATUS = dr["FDOCUMENTSTATUS"].ToString();
                                    //20250724 如果mes有发货记录，那么不能更新
                           
                                    string sql2 = string.Format("SELECT COUNT(1) FROM MES_INV_ITEM_OUTS WHERE TASK_NO = '{0}'", dr["FBillNo"]);
                                    string cntStr = this._sqlHelper.GetSingle(sql2)?.ToString();

                                    // 转换为整数
                                    int cnt = 0;
                                    if (!string.IsNullOrEmpty(cntStr) && int.TryParse(cntStr, out cnt))
                                    {
                                        if (cnt > 0)
                                        {
                                            // 存在发货记录，不能更新，返回失败
                                           
                                            ErrorMsg = "[该单据已在 MES 中有发货记录，无法更新。";
                                            result = false;
                                        }
                                    }

                                    if (FDOCUMENTSTATUS == "C")
                                    {
                                        mid = dr["ERPID"].ToString();
@@ -3292,6 +3384,7 @@
                        ArrayList list = new ArrayList();

                        list.Add(bj1);

                        var kc_json = new kcjson()
                        {
                            /* FormId = "STK_Inventory",
@@ -3409,6 +3502,118 @@

        }

        private bool TB_ERPTOMES_JSKC(DataSet ds, string operatype, ref string errorMsg)
        {
            ArrayList SQLStringList = new ArrayList();
            DataTable dt = ds.Tables[0];
            string sql = "";

            foreach (DataRow dr in dt.Rows)
            {
                try
                {
                    // 初始化金蝶 API 客户端
                    K3CloudApiClient client = new K3CloudApiClient("http://192.168.0.40/k3cloud/");
                    var loginResult = client.ValidateLogin("64c5000ed94a48", "沈子博", "Ss123123@", 2052);
                    var resultType = JObject.Parse(loginResult)["LoginResultType"].Value<int>();

                    if (resultType != 1)
                    {
                        errorMsg = "登录金蝶系统失败";
                        return false;
                    }

                    // 读取物料编码和仓库编码
                    string PD1 = dr["ITEM_NO"]?.ToString() ?? "";
                    string PD2 = dr["CKBM"]?.ToString() ?? "";

                    // 基础条件（你的 MES1 仓位条件）
                    string bjString1 = "(FStockLocId.FF100001.FNumber='MES1' " +
                                       "or FStockLocId.FF100002.FNumber='MES1' " +
                                       "or FStockLocId.FF100003.FNumber='MES1' " +
                                       "or FStockLocId.FF100004.FNumber='MES1' " +
                                       "or FStockLocId.FF100006.FNumber='MES1' " +
                                       "or FStockLocId.FF100008.FNumber='MES1' " +
                                       "or FStockLocId.FF100009.FNumber='MES1' " +
                                       "or FStockLocId.FF100010.FNumber='MES1' " +
                                       "or FStockLocId.FF100012.FNumber='MES1' " +
                                       "or FStockLocId.FF100013.FNumber='MES1')";

                    // 动态追加物料、仓库过滤
                    if (!string.IsNullOrEmpty(PD1) && string.IsNullOrEmpty(PD2))
                        bjString1 += $" and fmaterialid.Fnumber='{PD1}'";
                    else if (!string.IsNullOrEmpty(PD2) && string.IsNullOrEmpty(PD1))
                        bjString1 += $" and fstockid='{PD2}'";
                    else if (!string.IsNullOrEmpty(PD1) && !string.IsNullOrEmpty(PD2))
                        bjString1 += $" and fmaterialid.Fnumber='{PD1}' and fstockid='{PD2}'";

                    // 分页参数
                    int pageSize = 10000;
                    int startRow = 0;

                    while (true)
                    {
                        var kc_json = new kcjson()
                        {
                            FormId = "STK_Inventory",
                            FieldKeys = "fmaterialid, fbaseqty, fstockid, fbaseunitid, fstockunitid, fstocklocid, FStockOrgId, fmaterialid.Fnumber, FStockLocId.FF100001.FNumber, FStockLocId.FF100002.FNumber, FStockLocId.FF100003.FNumber, FStockLocId.FF100004.FNumber, FStockLocId.FF100006.FNumber, FStockLocId.FF100008.FNumber, FStockLocId.FF100009.FNumber, FStockLocId.FF100010.FNumber, FStockLocId.FF100012.FNumber, FStockLocId.FF100013.FNumber",
                            FilterString = bjString1,
                            OrderString = "",
                            TopRowCount = 0,
                            StartRow = startRow,
                            Limit = pageSize,
                            SubSystemId = ""
                        };

                        string jsonString = JsonConvert.SerializeObject(kc_json);
                        var BJ = jsonString.Replace("\\", string.Empty);
                        var BJ1 = BJ.Replace("[\"", "[");
                        var BJ2 = BJ1.Replace("\"]", "]");

                        // 执行查询
                        var inventoryData = client.ExecuteBillQuery(BJ2);

                        if (inventoryData == null || inventoryData.Count == 0)
                            break; // 数据取完了

                        // 拼接插入 SQL
                        for (int i = 0; i < inventoryData.Count; i++)
                        {
                            sql = string.Format(
                                @"INSERT INTO MES_ERP_KC(CPBM, SL, CKBM, DWBM, DW, ID, GXSJ, CW, ZZ, bm)
                          VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', KC.NEXTVAL, to_char(sysdate,'YYYY-MM-DD HH24:MI:SS'), '{5}', '{6}', '{7}')",
                                inventoryData[i][0], inventoryData[i][1], inventoryData[i][2],
                                inventoryData[i][3], inventoryData[i][4], inventoryData[i][5],
                                inventoryData[i][6], inventoryData[i][7]
                            );

                            SQLStringList.Add(sql);

                            if (SQLStringList.Count >= 500)
                            {
                                _sqlHelper.ExecuteSqlTran(SQLStringList);
                                SQLStringList.Clear();
                            }
                        }

                        // 下一页
                        startRow += pageSize;
                    }
                }
                catch (Exception e)
                {
                    errorMsg = e.Message;
                    return false;
                }
            }

            // 最后一次批量插入
            if (SQLStringList.Count > 0)
                return _sqlHelper.ExecuteSqlTran(SQLStringList);

            return true;
        }


        private bool TB_ERPTOMES_SYS(DataSet ds, string operatype, ref string ErrorMsg)
        {