public void ExportCSV() { string newCSVName = string.Empty; OrderVendorProducesQuery query = new OrderVendorProducesQuery(); DataTable dt = new DataTable(); try { #region 供應商查詢條件 uint v_id = 0; if (uint.TryParse(Request.Params["Vendor_Id"].ToString(), out v_id)) { query.Item_Vendor_Id = v_id; } 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["datetype"])) { query.date_type = Request.Params["datetype"].ToString(); } DateTime dtime; if (DateTime.TryParse(Request.Params["dateStart"].ToString(), out dtime)) { query.dateStart = dtime; } if (DateTime.TryParse(Request.Params["dateEnd"].ToString(), out dtime)) { query.dateEnd = dtime.AddDays(1); } if (!string.IsNullOrEmpty(Request.Params["order_status"]) && Request.Params["order_status"] != "null") {//狀態 query.slave = Request.Params["order_status"].ToString(); } if (uint.TryParse(Request.Params["order_payment"], out v_id)) {//付款方式 query.order_payment = v_id; } if (!string.IsNullOrEmpty(Request.Params["product_freight_set"]) && Request.Params["product_freight_set"] != "null") { query.product_freight_set_in = Request.Params["product_freight_set"]; } if (!string.IsNullOrEmpty(Request.Params["product_manage"])) {//供應商管理者查詢條件 query.product_manage = Request.Params["product_manage"]; } #endregion _orderVendorProducesMgr = new OrderVendorProducesMgr(mySqlConnectionString); DataTable dta = new DataTable(); dta = _orderVendorProducesMgr.ExportCsv(query); #region 表頭 dt.Columns.Add("付款單號", typeof(String)); dt.Columns.Add("供應商", typeof(String)); dt.Columns.Add("倉別", typeof(String)); dt.Columns.Add("品牌名稱", typeof(String)); dt.Columns.Add("商品名稱", typeof(String)); dt.Columns.Add("購物編號", typeof(String)); dt.Columns.Add("規格", typeof(String)); dt.Columns.Add("付款方式", typeof(String)); dt.Columns.Add("商品類型", typeof(String)); dt.Columns.Add("進貨價", typeof(String)); dt.Columns.Add("實際售價", typeof(String)); dt.Columns.Add("數量", typeof(String)); dt.Columns.Add("使用購物金", typeof(String)); dt.Columns.Add("使用抵用券", typeof(String)); dt.Columns.Add("使用HG", typeof(String)); dt.Columns.Add("小計", typeof(String)); dt.Columns.Add("促銷", typeof(String)); dt.Columns.Add("訂購姓名", typeof(String)); dt.Columns.Add("狀態", typeof(String)); dt.Columns.Add("收件人", typeof(String)); dt.Columns.Add("性別", typeof(String)); dt.Columns.Add("郵遞區號", typeof(String)); //dt.Columns.Add("地址", typeof(String)); //dt.Columns.Add("收貨人手機", typeof(String)); //dt.Columns.Add("收貨人電話", typeof(String)); dt.Columns.Add("購物單狀態", typeof(String)); dt.Columns.Add("訂單日期", typeof(String)); dt.Columns.Add("出貨日期", typeof(String)); dt.Columns.Add("可出貨日期", typeof(String)); dt.Columns.Add("付款日期", typeof(String)); dt.Columns.Add("發票日期", typeof(String)); dt.Columns.Add("貨款日期", typeof(String)); dt.Columns.Add("備註", typeof(String)); dt.Columns.Add("管理人員備註", typeof(String)); dt.Columns.Add("貨運模式", typeof(String)); dt.Columns.Add("註冊時間", typeof(String)); dt.Columns.Add("出生年月", typeof(String)); dt.Columns.Add("管理者", typeof(String)); //dt.Columns.Add("電子信箱", typeof(String)); dt.Columns.Add("宅配代碼", typeof(String)); dt.Columns.Add("宅配時間", typeof(String)); dt.Columns.Add("商品細項編號", typeof(String)); dt.Columns.Add("假日可出貨", typeof(String)); #endregion foreach (DataRow dr in dta.Rows) { #region 數據插入 DataRow newRow = dt.NewRow(); newRow[0] = dr["order_id"].ToString(); newRow[1] = dr["vendor_name_simple"].ToString(); switch (dr["product_mode"].ToString()) {//出貨方式 case "1": newRow[2] = "供應商自行出貨"; break; case "2": newRow[2] = "寄倉"; break; case "3": newRow[2] = "調度"; break; default: newRow[2] = "其他"; break; } newRow[3] = dr["brand_name"].ToString(); newRow[4] = dr["product_name"].ToString(); newRow[5] = dr["detail_id"].ToString(); newRow[6] = dr["product_spec_name"].ToString(); newRow[7] = dr["order_payment"].ToString(); switch (dr["item_mode"].ToString()) {//商品類型 case "1": newRow[8] = "父商品"; break; case "2": newRow[8] = "子商品"; break; default: newRow[8] = "單一商品"; break; } //if (dr["item_mode"].ToString() == "1") //{ // newRow[9] = ""; //} //else //{ newRow[9] = dr["single_cost"]; //} if (dr["event_cost"].ToString() != "0") { newRow[9] = dr["event_cost"]; } newRow[12] = Convert.ToInt32(dr["deduct_bonus"].ToString() == "" ? "0" : dr["deduct_bonus"].ToString()); newRow[13] = dr["deduct_welfare"].ToString(); newRow[14] = dr["deduct_happygo_money"].ToString(); int zk = Convert.ToInt32(dr["deduct_bonus"].ToString() == "" ? "0" : dr["deduct_bonus"].ToString()) + Convert.ToInt32(dr["deduct_welfare"].ToString() == "" ? "0" : dr["deduct_welfare"].ToString()) + Convert.ToInt32(dr["deduct_happygo_money"].ToString() == "" ? "0" : dr["deduct_happygo_money"].ToString()); if (int.Parse(dr["item_mode"].ToString()) == 2)//single_money { double price = double.Parse(dr["single_money"].ToString()) / int.Parse(dr["buy_num"].ToString()); newRow[10] = uint.Parse(Math.Round(price, 0).ToString());//實際售價 newRow[11] = int.Parse(dr["buy_num"].ToString()) * int.Parse(dr["parent_num"].ToString());//購買數量 newRow[15] = int.Parse(dr["single_money"].ToString()) * int.Parse(dr["parent_num"].ToString())-zk;//小計 } else { newRow[10] = dr["single_money"].ToString(); newRow[11] = dr["buy_num"].ToString(); newRow[15] = int.Parse(dr["single_money"].ToString()) * int.Parse(dr["buy_num"].ToString())-zk; } //newRow[10] = dr["single_money"].ToString(); //newRow[11] = dr["buy_num"].ToString(); //newRow[15] = dr["subtotal"].ToString(); switch (dr["event"].ToString()) {//促銷 case "0": newRow[16] = "-"; break; default: newRow[16] = "是"; break; } newRow[17] = dr["order_name"].ToString(); newRow[18] = dr["slave_status"].ToString(); newRow[19] = dr["delivery_name"].ToString(); switch (dr["delivery_gender"].ToString()) {//男 or 女 case "0": newRow[20] = "女"; break; case "1": newRow[20] = "男"; break; default: break; } newRow[21] = dr["delivery_zip"].ToString(); newRow[22] = dr["detail_status"].ToString(); newRow[23] = dr["order_createdate"].ToString(); newRow[24] = dr["slave_date_delivery"].ToString(); newRow[25] = dr["order_date_pay"].ToString(); newRow[26] = dr["money_collect_date"].ToString(); newRow[27] = dr["invoice_date"].ToString(); newRow[28] = dr["slave_date_close"].ToString(); newRow[29] = dr["note_order"].ToString(); newRow[30] = dr["note_admin"].ToString(); switch (dr["product_freight_set"].ToString()) {//運送方式 case "1": newRow[31] = "常溫"; break; case "3": newRow[31] = "常溫"; break; case "2": newRow[31] = "冷凍"; break; case "4": newRow[31] = "冷凍"; break; case "5": newRow[31] = "冷藏"; break; case "6": newRow[31] = "冷藏"; break; } newRow[32] = dr["user_reg_date"].ToString(); newRow[33] = dr["user_birthday"].ToString(); newRow[34] = dr["user_username"].ToString(); if (dr["delivery_store"].ToString() == "1" || dr["delivery_store"].ToString() == "10") { switch (dr["estimated_arrival_period"].ToString()) { case "0": newRow[35] = 4; break; case "1": newRow[35] = 1; break; case "2": newRow[35] = 2; break; case "3": newRow[35] = 3; break; } } switch (dr["estimated_arrival_period"].ToString()) {//收貨時間 case "0": newRow[36] = "不限時"; break; case "1": newRow[36] = "12:00以前"; break; case "2": newRow[36] = "12:00-17:00"; break; case "3": newRow[36] = "17:00-20:00"; break; default: break; } newRow[37] = dr["item_id"].ToString(); switch (dr["holiday_deliver"].ToString()) {//假日可出貨 case "1": newRow[38] = "是"; break; default: newRow[38] = "否"; break; } dt.Rows.Add(newRow); #endregion } if (dt.Rows.Count > 0) { string fileName = "供應商訂單匯出_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; MemoryStream ms = ExcelHelperXhf.ExportDT(dt, ""); 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 GetOrderVendorProduces() { List<OrderVendorProducesQuery> stores = new List<OrderVendorProducesQuery>(); string json = string.Empty; DataTable dt_payment = new DataTable(); DataTable dt_status = new DataTable(); try { StringBuilder sb = new StringBuilder(); OrderVendorProducesQuery query = new OrderVendorProducesQuery(); query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "20");//用於分頁的變量 uint v_id = 0; #region 供應商查詢條件 if (uint.TryParse(Request.Params["Vendor_Id"].ToString(), out v_id)) { query.Item_Vendor_Id = v_id; } 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["datetype"])) { query.date_type = Request.Params["datetype"].ToString(); } DateTime dtime; if (DateTime.TryParse(Request.Params["dateStart"].ToString(), out dtime)) { query.dateStart =Convert.ToDateTime(Request.Params["dateStart"]); } if (DateTime.TryParse(Request.Params["dateEnd"].ToString(), out dtime)) { query.dateEnd = Convert.ToDateTime(Request.Params["dateEnd"]); } if (!string.IsNullOrEmpty(Request.Params["order_status"])) {//狀態 query.slave = Request.Params["order_status"]; } if (uint.TryParse(Request.Params["order_payment"], out v_id)) {//付款方式 query.order_payment = v_id; } if (!string.IsNullOrEmpty(Request.Params["product_freight_set"])) { query.product_freight_set_in = Request.Params["product_freight_set"]; } if (!string.IsNullOrEmpty(Request.Params["product_manage"])) {//供應商管理者查詢條件 query.product_manage = Request.Params["product_manage"]; } #endregion _orderVendorProducesMgr = new OrderVendorProducesMgr(mySqlConnectionString); DataTable dt = _orderVendorProducesMgr.GetProductItem(); int totalCount = 0; stores = _orderVendorProducesMgr.GetOrderVendorProduces(query, out totalCount); dt_payment = GetTP("payment"); dt_status = GetTP("order_status"); for (int i = 0; i < stores.Count; i++) { DataRow[] drs = dt.Select("item_id=" + stores[i].Item_Id); if (drs.Count() > 0) { if (!string.IsNullOrEmpty(drs[0]["spec_image"].ToString())) { stores[i].pic_patch = "http://a.gimg.tw/product_spec/280x280/" + drs[0]["spec_image"].ToString().Substring(0, 2) + "/"; stores[i].pic_patch += drs[0]["spec_image"].ToString().Substring(2, 2) + "/" + drs[0]["spec_image"].ToString(); } } else { stores[i].pic_patch = ""; } if (stores[i].item_mode == 2) {//如果是子商品數量*該組合的量 double price = double.Parse(stores[i].Single_Money.ToString()) / stores[i].Buy_Num; stores[i].Single_Money = uint.Parse(Math.Round(price, 0).ToString()); stores[i].Buy_Num = stores[i].Buy_Num * stores[i].parent_num; } if (stores[i].order_payment > 0) {//帶出付款方式的參數 DataRow[] dr = dt_payment.Select("ParameterCode = '" + stores[i].order_payment.ToString() + "'"); DataTable _newdt = dt_payment.Clone(); foreach (DataRow r in dr) { _newdt.Rows.Add(r.ItemArray); } stores[i].payment = _newdt.Rows[0]["ParameterName"].ToString(); } if (stores[i].slave_status >= 0) {//帶出付款方式的參數 DataRow[] dr = dt_status.Select("ParameterCode = '" + stores[i].slave_status.ToString() + "'"); DataTable _newdt = dt_status.Clone(); foreach (DataRow r in dr) { _newdt.Rows.Add(r.ItemArray); } stores[i].slave = _newdt.Rows[0]["remark"].ToString(); } } //foreach (var item in stores) //{ // DataRow[] drs = dt.Select("item_id=" + item.Item_Id); // if (drs.Count() > 0) // { // if (!string.IsNullOrEmpty(drs[0]["spec_image"].ToString())) // { // item.pic_patch = "http://a.gimg.tw/product_spec/280x280/" + drs[0]["spec_image"].ToString().Substring(0, 2) + "/"; // item.pic_patch += drs[0]["spec_image"].ToString().Substring(2, 2) + "/" + drs[0]["spec_image"].ToString(); // } // } // else // { // item.pic_patch = ""; // } // 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_payment.Select("ParameterCode = '" + item.order_payment.ToString() + "'"); // DataTable _newdt = dt_payment.Clone(); // foreach (DataRow i in dr) // { // _newdt.Rows.Add(i.ItemArray); // } // item.payment = _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.slave = _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; }