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