/// <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_OrderInfo "); 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); }
public string ChangeRanking(string maxName, string minName, int rank) { StringBuilder strSql = new StringBuilder(); strSql.Append( "update [i200].[dbo].[T_Common_Sms] set sms_ranking=@rank where sms_maxclass=@maxName and sms_class=@minName;"); try { int reVal = HelperForFrontend.Execute(strSql.ToString(), new { rank = rank, maxName = maxName, minName = minName }); if (reVal > 0) { return("1"); } else { return("0"); } } catch (Exception ex) { return("0"); } }
/// <summary> /// 更新 RandomNumber 主要用户手机橱窗和 优惠券 /// <para>如果已经存在 就不在 更新</para> /// </summary> /// <param name="accid"></param> /// <param name="randomNumber"></param> /// <returns></returns> public bool UpdateRandomNumber(int accid, string randomNumber) { StringBuilder strSql = new StringBuilder(); strSql.Append(" declare @randomNumber varchar(100);"); strSql.Append(" select @randomNumber=RandomNumber from T_Account where ID=@accid;"); strSql.Append(" if(isnull(@randomNumber,'')='')"); strSql.Append(" begin"); strSql.Append(" update T_Account set RandomNumber=@rm where ID=@accid;"); strSql.Append(" select @rm;"); strSql.Append(" end "); strSql.Append(" else "); strSql.Append(" begin"); strSql.Append(" select @randomNumber;"); strSql.Append(" end "); object r = HelperForFrontend.ExecuteScalar(strSql.ToString(), new { rm = randomNumber, accid = accid }); if (r != null) { return(true); } else { return(false); } }
/// <summary> /// 得到相关 操作人员信息 /// </summary> /// <param name="column"></param> /// <param name="strWhere"></param> /// <returns></returns> public List <dynamic> GetList(string column, string strWhere) { List <dynamic> dynamicList = new List <dynamic>(); StringBuilder strSql = new StringBuilder(); strSql.Append("select @column from Sys_Manage_User "); if (strWhere.Length > 0) { strSql.Append(" where @where"); } try { dynamicList = HelperForFrontend.Query <dynamic>(strSql.ToString(), new { column = column, where = strWhere }).ToList(); } catch (Exception ex) { dynamicList = null; } return(dynamicList); }
/// <summary> /// 获得前几行数据 /// </summary> public int GetPageCount(string strWhere) { int rouCount = 0; StringBuilder strSql = new StringBuilder(); strSql.Append("select count(*) "); 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"); } try { rouCount = HelperForFrontend.Query <int>(strSql.ToString()).ToList()[0]; } catch (Exception ex) { rouCount = 0; } return(rouCount); }
/// <summary> /// 增加一条发票信息 /// </summary> /// <param name="model"></param> /// <returns></returns> public int AddInvoice(T_Order_Invoice model) { int reVal = 0; var strSql = new StringBuilder(); strSql.Append("insert into T_Order_Invoice("); strSql.Append("accId,oid,createDate,invoiceMoney,invoiceName,invoiceDesc,invoicePhone,invoiceAddress,invoiceStatus,invoiceNo,invoiceRemark,invoiceOperatorId,invoiceOPeratorTime,invoiceAddressee)"); strSql.Append(" values ("); strSql.Append("@accId,@oid,@createDate,@invoiceMoney,@invoiceName,@invoiceDesc,@invoicePhone,@invoiceAddress,@invoiceStatus,null,null,null,null,@invoiceAddressee)"); strSql.Append(";select @@IDENTITY"); try { reVal = HelperForFrontend.ExecuteScalar <int>(strSql.ToString(), new { accId = model.accId, oid = model.oid, createDate = model.createDate, invoiceMoney = model.invoiceMoney, invoiceName = model.invoiceName, invoiceDesc = model.invoiceDesc, invoicePhone = model.invoicePhone, invoiceAddress = model.invoiceAddress, invoiceStatus = model.invoiceStatus, invoiceAddressee = model.invoiceAddressee }); } catch (Exception ex) { return(0); } return(reVal); }
/// <summary> /// 更新 发票信息 /// </summary> /// <param name="id"></param> /// <param name="invoiceNum"></param> /// <param name="remark"></param> /// <returns></returns> public bool UpdateInvoiceNumber(int id, string invoiceNum, string remark, string express, int operId) { bool status = false; StringBuilder strSql = new StringBuilder(); strSql.Append(" update T_Order_Invoice set "); strSql.Append(" invoiceNo=@invoiceNo,invoiceRemark=@invoiceRemark,invoiceOperatorId=@invoiceOperatorId,invoiceExpress=@express,invoiceOPeratorTime=GETDATE(),invoiceStatus=1 "); strSql.Append(" where id=@id"); try { int result = HelperForFrontend.Execute(strSql.ToString(), new { id = id, invoiceNo = invoiceNum, invoiceRemark = remark, invoiceOperatorId = operId, express = express }); if (result > 0) { status = true; } } catch (Exception ex) { status = false; } return(status); }
/// <summary> /// 新增加 /// </summary> /// <param name="model"></param> /// <returns></returns> public int Add(t_App_Au model) { StringBuilder strSql = new StringBuilder(); strSql.Append(" declare @oid int; "); strSql.Append(" select @oid=id from t_App_Au where accid=@accid and appkey=@appkey; "); strSql.Append(" if(isnull(@oid,0)>0) "); strSql.Append(" begin "); strSql.Append(" update t_App_Au set aa_Status = @aa_Status, appkey = @appkey , "); strSql.Append(" appName = @appName ,stattime = @stattime ,endtime = @endtime ,aa_time = GETDATE() , "); strSql.Append(" aa_remark = @aa_remark where accid=@accid and appkey=@appkey; "); strSql.Append(" select @oid; "); strSql.Append(" end "); strSql.Append(" else "); strSql.Append(" begin "); strSql.Append(" insert into t_App_Au(aa_Status,accid,appkey,appName,stattime,endtime,aa_time,aa_remark) "); strSql.Append(" values (@aa_Status,@accid,@appkey,@appName,@stattime,@endtime,@aa_time,@aa_remark) ; "); strSql.Append(" select @@IDENTITY; "); strSql.Append(" end "); object id = HelperForFrontend.ExecuteScalar(strSql.ToString(), model); if (id != null) { return(Convert.ToInt32(id)); } else { return(0); } }
public string AddCommonSms(string maxCate, string minCate, string smscontent) { StringBuilder strSql = new StringBuilder(); strSql.Append("declare @ranking varchar(10);if not exists( select id from i200.dbo.T_Common_Sms where sms_class='" + minCate + "') select @ranking='99';select @ranking=sms_ranking from i200.dbo.T_Common_Sms where sms_class='" + minCate + "';INSERT INTO i200.dbo.T_Common_Sms(sms_maxclass, sms_class, sms_content, sms_time, sms_ranking) VALUES ('" + maxCate + "','" + minCate + "','" + smscontent + "','" + System.DateTime.Now + "',@ranking)"); try { int reVal = HelperForFrontend.Execute(strSql.ToString()); if (reVal > 0) { return("1"); } else { return("0"); } } catch (Exception ex) { return("0"); } }
public decimal GetCouponByCondition(string strWhere) { decimal count = 0; StringBuilder strSql = new StringBuilder(); strSql.Append("select sum(couponMoney) from T_OrderInfo "); 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); }
public int Add(AlipayUserInfo model) { StringBuilder strSql = new StringBuilder(); strSql.Append("if(not exists(select accId from T_PaymentInfo where accId=@accId)) " + "begin " + "INSERT INTO T_PaymentInfo(accId, InfoStatus, alipayAccount, alipayPID, alipayKey, StoreName, StoreIndustry, StoreAdress, RealName, Phone, TelNumber, Email, Remark) " + "VALUES (@accId,0,@aliAccount,@aliPid,@aliKey,@accName,'','','',@phoneNumber,'','',''); " + "end " + "if(not exists(select accId from t_App_Au where accid=@accId and appkey=10)) " + "begin " + "INSERT INTO t_App_Au(accid, appkey, appName, stattime, endtime, aa_time, aa_remark, aa_ShortUrl, aa_Status) " + "VALUES (@accId,10,'支付宝收款',GETDATE(),'2015-12-31',GETDATE(),'','',1); " + "end "); try { return(HelperForFrontend.Execute(strSql.ToString(), new { accId = model.AccId, aliAccount = model.AliAccount, aliPid = model.AliPid, aliKey = model.AliKey, accName = model.AccName, phoneNumber = model.PhoneNum })); } catch (Exception ex) { Logger.Error("添加用户支付宝收款信息失败", ex); return(0); } }
public string UpdateCommonSmsContent(int smsid, string maxCate, string minCate, string smscontent) { StringBuilder strSql = new StringBuilder(); strSql.Append( "update i200.dbo.T_Common_Sms set sms_maxclass=@maxCate,sms_class=@minCate,sms_content=@content where id=@id;"); string result = "0"; try { int reVal = HelperForFrontend.Execute(strSql.ToString(), new { maxCate = maxCate, minCate = minCate, content = smscontent, id = smsid }); if (reVal > 0) { result = "1"; } } catch (Exception ex) { result = "0"; } return(result); }
/// <summary> /// 高级版退款处理 /// </summary> /// <param name="accid"></param> /// <param name="oper"></param> /// <param name="model"></param> /// <returns></returns> public bool AdvanceDrawback(int accid, int oper, OrderProjectItem model) { //DateTime nowDate = DateTime.Now; bool status = false; StringBuilder strSql = new StringBuilder(); strSql.Append("update [i200].[dbo].[T_Business] set endtime=DATEADD(MONTH,@quantity,endtime) where accountid=@accid;"); try { int reVal = HelperForFrontend.Execute(strSql.ToString(), new { quantity = -model.itemQuantity, accid = accid }); if (reVal > 0) { AddDrawbackRec(model.oid, accid, model.itemName, oper, 2, 0, model.itemQuantity, ""); return(true);//高级版退款成功 } else { Logger.Info("高级版退款没有找到对应记录!" + DateTime.Now + "-" + accid); return(false); } } catch (Exception ex) { Logger.Error("高级版退款(更新Business表)出错!", ex); return(false); } }
/// <summary> /// 短信退款处理 /// </summary> /// <param name="accid"></param> /// <param name="oper"></param> /// <param name="model"></param> /// <returns></returns> public bool SmsDrawback(int accid, int oper, OrderProjectItem model) { StringBuilder strSql = new StringBuilder(); strSql.Append("declare @dx int;" + "set @dx=0;" + "" + "select @dx=dxunity from [i200].[dbo].[T_Business] where accountid=@accid; " + "if @dx>=@quantity " + "update [i200].[dbo].[T_Business] set dxunity=dxunity-@quantity where accountid=@accid " + "else " + "update [i200].[dbo].[T_Business] set dxunity=0 where accountid=@accid ;"); try { int reVal = HelperForFrontend.Execute(strSql.ToString(), new { quantity = model.itemQuantity, accid = accid }); if (reVal > 0) { AddDrawbackRec(model.oid, accid, model.itemName, oper, 2, 0, model.itemQuantity, ""); return(true);//短信条数重置成功 } else { Logger.Info("短信退款重置没有找到对应记录!" + DateTime.Now + "-" + accid); return(false); } } catch (Exception ex) { Logger.Error("短信退款重置(更新Business表)出错!", ex); return(false); } }
/// <summary> /// 绑定店铺 /// <para>{-1:处理错误,0:优惠券不存在,1:优惠券已使用或者已经作废,2:绑定成功}</para> /// </summary> /// <param name="accountid">店铺ID</param> /// <param name="CouponID">优惠券编号</param> /// <returns></returns> public int BindingAccount(int accountid, string CouponID) { StringBuilder strSql = new StringBuilder(); strSql.Append(" declare @stat int; "); strSql.Append(" select @stat=couponStatus from T_Order_CouponList where couponId=@CouponID; "); strSql.Append(" if(@stat is null) "); strSql.Append(" begin "); strSql.Append(" select 0; "); strSql.Append(" end "); strSql.Append(" else if(@stat=0) "); strSql.Append(" begin "); strSql.Append(" update T_Order_CouponList set couponStatus=2,toAccId=@account,receiveDate=getdate(),bindWay=1 where couponId=@CouponID; "); strSql.Append(" select 2; "); strSql.Append(" end "); strSql.Append(" else "); strSql.Append(" select 1; "); object rId = HelperForFrontend.ExecuteScalar(strSql.ToString(), new { account = accountid, CouponID = CouponID }); if (rId != null) { return(Convert.ToInt32(rId)); } else { return(-1); } }
public int UpdateExpressAddress(int oid, int accId, string expressCompany, string expressCode) { StringBuilder strSql = new StringBuilder(); strSql.Append("select ReceivingAddressId from [i200].[dbo].[T_OrderInfo] where oid=@oid and accId=@accId;"); int receiveId = DapperHelper.ExecuteScalar <int>(strSql.ToString(), new { oid = oid, accId = accId }); strSql.Clear(); strSql.Append( "update [i200].[dbo].[T_Receiving_Address] set ExpressCompany=@expressCompany,ExpressCode=@expressCode where AddressId=@addressId;"); try { int reVal = HelperForFrontend.Execute(strSql.ToString(), new { expressCompany = expressCompany, expressCode = expressCode, addressId = receiveId }); return(reVal); } catch (Exception ex) { Logger.Error("更新实物商品物流信息出错!", ex); return(0); } }
/// <summary> /// 更新订单状态 /// </summary> /// <param name="returnMoney"></param> /// <param name="oid"></param> /// <param name="accId"></param> /// <returns></returns> public bool UpdateOrderStatus(decimal returnMoney, int oid, int accId) { StringBuilder strSql = new StringBuilder(); strSql.Append( "update [i200].[dbo].[T_OrderInfo] set orderStatus=-2,returnMoney=@money where oid=@oid and accId=@accId; "); try { int reVal = HelperForFrontend.Execute(strSql.ToString(), new { money = returnMoney, oid = oid, accId = accId }); if (reVal > 0) { return(true); } else { Logger.Info("没有找到订单记录!" + oid); return(false); } } catch (Exception ex) { Logger.Error("退款更新订单状态出错!", ex); return(false); } }
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()); }
/// <summary> /// 处理任务 /// </summary> /// <param name="id"></param> /// <returns>{-1:出现异常,0:信息不存在,1:已经处理完成,2、处理完成}</returns> public int HandleTask(int id) { StringBuilder sqlStr = new StringBuilder(); sqlStr.Append(" declare @now int,@new int,@num int,@acc_id int,@status int,@t_explan varchar(200),@t_type int;"); sqlStr.Append(" select @acc_id=acc_id,@status=t_status,@t_explan=t_explan from T_Task_Journal where id=@id ;"); sqlStr.Append(" if(@acc_id is null) "); sqlStr.Append(" begin "); sqlStr.Append(" select 0; ");//信息不存在 sqlStr.Append(" end "); sqlStr.Append(" else "); sqlStr.Append(" begin "); sqlStr.Append(" if(@status=0) "); sqlStr.Append(" begin "); sqlStr.Append(" set @num=0; "); sqlStr.Append(" select @now=isnull(integral,0) from T_Business where accountid=@acc_id; "); sqlStr.Append(" if(@t_explan='分享生意专家') "); sqlStr.Append(" begin "); sqlStr.Append(" set @num=30;set @t_type=6; "); sqlStr.Append(" end "); sqlStr.Append(" else if(@t_explan='百度推广') "); sqlStr.Append(" begin "); sqlStr.Append(" set @num=50;set @t_explan='支持生意专家';set @t_type=5; "); sqlStr.Append(" end "); sqlStr.Append(" else if(@t_explan='推荐好友') "); sqlStr.Append(" begin "); sqlStr.Append(" set @num=100;set @t_type=10; "); sqlStr.Append(" end "); sqlStr.Append(" else if(@t_explan='关注微信') "); sqlStr.Append(" begin "); sqlStr.Append(" set @num=100;set @t_type=9; "); sqlStr.Append(" end "); sqlStr.Append(" if(@num>0) "); sqlStr.Append(" begin "); sqlStr.Append(" set @new=@now+@num; "); sqlStr.Append(" INSERT INTO T_LogInfo(accID, LogType, Keys, OriginalVal, EditVal, FinialVal, CreatTime, ReMark,Flags) "); sqlStr.Append(" VALUES (@acc_id,@t_type,'Integral',@now,@num,@new,GETDATE(),@t_explan,isnull(@num,0)); "); sqlStr.Append(" update T_Business set integral=@new where accountid=@acc_id; "); sqlStr.Append(" update T_Task_Journal set t_status=1 where id=@id; "); sqlStr.Append(" select 2; "); //处理完成 sqlStr.Append(" end "); sqlStr.Append(" end "); sqlStr.Append(" else "); sqlStr.Append(" select 1; "); //已经处理完成 sqlStr.Append(" end "); object rid = HelperForFrontend.ExecuteScalar(sqlStr.ToString(), new { id = id }); if (rid != null) { return(Convert.ToInt32(rid)); } else { return(-1); } }
public AlipayInfoModel GetAlipayInfoModel(int id) { StringBuilder strSql = new StringBuilder(); strSql.Append(" select top 1 [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(" from T_AlipayInfo "); strSql.Append(" where id=@id"); return(HelperForFrontend.GetModel <AlipayInfoModel>(strSql.ToString(), new { id = id })); }
/// <summary> /// 初始化一个店铺信息 /// </summary> /// <param name="accid"></param> public void InitializeUserInfor(int accid) { StringBuilder strSql = new StringBuilder(); strSql.Append(" if(NOT exists(select ID from tb_user_infor where UserId=@accid)) "); strSql.Append(" begin"); strSql.Append(" insert into tb_user_infor(UserId) values(@accid); "); strSql.Append(" end "); HelperForFrontend.Execute(strSql.ToString(), new { accid = accid }); }
/// <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); }
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> /// 根据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="groupId"></param> /// <param name="couponCode"></param> /// <param name="couponValue"></param> /// <param name="endTime"></param> /// <returns></returns> public int CreateCouponGroup(int groupId, string couponCode, int couponValue, DateTime endTime) { StringBuilder strSql = new StringBuilder(); strSql.Append("INSERT INTO T_Order_CouponList(groupId, couponId, couponValue, couponStatus, createDate, endDate, toAccId)"); strSql.Append(" VALUES (@couponInfoId,@couponCode,@couponValue,0,GETDATE(),@endDate,0)"); int rt = HelperForFrontend.Execute(strSql.ToString(), new { couponInfoId = groupId, couponCode = couponCode, couponValue = couponValue, endDate = endTime }); return(rt); }
/// <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="channelId">通道Id</param> /// <returns></returns> public int GetChannelSum(int channelId) { int iResult = -1; StringBuilder strSql = new StringBuilder(); strSql.Append("select sum(realCnt) as smsCnt from T_Sms_List where smsStatus=1 and smsChannel=@smsChannel;"); var sResult = HelperForFrontend.ExecuteScalar(strSql.ToString(), new { smsChannel = channelId }); if (sResult != null && sResult != DBNull.Value) { iResult = Convert.ToInt32(sResult); } return(iResult); }
/// <summary> /// 获取订单新增商品总数 /// </summary> /// <param name="stDate"></param> /// <param name="edDate"></param> /// <returns></returns> public string GetDailyNewGoodsNumSummary(DateTime stDate, DateTime edDate) { string iResult = string.Empty; StringBuilder strSql = new StringBuilder(); strSql.Append( "select count(*) cnt from [i200].[dbo].[t_GoodsExtend] " + "where ge_stat=1 and updateTime between @stDate and @edDate "); var sResult = HelperForFrontend.ExecuteScalar(strSql.ToString(), new { stDate = stDate, edDate = edDate }); if (sResult != null && sResult != DBNull.Value) { iResult = sResult.ToString(); } return(iResult); }