public List <ConsumeOrderData> QueryConsumeInfo(string memberID, string consumeID, DateTime st, DateTime et)
{
List <ConsumeOrderData> order_list = new List <ConsumeOrderData>();
DataSet ds = ExecuteDataSet(@"select order_id,member_id,dt,consume_value,score,pledge_amount,total_amount,operator_id,comment,from_value_card,pay_mode,pay_value from sales where member_id=@memberID
and dt BETWEEN @dt and @et and order_id like @consumeID GROUP BY dt DESC",
new MySqlParameter("@memberID", memberID),
new MySqlParameter("@dt", st),
new MySqlParameter("@et", et),
new MySqlParameter("@consumeID", @"%" + consumeID + @"%")
);
if (null != ds && ds.Tables.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
ConsumeOrderData data = BuildOrderData(dr);
if (null != data)
{
order_list.Add(data);
}
}
}
return(order_list);
}
private ConsumeOrderData BuildOrderData2(DataRow dr)
{
ConsumeOrderData data = new ConsumeOrderData();
data.OrderID = (string)dr["order_id"];
if (!string.IsNullOrEmpty(dr["dt"].ToString()))
{
data.ConsumeDate = ((DateTime)dr["dt"]).ToString(@"yyyy-MM-dd HH:mm:ss");
}
if (!string.IsNullOrEmpty(dr["total_amount"].ToString()))
{
data.TotalMoney = (decimal)dr["total_amount"];
}
if (!string.IsNullOrEmpty(dr["member_id"].ToString()))
{
data.MemberID = (string)dr["member_id"];
}
if (!string.IsNullOrEmpty(dr["name"].ToString()))
{
data.MemberName = (string)dr["name"];
}
if (!string.IsNullOrEmpty(dr["mobile"].ToString()))
{
data.Mobile = (string)dr["mobile"];
}
if (!string.IsNullOrEmpty(dr["score"].ToString()))
{
data.Integral = (int)dr["score"];
}
if (!string.IsNullOrEmpty(dr["pledge_amount"].ToString()))
{
data.Deposit = (decimal)dr["pledge_amount"];
}
if (!string.IsNullOrEmpty(dr["from_value_card"].ToString()))
{
data.CardPay = (decimal)dr["from_value_card"];
}
if (!string.IsNullOrEmpty(dr["operator_id"].ToString()))
{
UserDAL dal = new UserDAL();
data.Operator = dal.QueryByOptrID((int)dr["operator_id"]).name;
}
if (!string.IsNullOrEmpty(dr["comment"].ToString()))
{
data.Remark = (string)dr["comment"];
}
int pay_mode = 0;
if (!string.IsNullOrEmpty(dr["pay_mode"].ToString()))
{
pay_mode = Convert.ToInt32(dr["pay_mode"].ToString());
}
data.PayMode = ui.PayMode.getStringbyMode(pay_mode);
return(data);
}
public List <ConsumeOrderData> QueryConsumeInfByMemInfo(string begin_date, string end_date, string mem_type, string mem_id, string opt_name)
{
StringBuilder temp = new StringBuilder();
List <MySqlParameter> parameters = new List <MySqlParameter>();
temp.Append(@"select member.mobile, member.`name`,
sales.member_id, sales.order_id, sales.dt, sales.consume_value,
sales.total_amount, sales.discounted_value, sales.pledge_amount, sales.score,
sales.from_value_card, sales.pay_mode,
sales.operator_id, sales.comment
from sales left join member on sales.member_id = member.uuid
where sales.dt between @begin_date and @end_date");
parameters.Add(new MySqlParameter("@begin_date", begin_date));
parameters.Add(new MySqlParameter("@end_date", end_date));
if (!"所有类型".Equals(mem_type))
{
temp.Append(" and member.card_name = @card_type");
parameters.Add(new MySqlParameter("@card_type", mem_type));
}
if (opt_name != "所有操作员" && !string.IsNullOrEmpty(opt_name))
{
temp.Append(" and sales.operator_id = @operator");
UserDAL userdal = new UserDAL();
int optr_id = userdal.QueryByUserName(opt_name).OptrID;
parameters.Add(new MySqlParameter("@operator", optr_id));
}
if ("" != mem_id)
{
temp.Append(" and (member.card_id like @mem_card_id or member.name like @mem_name or member.mobile like @mobile) ");
parameters.Add(new MySqlParameter("@mem_card_id", "%" + mem_id + "%"));
parameters.Add(new MySqlParameter("@mem_name", "%" + mem_id + "%"));
parameters.Add(new MySqlParameter("@mobile", "%" + mem_id + "%"));
}
temp.Append(" order by sales.dt desc ");
List <ConsumeOrderData> order_list = new List <ConsumeOrderData>();
DataSet ds = ExecuteDataSet(temp.ToString(), parameters.ToArray());
if (null != ds && ds.Tables.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
ConsumeOrderData data = BuildOrderData2(dr);
if (null != data)
{
order_list.Add(data);
}
}
}
return(order_list);
}
public List <ConsumeOrderData> QueryFailedConsumeInfo(string memberID)
{
List <ConsumeOrderData> order_list = new List <ConsumeOrderData>();
DataSet ds = ExecuteDataSet(@"SELECT
order_id,
member_id,
dt,
consume_value,
score,
pledge_amount,
total_amount,
operator_id,
comment,
pay_mode
FROM
sales_failed
WHERE
member_id =@memberID
AND recording_state = 0
ORDER BY
dt DESC" ,
new MySqlParameter("@memberID", memberID)
);
if (null != ds && ds.Tables.Count > 0)
{
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
DataRow dr = ds.Tables[0].Rows[i];
ConsumeOrderData data = BuildOrderData(dr);
if (null != data)
{
order_list.Add(data);
}
}
}
return(order_list);
}
private ConsumeOrderData BuildOrderData(DataRow dr)
{
ConsumeOrderData data = new ConsumeOrderData();
data.OrderID = (string)dr["order_id"];
if (!string.IsNullOrEmpty(dr["dt"].ToString()))
{
data.ConsumeDate = ((DateTime)dr["dt"]).ToString(@"yyyy-MM-dd HH:mm:ss");
}
if (!string.IsNullOrEmpty(dr["total_amount"].ToString()))
{
data.TotalMoney = (decimal)dr["total_amount"];
}
if (!string.IsNullOrEmpty(dr["member_id"].ToString()))
{
data.MemberID = (string)dr["member_id"];
}
if (!string.IsNullOrEmpty(dr["score"].ToString()))
{
data.Integral = (int)dr["score"];
}
if (!string.IsNullOrEmpty(dr["pledge_amount"].ToString()))
{
data.Deposit = (decimal)dr["pledge_amount"];
}
if (!string.IsNullOrEmpty(dr["operator_id"].ToString()))
{
UserDAL dal = new UserDAL();
data.Operator = dal.QueryByOptrID((int)dr["operator_id"]).name;
}
if (!string.IsNullOrEmpty(dr["comment"].ToString()))
{
data.Remark = (string)dr["comment"];
}
int pay_mode = 0;
if (!string.IsNullOrEmpty(dr["pay_mode"].ToString()))
{
pay_mode = (int)dr["pay_mode"];
}
data.PayMode = @"现金";
switch (pay_mode)
{
case (int)PAYMODE.PAYMODE_MEMBER_CARD:
data.PayMode = @"储值卡";
break;
case (int)PAYMODE.PAYMODE_WEIXIN:
data.PayMode = @"红码"; // todo 微信 为红码支付
break;
case (int)PAYMODE.PAYMODE_ALI:
data.PayMode = @"支付宝";
break;
case (int)PAYMODE.PAYMODE_EWALLENT:
data.PayMode = @"会员电子钱包";
break;
case (int)PAYMODE.PAYMODE_VOUCHERS:
data.PayMode = @"代金券";
break;
case (int)PAYMODE.PAYMODE_BANKCARD:
data.PayMode = @"银行卡";
break;
case (int)PAYMODE.PAYMODE_UNITE:
data.PayMode = @"联合付款";
break;
default:
break;
}
return(data);
}
