private bool UpdateStatus(string cGuid, string cOrderNumber)
{
var wmf = new WmsFunction(Properties.Settings.Default.SqlServer);
var cmd = new SqlCommand("update Wms_M_Order set cOrderNumber=@cOrderNumber,bUpdate=1 where cGuid=@cGuid");
cmd.Parameters.AddWithValue("@cGuid", cGuid);
cmd.Parameters.AddWithValue("@cOrderNumber", cOrderNumber);
return(wmf.ExecSqlCmd(cmd));
}
private DataTable GetDetail(string cTable, string cGuid)
{
var wmf = new WmsFunction(Properties.Settings.Default.SqlServer);
var cmd = new SqlCommand("select * from " + cTable + " where cGuid=@cGuid");
cmd.Parameters.AddWithValue("@cGuid", cGuid);
var dt = wmf.GetSqlTable(cmd);
return(dt);
}
private bool UpdateStatus(string cGuid, string cOrderNumber)
{
var wmf = new WmsFunction(Properties.Settings.Default.SqlServer);
var cmd = new SqlCommand("update Wms_M_Order set cOrderNumber=@cOrderNumber,bUpdate=1 where cGuid=@cGuid");
cmd.Parameters.AddWithValue("@cGuid", cGuid);
cmd.Parameters.AddWithValue("@cOrderNumber", cOrderNumber);
return wmf.ExecSqlCmd(cmd);
}
private DataTable GetDetail(string cTable, string cGuid)
{
var wmf = new WmsFunction(Properties.Settings.Default.SqlServer);
var cmd = new SqlCommand("select * from " + cTable + " where cGuid=@cGuid");
cmd.Parameters.AddWithValue("@cGuid", cGuid);
var dt = wmf.GetSqlTable(cmd);
return dt;
}
/// <summary>
/// 执行下载
/// </summary>
private void ExecUpload()
{
lblLastTime.Text = DateTime.Now.ToString(CultureInfo.CurrentCulture);
var stw = new Stopwatch();
stw.Start();
//如果没有任何未入库的单据,则直接退出
if (dsOrder.Wms_M_Order ==null|| dsOrder.Wms_M_Order.Rows.Count < 1)
return;
var iSumSucces = 0;
var iSumFail = 0;
#region for New Order
for (var iFor = 0; iFor < dsOrder.Wms_M_Order.Rows.Count; iFor++)
{
var cGuid = dsOrder.Wms_M_Order.Rows[iFor]["cGuid"].ToString();
//先判断子表是否有数据
var dtDetail = GetDetail(dsOrder.Wms_M_Order.Rows[iFor]["cTable"].ToString(), cGuid);
if (dtDetail == null || dtDetail.Rows.Count < 1)
continue;
var cType = dsOrder.Wms_M_Order.Rows[iFor]["cType"].ToString();
var billMainCmd = string.Empty;
var billEntrycmd = string.Empty;
//判断出入库类型
if (cType.Equals("成品入库"))
{
billMainCmd = "develop_StockBill";
billEntrycmd = "develop_StockBillEntry";
}
else if (cType.Equals("采购入库"))
{
billMainCmd = "develop_StockBill_Purchase";
billEntrycmd = "develop_StockBillEntry_Purchase";
}
else if (cType.Equals("销售出库"))
{
billMainCmd = "develop_StockBill_Delivery";
billEntrycmd = "develop_StockBillEntry_Delivery";
}
else if (cType.Equals("领料出库"))
{
billMainCmd = "develop_StockBill_Produce";
billEntrycmd = "develop_StockBillEntry_Produce";
}
if (string.IsNullOrEmpty(billMainCmd))
{
continue;
}
//执行写入入库单操作
//先插入入库单主表,获取入库单内码和入库单号
string fBillNo;
using (var con = new SqlConnection(Properties.Settings.Default.KisServer))
{
con.Open();
var tran = con.BeginTransaction();
using (var cmdMain = con.CreateCommand())
{
cmdMain.Transaction = tran;
cmdMain.CommandType = CommandType.StoredProcedure;
//如果主表写入成功,则返回FBillNo和FinterID
int finterId;
//先获取最大ID号
cmdMain.CommandText = "GetICMaxNum";
cmdMain.Parameters.AddWithValue("@TableName", "ICStockBill");
cmdMain.Parameters.Add(new SqlParameter("@FInterID", SqlDbType.Int, 32));
cmdMain.Parameters["@FInterID"].Direction = ParameterDirection.Output;
cmdMain.Parameters.AddWithValue("@Increment", 1);
cmdMain.Parameters.AddWithValue("@UserID", 16422);
try
{
cmdMain.ExecuteNonQuery();
finterId = (int)cmdMain.Parameters["@FInterID"].Value;
}
catch (Exception ex)
{
VLogError(DateTime.Now + " GetICMaxNum", ex.Message);
tran.Rollback();
return;
}
if (finterId < 1)
{
//如果返回id不正确,回滚
tran.Rollback();
return;
}
cmdMain.CommandText = billEntrycmd;
//写入入库单子表
for (var i = 0; i < dtDetail.Rows.Count; i++)
{
//先清除传入的参数
cmdMain.Parameters.Clear();
cmdMain.Parameters.AddWithValue("@FInterID", finterId);
cmdMain.Parameters.AddWithValue("@FBrNo", 0);
cmdMain.Parameters.AddWithValue("@FItemID", dtDetail.Rows[i]["FitemID"]);
cmdMain.Parameters.AddWithValue("@FEntryID", i + 1);
cmdMain.Parameters.AddWithValue("@FQty", dtDetail.Rows[i]["iQuantity"]);
cmdMain.Parameters.AddWithValue("@FBatchNo", dtDetail.Rows[i]["cLotNo"]);
cmdMain.Parameters.AddWithValue("@Fnote",
"时间" + DateTime.Now.ToString(CultureInfo.CurrentCulture));
cmdMain.Parameters.AddWithValue("@FSPNumber", dtDetail.Rows[i]["FSPNumber"]);
if (cType.Equals("采购入库"))
{
cmdMain.Parameters.AddWithValue("@FSourceEntryID", dtDetail.Rows[i]["FEntryID"]);
cmdMain.Parameters.AddWithValue("@cOrderNumber", dtDetail.Rows[i]["cOrderNumber"]);
}
try
{
cmdMain.ExecuteNonQuery();
}
catch (Exception ex)
{
VLogError(DateTime.Now+"子表",ex.Message);
//插入子表有任何异常,回滚
tran.Rollback();
return;
}
}
cmdMain.CommandText = billMainCmd;
cmdMain.Parameters.Clear();
cmdMain.Parameters.AddWithValue("@FInterID", finterId);
cmdMain.Parameters.Add(new SqlParameter("@FBillNo", SqlDbType.NVarChar, 255));
cmdMain.Parameters["@FBillNo"].Direction = ParameterDirection.Output;
if (cType.Equals("采购入库"))
{
cmdMain.Parameters.AddWithValue("@FPONumber", dtDetail.Rows[0]["cOrderNumber"]);
}
if (cType.Equals("领料出库"))
{
cmdMain.Parameters.AddWithValue("@cCode", dtDetail.Rows[0]["cCode"]);
}
if (cType.Equals("销售出库"))
{
cmdMain.Parameters.AddWithValue("@cCode", dtDetail.Rows[0]["cCode"]);
}
try
{
cmdMain.ExecuteNonQuery();
fBillNo = cmdMain.Parameters["@FBillNo"].Value.ToString();
}
catch (Exception ex)
{
VLogError(DateTime.Now + "主表", ex.Message);
//如果执行失败,回滚
tran.Rollback();
return;
}
try
{
if (UpdateStatus(cGuid, fBillNo))
{
dsOrder.Wms_M_Order.Rows[iFor]["bUpdate"] = 1;
tran.Commit();
iSumSucces =iSumSucces+ 1;
}
else
{
tran.Rollback();
}
}
catch (Exception ex)
{
VLogError(DateTime.Now + "提交", ex.Message);
tran.Rollback();
return;
}
}
}
}
#endregion
stw.Stop();
//显示执行一次用时的时间
lblCostTime.Text = stw.Elapsed.Milliseconds.ToString(CultureInfo.InvariantCulture) + @"毫秒";
var sfun = new WmsFunction(Properties.Settings.Default.SqlServer);
using (var lcmd = new SqlCommand("AddLogAction"))
{
lcmd.CommandType= CommandType.StoredProcedure;
lcmd.Parameters.AddWithValue("@cFunction", "执行同步");
lcmd.Parameters.AddWithValue("@cDescription", "此次成功同步数:" + iSumSucces + "失败数量:" + iSumFail + "开始时间:"
+ lblLastTime.Text + "用时:" + lblCostTime.Text);
sfun.Sqlexcuate(lcmd);
}
}
/// <summary>
/// 执行下载
/// </summary>
private void ExecUpload()
{
lblLastTime.Text = DateTime.Now.ToString(CultureInfo.CurrentCulture);
var stw = new Stopwatch();
stw.Start();
//如果没有任何未入库的单据,则直接退出
if (dsOrder.Wms_M_Order == null || dsOrder.Wms_M_Order.Rows.Count < 1)
{
return;
}
var iSumSucces = 0;
var iSumFail = 0;
#region for New Order
for (var iFor = 0; iFor < dsOrder.Wms_M_Order.Rows.Count; iFor++)
{
var cGuid = dsOrder.Wms_M_Order.Rows[iFor]["cGuid"].ToString();
//先判断子表是否有数据
var dtDetail = GetDetail(dsOrder.Wms_M_Order.Rows[iFor]["cTable"].ToString(), cGuid);
if (dtDetail == null || dtDetail.Rows.Count < 1)
{
continue;
}
var cType = dsOrder.Wms_M_Order.Rows[iFor]["cType"].ToString();
var billMainCmd = string.Empty;
var billEntrycmd = string.Empty;
//判断出入库类型
if (cType.Equals("成品入库"))
{
billMainCmd = "develop_StockBill";
billEntrycmd = "develop_StockBillEntry";
}
else if (cType.Equals("采购入库"))
{
billMainCmd = "develop_StockBill_Purchase";
billEntrycmd = "develop_StockBillEntry_Purchase";
}
else if (cType.Equals("销售出库"))
{
billMainCmd = "develop_StockBill_Delivery";
billEntrycmd = "develop_StockBillEntry_Delivery";
}
else if (cType.Equals("领料出库"))
{
billMainCmd = "develop_StockBill_Produce";
billEntrycmd = "develop_StockBillEntry_Produce";
}
if (string.IsNullOrEmpty(billMainCmd))
{
continue;
}
//执行写入入库单操作
//先插入入库单主表,获取入库单内码和入库单号
string fBillNo;
using (var con = new SqlConnection(Properties.Settings.Default.KisServer))
{
con.Open();
var tran = con.BeginTransaction();
using (var cmdMain = con.CreateCommand())
{
cmdMain.Transaction = tran;
cmdMain.CommandType = CommandType.StoredProcedure;
//如果主表写入成功,则返回FBillNo和FinterID
int finterId;
//先获取最大ID号
cmdMain.CommandText = "GetICMaxNum";
cmdMain.Parameters.AddWithValue("@TableName", "ICStockBill");
cmdMain.Parameters.Add(new SqlParameter("@FInterID", SqlDbType.Int, 32));
cmdMain.Parameters["@FInterID"].Direction = ParameterDirection.Output;
cmdMain.Parameters.AddWithValue("@Increment", 1);
cmdMain.Parameters.AddWithValue("@UserID", 16422);
try
{
cmdMain.ExecuteNonQuery();
finterId = (int)cmdMain.Parameters["@FInterID"].Value;
}
catch (Exception ex)
{
VLogError(DateTime.Now + " GetICMaxNum", ex.Message);
tran.Rollback();
return;
}
if (finterId < 1)
{
//如果返回id不正确,回滚
tran.Rollback();
return;
}
cmdMain.CommandText = billEntrycmd;
//写入入库单子表
for (var i = 0; i < dtDetail.Rows.Count; i++)
{
//先清除传入的参数
cmdMain.Parameters.Clear();
cmdMain.Parameters.AddWithValue("@FInterID", finterId);
cmdMain.Parameters.AddWithValue("@FBrNo", 0);
cmdMain.Parameters.AddWithValue("@FItemID", dtDetail.Rows[i]["FitemID"]);
cmdMain.Parameters.AddWithValue("@FEntryID", i + 1);
cmdMain.Parameters.AddWithValue("@FQty", dtDetail.Rows[i]["iQuantity"]);
cmdMain.Parameters.AddWithValue("@FBatchNo", dtDetail.Rows[i]["cLotNo"]);
cmdMain.Parameters.AddWithValue("@Fnote",
"时间" + DateTime.Now.ToString(CultureInfo.CurrentCulture));
cmdMain.Parameters.AddWithValue("@FSPNumber", dtDetail.Rows[i]["FSPNumber"]);
if (cType.Equals("采购入库"))
{
cmdMain.Parameters.AddWithValue("@FSourceEntryID", dtDetail.Rows[i]["FEntryID"]);
cmdMain.Parameters.AddWithValue("@cOrderNumber", dtDetail.Rows[i]["cOrderNumber"]);
}
try
{
cmdMain.ExecuteNonQuery();
}
catch (Exception ex)
{
VLogError(DateTime.Now + "子表", ex.Message);
//插入子表有任何异常,回滚
tran.Rollback();
return;
}
}
cmdMain.CommandText = billMainCmd;
cmdMain.Parameters.Clear();
cmdMain.Parameters.AddWithValue("@FInterID", finterId);
cmdMain.Parameters.Add(new SqlParameter("@FBillNo", SqlDbType.NVarChar, 255));
cmdMain.Parameters["@FBillNo"].Direction = ParameterDirection.Output;
if (cType.Equals("采购入库"))
{
cmdMain.Parameters.AddWithValue("@FPONumber", dtDetail.Rows[0]["cOrderNumber"]);
}
if (cType.Equals("领料出库"))
{
cmdMain.Parameters.AddWithValue("@cCode", dtDetail.Rows[0]["cCode"]);
}
if (cType.Equals("销售出库"))
{
cmdMain.Parameters.AddWithValue("@cCode", dtDetail.Rows[0]["cCode"]);
}
try
{
cmdMain.ExecuteNonQuery();
fBillNo = cmdMain.Parameters["@FBillNo"].Value.ToString();
}
catch (Exception ex)
{
VLogError(DateTime.Now + "主表", ex.Message);
//如果执行失败,回滚
tran.Rollback();
return;
}
try
{
if (UpdateStatus(cGuid, fBillNo))
{
dsOrder.Wms_M_Order.Rows[iFor]["bUpdate"] = 1;
tran.Commit();
iSumSucces = iSumSucces + 1;
}
else
{
tran.Rollback();
}
}
catch (Exception ex)
{
VLogError(DateTime.Now + "提交", ex.Message);
tran.Rollback();
return;
}
}
}
}
#endregion
stw.Stop();
//显示执行一次用时的时间
lblCostTime.Text = stw.Elapsed.Milliseconds.ToString(CultureInfo.InvariantCulture) + @"毫秒";
var sfun = new WmsFunction(Properties.Settings.Default.SqlServer);
using (var lcmd = new SqlCommand("AddLogAction"))
{
lcmd.CommandType = CommandType.StoredProcedure;
lcmd.Parameters.AddWithValue("@cFunction", "执行同步");
lcmd.Parameters.AddWithValue("@cDescription", "此次成功同步数:" + iSumSucces + "失败数量:" + iSumFail + "开始时间:"
+ lblLastTime.Text + "用时:" + lblCostTime.Text);
sfun.Sqlexcuate(lcmd);
}
}
