private Dictionary <string, GoodsStock> QueryAllGoodsReplenish(string st, string et)
{
Dictionary <string, GoodsStock> id2Stock = new Dictionary <string, GoodsStock>();
DataTable dt = ExecuteDataTable(@"
SELECT
goods_id,
sum(num) AS sum_num,
sum(cost_amount) as total_money
FROM
goods_inout
WHERE
(`type`='01' or `type`='10')
AND dt between @st and @et
GROUP BY
goods_id",
new MySqlParameter("@st", st),
new MySqlParameter("@et", et));
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string goods_id = dt.Rows[i]["goods_id"].ToString();
if (ID2goods_dict.ContainsKey(goods_id) && child2parent.ContainsKey(goods_id))
{
if (id2Stock.ContainsKey(child2parent[goods_id]))
{
id2Stock[child2parent[goods_id]].Num += Convert.ToDecimal(dt.Rows[i]["sum_num"]) / ID2goods_dict[goods_id].SplitNum;
id2Stock[child2parent[goods_id]].CostAmount += Convert.ToDecimal(dt.Rows[i]["total_money"]);
}
else
{
GoodsStock item = new GoodsStock();
item.SetValue(ID2goods_dict[child2parent[goods_id]]);
item.Num = Convert.ToDecimal(dt.Rows[i]["sum_num"]) / ID2goods_dict[goods_id].SplitNum;
item.CostAmount = Convert.ToDecimal(dt.Rows[i]["total_money"]);
id2Stock.Add(child2parent[goods_id], item);
}
continue;
}
if (ID2goods_dict.ContainsKey(goods_id))
{
if (id2Stock.ContainsKey(goods_id))
{
id2Stock[goods_id].Num += Convert.ToDecimal(dt.Rows[i]["sum_num"]);
id2Stock[goods_id].CostAmount += Convert.ToDecimal(dt.Rows[i]["total_money"]);
}
else
{
GoodsStock item = new GoodsStock();
item.SetValue(ID2goods_dict[goods_id]);
item.Num = Convert.ToDecimal(dt.Rows[i]["sum_num"]);
item.CostAmount = Convert.ToDecimal(dt.Rows[i]["total_money"]);
id2Stock.Add(goods_id, item);
}
}
}
}
return(id2Stock);
}
private Dictionary <string, GoodsStock> QueryAllGoodsSellInfoByTime(string st, string et)
{
Dictionary <string, GoodsStock> id2Stock = new Dictionary <string, GoodsStock>();
DataTable dt = ExecuteDataTable(@"
SELECT
sales_goods.goods_id,
sum(sales_goods.num) AS total_num,
sum(sales_goods.total_amount) AS total_money,
sum(sales_goods.cost_amount) AS cost_money
FROM
sales_goods
LEFT JOIN sales ON sales.order_id = sales_goods.order_id
WHERE
sales.dt BETWEEN @st AND @et
GROUP BY
goods_id",
new MySqlParameter("@st", st),
new MySqlParameter("@et", et));
if (dt == null && dt.Rows.Count <= 0)
{
return(null);
}
for (int i = 0; i < dt.Rows.Count; i++)
{
string goods_id = dt.Rows[i]["goods_id"].ToString();
if (ID2goods_dict.ContainsKey(goods_id) && child2parent.ContainsKey(goods_id))
{
if (id2Stock.ContainsKey(child2parent[goods_id]))
{
id2Stock[child2parent[goods_id]].Num += Convert.ToDecimal(dt.Rows[i]["total_num"]) / ID2goods_dict[goods_id].SplitNum;
id2Stock[child2parent[goods_id]].ActulSaleAmount += Convert.ToDecimal(dt.Rows[i]["total_money"]);
id2Stock[child2parent[goods_id]].CostAmount += Convert.ToDecimal(dt.Rows[i]["cost_money"]);
}
else
{
GoodsStock item = new GoodsStock();
item.SetValue(ID2goods_dict[child2parent[goods_id]]);
item.Num = Convert.ToDecimal(dt.Rows[i]["total_num"]) / ID2goods_dict[goods_id].SplitNum;
item.ActulSaleAmount = Convert.ToDecimal(dt.Rows[i]["total_money"]);
item.CostAmount += Convert.ToDecimal(dt.Rows[i]["cost_money"]);
id2Stock.Add(child2parent[goods_id], item);
}
continue;
}
if (ID2goods_dict.ContainsKey(goods_id))
{
if (id2Stock.ContainsKey(goods_id))
{
id2Stock[goods_id].Num += Convert.ToDecimal(dt.Rows[i]["total_num"]);
id2Stock[goods_id].ActulSaleAmount += Convert.ToDecimal(dt.Rows[i]["total_money"]);
id2Stock[goods_id].CostAmount += Convert.ToDecimal(dt.Rows[i]["cost_money"]);
}
else
{
GoodsStock item = new GoodsStock();
item.SetValue(ID2goods_dict[goods_id]);
item.Num = Convert.ToDecimal(dt.Rows[i]["total_num"]);
item.ActulSaleAmount = Convert.ToDecimal(dt.Rows[i]["total_money"]);
item.CostAmount += Convert.ToDecimal(dt.Rows[i]["cost_money"]);
id2Stock.Add(goods_id, item);
}
}
}
DataTable ret_dt = ExecuteDataTable(@"
SELECT
goods_return.goods_id,
sum(goods_return.num) AS total_num,
sum(goods_return.return_value) AS total_money,
sum(goods_return.cost_amount) AS cost_money
FROM
goods_return
LEFT JOIN sales_return ON sales_return.uuid = goods_return.return_id
WHERE
sales_return.dt BETWEEN @st AND @et
GROUP BY
goods_id",
new MySqlParameter("@st", st),
new MySqlParameter("@et", et));
if (ret_dt != null && ret_dt.Rows.Count > 0)
{
for (int i = 0; i < ret_dt.Rows.Count; i++)
{
string goods_id = ret_dt.Rows[i]["goods_id"].ToString();
if (child2parent.ContainsKey(goods_id) && id2Stock.ContainsKey(child2parent[goods_id])) // 子商品转换为父商品销售量
{
id2Stock[child2parent[goods_id]].Num -= Convert.ToDecimal(ret_dt.Rows[i]["total_num"]) / ID2goods_dict[goods_id].SplitNum;
id2Stock[child2parent[goods_id]].ActulSaleAmount -= Convert.ToDecimal(ret_dt.Rows[i]["total_money"]);
id2Stock[child2parent[goods_id]].CostAmount -= Convert.ToDecimal(ret_dt.Rows[i]["cost_money"]);
continue;
}
if (id2Stock.ContainsKey(goods_id))
{
id2Stock[goods_id].Num -= Convert.ToDecimal(ret_dt.Rows[i]["total_num"]);
id2Stock[goods_id].ActulSaleAmount -= Convert.ToDecimal(ret_dt.Rows[i]["total_money"]);
id2Stock[goods_id].CostAmount -= Convert.ToDecimal(ret_dt.Rows[i]["cost_money"]);
}
}
}
return(id2Stock);
}
private Dictionary <string, GoodsStock> QueryAllGoodsStockByTime(string t)
{
Dictionary <string, GoodsStock> id2Stock = new Dictionary <string, GoodsStock>();
DataTable dt = ExecuteDataTable(@"
SELECT
goods_id,
num,
cost_amount
FROM
goods");
if (dt != null && dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string goods_id = dt.Rows[i]["goods_id"].ToString();
if (ID2goods_dict.ContainsKey(goods_id) && child2parent.ContainsKey(goods_id)) // 关联商品 子商品切换回父商品
{
if (id2Stock.ContainsKey(child2parent[goods_id])) // 已经存在父商品
{
id2Stock[child2parent[goods_id]].Num += Convert.ToDecimal(dt.Rows[i]["num"]) / ID2goods_dict[goods_id].SplitNum;
id2Stock[child2parent[goods_id]].CostAmount += Convert.ToDecimal(dt.Rows[i]["cost_amount"]);
}
else
{
GoodsStock item = new GoodsStock();
item.SetValue(ID2goods_dict[child2parent[goods_id]]);
item.Num = Convert.ToDecimal(dt.Rows[i]["num"]) / ID2goods_dict[goods_id].SplitNum;
item.CostAmount += Convert.ToDecimal(dt.Rows[i]["cost_amount"]);
id2Stock.Add(child2parent[goods_id], item);
}
continue;
}
// 非关联商品 或 父商品
if (ID2goods_dict.ContainsKey(goods_id))
{
if (id2Stock.ContainsKey(goods_id))
{
id2Stock[goods_id].Num += Convert.ToDecimal(dt.Rows[i]["num"]);
id2Stock[goods_id].CostAmount += Convert.ToDecimal(dt.Rows[i]["cost_amount"]);
}
else
{
GoodsStock item = new GoodsStock();
item.SetValue(ID2goods_dict[goods_id]);
item.Num = Convert.ToDecimal(dt.Rows[i]["num"]);
item.CostAmount = Convert.ToDecimal(dt.Rows[i]["cost_amount"]);
id2Stock.Add(goods_id, item);
}
}
}
}
string now = DateTime.Now.ToLocalTime().ToString();
Dictionary <string, GoodsStock> breakageDict = QueryAllGoodsBreakage(t, now); // 报损
Dictionary <string, GoodsStock> callOutDict = QueryAllGoodsCallOut(t, now); // 调出
Dictionary <string, GoodsStock> callInDict = QueryAllGoodsCallIn(t, now); // 调入
Dictionary <string, GoodsStock> replenishDict = QueryAllGoodsReplenish(t, now); // 入库
Dictionary <string, GoodsStock> sellDict = QueryAllGoodsSellInfoByTime(t, now); // 销售
foreach (var kv in id2Stock)
{
if (breakageDict.ContainsKey(kv.Key))
{
kv.Value.Num += breakageDict[kv.Key].Num;
kv.Value.CostAmount += breakageDict[kv.Key].CostAmount;
}
if (callOutDict.ContainsKey(kv.Key))
{
kv.Value.Num += callOutDict[kv.Key].Num;
kv.Value.CostAmount += callOutDict[kv.Key].CostAmount;
}
if (callInDict.ContainsKey(kv.Key))
{
kv.Value.Num -= callInDict[kv.Key].Num;
kv.Value.CostAmount -= callInDict[kv.Key].CostAmount;
}
if (replenishDict.ContainsKey(kv.Key))
{
kv.Value.Num -= replenishDict[kv.Key].Num;
kv.Value.CostAmount -= replenishDict[kv.Key].CostAmount;
}
if (sellDict.ContainsKey(kv.Key))
{
kv.Value.Num += sellDict[kv.Key].Num;
kv.Value.CostAmount += sellDict[kv.Key].CostAmount;
}
}
return(id2Stock);
}
