/// <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);
}
}
/// <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="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>
/// 绑定店铺
/// <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);
}
}
/// <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);
}
}
/// <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);
}
/// <summary>
/// 添加日志
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool AddAlipayInfoLogBase(T_AlipayInfoLogModel model)
{
bool bResult = false;
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into T_AlipayInfoLog(");
strSql.Append("accId,createTime,columnName,oldValue,nowValue,lgUserId,lgUserIp");
strSql.Append(") values (");
strSql.Append("@accId,@createTime,@columnName,@oldValue,@nowValue,@lgUserId,@lgUserIp");
strSql.Append(") ");
strSql.Append(";select @@IDENTITY");
object ro = HelperForFrontend.ExecuteScalar(strSql.ToString(), model);
if (ro != null)
{
bResult = true;
}
return(bResult);
}
/// <summary>
/// 根据id获得提现金钱数
/// </summary>
/// <param name="withdrawalRecordId"></param>
/// <returns></returns>
public decimal GetWithdrawalMoneyByWithdrawalRecordId(int withdrawalRecordId)
{
decimal count = 0;
StringBuilder strSql = new StringBuilder();
strSql.Append("select withdrawalAmount from T_WithdrawalRecord where id=@withdrawalRecordId ");
try
{
count = HelperForFrontend.ExecuteScalar <decimal>(strSql.ToString(), new
{
withdrawalRecordId = withdrawalRecordId
});
}
catch (Exception ex)
{
count = 0;
}
return(count);
}
/// <summary>
/// 增加一条数据
/// </summary>
public int Add(T_Order_CouponInfo model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into i200.dbo.T_Order_CouponInfo(");
strSql.Append("couponDesc,maxLimitNum,createDate,endDate,operatorId,operarorIp,operatorTime,remark,couponType,bindType,bindValue,bindName,ruleType,ruleValue,couponValue,couponStatus,prefixAgent");
strSql.Append(") values (");
strSql.Append("@couponDesc,@maxLimitNum,@createDate,@endDate,@operatorId,@operarorIp,@operatorTime,@remark,@couponType,@bindType,@bindValue,@bindName,@ruleType,@ruleValue,@couponValue,@couponStatus,@prefixAgent");
strSql.Append(") ");
strSql.Append(";select @@IDENTITY");
object obj = HelperForFrontend.ExecuteScalar <int>(strSql.ToString(), new
{
couponDesc = model.couponDesc,
maxLimitNum = model.maxLimitNum,
createDate = model.createDate,
endDate = model.endDate,
operatorId = model.operatorId,
operarorIp = model.operarorIp,
operatorTime = model.operatorTime,
remark = model.remark,
couponType = model.couponType,
bindType = model.bindType,
bindValue = model.bindValue,
bindName = model.bindName,
ruleType = model.ruleType,
ruleValue = model.ruleValue,
couponValue = model.couponValue,
couponStatus = model.couponStatus,
prefixAgent = model.prefixAgent
});
if (obj == null)
{
return(0);
}
else
{
return(Convert.ToInt32(obj));
}
}
/// <summary>
/// 新增
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Add(T_Goods_Attribute model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into T_Goods_Attribute(");
strSql.Append("gaName,gaType,gaRemark,gaTime,gaAlive,accId");
strSql.Append(") values (");
strSql.Append("@gaName,@gaType,@gaRemark,@gaTime,@gaAlive,@accId");
strSql.Append(") ");
strSql.Append(";select @@IDENTITY");
object obj = HelperForFrontend.ExecuteScalar(strSql.ToString(), model);
if (obj != null)
{
return(Convert.ToInt32(obj));
}
else
{
return(0);
}
}
/// <summary>
/// 新增加一条数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int Add(T_OutLink model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into T_OutLink(");
strSql.Append("linkClass,PV,EndTime,ShortUrl,linktype,linkurl,remark,ClickCount,CreateTime,managerid,state,linkname");
strSql.Append(") values (");
strSql.Append("@linkClass,@PV,@EndTime,@ShortUrl,@linktype,@linkurl,@remark,@ClickCount,@CreateTime,@managerid,@state,@linkname");
strSql.Append(") ");
strSql.Append(";select @@IDENTITY");
object obj = HelperForFrontend.ExecuteScalar(strSql.ToString(), model);
if (obj != null)
{
return(Convert.ToInt32(obj));
}
else
{
return(0);
}
}
public string ChangeStatus(string maxName, string minName)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("declare @status int;");
strSql.Append(
"select @status=HiddenType from [i200].[dbo].[T_Common_Sms] where sms_maxclass=@maxName and sms_class=@minName;");
strSql.Append("select @status;");
int status = HelperForFrontend.ExecuteScalar <int>(strSql.ToString(), new { maxName = maxName, minName = minName });
strSql.Clear();
int update = (status == 0 ? 1 : 0);
strSql.Append(
"update [i200].[dbo].[T_Common_Sms] set HiddenType=@update where sms_maxclass=@maxName and sms_class=@minName;");
try
{
int reVal = HelperForFrontend.Execute(strSql.ToString(),
new { update = update, maxName = maxName, minName = minName });
if (reVal > 0)
{
return("1");
}
else
{
return("0");
}
}
catch (Exception ex)
{
return("0");
}
}
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>
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);
}