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