using DataexchangeServer.Helper;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using Kingdee.BOS.WebApi.Client;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json;
using System.Windows.Documents;
using System.Web.UI.MobileControls;
namespace DataexchangeServer
{
//数据推送类
public class AE_MES_ERP
{
private OracleDb _sqlHelper;
string conStr;
public AE_MES_ERP()
{
conStr = "Data Source = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.13)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = ORCL))); Persist Security Info=True;User ID = jfprd; Password=jf_prd"; ;
_sqlHelper = new OracleDb(conStr);
}
#region 0.同步金蝶系统数据bool ErpToMesData(string taskid, DataSet inputds, string operatype, DataRow drSource, ref string msg)
///
/// 同步金蝶系统数据
///
/// 任务ID
/// JSON数据表集合
/// INSERT,UPDATE,DELETE
/// 原始数据行
/// 返回的执行错误信息
///
public bool ErpToMesData(string taskid, DataSet inputds, string operatype, DataRow drSource, ref string msg)
{
bool result = true;
switch (taskid)
{
//1.ERPBMA部门信息表(同步推送)
case "TB_ERPTOMES_BM":
result = TB_ERPTOMES_BM(inputds, operatype, ref msg, drSource);
break;
//2.ERPRYA人员信息表(同步推送)
case "TB_ERPTOMES_RY":
result = TB_ERPTOMES_RY(inputds, operatype, ref msg, drSource);
break;
//3.ERPWFL物料分类表(同步推送)
case "TB_ERPTOMES_WF":
result = TB_ERPTOMES_WF(inputds, operatype, ref msg);
break;
//4.ERPWLA物料表(同步推送)
case "TB_ERPTOMES_WL":
result = TB_ERPTOMES_WL(inputds, operatype, ref msg, drSource);
break;
//4.ERPWLA物料表(同步推送)
case "TB_ERPTOMES_JSKC":
result = TB_ERPTOMES_JSKC(inputds, operatype, ref msg, drSource);
break;
//5.ERPDWA计量单位(同步推送)
case "TB_ERPTOMES_DW":
result = TB_ERPTOMES_DW(inputds, operatype, ref msg, drSource);
break;
//6.ERPKHA客户信息(同步推送)
case "TB_ERPTOMES_KH":
result = TB_ERPTOMES_KH(inputds, operatype, ref msg, drSource);
break;
//7.ERPGYS供应商信息(同步推送)
case "TB_ERPTOMES_GYS":
result = TB_ERPTOMES_GYS(inputds, operatype, ref msg, drSource);
break;
//8.ERPCKA仓库信息档(同步推送)
case "TB_ERPTOMES_CK":
result = TB_ERPTOMES_CK(inputds, operatype, ref msg, drSource);
break;
//即时库存
case "TB_ERPTOMES_JSKC1":
result = TB_ERPTOMES_JSKC1(inputds, operatype, ref msg);
break;
//9.ERPBOMA(同步推送)
//10.ERPCGA采购订单｜委外订单(同步推送)
case "TB_ERPTOMES_CG":
result = TB_ERPTOMES_CG(inputds, operatype, ref msg);
break;
case "TB_ERPTOMES_WW":
result = TB_ERPTOMES_CG(inputds, operatype, ref msg);
break;
//销售订单
case "TB_ERPTOMES_XSD":
result = TB_ERPTOMES_XSD(inputds, operatype, ref msg);
break;
//销售退货
case "TB_ERPTOMES_XSRK":
result = TB_ERPTOMES_XSRK(inputds, operatype, ref msg);
break;
//销售发货
case "TB_ERPTOMES_XSCK":
result = TB_ERPTOMES_XSCK(inputds, operatype, ref msg);
break;
//ERP视图
case "TB_ERPTOMES_BAR":
result = TB_ERPTOMES_BAR(inputds, operatype, ref msg);
break;
//11.ERPTLA生产投料单(同步推送)
case "TB_ERPTOMES_TL":
result = TB_ERPTOMES_TL(inputds, operatype, ref msg);
break;
//12.ERPTHCONFIRM供应商退货确认(同步推送)
case "TB_ERPTOMES_TH_CONFIRM":
result = TB_ERPTOMES_TH_CONFIRM(inputds, operatype, ref msg);
break;
//13.TB_ERPTOMES_RWHB任务汇报单(同步推送)
case "TB_ERPTOMES_RWHB":
result = TB_ERPTOMES_RWHB(inputds, operatype, ref msg);
break;
//14.MesToErp(同步推送)
case "TB_MESTOERP_WGRK"://MESWGRK金蝶条码入库（外购入库）(同步推送)
result = MesToErp(drSource, ref msg);
break;
case "STK_TransferDirect"://MES 调拨单
//result = MesToErp(drSource, ref msg);
result = MesToErpFormal(drSource, ref msg);
break;
//case "WW_InStock"://MESWWRK金蝶条码入库（委外入库）(同步推送)
case "TB_MESTOERP_QTRK"://MESQTRK金蝶条码入库（其他入库）(同步推送)
case "TB_MESTOERP_CPRK"://MESCPRK金蝶条码入库（产品入库）(同步推送)
case "TB_MESTOERP_DD"://MESDD金蝶条码入库（调拨）(同步推送)
case "TB_MESTOERP_PYPK"://MESPYPK金蝶条码入库（盘盈盘亏）(同步推送)
case "TB_MESTOERP_LL"://MESLL金蝶条码入库（领料单）(同步推送)
case "TB_MESTOERP_WTRK"://MESWTRK金蝶条码入库（受托入库）(同步推送)
case "TB_MESTOERP_QTCK"://其他出库
case "TB_MESTOERP_XCK"://销售出库（蓝-订单、红）
//case "SUB_PickMtrl"://委外出库(蓝、红)
case "TB_MESTOERP_WGTL"://外购退料
//case "SUB_RETURNMTRL"://委外退料
case "TB_MESTOERP_JYTL"://检验退料
// case "WW_ReturnMtrl"://委外退库
case "TB_MESTOERP_SLSH"://推送审核送货单
//case "PRD_FeedMtrl"://推送MES生产补料（超领料）单
result = MesToErp(drSource, ref msg);
break;
case "TB_MESTOERP_GDRK"://ERP工单库存查询
result = MesToErp(drSource, ref msg);
break;
//委外补料
case "SUB_FEEDMTRL":
result = MesToErpFormal(drSource, ref msg);
break;
case "PUR_MRB"://采购退货推送
result = MesToErpFormal(drSource, ref msg);
break;
//case "PUR_MRB"://采购退货推送
// result = MesToErp(drSource, ref msg);
// break;
//销售发货
case "SAL_OUTSTOCK":
result = MesToErpFormal(drSource, ref msg);
break;
//销售退货
case "SAL_RETURNSTOCK":
result = MesToErpFormal(drSource, ref msg);
break;
case "SUB_PickMtrl": //委外领料
result = MesToErpFormal(drSource, ref msg);
break;
case "SUB_RETURNMTRL"://委外退料
result = MesToErpFormal(drSource, ref msg);
break;
case "WW_ReturnMtrl": //委外退库
result = MesToErpFormal(drSource, ref msg);
break;
case "WW_InStock": //委外入库
result = MesToErpFormal(drSource, ref msg);
break;
case "PRD_FeedMtrl"://推送MES生产补料单
result = MesToErpFormal(drSource, ref msg);
break;
//MES标识
case "STK_MESBS":
result = MESTOERPBS(drSource, ref msg);
break;
case "WW_TransferDirect"://推送委外调拨单
result = MesToErp(drSource, ref msg);
break;
case "TB_MESTOERP_ICMO"://推送审核送货单
result = MesToErp(drSource, ref msg);
break;
case "STK_InStock"://推送审核送货单
result = MesToErpFormal(drSource, ref msg);
break;
//case "STK_InStock"://推送审核送货单
// result = MesToErp(drSource, ref msg);
// break;
case "PRD_PickMtrl"://推送MES领料单
//result = MesToErp(drSource, ref msg);
result = MesToErpFormal(drSource, ref msg);
break;
case "PRD_INSTOCK"://推送MES生产入库单
//result = MesToErp(drSource, ref msg);
result = MesToErpFormal(drSource, ref msg);
break;
case "PRD_ReturnMtrl"://推送MES生产退料单
result = MesToErpFormal(drSource, ref msg);
//result = MesToErp(drSource, ref msg);
break;
case "STK_MISCELLANEOUS"://推送MES其他入库单
// result = MesToErp(drSource, ref msg);
result = MesToErpFormal(drSource, ref msg);
break;
case "STK_MisDelivery"://推送MES其他出库单
result = MesToErpFormal(drSource, ref msg);
//result = MesToErp(drSource, ref msg);
break;
//15.到货单(同步推送)
case "TB_ERPTOMES_SLTZD":
result = TB_ERPTOMES_SLTZD(inputds, operatype, ref msg);
break;
//结案标识
case "TB_ERPTOMES_JA":
result = TB_ERPTOMES_JA(inputds, operatype, ref msg, drSource);
break;
}
return result;
}
#region 16.TB_ERPTOMES_RWHB任务汇报单(同步推送) bool TB_ERPTOMES_RWHB(DataSet inputds, string operatype, ref string msg)
private bool TB_ERPTOMES_RWHB(DataSet ds, string operatype, ref string ErrorMsg)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
ArrayList SQLStringListT = new ArrayList();
string dtName = "";
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
try
{
foreach (DataTable dt in ds.Tables)
{
dtName = dt.TableName;
IDList.Clear();
//主从表的同步，修改和新增一样，先删除原记录后插入新记录,要求同步过来的数据也是整单完整数据
if (operatype == "INSERT" || operatype == "UPDATE")
{
#region INSERT
if (dt.TableName == "ERPSLTZA")
{
#region 任务汇报单主表的处理
// dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
////插入前先删除重复键
string sqlT = "";
string sqlG = "";
string SQLF = "";
string SQLD = "";
sqlG = string.Format(@"SELECT count(*) cou FROM PURHBD WHERE HBD001='{0}' ", dr["FBillNo"]);
sqlT = string.Format(@"SELECT count(*) cou FROM PURHBE WHERE HBE001='{0}'", dr["ERPID"]);
//int a = _sqlHelper.ExecuteNonQuery(sqlT);
int g = Convert.ToInt32(_sqlHelper.GetSingle(sqlG));
int c = Convert.ToInt32(_sqlHelper.GetSingle(sqlT));
if (g == 0)
{
if (c != 0)
{
try
{
//删除主表
SQLF = string.Format(@"DELETE PURHBD WHERE HBD001='{0}' ", dr["FBillNo"]);
int B = _sqlHelper.ExecuteNonQuery(SQLF);
//删除从表
SQLD = string.Format(@"DELETE PURHBE WHERE HBE001='{0}'", dr["ERPID"]);
int D = _sqlHelper.ExecuteNonQuery(SQLD);
}
catch (Exception)
{
return false;
}
}
}
else
{
return false;
}
// IDList.Add(dr["ERPID"].ToString());
//dr["AUDITING"] = 1;
//string f = DateTime.Now.ToString("yyyy-MM-dd");
//string t = DateTime.Now.ToShortTimeString().ToString();
//string TIME = (f + " " + t).ToString();
sql = string.Format(@" INSERT INTO PURHBD(ID,HBD001,HBD002,HBD003,HBD004,HBD005,HBD006,HBD007,ERPID,STATUS)
VALUES (seq_down.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','1') ", dr["FBillNo"], dr["FworkshopNumber"], dr["Fworkshop"], dr["FSelTranType"], dr["FBillerID"], dr["FCheckerID"], dr["FCheckDate"], dr["ERPID"]);
SQLStringList.Add(sql);
}
_sqlHelper.ExecuteSqlTran(SQLStringList);
#endregion
}
if (dt.TableName == "ERPRWHB")
{
#region 汇报单明细的处理
//dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
// IDList.Add(dr["ERPID"].ToString());
// Double a = Convert.ToDouble(dr["CGB008"].ToString()) - Convert.ToDouble(dr["CGB013"].ToString());
//dr["AUDITING"] = 1;
sql = string.Format(@" INSERT INTO PURHBE(ID,HBE001,HBE002,HBE003,HBE004,HBE005,HBE006,HBE007,HBE008,HBE009,HBE010,HBE011,HBE012,HBE013,HBE014,HBE015)
VALUES (seq_down.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}') ", dr["ERPID"], dr["FEntryID"], dr["FItemID"]
, dr["FName"], dr["FModel"], dr["FworkshopID"], dr["Fworkshop"], dr["FUnitID"], dr["FAuxQtyfinish"], dr["FAuxQtyloss"], dr["FAuxQtypass"], dr["FworkstartDate"], dr["FworkendDate"], dr["FNote"], dr["FsourceBillNo"]);
SQLStringListT.Add(sql);
}
_sqlHelper.ExecuteSqlTran(SQLStringListT);
#endregion
}
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
if (dt.TableName == "ERPDHA")
{
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from PURDHA where ERPID in ('{0}'); delete from PURDHB where ERPID in ('{0}'); ", string.Join("','", IDList));
}
_sqlHelper.ExecuteNonQuery(sql);
#endregion
}
}
return true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
return false;
}
}
#endregion
#endregion
#region 17.ERPBMA部门信息表(同步推送) bool TB_ERPTOMES_BM(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPBMA部门信息表(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_BM(DataSet ds, string operatype, ref string ErrorMsg, DataRow ddr)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
if (operatype == "INSERT" || operatype == "UPDATE")
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
//处理前判断是否存在
//operatype = DataIsInDB("HRMMAA", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT" || operatype == "UPDATE")
{
#region INSERT
dt.Columns.Add("MAA001", typeof(string));
dt.Columns.Add("AUDITING", typeof(string));
// 构建删除SQL语句
string deleteSql = $"DELETE FROM SYS_DEPARTMENT WHERE ID IN ('{string.Join("','", IDList)}')";
_sqlHelper.ExecuteNonQuery(deleteSql); // 执行删除操作
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
sql = string.Format(@" INSERT INTO SYS_DEPARTMENT(ID,DEPARTMENTCODE,DEPARTMENTNAME,COMPANY,FACTORY,PART)
VALUES ('{0}','{1}','{2}','1000','1000','{3}') ", dr["ERPID"], dr["BMA001"], dr["BMA002"], ddr["TARGET_TABLE"]);
SQLStringList.Add(sql);
}
//插入前先删除重复键
// DeleteDataBeforeInsert("HRMMAA", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("HRMMAA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "HRMMAA重复键删除失败！";
// return false;
//}
return _sqlHelper.ExecuteSqlTran(SQLStringList);
#endregion
}
/* else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql += string.Format(@" update HRMMAA
set
MAA002='{1}',
MAA003='{2}',
MAA004='{3}',
MAA005='{4}',
MAA006='{5}',
MAA007='{6}'
where ERPID='{0}'; ", dr["ERPID"], dr["BMA001"], dr["BMA002"], dr["BMA003"], dr["BMA004"], dr["BMA005"], dr["BMA006"]);
}
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}*/
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
// 构建删除SQL语句
string deleteSql = $"DELETE FROM SYS_DEPARTMENT WHERE ID IN ('{string.Join("','", IDList)}')";
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 18.ERPRYA人员信息表(同步推送) bool TB_ERPTOMES_RY(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPRYA人员信息表(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_RY(DataSet ds, string operatype, ref string ErrorMsg, DataRow drr)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
if (operatype == "INSERT" || operatype == "UPDATE")
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
//处理前判断是否存在
// operatype = DataIsInDB("HRMMAC", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT")
{
#region INSERT
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
sql = string.Format(@" INSERT INTO MES_STAFF(ID,STAFF_NO,STAFF_NAME,DEPARTMENT_NO,MEMO,PART)
VALUES ('{0}','{1}','{2}','{3}','{4}','{5}') ", dr["ERPID"], dr["RYA001"], dr["RYA002"], dr["RYA003"], dr["RYA010"], drr["TARGET_TABLE"]);
SQLStringList.Add(sql);
}
return _sqlHelper.ExecuteSqlTran(SQLStringList);
//插入前先删除重复键
// DeleteDataBeforeInsert("HRMMAC", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("HRMMAC", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "HRMMAC重复键删除失败！";
// return false;
//}
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql += string.Format(@" update HRMMAC
set
MAC002='{1}',
MAC003='{2}',
MAC001='{3}',
MAC019='{4}',
MAC004='{5}',
MAC005='{6}',
MAC007='{7}',
MAC010='{8}',
MAC011='{9}',
MAC013='{10}'
where ERPID='{0}'; ", dr["ERPID"]
, dr["RYA001"], dr["RYA002"], dr["RYA003"], dr["RYA004"], dr["RYA005"]
, dr["RYA006"], dr["RYA007"], dr["RYA008"], dr["RYA009"], dr["RYA010"]);
}
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from HRMMAC where ERPID in ('{0}'); ", string.Join("','", IDList));
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 19.ERPWFL物料分类表(同步推送) bool TB_ERPTOMES_WF(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPWFL物料分类表(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_WF(DataSet ds, string operatype, ref string ErrorMsg)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
if (operatype == "INSERT" || operatype == "UPDATE")
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
//处理前判断是否存在
// operatype = DataIsInDB("BASBAB", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT")
{
#region INSERT
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
sql = string.Format(@" INSERT INTO MES_ITEM_TYPE(ID, TCODE, TNAME,COMPANY,FACTORY)
VALUES ('{0}','{1}','{2}','1000','1000') ", dr["ERPID"], dr["WFL001"], dr["WFL002"]);
SQLStringList.Add(sql);
}
//插入前先删除重复键
//DeleteDataBeforeInsert("BASBAB", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("BASBAB", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "BASBAB重复键删除失败！";
// return false;
//}
return _sqlHelper.ExecuteSqlTran(SQLStringList);
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql += string.Format(@" update BASBAB
set
BAB001='{1}',
BAB003='{2}',
BAB002='{3}',
BAB004='{4}'
where ERPID='{0}'; ", dr["ERPID"]
, dr["WFL001"], dr["WFL002"], dr["WFL003"], dr["WFL004"]);
}
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from BASBAB where ERPID in ('{0}'); ", string.Join("','", IDList));
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 20.ERPWLA物料表(同步推送) bool TB_ERPTOMES_WL(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPWLA物料表(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_WL(DataSet ds, string operatype, ref string ErrorMsg, DataRow ddr)
{
string sql = "";
string sqlL = "";
//用来拼ID集合
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
ArrayList SQLStringListT = new ArrayList();
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
if (operatype == "INSERT" || operatype == "UPDATE")
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
//处理前判断是否存在
// operatype = DataIsInDB("INVMBA", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT")
{
#region INSERT
foreach (DataRow dr in dt.Rows)
{
//插入前先检查是否存在相同数据，若存在则删除
string sqlT = "";
string SQLF = "";
int issc = 1;
sqlT = string.Format(@"SELECT count(*) cou FROM MES_ITEMS WHERE ID='{0}' ", dr["ERPID"]);
//int a = _sqlHelper.ExecuteNonQuery(sqlT);
int c = Convert.ToInt32(_sqlHelper.GetSingle(sqlT));
if (c != 0)
{
issc = 0;
try
{
SQLF = string.Format(@"DELETE MES_ITEMS WHERE ID='{0}'", dr["ERPID"]);
int B = _sqlHelper.ExecuteNonQuery(SQLF);
}
catch (Exception)
{
return false;
}
}
IDList.Add(dr["ERPID"].ToString());
/* sql = string.Format(@" INSERT INTO MES_ITEMS(BE_CHANGED,ID,E_ITEM_ID,ITEM_NO,ITEM_NAME,ITEM_MODEL,ITEM_TYPE,LOWLIMIT,ITEM_UNIT,ERP_ITEM_NO,COMPANY,FACTORY,EXT_DATE,CUST_NO,DEPOT_CODE,PRD_PACK,HIGHLIMIT,PRD_CHECK,WLA024,PART,MOLD_NO)
VALUES (1,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','1000','1000','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}') ", dr["ERPID"], dr["ERPID"], dr["WLA001"], dr["WLA002"], dr["WLA003"], dr["WLA011"], dr["WLA006"], dr["WLA004"], dr["WLA001"], dr["WLA012"], dr["WLA014"], dr["WLA010"], dr["WLA008"], dr["WLA007"], dr["WLA013"], dr["WLA005"],ddr["TARGET_TABLE"],dr["MOLD_NO"]);
SQLStringList.Add(sql);*/
/*sql = string.Format(@"INSERT INTO MES_ITEMS(BE_CHANGED, ID, E_ITEM_ID, ITEM_NO, ITEM_NAME, ITEM_MODEL, ITEM_TYPE, LOWLIMIT, ITEM_UNIT, ERP_ITEM_NO, COMPANY, FACTORY, EXT_DATE, CUST_NO, DEPOT_CODE, PRD_PACK, HIGHLIMIT, PRD_CHECK, WLA024, PART, MOLD_NO, LASTUPDATE_DATE)
VALUES (1, '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '1000', '1000', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', GETDATE())",
dr["ERPID"], dr["ERPID"], dr["WLA001"], dr["WLA002"], dr["WLA003"], dr["WLA011"], dr["WLA006"], dr["WLA004"], dr["WLA001"], dr["WLA012"], dr["WLA014"], dr["WLA010"], dr["WLA008"], dr["WLA007"], dr["WLA013"], dr["WLA005"], ddr["TARGET_TABLE"], dr["MOLD_NO"]);
SQLStringList.Add(sql);*/
sql = string.Format(@"INSERT INTO MES_ITEMS(BE_CHANGED, ID, E_ITEM_ID, ITEM_NO, ITEM_NAME, ITEM_MODEL, ITEM_TYPE, LOWLIMIT, ITEM_UNIT, ERP_ITEM_NO, COMPANY, FACTORY, EXT_DATE, CUST_NO, DEPOT_CODE, PRD_PACK, HIGHLIMIT, PRD_CHECK, WLA024, PART, MOLD_NO,LASTUPDATE_DATE,IS_SC)
VALUES (1, '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '1000', '1000', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}',sysdate,'{18}')",
dr["ERPID"], dr["ERPID"], dr["WLA001"], dr["WLA002"], dr["WLA003"], dr["WLA011"], dr["WLA006"], dr["WLA004"], dr["WLA001"], dr["WLA012"], dr["WLA014"], dr["WLA010"], dr["WLA008"], dr["WLA007"], dr["WLA013"], dr["WLA005"], ddr["TARGET_TABLE"], dr["MOLD_NO"],issc);
SQLStringList.Add(sql);
}
return _sqlHelper.ExecuteSqlTran(SQLStringList);
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
/* sql = string.Format(@" update MES_ITEMS
set
BE_CHANGED=1,
ID='{1}',
E_ITEM_ID='{2}',
ITEM_NO='{3}',
ITEM_NAME='{4}',
ITEM_MODEL='{5}',
ITEM_TYPE='{6}',
LOWLIMIT='{7}',
ITEM_UNIT='{8}',
ERP_ITEM_NO='{9}',
COMPANY='1000',
FACTORY='1000',
EXT_DATE='{10}',
DEPOT_CODE='{11}',
PRD_PACK='{12}',
HIGHLIMIT='{13}',
PRD_CHECK='{14}',
WLA024='{15}' ,
PART='{16}'
where ID='{0}' ", dr["ERPID"], dr["ERPID"], dr["ERPID"], dr["WLA001"], dr["WLA002"], dr["WLA003"], dr["WLA011"], dr["WLA006"], dr["WLA004"], dr["WLA001"], dr["WLA012"], dr["WLA010"], dr["WLA008"], dr["WLA007"], dr["WLA013"], dr["WLA005"],ddr["TARGET_TABLE"]);
SQLStringListT.Add(sql);*/
sql = string.Format(@"UPDATE MES_ITEMS
SET
BE_CHANGED = 1,
ID = '{1}',
E_ITEM_ID = '{2}',
ITEM_NO = '{3}',
ITEM_NAME = '{4}',
ITEM_MODEL = '{5}',
ITEM_TYPE = '{6}',
LOWLIMIT = '{7}',
ITEM_UNIT = '{8}',
ERP_ITEM_NO = '{9}',
COMPANY = '1000',
FACTORY = '1000',
EXT_DATE = '{10}',
DEPOT_CODE = '{11}',
PRD_PACK = '{12}',
HIGHLIMIT = '{13}',
PRD_CHECK = '{14}',
WLA024 = '{15}' ,
PART = '{16}',
LASTUPDATE_DATE = GETDATE()
WHERE ID = '{0}'", dr["ERPID"], dr["ERPID"], dr["ERPID"], dr["WLA001"], dr["WLA002"], dr["WLA003"], dr["WLA011"], dr["WLA006"], dr["WLA004"], dr["WLA001"], dr["WLA012"], dr["WLA010"], dr["WLA008"], dr["WLA007"], dr["WLA013"], dr["WLA005"], ddr["TARGET_TABLE"]);
SQLStringListT.Add(sql);
}
return _sqlHelper.ExecuteSqlTran(SQLStringListT);
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from MES_ITEMS where ID in ('{0}'); ", string.Join("','", IDList));
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
private bool TB_ERPTOMES_JSKC(DataSet ds, string operatype, ref string ErrorMsg, DataRow ddr)
{
string sql = "";
string sqlL = "";
//用来拼ID集合
List IDList = new List();
List IDList2 = new List();
ArrayList SQLStringList = new ArrayList();
ArrayList SQLStringListT = new ArrayList();
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
if (operatype == "INSERT" || operatype == "UPDATE")
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
IDList.Add(dr["JSKC001"].ToString());
IDList2.Add(dr["JSKC002"].ToString());
}
//处理前判断是否存在
// operatype = DataIsInDB("INVMBA", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT" || operatype == "UPDATE")
{
#region INSERT
foreach (DataRow dr in dt.Rows)
{
//插入前先检查是否存在相同数据，若存在则删除
string sqlT = "";
string SQLF = "";
sqlT = string.Format(@"SELECT count(*) cou FROM MES_JSKC WHERE ID='{0}' and depot_code='{1}' ", dr["JSKC001"], dr["JSKC002"]);
//int a = _sqlHelper.ExecuteNonQuery(sqlT);
int c = Convert.ToInt32(_sqlHelper.GetSingle(sqlT));
if (c != 0)
{
try
{
SQLF = string.Format(@"delete FROM MES_JSKC WHERE ID='{0}' and depot_code='{1}' ", dr["JSKC001"], dr["JSKC001"]);
int B = _sqlHelper.ExecuteNonQuery(SQLF);
}
catch (Exception)
{
return false;
}
}
IDList.Add(dr["JSKC001"].ToString());
IDList2.Add(dr["JSKC002"].ToString());
/* sql = string.Format(@" INSERT INTO MES_ITEMS(BE_CHANGED,ID,E_ITEM_ID,ITEM_NO,ITEM_NAME,ITEM_MODEL,ITEM_TYPE,LOWLIMIT,ITEM_UNIT,ERP_ITEM_NO,COMPANY,FACTORY,EXT_DATE,CUST_NO,DEPOT_CODE,PRD_PACK,HIGHLIMIT,PRD_CHECK,WLA024,PART,MOLD_NO)
VALUES (1,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','1000','1000','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}') ", dr["ERPID"], dr["ERPID"], dr["WLA001"], dr["WLA002"], dr["WLA003"], dr["WLA011"], dr["WLA006"], dr["WLA004"], dr["WLA001"], dr["WLA012"], dr["WLA014"], dr["WLA010"], dr["WLA008"], dr["WLA007"], dr["WLA013"], dr["WLA005"],ddr["TARGET_TABLE"],dr["MOLD_NO"]);
SQLStringList.Add(sql);*/
/*sql = string.Format(@"INSERT INTO MES_ITEMS(BE_CHANGED, ID, E_ITEM_ID, ITEM_NO, ITEM_NAME, ITEM_MODEL, ITEM_TYPE, LOWLIMIT, ITEM_UNIT, ERP_ITEM_NO, COMPANY, FACTORY, EXT_DATE, CUST_NO, DEPOT_CODE, PRD_PACK, HIGHLIMIT, PRD_CHECK, WLA024, PART, MOLD_NO, LASTUPDATE_DATE)
VALUES (1, '{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '1000', '1000', '{9}', '{10}', '{11}', '{12}', '{13}', '{14}', '{15}', '{16}', '{17}', GETDATE())",
dr["ERPID"], dr["ERPID"], dr["WLA001"], dr["WLA002"], dr["WLA003"], dr["WLA011"], dr["WLA006"], dr["WLA004"], dr["WLA001"], dr["WLA012"], dr["WLA014"], dr["WLA010"], dr["WLA008"], dr["WLA007"], dr["WLA013"], dr["WLA005"], ddr["TARGET_TABLE"], dr["MOLD_NO"]);
SQLStringList.Add(sql);*/
sql = string.Format(@"INSERT INTO MES_JSKC(id,depot_code,kc,item_name,depot_name,create_data)
VALUES ( '{0}', '{1}', '{2}', '{3}', '{4}', sysdate)",
dr["JSKC001"], dr["JSKC002"], dr["JSKC003"], dr["JSKC004"], dr["JSKC005"]);
SQLStringList.Add(sql);
}
return _sqlHelper.ExecuteSqlTran(SQLStringList);
#endregion
}
/*else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql = string.Format(@"UPDATE MES_JSKC
SET
WHERE ID = '{0}'", dr["ERPID"], dr["ERPID"]);
SQLStringListT.Add(sql);
}
return _sqlHelper.ExecuteSqlTran(SQLStringListT);
#endregion
}*/
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from MES_JSKC where JSKC001 in ('{0}') and JSKC002 in ('{1}'); ", string.Join("','", IDList), string.Join("','", IDList2));
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
private bool TB_ERPTOMES_DW(DataSet ds, string operatype, ref string ErrorMsg, DataRow ddr)
{
string sql = "";
string sqlL = "";
//用来拼ID集合
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
ArrayList SQLStringListT = new ArrayList();
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
if (operatype == "INSERT" || operatype == "UPDATE")
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
//处理前判断是否存在
// operatype = DataIsInDB("INVMBA", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT")
{
#region INSERT
foreach (DataRow dr in dt.Rows)
{
//插入前先检查是否存在相同数据，若存在则删除
string sqlT = "";
string SQLF = "";
sqlT = string.Format(@"SELECT count(*) cou FROM MES_DW WHERE ID='{0}' ", dr["ERPID"]);
//int a = _sqlHelper.ExecuteNonQuery(sqlT);
int c = Convert.ToInt32(_sqlHelper.GetSingle(sqlT));
if (c != 0)
{
try
{
SQLF = string.Format(@"DELETE MES_DW WHERE ID='{0}'", dr["ERPID"]);
int B = _sqlHelper.ExecuteNonQuery(SQLF);
}
catch (Exception)
{
return false;
}
}
IDList.Add(dr["ERPID"].ToString());
sql = string.Format(@"INSERT INTO MES_DW(ID, OLDNUM, OLDNAME, NEWNUM,OLDQTY, NEWQTY,CREATE_TIME)
VALUES ( '{0}', '{1}', '{2}', '{3}', '{4}','{5}', sysdate)",
dr["ERPID"], dr["DWA001"], dr["DWA002"], dr["DWA003"], dr["DWA004"], dr["DWA005"]);
SQLStringList.Add(sql);
}
return _sqlHelper.ExecuteSqlTran(SQLStringList);
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
/* sql = string.Format(@" update MES_ITEMS
set
BE_CHANGED=1,
ID='{1}',
E_ITEM_ID='{2}',
ITEM_NO='{3}',
ITEM_NAME='{4}',
ITEM_MODEL='{5}',
ITEM_TYPE='{6}',
LOWLIMIT='{7}',
ITEM_UNIT='{8}',
ERP_ITEM_NO='{9}',
COMPANY='1000',
FACTORY='1000',
EXT_DATE='{10}',
DEPOT_CODE='{11}',
PRD_PACK='{12}',
HIGHLIMIT='{13}',
PRD_CHECK='{14}',
WLA024='{15}' ,
PART='{16}'
where ID='{0}' ", dr["ERPID"], dr["ERPID"], dr["ERPID"], dr["WLA001"], dr["WLA002"], dr["WLA003"], dr["WLA011"], dr["WLA006"], dr["WLA004"], dr["WLA001"], dr["WLA012"], dr["WLA010"], dr["WLA008"], dr["WLA007"], dr["WLA013"], dr["WLA005"],ddr["TARGET_TABLE"]);
SQLStringListT.Add(sql);*/
}
return _sqlHelper.ExecuteSqlTran(SQLStringListT);
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from MES_ITEMS where ERPID in ('{0}'); ", string.Join("','", IDList));
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 21.ERPKHA客户信息(同步推送) bool TB_ERPTOMES_KH(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPKHA客户信息(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_KH(DataSet ds, string operatype, ref string ErrorMsg, DataRow ddr)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
if (operatype == "INSERT" || operatype == "UPDATE")
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
//处理前判断是否存在
// operatype = DataIsInDB("COMMAA", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT")
{
#region INSERT
//dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
//dr["AUDITING"] = "Y";
//dr["KHA002"] = dr["KHA003"];
sql = string.Format(@" INSERT INTO MES_CUSTOMER(ID,CUST_NO,CUST_NAME,CUST_SNAME,STRAS,ANRED,TELF1,REMARK,COMPANY,FACTORY,PART)
VALUES ('{0}','{1}','{2}','{3}','{7}','{4}','{5}','{6}','1000','1000','{7}') ", dr["ERPID"], dr["KHA001"], dr["KHA003"], dr["KHA002"], dr["KHA005"], dr["KHA006"], dr["KHA007"], dr["KHA004"], ddr["TARGET_TABLE"]);
SQLStringList.Add(sql);
}
return _sqlHelper.ExecuteSqlTran(SQLStringList);
//插入前先删除重复键
// DeleteDataBeforeInsert("COMMAA", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("COMMAA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "COMMAA重复键删除失败！";
// return false;
//}
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql += string.Format(@" update COMMAA
set
MAA001='{1}',
MAA002='{2}',
MAA003='{3}',
MAA004='{4}',
MAA009='{5}',
MAA010='{6}',
MAA006='{7}'
where ERPID='{0}'; ", dr["ERPID"]
, dr["KHA001"], dr["KHA003"], dr["KHA003"], dr["KHA004"], dr["KHA005"], dr["KHA006"], dr["KHA007"]);
}
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from COMMAA where ERPID in ('{0}'); ", string.Join("','", IDList));
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 22.ERPGYS供应商信息(同步推送) bool TB_ERPTOMES_GYS(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPGYS供应商信息(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_GYS(DataSet ds, string operatype, ref string ErrorMsg, DataRow drr)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
if (operatype == "INSERT" || operatype == "UPDATE")
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
//处理前判断是否存在
//operatype = DataIsInDB("PURMAA", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT")
{
#region INSERT
foreach (DataRow dr in dt.Rows)
{
string sqlT = "";
string SQLF = "";
sqlT = string.Format(@"SELECT count(*) cou FROM mes_supplier WHERE ID='{0}' ", dr["ERPID"]);
//int a = _sqlHelper.ExecuteNonQuery(sqlT);
int c = Convert.ToInt32(_sqlHelper.GetSingle(sqlT));
if (c != 0)
{
try
{
SQLF = string.Format(@"DELETE FROM mes_supplier WHERE ID='{0}'", dr["ERPID"]);
int B = _sqlHelper.ExecuteNonQuery(SQLF);
}
catch (Exception)
{
return false;
}
}
IDList.Add(dr["ERPID"].ToString());
dr["GYS002"] = dr["GYS003"];
sql = string.Format(@" INSERT INTO mes_supplier(SUPP_NO,SUPP_NAME,SUPP_SNAME,ID,STRAS,COMPANY,FACTORY,PART)
VALUES ('{0}','{1}','{2}','{3}',0,'1000','1000','{4}') ", dr["GYS001"], dr["GYS003"], dr["GYS003"], dr["ERPID"], drr["TARGET_TABLE"]);
SQLStringList.Add(sql);
}
return _sqlHelper.ExecuteSqlTran(SQLStringList);
//插入前先删除重复键
// DeleteDataBeforeInsert("PURMAA", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("PURMAA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "PURMAA重复键删除失败！";
// return false;
//}
//OracleBulkCopyColumnMapping[] colMapping = new OracleBulkCopyColumnMapping[dt.Columns.Count];
//colMapping[0] = new OracleBulkCopyColumnMapping("GYS001", "SUPP_NO"); // 供应商编号
//colMapping[1] = new OracleBulkCopyColumnMapping("GYS002", "SUPP_NAME"); // 供应商简称
//colMapping[2] = new OracleBulkCopyColumnMapping("GYS003", "SUPP_NAME"); // 供应商名称
////colMapping[3] = new OracleBulkCopyColumnMapping("GYS004", "MAA004"); // 供应商英文
////colMapping[4] = new OracleBulkCopyColumnMapping("GYS005", "MAA009"); // 联系人
////colMapping[5] = new OracleBulkCopyColumnMapping("GYS006", "MAA010"); // 电话
////colMapping[6] = new OracleBulkCopyColumnMapping("GYS007", "MAA007"); // 备注
//colMapping[3] = new OracleBulkCopyColumnMapping("ERPID", "ID"); // ERPID
////colMapping[8] = new OracleBulkCopyColumnMapping("MAA005", "MAA005"); // 内/外销
////colMapping[8] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
//SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
//colMapping[0] = new SqlBulkCopyColumnMapping("CKA001", "MDA001"); // 仓库编号
//colMapping[1] = new SqlBulkCopyColumnMapping("CKA002", "MDA002"); // 仓库名称
//colMapping[2] = new SqlBulkCopyColumnMapping("CKA003", "MDA005"); // 质量状态
//colMapping[3] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); // ERPID
//colMapping[4] = new SqlBulkCopyColumnMapping("MDA003", "MDA003"); // ERP仓库编号
//colMapping[5] = new SqlBulkCopyColumnMapping("MDA004", "MDA004"); // 部材类型 A=原材料,B=半成品,C=成品
//colMapping[6] = new SqlBulkCopyColumnMapping("MDA006", "MDA006"); // 仓库性质 1=存货仓 0=非存货仓
//colMapping[7] = new SqlBulkCopyColumnMapping("MDA007", "MDA007"); // 纳入可用量计算 Y=纳入，N=不纳入
//colMapping[8] = new SqlBulkCopyColumnMapping("MDA008", "MDA008"); // 库存量不足准许出库 Y=可以，N=不可以
//colMapping[9] = new SqlBulkCopyColumnMapping("MDA009", "MDA009"); // 是否参与盘点 Y=参与，N=不参与
//colMapping[10] = new SqlBulkCopyColumnMapping("MDA011", "MDA011"); // 仓库状态 Y=生效，N=失效
//colMapping[11] = new SqlBulkCopyColumnMapping("MDA012", "MDA012");//仓库性质 1良品2不良品3待检品4报废品5途中品
// return DataTableToSQLServer(dt, conStr, "mes_supplier", colMapping, ref ErrorMsg);
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql += string.Format(@" update mes_supplier
set
SUPP_NO='{1}',
SUPP_NAME='{2}',
SUPP_SNAME='{3}'
where ERPID='{0}'; ", dr["ERPID"]
, dr["GYS001"], dr["GYS003"], dr["GYS003"]);
}
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
else if (operatype == "DELETE")//DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from mes_supplier where ID in ('{0}'); ", string.Join("','", IDList));
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
else
return false;
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 23.ERPCKA仓库信息档(同步推送) bool TB_ERPTOMES_CK(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPCKA仓库信息档(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_CK(DataSet ds, string operatype, ref string ErrorMsg, DataRow ddr)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
if (operatype == "INSERT" || operatype == "UPDATE")
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
//处理前判断是否存在
//operatype = DataIsInDB("BASMDA", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT")
{
#region INSERT
foreach (DataRow dr in dt.Rows)
{
string sqlT = "";
string SQLF = "";
sqlT = string.Format(@"SELECT count(*) cou FROM MES_DEPOTS WHERE DEPOT_ID='{0}' ", dr["ERPID"]);
//int a = _sqlHelper.ExecuteNonQuery(sqlT);
int c = Convert.ToInt32(_sqlHelper.GetSingle(sqlT));
if (c != 0)
{
try
{
SQLF = string.Format(@"DELETE FROM MES_DEPOTS WHERE DEPOT_ID='{0}'", dr["ERPID"]);
int B = _sqlHelper.ExecuteNonQuery(SQLF);
}
catch (Exception)
{
return false;
}
}
if (dr["CKA003"].ToString() == "1")
{
dr["CKA003"] = "0";
}
else
{
dr["CKA003"] = "1";
}
IDList.Add(dr["ERPID"].ToString());
sql = string.Format(@" INSERT INTO MES_DEPOTS(DEPOT_ID,DEPOT_CODE,DEPOT_NAME,IS_NG,COMPANY,FACTORY,ZUID,PART)
VALUES ('{0}','{1}','{2}','{3}','1000','1000','{4}','{5}') ", dr["ERPID"], dr["CKA001"], dr["CKA002"], dr["CKA003"], dr["CKA007"], ddr["TARGET_TABLE"]);
SQLStringList.Add(sql);
}
//插入前先删除重复键
// DeleteDataBeforeInsert("BASMDA", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("BASMDA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "BASMDA重复键删除失败！";
// return false;
//}
return _sqlHelper.ExecuteSqlTran(SQLStringList);
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql += string.Format(@" update BASMDA
set
MDA001='{1}',
MDA002='{2}',
MDA005='{3}'
where ERPID='{0}'; ", dr["ERPID"]
, dr["CKA001"], dr["CKA002"], dr["CKA003"]);
}
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from BASMDA where ERPID in ('{0}'); ", string.Join("','", IDList));
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 24.ERPTLA生产投料单(同步推送) bool TB_ERPTOMES_TL(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPTLA生产投料单(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_TL(DataSet ds, string operatype, ref string ErrorMsg)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
ArrayList SQLStringListT = new ArrayList();
string dtName = "";
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
try
{
foreach (DataTable dt in ds.Tables)
{
dtName = dt.TableName;
IDList.Clear();
SQLStringList.Clear();
SQLStringListT.Clear();
//主从表的同步，修改和新增一样，先删除原记录后插入新记录,要求同步过来的数据也是整单完整数据
if (operatype == "INSERT")
{
#region INSERT
if (dt.TableName == "ERPTLA")
{
#region 投料单主表的处理
foreach (DataRow dr in dt.Rows)
{
// 检查下游单据 WOMDAA 是否存在影响同步的数据
/* 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));
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"]);
return false;
}
//2025-01-22 修改单据状态审核/反审核
// 获取 typeA 字段的值
string typeA = dr["TYPEA"].ToString();
// 如果 typeA 是 'C'，执行插入操作
if (typeA == "C")
{
//插入前先检查是否存在相同数据，若存在则删除
/* string sqlT = "";
string SQLF = "";
string SQLD = "";
sqlT = string.Format(@"SELECT count(*) cou FROM WOMCAA WHERE CAA001='{0}'", dr["TLA001"]);
//int a = _sqlHelper.ExecuteNonQuery(sqlT);
int c = Convert.ToInt32(_sqlHelper.GetSingle(sqlT));
if (c != 0)
{
try
{
SQLF = string.Format(@"DELETE WOMCAA WHERE CAA001='{0}'", dr["TLA001"]);
int B = _sqlHelper.ExecuteNonQuery(SQLF);
SQLD = string.Format(@"DELETE WOMCAB WHERE CAB001='{0}' WWLL_TYPE is null", dr["ERPID"]);
int D = _sqlHelper.ExecuteNonQuery(SQLD);
}
catch (Exception)
{
return false;
}
}*/
string SQLF = string.Format(@"DELETE WOMCAA WHERE CAA001='{0}'", dr["TLA001"]);
int B = _sqlHelper.ExecuteNonQuery(SQLF);
string SQLD = string.Format(@"DELETE WOMCAB WHERE CAB001='{0}' and WWLL_TYPE is 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,TB_TIME,FSTATES,TYPEA)
VALUES (seq_down.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}',1,'{21}',sysdate,'{22}','{23}') ", dr["ERPID"], dr["TLA001"], dr["TLA002"], dr["TLA003"], dr["TLA004"], dr["TLA005"], dr["TLA006"], dr["TLA007"], dr["TLA008"], dr["TLA009"], dr["TLA010"], dr["TLA011"], dr["TLA012"], dr["TLA013"], dr["TLA014"], dr["TLA015"], dr["TLA016"], dr["TLA017"], dr["TLA018"], dr["TLA019"], dr["TLA020"], dr["TLA012"], dr["FSTATUS"], dr["TYPEA"]);
SQLStringList.Add(sql);
_sqlHelper.ExecuteSqlTran(SQLStringList);
}
else
{
string SQLF = string.Format(@"DELETE WOMCAA WHERE CAA001='{0}'", dr["TLA001"]);
int B = _sqlHelper.ExecuteNonQuery(SQLF);
string SQLD = string.Format(@"DELETE WOMCAB WHERE CAB001='{0}' and WWLL_TYPE is null", dr["ERPID"]);
int D = _sqlHelper.ExecuteNonQuery(SQLD);
}
}
#endregion
}
if (dt.TableName == "ERPTLB")
{
#region 投料单明细的处理
foreach (DataRow dr in dt.Rows)
{
sql = string.Format(@" INSERT INTO WOMCAB(ID,ERPID,CAB001,CAB002,CAB003,CAB004,CAB005,CAB006,CAB007,CAB008,CAB009,CAB010,CAB011,CAB012,CAB013,CAB014,CAB015,CAB016,TB_TIME,TYPEB,BILLTYPE)
VALUES (seq_down.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}',sysdate,'{17}','{18}') ", dr["ERPID"], dr["TLB001"], dr["TLB002"], dr["TLB003"], dr["TLB004"], dr["TLB005"], dr["TLB006"], dr["TLB007"], dr["TLB008"], dr["TLB009"], dr["TLB010"], dr["TLB011"], dr["TLB012"], dr["TLB013"], dr["TLB014"], dr["TLB015"], dr["TLB016"], dr["TYPEB"], dr["BILLTYPE"]);
SQLStringListT.Add(sql);
}
_sqlHelper.ExecuteSqlTran(SQLStringListT);
#endregion
}
if (dt.TableName == "ERPWWTLA")
{
#region 投料单主表的处理
foreach (DataRow dr in dt.Rows)
{
// 获取 typeA 字段的值
string typeA = dr["TYPEA"].ToString();
// 如果 typeA 是 'C'，执行插入操作
if (typeA == "C")
{
//插入前先检查是否存在相同数据，若存在则删除
/* string sqlT = "";
string SQLF = "";
string SQLD = "";
sqlT = string.Format(@"SELECT count(*) cou FROM WOMCAA WHERE CAA001='{0}'", dr["WWTLA001"]);
//int a = _sqlHelper.ExecuteNonQuery(sqlT);
int c = Convert.ToInt32(_sqlHelper.GetSingle(sqlT));
if (c != 0)
{
try
{
SQLF = string.Format(@"DELETE WOMCAA WHERE CAA001='{0}'", dr["WWTLA001"]);
int B = _sqlHelper.ExecuteNonQuery(SQLF);
SQLD = string.Format(@"DELETE WOMCAB WHERE CAB001='{0}' and WWLL_TYPE=1", dr["ERPID"]);
int D = _sqlHelper.ExecuteNonQuery(SQLD);
}
catch (Exception)
{
return false;
}
}*/
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 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)
VALUES (seq_down.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}',1,'{21}','{22}','{23}','{24}','{25}',1,'{26}','{27}') ", dr["ERPID"], dr["WWTLA001"], dr["WWTLA002"], dr["WWTLA003"], dr["WWTLA004"], dr["WWTLA005"], dr["WWTLA006"], dr["WWTLA007"], dr["WWTLA008"], dr["WWTLA009"], dr["WWTLA010"], dr["WWTLA011"], dr["WWTLA012"], dr["WWTLA013"], dr["WWTLA014"], dr["WWTLA015"], dr["WWTLA016"], dr["WWTLA017"], dr["WWTLA018"], dr["WWTLA019"], dr["WWTLA020"], dr["WWTLA012"], dr["WWTLA021"], dr["WWTLA023"], dr["WWTLA024"], dr["WWTLA025"], dr["FSTATUS"], dr["TYPEA"]);
SQLStringList.Add(sql);
}else
{
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 is not null", dr["ERPID"]);
int D = _sqlHelper.ExecuteNonQuery(SQLD);
}
}
_sqlHelper.ExecuteSqlTran(SQLStringList);
#endregion
}
if (dt.TableName == "WWTLB")
{
#region 投料单明细的处理
foreach (DataRow dr in dt.Rows)
{
sql = string.Format(@" INSERT INTO WOMCAB(ID,ERPID,CAB001,CAB002,CAB003,CAB004,CAB005,CAB006,CAB007,CAB008,CAB009,CAB010,CAB011,CAB012,CAB013,CAB014,CAB015,CAB016,CAB019,CAB018,WWLL_TYPE,TB_TIME,TYPEB,BILLTYPE)
VALUES (seq_down.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}',sysdate,'{20}','{21}') ", dr["ERPID"], dr["WWTLB001"], dr["WWTLB002"], dr["WWTLB003"], dr["WWTLB004"], dr["WWTLB005"], dr["WWTLB006"], dr["WWTLB007"], dr["WWTLB008"], dr["WWTLB009"], dr["WWTLB010"], dr["WWTLB011"], dr["WWTLB012"], dr["WWTLB013"], dr["WWTLB014"], dr["WWTLB015"], dr["WWTLB016"], dr["WWTLB017"], dr["WWTLB018"], dr["WWTLB019"], dr["TYPEB"], dr["BILLTYPE"]);
SQLStringListT.Add(sql);
}
_sqlHelper.ExecuteSqlTran(SQLStringListT);
#endregion
}
#endregion
}
else if (operatype == "UPDATE")
{
#region INSERT
if (dt.TableName == "ERPTLA")
{
#region 投料单主表的处理
foreach (DataRow dr in dt.Rows)
{
// 检查下游单据 WOMDAA 是否存在影响同步的数据
string sqlCheck = string.Format(@"SELECT COUNT(*) FROM WOMDAA WHERE DAA014 = '{0}' AND DAA011 > 0", dr["TLA001"]);
int downstreamCount = Convert.ToInt32(_sqlHelper.GetSingle(sqlCheck));
// 如果存在下游单据且数量 > 0，则返回失败
if (downstreamCount > 0)
{
ErrorMsg = string.Format("存在下游单据，无法同步数据: 主表记录 TLA001 = {0}", dr["TLA001"]);
return false;
}
//插入前先检查是否存在相同数据，若存在则删除
string sqlT = "";
string SQLF = "";
string SQLD = "";
sqlT = string.Format(@"SELECT count(*) cou FROM WOMCAA WHERE CAA001='{0}'", dr["TLA001"]);
//int a = _sqlHelper.ExecuteNonQuery(sqlT);
int c = Convert.ToInt32(_sqlHelper.GetSingle(sqlT));
if (c != 0)
{
try
{
SQLF = string.Format(@"DELETE WOMCAA WHERE CAA001='{0}'", dr["TLA001"]);
int B = _sqlHelper.ExecuteNonQuery(SQLF);
SQLD = string.Format(@"DELETE WOMCAB WHERE CAB001='{0}'", dr["ERPID"]);
int D = _sqlHelper.ExecuteNonQuery(SQLD);
}
catch (Exception)
{
return false;
}
}
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,BE_REFRESH)
VALUES (seq_down.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}',1,'{21}',1) ", dr["ERPID"], dr["TLA001"], dr["TLA002"], dr["TLA003"], dr["TLA004"], dr["TLA005"], dr["TLA006"], dr["TLA007"], dr["TLA008"], dr["TLA009"], dr["TLA010"], dr["TLA011"], dr["TLA012"], dr["TLA013"], dr["TLA014"], dr["TLA015"], dr["TLA016"], dr["TLA017"], dr["TLA018"], dr["TLA019"], dr["TLA020"], dr["TLA012"]);
SQLStringList.Add(sql);
}
_sqlHelper.ExecuteSqlTran(SQLStringList);
#endregion
}
if (dt.TableName == "ERPTLB")
{
#region 投料单明细的处理
foreach (DataRow dr in dt.Rows)
{
sql = string.Format(@" INSERT INTO WOMCAB(ID,ERPID,CAB001,CAB002,CAB003,CAB004,CAB005,CAB006,CAB007,CAB008,CAB009,CAB010,CAB011,CAB012,CAB013,CAB014,CAB015,CAB016)
VALUES (seq_down.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}') ", dr["ERPID"], dr["TLB001"], dr["TLB002"], dr["TLB003"], dr["TLB004"], dr["TLB005"], dr["TLB006"], dr["TLB007"], dr["TLB008"], dr["TLB009"], dr["TLB010"], dr["TLB011"], dr["TLB012"], dr["TLB013"], dr["TLB014"], dr["TLB015"], dr["TLB016"]);
SQLStringListT.Add(sql);
}
_sqlHelper.ExecuteSqlTran(SQLStringListT);
#endregion
}
if (dt.TableName == "ERPWWTLA")
{
#region 投料单主表的处理
foreach (DataRow dr in dt.Rows)
{
//插入前先检查是否存在相同数据，若存在则删除
string sqlT = "";
string SQLF = "";
string SQLD = "";
sqlT = string.Format(@"SELECT count(*) cou FROM WOMCAA WHERE CAA001='{0}'", dr["WWTLA001"]);
//int a = _sqlHelper.ExecuteNonQuery(sqlT);
int c = Convert.ToInt32(_sqlHelper.GetSingle(sqlT));
if (c != 0)
{
try
{
SQLF = string.Format(@"DELETE WOMCAA WHERE CAA001='{0}'", dr["WWTLA001"]);
int B = _sqlHelper.ExecuteNonQuery(SQLF);
SQLD = string.Format(@"DELETE WOMCAB WHERE CAB001='{0}'", dr["ERPID"]);
int D = _sqlHelper.ExecuteNonQuery(SQLD);
}
catch (Exception)
{
return false;
}
}
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,BE_REFRESH)
VALUES (seq_down.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}',1,'{21}',1) ", dr["ERPID"], dr["WWTLA001"], dr["WWTLA002"], dr["WWTLA003"], dr["WWTLA004"], dr["WWTLA005"], dr["WWTLA006"], dr["WWTLA007"], dr["WWTLA008"], dr["WWTLA009"], dr["WWTLA010"], dr["WWTLA011"], dr["WWTLA012"], dr["WWTLA013"], dr["WWTLA014"], dr["WWTLA015"], dr["WWTLA016"], dr["WWTLA017"], dr["WWTLA018"], dr["WWTLA019"], dr["WWTLA020"], dr["WWTLA012"]);
SQLStringList.Add(sql);
}
_sqlHelper.ExecuteSqlTran(SQLStringList);
#endregion
}
if (dt.TableName == "WWTLB")
{
#region 投料单明细的处理
foreach (DataRow dr in dt.Rows)
{
sql = string.Format(@" INSERT INTO WOMCAB(ID,ERPID,CAB001,CAB002,CAB003,CAB004,CAB005,CAB006,CAB007,CAB008,CAB009,CAB010,CAB011,CAB012,CAB013,CAB014,CAB015,CAB016,CAB019,CAB018)
VALUES (seq_down.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}') ", dr["ERPID"], dr["WWTLB001"], dr["WWTLB002"], dr["WWTLB003"], dr["WWTLB004"], dr["WWTLB005"], dr["WWTLB006"], dr["WWTLB007"], dr["WWTLB008"], dr["WWTLB009"], dr["WWTLB010"], dr["WWTLB011"], dr["WWTLB012"], dr["WWTLB013"], dr["WWTLB014"], dr["WWTLB015"], dr["WWTLB016"], dr["WWTLB017"], dr["WWTLB018"]);
SQLStringListT.Add(sql);
}
_sqlHelper.ExecuteSqlTran(SQLStringListT);
#endregion
}
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
if (dt.TableName == "ERPTLA")
{
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from WOMCAA where ERPID in ('{0}'); delete from WOMCAB where cab001 in ('{0}'); ", string.Join("','", IDList));
}
if (dt.TableName == "ERPWWTLA")
{
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from WOMCAA where ERPID in ('{0}'); delete from WOMCAB where cab001 in ('{0}'); ", string.Join("','", IDList));
}
_sqlHelper.ExecuteNonQuery(sql);
#endregion
}
}
return true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
return false;
}
}
#endregion
#region 25.MesToErp(同步推送) bool MesToErp(DataRow dr, ref string ErrorMsg)
///
/// MesToErp(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool MesToErp(DataRow dr, ref string ErrorMsg)
{
try
{
string task_name = dr["task_name"].ToString();
string descript = dr["descript"].ToString();
string create_time = dr["create_time"].ToString();
string creator = dr["creator"].ToString();
string operatype = dr["operatype"].ToString();
string source_table = dr["source_table"].ToString();
string target_table = dr["target_table"].ToString();
string data = dr["data"].ToString();
string data_inserted = dr["data_inserted"].ToString();
string data_deleted = dr["data_deleted"].ToString();
WebReference.WebService1 ws = new WebReference.WebService1();
string res = ws.mesToErpinfo(task_name, descript, create_time, creator, operatype, data, data_inserted, "NEW", "");
// string res = ws.GetJsonByMes(task_name, descript, create_time, creator, operatype, source_table, target_table, data, data_inserted, data_deleted);
if (res.Contains("成功"))
return true;
else
{
ErrorMsg = res;
return false;
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 26.MesToErpbs mes推ERP标识bool MesToErp(DataRow dr, ref string ErrorMsg)
///
/// MesToErp(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool MESTOERPBS(DataRow dr, ref string ErrorMsg)
{
try
{
string task_name = dr["task_name"].ToString();
string descript = dr["descript"].ToString();
string create_time = dr["create_time"].ToString();
string creator = dr["creator"].ToString();
string operatype = dr["operatype"].ToString();
string source_table = dr["source_table"].ToString();
string target_table = dr["target_table"].ToString();
string data = dr["data"].ToString();
string data_inserted = dr["data_inserted"].ToString();
string data_deleted = dr["data_deleted"].ToString();
string data_insertedf = dr["data_insertedf"].ToString();
string type = "采购订单";
WebReference.WebService1 ws = new WebReference.WebService1();
string res = ws.updateMesTabFormal(source_table, data_inserted, type);
// string res = ws.GetJsonByMes(task_name, descript, create_time, creator, operatype, source_table, target_table, data, data_inserted, data_deleted);
if (res.Contains("\"IsSuccess\":true"))
return true;
else
{
ErrorMsg = res;
return false;
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 26.ERPDHA到货单(同步推送) bool TB_ERPTOMES_SLTZD(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPDHA到货单(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_SLTZD(DataSet ds, string operatype, ref string ErrorMsg)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
ArrayList SQLStringListT = new ArrayList();
string dtName = "";
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
try
{
foreach (DataTable dt in ds.Tables)
{
dtName = dt.TableName;
IDList.Clear();
//主从表的同步，修改和新增一样，先删除原记录后插入新记录,要求同步过来的数据也是整单完整数据
if (operatype == "INSERT" || operatype == "UPDATE")
{
#region INSERT
if (dt.TableName == "ERPSLTZA")
{
#region 到货单主表的处理
dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
////插入前先删除重复键
string sqlT = "";
string sqlG = "";
string SQLF = "";
string SQLD = "";
sqlG = string.Format(@"SELECT count(*) cou FROM PURDHB WHERE DHB001='{0}' AND DHB015>0", dr["CGA001"]);
sqlT = string.Format(@"SELECT count(*) cou FROM PURDHA WHERE DHA001='{0}'", dr["CGA001"]);
//int a = _sqlHelper.ExecuteNonQuery(sqlT);
int g = Convert.ToInt32(_sqlHelper.GetSingle(sqlG));
int c = Convert.ToInt32(_sqlHelper.GetSingle(sqlT));
if (g == 0)
{
if (c != 0)
{
try
{
//删除主表
SQLF = string.Format(@"DELETE PURDHA WHERE DHA001='{0}'", dr["CGA001"]);
int B = _sqlHelper.ExecuteNonQuery(SQLF);
//删除从表
SQLD = string.Format(@"DELETE PURDHB WHERE DHB001='{0}'", dr["CGA001"]);
int D = _sqlHelper.ExecuteNonQuery(SQLD);
}
catch (Exception)
{
return false;
}
}
}
else
{
return false;
}
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = 1;
string f = DateTime.Now.ToString("yyyy-MM-dd");
string t = DateTime.Now.ToShortTimeString().ToString();
string TIME = (f + " " + t).ToString();
sql = string.Format(@" INSERT INTO PURDHA(ID,DHA001,DHA002,DHA003,DHA004,DHA007,DHA008,DHA009,DHA010,DHA011,DHA012,DHA016,DHA019,ERPID,AUDITING,DHA014,DHA018,DHA017,DHA020,CREATEUSER)
VALUES (seq_down.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}') ", dr["CGA001"], dr["CGA003"], dr["CGA004"]
, dr["CGA003"], dr["CGA005"], "", "", "", "", dr["CGA012"], "", "否", dr["ERPID"], dr["AUDITING"], dr["CGA006"], TIME, dr["CGA010"], dr["CGA002"], dr["CGA009"]);
SQLStringList.Add(sql);
}
_sqlHelper.ExecuteSqlTran(SQLStringList);
#endregion
}
if (dt.TableName == "ERPSLTZD")
{
#region 到货单明细的处理
dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
Double a = Convert.ToDouble(dr["CGB008"].ToString()) - Convert.ToDouble(dr["CGB013"].ToString());
dr["AUDITING"] = 1;
sql = string.Format(@" INSERT INTO PURDHB(ID,DHB001,DHB002,DHB003,DHB006,DHB007,DHB017,DHB018,DHB014,DHB032,DHB024,DHB037,AUDITING,DHB038,DHB039,DHB040,DHB041,DHB029,DHB013,DHB015,DHB016,DHB023,DHB042)
VALUES (seq_down.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','0','0','0','未检验','{17}') ", dr["CGB001"], dr["CGB002"], dr["CGB003"]
, dr["CGB008"], dr["CGB009"], dr["CGB010"], dr["CGB011"], dr["CGB012"], dr["CGB013"], dr["CGB014"], dr["ERPID"], dr["AUDITING"], dr["CGB004"], dr["CGB005"], dr["CGB006"], dr["CGB007"], a, dr["CGB015"]);
SQLStringListT.Add(sql);
}
_sqlHelper.ExecuteSqlTran(SQLStringListT);
#endregion
}
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
if (dt.TableName == "ERPDHA")
{
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from PURDHA where ERPID in ('{0}'); delete from PURDHB where ERPID in ('{0}'); ", string.Join("','", IDList));
}
_sqlHelper.ExecuteNonQuery(sql);
#endregion
}
}
return true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
return false;
}
}
#endregion
private bool TB_ERPTOMES_JA(DataSet ds, string operatype, ref string ErrorMsg, DataRow ddr)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
if (operatype == "INSERT" || operatype == "UPDATE")
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
IDList.Add(dr["JA002"].ToString());
}
//处理前判断是否存在
// operatype = DataIsInDB("COMMAA", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "UPDATE" || operatype == "INSERT")
{
#region INSERT
//dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
string sqlG = string.Format(@"SELECT count(*) cou FROM JABS WHERE PPBOM='{0}'", dr["JA001"]);
int g = Convert.ToInt32(_sqlHelper.GetSingle(sqlG));
if (g != 0)
{
try
{
//删除主表
string SQLF = string.Format(@"DELETE JABS WHERE PPBOM='{0}'", dr["JA001"]);
int B = _sqlHelper.ExecuteNonQuery(SQLF);
}
catch (Exception)
{
return false;
}
}
IDList.Add(dr["JA002"].ToString());
//dr["AUDITING"] = "Y";
//dr["KHA002"] = dr["KHA003"];
sql = string.Format(@" INSERT INTO JABS(ID,PPBOM,STATES,LINE_ID,TB_TIME)
VALUES ('{0}','{1}','{2}','{3}', to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS')) ", dr["JA002"], dr["JA001"], dr["JA003"], dr["JA004"]);
SQLStringList.Add(sql);
if (dr["JA003"] != null && dr["JA003"].ToString().Equals("结案", StringComparison.OrdinalIgnoreCase))
{
//修改工单为完工
string daa014 = dr["JA001"].ToString();
// string sqlja = string.Format(@"update womdaa set daa018='完工',WORK_BY='ERP结案',DAA017=sysdate where daa001 in (select a.daa001 from womdaa a left join JABS b on a.daa014=b.PPBOM where b.STATES='结案' and a.daa018 <>'完工')");
string sqlja = string.Format(@"update womdaa set daa018='完工',WORK_BY='ERP结案',DAA017=sysdate where daa018<>'完工' 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
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);
}
}
}
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), "");
//if (!DeleteDataBeforeInsert("COMMAA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "COMMAA重复键删除失败！";
// return false;
//}
#endregion
}
/* else if (operatype == "INSERT")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["JA002"].ToString());
//dr["AUDITING"] = "Y";
//dr["KHA002"] = dr["KHA003"];
sql = string.Format(@" INSERT INTO JABS(ID,PPBOM,STATES,LINE_ID)
VALUES ('{0}','{1}','{2}','{3}') ", dr["JA002"], dr["JA001"], dr["JA003"], dr["JA004"]);
SQLStringList.Add(sql);
}
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}*/
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["JA002"].ToString());
}
sql = string.Format(@" delete from JABS where ID in ('{0}'); ", string.Join("','", IDList));
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#region 27.ERPTHCONFIRM供应商退货确认 bool TB_ERPTOMES_TH_CONFIRM(DataSet ds, string operatype, ref string ErrorMsg)
private bool TB_ERPTOMES_TH_CONFIRM(DataSet ds, string operatype, ref string ErrorMsg)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
string dtName = "";
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
try
{
foreach (DataTable dt in ds.Tables)
{
dtName = dt.TableName;
IDList.Clear();
//主从表的同步，修改和新增一样，先删除原记录后插入新记录,要求同步过来的数据也是整单完整数据
if (operatype == "INSERT" || operatype == "UPDATE")
{
#region 更新至供应商退货单明细表 PURDKB
if (dt.TableName == "ERPTHCONFIRM")
{
foreach (DataRow dr in dt.Rows)
{
sql += string.Format(@" UPDATE PURDKB SET DKB024 = '{0}', DKB025 = '{1}'
WHERE DKB012 = '{2}' AND DKB013 = '{3}'
AND DKB003 = '{4}'
AND DKB007 = '{5}'; ", dr["THC007"]
, dr["THC008"], dr["THC003"], dr["THC004"], dr["THC005"], dr["THC006"]);
}
#endregion
}
}
else //DELETE
{
continue;
}
}
_sqlHelper.ExecuteNonQuery(sql);
return true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
return false;
}
}
#endregion
#region 17.ERPBMA信息视图表(同步推送) bool TB_ERPTOMES_BM(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPBMA部门信息表(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_BAR(DataSet ds, string operatype, ref string ErrorMsg)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
//if (operatype == "INSERT" || operatype == "UPDATE")
//{
// foreach (DataRow dr in ds.Tables[0].Rows)
// {
// //IDList.Add(dr["ERPID"].ToString());
// }
// //处理前判断是否存在
// //operatype = DataIsInDB("HRMMAA", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
//}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT")
{
#region INSERT
foreach (DataRow dr in dt.Rows)
{
//IDList.Add(dr["ERPID"].ToString());
string bar011Value = dr["BAR011"].ToString();
bar011Value = bar011Value.Replace(",", "，");
//bar011Value = bar011Value.Replace("\\", string.Empty);
int messageIndex = bar011Value.IndexOf("\"Message\":");
bar011Value = bar011Value.Replace("'", string.Empty);
if (messageIndex >= 0)
{
// 从 "Message" 位置开始截取
// bar011Value = bar011Value.Substring(messageIndex);
bar011Value = bar011Value.Substring(messageIndex + "\"Message\":".Length).Trim();
// 确保最多截取 3000 字符
if (bar011Value.Length > 2000)
{
bar011Value = bar011Value.Substring(0,2000);
}
}
/* bar011Value = bar011Value.Replace("'","\"");
if (bar011Value.Length > 3000)
{
bar011Value = bar011Value.Substring(0, 2000);
}*/
sql = string.Format(@"INSERT INTO ERPBAR (BAR001, BAR002, BAR003, BAR004, BAR005, BAR006, BAR008, BAR009, BAR010, BAR011, BAR012,ID)
VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}', '{6}', '{7}', '{8}', '{9}', '{10}',f_getseqnextvalue('ERPBAR'))",
dr["BAR001"], dr["BAR002"], dr["BAR003"], dr["BAR004"], dr["BAR005"], dr["BAR006"], dr["BAR008"], dr["BAR009"], dr["BAR010"], bar011Value, dr["BAR012"]);
SQLStringList.Add(sql);
}
return _sqlHelper.ExecuteSqlTran(SQLStringList);
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql += string.Format(@" update HRMMAA
set
MAA002='{1}',
MAA003='{2}',
MAA004='{3}',
MAA005='{4}',
MAA006='{5}',
MAA007='{6}'
where ERPID='{0}'; ", dr["ERPID"], dr["BMA001"], dr["BMA002"], dr["BMA003"], dr["BMA004"], dr["BMA005"], dr["BMA006"]);
}
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from HRMMAA where ERPID in ('{0}'); ", string.Join("','", IDList));
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
private bool TB_ERPTOMES_CG(DataSet ds, string operatype, ref string ErrorMsg)
{
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
ArrayList SQLStringListT = new ArrayList();
string dtName = "";
bool flag = ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0;
bool result;
if (flag)
{
ErrorMsg = "生成数据表失败";
result = false;
}
else
{
try
{
foreach (object obj in ds.Tables)
{
DataTable dt = (DataTable)obj;
dtName = dt.TableName;
IDList.Clear();
SQLStringList.Clear();
SQLStringListT.Clear();
bool flag2 = operatype == "INSERT" || operatype == "UPDATE";
if (flag2)
{
//2025-01-21 新增审核状态执行
bool flag3 = dt.TableName == "ERPCGA";
if (flag3)
{
foreach (object obj2 in dt.Rows)
{
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"]);
int c = Convert.ToInt32(this._sqlHelper.GetSingle(sqlT));
bool flag4 = c != 0;
if (flag4)
{
try
{
string SQLF = string.Format("DELETE FROM MES_ROH_IN WHERE EBELN='{0}'", dr["CGA001"]);
int B = this._sqlHelper.ExecuteNonQuery(SQLF);
}
catch (Exception)
{
return false;
}
}
string sql = string.Format(" INSERT INTO MES_ROH_IN(ID,ERPID,EBELN,BEDAT,CHG_TIME,F_TYPE,SUPP_NO,FACTORY,MANDT,CGA011,CGA012,CGA013,CGA014,CHECK_BY,CHECK_DATE,MEMO,FDOCUMENTSTATUS,FCLOSESTATUS,FCANCELSTATUS) " +
"VALUES (SEQ_BASEINFO_ID.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}') ", new object[]
{
dr["ERPID"],
dr["CGA001"],
dr["CGA003"],
dr["CGA004"],
dr["CGA005"],
dr["CGA006"],
"1000",
dr["CGA009"],
dr["CGA011"],
dr["CGA012"],
dr["CGA013"],
dr["CGA014"],
dr["CGA015"],
dr["CGA016"],
dr["CGA017"],
dr["FDOCUMENTSTATUS"],
dr["FCloseStatus"],
dr["FCancelStatus"]
});
SQLStringList.Add(sql);
this._sqlHelper.ExecuteSqlTran(SQLStringList);
}
else
{
string sqlT = string.Format("SELECT count(*) cou FROM MES_ROH_IN WHERE EBELN='{0}'", dr["CGA001"]);
int c = Convert.ToInt32(this._sqlHelper.GetSingle(sqlT));
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);
bool flag4 = c != 0;
return true;
/* if (flag4)
{
try
{
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)
{
return false;
}
}*/
}
}
}
bool flag5 = dt.TableName == "ERPCGB";
if (flag5)
{
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 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,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"],
dr2["CGB002"],
dr2["CGB003"],
dr2["CGB004"],
dr2["CGB005"],
dr2["CGB006"],
dr2["CGB007"],
dr2["CGB008"],
dr2["CGB009"],
dr2["CGB010"],
dr2["CGB011"],
dr2["CGB012"],
dr2["CGB013"],
dr2["CGB014"],
dr2["CGB015"],
dr2["CGB016"],
dr2["CGB017"],
dr2["CGB018"],
dr2["CGB019"],
dr2["CGB020"],
dr2["ERPID"],
dr2["CGB021"],
dr2["CGB022"],
dr2["CGB012"],
dr2["CGB014"],
dr2["FMRPCloseStatus"],
dr2["FMRPTerminateStatus"],
urgency, gys_jhtime, gbbz.Replace("'", "''"), cgb.Replace("'", "''"),
cgb025, cgb026.Replace("'", "''"), cgb028
});
SQLStringListT.Add(sql2);
}
this._sqlHelper.ExecuteSqlTran(SQLStringListT);
}
#region 2025-01-21注释掉
/* bool flag3 = dt.TableName == "ERPCGA";
if (flag3)
{
foreach (object obj2 in dt.Rows)
{
DataRow dr = (DataRow)obj2;
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)
{
try
{
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)
{
return false;
}
}
string sql = string.Format(" INSERT INTO MES_ROH_IN(ID,ERPID,EBELN,BEDAT,CHG_TIME,F_TYPE,SUPP_NO,FACTORY,MANDT,CGA011,CGA012,CGA013,CGA014,CHECK_BY,CHECK_DATE,MEMO) " +
"VALUES (SEQ_BASEINFO_ID.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}') ", new object[]
{
dr["ERPID"],
dr["CGA001"],
dr["CGA003"],
dr["CGA004"],
dr["CGA005"],
dr["CGA006"],
"1000",
dr["CGA009"],
dr["CGA011"],
dr["CGA012"],
dr["CGA013"],
dr["CGA014"],
dr["CGA015"],
dr["CGA016"],
dr["CGA017"]
});
SQLStringList.Add(sql);
}
this._sqlHelper.ExecuteSqlTran(SQLStringList);
}
bool flag5 = dt.TableName == "ERPCGB";
if (flag5)
{
foreach (object obj3 in dt.Rows)
{
DataRow dr2 = (DataRow)obj3;
string sql = 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) " +
"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}') ", new object[]
{
dr2["ERPID"],
dr2["CGB001"],
dr2["CGB002"],
dr2["CGB003"],
dr2["CGB004"],
dr2["CGB005"],
dr2["CGB006"],
dr2["CGB007"],
dr2["CGB008"],
dr2["CGB009"],
dr2["CGB010"],
dr2["CGB011"],
dr2["CGB012"],
dr2["CGB013"],
dr2["CGB014"],
dr2["CGB015"],
dr2["CGB016"],
dr2["CGB017"],
dr2["CGB018"],
dr2["CGB019"],
dr2["CGB020"],
dr2["ERPID"],
dr2["CGB021"],
dr2["CGB022"],
dr2["CGB012"],
dr2["CGB014"],
});
SQLStringListT.Add(sql);
}
this._sqlHelper.ExecuteSqlTran(SQLStringListT);
}*/
#endregion
}
else
{
bool flag3 = dt.TableName == "ERPCGA";
if (flag3)
{
foreach (DataRow dr in dt.Rows)
{
// 获取采购订单号
string cga001 = dr["CGA001"].ToString();
// 执行删除操作
string SQLF = string.Format("DELETE FROM MES_ROH_IN WHERE EBELN='{0}'", cga001);
int rowsAffectedF = this._sqlHelper.ExecuteNonQuery(SQLF);
string SQLD = string.Format("DELETE FROM MES_ROH_IN_DATA WHERE EBELN='{0}'", cga001);
int rowsAffectedD = this._sqlHelper.ExecuteNonQuery(SQLD);
// 在这里可以根据 rowsAffectedF 和 rowsAffectedD 判断删除操作是否成功
// rowsAffectedF 和 rowsAffectedD 分别表示 MES_ROH_IN 和 MES_ROH_IN_DATA 表中受影响的行数
}
}
}
}
result = true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
result = false;
}
}
return result;
}
private bool TB_ERPTOMES_XSD(DataSet ds, string operatype, ref string ErrorMsg)
{
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
ArrayList SQLStringListT = new ArrayList();
string mid = "";
string dtName = "";
bool flag = ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0;
bool result;
if (flag)
{
ErrorMsg = "生成数据表失败";
result = false;
}
else
{
try
{
foreach (object obj in ds.Tables)
{
DataTable dt = (DataTable)obj;
dtName = dt.TableName;
IDList.Clear();
SQLStringList.Clear();
SQLStringListT.Clear();
bool flag2 = operatype == "INSERT" || operatype == "UPDATE";
if (flag2)
{
bool flag3 = dt.TableName == "ERPXSA";
if (flag3)
{
foreach (object obj2 in dt.Rows)
{
DataRow dr = (DataRow)obj2;
mid = dr["ERPID"].ToString();
string sqlT = string.Format("SELECT count(*) cou FROM MES_ERPXSA WHERE ERPID='{0}'", dr["ERPID"]);
int c = Convert.ToInt32(this._sqlHelper.GetSingle(sqlT));
bool flag4 = c != 0;
if (flag4)
{
try
{
string SQLF = string.Format("DELETE FROM MES_ERPXSA WHERE ERPID='{0}'", dr["ERPID"]);
int B = this._sqlHelper.ExecuteNonQuery(SQLF);
string SQLD = string.Format("DELETE FROM MES_ERPXSB WHERE PID='{0}'", dr["ERPID"]);
int D = this._sqlHelper.ExecuteNonQuery(SQLD);
}
catch (Exception)
{
return false;
}
}
string sql = string.Format(" INSERT INTO MES_ERPXSA(ERPID,XSA001,XSA002,XSA003,XSA004,XSA005,XSA006,XSA007,XSA008,XSA009,XSA010,XSA011,XSA012,XSA013,XSA014,XSA015) " +
"VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}') ", new object[]
{
dr["ERPID"],
dr["XSA001"],
dr["XSA002"],
dr["XSA003"],
dr["XSA004"],
dr["XSA005"],
"1000",
"1000",
dr["XSA008"],
dr["XSA009"],
dr["XSA010"],
dr["XSA011"],
dr["XSA012"],
dr["XSA013"],
dr["XSA014"],
dr["XSA015"]
});
SQLStringList.Add(sql);
}
this._sqlHelper.ExecuteSqlTran(SQLStringList);
}
bool flag5 = dt.TableName == "ERPXSB";
if (flag5)
{
foreach (object obj3 in dt.Rows)
{
DataRow dr2 = (DataRow)obj3;
string sql = string.Format(" INSERT INTO MES_ERPXSB(ID,ERPID,PID,XSB001,XSB002,XSB003,XSB004,XSB005,XSB006,XSB007,XSB008,XSB009,XSB010,XSB011,XSB012,XSB013,XSB014) " +
"VALUES (SEQ_BASEINFO_ID.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}') ", new object[]
{
dr2["ERPID"],
mid,
dr2["XSB001"],
dr2["XSB002"],
dr2["XSB003"],
dr2["XSB004"],
dr2["XSB005"],
dr2["XSB006"],
dr2["XSB007"],
dr2["XSB008"],
dr2["XSB009"],
dr2["XSB010"],
dr2["XSB011"],
dr2["XSB012"],
dr2["XSB013"],
dr2["XSB014"]
});
SQLStringListT.Add(sql);
}
this._sqlHelper.ExecuteSqlTran(SQLStringListT);
}
}
}
result = true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
result = false;
}
}
return result;
}
private bool TB_ERPTOMES_XSRK(DataSet ds, string operatype, ref string ErrorMsg)
{
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
ArrayList SQLStringListT = new ArrayList();
string mid = "";
string dtName = "";
bool flag = ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0;
bool result;
if (flag)
{
ErrorMsg = "生成数据表失败";
result = false;
}
else
{
try
{
foreach (object obj in ds.Tables)
{
DataTable dt = (DataTable)obj;
dtName = dt.TableName;
IDList.Clear();
SQLStringList.Clear();
SQLStringListT.Clear();
bool flag2 = operatype == "INSERT" || operatype == "UPDATE";
if (flag2)
{
bool flag3 = dt.TableName == "ERPXSRKA";
if (flag3)
{
foreach (object obj2 in dt.Rows)
{
DataRow dr = (DataRow)obj2;
//2025-01-22 修改审核字段
string FDOCUMENTSTATUS = dr["FDOCUMENTSTATUS"].ToString();
if (FDOCUMENTSTATUS == "C")
{
mid = dr["ERPID"].ToString();
string sqlT = string.Format("SELECT count(*) cou FROM XSRKA WHERE ERPID='{0}'", dr["ERPID"]);
int c = Convert.ToInt32(this._sqlHelper.GetSingle(sqlT));
string SQLF = string.Format("DELETE FROM XSRKA WHERE ERPID='{0}'", dr["ERPID"]);
int B = this._sqlHelper.ExecuteNonQuery(SQLF);
string SQLD = string.Format("DELETE FROM XSRKB WHERE PID='{0}'", dr["ERPID"]);
int D = this._sqlHelper.ExecuteNonQuery(SQLD);
string sql = string.Format(" INSERT INTO XSRKA(ID,FBillerID,FBillNo,FCheckerID ,FCheckDate,FDeptNumber,FDeptName,FCustIDNumber,FCustIDName,FEMPNumber,FEMPName,ERPID,TB_TIME，FDOCUMENTSTATUS) " +
"VALUES (SEQ_XS01.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',sysdate,'{11}') ", new object[]
{
dr["FBillerID"],
dr["FBillNo"],
dr["FCheckerID"],
dr["FCheckDate"],
dr["FDeptNumber"],
dr["FDeptName"],
dr["FCustIDNumber"],
dr["FCustIDName"],
dr["FEMPNumber"],
dr["FEMPName"],
dr["ERPID"],
dr["FDOCUMENTSTATUS"]
});
SQLStringList.Add(sql);
}
else
{
string SQLF = string.Format("DELETE FROM XSRKA WHERE ERPID='{0}'", dr["ERPID"]);
int B = this._sqlHelper.ExecuteNonQuery(SQLF);
string SQLD = string.Format("DELETE FROM XSRKB WHERE PID='{0}'", dr["ERPID"]);
int D = this._sqlHelper.ExecuteNonQuery(SQLD);
}
}
this._sqlHelper.ExecuteSqlTran(SQLStringList);
}
bool flag5 = dt.TableName == "ERPXSRKB";
if (flag5)
{
foreach (object obj3 in dt.Rows)
{
DataRow dr2 = (DataRow)obj3;
string sql = string.Format(" INSERT INTO XSRKB(ID,ERPID,FEntryID,FNumber,FName,FModel,FAmount,FAuxPrice,FAuxQty,FAuxStockQty,FBatchNo,FBCommitQty,FCommitQty,FNote,FOrderBillNo,FOrderEntryID,FOrderInterID,FPrice,FQty,FStockIDNumber,FStockIDName,FStockQty,FUnitID ,pid) " +
"VALUES (SEQ_XS02.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}') ", new object[]
{
dr2["ERPID"],
dr2["FEntryID"],
dr2["FNumber"],
dr2["FName"],
dr2["FModel"],
dr2["FAmount"],
dr2["FAuxPrice"],
dr2["FAuxQty"],
dr2["FAuxStockQty"],
dr2["FBatchNo"],
dr2["FBCommitQty"],
dr2["FCommitQty"],
dr2["FNote"],
dr2["FOrderBillNo"],
dr2["FOrderEntryID"],
dr2["FOrderInterID"],
dr2["FPrice"],
dr2["FQty"],
dr2["FStockIDNumber"],
dr2["FStockIDName"],
dr2["FStockQty"],
dr2["FUnitID"],mid
});
SQLStringListT.Add(sql);
}
this._sqlHelper.ExecuteSqlTran(SQLStringListT);
}
}
else
{
#region DELETE
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
// 构建删除SQL语句
string deleteSql = $"DELETE FROM XSRKA WHERE ERPID IN ('{string.Join("','", IDList)}')";
_sqlHelper.ExecuteNonQuery(deleteSql);
string deleteSql2 = $"DELETE FROM XSRKB WHERE PID IN ('{string.Join("','", IDList)}')";
_sqlHelper.ExecuteNonQuery(deleteSql2);
#endregion
}
}
result = true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
result = false;
}
}
return result;
}
private bool TB_ERPTOMES_XSCK(DataSet ds, string operatype, ref string ErrorMsg)
{
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
ArrayList SQLStringListT = new ArrayList();
string mid = "";
string dtName = "";
bool flag = ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0;
bool result;
if (flag)
{
ErrorMsg = "生成数据表失败";
result = false;
}
else
{
try
{
foreach (object obj in ds.Tables)
{
DataTable dt = (DataTable)obj;
dtName = dt.TableName;
IDList.Clear();
SQLStringList.Clear();
SQLStringListT.Clear();
bool flag2 = operatype == "INSERT" || operatype == "UPDATE";
if (flag2)
{
bool flag3 = dt.TableName == "ERPXSCKA";
if (flag3)
{
foreach (object obj2 in dt.Rows)
{
DataRow dr = (DataRow)obj2;
//2025-01-22 修改审核字段
string FDOCUMENTSTATUS = dr["FDOCUMENTSTATUS"].ToString();
if (FDOCUMENTSTATUS == "C")
{
mid = dr["ERPID"].ToString();
string sqlT = string.Format("SELECT count(*) cou FROM XSCKA WHERE ERPID='{0}'", dr["ERPID"]);
int c = Convert.ToInt32(this._sqlHelper.GetSingle(sqlT));
bool flag4 = c != 0;
string SQLF = string.Format("DELETE FROM XSCKA WHERE ERPID='{0}'", dr["ERPID"]);
int B = this._sqlHelper.ExecuteNonQuery(SQLF);
string SQLD = string.Format("DELETE FROM XSCKB WHERE PID='{0}'", dr["ERPID"]);
int D = this._sqlHelper.ExecuteNonQuery(SQLD);
string sql = string.Format(" INSERT INTO XSCKA(ID,FBillerID,FBillNo,FCheckerID ,FCheckDate,FDeptNumber,FDeptName,FCustIDNumber,FCustIDName,FEMPNumber,FEMPName,ERPID,TB_TIME,FDOCUMENTSTATUS) " +
"VALUES (SEQ_XS03.nextval,'{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}',sysdate,'{11}') ", new object[]
{
dr["FBillerID"],
dr["FBillNo"],
dr["FCheckerID"],
dr["FCheckDate"],
dr["FDeptNumber"],
dr["FDeptName"],
dr["FCustIDNumber"],
dr["FCustIDName"],
dr["FEMPNumber"],
dr["FEMPName"],
dr["ERPID"],
dr["FDOCUMENTSTATUS"]
});
SQLStringList.Add(sql);
this._sqlHelper.ExecuteSqlTran(SQLStringList);
}
else
{
string SQLF = string.Format("DELETE FROM XSCKA WHERE ERPID='{0}'", dr["ERPID"]);
int B = this._sqlHelper.ExecuteNonQuery(SQLF);
string SQLD = string.Format("DELETE FROM XSCKB WHERE PID='{0}'", dr["ERPID"]);
int D = this._sqlHelper.ExecuteNonQuery(SQLD);
}
}
}
bool flag5 = dt.TableName == "ERPXSCKB";
if (flag5)
{
foreach (object obj3 in dt.Rows)
{
DataRow dr2 = (DataRow)obj3;
string sql = string.Format(" INSERT INTO XSCKB(ID,ERPID,FEntryID,FNumber,FName,FModel,FAmount,FAuxPrice,FAuxQty,FAuxStockQty,FBatchNo,FBCommitQty,FCommitQty,FNote,FOrderBillNo,FOrderEntryID,FOrderInterID,FPrice,FQty,FStockIDNumber,FStockIDName,FStockQty,FUnitID,pid,FH_TIME) " +
"VALUES (SEQ_XS04.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}') ", new object[]
{
dr2["ERPID"],
dr2["FEntryID"],
dr2["FNumber"],
dr2["FName"],
dr2["FModel"],
dr2["FAmount"],
dr2["FAuxPrice"],
dr2["FAuxQty"],
dr2["FAuxStockQty"],
dr2["FBatchNo"],
dr2["FBCommitQty"],
dr2["FCommitQty"],
dr2["FNote"],
dr2["FOrderBillNo"],
dr2["FOrderEntryID"],
dr2["FOrderInterID"],
dr2["FPrice"],
dr2["FQty"],
dr2["FStockIDNumber"],
dr2["FStockIDName"],
dr2["FStockQty"],
dr2["FUnitID"],mid,dr2["FDELIVERYDATE"]
});
SQLStringListT.Add(sql);
}
this._sqlHelper.ExecuteSqlTran(SQLStringListT);
}
}
else
{
#region DELETE
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
// 构建删除SQL语句
string deleteSql = $"DELETE FROM XSCKA WHERE ERPID IN ('{string.Join("','", IDList)}')";
_sqlHelper.ExecuteNonQuery(deleteSql);
string deleteSql2 = $"DELETE FROM XSCKB WHERE PID IN ('{string.Join("','", IDList)}')";
_sqlHelper.ExecuteNonQuery(deleteSql2);
#endregion
}
}
result = true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
result = false;
}
}
return result;
}
#region 28.MesToErpFormal(ERP正式账套同步推送) bool MesToErp(DataRow dr, ref string ErrorMsg)
///
/// MesToErpFormal(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool MesToErpFormal(DataRow dr, ref string ErrorMsg)
{
try
{
string task_name = dr["task_name"].ToString();
string descript = dr["descript"].ToString();
string create_time = dr["create_time"].ToString();
string creator = dr["creator"].ToString();
string operatype = dr["operatype"].ToString();
string source_table = dr["source_table"].ToString();
string target_table = dr["target_table"].ToString();
string data = dr["data"].ToString();
string data_inserted = dr["data_inserted"].ToString();
string data_deleted = dr["data_deleted"].ToString();
WebReference.WebService1 ws = new WebReference.WebService1();
string res = ws.mesToErpInfoFormal(task_name, descript, create_time, creator, operatype, data, data_inserted, "NEW", "");
// string res = ws.GetJsonByMes(task_name, descript, create_time, creator, operatype, source_table, target_table, data, data_inserted, data_deleted);
if (res.Contains("成功"))
return true;
else
{
ErrorMsg = res;
return false;
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
private bool TB_ERPTOMES_UNITCHANGE(DataSet ds, string operatype, ref string ErrorMsg, DataRow ddr)
{
string sql = "";
string sqlL = "";
//用来拼ID集合
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
ArrayList SQLStringListT = new ArrayList();
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
if (operatype == "INSERT" || operatype == "UPDATE")
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
//处理前判断是否存在
// operatype = DataIsInDB("INVMBA", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT")
{
#region INSERT
foreach (DataRow dr in dt.Rows)
{
//插入前先检查是否存在相同数据，若存在则删除
string sqlT = "";
string SQLF = "";
sqlT = string.Format(@"SELECT count(*) cou FROM MES_UNIT ID='{0}' ", dr["ERPID"]);
//int a = _sqlHelper.ExecuteNonQuery(sqlT);
int c = Convert.ToInt32(_sqlHelper.GetSingle(sqlT));
if (c != 0)
{
try
{
SQLF = string.Format(@"DELETE MES_UNIT WHERE ID='{0}'", dr["ERPID"]);
int B = _sqlHelper.ExecuteNonQuery(SQLF);
}
catch (Exception)
{
return false;
}
}
IDList.Add(dr["ERPID"].ToString());
sql = string.Format(@" INSERT INTO MES_UNIT(ID,OLDUNIT,NEWUNIT,OLDQTY,NEWQTY,CREATE_DATE,CREATE_BY,LASTUPDATE_DATE,LASTUPDATE_BY,COMPANY,FACTORY,MEMO)
VALUES ('{0}','{1}','{2}','{3}','{4}','{4}','{5}','{6}','{7}','{8}',1000,1000,'{9}') ", dr["ERPID"], dr["IUC001"], dr["IUC002"], dr["IUC003"], dr["IUC004"], dr["IUC006"], dr["IUC005"], dr["IUC006"], dr["IUC005"], dr["IUC007"]);
SQLStringList.Add(sql);
}
return _sqlHelper.ExecuteSqlTran(SQLStringList);
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql = string.Format(@" update MES_UNIT
set
ID='{0}',
OLDUNIT='{1}',
NEWUNIT='{2}',
OLDQTY='{3}',
NEWQTY='{4}',
CREATE_DATE='{5}',
CREATE_BY='{6}',
LASTUPDATE_DATE='{7}',
LASTUPDATE_BY='{8}',
COMPANY=1000,
FACTORY=1000,
MEMO='{9}'
where ID='{0}' ", dr["ERPID"], dr["IUC001"], dr["IUC002"], dr["IUC003"], dr["IUC004"], dr["IUC006"], dr["IUC005"], dr["IUC006"], dr["IUC005"], dr["IUC007"]);
SQLStringListT.Add(sql);
}
return _sqlHelper.ExecuteSqlTran(SQLStringListT);
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from MES_UNIT where ERPID in ('{0}'); ", string.Join("','", IDList));
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
public class kcjson
{
public string FormId { get; set; }
public string FieldKeys { get; set; }
//public ArrayList FilterString { get; set; }
public String FilterString { get; set; }
public string OrderString { get; set; }
public int TopRowCount { get; set; }
public int StartRow { get; set; }
public int Limit { get; set; }
public string SubSystemId { get; set; }
}
public class bjjson
{
public string Left { get; set; }
public string FieldName { get; set; }
public string Compare { get; set; }
public string Value { get; set; }
public string Right { get; set; }
public string Logic { get; set; }
}
private bool TB_ERPTOMES_JSKC1(DataSet ds, string operatype, ref string errorMsg)
{
ArrayList SQLStringList = new ArrayList();
DataTable dt = ds.Tables[0];
string sql = "";
List> inventoryData;
StringBuilder Info = new StringBuilder();
String SQL = "";
foreach (DataRow dr in dt.Rows)
{
try
{
// 使用 K3CloudApiClient 初始化
K3CloudApiClient client = new K3CloudApiClient("http://192.168.0.40/k3cloud/");
var loginResult = client.ValidateLogin("64c5000ed94a48", "沈子博", "Ss123123@", 2052);
var resultType = JObject.Parse(loginResult)["LoginResultType"].Value();
// 登录成功才继续
if (resultType == 1)
{
string PD1 = (string)dr["ITEM_NO"];
string PD2 = (string)dr["CKBM"];
var bj_json1 = new bjjson()
{
Left = "(",
FieldName = "fmaterialid",
Compare = "=",
Value = PD1,
Right = ")",
Logic = "0"
};
var bj_json2 = new bjjson()
{
Left = "(",
FieldName = "fstockid",
Compare = "=",
Value = PD2,
Right = ")",
Logic = "0"
};
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') ";
string bjString2 = "";
if (PD1 != "" && PD2 == "")
{
bjString1 = bjString1 + "and fmaterialid.Fnumber='" + PD1 + "'";
}
if (PD2 != "" && PD1 == "")
{
bjString1 = bjString1 + "and fstockid='" + PD2 + "'";
}
if (PD1 != "" && PD2 != "")
{
bjString1 = bjString1 + "and fmaterialid.Fnumber='" + PD1 + "'and fstockid='" + PD2 + "'";
}
var bj1 = bjString1 + bjString2;
//string BJSTRING3=
ArrayList list = new ArrayList();
list.Add(bj1);
var kc_json = new kcjson()
{
/* FormId = "STK_Inventory",
FieldKeys = "fmaterialid, fbaseqty, fstockid, fbaseunitid, fstockunitid,fstocklocid",
FilterString = list,
OrderString = "",
TopRowCount = 0,
StartRow = 0,
Limit = 100000,
SubSystemId = ""*/
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 = 0,
Limit = 100000,
SubSystemId = ""
};
/*var kc_json2 = new kcjson()
{
FormId = "STK_Inventory",
FieldKeys = "fmaterialid, fbaseqty, fstockid, fbaseunitid, fstockunitid,fstocklocid",
FilterString = list,
OrderString = "",
TopRowCount = 0,
StartRow = 20000,
Limit = 100000,
SubSystemId = ""
FormId= "STK_Inventory",
FieldKeys= "fmaterialid, fbaseqty, fstockid, fbaseunitid, fstockunitid,fstocklocid,FStockLocId.FF100001.FNumber,FStockLocId.FF100002.FNumber",
FilterString= "FStockLocId.FF100001.FNumber='MES1' or FStockLocId.FF100002.FNumber='MES1' ",
OrderString = "",
TopRowCount= 0,
StartRow=0,
Limit =100000,
SubSystemId= ""
};
*/
string jsonString = JsonConvert.SerializeObject(kc_json);
var BJ = jsonString.Replace("\\", string.Empty);
var BJ1 = BJ.Replace("[\"", "[");
var BJ2 = BJ1.Replace("\"]", "]");
// 调用金蝶WebAPI获取即时库存数据
inventoryData = client.ExecuteBillQuery(BJ2);
if (inventoryData.Count != 0)
{
//对返回结果进行解析和校验，这里使用的是JsonPatch
var resultJObject = JArray.Parse(JsonConvert.SerializeObject(inventoryData));
for (int i = 0; i < inventoryData.Count; i++)
{
//1/22
/* string sqlT = "";
string SQLF = "";
sqlT = string.Format(@"SELECT count(*) cou FROM MES_ERP_KC where ID='{0}' ", inventoryData[i][3]);
//int a = _sqlHelper.ExecuteNonQuery(sqlT);
int c = Convert.ToInt32(_sqlHelper.GetSingle(sqlT));
if (c != 0)
{
try
{
SQLF = string.Format(@"DELETE MES_ERP_KC WHERE ID='{0}'", inventoryData[i][3]);
int B = _sqlHelper.ExecuteNonQuery(SQLF);
}
catch (Exception)
{
return false;
}
}//*/
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); // 将生成的SQL语句添加到列表中
if (SQLStringList.Count > 500)
{
_sqlHelper.ExecuteSqlTran(SQLStringList);
SQLStringList.Clear();
}
}
if (SQLStringList.Count > 0)
{
return _sqlHelper.ExecuteSqlTran(SQLStringList);
}
}
// 控制批量插入大小
}
else
{
errorMsg = "登录金蝶系统失败";
return false;
}
}
catch (Exception e)
{
errorMsg = e.Message;
return false;
}
}
return true;
}
private bool TB_ERPTOMES_SYS(DataSet ds, string operatype, ref string ErrorMsg)
{
string sql = "";
string sqlL = "";
//用来拼ID集合
List IDList = new List();
ArrayList SQLStringList = new ArrayList();
ArrayList SQLStringListT = new ArrayList();
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
if (operatype == "INSERT" || operatype == "UPDATE")
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT")
{
#region INSERT
foreach (DataRow dr in dt.Rows)
{
//插入前先检查是否存在相同数据，若存在则删除
string sqlT = "";
string SQLF = "";
sqlT = string.Format(@"SELECT count(*) cou FROM MES_UNIT ID='{0}' ", dr["ERPID"]);
//int a = _sqlHelper.ExecuteNonQuery(sqlT);
int c = Convert.ToInt32(_sqlHelper.GetSingle(sqlT));
if (c != 0)
{
try
{
SQLF = string.Format(@"DELETE MES_UNIT WHERE ID='{0}'", dr["ERPID"]);
int B = _sqlHelper.ExecuteNonQuery(SQLF);
}
catch (Exception)
{
return false;
}
}
IDList.Add(dr["ERPID"].ToString());
sql = string.Format(@" INSERT INTO MES_UNIT(ID,OLDUNIT,NEWUNIT,OLDQTY,NEWQTY,CREATE_DATE,CREATE_BY,LASTUPDATE_DATE,LASTUPDATE_BY,COMPANY,FACTORY,MEMO)
VALUES ('{0}','{1}','{2}','{3}','{4}','{4}','{5}','{6}','{7}','{8}',1000,1000,'{9}') ", dr["ERPID"], dr["IUC001"], dr["IUC002"], dr["IUC003"], dr["IUC004"], dr["IUC006"], dr["IUC005"], dr["IUC006"], dr["IUC005"], dr["IUC007"]);
SQLStringList.Add(sql);
}
return _sqlHelper.ExecuteSqlTran(SQLStringList);
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql = string.Format(@" update MES_UNIT
set
ID='{0}',
OLDUNIT='{1}',
NEWUNIT='{2}',
OLDQTY='{3}',
NEWQTY='{4}',
CREATE_DATE='{5}',
CREATE_BY='{6}',
LASTUPDATE_DATE='{7}',
LASTUPDATE_BY='{8}',
COMPANY=1000,
FACTORY=1000,
MEMO='{9}'
where ID='{0}' ", dr["ERPID"], dr["IUC001"], dr["IUC002"], dr["IUC003"], dr["IUC004"], dr["IUC006"], dr["IUC005"], dr["IUC006"], dr["IUC005"], dr["IUC007"]);
SQLStringListT.Add(sql);
}
return _sqlHelper.ExecuteSqlTran(SQLStringListT);
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from MES_UNIT where ERPID in ('{0}'); ", string.Join("','", IDList));
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
}
}