From b3a5b4165d585463af639c35931d6c2962a795a0 Mon Sep 17 00:00:00 2001
From: yhj <1836460075@qq.com>
Date: 星期五, 15 八月 2025 17:59:14 +0800
Subject: [PATCH] 即时库存
---
JF_DataexchangeServer/DataexchangeServer/HandleData/AE_MES_ERP.cs | 241 ++++++++++++++++++++++++++++++++++++++++++++---
1 files changed, 223 insertions(+), 18 deletions(-)
diff --git a/JF_DataexchangeServer/DataexchangeServer/HandleData/AE_MES_ERP.cs b/JF_DataexchangeServer/DataexchangeServer/HandleData/AE_MES_ERP.cs
index 65840d1..d65b719 100644
--- a/JF_DataexchangeServer/DataexchangeServer/HandleData/AE_MES_ERP.cs
+++ b/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)
{
--
Gitblit v1.9.3