public void OrderBrandProducesExport() { OrderBrandProducesQuery query = new OrderBrandProducesQuery(); StringBuilder sb = new StringBuilder(); DateTime dtime = new DateTime(); uint uid; #region 查詢條件 if (!string.IsNullOrEmpty(Request.Params["selecttype"])) { query.selecttype = Request.Params["selecttype"].ToString(); } if (!string.IsNullOrEmpty(Request.Params["searchcon"])) { query.searchcon = Request.Params["searchcon"].ToString(); } if (!string.IsNullOrEmpty(Request.Params["date_type"])) { query.date_type = Request.Params["date_type"].ToString(); } if (!string.IsNullOrEmpty(Request.Params["dateOne"])) { query.dateOne = DateTime.Parse(Request.Params["dateOne"]); } if (!string.IsNullOrEmpty(Request.Params["dateTwo"])) { query.dateTwo = DateTime.Parse(Request.Params["dateTwo"]); } if (!string.IsNullOrEmpty(Request.Params["slave_status"]) && Request.Params["slave_status"] != "null") { query.slave = Request.Params["slave_status"].ToString(); } if (uint.TryParse(Request.Params["order_payment"],out uid)) { query.order_payment = uid; } #endregion if (!System.IO.Directory.Exists(Server.MapPath(excelPath))) { System.IO.Directory.CreateDirectory(Server.MapPath(excelPath)); } DataTable dtHZ = new DataTable(); #region 表頭 dtHZ.Columns.Add("付款單號", typeof(String)); dtHZ.Columns.Add("供應商", typeof(String)); dtHZ.Columns.Add("品牌名稱", typeof(String)); dtHZ.Columns.Add("商品名稱", typeof(String)); dtHZ.Columns.Add("規格", typeof(String)); dtHZ.Columns.Add("付款方式", typeof(String)); dtHZ.Columns.Add("進貨價", typeof(String)); dtHZ.Columns.Add("實際售價", typeof(String)); dtHZ.Columns.Add("數量", typeof(String)); dtHZ.Columns.Add("折扣(購物金+抵用卷+折扣金額)", typeof(String)); dtHZ.Columns.Add("小計", typeof(String)); dtHZ.Columns.Add("促銷", typeof(String)); dtHZ.Columns.Add("訂購姓名", typeof(String)); dtHZ.Columns.Add("狀態", typeof(String)); dtHZ.Columns.Add("收件人", typeof(String)); dtHZ.Columns.Add("性別", typeof(String)); dtHZ.Columns.Add("郵遞區號", typeof(String)); //dtHZ.Columns.Add("地址", typeof(String)); //dtHZ.Columns.Add("收貨人手機", typeof(String)); //dtHZ.Columns.Add("收貨人電話", typeof(String)); dtHZ.Columns.Add("訂單日期", typeof(String)); dtHZ.Columns.Add("出貨日期", typeof(String)); dtHZ.Columns.Add("付款時間", typeof(String)); dtHZ.Columns.Add("備註", typeof(String)); dtHZ.Columns.Add("管理員備註", typeof(String)); dtHZ.Columns.Add("貨運模式", typeof(String)); dtHZ.Columns.Add("註冊時間", typeof(String)); dtHZ.Columns.Add("出生年月", typeof(String)); // dtHZ.Columns.Add("電子信箱", typeof(String)); #endregion try { List<OrderBrandProducesQuery> stores = new List<OrderBrandProducesQuery>(); _Iorderbrandproduces = new OrderBrandProducesMgr(mySqlConnectionString); stores = _Iorderbrandproduces.OrderBrandProducesExport(query, sb.ToString()); #region 填充data foreach (var item in stores) { DataRow dr = dtHZ.NewRow(); dr["付款單號"] = item.order_id; dr["供應商"] = item.vendor_name_simple; dr["品牌名稱"] = item.brand_name; dr["商品名稱"] = item.product_name; dr["規格"] = item.product_spec_name; dr["付款方式"] = item.payments; if (item.event_cost == 0) { dr["進貨價"] = item.single_cost; } else { dr["進貨價"] = item.event_cost; } if (item.item_mode == 2) { double price = double.Parse(item.single_money.ToString()) / item.buy_num; dr["實際售價"] = uint.Parse(Math.Round(price, 0).ToString()); dr["數量"] = item.buy_num * item.parent_num; dr["小計"] = item.single_money * item.parent_num - item.deduct_bonus - item.deduct_happygo_money - item.deduct_welfare; //dr["實際售價"] = item.single_money / item.buy_num; //dr["數量"] = item.buy_num*item.parent_num; }else{ dr["實際售價"] = item.single_money; dr["數量"] = item.buy_num; dr["小計"] = item.buy_num * item.single_money - item.deduct_bonus - item.deduct_happygo_money - item.deduct_welfare; } //dr["數量"] = item.buy_num; dr["折扣(購物金+抵用卷+折扣金額)"] = item.deduct_bonus + item.deduct_happygo_money + item.deduct_welfare; // dr["小計"] = item.buy_num * item.single_money - item.deduct_bonus - item.deduct_happygo_money - item.deduct_welfare; if (item.event_cost != 0 && item.single_cost != item.single_money) { dr["促銷"] = "是"; } else { dr["促銷"] = "-"; } dr["訂購姓名"] = item.order_name; dr["狀態"] = item.states; dr["收件人"] = item.delivery_name; dr["性別"] = item.delivery_genders; dr["郵遞區號"] = item.delivery_zips; dr["訂單日期"] = item.order_createdates; dtime = DateTime.Parse("1970/01/01 08:00:00"); if (item.slave_date_deliverys == dtime) { dr["出貨日期"] = "-"; } else { dr["出貨日期"] = item.slave_date_deliverys; } if (item.order_date_pays == dtime) { dr["付款時間"] = "未付款"; } else { dr["付款時間"] = item.order_date_pays; } dr["備註"] = item.note_order; dr["管理員備註"] = item.note_admin; #region 貨運模式 switch (item.product_freight_set.ToString()) { case "1": dr["貨運模式"] = "常溫"; break; case "2": dr["貨運模式"] = "冷凍"; break; case "3": dr["貨運模式"] = "常溫"; break; case "4": dr["貨運模式"] = "冷凍"; break; case "5": dr["貨運模式"] = "冷藏"; break; case "6": dr["貨運模式"] = "冷藏"; break; } #endregion dr["註冊時間"] = item.user_reg_dates; dr["出生年月"] = item.user_birthday; // dr["電子信箱"] = item.user_email; dtHZ.Rows.Add(dr); } #endregion if (dtHZ.Rows.Count > 0) { string fileName = "品牌訂單匯出_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; MemoryStream ms = ExcelHelperXhf.ExportDT(dtHZ, ""); Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); Response.BinaryWrite(ms.ToArray()); } else { Response.Write("匯出數據不存在"); } } 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); } }
//加購物金欄位 public HttpResponseBase GetOrderRevenue() { string json = string.Empty; string brandserch = "是否单个查询"; StringBuilder sb = new StringBuilder();//查詢訂單的條件 StringBuilder Sbandlist = new StringBuilder();//查詢賣場和管別的條件 OrderBrandProducesQuery oq = new OrderBrandProducesQuery(); uint id; int cid; try { if (!string.IsNullOrEmpty(Request.Params["Brand_Id"]))/*品牌編號*/ { oq.bid = Request.Params["Brand_Id"]; if (oq.bid.Substring(0, 1) != "0") { sb.AppendFormat("and p.brand_id in ({0})", oq.bid); Sbandlist.AppendFormat("and vb.brand_id in ( {0})", oq.bid); } } if (uint.TryParse(Request.Params["product_manage"], out id)) {//獲取管理人 oq.product_manage = id; if (id > 0) { sb.AppendFormat(" AND v.product_manage='{0}'", id); } } if (int.TryParse(Request.Params["channel"], out cid)) {//獲取管理人 oq.channel = cid; if (cid > 0) { sb.AppendFormat(" AND om.channel='{0}'", cid); } } if (uint.TryParse(Request.Params["slave_status"], out id)) {//獲取 訂單狀態 oq.slave_status = id; switch (id) { case 0: sb.AppendFormat(" and os.slave_status in(0,2,4,99,5,6)"); break; default: sb.AppendFormat(" and os.slave_status={0}", id); break; } } if (uint.TryParse(Request.Params["order_payment"], out id)) {//獲取 付款方式 oq.order_payment = id; if (id > 0) { sb.AppendFormat(" and om.order_payment={0}", id); } } if (!string.IsNullOrEmpty(Request.Params["selecttype"])) { oq.selecttype = Request.Params["selecttype"].ToString(); } if (!string.IsNullOrEmpty(Request.Params["searchcon"])) { oq.searchcon = Request.Params["searchcon"].ToString(); if (string.IsNullOrEmpty(oq.searchcon) && oq.searchcon != "null") {//查詢內容不為空就執行模糊查詢 switch (oq.selecttype) { case "1": sb.AppendFormat(" AND od.product_name LIKE '%{0}%'", oq.searchcon); break; case "2"://會員編號 sb.AppendFormat(" AND om.user_id LIKE '%{0}%'", oq.searchcon); break; case "3": sb.AppendFormat(" om.order_name LIKE '%{0}%'", oq.searchcon); break; default: break; } } } string sqlap = sb.ToString(); #region 日期條件 DateTime dtime; if (DateTime.TryParse(Request.Params["dateOne"], out dtime)) { oq.dateOne = DateTime.Parse(dtime.ToString("yyyy-MM-dd 00:00:00")); if (oq.dateOne > DateTime.MinValue) { sb.AppendFormat(" AND om.order_createdate >= '{0}' ", CommonFunction.GetPHPTime(oq.dateOne.ToString())); } } if (DateTime.TryParse(Request.Params["dateTwo"], out dtime)) { oq.dateTwo = DateTime.Parse(dtime.ToString("yyyy-MM-dd 23:59:59")); if (oq.dateTwo > DateTime.MinValue) { sb.AppendFormat(" AND om.order_createdate <= '{0}' ", CommonFunction.GetPHPTime(oq.dateTwo.ToString())); } } #endregion int a = 0; _vbrand = new VendorBrandMgr(mySqlConnectionString); _Iorderbrandproduces = new OrderBrandProducesMgr(mySqlConnectionString); dt = _vbrand.GetBandList(Sbandlist.ToString());//查詢品牌列表 if (string.IsNullOrEmpty(brandserch))//查询所有 { DataTable ds = _Iorderbrandproduces.GetOrderVendorRevenuebyday(sb.ToString(), brandserch);//查詢品牌列表中的營業額,之查詢每天的 #region 没有的品牌添加上 foreach (DataRow item in ds.Rows) { DataRow[] rows = dt.Select("brand_id='" + item["brand_id"] + "' and vendor_id='" + item["vendor_id"] + "'"); if (rows.Count() == 0) { DataRow drr = dt.NewRow(); drr[0] = item["brand_id"]; drr[1] = item["brand_name"]; drr[2] = item["vendor_id"]; drr[3] = item["vendor_name_simple"]; dt.Rows.Add(drr); } } #endregion } string q = a.ToString(); TimeSpan s = oq.dateTwo - oq.dateOne;//查詢的開始日期和結束日期相差的天數 int day = s.Days; #region 測試方法 _Iorderbrandproduces = new OrderBrandProducesMgr(mySqlConnectionString); DataColumn addcolms;//循環添加列每月的統計 DataRow addrow = dt.NewRow();//添加一行每日小計 addrow[3] = "每日小計"; int m = 0;//記錄增加了多少列,來確定每月一記的具體位置 int M_total = 0;//每月一記 StringBuilder html_table = new StringBuilder();//匯出html頁面 html_table.AppendFormat(@"<div style='overflow:auto;text-align:right;width:1650px;height:550px;'><table style='border:1px;'>"); html_table.AppendFormat("<tr><td>品牌名稱</td><td>供應商名稱</td>"); int startclome = 4;//設求每月之初的列的下標,初始為4/。 int stopclome = 3;//每個月結束的列的下標,初始為3/。 #region 循環每天 for (int d = 0; d <= day; d++)//查詢每天 { int D_bandsum = 0; //所有品牌每日的營業額 string daytimes = oq.dateOne.AddDays(d).ToString("yyyy-MM-dd");//循環的每一天 addcolms = new DataColumn(daytimes, typeof(String)); dt.Columns.Add(addcolms); html_table.AppendFormat("<td> {0} </td>", daytimes); stopclome++;//加一列 #region 每日一記 string daytimeone = DateTime.Parse(daytimes).ToString("yyyy-MM-dd 00:00:00");//把每天轉換成初始從零點開始 string daytimetwo = DateTime.Parse(daytimes).ToString("yyyy-MM-dd 23:59:59");//每天的結束時間 string orderby = string.Format(" AND om.order_createdate >= '{0}' AND om.order_createdate <= '{1}'", CommonFunction.GetPHPTime(daytimeone), CommonFunction.GetPHPTime(daytimetwo)); DataTable ds = _Iorderbrandproduces.GetOrderVendorRevenuebyday(sqlap + orderby, brandserch);//查詢品牌列表中的營業額,之查詢每天的 for (int i = 0; i < dt.Rows.Count; i++)//循環品牌列表,把每天的營業額追加進這個表裡 { DataRow[] rows = ds.Select("brand_id='" + dt.Rows[i]["brand_id"] + "' and vendor_id='" + dt.Rows[i]["vendor_id"] + "'");//篩選出一個品牌的這個時間段的營業額 dt.Rows[i][daytimes] = 0;//單個品牌每日的營業額先賦初始值為0 foreach (DataRow row in rows)//篩選出的最多只有一條數據,如果有,加入某個品牌的每日營業額,沒有就為初始值 { if (!string.IsNullOrEmpty(row["order_createdate"].ToString())) { string tb_createdate = CommonFunction.GetNetTime(long.Parse(rows[0]["order_createdate"].ToString())).ToString("yyyy-MM-dd"); dt.Rows[i][daytimes] = int.Parse(rows[0]["subtotal"].ToString()); D_bandsum = D_bandsum + int.Parse(rows[0]["subtotal"].ToString()); } } } #endregion #region 是否添加每月小計 addrow[4 + d + m] = D_bandsum; M_total = M_total + D_bandsum; if (oq.dateOne.Month != oq.dateTwo.Month || oq.dateOne.Year != oq.dateTwo.Year)//開始時間和結束時間不是在同一個月 { DateTime days = DateTime.Parse(daytimes); int t = DateTime.DaysInMonth(days.Year, days.Month);//一個月多少天 if (t == days.Day || d == day)//現在這個月的月底 { addcolms = new DataColumn(days.Month + "月小計", typeof(String));//style='float:left;width:500px' dt.Columns.Add(addcolms); html_table.AppendFormat("<td ><font color='red'> {0}月小計 </font></td>", days.Month); m++; foreach (DataRow item in dt.Rows) { item[stopclome + 1] = 0;//每月總計賦初始值 for (int i = startclome; i <= stopclome; i++)//每個品牌的每月總計 { item[stopclome + 1] = int.Parse(item[stopclome + 1].ToString()) + int.Parse(item[i].ToString()); } } addrow[stopclome + 1] = M_total; startclome = stopclome + 2; stopclome++; M_total = 0; } } #endregion } #endregion dt.Rows.InsertAt(addrow, 0);//添加一行總計 #region 品牌總計 addcolms = new DataColumn("品牌總計", typeof(String)); html_table.AppendFormat("<td><font color='red'>品牌總計</font></td></tr>"); dt.Columns.Add(addcolms); int S_total = 0;//所有品牌的總計 DataTable dstable = _Iorderbrandproduces.GetOrderVendorRevenuebyday(sb.ToString(), brandserch);//查詢品牌列表中的營業額 for (int i = 1; i < dt.Rows.Count; i++) { int Aull_bandSum = 0;//每個商品的開始時間和結束時間的總計 DataRow[] rows = dstable.Select("brand_id='" + dt.Rows[i]["brand_id"] + "' and vendor_id='" + dt.Rows[i]["vendor_id"] + "'");//篩選出一個品牌的這個時間段的營業額 foreach (DataRow row in rows) { if (!string.IsNullOrEmpty(row["subtotal"].ToString())) { Aull_bandSum = Aull_bandSum + int.Parse(row["subtotal"].ToString()); } S_total = S_total + Aull_bandSum; } dt.Rows[i]["品牌總計"] = Aull_bandSum; } dt.Rows[0]["品牌總計"] = S_total; #endregion #endregion #region 要查詢的列表,匯成一張table dt.Columns.RemoveAt(2);//移除多餘的列,供應商編號 dt.Columns.RemoveAt(0);//移除多餘的列,品牌編號 dt.Columns[0].ColumnName = "品牌名稱"; dt.Columns[1].ColumnName = "供應商名稱"; for (int x = 0; x < dt.Rows.Count; x++) { html_table.AppendFormat("<tr>"); for (int y = 0; y < dt.Columns.Count; y++) { if (dt.Columns[y].ColumnName.ToString().IndexOf("月小計") > 0 || dt.Columns[y].ColumnName.ToString() == "品牌總計") { html_table.AppendFormat("<td style='text-align:right'> <font color='red'>{0}</font> </td>", dt.Rows[x][y]); } else { html_table.AppendFormat("<td style='text-align:right'> {0} </td>", dt.Rows[x][y]); } if (y == dt.Columns.Count - 1) { html_table.AppendFormat("</tr>"); } } } html_table.AppendFormat(@"</table></div>"); #endregion string n = html_table.ToString(); json = "{success:true,msg:\"" + html_table.ToString() + "\"}"; } 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,totalCount:0,data:[]}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public HttpResponseBase GetOrderBrandProduces() { List<OrderBrandProducesQuery> stores = new List<OrderBrandProducesQuery>(); string json = string.Empty; DataTable dt = new DataTable(); DataTable dt_status = new DataTable(); try { StringBuilder sb = new StringBuilder(); OrderBrandProducesQuery query = new OrderBrandProducesQuery(); query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "20");//用於分頁的變量 #region 查詢條件 if (!string.IsNullOrEmpty(Request.Params["selecttype"])) { query.selecttype = Request.Params["selecttype"].ToString(); } if (!string.IsNullOrEmpty(Request.Params["searchcon"])) { query.searchcon = Request.Params["searchcon"].ToString(); } if (!string.IsNullOrEmpty(Request.Params["date_type"])) { query.date_type = Request.Params["date_type"].ToString(); } if (!string.IsNullOrEmpty(Request.Params["dateOne"])) { query.dateOne = DateTime.Parse(Request.Params["dateOne"]); } if (!string.IsNullOrEmpty(Request.Params["dateTwo"])) { query.dateTwo = DateTime.Parse(Request.Params["dateTwo"]); } if (!string.IsNullOrEmpty(Request.Params["slave_status"]) && Request.Params["slave_status"] != "null") { query.slave = Request.Params["slave_status"].ToString(); } if (!string.IsNullOrEmpty(Request.Params["order_payment"])) { query.order_payment = uint.Parse(Request.Params["order_payment"].ToString()); } #endregion _Iorderbrandproduces = new OrderBrandProducesMgr(mySqlConnectionString); int totalCount = 0; stores = _Iorderbrandproduces.GetOrderBrandProduces(query, out totalCount, sb.ToString()); dt = GetTP("payment"); dt_status = GetTP("order_status"); foreach (var item in stores) { if (item.item_mode == 2) {//如果是子商品數量*該組合的量 double price = double.Parse(item.single_money.ToString()) / item.buy_num; item.single_money = uint.Parse(Math.Round(price, 0).ToString()); item.buy_num = item.buy_num * item.parent_num; } if (item.order_payment > 0) {//帶出付款方式的參數 DataRow[] dr = dt.Select("ParameterCode = '" + item.order_payment.ToString() + "'"); DataTable _newdt = dt.Clone(); foreach (DataRow i in dr) { _newdt.Rows.Add(i.ItemArray); } item.payments = _newdt.Rows[0]["ParameterName"].ToString(); } if (item.slave_status >= 0) {//帶出付款方式的參數 DataRow[] dr = dt_status.Select("ParameterCode = '" + item.slave_status.ToString() + "'"); DataTable _newdt = dt_status.Clone(); foreach (DataRow i in dr) { _newdt.Rows.Add(i.ItemArray); } item.states = _newdt.Rows[0]["remark"].ToString(); } } IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; json = "{success:true,'msg':'user',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; }