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); } }