public void OrderMasterExport()
{
string json = string.Empty;
DataTable _dt = new DataTable();
DataTable dtHZ = new DataTable();
OrderMasterQuery query = new OrderMasterQuery();
try
{
string newExcelName = string.Empty;
dtHZ.Columns.Add("付款單號", typeof(String));
dtHZ.Columns.Add("訂購人", typeof(String));
//dtHZ.Columns.Add("收貨人", typeof(String));
dtHZ.Columns.Add("訂單應收金額", typeof(int)); //A
dtHZ.Columns.Add("紅利折抵金額", typeof(String));
dtHZ.Columns.Add("付款方式", typeof(String));
dtHZ.Columns.Add("請款狀態", typeof(String));//C!=NULL 已經款 C==null 未請款 A=I商品取消
dtHZ.Columns.Add("付款單成立日期", typeof(String));
dtHZ.Columns.Add("銀行入帳日期", typeof(String));
dtHZ.Columns.Add("手續費", typeof(String));//B
dtHZ.Columns.Add("入帳金額", typeof(String));//C
dtHZ.Columns.Add("退貨入帳日期", typeof(String));
dtHZ.Columns.Add("退貨入帳手續費", typeof(String));//N
dtHZ.Columns.Add("退貨入帳金額", typeof(String));//M
dtHZ.Columns.Add("入帳總額", typeof(String));//D=B+C+M+N
dtHZ.Columns.Add("入帳金額差異", typeof(String));//E=A-D
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));
query.dateType = Convert.ToInt32(Request.Params["dateType"]);
if (Request.Params["dateType"].ToString() == "1" || Request.Params["dateType"].ToString() == "4" || Request.Params["dateType"].ToString() == "5")
{
if (!string.IsNullOrEmpty(Request.Params["orderTimeStart"]))
{
query.order_date_pay_startTime = Convert.ToDateTime(Request.Params["orderTimeStart"]);
}
if (!string.IsNullOrEmpty(Request.Params["orderTimeEnd"]))
{
query.order_date_pay_endTime = Convert.ToDateTime(Request.Params["orderTimeEnd"]);
}
}
else
{
if (!string.IsNullOrEmpty(Request.Params["orderTimeStart"]))
{
query.first_time = Convert.ToUInt32(CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Params["orderTimeStart"]).ToString("yyyy-MM-dd 00:00:00")));
}
if (!string.IsNullOrEmpty(Request.Params["orderTimeEnd"]))
{
query.last_time = Convert.ToUInt32(CommonFunction.GetPHPTime(Convert.ToDateTime(Request.Params["orderTimeEnd"]).ToString("yyyy-MM-dd 23:59:59")));
}
}
if (!string.IsNullOrEmpty(Request.Params["order_id"]))
{
query.Order_Id = Convert.ToUInt32(Request.Params["order_id"]);
}
if (!string.IsNullOrEmpty(Request.Params["show_type"]))
{
query.show_type = Convert.ToInt32(Request.Params["show_type"]);
} if (!string.IsNullOrEmpty(Request.Params["invoice_type"]))
{
query.invoice_type = Convert.ToInt32(Request.Params["invoice_type"]);
}
if (!string.IsNullOrEmpty(Request.Params["payment"]))
{
query.Order_Payment = Convert.ToUInt32(Request.Params["payment"]);
}
_OrderMasterMgr = new OrderMasterMgr(connectionString);
_dt = _OrderMasterMgr.OrderMasterExport(query);
if (_dt.Rows.Count > 0)
{
if (!System.IO.Directory.Exists(Server.MapPath(excelPath)))
{
System.IO.Directory.CreateDirectory(Server.MapPath(excelPath));
}
for (int i = 0; i < _dt.Rows.Count; i++)
{
DataRow dr = dtHZ.NewRow();
dr["付款單號"] = _dt.Rows[i]["order_id"];
dr["訂購人"] = _dt.Rows[i]["order_name"];
dr["訂單應收金額"] = Convert.ToInt32(_dt.Rows[i]["order_amount"]);//A
dr["紅利折抵金額"] = _dt.Rows[i]["deduct_card_bonus"];
dr["付款方式"] = _dt.Rows[i]["parameterName"];
if (!string.IsNullOrEmpty(_dt.Rows[i]["account_collection_money"].ToString()))
{
dr["請款狀態"] = "已請款";
}
else
{
dr["請款狀態"] = "未請款";
}
dr["付款單成立日期"] = _dt.Rows[i]["ordercreatedate"];
if (!string.IsNullOrEmpty(_dt.Rows[i]["account_collection_time"].ToString()))
{
dr["銀行入帳日期"] = Convert.ToDateTime(_dt.Rows[i]["account_collection_time"]).ToString("yyyy/MM/dd");
}
dr["手續費"] = _dt.Rows[i]["poundage"];//B
dr["入帳金額"] = _dt.Rows[i]["account_collection_money"]; //C
if (!string.IsNullOrEmpty(_dt.Rows[i]["return_collection_time"].ToString()))
{
dr["退貨入帳日期"] = Convert.ToDateTime(_dt.Rows[i]["return_collection_time"]).ToString("yyyy/MM/dd");
}
dr["退貨入帳手續費"] = _dt.Rows[i]["return_poundage"];
dr["退貨入帳金額"] = _dt.Rows[i]["return_collection_money"];
dr["入帳總額"] = _dt.Rows[i]["oacamount"];
if (!string.IsNullOrEmpty(dr["訂單應收金額"].ToString()) && !string.IsNullOrEmpty(dr["入帳總額"].ToString()))
{
dr["入帳金額差異"] = Convert.ToInt32(dr["訂單應收金額"].ToString()) - Convert.ToInt32(dr["入帳總額"].ToString());//E=A-D
}
if (!string.IsNullOrEmpty(_dt.Rows[i]["invoicedate"].ToString()))
{
dr["開立發票日期"] = _dt.Rows[i]["invoicedate"].ToString();
}
dr["發票銷售額"] = _dt.Rows[i]["free_tax"];//F
dr["發票稅額"] = _dt.Rows[i]["tax_amount"];//G
if (!string.IsNullOrEmpty(_dt.Rows[i]["invoice_date_manual"].ToString()))
{
dr["手開發票日期"] = Convert.ToDateTime(_dt.Rows[i]["invoice_date_manual"]).ToString("yyyy/MM/dd");
}
dr["手開發票銷售額"] = _dt.Rows[i]["invoice_sale_manual"];//F
dr["手開發票稅額"] = _dt.Rows[i]["invoice_tax_manual"];//G
dr["發票總額"] = _dt.Rows[i]["imramount"];//F+G
//if (!string.IsNullOrEmpty(dr["發票銷售額"].ToString()) && !string.IsNullOrEmpty(dr["發票稅額"].ToString()))
//{
// dr["發票總額"] = Convert.ToInt32(dr["發票銷售額"]) + Convert.ToInt32(dr["發票稅額"]);//F+G
//}
//dr["發票總額"] = Convert.ToInt32(_dt.Rows[i]["sales_amount"]) + Convert.ToInt32(_dt.Rows[i]["free_tax"]);
if (Convert.ToInt32(_dt.Rows[i]["money_cancel"]) != 0 && Convert.ToInt32(_dt.Rows[i]["money_return"]) == 0)
{
dr["商品取消金額"] = _dt.Rows[i]["money_cancel"];//I
}
if (Convert.ToInt32(_dt.Rows[i]["money_cancel"]) == 0 && Convert.ToInt32(_dt.Rows[i]["money_return"]) != 0)
{
dr["商品取消金額"] = _dt.Rows[i]["money_return"];
}
if (dr["訂單應收金額"].ToString() == dr["商品取消金額"].ToString())
{
dr["請款狀態"] = "商品取消";
}
dr["發票金額差異"] = _dt.Rows[i]["invoice_diff"];//入帳總額-發票總額
//if (!string.IsNullOrEmpty(dr["入帳總額"].ToString()) && !string.IsNullOrEmpty(dr["發票總額"].ToString()))
//{
// dr["發票金額差異"] = Convert.ToInt32(dr["入帳總額"].ToString()) - Convert.ToInt32(dr["發票總額"].ToString());//J=E-H
//}
dr["備註"] = _dt.Rows[i]["remark"]; ;
dtHZ.Rows.Add(dr);
}
}
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);
json = "{success:false,totalCount:0,data:[]}";
}
}
