/// <summary>
/// 获取已确认收货并未评价的购物车数据，每次获取前1000条
/// </summary>
/// <returns></returns>
public List <GroupUser> GetSuccessDataList(int iscomment = 0, int day = -15)
{
List <GroupUser> list = new List <GroupUser>();
string sqlwhere = "";
//1:已评论,0:未评论
if (iscomment >= 0)
{
sqlwhere = $" and gu.iscommentting={iscomment} ";
}
string sql = $"select gu.*,g.groupname,g.imgurl,s.appid aid from groupuser gu left join groups g on gu.groupid = g.id left join store s on g.storeid = s.id where gu.state={(int)MiniappPayState.已收货} and gu.recievegoodtime<='{DateTime.Now.AddDays(day)}' {sqlwhere} LIMIT 100";
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql, null))
{
while (dr.Read())
{
GroupUser amodel = base.GetModel(dr);
amodel.GroupName = dr["groupname"].ToString();
amodel.GroupImgUrl = dr["imgurl"].ToString();
if (dr["aid"] != DBNull.Value)
{
amodel.AId = Convert.ToInt32(dr["aid"]);
}
list.Add(amodel);
}
}
return(list);
}
public List <XcxAppAccountRelationGroupInfo> GetAgentTemplateInfonGroup(int agentId, ref int allCount)
{
List <Distribution> distributionList = DistributionBLL.SingleModel.GetList($"parentagentid={agentId}");
string sqlwhere = $"agentId={agentId}";
if (distributionList != null && distributionList.Count > 0)
{
string agentids = string.Join(",", distributionList.Select(x => x.AgentId).ToList());
sqlwhere = $"agentId in ({agentId},{agentids})";
}
string sql = $"select Count(*) count,TId,(select TName from xcxtemplate where id=r.TId) TName from xcxappaccountrelation r where {sqlwhere} group by TId";
List <XcxAppAccountRelationGroupInfo> miniappindexmodel = new List <XcxAppAccountRelationGroupInfo>();
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql, null))
{
while (dr.Read())
{
XcxAppAccountRelationGroupInfo model = new XcxAppAccountRelationGroupInfo();
model.TName = dr["TName"].ToString();
model.TId = Convert.ToInt32(string.IsNullOrEmpty(dr["TId"].ToString()) ? "0" : dr["TId"].ToString());
model.Count = Convert.ToInt32(string.IsNullOrEmpty(dr["Count"].ToString()) ? "0" : dr["Count"].ToString());
allCount += model.Count;
miniappindexmodel.Add(model);
}
}
return(miniappindexmodel);
}
/// <summary>
/// 获取用户开通模板最早时间列表
/// </summary>
/// <returns></returns>
public string GetListXcxappListSQL(string accountId)
{
string tids = "";
List <XcxAppAccountRelation> list = new List <XcxAppAccountRelation>();
string sql = $"select t.id tid,(select min(addtime) from xcxappaccountrelation where tid=t.id and accountid='{accountId}') addtime from xcxtemplate t where projecttype in ({(int)ProjectType.小程序},{(int)ProjectType.测试})";
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql, null))
{
list = GetList(dr);
}
if (list != null && list.Count > 0)
{
//获取用户对应模板更新日志，更新日志时间小于用户最早开通模板时间的不显示
foreach (XcxAppAccountRelation item in list)
{
if (item.AddTime.ToString("yyyy-MM-dd HH:mm:ss") != "0001-01-01 00:00:00")
{
tids += $" or ( type=1 and tid={item.TId} and updatetime>='{item.AddTime.ToString("yyyy-MM-dd HH:mm:ss")}') ";
}
}
}
return(tids);
}
/// <summary>
/// 获取电商销售前十普通商品
/// </summary>
/// <param name="aid"></param>
public List <StoreGoods> GetStoreGoodsDescData(int storeid)
{
List <StoreGoods> goodlist = new List <StoreGoods>();
string sql = $@"select sg.id,sg.storeid,sg.goodsname,
(select sum(sc.price * sc.count) from storegoodscart sc left
join storegoodsorder so on sc.goodsorderid = so.id
where so.state = 5 and sc.goodsid = sg.id and sc.state = 1) xprice
from storegoods sg
where sg.storeid = {storeid}
ORDER BY xprice DESC
LIMIT 10";
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql))
{
while (dr.Read())
{
StoreGoods model = new StoreGoods();
model.Id = Convert.ToInt32(dr["id"]);
model.StoreId = Convert.ToInt32(dr["storeid"]);
model.GoodsName = dr["goodsname"].ToString();
model.Price = Convert.ToInt32(dr["xprice"]);
goodlist.Add(model);
}
}
return(goodlist);
}
/// <summary>
/// 我的名片数据分析
/// </summary>
/// <param name="msgid"></param>
/// <param name="aid"></param>
/// <param name="datatype"></param>
/// <returns></returns>
public List <PlatUserFavoriteMsg> GetReportData(int msgid, int aid, int datatype, string starttime, string endtime)
{
List <PlatUserFavoriteMsg> list = new List <PlatUserFavoriteMsg>();
string sql = $"select count(*) count,actiontype from PlatUserFavoriteMsg where aid={aid} and msgid={msgid} and datatype={datatype} and state>=0 GROUP BY actiontype";
if (!string.IsNullOrEmpty(starttime))
{
sql += $" and addtime >='{starttime}'";
}
if (!string.IsNullOrEmpty(endtime))
{
sql += $" and addtime <'{endtime}'";
}
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql, null))
{
while (dr.Read())
{
if (dr["actiontype"] != DBNull.Value)
{
if (dr["count"] != DBNull.Value)
{
PlatUserFavoriteMsg tempmodel = new PlatUserFavoriteMsg();
tempmodel.ActionType = Convert.ToInt32(dr["actiontype"]);
tempmodel.Count = Convert.ToInt32(dr["count"]);
list.Add(tempmodel);
}
}
}
}
return(list);
}
public List <DistributionUserInfo> GetDistributionUserInfo(int did)
{
List <DistributionUserInfo> list = new List <DistributionUserInfo>();
string sql = $@"select ad.id,ad.parentagentid,ar.Percentage,ar.id ruleid, ar.name rulename,ar.nexpercentageid from(select * from AgentDistributionRelation where id = {did} or agentid = (select parentagentid from AgentDistributionRelation where id = {did})) ad
left join agentinfo a on a.id = ad.parentagentid
left join agentdistributionrule ar on ar.id = a.agentdistributionruleid";
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql, null))
{
while (dr.Read())
{
DistributionUserInfo duserinfo = new DistributionUserInfo();
if (dr["parentagentid"] != DBNull.Value)
{
duserinfo.AgentId = Convert.ToInt32(dr["parentagentid"]);
}
if (dr["Percentage"] != DBNull.Value)
{
duserinfo.Percentage = Convert.ToDouble(dr["Percentage"]);
}
if (dr["ruleid"] != DBNull.Value)
{
duserinfo.RuleId = Convert.ToInt32(dr["ruleid"]);
}
if (dr["id"] != DBNull.Value)
{
duserinfo.Id = Convert.ToInt32(dr["id"]);
}
if (dr["nexpercentageid"] != DBNull.Value)
{
duserinfo.NexPercentageId = Convert.ToInt32(dr["nexpercentageid"]);
if (did != duserinfo.Id)
{
if (duserinfo.NexPercentageId > 0)
{
AgentDistributionRule agentrule = AgentDistributionRuleBLL.SingleModel.GetModel(duserinfo.NexPercentageId);
if (agentrule != null)
{
duserinfo.Percentage = agentrule.Percentage;
}
}
else
{
duserinfo.Percentage = 0;
}
}
}
duserinfo.RuleName = dr["rulename"].ToString();
list.Add(duserinfo);
}
}
return(list);
}
public List <PlatMyCard> GetMyCardDataList(string appid, int aid, ref int count, string name = "", string phone = "", int pageSize = 10, int pageIndex = 1, string loginid = "", int storestate = 0)
{
List <PlatMyCard> list = new List <PlatMyCard>();
List <MySqlParameter> parms = new List <MySqlParameter>();
string sql = $"select mc.*,s.name storename from platmycard mc left join platstore s on mc.id=s.mycardid left join PlatStoreRelation sr on s.id = sr.StoreId";
string sqlcount = "select count(*) from platmycard mc left join platstore s on mc.id=s.mycardid left join PlatStoreRelation sr on s.id = sr.StoreId";
string sqlwhere = $" where mc.aid={aid} and mc.appid='{appid}'";
string sqlpage = $" limit {(pageIndex - 1) * pageSize},{pageSize}";
if (!string.IsNullOrEmpty(name))
{
sqlwhere += $" and mc.name like @name";
parms.Add(new MySqlParameter("@name", $"%{name}%"));
}
if (!string.IsNullOrEmpty(phone))
{
sqlwhere += $" and mc.phone like @phone ";
parms.Add(new MySqlParameter("@phone", $"%{phone}%"));
}
if (!string.IsNullOrEmpty(loginid))
{
sqlwhere += $" and mc.loginid like @loginid ";
parms.Add(new MySqlParameter("@loginid", $"%{loginid}%"));
}
if (storestate >= 0)
{
sqlwhere += $" and sr.State ={storestate}";
}
count = base.GetCountBySql(sqlcount + sqlwhere, parms.ToArray());
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql + sqlwhere + sqlpage, parms.ToArray()))
{
while (dr.Read())
{
PlatMyCard model = base.GetModel(dr);
model.StoreName = dr["storename"].ToString();
PlatPostUserMsgCountViewMolde msgmodel = PlatPostUserBLL.SingleModel.GetCountViewModel(model.Id);
if (msgmodel != null)
{
model.MsgCount = msgmodel.PostMsgCount;
}
list.Add(model);
}
}
return(list);
}
//查找订单列表
public List <AdminGoodsOrder> GetAdminList(string where, int pagesize, int pageindex, out int totalCount, string goodsName = "", string accName = "", string accPhone = "", string orderState = "", bool export = false)
{
List <AdminGoodsOrder> list = new List <AdminGoodsOrder>();
string sql;
string sqlCount;
sql = $@"select orders.Id,orders.OrderId,orders.BuyPrice,orders.UserId,orders.AccepterName as NickName,orders.AccepterTelePhone as TelePhone,orders.Message,orders.CreateDate,orders.State,orders.OrderNum,orders.Remark,orders.FreightPrice,orders.Address from miniappentgoodsorder orders {(string.IsNullOrEmpty(where) ? "" : " where " + where)} order by Id desc {(pagesize == 0 ? "" : " limit " + (pageindex <= 0 ? 0 : pageindex - 1) * pagesize + "," + pagesize)}";
//sql = $@"select orders.Id,orders.OrderId,orders.BuyPrice,orders.UserId,user.NickName,user.TelePhone,orders.Message,orders.CreateDate,orders.State,orders.OrderNum,orders.Remark,orders.FreightPrice,orders.Address from miniappgoodsorder orders inner join c_userinfo user on user.Id=orders.UserId {(string.IsNullOrEmpty(where) ? "" : " where " + where)} order by Id desc {(pagesize == 0 ? "" : " limit " + (pageindex <= 0 ? 0 : pageindex - 1) * pagesize + "," + pagesize)}";
if (export)//导出Excel的话，不需要分页
{
sql = $@"select orders.Id,orders.OrderId,orders.BuyPrice,orders.UserId,orders.AccepterName as NickName,orders.AccepterTelePhone as TelePhone,orders.Message,orders.CreateDate,orders.State,orders.OrderNum,orders.Remark,orders.FreightPrice,orders.Address from miniappgoodsorder orders {(string.IsNullOrEmpty(where) ? "" : " where " + where)} order by Id desc";
//sql = $@"select orders.Id,orders.OrderId,orders.BuyPrice,orders.UserId,user.NickName,user.TelePhone,orders.Message,orders.CreateDate,orders.State,orders.OrderNum,orders.Remark,orders.FreightPrice,orders.Address from miniappgoodsorder orders inner join c_userinfo user on user.Id=orders.UserId {(string.IsNullOrEmpty(where) ? "" : " where " + where)} order by Id desc";
}
sqlCount = $@"select count(*) from miniappgoodsorder orders {(string.IsNullOrEmpty(where) ? "" : " where " + where)}";
MiniappEntGoodsBLL _goodsbll = new MiniappEntGoodsBLL();
//拼接数据
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql, null))
{
while (dr.Read())
{
var model = GetModel(dr);
var cartlist = new MiniappEntGoodsCartBLL().GetList($"GoodsOrderId={model.Id}");
var detaillist = new List <OrderCardDetail>();
foreach (var item in cartlist)
{
var cart = new OrderCardDetail();
cart.Id = item.Id;
var goods = _goodsbll.GetModel(item.FoodGoodsId);
if (goods != null)
{
cart.GoodsName = goods.name;
cart.ImgUrl = goods.img;
}
cart.SpecInfo = item.SpecInfo;
cart.Price = item.Price;
cart.Count = item.Count;
detaillist.Add(cart);
}
model.GoodsList = detaillist;
list.Add(model);
}
}
totalCount = GetCountBySql(sqlCount);
return(list);
}
public List <VideoPlayList> GetVideoPlaylist(int pageIndex, int pageSize, int typeid, int orderby, int themeid, int orderbytype)
{
string sql = "select p.*,t.name themename from VideoPlayList p left join videoplaytheme t on p.theme=t.id ";
string sqlwhere = $" where p.state>=0 ";
string sqllimit = $" limit {(pageIndex - 1) * pageSize},{pageSize}";
if (typeid > -1)
{
sqlwhere += $" and p.typeid={typeid} ";
}
if (themeid >= 0)
{
sqlwhere += $" and p.theme={themeid} ";
}
string orderbystr = " p.sort desc,p.id desc";
switch (orderbytype)
{
case 1:
orderbystr = (orderby == 1 ? " p.playcount asc," : " p.playcount desc,") + orderbystr;
break;
case 2:
orderbystr = (orderby == 1 ? " p.PlayTime asc," : " p.PlayTime desc,") + orderbystr;
break;
}
List <VideoPlayList> list = new List <VideoPlayList>();
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql + sqlwhere + " order by " + orderbystr + sqllimit, null))
{
while (dr.Read())
{
VideoPlayList model = base.GetModel(dr);
if (dr["themename"] != null)
{
model.ThemeName = dr["themename"].ToString();
}
list.Add(model);
}
}
return(list);
}
/// <summary>
/// 企业智推版客户发送私信数量
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public List <ImMessage> GetListByQiyeCustomerUserIds(string userIds)
{
List <ImMessage> list = new List <ImMessage>();
if (string.IsNullOrEmpty(userIds))
{
return(list);
}
//用storeid表示数量
string sql = $"select Count(*) storeid,fuserid from immessage where fuserid in ({userIds}) GROUP BY fuserid";
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql, null))
{
list = base.GetList(dr);
}
return(list);
}
/// <summary>
/// 获取已确认收货并未评价的购物车数据，每次获取前1000条
/// </summary>
/// <returns></returns>
public List <PlatChildGoodsCart> GetSuccessDataList(int iscomment = 0, int day = -15)
{
List <PlatChildGoodsCart> list = new List <PlatChildGoodsCart>();
string sqlwhere = "";
//1:已评论,0:未评论
if (iscomment >= 0)
{
sqlwhere = $" and c.iscommentting={iscomment} ";
}
string sql = $"select c.* from platchildgoodscart c right JOIN platchildgoodsorder o on c.OrderId=o.id where o.State={(int)PlatChildOrderState.已完成} and o.AcceptTime<='{DateTime.Now.AddDays(day)}' {sqlwhere} LIMIT 100";
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql, null))
{
list = base.GetList(dr);
}
return(list);
}
public List <SaveMoneySetUserLog> GetListByUserId(int userInfoId, string appId, int pageSize, int pageIndex)
{
List <SaveMoneySetUserLog> list = new List <SaveMoneySetUserLog>();
string sql = $@"select * from (select id,appid,userid,moneysetuserid,moneysetid,type,beforemoney,aftermoney,changemoney,changenote,createdate,state,orderid,storeid from SaveMoneySetUserLog where appid='{appId}' and userid={userInfoId} and state=1
UNION
select id,appid,fuserid userid,0 moneysetuserid,0 moneysetid,-2 type,0 beforemoney,0 aftermoney,payment_free changemoney, shownote changenote,addtime createdate, status state,0 orderid,storeid from citymorders where appid = '{appId}' and fuserid = {userInfoId} and status = 1 and OrderType={(int)ArticleTypeEnum.MiniappWXDirectPay}
) d ORDER BY d.createdate desc LIMIT { (pageIndex - 1) * pageSize},{ pageSize}";
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql, null))
{
while (dr.Read())
{
SaveMoneySetUserLog amodel = base.GetModel(dr);
list.Add(amodel);
}
}
return(list);
}