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