//判断权限 public int GetLogins(string name) { List <UserLogin> list = null; int result = 0; try { string str = "select U_Impower from UserLogin where U_Name = @U_Name"; var args = new DynamicParameters(); args.Add("@U_Name", name); list = DapperHelper <UserLogin> .Query(str, args); result = list[0].U_Impower; } catch (Exception e) { Logger.Error(e.TargetSite.ToString()); } return(result); }
/// <summary> /// 获取兑换的各类商品的比例 /// </summary> /// <param name="stDate"></param> /// <param name="edDate"></param> /// <returns></returns> public List <IntPie> GetExRatio(DateTime stDate, DateTime edDate) { List <IntPie> dic = new List <IntPie>(); StringBuilder strSql = new StringBuilder(); strSql.Append("select eProjectName Name, count(*) Value " + "from [i200].[dbo].[T_ExchangeLog] " + "where eInsertTime<@edDate and eInsertTime>@stDate group by eProjectName;"); try { dic = DapperHelper.Query <IntPie>(strSql.ToString(), new { stDate = stDate, edDate = edDate }).ToList(); } catch (Exception ex) { return(dic); } return(dic); }
public List <MobileMapModel> SeperateModels(string where) { StringBuilder strSql = new StringBuilder(); strSql.Append(" select id,gName into #list from OTwoO.dbo.GoodsInfo;"); strSql.Append(" select * into #info from #list inner join ( "); strSql.Append(" select gid,accid,accountName,maxClass,minClass,goodsNum,price from OTwoO.dbo.GoodsList where "); strSql.Append(" gid in(select id from #list)) a on a.gid=#list.id; "); strSql.Append( " select * into #all from #info left join (select picUrl,gid as goodsId from OTwoO.dbo.goodsPic) g on g.goodsId=#info.gid;"); strSql.Append(" select top 50000 * from #all inner join( "); strSql.Append(" select accountid,TrueConty from i200.dbo.T_Account_User where "); strSql.Append(" accountid in(select accid from #info) and grade='管理员' and TrueConty is not null) a "); strSql.Append(" on a.accountid=#all.accid left join ( select companyaddress,phonenumber,id from i200.dbo.T_Account " + where + " ) t on t.ID=#all.accid left join ( select accid aid,aa_ShortUrl as ShortUrl from i200.dbo.t_App_Au where appName='HTML' ) s on #all.accid=s.aid where companyaddress is not null;"); strSql.Append(" drop table #list; "); strSql.Append(" drop table #info; "); strSql.Append(" drop table #all; "); return(DapperHelper.Query <MobileMapModel>(strSql.ToString()).ToList()); }
/// <summary> /// 获取符合条件的推送用户列表 /// 符合条件用户的公告型消息 /// </summary> /// <param name="id"></param> /// <returns></returns> public List <string> GetPushUserList(int id) { StringBuilder strSql = new StringBuilder(); switch (id) { case 1: //strSql.Append("select ID from i200.dbo.T_Account where LoginTimeLast between CAST((GETDATE()-3) as date) and (CAST(GETDATE() as date)) " + // "and ID in (select distinct accid from [I200].[dbo].[T_Token_Api] where createTime between CAST((GETDATE()-3) as date) and (CAST(GETDATE() as date)));"); //移动端三天内登录过用户 strSql.Append( "select distinct Accountid from i200.dbo.T_LOG where DATEDIFF(DAY,OperDate,GETDATE())<=3 and DATEDIFF(DAY,OperDate,GETDATE())>=0 and LogMode=4 "); strSql.Append(" union "); strSql.Append( " select ID from i200.dbo.T_Account where State=1 and DATEDIFF(DAY,RegTime,GETDATE())<=3 and DATEDIFF(DAY,RegTime,GETDATE())>=0;"); break; default: return(null); } return(DapperHelper.Query <string>(strSql.ToString()).ToList()); }
/// <summary> /// 页面获取条件组 /// </summary> /// <param name="verif"></param> /// <returns></returns> public List <ConditionSettingModel.ConditionRecoveryModel> GetRuleCondition(string verif) { StringBuilder strSql = new StringBuilder(); strSql.Append( "select rl.Remark,rl.RuleId,rl.MaxValue,rl.MinValue,rl.RangeData,rd.ColDesc,rl.Id,rd.ColName,rd.ConditionGroup,rd.ConditionType " + "from Sys_RuleList rl left join Sys_RuleDetail rd " + "on rl.RuleId=rd.Id " + "where VerifId=@verif;"); try { return(DapperHelper.Query <ConditionSettingModel.ConditionRecoveryModel>(strSql.ToString(), new { verif = verif }).ToList()); } catch (Exception ex) { Logger.Error("在消息发送页面获取筛选器条件出错!", ex); return(null); } }
/// <summary> /// 获取每日店铺支付宝收单信息 /// </summary> /// <param name="stDate"></param> /// <param name="edDate"></param> /// <returns></returns> public List <ShopAlipay> GetDailyShopAlipayList(DateTime stDate, DateTime edDate) { StringBuilder strSql = new StringBuilder(); strSql.Append( "select cast(CreateTime as date) DayDate,AccId,count(SalesId) SaleCount,sum(TotalMoney) SaleMoney " + "from [i200].[dbo].[T_Sale_Payment] " + "where PayStatus>=8 and CreateTime between @stDate and @edDate " + "group by AccId,cast(CreateTime as date)"); try { return(DapperHelper.Query <ShopAlipay>(strSql.ToString(), new { stDate = stDate, edDate = edDate }).ToList()); } catch (Exception ex) { return(null); } }
/// <summary> /// 获取每日支付宝交易店铺汇总信息(附加微信数据) /// </summary> /// <param name="stDate"></param> /// <param name="edDate"></param> /// <returns></returns> public List <dynamic> GetDailySummaryAboutAlipay(DateTime stDate, DateTime edDate) { StringBuilder strSql = new StringBuilder(); strSql.Append( "select cast(CreateTime as date) dayDate,Count(distinct AccId) AccIdCount,count(SalesId) SaleCount,sum(TotalMoney) SaleMoney " + "into #Alipay from [i200].[dbo].[T_Sale_Payment] " + "where PayStatus>=8 and CreateTime between @stDate and @edDate " + "group by cast(CreateTime as date);" + "select cast(bInsertTime as date) dayDate,Count(distinct AccId) wAccIdCount,count(bid) wSaleCount,sum(bRealMoney) wSaleMoney " + "into #weixin from [I200].[dbo].[T_Goods_Booking] where bState in (1,2,3,4) and payType=1 " + "and bInsertTime between @stDate and @edDate " + "group by cast(bInsertTime as date);" + "select #Alipay.*,#weixin.wAccIdCount,#weixin.wSaleCount,#weixin.wSaleMoney " + "from #Alipay left join #weixin " + "on #Alipay.dayDate=#weixin.dayDate;" + "drop table #Alipay;" + "drop table #weixin;"); //"select cast(CreateTime as date) dayDate,Count(distinct AccId) AccIdCount,count(SalesId) SaleCount,sum(TotalMoney) SaleMoney " + //"from [i200].[dbo].[T_Sale_Payment] " + //"where PayStatus>=8 and CreateTime between @stDate and @edDate " + //"group by cast(CreateTime as date)" try { return(DapperHelper.Query <dynamic>(strSql.ToString(), new { stDate = stDate, edDate = edDate }).ToList()); } catch (Exception ex) { Logger.Error("获取店铺支付宝收款详情出错!", ex); return(null); } }
/// <summary> /// 获得数据列表 /// </summary> /// <param name="top">前几行</param> /// <param name="columnName">需要获取的列名<para>为了方便不在处理,列与列用逗号分开,参照SQL写法</para></param> /// <param name="dapperWheres">查询条件列表</param> /// <param name="filedOrder">排序</param> public List <T> GetList <T>(int top, string columnName, List <DapperWhere> dapperWheres, string filedOrder) { StringBuilder strSql = new StringBuilder(); string where = ""; Dictionary <string, object> parm = new Dictionary <string, object>(); foreach (DapperWhere item in dapperWheres) { if (where.Length > 0) { where += " and "; } where += item.Where; parm[item.ColumnName] = item.Value; } strSql.Append("select "); if (top > 0) { strSql.Append(" top " + top.ToString() + " "); } strSql.Append(" " + columnName + " "); strSql.Append(" FROM Sys_DailyCheck "); if (where.Length > 0) { strSql.Append(" where " + where); } if (filedOrder.Length > 0) { strSql.Append(" order by " + filedOrder); } strSql.Append(";"); return(DapperHelper.Query <T>(strSql.ToString(), parm).ToList()); }
/// <summary> /// 获取单个用户发送的用户模型列表 /// </summary> /// <param name="id"></param> /// <returns></returns> public List <SingleShopInfoForMsg> GetSingleUserMsg(int id) { StringBuilder strSql = new StringBuilder(); switch (id) { case 2: //付费功能到期(三天和一天) //strSql.Append("") strSql.Append(""); break; case 3: //优惠券到期(三天或一天) strSql.Append( "select ci.couponDesc CouponName,ta.CompanyName,ta.ID Accid,DATEDIFF(DAY,getdate(),cl.endDate) Interval from [I200].[dbo].[T_Order_CouponList] cl " + "left join i200.dbo.T_Account ta on cl.toAccId=ta.ID " + "left join i200.dbo.T_Order_CouponInfo ci on cl.groupId=ci.id " + "where cl.couponStatus=2 and (DATEDIFF(DAY,getdate(),cl.endDate)=3 or DATEDIFF(DAY,getdate(),cl.endDate)=1);"); break; case 4: //获取周年用户 strSql.Append( "select DATEDIFF(YEAR,regtime,getdate()) Interval,ID Accid,CompanyName from [i200].[dbo].[T_Account] " + "where DATEPART(MONTH,RegTime)=DATEPART(MONTH,getdate()) and DATEPART(day,RegTime)=DATEPART(day,getdate()) and DATEDIFF(MONTH,RegTime,GETDATE())>0 and state=1"); break; } try { return(DapperHelper.Query <SingleShopInfoForMsg>(strSql.ToString()).ToList()); } catch (Exception ex) { Logger.Error("获取消息发送对象列表出错!", ex); return(new List <SingleShopInfoForMsg>());//异常则返回空列表 } }
public List <HourAnalysisItemList> GetLogRegTimeReportSourceDAL(int timeType) { DateTime startTime = DateTime.Now; DateTime endTime = DateTime.Now; startTime = startTime.AddDays(0 - Math.Abs(Convert.ToInt32(timeType))); StringBuilder strSql = new StringBuilder(); strSql.Append(" select cast((DATENAME(HOUR,OperDate))as int) [hour],COUNT(DISTINCT Accountid) [num] "); strSql.Append(" from i200.dbo.T_LOG "); strSql.Append(" where OperDate>@statTime and OperDate<@endTime "); strSql.Append(" group by DATENAME(HOUR,OperDate) "); strSql.Append(" order by hour asc "); List <HourAnalysisItemList> dynamicList = DapperHelper.Query <HourAnalysisItemList>(strSql.ToString(), new { statTime = startTime, endTime = endTime }).ToList(); return(dynamicList); //foreach (dynamic itemList in dynamicList) //{ // HourAnalysisItemList item = new HourAnalysisItemList(); // item.hour = itemList.hour; // item.num = itemList.num; //} }
public AgentList GetAgentList(int pageIndex) { AgentList agentModel = new AgentList(); int bgNumber = ((pageIndex - 1) * 15) + 1; int edNumber = (pageIndex) * 15; StringBuilder strSql = new StringBuilder(); strSql.Append("select * from (select row_number() over (order by ID desc) rowNumber,* from Sys_agent_mess) t where t.rowNumber between @bgNumber and @edNumber; "); agentModel.list = DapperHelper.Query <AgentModel>(strSql.ToString(), new { bgNumber = bgNumber, edNumber = edNumber }) .ToList(); strSql.Clear(); strSql.Append("select count(*) from Sys_agent_mess;"); agentModel.rowCount = DapperHelper.ExecuteScalar <int>(strSql.ToString()); agentModel.maxPage = (agentModel.rowCount % 15 == 0) ? agentModel.rowCount / 15 : (agentModel.rowCount / 15 + 1); return(agentModel); }
/// <summary> /// 每种商品的访客和兑换比 /// </summary> /// <param name="stDate"></param> /// <param name="edDate"></param> /// <returns></returns> public List <DecimalPie> GetVisitRatio(DateTime stDate, DateTime edDate) { List <DecimalPie> dic = new List <DecimalPie>(); StringBuilder strSql = new StringBuilder(); strSql.Append( "select ProductName Name,sum(Ratio)/count(*) Value " + "from [Sys_I200].[dbo].[Sys_DailyIntegralExchange] " + "where Daydate between @stDate and @edDate group by ProductName;"); try { dic = DapperHelper.Query <DecimalPie>(strSql.ToString(), new { stDate = stDate, edDate = edDate }).ToList(); } catch (Exception ex) { return(dic); } return(dic); }
/// <summary> /// 得到短信详情 /// </summary> /// <param name="noticeid">标记ID</param> /// <param name="accid">店铺ID</param> /// <param name="page">分页</param> /// <param name="pageSiz">每页面显示行</param> /// <param name="phone">电话</param> /// <returns></returns> public SmsDetail GetSmsDetailList(int noticeid, int accid = 0, int page = 1, int pageSiz = 50, string phone = "") { List <SmsDetails> detailList = new List <SmsDetails>(); SmsDetail details = new SmsDetail(); //页数计算 int bgNumber = ((page - 1) * pageSiz) + 1; int edNumber = (page) * pageSiz; StringBuilder strWhere = new StringBuilder(); strWhere.Append(" notifyID=@notifyID "); if (accid > 0) { strWhere.Append(" and accID=@accid "); } if (phone.Length > 0) { strWhere.Append(" and phoneNum like '%'+ @phone +'%'"); } StringBuilder strSql = new StringBuilder(); strSql.Append("select * from(SELECT ROW_NUMBER() over(order by id desc) as rowNumber, " + "id, " + "phoneNum," + "cast(smsChannel as varchar(100)) as smsChannel, " + "smsContent,LEN(smsContent) as ContentLength, " + "sendtime, " + "cast(smsStatus as varchar(100)) as smsStatus," + "userID, " + "realCnt, " + "isFree, " + "errDesc," + "smsReport " + " FROM I200.dbo.T_Sms_List where " + strWhere.ToString() + ") t where t.rowNumber between @startIndex and @endIndex;"); detailList = DapperHelper.Query <SmsDetails>(strSql.ToString(), new { accid = accid, notifyID = noticeid, phone = phone, startIndex = bgNumber, endIndex = edNumber }).ToList(); strSql.Clear(); strSql.Append("SELECT count(id) as smsCnt,'' as moreStr FROM I200.dbo.T_Sms_List where " + strWhere.ToString() + " ; "); details = DapperHelper.Query <SmsDetail>(strSql.ToString(), new { accid = accid, notifyID = noticeid, phone = phone, startIndex = bgNumber, endIndex = edNumber }).ToList()[0]; details.detailList = detailList; return(details); }
/// <summary> /// 根据业务Id计算相关产品的成本和利润 /// </summary> /// <param name="busId"></param> /// <param name="accId"></param> /// <returns></returns> /// 所有打包产品都算Saas服务 /// 单独的短信包和维客短信是第三方服务 /// 话费充值和实物产品算他营产品 public OrderItemProp GetListItemProps(int busId, int accId) { StringBuilder strSql = new StringBuilder(); OrderItemProp propModel = new OrderItemProp(); strSql.Append("select * from [i200].[dbo].[T_Order_Project_List] where busId=@busId;"); try { List <dynamic> projectList = DapperHelper.Query <dynamic>(strSql.ToString(), new { busId = busId }).ToList(); //独立产品的处理 if (projectList.Count == 1) { dynamic model = projectList[0]; //处理短信 if (model.itemId == 1) { propModel.Cost = Convert.ToInt32(model.itemQuantity) * 0.05; //标记为第三方服务 propModel.PureSms = "1"; } //处理维客短信 else if (model.itemName.ToString().IndexOf("维客") >= 0) { T_Sms_ListDAL dal = new T_Sms_ListDAL(); double smsCnt = dal.GetFreeSmsCnt(accId); propModel.Cost = smsCnt * 0.05; propModel.isFreeSms = 1; //标记为第三方服务 propModel.PureSms = "1"; } else if (model.itemName.ToString().IndexOf("充值100") >= 0) { propModel.Cost = 100; } else if (model.itemName.ToString().IndexOf("充值50") >= 0) { propModel.Cost = 50; } else if (model.itemName.ToString().IndexOf("充值30") >= 0) { propModel.Cost = 30; } else { propModel.Cost = 0; propModel.SelfMark = "1"; } } //组合产品的处理 else if (projectList.Count > 1) { dynamic model = projectList[0]; //propModel.SelfMark = "1"; //处理维客类套餐 if (model.itemName.ToString().IndexOf("维客") >= 0) { T_Sms_ListDAL dal = new T_Sms_ListDAL(); double smsCnt = dal.GetFreeSmsCnt(accId); propModel.Cost = smsCnt * 0.06; propModel.isFreeSms = 1; //标记为第三方服务 propModel.PureSms = "1"; } else { foreach (var item in projectList) { if (item.itemId == 1) { propModel.Cost += Convert.ToInt32(model.itemQuantity) * 0.06; } } propModel.SelfMark = "1"; } } //空列表 else { } return(propModel); } catch (Exception ex) { Logger.Error("获取用户详情订单摘要信息出错", ex); return(propModel); } }
public Dictionary <string, List <DecimalPie> > GetPaidType(DateTime stDate, DateTime edDate) { Dictionary <string, List <DecimalPie> > dataDic = new Dictionary <string, List <DecimalPie> >(); List <DecimalPie> dic = new List <DecimalPie>(); List <DecimalPie> dicSec = new List <DecimalPie>(); List <DecimalPie> dicAppend = new List <DecimalPie>(); StringBuilder strSql = new StringBuilder(); strSql.Append("select Remark Name," + "sum( case when (cast(FinialVal as int)>cast(OriginalVal as int)) then cast(EditVal as int) else 0 end ) Value " + "FROM [i200].[dbo].[T_LogInfo] where CreatTime>@stDate and CreatTime<@edDate and Keys='Integral' and LogType=17 group by Remark;"); dicSec = DapperHelper.Query <DecimalPie>(strSql.ToString(), new { stDate = stDate, edDate = edDate }).ToList(); dicSec.RemoveAll(x => x.Value == 0); strSql.Clear(); strSql.Append( "SELECT LogType Name," + "sum( case when (cast(FinialVal as int)>cast(OriginalVal as int)) then cast(EditVal as int) else 0 end ) Value " + "FROM [i200].[dbo].[T_LogInfo] where CreatTime>@stDate and CreatTime<@edDate and Keys='Integral' group by LogType;"); try { dic = DapperHelper.Query <DecimalPie>(strSql.ToString(), new { stDate = stDate, edDate = edDate }).ToList(); foreach (DecimalPie item in dic) { switch (item.Name) { case "1": item.Name = "积分兑换"; break; case "2": item.Name = "抽奖活动"; break; case "4": item.Name = "订单购买"; break; case "5": item.Name = "支持生意专家"; break; case "6": item.Name = "分享生意专家 完善资料 分享视频"; break; case "7": item.Name = "每日签到"; break; case "8": item.Name = "每日心情"; break; case "9": item.Name = "关注微信"; break; case "10": item.Name = "论坛金币兑换"; break; case "11": item.Name = "完成会员引导"; break; case "12": item.Name = "完成商品引导"; break; case "13": item.Name = "完成销售引导"; break; case "14": item.Name = "邀请注册"; break; case "16": item.Name = "新手任务"; break; case "17": item.Name = "每日行为"; break; } } dic.RemoveAll(x => x.Value == 0); } catch (Exception ex) { return(null); } foreach (DecimalPie item in dic) { dicAppend.Add(item); } dicAppend.RemoveAll(x => x.Name == "每日行为"); foreach (DecimalPie item in dicSec) { dicAppend.Add(item); } dataDic.Add("fData", dic); dataDic.Add("sData", dicAppend); return(dataDic); }
/// <summary> /// 绑定等级下拉 /// </summary> /// <returns></returns> public List <ShenLevel> GetLevel() { return(DapperHelper.Query <ShenLevel>("select * from slevel", null)); }
/// <summary> /// 得到优惠券列表 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="dapperWheres"></param> /// <returns></returns> public new List <OrderCouponInfoListItem> GetList(int pageIndex, int pageSize, List <DapperWhere> dapperWheres, string filedOrder = "") { StringBuilder strSql = new StringBuilder(); string where = ""; Dictionary <string, object> parm = new Dictionary <string, object>(); foreach (DapperWhere item in dapperWheres) { if (where.Length > 0) { where += " and "; } where += item.Where; parm[item.ColumnName] = item.Value; } if (filedOrder == "") { filedOrder = " id desc"; } strSql.Append(" SELECT id,couponDesc,maxLimitNum,createDate,endDate,operatorId,remark,couponStatus into #list FROM ( "); strSql.Append(" SELECT ROW_NUMBER() OVER ( "); if (filedOrder.Length > 0) { strSql.Append(" order by " + filedOrder + " "); } else { strSql.Append(" order by T.id desc"); } strSql.Append(" )AS Row, * from I200.dbo.T_Order_CouponInfo T "); if (where.Length > 0) { strSql.Append(" where " + where + " "); } strSql.Append(" ) TT "); strSql.Append(" WHERE TT.Row between @startIndex and @endIndex; "); strSql.Append(" select #list.id,couponDesc,maxLimitNum,createDate,endDate,operatorId,isnull(b.name,'系统') opertorName, "); strSql.Append(" remark,couponStatus,isnull(produceNum,0)produceNum,isnull(bindingNum,0)bindingNum,isnull(useNum,0) useNum "); strSql.Append(" from #list left outer join( "); strSql.Append(" select groupId,COUNT(id) produceNum,sum(case when toAccId>0 then 1 else 0 end) bindingNum,SUM(case when useAccId>0 then 1 else 0 end) useNum "); strSql.Append(" from i200.dbo.T_Order_CouponList where groupId in(select id from #list) group by groupId "); strSql.Append(" ) a on a.groupId=#list.id left outer join( "); strSql.Append(" select Id,name from Sys_Manage_User "); strSql.Append(" where Id in(select operatorId from #list)) b on b.Id=#list.operatorId "); strSql.Append(" drop table #list "); if (pageIndex < 1) { pageIndex = 1; } int bgNumber = ((pageIndex - 1) * pageSize) + 1; int edNumber = (pageIndex) * pageSize; parm["startIndex"] = bgNumber; parm["endIndex"] = edNumber; return(DapperHelper.Query <OrderCouponInfoListItem>(strSql.ToString(), parm).ToList()); }
/// <summary> /// 查询项目详情 /// </summary> /// <returns></returns> public List <ProjectInfo> ProjectDetail(int ProjectId) { string sql = "select * from ProjectInfo where ProjectId=@projectId and ProjectStaus=1"; return(DapperHelper <ProjectInfo> .Query(sql, new { projectId = ProjectId })); }
public Dictionary <string, string> GetTagByCondition(int pageIndex, int tagType, string insertName = "", string tagName = "") { Dictionary <string, string> dict = new Dictionary <string, string>() { { "list", "" }, { "PageCount", "" }, { "RowCount", "" }, }; StringBuilder strSql = new StringBuilder(); string where = " 1=1 "; int bgNumber = ((pageIndex - 1) * 15) + 1; int edNumber = (pageIndex) * 15; if (tagType != -99) { where += " and tagTypeid=" + tagType + " "; } if (!string.IsNullOrEmpty(insertName)) { where += " and insertName like '%" + insertName + "%' "; } if (!string.IsNullOrEmpty(tagName)) { where += " and t_Name like '%" + tagName + "%' "; } where += " and tagStatus=1 "; strSql.Append("select * from ( select *,row_number() over (order by id) rowNumber from Sys_TagInfo where" + where + ") t where t.rowNumber between @bgNumber and @edNumber;"); List <Sys_TagInfoBasic> list = DapperHelper.Query <Sys_TagInfoBasic>(strSql.ToString(), new { bgNumber = bgNumber, edNumber = edNumber }) .ToList(); //dict["list"] = // CommonLib.Helper.JsonSerializeObject( // , "yyyy-mm-dd HH:mm:ss"); strSql.Clear(); strSql.Append("select tag_id,count(*) countNum from [sys_i200].[dbo].[Sys_TagNexus] group by tag_id;"); List <dynamic> tagList = DapperHelper.Query <dynamic>(strSql.ToString()).ToList(); foreach (Sys_TagInfoBasic item in list) { try { item.AccidCount = Convert.ToInt32(tagList.Find(x => x.tag_id == item.id).countNum); } catch (Exception ex) { item.AccidCount = 0; } } strSql.Clear(); strSql.Append("select count(*) from Sys_TagInfo where" + where); int rowCount = DapperHelper.ExecuteScalar <int>(strSql.ToString()); dict["list"] = CommonLib.Helper.JsonSerializeObject(list, "yyyy-mm-dd HH:mm:ss"); dict["RowCount"] = rowCount.ToString(); dict["PageCount"] = (rowCount % 15 == 0 ? rowCount / 15 : (rowCount / 15 + 1)).ToString(); return(dict); }
public chartDataModel GetAgentStat(string stDate, string edDate, string[] cols) { DateTime st = Convert.ToDateTime(stDate); DateTime ed = Convert.ToDateTime(edDate); StringBuilder strSql = new StringBuilder(); StringBuilder strColumn = new StringBuilder(); StringBuilder strDrop = new StringBuilder(); Dictionary <string, string> ColumnList = new Dictionary <string, string>(); strSql.Append("declare @Reg int,@Act int,@Loyal int;"); strColumn.Append("select cast(@date as date) dayDate"); if (cols.Contains("reg")) { strColumn.Append(",@Reg Reg"); ColumnList.Add("Reg", "注册用户"); strSql.Append("select @Reg=COUNT(*) from i200.dbo.T_Account where agentID>0 and state=1 and cast(RegTime as date) <=@date;"); } if (cols.Contains("active")) { strColumn.Append(",@Act Active"); ColumnList.Add("Active", "活跃用户"); strSql.Append("select distinct accid into #actList from [Sys_I200].[dbo].[SysRpt_ShopActive] " + "where DATEDIFF(DAY,@date,updatetime)<lastNum and DATEDIFF(DAY,@date,updatetime)>=0 and active>4;" + "select @Act=COUNT(*) from i200.dbo.T_Account where AgentId>0 and state=1 and ID in (select accid from #actList);"); strDrop.Append("drop table #actList;"); } //if (cols.Contains("loyal")) //{ // strColumn.Append(",@Loyal Loyal"); // ColumnList.Add("Loyal", "忠诚用户"); // strSql.Append("select distinct accid into #loyList from [Sys_I200].[dbo].[SysRpt_ShopActive] " + // "where DATEDIFF(DAY,@date,updatetime)<lastNum and DATEDIFF(DAY,@date,updatetime)>0 and active=7;" + // "select @Loyal=COUNT(*) from i200.dbo.T_Account where AgentId<>0 and ID in (select accid from #loyList);"); // strDrop.Append("drop table #loyList;"); //} strSql.Append(strDrop.ToString()); strSql.Append(strColumn.ToString()); List <dynamic> list = new List <dynamic>(); DateTime iter = st; while (iter < ed) { list.Add(DapperHelper.Query <dynamic>(strSql.ToString(), new { date = iter.Date }).ToList()[0]); iter = iter.AddDays(1); } chartDataModel chartModel = new chartDataModel(); return(chartModel.SetDataAboutDataTime(st, ed, list, ColumnList, "代理商汇总")); }
/// <summary> /// 绑定项目状态 /// </summary> /// <returns></returns> public List <ProjectInfo> BandProjectStaus(int ProjectId) { string sql = "select P.Projectstage,s.StageName from ProjectInfo p join StagePlanInfo s on p.Projectstage=s.ProjectStageId where s.ProjectStageId=projectId"; return(DapperHelper <ProjectInfo> .Query(sql, new { projectId = ProjectId })); }
//以现有扩展信息更新行业结果 public int UpdateIndustryByExtendinfo() { StringBuilder strSql = new StringBuilder(); List <ShopExtIndustry> shopExtList = new List <ShopExtIndustry>(); //获取有分类的店铺及其大小分类 strSql.Append("select AccId,Industry IndustryStr from P_Sys_UserPortrait where PATINDEX('%[0-9]%',Industry)<>0;"); try { shopExtList = DapperHelper.Query <ShopExtIndustry>(strSql.ToString()).ToList(); } catch (Exception ex) { Logger.Error("获取现有用户扩展信息行业出错!", ex); } //如果获取到信息进行处理 if (shopExtList.Count > 0) { foreach (var item in shopExtList) { List <int> industryList = new List <int>(); List <string> industryStrList = item.IndustryStr.Split(',').ToList(); foreach (var indust in industryStrList) { int i = 0; if (int.TryParse(indust, out i)) { industryList.Add(i); } } if (industryList.Count > 0) { //获取大小行业 foreach (var itemId in industryList) { ShopIndustryDic dic = GetIndustryPairDic(itemId); if (dic.ParentId == 0) { item.Industry_1 = dic.Keyword; } else { item.Industry_2 = dic.Keyword; } } } //更新用户行业表 UpdateExtIndustry(item); } return(1); } else { return(-1); } }
/// <summary> /// 得到销售地图销售信息 /// </summary> /// <param name="oid"></param> /// <returns></returns> public List <saleMapItemList> GetSaleMapSaleList(int oid) { List <saleMapItemList> mapList = new List <saleMapItemList>(); StringBuilder sql = new StringBuilder(); sql.Append("create table #temp (orderid int,ShopNum nvarchar(30), ShopName nvarchar(300),ShopAddress nvarchar(100),SellMoney money,DetailAddress nvarchar(200),SellTime datetime,accountid int ); "); if (oid > 0) { sql.Append("insert into #temp(orderid,accountid,ShopNum,SellMoney,SellTime) "); sql.Append("select top 20 saleID,accID,saleNo,RealMoney,insertTime from i200.dbo.T_SaleInfo where saleID>'" + oid.ToString() + "' order by saleID asc; "); } else { sql.Append("insert into #temp(orderid,accountid,ShopNum,SellMoney,SellTime) "); sql.Append("select top 30 saleID,accID,saleNo,RealMoney,insertTime from i200.dbo.T_SaleInfo order by saleID desc; "); } sql.Append(" update #temp set ShopAddress=TrueConty from #temp inner join i200.dbo.t_account_user on t_account_user.accountid=#temp.accountid and grade='管理员'; "); sql.Append(" update #temp set ShopName=CompanyName,DetailAddress=CompanyAddress from #temp inner join i200.dbo.T_Account on #temp.accountid=T_Account.ID; "); sql.Append(" select * from #temp order by orderid asc ; "); sql.Append(" drop table #temp; "); List <dynamic> list = DapperHelper.Query(sql.ToString()).ToList(); if (list != null && list.Count > 0) { foreach (dynamic item in list) { saleMapItemList itemObj = new saleMapItemList(); itemObj.ShopCar = item.ShopNum.ToString(); itemObj.ShopName = item.ShopName.ToString(); itemObj.Address = item.ShopAddress.ToString(); itemObj.id = item.orderid.ToString(); if (string.IsNullOrEmpty(itemObj.Address) || itemObj.Address == "not find") { itemObj.Address = "乌鲁木齐"; } else { try { itemObj.Address = itemObj.Address.Split('.')[0]; } catch { itemObj.Address = "乌鲁木齐"; } } itemObj.SellMoney = string.Format("{0:C}", Convert.ToDouble(item.SellMoney.ToString())); if (item.DetailAddress != null) { itemObj.DetailAddress = item.DetailAddress.ToString(); } itemObj.SellTime = (int)System.DateTime.Now.Subtract(Convert.ToDateTime(item.SellTime.ToString())).TotalMinutes; mapList.Add(itemObj); } } return(mapList); }
/// <summary> /// 查询车辆品牌信息用于绑定下拉 /// </summary> /// <returns></returns> public List <Brand> GetBrands() { return(DapperHelper.Query <Brand>("select * from Brand", null)); }
public List <UserInfos> Show() { string sql = "select * from UserInfos"; return(DapperHelper <UserInfos> .Query(sql, null)); }
/// <summary> /// 项目阶段详情 /// </summary> /// <param name="ProjectStageId"></param> /// <returns></returns> public List <StagePlanInfo> StageDetail(int ProjectStageId) { string sql = "select * from StagePlanInfo where ProjectStageId=@projectStageId and StageStaus=1"; return(DapperHelper <StagePlanInfo> .Query(sql, new { projectStageId = ProjectStageId })); }
public UserInfos ShowById(int id) { string sql = "select * from UserInfos where id=@id"; return(DapperHelper <UserInfos> .Query(sql, new { id }).FirstOrDefault()); }
/// <summary> /// 数据反填 /// </summary> /// <returns></returns> public List <StagePlanInfo> StageBackFill(int PlanId) { string sql = "select * from StagePlanInfo where PlanId=@planId"; return(DapperHelper <StagePlanInfo> .Query(sql, new { planId = PlanId })); }
public string TagTransfer() { StringBuilder strSql = new StringBuilder(); strSql.Append("select acc_id accid " + "from Sys_TagNexus where tag_id=( select id from Sys_TagInfo where t_Name='朋友介绍'); "); List <int> accidList = DapperHelper.Query <int>(strSql.ToString()).ToList(); string reStr = ""; foreach (int accid in accidList) { strSql.Clear(); strSql.Append("if exists (select * from P_Sys_UserPortrait where AccId=@accid) "); strSql.Append(" begin "); strSql.Append(" select Id from P_Sys_UserPortrait where AccId=@accid; "); strSql.Append(" end "); strSql.Append(" else "); strSql.Append(" begin "); strSql.Append(" insert into P_Sys_UserPortrait (InsertTime,AccId,industry,UserSourcePortrait,ChoiceReason,PotentialNeed) values (GETDATE(),@accid,',',',',',',',') "); strSql.Append(" select @@IDENTITY; "); strSql.Append(" end"); int reVal = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { accid = accid }); strSql.Clear(); strSql.Append("select UserSourcePortrait from P_Sys_UserPortrait where AccId=@accid;"); string industry = DapperHelper.ExecuteScalar <string>(strSql.ToString(), new { accid = @accid }); if (string.IsNullOrEmpty(industry)) { industry = ",64,"; } else { if (!industry.Contains(",64,")) { industry += ",64,"; } //if (!industry.Contains(",27,")) //{ // industry += ",27,"; //} } strSql.Clear(); strSql.Append("update P_Sys_UserPortrait set UserSourcePortrait='" + industry + "' where accid=@accid;"); reVal = DapperHelper.Execute(strSql.ToString(), new { accid = accid }); if (reVal != 1) { reStr += " " + accid + ":" + reVal + "; "; } } return(reStr); }
/// <summary> /// 查询车辆型号信息用于绑定下拉 /// </summary> /// <returns></returns> public List <Types> GetType(int bid) { return(DapperHelper.Query <Types>($"select * from Type where Tbid={bid}", null)); }