/// <summary>
/// 发送短信查询
/// </summary>
/// <returns></returns>
public static DataTable GetBuyerInfoToMsg(string buyerNick, string lastTradeDate1, string lastTradeDate2, string grade, string num1, string num2,
string area, string tradeAmount1, string tradeAmount2, string sellerNick2, string drpDay, string tradePinNv, string buyCount, string Seller_Id)
{
try
{
string query = @"select buyer_id,buyer_nick,ISNULL(buyer_reallName,'unknown') AS buyer_reallName,cellPhone,status,grade,province,
trade_amount,group_id,item_num, CONVERT(varchar(100),last_trade_time, 23) as last_trade_time,memo from Buyer_" + Seller_Id + @" where cellPhone != '' and SELLER_ID = @SELLER_ID ";
List <SqlParameter> list = new List <SqlParameter>();
if (!string.IsNullOrEmpty(drpDay))
{
//过滤几天内发了的不再发
query += "and cellPhone not in (select cellPhone from MsgSendHis where DATEDIFF(dd,sendDate,getdate()) >= @filterDays and sellerNick = @SELLER_ID)";
SqlParameter p = new SqlParameter("@filterDays", drpDay);
list.Add(p);
//query += " and buyer_nick in( SELECT buyer_nick FROM TradeOrder WHERE seller_nick = @SELLER_ID AND status = 'TRADE_FINISHED' AND title like @productTitle) ";
//SqlParameter p = new SqlParameter("@productTitle", "%" + productTitle + "%");
//list.Add(p);
}
if (!string.IsNullOrEmpty(buyerNick))
{
query += " AND buyer_nick = @buyerNick ";
SqlParameter p1 = new SqlParameter("@buyerNick", buyerNick);
list.Add(p1);
}
if (!string.IsNullOrEmpty(lastTradeDate1))
{
query += " AND last_trade_time >= @last_trade_time1 ";
SqlParameter P2 = new SqlParameter("@last_trade_time1", Convert.ToDateTime(lastTradeDate1));
list.Add(P2);
}
if (!string.IsNullOrEmpty(lastTradeDate2))
{
query += " AND last_trade_time <= @last_trade_time2 ";
SqlParameter P3 = new SqlParameter("@last_trade_time2", Convert.ToDateTime(lastTradeDate2));
list.Add(P3);
}
if (!grade.Equals("all"))
{
query += " AND grade = @grade ";
SqlParameter P4 = new SqlParameter("@grade", grade);
list.Add(P4);
}
if (!string.IsNullOrEmpty(num1))
{
query += " AND item_num >= @item_num1 ";
SqlParameter P5 = new SqlParameter("@item_num1", num1);
list.Add(P5);
}
if (!string.IsNullOrEmpty(num2))
{
query += " AND item_num <= @item_num2 ";
SqlParameter P6 = new SqlParameter("@item_num2", num2);
list.Add(P6);
}
if (!area.Equals("all"))
{
query += " AND province like @area ";
SqlParameter P7 = new SqlParameter("@area", "%" + area + "%");
list.Add(P7);
}
if (!string.IsNullOrEmpty(tradeAmount1))
{
query += " AND trade_amount >= @tradeAmount1 ";
SqlParameter P8 = new SqlParameter("@tradeAmount1", tradeAmount1);
list.Add(P8);
}
if (!string.IsNullOrEmpty(tradeAmount2))
{
query += " AND trade_amount <= @tradeAmount2 ";
SqlParameter P9 = new SqlParameter("@tradeAmount2", tradeAmount2);
list.Add(P9);
}
if (!string.IsNullOrEmpty(buyCount))
{
query += " AND trade_count = @buyCount ";
SqlParameter P99 = new SqlParameter("@buyCount", buyCount);
list.Add(P99);
}
//按客户购买频率
if (!tradePinNv.Equals("all"))
{
if (tradePinNv.Equals("3"))
{
query += " AND last_trade_time <= DateAdd(m,-3,getdate()) ";
}
if (tradePinNv.Equals("6"))
{
query += " AND last_trade_time <= DateAdd(m,-6,getdate()) ";
}
if (tradePinNv.Equals("12"))
{
query += " AND last_trade_time <= DateAdd(m,-12,getdate()) ";
}
}
SqlParameter P11 = new SqlParameter("@SELLER_ID", sellerNick2);
list.Add(P11);
query += " order by last_trade_time desc ";
SqlParameter[] strParam = list.ToArray();
DataTable ds = DataBase.ExecuteDt(query, strParam, CommandType.Text);
return(ds);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}
/// <summary>
/// 获取所有会员信息
/// </summary>
/// <returns></returns>
public static DataTable GetBuyerInfo(string buyerNick, string status, string area, string grade, string group, string tradeAmount1,
string tradeAmount2, string sellerNick2, string buyCount, string Seller_Id)
{
try
{
string query = @"select buyer_id,buyer_nick,ISNULL(buyer_reallName,'unknown') AS buyer_reallName,cellPhone,status,grade,province,trade_amount,trade_count,group_id,item_num, CONVERT(varchar(100),last_trade_time, 23) as last_trade_time from Buyer_" + Seller_Id + " where SELLER_ID = @SELLER_ID ";
List <SqlParameter> list = new List <SqlParameter>();
if (!string.IsNullOrEmpty(buyerNick))
{
query += " AND buyer_nick = @buyerNick ";
SqlParameter p1 = new SqlParameter("@buyerNick", buyerNick);
list.Add(p1);
}
if (!status.Equals("all"))
{
query += " AND status = @status ";
SqlParameter P2 = new SqlParameter("@status", status);
list.Add(P2);
}
if (!area.Equals("all"))
{
query += " AND province like @area ";
SqlParameter P3 = new SqlParameter("@area", "%" + area + "%");
list.Add(P3);
}
if (!grade.Equals("all"))
{
query += " AND grade = @grade ";
SqlParameter P4 = new SqlParameter("@grade", grade);
list.Add(P4);
}
if (!string.IsNullOrEmpty(tradeAmount1))
{
query += " AND trade_amount >= @tradeAmount1 ";
SqlParameter P5 = new SqlParameter("@tradeAmount1", tradeAmount1);
list.Add(P5);
}
if (!string.IsNullOrEmpty(tradeAmount2))
{
query += " AND trade_amount <= @tradeAmount2 ";
SqlParameter P6 = new SqlParameter("@tradeAmount2", tradeAmount2);
list.Add(P6);
}
if (!string.IsNullOrEmpty(buyCount))
{
query += " AND trade_count = @buyCount ";
SqlParameter P66 = new SqlParameter("@buyCount", buyCount);
list.Add(P66);
}
if (!group.Equals("全部") && !String.IsNullOrEmpty(group))
{
if (group.Equals("新客户"))
{
query += "AND buyer_nick in(select T.buyer_nick from (select count(buyer_nick) as buyerCount,buyer_nick from Trade where status = 'TRADE_FINISHED' group by buyer_nick,seller_nick having count(buyer_nick) = 1 and seller_nick = @seller_nick1) as T inner join Buyer B on B.buyer_nick = T.buyer_nick where B.trade_amount > 0 group by T.buyer_nick)";
SqlParameter P7 = new SqlParameter("@seller_nick1", sellerNick2);
list.Add(P7);
}
if (group.Equals("老客户"))
{
query += "AND buyer_nick in(select T.buyer_nick from (select count(buyer_nick) as buyerCount,buyer_nick from Trade where status = 'TRADE_FINISHED' group by buyer_nick,seller_nick having count(buyer_nick) > 1 and seller_nick = @seller_nick1) as T inner join Buyer B on B.buyer_nick = T.buyer_nick where B.trade_amount > 0 group by T.buyer_nick)";
SqlParameter P8 = new SqlParameter("@seller_nick1", sellerNick2);
list.Add(P8);
}
if (group.Equals("休眠3个月"))
{
query += "AND buyer_nick in(select buyer_nick from Buyer where trade_amount > 0 and DATEDIFF(day,last_trade_time,getdate()) >= 90 and SELLER_ID = @seller_nick1 group by buyer_nick) ";
SqlParameter P9 = new SqlParameter("@seller_nick1", sellerNick2);
list.Add(P9);
}
if (group.Equals("潜在客户"))
{
query += "AND buyer_nick in(select buyer_nick from Buyer where trade_amount = 0 and SELLER_ID = @seller_nick1) ";
SqlParameter P10 = new SqlParameter("@seller_nick1", sellerNick2);
list.Add(P10);
}
}
SqlParameter P11 = new SqlParameter("@SELLER_ID", sellerNick2);
list.Add(P11);
query += " order by last_trade_time desc ";
SqlParameter[] strParam = list.ToArray();
DataTable ds = DataBase.ExecuteDt(query, strParam, CommandType.Text);
return(ds);
}
catch (Exception ex)
{
ExceptionReporter.WriteLog(ex, ExceptionPostion.TBApply_Data);
return(null);
}
}