/// <summary>
/// 查询物流未签收的买家订单
/// </summary>
/// <param name="sellerNick"></param>
/// <param name="status"></param>
/// <returns></returns>
public static DataTable GetTradeDataNoSuccess(string sellerNick, string status)
{
try
{
string query = @"
select a.buyer_nick,a.tid,a.payment,a.createDate,'未签收,未收货' as stutas ,
b.company_name from NotifyTrade as a,tab_logistics as b where a.tid=b.tid and
a.status!='taobao_trade_TradeSuccess' and b.action not in('SIGNED','TRADE_SUCCESS')";
List <SqlParameter> list = new List <SqlParameter>();
if (!string.IsNullOrEmpty(sellerNick))
{
SqlParameter p1 = new SqlParameter("@nick", sellerNick);
list.Add(p1);
query += " and a.seller_nick =@nick";
}
SqlParameter[] strParam = list.ToArray();
DataTable dt = DataBase.ExecuteDt(query, strParam, CommandType.Text);
return(dt);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
public static DataTable GetAdminChongZhiMsg(string startDate, string endDate)
{
try
{
string query = @"SELECT id,
[MsgCount]
,[Price]
,[Date]
FROM AdminChongZhiMsg";
List <SqlParameter> list = new List <SqlParameter>();
if (!string.IsNullOrEmpty(startDate))
{
query += " where Date >= @startDate ";
SqlParameter p2 = new SqlParameter("@startDate", startDate);
list.Add(p2);
}
if (!string.IsNullOrEmpty(endDate))
{
query += " AND Date <= @endDate";
SqlParameter p3 = new SqlParameter("@endDate", endDate);
list.Add(p3);
}
query += " order by Date desc";
SqlParameter[] strParam = list.ToArray();
DataTable dt = DataBase.ExecuteDt(query, strParam, CommandType.Text);
return(dt);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
public static String GetBuyerCount(string flag, string sellerNick)
{
try
{
string query = "";
if (!string.IsNullOrEmpty(flag) && flag.Equals("1"))
{
query = @"select count(1) as buyerCount from Buyer where SELLER_ID = @nick";
}
else
{
query = @"select count(1) as buyerCount from Buyer where SELLER_ID = @nick and cellPhone is not null";
}
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@nick", sellerNick)
};
DataTable dt = DataBase.ExecuteDt(query, param, CommandType.Text);
if (dt != null && dt.Rows.Count == 1)
{
return(dt.Rows[0]["buyerCount"].ToString());
}
return("0");
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return("0");
}
}
/// <summary>
/// 查询批量评价日志表
/// </summary>
/// <param name="sellerNick"></param>
/// <returns></returns>
public DataTable GetAutoRateListByNick(string sellerNick, string startDate, string endRateDate)
{
try
{
string query = @"select created,tid,nick,content,rateType,result from SellerRateHis where 1 = 1 ";
if (!string.IsNullOrEmpty(sellerNick))
{
query += " AND nick = @sellerNick ";
}
if (!string.IsNullOrEmpty(startDate))
{
query += " AND created >= @startDate ";
}
if (!string.IsNullOrEmpty(endRateDate))
{
query += " AND created <= @rateDate ";
}
query += " order by created desc ";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@sellerNick", sellerNick),
new SqlParameter("@startDate", startDate),
new SqlParameter("@rateDate", endRateDate)
};
DataTable dt = DataBase.ExecuteDt(query, param, CommandType.Text);
return(dt);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
public static DataTable GetAdLogBack(string sellerNick, string starteDate, string endDate)
{
string query = @"select sellerNick,planCount,count(1) as accessCount,CONVERT(varchar(100),createTime,23) as accessDate from TuiguangLog where 1 = 1 ";
List <SqlParameter> list = new List <SqlParameter>();
if (!string.IsNullOrEmpty(sellerNick))
{
query += " AND sellerNick = @sellerNick ";
SqlParameter p1 = new SqlParameter("@sellerNick", sellerNick);
list.Add(p1);
}
if (!string.IsNullOrEmpty(starteDate))
{
query += " AND CONVERT(varchar(100),createTime,23) >= @startDate ";
SqlParameter P2 = new SqlParameter("@startDate", Convert.ToDateTime(starteDate));
list.Add(P2);
}
if (!string.IsNullOrEmpty(endDate))
{
query += " AND CONVERT(varchar(100),createTime,23) <= @endDate ";
SqlParameter P3 = new SqlParameter("@endDate", Convert.ToDateTime(endDate).AddDays(1));
list.Add(P3);
}
query += " group by CONVERT(varchar(100),createTime,23),sellerNick,planCount order by CONVERT(varchar(100),createTime,23) desc ";
SqlParameter[] strParam = list.ToArray();
DataTable dt = DataBase.ExecuteDt(query, strParam, CommandType.Text);
return(dt);
}
public static DataTable GetTuiGuangLog(string sellerNick, string startDate, string endDate)
{
try
{
string query = @"select * from TuiguangLog where 1 = 1 ";
List <SqlParameter> list = new List <SqlParameter>();
if (!string.IsNullOrEmpty(sellerNick))
{
query += " AND sellerNick = @sellerNick ";
SqlParameter p1 = new SqlParameter("@sellerNick", sellerNick);
list.Add(p1);
}
if (!string.IsNullOrEmpty(startDate))
{
query += " AND createTime >= @startDate ";
SqlParameter P2 = new SqlParameter("@startDate", Convert.ToDateTime(startDate));
list.Add(P2);
}
if (!string.IsNullOrEmpty(endDate))
{
query += " AND createTime <= @endDate ";
SqlParameter P3 = new SqlParameter("@endDate", Convert.ToDateTime(endDate).AddDays(1));
list.Add(P3);
}
query += " order by createTime desc";
SqlParameter[] strParam = list.ToArray();
DataTable dt = DataBase.ExecuteDt(query, strParam, CommandType.Text);
return(dt);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
/// <summary>
/// 查询卖家的差评列表
/// </summary>
/// <param name="sellerNick"></param>
/// <returns></returns>
public DataTable GetBadRateListByNick(string sellerNick, string startDate, string endRateDate)
{
try
{
string query = @"select badContent,badBuyerNick,sellerNick,realName + cellphone as namePhone,rateDate,orderInfo,memo
from BadList where sellerNick = @sellerNick ";
if (!string.IsNullOrEmpty(startDate))
{
query += " AND rateDate >= @startDate ";
}
if (!string.IsNullOrEmpty(endRateDate))
{
query += " AND rateDate <= @rateDate ";
}
query += " order by rateDate desc ";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@sellerNick", sellerNick),
new SqlParameter("@startDate", startDate),
new SqlParameter("@rateDate", endRateDate)
};
DataTable dt = DataBase.ExecuteDt(query, param, CommandType.Text);
return(dt);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
/// <summary>
/// 获取卖家当前买家的级别
/// </summary>
/// <param name="nickName"></param>
/// <returns></returns>
public static DataTable GetBuyerGrade(string nickName)
{
try
{
string query = @"select count(grade) as grade from Buyer where SELLER_ID = @SELLER_ID and grade = 0
union all
select count(grade) as grade from Buyer where SELLER_ID = @SELLER_ID and grade = 1
union all
select count(grade) as grade from Buyer where SELLER_ID = @SELLER_ID and grade = 2
union all
select count(grade) as grade from Buyer where SELLER_ID = @SELLER_ID and grade = 3
union all
select count(grade) as grade from Buyer where SELLER_ID = @SELLER_ID and grade = 4";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@SELLER_ID", nickName)
};
DataTable dt = DataBase.ExecuteDt(query, param, CommandType.Text);
return(dt);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
/// <summary>
/// 查询卖家当前自动评价状态
/// </summary>
/// <param name="sellerNick"></param>
/// <returns></returns>
public String GetAutoRateStatus(string sellerNick)
{
try
{
string query = "select isAutoRating from RateConfig where sellerNick = @sellerNick";
// 设置SQL参数
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@sellerNick", sellerNick)
};
DataTable dt = DataBase.ExecuteDt(query, param, CommandType.Text);
if (dt.Rows.Count > 0)
{
if (dt.Rows[0]["isAutoRating"].Equals(1))
{
return("已开启");
}
}
return("未开启");
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return("未开启");
}
}
//获取全部申请的记录
public static DataTable GetBuyerExportAll(string sellerId)
{
try
{
string query = "select export_time as 申请时间,(case export_status WHEN 0 THEN '申请结束' WHEN 1 THEN '申请中' WHEN 2 THEN '申请通过' end) as 申请状态 from Buyer_export where buyer_SellerId=@buyer_SellerId";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@buyer_SellerId", sellerId)
};
DataTable dt = DataBase.ExecuteDt(query, param, CommandType.Text);
if (dt != null && dt.Rows.Count > 0)
{
return(dt);
}
else
{
return(null);
}
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
public static DataTable GetBuyerNick(string nick, string CreateDate)
{
try
{
string query = @"SELECT *
FROM [UserTempMsgOrder] where nick = @nick and CreateDate<=@CreateDate";
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@nick", nick),
new SqlParameter("@CreateDate", CreateDate)
};
DataTable dt = DataBase.ExecuteDt(query, param, CommandType.Text);
if (dt != null && dt.Rows.Count == 1)
{
return(dt);
}
return(null);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
public int CheckEndStatus(string sellerId)
{
try
{
string query = "select top 1 export_status from Buyer_export where buyer_SellerId = @buyer_SellerId order by id desc";
// 设置SQL参数
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@buyer_SellerId", sellerId),
};
DataTable dt = DataBase.ExecuteDt(query, param, CommandType.Text);
if (dt != null && dt.Rows.Count > 0)
{
return(Convert.ToInt32(dt.Rows[0]["export_status"]));
}
else
{
return(0);
}
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(0);
}
}
/// <summary>
/// 根据省份
/// </summary>
/// <param name="SELLER_ID"></param>
/// <returns></returns>
public static DataTable GetProvinceBuyersCount(string sellerId, List <string> province)
{
try
{
string query = @"select buyer_nick,buyer_reallName,cellPhone,province from buyer_" + sellerId + @" where cellPhone != '' and (";
List <string> province1 = province;
for (int i = 0; i < province1.Count; i++)
{
if (i == 0)
{
query += "province like '%" + province1[i] + "'";
}
else
{
query += "or province like '%" + province1[i] + "'";
}
}
query += ")";
DataTable dt = DataBase.ExecuteDt(query);
return(dt);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
public DataTable GetCloseOrderLogByNick(string buerNick, string orderNo, string sellerNick)
{
try
{
string query = @"select * from CloseOrderLog where sellerNick = @sellerNick";
if (!string.IsNullOrEmpty(buerNick))
{
query += " AND buyerNick = @buyerNick ";
}
if (!string.IsNullOrEmpty(orderNo))
{
query += " AND ordreNo = @ordreNo ";
}
// 设置SQL参数
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@sellerNick", sellerNick),
new SqlParameter("@buyerNick", buerNick),
new SqlParameter("@ordreNo", orderNo)
};
DataTable dt = DataBase.ExecuteDt(query, param, CommandType.Text);
return(dt);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
public static DataTable GetExportBuyers(string SELLER_ID, string startDate, string endDate, string area, string tradeAmountFrom, string tradeAmountTo, string Seller_Id)
{
try
{
string query = @"select buyer_nick,ISNULL(buyer_reallName,'unknown') AS buyer_reallName,cellPhone,case grade when 0 then '潜在会员'
when 1 then '普通会员' when 2 then '高级会员' when 3 then 'VIP会员' when 4 then '至尊VIP会员' end as grade,
province,trade_amount,item_num, CONVERT(varchar(100),last_trade_time, 23) as last_trade_time,address, birthDay
from Buyer_" + Seller_Id + " where 1 = 1 ";
List <SqlParameter> list = new List <SqlParameter>();
if (!string.IsNullOrEmpty(SELLER_ID))
{
query += " AND SELLER_ID = @SELLER_ID ";
SqlParameter p1 = new SqlParameter("@SELLER_ID", SELLER_ID);
list.Add(p1);
}
if (!string.IsNullOrEmpty(startDate))
{
query += " AND last_trade_time >= @startDate ";
SqlParameter P2 = new SqlParameter("@startDate", startDate);
list.Add(P2);
}
if (!string.IsNullOrEmpty(endDate))
{
query += " AND last_trade_time <= @endDate ";
SqlParameter P3 = new SqlParameter("@endDate", endDate);
list.Add(P3);
}
if (!area.Equals("all"))
{
query += " AND province like @area ";
SqlParameter P4 = new SqlParameter("@area", "%" + area + "%");
list.Add(P4);
}
if (!string.IsNullOrEmpty(tradeAmountFrom))
{
query += " AND trade_amount >= @tradeAmountFrom ";
SqlParameter P5 = new SqlParameter("@tradeAmountFrom", tradeAmountFrom);
list.Add(P5);
}
if (!string.IsNullOrEmpty(tradeAmountTo))
{
query += " AND trade_amount <= @tradeAmountTo ";
SqlParameter P6 = new SqlParameter("@tradeAmountTo", tradeAmountTo);
list.Add(P6);
}
query += " order by last_trade_time desc";
SqlParameter[] strParam = list.ToArray();
DataTable dt = DataBase.ExecuteDt(query, strParam, CommandType.Text);
return(dt);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
//根据类型和二级标题获取msg
public static DataTable getMsgByPageType()
{
try
{
string query = "select num=row_number() over(order by id),* from tab_msgTemp order by page_type";
return(DataBase.ExecuteDt(query));;
}
catch
{
return(null);;
}
}
public static DataTable GetSellerNoDetailsInfo(string Seller_Id)
{
try
{
string query = @"select SELLER_ID,buyer_nick from Buyer_" + Seller_Id + @" where cellPhone =''";
return(DataBase.ExecuteDt(query));
}
catch (Exception ex)
{
return(null);
}
}
public static DataTable GetCompanyLst()
{
try
{
string query = @"select * from LogisticCompany ";
return(DataBase.ExecuteDt(query));
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
public static DataTable GetAllOPENIDs()
{
try
{
string query = @"select * from TuiGuangIDs order by status";
return(DataBase.ExecuteDt(query));
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
public static DataTable GetSystemOpenIDs()
{
try
{
string query = "select openid from TuiGuangIDs";
return(DataBase.ExecuteDt(query));
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
/// <summary>
/// 获取显示的系统公告
/// </summary>
/// <returns></returns>
public static DataTable QueryShowMsg()
{
try
{
string query = @"SELECT top 5 * FROM SystemMessages where Status=1 order by SystemDate desc";
return(DataBase.ExecuteDt(query));;
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
public static DataTable GetValidData()
{
try
{
string query = @"select sellerNick,itemNo from TuiguangPro where cast(endUseTime as datetime) >= getdate() and tuiStatus = 1 ";
DataTable dt = DataBase.ExecuteDt(query);
return(dt);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
public static DataTable GetIPProxyList()
{
try
{
string query = "select * from IPProxy";
DataTable dt = DataBase.ExecuteDt(query);
return(dt);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
/// <summary>
/// 活跃度高且购物能力强的会员
/// </summary>
/// <param name="SELLER_ID"></param>
/// <returns></returns>
public static DataTable GetHuoYueGaoGouMaiGaoBuyersCount(string sellerId)
{
try
{
string query = @"select buyer_nick,buyer_reallName,cellPhone from Buyer_" + sellerId + " where trade_amount >= 300 and DATEDIFF(day,last_trade_time,getdate()) > 5 and cellPhone != ''";
DataTable dt = DataBase.ExecuteDt(query);
return(dt);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
public static DataTable GetWhitelistForMarket()
{
try
{
string query = "select * from MsgSendBLK";
DataTable dt = DataBase.ExecuteDt(query);
return(dt);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
/// <summary>
/// 下单7天内还未付款的会员
/// </summary>
/// <param name="SELLER_ID"></param>
/// <returns></returns>
public static DataTable GetUnPay7DaysBuyersCount(string sellerId)
{
try
{
string query = @"select buyer_nick,ISNULL(buyer_reallName,'unknown') AS buyer_reallName,cellPhone from Buyer_" + sellerId + " where grade = 0 and DATEDIFF(day,last_trade_time,getdate()) <= 7 and cellPhone != ''";
DataTable dt = DataBase.ExecuteDt(query);
return(dt);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
/// <summary>
/// 添加卖家评价配置
/// </summary>
/// <param name="obj"></param>
/// <returns></returns>
public bool AddRateConfig(RateConfig obj)
{
try
{
string query = @"
insert into RateConfig(sellerNick,isMiaoRate,isWaitBuyerRate,waitBuyerTimeDay,waitBuyerTimeHour,waitBuyerTimeFen,blackBuyerRatedIsRate,
blackBuyerNoRateQiangRateDay,blackBuyerNoRateQiangRateHour,blackBuyerNoRateQiangRateFen,isQiangRate,
qiangRateTimeDay,qiangRateTimeHour,qiangRateTimeFen,atuoAddBlackListBadRate,atuoAddBlackListTuiKuan,badRateContent,result,content1,content2,content3,contentChoice,
createDate,isAutoRating)
values(@sellerNick,@isMiaoRate,@isWaitBuyerRate,@waitBuyerTimeDay,@waitBuyerTimeHour,@waitBuyerTimeFen,@blackBuyerRatedIsRate,
@blackBuyerNoRateQiangRateDay,@blackBuyerNoRateQiangRateHour,@blackBuyerNoRateQiangRateFen,@isQiangRate,
@qiangRateTimeDay,@qiangRateTimeHour,@qiangRateTimeFen,@atuoAddBlackListBadRate,@atuoAddBlackListTuiKuan,@badRateContent,@result,@content1,@content2,@content3,@contentChoice,
getdate(),0)";
// 设置SQL参数
SqlParameter[] param = new SqlParameter[]
{
new SqlParameter("@sellerNick", obj.SellerNick),
new SqlParameter("@isMiaoRate", obj.IsMiaoRate),
new SqlParameter("@isWaitBuyerRate", obj.IsWaitBuyerRate),
new SqlParameter("@waitBuyerTimeDay", obj.WaitBuyerTimeDay),
new SqlParameter("@waitBuyerTimeHour", obj.WaitBuyerTimeHour),
new SqlParameter("@waitBuyerTimeFen", obj.WaitBuyerTimeFen),
new SqlParameter("@blackBuyerRatedIsRate", obj.BlackBuyerRatedIsRate),
new SqlParameter("@blackBuyerNoRateQiangRateDay", obj.BlackBuyerNoRateQiangRateDay),
new SqlParameter("@blackBuyerNoRateQiangRateHour", obj.BlackBuyerNoRateQiangRateHour),
new SqlParameter("@blackBuyerNoRateQiangRateFen", obj.BlackBuyerNoRateQiangRateFen),
new SqlParameter("@isQiangRate", obj.IsQiangRate),
new SqlParameter("@qiangRateTimeDay", obj.QiangRateTimeDay),
new SqlParameter("@qiangRateTimeHour", obj.QiangRateTimeHour),
new SqlParameter("@qiangRateTimeFen", obj.QiangRateTimeFen),
new SqlParameter("@atuoAddBlackListBadRate", obj.AtuoAddBlackListBadRate),
new SqlParameter("@atuoAddBlackListTuiKuan", obj.AtuoAddBlackListTuiKuan),
new SqlParameter("@badRateContent", obj.BadRateContent == null ? "" : obj.BadRateContent),
new SqlParameter("@result", obj.Result == null ? "" : obj.Result),
new SqlParameter("@content1", obj.Content1 == null ? "" : obj.Content1),
new SqlParameter("@content2", obj.Content2 == null ? "" : obj.Content2),
new SqlParameter("@content3", obj.Content3 == null ? "" : obj.Content3),
new SqlParameter("@contentChoice", obj.ContentChoice)
};
DataTable dt = DataBase.ExecuteDt(query, param, CommandType.Text);
return(true);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(false);
}
}
public static DataTable GetExportBuyers(string sql)
{
try
{
string query = sql;
DataTable dt = DataBase.ExecuteDt(query);
return(dt);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
/// <summary>
/// 查询卖家物流已经签收，但未确认收货的买家的订单
/// </summary>
/// <param name="sellerNick"></param>
/// <param name="status"></param>
/// <returns></returns>
public static DataTable GetTradeData(string sellerNick, string startDate, string endDate, string startPay, string endDPay, string status)
{
try
{
string query = @"select a.buyer_nick,a.tid,a.payment,a.createDate,'已经签收，但未确认收货' as stutas,
b.company_name from NotifyTrade as a,tab_logistics as b where a.tid=b.tid and
a.status='taobao_trade_TradeSellerShip' and b.action='SIGNED' ";
List <SqlParameter> list = new List <SqlParameter>();
if (!string.IsNullOrEmpty(sellerNick))
{
SqlParameter p1 = new SqlParameter("@nick", sellerNick);
list.Add(p1);
query += " and a.seller_nick =@nick";
}
if (!string.IsNullOrEmpty(startDate))
{
SqlParameter p1 = new SqlParameter("@createdate", startDate);
list.Add(p1);
query += " and a.createdate >@createdate";
}
if (!string.IsNullOrEmpty(endDate))
{
SqlParameter p1 = new SqlParameter("@endcreatedate", endDate);
list.Add(p1);
query += " and a.createdate <@endcreatedate";
}
if (!string.IsNullOrEmpty(startPay))
{
SqlParameter p1 = new SqlParameter("@startPay", startPay);
list.Add(p1);
query += " and convert(numeric(6,2),a.payment) >@startPay";
}
if (!string.IsNullOrEmpty(endDPay))
{
SqlParameter p1 = new SqlParameter("@endDPay", endDPay);
list.Add(p1);
query += " and convert(numeric(6,2),a.payment) <@endDPay";
}
SqlParameter[] strParam = list.ToArray();
DataTable dt = DataBase.ExecuteDt(query, strParam, CommandType.Text);
return(dt);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
public static DataTable GetOldCusByMonth(string sellerNick, string year, bool week, bool month, bool ji)
{
try
{
string query = @"select count(1) as oldCusCount,@year + ' 年 '+ cast(datepart(month,last_trade_time) as nvarchar(50)) + ' 月' as Cusmonth from Buyer
where SELLER_ID = @seller_nick ";
List <SqlParameter> list = new List <SqlParameter>();
if (!string.IsNullOrEmpty(year))
{
query += " AND datepart(year,last_trade_time) = @year ";
SqlParameter p1 = new SqlParameter("@year", year);
list.Add(p1);
}
if (week)
{
query += " and DATEDIFF(day,last_trade_time,getdate()) <= 7 ";
}
if (month)
{
query += " and DATEDIFF(day,last_trade_time,getdate()) <= 30 ";
}
if (ji)
{
query += " and DATEDIFF(day,last_trade_time,getdate()) <= 90 ";
}
query += @" and buyer_nick in
(
select T.buyer_nick from (
select count(buyer_nick) as buyerCount,buyer_nick from Trade where seller_nick = @seller_nick and status = 'TRADE_FINISHED'
group by buyer_nick having count(buyer_nick) > 1
) as T
inner join Buyer B on B.buyer_nick = T.buyer_nick where B.SELLER_ID = @seller_nick and B.trade_amount > 0 group by T.buyer_nick
) group by datepart(month,last_trade_time) ";
if (!string.IsNullOrEmpty(sellerNick))
{
SqlParameter p2 = new SqlParameter("@seller_nick", sellerNick);
list.Add(p2);
}
SqlParameter[] strParam = list.ToArray();
return(DataBase.ExecuteDt(query, strParam, CommandType.Text));
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}