public void HandleAccountPhone(DateTime dateTime)
{
ArrayList al = new ArrayList();
StringBuilder builder1 = new StringBuilder();
builder1.AppendFormat(MemberSqls.SELECT_LEEK_PHONE_BY_DATE_FLAG, dateTime.ToString("yyyy-MM-dd"));
string sql1 = builder1.ToString();
DataTable dt1 = DatabaseOperationWeb.ExecuteSelectDS(sql1, "T").Tables[0];
if (dt1 != null && dt1.Rows.Count > 0)
{
string phone = "";
foreach (DataRow dr in dt1.Rows)
{
if (phone == "")
{
phone = "'" + dr["phone"].ToString() + "'";
}
else
{
phone += ",'" + dr["phone"].ToString() + "'";
}
}
if (phone != "")
{
try
{
DatabaseOperationWeb.TYPE = new DBManagerZE();
StringBuilder builder2 = new StringBuilder();
builder2.AppendFormat(MemberSqls.SELECT_RECHARGE_LIST_BY_PHONES_3MONTHAGO,
dateTime.AddMonths(-3).ToString("yyyy-MM-dd"), dateTime.AddDays(-1).ToString("yyyy-MM-dd"), phone);
string sql2 = builder2.ToString();
DataTable dt2 = DatabaseOperationWeb.ExecuteSelectDS(sql2, "T").Tables[0];
if (dt2 != null && dt2.Rows.Count > 0)
{
string ids = "";
foreach (DataRow dr2 in dt2.Rows)
{
DataRow[] drs = dt1.Select("phone='" + dr2["chat_user_id"].ToString() + "'");
if (drs.Length > 0)
{
if (ids == "")
{
ids = "'" + drs[0]["PID"].ToString() + "'";
}
else
{
ids += ",'" + drs[0]["PID"].ToString() + "'";
}
}
}
if (ids != "")
{
StringBuilder builder3 = new StringBuilder();
builder3.AppendFormat(MemberSqls.UPDATE_MEMBER_LEEK_BY_PHONE, ids);
string sql3 = builder3.ToString();
al.Add(sql3);
}
}
}
catch (Exception ex)
{
}
finally
{
DatabaseOperationWeb.TYPE = new DBManager();
}
}
}
if (al.Count > 0)
{
DatabaseOperationWeb.ExecuteDML(al);
}
}
public void getAccountSelectList()
{
StringBuilder builder = new StringBuilder();
builder.AppendFormat(MemberSqls.SELECT_MAX_ACCOUNT_DAY);
string sql = builder.ToString();
DataTable dt = DatabaseOperationWeb.ExecuteSelectDS(sql, "T").Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
double proportion = getProportion();
DateTime maxDate = Convert.ToDateTime(dt.Rows[0][0]).AddDays(1);
for (DateTime i = maxDate; i < DateTime.Now.AddDays(-1); i = i.AddDays(1))
{
HandleAccountPhone(Convert.ToDateTime("2019-12-01"));
ArrayList al = new ArrayList();
string dateStr = i.ToString("yyyy-MM-dd");
StringBuilder builder1 = new StringBuilder();
builder1.AppendFormat(MemberSqls.SELECT_LEEK_PHONE_BY_DATE, i.AddDays(1).ToString("yyyy-MM-dd"));
string sql1 = builder1.ToString();
DataTable dt1 = DatabaseOperationWeb.ExecuteSelectDS(sql1, "T").Tables[0];
if (dt1 != null && dt1.Rows.Count > 0)
{
string phone = "";
foreach (DataRow dr in dt1.Rows)
{
if (phone == "")
{
phone = "'" + dr["phone"].ToString() + "'";
}
else
{
phone += ",'" + dr["phone"].ToString() + "'";
}
}
try
{
DatabaseOperationWeb.TYPE = new DBManagerZE();
StringBuilder builder2 = new StringBuilder();
builder2.AppendFormat(MemberSqls.SELECT_RECHARGE_LIST_BY_PHONES, dateStr, phone);
string sql2 = builder2.ToString();
DataTable dt2 = DatabaseOperationWeb.ExecuteSelectDS(sql2, "T").Tables[0];
if (dt2 != null && dt2.Rows.Count > 0)
{
foreach (DataRow dr2 in dt2.Rows)
{
DataRow[] drs = dt1.Select("phone='" + dr2["chat_user_id"].ToString() + "'");
if (drs.Length > 0)
{
double pro = proportion;
double.TryParse(drs[0]["proportion"].ToString(), out pro);
double price = Convert.ToDouble(dr2["ALLREC_MONEY"]) * pro;
StringBuilder builder3 = new StringBuilder();
builder3.AppendFormat(MemberSqls.ADD_ACCOUNT, dateStr, drs[0]["LEEK_MEMBER_ID"].ToString(),
drs[0]["LEEK_NAME"].ToString(), drs[0]["PHONE"].ToString(),
Convert.ToDouble(dr2["ALLREC_MONEY"]), price.ToString(),
drs[0]["MEMBER_ID"].ToString(), drs[0]["RESELLER_TYPE"].ToString());
string sql3 = builder3.ToString();
al.Add(sql3);
}
}
}
}
catch (Exception ex)
{
}
finally
{
DatabaseOperationWeb.TYPE = new DBManager();
}
}
StringBuilder builder4 = new StringBuilder();
builder4.AppendFormat(MemberSqls.ADD_ACCOUNT_LOG, al.Count, dateStr);
string sql4 = builder4.ToString();
al.Add(sql4);
DatabaseOperationWeb.ExecuteDML(al);
}
}
}
public OrderListItem getOrder(string openid, string orderId)
{
OrderListItem orderListItem = new OrderListItem();
string sql = "select merchantOrderId,tradeTime,status,tradeAmount,waybillno,waybilltype,payTime,consigneeName," +
"consigneeMobile,addrProvince,addrCity,addrDistrict,addrDetail " +
"from t_order_list " +
"where merchantOrderId = '" + orderId + "' and customerCode = '" + openid + "' ";
DataTable dt = DatabaseOperationWeb.ExecuteSelectDS(sql, "t_goods_list").Tables[0];
if (dt.Rows.Count > 0)
{
string slt = "";
string pro = "";
string sql1 = "select barCode,skuBillName,slt,quantity,skuUnitPrice " +
"from t_order_goods " +
"where merchantOrderId = '" + orderId + "' " +
"group by barCode";
DataTable dt1 = DatabaseOperationWeb.ExecuteSelectDS(sql1, "t_goods_list").Tables[0];
if (dt1.Rows.Count > 0)
{
List <OrderGoodsListItem> orderGoodsList = new List <OrderGoodsListItem>();
for (int j = 0; j < dt1.Rows.Count; j++)
{
OrderGoodsListItem orderGoodsListItem = new OrderGoodsListItem();
orderGoodsListItem.barCode = dt1.Rows[j]["barCode"].ToString();
orderGoodsListItem.slt = dt1.Rows[j]["slt"].ToString();
orderGoodsListItem.skuUnitPrice = dt1.Rows[j]["skuUnitPrice"].ToString();
orderGoodsListItem.quantity = dt1.Rows[j]["quantity"].ToString();
orderGoodsListItem.skuBillName = dt1.Rows[j]["skuBillName"].ToString();
orderGoodsList.Add(orderGoodsListItem);
}
orderListItem.orderGoodsList = orderGoodsList;
}
orderListItem.billId = dt.Rows[0]["merchantOrderId"].ToString();
orderListItem.imgUrl = slt;
orderListItem.createTime = dt.Rows[0]["tradeTime"].ToString();
orderListItem.payTime = Convert.ToDateTime(dt.Rows[0]["payTime"].ToString()).ToString("yyyy-MM-dd HH:mm:ss");
orderListItem.waybilltype = dt.Rows[0]["waybilltype"].ToString();
orderListItem.waybillno = dt.Rows[0]["waybillno"].ToString();
//orderListItem.product = pro.Substring(0, pro.Length - 1);
orderListItem.total = dt.Rows[0]["tradeAmount"].ToString();
orderListItem.consigneeName = dt.Rows[0]["consigneeName"].ToString();
orderListItem.consigneeMobile = dt.Rows[0]["consigneeMobile"].ToString();
orderListItem.addr = dt.Rows[0]["addrProvince"].ToString() + dt.Rows[0]["addrCity"].ToString() + dt.Rows[0]["addrDistrict"].ToString() + dt.Rows[0]["addrDetail"].ToString();
string status = dt.Rows[0]["status"].ToString();
if (status == "1" || status == "2")
{
status = "等待发货";
}
else if (status == "4" || status == "6")
{
status = "已完成";
}
else if (status == "3")
{
status = "已发货";
}
orderListItem.status = status;
return(orderListItem);
}
else
{
return(null);
}
}
public OrderListResult getOrderList(string openid, string shop)
{
string st = "";
if (shop != "")
{
st = " and l.purchaserCode = '" + shop + "' ";
}
OrderListResult orderListResult = new OrderListResult();
string sql = "select l.merchantOrderId,l.tradeTime,l.status,l.tradeAmount,l.payTime from t_order_list l "
+ " where l.sendapi = 'XXC' and l.customerCode = '" + openid + "' and l.`status` != 0 " + st + " order by id desc ";
DataTable dt = DatabaseOperationWeb.ExecuteSelectDS(sql, "t_goods_list").Tables[0];
if (dt.Rows.Count > 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
string slt = "";
string pro = "";
string sql1 = "select barCode,max(skuBillName) goodsname,max(slt) slt " +
"from t_order_goods " +
"where merchantOrderId = '" + dt.Rows[i]["merchantOrderId"].ToString() + "' " +
"group by barCode";
DataTable dt1 = DatabaseOperationWeb.ExecuteSelectDS(sql1, "t_goods_list").Tables[0];
if (dt1.Rows.Count > 0)
{
slt = dt1.Rows[0]["slt"].ToString();
for (int j = 0; j < dt1.Rows.Count; j++)
{
pro += dt1.Rows[j]["goodsname"].ToString() + ",";
}
}
OrderListItem orderListItem = new OrderListItem();
orderListItem.billId = dt.Rows[i]["merchantOrderId"].ToString();
orderListItem.imgUrl = slt;
orderListItem.createTime = dt.Rows[i]["tradeTime"].ToString();
orderListItem.payTime = dt.Rows[i]["payTime"].ToString();
orderListItem.product = pro.Substring(0, pro.Length - 1);
orderListItem.total = dt.Rows[i]["tradeAmount"].ToString();
string status = dt.Rows[i]["status"].ToString();
if (status == "1" || status == "2")
{
status = "等待发货";
}
else if (status == "4" || status == "5")
{
status = "已完成";
}
else if (status == "3")
{
status = "已发货";
}
orderListItem.status = status;
orderListResult.orderList.Add(orderListItem);
}
return(orderListResult);
}
else
{
return(new OrderListResult());
}
}
public DataTable getGoods(string barcode)
{
string sql = "select id,thumb,goodsname,price,stock from t_goods_list where barcode = '" + barcode + "'";
return(DatabaseOperationWeb.ExecuteSelectDS(sql, "t_goods_list").Tables[0]);
}
public string getDBTest()
{
return(DatabaseOperationWeb.ExecuteSelectDS("select now()", "Test").Tables[0].Rows[0][0].ToString());
}
public List <ActiveConsumeInfo> getActiveConsumeListByActiveType()
{
List <ActiveConsumeInfo> list = new List <ActiveConsumeInfo>();
StringBuilder builder = new StringBuilder();
builder.AppendFormat(OrderSqls.SELECT_ACTIVECONSUMELIST_BY_ACTIVETYPE);
string sql = builder.ToString();
DataTable dt = DatabaseOperationWeb.ExecuteSelectDS(sql, "T").Tables[0];
if (dt != null && dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
string activeState = dr["active_state"].ToString();
string activeType = dr["active_type"].ToString();
string valueType = dr["value_type"].ToString();
if (activeState == "-1")
{
activeState = "已结束";
}
else if (activeState == "1")
{
activeState = "进行中";
}
if (valueType == "0")
{
valueType = "赠送礼品";
}
else if (valueType == "1")
{
valueType = "赠送心值";
}
else if (valueType == "2")
{
valueType = "提高兑换上限";
}
string activeImg = dr["active_img"].ToString();
if (activeImg == "")
{
activeImg = "http://bonus-gift-server.oss-cn-beijing.aliyuncs.com/imgs/cardImg.jpg";
}
ActiveConsumeInfo activeConsumeInfo = new ActiveConsumeInfo
{
activeId = dr["active_id"].ToString(),
activeStore = dr["active_store"].ToString(),
remark = dr["remark"].ToString(),
activeState = activeState,
activeType = "到店签到",
activeTimeFrom = dr["active_time_from"].ToString().Substring(0, 10),
activeTimeTo = dr["active_time_to"].ToString().Substring(0, 10),
consume = dr["consume"].ToString(),
activeConsumeId = dr["active_consume_id"].ToString(),
itemNums = dr["item_nums"].ToString(),
itemValue = dr["item_value"].ToString(),
valueType = valueType,
storeName = dr["store_name"].ToString(),
activeImg = activeImg,
};
list.Add(activeConsumeInfo);
}
}
return(list);
}