/// <summary>
/// 获取百科信息列表
/// </summary>
/// <param name="strWhere"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="orderFiled"></param>
/// <returns></returns>
public DataTable GetBknews(string title, int type, int childnode, int state, int pageIndex, int pageSize, string orderFiled, out int totalcount)
{
StringBuilder strWhere = new StringBuilder();
strWhere = strWhere.Append($"a.type<> { (int)newsType.news}");
string str = $"type<> { (int)newsType.news}";
if (!string.IsNullOrEmpty(title))
{
strWhere = strWhere.Append($" and a.title like '%{title}%'");
str += $" and title like '%{title}%'";
}
if (type != 0)
{
strWhere = strWhere.Append($" and a.type={type}");
str += $" and type={type}";
}
if (childnode != 0)
{
strWhere = strWhere.Append($" and a.childnode={childnode}");
str += $" and childnode={childnode}";
}
if (state != -1)
{
strWhere = strWhere.Append($" and a.state={state}");
str += $" and state={state}";
}
string sql = $"select a.*,b.name as nodeName from homenews a left join homebkmenu b on a.childNode=b.id where {strWhere} limit {(pageIndex - 1) * pageSize},{pageSize}";
DataTable dt = SqlMySql.ExecuteDataSet(Utility.dbEnum.QLWL.ToString(), CommandType.Text, sql).Tables[0];
totalcount = this.GetCount(str);
return(dt);
}
public ActionResult sql(string sql, string bfsql)
{
testSqlModel model = new testSqlModel();
model.sql = sql;
model.bfsql = bfsql;
if (string.IsNullOrEmpty(sql))
{
return(View(model));
}
try
{
DataSet ds = SqlMySql.ExecuteDataSet(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql);
if (ds.Tables.Count > 0)
{
model.table = ds.Tables[0];
}
return(View(model));
}
catch (Exception ex)
{
model.msg = ex.Message;
return(View(model));
}
}
public DataTable GetTable(int aId, int storeId, int payType = 0, DateTime?queryBegin = null, DateTime?queryEnd = null)
{
string selectSql = BuildSelectSql(aId, storeId);
string whereSql = BuildWhereSql(payType, queryBegin, queryEnd);
string orderSql = "order by t.addtime desc ";
return(SqlMySql.ExecuteDataSet(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, $"{selectSql} {whereSql} {orderSql}").Tables[0]);
}
public List <TechnicianInfo> GetTechnicianList(int storeId, int sex, int orderByAge, int orderByCount, int pageSize, int pageIndex)
{
string sql = $"select *,(servicecount+basecount) as sum from technicianinfo where storeid={storeId} and state>=0";
string orderstr = " order by 1=1,";
if (sex != 0)
{
sql += $" and sex={sex}";
}
switch (orderByAge)
{
case 1: orderstr += " birthday desc,"; break;
case 2: orderstr += " birthday asc,"; break;
}
switch (orderByCount)
{
case 1: orderstr += " sum asc,"; break;
case 2: orderstr += " sum desc,"; break;
}
sql += $"{orderstr.TrimEnd(',')} limit {(pageIndex - 1) * pageSize},{pageSize}";
List <TechnicianInfo> list = new List <TechnicianInfo>();
DataSet ds = SqlMySql.ExecuteDataSet(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql);
if (ds.Tables.Count <= 0)
{
return(list);
}
DataTable dt = ds.Tables[0];
if (dt == null || dt.Rows.Count <= 0)
{
return(list);
}
TechnicianInfo model = null;
foreach (DataRow row in dt.Rows)
{
model = new TechnicianInfo();
model.id = Convert.ToInt32(row["id"]);
model.appId = Convert.ToInt32(row["appId"]);
model.storeId = Convert.ToInt32(row["storeId"]);
model.unionId = row["unionid"].ToString();
model.jobNumber = row["jobNumber"].ToString();
model.sex = Convert.ToInt32(row["sex"]);
model.birthday = Convert.ToDateTime(row["birthday"]);
model.headImg = row["headImg"].ToString();
model.serviceCount = Convert.ToInt32(row["sum"]);
model.photo = row["photo"].ToString();
model.state = Convert.ToInt32(row["state"]);
model.desc = row["desc"].ToString();
model.switchModel = JsonConvert.DeserializeObject <TechnicianSwitch>(row["switchconfig"].ToString());
list.Add(model);
}
return(list);
}
public List <XcxTemplateDetail> GetSaleDetailList(int agentId, int agenttype, string ceshitype = "")
{
List <XcxTemplateDetail> list = null;
// string sql = $"select Tname,a.* from xcxtemplate LEFT JOIN (select Tid, count(*) as count ,sum(price) as sum from xcxappaccountrelation where agentid={agentId} group by Tid ) as a on xcxtemplate.Id = a.tid where xcxtemplate.state = 0 and xcxtemplate.ProjectType = 2";
string sql = $"select a.*, xcxtemplate.TName from xcxtemplate LEFT JOIN (select sum(cost) as sum,type, count(1) as count,tid from agentdepositlog where customerid='{agentId}' and type=3 group by tid ) as a on a.tid = xcxtemplate.Id where xcxtemplate.state = 0 and (xcxtemplate.ProjectType in({(int)ProjectType.小程序},{(int)ProjectType.测试}) {(ceshitype.Length > 0 ? $" or xcxtemplate.type in ({ceshitype})" : "")}) and xcxtemplate.state>=0";
if (agenttype == 1)
{
sql += $" and (xcxtemplate.price<=0 or xcxtemplate.type={(int)TmpType.小程序专业模板})";
}
DataSet ds = SqlMySql.ExecuteDataSet(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql);
if (ds.Tables.Count <= 0)
{
return(list);
}
DataTable dt = ds.Tables[0];
if (dt == null || dt.Rows.Count <= 0)
{
return(list);
}
list = new List <XcxTemplateDetail>();
foreach (DataRow dr in dt.Rows)
{
XcxTemplateDetail detail = new XcxTemplateDetail();
if (dr["Tname"] != DBNull.Value)
{
detail.name = dr["Tname"].ToString();
}
else
{
detail.name = string.Empty;
}
if (dr["sum"] != DBNull.Value)
{
detail.sum = Convert.ToInt32(dr["sum"]);
}
else
{
detail.sum = 0;
}
if (dr["count"] != DBNull.Value)
{
detail.Count = Convert.ToInt32(dr["count"]);
}
else
{
detail.Count = 0;
}
detail.showsum = (detail.sum * 0.01).ToString("0.00");
list.Add(detail);
}
return(list);
}
public Tuple <List <QiyeYeJi>, int> GetListFromTable(int aId, int did, int pageIndex, int pageSize, string kw = "", int typeid = 0)
{
Tuple <string, List <MySqlParameter> > sqlResult = BuildSql(aId, did, pageIndex, pageSize, kw, typeid);
DataTable dt = SqlMySql.ExecuteDataSet(dbEnum.MINIAPP.ToString(), CommandType.Text, sqlResult.Item1, sqlResult.Item2?.ToArray()).Tables[0];
int totalCount = Convert.ToInt32(SqlMySql.ExecuteScalar(dbEnum.MINIAPP.ToString(), CommandType.Text, sqlResult.Item1.Replace("*", "count(0)"), sqlResult.Item2?.ToArray()));
List <QiyeYeJi> list = DataHelper.ConvertDataTableToList <QiyeYeJi>(dt);
QiyeYeJiBLL logBLL = new QiyeYeJiBLL();
return(Tuple.Create(list, totalCount));
}
public List <UserPointsInfo> GetUserPointsInfoList(int appId, out int totalCount, int pageIndex = 1, int pageSize = 10, string nickName = "")
{
totalCount = 0;
string sqlWhere = string.Empty;
List <UserPointsInfo> list = new List <UserPointsInfo>();
List <MySqlParameter> mysqlParameter = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(nickName))
{
sqlWhere = " where u.NickName like @userName";
mysqlParameter.Add(new MySql.Data.MySqlClient.MySqlParameter("@userName", "%" + nickName + "%"));
}
string sql = $"SELECT u.NickName,u.HeadImgUrl ,userid,points,playcardnum from (SELECT userid, SUM(curintegral) as points,COUNT(Id) as playcardnum from exchangeuserintegrallog where appid = {appId} and ordertype = 3 GROUP BY userId) p LEFT JOIN c_userinfo u on u.id = p.userId {sqlWhere} limit {pageSize * (pageIndex - 1)},{pageSize}";
DataSet ds = SqlMySql.ExecuteDataSet(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql, mysqlParameter.ToArray());
if (ds.Tables == null || ds.Tables.Count <= 0)
{
return(list);
}
DataTable dt = ds.Tables[0];
if (dt == null || dt.Rows.Count <= 0)
{
return(list);
}
foreach (DataRow row in dt.Rows)
{
list.Add(new UserPointsInfo()
{
UserId = Convert.ToInt32(row["userid"]),
Name = row["NickName"] != DBNull.Value ? row["NickName"].ToString() : string.Empty,
HeaderImg = row["HeadImgUrl"] != DBNull.Value ? row["HeadImgUrl"].ToString() : string.Empty,
TotalPoints = Convert.ToInt32(row["points"]),
PlayCardNum = Convert.ToInt32(row["playcardnum"])
});
}
sql = $"SELECT COUNT(userid) as number from (SELECT userid from exchangeuserintegrallog where appid = {appId} and ordertype = 3 GROUP BY userId) p LEFT JOIN c_userinfo u on u.id = p.userId {sqlWhere}";
var obj = SqlMySql.ExecuteScalar(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql, mysqlParameter.ToArray());
if (obj != DBNull.Value)
{
totalCount = Convert.ToInt32(obj);
}
return(list);
}
public Tuple <List <PinGoods>, int> GetListFromTable(int aId, int storeId, int pageIndex, int pageSize, string kw = "", int cateIdOne = 0, int cateId = 0)
{
List <MySqlParameter> parameters = null;
StringBuilder sqlFilter = new StringBuilder();
StringBuilder sqlFilter2 = new StringBuilder();
pageIndex = pageIndex - 1;
if (pageIndex < 0)
{
pageIndex = 0;
}
if (!string.IsNullOrEmpty(kw))
{
if (parameters == null)
{
parameters = new List <MySqlParameter>();
}
parameters.Add(new MySqlParameter("@kw", Utils.FuzzyQuery(kw)));
sqlFilter.Append(" and g.name like @kw ");
sqlFilter2.Append(" and name like @kw ");
}
if (cateIdOne > 0)
{
if (parameters == null)
{
parameters = new List <MySqlParameter>();
}
parameters.Add(new MySqlParameter("@cateIdOne", cateIdOne));
sqlFilter.Append(" and g.cateIdOne=@cateIdOne ");
sqlFilter2.Append(" and cateIdOne=@cateIdOne ");
}
if (cateId > 0)
{
if (parameters == null)
{
parameters = new List <MySqlParameter>();
}
parameters.Add(new MySqlParameter("@cateId", cateId));
sqlFilter.Append(" and g.cateId=@cateId ");
sqlFilter2.Append(" and cateId=@cateId ");
}
DataTable dt = SqlMySql.ExecuteDataSet(dbEnum.MINIAPP.ToString(), CommandType.Text, $"SELECT g.*,(SELECT name from PinCategory c where c.id=g.cateId) as cateName,(SELECT name from PinCategory c where c.id=g.cateIdOne) as cateNameOne from {TableName} g where g.aid={aId} and g.storeid={storeId} and g.state<>-1 {sqlFilter} order by g.sort desc limit {pageSize * pageIndex},{pageSize}", parameters?.ToArray()).Tables[0];
int totalCount = GetCount($"aid={aId} and storeid={storeId} and state<>-1 {sqlFilter2}", parameters?.ToArray());
return(Tuple.Create(DataHelper.ConvertDataTableToList <PinGoods>(dt), totalCount));
}
public List <DishStore> GetListFromTable(int pageIndex, int pageSize, string kw = "", int aId = 0)
{
List <MySqlParameter> parameters = null;
StringBuilder sqlFilter = new StringBuilder();
if (!string.IsNullOrEmpty(kw))
{
if (parameters == null)
{
parameters = new List <MySqlParameter>();
}
parameters.Add(new MySqlParameter("@kw", Utils.FuzzyQuery(kw)));
sqlFilter.Append(" and dish_name like @kw ");
}
DataTable dt = SqlMySql.ExecuteDataSet(dbEnum.MINIAPP.ToString(), CommandType.Text, $"SELECT s.*,(SELECT title from dishcategory c where c.id=s.dish_cate_id and c.type=1) as cat_name from dishstore s where s.state<>-1 and s.aid={aId} {sqlFilter} limit {pageSize * pageIndex},{pageSize}", parameters?.ToArray()).Tables[0];
return(DataHelper.ConvertDataTableToList <DishStore>(dt));
}
/// <summary>
/// 获取帖子总打赏金额
/// </summary>
/// <param name="minisnsId"></param>
/// <returns></returns>
public int GetRewardSumByArticle(int articleid)
{
int sum = 0;
DataSet set = SqlMySql.ExecuteDataSet(SqlMySql.GetSqlConnection(dbEnum.QLWL.ToString()), System.Data.CommandType.Text, "select SUM(rewardMoney) from rewardorder WHERE `status`=1 and articleid=" + articleid.ToString(), null);
if (set != null && set.Tables.Count > 0)
{
DataTable tb = set.Tables[0];
if (tb != null && tb.Rows.Count > 0)
{
if (!string.IsNullOrEmpty(tb.Rows[0][0].ToString()))
{
sum = Convert.ToInt32(tb.Rows[0][0]);
}
}
}
return(sum);
}
public List <PayRecordModel> GetList(int aId, int storeId, int pageIndex, int pageSize, out int total, int payType = 0, DateTime?queryBegin = null, DateTime?queryEnd = null)
{
string selectSql = BuildSelectSql(aId, storeId);
string whereSql = BuildWhereSql(payType, queryBegin, queryEnd);
string orderSql = "order by t.addtime desc ";
string pagerSql = $"limit {pageIndex * pageSize},{pageSize} ";
string countSql = $@"SELECT count(0)
FROM(
SELECT aid,shop_id as storeid,user_id as userid,add_time as addtime, account_info as info,account_money as money,'储值支付' as paytype
FROM DishCardAccountLog
WHERE account_type=2 and aid={aId} and shop_id={storeId} and account_info like '门店买单,%'
UNION ALL
SELECT MinisnsId as aid,CommentId as storeid,FuserId as userid,Addtime, ShowNote as info,payment_free as money,'微信支付' as paytype
FROM citymorders where OrderType=3001017 and `Status`=1 and MinisnsId={aId} and CommentId={storeId}
) t
WHERE 1=1 {whereSql}";
total = Convert.ToInt32(SqlMySql.ExecuteScalar(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, countSql));
return(DataHelper.ConvertDataTableToList <PayRecordModel>(SqlMySql.ExecuteDataSet(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, $"{selectSql} {whereSql} {orderSql} {pagerSql}").Tables[0]));
}
/// <summary>
/// 获取商品的规格属性
/// </summary>
/// <param name="goodList"></param>
/// <returns></returns>
public List <object> GetGoodsAttrSpaceByGoodsIds(List <FoodGoods> goodList)
{
List <FoodGoodsAttr> attrList = null;
if (goodList == null || goodList.Count <= 0)
{
return(null);
}
string goodsIds = string.Join(",", goodList.Select(goods => goods.Id));
string sql = $"select attrspec.foodgoodsid, attr.id as attrId,attr.FoodId,attr.AttrName,attr.State as AttrState,spec.id as SpecId,spec.SpecName,spec.state specState from foodgoodsattrspec attrspec left join foodgoodsspec as spec on spec.id = attrspec.specid left join foodgoodsattr as attr on spec.attrid = attr.id and attr.id = attrspec.attrid where attrspec.foodgoodsid in ({goodsIds})";
DataSet ds = SqlMySql.ExecuteDataSet(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql);
if (ds.Tables.Count <= 0)
{
return(null);
}
DataTable dt = ds.Tables[0];
List <FoodGoodsAttrSpecModel> list = null;
if (dt != null && dt.Rows.Count > 0)
{
list = new List <FoodGoodsAttrSpecModel>();
foreach (DataRow row in dt.Rows)
{
list.Add(ConvertToAttrSpecModel(row));
}
}
List <object> objList = new List <object>();
if (list != null && list.Count > 0)
{
foreach (var goods in goodList)
{
//取出商品的规格属性
List <FoodGoodsAttrSpecModel> childList = list.Where(attrSpecModel => attrSpecModel.foodGoodsId == goods.Id).ToList();
attrList = new List <FoodGoodsAttr>();
//取出商品规格并去除重复的
childList.ForEach(child =>
{
if (child.attr != null && attrList.Where(attr => attr.Id == child.attr.Id).ToList().Count <= 0)
{
attrList.Add(child.attr);
}
});
//根据规格取相应的属性值
attrList.ForEach(attr =>
{
attr.SpecList = new List <FoodGoodsSpec>();
childList.ForEach(child =>
{
if (child.spec != null && child.spec.AttrId == attr.Id && attr.SpecList.Where(spec => spec.Id == child.spec.Id).ToList().Count <= 0)
{
attr.SpecList.Add(child.spec);
}
});
});
//拼接数据
object obj = new
{
goods = goods,
lables = goods.labelNameStr?.Split(','),
attrList = attrList
};
objList.Add(obj);
}
}
else
{
//拼接数据
goodList.ForEach(goods =>
{
object obj = new
{
goods = goods,
lables = goods.labelNameStr?.Split(','),
attrList = attrList
};
objList.Add(obj);
});
}
return(objList);
}
public ActionResult PayRecords(string act = "", int aId = 0, int pageIndex = 0, int pageSize = 10, int recordType = 0, string storePhone = "",
string payUserPhone = "",
string transaction_id = "",
string out_trade_no = "",
int parentAgentId = 0)
{
pageIndex -= 1;
if (pageIndex < 0)
pageIndex = 0;
if (string.IsNullOrEmpty(act))
{
string fieldSql = $@"
p.transaction_id,
p.out_trade_no,
p.time_end,
p.total_fee,
c.TuserId,
c.orderno,
c.ShowNote,
c.id as CityMordersId,
s.storename,
s.phone as StoreUserPhone,
go.ordertype,
go.goodsid as GoodsOrderGoodsId,
fu.id as payUserId,
fu.NickName as payusername,
fu.telephone as PayUserPhone,
su.NickName as storeUserName,
su.Id as storeUserId,
g.name as goodsname,
g.id as goodsid,
g.img as goodsimg
";
if (parentAgentId > 0)
{
fieldSql += ",aa.fuserid";
}
string fromSql = $@"
from payresult p LEFT JOIN citymorders c on p.out_trade_no = c.orderno
LEFT JOIN c_userinfo fu on c.FuserId = fu.Id
LEFT JOIN pinstore s on c.TuserId = s.id
LEFT JOIN c_userinfo su on s.userid = su.id
LEFT JOIN pingoodsorder go on c.id=go.payno
LEFT JOIN pingoods g on go.goodsid=g.id
";
if (parentAgentId > 0)
{
fromSql += " LEFT JOIN pinagent aa on fu.id=aa.userid ";
}
List<MySqlParameter> parameters = new List<MySqlParameter>();
string whereSql = $" c.ActionType ='{(int)ArticleTypeEnum.PinOrderPay}' and go.aid={aId} ";
if (recordType == 1)
whereSql += " and go.ordertype=1 ";
else if (recordType == 2)
whereSql += " and go.ordertype=0 ";
if (!string.IsNullOrEmpty(payUserPhone))
{
whereSql += $" and fu.telephone=@PayUserPhone";
parameters.Add(new MySqlParameter("@PayUserPhone", payUserPhone));
}
if (!string.IsNullOrEmpty(storePhone))
{
whereSql += " and s.phone=@StoreUserPhone";
parameters.Add(new MySqlParameter("@StoreUserPhone", storePhone));
}
if (!string.IsNullOrEmpty(transaction_id))
{
whereSql += " and p.transaction_id=@transaction_id";
parameters.Add(new MySqlParameter("@transaction_id", transaction_id));
}
if (!string.IsNullOrEmpty(out_trade_no))
{
whereSql += " and p.out_trade_no=@out_trade_no";
parameters.Add(new MySqlParameter("@out_trade_no", out_trade_no));
}
if (parentAgentId > 0)
{
whereSql += " and go.ordertype=1 and aa.fuserid=" + parentAgentId;//and aa.state=1
}
string orderSql = " order by p.id desc ";
string pagerSql = $" limit {pageSize * pageIndex},{pageSize} ";
string querySql = $" select distinct {fieldSql} {fromSql} where {whereSql} {orderSql} {pagerSql}";
string countSql = $" select distinct count(0) {fromSql} where {whereSql}";
List<PayRecordModel> list = DataHelper.ConvertDataTableToList<PayRecordModel>(SqlMySql.ExecuteDataSet(Utility.dbEnum.MINIAPP.ToString(), System.Data.CommandType.Text, querySql, parameters.ToArray()).Tables[0]);
string goodsIds = string.Join(",",list?.Select(s=>s.GoodsId).Distinct());
List<PinGoods> pinGoodsList = PinGoodsBLL.SingleModel.GetListByIds(goodsIds);
list?.ForEach(p =>
{
p.OrderGoods = pinGoodsList?.FirstOrDefault(f=>f.id ==p.GoodsId);
if (p.OrderType == 1)
{
PinAgent agentInfo = PinAgentBLL.SingleModel.GetModelByUserId(p.PayUserId);
if (agentInfo != null && agentInfo.fuserId > 0)
{
p.ParentAgentUser = C_UserInfoBLL.SingleModel.GetModel(agentInfo.fuserId);
p.ParentAgentStore = PinStoreBLL.SingleModel.GetModelByAid_Id(agentInfo.aId, agentInfo.fuserId);
}
}
});
ViewModel<PayRecordModel> vm = new ViewModel<PayRecordModel>();
vm.DataList = list;
vm.aId = aId;
vm.PageSize = pageSize;
vm.PageIndex = pageIndex;
vm.TotalCount = PayResultBLL.SingleModel.GetCountBySql(countSql, parameters.ToArray());
return View(vm);
}
return View();
}
/// <summary>
/// 获取会员列表
/// </summary>
/// <param name="appId"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="userName"></param>
/// <param name="levelid"></param>
/// <param name="leveltype"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public MiniappVipInfo GetVipList(string appId, int pageIndex, int pageSize, string userName, int levelid, int leveltype, string startDate, string endDate, string telePhone = "", int isor = 0)
{
string userIds = string.Empty;
string sqlwhere = $" viprelation.appid='{appId}' and viprelation.state>=0 and c_userinfo.nickname is not NULL and c_userinfo.headimgurl is not NULL";
List <MySql.Data.MySqlClient.MySqlParameter> mysqlParameter = new List <MySql.Data.MySqlClient.MySqlParameter>();
if (isor > 0)
{
if (!string.IsNullOrEmpty(userName) && !string.IsNullOrEmpty(telePhone))
{
sqlwhere += $" and ( c_userinfo.nickname like @userName or c_userinfo.TelePhone like @telePhone )";
mysqlParameter.Add(new MySql.Data.MySqlClient.MySqlParameter("@userName", "%" + userName + "%"));
mysqlParameter.Add(new MySql.Data.MySqlClient.MySqlParameter("@TelePhone", "%" + telePhone + "%"));
}
}
else
{
if (!string.IsNullOrEmpty(userName))
{
sqlwhere += $" and c_userinfo.nickname like @userName ";
mysqlParameter.Add(new MySql.Data.MySqlClient.MySqlParameter("@userName", "%" + userName + "%"));
}
if (!string.IsNullOrEmpty(telePhone))
{
//再将电话号码匹配订单的收货号码 找出下单的这个用户
userIds = EntGoodsOrderBLL.SingleModel.GetListUserIdByAccepterTelePhone(telePhone);
if (!string.IsNullOrEmpty(userIds))
{
sqlwhere += $" and (c_userinfo.Id in({userIds}) or c_userinfo.TelePhone like @telePhone)";
}
else
{
sqlwhere += $" and c_userinfo.TelePhone like @telePhone ";
}
mysqlParameter.Add(new MySql.Data.MySqlClient.MySqlParameter("@TelePhone", "%" + telePhone + "%"));
}
}
if (levelid > 0)
{
sqlwhere += $" and viprelation.levelid={levelid}";
}
if (leveltype != -1)
{
sqlwhere += $" and viplevel.type={leveltype}";
}
if (!string.IsNullOrWhiteSpace(startDate))
{
sqlwhere += $" and viprelation.addtime>=@startDate ";
mysqlParameter.Add(new MySql.Data.MySqlClient.MySqlParameter("@startDate", startDate + " 00:00:00"));
}
if (!string.IsNullOrWhiteSpace(endDate))
{
sqlwhere += $" and viprelation.addtime<=@endDate ";
mysqlParameter.Add(new MySql.Data.MySqlClient.MySqlParameter("@endDate", endDate + " 23:59:59"));
}
string sql = $"select viprelation.*,c_userinfo.Remark,c_userinfo.id as u_id,c_userinfo.TelePhone,c_userinfo.nickname,c_userinfo.headimgurl,c_userInfo.usertype,viplevel.type,viplevel.discount,viplevel.name,savemoneysetuser.AccountMoney,VipWxCardCode.Code from viprelation left join c_userinfo on viprelation.uid=c_userinfo.id left join viplevel on viprelation.levelid= viplevel.id left join savemoneysetuser on viprelation.uid=savemoneysetuser.userid left join VipWxCardCode on c_userinfo.id=VipWxCardCode.UserId where {sqlwhere} order by id desc limit {pageSize * (pageIndex - 1)},{pageSize}";
string countsql = $"select count(*) as count from viprelation left join c_userinfo on viprelation.uid=c_userinfo.id left join viplevel on viprelation.levelid= viplevel.id where {sqlwhere}";
MiniappVipInfo model = new MiniappVipInfo();
DataSet ds = SqlMySql.ExecuteDataSet(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql, mysqlParameter.ToArray());
if (ds.Tables.Count <= 0)
{
return(model);
}
DataTable dt = ds.Tables[0];
if (dt == null || dt.Rows.Count <= 0)
{
return(model);
}
model.relationList = new List <VipRelation>();
foreach (DataRow row in dt.Rows)
{
VipRelation viprelation = new VipRelation();
viprelation.levelInfo = new VipLevel();
if (row["Code"] != DBNull.Value)
{
viprelation.WxVipCode = row["Code"].ToString();
}
if (row["TelePhone"] != DBNull.Value)
{
viprelation.TelePhone = row["TelePhone"].ToString();
}
if (row["id"] != DBNull.Value)
{
viprelation.Id = Convert.ToInt32(row["id"]);
}
if (row["addtime"] != DBNull.Value)
{
viprelation.addtime = Convert.ToDateTime(row["addtime"]);
}
if (row["updatetime"] != DBNull.Value)
{
viprelation.updatetime = Convert.ToDateTime(row["updatetime"]);
}
if (row["nickname"] != DBNull.Value)
{
viprelation.username = row["nickname"].ToString();
}
if (row["pricesum"] != DBNull.Value)
{
viprelation.PriceSum = Convert.ToInt32(row["pricesum"]);
}
if (row["name"] != DBNull.Value)
{
viprelation.levelInfo.name = row["name"].ToString();
}
if (row["discount"] != DBNull.Value)
{
viprelation.levelInfo.discount = Convert.ToInt32(row["discount"]);
}
if (row["type"] != DBNull.Value)
{
viprelation.levelInfo.type = Convert.ToInt32(row["type"]);
}
if (row["levelid"] != DBNull.Value)
{
viprelation.levelid = Convert.ToInt32(row["levelid"]);
}
if (row["headimgurl"] != DBNull.Value)
{
viprelation.headimgurl = row["headimgurl"].ToString();
}
if (row["AccountMoney"] != DBNull.Value)
{
viprelation.AccountMoney = Convert.ToInt32(row["AccountMoney"]);
}
if (row["uid"] != DBNull.Value)
{
viprelation.uid = Convert.ToInt32(row["uid"]);
}
if (row["usertype"] != DBNull.Value)
{
viprelation.userType = Convert.ToInt32(row["usertype"]);
}
if (row["Remark"] != DBNull.Value)
{
viprelation.Remark = Convert.ToString(row["Remark"]);
}
if (string.IsNullOrEmpty(viprelation.TelePhone))
{
//暂时获取该用户普通订单的电话号码，待下次微信授权电话号码再拿微信授权电话号码
EntGoodsOrder order = EntGoodsOrderBLL.SingleModel.GetModel($"UserId={viprelation.uid}");
if (order != null)
{
viprelation.TelePhone = order.AccepterTelePhone;
}
}
viprelation.SaveMoneySum = SaveMoneySetUserBLL.SingleModel.GetSaveMoneySum(viprelation.uid) * 0.01;
model.relationList.Add(viprelation);
}
DataSet dc = SqlMySql.ExecuteDataSet(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, countsql, mysqlParameter.ToArray());
if (dc.Tables.Count <= 0)
{
return(model);
}
DataTable cdt = dc.Tables[0];
model.recordCount = Convert.ToInt32(cdt.Rows[0]["count"]);
model.kfCount = model.relationList.Where(relation => relation.userType == 1).Count();
return(model);
}
public List <DishAttr> GetListFromTable(int pageIndex, int pageSize, int fid)
{
DataTable dt = SqlMySql.ExecuteDataSet(dbEnum.MINIAPP.ToString(), CommandType.Text, $"SELECT a.*,(SELECT cat_name from dishattrtype where dishattrtype.id=a.cat_id and dishattrtype.state=1) as cat_name from dishattr a where a.state=1 and cat_id={fid} order by id desc limit {pageSize * pageIndex},{pageSize}").Tables[0];
return(DataHelper.ConvertDataTableToList <DishAttr>(dt));
}
public ActionResult charts(string act = "", string tpl = "", DateTime?stime = null, DateTime?etime = null)
{
DateTime now = DateTime.Now;
if (stime == null)
{
stime = DateTime.Now.AddDays(-7);
}
if (etime == null)
{
etime = new DateTime(now.Year, now.Month, now.Day);
}
ViewBag.stime = stime.Value.ToString("yyyy-MM-dd");
ViewBag.etime = etime.Value.ToString("yyyy-MM-dd");
if (string.IsNullOrEmpty(act))
{
return(View());
}
else
{
ChartsDataModel model = new ChartsDataModel();
if (tpl == "all")
{
ChartsLineModel lineModel = new ChartsLineModel();
lineModel.TId = "all";
lineModel.Name = "全部";
string sql = $@"
SELECT 'all' as 模板, SUM(o.payment_free) / 100 as 总金额,DATE_FORMAT(Addtime, '%Y-%m-%d') as 日期 from citymorders o
where OrderType >= 3001001 and OrderType<= 3001026 and `Status`= 1 and o.addtime between '{stime.Value.ToString("yyyy-MM-dd 00:00:00")}' and '{etime.Value.ToString("yyyy-MM-dd 23:59:59")}'
and MinisnsId not in(SELECT id from xcxappaccountrelation WHERE AccountId='20d5ea85-b0b3-4135-b250-617054be406b' and id<>6933593)
group by 日期 ";
DataTable dt = SqlMySql.ExecuteDataSet(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
model.Labels.Add(item["日期"].ToString());
lineModel.Datas.Add(Convert.ToDouble(item["总金额"]));
}
model.LinList.Add(lineModel);
}
}
else
{
TimeSpan ts = etime.Value - stime.Value;
int days = 1;
if (ts.Days <= 1)
{
days = 1;
}
else
{
days = ts.Days;
}
for (int i = days; i >= 1; i--)
{
model.Labels.Add(DateTime.Now.AddDays(-i).ToString("yyyy-MM-dd"));
}
Dictionary <string, string> tplDic = new Dictionary <string, string>
{
{ "pro", "3001006,3001010,3001015,3001020" }, //订单,储值买单，微信买单，内容付费
{ "dish", "3001016,3001017,3001019" }, //订单，微信买单，会员充值
{ "food", "3001002" }, //订单
{ "store", "3001001" }, //订单
{ "pin", "3001022" },
{ "platform", "3001025" },
{ "mutistore", "3001009" }
};
Dictionary <string, string> tplNameDic = new Dictionary <string, string>
{
{ "pro", "专业版" },
{ "dish", "智慧餐厅" },
{ "food", "餐饮版" },
{ "store", "电商版" },
{ "pin", "拼享惠" },
{ "platform", "平台" },
{ "mutistore", "多门店" },
};
List <string> tplSearch = new List <string>();
string[] kv = tpl.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
for (int i = 0; i < kv.Length; i++)
{
if (tplDic.ContainsKey(kv[i]))
{
ChartsLineModel lineModel = new ChartsLineModel();
lineModel.TId = kv[i];
lineModel.Name = tplNameDic[kv[i]];
string sql = $@"
SELECT 'all' as 模板, SUM(o.payment_free) / 100 as 总金额,DATE_FORMAT(Addtime, '%Y-%m-%d') as 日期 from citymorders o
where OrderType in({tplDic[kv[i]]}) and `Status`= 1 and o.addtime between '{stime.Value.ToString("yyyy-MM-dd 00:00:00")}' and '{etime.Value.ToString("yyyy-MM-dd 23:59:59")}'
and MinisnsId not in(SELECT id from xcxappaccountrelation WHERE AccountId='20d5ea85-b0b3-4135-b250-617054be406b' and id<>6933593)
group by 日期 ";
DataTable dt = SqlMySql.ExecuteDataSet(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql).Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow item in dt.Rows)
{
lineModel.Datas.Add(Convert.ToDouble(item["总金额"]));
}
}
model.LinList.Add(lineModel);
}
}
}
return(Json(model));
}
}
public ActionResult subgoodlist(int appId, int pageIndex = 1, int pageSize = 20)
{
ViewModel <SubStoreGoodsView> vm = new ViewModel <SubStoreGoodsView>();
string search = Context.GetRequest("search", "");
int plabels = Context.GetRequestInt("plabels", 0);
int ptype = Context.GetRequestInt("ptype", 0);
int ptag = Context.GetRequestInt("ptag", -1);
int storeid = Context.GetRequestInt("storeId", 0);
string isPost = Context.GetRequest("isPost", string.Empty);
string strwhere = $"aid={appId} and substate=1 and state=1 and StoreId={storeid} and goodtype={(int)EntGoodsType.普通产品}";
List <MySqlParameter> parameters = new List <MySqlParameter>();
if (search.Trim() != "")
{
strwhere += $" and name like @name ";
parameters.Add(new MySqlParameter("@name", $"%{search}%"));
}
if (plabels > 0)
{
strwhere += $" and FIND_IN_SET(@plabels,plabels)";
parameters.Add(new MySqlParameter("@plabels", plabels));
}
if (ptype > 0)
{
strwhere += $" and FIND_IN_SET (@ptype,ptypes) ";
parameters.Add(new MySqlParameter("@ptype", ptype));
}
if (ptag > -1)
{
strwhere += $" and subtag={ptag} ";
}
string selSql = $"select * from substoregoodsview where {strwhere} limit {(pageIndex - 1) * pageSize},{pageSize}";
DataTable dt = SqlMySql.ExecuteDataSet(dbEnum.MINIAPP.ToString(), CommandType.Text, selSql, parameters.ToArray()).Tables[0];
vm.DataList = DataHelper.ConvertDataTableToList <SubStoreGoodsView>(dt);
string countSql = $"select count(0) from substoregoodsview where {strwhere}";
vm.TotalCount = Convert.ToInt32(SqlMySql.ExecuteScalar(dbEnum.MINIAPP.ToString(), CommandType.Text, countSql, parameters.ToArray()));
vm.PageIndex = pageIndex;
vm.PageSize = pageSize;
vm.DataList.ForEach(x =>
{
if (!string.IsNullOrEmpty(x.ptypes))
{
string sql = $"SELECT GROUP_CONCAT(`name`) from entgoodtype where FIND_IN_SET(id,@ptypes)";
x.ptypestr = SqlMySql.ExecuteScalar(dbEnum.MINIAPP.ToString(),
CommandType.Text, sql,
new MySqlParameter[] { new MySqlParameter("@ptypes", x.ptypes) }).ToString();
}
if (!string.IsNullOrEmpty(x.plabels))
{
string sql = $"SELECT group_concat(name) from entgoodlabel where FIND_IN_SET(id,@plabels)";
x.plabelstr = SqlMySql.ExecuteScalar(Utility.dbEnum.MINIAPP.ToString(),
CommandType.Text, sql,
new MySqlParameter[] { new MySqlParameter("@plabels", x.plabels) }).ToString();
}
});
vm.CurrentUserRoles = UserRoleBLL.SingleModel.GetCurrentUserRoles(dzuserId, appId, storeid);
if (isPost == "isPost")
{
ViewModel <EntGoods> entgoodView = new ViewModel <EntGoods>();
entgoodView.TotalCount = vm.TotalCount;
entgoodView.PageIndex = vm.PageIndex;
entgoodView.PageSize = vm.PageSize;
vm.DataList.ForEach(p =>
{
entgoodView.DataList.Add(new EntGoods()
{
id = p.Pid,
name = p.name,
img = p.img,
price = p.price,
unit = p.unit
});
});
return(Content(JsonConvert.SerializeObject(entgoodView, new IsoDateTimeConverter()
{
DateTimeFormat = "yyyy-MM-dd HH:mm:ss"
})));
}
return(View(vm));
}
public List <DishAttrType> GetListFromTable(int pageIndex, int pageSize, int aId, int storeId)
{
DataTable dt = SqlMySql.ExecuteDataSet(dbEnum.MINIAPP.ToString(), CommandType.Text, $"SELECT t.*,(SELECT count(0) from dishattr where dishattr.cat_id=t.id and dishattr.state=1) as attrcount from dishattrtype t where t.state<>-1 and t.storeId={storeId} and t.aid={aId} order by id desc limit {pageSize * pageIndex},{pageSize}").Tables[0];
return(DataHelper.ConvertDataTableToList <DishAttrType>(dt));
}
public ActionResult Comments(string act = "", int id = 0, int aId = 0, int storeId = 0, int pageIndex = 0, int pageSize = 20, string kw = "")
{
pageIndex = pageIndex - 1;
if (pageIndex < 0)
{
pageIndex = 0;
}
//显示
if (string.IsNullOrEmpty(act))
{
string filterSql = $" pc.state=1 and pc.aid={aId} and pc.storeid={storeId} ";
ViewModel <PinComment> vm = new ViewModel <PinComment>();
List <MySqlParameter> parameters = null;
string sqlField = "pc.*,pg.name as GoodName,pg.img as GoodImgUrl";
string sqlCount = "count(0)";
string sql = $"from PinComment pc left join pingoods pg on pc.goodsid=pg.id where pc.aid={aId} and pc.storeid={storeId}";
if (!string.IsNullOrEmpty(kw))
{
sql += $" and pg.name like @kw ";
parameters = new List <MySqlParameter>();
parameters.Add(new MySqlParameter("kw", Utils.FuzzyQuery(kw)));
}
DataTable dt = SqlMySql.ExecuteDataSet(dbEnum.MINIAPP.ToString(), CommandType.Text, $"SELECT {sqlField} {sql} order by pc.id desc limit {pageSize * pageIndex},{pageSize}", parameters?.ToArray()).Tables[0];
vm.DataList = DataHelper.ConvertDataTableToList <PinComment>(dt);
string userIds = string.Join(",", vm.DataList.Select(s => s.UserId).Distinct());
List <C_UserInfo> userInfoList = C_UserInfoBLL.SingleModel.GetListByIds(userIds);
vm.DataList?.ForEach(p =>
{
C_UserInfo user = userInfoList?.FirstOrDefault(f => f.Id == p.UserId);
if (user != null)
{
p.NickName = user.NickName;
p.UserPhoto = user.HeadImgUrl;
}
});
vm.PageIndex = pageIndex;
vm.PageSize = pageSize;
vm.TotalCount = PinCommentBLL.SingleModel.GetCountBySql($" select {sqlCount} {sql}", parameters?.ToArray());
vm.aId = aId;
vm.storeId = storeId;
return(View(vm));
}
else
{
//删除
if (act == "del")
{
if (id <= 0)
{
result.msg = "参数错误";
}
else
{
PinComment updateModel = PinCommentBLL.SingleModel.GetModel(id);
if (updateModel != null)
{
updateModel.State = -1;
bool updateResult = PinCommentBLL.SingleModel.Update(updateModel);
if (updateResult)
{
result.code = 1;
result.msg = "删除成功";
}
else
{
result.msg = "删除失败";
}
}
else
{
result.msg = "删除失败,对象不存在或已删除";
}
}
}
}
return(Json(result, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 获取积分榜
/// </summary>
/// <param name="appId"></param>
/// <param name="aid"></param>
/// <param name="totalCount"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="nickName"></param>
/// <returns></returns>
public List <UserIntegralDetail> GetUserIntegralDetailList(string appId, int aid, out int totalCount, int pageIndex = 1, int pageSize = 10, string nickName = "")
{
totalCount = 0;
string sqlWhere = $"where v.appid = '{appId}' and v.state >= 0 and u.nickname is not NULL and u.headimgurl is not NULL ";
List <UserIntegralDetail> list = new List <UserIntegralDetail>();
List <MySqlParameter> mysqlParameter = new List <MySqlParameter>();
if (!string.IsNullOrEmpty(nickName))
{
sqlWhere += " and u.NickName like @userName";
mysqlParameter.Add(new MySql.Data.MySqlClient.MySqlParameter("@userName", "%" + nickName + "%"));
}
string sql = $"select u.id as userid,u.nickname,u.headimgurl, u.Remark,e.integral from viprelation v left join c_userinfo u on v.uid=u.id left join ExchangeUserIntegral e on e.userId=u.Id { sqlWhere} order by e.integral desc limit {pageSize * (pageIndex - 1)},{pageSize}";
DataSet ds = SqlMySql.ExecuteDataSet(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql, mysqlParameter.ToArray());
if (ds.Tables == null || ds.Tables.Count <= 0)
{
return(list);
}
DataTable dt = ds.Tables[0];
if (dt == null || dt.Rows.Count <= 0)
{
return(list);
}
int userId = 0;
foreach (DataRow row in dt.Rows)
{
userId = Convert.ToInt32(row["userid"]);
UserIntegralDetail userIntegralDetail = new UserIntegralDetail();
userIntegralDetail.UserId = userId;
userIntegralDetail.NickName = row["NickName"] != DBNull.Value ? row["NickName"].ToString() : string.Empty;
if (row["Remark"] != DBNull.Value)
{
userIntegralDetail.NickName += !string.IsNullOrEmpty(row["Remark"].ToString()) ? "(" + row["Remark"].ToString() + ")" : string.Empty;
}
userIntegralDetail.Avatar = row["HeadImgUrl"] != DBNull.Value ? row["HeadImgUrl"].ToString() : string.Empty;
userIntegralDetail.TotalPoints = row["integral"] != DBNull.Value ? Convert.ToInt32(row["integral"]) : 0;
userIntegralDetail.PlayCardNum = GetPlayCardNumByUserId(userId);
userIntegralDetail.FromConsumPoints = GetUserIntegralByType(0, userId);
userIntegralDetail.FromSavmeMoneyPoints = GetUserIntegralByType(2, userId);
userIntegralDetail.CostTotalPoints = GetUserIntegralByType(1, userId);
userIntegralDetail.FromPlayCardPoints = GetUserIntegralByType(3, userId);
list.Add(userIntegralDetail);
}
sql = $"select count(u.id) from viprelation v left join c_userinfo u on v.uid=u.id { sqlWhere}";
var obj = SqlMySql.ExecuteScalar(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql, mysqlParameter.ToArray());
if (obj != DBNull.Value)
{
totalCount = Convert.ToInt32(obj);
}
return(list);
}
/// <summary>
/// 供导出使用
/// </summary>
/// <param name="aId"></param>
/// <param name="did"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize">传入<0的数字表示导出全部</param>
/// <param name="kw"></param>
/// <param name="typeid"></param>
/// <returns></returns>
public DataTable GetDataTable(int aId, int did, int pageIndex, int pageSize, string kw = "", int typeid = 0)
{
Tuple <string, List <MySqlParameter> > sqlResult = BuildSql(aId, did, pageIndex, pageSize, kw, typeid);
return(SqlMySql.ExecuteDataSet(dbEnum.MINIAPP.ToString(), CommandType.Text, sqlResult.Item1, sqlResult.Item2?.ToArray()).Tables[0]);
}