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));
}
}
/// <summary>
/// 通过经纬度获取到店自取的附近门店,按距离排序最近的优先然后再按时间
/// </summary>
/// <param name="aid"></param>
/// <param name="totalCount"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="ws_lat"></param>
/// <param name="ws_lng"></param>
/// <returns></returns>
public List <PickPlace> GetListNearStoreByLocation(int aid, out int totalCount, int pageSize = 10, int pageIndex = 1, double ws_lat = 0, double ws_lng = 0)
{
List <PickPlace> list = new List <PickPlace>();
totalCount = 0;
string strWhere = $" aid={aid} and state>=0 ";
string strWhereCount = $" aid={aid} and state>=0 ";
string sql = $"select *,ROUND(6378.138*2*ASIN(SQRT(POW(SIN(({ws_lat}*PI()/180-lat*PI()/180)/2),2)+COS({ws_lat}*PI()/180)*COS(lat*PI()/180)*POW(SIN(({ws_lng}*PI()/180-lng*PI()/180)/2),2)))*1000) AS distance from PickPlace where { strWhere} ORDER BY distance asc, addtime desc limit {(pageIndex - 1) * pageSize},{pageSize}";
using (var dr = SqlMySql.ExecuteDataReader(connName, CommandType.Text, sql, null))
{
while (dr.Read())
{
PickPlace pickPlace = base.GetModel(dr);
pickPlace.DistanceStr = (Convert.ToInt32(dr["distance"]) * 0.001) < 1 ? $"{dr["distance"].ToString()}m" : $"{Convert.ToInt32(dr["distance"]) * 0.001}km";
list.Add(pickPlace);
}
if (list.Count > 0)
{
totalCount = base.GetCount(strWhereCount);
}
return(list);
}
}
/// <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="name"></param>
/// <param name="aid"></param>
/// <returns></returns>
public List <PlatStore> GetListByNameOrPhone(string name, int aid)
{
List <PlatStore> list = new List <PlatStore>();
MySqlParameter[] parms = new MySqlParameter[] {
new MySqlParameter("@name", $"%{name}%"),
new MySqlParameter("@phone", $"%{name}%")
};
string sql = $"select s.*,c.userid from platstore s left join platmycard c on s.MyCardId=c.id where s.bindplataid={aid} and (s.Name like @name or c.Phone like @phone)";
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql, parms.ToArray()))
{
while (dr.Read())
{
PlatStore model = base.GetModel(dr);
if (dr["userid"] != DBNull.Value)
{
model.UserId = Convert.ToInt32(dr["userid"]);
}
list.Add(model);
}
}
return(list);
}
/// <summary>
/// 获取所有同城的区域信息,要排除行业版的
/// </summary>
/// <returns></returns>
public List <C_Area> GetCityInfo3()
{
List <C_Area> resultList = new List <C_Area>();
string strSql = @"SELECT area.`code`,area.`name`,area.`level`,area.pingyin
from c_cityinfo cityinfo LEFT JOIN c_area area on cityinfo.AreaCode = area.`code`
where area.`code`>0 and cityinfo.IsThirdTools=0 ";
using (MySqlDataReader dr = SqlMySql.ExecuteDataReader(connName, CommandType.Text, strSql, null))
{
while (dr.Read())
{
var model = new C_Area();
if (dr["level"] != DBNull.Value)
{
model.Level = Convert.ToInt32(dr["level"]);
}
if (dr["name"] != DBNull.Value)
{
model.Name = dr["name"].ToString();
}
if (dr["code"] != DBNull.Value)
{
model.Code = Convert.ToInt32(dr["code"]);
}
if (dr["pingyin"] != DBNull.Value)
{
model.PingYin = dr["pingyin"].ToString();
}
resultList.Add(model);
}
}
return(resultList);
}
public ActionResult UpdateEntGoodType(int appId)
{
string sql = $"update entgoodtype set parentid=-1 where aid={appId}";
int i = SqlMySql.ExecuteNonQuery(Utility.dbEnum.MINIAPP.ToString(), System.Data.CommandType.Text, sql, null);
return(Json(new { isok = true, msg = "成功", obj = i }, JsonRequestBehavior.AllowGet));
}
/// <summary>
/// 退款记录报表
/// </summary>
/// <returns></returns>
public int getReFundQueueReport_Count(DateTime?starttime = null, DateTime?endtime = null, int pageIndex = 1, int pageSize = 50)
{
StringBuilder sb = new StringBuilder();
sb.Append($" select count(0) from refundqueue ");
sb.Append($" where retype = 1 "); //retype = 1 :同城退款记录
sb.Append(starttime != null ? $" and DATE_FORMAT(addtime,'%Y-%m-%d') >= DATE_FORMAT(@starttime,'%Y-%m-%d')" : "");
sb.Append(endtime != null ? $" and DATE_FORMAT(addtime,'%Y-%m-%d') <= DATE_FORMAT(@endtime,'%Y-%m-%d')" : "");
List <MySqlParameter> mysql = new List <MySqlParameter>();
if (starttime != null)
{
mysql.Add(new MySqlParameter("@starttime", starttime));
}
if (endtime != null)
{
mysql.Add(new MySqlParameter("@endtime", endtime));
}
//List<ReFundQueue> list = base.GetListBySql(sb.ToString());
List <ReFundQueueReport> list = new List <ReFundQueueReport>();
return(Convert.ToInt32(SqlMySql.ExecuteScalar(Utility.dbEnum.QLWL.ToString(), CommandType.Text, sb.ToString(), mysql.ToArray())));
}
/// <summary>
/// 获取小程序指定时间内完成订单总收入
/// </summary>
/// <param name="appId"></param>
/// <param name="payState"></param>
/// <param name="startDate"></param>
/// <param name="endDate"></param>
/// <returns></returns>
public int GetPriceSumByAppId_Date(string appId, string startDate, string endDate)
{
int priceSum = 0;
if (string.IsNullOrEmpty(appId) || string.IsNullOrEmpty(startDate) || string.IsNullOrEmpty(endDate))
{
return(priceSum);
}
List <MySqlParameter> paramters = new List <MySqlParameter>();
paramters.Add(new MySqlParameter("@appId", appId));
paramters.Add(new MySqlParameter("@startDate", startDate));
paramters.Add(new MySqlParameter("@endDate", endDate));
//string sql = $"select sum(BuyPrice) pricesum from groupuser where appid=@appId and State=-1 and PayTime>=@startDate and PayTime<=@endDate";
string sql = $"select sum(BuyPrice) pricesum from groupuser where appid=@appId and State=-1 and PayTime between @startDate and @endDate";
using (var dr = SqlMySql.ExecuteDataReader(dbEnum.MINIAPP.ToString(), CommandType.Text, sql, paramters.ToArray()))
{
while (dr.Read())
{
priceSum = dr["pricesum"] == DBNull.Value ? 0 : Convert.ToInt32(dr["pricesum"]);
}
}
return(priceSum);
}
/// <summary>
/// 获取累积提现金额
/// </summary>
/// <param name="aId"></param>
/// <param name="userId"></param>
/// <returns></returns>
public int GetSumCash(int aId, int userId)
{
string sql = $"select sum(cashMoney) from DrawCashApply where aid={aId} and userid={userId} and drawstate={(int)DrawCashState.提现成功}";
var result = SqlMySql.ExecuteScalar(connName, CommandType.Text, sql);
return(result == DBNull.Value ? 0 : Convert.ToInt32(result));
}
public override object Add(FoodAddress model)
{
var addSql = BuildAddSql(model);
addSql += "select last_insert_id(); ";
return(SqlMySql.ExecuteScalar(connName, System.Data.CommandType.Text, addSql, null));
}
/// <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);
}
/// <summary>
/// 获取分销员列表
/// </summary>
/// <param name="strWhere"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="strOrder"></param>
/// <returns></returns>
public List <SalesMan> GetListSalesMan(string strWhere, int pageIndex = 1, int pageSize = 10, string strOrder = "")
{
string sql = $"select m.*,u.NickName from SalesMan m LEFT join C_UserInfo u on m.UserId=u.Id where {strWhere} group by m.userId order by {strOrder} LIMIT {(pageIndex - 1) * pageSize},{pageSize}";
using (var dr = SqlMySql.ExecuteDataReader(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql))
{
List <SalesMan> list = new List <SalesMan>();
while (dr.Read())
{
SalesMan salesMan = GetModel(dr);
if (salesMan != null && salesMan.Id > 0)
{
salesMan.nickName = (dr["NickName"] == DBNull.Value ? string.Empty : dr["NickName"].ToString());
salesMan.orderCount = VipRelationBLL.SingleModel.GetEntGoodsOrderCount(salesMan.UserId);
VipRelation vip = VipRelationBLL.SingleModel.GetModel($"uid={salesMan.UserId}");
if (vip != null)
{
salesMan.orderPrice = vip.pricestr;
}
else
{
salesMan.orderPrice = "0.00";
}
salesMan = GetSalesMan(salesMan);
list.Add(salesMan);
}
}
return(list.Count > 0 ? list : null);
}
}
/// <summary>
/// 根据用户输入的关键词模糊查询名称或者电话 找到分销员ID集合
/// </summary>
/// <param name="keyMsg"></param>
/// <param name="aid"></param>
/// <returns></returns>
public string GetSaleManIdsByPhoneName(string keyMsg, int aid)
{
string sql = $"select m.Id as salesManId from SalesMan m LEFT join C_UserInfo u on m.UserId=u.Id where m.TelePhone like @keyMsg or u.NickName like @keyMsg";
List <MySqlParameter> parameters = new List <MySqlParameter>();
parameters.Add(new MySqlParameter("@keyMsg", $"%{keyMsg}%"));
using (var dr = SqlMySql.ExecuteDataReader(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql, parameters.ToArray()))
{
List <string> listId = new List <string>();
while (dr.Read())
{
if (dr["salesManId"] != DBNull.Value)
{
listId.Add(dr["salesManId"].ToString());
}
}
if (listId.Count > 0)
{
return(string.Join(",", listId));
}
else
{
return("-1");
}
}
}
public List <object> GetStoreListByAgentId_type(int agentId, int pageIndex, int pageSize, out int count, int extractType = 0)
{
string sqlwhere = $" agentid={agentId} and state=1";
if (extractType == 1)
{
//查找下下级
sqlwhere = $" agentid in({GetGrandfatherStoreAgentIds(agentId)}) and state=1 ";
}
count = GetCount(sqlwhere);
List <PinStore> list = GetList(sqlwhere, pageSize, pageIndex, "*", "id desc");
List <object> objList = new List <object>();
if (list != null && list.Count > 0)
{
foreach (var store in list)
{
string sql = $"select sum(income) as income from pinagentincomelog where source=1 and agentid={agentId} and sourceuid={store.userId} and ExtractType={extractType}";
var result = SqlMySql.ExecuteScalar(connName, System.Data.CommandType.Text, sql);
string income = result == DBNull.Value ? "0.00" : (Convert.ToInt32(result) * 0.001 * 0.01).ToString("0.00");
objList.Add(new { headImg = store.logo, name = store.storeName, income, addtime = store.startDateStr, phone = store.phone, storeId = store.id });
}
}
return(objList);
}
/// <summary>
/// 获取累计客户列表
/// </summary>
/// <param name="strWhere"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="strOrder"></param>
/// <returns></returns>
public List <SalesManRecordUser> GetListSalesManRecordUser(int appId, int salesManId, int userId, int state = 0, int pageIndex = 1, int pageSize = 10, string strOrder = "t.Id desc")
{
//TODO 测试使用分钟MINUTE 线上使用天Day 分销
// string sql = $"SELECT t.*,u.NickName,u.HeadImgUrl from (SELECT * from ( SELECT *,datediff( DATE_ADD(UpdateTime,INTERVAL protected_time MINUTE),now()) as cur_protected_time from SalesManRecordUser ORDER BY cur_protected_time DESC ) m WHERE m.cur_protected_time<=0 and m.appId={appId} and m.salesManId={salesManId} and m.userId<>{userId} GROUP BY m.userId UNION SELECT * from ( SELECT *,datediff( DATE_ADD(UpdateTime,INTERVAL protected_time MINUTE),now()) as cur_protected_time from SalesManRecordUser ORDER BY cur_protected_time DESC ) m WHERE m.cur_protected_time>0 and m.appId={appId} and m.salesManId={salesManId} and m.userId<>{userId} GROUP BY m.userId) t LEFT join C_UserInfo u on t.userId=u.Id GROUP BY t.userId order by {strOrder} LIMIT {(pageIndex - 1) * pageSize},{pageSize}";
string sql = $"SELECT t.*,u.NickName,u.HeadImgUrl from (SELECT * from ( SELECT *,datediff( DATE_ADD(UpdateTime,INTERVAL protected_time Day),now()) as cur_protected_time from SalesManRecordUser ORDER BY cur_protected_time DESC ) m WHERE m.cur_protected_time<=0 and m.appId={appId} and m.salesManId={salesManId} and m.userId<>{userId} GROUP BY m.userId UNION SELECT * from ( SELECT *,datediff( DATE_ADD(UpdateTime,INTERVAL protected_time Day),now()) as cur_protected_time from SalesManRecordUser ORDER BY cur_protected_time DESC ) m WHERE m.cur_protected_time>0 and m.appId={appId} and m.salesManId={salesManId} and m.userId<>{userId} GROUP BY m.userId) t LEFT join C_UserInfo u on t.userId=u.Id GROUP BY t.userId order by {strOrder} LIMIT {(pageIndex - 1) * pageSize},{pageSize}";
if (state == 1)
{
//表示未失效的
// sql = $"SELECT t.*,u.NickName,u.HeadImgUrl from ( SELECT * from ( SELECT *,datediff( DATE_ADD(UpdateTime,INTERVAL protected_time MINUTE),now()) as cur_protected_time from SalesManRecordUser ORDER BY cur_protected_time DESC ) m WHERE m.cur_protected_time>0 and m.appId={appId} and m.salesManId={salesManId} and m.userId<>{userId} GROUP BY m.userId) t LEFT join C_UserInfo u on t.userId=u.Id order by {strOrder} LIMIT {(pageIndex - 1) * pageSize},{pageSize}";
sql = $"SELECT t.*,u.NickName,u.HeadImgUrl from ( SELECT * from ( SELECT *,datediff( DATE_ADD(UpdateTime,INTERVAL protected_time Day),now()) as cur_protected_time from SalesManRecordUser ORDER BY cur_protected_time DESC ) m WHERE m.cur_protected_time>0 and m.appId={appId} and m.salesManId={salesManId} and m.userId<>{userId} GROUP BY m.userId) t LEFT join C_UserInfo u on t.userId=u.Id order by {strOrder} LIMIT {(pageIndex - 1) * pageSize},{pageSize}";
}
if (state == 2)
{
//表示已失效的
// sql = $"SELECT t.*,u.NickName,u.HeadImgUrl from ( SELECT * from ( SELECT *,datediff( DATE_ADD(UpdateTime,INTERVAL protected_time MINUTE),now()) as cur_protected_time from SalesManRecordUser ORDER BY cur_protected_time DESC ) m WHERE m.cur_protected_time<=0 and m.appId={appId} and m.salesManId={salesManId} and m.userId<>{userId} GROUP BY m.userId) t LEFT join C_UserInfo u on t.userId=u.Id order by {strOrder} LIMIT {(pageIndex - 1) * pageSize},{pageSize}";
sql = $"SELECT t.*,u.NickName,u.HeadImgUrl from ( SELECT * from ( SELECT *,datediff( DATE_ADD(UpdateTime,INTERVAL protected_time Day),now()) as cur_protected_time from SalesManRecordUser ORDER BY cur_protected_time DESC ) m WHERE m.cur_protected_time<=0 and m.appId={appId} and m.salesManId={salesManId} and m.userId<>{userId} GROUP BY m.userId) t LEFT join C_UserInfo u on t.userId=u.Id order by {strOrder} LIMIT {(pageIndex - 1) * pageSize},{pageSize}";
}
using (var dr = SqlMySql.ExecuteDataReader(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql))
{
List <SalesManRecordUser> list = new List <SalesManRecordUser>();
while (dr.Read())
{
SalesManRecordUser salesManRecordUser = GetModel(dr);
salesManRecordUser = GetRecordUserOrderCountMoneyCps(salesManRecordUser);
salesManRecordUser.NickName = dr["NickName"].ToString();
salesManRecordUser.ImgLogo = dr["HeadImgUrl"].ToString();
salesManRecordUser.cur_protected_time = Convert.ToInt32(dr["cur_protected_time"]) > 0 ? Convert.ToInt32(dr["cur_protected_time"]):0;
list.Add(salesManRecordUser);
}
return(list.Count > 0 ? list : null);
}
}
/// <summary>
/// 更新砍价开始结束时间（批量）
/// </summary>
/// <returns></returns>
public bool BargainUser_UpdateBacth(string startdate, string enddate, string BName, int bid)
{
string sql = $"update BargainUser set StartDate='{startdate}',EndDate='{enddate}',BName='{BName}' where BId = {bid}";
int result = SqlMySql.ExecuteNonQuery(connName, CommandType.Text, sql, null);
return(result > 0);
}
/// <summary>
/// 获取累计客户条数根据条件
/// </summary>
/// <param name="strWhere"></param>
/// <returns></returns>
public int GetSalesManRecordUserCount(int appId, int salesManId, int userId, int state = 0)
{
//TODO 测试使用分钟MINUTE 线上使用天Day 分销
// string sql = $"SELECT COUNT(Id) as number from ( SELECT t.Id from (SELECT * from ( SELECT *,datediff( DATE_ADD(UpdateTime,INTERVAL protected_time MINUTE),now()) as cur_protected_time from SalesManRecordUser ORDER BY cur_protected_time DESC ) m WHERE m.cur_protected_time<=0 and m.appId={appId} and m.salesManId={salesManId} and m.userId<>{userId} GROUP BY m.userId UNION SELECT * from ( SELECT *,datediff( DATE_ADD(UpdateTime,INTERVAL protected_time MINUTE),now()) as cur_protected_time from SalesManRecordUser ORDER BY cur_protected_time DESC ) m WHERE m.cur_protected_time>0 and m.appId={appId} and m.salesManId={salesManId} and m.userId<>{userId} GROUP BY m.userId) t LEFT join C_UserInfo u on t.userId=u.Id GROUP BY t.userId) w";
string sql = $"SELECT COUNT(Id) as number from ( SELECT t.Id from (SELECT * from ( SELECT *,datediff( DATE_ADD(UpdateTime,INTERVAL protected_time Day),now()) as cur_protected_time from SalesManRecordUser ORDER BY cur_protected_time DESC ) m WHERE m.cur_protected_time<=0 and m.appId={appId} and m.salesManId={salesManId} and m.userId<>{userId} GROUP BY m.userId UNION SELECT * from ( SELECT *,datediff( DATE_ADD(UpdateTime,INTERVAL protected_time Day),now()) as cur_protected_time from SalesManRecordUser ORDER BY cur_protected_time DESC ) m WHERE m.cur_protected_time>0 and m.appId={appId} and m.salesManId={salesManId} and m.userId<>{userId} GROUP BY m.userId) t LEFT join C_UserInfo u on t.userId=u.Id GROUP BY t.userId) w";
if (state == 1)
{
//表示未失效的
// sql = $"SELECT COUNT(Id) as number from (SELECT t.Id from ( SELECT * from ( SELECT *,datediff( DATE_ADD(UpdateTime,INTERVAL protected_time MINUTE),now()) as cur_protected_time from SalesManRecordUser ORDER BY cur_protected_time DESC ) m WHERE m.cur_protected_time>0 and m.appId={appId} and m.salesManId={salesManId} and m.userId<>{userId} GROUP BY m.userId) t LEFT join C_UserInfo u on t.userId=u.Id ) w ";
sql = $"SELECT COUNT(Id) as number from (SELECT t.Id from ( SELECT * from ( SELECT *,datediff( DATE_ADD(UpdateTime,INTERVAL protected_time Day),now()) as cur_protected_time from SalesManRecordUser ORDER BY cur_protected_time DESC ) m WHERE m.cur_protected_time>0 and m.appId={appId} and m.salesManId={salesManId} and m.userId<>{userId} GROUP BY m.userId) t LEFT join C_UserInfo u on t.userId=u.Id ) w ";
}
if (state == 2)
{
//表示已失效的
// sql = $"SELECT COUNT(Id) as number from (SELECT t.Id from ( SELECT * from ( SELECT *,datediff( DATE_ADD(UpdateTime,INTERVAL protected_time MINUTE),now()) as cur_protected_time from SalesManRecordUser ORDER BY cur_protected_time DESC ) m WHERE m.cur_protected_time<=0 and m.appId={appId} and m.salesManId={salesManId} and m.userId<>{userId} GROUP BY m.userId) t LEFT join C_UserInfo u on t.userId=u.Id ) w";
sql = $"SELECT COUNT(Id) as number from (SELECT t.Id from ( SELECT * from ( SELECT *,datediff( DATE_ADD(UpdateTime,INTERVAL protected_time Day),now()) as cur_protected_time from SalesManRecordUser ORDER BY cur_protected_time DESC ) m WHERE m.cur_protected_time<=0 and m.appId={appId} and m.salesManId={salesManId} and m.userId<>{userId} GROUP BY m.userId) t LEFT join C_UserInfo u on t.userId=u.Id ) w";
}
object obj = SqlMySql.ExecuteScalar(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql);
if (obj != null)
{
return(Convert.ToInt32(obj));
}
return(0);
}
/// <summary>
/// 跟进 退款状态 (退款是否成功)
/// </summary>
/// <returns></returns>
public void StartOutOrderStateServer()
{
TransactionModel tranModel = new TransactionModel();
string sql = $@"select eo.*,r.result_code as refundcode from QiyeGoodsOrder eo left join citymorders co on eo.orderid = co.id left join ReFundResult r on r.transaction_id = co.trade_no where eo.State in ({(int)QiyeOrderState.退款中}) and eo.RefundTime <= (NOW() - interval 17 second) and eo.BuyMode = 1";//and r.result_code = 'SUCCESS' ,{(int)QiyeOrderState.退款失败}
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql, null))
{
while (dr.Read())
{
QiyeGoodsOrder model = base.GetModel(dr);
if (dr["refundcode"].ToString() == "SUCCESS")
{
model.State = (int)QiyeOrderState.退款成功;
if (base.Update(model, "state"))
{
//发给用户退款成功通知
object orderData = TemplateMsg_Miniapp.QiyeGetTemplateMessageData(model, SendTemplateMessageTypeEnum.企业智推版订单退款通知);
TemplateMsg_Miniapp.SendTemplateMessage(model.UserId, SendTemplateMessageTypeEnum.企业智推版订单退款通知, TmpType.企业智推版, orderData);
}
}
else
{
model.State = (int)QiyeOrderState.退款失败;
base.Update(model, "state");
}
}
}
}
/// <summary>
/// 退款记录报表所选时段总流水
/// </summary>
/// <returns></returns>
public double getReFundQueueReport_SumMoney(DateTime?starttime = null, DateTime?endtime = null, int pageIndex = 1, int pageSize = 50)
{
StringBuilder sb = new StringBuilder();
sb.Append($" select ifnull(sum(r.money),0) from refundqueue ");
sb.Append($" where retype = 1 "); //retype = 1 :同城退款记录
sb.Append(starttime != null ? $" and DATE_FORMAT(addtime,'%Y-%m-%d') >= DATE_FORMAT(@starttime,'%Y-%m-%d')" : "");
sb.Append(endtime != null ? $" and DATE_FORMAT(addtime,'%Y-%m-%d') <= DATE_FORMAT(@endtime,'%Y-%m-%d')" : "");
//sb.Append($" order by addtime desc LIMIT {(pageIndex - 1) * pageSize},{pageSize} ) as r "); //retype = 1 :同城退款记录
List <MySqlParameter> mysql = new List <MySqlParameter>();
if (starttime != null)
{
mysql.Add(new MySqlParameter("@starttime", starttime));
}
if (endtime != null)
{
mysql.Add(new MySqlParameter("@endtime", endtime));
}
var sumMoney = Convert.ToDouble(SqlMySql.ExecuteScalar(new ReFundQueueBLL().connName, CommandType.Text, sb.ToString(), mysql.ToArray()));
return(sumMoney);
}
public List <PlatStatistics> GetCountList(string aids)
{
List <PlatStatistics> list = new List <PlatStatistics>();
if (string.IsNullOrEmpty(aids))
{
return(list);
}
string sql = $"select count(*) count,aid from platstatistics where aid in ({aids}) group by aid";
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql, null))
{
while (dr.Read())
{
PlatStatistics model = base.GetModel(dr);
if (dr["count"] != DBNull.Value)
{
model.Count = Convert.ToInt32(dr["count"]);
}
list.Add(model);
}
}
return(list);
}
public ReFundQueueReportAmount getMoneyAmount()
{
ReFundQueueReportAmount amount = new ReFundQueueReportAmount();
//当日统计
string day_amount_sql = $" select ifnull(sum(money),0) as day_amount from refundqueue "
+ $" WHERE retype = 1 "
+ $" and date_format(addtime, '%Y-%m-%d') = date_format(now(), '%Y-%m-%d'); ";
amount.day_amount = Convert.ToDouble(SqlMySql.ExecuteScalar(Utility.dbEnum.QLWL.ToString(), CommandType.Text, day_amount_sql, new MySqlParameter[] { }));
//当周统计
string week_amount_sql = $" SELECT ifnull(sum(money),0) as week_amount FROM refundqueue "
+ $" WHERE retype = 1 "
+ $" and YEARWEEK(date_format(addtime, '%Y-%m-%d')) = YEARWEEK(date_format(now(), '%Y-%m-%d')); ";
amount.week_amount = Convert.ToDouble(SqlMySql.ExecuteScalar(Utility.dbEnum.QLWL.ToString(), CommandType.Text, week_amount_sql, new MySqlParameter[] { }));
//当月统计
string month_amount_sql = $" SELECT ifnull(sum(money),0) as month_amount FROM refundqueue "
+ $" WHERE retype = 1 "
+ $" and date_format(addtime, '%Y-%m') = date_format(now(), '%Y-%m'); ";
amount.month_amount = Convert.ToDouble(SqlMySql.ExecuteScalar(Utility.dbEnum.QLWL.ToString(), CommandType.Text, month_amount_sql, new MySqlParameter[] { }));
//string day_amount_sql = $" SELECT sum(payment_free) as day_amount FROM citymorders WHERE YEARWEEK(date_format(payment_time, '%Y-%m-%d')) = YEARWEEK(date_format('{date}', '%Y-%m-%d')); ";
//amount.day_amount = Convert.ToDouble(SqlMySql.ExecuteScalar(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, day_amount_sql, null));
return(amount);
}
/// <summary>
/// 专业版接口获取拼团列表
/// </summary>
/// <param name="rid"></param>
/// <param name="name"></param>
/// <param name="state"></param>
/// <param name="pagesize"></param>
/// <param name="pageindex"></param>
/// <returns></returns>
public List <EntGroupsRelation> GetListEntGroups_api(string ids, int rid, int storeid = 0)
{
List <MySqlParameter> parameters = new List <MySqlParameter>();
List <EntGroupsRelation> list = new List <EntGroupsRelation>();
string strSql = $@"select eg.`name`,eg.id as goodid,eg.img,eg.price,eg.stock,eg.salesCount,eg.virtualSalesCount,egr.* from entgoods eg left join entgroupsrelation egr on eg.id = egr.entgoodsid where find_in_set(eg.id,@ids) and egr.rid={rid} and egr.state=1 and egr.storeid={storeid} order by find_in_set(eg.id,@ids)";
parameters.Add(new MySqlParameter("@ids", ids));
using (MySqlDataReader dr = SqlMySql.ExecuteDataReader(connName, CommandType.Text, strSql, parameters.ToArray()))
{
while (dr.Read())
{
EntGroupsRelation model = GetModel(dr);
if (model != null)
{
model.Name = dr["name"].ToString();
if (DBNull.Value != dr["price"])
{
model.SinglePrice = float.Parse(dr["price"].ToString());
}
if (DBNull.Value != dr["stock"])
{
model.CreateNum = int.Parse(dr["stock"].ToString());
}
if (!string.IsNullOrEmpty(dr["img"].ToString()))
{
model.ImgUrl = ImgHelper.ResizeImg(dr["img"].ToString(), 750, 750);
}
int totalcount = 0;
List <object> userlist = EntGroupSponsorBLL.SingleModel.GetGoupsUserImgs(model.Id, ref totalcount, (int)TmpType.小程序专业模板, model.EntGoodsId);
//判断是否已结束
if (totalcount >= model.CreateNum)
{
model.State = 2;
}
else if ((model.ValidDateStart < DateTime.Now))
{
//判断是否开始
model.State = 1;
}
else
{
model.State = -1;
}
//已团数量
model.GroupsNum = totalcount;
model.salesCount = Convert.ToInt32(dr["salesCount"]);
model.virtualSalesCount = Convert.ToInt32(dr["virtualSalesCount"]);
list.Add(model);
}
}
}
return(list);
}
//public bool DeletCityInfo(int cityinfoid, Guid accid, string nick)
//{
// var city = GetModel(cityinfoid);
// if (null == city)
// return false;
// Delete(cityinfoid);
// RedisUtil.Remove(string.Format(CityInfoCacheKey, city.AreaCode));
// // 添加操作日志
// var log = new C_OperateLog
// {
// AccountId = accid,
// CreateDate = DateTime.Now,
// Remark = nick + "删除同城,Id=" + cityinfoid
// };
// //new C_OperateLogBLL().Add(log);
// //var bllSubCityInfo = new C_CitySubAreaBLL();
// //子区域
// //bllSubCityInfo.Delete($"CityInfoId={cityinfoid}");
// //角色
// //new C_UserRoleBLL().Delete($"CityInfoId={cityinfoid}");
// //论坛
// var bllforum = new MinisnsBll();
// var forum = bllforum.GetModel(city.MiniSnsId);
// forum.mprices = 0;
// bllforum.Update(forum, "mprices");
// return true;
//}
//public string AddICityInfo(C_UserInfo userInfo, DateTime endTime,int regionCode)
//{
// try
// {
// var bllCityInfo = new C_CityInfoBLL();
// int areaCode = 100001001;
// var where = $"SELECT max(areacode) areacode from c_cityinfo";
// var newst = bllCityInfo.GetListBySql(where).FirstOrDefault();
// if (null != newst)
// {
// areaCode = newst.AreaCode + 1;
// }
// var cityforuminfo = new C_CityInfo
// {
// AreaCode = areaCode,
// IsThirdTools = 2,
// CName = userInfo.NickName + "-分类信息小程序",
// CreateDate = DateTime.Now,
// EndDate = endTime,
// LogoUrl = userInfo.HeadImgUrl,
// OpenId = userInfo.OpenId,
// UnionId = userInfo.UnionId,
// State = 0,
// RegionCode = regionCode
// };
// var cityinfoid = Convert.ToInt32(bllCityInfo.Add(cityforuminfo));
// //添加权限
// var role = new C_UserRole
// {
// CityInfoId = cityinfoid,
// AreaCode = cityforuminfo.AreaCode,
// OpenId = cityforuminfo.OpenId,
// UnionId = cityforuminfo.UnionId,
// UserState = 0,
// RoleId = 1
// };
// //new C_UserRoleBLL().Add(role);
// }
// catch (Exception ex)
// {
// log4net.LogHelper.WriteError(this.GetType(), ex);
// return "内部错误，请查看错误日志";
// }
// return string.Empty;
//}
#region 城市切换-蔡华兴
/// <summary>
/// 获取有同城信息城市数据
/// </summary>
/// <returns></returns>
public List <C_CityInfo> GetCityInfo()
{
List <C_CityInfo> resultList = new List <C_CityInfo>();
string strSql = @"select * from (
select area.level,area.name as areaname
,area.pingyin as py,area.code
,(select cityinfo.id from c_cityinfo cityinfo where (area.level=2 and left(cityinfo.AreaCode,4) = left(area.code,4)) or left(cityinfo.AreaCode,6)=left(area.code,6) LIMIT 0,1) as areaCount
from c_area area where area.level BETWEEN 2 and 3
) result where result.areaCount>0 ORDER BY result.code;";
using (MySqlDataReader dr = SqlMySql.ExecuteDataReader(connName, CommandType.Text, strSql, null))
{
while (dr.Read())
{
var model = new C_CityInfo {
CName = dr["areaname"].ToString()
};
if (dr["code"] != DBNull.Value)
{
model.AreaCode = int.Parse(dr["code"].ToString());
}
model.ConfigJson = dr["py"].ToString();
resultList.Add(model);
}
}
return(resultList);
}
//删除商品多规格关系表
public int DelAttrList(int foodgoodsid)
{
string sql = $"delete from foodgoodsattrspec where foodgoodsid={foodgoodsid}";
int result = SqlMySql.ExecuteNonQuery(connName, CommandType.Text, sql, null);
return(result);
}
/// <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);
}
/// <summary>
/// 获取累计客户的订单成交数量-成交总额-成交总佣金
/// </summary>
/// <returns></returns>
public SalesManRecordUser GetRecordUserOrderCountMoneyCps(SalesManRecordUser salesManRecordUser)
{
int aid = salesManRecordUser.appId;
int userId = salesManRecordUser.userId;
int salesManId = salesManRecordUser.salesManId;
string sql = $"SELECT COUNT(car.Id) as number,SUM(car.price*.car.Count) as totalPrice,SUM(car.price*car.cps_rate*car.Count) as cpsMoney from entgoodscart car LEFT JOIN entgoodsorder o on car.goodsorderId=o.id where car.userId={salesManRecordUser.userId} and car.salesManRecordUserId in(SELECT Id from salesmanrecorduser where userId = {salesManRecordUser.userId} and appId = {salesManRecordUser.appId} and salesmanId = {salesManRecordUser.salesManId}) and o.State=3";
using (var dr = SqlMySql.ExecuteDataReader(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql))
{
while (dr.Read())
{
salesManRecordUser = GetModel(dr);
// salesManRecordUser.orderCount = dr["number"] == DBNull.Value ? 0 : Convert.ToInt32(dr["number"]);
salesManRecordUser.orderMoneyStr = dr["totalPrice"] == DBNull.Value ? "0.00" : (Convert.ToDouble(dr["totalPrice"]) * 0.01).ToString("0.00");
salesManRecordUser.cpsMoneyStr = dr["cpsMoney"] == DBNull.Value ? "0.00" : (Convert.ToDouble(dr["cpsMoney"]) * 0.0001).ToString("0.00");
}
}
sql = $"SELECT count(goodsorderid) from( SELECT goodsorderid from entgoodscart car LEFT JOIN entgoodsorder o on car.goodsorderId=o.id where car.userId={userId} and car.salesManRecordUserId in(SELECT Id from salesmanrecorduser where userId = {userId} and appId = {aid} and salesmanId ={salesManId}) and o.State=3 GROUP BY goodsorderid) c";
object obj = SqlMySql.ExecuteScalar(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql);
if (obj != DBNull.Value)
{
salesManRecordUser.orderCount = Convert.ToInt32(obj);
}
return(salesManRecordUser);
}
/// <summary>
/// 获取店铺销售成交金额
/// </summary>
/// <param name="storeid">店铺ID</param>
/// <param name="startdate">开始时间</param>
/// <param name="enddate">结束时间</param>
/// <returns></returns>
public int GetStoreInCome(int storeid, string startdate, string enddate)
{
string sql = $@"select (select sum(buyprice) from storegoodsorder where storeid = {storeid} and state ={(int)OrderState.已收货} and paydate>='{startdate}' and paydate<='{enddate}') as goodprice,
(select sum(gu.BuyPrice) from groupuser gu
left join groups g on gu.GroupId = g.Id
where g.storeid = {storeid} and gu.state = {(int)MiniappPayState.已收货} and gu.RecieveGoodTime>='{startdate}' and gu.RecieveGoodTime<='{enddate}') as groupprice,
(select sum(bu.CurrentPrice) from bargainuser bu left join bargain b on bu.BId = b.Id where b.storeid = {storeid} and bu.state = 8 and bu.ConfirmReceiveGoodsTime>='{startdate}' and ConfirmReceiveGoodsTime<='{enddate}') as bargainprice";
int sum = 0;
using (MySqlDataReader dr = SqlMySql.ExecuteDataReaderMaster(connName, CommandType.Text, sql))
{
while (dr.Read())
{
if (DBNull.Value != dr["goodprice"])
{
sum += Convert.ToInt32(dr["goodprice"]);
}
if (DBNull.Value != dr["groupprice"])
{
sum += Convert.ToInt32(dr["groupprice"]);
}
if (DBNull.Value != dr["bargainprice"])
{
sum += Convert.ToInt32(dr["bargainprice"]);
}
}
}
return(sum);
}
/// <summary>
/// 根据订单号查询该订单下的产品绑定关系
/// </summary>
/// <param name="orderId"></param>
/// <returns></returns>
public List <RelationViewModel> GetRelationSearch(int orderId)
{
string sql = $"SELECT u.NickName,s.TelePhone,r.Id as relationFlag,r.UpdateTime as StartTime, DATE_ADD(r.UpdateTime,INTERVAL r.protected_time Day) as endTime from entgoodsorder o LEFT JOIN entgoodscart car on o.Id=car.GoodsOrderId LEFT JOIN salesmanrecorduser r on r.Id=car.salesManRecordUserId LEFT JOIN salesman s on s.Id=r.salesmanId LEFT JOIN c_userinfo u on u.Id=car.userId where o.Id={orderId}";
using (var dr = SqlMySql.ExecuteDataReader(Utility.dbEnum.MINIAPP.ToString(), CommandType.Text, sql))
{
List <RelationViewModel> list = new List <RelationViewModel>();
while (dr.Read())
{
RelationViewModel relationViewModel = new RelationViewModel();
relationViewModel.orderUserName = Convert.ToString(dr["NickName"]);
relationViewModel.saleManTelephone = dr["TelePhone"] == DBNull.Value ? "-" : Convert.ToString(dr["TelePhone"]);
relationViewModel.relationFlag = dr["relationFlag"] == DBNull.Value ? 0 : Convert.ToInt32(dr["relationFlag"]);
relationViewModel.relationConnectTime = dr["StartTime"] == DBNull.Value ? "-" : Convert.ToString(dr["StartTime"]);
relationViewModel.relationEndTime = dr["endTime"] == DBNull.Value ? "-" : Convert.ToString(dr["endTime"]);
if (relationViewModel.relationEndTime != "-")
{
if (Convert.ToDateTime(relationViewModel.relationEndTime) > DateTime.Now)
{
relationViewModel.state = "绑定";
}
}
list.Add(relationViewModel);
}
return(list);
}
}
public int GetPVCount(int?aid, string appid = "", string startime = "", string endtime = "")
{
if (aid == null || aid <= 0)
{
return(0);
}
string sql = $"select sum(VisitPV) from PlatStatisticalFlow where aid={aid} ";
if (!string.IsNullOrEmpty(appid))
{
sql += $" and appid='{appid}'";
}
if (!string.IsNullOrEmpty(startime))
{
sql += $" and date_format(refdate, '%Y-%m-%d')>='{startime}'";
}
if (!string.IsNullOrEmpty(endtime))
{
sql += $" and date_format(refdate, '%Y-%m-%d')<'{endtime}'";
}
object result = SqlMySql.ExecuteScalar(connName, CommandType.Text, sql, null);
if (DBNull.Value != result)
{
return(Convert.ToInt32(result));
}
return(0);
}
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);
}
