private int GetOverDays(string serialNumber, int goodsID, out decimal goodsPrice, out int stockCount)
{
//计算距离过期时间
GoodsBaseInfo bInfo = new GoodsBaseInfo();
bInfo.SerialNumber = serialNumber;
OQL q = OQL.From(bInfo)
.Select(bInfo.CanUserMonth)
.Where(bInfo.SerialNumber)
.END;
bInfo = EntityQuery <GoodsBaseInfo> .QueryObject(q);
int canUseMonth = bInfo.CanUserMonth;
GoodsStock stock = new GoodsStock();
stock.GoodsID = goodsID;
EntityQuery <GoodsStock> .Fill(stock);
goodsPrice = stock.GoodsPrice;
stockCount = stock.Stocks;
//距离过期时间
int overDays = canUseMonth * 30 - DateTime.Now.Subtract(stock.MakeOnDate).Days;
return(overDays);
}
/// <summary>
/// 获取商品销售价格信息
/// </summary>
/// <returns></returns>
public IEnumerable <GoodsSaleInfoVM> GetGoodsSaleInfo()
{
GoodsBaseInfo bInfo = new GoodsBaseInfo();
GoodsStock stock = new GoodsStock();
OQL joinQ = OQL.From(bInfo)
.Join(stock).On(bInfo.SerialNumber, stock.SerialNumber)
.Select(bInfo.GoodsName, bInfo.Manufacturer, bInfo.SerialNumber, stock.GoodsPrice, stock.MakeOnDate, bInfo.CanUserMonth, stock.Stocks, stock.GoodsID)
.OrderBy(bInfo.GoodsName, "asc")
.END;
PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper();
EntityContainer ec = new EntityContainer(joinQ, db);
ec.Execute();
var result = ec.Map <GoodsSaleInfoVM>(e =>
{
e.GoodsName = ec.GetItemValue <string>(0);
e.Manufacturer = ec.GetItemValue <string>(1);
e.SerialNumber = ec.GetItemValue <string>(2);
e.GoodsPrice = ec.GetItemValue <decimal>(3);
e.MakeOnDate = ec.GetItemValue <DateTime>(4);
e.CanUserMonth = ec.GetItemValue <int>(5);
e.Stocks = ec.GetItemValue <int>(6);
e.GoodsID = ec.GetItemValue <int>(7);
return(e);
}
);
return(result);
}
static void TestModel()
{
//http://www.cnblogs.com/assion/archive/2011/05/13/2045253.html
//我们创建几样商品
GoodsStock RedWine = new GoodsStock()
{
GoodsName = "红酒", GoodsPrice = 1800, GoodsNumber = 10
};
GoodsStock Condoms = new GoodsStock()
{
GoodsName = "安全套", GoodsPrice = 35, GoodsNumber = 10
};
//我们创建几位顾客
Customer Chunge = new Customer()
{
CustomerName = "春哥"
};
Customer Beianqi = new Customer()
{
CustomerName = "贝安琪"
};
Customer Noname = new Customer();
//有一台收银机
CashierRegisterMachines crManchines = new CashierRegisterMachines()
{
CashRegisterNo = "CR00011"
};
//当然，我们需要收银员啊
Cashier CashierMM = new Cashier(crManchines)
{
CashierName = "收银员MM", WorkNumber = "SYY10011"
};
//顾客开始排队结帐了
Queue <Customer> customerQueue = new Queue <Customer>();
customerQueue.Enqueue(Chunge);
customerQueue.Enqueue(Beianqi);
customerQueue.Enqueue(Noname);
//队伍过来，按先后顺序挨个收银喽
foreach (var customer in customerQueue)
{
//收银
CashierMM.CashRegister(customer);
}
}
/// <summary>
/// 获取所有的可售商品总数
/// </summary>
/// <returns></returns>
public int GetGoodsStockCount()
{
GoodsStock stock = new GoodsStock();
OQL q = new OQL(stock);
q.Select()
.Count(stock.Stocks, "库存数量")
.Where(q.Condition.AND(stock.Stocks, ">", 0));
stock = EntityQuery <GoodsStock> .QueryObject(q);
return(stock.Stocks);
}
static void TestBIZ()
{
//我们创建几样商品
GoodsStock RedWine = new GoodsStock()
{
GoodsName = "红酒", GoodsPrice = 1800, GoodsNumber = 10, SerialNumber = "J000111"
};
GoodsStock Condoms = new GoodsStock()
{
GoodsName = "安全套", GoodsPrice = 35, GoodsNumber = 10, SerialNumber = "T213000"
};
//我们创建几位顾客
Customer Chunge = new Customer()
{
CustomerName = "春哥"
};
Customer Beianqi = new Customer()
{
CustomerName = "贝安琪"
};
Customer Noname = new Customer();
//有一台收银机
CashierRegisterMachines crManchines = new CashierRegisterMachines()
{
CashRegisterNo = "CR00011"
};
//当然，我们需要收银员啊
Cashier CashierMM = new Cashier(crManchines)
{
CashierName = "收银员MM", WorkNumber = "SYY10011"
};
//顾客逛了一圈，选了自己想要的商品
Chunge.LikeBuy(RedWine.TakeOut(1));
Beianqi.LikeBuy(RedWine.TakeOut(1));
Beianqi.LikeBuy(Condoms.TakeOut(1));
Noname.LikeBuy(Condoms.TakeOut(2));
//调用收银业务类
CashierRegisterBIZ biz = new CashierRegisterBIZ(CashierMM, crManchines);
biz.AddQueue(Chunge);
biz.AddQueue(Beianqi);
biz.AddQueue(Noname);
biz.CashierRegister();
}
//该方法已经废弃，合并在 GetOverDays 中
private int GetStockCount(int goodsID)
{
GoodsStock stock = new GoodsStock();
stock.GoodsID = goodsID;
OQL q = new OQL(stock);
q.Select(stock.Stocks)
.Where(stock.GoodsID);
stock = EntityQuery <GoodsStock> .QueryObject(q);
int stockCount = stock.Stocks;
return(stockCount);
}
protected void btnDelete_Click(object sender, EventArgs e)
{
if (this.dlCHJLH.Text != "")
{
GoodsManageBIZ biz = new GoodsManageBIZ();
GoodsStock info = new GoodsStock()
{
GoodsID = int.Parse(this.dlCHJLH.Text)
};
biz.DeleteGoodsStock(info);
lblMsg.Text = "删除成功！";
MyWebForm.ClearIBData(this.Controls);
//重新绑定数据
this.ProPageToolBar1.ReBindResultData();
}
else
{
lblMsg.Text = "请先选择一条记录！";
}
}
/// <summary>
/// 删除商品存货信息
/// </summary>
/// <param name="info"></param>
/// <returns></returns>
public bool DeleteGoodsStock(GoodsStock info)
{
return(EntityQuery <GoodsStock> .Instance.Delete(info) > 0);
}
/// <summary>
/// 获取商品销售价格信息
/// </summary>
/// <returns></returns>
public IEnumerable <GoodsSaleInfoVM> GetGoodsSaleInfo()
{
GoodsBaseInfo bInfo = new GoodsBaseInfo();
GoodsStock stock = new GoodsStock();
/*
* Select采用指定详细实体类属性的方式：
* OQL joinQ = OQL.From(bInfo)
* .Join(stock).On(bInfo.SerialNumber, stock.SerialNumber)
* .Select(
* bInfo.GoodsName,
* bInfo.Manufacturer,
* bInfo.SerialNumber,
* stock.GoodsPrice,
* stock.MakeOnDate,
* bInfo.CanUserMonth,
* stock.Stocks,
* stock.GoodsID)
* .OrderBy(bInfo.GoodsName, "asc")
* .END;
*/
//Select 方法不指定具体要选择的实体类属性，可以推迟到EntityContainer类的MapToList 方法上指定
OQL joinQ = OQL.From(bInfo)
.Join(stock).On(bInfo.SerialNumber, stock.SerialNumber)
.Select()
.OrderBy(bInfo.SerialNumber, "asc").OrderBy(bInfo.GoodsName, "asc")
.END;
joinQ.Limit(3, 3);
PWMIS.DataProvider.Data.AdoHelper db = PWMIS.DataProvider.Adapter.MyDB.GetDBHelper();
EntityContainer ec = new EntityContainer(joinQ, db);
/*
* 如果OQL的Select方法指定了详细的实体类属性，那么映射结果，可以采取下面的方式：
* var result = ec.Map<GoodsSaleInfoVM>(e =>
* {
* e.GoodsName = ec.GetItemValue<string>(0);
* e.Manufacturer = ec.GetItemValue<string>(1);
* e.SerialNumber = ec.GetItemValue<string>(2);
* e.GoodsPrice = ec.GetItemValue<decimal>(3);
* e.MakeOnDate = ec.GetItemValue<DateTime>(4);
* e.CanUserMonth = ec.GetItemValue<int>(5);
* e.Stocks = ec.GetItemValue<int>(6);
* e.GoodsID = ec.GetItemValue<int>(7);
* return e;
* }
* );
*/
var result = ec.MapToList <GoodsSaleInfoVM>(() => new GoodsSaleInfoVM()
{
GoodsName = bInfo.GoodsName,
Manufacturer = bInfo.Manufacturer,
SerialNumber = bInfo.SerialNumber,
GoodsPrice = stock.GoodsPrice,
MakeOnDate = stock.MakeOnDate,
CanUserMonth = bInfo.CanUserMonth,
Stocks = stock.Stocks,
GoodsID = stock.GoodsID,
ExpireDate = stock.MakeOnDate.AddMonths(bInfo.CanUserMonth)
});
return(result);
}
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> 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> 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);
}
