/// <summary> /// 获得物料 成本单价 (移动平均法,出库) /// </summary> private double CalculateCostPriceOut(string matId, int matType, string storeHouseId) { SqlDBConnect db = new SqlDBConnect(); double costPrice = 0.0;//最终返回的 物料的 成本单价 string maxBalanceTime = StockStatusDAO.GetBalanceTime(); string strSql = "select LastCount,LastCost,BalanceTime from T_Stock_Status " + "where StoreHouseId='{0}' and MatId='{1}' and MatType={2} and BalanceTime='{3}'"; strSql = string.Format(strSql, storeHouseId, matId, matType, maxBalanceTime); DataTable dt = db.Get_Dt(strSql); double lastCost = 0.0; //取最近的一次结存的 成本单价 string BalanceTime = "190001"; //取最近的一次结存的 结存时间 if (dt != null && dt.Rows.Count > 0) { //取最近的一次结存的 成本单价 lastCost = Convert.ToDouble(dt.Rows[0]["LastCost"].ToString().Trim()); BalanceTime = dt.Rows[0]["BalanceTime"].ToString().Trim(); } string strSqlSel = "select T_Receipts_Det.ReceiptId,STaxPurchPrice,CurAveragePrice from T_Receipts_Det,T_Receipt_Main,T_ReceiptModal " + "where T_ReceiptModal.ReceTypeID=T_Receipt_Main.ReceiptTypeID and T_ReceiptModal.InOrOutBound='入库' and " + "T_Receipt_Main.SourceStoreH='{0}' and T_Receipt_Main.CurWorkMonth > '{1}' and " + "T_Receipt_Main.ReceiptId=T_Receipts_Det.ReceiptId and " + "T_Receipts_Det.MatId='{2}' and T_Receipts_Det.MatType={3}"; strSqlSel = string.Format(strSqlSel, storeHouseId, BalanceTime, matId, matType); DataTable dtDet = db.Get_Dt(strSqlSel); if (dtDet != null && dtDet.Rows.Count > 0) { costPrice = Convert.ToDouble(dtDet.Rows[dtDet.Rows.Count - 1]["CurAveragePrice"].ToString().Trim());//该仓库该物料该类型最后一次移动平均价 } else { costPrice = lastCost; } return(costPrice); }
private void button3_Click(object sender, EventArgs e) { DBUtil dbUtil = new DBUtil(); string strSql = "select T_Receipt_Main.*,T_Receipts_Det.* from T_Receipts_Det,T_Receipt_Main " + "where T_Receipt_Main.ReceiptId=T_Receipts_Det.ReceiptId"; DataTable dt = (new SqlDBConnect()).Get_Dt(strSql); if (dt == null || dt.Rows.Count <= 0) { return; } this.progressBar1.Maximum = dt.Rows.Count; for (int i = 0; i < dt.Rows.Count; i++) { List <string> sqls = new List <string>(); int numTem = 0; //记录事务处理中,未实际插入数据表的num double lastCostTem = 0.0; //记录未实际存入数据库的最后的成本单价 string curWorkMonth = dt.Rows[i]["CurWorkMonth"].ToString().Trim(); string occurTime = dt.Rows[i]["OccurTime"].ToString().Trim(); string strReceiptId = dt.Rows[i]["ReceiptId"].ToString().Trim(); //单据类型 string strReceTypeId = dt.Rows[i]["ReceiptTypeID"].ToString().Trim(); //单据类型 int matType = Convert.ToInt32(dt.Rows[i]["MatType"].ToString().Trim()); //物料类型 string matId = dt.Rows[i]["MatId"].ToString().Trim(); string SStorehouseId = dt.Rows[i]["SourceStoreH"].ToString().Trim(); //仓库 int num = Convert.ToInt32(dt.Rows[i]["num"].ToString().Trim()); double price = Convert.ToDouble(dt.Rows[i]["price"].ToString().Trim()); int orderNo = Convert.ToInt32(dt.Rows[i]["OrderNo"].ToString().Trim()); string InOrOutBound = dbUtil.Get_Single_val("T_ReceiptModal", "InOrOutBound", "ReceTypeID", strReceTypeId); if (strReceTypeId == "03") { #region 03单 if (matType == 0) //新机 { //(0新机)计算成本单价 double costPrice = CalculateCostPrice("03", matId, matType, SStorehouseId, num, price, ref numTem, ref lastCostTem); string strSqlUpdateCostPrice = "update T_Receipts_Det set STaxPurchPrice={0} where ReceiptId='{1}' and OrderNo={2}"; strSqlUpdateCostPrice = string.Format(strSqlUpdateCostPrice, costPrice, strReceiptId, orderNo); sqls.Add(strSqlUpdateCostPrice); numTem += num; //更新当前仓库、当前物料ID、当前类型的商品的CurAveragePrice(移动平均单价) string strSqlUpdateCurAverPrice = "update T_Receipts_Det set CurAveragePrice={0} from T_Receipt_Main,T_Receipts_Det " + "where T_Receipt_Main.SourceStoreH='{1}' and T_Receipt_Main.CurWorkMonth > '{2}' " + "and T_Receipt_Main.ReceiptId=T_Receipts_Det.ReceiptId and T_Receipts_Det.MatId='{3}' and T_Receipts_Det.MatType={4}"; strSqlUpdateCurAverPrice = string.Format(strSqlUpdateCurAverPrice, costPrice, SStorehouseId, StockStatusDAO.GetBalanceTime(), matId, matType); sqls.Add(strSqlUpdateCurAverPrice); } else { //(1旧机、2样机)无核销 计算成本单价 string strSqlUpdateCostPrice = "update T_Receipts_Det set STaxPurchPrice={0} where ReceiptId='{1}' and OrderNo={2}"; strSqlUpdateCostPrice = string.Format(strSqlUpdateCostPrice, price, strReceiptId, orderNo); sqls.Add(strSqlUpdateCostPrice); } //如果是03单,要同时更新 YnCompleteVerificate_03 字段 string strSqlUpdate_03 = "update T_Receipts_Det set YnCompleteVerificate_03='false' where ReceiptId='{0}' and OrderNo={1}"; strSqlUpdate_03 = string.Format(strSqlUpdate_03, strReceiptId, orderNo); sqls.Add(strSqlUpdate_03); #endregion } if (strReceTypeId == "01") { bool isFound = false; string strSqlSel = "select * from S90核销明细 where 进单类别='03' and 销单编号 in " + "(select 销单编号 from s90核销明细 where 销单类别='20' and 进单编号='{0}' and 进单分录号={1} and 进单类别='{2}' and 进单工作年月='{3}')"; strSqlSel = string.Format(strSqlSel, strReceiptId, orderNo, strReceTypeId, curWorkMonth); DataTable dataTable = (new SqlDBConnect()).Get_Dt(strSqlSel); if (dataTable != null && dataTable.Rows.Count > 0) { isFound = true; } if (isFound == false) //普通01单 { #region 普通01单 if (matType == 0) //新机 { //(0新机)计算成本单价 double costPrice = CalculateCostPrice("01", matId, matType, SStorehouseId, num, price, ref numTem, ref lastCostTem); string strSqlUpdateCostPrice = "update T_Receipts_Det set STaxPurchPrice={0} where ReceiptId='{1}' and OrderNo={2}"; strSqlUpdateCostPrice = string.Format(strSqlUpdateCostPrice, costPrice, strReceiptId, orderNo); sqls.Add(strSqlUpdateCostPrice); numTem += num; //更新当前仓库、当前物料ID、当前类型的商品的CurAveragePrice(移动平均单价) string strSqlUpdateCurAverPrice = "update T_Receipts_Det set CurAveragePrice={0} from T_Receipt_Main,T_Receipts_Det " + "where T_Receipt_Main.SourceStoreH='{1}' and T_Receipt_Main.CurWorkMonth > '{2}' " + "and T_Receipt_Main.ReceiptId=T_Receipts_Det.ReceiptId and T_Receipts_Det.MatId='{3}' and T_Receipts_Det.MatType={4}"; strSqlUpdateCurAverPrice = string.Format(strSqlUpdateCurAverPrice, costPrice, SStorehouseId, StockStatusDAO.GetBalanceTime(), matId, matType); sqls.Add(strSqlUpdateCurAverPrice); } else { //(1旧机、2样机)无核销 计算成本单价 string strSqlUpdateCostPrice = "update T_Receipts_Det set STaxPurchPrice={0} where ReceiptId='{1}' and OrderNo={2}"; strSqlUpdateCostPrice = string.Format(strSqlUpdateCostPrice, price, strReceiptId, orderNo); sqls.Add(strSqlUpdateCostPrice); } #endregion } else //为核销03单的01单 { #region //核销03单的01单 int num1 = 0; string receiptId20 = ""; string receiptId90 = ""; string receiptId03 = dataTable.Rows[i]["简单编号"].ToString().Trim(); int orderNo03 = Convert.ToInt32(dataTable.Rows[i]["进单分录号"].ToString().Trim()); string receiptTypeId03 = dataTable.Rows[i]["进单类别"].ToString().Trim(); string curWorkMonth03 = dataTable.Rows[i]["进单工作年月"].ToString().Trim(); int num03 = Convert.ToInt32(dataTable.Rows[i]["进货数量"].ToString().Trim()); double price03 = Convert.ToDouble(dataTable.Rows[i]["进货单价"].ToString().Trim()); //自动产生一个20单,一个90单 for (int j = 0; j < dataTable.Rows.Count - 1; j++) { num1++; //生成20单 if (num1 == 1) { //产生单据号,并返回更新"T_ReceiptRule"的SQL语句 string SqlUpdateBillRull20 = ""; receiptId20 = DBUtil.Produce_Bill_Id("20", DBUtil.getServerTime().ToString().Trim(), ref SqlUpdateBillRull20); string strSqlMain_20 = "insert into T_Receipt_Main(ReceiptId,CurWorkMonth,OccurTime,SourceStoreH,ReceiptTypeID) values('{0}','{1}','{2}','{3}','{4}')"; strSqlMain_20 = string.Format(strSqlMain_20, receiptId20, curWorkMonth, occurTime, SStorehouseId, "20"); sqls.Add(strSqlMain_20); sqls.Add(SqlUpdateBillRull20);//更新单据号 } string strSqlDet_20 = "insert into T_Receipts_Det(ReceiptId,OrderNo,MatId,MatType,price,num,ReceiptId20_03) values('{0}',{1},'{2}',{3},{4},{5},'{6}')"; strSqlDet_20 = string.Format(strSqlDet_20, receiptId20, orderNo03, matId, matType, price03, -num, receiptId03); sqls.Add(strSqlDet_20); //计算20单 物料成本 double costPrice = CalculateCostPrice("20", matId, matType, SStorehouseId, -num, price03, ref numTem, ref lastCostTem); string strSqlUpdateCostPrice = "update T_Receipts_Det set STaxPurchPrice={0} where ReceiptId='{1}' and OrderNo={2}"; strSqlUpdateCostPrice = string.Format(strSqlUpdateCostPrice, costPrice, receiptId20, orderNo03); sqls.Add(strSqlUpdateCostPrice); numTem += -num;///// //计算该01单 物料成本 double costPrice01 = CalculateCostPrice("01", matId, matType, SStorehouseId, num, price, ref numTem, ref lastCostTem); string strSqlUpdateCostPrice_01 = "update T_Receipts_Det set STaxPurchPrice={0} where ReceiptId='{1}' and OrderNo={2}"; strSqlUpdateCostPrice_01 = string.Format(strSqlUpdateCostPrice_01, costPrice01, strReceiptId, orderNo); sqls.Add(strSqlUpdateCostPrice_01); numTem += num;///// //生成90单 if (num1 == 1) //子表有多行,主表只插入一条记录 { //产生单据号,并返回更新"T_ReceiptRule"的SQL语句 string SqlUpdateBillRull90 = ""; receiptId90 = DBUtil.Produce_Bill_Id("90", DBUtil.getServerTime().ToString().Trim(), ref SqlUpdateBillRull90); string strSqlMain_90 = "insert into T_Receipt_Main(ReceiptId,CurWorkMonth,OccurTime,SourceStoreH,ReceiptTypeID) values('{0}','{1}','{2}','{3}','{4}')"; strSqlMain_90 = string.Format(strSqlMain_90, receiptId90, curWorkMonth, occurTime, SStorehouseId, "90"); sqls.Add(strSqlMain_90); sqls.Add(SqlUpdateBillRull90);//更新单据号 } string strSqlDet_90 = "insert into T_Receipts_Det(ReceiptId,OrderNo,MatId,MatType,price,num,ReceiptId90_03) values('{0}',{1},'{2}',{3},{4},{5},'{6}')"; strSqlDet_90 = string.Format(strSqlDet_90, receiptId90, orderNo03, matId, matType, price - price03, num, receiptId03); sqls.Add(strSqlDet_90); //计算90单 物料成本 double costPrice90 = CalculateCostPrice("90", matId, matType, SStorehouseId, num, price - price03, ref numTem, ref lastCostTem); string strSqlUpdateCostPrice_90 = "update T_Receipts_Det set STaxPurchPrice={0} where ReceiptId='{1}' and OrderNo={2}"; strSqlUpdateCostPrice_90 = string.Format(strSqlUpdateCostPrice_90, costPrice90, receiptId90, orderNo03); sqls.Add(strSqlUpdateCostPrice_90); //更新当前仓库、当前物料ID、当前类型的商品的CurAveragePrice(移动平均单价) string strSqlUpdateCurAverPrice = "update T_Receipts_Det set CurAveragePrice={0} from T_Receipt_Main,T_Receipts_Det " + "where T_Receipt_Main.SourceStoreH='{1}' and T_Receipt_Main.CurWorkMonth > '{2}' " + "and T_Receipt_Main.ReceiptId=T_Receipts_Det.ReceiptId and T_Receipts_Det.MatId='{3}' and T_Receipts_Det.MatType={4}"; strSqlUpdateCurAverPrice = string.Format(strSqlUpdateCurAverPrice, costPrice90, SStorehouseId, StockStatusDAO.GetBalanceTime(), matId, matType); sqls.Add(strSqlUpdateCurAverPrice); //更新03单的已核销数量 AlreadyVerificateNum_03 string strSqlUpdate_AlreadyVerificateNum_03 = "update T_Receipts_Det set AlreadyVerificateNum_03=AlreadyVerificateNum_03 + {0} where ReceiptId='{1}' and OrderNo={2}"; strSqlUpdate_AlreadyVerificateNum_03 = string.Format(strSqlUpdate_AlreadyVerificateNum_03, num, receiptId03, orderNo03); sqls.Add(strSqlUpdate_AlreadyVerificateNum_03); //如果该条物料核销完,更新相应03单的YnCompleteVerificate_03='true' if (num03 - num <= 0) { string strSqlUpdate_03 = "update T_Receipts_Det set YnCompleteVerificate_03='true' where ReceiptId='{0}' and OrderNo={1}"; strSqlUpdate_03 = string.Format(strSqlUpdate_03, receiptId03, orderNo03); sqls.Add(strSqlUpdate_03); } //更新01单子表该条记录的 ReceiptId01_03 string strSqlUpate_01 = "update T_Receipts_Det set ReceiptId01_03='{0}' where ReceiptId='{1}' and OrderNo={2}"; strSqlUpate_01 = string.Format(strSqlUpate_01, receiptId03, strReceiptId, orderNo); sqls.Add(strSqlUpate_01); } #endregion } } if (InOrOutBound == "出库") { double costPrice = CalculateCostPriceOut(matId, matType, SStorehouseId); //更新成本单价(为入库时的移动平均成本) string strSqlUpdateCostPrice = "update T_Receipts_Det set STaxPurchPrice={0} where ReceiptId='{1}' and OrderNo={2}"; strSqlUpdateCostPrice = string.Format(strSqlUpdateCostPrice, costPrice, strReceiptId, orderNo); sqls.Add(strSqlUpdateCostPrice); } //执行事务处理所有的Sql SqlDBConnect db = new SqlDBConnect(); db.Exec_Tansaction(sqls); this.progressBar1.Value += 1; } }
private void StockStatusOperate() { try { DateTime from_, to_; from_ = System.DateTime.Now; string balanceTime = this.textBoxStockMonth.Text.Trim(); if (balanceTime.Length != 6) { MessageBox.Show("请输入正确的结存年月(如:201108)!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } string maxBalanceTime = StockStatusDAO.GetBalanceTime(); if (string.Compare(balanceTime, maxBalanceTime) > 0) { MessageBox.Show("前面还有未结存的月份!", "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } SqlDBConnect db = new SqlDBConnect(); string strSql = "select distinct SourceStoreH, MatId, MatType from T_Receipts_Det,T_Receipt_Main " + "where CurWorkMonth = '{0}' and T_Receipts_Det.receiptId=T_Receipt_Main.receiptId and ReceiptTypeID != 'YS' and ReceiptTypeID != 'YF'"; strSql = string.Format(strSql, balanceTime); DataTable dt = db.Get_Dt(strSql); if (dt == null || dt.Rows.Count <= 0) { return; } this.Invoke((EventHandler) delegate { this.progressBar1.Value = 0; }); this.Invoke((EventHandler) delegate { this.progressBar1.Minimum = 0; }); this.Invoke((EventHandler) delegate { this.progressBar1.Maximum = dt.Rows.Count; }); List <string> sqls = new List <string>(); foreach (DataRow dr in dt.Rows) { double firstCount = 0; //期初数量 double firstMoney = 0.0; //期初成本金额 double stockInCount = 0; //收入数量 double stockInMoney = 0.0; double stockOutCount = 0; //发出数量 double stockOutMoney = 0.0; double lastCount = 0; //期末数量 double lastMoney = 0.0; //期末金额 double firstRoadCount = 0; //期初在途数量(未冲销03单) double firstRoadMoney = 0.0; double firstOutCount = 0; //期初发出数量(75、88数量) double firstOutMoney = 0.0; double lastRoadCount = 0; //期末在途数量(未冲销03单) double lastRoadMoney = 0.0; double lastOutCount = 0; //期末发出数量(75、88数量) double lastOutMoney = 0.0; double roadCount = 0; //本期在途数量 double roadMoney = 0.0; double outCount = 0; //本期发出数量 double outMoney = 0.0; string storeHouseId = dr["SourceStoreH"].ToString().Trim(); string matId = dr["MatId"].ToString().Trim(); int matType = Convert.ToInt32(dr["MatType"].ToString().Trim()); //入库 string sql_1 = "select sum(num) as num, sum(TTaxPurchPrice) as TTaxPurchPrice from T_Receipt_Main_Det " + "where CurWorkMonth = '{0}' and SourceStoreH='{1}' and MatId='{2}' and MatType={3} " + "and ReceiptTypeID < '51' and ReceiptTypeID != 'YS' and ReceiptTypeID != 'YF'"; sql_1 = string.Format(sql_1, balanceTime, storeHouseId, matId, matType); DataTable dt_1 = db.Get_Dt(sql_1); if (dt_1 != null && dt_1.Rows.Count > 0) { if (dt_1.Rows[0]["num"].ToString().Trim() != "") { stockInCount = Convert.ToDouble(dt_1.Rows[0]["num"].ToString().Trim()); } if (dt_1.Rows[0]["TTaxPurchPrice"].ToString().Trim() != "") { stockInMoney = Convert.ToDouble(dt_1.Rows[0]["TTaxPurchPrice"].ToString().Trim()); } } //出库 string sql_2 = "select sum(num) as num, sum(TTaxPurchPrice) as TTaxPurchPrice from T_Receipt_Main_Det " + "where CurWorkMonth = '{0}' and SourceStoreH='{1}' and MatId='{2}' and MatType={3} " + "and ReceiptTypeID >= '51' and ReceiptTypeID != 'YS' and ReceiptTypeID != 'YF'";// and ReceiptTypeID != '90'"; sql_2 = string.Format(sql_2, balanceTime, storeHouseId, matId, matType); DataTable dt_2 = db.Get_Dt(sql_2); if (dt_2 != null && dt_2.Rows.Count > 0) { if (dt_2.Rows[0]["num"].ToString().Trim() != "") { stockOutCount = Convert.ToDouble(dt_2.Rows[0]["num"].ToString().Trim()); } if (dt_2.Rows[0]["TTaxPurchPrice"].ToString().Trim() != "") { stockOutMoney = Convert.ToDouble(dt_2.Rows[0]["TTaxPurchPrice"].ToString().Trim());// } } //本期在途(未冲销03单) string sql_3 = "select sum(num) as num,sum(TTaxPurchPrice) as TTaxPurchPrice from T_Receipt_Main_Det " + "where CurWorkMonth = '{0}' and SourceStoreH='{1}' and MatId='{2}' and MatType={3} " + "and (ReceiptTypeID = '03' or ReceiptTypeID = '20')"; sql_3 = string.Format(sql_3, balanceTime, storeHouseId, matId, matType); DataTable dt_3 = db.Get_Dt(sql_3); if (dt_3 != null && dt_3.Rows.Count > 0) { if (dt_3.Rows[0]["num"].ToString().Trim() != "") { roadCount = Convert.ToDouble(dt_3.Rows[0]["num"].ToString().Trim()); } if (dt_3.Rows[0]["TTaxPurchPrice"].ToString().Trim() != "") { roadMoney = Convert.ToDouble(dt_3.Rows[0]["TTaxPurchPrice"].ToString().Trim());// } } //本期发出(75、88数量) string sql_4 = "select sum(num) as num,sum(TTaxPurchPrice) as TTaxPurchPrice from T_Receipt_Main_Det " + "where CurWorkMonth = '{0}' and SourceStoreH='{1}' and MatId='{2}' and MatType={3} " + "and (ReceiptTypeID = '75' or ReceiptTypeID = '88')"; sql_4 = string.Format(sql_4, balanceTime, storeHouseId, matId, matType); DataTable dt_4 = db.Get_Dt(sql_4); if (dt_4 != null && dt_4.Rows.Count > 0) { if (dt_4.Rows[0]["num"].ToString().Trim() != "") { outCount = Convert.ToDouble(dt_4.Rows[0]["num"].ToString().Trim()); } if (dt_4.Rows[0]["TTaxPurchPrice"].ToString().Trim() != "") { outMoney = Convert.ToDouble(dt_4.Rows[0]["TTaxPurchPrice"].ToString().Trim());// } } //期初 string strSql_ = "select FirstCount,FirstCostPrice,FirstMoney,FirstRoadCount,FirstRoadMoney,FirstOutCount,FirstOutMoney,BalanceTime from T_Stock_Status " + "where BalanceTime='{0}' and StoreHouseId='{1}' and MatId='{2}' and MatType={3} "; strSql_ = string.Format(strSql_, balanceTime, storeHouseId, matId, matType); DataTable dt_ = db.Get_Dt(strSql_); if (dt_ != null && dt_.Rows.Count > 0) { //取该物料本期期初的数量、成本单价、成本金额 firstCount = Convert.ToDouble(dt_.Rows[0]["FirstCount"].ToString().Trim()); firstMoney = Convert.ToDouble(dt_.Rows[0]["FirstMoney"].ToString().Trim()); firstRoadCount = Convert.ToDouble(dt_.Rows[0]["FirstRoadCount"].ToString().Trim()); firstRoadMoney = Convert.ToDouble(dt_.Rows[0]["FirstRoadMoney"].ToString().Trim()); firstOutCount = Convert.ToDouble(dt_.Rows[0]["FirstOutCount"].ToString().Trim()); firstOutMoney = Convert.ToDouble(dt_.Rows[0]["FirstOutMoney"].ToString().Trim()); lastCount = firstCount + stockInCount - stockOutCount; lastMoney = firstMoney + stockInMoney - stockOutMoney; lastRoadCount = firstRoadCount + roadCount; lastRoadMoney = firstRoadMoney + roadMoney; lastOutCount = firstOutCount + outCount; lastOutMoney = firstOutMoney + outMoney; string strSqlUpdate = "update T_Stock_Status set StockInCount={0},StockInMoney={1},StockOutCount={2},StockOutMoney={3}," + "LastCount={4},LastMoney={5},LastRoadCount={6},LastRoadMoney={7},LastOutCount={8},LastOutMoney={9} " + " where BalanceTime='{10}' and StoreHouseId='{11}' and MatId='{12}' and MatType={13} "; strSqlUpdate = string.Format(strSqlUpdate, stockInCount, stockInMoney, stockOutCount, stockOutMoney, lastCount, lastMoney, lastRoadCount, lastRoadMoney, lastOutCount, lastOutMoney, balanceTime, storeHouseId, matId, matType); sqls.Add(strSqlUpdate); } else //不存在,为本工作月新增商品 { lastCount = stockInCount - stockOutCount; lastMoney = stockInMoney - stockOutMoney; lastRoadCount = roadCount; lastRoadMoney = roadMoney; lastOutCount = outCount; lastOutMoney = outMoney; string strSqlInsert = "insert into T_Stock_Status(StoreHouseId,MatId,MatType,BalanceTime,FirstCount,FirstMoney,FirstRoadCount,FirstRoadMoney,FirstOutCount,FirstOutMoney," + "StockInCount,StockInMoney,StockOutCount,StockOutMoney,LastCount,LastMoney,LastRoadCount,LastRoadMoney,LastOutCount,LastOutMoney) " + "values('{0}','{1}',{2},'{3}',{4},{5},{6},{7},{8},{9},{10},{11},{12},{13},{14},{15},{16},{17},{18},{19}"; strSqlInsert = string.Format(strSqlInsert, storeHouseId, matId, matType, balanceTime, 0, 0, 0, 0, 0, 0, stockInCount, stockInMoney, stockOutCount, stockOutMoney, lastCount, lastMoney, lastRoadCount, lastRoadMoney, lastOutCount, lastOutMoney); sqls.Add(strSqlInsert); } this.Invoke((EventHandler) delegate { this.progressBar1.Value += 1; }); } //处理结存库中存在,但当月并未发生的物料 string sqlSS = "select T_Stock_Status.* from T_Stock_Status " + "where T_Stock_Status.StoreHouseId + T_Stock_Status.MatId + CONVERT(Nvarchar, T_Stock_Status.matType) not in " + "(select T_Receipt_Main_Det.SourceStoreH + T_Receipt_Main_Det.MatId + CONVERT(Nvarchar, T_Receipt_Main_Det.matType) " + "from T_Receipt_Main_Det where CurWorkMonth='{0}' and ReceiptTypeID != 'YS' and ReceiptTypeID != 'YF') " + "and BalanceTime = '{1}'"; sqlSS = string.Format(sqlSS, balanceTime, balanceTime); DataTable dtSS = db.Get_Dt(sqlSS); this.Invoke((EventHandler) delegate { this.progressBar1.Value = 0; }); this.Invoke((EventHandler) delegate { this.progressBar1.Minimum = 0; }); this.Invoke((EventHandler) delegate { this.progressBar1.Maximum = dtSS.Rows.Count; }); if (dtSS != null && dtSS.Rows.Count > 0) { foreach (DataRow dr in dtSS.Rows) { string storeHouseId = dr["StoreHouseId"].ToString().Trim(); string matId = dr["MatId"].ToString().Trim(); int matType = Convert.ToInt32(dr["MatType"].ToString().Trim()); double firstCount = Convert.ToDouble(dr["FirstCount"].ToString().Trim()); double firstMoney = Convert.ToDouble(dr["FirstMoney"].ToString().Trim()); double firstRoadCount = Convert.ToDouble(dr["FirstRoadCount"].ToString().Trim()); double firstRoadMoney = Convert.ToDouble(dr["FirstRoadMoney"].ToString().Trim()); double firstOutCount = Convert.ToDouble(dr["FirstOutCount"].ToString().Trim()); double firstOutMoney = Convert.ToDouble(dr["FirstOutMoney"].ToString().Trim()); string strSqlUpdate = "update T_Stock_Status set StockInCount={0},StockInMoney={1},StockOutCount={2},StockOutMoney={3}," + "LastCount={4},LastMoney={5},LastRoadCount={6},LastRoadMoney={7},LastOutCount={8},LastOutMoney={9} " + " where BalanceTime='{10}' and StoreHouseId='{11}' and MatId='{12}' and MatType={13}"; strSqlUpdate = string.Format(strSqlUpdate, 0, 0, 0, 0, firstCount, firstMoney, firstRoadCount, firstRoadMoney, firstOutCount, firstOutMoney, balanceTime, storeHouseId, matId, matType); sqls.Add(strSqlUpdate); this.Invoke((EventHandler) delegate { this.progressBar1.Value += 1; }); } } db.Exec_Tansaction(sqls); //执行 //插入下一个结存月的期初信息 List <string> sqlsIns = new List <string>(); string nextBalanceTime = Util.GetNextMonth(balanceTime);;//计算下一个月 string strSqlIns = "select * from T_Stock_Status where BalanceTime='{0}'"; strSqlIns = string.Format(strSqlIns, balanceTime); DataTable dtIns = db.Get_Dt(strSqlIns); this.Invoke((EventHandler) delegate { this.progressBar1.Value = 0; }); this.Invoke((EventHandler) delegate { this.progressBar1.Minimum = 0; }); this.Invoke((EventHandler) delegate { this.progressBar1.Maximum = dtIns.Rows.Count; }); for (int i = 0; i < dtIns.Rows.Count; i++) { string storeHouseId = dtIns.Rows[i]["StoreHouseId"].ToString().Trim(); string matId = dtIns.Rows[i]["MatId"].ToString().Trim(); int matType = Convert.ToInt32(dtIns.Rows[i]["MatType"].ToString().Trim()); double lastCount = Convert.ToDouble(dtIns.Rows[i]["LastCount"].ToString().Trim()); //期末数量 double lastMoney = Convert.ToDouble(dtIns.Rows[i]["LastMoney"].ToString().Trim()); //期末金额 double lastRoadCount = Convert.ToDouble(dtIns.Rows[i]["LastRoadCount"].ToString().Trim()); //期末在途数量(未冲销03单) double lastRoadMoney = Convert.ToDouble(dtIns.Rows[i]["LastRoadMoney"].ToString().Trim()); double lastOutCount = Convert.ToDouble(dtIns.Rows[i]["LastOutCount"].ToString().Trim()); //期末发出数量(75、88数量) double lastOutMoney = Convert.ToDouble(dtIns.Rows[i]["LastOutMoney"].ToString().Trim()); string sql = "insert into T_Stock_Status(StoreHouseId,MatId,MatType,BalanceTime,FirstCount,FirstMoney,FirstRoadCount,FirstRoadMoney,FirstOutCount,FirstOutMoney) " + "values('{0}','{1}',{2},'{3}',{4},{5},{6},{7},{8},{9})"; sql = string.Format(sql, storeHouseId, matId, matType, nextBalanceTime, lastCount, lastMoney, lastRoadCount, lastRoadMoney, lastOutCount, lastOutMoney); sqlsIns.Add(sql); this.Invoke((EventHandler) delegate { this.progressBar1.Value += 1; }); } db.Exec_Tansaction(sqlsIns); to_ = System.DateTime.Now; string strInf = "结存成功!\n开始时间:" + from_.ToShortTimeString() + "\n结束时间:" + to_.ToShortTimeString(); MessageBox.Show(strInf, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); this.Invoke((EventHandler) delegate { this.progressBar1.Value = 0; }); } catch (Exception ex) { MessageBox.Show(ex.ToString(), "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); return; } }
/// <summary> /// 获得某一仓库、某一物料、某一类型商品的库存 /// </summary> /// <param name="SStorehouseId"></param> /// <param name="matId"></param> /// <param name="matType"></param> /// <returns></returns> public static StockStatus GetStockNum(string SStorehouseId, string matId, int matType) { double stockNum = 0; //当前库存 double firstCount = 0; //期初数量 double stockInCount = 0; //收入数量 double stockOutCount = 0; //发出数量 SqlDBConnect db = new SqlDBConnect(); //查找最近一次结存的数量 string maxBalanceTime = StockStatusDAO.GetBalanceTime(); string strSql_ = "select FirstCount,FirstCostPrice,BalanceTime from T_Stock_Status " + "where StoreHouseId='{0}' and MatId='{1}' and MatType={2} and BalanceTime='{3}'"; strSql_ = string.Format(strSql_, SStorehouseId, matId, matType, maxBalanceTime); DataTable dt_ = db.Get_Dt(strSql_); if (dt_ != null && dt_.Rows.Count > 0) { firstCount = Convert.ToDouble(dt_.Rows[0]["FirstCount"].ToString().Trim()); } //入库 string sql_1 = "select sum(num) num from T_Receipt_Main_Det where SourceStoreH='{0}' and MatId='{1}' and MatType='{2}' and CurWorkMonth >= '{3}' " + "and ReceiptTypeID < '51' and ReceiptTypeID != 'YS' and ReceiptTypeID != 'YF'"; sql_1 = string.Format(sql_1, SStorehouseId, matId, matType, maxBalanceTime); DataTable dt_1 = db.Get_Dt(sql_1); if (dt_1 != null && dt_1.Rows.Count > 0) { if (dt_1.Rows[0]["num"].ToString().Trim() != "") { stockInCount = Convert.ToDouble(dt_1.Rows[0]["num"].ToString().Trim()); } } //出库 string sql_2 = "select sum(num) num from T_Receipt_Main_Det where SourceStoreH='{0}' and MatId='{1}' and MatType='{2}' and CurWorkMonth >= '{3}' " + "and ReceiptTypeID >= '51' and ReceiptTypeID <= '90'"; sql_2 = string.Format(sql_2, SStorehouseId, matId, matType, maxBalanceTime); DataTable dt_2 = db.Get_Dt(sql_2); if (dt_2 != null && dt_2.Rows.Count > 0) { if (dt_2.Rows[0]["num"].ToString().Trim() != "") { stockOutCount = Convert.ToDouble(dt_2.Rows[0]["num"].ToString().Trim()); } } stockNum = firstCount + stockInCount - stockOutCount; StockStatus stockStatus = new StockStatus(); stockStatus.firstCount = firstCount; stockStatus.stockInCount = stockInCount; stockStatus.stockOutCount = stockOutCount; stockStatus.stockNum = stockNum; return(stockStatus); }
private void StockStatusOperateForm_Load(object sender, EventArgs e) { string maxBalanceTime = StockStatusDAO.GetBalanceTime(); this.textBoxStockMonth.Text = this.curWorkMonth.Trim(); }