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