/// <summary>
/// 匯出供應商出貨單
/// </summary>
public void OrderWaitDeliverExport()
{
string json = string.Empty;
StringBuilder sb = new StringBuilder();
try
{
#region 前提查詢條件
string type = Request.Params["search_type"];
if (!string.IsNullOrEmpty(type.Trim()) && !type.Equals("null"))//查詢條件 ||
{
int search_type = int.Parse(Request.Params["search_type"]);
string searchcontent = "";
if (!string.IsNullOrEmpty(Request.Params["search_type"]))
{
searchcontent = Request.Params["searchcontent"];
}
switch (search_type)
{
case 1:
sb.AppendFormat(" and om.order_id LIKE '%{0}%' ", searchcontent);
break;
case 2:
sb.AppendFormat(" AND om.order_name LIKE '%{0}%' ", searchcontent);
break;
case 3:
sb.AppendFormat(" AND u.user_email LIKE '%{0}%' ", searchcontent);
break;
case 4:
sb.AppendFormat(" AND om.delivery_name LIKE '%{0}%' ", searchcontent);
break;
case 5:
sb.AppendFormat(" AND om.source_trace LIKE '%{0}%' ", searchcontent);
break;
default:
sb.Append(" ");
break;
}
}
type = Request.Params["search_vendor"];
if (!string.IsNullOrEmpty(Request.Params["search_vendor"]) && !type.Equals("null"))//供應商條件
{
if (int.Parse(Request.Params["search_vendor"]) != 0)
{
sb.AppendFormat(" AND os.vendor_id = '{0}' ", Request.Params["search_vendor"]);
}
}
#endregion
string fileName = "order_deliver" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv";
List<OrderSlaveQuery> store = new List<OrderSlaveQuery>();
DataTable _dtZip = new DataTable();
_zipMgr = new ZipMgr(mySqlConnectionString);
_dtZip = _zipMgr.ZipTable(null, null);
OrderSlaveQuery query = new OrderSlaveQuery();
int totalCount = 0;
_IOrderSlaveMgr = new OrderSlaveMgr(mySqlConnectionString);
query.IsPage = false;
store = _IOrderSlaveMgr.GetOrderWaitDeliver(query, sb.ToString(), out totalCount);//匯出出供應商出貨單
DataTable dtHZ = new DataTable();
string newExcelName = string.Empty;
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));
for (int i = 0; i < store.Count; i++)
{
DataRow dr = dtHZ.NewRow();
dr[0] = store[i].order_id;
dr[1] = store[i].order_date_pay;
dr[2] = store[i].vendor_name_simple;
dr[3] = store[i].delivery_name;
//dr[4] = store[i].delivery_mobile;
//DataRow[] rows = _dtZip.Select("zipcode='" + store[i].delivery_zip + "'");
//StringBuilder sbaddress = new StringBuilder();
//if (rows.Count() != 0)
//{
// dr[5] = sbaddress.Append(rows[0]["middle"].ToString() + rows[0]["small"].ToString());
//}
//sbaddress.Append(store[i].delivery_address);
//string address = sbaddress.ToString();
//address = address.Replace(',', '，');
//address = address.Replace("\n", "");
//dr[5] = address;
store[i].note_order = store[i].note_order.Replace(',', '，');
store[i].note_order = store[i].note_order.Replace("\n", "");
dr[4] = store[i].note_order;
dtHZ.Rows.Add(dr);
}
if (!System.IO.Directory.Exists(Server.MapPath(excelPath)))
{
System.IO.Directory.CreateDirectory(Server.MapPath(excelPath));
}
string newName = string.Empty;
newName = Server.MapPath(excelPath) + fileName;
if (System.IO.File.Exists(newName))
{
//設置文件的屬性，以防刪除文件的時候因為文件的屬性造成無法刪除
System.IO.File.SetAttributes(newName, FileAttributes.Normal);
System.IO.File.Delete(newName);
}
StringWriter sw = ExcelHelperXhf.SetCsvFromData(dtHZ, fileName);
Response.Clear();
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.ContentType = "application/ms-excel";
Response.ContentEncoding = Encoding.Default;
Response.Write(sw);
Response.End();
//StringWriter sw = ExcelHelperXhf.SetCsvFromData(dtHZ, fileName);
//Response.Clear();
//Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(fileName));
//Response.ContentType = "application/ms-excel";
//Response.ContentEncoding = Encoding.Default;
//Response.Write(sw);
//Response.End();
//MemoryStream ms = ExcelHelperXhf.ExportDT(dtHZ, "order_deliver" + "_" + DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss"));
//Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
//Response.BinaryWrite(ms.ToArray());
}
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,data:[]}";
}
//this.Response.Clear();
//this.Response.Write(jsonStr.ToString());
//this.Response.End();
//return this.Response;
}
/// <summary>
/// 自出商品列表
/// </summary>
/// <returns></returns>
public HttpResponseBase VendorWaitDeliverList()
{
string jsonStr = String.Empty;
StringBuilder sb = new StringBuilder();
_IOrderSlaveMgr = new OrderSlaveMgr(mySqlConnectionString);
try
{
List<OrderSlaveQuery> store = new List<OrderSlaveQuery>();
OrderSlaveQuery query = new OrderSlaveQuery();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
query.vendor_id = ((BLL.gigade.Model.Vendor)Session["vendor"]).vendor_id;//Convert.ToUInt32((System.Web.HttpContext.Current.Session["caller"] as Caller).user_id);
int totalCount = 0;
store = _IOrderSlaveMgr.GetVendorWaitDeliver(query, sb.ToString(), out totalCount);//查询出供應商出貨單Vendor delivery
int[] map = { 4, 1, 2, 3 };
Dictionary<int, int[]> delivery_store_arrival_period = new Dictionary<int, int[]>();//參考路径:vendor.gigade100.com/order/order_wait_deliver.php第100行
delivery_store_arrival_period.Add(1, map);
delivery_store_arrival_period.Add(10, map);
delivery_store_arrival_period.Add(16, map);
delivery_store_arrival_period.Add(17, map);
#region 到貨時段//0,不限時1,12:00以前
List<Parametersrc> paramentTime = new List<Parametersrc>();//到貨時段
_ptersrc = new ParameterMgr(mySqlConnectionString);
paramentTime = _ptersrc.GetAllKindType("Estimated_Arrival_Period");//物流業者
DataTable _dtDelivery = new DataTable();
_dtDelivery.Columns.Add("parametercode", typeof(String));
_dtDelivery.Columns.Add("parameterName", typeof(String));
_dtDelivery.Columns.Add("remark", typeof(String));
foreach (var item in paramentTime)
{
DataRow dr = _dtDelivery.NewRow();
dr[0] = item.ParameterCode;
dr[1] = item.parameterName;
dr[2] = item.remark;
_dtDelivery.Rows.Add(dr);
}
#endregion
#region 台灣地區的參數
DataTable _dtZip = new DataTable();
_zipMgr = new ZipMgr(mySqlConnectionString);
_dtZip = _zipMgr.ZipTable(null, null);
#endregion
foreach (var item in store)//為什麼是待出貨能，因為查詢的條件就是狀態為待出貨的
{
item.status = "待出貨";
item.delivery = "";
item.pay_time = item.order_date_pay.ToString("yyyy/MM/dd HH:mm:ss");
if (delivery_store_arrival_period.Keys.Contains(item.delivery_store))
{
DataRow[] rows = _dtDelivery.Select("ParameterCode='" + item.estimated_arrival_period + "'");
foreach (DataRow row in rows)//篩選出的最多只有一條數據，
{
item.delivery = "";
if (!string.IsNullOrEmpty(row["ParameterCode"].ToString()))
{
item.delivery = row["ParameterName"].ToString();//---送貨時段
}
}
}
DataRow[] ziprows = _dtZip.Select("zipcode='" + item.delivery_zip + "'");
foreach (var ziprow in ziprows)
{
if (!string.IsNullOrEmpty(ziprow["zipcode"].ToString()))
{
item.delivery_address = item.delivery_zip + " " + ziprow["middle"].ToString() + ziprow["small"].ToString() + item.delivery_address;
}
}
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd";
jsonStr = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(store, 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);
jsonStr = "{success:false,msg:0}";
}
this.Response.Clear();
this.Response.Write(jsonStr.ToString());
this.Response.End();
return this.Response;
}