public DataTable ExportCsv(OrderVendorProducesQuery store) { try { return _iOrderVendorDao.ExportCsv(store); } catch (Exception ex) { throw new Exception("OrderVendorProducesMgr.ExportCsv-->" + ex.Message, ex); } }
public List<OrderVendorProducesQuery> GetOrderVendorProduces(OrderVendorProducesQuery store, out int totalCount)/*返回供應商訂單查詢列表*/ { try { return _iOrderVendorDao.GetOrderVendorProduces(store, out totalCount); } catch (Exception ex) { throw new Exception("OrderVendorProducesMgr.GetOrderVendorProduces-->" + ex.Message, ex); } }
public List<OrderVendorProducesQuery> GetOrderVendorProduces(OrderVendorProducesQuery store, out int totalCount)/*返回供應商訂單查詢列表*/ { StringBuilder sql = new StringBuilder(); StringBuilder sqlcount = new StringBuilder(); StringBuilder sqlfrom = new StringBuilder(); StringBuilder sqlwhere = new StringBuilder(); List<OrderVendorProducesQuery> list = new List<OrderVendorProducesQuery>(); StringBuilder search = new StringBuilder(); try { sqlcount.Append(@" SELECT count(od.detail_id) as count "); sql.Append(@" SELECT od.item_id,om.user_id,od.product_mode,od.product_name,od.product_spec_name,od.single_cost,od.event_cost,od.single_money,od.single_money as SingleMoney,od.deduct_bonus,od.buy_num,od.item_mode,od.parent_num,FROM_UNIXTIME(os.slave_date_delivery) as slave_date_delivery,os.order_id,om.user_id,FROM_UNIXTIME(om.order_createdate) as order_createdate,om.order_name,om.order_mobile,om.note_order, om.delivery_name,om.delivery_gender,om.delivery_zip,om.delivery_address,v.vendor_name_simple,FROM_UNIXTIME(om.order_date_pay) as order_date_pay,FROM_UNIXTIME(om.money_collect_date) as money_collect_date,mu.user_username as product_manage,om.order_payment,os.slave_status "); sqlfrom.AppendFormat(@" FROM order_master om LEFT JOIN order_slave os ON om.order_id=os.order_id LEFT JOIN order_detail od ON os.slave_id = od.slave_id LEFT JOIN vendor v ON od.item_vendor_id = v.vendor_id LEFT JOIN manage_user mu ON v.product_manage = mu.user_id "); //tp1.remark as slave_status,tp.parameterName as order_payment, LEFT JOIN t_parametersrc tp ON om.order_payment = tp.parameterCode AND tp.parameterType = 'payment' LEFT JOIN t_parametersrc tp1 ON os.slave_status = tp1.parameterCode AND tp1.parameterType = 'order_status' sqlwhere.AppendFormat(" WHERE 1=1 "); #region where if (store.Item_Vendor_Id > 0) { sqlwhere.AppendFormat(" and od.item_vendor_id='{0}'", store.Item_Vendor_Id); } if (!string.IsNullOrEmpty(store.searchcon)) { switch (store.selecttype) { case "1": sqlwhere.AppendFormat(" AND od.product_name LIKE '%{0}%' ", store.searchcon); break; case "2": sqlwhere.AppendFormat(" AND om.user_id LIKE '%{0}%' ", store.searchcon); break; case "3": sqlwhere.AppendFormat(" AND v.vendor_name_simple LIKE '%{0}%' ", store.searchcon); break; case "4": sqlwhere.AppendFormat(" AND od.parent_id ='{0}' OR od.item_id ='{0}' ", store.searchcon); break; case "5": sqlwhere.AppendFormat(" AND om.order_id ='{0}' ", store.searchcon); break; default: break; } } if (store.slave != "null" && !string.IsNullOrEmpty(store.slave) && store.slave != "-1") { sqlwhere.AppendFormat(" and os.slave_status='{0}'", store.slave); } if (store.order_payment > 0) {//付款方式 sqlwhere.AppendFormat(" and om.order_payment={0}", store.order_payment); } if (!string.IsNullOrEmpty(store.product_freight_set_in)) {//運送方式 sqlwhere.AppendFormat(@" AND od.product_freight_set IN ({0}) ", store.product_freight_set_in); } if (store.product_manage != "0" && !string.IsNullOrEmpty(store.product_manage)) {//供應商管理者查詢條件 sqlwhere.AppendFormat(" AND v.product_manage='{0}'", store.product_manage); } if (!string.IsNullOrEmpty(store.date_type)) {//日期條件 switch (store.date_type) { case "1": if (store.dateStart > DateTime.MinValue) { sqlwhere.AppendFormat(" AND om.order_createdate >= '{0}' ", CommonFunction.GetPHPTime(store.dateStart.ToString())); } if (store.dateEnd > DateTime.MinValue) { sqlwhere.AppendFormat(" AND om.order_createdate < '{0}' ", CommonFunction.GetPHPTime(store.dateEnd.ToString())); } break; case "2": if (store.dateStart > DateTime.MinValue) { sqlwhere.AppendFormat(" AND om.money_collect_date >= '{0}' ", CommonFunction.GetPHPTime(store.dateStart.ToString())); } if (store.dateEnd > DateTime.MinValue) { sqlwhere.AppendFormat(" AND om.money_collect_date < '{0}' ", CommonFunction.GetPHPTime(store.dateEnd.ToString())); } break; case "3": if (store.dateStart > DateTime.MinValue) { sqlwhere.AppendFormat(" AND om.order_date_pay >= '{0}' ", CommonFunction.GetPHPTime(store.dateStart.ToString())); } if (store.dateEnd > DateTime.MinValue) { sqlwhere.AppendFormat(" AND om.order_date_pay < '{0}' ", CommonFunction.GetPHPTime(store.dateEnd.ToString())); } break; case "4": if (store.dateStart > DateTime.MinValue) { sqlwhere.AppendFormat(" AND os.slave_date_delivery >= '{0}' ", CommonFunction.GetPHPTime(store.dateStart.ToString())); } if (store.dateEnd > DateTime.MinValue) { sqlwhere.AppendFormat(" AND os.slave_date_delivery < '{0}' ", CommonFunction.GetPHPTime(store.dateEnd.ToString())); } break; case "5": if (store.dateStart > DateTime.MinValue) { sqlwhere.AppendFormat(" AND om.order_date_close >= '{0}' ", CommonFunction.GetPHPTime(store.dateStart.ToString())); } if (store.dateEnd > DateTime.MinValue) { sqlwhere.AppendFormat(" AND om.order_date_close < '{0}' ", CommonFunction.GetPHPTime(store.dateEnd.ToString())); } break; default: break; } } #endregion totalCount = 0; if (store.IsPage) { System.Data.DataTable _dt = _accessMySql.getDataTable(sqlcount.ToString() + sqlfrom.ToString() + sqlwhere.ToString()); if (_dt != null && _dt.Rows.Count > 0) { totalCount = int.Parse(_dt.Rows[0][0].ToString()); } sqlwhere.AppendFormat(" ORDER BY order_createdate DESC, od.detail_id ASC limit {0},{1};", store.Start, store.Limit); } return _accessMySql.getDataTableForObj<OrderVendorProducesQuery>(sql.ToString() + sqlfrom.ToString() + sqlwhere.ToString()); } catch (Exception ex) { throw new Exception("OrderVendorProducesDao.GetOrderVendorProduces-->" + ex.Message + sql.ToString() + sqlfrom.ToString() + sqlwhere.ToString(), ex); } }
public DataTable ExportCsv(OrderVendorProducesQuery store) { StringBuilder sql = new StringBuilder(); StringBuilder sqlwhere = new StringBuilder(); try { //sql.AppendLine(@"SELECT om.order_id, v.vendor_name_simple,CASE od.product_mode WHEN 1 THEN '供應商自行出貨' WHEN 2 THEN '寄倉' WHEN 3 THEN '調度' END AS product_mode,vb.brand_name, od.product_name, od.detail_id, od.product_spec_name, tppp.parameterName as order_payment,CASE od.item_mode WHEN 1 THEN '父商品' WHEN 2 THEN '子商品' ELSE '單一商品' END AS item_mode,CASE om.holiday_deliver when 0 OR 1 THEN '' END AS cost,od.single_cost,od.event_cost,CASE od.item_mode WHEN 2 THEN od.single_money/od.buy_num ELSE od.single_money END AS single_money,CASE od.item_mode WHEN 2 THEN od.buy_num*od.parent_num ELSE od.buy_num END AS buy_num,od.deduct_bonus, od.deduct_welfare, od.deduct_happygo_money,CASE od.item_mode WHEN 2 THEN od.single_money*od.parent_num-od.deduct_bonus-od.deduct_welfare-od.deduct_happygo_money ELSE od.single_money*od.buy_num-od.deduct_bonus-od.deduct_welfare-od.deduct_happygo_money END as subtotal,CASE od.event_cost when 0 THEN '-' ELSE '是' END AS `event`, om.order_name, tp.remark as slave_status ,om.delivery_name, CASE om.delivery_gender WHEN 0 THEN '女' WHEN 1 THEN '男' END AS delivery_gender,CONCAT_WS( '', zip.zipcode, zip.middle, zip.small ) AS delivery_zip, om.delivery_address,om.delivery_mobile, om.delivery_phone,tpp.remark AS detail_status, FROM_UNIXTIME(om.order_createdate) AS order_createdate,CASE os.slave_date_delivery WHEN 0 THEN '-' ELSE FROM_UNIXTIME(os.slave_date_delivery) END AS slave_date_delivery,CASE om.order_date_pay WHEN 0 THEN '-' ELSE FROM_UNIXTIME(om.order_date_pay) END AS order_date_pay,CASE om.money_collect_date WHEN 0 THEN '-' ELSE FROM_UNIXTIME(om.money_collect_date) END AS money_collect_date,CASE imr.invoice_date WHEN 0 THEN '-' ELSE FROM_UNIXTIME(imr.invoice_date) END AS invoice_date,CASE os.slave_date_close WHEN 0 THEN '-' ELSE FROM_UNIXTIME(os.slave_date_close) END AS slave_date_close, om.note_order,om.note_admin, CASE od.product_freight_set WHEN 1 OR 3 THEN '常溫' WHEN 2 OR 4 THEN '低溫' END AS product_freight_set,FROM_UNIXTIME(u.user_reg_date) as user_reg_date, CONCAT_WS( '-', u.user_birthday_year, u.user_birthday_month, u.user_birthday_day ) AS user_birthday,mu.user_username, u.user_email, om.delivery_store,om.estimated_arrival_period,CASE om.estimated_arrival_period WHEN 0 THEN '不限時' WHEN 1 THEN '12:00以前' WHEN 2 THEN '12:00-17:00' WHEN 3 THEN '17:00-20:00' END AS delivery,od.item_id,CASE om.holiday_deliver WHEN 1 THEN '是' ELSE '否' END AS holiday_deliver "); //sql.AppendLine(@"SELECT om.order_id, v.vendor_name_simple,od.product_mode,vb.brand_name, od.product_name, od.detail_id, od.product_spec_name, tppp.parameterName as order_payment,od.item_mode, '' AS cost,od.single_cost,od.event_cost,CASE od.item_mode WHEN 2 THEN od.single_money/od.buy_num ELSE od.single_money END AS single_money,CASE od.item_mode WHEN 2 THEN od.buy_num*od.parent_num ELSE od.buy_num END AS buy_num,od.deduct_bonus, od.deduct_welfare, od.deduct_happygo_money,CASE od.item_mode WHEN 2 THEN od.single_money*od.parent_num-od.deduct_bonus-od.deduct_welfare-od.deduct_happygo_money ELSE od.single_money*od.buy_num-od.deduct_bonus-od.deduct_welfare-od.deduct_happygo_money END as subtotal,od.event_cost AS `event`, om.order_name, tp.remark as slave_status ,om.delivery_name,om.delivery_gender,CONCAT_WS( '', zip.zipcode, zip.middle, zip.small ) AS delivery_zip, om.delivery_address,om.delivery_mobile, om.delivery_phone,tpp.remark AS detail_status, FROM_UNIXTIME(om.order_createdate) AS order_createdate,CASE os.slave_date_delivery WHEN 0 THEN '-' ELSE FROM_UNIXTIME(os.slave_date_delivery) END AS slave_date_delivery,CASE om.order_date_pay WHEN 0 THEN '-' ELSE FROM_UNIXTIME(om.order_date_pay) END AS order_date_pay,CASE om.money_collect_date WHEN 0 THEN '-' ELSE FROM_UNIXTIME(om.money_collect_date) END AS money_collect_date,CASE imr.invoice_date WHEN 0 THEN '-' ELSE FROM_UNIXTIME(imr.invoice_date) END AS invoice_date,CASE os.slave_date_close WHEN 0 THEN '-' ELSE FROM_UNIXTIME(os.slave_date_close) END AS slave_date_close, om.note_order,om.note_admin, od.product_freight_set,FROM_UNIXTIME(u.user_reg_date) as user_reg_date, CONCAT_WS( '-', u.user_birthday_year, u.user_birthday_month, u.user_birthday_day ) AS user_birthday,mu.user_username, u.user_email, om.delivery_store,om.estimated_arrival_period,'' AS delivery,od.item_id,om.holiday_deliver "); //CASE od.item_mode WHEN 2 THEN od.single_money*od.parent_num-od.deduct_bonus-od.deduct_welfare-od.deduct_happygo_money ELSE od.single_money*od.buy_num-od.deduct_bonus-od.deduct_welfare-od.deduct_happygo_money END as subtotal, sql.AppendLine(@"SELECT om.order_id, v.vendor_name_simple,od.product_mode,vb.brand_name, od.product_name, od.detail_id, od.product_spec_name, tppp.parameterName as order_payment,od.item_mode, '' AS cost,od.single_cost,od.event_cost,od.single_money,od.buy_num,od.parent_num,od.deduct_bonus, od.deduct_welfare, od.deduct_happygo_money,od.event_cost AS `event`, om.order_name, tp.remark as slave_status ,om.delivery_name,om.delivery_gender,CONCAT_WS( '', zip.zipcode, zip.middle, zip.small ) AS delivery_zip, om.delivery_address,om.delivery_mobile, om.delivery_phone,tpp.remark AS detail_status, FROM_UNIXTIME(om.order_createdate) AS order_createdate,CASE os.slave_date_delivery WHEN 0 THEN '-' ELSE FROM_UNIXTIME(os.slave_date_delivery) END AS slave_date_delivery,CASE om.order_date_pay WHEN 0 THEN '-' ELSE FROM_UNIXTIME(om.order_date_pay) END AS order_date_pay,CASE om.money_collect_date WHEN 0 THEN '-' ELSE FROM_UNIXTIME(om.money_collect_date) END AS money_collect_date,CASE imr.invoice_date WHEN 0 THEN '-' ELSE FROM_UNIXTIME(imr.invoice_date) END AS invoice_date,CASE os.slave_date_close WHEN 0 THEN '-' ELSE FROM_UNIXTIME(os.slave_date_close) END AS slave_date_close, om.note_order,om.note_admin, od.product_freight_set,FROM_UNIXTIME(u.user_reg_date) as user_reg_date, CONCAT_WS( '-', u.user_birthday_year, u.user_birthday_month, u.user_birthday_day ) AS user_birthday,mu.user_username, u.user_email, om.delivery_store,om.estimated_arrival_period,'' AS delivery,od.item_id,om.holiday_deliver "); sql.AppendLine(@" FROM order_master om LEFT JOIN order_slave os ON om.order_id=os.order_id LEFT JOIN order_detail od ON os.slave_id = od.slave_id LEFT JOIN product_item pi ON od.item_id = pi.item_id LEFT JOIN product p ON pi.product_id = p.product_id LEFT JOIN vendor_brand vb ON p.brand_id=vb.brand_id LEFT JOIN vendor v ON od.item_vendor_id = v.vendor_id LEFT JOIN manage_user mu ON v.product_manage = mu.user_id LEFT JOIN (select invoice_date,order_id from invoice_master_record GROUP BY order_id) imr on om.order_id = imr.order_id LEFT JOIN t_zip_code zip ON zip.zipcode = om.delivery_zip LEFT JOIN t_parametersrc tp ON os.slave_status = tp.parameterCode AND tp.parameterType = 'order_status' LEFT JOIN t_parametersrc tpp ON od.detail_status = tpp.parameterCode AND tpp.parameterType = 'order_status' LEFT JOIN t_parametersrc tppp ON om.order_payment = tppp.parameterCode AND tppp.parameterType = 'payment' LEFT JOIN users u ON om.user_id = u.user_id where 1=1 "); #region where if (store.Item_Vendor_Id > 0) { sqlwhere.AppendFormat(" and od.item_vendor_id='{0}'", store.Item_Vendor_Id); } if (!string.IsNullOrEmpty(store.searchcon)) { switch (store.selecttype) { case "1": sqlwhere.AppendFormat(" AND od.product_name LIKE '%{0}%' ", store.searchcon); break; case "2": sqlwhere.AppendFormat(" AND om.user_id LIKE '%{0}%' ", store.searchcon); break; case "3": sqlwhere.AppendFormat(" AND v.vendor_name_simple LIKE '%{0}%' ", store.searchcon); break; case "4": sqlwhere.AppendFormat(" AND od.parent_id ='{0}' OR od.item_id ='{0}' ", store.searchcon); break; default: break; } } if (!string.IsNullOrEmpty(store.slave) && store.slave != "-1") { sqlwhere.AppendFormat(" and os.slave_status={0}", store.slave); } if (store.order_payment > 0) {//付款方式 sqlwhere.AppendFormat(" and om.order_payment={0}", store.order_payment); } if (!string.IsNullOrEmpty(store.product_freight_set_in)) {//運送方式 sqlwhere.AppendFormat(@" AND od.product_freight_set IN ({0}) ", store.product_freight_set_in); } if (store.product_manage != "0" && !string.IsNullOrEmpty(store.product_manage) && store.product_manage != "null") {//供應商管理者查詢條件 sqlwhere.AppendFormat(" AND v.product_manage='{0}'", store.product_manage); } if (!string.IsNullOrEmpty(store.date_type)) {//日期條件 switch (store.date_type) { case "1": if (store.dateStart > DateTime.MinValue) { sqlwhere.AppendFormat(" AND om.order_createdate >= '{0}' ", CommonFunction.GetPHPTime(store.dateStart.ToString())); } if (store.dateEnd > DateTime.MinValue) { sqlwhere.AppendFormat(" AND om.order_createdate < '{0}' ", CommonFunction.GetPHPTime(store.dateEnd.ToString())); } break; case "2": if (store.dateStart > DateTime.MinValue) { sqlwhere.AppendFormat(" AND om.money_collect_date >= '{0}' ", CommonFunction.GetPHPTime(store.dateStart.ToString())); } if (store.dateEnd > DateTime.MinValue) { sqlwhere.AppendFormat(" AND om.money_collect_date < '{0}' ", CommonFunction.GetPHPTime(store.dateEnd.ToString())); } break; case "3": if (store.dateStart > DateTime.MinValue) { sqlwhere.AppendFormat(" AND om.order_date_pay >= '{0}' ", CommonFunction.GetPHPTime(store.dateStart.ToString())); } if (store.dateEnd > DateTime.MinValue) { sqlwhere.AppendFormat(" AND om.order_date_pay < '{0}' ", CommonFunction.GetPHPTime(store.dateEnd.ToString())); } break; case "4": if (store.dateStart > DateTime.MinValue) { sqlwhere.AppendFormat(" AND os.slave_date_delivery >= '{0}' ", CommonFunction.GetPHPTime(store.dateStart.ToString())); } if (store.dateEnd > DateTime.MinValue) { sqlwhere.AppendFormat(" AND os.slave_date_delivery < '{0}' ", CommonFunction.GetPHPTime(store.dateEnd.ToString())); } break; case "5": if (store.dateStart > DateTime.MinValue) { sqlwhere.AppendFormat(" AND om.order_date_close >= '{0}' ", CommonFunction.GetPHPTime(store.dateStart.ToString())); } if (store.dateEnd > DateTime.MinValue) { sqlwhere.AppendFormat(" AND om.order_date_close < '{0}' ", CommonFunction.GetPHPTime(store.dateEnd.ToString())); } break; default: break; } } #endregion sqlwhere.AppendLine(@" ORDER BY order_createdate DESC , od.detail_id ASC"); return _accessMySql.getDataTable(sql.ToString() + sqlwhere.ToString()); } catch (Exception ex) { throw new Exception("OrderVendorProducesDao.ExportCsv-->" + ex.Message + sql.ToString(), ex); } }
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; }