/// <summary> /// 数据筛选专用 /// </summary> /// <returns></returns> public FiltrateData.AllMax GetAllMax() { StringBuilder strSql = new StringBuilder(); strSql.Append(" select max(userNum) MaxUserNum,max(saleNum) MaxSaleNum,DATEDIFF(day,min(lastLoginTime),GETDATE()) MaxLastLoginTime,max(orderMoney) MaxOrderMoney, "); strSql.Append(" max(orderNum) MaxOrderNum,max(smsNum) MaxSmsNum,max(goodsNum) MaxGoodsNum,max(outlayNum) MaxOutlayNum,max(allLoginNum) MaxAllLoginNum "); strSql.Append(" from SysRpt_ShopInfo; "); return(DapperHelper.GetModel <FiltrateData.AllMax>(strSql.ToString())); }
/// <summary> /// 获取通知消息 /// </summary> /// <param name="typeId">类型Id</param> /// <returns></returns> public NoticeTextModel GetNoticeText(int typeId) { var strSql = new StringBuilder(); strSql.Append(" SELECT top(1) Id, nType Type, nDisplay Display, nTitle Title, nContent Content, nTime DateTime, nOperatorName, nOperatorIp"); strSql.Append(" FROM i200.dbo.T_NoticeText"); strSql.Append(" where nType=@nType"); strSql.Append(" order by id desc"); return(DapperHelper.GetModel <NoticeTextModel>(strSql.ToString(), new { nType = typeId })); }
/// <summary> /// 得到一个店铺收集信息 /// </summary> /// <param name="accid"></param> /// <returns></returns> public SysAccountInfo GetAccountInfo(int accid) { string column = "accid,a_QQ sys_qq,a_WeiXin sys_weixin,a_Tel sys_tel,a_ShopSize sys_shopsize,a_Operate sys_operate,a_Address sys_address,a_Industry sys_industry,a_Name sys_name,a_IdentityNumber sys_indentity,a_Duration sys_duration,a_OtherSoftware sys_software,a_Remark sys_remark,feedbackTel feedbackTel,feedbackQQ feedbackQQ,sysAddress aboutAddress"; StringBuilder strSql = new StringBuilder(); strSql.Append("select top 1 "); strSql.Append(column); strSql.Append(" from Sys_Account where accid=@accid"); return(DapperHelper.GetModel <SysAccountInfo>(strSql.ToString(), new { accid = accid })); }
/// <summary> /// 得到店铺的基本信息 /// <para> /// 包含 店铺基本信息,店铺版本,最后登录时间等 /// </para> /// </summary> /// <param name="accid"></param> /// <returns></returns> public T_AccountBasic GetAccountBasic(int accid) { StringBuilder strSql = new StringBuilder(); strSql.Append(" select top 1 a.ID,CompanyName,UserRealName,shotName,RegTime,CompanyAddress,ServiceManager,LoginTimeWeb,LoginTimeLast,AgentId,aotjb,endtime,gsreguser,integral,active,smsSurplusNum,SmsBilling,PhoneNumber,UserEmail,AgentName,a.Remark from( "); strSql.Append(" select ID,CompanyName,UserRealName,shotName,RegTime,CompanyAddress,ServiceManager,LoginTimeWeb,LoginTimeLast,AgentId,Remark from i200.dbo.T_Account where ID=@accid) a "); strSql.Append(" left outer join (select accountid,aotjb,endtime,gsreguser,integral,active,SmsBilling,dxunity smsSurplusNum from i200.dbo.T_Business where accountid=@accid) b on a.ID=b.accountid "); strSql.Append(" left outer join (select accountid,PhoneNumber,UserEmail from i200.dbo.T_Account_User where accountid=@accid and grade='管理员') c on a.ID=c.accountid " + " left outer join (select AgentName,ID from Sys_agent_mess) d on d.ID=a.AgentId; "); return(DapperHelper.GetModel <T_AccountBasic>(strSql.ToString(), new { accid = accid })); }
/// <summary> /// 优惠券内容 /// </summary> /// <param name="id"></param> /// <returns></returns> public new OrderCouponInfo GetModel(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append(" declare @produceNum int,@bindingNum int,@useNum int; "); strSql.Append(" select @produceNum= COUNT(id),@bindingNum= sum(case when toAccId>0 then 1 else 0 end),@useNum=SUM(case when useAccId>0 then 1 else 0 end) "); strSql.Append(" from i200.dbo.T_Order_CouponList where groupId=@id ; "); strSql.Append(" select id,couponType,bindType,bindValue,ruleType,ruleValue,couponStatus,couponValue,couponDesc,maxLimitNum,createDate,endDate,operatorId,remark, "); strSql.Append(" @produceNum produceNum,@bindingNum bindingNum,@useNum useNum from i200.dbo.T_Order_CouponInfo where id=@id order by id desc; "); OrderCouponInfo infoModel = DapperHelper.GetModel <OrderCouponInfo>(strSql.ToString(), new { id = id }); return(infoModel); }
/// <summary> /// 获取单条发票信息 /// </summary> /// <param name="oid"></param> /// <returns></returns> public InvoiceSimple GetInvoiceInfo(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append( "select invoiceRemark,invoiceExpress,invoiceNo from [i200].[dbo].[T_Order_Invoice] where id=@id;"); try { return(DapperHelper.GetModel <InvoiceSimple>(strSql.ToString(), new { id = id })); } catch (Exception ex) { return(null); } }
/// <summary> /// 根据Id获取模板 /// </summary> /// <param name="id"></param> /// <returns></returns> public TriggerTemplateModel GeTemplateModel(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select * from Sys_MessageNotify where EventId=@id and EnableStatus=1;"); try { return(DapperHelper.GetModel <TriggerTemplateModel>(strSql.ToString(), new { id = id })); } catch (Exception ex) { Logger.Error("获取模板信息出错!", ex); return(null); } }
/// <summary> /// 得到某段时间内的平均数据 /// </summary> /// <param name="startTime"></param> /// <param name="endTime"></param> /// <returns></returns> public PanelShowModel.SomedayDataCount GetAverageData(DateTime startTime, DateTime endTime) { StringBuilder strSql = new StringBuilder(); strSql.Append(" declare @dateLeng int; "); strSql.Append(" set @dateLeng=DATEDIFF(DAY,@startTime,@endTime); "); strSql.Append(" if(isnull(@dateLeng,0)<1) "); strSql.Append(" begin "); strSql.Append(" set @dateLeng=1; "); strSql.Append(" end "); strSql.Append(" select SUM(NewAccNum)/@dateLeng regNum,SUM(saleNum)/@dateLeng saleNum,SUM(saleMoney)/@dateLeng saleMoney,SUM(userNum)/@dateLeng userNum, "); strSql.Append(" SUM(addGoodsNum)/@dateLeng goodsNum,SUM(smsNum)/@dateLeng smsNum,SUM(orderNum)/@dateLeng orderNum,SUM(orderMoney)/@dateLeng orderMoney "); strSql.Append(" from SysRpt_WebDayInfo where S_Date>=@startTime and S_Date<@endTime; "); return(DapperHelper.GetModel <PanelShowModel.SomedayDataCount>(strSql.ToString(), new { startTime = startTime, endTime = endTime })); }
//public OrderPartition GetOrderType(DateTime stDate, DateTime edDate) //{ // StringBuilder strSql = new StringBuilder(); // strSql.Append("") //} #region 退款相关操作 /// <summary> /// 获取退款信息 /// </summary> /// <param name="oid"></param> /// <returns></returns> public DrawbackInfoModel GetDrawbackOrderInfo(int oid) { StringBuilder strSql = new StringBuilder(); strSql.Append("select oid,createDate,busId,orderTypeId,accid,RealPayMoney,busQuantity from i200.dbo.T_OrderInfo " + "where oid=@oid;"); try { return(DapperHelper.GetModel <DrawbackInfoModel>(strSql.ToString(), new { oid = oid })); } catch (Exception ex) { Logger.Error("获取退款订单信息出错!", ex); return(null); } }
/// <summary> /// 从设置表里抽取并还原发送设置的内容 /// </summary> /// <param name="id"></param> /// <returns></returns> public ConditionSettingModel.SettingModel GetSendingTaskItem(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append("select * from I200_Task.dbo.ConditionSetting where Id=@id;"); try { return(DapperHelper.GetModel <ConditionSettingModel.SettingModel>(strSql.ToString(), new { id = id })); } catch (Exception ex) { Logger.Error("从设置表获取Id为" + id + "的信息出错!", ex); return(null); } }
/// <summary> /// 根据优惠券编号获取优惠券信息 /// </summary> /// <param name="couponCode"></param> /// <returns></returns> public dynamic GetCouponInfoByCode(string couponCode) { StringBuilder strSql = new StringBuilder(); strSql.Append("select ci.couponDesc,ci.endDate from i200.dbo.T_Order_CouponInfo ci " + "left join [i200].[dbo].[T_Order_CouponList] cl " + "on ci.id=cl.groupId " + "where cl.couponId=@couponCode;"); try { return(DapperHelper.GetModel <dynamic>(strSql.ToString(), new { couponCode = couponCode })); } catch (Exception ex) { Logger.Error("获取优惠券信息出错!", ex); return(null); } }
/// <summary> /// 获取今日当前数据 /// </summary> /// <returns></returns> public PanelShowModel.SomedayDataCount GetNowData() { StringBuilder strSql = new StringBuilder(); strSql.Append(" declare "); strSql.Append(" @regNum int , "); // 注册数 strSql.Append(" @saleNum int , "); // 销售数 strSql.Append(" @saleMoney decimal(18,2) , "); // 销售金额 strSql.Append(" @userNum int , "); // 会员数 strSql.Append(" @goodsNum int , "); // 商品数 strSql.Append(" @smsNum int , "); // 短信数 strSql.Append(" @orderNum int , "); // 订单数 strSql.Append(" @bgTime datetime , "); // 开始时间 strSql.Append(" @edTime datetime , "); // 结束时间 strSql.Append(" @orderMoney decimal(18,2); "); // 订单金额 strSql.Append(" set @bgTime=CAST(GETDATE() as date) ; "); strSql.Append(" set @edTime=GETDATE() ; "); strSql.Append(" select @regNum=COUNT(ID) from i200.dbo.T_Account where RegTime between @bgTime and @edTime and "); strSql.Append(" State=1; "); strSql.Append(" select @saleNum=COUNT(saleID),@saleMoney=SUM(RealMoney) from i200.dbo.T_SaleInfo where insertTime between @bgTime and @edTime "); strSql.Append(" and RealMoney<10000; "); strSql.Append(" select @userNum=COUNT(uid) from i200.dbo.T_UserInfo where uInsertTime between @bgTime and @edTime; "); strSql.Append(" select @goodsNum=COUNT(gid) from i200.dbo.T_GoodsInfo where gAddTime between @bgTime and @edTime; "); strSql.Append(" select @smsNum=COUNT(id) from i200.dbo.T_Sms_List where sendTime between @bgTime and @edTime "); strSql.Append(" and smsStatus=1; "); strSql.Append(" select @orderNum=COUNT(oid),@orderMoney=SUM(RealPayMoney) from i200.dbo.T_OrderInfo where (OrderStatus=2 or (OrderStatus=1 and OrderTypeId=2)) and "); strSql.Append(" transactionDate between @bgTime and @edTime; "); strSql.Append(" select @regNum regNum ,@saleNum saleNum ,isnull(@saleMoney,0) saleMoney ,"); strSql.Append(" @userNum userNum ,@goodsNum goodsNum , @smsNum smsNum ,@orderNum orderNum ,isnull(@orderMoney,0) orderMoney; "); return(DapperHelper.GetModel <PanelShowModel.SomedayDataCount>(strSql.ToString())); }
/// <summary> /// 得到一个账号信息 /// </summary> /// <param name="uid"></param> /// <returns></returns> public ManageUserModel GetManageUserModel(int uid) { Sys_Manage_User manageUser = new Sys_Manage_User(); ManageUserModel AccModel = new ManageUserModel(); StringBuilder strSql = new StringBuilder(); strSql.Append("select top(1) Id,UserName,P_session,LoginCounter,phone,name,PassWord,MenuPermission from Sys_Manage_User where [state]=1 and Id=@uid;"); manageUser = DapperHelper.GetModel <Sys_Manage_User>(strSql.ToString(), new { uid = uid }); if (manageUser != null) { AccModel.UserID = manageUser.Id; AccModel.UserName = manageUser.UserName; AccModel.PowerSession = Convert.ToInt32(manageUser.P_session); AccModel.LoginCnt = Convert.ToInt32(manageUser.LoginCounter); AccModel.Phone = manageUser.phone; AccModel.Name = manageUser.name; AccModel.MenuPermission = manageUser.MenuPermission; } return(AccModel); }
public RemarkList GetRemarkInfo(string remarkId) { RemarkList list = new RemarkList(); P_Sys_PortraitRemarkModel model = new P_Sys_PortraitRemarkModel(); StringBuilder strSql = new StringBuilder(); string[] strArr = remarkId.Split(','); foreach (string str in strArr) { if (!string.IsNullOrEmpty(str) && str != " ") { strSql.Append("select * from P_Sys_PortraitRemark where Id=@id"); model = DapperHelper.GetModel <P_Sys_PortraitRemarkModel>(strSql.ToString(), new { id = int.Parse(str) }); strSql.Clear(); list.DataList.Add(model); } } return(list); }
/// <summary> /// 得到某日此时的数据 /// </summary> /// <param name="webDate"></param> /// <returns></returns> public PanelShowModel.SomedayDataCount GetThisMomentData(DateTime webDate) { StringBuilder strSql = new StringBuilder(); strSql.Append(" declare "); strSql.Append(" @regNum int , "); // 注册数 strSql.Append(" @saleNum int , "); // 销售数 strSql.Append(" @saleMoney decimal(18,2) , "); // 销售金额 strSql.Append(" @userNum int , "); // 会员数 strSql.Append(" @goodsNum int , "); // 商品数 strSql.Append(" @smsNum int , "); // 短信数 strSql.Append(" @orderNum int , "); // 订单数 strSql.Append(" @orderMoney decimal(18,2); "); // 订单金额 strSql.Append(" select @regNum=COUNT(ID) from i200.dbo.T_Account where DATEDIFF(day,RegTime,@insertTime)=0 and "); strSql.Append(" CAST(RegTime as time)<CAST(@insertTime as time) and State=1; "); strSql.Append(" select @saleNum=COUNT(saleID),@saleMoney=SUM(RealMoney) from i200.dbo.T_SaleInfo where DATEDIFF(DAY,insertTime,@insertTime)=0 and "); strSql.Append(" CAST(insertTime as time)<CAST(@insertTime as time) and RealMoney<10000; "); strSql.Append(" select @userNum=COUNT(uid) from i200.dbo.T_UserInfo where DATEDIFF(day,uInsertTime,@insertTime)=0 "); strSql.Append(" and CAST(uInsertTime as time)<CAST(@insertTime as time); "); strSql.Append(" select @goodsNum=COUNT(gid) from i200.dbo.T_GoodsInfo where DATEDIFF(day,gAddTime,@insertTime)=0 "); strSql.Append(" and CAST(gAddTime as time)<CAST(@insertTime as time); "); strSql.Append(" select @smsNum=COUNT(id) from i200.dbo.T_Sms_List where DATEDIFF(day,sendTime,@insertTime)=0 and "); strSql.Append(" CAST(sendTime as time)<CAST(@insertTime as time) and smsStatus=1; "); strSql.Append(" select @orderNum=COUNT(oid),@orderMoney=SUM(RealPayMoney) from i200.dbo.T_OrderInfo where orderStatus=2 and "); strSql.Append(" DATEDIFF(day,transactionDate,@insertTime)=0 and CAST(transactionDate as time)<CAST(@insertTime as time); "); strSql.Append(" select @insertTime nowTime, @regNum regNum ,@saleNum saleNum ,isnull(@saleMoney,0) saleMoney ,"); strSql.Append(" @userNum userNum ,@goodsNum goodsNum , @smsNum smsNum ,@orderNum orderNum ,isnull(@orderMoney,0) orderMoney; "); return(DapperHelper.GetModel <PanelShowModel.SomedayDataCount>(strSql.ToString(), new { insertTime = webDate })); }
/// <summary> /// 得到店铺今日汇总信息 /// </summary> /// <param name="accid"></param> /// <returns></returns> public T_AccountSummarize.TodaySummarize GetAccountTodaySummarize(int accid) { StringBuilder strSql = new StringBuilder(); strSql.Append(" declare @saleNum int,@saleMoney decimal(18,2),@userNum int,@goodsNum int,@orderNum int,"); strSql.Append("@orderMoney decimal(18,2),@smsNum int,@outlayNum int,@outlayMoney decimal(18,2),@couponNum int,@useCouponNum int;"); strSql.Append(" select @saleNum=COUNT(saleID),@saleMoney=SUM(RealMoney) from i200.dbo.T_SaleInfo where DATEDIFF(day,insertTime,GETDATE())=0 and accID=@accid; "); strSql.Append(" select @userNum=COUNT(uid) from i200.dbo.T_UserInfo where DATEDIFF(day,uInsertTime,GETDATE())=0 and accID=@accid; "); strSql.Append(" select @goodsNum=COUNT(gid) from i200.dbo.T_GoodsInfo where DATEDIFF(DAY,gInsertDate,GETDATE())=0 and accID=@accid; "); strSql.Append(" select @orderNum=COUNT(oid),@orderMoney=SUM(RealPayMoney) from i200.dbo.T_OrderInfo where "); strSql.Append(" DATEDIFF(day,T_OrderInfo.transactionDate,GETDATE())=0 and accId=@accid and (orderStatus=2 or (OrderStatus=1 and OrderTypeId=2)); "); strSql.Append(" select @smsNum=COUNT(id) from i200.dbo.T_Sms_List where DATEDIFF(DAY,sendtime,GETDATE())=0 and accID=@accid and smsStatus=1; "); strSql.Append("select @outlayNum=COUNT(ID),@outlayMoney=SUM(PaySum) from i200.dbo.t_PayRecord where DATEDIFF(day,paydate,getdate())=0 and ShopperId=@accid;"); strSql.Append( "select @couponNum=count(*) from i200.dbo.T_Order_CouponList where Datediff(day,createDate,getdate())=0 and toAccId=@accid;"); strSql.Append( "select @useCouponNum=count(*) from i200.dbo.T_Order_CouponList where Datediff(day,createDate,getdate())=0 and useAccId=@accid;"); strSql.Append(" select @saleNum saleNum,isnull(@saleMoney,0) saleMoney,@userNum userNum,@goodsNum goodsNum,@orderNum orderNum, "); strSql.Append("isnull(@orderMoney,0) orderMoney,@smsNum smsNum,@outlayNum outlayNum,isnull(@outlayMoney,0) outlayMoney,@couponNum couponNum,@useCouponNum useCouponNum;"); return(DapperHelper.GetModel <T_AccountSummarize.TodaySummarize>(strSql.ToString(), new { accid = accid })); }
public string GetLatestLogClient(int accId) { string client = ""; StringBuilder strSql = new StringBuilder(); strSql.Append("select top 1 LogMode,OperDate from i200.dbo.T_LOG where Accountid=" + accId + " order by OperDate desc"); dynamic Log = DapperHelper.GetModel <dynamic>(strSql.ToString()); strSql.Clear(); strSql.Append("select top 1 AppKey,createTime from i200.dbo.T_Token_Api where accId=" + accId + " order by createTime desc"); dynamic App = DapperHelper.GetModel <dynamic>(strSql.ToString()); if (App != null && Log != null) { if (Convert.ToDateTime(Log.OperDate) > Convert.ToDateTime(App.createTime)) { client = Log.LogMode.ToString(); if (client == "4") { client = App.AppKey.ToString(); } } else { client = App.AppKey.ToString(); } } else if (App == null) { client = Log.LogMode.ToString(); } else { client = App.AppKey.ToString(); } if (client == "0" || client == "1") { return("0"); } else if (client.IndexOf('3') == 0) { return("8"); } else if (client.IndexOf("Android") >= 0) { return("11"); } else if (client.IndexOf("iPad") >= 0) { return("13"); } else if (client.IndexOf("iPhone") >= 0) { return("10"); } return(""); }