private GatherStorageRecordData buildGatherStorageRecordData(DataRow dr)
{
GatherStorageRecordData data = new GatherStorageRecordData();
data.ID = (string)dr["goods_id"];
data.MerchID = (string)dr["code"];
if (!string.IsNullOrEmpty(dr["ean"].ToString()))
{
data.Ean = (string)dr["ean"];
}
data.MerchName = (string)dr["name"];
data.Uint = (string)dr["units"];
data.Category = (string)dr["category_name"];
data.RealAmount = (float)dr["num"];
if (!string.IsNullOrEmpty(dr["product_parent_id"].ToString()))
{
data.ParentGoodsId = (string)dr["product_parent_id"];
}
else
{
data.ParentGoodsId = "";
}
if (!string.IsNullOrEmpty(dr["product_parent_id"].ToString()))
{
data.CountPerParent = (int)dr["product_num"];
}
else
{
data.CountPerParent = 0;
}
return(data);
}
public List <GatherStorageRecordData> QueryGoodsInOutSummary(string beginDT, string endDT, string category, string content, bool isAllGoods = false)
{
DataSet ds;
if (!string.IsNullOrEmpty(category) && category != "所有类型")
{
ds = ExecuteDataSet(@"select goods.goods_id,goods.code,goods.ean,goods.name,goods.units,goods.num,
goods_category.category_name,goods.product_parent_id,goods.product_num
from goods left join goods_category on goods.category = goods_category.id
where (goods.code like @code or goods.ean like @ean or goods.name like @name) and goods_category.id = @category order by goods.code",
new MySqlParameter("@category", category),
new MySqlParameter("@code", "%" + content + "%"),
new MySqlParameter("@ean", "%" + content + "%"),
new MySqlParameter("@name", "%" + content + "%")
);
}
else
{
ds = ExecuteDataSet(@"select goods.goods_id,goods.code,goods.ean,goods.name,goods.units,goods.num,
goods_category.category_name,goods.product_parent_id,goods.product_num
from goods left join goods_category on goods.category = goods_category.id
where (goods.code like @code or goods.ean like @ean or goods.name like @name) order by goods.code",
new MySqlParameter("@code", "%" + content + "%"),
new MySqlParameter("@ean", "%" + content + "%"),
new MySqlParameter("@name", "%" + content + "%")
);
}
Dictionary <string, GatherStorageRecordData> goodsDict = new Dictionary <string, GatherStorageRecordData>();
SortedSet <string> goods_set = new SortedSet <string>();
if (null != ds && ds.Tables.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; ++i)
{
DataRow dr = ds.Tables[0].Rows[i];
GatherStorageRecordData data = buildGatherStorageRecordData(dr);
goodsDict.Add(data.ID, data);
}
}
if (goodsDict.Count > 0)
{
string goods_ids_str = "";
foreach (string goods_id in goodsDict.Keys)
{
goods_ids_str += "'" + goods_id + "',";
}
goods_ids_str = goods_ids_str.Substring(0, goods_ids_str.Length - 1); // 去掉最后的逗号
ds = ExecuteDataSet(
@"select sales_goods.goods_id, sales_goods.num, sales_goods.total_amount
from sales_goods left join sales on sales_goods.order_id = sales.order_id
where goods_id in (" + goods_ids_str + @") and sales.dt between @begin and @end;
select exchange_record.goods_id, exchange_record.num from exchange_record
where goods_id in (" + goods_ids_str + @") and exchange_record.dt between @begin and @end;
select goods_return.goods_id, goods_return.num, goods_return.return_value
from goods_return left join sales_return on goods_return.return_id = sales_return.uuid
where goods_id in (" + goods_ids_str + @") and sales_return.dt between @begin and @end;
select goods_inout.goods_id, goods_inout.num from goods_inout
where goods_id in (" + goods_ids_str + @") and type = '07' and goods_inout.dt between @begin and @end;
select goods_inout.goods_id, goods_inout.num from goods_inout
where goods_id in (" + goods_ids_str + @") and type = '08' and goods_inout.dt between @begin and @end; ",
new MySqlParameter("@begin", beginDT),
new MySqlParameter("@end", endDT));
if (null != ds && ds.Tables.Count > 4)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; ++i)
{
DataRow dr = ds.Tables[0].Rows[i];
string goods_id = (string)dr["goods_id"];
if (goodsDict.ContainsKey(goods_id))
{
goodsDict[goods_id].ConsumeAmount += (float)dr["num"];
goodsDict[goods_id].RealConsPay += Convert.ToDecimal(dr["total_amount"]);
goods_set.Add(goods_id);
}
}
for (int i = 0; i < ds.Tables[1].Rows.Count; ++i)
{
DataRow dr = ds.Tables[1].Rows[i];
string goods_id = (string)dr["goods_id"];
if (goodsDict.ContainsKey(goods_id))
{
goodsDict[goods_id].ExchangeAmount += (int)dr["num"];
goods_set.Add(goods_id);
}
}
for (int i = 0; i < ds.Tables[2].Rows.Count; ++i)
{
DataRow dr = ds.Tables[2].Rows[i];
string goods_id = (string)dr["goods_id"];
if (goodsDict.ContainsKey(goods_id))
{
goodsDict[goods_id].ReturnAmount += (float)dr["num"];
goodsDict[goods_id].RealConsPay -= Convert.ToDecimal(dr["return_value"]);
goods_set.Add(goods_id);
}
}
for (int i = 0; i < ds.Tables[3].Rows.Count; ++i)
{
DataRow dr = ds.Tables[3].Rows[i];
string goods_id = (string)dr["goods_id"];
if (goodsDict.ContainsKey(goods_id))
{
goodsDict[goods_id].BreakDownAmount += -(float)dr["num"];
goods_set.Add(goods_id);
}
}
for (int i = 0; i < ds.Tables[4].Rows.Count; ++i)
{
DataRow dr = ds.Tables[4].Rows[i];
string goods_id = (string)dr["goods_id"];
if (goodsDict.ContainsKey(goods_id))
{
goodsDict[goods_id].ExchangeAmount += -(float)dr["num"];
goods_set.Add(goods_id);
}
}
}
}
if (isAllGoods)
{
return(goodsDict.Values.ToList());
}
else
{
List <GatherStorageRecordData> goods_list = new List <GatherStorageRecordData>();
foreach (var id in goods_set)
{
goods_list.Add(goodsDict[id]);
}
return(goods_list);
}
}
