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); }