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); } }