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