public List <string> GetminCate(string maxCate) { StringBuilder strSql = new StringBuilder(); strSql.Append("select sms_class from i200.dbo.T_Common_Sms where sms_maxclass='" + maxCate + "' group by sms_class;"); return(HelperForFrontend.Query <string>(strSql.ToString()).ToList()); }
public string GetCateList(string type) { StringBuilder strSql = new StringBuilder(); strSql.Append( "select sms_class,sms_ranking,HiddenType from [i200].[dbo].[T_Common_Sms] where sms_maxclass=@maxClass group by sms_class,sms_ranking,HiddenType order by sms_ranking"); List <SimpleSms> list = HelperForFrontend.Query <SimpleSms>(strSql.ToString(), new { maxClass = type }).ToList(); return(CommonLib.Helper.JsonSerializeObject(list)); }
/// <summary> /// 获取虚拟商品列表 /// </summary> /// <param name="where"></param> /// <returns></returns> public List <Order_Project_Model> GetProjectProductName(string where) { StringBuilder strSql = new StringBuilder(); strSql.Append(" select busId,displayName,projectType from T_Order_Project "); strSql.Append(" where busId in ( " + where + " )"); List <Order_Project_Model> listitem = new List <Order_Project_Model>(); listitem = HelperForFrontend.Query <Order_Project_Model>(strSql.ToString()).ToList(); return(listitem); }
/// <summary> /// 获得前几行数据 /// </summary> public List <OrderInvoiceModel> GetPage(int pageIndex, int pageSize, string Column, string strWhere, string orderWhere) { List <OrderInvoiceModel> models = new List <OrderInvoiceModel>(); if (string.IsNullOrEmpty(orderWhere)) { orderWhere = "createDate desc, id desc"; } pageIndex = pageIndex < 1 ? 1 : pageIndex; pageSize = pageSize < 1 ? 20 : pageSize; //页数计算 int bgNumber = ((pageIndex - 1) * pageSize) + 1; int edNumber = (pageIndex) * pageSize; StringBuilder strSql = new StringBuilder(); if (string.IsNullOrEmpty(Column)) { Column = "*"; } strSql.Append("select * from ("); strSql.Append("select ROW_NUMBER() over (order by o." + orderWhere + ") as rowNumber, "); strSql.Append(" " + Column + " "); strSql.Append(" from T_Order_Invoice i left join T_OrderInfo o on i.oid=o.oid "); if (strWhere.Length > 0) { strSql.Append(" where " + strWhere + " and o.orderStatus=2 "); } else { strSql.Append(" where o.orderStatus=2 "); } strSql.Append(") t "); strSql.Append(" where t.rowNumber between @bgNumber and @edNumber;"); try { models = HelperForFrontend.Query <OrderInvoiceModel>(strSql.ToString(), new { bgNumber = bgNumber, edNumber = edNumber }).ToList(); } catch (Exception ex) { models = null; } return(models); }
/// <summary> /// 根据ID获取实物列表 /// </summary> /// <param name="where"></param> /// <returns></returns> public List <MaterialGoodsBaseModel> GetMaterialGoodsName(string where) { StringBuilder strSql = new StringBuilder(); strSql.Append(" select GoodsId,ImagePath,AliasName from T_MaterialGoods "); strSql.Append(" where GoodsId in ( " + where + " )"); List <MaterialGoodsBaseModel> listitem = new List <MaterialGoodsBaseModel>(); listitem = HelperForFrontend.Query <MaterialGoodsBaseModel>(strSql.ToString()).ToList(); return(listitem); }
/// <summary> /// 获取用户的实物订单 /// </summary> /// <param name="accid"></param> /// <returns></returns> public List <dynamic> GetMaterialListByAccId(int accid) { StringBuilder strSql = new StringBuilder(); strSql.Append("select g.AliasName opName,b.busId , busSumMoney,transactionDate,RealPayMoney,0 LoginTimes,orderPayType,b.commuteIntegral,b.commuteIntegralMoney,b.couponMoney,orderTypeId,b.invoiceId,i.invoiceStatus from ( "); strSql.Append(" select busid , busSumMoney,transactionDate,RealPayMoney,0 LoginTimes,orderPayType,commuteIntegral,commuteIntegralMoney,couponMoney,orderTypeId,invoiceId "); strSql.Append(" from i200.dbo.T_OrderInfo where "); strSql.Append(" orderStatus=1 and OrderTypeId=2 and accId=@accId ) b "); strSql.Append(" left outer join [i200].[dbo].[T_MaterialGoods] g on g.GoodsId=b.busId " + " left join i200.dbo.T_Order_Invoice i on b.invoiceId=i.id order by transactionDate desc "); return(HelperForFrontend.Query(strSql.ToString(), new { accId = accid }).ToList()); }
/// <summary> /// 获得审核统计信息 /// </summary> /// <param name="iStatus">审核状态</param> /// <param name="bgDate">起始日期</param> /// <param name="edDate">结束日期</param> /// <returns></returns> public ReviewModel GetReviewCount(int iStatus, DateTime bgDate, DateTime edDate) { var reviewModel = new ReviewModel(); var strSql = new StringBuilder(); var strWhereSql = new StringBuilder(); if (iStatus != 0) { //审核状态 strWhereSql.Append(" and Review=@Review"); } else { strWhereSql.Append(" and Review>0"); } if (bgDate != null && edDate != null) { //指定时间方式 bgDate = Convert.ToDateTime(Convert.ToDateTime(bgDate).ToShortDateString()); edDate = Convert.ToDateTime(Convert.ToDateTime(edDate).ToShortDateString()).AddHours(23).AddMinutes(59).AddSeconds(59); strWhereSql.Append(" and submitTime between @bgDate and @edDate"); } strSql.Append(" select count(id) as SumCnt,"); strSql.Append(" sum(case when Review = 1 then 1 else 0 end) as WaitCnt,"); strSql.Append(" sum(case when Review = 2 then 1 else 0 end) as PassCnt,"); strSql.Append(" sum(case when Review = 3 then 1 else 0 end) as RejectCnt"); strSql.Append(" from T_Sms_Notify"); strSql.Append(" where accID>0 "); strSql.Append(strWhereSql.ToString()); try { reviewModel = HelperForFrontend.Query <ReviewModel>(strWhereSql.ToString(), new { Review = iStatus, bgDate = bgDate, edDate = edDate }).ToList()[0]; } catch (Exception) { reviewModel = null; } return(reviewModel); }
/// <summary> /// 得到数据列表 回访专用 /// </summary> /// <param name="Column">列名</param> /// <param name="strWhere">条件集合</param> /// <returns></returns> public List <T_Account> GetVisitList(string Column, string strWhere) { Column = Column.Length > 0 ? Column : "*"; StringBuilder strSql = new StringBuilder(); strSql.Append("select "); strSql.Append(" " + Column + " "); strSql.Append(" from I200.dbo.T_Account "); if (strWhere.Length > 0) { strSql.Append(" where " + strWhere); } strSql.Append(";"); return(HelperForFrontend.Query <T_Account>(strSql.ToString()).ToList()); }
/// <summary> /// 返回小分类 /// </summary> /// <param name="classid"></param> /// <returns></returns> public List <string> GetSmsClass(string classid) { List <string> smsClass = new List <string>(); StringBuilder strSql = new StringBuilder(); strSql.Append("SELECT distinct sms_class FROM T_Common_Sms where sms_maxclass='" + classid + "'"); try { smsClass = HelperForFrontend.Query <string>(strSql.ToString()).ToList(); } catch (Exception) { smsClass = null; } return(smsClass); }
/// <summary> /// 获取记录 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="Column"></param> /// <param name="strWhere"></param> /// <returns></returns> public List <EvaluationModel> GetPage(int pageIndex, int pageSize, string Column, string strWhere) { List <EvaluationModel> listitem = new List <EvaluationModel>(); pageIndex = pageIndex < 1 ? 1 : pageIndex; pageSize = pageSize < 1 ? 20 : pageSize; //页数计算 int bgNumber = ((pageIndex - 1) * pageSize) + 1; int edNumber = (pageIndex) * pageSize; StringBuilder strSql = new StringBuilder(); strSql.Append("select * from ("); strSql.Append("select ROW_NUMBER() over (order by id desc) as rowNumber "); if (Column.Length == 0) { Column = "id,accId,dbo.GetAccountName(accId) accountName,productID,productType,content,isDisplay,isDelete,operatorIP,operatorUserId,createTime,editVale,remark,score"; } strSql.Append(" ," + Column + " "); strSql.Append(" from T_Product_Evaluation "); if (strWhere.Length > 0) { strSql.Append(" where "); } strSql.Append(strWhere); strSql.Append(" ) t "); strSql.Append(" where t.rowNumber between @bgNumber and @edNumber; "); try { listitem = HelperForFrontend.Query <EvaluationModel>(strSql.ToString(), new { bgNumber = bgNumber, edNumber = edNumber }).ToList(); } catch (Exception ex) { listitem = null; } return(listitem); }
/// <summary> /// 获取提现记录某几行 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="Column"></param> /// <param name="strWhere"></param> /// <returns></returns> public List <WithdrawalRecordModel> GetPage(int pageIndex, int pageSize, string Column, string strWhere) { List <WithdrawalRecordModel> listitem = new List <WithdrawalRecordModel>(); pageIndex = pageIndex < 1 ? 1 : pageIndex; pageSize = pageSize < 1 ? 20 : pageSize; //页数计算 int bgNumber = ((pageIndex - 1) * pageSize) + 1; int edNumber = (pageIndex) * pageSize; StringBuilder strSql = new StringBuilder(); strSql.Append("select * from ("); strSql.Append("select ROW_NUMBER() over (order by id desc) as rowNumber "); if (Column.Length == 0) { Column = "id,twr.accId,dbo.GetAccountName(twr.accId)accountName,twi.paymentType,twi.accountInfo,twi.paymentName,twr.withdrawalInfoId,twr.withdrawalAmount,twr.balanceAmount,twr.totalAmount,twr.createTime,twr.certTime,twr.transferTime,twr.completeTime,twr.status"; } strSql.Append(" ," + Column + " "); strSql.Append(" from T_WithdrawalRecord twr left outer join T_WithdrawalInfo twi on twr.withdrawalInfoId=twi.withdrawalInfoId "); if (strWhere.Length > 0) { strSql.Append(" where "); } strSql.Append(strWhere); strSql.Append(" ) t "); strSql.Append(" where t.rowNumber between @bgNumber and @edNumber; "); try { listitem = HelperForFrontend.Query <WithdrawalRecordModel>(strSql.ToString(), new { bgNumber = bgNumber, edNumber = edNumber }).ToList(); } catch (Exception ex) { listitem = null; } return(listitem); }
/// <summary> /// 获取待审核店铺列表 /// </summary> /// <returns></returns> public List <UncheckedShopModel> GetUncheckedShopList(DateTime regTime) { StringBuilder strSql = new StringBuilder(); strSql.Append(" select ID,CompanyName,UserRealName,RegTime,Reg_Code,Remark,PhoneNumber,useremail,0 regOk into #list from T_Account "); strSql.Append(" where State=0 and DATEDIFF(day,RegTime,@regTime)=0 "); strSql.Append(" update #list set PhoneNumber=b.PhoneNumber,UserEmail=b.UserEmail from T_Account_User b where "); strSql.Append(" #list.ID=b.accountid and grade='管理员' "); strSql.Append(" select id,CompanyName,PhoneNumber,useremail into #RegOk from T_Account where State=1 and "); strSql.Append(" RegTime>CAST(@regTime as date) and DATEDIFF(HOUR,CAST(@regTime as date),RegTime)<28 "); strSql.Append(" update #RegOk set PhoneNumber=b.PhoneNumber,UserEmail=b.UserEmail from T_Account_User b where "); strSql.Append(" #RegOk.ID=b.accountid and grade='管理员' "); strSql.Append(" update #list set regOk=1 where CompanyName in(select CompanyName from #RegOk) or "); strSql.Append(" PhoneNumber in(select PhoneNumber from #RegOk) or UserEmail in(select UserEmail from #RegOk); "); strSql.Append(" select Id,CompanyName,PhoneNumber,UserEmail,RegTime,UserRealName,Reg_Code,regOk from #list order by RegTime desc; "); strSql.Append(" drop table #list; "); strSql.Append(" drop table #RegOk; "); return(HelperForFrontend.Query <UncheckedShopModel>(strSql.ToString(), new { regTime = regTime }).ToList()); }
/// <summary> /// 获取短信通道累计发送数 /// </summary> /// <param name="channel"></param> /// <returns></returns> public int GetChannelSendNum(int channel) { int result = -1; StringBuilder strSql = new StringBuilder(); strSql.Append("select sum(realCnt) as smsCnt from T_Sms_List where smsStatus=1 and smsChannel=" + channel.ToString()); try { result = HelperForFrontend.Query <int>(strSql.ToString()).ToList()[0]; } catch (Exception) { result = -1; } return(result); }
/// <summary> /// 获取前几行数据 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="Column"></param> /// <param name="strWhere"></param> /// <returns></returns> public List <AlphaApplyModel> GetPage(int pageIndex, int pageSize, string Column, string strWhere) { List <AlphaApplyModel> listitem = new List <AlphaApplyModel>(); pageIndex = pageIndex < 1 ? 1 : pageIndex; pageSize = pageSize < 1 ? 20 : pageSize; //页数计算 int bgNumber = ((pageIndex - 1) * pageSize) + 1; int edNumber = (pageIndex) * pageSize; StringBuilder strSql = new StringBuilder(); strSql.Append("select * from ("); strSql.Append("select ROW_NUMBER() over (order by id desc) as rowNumber "); if (Column.Length == 0) { Column = "id,userAccId,userJob,userPhone,userAccName,userAlphaClient,createTime,status,alphaVersion"; } strSql.Append(" ," + Column + " "); strSql.Append(" from T_AlphaApply "); if (strWhere.Length > 0) { strSql.Append(" where "); } strSql.Append(strWhere); strSql.Append(" ) t "); strSql.Append(" where t.rowNumber between @bgNumber and @edNumber; "); try { listitem = HelperForFrontend.Query <AlphaApplyModel>(strSql.ToString(), new { bgNumber = bgNumber, edNumber = edNumber }).ToList(); } catch (Exception ex) { listitem = null; } return(listitem); }
/// <summary> /// 获取分页数据 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="Column"></param> /// <param name="strWhere"></param> /// <returns></returns> public List <AlipayInfoModel> GetPage(int pageIndex, int pageSize, string Column, string strWhere) { List <AlipayInfoModel> listitem = new List <AlipayInfoModel>(); pageIndex = pageIndex < 1 ? 1 : pageIndex; pageSize = pageSize < 1 ? 20 : pageSize; //页数计算 int bgNumber = ((pageIndex - 1) * pageSize) + 1; int edNumber = (pageIndex) * pageSize; StringBuilder strSql = new StringBuilder(); strSql.Append("select * from ("); strSql.Append("select ROW_NUMBER() over (order by id desc) as rowNumber "); if (Column.Length == 0) { Column = "[id],[accId],dbo.GetAccountName(accId) accountName,[status],[companyType],[createTime],[modifiedTime],[completeTime],[artificialPerson],[phone],[alipayAccount],[companyName],[industryClassification],[businessArea],[shopOutPhotoURL],[shopInPhotoURL],[businessLicensePhotoURL],[doorPhotoURL],[firstShopWorkPhotoURL],[secondShopWorkPhotoURL],[thirdShopWorkPhotoURL],[companyAddress],[PID],[Key],[remark]"; } strSql.Append(" ," + Column + " "); strSql.Append(" from T_AlipayInfo "); if (strWhere.Length > 0) { strSql.Append(" where "); } strSql.Append(strWhere); strSql.Append(" ) t "); strSql.Append(" where t.rowNumber between @bgNumber and @edNumber; "); try { listitem = HelperForFrontend.Query <AlipayInfoModel>(strSql.ToString(), new { bgNumber = bgNumber, edNumber = edNumber }).ToList(); } catch (Exception ex) { listitem = null; } return(listitem); }
/// <summary> /// 获取符合条件的指定列 /// </summary> /// <param name="column"></param> /// <param name="strWhere"></param> /// <returns></returns> public List <T_Account> GetListByColumn(string column, string strWhere) { List <T_Account> accList = new List <T_Account>(); StringBuilder strSql = new StringBuilder(); strSql.Append("select " + column + " from T_Account "); if (strWhere.Length > 0) { strSql.Append(" where " + strWhere); } try { accList = HelperForFrontend.Query <T_Account>(strSql.ToString()).ToList(); } catch (Exception ex) { accList = null; } return(accList); }
/// <summary> /// 获取总行数 /// </summary> /// <param name="strWhere"></param> /// <returns></returns> public int GetPageCount(string strWhere) { int count = 0; StringBuilder strSql = new StringBuilder(); strSql.Append("select count(*) from T_Product_Evaluation"); if (strWhere.Length > 0) { strSql.Append(" where "); } strSql.Append(strWhere); try { count = HelperForFrontend.Query <int>(strSql.ToString()).ToList()[0]; } catch (Exception ex) { count = 0; } return(count); }
/// <summary> /// 提现相关 /// </summary> /// <param name="strWhere"></param> /// <returns></returns> public int GetPageCount(string strWhere) { int count = 0; StringBuilder strSql = new StringBuilder(); strSql.Append("select count(*) from T_WithdrawalRecord twr left outer join T_WithdrawalInfo twi on twr.withdrawalInfoId=twi.withdrawalInfoId "); if (strWhere.Length > 0) { strSql.Append(" where "); } strSql.Append(strWhere); try { count = HelperForFrontend.Query <int>(strSql.ToString()).ToList()[0]; } catch (Exception ex) { count = 0; } return(count); }
/// <summary> /// 从主站直接获取最新信息 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="top"></param> /// <param name="columnName"></param> /// <param name="dapperWheres"></param> /// <param name="filedOrder"></param> /// <returns></returns> public List <T> GetNewList <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 I200.dbo.t_App_Au "); if (where.Length > 0) { strSql.Append(" where " + where); } if (filedOrder.Length > 0) { strSql.Append(" order by " + filedOrder); } strSql.Append(";"); return(HelperForFrontend.Query <T>(strSql.ToString(), parm).ToList()); }
/// <summary> /// 获得前几行数据 /// </summary> public List <OrderInfoModel> GetPage(int pageIndex, int pageSize, string Column, string strWhere) { List <OrderInfoModel> model = new List <OrderInfoModel>(); pageIndex = pageIndex < 1 ? 1 : pageIndex; pageSize = pageSize < 1 ? 20 : pageSize; //页数计算 int bgNumber = ((pageIndex - 1) * pageSize) + 1; int edNumber = (pageIndex) * pageSize; StringBuilder strSql = new StringBuilder(); strSql.Append("select * from ("); strSql.Append("select ROW_NUMBER() over (order by oid desc) as rowNumber "); if (Column.Length > 0) { strSql.Append(" ," + Column + " "); } else { Column = "oid,orderNo,dbo.GetAccountName(accId) accountName,accId,ta.UserRealName,datediff(dd,ta.LoginTimeLast,getdate()) as LoginLast,tb.aotjb as Edit,commuteIntegral,commuteIntegralMoney,dbo.GetOrderProjectName(busId) OrderProjectName,busId,orderPayType, busQuantity,busPrice,busSumMoney,couponMoney,couponId,confirmRemark,orderPayDesc, RealPayMoney,createDate, transactionDate, orderStatus, invoiceId,OrderTypeId,ReceivingAddressId,oFlag"; strSql.Append(" ," + Column + " "); } strSql.Append(" from T_OrderInfo left join i200.dbo.T_Account ta on accId=ta.ID left join i200.dbo.T_Business tb on accId=tb.accountid "); if (strWhere.Length > 0) { strSql.Append(" where "); } strSql.Append(strWhere); strSql.Append(" ) t "); strSql.Append(" where t.rowNumber between @bgNumber and @edNumber; "); try { model = HelperForFrontend.Query <OrderInfoModel>(strSql.ToString(), new { bgNumber = bgNumber, edNumber = edNumber }).ToList(); } catch (Exception ex) { model = null; } int count = 0; foreach (OrderInfoModel item in model) { if (item.orderStatus == 2) { strSql.Clear(); strSql.Append( "select count(*) from T_OrderInfo where accId=@accId and transactionDate<@date and orderStatus=2;"); try { count = HelperForFrontend.ExecuteScalar <int>(strSql.ToString(), new { accId = item.accId, date = item.transactionDate }); } catch (Exception ex) { count = 1; } if (count == 0) { item.FirstFlag = "1"; } else { item.FirstFlag = "0"; } } else { item.FirstFlag = "0"; } } return(model); }
/// <summary> /// 获取店铺查找的结果集 /// </summary> /// <param name="pageIndex"></param> /// <param name="pageSize"></param> /// <param name="dapperWhere">条件</param> /// <param name="filedOrder"></param> /// <returns></returns> public List <dynamic> GetSearchList(int pageIndex, int pageSize, List <DapperWhere> dapperWhere, string filedOrder, string searchStr = "") { string where = ""; Dictionary <string, object> parm = new Dictionary <string, object>(); foreach (DapperWhere item in dapperWhere) { if (where.Length > 0) { where += " and "; } where += item.Where; parm[item.ColumnName] = item.Value; } StringBuilder strSql = new StringBuilder(); strSql.Append("create table #userinfor(" + "ID int, UserRealName nvarchar(50), " + "PhoneNumber nvarchar(20)," + "regLength int," + "useremail nvarchar(50)," + "CompanyName nvarchar(100), " + "LoginTimeLast datetime," + "LoginTimeWeb int," + "TureAddress nvarchar(200)," + "Edit int,Orde int,OrdeMoney money," + "Revisit int," + "LoginLast int," + "Club int," + "Goods int," + "sale int," + "salemoney money," + "pay int," + "paymoney money," + "sms int,havasms int,Remark varchar(200)); " + " " + "insert into #userinfor (ID,UserRealName,PhoneNumber,useremail,CompanyName,LoginLast,LoginTimeLast,LoginTimeWeb,regLength,Remark) "); strSql.Append(" SELECT ID,UserRealName,PhoneNumber,useremail,CompanyName,LoginLast,LoginTimeLast,LoginTimeWeb,regleng,accountRm 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 rowNumber,ID,UserRealName,PhoneNumber,useremail,CompanyName,datediff(dd,LoginTimeLast,getdate()) as LoginLast," + " LoginTimeLast,LoginTimeWeb," + " datediff(day,regtime,getdate()) regleng,isnull(Remark,'') accountRm" + " from i200.dbo.T_Account T where [State]=1 "); if (string.IsNullOrEmpty(searchStr)) { if (where.Length > 0) { strSql.Append(" and " + where); } } else { strSql.Append(" and (phoneNumber like '%" + searchStr + "%' or "); strSql.Append(" CompanyName like '%" + searchStr + "%' or "); strSql.Append(" UserRealName like '%" + searchStr + "%' or "); strSql.Append(" UserEmail like '%" + searchStr + "%' or "); strSql.Append(" BBS_Uid like '%" + searchStr + "%' "); if (Regex.IsMatch(searchStr, "^((\\+|-)\\d)?\\d*$")) { strSql.Append(" or ID=" + searchStr); } strSql.Append(" )"); } strSql.Append(" ) TT "); strSql.Append(" WHERE TT.rowNumber between @startIndex and @endIndex; "); strSql.Append( "update #userinfor set TureAddress=TrueConty from i200.dbo.T_Account_User inner join #userinfor on #userinfor.ID=T_Account_User.accountid where grade='管理员'; " + "update #userinfor set Edit=aotjb,havasms=dxunity from i200.dbo.T_Business inner join #userinfor on accountid=#userinfor.ID; " + "update #userinfor set Orde=orderList.num,OrdeMoney=sumMoney from #userinfor inner join (select accId,COUNT(oid) num,SUM(RealPayMoney) sumMoney from i200.dbo.T_OrderInfo where accId in (select ID from #userinfor) and orderStatus=2 and DATEDIFF(day,createDate,getdate())=0 group by accId) orderList on orderList.accId=#userinfor.ID; " + //"update #userinfor set Revisit=relist.num from #userinfor inner join (select accID,COUNT(id) as num from T_SysRevisit_Info where T_SysRevisit_Info.accID in (select ID from #userinfor) group by accID) relist on relist.accID=#userinfor.ID; " + "update #userinfor set Club=userlist.num from #userinfor inner join (select accID,COUNT(uid) as num from i200.dbo.T_UserInfo where T_UserInfo.accID in (select ID from #userinfor) and DATEDIFF(day,uInsertTime,getdate())=0 group by accID) userlist on userlist.accID=#userinfor.ID; " + "update #userinfor set Goods=goodslist.num from #userinfor inner join (select accID,COUNT(gid) as num from i200.dbo.T_GoodsInfo where T_GoodsInfo.accID in (select ID from #userinfor) and DATEDIFF(day,gAddTime,getdate())=0 group by accID) goodslist on goodslist.accID=#userinfor.ID; " + "update #userinfor set sale=salelist.num,salemoney=rm from #userinfor inner join (select accID,COUNT(uid) as num,SUM(RealMoney) rm from i200.dbo.T_SaleInfo where T_SaleInfo.accID in (select ID from #userinfor) and DATEDIFF(day,insertTime,getdate())=0 group by accID) salelist on salelist.accID=#userinfor.ID; " + "update #userinfor set pay=paylist.num,paymoney=pMoney from #userinfor inner join (select ShopperId,COUNT(id) as num,SUM(paysum) pMoney from i200.dbo.t_PayRecord where t_PayRecord.ShopperId in (select ID from #userinfor) and DATEDIFF(day,PayDate,getdate())=0 group by ShopperId) paylist on paylist.ShopperId=#userinfor.ID; " + "update #userinfor set sms=smslist.num from #userinfor inner join (select accID,COUNT(id) as num from i200.dbo.T_Sms_List where T_Sms_List.accID in (select ID from #userinfor) and DATEDIFF(day,sendtime,getdate())=0 group by accID) smslist on smslist.accID=#userinfor.ID; " + "select * from #userinfor; " + "drop table #userinfor;"); if (pageIndex < 1) { pageIndex = 1; } int bgNumber = ((pageIndex - 1) * pageSize) + 1; int edNumber = (pageIndex) * pageSize; parm["startIndex"] = bgNumber; parm["endIndex"] = edNumber; return(HelperForFrontend.Query <dynamic>(strSql.ToString(), parm).ToList()); }
/// <summary> /// 短信发送列表 /// </summary> /// <param name="iPage">当前页数</param> /// <param name="accId">店铺Id</param> /// <param name="smsType">短信类型</param> /// <param name="smsStatus">短信状态</param> /// <param name="priority">通道优先级</param> /// <param name="bgDate">开始日期</param> /// <param name="edDate">结束日期</param> /// <returns></returns> public SmsListModel GetSmsList(int iPage, int accId, int smsType, int smsStatus, int priority, DateTime bgDate, DateTime edDate) { var smsListModel = new SmsListModel(); var strSql = new StringBuilder(); var strWhereSql = new StringBuilder(); if (priority != -99) { //发送优先级 strWhereSql.Append(" priority=@priority"); } else { //发送优先级 strWhereSql.Append(" priority>-2"); } if (accId != 0) { //店铺Id strWhereSql.Append(" and accID=@accID"); } if (smsType != -99) { //短信类型 strWhereSql.Append(" and smsType=@smsType"); } if (smsStatus != -99) { //短信状态 strWhereSql.Append(" and smsStatus=@smsStatus"); } if (bgDate != null && edDate != null) { //指定时间方式 bgDate = Convert.ToDateTime(Convert.ToDateTime(bgDate).ToShortDateString()); edDate = Convert.ToDateTime(Convert.ToDateTime(edDate).ToShortDateString()).AddHours(23).AddMinutes(59).AddSeconds(59); strWhereSql.Append(" and submitTime between @bgDate and @edDate"); } strSql.Append(" select count(id) [rowCount],sum(totalSmsCnt) as SmsCnt,"); strSql.Append(" sum(realSmsCnt) as RealCnt,"); strSql.Append(" sum(case when isFree=1 then realSmsCnt else 0 end) as FreeCnt"); strSql.Append(" from T_Sms_Notify"); if (strWhereSql.Length > 0) { strSql.Append(" where" + strWhereSql.ToString()); } smsListModel = HelperForFrontend.Query <SmsListModel>(strSql.ToString(), new { bgDate = bgDate, edDate = edDate, priority = priority, accID = accId, smsType = smsType, smsStatus = smsStatus, }).ToList()[0]; strSql.Clear(); strSql.Append(" select TT.*,T_Account.CompanyName StoreName from (select row_number() over(order by completeTime desc) as rowNumer,"); strSql.Append(" id, accID, userCnt, smsContent, smsType, useBalance, priority, succeedCnt, failCnt, skipCnt, smsStatus, submitTime, RegularTime, completeTime, smsChannel, totalSmsCnt, realSmsCnt, isFree,errDesc, ChannelEx,PhoneList,ReviewDesc"); strSql.Append(" FROM T_Sms_Notify"); if (strWhereSql.Length > 0) { strSql.Append(" where" + strWhereSql.ToString()); } strSql.Append(" ) TT"); strSql.Append(" left outer join T_Account on T_Account.id=TT.accID"); strSql.Append(" where TT.rowNumer between @startIndex and @endIndex"); //页数计算 int bgNumber = ((iPage - 1) * 20) + 1; int edNumber = (iPage) * 20; List <SmsNotifyItem> itemList = HelperForFrontend.Query <SmsNotifyItem>(strSql.ToString(), new { priority = priority, accID = accId, smsType = smsType, smsStatus = smsStatus, bgDate = bgDate, edDate = edDate, startIndex = bgNumber, endIndex = edNumber }).ToList(); smsListModel.Data = itemList; return(smsListModel); }
/// <summary> /// 获得短信审核列表 /// </summary> /// <param name="iPage">当前页数</param> /// <param name="iStatus">审核状态</param> /// <param name="operatorId">操作人员Id</param> /// <param name="bgDate">开始日期</param> /// <param name="edDate">结束日期</param> /// <returns></returns> public ReviewModel GetReviewList(int iPage, int iStatus, int operatorId, DateTime bgDate, DateTime edDate) { var reviewModel = new ReviewModel(); var strSql = new StringBuilder(); var strWhereSql = new StringBuilder(); if (iStatus != 0) { //审核状态 strWhereSql.Append(" and Review=@Review"); } else { strWhereSql.Append(" and Review>0"); } if (operatorId != 0) { //审核人员 strWhereSql.Append(" and operatorId=@operatorId"); } if (bgDate != null && edDate != null) { //指定时间方式 bgDate = Convert.ToDateTime(Convert.ToDateTime(bgDate).ToShortDateString()); edDate = Convert.ToDateTime(Convert.ToDateTime(edDate).ToShortDateString()).AddHours(23).AddMinutes(59).AddSeconds(59); strWhereSql.Append(" and submitTime between @bgDate and @edDate"); } strSql.Append(" select count(id) as SumCnt,"); strSql.Append(" sum(case when Review = 1 then 1 else 0 end) as WaitCnt,"); strSql.Append(" sum(case when Review = 2 then 1 else 0 end) as PassCnt,"); strSql.Append(" sum(case when Review = 3 then 1 else 0 end) as RejectCnt"); strSql.Append(" from T_Sms_Notify"); strSql.Append(" where accID>0 "); strSql.Append(strWhereSql.ToString()); try { reviewModel = HelperForFrontend.Query <ReviewModel>(strSql.ToString(), new { bgDate = bgDate, edDate = edDate, Review = iStatus, operatorId = operatorId }).ToList()[0]; reviewModel.PageNow = iPage; } catch (Exception ex) { return(null); } strSql.Clear(); strSql.Append(" select TT.*,T_Account.CompanyName StoreName,'' as OperatorName from (select row_number() over(order by submitTime desc) as rowNumer,"); strSql.Append(" id, accID, userCnt, smsContent, smsType, useBalance, priority, succeedCnt, failCnt, skipCnt, smsStatus, submitTime, RegularTime, completeTime, smsChannel, totalSmsCnt, realSmsCnt, isFree,errDesc, ChannelEx, Review, ReviewOperator, ReviewDate, ReviewDesc"); strSql.Append(" FROM T_Sms_Notify"); strSql.Append(" where accID>0"); strSql.Append(strWhereSql.ToString()); strSql.Append(" ) TT"); strSql.Append(" left outer join T_Account on T_Account.id=TT.accID"); strSql.Append(" where TT.rowNumer between @startIndex and @endIndex"); //页数计算 int bgNumber = ((iPage - 1) * 20) + 1; int edNumber = (iPage) * 20; try { List <SmsNotifyItem> itemList = HelperForFrontend.Query <SmsNotifyItem>(strSql.ToString(), new { Review = iStatus, operatorId = operatorId, bgDate = bgDate, edDate = edDate, startIndex = bgNumber, endIndex = edNumber }).ToList(); reviewModel.Data = itemList; } catch (Exception ex) { return(null); } return(reviewModel); }
/// <summary> /// 获得短信通道设置信息 /// </summary> /// <returns></returns> public SmsChannelInfo GetChannelInfo() { SmsConfigInfoTmp tmpResult = new SmsConfigInfoTmp(); SmsChannelInfo smsChannelInfo = new SmsChannelInfo(); StringBuilder strSql = new StringBuilder(); strSql.Append("declare @SendMode int;"); strSql.Append(" select @SendMode=Value from T_SysConfig where Item='sms_SendMode';"); strSql.Append(" if(@SendMode=1)"); strSql.Append(" begin"); strSql.Append(" SELECT @SendMode as sendmode,max(case when Item='sms_channel_sys' then Value end) as sms_channel_sys,max(case when Item='sms_channel_f' then Value end) as sms_channel_f,max(case when Item='sms_channel_s' then Value end) as sms_channel_s FROM T_SysConfig where Item='sms_channel_f' or Item='sms_channel_s' or Item='sms_channel_sys';"); strSql.Append(" end"); strSql.Append(" else"); strSql.Append(" begin"); strSql.Append(" SELECT @SendMode as sendmode,Value from T_SysConfig where Item='sms_option';"); strSql.Append(" end"); try { tmpResult = HelperForFrontend.Query <SmsConfigInfoTmp>(strSql.ToString()).ToList()[0]; } catch (Exception ex) { tmpResult = null; } if (tmpResult != null) { if (tmpResult.sendmode != null) { smsChannelInfo.SendMode = tmpResult.sendmode; if (smsChannelInfo.SendMode == 2) { //运营商绑定模式 if (tmpResult.Value != null) { string objVal = tmpResult.Value.ToString(); SmsChannel smsChannel = new SmsChannel(); smsChannel = Helper.JsonDeserializeObject <SmsChannel>(objVal.Trim()); smsChannelInfo.SmsOperator = smsChannel; } } else { //通道绑定模式 SmsPriorityItem smsPriorityItem = new SmsPriorityItem(); SmsChannelUnit smsChannelUnitSys = new SmsChannelUnit(); SmsChannelUnit smsChannelUnitFast = new SmsChannelUnit(); SmsChannelUnit smsChannelUnitGroup = new SmsChannelUnit(); smsPriorityItem.SysPriority = smsChannelUnitSys; smsPriorityItem.FastPriority = smsChannelUnitFast; smsPriorityItem.GroupPriority = smsChannelUnitGroup; smsPriorityItem.SysPriority.ChannelId = tmpResult.sms_channel_sys; smsPriorityItem.FastPriority.ChannelId = tmpResult.sms_channel_f; smsPriorityItem.GroupPriority.ChannelId = tmpResult.sms_channel_s; smsChannelInfo.SmsPriority = smsPriorityItem; } } } return(smsChannelInfo); }
public Dictionary <string, string> GetSmsContent(int page, string type, string subType) { Dictionary <string, string> dic = new Dictionary <string, string>() { { "list", "" }, { "rowCount", "" }, { "maxRow", "" } }; StringBuilder strSql = new StringBuilder(); int pageIndex = page; int pageSize = 15; int bgNumber = ((pageIndex - 1) * pageSize) + 1; int edNumber = (pageIndex) * pageSize; List <T_Common_Sms> list = new List <T_Common_Sms>(); if (string.IsNullOrEmpty(subType)) { strSql.Append("select * from " + "(select ROW_NUMBER() over (order by sms_time desc) rowNumber,* from i200.dbo.T_Common_Sms " + "where sms_maxclass=@sms_max) t where t.rowNumber between @bgNumber and @edNumber;"); list = HelperForFrontend.Query <T_Common_Sms>(strSql.ToString(), new { sms_max = type, bgNumber = bgNumber, edNumber = edNumber }).ToList(); } else { strSql.Append("select * from " + "(select ROW_NUMBER() over (order by sms_time desc) rowNumber,* from i200.dbo.T_Common_Sms " + "where sms_maxclass=@sms_max and sms_class=@sms_min) t where t.rowNumber between @bgNumber and @edNumber;"); list = HelperForFrontend.Query <T_Common_Sms>(strSql.ToString(), new { sms_max = type, sms_min = subType, bgNumber = bgNumber, edNumber = edNumber }).ToList(); } strSql.Clear(); int count = 0; if (string.IsNullOrEmpty(subType)) { strSql.Append("select count(*) from i200.dbo.T_Common_Sms " + "where sms_maxclass=@sms_max;"); count = HelperForFrontend.ExecuteScalar <int>(strSql.ToString(), new { sms_max = type }); } else { strSql.Append("select count(*) from i200.dbo.T_Common_Sms " + "where sms_maxclass=@sms_max and sms_class=@sms_min;"); count = HelperForFrontend.ExecuteScalar <int>(strSql.ToString(), new { sms_max = type, sms_min = subType }); } dic["list"] = CommonLib.Helper.JsonSerializeObject(list, "yyyy-MM-dd"); dic["rowCount"] = count.ToString(); int maxRow = count % 15 == 0 ? count / 15 : (count / 15 + 1); dic["maxRow"] = maxRow.ToString(); return(dic); }
/// <summary> /// 产品订单分析 /// </summary> /// <returns>{bus_name:名称,num:总数,quantity:数量,smsNum:短信数量,accNum:版本月数,money:金额,baifen:金额比,bus_mclass:类别}</returns> public OrderAnalysis GetBussinessOrderAnalyse(DateTime stDate, DateTime edDate) { StringBuilder strSql = new StringBuilder(); strSql.Append(" select oid,busId,busQuantity,RealPayMoney,(case when busId=0 then remark else '' end ) busName,null smsCount,null banCount,0 bus_mclass into #list from T_OrderInfo "); strSql.Append(" where createDate<= @endTime and createDate >= @statTime and "); strSql.Append(" orderStatus=2 and (OrderTypeId=1 or OrderTypeId=4); "); strSql.Append(" update #list set smsCount=a.smsCount , banCount=a.banCount from( "); strSql.Append(" select oid,Sum(case when itemId=1 then itemQuantity else 0 end) smsCount,Sum(case when itemId=3 then (itemQuantity *2) when itemId=2 then itemQuantity else 0 end) banCount "); strSql.Append(" from T_Order_List where oid in(select oid from #list) group by oid) a where a.oid=#list.oid; "); strSql.Append(" if(EXISTS(select * from #list where smsCount is null)) "); strSql.Append(" begin "); strSql.Append(" update #list set smsCount=a.smsCount , banCount=a.banCount from( "); strSql.Append(" select busId,Sum(case when itemId=1 then itemQuantity else 0 end) smsCount,Sum(case when itemId=3 then (itemQuantity*2) when "); strSql.Append(" itemId=2 then itemQuantity else 0 end) banCount from T_Order_Project_List where busId in(select busId from #list where smsCount is null) group by busId "); strSql.Append(" ) a where #list.smsCount is null and #list.banCount is null and #list.busId=a.busId ; "); strSql.Append(" end "); strSql.Append(" update #list set busName=T_Order_Project.displayName ,bus_mclass=projectType from T_Order_Project where T_Order_Project.busId=#list.busId ; "); strSql.Append(" select busName,bus_mclass,COUNT(oid) num,SUM(busQuantity) quantity,SUM(smsCount * busQuantity) smsNum,SUM(banCount * busQuantity) accNum, "); strSql.Append(" SUM(RealPayMoney) [money] from #list group by busName,bus_mclass "); #region 获取其他类型订单数据 strSql.Append(" union all "); strSql.Append(" select mg.AliasName busName,5 bus_mclass,COUNT(oi.oid) num,COUNT(oi.oid) quantity, "); strSql.Append(" 0 smsNum,0 accNum,SUM(oi.RealPayMoney) money from i200.dbo.T_OrderInfo oi "); strSql.Append(" left join [I200].[dbo].[T_MaterialGoods] mg on oi.busId=mg.GoodsId "); strSql.Append(" where oi.OrderTypeId=2 and orderStatus=1 and createDate<= @endTime and createDate >= @statTime "); strSql.Append(" group by mg.AliasName "); strSql.Append(" union all "); strSql.Append(" select '京东日百供货' busName,6 bus_mclass,COUNT(oid) num,COUNT(oid) quantity,0 smsNum,0 accNum,SUM(RealPayMoney) money "); strSql.Append(" from i200.dbo.T_OrderInfo where OrderTypeId=3 and orderStatus=2 and createDate<= @endTime and createDate >= @statTime; "); #endregion strSql.Append(" drop table #list; "); List <dynamic> ds = HelperForFrontend.Query <dynamic>(strSql.ToString(), new { endTime = edDate, statTime = stDate }).ToList(); int allNum = 0; int allQuantity = 0; int allSmsNum = 0; int allAccNum = 0; int allMoney = 0; OrderAnalysis orderAM = new OrderAnalysis(); if (ds.Count > 0) { foreach (dynamic dr in ds) { if (dr.num != 0) { OrderAnalysisItem itemModel = new OrderAnalysisItem(); if (dr.busName != null && dr.busName != "") { itemModel.busName = dr.busName.ToString(); } if (dr.bus_mclass != null && dr.bus_mclass != 0) { int bus_m = Convert.ToInt32(dr.bus_mclass); if (bus_m == 1) { itemModel.bus_mclass = "短信"; } else if (bus_m == 2) { itemModel.bus_mclass = "版本"; } else if (bus_m == 3) { itemModel.bus_mclass = "产品"; } else if (bus_m == 4) { itemModel.bus_mclass = "套餐"; } else if (bus_m == 5) { itemModel.bus_mclass = "个"; } else if (bus_m == 6) { itemModel.bus_mclass = "批"; } } if (dr.num != null && dr.num != 0) { itemModel.num = Convert.ToInt32(dr.num); } if (dr.quantity != null && dr.quantity != 0) { itemModel.quantity = Convert.ToInt32(dr.quantity); } if (dr.smsNum != null && dr.smsNum != 0) { itemModel.smsNum = Convert.ToInt32(dr.smsNum); } if (dr.accNum != null && dr.accNum != 0) { itemModel.accNum = Convert.ToInt32(dr.accNum) / 2; } if (dr.money != null && dr.money != 0) { itemModel.money = Convert.ToInt32(dr.money); } allNum += itemModel.num; allQuantity += itemModel.quantity; allSmsNum += itemModel.smsNum; allAccNum += itemModel.accNum; allMoney += itemModel.money; orderAM.itemList.Add(itemModel); } } } orderAM.num = allNum; orderAM.quantity = allQuantity; orderAM.smsNum = allSmsNum; orderAM.accNum = allAccNum; orderAM.money = allMoney; return(orderAM); }
/// <summary> /// 得到当前系统今天的统计信息 /// </summary> /// <param name="type">{新增店铺 "shop",新增会员 "Reg",新增商品 "goods",销售笔数和销售金额 "Sell",销售商品数 "saleGoods",当日支出 "Pay",当日短信 "Sms",当日订单 "Order",客户端登录 "kehuduan",登录 "loginNum"}</param> /// <returns></returns> public List <dynamic> GetIndexNowData(string type, DateTime nowDay) { #region 今天统计 StringBuilder strSql = new StringBuilder(); //店铺数,会员数,商品数,销售数,销售金额,支出数,支出金额,短信数,订单数,订单金额,微信,销售商品数,客户端,登录人数,IOS注册,安卓注册 List <dynamic> partData = new List <dynamic>(); strSql.Append(" declare "); strSql.Append(" @bgTime datetime , "); // 开始时间 strSql.Append(" @edTime datetime ; "); // 结束时间 strSql.Append(" set @bgTime=CAST(GETDATE() as date) ; "); // 短信数 strSql.Append(" set @edTime=GETDATE() ; "); // 订单数 switch (type) { //新增店铺 case "shop": strSql.Append(" select DATEPART(HOUR,RegTime) as Period,COUNT(ID) as shop,sum(case when Remark='11' then 1 else 0 end) as android,sum(case when Remark='10' then 1 else 0 end) as ios "); strSql.Append(" from I200.dbo.T_Account where RegTime between @BgTime and @EdTime and [State]=1 group by DATEPART(HOUR,RegTime) "); break; //新增会员 case "Reg": strSql.Append(" select DATEPART(HOUR,uRegTime) as Period,COUNT(uid) as Reg from I200.dbo.T_UserInfo where uRegTime between @BgTime and @EdTime group by DATEPART(HOUR,uRegTime) "); break; //新增商品 case "goods": strSql.Append(" select DATEPART(HOUR,gAddTime) as Period,COUNT(*) as goods from I200.dbo.T_GoodsInfo where gAddTime between @BgTime and @EdTime group by DATEPART(HOUR,gAddTime) "); break; //销售笔数和销售金额 case "Sell": strSql.Append(" select DATEPART(HOUR,insertTime) as Period,COUNT(saleID) as Sell,isnull(sum(RealMoney),0) as SellNum from I200.dbo.T_SaleInfo where insertTime between @BgTime and @EdTime and RealMoney<10000 and RealMoney>0 group by DATEPART(HOUR,insertTime) "); break; //销售商品数 case "saleGoods": strSql.Append(" select DATEPART(HOUR,insertTime) as Period,count(DISTINCT GoodsName) as saleGoods from I200.dbo.T_Sale_List where insertTime between @BgTime and @EdTime group by DATEPART(HOUR,insertTime) "); break; //当日支出 case "Pay": strSql.Append(" select DATEPART(HOUR,PayDate) as Period,COUNT(id) as Pay,isnull(SUM(PaySum),0) as PayNum from I200.dbo.T_PayRecord where PayDate between @BgTime and @EdTime group by DATEPART(HOUR,PayDate) "); break; //当日短信 case "Sms": strSql.Append(" select DATEPART(HOUR,sendTime) as Period,isnull(sum(realCnt),0) as Sms from I200.dbo.T_Sms_List where sendTime between @BgTime and @EdTime group by DATEPART(HOUR,sendTime) "); break; //当日订单 case "Order": strSql.Append(" select DATEPART(HOUR,transactionDate) as Period,COUNT(oid) as [Order],isnull(SUM(RealPayMoney),0) as OrderNum from I200.dbo.T_OrderInfo where transactionDate between @BgTime and @EdTime and (OrderStatus=2 or (OrderStatus=1 and OrderTypeId=2)) group by DATEPART(HOUR,transactionDate);"); break; //客户端登录 case "kehuduan": strSql.Append(" select DATEPART(HOUR,OperDate) as Period,COUNT(DISTINCT Accountid) as kehuduan from I200.dbo.T_LOG where LogMode like '3%' and OperDate between @BgTime and @EdTime group by DATEPART(HOUR,OperDate); "); break; //登录 case "loginNum": strSql.Append(" select DATEPART(HOUR,OperDate) as Period,COUNT(DISTINCT Accountid) as loginNum from I200.dbo.T_LOG where OperDate between @BgTime and @EdTime group by DATEPART(HOUR,OperDate)"); break; } #endregion partData = HelperForFrontend.Query <dynamic>(strSql.ToString(), new { date = nowDay }).ToList(); return(partData); }