/// <summary>
/// 获取获取商品分类销售情况
/// </summary>
/// <param name="storeid">店铺ID</param>
public List <MiniAppStoreGoods> GetGoodTypeInCome(int storeid, string starttime, string endtime, ref int salesum)
{
string sql = $@"select *,sum(goodtypes.xprice) as salesum from (
select st.id,st.name,st.storeid,st.State,
(select sum(sc.price * sc.count) from storegoodscart sc left join storegoodsorder so on sc.goodsorderid = so.id where so.state = {(int)OrderState.已收货} and sc.goodsid = sg.id and sc.state = 1 and so.paydate>='{starttime}' and so.paydate<='{endtime}' ) xprice
from storegoodstype st left join storegoods sg on st.id = sg.TypeId
where st.storeid = {storeid} and st.state>-1
) goodtypes
group by goodtypes.id";
List <MiniAppStoreGoods> groupslist = new List <MiniAppStoreGoods>();
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql))
{
while (dr.Read())
{
MiniAppStoreGoods model = new MiniAppStoreGoods();
model.TypeId = Convert.ToInt32(dr["id"]);
model.StoreId = Convert.ToInt32(dr["storeid"]);
model.GoodsName = dr["name"].ToString();
if (DBNull.Value != dr["salesum"])
{
model.Price = Convert.ToInt32(dr["salesum"]);
salesum += model.Price;
}
groupslist.Add(model);
}
}
return(groupslist);
}
/// <summary>
/// 获取销售前十商品
/// </summary>
/// <param name="storeid">店铺ID</param>
/// <param name="odbtype">排序类型，1：销售类排序，0：销售额排序</param>
/// <param name="limit">获取数量</param>
/// <returns></returns>
public List <MiniAppStoreGoods> GetStoreGroupsDescData(int storeid, int odbtype, string starttime, string endtime, int limit = 10)
{
string orderby = "goods.xprice";
string wheresql = " goods.xprice>0";
string limitsql = " LIMIT " + limit;
if (odbtype == 1)
{
orderby = "goods.buynum";
wheresql = " goods.buynum>0";
}
if (limit == 0)
{
limitsql = "";
}
List <MiniAppStoreGoods> groupslist = new List <MiniAppStoreGoods>();
string sql = $@"select * from (SELECT gs.id,gs.groupname,gs.storeid,
(select sum(gu.buyprice) from groupuser gu where gu.groupid = gs.id and gu.state = {(int)MiniappPayState.已收货} and gu.recievegoodtime>='{starttime}' and gu.recievegoodtime<='{endtime}') xprice,
(select sum(gu.buynum) from groupuser gu where gu.groupid = gs.id and gu.state = {(int)MiniappPayState.已收货} and gu.recievegoodtime>='{starttime}' and gu.recievegoodtime<='{endtime}') buynum,1 as goodtype
from groups gs
where gs.storeid = {storeid}
UNION
select sg.id,sg.goodsname,sg.storeid,
(select sum(sc.price * sc.count) from storegoodscart sc left join storegoodsorder so on sc.goodsorderid = so.id where so.state = {(int)OrderState.已收货} and sc.goodsid = sg.id and sc.state = 1 and so.paydate>='{starttime}' and so.paydate<='{endtime}') xprice,
(select sum(sc.count) from storegoodscart sc left join storegoodsorder so on sc.goodsorderid = so.id where so.state = {(int)OrderState.已收货} and sc.goodsid = sg.id and sc.state = 1 and so.paydate>='{starttime}' and so.paydate<='{endtime}') buynum,2 as goodtype
from storegoods sg
where sg.storeid = {storeid}
UNION
select b.id,b.bname,storeid,
(select sum(CurrentPrice) from bargainuser bu where bu.BId=b.id and bu.state = 8 and ConfirmReceiveGoodsTime>='{starttime}' and ConfirmReceiveGoodsTime<='{endtime}') xprice,
(select count(*) from bargainuser bu where bu.BId=b.id and bu.state = 8 and ConfirmReceiveGoodsTime>='{starttime}' and ConfirmReceiveGoodsTime<='{endtime}') buynum,3 as goodtype
from bargain b
where b.StoreId={storeid}
) goods where {wheresql}
order by {orderby} DESC
{limitsql}";
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql))
{
while (dr.Read())
{
MiniAppStoreGoods model = new MiniAppStoreGoods();
model.Id = Convert.ToInt32(dr["id"]);
model.StoreId = Convert.ToInt32(dr["storeid"]);
model.GoodsName = dr["groupname"].ToString();
model.IsSell = Convert.ToInt32(dr["goodtype"]);
if (DBNull.Value != dr["xprice"])
{
model.Price = Convert.ToInt32(dr["xprice"]);
}
if (DBNull.Value != dr["buynum"])
{
model.salesCount = Convert.ToInt32(dr["buynum"]);
}
groupslist.Add(model);
}
}
return(groupslist);
}
