/// <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("");
}