常用的接口对接
中间库
优点：
1.简单，数据查询、处理起来都比较的简单
2.通过对表的权限控制，可以明确与用户交互了哪些内容，便于问题定位。如控制用户只能对表执行insert或select操作，写入到数据表中的数据都是该用户执行的。
缺点：
1.并发大时，对数据库会造成较大的压力
2.接口变动时，影响较大
3.不太适合多用户的并发。如同一套结构的数据表支持多个不同的用户
URL
简单描述：
1.基于HTTP或者HTTPS的模式对数据进行传输
2.服务端通过暴露地址，参数名称，编码
3.客户端按照指定的参数名称进行调用，调用的过程中对数据进行编码和封装
优点：
1.并发易控制，请求简单，解析简单
2.在B/S应用的系统中支持较好，可以方便的进行通讯，如支付宝就在使用这种方式
缺点：
传输的内容长度受限制
WebService
简单描述：
1.WebService的主要目标是跨平台的可互操作性。为了达到这一目标，WebService完全基于XML（可扩展标记语言），XSD（XMLSchema）等独立于平台、独立于软件供应商的标准，是创建可互操作的、分布式应用程序的新平台。
2.目前主流的对接方式，是行业内认可的标准
3.开源的框架较多，如AXIS2、XFIRE等
关于数据中间库接口的描述
接口主要是解决系统数据相互交换读写的问题。解决方法有如下：
用直接读取数据库的方式，建立特定权限的数据库访问用户，只能访问接口信息相关的部分数据表，而不是全部。这样在读数据时可以保证数据的及时性；并不影响原来系统的数据。
为了保证双方相互访问的透明与高效，可以制定两方都认可的数据访问规范性文档，明确如：数据库名、密码、可读表或视图、及具体字段的含义等信息。目前来说，我做的中间库接口还是比较多的，URL也只占很小一部分。所以今天写个中间库Demo。
SQLServer&&SQLServer，使用多线程
public partial class Form1 : Form
{
public Thread objth00;//采购订单
public Thread objth01;//物料主数据
public Thread objth02;//供应商主数据
public Thread objth03;//客户主数据
public Thread objth04;//成本中心
public Thread objth05;//内部订单
public Form1()
{
InitializeComponent();
}
//中间库连接
static SqlConnection SqlOpen()
{
string connStr = "Data Source=小鹏的电脑\\MSSQLSERVER19;Initial Catalog=SAP_PRD_TEST;User ID=sa;Password=123456;MultipleActiveResultSets=true";
SqlConnection con = new SqlConnection(connStr);
con.Open();
return con;
}
//WMS数据库连接
static SqlConnection SqlOpens()
{
string connStr = "Data Source=小鹏的电脑\\MSSQLSERVER16;Initial Catalog=wms_data;User ID=sa;Password=123456;MultipleActiveResultSets=true";
SqlConnection conn = new SqlConnection(connStr);
conn.Open();
return conn;
}
private void button1_Click(object sender, EventArgs e)
{
objth00 = new Thread(new ThreadStart(this.th_getordno_00));
objth00.Start();
objth01 = new Thread(new ThreadStart(this.th_getordno_01));
objth01.Start();
objth02 = new Thread(new ThreadStart(this.th_getordno_02));
objth02.Start();
objth03 = new Thread(new ThreadStart(this.th_getordno_03));
objth03.Start();
objth04 = new Thread(new ThreadStart(this.th_getordno_04));
objth04.Start();
objth05 = new Thread(new ThreadStart(this.th_getordno_05));
objth05.Start();
button1.Enabled = false;
button2.Enabled = true;
}
public void th_getordno_00()
{
while (true)
{
try
{
getordno_00();
Thread.Sleep(500);
}
catch (Exception e)
{
IniFile ls_IniFile = new IniFile(Environment.CurrentDirectory + "\\log.ini");
ls_IniFile.WriteLog("th_getordno_00", e.Message.ToString());
continue;
}
}
}
public void th_getordno_01()
{
while (true)
{
try
{
getordno_01();
Thread.Sleep(500);
}
catch (Exception e)
{
IniFile ls_IniFile = new IniFile(Environment.CurrentDirectory + "\\log.ini");
ls_IniFile.WriteLog("th_getordno_01", e.Message.ToString());
continue;
}
}
}
public void th_getordno_02()
{
while (true)
{
try
{
getordno_02();
Thread.Sleep(500);
}
catch (Exception e)
{
IniFile ls_IniFile = new IniFile(Environment.CurrentDirectory + "\\log.ini");
ls_IniFile.WriteLog("th_getordno_02", e.Message.ToString());
continue;
}
}
}
public void th_getordno_03()
{
while (true)
{
try
{
getordno_03();
Thread.Sleep(500);
}
catch (Exception e)
{
IniFile ls_IniFile = new IniFile(Environment.CurrentDirectory + "\\log.ini");
ls_IniFile.WriteLog("th_getordno_03", e.Message.ToString());
continue;
}
}
}
public void th_getordno_04()
{
while (true)
{
try
{
getordno_04();
Thread.Sleep(500);
}
catch (Exception e)
{
IniFile ls_IniFile = new IniFile(Environment.CurrentDirectory + "\\log.ini");
ls_IniFile.WriteLog("th_getordno_04", e.Message.ToString());
continue;
}
}
}
public void th_getordno_05()
{
while (true)
{
try
{
getordno_05();
Thread.Sleep(500);
}
catch (Exception e)
{
IniFile ls_IniFile = new IniFile(Environment.CurrentDirectory + "\\log.ini");
ls_IniFile.WriteLog("th_getordno_05", e.Message.ToString());
continue;
}
}
}
//采购订单同步
public void getordno_00()
{
string EBELN = "";
string LIFNR = "";
string NAME1 = "";
string PONAM = "";
string AEDAT = "";
string EBELP = "";
string LOEKZ = "";
string MATNR = "";
string MAKTX = "";
string WERKS = "";
string LGORT = "";
decimal MENGE = 0;
string MEINS = "";
string EINDT = "";
string RETPO = "";
string STATE = "";
string ERDAT = "";
string ERZET = "";
string ERNAM = "";
string WMSST = "";
string TYPE = "";
string MESSAGE = "";
string LPSTATE = "";
string LPDAT = "";
string LPZET = "";
string WMSDAT = "";
string WMSZET = "";
string ERZET2 = "";
string WMSNAM = "";
SqlConnection con = null;
SqlConnection conn = null;
try
{
con = SqlOpen();
conn = SqlOpens();
string sql = "select EBELN,LIFNR,NAME1,PONAM,AEDAT,EBELP,LOEKZ,MATNR,MAKTX,WERKS,LGORT,MENGE,MEINS,EINDT,RETPO,STATE,ERDAT,ERZET,ERNAM from ZWMS_MM0030 where WMSST !='Y'";
SqlCommand sqlComm = new SqlCommand(sql, con);
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
EBELN = reader["EBELN"].ToString();
LIFNR = reader["LIFNR"].ToString();
NAME1 = reader["NAME1"].ToString();
PONAM = reader["PONAM"].ToString();
AEDAT = reader["AEDAT"].ToString();
EBELP = reader["EBELP"].ToString();
LOEKZ = reader["LOEKZ"].ToString();
MATNR = reader["MATNR"].ToString();
MAKTX = reader["MAKTX"].ToString();
WERKS = reader["WERKS"].ToString();
LGORT = reader["LGORT"].ToString();
MENGE = Convert.ToDecimal(reader["MENGE"]);
MEINS = reader["MEINS"].ToString();
EINDT = reader["EINDT"].ToString();
RETPO = reader["RETPO"].ToString();
STATE = reader["STATE"].ToString();
ERDAT = reader["ERDAT"].ToString();
ERZET = reader["ERZET"].ToString();
ERNAM = reader["ERNAM"].ToString();
string insql = "insert into Interface_MM0030(EBELN,LIFNR,NAME1,PONAM,AEDAT,EBELP,LOEKZ,MATNR,MAKTX,WERKS,LGORT,MENGE,MEINS,EINDT,RETPO,STATE,ERDAT,ERZET,ERNAM)";
insql += "values('{0}', '{1}', '{2}','{3}', '{4}','{5}','{6}','{7}','{8}','{9}', '{10}', '{11}','{12}', '{13}','{14}','{15}','{16}','{17}','{18}')";
insql = string.Format(insql, EBELN, LIFNR, NAME1, PONAM, AEDAT, EBELP, LOEKZ, MATNR, MAKTX, WERKS, LGORT, MENGE, MEINS, EINDT, RETPO, STATE, ERDAT, ERZET, ERNAM);
SqlCommand cmd = new SqlCommand(insql, conn);
if (cmd.ExecuteNonQuery() > 0)
{
string upsql = "update ZWMS_MM0030 set WMSST ='Y' where EBELN ='" + EBELN + "'";
SqlCommand cmd2 = new SqlCommand(upsql, con);
if (cmd2.ExecuteNonQuery() > 0)
{
}
else
{
string delsql = "delete from Interface_MM0030 where EBELN ='" + EBELN + "'";
SqlCommand cmd3 = new SqlCommand(delsql, conn);
int rows = cmd3.ExecuteNonQuery();
}
}
}
con.Close();
conn.Close();
GC.Collect();
}
catch (Exception ex)
{
IniFile ls_IniFile = new IniFile(Environment.CurrentDirectory + "\\log.ini");
ls_IniFile.WriteLog("getordno_00", ex.Message.ToString());
}
}
//物料主数据同步
public void getordno_01()
{
string MATNR = "";
string MAKTX = "";
string ENMAKTX = "";
string THMAKTX = "";
string MTART = "";
string MTBEZ = "";
string MATKL = "";
string WGBEZ = "";
string BISMT = "";
string LVORM = "";
string MEINS = "";
string ERDAT = "";
string ERZET = "";
SqlConnection con = null;
SqlConnection conn = null;
try
{
con = SqlOpen();
conn = SqlOpens();
string sql = "select MATNR,MAKTX,ENMAKTX,THMAKTX,MTART,MTBEZ,MATKL,WGBEZ,BISMT,LVORM,MEINS,ERDAT,ERZET from ZWMS_MM0080 where TYPE !='Y'";
SqlCommand sqlComm = new SqlCommand(sql, con);
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
MATNR = reader["MATNR"].ToString();
MAKTX = reader["MAKTX"].ToString();
ENMAKTX = reader["ENMAKTX"].ToString();
THMAKTX = reader["THMAKTX"].ToString();
MTART = reader["MTART"].ToString();
MTBEZ = reader["MTBEZ"].ToString();
MATKL = reader["MATKL"].ToString();
WGBEZ = reader["WGBEZ"].ToString();
BISMT = reader["BISMT"].ToString();
LVORM = reader["LVORM"].ToString();
MEINS = reader["MEINS"].ToString();
ERDAT = reader["ERDAT"].ToString();
ERZET = reader["ERZET"].ToString();
string insql = "insert into Interface_MM0080(MATNR,MAKTX,ENMAKTX,THMAKTX,MTART,MTBEZ,MATKL,WGBEZ,BISMT,LVORM,MEINS,ERDAT,ERZET)";
insql += "values('{0}', '{1}', '{2}','{3}', '{4}','{5}','{6}','{7}','{8}','{9}', '{10}', '{11}','{12}')";
insql = string.Format(insql, MATNR, MAKTX, ENMAKTX, THMAKTX, MTART, MTBEZ, MATKL, WGBEZ, BISMT, LVORM, MEINS, ERDAT, ERZET);
SqlCommand cmd = new SqlCommand(insql, conn);
if (cmd.ExecuteNonQuery() > 0)
{
string upsql = "update ZWMS_MM0080 set TYPE ='Y' where MATNR ='" + MATNR + "'";
SqlCommand cmd2 = new SqlCommand(upsql, con);
if (cmd2.ExecuteNonQuery() > 0)
{
}
else
{
string delsql = "delete from Interface_MM0080 where MATNR ='" + MATNR + "'";
SqlCommand cmd3 = new SqlCommand(delsql, conn);
int rows = cmd3.ExecuteNonQuery();
}
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
IniFile ls_IniFile = new IniFile(Environment.CurrentDirectory + "\\log.ini");
ls_IniFile.WriteLog("getordno_01", ex.Message.ToString());
}
}
//供应商主数据同步
public void getordno_02()
{
string LIFNR = "";
string SUPNAME1 = "";
string SPERM = "";
string ERDAT = "";
string ERZET = "";
SqlConnection con = null;
SqlConnection conn = null;
try
{
con = SqlOpen();
conn = SqlOpens();
string sql = "select LIFNR,SUPNAME1,SPERM,ERDAT,ERZET from ZWMS_MM0090 where TYPE !='Y'";
SqlCommand sqlComm = new SqlCommand(sql, con);
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
LIFNR = reader["LIFNR"].ToString();
SUPNAME1 = reader["SUPNAME1"].ToString();
SPERM = reader["SPERM"].ToString();
ERDAT = reader["ERDAT"].ToString();
ERZET = reader["ERZET"].ToString();
string insql = "insert into Interface_MM0090(LIFNR,SUPNAME1,SPERM,ERDAT,ERZET)";
insql += "values('{0}', '{1}', '{2}','{3}', '{4}')";
insql = string.Format(insql, LIFNR, SUPNAME1, SPERM, ERDAT, ERZET);
SqlCommand cmd = new SqlCommand(insql, conn);
if (cmd.ExecuteNonQuery() > 0)
{
string upsql = "update ZWMS_MM0090 set TYPE ='Y' where LIFNR ='" + LIFNR + "'";
SqlCommand cmd2 = new SqlCommand(upsql, con);
if (cmd2.ExecuteNonQuery() > 0)
{
}
else
{
string delsql = "delete from Interface_MM0090 where LIFNR ='" + LIFNR + "'";
SqlCommand cmd3 = new SqlCommand(delsql, conn);
int rows = cmd3.ExecuteNonQuery();
}
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
IniFile ls_IniFile = new IniFile(Environment.CurrentDirectory + "\\log.ini");
ls_IniFile.WriteLog("getordno_02", ex.Message.ToString());
}
}
//客户主数据同步
public void getordno_03()
{
string KUNNR = "";
string CUSNAME1 = "";
string SPERR = "";
string ERDAT = "";
string ERZET = "";
SqlConnection con = null;
SqlConnection conn = null;
try
{
con = SqlOpen();
conn = SqlOpens();
string sql = "select KUNNR,CUSNAME1,SPERR,ERDAT,ERZET from ZWMS_MM0100 where TYPE !='Y'";
SqlCommand sqlComm = new SqlCommand(sql, con);
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
KUNNR = reader["KUNNR"].ToString();
CUSNAME1 = reader["CUSNAME1"].ToString();
SPERR = reader["SPERR"].ToString();
ERDAT = reader["ERDAT"].ToString();
ERZET = reader["ERZET"].ToString();
string insql = "insert into Interface_MM0100(KUNNR,CUSNAME1,SPERR,ERDAT,ERZET)";
insql += "values('{0}', '{1}', '{2}','{3}', '{4}')";
insql = string.Format(insql, KUNNR, CUSNAME1, SPERR, ERDAT, ERZET);
SqlCommand cmd = new SqlCommand(insql, conn);
if (cmd.ExecuteNonQuery() > 0)
{
string upsql = "update ZWMS_MM0100 set TYPE ='Y' where KUNNR ='" + KUNNR + "'";
SqlCommand cmd2 = new SqlCommand(upsql, con);
if (cmd2.ExecuteNonQuery() > 0)
{
}
else
{
string delsql = "delete from Interface_MM0100 where KUNNR ='" + KUNNR + "'";
SqlCommand cmd3 = new SqlCommand(delsql, conn);
int rows = cmd3.ExecuteNonQuery();
}
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
IniFile ls_IniFile = new IniFile(Environment.CurrentDirectory + "\\log.ini");
ls_IniFile.WriteLog("getordno_03", ex.Message.ToString());
}
}
//成本中心同步
public void getordno_04()
{
string KOSTL = "";
string KTEXT = "";
string ENKTEXT = "";
string THKTEXT = "";
string BUKRS = "";
string ERDAT = "";
string ERZET = "";
SqlConnection con = null;
SqlConnection conn = null;
try
{
con = SqlOpen();
conn = SqlOpens();
string sql = "select KOSTL,KTEXT,ENKTEXT,THKTEXT,BUKRS,ERDAT,ERZET from Sap_Cost_Center where TYPE !='Y'";
SqlCommand sqlComm = new SqlCommand(sql, con);
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
KOSTL = reader["KOSTL"].ToString();
KTEXT = reader["KTEXT"].ToString();
ENKTEXT = reader["ENKTEXT"].ToString();
THKTEXT = reader["THKTEXT"].ToString();
BUKRS = reader["BUKRS"].ToString();
ERDAT = reader["ERDAT"].ToString();
ERZET = reader["ERZET"].ToString();
string insql = "insert into Interface_Sap_Cost_Center(KOSTL,KTEXT,ENKTEXT,THKTEXT,BUKRS,ERDAT,ERZET)";
insql += "values('{0}', '{1}', '{2}','{3}', '{4}','{5}','{6}')";
insql = string.Format(insql, KOSTL, KTEXT, ENKTEXT, THKTEXT, BUKRS, ERDAT, ERZET);
SqlCommand cmd = new SqlCommand(insql, conn);
if (cmd.ExecuteNonQuery() > 0)
{
string upsql = "update Sap_Cost_Center set TYPE ='Y' where KOSTL ='" + KOSTL + "'";
SqlCommand cmd2 = new SqlCommand(upsql, con);
if (cmd2.ExecuteNonQuery() > 0)
{
}
else
{
string delsql = "delete from Interface_Sap_Cost_Center where KOSTL ='" + KOSTL + "'";
SqlCommand cmd3 = new SqlCommand(delsql, conn);
int rows = cmd3.ExecuteNonQuery();
}
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
IniFile ls_IniFile = new IniFile(Environment.CurrentDirectory + "\\log.ini");
ls_IniFile.WriteLog("getordno_04", ex.Message.ToString());
}
}
//内部订单同步
public void getordno_05()
{
string AUFNR = "";
string KTEXT = "";
string BUKRS = "";
string ERDAT = "";
string ERZET = "";
SqlConnection con = null;
SqlConnection conn = null;
try
{
con = SqlOpen();
conn = SqlOpens();
string sql = "select AUFNR,KTEXT,BUKRS,ERDAT,ERZET from Sap_Internal_Orders where TYPE !='Y'";
SqlCommand sqlComm = new SqlCommand(sql, con);
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
AUFNR = reader["AUFNR"].ToString();
KTEXT = reader["KTEXT"].ToString();
BUKRS = reader["BUKRS"].ToString();
ERDAT = reader["ERDAT"].ToString();
ERZET = reader["ERZET"].ToString();
string insql = "insert into Interface_Sap_Internal_Orders(AUFNR,KTEXT,BUKRS,ERDAT,ERZET)";
insql += "values('{0}', '{1}', '{2}','{3}', '{4}')";
insql = string.Format(insql, AUFNR, KTEXT, BUKRS, ERDAT, ERZET);
SqlCommand cmd = new SqlCommand(insql, conn);
if (cmd.ExecuteNonQuery() > 0)
{
string upsql = "update Sap_Internal_Orders set TYPE ='Y' where AUFNR ='" + AUFNR + "'";
SqlCommand cmd2 = new SqlCommand(upsql, con);
if (cmd2.ExecuteNonQuery() > 0)
{
}
else
{
string delsql = "delete from Interface_Sap_Internal_Orders where AUFNR ='" + AUFNR + "'";
SqlCommand cmd3 = new SqlCommand(delsql, conn);
int rows = cmd3.ExecuteNonQuery();
}
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
IniFile ls_IniFile = new IniFile(Environment.CurrentDirectory + "\\log.ini");
ls_IniFile.WriteLog("getordno_05", ex.Message.ToString());
}
}
private void button2_Click(object sender, EventArgs e)
{
System.Environment.Exit(0);
}
}
SQLServer&&Oracle，使用定时器
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
//Oracle中间数据库连接
static OracleConnection OpenConn()
{
OracleConnection conn = new OracleConnection();
conn.ConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.64.1.20)(PORT=1900))(CONNECT_DATA=(SERVICE_NAME=PROD)));Persist Security Info=True;User ID=BJLK;Password=JK123456;";
conn.Open();
return conn;
}
//WMS数据库连接
static SqlConnection SqlOpen()
{
string connStr = "Data Source=.;Initial Catalog=BC604;User ID=wms;Password=wms;MultipleActiveResultSets=true";
SqlConnection con = new SqlConnection(connStr);
con.Open();
return con;
}
//物料基础数据同步WMS（LOG_MATERIAL）
//数据流向：ERP –> WMS
//流程：由ERP发起，ERP将组织好的物料信息定期同步到中间表，WMS通过中间表读取物料信息。
public void BasicInformationSynchronization()
{
OracleConnection conn = null;
SqlConnection con = null;
try
{
string ORGANIZATION_ID; string ORGANIZATION_CODE; string ITEM_CODE;
string ITEM_DESC; string UNIT; string ONHAND_QUANTITY;
string ITEM_COST; string ITEM_STATUS; string ITEM_CATEGORY_ID;
string ITEM_CATEGORY_NAME; string USER_ID; string CREAT_DATE;
string PROCESS_MESSAGE;
conn = OpenConn();//中间库
con = SqlOpen();//WMS
string sql = " select ORGANIZATION_ID,ORGANIZATION_CODE,cast(ITEM_CODE as varchar(100)) as ITEM_CODE,ITEM_DESC,UNIT,ONHAND_QUANTITY,cast(ITEM_COST as varchar(100)) as ITEM_cost,ITEM_STATUS,ITEM_CATEGORY_ID,ITEM_CATEGORY,CREATED_BY,CREATION_DATE,PROCESS_MESSAGE ";
sql = sql + " from CUX_ITEM_DATA_ISSUE where PROCESS_STATUS = 'P' ";
OracleCommand cmd = new OracleCommand(sql, conn);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "CUX_ITEM_DATA_ISSUE");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
ORGANIZATION_ID = ds.Tables[0].Rows[i]["ORGANIZATION_ID"].ToString();
ORGANIZATION_CODE = ds.Tables[0].Rows[i]["ORGANIZATION_CODE"].ToString();
ITEM_CODE = ds.Tables[0].Rows[i]["ITEM_CODE"].ToString();
ITEM_DESC = ds.Tables[0].Rows[i]["ITEM_DESC"].ToString();
UNIT = ds.Tables[0].Rows[i]["UNIT"].ToString();
ONHAND_QUANTITY = ds.Tables[0].Rows[i]["ONHAND_QUANTITY"].ToString();
ITEM_COST = ds.Tables[0].Rows[i]["ITEM_COST"].ToString();
ITEM_STATUS = ds.Tables[0].Rows[i]["ITEM_STATUS"].ToString();
ITEM_CATEGORY_ID = ds.Tables[0].Rows[i]["ITEM_CATEGORY_ID"].ToString();
ITEM_CATEGORY_NAME = ds.Tables[0].Rows[i]["ITEM_CATEGORY"].ToString();
USER_ID = ds.Tables[0].Rows[i]["CREATED_BY"].ToString();
CREAT_DATE = ds.Tables[0].Rows[i]["CREATION_DATE"].ToString();
PROCESS_MESSAGE = ds.Tables[0].Rows[i]["PROCESS_MESSAGE"].ToString();
string insertsql = "insert into CUX_ITEM_DATA_ISSUE(ORGANIZATION_ID,ORGANIZATION_CODE,ITEM_CODE,ITEM_DESC,UNIT,ONHAND_QUANTITY,ITEM_COST,ITEM_STATUS,ITEM_CATEGORY_ID,ITEM_CATEGORY_NAME,USER_ID,CREAT_DATE,PROCESS_MESSAGE)values('" + ORGANIZATION_ID + "','" + ORGANIZATION_CODE + "','" + ITEM_CODE + "','" + ITEM_DESC + "','" + UNIT + "','" + ONHAND_QUANTITY + "','" + ITEM_COST + "','" + ITEM_STATUS + "','" + ITEM_CATEGORY_ID + "','" + ITEM_CATEGORY_NAME + "','" + USER_ID + "','" + CREAT_DATE + "','" + PROCESS_MESSAGE + "')";
SqlCommand cmd2 = new SqlCommand(insertsql, con);
if (cmd2.ExecuteNonQuery() > 0)
{
string updatesql = "update CUX_ITEM_DATA_ISSUE set PROCESS_STATUS ='Y' where ORGANIZATION_ID ='" + ORGANIZATION_ID + "'";
OracleCommand cmd3 = new OracleCommand(updatesql, conn);
int result = cmd3.ExecuteNonQuery();
}
}
conn.Close();
con.Close();
}
catch (Exception ex)
{
MessageBox.Show("基础信息同步异常：" + ex.Message);
}
}
//账户别名同步WMS（替换原部门接口）（account）
//数据流向：ERP –> WMS
//流程：ERP定期将账户别名信息同步到中间表，WMS通过中间表读取账户别名信息，WMS将别名显示在出库单上，供用户选择，后续WMS传ERP出库单据的时候需要带上账户别名信息。
#region
//public void AccountAliasSynchronization()
//{
// OracleConnection conn = null;
// SqlConnection con = null;
// try
// {
// int ORGANIZATION_ID; string ORGANIZATION_CODE; string ORGANIZATION_NAME; int DISPOSITION_ID; string SEGMENT1;
// string DESCRIPTION; string EFFECTIVE_DATE; string DISABLE_DATE; string ACCOUNTS; string ENABLED_FLAG;
// int USER_ID; string CREAT_DATE; string PROCESS_MESSAGE;
// conn = OpenConn();//中间库
// con = SqlOpen();//WMS
// string sql = "select * from ACCOUNT where PROCESS_STATUS = 'P' AND ROWNUM<100";
// OracleCommand cmd = new OracleCommand(sql, conn);
// OracleDataAdapter da = new OracleDataAdapter(cmd);
// DataTable ds = new DataTable();
// da.Fill(ds);
// for (int i = 0; i < ds.Rows.Count; i++)
// {
// int ISSUE_ID = Convert.ToInt32(ds.Rows[i]["ISSUE_ID"]);
// ORGANIZATION_ID = Convert.ToInt32(ds.Rows[i]["ORGANIZATION_ID"]);
// ORGANIZATION_CODE = ds.Rows[i]["ORGANIZATION_CODE"].ToString();
// ORGANIZATION_NAME = ds.Rows[i]["ORGANIZATION_NAME"].ToString();
// DISPOSITION_ID = Convert.ToInt32(ds.Rows[i]["DISPOSITION_ID"]);
// SEGMENT1 = ds.Rows[i]["SEGMENT1"].ToString();
// DESCRIPTION = ds.Rows[i]["DESCRIPTION"].ToString();
// EFFECTIVE_DATE = ds.Rows[i]["EFFECTIVE_DATE"].ToString();
// DISABLE_DATE = ds.Rows[i]["DISABLE_DATE"].ToString();
// ACCOUNTS = ds.Rows[i]["ACCOUNTS"].ToString();
// ENABLED_FLAG = ds.Rows[i]["ENABLED_FLAG"].ToString();
// USER_ID = Convert.ToInt32(ds.Rows[i]["USER_ID"]);
// CREAT_DATE = ds.Rows[i]["CREAT_DATE"].ToString();
// PROCESS_MESSAGE = ds.Rows[i]["PROCESS_MESSAGE"].ToString();
// string insertsql = "insert into ACCOUNT(ORGANIZATION_ID,ORGANIZATION_CODE,ORGANIZATION_NAME,DISPOSITION_ID,SEGMENT1,DESCRIPTION,EFFECTIVE_DATE,DISABLE_DATE,ACCOUNTS,ENABLED_FLAG,USER_ID,CREAT_DATE,PROCESS_MESSAGE)values('" + ORGANIZATION_ID + "','" + ORGANIZATION_CODE + "','" + ORGANIZATION_NAME + "','" + DISPOSITION_ID + "','" + SEGMENT1 + "','" + DESCRIPTION + "','" + EFFECTIVE_DATE + "','" + DISABLE_DATE + "','" + ACCOUNTS + "','" + ENABLED_FLAG + "','" + USER_ID + "','" + CREAT_DATE + "','" + PROCESS_MESSAGE + "')";
// SqlCommand cmd2 = new SqlCommand(insertsql, con);
// if (cmd2.ExecuteNonQuery() > 0)
// {
// string updatesql = "update ACCOUNT set PROCESS_STATUS ='Y' where ORGANIZATION_ID ='" + ORGANIZATION_ID + "'and ISSUE_ID ='" + ISSUE_ID + "'";
// OracleCommand cmd3 = new OracleCommand(updatesql, conn);
// int result = cmd3.ExecuteNonQuery();
// }
// }
// con.Close();
// conn.Close();
// }
// catch (Exception ex)
// {
// MessageBox.Show("账号别名同步异常：" + ex.Message);
// }
//}
#endregion
public void AccountAliasSynchronization()
{
OracleConnection conn = null;
SqlConnection con = null;
try
{
string ORGANIZATION_ID; string ORGANIZATION_CODE; string ORGANIZATION_NAME; string DISPOSITION_ID; string SEGMENT1;
string DESCRIPTION; string EFFECTIVE_DATE; string DISABLE_DATE; string ACCOUNTS; string ENABLED_FLAG;
int USER_ID; string CREAT_DATE; string PROCESS_MESSAGE;
conn = OpenConn();//中间库
con = SqlOpen();//WMS
string sql = "select ISSUE_ID,cast(ORGANIZATION_ID as varchar(300)) as ORGANIZATION_ID,ORGANIZATION_CODE,ORGANIZATION_DESC,cast(DISPOSITION_ID as varchar(300)) as DISPOSITION_ID,SEGMENT1,DESCRIPTION,cast(EFFECTICE_DATE as varchar(100)) as EFFECTICE_DATE,cast(DISABLE_DATE as varchar(100)) as DISABLE_DATE,cast(ACCOUNTS as varchar(100)) as ACCOUNTS,ENABLED_FLAG,USER_ID,CREAT_DATE, PROCESS_MESSAGE from CUX_ACCOUNT_ALIAS_ISSUE where PROCESS_STATUS = 'P'";
OracleCommand cmd = new OracleCommand(sql, conn);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "CUX_ACCOUNT_ALIAS_ISSUE");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
int ISSUE_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["ISSUE_ID"]);
ORGANIZATION_ID = ds.Tables[0].Rows[i]["ORGANIZATION_ID"].ToString();
ORGANIZATION_CODE = ds.Tables[0].Rows[i]["ORGANIZATION_CODE"].ToString();
ORGANIZATION_NAME = ds.Tables[0].Rows[i]["ORGANIZATION_DESC"].ToString();
DISPOSITION_ID = ds.Tables[0].Rows[i]["DISPOSITION_ID"].ToString();
SEGMENT1 = ds.Tables[0].Rows[i]["SEGMENT1"].ToString();
DESCRIPTION = ds.Tables[0].Rows[i]["DESCRIPTION"].ToString();
EFFECTIVE_DATE = ds.Tables[0].Rows[i]["EFFECTICE_DATE"].ToString();
DISABLE_DATE = ds.Tables[0].Rows[i]["DISABLE_DATE"].ToString();
ACCOUNTS = ds.Tables[0].Rows[i]["ACCOUNTS"].ToString();
ENABLED_FLAG = ds.Tables[0].Rows[i]["ENABLED_FLAG"].ToString();
USER_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["USER_ID"]);
CREAT_DATE = ds.Tables[0].Rows[i]["CREAT_DATE"].ToString();
PROCESS_MESSAGE = ds.Tables[0].Rows[i]["PROCESS_MESSAGE"].ToString();
string insertsql = "insert into CUX_ACCOUNT_ALIAS_ISSUE(ORGANIZATION_ID,ORGANIZATION_CODE,ORGANIZATION_NAME,DISPOSITION_ID,SEGMENT1,DESCRIPTION,EFFECTIVE_DATE,DISABLE_DATE,ACCOUNTS,ENABLED_FLAG,USER_ID,CREAT_DATE,PROCESS_MESSAGE)values('" + ORGANIZATION_ID + "','" + ORGANIZATION_CODE + "','" + ORGANIZATION_NAME + "','" + DISPOSITION_ID + "','" + SEGMENT1 + "','" + DESCRIPTION + "','" + EFFECTIVE_DATE + "','" + DISABLE_DATE + "','" + ACCOUNTS + "','" + ENABLED_FLAG + "','" + USER_ID + "','" + CREAT_DATE + "','" + PROCESS_MESSAGE + "')";
SqlCommand cmd2 = new SqlCommand(insertsql, con);
if (cmd2.ExecuteNonQuery() > 0)
{
string updatesql = "update CUX_ACCOUNT_ALIAS_ISSUE set PROCESS_STATUS ='Y' where ISSUE_ID ='"+ ISSUE_ID + "'";
OracleCommand cmd3 = new OracleCommand(updatesql, conn);
int result = cmd3.ExecuteNonQuery();
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("账号别名同步异常：" + ex.Message);
}
}
//采购入库单据下发头表（PROCUREMENTINHEAD）
//数据流向：ERP –> WMS。
//流程：由ERP发起，计划员在ERP系统新建一批采购订单，由审批人员对采购单进行审批，程序自动将审批后的采购单据下发到中间表，WMS从中间表中读取采购单据信息。
public void HeaderTableForPurchasingInboundDocuments()
{
OracleConnection conn = null;
SqlConnection con = null;
try
{
string HEADER_ID; string VENDOR_NAME; string PO_ORDER_NUMBER;
string ORG_ID; string ORDER_TYPE; string CREAT_DATE; int USER_ID; string CURRENCY_CODE;
string PROCESS_MESSAGE;
conn = OpenConn();//中间库
con = SqlOpen();//WMS
string sql = "select cast(HEADER_ID as varchar(300)) as HEADER_ID,VENDOR_NAME,PO_ORDER_NUM,cast(ORG_ID as varchar(300)) as ORG_ID,PO_ORDER_TYPE,CREATion_DATE,USER_ID,DEPARTMENT_CODE,PROCESS_MESSAGE from CUX_PO_IN_NOTICES_HEADER where PROCESS_STATUS = 'P'";
OracleCommand cmd = new OracleCommand(sql, conn);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "CUX_PO_IN_NOTICES_HEADER");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
HEADER_ID = ds.Tables[0].Rows[i]["HEADER_ID"].ToString();
VENDOR_NAME = ds.Tables[0].Rows[i]["VENDOR_NAME"].ToString();
PO_ORDER_NUMBER = ds.Tables[0].Rows[i]["PO_ORDER_NUM"].ToString();
ORG_ID = ds.Tables[0].Rows[i]["ORG_ID"].ToString();
ORDER_TYPE = ds.Tables[0].Rows[i]["PO_ORDER_TYPE"].ToString();
CREAT_DATE = ds.Tables[0].Rows[i]["CREATION_DATE"].ToString();
USER_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["USER_ID"]);
CURRENCY_CODE = ds.Tables[0].Rows[i]["DEPARTMENT_CODE"].ToString();
PROCESS_MESSAGE = ds.Tables[0].Rows[i]["PROCESS_MESSAGE"].ToString();
string insertsql = "insert into CUX_PO_IN_NOTICES_HEADER(HEADER_ID,VENDOR_NAME,PO_ORDER_NUMBER,ORG_ID,ORDER_TYPE,CREAT_DATE,USER_ID,CURRENCY_CODE,PROCESS_MESSAGE)values('" + HEADER_ID + "','" + VENDOR_NAME + "','" + PO_ORDER_NUMBER + "','" + ORG_ID + "','" + ORDER_TYPE + "','" + CREAT_DATE + "','" + USER_ID + "','" + CURRENCY_CODE + "','" + PROCESS_MESSAGE + "')";
SqlCommand cmd2 = new SqlCommand(insertsql, con);
if (cmd2.ExecuteNonQuery() > 0)
{
string updatesql = "update CUX_PO_IN_NOTICES_HEADER set PROCESS_STATUS ='Y' where ORG_ID ='" + ORG_ID + "' and HEADER_ID='" + HEADER_ID + "'";
OracleCommand cmd3 = new OracleCommand(updatesql, conn);
int result = cmd3.ExecuteNonQuery();
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("入库单头同步异常：" + ex.Message);
}
}
//采购入库单据下发行表（PROCUREMENTINITMNO）
public void IssueTableForPurchasingInboundDocuments()
{
OracleConnection conn = null;
SqlConnection con = null;
try
{
string HEADER_ID; string LINE_ID; string ORGAIZATION_CODE; string SUBINV_CODE; string PO_ORDER_NUMBER; string LINE_NUM; string LINE_TYPE; string ITEM_CODE; string ITEM_DESC; string QUANTITY;
string UNIT_PRICE; string UNIT; string ITEM_CATEGORY_ID; string LINE_DESC; string CREAT_DATE; string PROCESS_MESSAGE;
conn = OpenConn();//中间库
con = SqlOpen();//WMS
string sql = "select PROCESS_MESSAGE, LINE_ID,HEADER_ID,PO_LINE_NUM,LINE_TYPE,LINE_TYPE_ID,ITEM_ID,CAST(ITEM_CODE AS VARCHAR(100)) AS ITEM_CODE,CAST(ITEM_DESC AS VARCHAR(100)) AS ITEM_DESC, SPECIFICATIONS,CAST(QUANTITY AS VARCHAR(100)) AS QUANTITY, UNIT, ITEM_CATEGORY_ID, LINE_DESC, PO_ORDER_NUM, CAST(LAST_UPDATE_DATE AS VARCHAR(100)) AS LAST_UPDATE_DATE, LAST_UPDATED_BY,LAST_UPDATE_LOGIN, CAST(CREATION_DATE AS VARCHAR(100)) AS CREATION_DATE, CREATED_BY, ORGANIZATION_CODE, CAST(SUBINV_CODE AS VARCHAR(100)) AS SUBINV_CODE, CAST(UNIT_PRICE AS VARCHAR(100)) AS UNIT_PRICE from CUX_PO_IN_NOTICES_LINE where PROCESS_STATUS = 'P'";
OracleCommand cmd = new OracleCommand(sql, conn);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "CUX_PO_IN_NOTICES_LINE");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
HEADER_ID = ds.Tables[0].Rows[i]["HEADER_ID"].ToString();
LINE_ID = ds.Tables[0].Rows[i]["LINE_ID"].ToString();
ORGAIZATION_CODE = ds.Tables[0].Rows[i]["ORGANIZATION_CODE"].ToString();
SUBINV_CODE = ds.Tables[0].Rows[i]["SUBINV_CODE"].ToString();
PO_ORDER_NUMBER = ds.Tables[0].Rows[i]["PO_ORDER_NUM"].ToString();
LINE_NUM = ds.Tables[0].Rows[i]["PO_LINE_NUM"].ToString();
LINE_TYPE = ds.Tables[0].Rows[i]["LINE_TYPE"].ToString();
ITEM_CODE = ds.Tables[0].Rows[i]["ITEM_CODE"].ToString();
ITEM_DESC = ds.Tables[0].Rows[i]["ITEM_DESC"].ToString();
QUANTITY = ds.Tables[0].Rows[i]["QUANTITY"].ToString();
UNIT_PRICE = ds.Tables[0].Rows[i]["UNIT_PRICE"].ToString();
UNIT = ds.Tables[0].Rows[i]["UNIT"].ToString();
ITEM_CATEGORY_ID = ds.Tables[0].Rows[i]["ITEM_CATEGORY_ID"].ToString();
CREAT_DATE = ds.Tables[0].Rows[i]["CREATION_DATE"].ToString();
LINE_DESC = ds.Tables[0].Rows[i]["LINE_DESC"].ToString();
PROCESS_MESSAGE = ds.Tables[0].Rows[i]["PROCESS_MESSAGE"].ToString();
string insertsql = "insert into CUX_PO_IN_NOTICES_LINE(HEADER_ID,LINE_ID,ORGAIZATION_CODE,SUBINV_CODE, PO_ORDER_NUMBER,LINE_NUM,LINE_TYPE,ITEM_CODE,ITEM_DESC, QUANTITY,UNIT_PRICE, UNIT,ITEM_CATEGORY_ID,LINE_DESC,CREAT_DATE, PROCESS_MESSAGE)values('" + HEADER_ID + "','" + LINE_ID + "','" + ORGAIZATION_CODE + "','" + SUBINV_CODE + "','" + PO_ORDER_NUMBER + "','" + LINE_NUM + "','" + LINE_TYPE + "','" + ITEM_CODE + "','" + ITEM_DESC + "','" + QUANTITY + "','" + UNIT_PRICE + "','" + UNIT + "','" + ITEM_CATEGORY_ID + "','" + LINE_DESC + "','" + CREAT_DATE + "','" + PROCESS_MESSAGE + "')";
SqlCommand cmd2 = new SqlCommand(insertsql, con);
if (cmd2.ExecuteNonQuery() > 0)
{
string updatesql = "update CUX_PO_IN_NOTICES_LINE set PROCESS_STATUS ='Y' where LINE_ID ='" + LINE_ID + "' and HEADER_ID ='" + HEADER_ID + "'";
OracleCommand cmd3 = new OracleCommand(updatesql, conn);
int result = cmd3.ExecuteNonQuery();
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("采购入库行表同步异常：" + ex.Message);
}
}
//采购入库单据反馈头表（PROCUREMENTINFEEDBACKHEAD）
//数据流向：WMS –> ERP
//方案：由WMS发起，对4步骤下发的入库单据做实际货物的入库操作后，将入库完成信息反馈给ERP，ERP自动做采购接收、采购入库。
public void PurchaseInboundDocumentFeedbackHeaderTable()
{
OracleConnection conn = null;
SqlConnection con = null;
try
{
int DELIVER_HEADER_ID; int ORG_ID; string PO_ORDER_NUMBER;
string SOURCE_ORDER_NUMBER; string ORGAIZATION_CODE; string
VENDOR_NAME; int USER_ID; string CREAT_DATE; string PROCESS_MESSAGE;
conn = OpenConn();//中间库
con = SqlOpen();//WMS
string sql = "select * from CUX_PO_DELIVERS_HEADER where PROCESS_STATUS = 'P'";
SqlCommand sqlComm = new SqlCommand(sql, con);
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
DELIVER_HEADER_ID = Convert.ToInt32(reader["DELIVER_HEADER_ID"]);
ORG_ID = Convert.ToInt32(reader["ORG_ID"]);
PO_ORDER_NUMBER = reader["PO_ORDER_NUMBER"].ToString();
SOURCE_ORDER_NUMBER = reader["SOURCE_ORDER_NUMBER"].ToString();
ORGAIZATION_CODE = reader["ORGAIZATION_CODE"].ToString();
VENDOR_NAME = reader["VENDOR_NAME"].ToString();
USER_ID = Convert.ToInt32(reader["USER_ID"]);
CREAT_DATE = reader["CREAT_DATE"].ToString();
PROCESS_MESSAGE = reader["PROCESS_MESSAGE"].ToString();
string insql = "insert into CUX_PO_DELIVERS_HEADER(DELIVER_HEADER_ID,ORG_ID,PO_ORDER_NUM,SOURCE_ORDER_NUM,ORGANIZATION_CODE,VENDOR_NAME,USER_ID,CREATION_DATE,PROCESS_MESSAGE)values('" + DELIVER_HEADER_ID + "','" + ORG_ID + "','" + PO_ORDER_NUMBER + "','" + SOURCE_ORDER_NUMBER + "','" + ORGAIZATION_CODE + "','" + VENDOR_NAME + "','" + USER_ID + "',sysdate ,'" + PROCESS_MESSAGE + "')";
OracleCommand cmd = new OracleCommand(insql, conn);
if (cmd.ExecuteNonQuery() > 0)
{
string updatesql = "update CUX_PO_DELIVERS_HEADER set PROCESS_STATUS ='Y' where DELIVER_HEADER_ID ='" + DELIVER_HEADER_ID + "' and ORG_ID='" + ORG_ID + "'";
SqlCommand sqlComm2 = new SqlCommand(updatesql, con);
int result = sqlComm2.ExecuteNonQuery();
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("采购入库头表反馈同步异常：" + ex.Message);
}
}
//采购入库单据反馈行表（PROCUREMENTINFEEDBACKITMNO）
public void PurchaseInboundDocumentFeedbackLineTable()
{
OracleConnection conn = null;
SqlConnection con = null;
try
{
int DELIVER_LINE_ID; int DELIVER_HEADER_ID; string ORGAIZATION_CODE; string SUBINV_CODE; string PO_ORDER_NUMBER; int LINE_NUM; string SOURCE_ORDER_NUM;
int SOURCE_LINE_NUM; string ITEM_CODE; string ITEM_DESC;
string UNIT; int PRIMARY_QUANTITY; int QUANTITY; string DELIVER_DATE; string CREAT_DATE; string PROCESS_MESSAGE;
conn = OpenConn();//中间库
con = SqlOpen();//WMS
string sql = "select * from CUX_PO_DELIVERS_LINE where PROCESS_STATUS = 'P'";
SqlCommand sqlComm = new SqlCommand(sql, con);
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
DELIVER_LINE_ID = Convert.ToInt32(reader["DELIVER_LINE_ID"]);
DELIVER_HEADER_ID = Convert.ToInt32(reader["DELIVER_HEADER_ID"]);
ORGAIZATION_CODE = reader["ORGAIZATION_CODE"].ToString();
SUBINV_CODE = reader["SUBINV_CODE"].ToString();
PO_ORDER_NUMBER = reader["PO_ORDER_NUMBER"].ToString();
LINE_NUM = Convert.ToInt32(reader["LINE_NUM"]);
SOURCE_ORDER_NUM = reader["SOURCE_ORDER_NUM"].ToString();
SOURCE_LINE_NUM = Convert.ToInt32(reader["SOURCE_LINE_NUM"]);
ITEM_CODE = reader["ITEM_CODE"].ToString();
ITEM_DESC = reader["ITEM_DESC"].ToString();
UNIT = reader["UNIT"].ToString();
PRIMARY_QUANTITY = Convert.ToInt32(reader["PRIMARY_QUANTITY"]);
QUANTITY = Convert.ToInt32(reader["QUANTITY"]);
DELIVER_DATE = reader["DELIVER_DATE"].ToString();
CREAT_DATE = reader["CREAT_DATE"].ToString();
PROCESS_MESSAGE = reader["PROCESS_MESSAGE"].ToString();
string insql = "insert into CUX_PO_DELIVERS_LINER(DELIVER_LINE_ID,DELIVER_HEADER_ID,ORGAIZATION_CODE,SUBINV_CODE,PO_ORDER_NUMBER,PO_LINE_NUM,SOURCE_ORDER_NUM,SOURCE_LINE_NUM,ITEM_CODE,ITEM_DESC,UNIT,PRIMARY_QUANTITY,QUANTITY,DELIVER_DATE,CREATION_DATE,PROCESS_MESSAGE)values('" + DELIVER_LINE_ID + "','" + DELIVER_HEADER_ID + "','" + ORGAIZATION_CODE + "','" + SUBINV_CODE + "','" + PO_ORDER_NUMBER + "','" + LINE_NUM + "','" + SOURCE_ORDER_NUM + "','" + SOURCE_LINE_NUM + "','" + ITEM_CODE + "','" + ITEM_DESC + "','" + UNIT + "','" + PRIMARY_QUANTITY + "','" + QUANTITY + "',sysdate,sysdate,'" + PROCESS_MESSAGE + "')";
OracleCommand cmd = new OracleCommand(insql, conn);
if (cmd.ExecuteNonQuery() > 0)
{
string updatesql = "update CUX_PO_DELIVERS_LINE set PROCESS_STATUS ='Y' where DELIVER_LINE_ID ='" + DELIVER_LINE_ID + "'and DELIVER_HEADER_ID='" + DELIVER_HEADER_ID + "'";
SqlCommand sqlComm2 = new SqlCommand(updatesql, con);
int result = sqlComm2.ExecuteNonQuery();
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("采购入库行表反馈同步异常：" + ex.Message);
}
}
//出库单头表（OUTHEAD）
//数据流向：WMS –> ERP。
//流程：由WMS发起，操作员在WMS系统上新建出库单据，库管员对出库单据进行审核，并将审核通过后的出库单据传递到出库中间表中，ERP从出库中间表中读取出库单据，自动生成对应的搬运单
public void OutboundSingleHeaderTable()
{
OracleConnection conn = null;
SqlConnection con = null;
try
{
int MOVE_HEADER_ID; string ORGAIZATION_CODE; string OUTSTOCK_NUM;
int ORG_ID; string OUTSTOCK_DATE; int TRANSACTION_TYPE_ID;
string DISPOSITION_ID;
string CREAT_DATE; int USER_ID;
string PROCESS_MSSSAGE;
conn = OpenConn();//中间库
con = SqlOpen();//WMS
string sql = "select * from CUX_INV_MOVE_ORDER_H where PROCESS_STATUS = 'P'";
SqlCommand sqlComm = new SqlCommand(sql, con);
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
MOVE_HEADER_ID = Convert.ToInt32(reader["MOVE_HEADER_ID"]);
ORGAIZATION_CODE = reader["ORGAIZATION_CODE"].ToString();
OUTSTOCK_NUM = reader["OUTSTOCK_NUM"].ToString();
ORG_ID = Convert.ToInt32(reader["ORG_ID"]);
OUTSTOCK_DATE = reader["OUTSTOCK_DATE"].ToString();
TRANSACTION_TYPE_ID = Convert.ToInt32(reader["TRANSACTION_TYPE_ID"]);
DISPOSITION_ID = reader["DISPOSITION_ID"].ToString();
CREAT_DATE = reader["CREAT_DATE"].ToString();
USER_ID = Convert.ToInt32(reader["USER_ID"]);
PROCESS_MSSSAGE = reader["PROCESS_MSSSAGE"].ToString();
string insertsql = "insert into CUX_INV_MOVE_ORDER_H(MOVE_HEADER_ID,ORGANIZATION_CODE,OUTSTOCK_NUM,ORG_ID,OUTSTOCK_DATE,TRANSACTION_TYPE_ID,DISPOSITION_ID,USER_ID,CREATION_DATE,PROCESS_MESSAGE,PROCESS_STATUS)values('" + MOVE_HEADER_ID + "','" + ORGAIZATION_CODE + "','" + OUTSTOCK_NUM + "','" + ORG_ID + "',SYSDATE,'" + TRANSACTION_TYPE_ID + "','" + DISPOSITION_ID + "','" + USER_ID + "',SYSDATE,'" + PROCESS_MSSSAGE + "','P')";
OracleCommand cmd = new OracleCommand(insertsql, conn);
if (cmd.ExecuteNonQuery() > 0)
{
string updatesql = "update CUX_INV_MOVE_ORDER_H set PROCESS_STATUS ='Y' where ORG_ID ='" + ORG_ID + "' and OUTSTOCK_NUM='" + OUTSTOCK_NUM + "'";
SqlCommand sqlComm2 = new SqlCommand(updatesql, con);
int result = sqlComm2.ExecuteNonQuery();
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("出库单头同步异常：" + ex.Message);
}
}
//出库单行表（OUTITMNO）
public void OutboundSingleLineTable()
{
OracleConnection conn = null;
SqlConnection con = null;
try
{
int MOVE_LINE_ID; int MOVE_HEADER_ID; string ORGAIZATION_CODE; string SUBINV_CODE; string OUTSTOCK_NUM;
int OUTSTOCK_LINE_NUM; string ITEM_CODE; string ITEM_DESC;
int ITEM_QUANTITY; string UNIT; string OUTSTOCK_DATE; string REMARK; string CREAT_DATE; string PROCESS_MSSSAGE;
conn = OpenConn();//中间库
con = SqlOpen();//WMS
string sql = "select * from CUX_INV_MOVE_ORDER_L where PROCESS_STATUS = 'P'";
SqlCommand sqlComm = new SqlCommand(sql, con);
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
MOVE_LINE_ID = Convert.ToInt32(reader["MOVE_LINE_ID"]);
MOVE_HEADER_ID = Convert.ToInt32(reader["MOVE_HEADER_ID"]);
ORGAIZATION_CODE = reader["ORGAIZATION_CODE"].ToString();
SUBINV_CODE = reader["SUBINV_CODE"].ToString();
OUTSTOCK_NUM = reader["OUTSTOCK_NUM"].ToString();
OUTSTOCK_LINE_NUM = Convert.ToInt32(reader["OUTSTOCK_LINE_NUM"]);
ITEM_CODE = reader["ITEM_CODE"].ToString();
ITEM_DESC = reader["ITEM_DESC"].ToString();
ITEM_QUANTITY = Convert.ToInt32(reader["ITEM_QUANTITY"]);
UNIT = reader["UNIT"].ToString();
OUTSTOCK_DATE = reader["OUTSTOCK_DATE"].ToString();
REMARK = reader["REMARK"].ToString();
CREAT_DATE = reader["CREAT_DATE"].ToString();
PROCESS_MSSSAGE = reader["PROCESS_MSSSAGE"].ToString();
string insertsql = "insert into CUX_INV_MOVE_ORDER_L(MOVE_LINE_ID,MOVE_HEADER_ID,ORGANIZATION_CODE,SUBINV_CODE,OUTSTOCK_NUM,OUTSTOCK_LINE_NUM,ITEM_CODE,ITEM_DESC,ITEM_QUANTITY,UNIT,OUTSTOCK_DATE,CREATION_DATE,PROCESS_STATUS)values('" + MOVE_LINE_ID + "','" + MOVE_HEADER_ID + "','" + ORGAIZATION_CODE + "','" + SUBINV_CODE + "','" + OUTSTOCK_NUM + "','" + OUTSTOCK_LINE_NUM + "','" + ITEM_CODE + "','" + ITEM_DESC + "','" + ITEM_QUANTITY + "','" + UNIT + "',SYSDATE,SYSDATE,'P')";
OracleCommand cmd = new OracleCommand(insertsql, conn);
if (cmd.ExecuteNonQuery() > 0)
{
string updatesql = "update CUX_INV_MOVE_ORDER_L set PROCESS_STATUS ='Y' where OUTSTOCK_NUM ='" + OUTSTOCK_NUM + "' and OUTSTOCK_LINE_NUM ='" + OUTSTOCK_LINE_NUM + "'";
SqlCommand sqlComm2 = new SqlCommand(updatesql, con);
int result = sqlComm2.ExecuteNonQuery();
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("出库单行同步异常：" + ex.Message);
}
}
//出库单反馈头表（OUTFEEDBACKHEAD）
//数据流向：WMS –> ERP
//流程：由WMS发起，WMS将实际货物出库信息反馈到出库确认中间表，ERP读取到出库确认中间表数据自动做物料搬运单处理。
public void OutboundSingleFeedbackHeaderTable()
{
OracleConnection conn = null;
SqlConnection con = null;
try
{
int OUTSTOCK_HEADER_ID; int ORG_ID; string OUTSTOCK_NUM;
string ORGANIZATION_CODE; string OUTSTOCK_DATE; int USER_ID;
string CREAT_DATE; string PROCESS_MSSSAGE;
conn = OpenConn();//中间库
con = SqlOpen();//WMS
string sql = "select * from CUX_EDIT_OUTSTOCK_H where PROCESS_STATUS = 'P'";
SqlCommand sqlComm = new SqlCommand(sql, con);
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
OUTSTOCK_HEADER_ID = Convert.ToInt32(reader["OUTSTOCK_HEADER_ID"]);
ORG_ID = Convert.ToInt32(reader["ORG_ID"]);
OUTSTOCK_NUM = reader["OUTSTOCK_NUM"].ToString();
ORGANIZATION_CODE = reader["ORGANIZATION_CODE"].ToString();
OUTSTOCK_DATE = reader["OUTSTOCK_DATE"].ToString();
USER_ID = Convert.ToInt32(reader["USER_ID"]);
CREAT_DATE = reader["CREAT_DATE"].ToString();
PROCESS_MSSSAGE = reader["PROCESS_MSSSAGE"].ToString();
string insql = "insert into CUX_EDIT_OUTSTOCK_H(OUTSTOCK_HEADER_ID,ORG_ID,OUTSTOCK_NUM,ORGANIZATION_CODE,OUTSTOCK_DATE,USER_ID,CREAT_DATE,PROCESS_STATUS)values('" + OUTSTOCK_HEADER_ID + "','" + ORG_ID + "','" + OUTSTOCK_NUM + "','" + ORGANIZATION_CODE + "','" + OUTSTOCK_DATE + "','" + USER_ID + "','" + CREAT_DATE + "','P')";
OracleCommand cmd = new OracleCommand(insql, conn);
if (cmd.ExecuteNonQuery() > 0)
{
string updatesql = "update CUX_EDIT_OUTSTOCK_H set PROCESS_STATUS ='Y' where ORG_ID ='" + ORG_ID + "' and OUTSTOCK_NUM ='" + OUTSTOCK_NUM + "'";
SqlCommand sqlComm2 = new SqlCommand(updatesql, con);
int result = sqlComm2.ExecuteNonQuery();
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("出库单头表反馈同步异常：" + ex.Message);
}
}
//出库单反馈行表（OUTFEEDBACKITMNO）
public void OutboundSingleFeedbackLineTable()
{
OracleConnection conn = null;
SqlConnection con = null;
try
{
int OUTSTOCK_LINE_ID; int OUTSTOCK_HEADER_ID; string SUBINV_CODE; string OUTSTOCK_NUM; int OUTSTOCK_LINE_NUM;
string ITEM_CODE; string ITEM_DESC; int PRIMARY_QUANTITY;
int QUANTITY; string UNIT; string OUTSTOCK_DATE; string REMARK; string CREAT_DATE; string PROCESS_MSSSAGE;
conn = OpenConn();//中间库
con = SqlOpen();//WMS
string sql = "select * from CUX_EDIT_OUTSTOCK_L where PROCESS_STATUS = 'P'";
SqlCommand sqlComm = new SqlCommand(sql, con);
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
OUTSTOCK_LINE_ID = Convert.ToInt32(reader["OUTSTOCK_LINE_ID"]);
OUTSTOCK_HEADER_ID = Convert.ToInt32(reader["OUTSTOCK_HEADER_ID"]);
SUBINV_CODE = reader["SUBINV_CODE"].ToString();
OUTSTOCK_NUM = reader["OUTSTOCK_NUM"].ToString();
OUTSTOCK_LINE_NUM = Convert.ToInt32(reader["OUTSTOCK_LINE_NUM"]);
ITEM_CODE = reader["ITEM_CODE"].ToString();
ITEM_DESC = reader["ITEM_DESC"].ToString();
PRIMARY_QUANTITY = Convert.ToInt32(reader["PRIMARY_QUANTITY"]);
QUANTITY = Convert.ToInt32(reader["QUANTITY"]);
UNIT = reader["UNIT"].ToString();
OUTSTOCK_DATE = reader["OUTSTOCK_DATE"].ToString();
REMARK = reader["REMARK"].ToString();
CREAT_DATE = reader["CREAT_DATE"].ToString();
PROCESS_MSSSAGE = reader["PROCESS_MSSSAGE"].ToString();
string insql = "insert into CUX_EDIT_OUTSTOCK_L(OUTSTOCK_LINE_ID,OUTSTOCK_HEADER_ID,SUBINV_CODE,OUTSTOCK_NUM,OUTSTOCK_LINE_NUM,ITEM_CODE,ITEM_DESC,PRIMARY_QUANTITY,QUANTITY,UNIT,OUTSTOCK_DATE,REMARK,CREAT_DATE,PROCESS_STATUS)values('" + OUTSTOCK_LINE_ID + "','" + OUTSTOCK_HEADER_ID + "','" + SUBINV_CODE + "','" + OUTSTOCK_NUM + "','" + OUTSTOCK_LINE_NUM + "','" + ITEM_CODE + "','" + ITEM_DESC + "','" + PRIMARY_QUANTITY + "','" + QUANTITY + "','" + UNIT + "','" + OUTSTOCK_DATE + "','" + REMARK + "','" + CREAT_DATE + "','P')";
OracleCommand cmd = new OracleCommand(insql, conn);
if (cmd.ExecuteNonQuery() > 0)
{
string updatesql = "update CUX_EDIT_OUTSTOCK_L set PROCESS_STATUS ='Y' where OUTSTOCK_NUM ='" + OUTSTOCK_NUM + "' and OUTSTOCK_LINE_NUM ='" + OUTSTOCK_LINE_NUM + "'";
SqlCommand sqlComm2 = new SqlCommand(updatesql, con);
int result = sqlComm2.ExecuteNonQuery();
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("出库单行表反馈同步异常：" + ex.Message);
}
}
//采购退货头表（PURCHASERETURNSHEAD）
//数据流向：WMS –> ERP
//流程：由WMS发起，WMS生成采购退货单，并将采购退货单和原采购入库订单进行绑定，将采购退货单据信息反馈到采购退货中间表，ERP读取采购退货中间表数据自动做采退操作(退货至供应商)。
public void PurchaseTeturnHeaderTable()
{
OracleConnection conn = null;
SqlConnection con = null;
try
{
int REFUND_HEADER_ID; int ORG_ID; string ORGANIZATION_CODE; string SUBINV_CODE; string SOURCE_ORDER_NUM; string PO_ORDER_NUMBER; string REFUND_ORDER_NUM; string VENDOR_NAME; string REFUND_DATE; int USER_ID; string
CREAT_DATE; string PROCESS_MESSAGE;
conn = OpenConn();//中间库
con = SqlOpen();//WMS
string sql = "select * from CCUX_PO_REFUND_H where PROCESS_STATUS = 'P'";
SqlCommand sqlComm = new SqlCommand(sql, con);
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
REFUND_HEADER_ID = Convert.ToInt32(reader["REFUND_HEADER_ID"]);
ORG_ID = Convert.ToInt32(reader["ORG_ID"]);
ORGANIZATION_CODE = reader["ORGANIZATION_CODE"].ToString();
SUBINV_CODE = reader["SUBINV_CODE"].ToString();
SOURCE_ORDER_NUM = reader["SOURCE_ORDER_NUM"].ToString();
PO_ORDER_NUMBER = reader["PO_ORDER_NUMBER"].ToString();
REFUND_ORDER_NUM = reader["REFUND_ORDER_NUM"].ToString();
VENDOR_NAME = reader["VENDOR_NAME"].ToString();
REFUND_DATE = reader["REFUND_DATE"].ToString();
USER_ID = Convert.ToInt32(reader["USER_ID"]);
CREAT_DATE = reader["CREAT_DATE"].ToString();
PROCESS_MESSAGE = reader["PROCESS_MESSAGE"].ToString();
string insertsql = "insert into CCUX_PO_REFUND_H(REFUND_HEADER_ID,ORG_ID,ORGANIZATION_CODE,SUBINV_CODE,SOURCE_ORDER_NUM,PO_ORDER_NUMBER,REFUND_ORDER_NUM,VENDOR_NAME,REFUND_DATE,USER_ID,CREATION_DATE,PROCESS_MESSAGE,PROCESS_STATUS)values('" + REFUND_HEADER_ID + "','" + ORG_ID + "','" + ORGANIZATION_CODE + "','" + SUBINV_CODE + "','" + SOURCE_ORDER_NUM + "','" + PO_ORDER_NUMBER + "','" + REFUND_ORDER_NUM + "','" + VENDOR_NAME + "',sysdate,'" + USER_ID + "',sysdate,'" + PROCESS_MESSAGE + "','P')";
OracleCommand cmd = new OracleCommand(insertsql, conn);
if (cmd.ExecuteNonQuery() > 0)
{
string updatesql = "update CCUX_PO_REFUND_H set PROCESS_STATUS ='Y' where ORG_ID ='" + ORG_ID + "' and SOURCE_ORDER_NUM='" + SOURCE_ORDER_NUM + "' and PO_ORDER_NUMBER ='" + PO_ORDER_NUMBER + "' and REFUND_ORDER_NUM='" + REFUND_ORDER_NUM + "'";
SqlCommand sqlComm2 = new SqlCommand(updatesql, con);
int result = sqlComm2.ExecuteNonQuery();
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("采购退货头同步异常：" + ex.Message);
}
}
//采购退货行表（PURCHASERETURNSITMNO）
public void PurchaseTeturnLineTable()
{
OracleConnection conn = null;
SqlConnection con = null;
try
{
int REFUND_LINE_ID; int REFUND_HEADER_ID; string ORGAIZATION_CODE; string REFUND_ORDER_NUM; int REFUND_LINE_NUM; string PO_ORDER_NUMBER; int PO_LINE_NUM; string SOURCE_ORDER_NUM; int SOURCE_LINE_NUM; string ITEM_CODE;
string ITEM_DESC; string UNIT; int PRIMARY_QUANTITY; int QUANTITY; string SUBINV_CODE; string DELIVER_DATE; string CREAT_DATE; string PROCESS_MESSAGE;
conn = OpenConn();//中间库
con = SqlOpen();//WMS
string sql = "select * from CUX_PO_REFUND_L where PROCESS_STATUS = 'P'";
SqlCommand sqlComm = new SqlCommand(sql, con);
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
REFUND_LINE_ID = Convert.ToInt32(reader["REFUND_LINE_ID"]);
REFUND_HEADER_ID = Convert.ToInt32(reader["REFUND_HEADER_ID"]);
ORGAIZATION_CODE = reader["ORGAIZATION_CODE"].ToString();
REFUND_ORDER_NUM = reader["REFUND_ORDER_NUM"].ToString();
REFUND_LINE_NUM = Convert.ToInt32(reader["REFUND_LINE_NUM"]);
PO_ORDER_NUMBER = reader["PO_ORDER_NUMBER"].ToString();
PO_LINE_NUM = Convert.ToInt32(reader["PO_LINE_NUM"]);
SOURCE_ORDER_NUM = reader["SOURCE_ORDER_NUM"].ToString();
SOURCE_LINE_NUM = Convert.ToInt32(reader["SOURCE_LINE_NUM"]);
ITEM_CODE = reader["ITEM_CODE"].ToString();
ITEM_DESC = reader["ITEM_DESC"].ToString();
UNIT = reader["UNIT"].ToString();
PRIMARY_QUANTITY = Convert.ToInt32(reader["PRIMARY_QUANTITY"]);
QUANTITY = Convert.ToInt32(reader["QUANTITY"]);
SUBINV_CODE = reader["SUBINV_CODE"].ToString();
DELIVER_DATE = reader["DELIVER_DATE"].ToString();
CREAT_DATE = reader["CREAT_DATE"].ToString();
PROCESS_MESSAGE = reader["PROCESS_MESSAGE"].ToString();
string insertsql = "insert into CUX_PO_REFUND_L(REFUND_LINE_ID,REFUND_HEADER_ID,ORGANIZATION_CODE,REFUND_ORDER_NUM,REFUND_LINE_NUM,PO_ORDER_NUMBER,PO_LINE_NUM,SOURCE_ORDER_NUM,SOURCE_LINE_NUM,ITEM_CODE,ITEM_DESC,UNIT,PRIMARY_QUANTITY,QUANTITY,SUBINV_CODE,REFUND_DATE,CREATION_DATE,PROCESS_MESSAGE,PROCESS_STATUS)values('" + REFUND_LINE_ID + "','" + REFUND_HEADER_ID + "','" + ORGAIZATION_CODE + "','" + REFUND_ORDER_NUM + "','" + REFUND_LINE_NUM + "','" + PO_ORDER_NUMBER + "','" + PO_LINE_NUM + "','" + SOURCE_ORDER_NUM + "','" + SOURCE_LINE_NUM + "','" + ITEM_CODE + "','" + ITEM_DESC + "','" + UNIT + "','" + PRIMARY_QUANTITY + "','" + QUANTITY + "','" + SUBINV_CODE + "',sysdate,sysdate,'" + PROCESS_MESSAGE + "','P')";
OracleCommand cmd = new OracleCommand(insertsql, conn);
if (cmd.ExecuteNonQuery() > 0)
{
string updatesql = "update CUX_PO_REFUND_L set PROCESS_STATUS ='Y' where REFUND_ORDER_NUM ='" + REFUND_ORDER_NUM + "' and REFUND_LINE_NUM='" + REFUND_LINE_NUM + "' and PO_ORDER_NUMBER ='" + PO_ORDER_NUMBER + "' and PO_LINE_NUM ='" + PO_LINE_NUM + "'";
SqlCommand sqlComm2 = new SqlCommand(updatesql, con);
int result = sqlComm2.ExecuteNonQuery();
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("采购退货行同步异常：" + ex.Message);
}
}
//杂收(搬运单退货)（RETURNOFTHEHANDLINGORDER）
//数据流向：WMS –> ERP
//流程：由WMS发起，在WMS生成出库退货单据，并将出库退货单据和原出库订单进行绑定，将出库退货单据信息反馈到出库退货中间表，ERP读取出库退货中间表数据做账户别名接收。
public void ReturnOfTheHandlingOrder()
{
OracleConnection conn = null;
SqlConnection con = null;
try
{
int ORG_ID; string ORGAIZATION_CODE; string SUBINV_CODE; string OUTSTOCK_NUM;
int OUTSTOCK_LINE_NUM; string OUT_REFUND_NUM; int OUT_REFUND_LINE_NUM;
string ITEM_CODE; string ITEM_DESC; string UNIT; string TRANSACTION_QUANTITY;
string TRANSACTION_DATE; int USER_ID; string CREAT_DATE; string PROCESS_MESSAGE;
conn = OpenConn();//中间库
con = SqlOpen();//WMS
string sql = "select * from CUX_OUTSTOCK_REFUND_TABLE where PROCESS_STATUS = 'P'";
SqlCommand sqlComm = new SqlCommand(sql, con);
SqlDataReader reader = sqlComm.ExecuteReader();
while (reader.Read())
{
int REFUND_ID = Convert.ToInt32(reader["REFUND_ID"]);
ORG_ID = Convert.ToInt32(reader["ORG_ID"]);
ORGAIZATION_CODE = reader["ORGAIZATION_CODE"].ToString();
SUBINV_CODE = reader["SUBINV_CODE"].ToString();
OUTSTOCK_NUM = reader["OUTSTOCK_NUM"].ToString();
OUTSTOCK_LINE_NUM = Convert.ToInt32(reader["OUTSTOCK_LINE_NUM"]);
OUT_REFUND_NUM = reader["OUT_REFUND_NUM"].ToString();
OUT_REFUND_LINE_NUM = Convert.ToInt32(reader["OUT_REFUND_LINE_NUM"]);
ITEM_CODE = reader["ITEM_CODE"].ToString();
ITEM_DESC = reader["ITEM_DESC"].ToString();
UNIT = reader["UNIT"].ToString();
TRANSACTION_QUANTITY = reader["TRANSACTION_QUANTITY"].ToString();
TRANSACTION_DATE = reader["TRANSACTION_DATE"].ToString();
USER_ID = Convert.ToInt32(reader["USER_ID"]);
CREAT_DATE = reader["CREAT_DATE"].ToString();
PROCESS_MESSAGE = reader["PROCESS_MESSAGE"].ToString();
string insql = "insert into CUX_OUTSTOCK_REFUND_TABLE(REFUND_ID,ORG_ID, ORGAIZATION_CODE, SUBINV_CODE, OUTSTOCK_NUM, OUTSTOCK_LINE_NUM, OUT_REFUND_NUM, OUT_REFUND_LINE_NUM, ITEM_CODE, ITEM_DESC, UNIT,TRANSACTION_QUANTITY,TRANSACTION_DATE,USER_ID,CREAT_DATE, PROCESS_STATUS)values('" + REFUND_ID + "','" + ORG_ID + "','" + ORGAIZATION_CODE + "','" + SUBINV_CODE + "','" + OUTSTOCK_NUM + "','" + OUTSTOCK_LINE_NUM + "','" + OUT_REFUND_NUM + "','" + OUT_REFUND_LINE_NUM + "','" + ITEM_CODE + "','" + ITEM_DESC + "','" + UNIT + "','" + TRANSACTION_QUANTITY + "','" + TRANSACTION_DATE + "','" + USER_ID + "','" + CREAT_DATE + "','P')";
OracleCommand cmd = new OracleCommand(insql, conn);
if (cmd.ExecuteNonQuery() > 0)
{
string updatesql = "update CUX_OUTSTOCK_REFUND_TABLE set PROCESS_STATUS ='Y' where ORG_ID ='" + ORG_ID + "' and OUT_REFUND_NUM='" + OUT_REFUND_NUM + "' and OUT_REFUND_LINE_NUM ='" + OUT_REFUND_LINE_NUM + "'";
SqlCommand sqlComm2 = new SqlCommand(updatesql, con);
int result = sqlComm2.ExecuteNonQuery();
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("杂收(搬运单退货)表反馈同步异常：" + ex.Message);
}
}
//库存组织同步接口（新增）（INVENTORYSYNCHRONIZATION）
//数据流向：ERP –> WMS
//流程：ERP将组织好的库存组织信息，自动同步到库存中间表，WMS读取中间表数据存放在WMS本地。
//目的：用户在WMS界面选择创建哪个组织的单据。
public void InventoryOrganizationSynchronization()
{
OracleConnection conn = null;
SqlConnection con = null;
try
{
string SET_OF_BOOKS_ID; string BOOKS_CODE; string BOOKS_NAME; string ORG_ID;
string ORG_NAME; string ORGANIZATION_ID; string ORGANIZATION_CODE; string ORGANIZATION_NAME; string SUBINV_CODE; string SUBINV_DESC; string CREAT_DATE; string PROCESS_MESSAGE;
conn = OpenConn();//中间库
con = SqlOpen();//WMS
string sql = "select ISSUE_ID, SET_OF_BOOKS_ID,BOOKS_CODE,BOOKS_NAME,ORG_ID,ORG_NAME,ORGANIZATION_ID,ORGANIZATION_CODE,ORGANIZATION_NAME,cast(SUBINV_CODE as varchar(100)) as SUBINV_CODE,SUBINV_DESC,CREAT_DATE,PROCESS_MESSAGE,cast(CREATION_DATE as varchar(100)) as CREATION_DATE ,created_by,last_updated_by,cast(LAST_UPDATE_DATE as varchar(100)) as LAST_UPDATE_DATE,last_update_login from CUX_INV_ORG_DATA_ISSUE where PROCESS_STATUS = 'P'";
OracleCommand cmd = new OracleCommand(sql, conn);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "CUX_INV_ORG_DATA_ISSUE");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
int ISSUE_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["ISSUE_ID"]);
SET_OF_BOOKS_ID = ds.Tables[0].Rows[i]["SET_OF_BOOKS_ID"].ToString();
BOOKS_CODE = ds.Tables[0].Rows[i]["BOOKS_CODE"].ToString();
BOOKS_NAME = ds.Tables[0].Rows[i]["BOOKS_NAME"].ToString();
ORG_ID = ds.Tables[0].Rows[i]["ORG_ID"].ToString();
ORG_NAME = ds.Tables[0].Rows[i]["ORG_NAME"].ToString();
ORGANIZATION_ID = ds.Tables[0].Rows[i]["ORGANIZATION_ID"].ToString();
ORGANIZATION_CODE = ds.Tables[0].Rows[i]["ORGANIZATION_CODE"].ToString();
ORGANIZATION_NAME = ds.Tables[0].Rows[i]["ORGANIZATION_NAME"].ToString();
SUBINV_CODE = ds.Tables[0].Rows[i]["SUBINV_CODE"].ToString();
SUBINV_DESC = ds.Tables[0].Rows[i]["SUBINV_DESC"].ToString();
CREAT_DATE = ds.Tables[0].Rows[i]["CREAT_DATE"].ToString();
PROCESS_MESSAGE = ds.Tables[0].Rows[i]["PROCESS_MESSAGE"].ToString();
string insertsql = "insert into CUX_INV_ORG_DATA_ISSUE(SET_OF_BOOKS_ID,BOOKS_CODE,BOOKS_NAME,ORG_ID,ORG_NAME,ORGANIZATION_ID,ORGANIZATION_CODE,ORGANIZATION_NAME,SUBINV_CODE,SUBINV_DESC,CREAT_DATE,PROCESS_MESSAGE)values('" + SET_OF_BOOKS_ID + "','" + BOOKS_CODE + "','" + BOOKS_NAME + "','" + ORG_ID + "','" + ORG_NAME + "','" + ORGANIZATION_ID + "','" + ORGANIZATION_CODE + "','" + ORGANIZATION_NAME + "','" + SUBINV_CODE + "','" + SUBINV_DESC + "','" + CREAT_DATE + "','" + PROCESS_MESSAGE + "')";
SqlCommand cmd2 = new SqlCommand(insertsql, con);
if (cmd2.ExecuteNonQuery() > 0)
{
string updatesql = "update CUX_INV_ORG_DATA_ISSUE set PROCESS_STATUS ='Y' where ORG_ID ='" + ORG_ID + "' and ISSUE_ID='" + ISSUE_ID + "'";
OracleCommand cmd3 = new OracleCommand(updatesql, conn);
int result = cmd3.ExecuteNonQuery();
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("库存组织同步异常：" + ex.Message);
}
}
//搬运单事务处理类型同步接口（新增）（HANDLEORDERTRANSACTIONS）
//数据流向：ERP –> WMS
//流程：ERP将组织好的物料搬运单事务处理类型信息，自动同步到中间表，WMS读取中间表数据存放在WMS本地。
//目的：用户在WMS出库单上选择用哪个类型
public void MoveOrderTransactionTypeSynchronization()
{
OracleConnection conn = null;
SqlConnection con = null;
try
{
string TRANSACTION_TYPE_ID; string TRANSACTION_TYPE_NAME; string TRANSACTION_TYPE_DESC; string SOURCE_TYPE_ID; string SOURCE_TYPE_NAME;
string USER_DEFINED_FLAG; string CREAT_DATE; string PROCESS_MESSAGE;
conn = OpenConn();//中间库
con = SqlOpen();//WMS
string sql = "select type_id, cast(TRANSACTION_TYPE_ID as varchar(300)) as TRANSACTION_TYPE_ID,TRANSACTION_TYPE_NAME,TRANSACTION_TYPE_DESC,cast(SOURCE_TYPE_ID as varchar(300)) as SOURCE_TYPE_ID,SOURCE_TYPE_NAME,CREAT_DATE,CAST(CREATION_DATE AS varchar(100)) as CREATION_DATE,created_by,last_updated_by,cast(last_update_date as varchar(100)) as last_update_date,last_update_login,PROCESS_MESSAGE from CUX_TRANS_TYPE_ISSUE where PROCESS_STATUS = 'P'";
OracleCommand cmd = new OracleCommand(sql, conn);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "CUX_TRANS_TYPE_ISSUE");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
int TYPE_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["TYPE_ID"]);
TRANSACTION_TYPE_ID = ds.Tables[0].Rows[i]["TRANSACTION_TYPE_ID"].ToString();
TRANSACTION_TYPE_NAME = ds.Tables[0].Rows[i]["TRANSACTION_TYPE_NAME"].ToString();
TRANSACTION_TYPE_DESC = ds.Tables[0].Rows[i]["TRANSACTION_TYPE_DESC"].ToString();
SOURCE_TYPE_ID = ds.Tables[0].Rows[i]["SOURCE_TYPE_ID"].ToString();
SOURCE_TYPE_NAME = ds.Tables[0].Rows[i]["SOURCE_TYPE_NAME"].ToString();
USER_DEFINED_FLAG = ds.Tables[0].Rows[i]["CREATED_BY"].ToString();
CREAT_DATE = ds.Tables[0].Rows[i]["CREAT_DATE"].ToString();
PROCESS_MESSAGE = ds.Tables[0].Rows[i]["PROCESS_MESSAGE"].ToString();
string insertsql = "insert into CUX_TRANS_TYPE_ISSUE(TRANSACTION_TYPE_ID,TRANSACTION_TYPE_NAME,TRANSACTION_TYPE_DESC,SOURCE_TYPE_ID,SOURCE_TYPE_NAME,USER_DEFINED_FLAG,CREAT_DATE,PROCESS_MESSAGE)values('" + TRANSACTION_TYPE_ID + "','" + TRANSACTION_TYPE_NAME + "','" + TRANSACTION_TYPE_DESC + "','" + SOURCE_TYPE_ID + "','" + SOURCE_TYPE_NAME + "','" + USER_DEFINED_FLAG + "','" + CREAT_DATE + "','" + PROCESS_MESSAGE + "')";
SqlCommand cmd2 = new SqlCommand(insertsql, con);
if (cmd2.ExecuteNonQuery() > 0)
{
string updatesql = "update CUX_TRANS_TYPE_ISSUE set PROCESS_STATUS ='Y' where TRANSACTION_TYPE_ID ='" + TRANSACTION_TYPE_ID + "' and TYPE_ID ='" + TYPE_ID + "'";
OracleCommand cmd3 = new OracleCommand(updatesql, conn);
int result = cmd3.ExecuteNonQuery();
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("搬运单事务处理类型同步接口同步异常：" + ex.Message);
}
}
//人员信息同步WMS（新增）（SYNCHRONIZATIONOFPERSONNELINFORMATION）
//数据流向：ERP WMS
//流程：ERP将组织好的人员信息，自动同步到中间表，WMS读取中间表数据存放在WMS本地。
//目的：确定单据的操作人。
#region
//public void SynchronizationOfPersonnelInformation()
//{
// OracleConnection conn = null;
// SqlConnection con = null;
// try
// {
// int USER_ID; string USER_NAME; string STATUS; string TRANSACTION_TYPE_DESC;
// string SOURCE_TYPE_ID; string CREAT_DATE; string PROCESS_MESSAGE;
// conn = OpenConn();//中间库
// con = SqlOpen();//WMS
// string sql = "select * from SYNCHRONIZATIONOFPERSONNELINFORMATION where PROCESS_STATUS = 'P'";
// OracleCommand cmd = new OracleCommand(sql, conn);
// OracleDataAdapter da = new OracleDataAdapter(cmd);
// DataTable ds = new DataTable();
// da.Fill(ds);
// for (int i = 0; i < ds.Rows.Count; i++)
// {
// int ISSUE_ID = Convert.ToInt32(ds.Rows[i]["ISSUE_ID"]);
// USER_ID = Convert.ToInt32(ds.Rows[i]["USER_ID"]);
// USER_NAME = ds.Rows[i]["USER_NAME"].ToString();
// STATUS = ds.Rows[i]["STATUS"].ToString();
// TRANSACTION_TYPE_DESC = ds.Rows[i]["TRANSACTION_TYPE_DESC"].ToString();
// SOURCE_TYPE_ID = ds.Rows[i]["SOURCE_TYPE_ID"].ToString();
// CREAT_DATE = ds.Rows[i]["CREAT_DATE"].ToString();
// PROCESS_MESSAGE = ds.Rows[i]["PROCESS_MESSAGE"].ToString();
// string insertsql = "insert into SYNCHRONIZATIONOFPERSONNELINFORMATION(USER_ID,USER_NAME,STATUS,TRANSACTION_TYPE_DESC,SOURCE_TYPE_ID,CREAT_DATE,PROCESS_MESSAGE)values('" + USER_ID + "','" + USER_NAME + "','" + STATUS + "','" + TRANSACTION_TYPE_DESC + "','" + SOURCE_TYPE_ID + "','" + CREAT_DATE + "','" + PROCESS_MESSAGE + "')";
// SqlCommand cmd2 = new SqlCommand(insertsql, con);
// if (cmd2.ExecuteNonQuery() > 0)
// {
// string updatesql = "update SYNCHRONIZATIONOFPERSONNELINFORMATION set PROCESS_STATUS ='Y' where TRANSACTION USER_ID ='" + USER_ID + "' and ISSUE_ID ='" + ISSUE_ID + "'";
// OracleCommand cmd3 = new OracleCommand(updatesql, conn);
// int result = cmd3.ExecuteNonQuery();
// }
// }
// con.Close();
// conn.Close();
// }
// catch (Exception ex)
// {
// MessageBox.Show("人员信息同步WMS接口同步异常：" + ex.Message);
// }
//}
#endregion
public void SynchronizationOfPersonnelInformation()
{
OracleConnection conn = null;
SqlConnection con = null;
try
{
string USER_ID; string USER_NAME; string STATUS; string TRANSACTION_TYPE_DESC;
string SOURCE_TYPE_ID; string CREAT_DATE; string PROCESS_MESSAGE;
conn = OpenConn();//中间库
con = SqlOpen();//WMS
string sql = "select ISSUE_ID, cast(USER_ID as varchar(300)) as USER_ID,USER_NAME,person_id,last_name,cast(start_date as varchar(100)) as start_date,cast(end_date as varchar(100)) as end_date,STATUS,process_status,process_message,cast(creation_date as varchar(100)) as creation_date ,created_by,last_updated_by,cast(last_update_date as varchar(100)) as last_update_date,last_update_login,CREAT_DATE,PROCESS_MESSAGE from CUX_USER_DATA_ISSUE where PROCESS_STATUS = 'P'";
OracleCommand cmd = new OracleCommand(sql, conn);
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "CUX_USER_DATA_ISSUE");
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
int ISSUE_ID = Convert.ToInt32(ds.Tables[0].Rows[i]["ISSUE_ID"]);
USER_ID = ds.Tables[0].Rows[i]["USER_ID"].ToString();
USER_NAME = ds.Tables[0].Rows[i]["USER_NAME"].ToString();
STATUS = ds.Tables[0].Rows[i]["STATUS"].ToString();
TRANSACTION_TYPE_DESC = ds.Tables[0].Rows[i]["LAST_NAME"].ToString();
SOURCE_TYPE_ID = ds.Tables[0].Rows[i]["CREATED_BY"].ToString();
CREAT_DATE = ds.Tables[0].Rows[i]["CREAT_DATE"].ToString();
PROCESS_MESSAGE = ds.Tables[0].Rows[i]["PROCESS_MESSAGE"].ToString();
string insertsql = "insert into CUX_USER_DATA_ISSUE(USER_ID,USER_NAME,STATUS,TRANSACTION_TYPE_DESC,SOURCE_TYPE_ID,CREAT_DATE,PROCESS_MESSAGE)values('" + USER_ID + "','" + USER_NAME + "','" + STATUS + "','" + TRANSACTION_TYPE_DESC + "','" + SOURCE_TYPE_ID + "','" + CREAT_DATE + "','" + PROCESS_MESSAGE + "')";
SqlCommand cmd2 = new SqlCommand(insertsql, con);
if (cmd2.ExecuteNonQuery() > 0)
{
string updatesql = "update CUX_USER_DATA_ISSUE set PROCESS_STATUS ='Y' where USER_ID ='" + USER_ID + "' and ISSUE_ID ='" + ISSUE_ID + "'";
OracleCommand cmd3 = new OracleCommand(updatesql, conn);
int result = cmd3.ExecuteNonQuery();
}
}
con.Close();
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show("人员信息同步WMS接口同步异常：" + ex.Message);
}
}
private void button1_Click(object sender, EventArgs e)
{
System.Timers.Timer timer = new System.Timers.Timer(2000);//设定计时器，100秒进行一次循环
timer.Elapsed += new System.Timers.ElapsedEventHandler(Start);
timer.AutoReset = true;//执行多次--false执行一次
timer.Enabled = true;//执行事件为true,定时器启动
textBox1.Text = "WMS、中间库连接正常！";
}
public void Start(object sender, ElapsedEventArgs e)
{
BasicInformationSynchronization();
// AccountAliasSynchronization();
//HeaderTableForPurchasingInboundDocuments();
//PurchaseInboundDocumentFeedbackLineTable();
//InventoryOrganizationSynchronization();
//MoveOrderTransactionTypeSynchronization();
//SynchronizationOfPersonnelInformation();
}
private void button2_Click(object sender, EventArgs e)
{
System.Timers.Timer timer2 = new System.Timers.Timer(2000);//设定计时器，100秒进行一次循环
timer2.Elapsed += new System.Timers.ElapsedEventHandler(Start2);
timer2.AutoReset = true;//执行多次--false执行一次
timer2.Enabled = true;//执行事件为true,定时器启动
}
public void Start2(object sender, ElapsedEventArgs e)
{
PurchaseInboundDocumentFeedbackHeaderTable();
PurchaseInboundDocumentFeedbackHeaderTable();
OutboundSingleHeaderTable();
OutboundSingleLineTable();
OutboundSingleFeedbackHeaderTable();
OutboundSingleFeedbackLineTable();
PurchaseTeturnHeaderTable();
PurchaseTeturnLineTable();
ReturnOfTheHandlingOrder();
}
private void button3_Click(object sender, EventArgs e)
{
System.Environment.Exit(0);
}
private void button4_Click(object sender, EventArgs e)
{
//BasicInformationSynchronization();
//InventoryOrganizationSynchronization();
//SynchronizationOfPersonnelInformation();
//MoveOrderTransactionTypeSynchronization();
//AccountAliasSynchronization();
//HeaderTableForPurchasingInboundDocuments();
// AccountAliasSynchronization();
//IssueTableForPurchasingInboundDocuments();
//PurchaseInboundDocumentFeedbackHeaderTable();
//PurchaseInboundDocumentFeedbackLineTable();
//PurchaseTeturnHeaderTable();
//PurchaseTeturnLineTable();
//OutboundSingleHeaderTable();
//OutboundSingleLineTable();
OutboundSingleFeedbackHeaderTable();
}
}
5023
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
