public List<UserVipListQuery> ExportVipListCsv(UserVipListQuery query,ref int totalCount)
{
try
{
string userIdString = _usersDao.GetViplistUserId(query);
if (!string.IsNullOrEmpty(userIdString))
{
//return _usersDao.ExportVipListCsv(query, userIdString);
return _usersDao.GetVipList(query, userIdString,ref totalCount);
}
else
{
List<Model.Query.UserVipListQuery> list = new List<UserVipListQuery>();
return list;
}
}
catch (Exception ex)
{
throw new Exception("UsersMgr-->ExportVipListCsv-->" + ex.Message, ex);
}
}
public HttpResponseBase GetVipList()
{
/********************************************************/
List<UserVipListQuery> stores = new List<UserVipListQuery>();
string json = string.Empty;
try
{
UserVipListQuery query = new UserVipListQuery();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
if (!string.IsNullOrEmpty(Request.Params["dateOne"]))
{
query.create_dateOne = (uint)CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Params["dateOne"]).ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(Request.Params["dateTwo"]))
{
query.create_dateTwo = (uint)CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Params["dateTwo"]).ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(Request.Params["user_id"]))
{
query.user_id = uint.Parse(Request.Params["user_id"]);
}
usersMgr = new UsersMgr(mySqlConnectionString);
int totalCount = 0;
stores = usersMgr.GetVipList(query, ref totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
// timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
//listUser是准备转换的对象
DataTable _dt = new DataTable();
foreach (var item in stores)
{
if (!string.IsNullOrEmpty(item.user_name))
{
item.user_name = item.user_name.Substring(0, 1) + "**";
}
//item.sum_bonus = item.normal_deduct_bonus + item.low_deduct_bonus;
//item.sum_amount = item.normal_product + item.low_product;
//獲取客單價的上限
decimal s = item.sum_amount / item.cou;
int sint = Convert.ToInt32(s);
item.aver_amount = s > sint ? sint + 1 : sint;
//獲取時間
item.reg_date = CommonFunction.GetNetTime(item.user_reg_date);
item.create_date = CommonFunction.GetNetTime(item.order_createdate);
item.birthday = item.user_birthday_year.ToString() + "/" + item.user_birthday_month.ToString() + "/" + item.user_birthday_day.ToString();
item.mytype = item.user_type == 1 ? "網絡會員" : "電話會員";
item.vip = "N";
_dt = usersMgr.IsVipUserId(item.user_id);
if (_dt.Rows.Count != 0)
{
item.vip = "Y";
}
}
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(stores, Formatting.Indented, timeConverter) + "}";//返回json數據
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:true,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase ExportVipListCsv()
{
string json = string.Empty;
UserVipListQuery query = new UserVipListQuery();
List<UserVipListQuery> stores = new List<UserVipListQuery>();
try
{
if (!string.IsNullOrEmpty(Request.Params["dateOne"]))
{
query.create_dateOne = (uint)CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Params["dateOne"]).ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(Request.Params["dateTwo"]))
{
query.create_dateTwo = (uint)CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Params["dateTwo"]).ToString("yyyy-MM-dd HH:mm:ss"));
}
if (!string.IsNullOrEmpty(Request.Params["user_id"]))
{
query.user_id = uint.Parse(Request.Params["user_id"]);
}
int totalCount = 0;
query.IsPage = false;
zMgr = new ZipMgr(mySqlConnectionString);
usersMgr = new UsersMgr(mySqlConnectionString);
stores = usersMgr.ExportVipListCsv(query, ref totalCount);
DataTable _vipdt = usersMgr.IsVipUserId(0);
DataTable newDt = new DataTable();
newDt.Columns.Add("user_id", typeof(string));
newDt.Columns.Add("user_status", typeof(string));
newDt.Columns.Add("user_name", typeof(string));
newDt.Columns.Add("user_gender", typeof(string));
newDt.Columns.Add("VIP", typeof(string));
// newDt.Columns.Add("user_email", typeof(string));
newDt.Columns.Add("age", typeof(string));
newDt.Columns.Add("user_birthday_month", typeof(string));
// newDt.Columns.Add("user_address", typeof(string));
newDt.Columns.Add("user_reg_date", typeof(string));//需要轉
newDt.Columns.Add("order_createdate", typeof(string));//需要轉
newDt.Columns.Add("last_time", typeof(string));//需要轉
newDt.Columns.Add("sum_amount", typeof(string));
newDt.Columns.Add("cou", typeof(string));
newDt.Columns.Add("aver_amount", typeof(string));//計算得出
newDt.Columns.Add("sum_bonus", typeof(string));
newDt.Columns.Add("normal_product", typeof(string));
newDt.Columns.Add("freight_normal", typeof(string));
newDt.Columns.Add("low_product", typeof(string));
newDt.Columns.Add("freight_low", typeof(string));
newDt.Columns.Add("ct", typeof(string));
newDt.Columns.Add("HG", typeof(string));
newDt.Columns.Add("ht", typeof(string));
newDt.Columns.Add("ml_code", typeof(string));
//newDt.Columns.Add("order_product_subtotal", typeof(string));
for (int i = 0; i < stores.Count; i++)
{
DataRow newRow = newDt.NewRow();
newRow["user_id"] = stores[i].user_id;
//newRow["user_status"] = stores[i].user_status;
switch (stores[i].user_status)
{
case 0: newRow["user_status"] = "未啟用";
break;
case 1: newRow["user_status"] = "已啟用";
break;
case 2: newRow["user_status"] = "停用";
break;
case 5: newRow["user_status"] = "簡易會員";
break;
}
newRow["user_name"] = stores[i].user_name;
newRow["user_gender"] = stores[i].user_gender == 0 ? "小姐" : "先生";
newRow["VIP"] = "N";
for (int j = 0; j < _vipdt.Rows.Count; j++)
{
if (_vipdt.Rows[j]["user_id"] != null)
{
if (stores[i].user_id.ToString() == _vipdt.Rows[j]["user_id"].ToString())
{
newRow["VIP"] = "Y";
break;
}
}
}
//newRow["user_email"] = stores[i].user_email;
newRow["age"] = DateTime.Now.Year - stores[i].user_birthday_year;
newRow["user_birthday_month"] = stores[i].user_birthday_month;
//if (zMgr.QueryCityAndZip(stores[i].user_zip.ToString()) != null)
//{
// newRow["user_address"] = zMgr.QueryCityAndZip(stores[i].user_zip.ToString()).middle + " " + zMgr.QueryCityAndZip(stores[i].user_zip.ToString()).small;
//}
//else
//{
// newRow["user_address"] = "";
//}
newRow["user_reg_date"] = CommonFunction.DateTimeToString(CommonFunction.GetNetTime(stores[i].user_reg_date));
newRow["order_createdate"] = CommonFunction.DateTimeToString(CommonFunction.GetNetTime(stores[i].order_createdate));
newRow["last_time"] = newRow["order_createdate"];
//if (stores[i].last_time == Convert.ToUInt32(CommonFunction.GetPHPTime("1970-1-1 8:00")))
//{
// newRow["last_time"] = "";
//}
//else
//{
// newRow["last_time"] = CommonFunction.DateTimeToString(CommonFunction.GetNetTime(stores[i].last_time));
//}
newRow["sum_amount"] = stores[i].sum_amount;
newRow["cou"] = stores[i].cou;
decimal s = stores[i].sum_amount / stores[i].cou;
int sint = Convert.ToInt32(s);
newRow["aver_amount"] = s > sint ? sint + 1 : sint;
newRow["sum_bonus"] = stores[i].sum_bonus;
newRow["normal_product"] = stores[i].normal_product;
newRow["freight_normal"] = stores[i].freight_normal;
newRow["low_product"] = stores[i].low_product;
newRow["freight_low"] = stores[i].freight_low;
newRow["ct"] = stores[i].ct;
newRow["HG"] = stores[i].ct;
newRow["ht"] = stores[i].ht;
newRow["ml_code"] = stores[i].ml_code;
//newRow["order_product_subtotal"] = stores[i].order_product_subtotal;
newDt.Rows.Add(newRow);
}
// string[] columnName = { "編號", "會員狀態", "姓名", "性別", "VIP", "電子郵件", "年齡", "生日月份", "居住區", "註冊時間", "最近歸檔日", "最近購買日", "購買金額", "購買次數", "客單價", "購物金使用", "常溫商品總額", "常溫商品運費", "低溫商品總額", "低溫商品運費", "中信折抵", "HG折抵", "台新折抵" };
string[] columnName = { "會員編號", "會員狀態", "姓名", "性別", "VIP", "年齡", "生日月份", "註冊時間", "最近歸檔日", "最近購買日", "購買金額", "購買次數", "客單價", "購物金使用", "常溫商品總額", "常溫商品運費", "低溫商品總額", "低溫商品運費", "中信折抵", "HG折抵", "台新折抵", "會員等級" };//, "近期累積金額" };
string fileName = "Vip_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv";
string newFileName = Server.MapPath(excelPath) + fileName;
json = "{success:'true',filename:'" + fileName + "'}";
CsvHelper.ExportDataTableToCsv(newDt, newFileName, columnName, true);
}
catch (Exception ex)
{
Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage();
logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message);
logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name;
log.Error(logMessage);
json = "{success:'false'}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
//create by shuangshuang0420j 20140923 17:44
public List<Model.Query.UserVipListQuery> GetVipList(Model.Query.UserVipListQuery uvlq,string userIdString, ref int totalCount)
{
StringBuilder sql = new StringBuilder();
try
{
uvlq.Replace4MySQL();
string timelimit = string.Empty;
//添加搜索條件
if (uvlq.create_dateOne != 0)
{
timelimit += string.Format(" and om.order_createdate >='{0}' ", uvlq.create_dateOne);
}
if (uvlq.create_dateTwo != 0)
{
timelimit += string.Format(" and om.order_createdate <='{0}' ", uvlq.create_dateTwo);
}
StringBuilder sbuser = new StringBuilder();
sbuser.Append(" select ");
sbuser.Append(" u.user_id,u.user_status,u.last_time,count(om.order_id) as cou,u.user_name ,u.ml_code, ");
sbuser.Append(" u.user_phone,u.user_mobile,u.user_gender,u.user_birthday_year,u.user_birthday_month,");
sbuser.Append(" u.user_birthday_day,u.user_type,u.adm_note,u.send_sms_ad,u.paper_invoice, u.user_password,u.user_reg_date,");
sbuser.Append(" sum(om.order_freight_low) as freight_low ,sum(om.order_freight_normal) as freight_normal, ");
sbuser.Append(" max( om.order_createdate ) as order_createdate,");
sbuser.Append(" round(sum(om.deduct_happygo_convert * om.deduct_happygo)) as happygo ");
sbuser.Append(" from order_master om inner join users u on u.user_id = om.user_id ");
sbuser.Append(" where om.order_status = 99 ");
sbuser.AppendFormat(" and om.user_id in ({0}) ", userIdString);
sbuser.Append(timelimit);
sbuser.Append(" group by om.user_id order by om.user_id ");
//order_status=99表示訂單已歸檔
//得到常溫商品總額集合sbNPro
StringBuilder sbNPro = new StringBuilder("");
sbNPro.Append(@" select om.user_id,sum(od.single_money * (case when od.item_mode=0 then od.buy_num
when od.item_mode=2 then od.parent_num end)) as normal_product ,sum(od.deduct_bonus) as normal_deduct_bonus");
sbNPro.Append(" from order_master om ");
sbNPro.Append(" left join order_slave os on os.order_id = om.order_id");
sbNPro.Append(" left join order_detail od on od.slave_id = os.slave_id");
sbNPro.AppendFormat(@" where od.product_freight_set in (1,3) and od.detail_status = 4 and om.order_status = 99
and od.item_mode in (0,2) and om.user_id in ({0}) ", userIdString);
sbNPro.Append(timelimit);
sbNPro.Append(" group by om.user_id order by om.user_id desc");
//得到低溫商品總額集合sbLPro
StringBuilder sbLPro = new StringBuilder("");
sbLPro.Append(@" select om.user_id,sum(od.single_money * (case when od.item_mode=0 then od.buy_num
when od.item_mode=2 then od.parent_num end)) as low_product,sum(od.deduct_bonus) as low_deduct_bonus ");
sbLPro.Append(" from order_master om ");
sbLPro.Append(" left join order_slave os on os.order_id = om.order_id");
sbLPro.Append(" left join order_detail od on od.slave_id = os.slave_id");
sbLPro.AppendFormat(@" where od.product_freight_set in (2,4,5,6) and od.detail_status = 4 and om.order_status = 99
and od.item_mode in (0,2) and om.user_id in ({0}) ", userIdString);
sbLPro.Append(timelimit);
sbLPro.Append(" group by om.user_id order by om.user_id desc");
//得到ct集合sbCT
StringBuilder sbCT = new StringBuilder("");
sbCT.Append(" select om.user_id, sum(opc.offsetamt) as ct ");
sbCT.Append(" from order_master om inner join order_payment_ct opc on om.order_id = opc.lidm ");
sbCT.AppendFormat(@" where om.order_status = 99 and om.user_id in ({0}) ", userIdString);
sbCT.Append(timelimit);
sbCT.Append(" group by om.user_id order by om.user_id desc");
//得到ht集合sbHT
StringBuilder sbHT = new StringBuilder("");
sbHT.Append(" select om.user_id, sum(oph.redem_discount_amount) as ht");
sbHT.Append(" from order_master om inner join order_payment_hitrust oph on om.order_id = oph.order_id");
sbHT.AppendFormat(@" where om.order_status = 99 and om.user_id in ({0}) ", userIdString);
sbHT.Append(timelimit);
sbHT.Append(" group by om.user_id order by om.user_id desc");
#region 手動插入數據
//// foreach ( var sbuserItem in sbuserList)
//// {
//// foreach (var sbNProItem in sbNProList)
//// {
//// if (sbuserItem.user_id == sbNProItem.user_id)
//// {
//// sbuserItem.normal_product = sbNProItem.normal_product;
//// sbuserItem.normal_deduct_bonus = sbNProItem.normal_deduct_bonus;
//// //sbuserItem.sum_amount + = sbNProItem.normal_product;
//// //sbuserItem.sum_bonus + = sbNProItem.normal_deduct_bonus;
//// }
//// }
//// foreach (var sbLProItem in sbLProList)
//// {
//// if (sbuserItem.user_id == sbLProItem.user_id)
//// {
//// sbuserItem.low_product = sbLProItem.low_product;
//// sbuserItem.low_deduct_bonus = sbLProItem.low_deduct_bonus;
//// //sbuserItem.sum_amount + = sbLProItem.low_product;
//// //sbuserItem.sum_bonus + = sbLProItem.low_deduct_bonus;
//// }
//// }
//// foreach (var sbCTItem in sbCTList)
//// {
//// if (sbuserItem.user_id == sbCTItem.user_id)
//// {
//// sbuserItem.ct = sbCTItem.ct;
//// }
//// }
//// foreach (var sbHTItem in sbHTList)
//// {
//// if (sbuserItem.user_id == sbHTItem.user_id)
//// {
//// sbuserItem.ht = sbHTItem.ht;
//// }
//// }
//// }
#endregion
sql.Append(@" select b.*,IFNULL(n.normal_product,0) as normal_product,IFNULL(l.low_product,0) as low_product,IFNULL(c.ct,0) as ct ,IFNULL(h.ht,0) as ht,
IFNULL(n.normal_deduct_bonus,0)+IFNULL(l.low_deduct_bonus,0) as sum_bonus,
IFNULL(n.normal_product,0)+IFNULL(l.low_product,0) as sum_amount ");
sql.AppendFormat(" from ( {0} ) b ", sbuser);
sql.AppendFormat(" left join ( {0} ) n on n.user_id = b.user_id", sbNPro);
sql.AppendFormat(" left join ( {0} ) l on l.user_id = b.user_id", sbLPro);
sql.AppendFormat(" left join ( {0} ) c on c.user_id = b.user_id", sbCT);
sql.AppendFormat(" left join ( {0} ) h on h.user_id = b.user_id", sbHT);
sql.AppendFormat(" where 1=1 ");//order by sum_amount DESC
//得到數據總條數
totalCount = 0;
if (uvlq.IsPage)
{
string sqlForCount = @"select count(s.user_id) as totalCount from (select u.user_id from order_master om inner join users u
on u.user_id = om.user_id where om.order_status = 99 " + timelimit + " group by om.user_id) s ";
System.Data.DataTable _dt = _accessMySql.getDataTable(sqlForCount);
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]);
}
//sql.AppendFormat(" limit {0},{1}", uvlq.Start, uvlq.Limit);//分頁
}
DataTable vipListDT = _accessMySql.getDataTable(sql.ToString());
List<Model.Query.UserVipListQuery> store = new List<UserVipListQuery>();
for (var i = 0; i < vipListDT.Rows.Count; i++)
{
Model.Query.UserVipListQuery query = new UserVipListQuery();
query.user_id = Convert.ToUInt32(vipListDT.Rows[i]["user_id"]);
query.user_status = Convert.ToUInt32(vipListDT.Rows[i]["user_status"]);
query.cou = Convert.ToInt64(vipListDT.Rows[i]["cou"]);
query.user_name = Convert.ToString(vipListDT.Rows[i]["user_name"]);
query.ml_code = Convert.ToString(vipListDT.Rows[i]["ml_code"]);
query.user_phone = Convert.ToString(vipListDT.Rows[i]["user_phone"]);
query.user_mobile = Convert.ToString(vipListDT.Rows[i]["user_mobile"]);
query.user_gender = Convert.ToUInt32(vipListDT.Rows[i]["user_gender"]);
query.user_birthday_year = Convert.ToUInt32(vipListDT.Rows[i]["user_birthday_year"]);
query.user_birthday_month = Convert.ToUInt32(vipListDT.Rows[i]["user_birthday_month"]);
query.user_birthday_day = Convert.ToUInt32(vipListDT.Rows[i]["user_birthday_day"]);
query.user_type = Convert.ToInt32(vipListDT.Rows[i]["user_type"]);
query.adm_note = Convert.ToString(vipListDT.Rows[i]["adm_note"]);
query.send_sms_ad = Convert.ToBoolean(vipListDT.Rows[i]["send_sms_ad"]);
query.paper_invoice = Convert.ToBoolean(vipListDT.Rows[i]["paper_invoice"]);
query.user_password = Convert.ToString(vipListDT.Rows[i]["user_password"]);
query.user_reg_date = Convert.ToUInt32(vipListDT.Rows[i]["user_reg_date"]);
query.last_time = Convert.ToUInt32(vipListDT.Rows[i]["last_time"]);
query.freight_low = Convert.ToDecimal(vipListDT.Rows[i]["freight_low"]);
query.freight_normal = Convert.ToDecimal(vipListDT.Rows[i]["freight_normal"]);
query.order_createdate = Convert.ToUInt32(vipListDT.Rows[i]["order_createdate"]);
query.happygo = Convert.ToDouble(vipListDT.Rows[i]["happygo"]);
query.normal_product = Convert.ToDecimal(vipListDT.Rows[i]["normal_product"]);
query.low_product = Convert.ToDecimal(vipListDT.Rows[i]["low_product"]);
query.ct = Convert.ToDecimal(vipListDT.Rows[i]["ct"]);
query.ht = Convert.ToDecimal(vipListDT.Rows[i]["ht"]);
query.sum_bonus = Convert.ToDecimal(vipListDT.Rows[i]["sum_bonus"]);
query.sum_amount = Convert.ToDecimal(vipListDT.Rows[i]["sum_amount"]);
store.Add(query);
}
return store;
}
catch (Exception ex)
{
throw new Exception("UsersDao-->GetVipList-->" + ex.Message + sql, ex);
}
}
