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)
|
/// <summary>
|
/// 同步金蝶系统数据
|
/// </summary>
|
/// <param name="taskid">任务ID</param>
|
/// <param name="inputds">JSON数据表集合</param>
|
/// <param name="operatype">INSERT,UPDATE,DELETE</param>
|
/// <param name="drSource">原始数据行</param>
|
/// <param name="msg">返回的执行错误信息</param>
|
/// <returns></returns>
|
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_SLTZ"://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<string> IDList = new List<string>();
|
|
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)
|
/// <summary>
|
/// ERPBMA部门信息表(同步推送)
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_BM(DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
|
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)
|
/// <summary>
|
/// ERPGWA岗位信息表(同步推送)
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_GW(DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
|
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)
|
/// <summary>
|
/// ERPRYA人员信息表(同步推送)
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_RY(DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
|
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)
|
/// <summary>
|
/// ERPWFL物料分类表(同步推送)
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_WF(DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
|
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)
|
/// <summary>
|
/// ERPWLA物料表(同步推送)
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_WL(DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
string sqlL = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
|
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)
|
/// <summary>
|
/// ERPDWA计量单位(同步推送)
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_DW(DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
|
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)
|
/// <summary>
|
/// ERPKHA客户信息(同步推送)
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_KH(DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
|
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)
|
/// <summary>
|
/// ERPGYS供应商信息(同步推送)
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_GYS(DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
|
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)
|
/// <summary>
|
/// ERPCKA仓库信息档(同步推送)
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_CK(DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
|
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)
|
/// <summary>
|
/// ERPGXA工序(同步推送)
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_GX(DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
|
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)
|
/// <summary>
|
/// ERPBOMA(同步推送)
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_BOM(DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
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)
|
/// <summary>
|
/// ERPTDL替代料同步(同步推送)
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_TDL(DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
|
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)
|
/// <summary>
|
/// ERPCGA采购订单｜委外订单(同步推送)
|
/// </summary>
|
/// <param name="task_name">任务名称</param>
|
/// <param name="ds">数据集</param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_CG(string task_name,DataSet ds, string operatype, ref string ErrorMsg,string rtaskid)
|
{
|
string sql = "";
|
string org = "宁波";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
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 = "丽德";
|
dr["NEWERPID"] = "LD" + dr["ERPID"].ToString();
|
IDList.Add("LD" + dr["ERPID"].ToString());
|
|
if (dr["CGA0018"].ToString() == "103")
|
{
|
string UP = string.Format(@"EXEC [WMS_CGLD_TOJX] '{0}' ", rtaskid);
|
_sqlHelper.ExecuteNonQuery(UP);
|
|
string SYSDEC = string.Format(@"UPDATE SYSDEC SET STATES='IGNORE' WHERE task_name='TB_ERPTOMES_CG' AND STATES='NEW' AND TASK_ID ='{0}'", rtaskid);
|
_sqlHelper.ExecuteNonQuery(SYSDEC);
|
|
return true;
|
}
|
|
|
}
|
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)
|
/// <summary>
|
/// ERPTLA生产投料单(同步推送)
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
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<string> IDList = new List<string>();
|
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);
|
}
|
|
}
|
else 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)
|
/// <summary>
|
/// ERPXCKA销售出库单(同步推送)
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_XCK(DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
string djType = "出库";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
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)
|
/// <summary>
|
/// ERPQCKA其它出库单(同步推送)
|
/// </summary>
|
/// <param name="task_name">任务名称</param>
|
/// <param name="ds">数据集</param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_QCK(string task_name, DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
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)
|
/// <summary>
|
/// ERPTHA供应商退货单(同步推送)
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_TH(DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
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)
|
/// <summary>
|
/// MesToErp(同步推送)
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
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)
|
/// <summary>
|
/// ERPXSA销售订单(同步推送)
|
/// </summary>
|
/// <param name="ds">数据集</param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_XSD(DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
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)
|
/// <summary>
|
/// icstockbill委外出库单(同步推送)
|
/// </summary>
|
/// <param name="ds">数据集</param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_WCK(DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
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)
|
/// <summary>
|
/// ERPDHA到货单(同步推送)
|
/// </summary>
|
/// <param name="dt"></param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_CGDH(DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
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<string> IDList = new List<string>();
|
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<string> IDList = new List<string>();
|
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)
|
/// <summary>
|
/// ERPCGA采购订单｜委外订单(同步推送)
|
/// </summary>
|
/// <param name="task_name">任务名称</param>
|
/// <param name="ds">数据集</param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_FHTZ(string task_name, DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
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)
|
/// <summary>
|
/// ERPCGA采购订单｜委外订单(同步推送)
|
/// </summary>
|
/// <param name="task_name">任务名称</param>
|
/// <param name="ds">数据集</param>
|
/// <param name="operatype">执行类型</param>
|
/// <param name="ErrorMsg">错误说明</param>
|
/// <returns></returns>
|
private bool TB_ERPTOMES_WW(string task_name, DataSet ds, string operatype, ref string ErrorMsg)
|
{
|
string sql = "";
|
//用来拼ID集合
|
List<string> IDList = new List<string>();
|
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数据的方法
|
/// <summary>
|
/// 封装批量插入数据SQL Server数据的方法
|
/// </summary>
|
/// <param name="dt">源数据表</param>
|
/// <param name="connectString">数据库连接字符串</param>
|
/// <param name="tableName">目标表名</param>
|
/// <param name="colMapping">字段映射</param>
|
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)
|
/// <summary>
|
/// 插入数据前先删除，以免主键重复
|
/// </summary>
|
/// <param name="TableName">表名</param>
|
/// <param name="IDColumnName">主键列，复主键直接加起来</param>
|
/// <param name="IDList">主键值</param>
|
/// <param name="OtherWhere">其它条件 以and 开头</param>
|
/// <returns></returns>
|
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)
|
/// <summary>
|
/// 数据的动作重新判断
|
/// </summary>
|
/// <param name="TableName">表名</param>
|
/// <param name="IDColumnName">主键列，复主键直接加起来</param>
|
/// <param name="IDList">主键值</param>
|
/// <param name="OtherWhere">其它条件 以and 开头</param>
|
/// <returns></returns>
|
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
|
|
}
|
|
|
}
|
