using DataexchangeServer.Helper;
using Newtonsoft.Json;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Net;
using System.Net.Http;
using System.Net.Http.Headers;
using System.Text;
namespace DataexchangeServer
{
//数据推送类
public class AE_MES_ERP
{
private SQLHelper _sqlHelper;
string conStr;
public AE_MES_ERP()
{
//conStr = Encoding.Default.GetString(Convert.FromBase64String(ConfigurationManager.ConnectionStrings["conn"].ConnectionString));
conStr = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
conStr = SecurityHelper.Decrypt(conStr);
_sqlHelper = new SQLHelper(conStr);
//_sqlHelper = new SQLHelper(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,ref string flag,string rtaskid)
{
bool result = true;
switch (taskid)
{
//1.ERPBMA部门信息表(同步推送)
case "TB_ERPTOMES_BM":
result = TB_ERPTOMES_BM(inputds, operatype, ref msg);
break;
//2.ERPGWA岗位信息表(同步推送)
case "TB_ERPTOMES_GW":
result = TB_ERPTOMES_GW(inputds, operatype, ref msg);
break;
//3.ERPRYA人员信息表(同步推送)
case "TB_ERPTOMES_RY":
result = TB_ERPTOMES_RY(inputds, operatype, ref msg);
break;
//4.ERPWFL物料分类表(同步推送)
case "TB_ERPTOMES_WF":
result = TB_ERPTOMES_WF(inputds, operatype, ref msg);
break;
//5.ERPWLA物料表(同步推送)
case "TB_ERPTOMES_WL":
result = TB_ERPTOMES_WL(inputds, operatype, ref msg);
break;
//6.ERPDWA计量单位(同步推送)
case "TB_ERPTOMES_DW":
result = TB_ERPTOMES_DW(inputds, operatype, ref msg);
break;
//7.ERPKHA客户信息(同步推送)
case "TB_ERPTOMES_KH":
result = TB_ERPTOMES_KH(inputds, operatype, ref msg);
break;
//8.ERPGYS供应商信息(同步推送)
case "TB_ERPTOMES_GYS":
result = TB_ERPTOMES_GYS(inputds, operatype, ref msg);
break;
//9.ERPCKA仓库信息档(同步推送)
case "TB_ERPTOMES_CK":
result = TB_ERPTOMES_CK(inputds, operatype, ref msg);
break;
//10.ERPGXA工序(同步推送)
case "TB_ERPTOMES_GX":
result = TB_ERPTOMES_GX(inputds, operatype, ref msg);
break;
//11.ERPBOMA(同步推送)
case "TB_ERPTOMES_BOM":
result = TB_ERPTOMES_BOM(inputds, operatype, ref msg);
break;
//12.ERPTDL替代料同步(同步推送)
case "TB_ERPTOMES_TDL":
result = TB_ERPTOMES_TDL(inputds, operatype, ref msg);
break;
//15.同步客户信息(同步推送)
case "TB_ERPTOME_KHWL":
result = TB_ERPTOME_KHWL(inputds, operatype, ref msg);
break;
//13.ERPCGA采购订单｜委外订单(同步推送)
case "TB_ERPTOMES_CG":
result = TB_ERPTOMES_CG(taskid, inputds, operatype, ref msg, rtaskid);
break;
//14.ERPTLA生产投料单(同步推送)
case "TB_ERPTOMES_TL":
result = TB_ERPTOMES_TL(inputds, operatype, ref msg, drSource);
break;
//15.ERPXCKA销售出库单(同步推送)(以后不用了)
case "TB_ERPTOMES_XCK":
result = TB_ERPTOMES_XCK(inputds, operatype, ref msg);
break;
//16.ERPQCKA其它出库单(同步推送)(以后不用了)
case "TB_ERPTOMES_QCK":
result = TB_ERPTOMES_QCK(taskid, inputds, operatype, ref msg);
break;
//17.ERPTHA供应商退货单(同步推送)(以后不用了)
case "TB_ERPTOMES_TH":
result = TB_ERPTOMES_TH(inputds, operatype, ref msg);
break;
//22.ERPTHCONFIRM供应商退货确认(同步推送)
case "TB_ERPTOMES_TH_CONFIRM":
result = TB_ERPTOMES_TH_CONFIRM(inputds, operatype, ref msg);
break;
//18.MesToErp(同步推送)
case "TB_MESTOERP_WGRK"://MESWGRK金蝶条码入库（外购入库）(同步推送)
case "TB_MESTOERP_WWRK"://MESWWRK金蝶条码入库（委外入库）(同步推送)
case "TB_MESTOERP_QTRK"://MESQTRK金蝶条码入库（其他入库）(同步推送)
case "TB_MESTOERP_CPRK"://MESCPRK金蝶条码入库（产品入库）(同步推送)
case "TB_MESTOERP_CGRK"://MESCPRK金蝶条码入库（采购入库）(同步推送)
case "TB_MESTOERP_XSCK"://MESCPRK金蝶条码出库（销售出库）(同步推送)
case "TB_MESTOERP_WLQDBB"://MESGCBOM金蝶高冲用料变更(同步推送)
case "TB_MESTOERP_DD"://MESDD金蝶条码入库（调拨）(同步推送)
case "TB_MESTOERP_PYPK"://MESPYPK金蝶条码入库（盘盈盘亏）(同步推送)
case "TB_MESTOERP_LL"://MESLL金蝶条码出库（领料单）(同步推送)
case "TB_MESTOERP_SCTL"://MESLL金蝶条码入库（领料单）(同步推送)
case "TB_MESTOERP_SCBL"://MESLL金蝶条码入库（领料单）(同步推送)
case "TB_MESTOERP_WTRK"://MESWTRK金蝶条码入库（受托入库）(同步推送)
case "TB_MESTOERP_QTCK"://其他出库
case "TB_MESTOERP_XCK"://销售出库（蓝-订单、红）
case "TB_MESTOERP_WWCK"://委外出库(蓝、红)
case "TB_MESTOERP_WGTL"://外购退料
case "TB_MESTOERP_JYTL"://检验退料
case "TB_MESTOERP_SLSH"://推送审核送货单
case "TB_MESTOERP_SCDDXD"://推送审核送货单
case "TB_MESTOERP_TransferIn"://分布式调入单
case "TB_MESTOERP_ZJDB"://直接调拨单
case "TB_MESTOERP_WWDDXD"://委外订单下达
case "TB_MESTOERP_WWCG"://委外下推采购
result = MesToErp(drSource, ref msg, rtaskid);
break;
case "TB_MESTOERP_GDRK"://ERP工单入库查询
result = MesToErp(drSource, ref msg, rtaskid);
break;
case "TB_MESTOERP_CGRKLD":
result = MesToErpLD(drSource, ref msg, rtaskid);
break;
case "TB_ERPTOMES_XSD":
result = TB_ERPTOMES_XSD(inputds, operatype, ref msg);
break;
//20.委外其它出库单(同步推送)
case "TB_ERPTOMES_WCK":
result = TB_ERPTOMES_WCK(inputds, operatype, ref msg);
break;
//21.到货单(同步推送)
case "TB_ERPTOMES_CGDH":
result = TB_ERPTOMES_CGDH(inputds, operatype, ref msg);
break;
case "TB_MESTOERP_ICMO"://超入工单生成
result = MesToErp(drSource, ref msg,rtaskid);
break;
//23.TB_ERPTOMES_GDRK工单入库数量查询(同步推送)
case "TB_ERPTOMES_GDRK":
result = TB_ERPTOMES_GDRK(inputds, operatype, ref msg);
break;
//24.TB_ERPTOMES_FHTZ发货通知单
case "TB_ERPTOMES_FHTZ"://接收发货通知单
result = TB_ERPTOMES_FHTZ(taskid, inputds, operatype, ref msg);
break;
//25.委外投料单
case "TB_ERPTOMES_WW":
result = TB_ERPTOMES_WW(taskid, inputds, operatype, ref msg);
break;
case "TB_XKY_GET_DHD"://推送审核送货单
result = XKY_GET_DHD(drSource, ref msg);
break;
case "TB_XKY_GET_DETAILS"://推送审核送货单
result = XKY_GET_DETAILS(drSource, ref msg);
break;
case "TB_XKY_GET_BARCODE"://推送审核送货单
result = XKY_GET_BARCODE(drSource, ref msg);
break;
}
if (result)
flag = "1";
else flag = "0";
return result;
}
private bool TB_ERPTOME_KHWL(DataSet ds, string operatype, ref string ErrorMsg)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
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("GS_KWL", "ID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT" || operatype == "UPDATE")
{
#region INSERT
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
//插入前先删除重复键
DeleteDataBeforeInsert("GS_KWL", "ID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("HRMMAA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "HRMMAA重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("ERPID", "ID"); //ID
colMapping[1] = new SqlBulkCopyColumnMapping("KWL001", "KWL001"); //ID
colMapping[2] = new SqlBulkCopyColumnMapping("KWL002", "KWL002");//部门编号
colMapping[3] = new SqlBulkCopyColumnMapping("KWL003", "KWL003");//部门名称
colMapping[4] = new SqlBulkCopyColumnMapping("KWL004", "KWL004");//行政级别
colMapping[5] = new SqlBulkCopyColumnMapping("KWL005", "KWL005");//责任者工号
colMapping[6] = new SqlBulkCopyColumnMapping("KWL006", "KWL006");//责任者姓名
colMapping[7] = new SqlBulkCopyColumnMapping("KWL007", "KWL007");//上级部门
colMapping[8] = new SqlBulkCopyColumnMapping("WLA008", "KWL008");
return DataTableToSQLServer(dt, conStr, "GS_KWL", colMapping, ref ErrorMsg);
#endregion
}
//else if (operatype == "UPDATE")
//{
// //ErrorMsg = "执行修改数据表";
// #region UPDATE
// foreach (DataRow dr in dt.Rows)
// {
// sql += string.Format(@" update GS_KWL
// set
// KWL001='{1}',
// KWL002='{2}',
// KWL003='{3}',
// KWL004='{4}',
// KWL005='{5}',
// KWL006='{6}',
// KWL007='{7}',
// KWL008='{8}'
// where ID='{0}' AND KWL003='{3}' ; ", dr["ERPID"], dr["KWL001"], dr["KWL002"], dr["KWL003"], dr["KWL004"], dr["KWL005"], dr["KWL006"], dr["KWL007"], dr["WLA008"]);
// }
// return _sqlHelper.ExecuteNonQuery(sql) > 0;
// #endregion
//}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
return true;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 1.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)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
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("GS_BM", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT")
{
#region INSERT
dt.Columns.Add("BM001", typeof(string));
dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = "Y";
dr["BM001"] = dr["ERPID"];
if (dr["BMA003"].ToString() == "")
{
dr["BMA003"] = "3";//默认本部
}
}
//插入前先删除重复键
DeleteDataBeforeInsert("GS_BM", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("HRMMAA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "HRMMAA重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); //ERPID
colMapping[1] = new SqlBulkCopyColumnMapping("BM001", "BM001"); //ID
colMapping[2] = new SqlBulkCopyColumnMapping("BMA001", "BM002");//部门编号
colMapping[3] = new SqlBulkCopyColumnMapping("BMA002", "BM003");//部门名称
colMapping[4] = new SqlBulkCopyColumnMapping("BMA003", "BM004");//行政级别
colMapping[5] = new SqlBulkCopyColumnMapping("BMA004", "BM005");//责任者工号
colMapping[6] = new SqlBulkCopyColumnMapping("BMA005", "BM006");//责任者姓名
colMapping[7] = new SqlBulkCopyColumnMapping("BMA006", "BM007");//上级部门
colMapping[8] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
return DataTableToSQLServer(dt, conStr, "GS_BM", colMapping, ref ErrorMsg);
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql += string.Format(@" update GS_BM
set
BM002='{1}',
BM003='{2}',
BM004='{3}',
BM005='{4}',
BM006='{5}',
BM007='{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
return true;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 2.ERPGWA岗位信息表(同步推送) bool TB_ERPTOMES_GW(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPGWA岗位信息表(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_GW(DataSet ds, string operatype, ref string ErrorMsg)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
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("HRMMBA", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT")
{
#region INSERT
dt.Columns.Add("MBA004", typeof(string));
dt.Columns.Add("MBA005", typeof(string));
dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = "Y";
dr["MBA004"] = "1";//人员类别 1-一线;2-二线
dr["MBA005"] = "0";//岗位类别 0-一般职;1-管理职;2-技术职;3-专业职;4-其它职
}
//插入前先删除重复键
DeleteDataBeforeInsert("HRMMBA", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("HRMMBA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "HRMMBA重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); //ERPID
colMapping[1] = new SqlBulkCopyColumnMapping("GWA001", "MBA001");//岗位编号
colMapping[2] = new SqlBulkCopyColumnMapping("GWA002", "MBA002");//岗位名称
colMapping[3] = new SqlBulkCopyColumnMapping("GWA003", "MBA007");//岗位要求
colMapping[4] = new SqlBulkCopyColumnMapping("GWA004", "MBA003");//建立日期
colMapping[5] = new SqlBulkCopyColumnMapping("GWA005", "MBA008");//备注
colMapping[6] = new SqlBulkCopyColumnMapping("MBA004", "MBA004");//人员类别
colMapping[7] = new SqlBulkCopyColumnMapping("MBA005", "MBA005");//岗位类别
colMapping[8] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
return DataTableToSQLServer(dt, conStr, "HRMMBA", colMapping, ref ErrorMsg);
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql += string.Format(@" update HRMMBA
set
MBA001='{1}',
MBA002='{2}',
MBA007='{3}',
MBA003='{4}',
MBA008='{5}'
where ERPID='{0}'; ", dr["ERPID"], dr["GWA001"], dr["GWA002"], dr["GWA003"], dr["GWA004"], dr["GWA005"]);
}
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 HRMMBA where ERPID in ('{0}'); ", string.Join("','", IDList));
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 3.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)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
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("GS_RY", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT")
{
#region INSERT
dt.Columns.Add("RY006", typeof(string));
dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = "Y";
dr["RY006"] = "1";//人员类别 1-一线;2-二线
}
//插入前先删除重复键
DeleteDataBeforeInsert("GS_RY", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("HRMMAC", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "HRMMAC重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); //ERPID
colMapping[1] = new SqlBulkCopyColumnMapping("RYA001", "RY002");//职工工号
colMapping[2] = new SqlBulkCopyColumnMapping("RYA002", "RY003");//职工姓名
colMapping[3] = new SqlBulkCopyColumnMapping("RYA003", "RY001");//部门编码
colMapping[4] = new SqlBulkCopyColumnMapping("RYA004", "RY019");//部门名称
colMapping[5] = new SqlBulkCopyColumnMapping("RYA005", "RY004");//岗位编码
colMapping[6] = new SqlBulkCopyColumnMapping("RYA006", "RY005");//岗位名称
colMapping[7] = new SqlBulkCopyColumnMapping("RYA007", "RY007");//手机号
colMapping[8] = new SqlBulkCopyColumnMapping("RYA008", "RY010");//调入日期
colMapping[9] = new SqlBulkCopyColumnMapping("RYA009", "RY011");//调出日期
colMapping[10] = new SqlBulkCopyColumnMapping("RYA010", "RY013");//备注
colMapping[11] = new SqlBulkCopyColumnMapping("RY006", "RY006");//备注
colMapping[12] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
return DataTableToSQLServer(dt, conStr, "GS_RY", colMapping, ref ErrorMsg);
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql += string.Format(@" update GS_RY
set
RY002='{1}',
RY003='{2}',
RY001='{3}',
RY019='{4}',
RY004='{5}',
RY005='{6}',
RY007='{7}',
RY010='{8}',
RY011='{9}',
RY013='{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
return true;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 4.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();
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
dt.Columns.Add("BAB005", typeof(string));
//dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
//dr["AUDITING"] = "Y";
dr["BAB005"] = "N";//是否系统
if (dr["WFL001"].ToString()=="10" || dr["WFL001"].ToString() == "12" || dr["WFL001"].ToString() == "11")
{
dr["WFL003"] = "A";
}
if (dr["WFL001"].ToString()== "30" )
{
dr["WFL003"] = "C";
}
if (dr["WFL001"].ToString()== "22"|| dr["WFL001"].ToString() == "21"|| dr["WFL001"].ToString() == "23"|| dr["WFL001"].ToString() == "24"|| dr["WFL001"].ToString() == "25")
{
dr["WFL003"] = "B";
}
if (dr["WFL001"].ToString() == "90" || dr["WFL001"].ToString() == "91" || dr["WFL001"].ToString() == "92" )
{
dr["WFL003"] = "D";
}
}
//插入前先删除重复键
DeleteDataBeforeInsert("BASBAB", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("BASBAB", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "BASBAB重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); //ERPID
colMapping[1] = new SqlBulkCopyColumnMapping("WFL001", "BAB001");//分类编号
colMapping[2] = new SqlBulkCopyColumnMapping("WFL002", "BAB003");//分类名称
colMapping[3] = new SqlBulkCopyColumnMapping("WFL003", "BAB002");//父节点编号
colMapping[4] = new SqlBulkCopyColumnMapping("WFL004", "BAB004");//备注
colMapping[5] = new SqlBulkCopyColumnMapping("BAB005", "BAB005");
return DataTableToSQLServer(dt, conStr, "BASBAB", colMapping, ref ErrorMsg);
#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 5.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)
{
string sql = "";
string sqlL = "";
//用来拼ID集合
List IDList = new List();
if (operatype != "INSERT" && operatype != "UPDATE")
{
sql = string.Format(@" delete from GS_WL where WL001 in ('{0}') ", operatype);
_sqlHelper.ExecuteNonQuery(sql);
return true;
}
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["WLA001"].ToString());
}
//处理前判断是否存在
operatype = DataIsInDB("GS_WL", "WL001", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT")
{
#region INSERT
dt.Columns.Add("AUDITING", typeof(string));
//dt.Columns.Add("MBA030", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["WLA001"].ToString());
string str = dr["WLA016"].ToString();
string strT = dr["WLA018"].ToString();
//dr["WLA011"] = str+"."+ strT;
dr["AUDITING"] = "Y";
//dr["MBA030"] = "A";
}
//插入前先删除重复键
DeleteDataBeforeInsert("GS_WL", "WL001", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("INVMBA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "INVMBA重复键删除失败！";
// return false;
//}
//选择想要的列插入新表
DataTable dtHead = dt.DefaultView.ToTable(true, "WLA001", "WLA002", "WLA003", "WLA004", "WLA005", "WLA006", "WLA007", "WLA008", "WLA009", "WLA010", "WLA011", "WLA012", "WLA015", "ERPID", "AUDITING", "WLA013",
"WLA014", "WLA020", "WLA021", "WLA022", "WLA023", "WLA024", "WLA025", "WLA026", "WLA027", "WLA028", "WLA029", "WLA030", "WLA031", "WLA032", "WLA033", "WLA034", "WLA035", "WLA036", "WLA037", "WLA038", "WLA039",
"WLA040", "WLA041","WLA042", "WLA043", "WLA044", "WLA045", "WLA046", "WLA047", "WLA048", "WLA049", "WLA050", "WLA051", "WLA052", "WLA053", "WLA054", "WLA055","WLA056", "WLA057", "WLA058");
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dtHead.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("WLA001", "WL001"); // 物料编码
colMapping[1] = new SqlBulkCopyColumnMapping("WLA002", "WL002"); // 物料名称
colMapping[2] = new SqlBulkCopyColumnMapping("WLA003", "WL004"); // 物料规格
colMapping[3] = new SqlBulkCopyColumnMapping("WLA004", "WL005"); // 单位
colMapping[4] = new SqlBulkCopyColumnMapping("WLA005", "WL007"); // ABC分类
colMapping[5] = new SqlBulkCopyColumnMapping("WLA006", "WL011"); // 安全库存
colMapping[6] = new SqlBulkCopyColumnMapping("WLA007", "WL012"); // 最大库存
colMapping[7] = new SqlBulkCopyColumnMapping("WLA008", "WL013"); // 最小包装数量
colMapping[8] = new SqlBulkCopyColumnMapping("WLA009", "WL020"); // 虚拟料号
colMapping[9] = new SqlBulkCopyColumnMapping("WLA010", "WL026"); // 主要仓库
colMapping[10] = new SqlBulkCopyColumnMapping("WLA011", "WL003"); // 物料分类编号
colMapping[11] = new SqlBulkCopyColumnMapping("WLA012", "WL029"); // 保质期
colMapping[12] = new SqlBulkCopyColumnMapping("WLA015", "WL024"); // 备注
colMapping[13] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); // ERPID
colMapping[14] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
//免检标志根据数据看是否用备注来存，然后通过触发器更新到INVMBD特性表中
colMapping[15] = new SqlBulkCopyColumnMapping("WLA013", "WL015"); // 免检标志
colMapping[16] = new SqlBulkCopyColumnMapping("WLA014", "WL017"); // 客户编码
colMapping[17] = new SqlBulkCopyColumnMapping("WLA025", "WL008"); // 图号
//colMapping[17] = new SqlBulkCopyColumnMapping("WLA020", "MBA039"); // 分组编号
//colMapping[18] = new SqlBulkCopyColumnMapping("WLA021", "MBA040"); // 分组名称
//colMapping[19] = new SqlBulkCopyColumnMapping("WLA022", "Exprop1"); // 辅助属性1
//colMapping[20] = new SqlBulkCopyColumnMapping("WLA023", "Exprop2"); // 辅助属性2
//colMapping[21] = new SqlBulkCopyColumnMapping("WLA024", "Exprop3"); // 辅助属性3
//colMapping[22] = new SqlBulkCopyColumnMapping("WLA025", "Exprop4"); // 辅助属性4
//colMapping[23] = new SqlBulkCopyColumnMapping("WLA026", "Exprop5"); // 辅助属性5
//colMapping[24] = new SqlBulkCopyColumnMapping("WLA027", "MBA037"); // 是否启用批号
//colMapping[25] = new SqlBulkCopyColumnMapping("MBA030", "MBA030"); // 生成分类，默认原料
//colMapping[26] = new SqlBulkCopyColumnMapping("WLA028", "MBA041"); // 上级分组编号
//colMapping[27] = new SqlBulkCopyColumnMapping("WLA029", "MBA042"); // 上级分组名称
//colMapping[28] = new SqlBulkCopyColumnMapping("WLA030", "MBA043"); // 图号
// int A = _sqlHelper.ExecuteNonQuery(sqlL);
colMapping[23] = new SqlBulkCopyColumnMapping("WLA026", "WL039");
colMapping[24] = new SqlBulkCopyColumnMapping("WLA027", "WL040");
colMapping[25] = new SqlBulkCopyColumnMapping("WLA028", "WL041");
colMapping[26] = new SqlBulkCopyColumnMapping("WLA029", "WL042");
colMapping[27] = new SqlBulkCopyColumnMapping("WLA030", "WL043");
colMapping[28] = new SqlBulkCopyColumnMapping("WLA031", "WL044");
colMapping[29] = new SqlBulkCopyColumnMapping("WLA032", "WL045");
colMapping[30] = new SqlBulkCopyColumnMapping("WLA033", "WL046");
colMapping[31] = new SqlBulkCopyColumnMapping("WLA034", "WL047");
colMapping[32] = new SqlBulkCopyColumnMapping("WLA035", "WL048");
colMapping[33] = new SqlBulkCopyColumnMapping("WLA036", "WL049");
colMapping[34] = new SqlBulkCopyColumnMapping("WLA037", "WL050");
colMapping[35] = new SqlBulkCopyColumnMapping("WLA038", "WL051");
colMapping[36] = new SqlBulkCopyColumnMapping("WLA039", "WL052");
colMapping[37] = new SqlBulkCopyColumnMapping("WLA040", "WL053");
colMapping[38] = new SqlBulkCopyColumnMapping("WLA041", "WL054");
colMapping[39] = new SqlBulkCopyColumnMapping("WLA042", "WL055");
colMapping[40] = new SqlBulkCopyColumnMapping("WLA043", "WL056");
colMapping[41] = new SqlBulkCopyColumnMapping("WLA044", "WL057");
colMapping[42] = new SqlBulkCopyColumnMapping("WLA045", "WL058");
colMapping[43] = new SqlBulkCopyColumnMapping("WLA046", "WL059");
colMapping[44] = new SqlBulkCopyColumnMapping("WLA047", "WL060");
colMapping[45] = new SqlBulkCopyColumnMapping("WLA048", "WL061");
colMapping[46] = new SqlBulkCopyColumnMapping("WLA049", "WL062");
colMapping[47] = new SqlBulkCopyColumnMapping("WLA050", "WL063");
colMapping[48] = new SqlBulkCopyColumnMapping("WLA051", "WL064");
colMapping[49] = new SqlBulkCopyColumnMapping("WLA052", "WL065");
colMapping[50] = new SqlBulkCopyColumnMapping("WLA053", "WL066");
colMapping[51] = new SqlBulkCopyColumnMapping("WLA054", "WL067");
colMapping[52] = new SqlBulkCopyColumnMapping("WLA055", "WL068");
colMapping[53] = new SqlBulkCopyColumnMapping("WLA056", "WL069");
colMapping[54] = new SqlBulkCopyColumnMapping("WLA057", "WL070");
colMapping[55] = new SqlBulkCopyColumnMapping("WLA058", "WL071");
return DataTableToSQLServer(dtHead, conStr, "GS_WL", colMapping, ref ErrorMsg);
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
string str = dr["WLA016"].ToString();
string strT = dr["WLA018"].ToString();
//dr["WLA011"] = str + "." + strT;
sql += string.Format(@" update GS_WL
set
WL001='{1}',
WL002='{2}',
WL004='{3}',
WL005='{4}',
WL007='{5}',
WL011='{6}',
WL012='{7}',
WL013='{8}',
WL020='{9}',
WL026='{10}',
WL003='{11}',
WL024='{12}',
WL015='{13}',
WL017='{14}',
WL008='{15}',ERPID='{16}',
WL039='{17}',WL040='{18}',WL041='{19}',WL042='{20}',WL043='{21}',WL044='{22}',WL045='{23}',WL046='{24}',WL047='{25}',WL048='{26}',
WL049='{27}',WL050='{28}',WL051='{29}',WL052='{30}',WL053='{31}',WL054='{32}',WL055='{33}',WL056='{34}',WL057='{35}',WL058='{36}',
WL059='{37}',WL060='{38}',WL061='{39}',WL062='{40}',WL063='{41}',WL064='{42}',WL065='{43}',WL066='{44}',WL067='{45}',WL068='{46}',WL069='{47}',WL070='{48}',WL071='{49}'
where WL001='{0}'; ", dr["WLA001"]
, dr["WLA001"], dr["WLA002"], dr["WLA003"], dr["WLA004"], dr["WLA005"], dr["WLA006"], dr["WLA007"]
, dr["WLA008"], dr["WLA009"], dr["WLA010"], dr["WLA011"], dr["WLA015"], dr["WLA013"], dr["WLA014"], dr["WLA025"], dr["ERPID"]
, dr["WLA026"], dr["WLA027"], dr["WLA028"], dr["WLA029"], dr["WLA030"], dr["WLA031"], dr["WLA032"], dr["WLA033"], dr["WLA034"]
, dr["WLA035"], dr["WLA036"], dr["WLA037"], dr["WLA038"], dr["WLA039"], dr["WLA040"], dr["WLA041"], dr["WLA042"], dr["WLA043"]
, dr["WLA044"], dr["WLA045"], dr["WLA046"], dr["WLA047"], dr["WLA048"], dr["WLA049"], dr["WLA050"], dr["WLA051"], dr["WLA052"]
, dr["WLA053"], dr["WLA054"], dr["WLA055"], dr["WLA056"], dr["WLA057"], dr["WLA058"]
);
}
//int A = _sqlHelper.ExecuteNonQuery(sqlL);
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
return true;
//foreach (DataRow dr in dt.Rows)
//{
// IDList.Add(dr["ERPID"].ToString());
//}
//sql = string.Format(@" delete from INVMBA where ERPID in ('{0}'); ", string.Join("','", IDList));
//return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 6.ERPDWA计量单位(同步推送) bool TB_ERPTOMES_DW(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPDWA计量单位(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_DW(DataSet ds, string operatype, ref string ErrorMsg)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
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("BASMUA", "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";
}
//插入前先删除重复键
DeleteDataBeforeInsert("BASMUA", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("BASMUA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "BASMUA重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); //ERPID
colMapping[1] = new SqlBulkCopyColumnMapping("DWA001", "MUA001");//单位编码
colMapping[2] = new SqlBulkCopyColumnMapping("DWA002", "MUA002");//单位名称
return DataTableToSQLServer(dt, conStr, "BASMUA", colMapping, ref ErrorMsg);
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql += string.Format(@" update BASMUA
set
MUA001='{1}',
MUA002='{2}'
where ERPID='{0}'; ", dr["ERPID"]
, dr["DWA001"], dr["DWA002"]);
}
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 BASMUA where ERPID in ('{0}'); ", string.Join("','", IDList));
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 7.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)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
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"];
}
//插入前先删除重复键
DeleteDataBeforeInsert("COMMAA", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("COMMAA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "COMMAA重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("KHA001", "MAA001"); // 客户编号
colMapping[1] = new SqlBulkCopyColumnMapping("KHA002", "MAA002"); // 客户简称
colMapping[2] = new SqlBulkCopyColumnMapping("KHA003", "MAA003"); // 客户名称
colMapping[3] = new SqlBulkCopyColumnMapping("KHA004", "MAA004"); // 客户英文
colMapping[4] = new SqlBulkCopyColumnMapping("KHA005", "MAA009"); // 联系人
colMapping[5] = new SqlBulkCopyColumnMapping("KHA006", "MAA010"); // 电话
colMapping[6] = new SqlBulkCopyColumnMapping("KHA007", "MAA006"); // 备注
colMapping[7] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); // ERPID
//colMapping[8] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
return DataTableToSQLServer(dt, conStr, "COMMAA", colMapping, ref ErrorMsg);
#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 8.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)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
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("GS_GYS", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT" || operatype == "UPDATE")
{
#region INSERT
//dt.Columns.Add("MAA005", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
//插入前先删除重复键
DeleteDataBeforeInsert("GS_GYS", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("PURMAA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "PURMAA重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("GYSA001", "GYS001"); // 供应商编号
colMapping[1] = new SqlBulkCopyColumnMapping("GYSA002", "GYS002"); // 供应商简称
colMapping[2] = new SqlBulkCopyColumnMapping("GYSA003", "GYS003"); // 审核状态
colMapping[3] = new SqlBulkCopyColumnMapping("GYSA004", "GYS004"); // 禁用状态
colMapping[4] = new SqlBulkCopyColumnMapping("GYSA005", "GYS005"); // 组织号
colMapping[5] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); // ERPID
colMapping[6] = new SqlBulkCopyColumnMapping("FUseOrgId", "FUseOrgId"); // 内/外销
//colMapping[8] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
return DataTableToSQLServer(dt, conStr, "GS_GYS", colMapping, ref ErrorMsg);
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql += string.Format(@" update GS_GYS
set
GYS001='{1}',
GYS002='{2}',
GYS003='{3}',
GYS004='{4}',
GYS005='{5}',
FUseOrgld='{6}'
where ERPID='{0}'; ", dr["ERPID"]
, dr["GYSA001"], dr["GYSA002"], dr["GYSA003"], dr["GYSA004"], dr["GYSA005"], dr["FUseOrgld"]);
}
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 GS_GYS where ERPID in ('{0}'); ", string.Join("','", IDList));
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 9.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)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
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("GS_CKA", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT")
{
#region INSERT
dt.Columns.Add("MDA003", typeof(string));//ERP仓库编号
dt.Columns.Add("MDA004", typeof(string));//部材类型 A=原材料,B=半成品,C=成品
dt.Columns.Add("MDA006", typeof(string));//存货性质 1=存货仓 0=非存货仓
dt.Columns.Add("MDA007", typeof(string));//纳入可用量计算 Y=纳入，N=不纳入
dt.Columns.Add("MDA008", typeof(string));//库存量不足准许出库 Y=可以，N=不可以
dt.Columns.Add("MDA009", typeof(string));//是否参与盘点 Y=参与，N=不参与
dt.Columns.Add("MDA011", typeof(string));//仓库状态 Y=生效，N=失效
dt.Columns.Add("MDA012", typeof(string));//仓库状态 Y=生效，N=失效
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["MDA003"] = dr["CKA001"];
dr["MDA004"] = "A";
dr["MDA006"] = "1";
dr["MDA007"] = "Y";
dr["MDA008"] = "N";
dr["MDA009"] = "Y";
dr["MDA011"] = "Y";
dr["CKA003"] = "1";
}
//插入前先删除重复键
DeleteDataBeforeInsert("GS_CKA", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("BASMDA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "BASMDA重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("CKA001", "CKA001"); // 仓库编号
colMapping[1] = new SqlBulkCopyColumnMapping("CKA002", "CKA002"); // 仓库名称
colMapping[2] = new SqlBulkCopyColumnMapping("CKA003", "CKA005"); // 质量状态
colMapping[3] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); // ERPID
colMapping[4] = new SqlBulkCopyColumnMapping("MDA003", "CKA003"); // ERP仓库编号
colMapping[5] = new SqlBulkCopyColumnMapping("MDA004", "CKA004"); // 部材类型 A=原材料,B=半成品,C=成品
colMapping[6] = new SqlBulkCopyColumnMapping("MDA006", "CKA006"); // 仓库性质 1=存货仓 0=非存货仓
colMapping[7] = new SqlBulkCopyColumnMapping("MDA007", "CKA007"); // 纳入可用量计算 Y=纳入，N=不纳入
colMapping[8] = new SqlBulkCopyColumnMapping("MDA008", "CKA008"); // 库存量不足准许出库 Y=可以，N=不可以
colMapping[9] = new SqlBulkCopyColumnMapping("MDA009", "CKA009"); // 是否参与盘点 Y=参与，N=不参与
colMapping[10] = new SqlBulkCopyColumnMapping("MDA011", "CKA011"); // 仓库状态 Y=生效，N=失效
colMapping[11] = new SqlBulkCopyColumnMapping("MDA012", "CKA012");//仓库性质 1良品2不良品3待检品4报废品5途中品
return DataTableToSQLServer(dt, conStr, "GS_CKA", colMapping, ref ErrorMsg);
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql += string.Format(@" update GS_CKA
set
CKA001='{1}',
CKA002='{2}',
CKA005='{3}'
where ERPID='{0}'; ", dr["ERPID"]
, dr["CKA001"], dr["CKA002"], "1");
}
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
return true;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 10.ERPGXA工序(同步推送) bool TB_ERPTOMES_GX(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPGXA工序(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_GX(DataSet ds, string operatype, ref string ErrorMsg)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
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("MCSMBA", "ERPID", string.Join("','", IDList), "") ? "UPDATE" : "INSERT";
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT")
{
#region INSERT
dt.Columns.Add("MBA008", typeof(string));//是否返工点
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["GXA005"] = "Y";
dr["MBA008"] = "N";
}
//插入前先删除重复键
DeleteDataBeforeInsert("MCSMBA", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("MCSMBA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "MCSMBA重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); // ERPID
colMapping[1] = new SqlBulkCopyColumnMapping("GXA001", "MBA001"); // 工序代码
colMapping[2] = new SqlBulkCopyColumnMapping("GXA002", "MBA002"); // 工序名称
colMapping[3] = new SqlBulkCopyColumnMapping("GXA003", "MBA003"); // 工序描述
colMapping[4] = new SqlBulkCopyColumnMapping("GXA004", "MBA004"); // 工序要求
colMapping[5] = new SqlBulkCopyColumnMapping("GXA005", "MBA006"); // 使用状态
colMapping[6] = new SqlBulkCopyColumnMapping("GXA006", "MBA007"); // 备注信息
colMapping[7] = new SqlBulkCopyColumnMapping("MBA008", "MBA008"); // 是否返工点
//colMapping[8] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
return DataTableToSQLServer(dt, conStr, "MCSMBA", colMapping, ref ErrorMsg);
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql += string.Format(@" update MCSMBA
set
MBA001='{1}',
MBA002='{2}',
MBA003='{3}',
MBA004='{4}',
MBA006='{5}',
MBA007='{6}'
where ERPID='{0}'; ", dr["ERPID"]
, dr["GXA001"], dr["GXA002"], dr["GXA003"], dr["GXA004"], dr["GXA005"], dr["GXA006"]);
}
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 MCSMBA where ERPID in ('{0}'); ", string.Join("','", IDList));
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 11.ERPBOMA(同步推送) bool TB_ERPTOMES_BOM(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPBOMA(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_BOM(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 INSERT
if (dt.TableName == "ERPBOMA")
{
#region BOM主表的处理
dt.Columns.Add("AUDITING", typeof(string));
//dt.Columns.Add("ID", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = "Y";
if (dr["BOMA007"].ToString() == "")
{
dr["BOMA007"] = "0";
}
if (dr["BOMA009"].ToString() == "")
{
dr["BOMA009"] = "0";
}
if (dr["BOMA010"].ToString() == "")
{
dr["BOMA010"] = "0";
}
if (dr["BOMA011"].ToString() == "")
{
dr["BOMA011"] = "0";
}
if (dr["BOMA013"].ToString() == "")
{
dr["BOMA013"] = "0";
}
//dr["ID"] = dr["ERPID"];
}
//插入前先删除重复键
DeleteDataBeforeInsert("BOMMAA", "ID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("BOMMAA", "ID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "BOMMAA重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("ERPID", "ID"); // ID
colMapping[1] = new SqlBulkCopyColumnMapping("BOMA001", "MAA001"); // 主件编码
colMapping[2] = new SqlBulkCopyColumnMapping("BOMA002", "MAA002"); // 单位
colMapping[3] = new SqlBulkCopyColumnMapping("BOMA003", "MAA003"); // BOM版本
colMapping[4] = new SqlBulkCopyColumnMapping("BOMA004", "MAA004"); // 变更单号
colMapping[5] = new SqlBulkCopyColumnMapping("BOMA005", "MAA005"); // ECN变更版次
colMapping[6] = new SqlBulkCopyColumnMapping("BOMA006", "MAA006"); // 程序编号
colMapping[7] = new SqlBulkCopyColumnMapping("BOMA007", "MAA007"); // 总点数
colMapping[8] = new SqlBulkCopyColumnMapping("BOMA008", "MAA008"); // 基板名称
colMapping[9] = new SqlBulkCopyColumnMapping("BOMA009", "MAA009"); // 基板厚度
colMapping[10] = new SqlBulkCopyColumnMapping("BOMA010", "MAA010"); // 基版宽度
colMapping[11] = new SqlBulkCopyColumnMapping("BOMA011", "MAA011"); // 基板枚数
colMapping[12] = new SqlBulkCopyColumnMapping("BOMA012", "MAA012"); // 时间戳
colMapping[13] = new SqlBulkCopyColumnMapping("BOMA013", "MAA013"); // 标准用量
colMapping[14] = new SqlBulkCopyColumnMapping("BOMA014", "MAA014"); // 备注信息
colMapping[15] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
DataTableToSQLServer(dt, conStr, "BOMMAA", colMapping, ref ErrorMsg);
#endregion
}
if (dt.TableName == "ERPBOMB")
{
#region BOM明细的处理
dt.Columns.Add("AUDITING", typeof(string));
//dt.Columns.Add("ID", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = "Y";
if (dr["BOMB007"].ToString() == "")
{
dr["BOMB007"] = "0";
}
if (dr["BOMB008"].ToString() == "")
{
dr["BOMB008"] = "1";
}
if (dr["BOMB009"].ToString() == "")
{
dr["BOMB009"] = "0";
}
if (dr["BOMB011"].ToString() == "")
{
dr["BOMB011"] = "0";
}
if (dr["BOMB017"].ToString() == "")
{
dr["BOMB017"] = "0";
}
if (dr["BOMB006"].ToString() == "")
{
dr["BOMB006"] = "PCS";
}
//dr["ID"] = dr["ERPID"];
}
//插入前先删除重复键
DeleteDataBeforeInsert("BOMMAB", "ID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("BOMMAB", "ID+MAB002", string.Join("','", IDList), ""))
//{
// ErrorMsg = "BOMMAB重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("ERPID", "ID"); // ID
colMapping[1] = new SqlBulkCopyColumnMapping("BOMB001", "MAB001"); // 主件编码
colMapping[2] = new SqlBulkCopyColumnMapping("BOMB002", "MAB002"); // 序号
colMapping[3] = new SqlBulkCopyColumnMapping("BOMB003", "MAB003"); // 物料编码
colMapping[4] = new SqlBulkCopyColumnMapping("BOMB004", "MAB024"); // 物料名称
colMapping[5] = new SqlBulkCopyColumnMapping("BOMB005", "MAB029"); // 物料规格
colMapping[6] = new SqlBulkCopyColumnMapping("BOMB006", "MAB004"); // 单位
colMapping[7] = new SqlBulkCopyColumnMapping("BOMB007", "MAB006"); // 组成用量
colMapping[8] = new SqlBulkCopyColumnMapping("BOMB008", "MAB007"); // 底数
colMapping[9] = new SqlBulkCopyColumnMapping("BOMB009", "MAB008"); // 损耗率%
colMapping[10] = new SqlBulkCopyColumnMapping("BOMB010", "MAB009"); // 工艺
colMapping[11] = new SqlBulkCopyColumnMapping("BOMB011", "MAB010"); // 水口料比例
colMapping[12] = new SqlBulkCopyColumnMapping("BOMB012", "MAB011"); // 生效日期
colMapping[13] = new SqlBulkCopyColumnMapping("BOMB013", "MAB013"); // 失效日期
colMapping[14] = new SqlBulkCopyColumnMapping("BOMB014", "MAB012"); // 板号
colMapping[15] = new SqlBulkCopyColumnMapping("BOMB015", "MAB015"); // 点位
colMapping[16] = new SqlBulkCopyColumnMapping("BOMB016", "MAB016"); // 材料类型
colMapping[17] = new SqlBulkCopyColumnMapping("BOMB017", "MAB017"); // 水口
colMapping[18] = new SqlBulkCopyColumnMapping("BOMB018", "MAB018"); // 工位
colMapping[19] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
DataTableToSQLServer(dt, conStr, "BOMMAB", colMapping, ref ErrorMsg);
#endregion
}
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
if (dt.TableName == "ERPBOMA")
{
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from BOMMAA where ID in ('{0}'); delete from BOMMAB where ID in ('{0}'); ", string.Join("','", IDList));
}
_sqlHelper.ExecuteNonQuery(sql);
#endregion
}
}
return true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
return false;
}
}
#endregion
#region 12.ERPTDL替代料同步(同步推送) bool TB_ERPTOMES_TDL(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPTDL替代料同步(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_TDL(DataSet ds, string operatype, ref string ErrorMsg)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
if (ds.Tables.Count == 0 || ds.Tables[0].Rows.Count == 0)
{
ErrorMsg = "生成数据表失败";
return false;
}
try
{
DataTable dt = ds.Tables[0];
if (operatype == "INSERT")
{
#region INSERT
dt.Columns.Add("ID", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["ID"] = dr["ERPID"];
}
//插入前先删除重复键
DeleteDataBeforeInsert("BOMMBB", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("BOMMBB", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "BOMMBB重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); // ID
colMapping[1] = new SqlBulkCopyColumnMapping("TDL001", "MBB001"); // 主件品号
colMapping[2] = new SqlBulkCopyColumnMapping("TDL002", "MBB002"); // BOM版本号
colMapping[3] = new SqlBulkCopyColumnMapping("TDL003", "MBB003"); // 材料品号
colMapping[4] = new SqlBulkCopyColumnMapping("TDL004", "MBB004"); // 通用材料品号
colMapping[5] = new SqlBulkCopyColumnMapping("TDL005", "MBB006"); // 通用顺序
colMapping[6] = new SqlBulkCopyColumnMapping("TDL006", "MBB007"); // 生效日期
colMapping[7] = new SqlBulkCopyColumnMapping("TDL007", "MBB008"); // 失效日期
colMapping[8] = new SqlBulkCopyColumnMapping("TDL008", "MBB013"); // 配套变更组
colMapping[9] = new SqlBulkCopyColumnMapping("TDL009", "MBB012"); // 备注
colMapping[10] = new SqlBulkCopyColumnMapping("ID", "ID"); // 备注
return DataTableToSQLServer(dt, conStr, "BOMMBB", colMapping, ref ErrorMsg);
#endregion
}
else if (operatype == "UPDATE")
{
//ErrorMsg = "执行修改数据表";
#region UPDATE
foreach (DataRow dr in dt.Rows)
{
sql += string.Format(@" update BOMMBB
set
MBB001='{1}',
MBB002='{2}',
MBB003='{3}',
MBB004='{4}',
MBB006='{5}',
MBB007='{6}',
MBB008='{7}',
MBB013='{8}',
MBB012='{9}'
where ERPID='{0}'; ", dr["ERPID"]
, dr["TDL001"], dr["TDL002"], dr["TDL003"], dr["TDL004"]
, dr["TDL005"], dr["TDL006"], dr["TDL007"], dr["TDL008"], dr["TDL009"]);
}
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 BOMMBB where ERPID in ('{0}'); ", string.Join("','", IDList));
return _sqlHelper.ExecuteNonQuery(sql) > 0;
#endregion
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
#region 13.ERPCGA采购订单｜委外订单(同步推送) bool TB_ERPTOMES_CG(string task_name,DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPCGA采购订单｜委外订单(同步推送)
///
/// 任务名称
/// 数据集
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_CG(string task_name,DataSet ds, string operatype, ref string ErrorMsg,string rtaskid)
{
string sql = "";
string org = "宁波";
//用来拼ID集合
List IDList = new List();
string dtName = "";
if (operatype != "INSERT" && operatype != "UPDATE")
{
sql = string.Format(@" delete from GS_CG where CGA001 in ('{0}') ", operatype);
string sqlTT = string.Format(@" delete from CGDB where CGB001 in ('{0}') ", operatype);
_sqlHelper.ExecuteNonQuery(sql);
_sqlHelper.ExecuteNonQuery(sqlTT);
return true;
}
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 == "ERPCGA")
{
#region 采购订单主表的处理
dt.Columns.Add("AUDITING", typeof(string));
dt.Columns.Add("NEWERPID", typeof(string));
foreach (DataRow dr in dt.Rows)
{
if (dr.Table.Columns.Contains("CGAORG"))
{
org = "丽德";
string UP = string.Format(@"EXEC [WMS_CGLD_TOJX] '{0}' ", rtaskid);
_sqlHelper.ExecuteNonQuery(UP);
dr["NEWERPID"] = "LD"+dr["ERPID"].ToString();
IDList.Add("LD"+dr["ERPID"].ToString());
}
else
{
IDList.Add(dr["ERPID"].ToString());
dr["NEWERPID"] = dr["ERPID"].ToString();
}
dr["AUDITING"] = "Y";
if (dr["CGA002"].ToString() == "")
{
dr["CGA002"] = task_name == "TB_ERPTOMES_CG" ? "3301" : "3302";
}
}
//插入前先删除重复键
DeleteDataBeforeInsert("GS_CG", "ID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("PURDDA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "PURDDA重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "flagApp");
colMapping[1] = new SqlBulkCopyColumnMapping("NEWERPID", "ID"); //ID
colMapping[2] = new SqlBulkCopyColumnMapping("CGA001", "CGA001"); //采购单号
colMapping[3] = new SqlBulkCopyColumnMapping("CGA002", "CGA002"); //采购单别
colMapping[4] = new SqlBulkCopyColumnMapping("CGA003", "CGA003"); //采购日期
colMapping[5] = new SqlBulkCopyColumnMapping("CGA004", "CGA004"); //单据日期
colMapping[6] = new SqlBulkCopyColumnMapping("CGA005", "CGA005"); //单据类型 1、采购2、委外
colMapping[7] = new SqlBulkCopyColumnMapping("CGA006", "CGA006"); //供应商
colMapping[8] = new SqlBulkCopyColumnMapping("CGA007", "CGA007"); //供应商简称
colMapping[9] = new SqlBulkCopyColumnMapping("CGA008", "CGA008"); //关闭状态
colMapping[10] = new SqlBulkCopyColumnMapping("CGA009", "CGA009"); //客户编号
colMapping[11] = new SqlBulkCopyColumnMapping("CGA0010", "CGA010"); //发文单号
colMapping[12] = new SqlBulkCopyColumnMapping("CGA0011", "CGA011"); //交易币种
colMapping[13] = new SqlBulkCopyColumnMapping("CGA0012", "CGA012"); //营业担当
colMapping[14] = new SqlBulkCopyColumnMapping("CGA0013", "CGA013"); //营业电话
colMapping[15] = new SqlBulkCopyColumnMapping("CGA0014", "CGA014"); //采购担当
colMapping[16] = new SqlBulkCopyColumnMapping("CGA0015", "CGA015"); //审核者
colMapping[17] = new SqlBulkCopyColumnMapping("CGA0016", "CGA016"); //审核日期
colMapping[18] = new SqlBulkCopyColumnMapping("CGA0017", "CGA017"); //备注
if (dt.Columns.Contains("CGAORG"))
{
colMapping[19] = new SqlBulkCopyColumnMapping("CGAORG", "CGAORG"); //备注
}
else
{
}
if (dt.Columns.Contains("CGA018"))
{
colMapping[20] = new SqlBulkCopyColumnMapping("CGA018", "CGA018"); //备注
}
if (dt.Columns.Contains("CGA0018"))
{
colMapping[20] = new SqlBulkCopyColumnMapping("CGA0018", "CGA018"); //备注
}
DataTableToSQLServer(dt, conStr, "GS_CG", colMapping, ref ErrorMsg);
#endregion
}
if (dt.TableName == "ERPCGB")
{
#region 采购订单明细的处理
dt.Columns.Add("AUDITING", typeof(string));
dt.Columns.Add("NEWERPID", typeof(string));
dt.Columns.Add("NEWERPIDB", typeof(string));
foreach (DataRow dr in dt.Rows)
{
if (org=="丽德")
{
IDList.Add("LD"+dr["ERPID"].ToString());
dr["NEWERPID"] = "LD" + dr["ERPID"].ToString();
dr["NEWERPIDB"] = "LD" + dr["ERP_EntryID"].ToString();
}
else
{
IDList.Add(dr["ERPID"].ToString());
dr["NEWERPID"] = dr["ERPID"].ToString();
dr["NEWERPIDB"] = dr["ERP_EntryID"].ToString();
}
dr["AUDITING"] = "Y";
if (dr["CGB008"].ToString() == "")
{
dr["CGB008"] = "0";
}
else
{
Decimal A = Convert.ToDecimal(dr["CGB008"].ToString());
string B = A.ToString();
dr["CGB008"] = B;
}
if (dr["CGB0010"].ToString() == "")
{
dr["CGB0010"] = "0";
}
else
{
Decimal A = Convert.ToDecimal(dr["CGB0010"].ToString());
string B = A.ToString();
dr["CGB0010"] = B;
}
if (dr["CGB0011"].ToString() == "")
{
dr["CGB0011"] = "0";
}
else
{
Decimal A = Convert.ToDecimal(dr["CGB0011"].ToString());
string B = A.ToString();
dr["CGB0011"] = B;
}
if (dr["CGB0014"].ToString() == "")
{
dr["CGB0014"] = "0";
}
else
{
Decimal A = Convert.ToDecimal(dr["CGB0014"].ToString());
string B = A.ToString();
dr["CGB0014"] = B;
}
if (dr["CGB0015"].ToString() == "")
{
dr["CGB0015"] = "0";
}
}
//插入前先删除重复键
DeleteDataBeforeInsert("CGDB", "PID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("PURDDB", "ERPID+DDB002", string.Join("','", IDList), ""))
//{
// ErrorMsg = "PURDDB重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "flagApp");
colMapping[1] = new SqlBulkCopyColumnMapping("NEWERPID", "PID"); //ERPID
colMapping[2] = new SqlBulkCopyColumnMapping("CGB001", "CGB001"); //采购单号
colMapping[3] = new SqlBulkCopyColumnMapping("CGB002", "CGB002"); //序号
colMapping[4] = new SqlBulkCopyColumnMapping("CGB003", "CGB003"); //物料编码
colMapping[5] = new SqlBulkCopyColumnMapping("CGB004", "CGB004"); //物料名称
colMapping[6] = new SqlBulkCopyColumnMapping("CGB005", "CGB005"); //物料规格
colMapping[7] = new SqlBulkCopyColumnMapping("CGB006", "CGB006"); //仓库编号
colMapping[8] = new SqlBulkCopyColumnMapping("CGB007", "CGB007"); //仓库名称
colMapping[9] = new SqlBulkCopyColumnMapping("CGB008", "CGB008"); //采购数量
colMapping[10] = new SqlBulkCopyColumnMapping("CGB009", "CGB009"); //单位
colMapping[11] = new SqlBulkCopyColumnMapping("CGB0010", "CGB010"); //采购单价
colMapping[12] = new SqlBulkCopyColumnMapping("CGB0011", "CGB011"); //采购金额
colMapping[13] = new SqlBulkCopyColumnMapping("CGB0012", "CGB012"); //预交货日
colMapping[14] = new SqlBulkCopyColumnMapping("CGB0013", "CGB013"); //客户料号
colMapping[15] = new SqlBulkCopyColumnMapping("CGB0014", "CGB014"); //已交数量
colMapping[16] = new SqlBulkCopyColumnMapping("CGB0015", "CGB015"); //已委外出库数量
colMapping[17] = new SqlBulkCopyColumnMapping("CGB0016", "CGB016"); //结束
colMapping[18] = new SqlBulkCopyColumnMapping("CGB0018", "CGB018"); //备注
colMapping[19] = new SqlBulkCopyColumnMapping("NEWERPIDB", "ID"); //备注
colMapping[20] = new SqlBulkCopyColumnMapping("CGB0017", "CGB017"); //备注
colMapping[21] = new SqlBulkCopyColumnMapping("CGB0019", "CGB019"); //交货上限
colMapping[22] = new SqlBulkCopyColumnMapping("CGB0020", "CGB020"); //超收比例
colMapping[23] = new SqlBulkCopyColumnMapping("CGB0021", "CGB021"); //含税单价
colMapping[24] = new SqlBulkCopyColumnMapping("CGB0022", "CGB022"); //价税合计
DataTableToSQLServer(dt, conStr, "CGDB", colMapping, ref ErrorMsg);
foreach (DataRow dF in dt.Rows)
{
string UP = string.Format(@"EXEC API_UPDATE_CGDB {0},{1} ", dF["CGB001"].ToString(), dF["CGB002"].ToString());
_sqlHelper.ExecuteNonQuery(UP);
}
#endregion
}
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
//if (dt.TableName == "ERPCGA")
//{
// foreach (DataRow dr in dt.Rows)
// {
// IDList.Add(dr["ERPID"].ToString());
// }
// sql = string.Format(@" delete from PURDDA where ERPID in ('{0}'); delete from PURDDB where ERPID in ('{0}'); ", string.Join("','", IDList));
//}
//_sqlHelper.ExecuteNonQuery(sql);
#endregion
}
}
return true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
return false;
}
}
#endregion
#region 14.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,DataRow drT)
{
string sql = "";
string taskid = drT["task_id"].ToString();
string data = drT["data"].ToString();
//用来拼ID集合
List IDList = new List();
string dtName = "";
if (operatype != "INSERT" && operatype != "UPDATE")
{
sql = string.Format(@" delete from WOMCAA where CAA002 in ('{0}') ", operatype);
_sqlHelper.ExecuteNonQuery(sql);
return true;
}
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 == "ERPTLA")
{
#region 投料单主表的处理
// dt.Columns.Add("AUDITING", typeof(string));
dt.Columns.Add("CAA022", typeof(string));
dt.Columns.Add("CAA020", typeof(string));
foreach (DataRow dr in dt.Rows)
{
String sqlDE = string.Format(@"delete from WOMCAB where PID in ('{0}')", dr["TLA001"].ToString());
_sqlHelper.ExecuteNonQuery(sqlDE);
IDList.Add(dr["ERP_EntryID"].ToString());
dr["TLA012"] = Convert.ToDouble(dr["TLA012"]).ToString();
// dr["AUDITING"] = "Y";
dr["CAA022"] = dr["TLA012"];
dr["TLA002"] = dr["TLA019"];//工单批次直接取任务单号吧
dr["CAA020"] = DateTime.Now.ToString("yyyy-MM-dd");
if (dr["TLA004"].ToString() == "" || dr["TLA004"].ToString() == "1")
{
dr["TLA004"] = "5102";//默认DIP工单
}
else
{
dr["TLA004"] = "5109";//返工工单
}
if (dr["TLA015"].ToString().Length<5)
{
string womcaa = string.Format(@"SELECT * FROM WORKNO(NOLOCK) WHERE 单据编号='{0}'", dr["TLA002"].ToString());
DataTable WOM= _sqlHelper.ExecuteDataTable(womcaa);
if (WOM.Rows.Count<1)
{
dr["TLA015"] ="";
dr["TLA021"] = "";
}
else
{
dr["TLA015"] = WOM.Rows[0]["备注"].ToString();
dr["TLA021"] = WOM.Rows[0]["客户名称"].ToString();
string womca = string.Format(@"SELECT * FROM KUDD(NOLOCK) WHERE fbillno='{0}'", WOM.Rows[0]["备注"].ToString());
DataTable WOMT = _sqlHelper.ExecuteDataTable(womca);
if (WOMT.Rows.Count < 1)
{
dr["TLA014"] = "";
}
else
{
dr["TLA014"] = WOMT.Rows[0]["forderbillno"].ToString();
}
}
}
}
//插入前先删除重复键
DeleteDataBeforeInsert("WOMCAA", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("WOMCAA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "WOMCAA重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
//colMapping[1] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
colMapping[1] = new SqlBulkCopyColumnMapping("ERP_EntryID", "ERPID"); //ERPID
colMapping[2] = new SqlBulkCopyColumnMapping("TLA001", "CAA001"); //单号
colMapping[3] = new SqlBulkCopyColumnMapping("TLA002", "CAA002"); //ERP工单批次
colMapping[4] = new SqlBulkCopyColumnMapping("TLA003", "CAA003"); //客户编号
colMapping[5] = new SqlBulkCopyColumnMapping("TLA004", "CAA004"); //单据类型
colMapping[6] = new SqlBulkCopyColumnMapping("TLA005", "CAA005"); //开单日期
colMapping[7] = new SqlBulkCopyColumnMapping("TLA006", "CAA006"); //产品编码
colMapping[8] = new SqlBulkCopyColumnMapping("TLA007", "CAA007"); //产品名称
colMapping[9] = new SqlBulkCopyColumnMapping("TLA008", "CAA008"); //产品规格
colMapping[10] = new SqlBulkCopyColumnMapping("TLA009", "CAA009"); //单位
colMapping[11] = new SqlBulkCopyColumnMapping("TLA010", "CAA010"); //预计开工时间
colMapping[12] = new SqlBulkCopyColumnMapping("TLA011", "CAA011"); //预计完工时间
colMapping[13] = new SqlBulkCopyColumnMapping("TLA012", "CAA012"); //工单数量
colMapping[14] = new SqlBulkCopyColumnMapping("TLA013", "CAA015"); //BOM版次
colMapping[15] = new SqlBulkCopyColumnMapping("TLA021", "CAA017"); //客户名称
colMapping[16] = new SqlBulkCopyColumnMapping("TLA015", "CAA018"); //销售订单号
colMapping[17] = new SqlBulkCopyColumnMapping("TLA023", "CAA021"); //备注
colMapping[18] = new SqlBulkCopyColumnMapping("CAA022", "CAA022"); //计划数量
colMapping[19] = new SqlBulkCopyColumnMapping("TLA017", "CAA023"); //ERP源单ID
colMapping[20] = new SqlBulkCopyColumnMapping("TLA018", "CAA025"); //ERP源单行号
colMapping[21] = new SqlBulkCopyColumnMapping("TLA019", "CAA024"); //ERP源单单号
colMapping[22] = new SqlBulkCopyColumnMapping("TLA020", "CAA014"); //工作中心
colMapping[23] = new SqlBulkCopyColumnMapping("TLA001", "ID"); //工作中心
colMapping[24] = new SqlBulkCopyColumnMapping("TLA022", "CAA019"); //已入库数量
colMapping[25] = new SqlBulkCopyColumnMapping("TLA014", "CAA016"); //客户订单号
colMapping[26] = new SqlBulkCopyColumnMapping("ERPID", "PID"); //客户订单号
colMapping[0] = new SqlBulkCopyColumnMapping("CAA020", "CAA020"); //审核时间
colMapping[27] = new SqlBulkCopyColumnMapping("TLA024", "CAA029"); //工单状态
colMapping[28] = new SqlBulkCopyColumnMapping("TLA025", "CAA030"); //废料关联
DataTableToSQLServer(dt, conStr, "WOMCAA", colMapping, ref ErrorMsg);
//更新指令工单
foreach (DataRow dF in dt.Rows)
{
if (dF["TLA020"].ToString().Contains("装配"))
{
if (dF["TLA023"].ToString().Contains("-"))
{
string[] tr = dF["TLA023"].ToString().Split('-');
//int i = dF["TLA023"].ToString().LastIndexOf('-');
string StrNew = tr[1].ToString();//dF["TLA023"].ToString().Substring(i + 1, 10);
string SQLT = string.Format(@"SELECT TOP 1 * FROM WOMCAA(NOLOCK) WHERE CAA018='{0}' ", StrNew);
DataTable CAA = _sqlHelper.ExecuteDataTable(SQLT);
if (CAA.Rows.Count > 0)
{
string UP = string.Format(@"UPDATE WOMCAA SET CAA016='{0}',CAA017='{1}',CAA018='{2}' WHERE CAA002='{3}' ", CAA.Rows[0]["CAA016"].ToString(), CAA.Rows[0]["CAA017"].ToString(), CAA.Rows[0]["CAA018"].ToString(), dF["TLA002"].ToString());
_sqlHelper.ExecuteNonQuery(UP);
}
}
}
string womDaa = string.Format(@"UPDATE WOMDAA SET DAA059 = CAA018, DAA016 = CAA008, DAA039 = CAA017,DAA058=CAA016 FROM WOMCAA WHERE CAA002 = DAA043 and CAA001='{0}' AND CAA001 <> '1'", dF["TLA001"].ToString());
_sqlHelper.ExecuteNonQuery(womDaa);
string womDa = string.Format(@" ;WITH A AS(
SELECT SUM(DAA027) F ,DAA014,DAA059 ,DAA043 FROM WOMDAA(NOLOCK)
WHERE DAA038='{0}' AND DAA014='{1}'
GROUP BY DAA014,DAA059 ,DAA043
)
UPDATE WOMCAA SET CAA013=A.F,CAA027=1 FROM A WHERE A.DAA059=CAA018 AND A.DAA043=CAA002 AND CAA006=DAA014 AND CAA001='{0}'", dF["TLA001"].ToString(), dF["TLA006"].ToString());
_sqlHelper.ExecuteNonQuery(womDa);
}
#endregion
}
if (dt.TableName == "ERPTLB")
{
#region 投料单明细的处理
//dt.Columns.Add("AUDITING", typeof(string));
dt.Columns.Add("CAB016", typeof(string));
foreach (DataRow dr in dt.Rows)
{
if (dr["TLB003"].ToString().Contains("05.0001."))
{
dr["TLB006"]=Math.Ceiling(Convert.ToDecimal(dr["TLB006"].ToString())).ToString();
}
else
{
dr["TLB006"] = Convert.ToDecimal(dr["TLB006"]).ToString();
}
IDList.Add(dr["ERP_EntryID"].ToString());
dr["TLB012"] = Convert.ToDecimal(dr["TLB012"]).ToString();
// dr["AUDITING"] = "Y";
dr["CAB016"] = "0";
if (dr["TLB006"].ToString() == "")
{
dr["TLB006"] = "0";
}
if (dr["TLB007"].ToString() == "")
{
dr["TLB007"] = "0";
}
if (dr["TLB012"].ToString() == "")
{
dr["TLB012"] = "0";
}
if (dr["TLB013"].ToString() == "")
{
dr["TLB013"] = "1";
}
}
//插入前先删除重复键
DeleteDataBeforeInsert("WOMCAB", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("WOMCAB", "ERPID+CAB002", string.Join("','", IDList), ""))
//{
// ErrorMsg = "WOMCAB重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
// colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
colMapping[1] = new SqlBulkCopyColumnMapping("ERP_EntryID", "ERPID"); //ERPID
colMapping[2] = new SqlBulkCopyColumnMapping("TLB001", "CAB001"); //单号
colMapping[3] = new SqlBulkCopyColumnMapping("TLB002", "CAB002"); //序号
colMapping[4] = new SqlBulkCopyColumnMapping("TLB003", "CAB003"); //材料编码
colMapping[5] = new SqlBulkCopyColumnMapping("TLB004", "CAB004"); //材料名称
colMapping[6] = new SqlBulkCopyColumnMapping("TLB005", "CAB005"); //材料规格
colMapping[7] = new SqlBulkCopyColumnMapping("TLB006", "CAB006"); //需领用量
colMapping[8] = new SqlBulkCopyColumnMapping("TLB007", "CAB007"); //已领用量
colMapping[9] = new SqlBulkCopyColumnMapping("TLB008", "CAB008"); //工艺
colMapping[10] = new SqlBulkCopyColumnMapping("TLB009", "CAB009"); //单位
colMapping[11] = new SqlBulkCopyColumnMapping("TLB010", "CAB010"); //材料类型
colMapping[12] = new SqlBulkCopyColumnMapping("TLB011", "CAB011"); //主料
colMapping[13] = new SqlBulkCopyColumnMapping("TLB012", "CAB012"); //组成用量
colMapping[14] = new SqlBulkCopyColumnMapping("TLB013", "CAB013"); //底数
colMapping[15] = new SqlBulkCopyColumnMapping("TLB014", "CAB014"); //是否替料
colMapping[16] = new SqlBulkCopyColumnMapping("TLB015", "CAB015"); //点位
colMapping[17] = new SqlBulkCopyColumnMapping("CAB016", "CAB016"); //损耗
colMapping[18] = new SqlBulkCopyColumnMapping("TLB001", "PID"); //损耗
colMapping[0] = new SqlBulkCopyColumnMapping("TLB017", "CAB023"); //仓库
colMapping[19] = new SqlBulkCopyColumnMapping("TLB019", "CAB025"); //MES指令单
if (dt.Columns.Contains("TLB020"))
colMapping[20] = new SqlBulkCopyColumnMapping("TLB020", "CAB028"); //MES指令单
if (dt.Columns.Contains("TLB021"))
colMapping[21] = new SqlBulkCopyColumnMapping("TLB021", "CAB029"); //MES指令单
if (dt.Columns.Contains("TLB022"))
colMapping[22] = new SqlBulkCopyColumnMapping("TLB022", "CAB030"); //MES指令单
//colMapping[19] = new SqlBulkCopyColumnMapping("TLB017", "CAB024"); //单身备注
//colMapping[0] = new SqlBulkCopyColumnMapping("ERPMXID", "ERPMXID"); //ERPMXID
DataTableToSQLServer(dt, conStr, "WOMCAB", colMapping, ref ErrorMsg);
#endregion
}
string SYSDEC = string.Format(@"UPDATE SYSDEC SET STATES='IGNORE' WHERE task_name='TB_ERPTOMES_TL' AND DATA='{0}' AND STATES='NEW' AND TASK_ID !='{1}'", data, taskid);
_sqlHelper.ExecuteNonQuery(SYSDEC);
#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 ERPID in ('{0}'); ", string.Join("','", IDList));
}
_sqlHelper.ExecuteNonQuery(sql);
#endregion
}
}
return true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
return false;
}
}
#endregion
#region 15.ERPXCKA销售出库单(同步推送) bool TB_ERPTOMES_XCK(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPXCKA销售出库单(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_XCK(DataSet ds, string operatype, ref string ErrorMsg)
{
string sql = "";
string djType = "出库";
//用来拼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 INSERT
if (dt.TableName == "ERPXCKA")
{
#region 销售出库单主表的处理
dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = "Y";
//dr["XCKA002"] = dr["XCKA002"] == "退货" ? "2401" : "2307";
if (dr["XCKA002"].ToString() == "退货")
{
dr["XCKA002"] = "2401";
djType = "退货";
}
else
{
dr["XCKA002"] = "2307";
djType = "出库";
}
if (dr["XCKA020"].ToString() == "")
{
dr["XCKA020"] = "0";
}
}
if (djType == "退货")
{
#region 退货
//插入前先删除重复键
DeleteDataBeforeInsert("COMDEA", "ERPID", string.Join("','", IDList), "");
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
colMapping[1] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); //ERPID
colMapping[2] = new SqlBulkCopyColumnMapping("XCKA001", "DEA001"); //单号
colMapping[3] = new SqlBulkCopyColumnMapping("XCKA002", "DEA002"); //单据类型
colMapping[4] = new SqlBulkCopyColumnMapping("XCKA003", "DEA003"); //交易日期
colMapping[5] = new SqlBulkCopyColumnMapping("XCKA004", "DEA004"); //送货时间
colMapping[6] = new SqlBulkCopyColumnMapping("XCKA005", "DEA005"); //单据日期
colMapping[7] = new SqlBulkCopyColumnMapping("XCKA006", "DEA006"); //退货工厂
colMapping[8] = new SqlBulkCopyColumnMapping("XCKA007", "DEA007"); //工厂简称
colMapping[9] = new SqlBulkCopyColumnMapping("XCKA008", "DEA008"); //客户编号
colMapping[10] = new SqlBulkCopyColumnMapping("XCKA009", "DEA009"); //客户简称
colMapping[11] = new SqlBulkCopyColumnMapping("XCKA010", "DEA010"); //退货类型
colMapping[12] = new SqlBulkCopyColumnMapping("XCKA011", "DEA012"); //发行单号
colMapping[13] = new SqlBulkCopyColumnMapping("XCKA012", "DEA016"); //送货公司
colMapping[14] = new SqlBulkCopyColumnMapping("XCKA013", "DEA018"); //运输方式
colMapping[15] = new SqlBulkCopyColumnMapping("XCKA014", "DEA017"); //送货地点
colMapping[16] = new SqlBulkCopyColumnMapping("XCKA015", "DEA013"); //结算币种
colMapping[17] = new SqlBulkCopyColumnMapping("XCKA016", "DEA011"); //收货公司
colMapping[18] = new SqlBulkCopyColumnMapping("XCKA017", "DEA015"); //送货地址
colMapping[19] = new SqlBulkCopyColumnMapping("XCKA018", "DEA019"); //审核人员
colMapping[20] = new SqlBulkCopyColumnMapping("XCKA019", "DEA020"); //审核时间
colMapping[21] = new SqlBulkCopyColumnMapping("XCKA020", "DEA021"); //送货数量
colMapping[22] = new SqlBulkCopyColumnMapping("XCKA021", "DEA014"); //备注
DataTableToSQLServer(dt, conStr, "COMDEA", colMapping, ref ErrorMsg);
#endregion
}
else
{
#region 出库
//插入前先删除重复键
DeleteDataBeforeInsert("COMDFA", "ERPID", string.Join("','", IDList), "");
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
colMapping[1] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); //ERPID
colMapping[2] = new SqlBulkCopyColumnMapping("XCKA001", "DFA001"); //送货单号
colMapping[3] = new SqlBulkCopyColumnMapping("XCKA002", "DFA002"); //单据类型
colMapping[4] = new SqlBulkCopyColumnMapping("XCKA003", "DFA003"); //送货日期
colMapping[5] = new SqlBulkCopyColumnMapping("XCKA004", "DFA004"); //送货时间
colMapping[6] = new SqlBulkCopyColumnMapping("XCKA005", "DFA005"); //单据日期
colMapping[7] = new SqlBulkCopyColumnMapping("XCKA006", "DFA006"); //送货工厂
colMapping[8] = new SqlBulkCopyColumnMapping("XCKA007", "DFA007"); //工厂简称
colMapping[9] = new SqlBulkCopyColumnMapping("XCKA008", "DFA008"); //客户编号
colMapping[10] = new SqlBulkCopyColumnMapping("XCKA009", "DFA009"); //客户简称
colMapping[11] = new SqlBulkCopyColumnMapping("XCKA010", "DFA010"); //送货类型
colMapping[12] = new SqlBulkCopyColumnMapping("XCKA011", "DFA011"); //发行单号
colMapping[13] = new SqlBulkCopyColumnMapping("XCKA012", "DFA012"); //起运地
colMapping[14] = new SqlBulkCopyColumnMapping("XCKA013", "DFA013"); //运输方式
colMapping[15] = new SqlBulkCopyColumnMapping("XCKA014", "DFA014"); //目的地
colMapping[16] = new SqlBulkCopyColumnMapping("XCKA015", "DFA015"); //结算币种
colMapping[17] = new SqlBulkCopyColumnMapping("XCKA016", "DFA016"); //收货公司
colMapping[18] = new SqlBulkCopyColumnMapping("XCKA017", "DFA017"); //送货地址
colMapping[19] = new SqlBulkCopyColumnMapping("XCKA018", "DFA018"); //审核人员
colMapping[20] = new SqlBulkCopyColumnMapping("XCKA019", "DFA019"); //审核时间
colMapping[21] = new SqlBulkCopyColumnMapping("XCKA020", "DFA020"); //送货数量
colMapping[22] = new SqlBulkCopyColumnMapping("XCKA021", "DFA021"); //备注
DataTableToSQLServer(dt, conStr, "COMDFA", colMapping, ref ErrorMsg);
#endregion
}
#endregion
}
if (dt.TableName == "ERPXCKB")
{
#region 销售出库单明细的处理
dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = "N";
if (dr["XCKB006"].ToString() == "")
{
dr["XCKB006"] = "0";
}
else
{
if (djType == "退货")
{
dr["XCKB006"] = Convert.ToDouble(dr["XCKB006"].ToString()) * -1;
}
}
if (dr["XCKB007"].ToString() == "")
{
dr["XCKB007"] = "0";
}
if (dr["XCKB008"].ToString() == "")
{
dr["XCKB008"] = "0";
}
else
{
if (djType == "退货")
{
dr["XCKB008"] = Convert.ToDouble(dr["XCKB008"].ToString()) * -1;
}
}
if (dr["XCKB009"].ToString() == "")
{
dr["XCKB009"] = "0";
}
if (dr["XCKB010"].ToString() == "")
{
dr["XCKB010"] = "0";
}
if (dr["XCKB016"].ToString() == "")
{
dr["XCKB016"] = "0";
}
if (dr["XCKB017"].ToString() == "")
{
dr["XCKB017"] = "0";
}
if (dr["XCKB018"].ToString() == "")
{
dr["XCKB018"] = "0";
}
}
if (djType == "退货")
{
#region 退货
//插入前先删除重复键
DeleteDataBeforeInsert("COMDEB", "ERPID", string.Join("','", IDList), "");
DataTable dtt = new DataTable();
dtt.Columns.Add("AUDITING", typeof(String));
dtt.Columns.Add("ERPID", typeof(String));
dtt.Columns.Add("XCKB001", typeof(String));
dtt.Columns.Add("XCKB002", typeof(String));
dtt.Columns.Add("XCKB003", typeof(String));
dtt.Columns.Add("XCKB004", typeof(String));
dtt.Columns.Add("XCKB005", typeof(String));
dtt.Columns.Add("XCKB006", typeof(double));
dtt.Columns.Add("XCKB007", typeof(double));
dtt.Columns.Add("XCKB008", typeof(double));
dtt.Columns.Add("XCKB011", typeof(String));
dtt.Columns.Add("XCKB012", typeof(String));
dtt.Columns.Add("XCKB013", typeof(String));
dtt.Columns.Add("XCKB014", typeof(String));
dtt.Columns.Add("XCKB015", typeof(String));
dtt.Columns.Add("XCKB020", typeof(String));
dtt.Columns.Add("XCKB024", typeof(String));
foreach (DataRow dr in dt.Rows)
{
DataRow newRow = dtt.NewRow();
newRow["AUDITING"] = dr["AUDITING"];
newRow["ERPID"] = dr["ERPID"];
newRow["XCKB001"] = dr["XCKB001"];
newRow["XCKB002"] = dr["XCKB002"];
newRow["XCKB003"] = dr["XCKB003"];
newRow["XCKB004"] = dr["XCKB004"];
newRow["XCKB005"] = dr["XCKB005"];
newRow["XCKB006"] = dr["XCKB006"];
newRow["XCKB007"] = dr["XCKB007"];
newRow["XCKB008"] = dr["XCKB008"];
newRow["XCKB011"] = dr["XCKB011"];
newRow["XCKB012"] = dr["XCKB012"];
newRow["XCKB013"] = dr["XCKB013"];
newRow["XCKB014"] = dr["XCKB014"];
newRow["XCKB015"] = dr["XCKB015"];
newRow["XCKB020"] = dr["XCKB020"];
newRow["XCKB024"] = dr["XCKB024"];
dtt.Rows.Add(newRow);
}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dtt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
colMapping[1] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); //ERPID
colMapping[2] = new SqlBulkCopyColumnMapping("XCKB001", "DEB001"); // 单号
colMapping[3] = new SqlBulkCopyColumnMapping("XCKB002", "DEB002"); // 序号
colMapping[4] = new SqlBulkCopyColumnMapping("XCKB003", "DEB003"); // 产品编号
colMapping[5] = new SqlBulkCopyColumnMapping("XCKB004", "DEB004"); // 产品名称
colMapping[6] = new SqlBulkCopyColumnMapping("XCKB005", "DEB005"); // 产品规格
colMapping[7] = new SqlBulkCopyColumnMapping("XCKB006", "DEB006"); // 数量
colMapping[8] = new SqlBulkCopyColumnMapping("XCKB007", "DEB011"); // 单价
colMapping[9] = new SqlBulkCopyColumnMapping("XCKB008", "DEB012"); // 金额
//colMapping[10] = new SqlBulkCopyColumnMapping("XCKB009", "DFB009"); // 已完成数量
//colMapping[11] = new SqlBulkCopyColumnMapping("XCKB010", "DFB010"); // 总箱数
colMapping[10] = new SqlBulkCopyColumnMapping("XCKB011", "DEB018"); // 订单单号
colMapping[11] = new SqlBulkCopyColumnMapping("XCKB012", "DEB015"); // 客户单号
colMapping[12] = new SqlBulkCopyColumnMapping("XCKB013", "DEB027"); // 订单序号
colMapping[13] = new SqlBulkCopyColumnMapping("XCKB014", "DEB007"); // 仓库
colMapping[14] = new SqlBulkCopyColumnMapping("XCKB015", "DEB008"); // 仓库名称
//colMapping[17] = new SqlBulkCopyColumnMapping("XCKB016", "DFB016"); // 净重
//colMapping[18] = new SqlBulkCopyColumnMapping("XCKB017", "DFB017"); // 毛重
//colMapping[19] = new SqlBulkCopyColumnMapping("XCKB018", "DFB018"); // 体积
//colMapping[20] = new SqlBulkCopyColumnMapping("XCKB019", "DFB019"); // 包装明细
colMapping[15] = new SqlBulkCopyColumnMapping("XCKB020", "DEB029"); // BOM版本号
//colMapping[22] = new SqlBulkCopyColumnMapping("XCKB021", "DFB021"); // 客户料号
//colMapping[23] = new SqlBulkCopyColumnMapping("XCKB022", "DFB022"); // LOT批号
//colMapping[24] = new SqlBulkCopyColumnMapping("XCKB023", "DFB023"); // 卡板号
colMapping[16] = new SqlBulkCopyColumnMapping("XCKB024", "DEB030"); // 备注信息
DataTableToSQLServer(dtt, conStr, "COMDEB", colMapping, ref ErrorMsg);
#endregion
}
else
{
#region 出库
//插入前先删除重复键
DeleteDataBeforeInsert("COMDFB", "ERPID", string.Join("','", IDList), "");
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
colMapping[1] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); //ERPID
colMapping[2] = new SqlBulkCopyColumnMapping("XCKB001", "DFB001"); // 单号
colMapping[3] = new SqlBulkCopyColumnMapping("XCKB002", "DFB002"); // 序号
colMapping[4] = new SqlBulkCopyColumnMapping("XCKB003", "DFB003"); // 产品编号
colMapping[5] = new SqlBulkCopyColumnMapping("XCKB004", "DFB004"); // 产品名称
colMapping[6] = new SqlBulkCopyColumnMapping("XCKB005", "DFB005"); // 产品规格
colMapping[7] = new SqlBulkCopyColumnMapping("XCKB006", "DFB006"); // 数量
colMapping[8] = new SqlBulkCopyColumnMapping("XCKB007", "DFB007"); // 单价
colMapping[9] = new SqlBulkCopyColumnMapping("XCKB008", "DFB008"); // 金额
colMapping[10] = new SqlBulkCopyColumnMapping("XCKB009", "DFB009"); // 已完成数量
colMapping[11] = new SqlBulkCopyColumnMapping("XCKB010", "DFB010"); // 总箱数
colMapping[12] = new SqlBulkCopyColumnMapping("XCKB011", "DFB011"); // 订单单号
colMapping[13] = new SqlBulkCopyColumnMapping("XCKB012", "DFB012"); // 客户单号
colMapping[14] = new SqlBulkCopyColumnMapping("XCKB013", "DFB013"); // 订单序号
colMapping[15] = new SqlBulkCopyColumnMapping("XCKB014", "DFB014"); // 仓库
colMapping[16] = new SqlBulkCopyColumnMapping("XCKB015", "DFB015"); // 仓库名称
colMapping[17] = new SqlBulkCopyColumnMapping("XCKB016", "DFB016"); // 净重
colMapping[18] = new SqlBulkCopyColumnMapping("XCKB017", "DFB017"); // 毛重
colMapping[19] = new SqlBulkCopyColumnMapping("XCKB018", "DFB018"); // 体积
colMapping[20] = new SqlBulkCopyColumnMapping("XCKB019", "DFB019"); // 包装明细
colMapping[21] = new SqlBulkCopyColumnMapping("XCKB020", "DFB020"); // BOM版本号
colMapping[22] = new SqlBulkCopyColumnMapping("XCKB021", "DFB021"); // 客户料号
colMapping[23] = new SqlBulkCopyColumnMapping("XCKB022", "DFB022"); // LOT批号
colMapping[24] = new SqlBulkCopyColumnMapping("XCKB023", "DFB023"); // 卡板号
colMapping[25] = new SqlBulkCopyColumnMapping("XCKB024", "DFB024"); // 备注信息
DataTableToSQLServer(dt, conStr, "COMDFB", colMapping, ref ErrorMsg);
#endregion
}
#endregion
}
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
if (dt.TableName == "ERPXCKA")
{
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from COMDFA where ERPID in ('{0}'); delete from COMDFB where ERPID in ('{0}'); ", string.Join("','", IDList));
}
_sqlHelper.ExecuteNonQuery(sql);
#endregion
}
}
return true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
return false;
}
}
#endregion
#region 16.ERPQCKA其它出库单(同步推送) bool TB_ERPTOMES_QCK(string task_name, DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPQCKA其它出库单(同步推送)
///
/// 任务名称
/// 数据集
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_QCK(string task_name, 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 INSERT
if (dt.TableName == "ERPQCKA")
{
#region 其它出库单主表的处理
dt.Columns.Add("AUDITING", typeof(string));
dt.Columns.Add("DAF001", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = "Y";
dr["DAF001"] = task_name == "TB_ERPTOMES_QCK" ? "5403" : "5408";//委外出库单
if (dr["QCKA008"].ToString() == "")
{
dr["QCKA008"] = "0";
}
}
//插入前先删除重复键
DeleteDataBeforeInsert("WOMDAF", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("WOMDAF", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "WOMDAF重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
colMapping[1] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); //ERPID
colMapping[2] = new SqlBulkCopyColumnMapping("DAF001", "DAF001"); //单别
colMapping[3] = new SqlBulkCopyColumnMapping("QCKA001", "DAF002"); //单号
colMapping[4] = new SqlBulkCopyColumnMapping("QCKA002", "DAF003"); //客户编码
colMapping[5] = new SqlBulkCopyColumnMapping("QCKA003", "DAF005"); //单据日期
colMapping[6] = new SqlBulkCopyColumnMapping("QCKA004", "DAF006"); //单据时间
colMapping[7] = new SqlBulkCopyColumnMapping("QCKA005", "DAF007"); //创建人员
colMapping[8] = new SqlBulkCopyColumnMapping("QCKA006", "DAF012"); //委外厂商
colMapping[9] = new SqlBulkCopyColumnMapping("QCKA007", "DAF013"); //厂商名称
colMapping[10] = new SqlBulkCopyColumnMapping("QCKA008", "DAF017"); //生产套数
colMapping[11] = new SqlBulkCopyColumnMapping("QCKA009", "DAF018"); //班次
colMapping[12] = new SqlBulkCopyColumnMapping("QCKA010", "DAF020"); //生产线别
colMapping[13] = new SqlBulkCopyColumnMapping("QCKA011", "DAF021"); //备注
DataTableToSQLServer(dt, conStr, "WOMDAF", colMapping, ref ErrorMsg);
#endregion
}
if (dt.TableName == "ERPQCKB")
{
#region 其它出库单明细的处理
dt.Columns.Add("AUDITING", typeof(string));
dt.Columns.Add("DAG001", typeof(string));
dt.Columns.Add("DAG014", typeof(string));
dt.Columns.Add("DAG015", typeof(string));
dt.Columns.Add("DAG021", typeof(string));
dt.Columns.Add("DAG023", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = "N";
dr["DAG001"] = task_name == "TB_ERPTOMES_QCK" ? "5403" : "5408";//委外出库单
dr["DAG014"] = dr["QCKB008"];//配料数量
dr["DAG015"] = "0";//已发数量
dr["DAG021"] = 0 - Convert.ToDecimal(dr["QCKB008"].ToString());//差异数量
dr["DAG023"] = dr["QCKB008"];//待发数量
if (dr["QCKB007"].ToString() == "")
{
dr["QCKB007"] = "0";
}
if (dr["QCKB008"].ToString() == "")
{
dr["QCKB008"] = "0";
}
}
//处理K3的产品信息在明细的问题
sql = "";
DataTable dtCPMsg = dt.DefaultView.ToTable(true, "ERPID", "QCKB013", "QCKB014", "QCKB015", "QCKB016");
foreach (DataRow dr in dtCPMsg.Rows)
{
sql += string.Format(@"
update WOMDAF
set DAF023='{1}',--产品编码
DAF024='{2}',--产品名称
DAF025='{3}',--产品型号
DAF019='{4}' --BOM版本
where ERPID='{0}';
", dr["ERPID"], dr["QCKB013"], dr["QCKB014"], dr["QCKB015"], dr["QCKB016"]);
}
_sqlHelper.ExecuteNonQuery(sql);
//插入前先删除重复键
DeleteDataBeforeInsert("WOMDAG", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("WOMDAG", "ERPID+DAG003", string.Join("','", IDList), ""))
//{
// ErrorMsg = "WOMDAG重复键删除失败！";
// return false;
//}
DataTable dt1 = dt.DefaultView.ToTable(false,"AUDITING","ERPID","DAG001","QCKB001","QCKB002","QCKB003","QCKB004","QCKB005",
"QCKB006", "QCKB007", "QCKB008", "QCKB009", "QCKB010", "QCKB011", "QCKB012", "QCKB017",
"DAG014", "DAG015", "DAG021", "DAG023");
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt1.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
colMapping[1] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); //ERPID
colMapping[2] = new SqlBulkCopyColumnMapping("DAG001", "DAG001"); // 单别
colMapping[3] = new SqlBulkCopyColumnMapping("QCKB001", "DAG002"); // 单号
colMapping[4] = new SqlBulkCopyColumnMapping("QCKB002", "DAG003"); // 序号
colMapping[5] = new SqlBulkCopyColumnMapping("QCKB003", "DAG004"); // 物料编码
colMapping[6] = new SqlBulkCopyColumnMapping("QCKB004", "DAG005"); // 物料名称
colMapping[7] = new SqlBulkCopyColumnMapping("QCKB005", "DAG006"); // 物料规格
colMapping[8] = new SqlBulkCopyColumnMapping("QCKB006", "DAG007"); // 单位
colMapping[9] = new SqlBulkCopyColumnMapping("QCKB007", "DAG010"); // BOM用量
colMapping[10] = new SqlBulkCopyColumnMapping("QCKB008", "DAG011"); // 出库数量
colMapping[11] = new SqlBulkCopyColumnMapping("DAG014", "DAG014"); // 配料数量
colMapping[12] = new SqlBulkCopyColumnMapping("DAG015", "DAG015"); // 已发数量
colMapping[13] = new SqlBulkCopyColumnMapping("DAG021", "DAG021"); // 差异数量
colMapping[14] = new SqlBulkCopyColumnMapping("DAG023", "DAG023"); // 待发数量
colMapping[15] = new SqlBulkCopyColumnMapping("QCKB009", "DAG028"); // 主料
colMapping[16] = new SqlBulkCopyColumnMapping("QCKB010", "DAG029"); // 是否替代
colMapping[17] = new SqlBulkCopyColumnMapping("QCKB011", "DAG016"); // 配料人员
colMapping[18] = new SqlBulkCopyColumnMapping("QCKB012", "DAG033"); // 备注
colMapping[19] = new SqlBulkCopyColumnMapping("QCKB017", "DAG040"); // 发料仓库
DataTableToSQLServer(dt1, conStr, "WOMDAG", colMapping, ref ErrorMsg);
#endregion
}
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
if (dt.TableName == "ERPQCKA")
{
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from WOMDAF where ERPID in ('{0}'); delete from WOMDAG where ERPID in ('{0}'); ", string.Join("','", IDList));
}
_sqlHelper.ExecuteNonQuery(sql);
#endregion
}
}
return true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
return false;
}
}
#endregion
#region 17.ERPTHA供应商退货单(同步推送) bool TB_ERPTOMES_TH(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPTHA供应商退货单(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_TH(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 INSERT
if (dt.TableName == "ERPTHA")
{
#region 退货单主表的处理
dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = "N";
dr["THA002"] = "3601";
if (dr["THA011"].ToString() == "")
{
dr["THA011"] = "0";
}
string tha003 =dr["THA003"].ToString().Substring(0,10);
dr["THA003"] = tha003;
}
//插入前先删除重复键
DeleteDataBeforeInsert("PURDKA", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("PURDKA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "PURDKA重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
colMapping[1] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); //ERPID
colMapping[2] = new SqlBulkCopyColumnMapping("THA001", "DKA001"); //退货单号
colMapping[3] = new SqlBulkCopyColumnMapping("THA002", "DKA002"); //退货类型
colMapping[4] = new SqlBulkCopyColumnMapping("THA003", "DKA003"); //退货日期
colMapping[5] = new SqlBulkCopyColumnMapping("THA004", "DKA004"); //退货时间
colMapping[6] = new SqlBulkCopyColumnMapping("THA005", "DKA005"); //供应商编码
colMapping[7] = new SqlBulkCopyColumnMapping("THA006", "DKA006"); //供应商名称
colMapping[8] = new SqlBulkCopyColumnMapping("THA007", "DKA007"); //退货单号
colMapping[9] = new SqlBulkCopyColumnMapping("THA008", "DKA009"); //客户编号
colMapping[10] = new SqlBulkCopyColumnMapping("THA009", "DKA010"); //工厂编号
colMapping[11] = new SqlBulkCopyColumnMapping("THA010", "DKA011"); //目的地
colMapping[12] = new SqlBulkCopyColumnMapping("THA011", "DKA016"); //数量
colMapping[13] = new SqlBulkCopyColumnMapping("THA012", "DKA008"); //创建人员
colMapping[14] = new SqlBulkCopyColumnMapping("THA013", "DKA017"); //备注
DataTableToSQLServer(dt, conStr, "PURDKA", colMapping, ref ErrorMsg);
#endregion
}
if (dt.TableName == "ERPTHB")
{
#region 退货单明细的处理
dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = "N";
if (dr["THB006"].ToString() == "")
{
dr["THB006"] = "0";
}
//if (dr["THB007"].ToString() == "")
//{
dr["THB007"] = "0";
//}
//if (dr["THB008"].ToString() == "")
//{
dr["THB008"] = dr["THB006"];
//}
if (dr["THB010"].ToString() == "")
{
dr["THB010"] = "0";
}
if (dr["THB011"].ToString() == "")
{
dr["THB011"] = "0";
}
}
//插入前先删除重复键
DeleteDataBeforeInsert("PURDKB", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("PURDKB", "ERPID+DKB002", string.Join("','", IDList), ""))
//{
// ErrorMsg = "PURDKB重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
colMapping[1] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); //ERPID
colMapping[2] = new SqlBulkCopyColumnMapping("THB001", "DKB001"); //单号
colMapping[3] = new SqlBulkCopyColumnMapping("THB002", "DKB002"); //序号
colMapping[4] = new SqlBulkCopyColumnMapping("THB003", "DKB003"); //物料编码
colMapping[5] = new SqlBulkCopyColumnMapping("THB004", "DKB004"); //物料名称
colMapping[6] = new SqlBulkCopyColumnMapping("THB005", "DKB005"); //物料规格
colMapping[7] = new SqlBulkCopyColumnMapping("THB006", "DKB006"); //申请退货数量
colMapping[8] = new SqlBulkCopyColumnMapping("THB007", "DKB007"); //实际退货数量
colMapping[9] = new SqlBulkCopyColumnMapping("THB008", "DKB008"); //差异数量
colMapping[10] = new SqlBulkCopyColumnMapping("THB009", "DKB009"); //单位
colMapping[11] = new SqlBulkCopyColumnMapping("THB010", "DKB010"); //单价
colMapping[12] = new SqlBulkCopyColumnMapping("THB011", "DKB011"); //金额
colMapping[13] = new SqlBulkCopyColumnMapping("THB012", "DKB012"); //来源单号
colMapping[14] = new SqlBulkCopyColumnMapping("THB013", "DKB013"); //来源序号
colMapping[15] = new SqlBulkCopyColumnMapping("THB014", "DKB014"); //包装明细
colMapping[16] = new SqlBulkCopyColumnMapping("THB015", "DKB015"); //不良区分
colMapping[17] = new SqlBulkCopyColumnMapping("THB016", "DKB016"); //不良项目
colMapping[18] = new SqlBulkCopyColumnMapping("THB017", "DKB017"); //仓库编码
colMapping[19] = new SqlBulkCopyColumnMapping("THB018", "DKB018"); //备注
colMapping[20] = new SqlBulkCopyColumnMapping("ERPMXID", "ERPMXID"); //ERPMXID
DataTableToSQLServer(dt, conStr, "PURDKB", colMapping, ref ErrorMsg);
#endregion
}
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
if (dt.TableName == "ERPTHA")
{
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from PURDKA where ERPID in ('{0}'); delete from PURDKB where ERPID in ('{0}'); ", string.Join("','", IDList));
}
_sqlHelper.ExecuteNonQuery(sql);
#endregion
}
}
return true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
return false;
}
}
#endregion
#region 18.MesToErp(同步推送) bool MesToErp(DataRow dr, ref string ErrorMsg)
///
/// MesToErp(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool MesToErp(DataRow dr, ref string ErrorMsg,string rtaskid)
{
string sql = string.Format(@" update sysdec set states='NEWIN' WHERE TASK_ID='{0}' ", rtaskid);
_sqlHelper.ExecuteNonQuery(sql);
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.UserServiceImplService a = new WebReference.UserServiceImplService();
WebReference.user US = new WebReference.user();
// sendt.arg0.
US.category = task_name;
US.data = data_inserted;
US.op = operatype;
string res = a.Body(US).msg.ToString();
sql = string.Format(@" update sysdec set states='NEW' WHERE TASK_ID='{0}' ", rtaskid);
_sqlHelper.ExecuteNonQuery(sql);
//string res = a.arg0.ws.MES_TO_ERP_DataTransport(task_name, descript, create_time, creator, operatype, source_table, target_table, data, data_inserted, data_deleted);
if (res.Contains("OK"))
{
ErrorMsg = res;
return true;
}
else
{
ErrorMsg = res;
return false;
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#endregion
private bool MesToErpLD(DataRow dr, ref string ErrorMsg, string rtaskid)
{
string sql = string.Format(@" update sysdec set states='NEWIN' WHERE TASK_ID='{0}' ", rtaskid);
_sqlHelper.ExecuteNonQuery(sql);
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();
WebReferenceLD1.UserServiceImplService a = new WebReferenceLD1.UserServiceImplService();
WebReferenceLD1.user US = new WebReferenceLD1.user();
// sendt.arg0.
US.category = "TB_MESTOERP_CGRK";
US.data = data_inserted;
US.op = operatype;
string res = a.Body(US).msg.ToString();
sql = string.Format(@" update sysdec set states='NEW' WHERE TASK_ID='{0}' ", rtaskid);
_sqlHelper.ExecuteNonQuery(sql);
//string res = a.arg0.ws.MES_TO_ERP_DataTransport(task_name, descript, create_time, creator, operatype, source_table, target_table, data, data_inserted, data_deleted);
if (res.Contains("OK"))
{
ErrorMsg = res;
return true;
}
else
{
ErrorMsg = res;
return false;
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#region 19.ERPXSA销售订单(同步推送) bool TB_ERPTOMES_XSD(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPXSA销售订单(同步推送)
///
/// 数据集
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_XSD(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 INSERT
if (dt.TableName == "ERPXSA")
{
#region 采购订单主表的处理
dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = "Y";
if (dr["XSA002"].ToString() == "")
{
dr["XSA002"] = "2201";
}
if (dr["XSA012"].ToString() == "")
{
dr["XSA012"] = "0";
}
}
//插入前先删除重复键
DeleteDataBeforeInsert("COMDBA", "ERPID", string.Join("','", IDList), "");
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
colMapping[1] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); //ID
colMapping[2] = new SqlBulkCopyColumnMapping("XSA001", "DBA001"); // 订单单号
colMapping[3] = new SqlBulkCopyColumnMapping("XSA002", "DBA002"); // 订单单别
colMapping[4] = new SqlBulkCopyColumnMapping("XSA003", "DBA003"); // 受订日期
colMapping[5] = new SqlBulkCopyColumnMapping("XSA004", "DBA004"); // 单据版本
colMapping[6] = new SqlBulkCopyColumnMapping("XSA005", "DBA005"); // 单据日期
colMapping[7] = new SqlBulkCopyColumnMapping("XSA006", "DBA006"); // 工厂编号
colMapping[8] = new SqlBulkCopyColumnMapping("XSA007", "DBA007"); // 工厂简称
colMapping[9] = new SqlBulkCopyColumnMapping("XSA008", "DBA008"); // 客户编号
colMapping[10] = new SqlBulkCopyColumnMapping("XSA009", "DBA009"); // 客户简称
colMapping[11] = new SqlBulkCopyColumnMapping("XSA010", "DBA010"); // 客户单号
colMapping[12] = new SqlBulkCopyColumnMapping("XSA011", "DBA011"); // 部门编号
colMapping[13] = new SqlBulkCopyColumnMapping("XSA012", "DBA033"); // 总数量
colMapping[14] = new SqlBulkCopyColumnMapping("XSA013", "DBA023"); // 备注
colMapping[15] = new SqlBulkCopyColumnMapping("XSA014", "DBA031"); // 审核日期
colMapping[16] = new SqlBulkCopyColumnMapping("XSA015", "DBA032"); // 审核者
DataTableToSQLServer(dt, conStr, "COMDBA", colMapping, ref ErrorMsg);
#endregion
}
if (dt.TableName == "ERPXSB")
{
#region 采购订单明细的处理
dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = "Y";
if (dr["XSB008"].ToString() == "")
{
dr["XSB008"] = "0";
}
if (dr["XSB009"].ToString() == "")
{
dr["XSB009"] = "0";
}
if (dr["XSB010"].ToString() == "")
{
dr["XSB010"] = "0";
}
if (dr["XSB012"].ToString() == "")
{
dr["XSB012"] = "0";
}
}
//插入前先删除重复键
DeleteDataBeforeInsert("COMDBB", "ERPID", string.Join("','", IDList), "");
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
colMapping[1] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); //ERPID
colMapping[2] = new SqlBulkCopyColumnMapping("XSB001", "DBB001"); //订单单号
colMapping[3] = new SqlBulkCopyColumnMapping("XSB002", "DBB002"); //序号
colMapping[4] = new SqlBulkCopyColumnMapping("XSB003", "DBB003"); //产品编码
colMapping[5] = new SqlBulkCopyColumnMapping("XSB004", "DBB004"); //产品名称
colMapping[6] = new SqlBulkCopyColumnMapping("XSB005", "DBB005"); //产品规格
colMapping[7] = new SqlBulkCopyColumnMapping("XSB006", "DBB006"); //预交货日期
colMapping[8] = new SqlBulkCopyColumnMapping("XSB007", "DBB007"); //订单数量
colMapping[9] = new SqlBulkCopyColumnMapping("XSB008", "DBB008"); //单位
colMapping[10] = new SqlBulkCopyColumnMapping("XSB009", "DBB009"); //单价
colMapping[11] = new SqlBulkCopyColumnMapping("XSB010", "DBB010"); //金额
colMapping[12] = new SqlBulkCopyColumnMapping("XSB011", "DBB011"); //客户产品编码
colMapping[13] = new SqlBulkCopyColumnMapping("XSB012", "DBB012"); //已交货数
colMapping[14] = new SqlBulkCopyColumnMapping("XSB013", "DBB013"); //结束
colMapping[15] = new SqlBulkCopyColumnMapping("XSB014", "DBB015"); //备注
DataTableToSQLServer(dt, conStr, "COMDBB", colMapping, ref ErrorMsg);
#endregion
}
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
if (dt.TableName == "ERPXSA")
{
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
}
sql = string.Format(@" delete from COMDBA where ERPID in ('{0}'); delete from COMDBB where ERPID in ('{0}'); ", string.Join("','", IDList));
}
_sqlHelper.ExecuteNonQuery(sql);
#endregion
}
}
return true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
return false;
}
}
#endregion
#region 20.icstockbill委外出库单(同步推送) TB_ERPTOMES_WCK(DataSet ds, string operatype, ref string ErrorMsg)
///
/// icstockbill委外出库单(同步推送)
///
/// 数据集
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_WCK(DataSet ds, string operatype, ref string ErrorMsg)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
string dtName = "";
string BillNo = "";
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 == "icstockbill")
{
#region 委外出库单主表的处理
dt.Columns.Add("AUDITING", typeof(string));
dt.Columns.Add("DAF001", typeof(string));
dt.Columns.Add("DAF017", typeof(decimal));
dt.Columns.Add("DAF026", typeof(decimal));
dt.Columns.Add("DAF027", typeof(decimal));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["FInterID"].ToString());
dr["AUDITING"] = "N";
dr["DAF001"] = "5408";//委外出库单
BillNo = dr["FBillNo"].ToString();
dr["DAF017"] = "0";
dr["DAF026"] = "0";
dr["DAF027"] = "0";
}
//插入前先删除重复键
DeleteDataBeforeInsert("WOMDAF", "ERPID", string.Join("','", IDList), "");
DataTable dtHead = dt.DefaultView.ToTable(true, "AUDITING", "FInterID", "DAF001", "DAF017", "DAF026", "DAF027", "FBillNo", "ftdate", "fsupplyid", "fsupplyName", "fexplanation", "ffmanagerid", "FDeptID");
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dtHead.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
colMapping[1] = new SqlBulkCopyColumnMapping("FInterID", "ERPID"); //ERPID
colMapping[2] = new SqlBulkCopyColumnMapping("DAF001", "DAF001"); //单别
colMapping[3] = new SqlBulkCopyColumnMapping("FBillNo", "DAF002"); //单号
colMapping[4] = new SqlBulkCopyColumnMapping("ftdate", "DAF005"); //单据日期
colMapping[5] = new SqlBulkCopyColumnMapping("fsupplyid", "DAF012"); //委外厂商
colMapping[6] = new SqlBulkCopyColumnMapping("fsupplyname", "DAF013"); //厂商名称
colMapping[7] = new SqlBulkCopyColumnMapping("fexplanation", "DAF021"); //备注
colMapping[8] = new SqlBulkCopyColumnMapping("ffmanagerid", "DAF007"); //领料人
colMapping[9] = new SqlBulkCopyColumnMapping("FDeptID", "DAF004"); //部门
colMapping[10] = new SqlBulkCopyColumnMapping("DAF017", "DAF017"); //生产套数
colMapping[11] = new SqlBulkCopyColumnMapping("DAF026", "DAF026"); //指令数量
colMapping[12] = new SqlBulkCopyColumnMapping("DAF027", "DAF027"); //标准产能
DataTableToSQLServer(dtHead, conStr, "WOMDAF", colMapping, ref ErrorMsg);
#endregion
}
if (dt.TableName == "icstockbillentry")
{
#region 委外出库单明细的处理
dt.Columns.Add("AUDITING", typeof(string));
dt.Columns.Add("DAG001", typeof(string));
dt.Columns.Add("DAG002", typeof(string));
dt.Columns.Add("DAG010", typeof(double));
dt.Columns.Add("DAG014", typeof(string));
dt.Columns.Add("DAG015", typeof(string));
dt.Columns.Add("DAG021", typeof(string));
dt.Columns.Add("DAG023", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["FInterID"].ToString());
dr["AUDITING"] = "N";
dr["DAG001"] = "5408";//委外出库单
dr["DAG002"] = BillNo;//委外出库单
dr["DAG010"] = 1;//委外出库单
dr["DAG014"] = dr["FQty"];//配料数量
dr["DAG015"] = "0";//已发数量
dr["DAG021"] = 0 - Convert.ToDecimal(dr["FQty"].ToString());//差异数量
dr["DAG023"] = dr["FQty"];//待发数量
if (dr["FQty"].ToString() == "")
{
dr["FQty"] = "0";
}
}
//处理K3的产品信息在明细的问题
sql = "";
DataTable dtCPMsg = dt.DefaultView.ToTable(true, "FInterID", "FNumberProduct", "FNameProduct", "FModelProduct", "FOrderBillNo");
foreach (DataRow dr in dtCPMsg.Rows)
{
sql += string.Format(@"
update WOMDAF
set DAF023='{1}',--产品编码
DAF024='{2}',--产品名称
DAF025='{3}',--产品型号
DAF010='{4}'--委外订单号
where ERPID='{0}';
", dr["FInterID"], dr["FNumberProduct"], dr["FNameProduct"], dr["FModelProduct"], dr["FOrderBillNo"]);
}
_sqlHelper.ExecuteNonQuery(sql);
//插入前先删除重复键
DeleteDataBeforeInsert("WOMDAG", "ERPID", string.Join("','", IDList), "");
DataTable dt1 = dt.DefaultView.ToTable(false,
"AUDITING", "FInterID", "DAG001", "DAG002", "FEntryID", "FNumber", "Fname", "Fmodel", "FUnitID",
"DAG010", "FQtyMust", "DAG014", "DAG015", "DAG021", "DAG023" , "FBatchNo", "Fnote", "FDCStockID");
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt1.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
colMapping[1] = new SqlBulkCopyColumnMapping("FInterID", "ERPID"); //ERPID
colMapping[2] = new SqlBulkCopyColumnMapping("DAG001", "DAG001"); // 单别
colMapping[3] = new SqlBulkCopyColumnMapping("DAG002", "DAG002"); // 单号
colMapping[4] = new SqlBulkCopyColumnMapping("FEntryID", "DAG003"); // 序号
colMapping[5] = new SqlBulkCopyColumnMapping("FNumber", "DAG004"); // 物料编码
colMapping[6] = new SqlBulkCopyColumnMapping("Fname", "DAG005"); // 物料名称
colMapping[7] = new SqlBulkCopyColumnMapping("Fmodel", "DAG006"); // 物料规格
colMapping[8] = new SqlBulkCopyColumnMapping("FUnitID", "DAG007"); // 单位
colMapping[9] = new SqlBulkCopyColumnMapping("DAG010", "DAG010"); // BOM用量
colMapping[10] = new SqlBulkCopyColumnMapping("FQtyMust", "DAG011"); // 出库数量
colMapping[11] = new SqlBulkCopyColumnMapping("DAG014", "DAG014"); // 配料数量
colMapping[12] = new SqlBulkCopyColumnMapping("DAG015", "DAG015"); // 已发数量
colMapping[13] = new SqlBulkCopyColumnMapping("DAG021", "DAG021"); // 差异数量
colMapping[14] = new SqlBulkCopyColumnMapping("DAG023", "DAG023"); // 待发数量
colMapping[15] = new SqlBulkCopyColumnMapping("FBatchNo", "DAG026"); // 批号
colMapping[16] = new SqlBulkCopyColumnMapping("Fnote", "DAG033"); // 备注
colMapping[17] = new SqlBulkCopyColumnMapping("FDCStockID", "DAG040"); // 发料仓库
DataTableToSQLServer(dt1, conStr, "WOMDAG", colMapping, ref ErrorMsg);
#endregion
}
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
if (dt.TableName == "icstockbill")
{
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["FInterID"].ToString());
}
sql = string.Format(@" delete from WOMDAF where ERPID in ('{0}'); delete from WOMDAG where ERPID in ('{0}'); ", string.Join("','", IDList));
}
_sqlHelper.ExecuteNonQuery(sql);
#endregion
}
}
return true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
return false;
}
}
#endregion
#region 21.ERPDHA到货单(同步推送) bool TB_ERPTOMES_CGDH(DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPDHA到货单(同步推送)
///
///
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_CGDH(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 INSERT
if (dt.TableName == "ERPDHA")
{
#region 到货单主表的处理
dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = "N";
}
//插入前先删除重复键
DeleteDataBeforeInsert("PURDHA", "ERPID", string.Join("','", IDList), "");
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("DHA001", "DHA001"); //单号
colMapping[1] = new SqlBulkCopyColumnMapping("DHA002", "DHA002"); //单据日期
colMapping[2] = new SqlBulkCopyColumnMapping("DHA003", "DHA003"); //单据类型
colMapping[3] = new SqlBulkCopyColumnMapping("DHA004", "DHA004"); //到货日期
colMapping[4] = new SqlBulkCopyColumnMapping("DHA005", "DHA007"); //供应商编号
colMapping[5] = new SqlBulkCopyColumnMapping("DHA006", "DHA008"); //供应商名称
colMapping[6] = new SqlBulkCopyColumnMapping("DHA007", "DHA009"); //客户编号
colMapping[7] = new SqlBulkCopyColumnMapping("DHA008", "DHA010"); //客户名称
colMapping[8] = new SqlBulkCopyColumnMapping("DHA009", "DHA011"); //供应商送货单号
colMapping[9] = new SqlBulkCopyColumnMapping("DHA010", "DHA012"); //发票号码
colMapping[10] = new SqlBulkCopyColumnMapping("DHA011", "DHA016"); //收货方式
colMapping[11] = new SqlBulkCopyColumnMapping("DHA012", "DHA020"); //是否急料
colMapping[12] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); // ERPID
colMapping[13] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING"); //AUDITING
DataTableToSQLServer(dt, conStr, "PURDHA", colMapping, ref ErrorMsg);
#endregion
}
if (dt.TableName == "ERPDHB")
{
#region 到货单明细的处理
dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = "N";
if (dr["DHB006"].ToString() == "")
{
dr["DHB006"] = "0";
}
if (dr["DHB014"].ToString() == "")
{
dr["DHB014"] = "0";
}
else
{
dr["DHB014"] = Convert.ToDouble(dr["DHB014"]).ToString() ;
}
if (dr["DHB015"].ToString() == "")
{
dr["DHB015"] = "0";
}
else
{
dr["DHB015"] = Convert.ToDouble(dr["DHB015"]).ToString();
}
if (dr["DHB020"].ToString() == "")
{
dr["DHB020"] = "0";
}
else
{
dr["DHB020"] = Convert.ToDouble(dr["DHB020"]).ToString();
}
}
//插入前先删除重复键
DeleteDataBeforeInsert("PURDHB", "ERPID", string.Join("','", IDList), "");
DataTable dtHead = dt.DefaultView.ToTable(true, "DHB001", "DHB002", "DHB003", "DHB004", "DHB005", "DHB006", "DHB007", "DHB008", "DHB009", "DHB010", "DHB011", "DHB012", "DHB013", "DHB014", "AUDITING", "DHB015", "DHB016", "DHB017", "DHB018", "DHB019", "DHB020", "DHB021", "DHB022", "DHB023", "DHB024", "DHB025", "ERPID", "ERPMXID", "WLA027", "WLA029", "WLA031", "WLA033", "WLA035");
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dtHead.Columns.Count];
//SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("DHB001", "DHB001"); // 单号
colMapping[1] = new SqlBulkCopyColumnMapping("DHB002", "DHB002"); // 序号
colMapping[2] = new SqlBulkCopyColumnMapping("DHB003", "DHB003"); // 物料编码
colMapping[3] = new SqlBulkCopyColumnMapping("DHB004", "DHB004"); // 物料名称
colMapping[4] = new SqlBulkCopyColumnMapping("DHB005", "DHB005"); // 物料规格
colMapping[5] = new SqlBulkCopyColumnMapping("DHB006", "DHB006"); // 交货数量
colMapping[6] = new SqlBulkCopyColumnMapping("DHB007", "DHB007"); // 单位
colMapping[7] = new SqlBulkCopyColumnMapping("DHB008", "DHB008"); // 仓库
colMapping[8] = new SqlBulkCopyColumnMapping("DHB009", "DHB009"); // 仓库名称
colMapping[9] = new SqlBulkCopyColumnMapping("DHB010", "DHB010"); // 批号
colMapping[10] = new SqlBulkCopyColumnMapping("DHB011", "DHB011"); // 来源单号
colMapping[11] = new SqlBulkCopyColumnMapping("DHB012", "DHB012"); // 来源序号
colMapping[12] = new SqlBulkCopyColumnMapping("DHB013", "DHB014"); // 到厂日期
colMapping[13] = new SqlBulkCopyColumnMapping("DHB014", "DHB017"); // 进货单价
colMapping[14] = new SqlBulkCopyColumnMapping("DHB015", "DHB018"); // 进货金额
colMapping[15] = new SqlBulkCopyColumnMapping("DHB016", "DHB021"); // 备品数
colMapping[16] = new SqlBulkCopyColumnMapping("DHB017", "DHB025"); // ERP采购单号ID
colMapping[17] = new SqlBulkCopyColumnMapping("DHB018", "DHB026"); // ERP采购序号ID
colMapping[18] = new SqlBulkCopyColumnMapping("DHB019", "DHB027"); // ERP物料ID
colMapping[19] = new SqlBulkCopyColumnMapping("DHB020", "DHB028"); // 当时订单数
colMapping[20] = new SqlBulkCopyColumnMapping("DHB021", "DHB029"); // po下单时间
colMapping[21] = new SqlBulkCopyColumnMapping("DHB022", "DHB024"); // 备注
colMapping[22] = new SqlBulkCopyColumnMapping("DHB023", "DHB046"); // 客户编号
colMapping[23] = new SqlBulkCopyColumnMapping("DHB024", "DHB047"); // 客户名称
colMapping[24] = new SqlBulkCopyColumnMapping("DHB025", "DHB045"); // 销售订单号
colMapping[25] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); // ERPID
colMapping[26] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING"); // AUDITING
colMapping[27] = new SqlBulkCopyColumnMapping("ERPMXID", "ERPMXID"); // ERPMXID
colMapping[28] = new SqlBulkCopyColumnMapping("WLA027", "Exprop1"); // 辅助属性
colMapping[29] = new SqlBulkCopyColumnMapping("WLA029", "Exprop2"); // 辅助属性
colMapping[30] = new SqlBulkCopyColumnMapping("WLA031", "Exprop3"); // 辅助属性
colMapping[31] = new SqlBulkCopyColumnMapping("WLA033", "Exprop4"); // 辅助属性
colMapping[32] = new SqlBulkCopyColumnMapping("WLA035", "Exprop5"); // 辅助属性
DataTableToSQLServer(dtHead, conStr, "PURDHB", colMapping, ref ErrorMsg);
#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
#region 22.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 23.TB_ERPTOMES_GDRK工单入库数量查询 bool TB_ERPTOMES_GDRK(DataSet ds, string operatype, ref string ErrorMsg)
private bool TB_ERPTOMES_GDRK(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 更新工单表 WOMCAA
if (dt.TableName == "ERPGDRKA")
{
foreach (DataRow dr in dt.Rows)
{
sql += string.Format(@" UPDATE WOMCAA SET CAA026 = '{1}', CAA027 = '{2}',CAA013='{4}'
WHERE CAA002='{0}' AND CAA006='{3}' AND CAA025='{5}'
; ", dr["FICMOBILLNO"]
, dr["FStockInQuaAuxQty"], dr["FPickMtlQty"], dr["FItemid"], dr["FStockInQuaAuxQty"], dr["FEntryId"]);
}
#endregion
}
}
else //DELETE
{
continue;
}
}
_sqlHelper.ExecuteNonQuery(sql);
return true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
return false;
}
}
#endregion
#region 24.ERPFHA发货通知单(同步推送) bool TB_ERPTOMES_FHTZ(string task_name,DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPCGA采购订单｜委外订单(同步推送)
///
/// 任务名称
/// 数据集
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_FHTZ(string task_name, DataSet ds, string operatype, ref string ErrorMsg)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
string dtName = "";
if (operatype != "INSERT" && operatype != "UPDATE")
{
sql = string.Format(@" delete from COMDFA where DFA001 in ('{0}') ", operatype);
string sqlTT = string.Format(@" delete from COMDFB where DFB001 in ('{0}') ", operatype);
_sqlHelper.ExecuteNonQuery(sql);
_sqlHelper.ExecuteNonQuery(sqlTT);
return true;
}
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 == "ERPFHA")
{
#region 发货通知单主表的处理
dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = "Y";
}
//插入前先删除重复键
DeleteDataBeforeInsert("COMDFA", "ID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("PURDDA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "PURDDA重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "flagApp");
colMapping[1] = new SqlBulkCopyColumnMapping("ERPID", "ID"); //ID
colMapping[2] = new SqlBulkCopyColumnMapping("FHA001", "DFA001"); //发货单号
colMapping[3] = new SqlBulkCopyColumnMapping("FHA002", "DFA002"); //客户名称
colMapping[4] = new SqlBulkCopyColumnMapping("FHA003", "DFA003"); //发货组织
colMapping[5] = new SqlBulkCopyColumnMapping("FHA004", "DFA004"); //审核日期
colMapping[6] = new SqlBulkCopyColumnMapping("FHA005", "DFA005"); //审核人员
colMapping[7] = new SqlBulkCopyColumnMapping("FHA006", "DFA006"); //关闭状态
colMapping[8] = new SqlBulkCopyColumnMapping("FHA007", "DFA007"); //关闭日期
colMapping[9] = new SqlBulkCopyColumnMapping("FHA008", "DFA008"); //作废状态
colMapping[10] = new SqlBulkCopyColumnMapping("FHA009", "DFA009"); //作废日期
colMapping[11] = new SqlBulkCopyColumnMapping("FHA0010", "DFA010"); //备注
DataTableToSQLServer(dt, conStr, "COMDFA", colMapping, ref ErrorMsg);
#endregion
}
if (dt.TableName == "ERPFHB")
{
#region 采购订单明细的处理
dt.Columns.Add("AUDITING", typeof(string));
foreach (DataRow dr in dt.Rows)
{
IDList.Add(dr["ERPID"].ToString());
dr["AUDITING"] = "Y";
//销售数量
if (dr["FHB008"].ToString() == "")
{
dr["FHB008"] = "0";
}
else
{
double A = Convert.ToDouble(dr["FHB008"].ToString());
string B = A.ToString();
dr["FHB008"] = B;
}
//未出库数
if (dr["FHB020"].ToString() == "")
{
dr["FHB020"] = "0";
}
else
{
double A = Convert.ToDouble(dr["FHB020"].ToString());
string B = A.ToString();
dr["FHB020"] = B;
}
//累计已出库数
if (dr["FHB024"].ToString() == "")
{
dr["FHB024"] = "0";
}
else
{
double A = Convert.ToDouble(dr["FHB024"].ToString());
string B = A.ToString();
dr["FHB024"] = B;
}
if (dr["FHB024"].ToString() == "")
{
dr["FHB024"] = "0";
}
else
{
double A = Convert.ToDouble(dr["FHB024"].ToString());
string B = A.ToString();
dr["FHB024"] = B;
}
}
//插入前先删除重复键
DeleteDataBeforeInsert("COMDFB", "PID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("PURDDB", "ERPID+DDB002", string.Join("','", IDList), ""))
//{
// ErrorMsg = "PURDDB重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "flagApp");
colMapping[1] = new SqlBulkCopyColumnMapping("ERPID", "PID"); //ERPID
colMapping[2] = new SqlBulkCopyColumnMapping("FHB001", "DFB001"); //发货单号
colMapping[3] = new SqlBulkCopyColumnMapping("FHB002", "DFB002"); //序号
colMapping[4] = new SqlBulkCopyColumnMapping("FHB003", "DFB003"); //客户物料编码
colMapping[5] = new SqlBulkCopyColumnMapping("FHB004", "DFB004"); //客户物料名称
colMapping[6] = new SqlBulkCopyColumnMapping("FHB005", "DFB005"); //产品编号
colMapping[7] = new SqlBulkCopyColumnMapping("FHB006", "DFB006"); //产品名称
colMapping[8] = new SqlBulkCopyColumnMapping("FHB007", "DFB007"); //产品规格
colMapping[9] = new SqlBulkCopyColumnMapping("FHB008", "DFB008"); //销售数量
colMapping[10] = new SqlBulkCopyColumnMapping("FHB009", "DFB009"); //销售单位
colMapping[11] = new SqlBulkCopyColumnMapping("FHB010", "DFB010"); //单价
colMapping[12] = new SqlBulkCopyColumnMapping("FHB011", "DFB011"); //含税单价
colMapping[13] = new SqlBulkCopyColumnMapping("FHB012", "DFB012"); //税率
colMapping[14] = new SqlBulkCopyColumnMapping("FHB013", "DFB013"); //税额
colMapping[15] = new SqlBulkCopyColumnMapping("FHB014", "DFB014"); //含税合计
colMapping[16] = new SqlBulkCopyColumnMapping("FHB015", "DFB015"); //要货日期
colMapping[17] = new SqlBulkCopyColumnMapping("FHB016", "DFB016"); //出货仓库
colMapping[18] = new SqlBulkCopyColumnMapping("FHB017", "DFB017"); //计价数量
colMapping[19] = new SqlBulkCopyColumnMapping("FHB018", "DFB018"); //订单单号
colMapping[20] = new SqlBulkCopyColumnMapping("FHB019", "DFB019"); //备注信息
colMapping[21] = new SqlBulkCopyColumnMapping("FHB020", "DFB020"); //未出库数
colMapping[22] = new SqlBulkCopyColumnMapping("FHB021", "DFB021"); //出库上限
colMapping[23] = new SqlBulkCopyColumnMapping("FHB022", "DFB022"); //出库下线
colMapping[24] = new SqlBulkCopyColumnMapping("FHB023", "DFB023"); //是否控制出库数量
colMapping[25] = new SqlBulkCopyColumnMapping("FHB024", "DFB024"); //累计出库数
colMapping[26] = new SqlBulkCopyColumnMapping("FHB025", "DFB025"); //产品类型
colMapping[27] = new SqlBulkCopyColumnMapping("FHB026", "DFB026"); //父标识
colMapping[28] = new SqlBulkCopyColumnMapping("FHB027", "DFB027"); //标识列
colMapping[29] = new SqlBulkCopyColumnMapping("ERP_EntryID", "ID"); //明细ID
DataTableToSQLServer(dt, conStr, "COMDFB", colMapping, ref ErrorMsg);
#endregion
}
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
//if (dt.TableName == "ERPCGA")
//{
// foreach (DataRow dr in dt.Rows)
// {
// IDList.Add(dr["ERPID"].ToString());
// }
// sql = string.Format(@" delete from PURDDA where ERPID in ('{0}'); delete from PURDDB where ERPID in ('{0}'); ", string.Join("','", IDList));
//}
//_sqlHelper.ExecuteNonQuery(sql);
#endregion
}
}
return true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
return false;
}
}
#endregion
#region 25.ERPWWA委外订单(同步推送) bool TB_ERPTOMES_WW(string task_name,DataSet ds, string operatype, ref string ErrorMsg)
///
/// ERPCGA采购订单｜委外订单(同步推送)
///
/// 任务名称
/// 数据集
/// 执行类型
/// 错误说明
///
private bool TB_ERPTOMES_WW(string task_name, DataSet ds, string operatype, ref string ErrorMsg)
{
string sql = "";
//用来拼ID集合
List IDList = new List();
string dtName = "";
if (operatype != "INSERT" && operatype != "UPDATE")
{
sql = string.Format(@" delete from WOMWAA where WAA002 in ('{0}') ", operatype);
_sqlHelper.ExecuteNonQuery(sql);
return true;
}
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 == "ERPWWA")
{
#region 投料单主表的处理
// dt.Columns.Add("AUDITING", typeof(string));
dt.Columns.Add("WAA022", typeof(string));
dt.Columns.Add("WAA020", typeof(string));
foreach (DataRow dr in dt.Rows)
{
String sqlDE = string.Format(@"delete from WOMWAB where PID in ('{0}')", dr["WWTL001"].ToString());
_sqlHelper.ExecuteNonQuery(sqlDE);
IDList.Add(dr["ERPID"].ToString());
//dr["TLA012"] = Convert.ToDouble(dr["TLA012"]).ToString();
//// dr["AUDITING"] = "Y";
//dr["WAA022"] = dr["TLA012"];
////dr["TLA002"] = dr["TLA019"];//工单批次直接取任务单号吧
//dr["WAA020"] = DateTime.Now.ToString("yyyy-MM-dd");
//if (dr["TLA004"].ToString() == "" || dr["TLA004"].ToString() == "1")
//{
// dr["TLA004"] = "5102";//默认DIP工单
//}
//else
//{
// dr["TLA004"] = "5109";//返工工单
//}
}
//插入前先删除重复键
DeleteDataBeforeInsert("WOMWAA", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("WOMCAA", "ERPID", string.Join("','", IDList), ""))
//{
// ErrorMsg = "WOMCAA重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
//colMapping[1] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
colMapping[1] = new SqlBulkCopyColumnMapping("ERPID", "ERPID"); //ERPID
colMapping[2] = new SqlBulkCopyColumnMapping("WWTL001", "WAA001"); //单号
colMapping[3] = new SqlBulkCopyColumnMapping("WWTL020", "WAA002"); //ERP工单批次
colMapping[4] = new SqlBulkCopyColumnMapping("WWTL003", "WAA003"); //客户编号
colMapping[5] = new SqlBulkCopyColumnMapping("WWTL004", "WAA004"); //单据类型
colMapping[6] = new SqlBulkCopyColumnMapping("WWTL005", "WAA005"); //开单日期
colMapping[7] = new SqlBulkCopyColumnMapping("WWTL006", "WAA006"); //产品编码
colMapping[8] = new SqlBulkCopyColumnMapping("WWTL007", "WAA007"); //产品名称
colMapping[9] = new SqlBulkCopyColumnMapping("WWTL008", "WAA008"); //产品规格
colMapping[10] = new SqlBulkCopyColumnMapping("WWTL009", "WAA009"); //单位
colMapping[11] = new SqlBulkCopyColumnMapping("WWTL010", "WAA010"); //预计开工时间
colMapping[12] = new SqlBulkCopyColumnMapping("WWTL011", "WAA011"); //预计完工时间
colMapping[13] = new SqlBulkCopyColumnMapping("WWTL012", "WAA012"); //工单数量
colMapping[14] = new SqlBulkCopyColumnMapping("WWTL013", "WAA013"); //BOM版次
colMapping[15] = new SqlBulkCopyColumnMapping("WWTL014", "WAA014"); //供应商
colMapping[16] = new SqlBulkCopyColumnMapping("WWTL015", "WAA015"); //采购订单
colMapping[17] = new SqlBulkCopyColumnMapping("WWTL016", "WAA016"); //采购订单行号
colMapping[18] = new SqlBulkCopyColumnMapping("WWTL017", "WAA017"); //备注
colMapping[19] = new SqlBulkCopyColumnMapping("WWTL018", "WAA018"); //ERP源单ID
colMapping[20] = new SqlBulkCopyColumnMapping("WWTL019", "WAA019"); //ERP源单序号
colMapping[21] = new SqlBulkCopyColumnMapping("WWTL020", "WAA020"); //委外订单号
colMapping[22] = new SqlBulkCopyColumnMapping("WWTL021", "WAA021"); //工作中心
colMapping[23] = new SqlBulkCopyColumnMapping("WWTL022", "WAA022"); //未入库数
colMapping[24] = new SqlBulkCopyColumnMapping("WWTL023", "WAA023"); //业务状态
colMapping[25] = new SqlBulkCopyColumnMapping("WWTL024", "WAA024"); //领料状态
colMapping[26] = new SqlBulkCopyColumnMapping("WWTL001", "ID"); //客户订单号
//colMapping[0] = new SqlBulkCopyColumnMapping("WAA020", "WAA020"); //审核时间
DataTableToSQLServer(dt, conStr, "WOMWAA", colMapping, ref ErrorMsg);
#endregion
}
if (dt.TableName == "ERPWWB")
{
#region 投料单明细的处理
//dt.Columns.Add("AUDITING", typeof(string));
//dt.Columns.Add("CAB016", typeof(string));
foreach (DataRow dr in dt.Rows)
{
//IDList.Add(dr["ERP_EntryID"].ToString());
if (dr["WWTLB006"].ToString() == " "|| dr["WWTLB006"].ToString() == "")
{
dr["WWTLB006"] = "0";
}
if (dr["WWTLB007"].ToString() == " "|| dr["WWTLB007"].ToString() == "")
{
dr["WWTLB007"] = "0";
}
if (dr["WWTLB012"].ToString() == " "|| dr["WWTLB012"].ToString() == "")
{
dr["WWTLB012"] = "0";
}
if (dr["WWTLB013"].ToString() == " "|| dr["WWTLB013"].ToString() == "")
{
dr["WWTLB013"] = "1";
}
dr["WWTLB006"] = Convert.ToDecimal(dr["WWTLB006"]).ToString();
dr["WWTLB007"] = Convert.ToDecimal(dr["WWTLB007"]).ToString();
dr["WWTLB012"] = Convert.ToDecimal(dr["WWTLB012"]).ToString();
dr["WWTLB013"] = Convert.ToDecimal(dr["WWTLB013"]).ToString();
//// dr["AUDITING"] = "Y";
//dr["CAB016"] = "0";
}
//插入前先删除重复键
DeleteDataBeforeInsert("WOMWAB", "ERPID", string.Join("','", IDList), "");
//if (!DeleteDataBeforeInsert("WOMCAB", "ERPID+CAB002", string.Join("','", IDList), ""))
//{
// ErrorMsg = "WOMCAB重复键删除失败！";
// return false;
//}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count+5];
// colMapping[0] = new SqlBulkCopyColumnMapping("AUDITING", "AUDITING");
colMapping[1] = new SqlBulkCopyColumnMapping("ERP_EntryID", "ERPID"); //ERPID
colMapping[2] = new SqlBulkCopyColumnMapping("WWTLB001", "WAB001"); //用料清单单号
colMapping[3] = new SqlBulkCopyColumnMapping("WWTLB002", "WAB002"); //序号
colMapping[4] = new SqlBulkCopyColumnMapping("WWTLB003", "WAB003"); //材料编码
colMapping[5] = new SqlBulkCopyColumnMapping("WWTLB004", "WAB004"); //材料名称
colMapping[6] = new SqlBulkCopyColumnMapping("WWTLB005", "WAB005"); //材料规格
colMapping[7] = new SqlBulkCopyColumnMapping("WWTLB006", "WAB006"); //需领用量
colMapping[8] = new SqlBulkCopyColumnMapping("WWTLB007", "WAB007"); //已领用量
colMapping[9] = new SqlBulkCopyColumnMapping("WWTLB008", "WAB008"); //工艺
colMapping[10] = new SqlBulkCopyColumnMapping("WWTLB009", "WAB009"); //单位
colMapping[11] = new SqlBulkCopyColumnMapping("WWTLB010", "WAB010"); //材料类型
colMapping[12] = new SqlBulkCopyColumnMapping("WWTLB011", "WAB011"); //主料
colMapping[13] = new SqlBulkCopyColumnMapping("WWTLB012", "WAB012"); //组成用量
colMapping[14] = new SqlBulkCopyColumnMapping("WWTLB013", "WAB013"); //底数
colMapping[15] = new SqlBulkCopyColumnMapping("WWTLB014", "WAB014"); //是否替料
colMapping[16] = new SqlBulkCopyColumnMapping("WWTLB015", "WAB015"); //点位
colMapping[17] = new SqlBulkCopyColumnMapping("WWTLB016", "WAB016"); //委外单号
colMapping[18] = new SqlBulkCopyColumnMapping("WWTLB001", "PID");
colMapping[0] = new SqlBulkCopyColumnMapping("WWTLB017", "WAB017"); //委外单号ID
colMapping[19] = new SqlBulkCopyColumnMapping("WWTLB018", "WAB018");
colMapping[20] = new SqlBulkCopyColumnMapping("ERP_EntryID", "ID");
//colMapping[19] = new SqlBulkCopyColumnMapping("WWTLB017", "CAB024"); //单身备注
//colMapping[0] = new SqlBulkCopyColumnMapping("ERPMXID", "ERPMXID"); //ERPMXID
DataTableToSQLServer(dt, conStr, "WOMWAB", colMapping, ref ErrorMsg);
#endregion
}
#endregion
}
else //DELETE
{
//ErrorMsg = "执行删除数据表";
#region DELETE
if (dt.TableName == "ERPWWA")
{
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 ERPID in ('{0}'); ", string.Join("','", IDList));
}
_sqlHelper.ExecuteNonQuery(sql);
#endregion
}
}
return true;
}
catch (Exception ex)
{
ErrorMsg = "[" + dtName + "]" + ex.Message;
return false;
}
}
#endregion
private bool XKY_GET_DHD(DataRow dr, ref string ErrorMsg)
{
//DataTable dt=new DataTable();
DataTable dt = _sqlHelper.ExecuteDataTable("select top(1) * from XKYDHD(nolock) where 1=2");
ApiCommonParam Apiparam = ApiCommonParam.NewApiCommon();
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();
ArrayList SQLStringList = new ArrayList();
// var client = new HttpClient();
var url = "https://openapi.xiekeyun.com/delivery/getNoList.json";
HttpClient httpClient = new HttpClient();
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
string json = "{\"commonParam\":" + JsonConvert.SerializeObject(Apiparam) + ",\"body\":" + data_inserted + "}";
byte[] postData = Encoding.UTF8.GetBytes(json);
// 创建请求对象
HttpWebRequest request = (HttpWebRequest)WebRequest.Create("https://openapi.xiekeyun.com/delivery/getNoList.json");
request.Method = "POST";
request.ContentType = "application/json";
request.ContentLength = postData.Length;
// 发送请求数据
using (Stream stream = request.GetRequestStream())
{
stream.Write(postData, 0, postData.Length);
}
dynamic responseJson;
// 发送请求并获取响应
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
using (Stream stream = response.GetResponseStream())
using (StreamReader reader = new StreamReader(stream))
{
string result2 = reader.ReadToEnd();
responseJson = JsonConvert.DeserializeObject(result2);
}
// 读取响应内容
//dt.Columns.Add("DELIVERYNO", typeof(string));
//dt.Columns.Add("DELIVERYTYPE", typeof(string));
//dt.Columns.Add("INNERVENDORCODE", typeof(string));
//dt.Columns.Add("PURCHASETYPE", typeof(string));
//dt.Columns.Add("STATUS", typeof(string));
//dt.Columns.Add("LOGISTICSSTATUS", typeof(string));
//dt.Columns.Add("LASTOPERATETIME", typeof(string));
//dt.Columns.Add("GETTIME", typeof(string));
//dt.Columns.Add("FLAG", typeof(string));
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
//colMapping[0] = new SqlBulkCopyColumnMapping("DELIVERYNO", "deliveryno"); //
//colMapping[1] = new SqlBulkCopyColumnMapping("DELIVERYTYPE", "deliverytype"); //
//colMapping[2] = new SqlBulkCopyColumnMapping("INNERVENDORCODE", "innervendorcode"); //
//colMapping[3] = new SqlBulkCopyColumnMapping("PURCHASETYPE", "purchasetype"); //
//colMapping[4] = new SqlBulkCopyColumnMapping("STATUS", "status"); //
//colMapping[5] = new SqlBulkCopyColumnMapping("LOGISTICSSTATUS", "logisticsstatus"); //
//colMapping[6] = new SqlBulkCopyColumnMapping("LASTOPERATETIME", "lastoperatetime"); //
//colMapping[7] = new SqlBulkCopyColumnMapping("GETTIME", "gettime"); //
//colMapping[8] = new SqlBulkCopyColumnMapping("FLAG", "flag"); //
if (responseJson.result == 1)
{
foreach (var item in responseJson.dataList)
{
//插入前先删除重复键
// DeleteDataBeforeInsert("XKYDHD", "DELIVERYNO", string.Join("','", item.deliveryNo), "");
// DeleteDataBeforeInsert("XKYDHD_DETAILS", "DELIVERYNO", string.Join("','", item.deliveryNo), "");
// DeleteDataBeforeInsert("XKYDHD_DETAILS_LINE", "DELIVERYNO", string.Join("','", item.deliveryNo), "");
DataRow dr2 = dt.NewRow();
dr2["DELIVERYNO"] = item.deliveryNo;//通过名称赋值
dr2["DELIVERYTYPE"] = item.deliveryType;//通过名称赋值
dr2["INNERVENDORCODE"] = item.innerVendorCode;//通过名称赋值
dr2["PURCHASETYPE"] = item.purchaseType;//通过名称赋值
dr2["STATUS"] = item.status;//通过名称赋值
dr2["LOGISTICSSTATUS"] = item.logisticsStatus;//通过名称赋值
dr2["LASTOPERATETIME"] = item.lastOperateTime;//通过名称赋值
dr2["GETTIME"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");//通过名称赋值
dr2["FLAG"] = "0";//通过名称赋值
dt.Rows.Add(dr2);
}
if
(DataTableToSQLServer(dt, conStr, "XKYDHD", colMapping, ref ErrorMsg))
return true;
else
return false;
}
else
{
ErrorMsg = responseJson.errorMsg;
return false;
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
private bool XKY_GET_DETAILS(DataRow dr, ref string ErrorMsg)
{
DataTable dt = _sqlHelper.ExecuteDataTable("select top(1) * from XKYDETAIL(nolock) where 1=2");
DataTable dt_line = _sqlHelper.ExecuteDataTable("select top(1) * from XKYDETAIL_LINE(nolock) where 1=2");
ApiCommonParam Apiparam = ApiCommonParam.NewApiCommon();
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();
ArrayList SQLStringList = new ArrayList();
// var client = new HttpClient();
var url = "https://openapi.xiekeyun.com/delivery/getDetail.json";
HttpClient httpClient = new HttpClient();
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
string json = "{\"commonParam\":" + JsonConvert.SerializeObject(Apiparam) + ",\"body\":" + data_inserted + "}";
byte[] postData = Encoding.UTF8.GetBytes(json);
// 创建请求对象
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
request.Method = "POST";
request.ContentType = "application/json";
request.ContentLength = postData.Length;
// 发送请求数据
using (Stream stream = request.GetRequestStream())
{
stream.Write(postData, 0, postData.Length);
}
dynamic responseJson;
// 发送请求并获取响应
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
using (Stream stream = response.GetResponseStream())
using (StreamReader reader = new StreamReader(stream))
{
string result2 = reader.ReadToEnd();
// data_insertedf = result2.Substring(0,3900);
responseJson = JsonConvert.DeserializeObject(result2);
}
// 读取响应内容
// SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[0];
// 读取响应内容
if (responseJson.result == 1)
{
dynamic item = responseJson.data;
//插入前先删除重复键
//DeleteDataBeforeInsert("XKYDHD", "DELIVERYNO", string.Join("','", item.deliveryNo), "");
DeleteDataBeforeInsert("XKYDETAIL", "DELIVERYNO", string.Join("','", item.deliveryNo), "");
// DeleteDataBeforeInsert("XKYDHD_DETAILS_LINE", "DELIVERYNO", string.Join("','", item.deliveryNo), "");
DataRow dr2 = dt.NewRow();
dr2["DELIVERYNO"] = item.deliveryNo;
dr2["INNERVENDORCODE"] = item.innerVendorCode;
dr2["INNERVENDORNAME"] = item.innerVendorName;
dr2["DELIVERYTYPE"] = item.deliveryType;
dr2["DELIVERYDATE"] = item.deliveryDate;
dr2["PLANARRIVEDDATE"] = item.planArrivedDate;
dr2["CONTACTPERSION"] = item.contactPersion;
dr2["CONTACTMOBILE"] = item.contactMobile;
dr2["ADDRESSCODE"] = item.addressCode;
dr2["ADDRESSINFO"] = item.addressInfo;
dr2["DELIVERYCARNO"] = item.deliveryCarNo;
dr2["REMARK"] = item.remark;
dr2["STATUS"] = item.status;
dr2["DIRECTDELIVERYFLAG"] = item.directDeliveryFlag;
dr2["LOGISTICSSTATUS"] = item.logisticsStatus;
dr2["PUBLISHTIME"] = item.publishTime;
dr2["UPDATETIME"] = item.updateTime;
dr2["PROFITCENTERCODE"] = item.profitCenterCode;
dr2["GROSSWEIGHT"] = item.grossWeight;
dr2["NETWEIGHT"] = item.netWeight;
dr2["UNITWEIGHTCODE"] = item.unitWeightCode;
dr2["UNITWEIGHTNAME"] = item.unitWeightName;
dr2["TOTALPACKINGQTY"] = item.totalPackingQty;
dr2["TOTALPACKAGEQUANTITY"] = item.totalPackageQuantity;
dr2["TRANSPORTTYPECODE"] = item.transportTypeCode;
dr2["TRANSPORTTYPENAME"] = item.transportTypeName;
dr2["PACKINGWAYCODE"] = item.packingWayCode;
dr2["PACKINGWAYNAME"] = item.packingWayName;
// dr2["EXTENDN01"] = item.extendN01;
// dr2["EXTENDN02"] = item.extendN02;
// dr2["EXTENDN03"] = item.extendN03;
dr2["CARGODELIVERYTIME"] = item.cargoDeliveryTime;
dr2["LOGISTICSTYPE"] = item.logisticsType;
dr2["LOGISTICSTYPESHOW"] = item.logisticsTypeShow;
dr2["LOGISTICSNUMBER"] = item.logisticsNumber;
dr2["FIRSTCHECK"] = item.firstCheck;
dt.Rows.Add(dr2);
if (DataTableToSQLServer(dt, conStr, "XKYDETAIL", colMapping, ref ErrorMsg))
;
else
return false;
foreach (var item2 in responseJson.data.lineList)
{
DeleteDataBeforeInsert("XKYDETAIL_LINE", "DELIVERYNO", string.Join("','", item.deliveryNo), "");
DataRow dr3 = dt_line.NewRow();
dr3["DELIVERYNO"] = item.deliveryNo;
dr3["LINE_NO"] = item2.lineNo;
dr3["POERPNO"] = item2.poErpNo;
dr3["PURCHASETYPE"] = item2.purchaseType;
dr3["POLINENO"] = item2.poLineNo;
dr3["PRODUCTCODE"] = item2.productCode;
dr3["PRODUCTNAME"] = item2.productName;
dr3["PRODUCTSCALE"] = item2.productScale;
dr3["DELIVERYUNITCODE"] = item2.deliveryUnitCode;
dr3["DELIVERYUNITNAME"] = item2.deliveryUnitName;
dr3["DELIVERYQTY"] = item2.deliveryQty;
dr3["DELIVERYVALUATIONQTY"] = item2.deliveryValuationQty;
dr3["PRICE"] = item2.price;
dr3["TAXPRICE"] = item2.taxPrice;
dr3["SCHDYNAMICVALUE"] = item2.schDynamicValue;
dr3["SCHEDULEBATCHNO"] = item2.scheduleBatchNo;
dr3["SCHEDULEORDERNO"] = item2.scheduleOrderNo;
dr3["SCHEDULEDETAILNO"] = item2.scheduleDetailNo;
dr3["URGENTFLAG"] = item2.urgentFlag;
dr3["SRCBILLTYPE"] = item2.srcBillType;
dr3["SRCORDERLINEREMARK"] = item2.srcOrderLineRemark;
dr3["STATUS"] = item2.status;
dr3["RECEIVESTATUS"] = item2.receiveStatus;
dr3["GROSSWEIGHT"] = item2.grossWeight;
dr3["NETWEIGHT"] = item2.netWeight;
dr3["UNITWEIGHTCODE"] = item2.unitWeightCode;
dr3["UNITWEIGHTNAME"] = item2.unitWeightName;
dr3["PACKINGQTY"] = item2.packingQty;
dr3["BRANDNAME"] = item2.brandName;
dr3["OTHEREXTEND"] = item2.otherExtend;
dr3["ORIGINALCOUNTRYCODE"] = item2.originalCountryCode;
dr3["ORIGINALCOUNTRYNAME"] = item2.originalCountryName;
dr3["REMARK"] = item2.remark;
// dr3["EXTENDN01"] = item2.purchaseDesc.extendN01;
// dr3["EXTENDN02"] = item2.purchaseDesc.extendN02;
// dr3["EXTENDN03"] = item2.purchaseDesc.extendN03;
// dr3["EXTENDN04"] = item2.purchaseDesc.extendN04;
// dr3["EXTENDN05"] = item2.purchaseDesc.extendN05;
// dr3["EXTENDN06"] = item2.purchaseDesc.extendN06;
dr3["STORECODE"] = item2.storeCode;
dr3["STORENAME"] = item2.storeName;
dr3["VERIFYQTY"] = item2.verifyQty;
dr3["ALLOWQTY"] = item2.allowQty;
dr3["COMPROMISEQTY"] = item2.compromiseQty;
dr3["REFUSEDQTY"] = item2.refusedQty;
dr3["RTOERPNO"] = item2.rtoErpNo;
dr3["RTOLINENO"] = item2.rtoLineNo;
dr3["PRODLENGTH"] = item2.prodLength;
dr3["PRODWIDTH"] = item2.prodWidth;
dr3["PRODHEIGHT"] = item2.prodHeight;
dr3["BUYERNAME"] = item2.buyerName;
dr3["EXPECTEDDATE"] = item2.expectedDate;
dr3["ANSWERDATE"] = item2.answerDate;
dr3["CURRENCYCODE"] = item2.currencyCode;
dr3["CURRENCYNAME"] = item2.currencyName;
dr3["SENDVERIFYSTATUS"] = item2.sendVerifyStatus;
dr3["RESULTFLAG"] = item2.resultFlag;
dr3["APPLYDELIVERYQTY"] = item2.applyDeliveryQty;
dr3["APPLYDELIVERYVALUATIONQTY"] = item2.applyDeliveryValuationQty;
dr3["BATCHNUMBER"] = item2.batchNumber;
dr3["RECEIVEQTY"] = item2.receiveQty;
dr3["STORAGEQTY"] = item2.storageQty;
dr3["RETURNQTY"] = item2.returnQty;
dr3["FIRSTCHECK"] = item2.firstCheck;
dt_line.Rows.Add(dr3);
}
if (DataTableToSQLServer(dt_line, conStr, "XKYDETAIL_LINE", colMapping, ref ErrorMsg))
return true;
else
return false;
}
else
{
ErrorMsg = responseJson.errorMsg;
return false;
}
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
private bool XKY_GET_BARCODE(DataRow dr, ref string ErrorMsg)
{
DataTable dt = _sqlHelper.ExecuteDataTable("select top(1) * from XKYBARCODE(nolock) where 1=2");
ApiCommonParam Apiparam = ApiCommonParam.NewApiCommon();
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();
ArrayList SQLStringList = new ArrayList();
// var client = new HttpClient();
var url = "https://openapi.xiekeyun.com/barcode/byDeliveryNo.json";
HttpClient httpClient = new HttpClient();
httpClient.DefaultRequestHeaders.Accept.Add(new MediaTypeWithQualityHeaderValue("application/json"));
string json = "{\"commonParam\":" + JsonConvert.SerializeObject(Apiparam) + ",\"body\":" + data_inserted + "}";
byte[] postData = Encoding.UTF8.GetBytes(json);
// 创建请求对象
HttpWebRequest request = (HttpWebRequest)WebRequest.Create(url);
request.Method = "POST";
request.ContentType = "application/json";
request.ContentLength = postData.Length;
// 发送请求数据
using (Stream stream = request.GetRequestStream())
{
stream.Write(postData, 0, postData.Length);
}
dynamic responseJson;
// 发送请求并获取响应
HttpWebResponse response = (HttpWebResponse)request.GetResponse();
using (Stream stream = response.GetResponseStream())
using (StreamReader reader = new StreamReader(stream))
{
string result2 = reader.ReadToEnd();
responseJson = JsonConvert.DeserializeObject(result2);
}
SqlBulkCopyColumnMapping[] colMapping = new SqlBulkCopyColumnMapping[dt.Columns.Count];
// 读取响应内容
if (responseJson.result == 1)
{
if (responseJson.dataList != null)
{
foreach (var item in responseJson.dataList)
{
//插入前先删除重复键
//DeleteDataBeforeInsert("XKYDHD", "DELIVERYNO", string.Join("','", item.deliveryNo), "");
DeleteDataBeforeInsert("XKYDHD_DETAILS", "DELIVERYNO", string.Join("','", item.deliveryNo), "");
// DeleteDataBeforeInsert("XKYDHD_DETAILS_LINE", "DELIVERYNO", string.Join("','", item.deliveryNo), "");
DataRow dr2 = dt.NewRow();
dr2["DELIVERYNO"] = data;
dr2["POERPNO"] = item.dynamicData.poErpNo;
dr2["POLINENO"] = item.dynamicData.poLineNo;
dr2["PRODUCTCODE"] = item.productCode;
dr2["PRODUCTNAME"] = item.productName;
dr2["PRODUCTSCALE"] = item.productScale;
dr2["SMALLBARCODE"] = item.smallBarcode;
dr2["BIGBARCODE"] = item.bigBarcode;
dr2["OUTERBARCODE"] = item.outerBarcode;
dr2["INCLUDEQTY"] = item.includeQty;
dr2["INNERVENDORCODE"] = item.dynamicData.innerVendorCode;
dr2["DELIVERYDATE"] = item.dynamicData.deliveryDate;
dr2["CREATETYPE"] = item.createType;
dr2["PACKLEVEL"] = item.packLevel;
dr2["UPDATETIME"] = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
dr2["FLAG"] = "0";
dr2["BILL_NO"] = source_table;
dt.Rows.Add(dr2);
}
if (DataTableToSQLServer(dt, conStr, "XKYBARCODE", colMapping, ref ErrorMsg))
return true;
else
return false;
}
return true;
}
;
ErrorMsg = responseJson.errorMsg;
return false;
}
catch (Exception ex)
{
ErrorMsg = ex.Message;
return false;
}
}
#region 封装批量插入数据SQL Server数据的方法
///
/// 封装批量插入数据SQL Server数据的方法
///
/// 源数据表
/// 数据库连接字符串
/// 目标表名
/// 字段映射
public bool DataTableToSQLServer(DataTable dt,string connectString,string tableName,SqlBulkCopyColumnMapping[] colMapping,ref string msg)
{
using (SqlConnection destinationConnection = new SqlConnection(connectString))
{
destinationConnection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.FireTriggers,null))
{
try
{
bulkCopy.DestinationTableName = tableName;//要插入的表的表名
bulkCopy.BatchSize = dt.Rows.Count;
foreach (SqlBulkCopyColumnMapping item in colMapping)
{
if (item==null)
{
continue;
}
bulkCopy.ColumnMappings.Add(item);
}
bulkCopy.WriteToServer(dt);
return true;
}
catch (Exception ex)
{
msg = "[" + tableName + "]" + ex.Message;
return false;
}
finally
{
}
}
}
}
#endregion
#region 插入数据前先删除，以免主键重复 bool DeleteDataBeforeInsert(string TableName, string IDColumnName, string IDList, string OtherWhere)
///
/// 插入数据前先删除，以免主键重复
///
/// 表名
/// 主键列，复主键直接加起来
/// 主键值
/// 其它条件 以and 开头
///
public bool DeleteDataBeforeInsert(string TableName, string IDColumnName, string IDList, string OtherWhere)
{
string sql = string.Format(@"delete from {0} where {1} in ('{2}') {3}", TableName, IDColumnName, IDList, OtherWhere);
return _sqlHelper.ExecuteNonQuery(sql) > 0;
}
#endregion
#region 数据的动作重新判断 bool DataIsInDB(string TableName, string IDColumnName, string IDList, string OtherWhere)
///
/// 数据的动作重新判断
///
/// 表名
/// 主键列，复主键直接加起来
/// 主键值
/// 其它条件 以and 开头
///
public bool DataIsInDB(string TableName, string IDColumnName, string IDList, string OtherWhere)
{
string sql = string.Format(@"select count(1) cnt from {0} with(nolock) where {1} in ('{2}') {3}", TableName, IDColumnName, IDList, OtherWhere);
return (int)_sqlHelper.ExecuteScalar(sql) > 0;
}
#endregion
}
}