public void DeliversExport()
{
string json = string.Empty;
StringBuilder sb = new StringBuilder();
string jsonStr = String.Empty;
try
{
List<DeliverMasterQuery> store = new List<DeliverMasterQuery>();
DeliverMasterQuery query = new DeliverMasterQuery();
#region 查詢條件
if (!string.IsNullOrEmpty(Request.Params["type"]))//出貨類別
{
query.types = Request.Params["type"];
}
if (!string.IsNullOrEmpty(Request.Params["delivery_status"]) && int.Parse(Request.Params["delivery_status"]) != -1)//出貨狀態
{
query.status = Request.Params["delivery_status"];
}
if (!string.IsNullOrEmpty(Request.Params["export_id"]) && int.Parse(Request.Params["export_id"]) != 0) //出貨方式
{
query.vendor_id = uint.Parse(Request.Params["export_id"]);
}
query.delivery_store = 0;
if (!string.IsNullOrEmpty(Request.Params["delivery_store"]) && int.Parse(Request.Params["delivery_store"]) != 0)//物流商
{
query.delivery_store = uint.Parse(Request.Params["delivery_store"]);
}
query.warehouse_status = -1;
if (!string.IsNullOrEmpty(Request.Params["warehouse_statu"]) && int.Parse(Request.Params["warehouse_statu"]) != -1) //調度狀態
{
query.warehouse_status = int.Parse(Request.Params["warehouse_statu"]);
}
query.priority = -1;
if (!string.IsNullOrEmpty(Request.Params["priority"]) && int.Parse(Request.Params["priority"]) != -1)//出貨篩選
{
query.priority = int.Parse(Request.Params["priority"]);
}
if (!string.IsNullOrEmpty(Request.Params["datequery"]) && int.Parse(Request.Params["datequery"]) != 0)//日期條件
{
if (!string.IsNullOrEmpty(Request.Params["time_start"]))//出貨日期
{
query.time_start = DateTime.Parse(Request.Params["time_start"]);
}
if (!string.IsNullOrEmpty(Request.Params["time_end"]))//出貨日期
{
query.time_end = DateTime.Parse(Request.Params["time_end"]);
}
}
if (!string.IsNullOrEmpty(Request.Params["search"]))//搜索
{
query.vendor_name_simple = Request.Params["search"];
}
#endregion
_DeliverMsterMgr = new DeliverMasterMgr(mySqlConnectionString);
string fileName = "delivers" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv";
store = _DeliverMsterMgr.GetdeliverListCSV(query);
#region 獲取物流業者列表，訂單狀態列表。然後根據判斷來<數據來自參數表>
List<Parametersrc> Shipment = new List<Parametersrc>();
_ptersrc = new ParameterMgr(mySqlConnectionString);
Shipment = _ptersrc.GetAllKindType("Deliver_Store");//物流業者
DataTable _dtShipment = new DataTable();
_dtShipment.Columns.Add("parametercode", typeof(String));
_dtShipment.Columns.Add("parameterName", typeof(String));
_dtShipment.Columns.Add("remark", typeof(String));
foreach (var item in Shipment)
{
DataRow dr = _dtShipment.NewRow();
dr[0] = item.ParameterCode;
dr[1] = item.parameterName;
dr[2] = item.remark;
_dtShipment.Rows.Add(dr);
}
Shipment = _ptersrc.GetAllKindType("order_status");//訂單狀態
DataTable _dtOrderStatus = new DataTable();
_dtOrderStatus.Columns.Add("parametercode", typeof(String));
_dtOrderStatus.Columns.Add("parameterName", typeof(String));
_dtOrderStatus.Columns.Add("remark", typeof(String));
foreach (var item in Shipment)
{
DataRow dr = _dtOrderStatus.NewRow();
dr[0] = item.ParameterCode;
dr[1] = item.parameterName;
dr[2] = item.remark;
_dtOrderStatus.Rows.Add(dr);
}
#endregion
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));
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));
dtHZ.Columns.Add("付款時間", typeof(String));
foreach (var item in store)
{
DataRow dr = dtHZ.NewRow();
dr[0] = processingTime(item.delivery_date);
dr[1] = processingTime(item.arrival_date);
dr[2] = item.order_id;
#region 把訂單狀態和物流業者提取賦值
DataRow[] rows = _dtShipment.Select("ParameterCode='" + item.delivery_store + "'");
foreach (DataRow row in rows)//篩選出的最多只有一條數據，如果有，把物流商的名稱傳遞過去，如果沒有，把物流商編號傳遞過去
{
item.ShipmentName = item.delivery_store.ToString();
if (!string.IsNullOrEmpty(row["ParameterCode"].ToString()))
{
// item.ShipmentName = row["ParameterName"].ToString();
dr[12] = row["ParameterName"].ToString();//---物流業者
}
}
if (item.type == 1 || item.type == 2)//訂單狀態
{
rows = _dtOrderStatus.Select("ParameterCode='" + item.order_status + "'");
foreach (DataRow row in rows)//篩選出的最多只有一條數據，如果有，把物流商的名稱傳遞過去，如果沒有，把物流商編號傳遞過去
{
dr[3] = item.order_status.ToString();
if (!string.IsNullOrEmpty(row["ParameterCode"].ToString()))
{
dr[3] = row["remark"].ToString();
}
}
}
#endregion
dr[4] = item.delivery_name;
dr[5] = item.deliver_id;
#region 出貨單狀態
uint stat = item.delivery_status;//出貨單狀態
switch (stat)
{
case 0:
dr[6] = "待出貨";
break;
case 1:
dr[6] = "可出貨";
break;
case 2:
dr[6] = "出貨中";
break;
case 3:
dr[6] = "已出貨";
break;
case 4:
dr[6] = "已到貨";
break;
case 5:
dr[6] = "未到貨";
break;
case 6:
dr[6] = "取消出貨";
break;
case 7:
dr[6] = "待取貨";
break;
default:
dr[6] = "意外數據錯誤";
break;
}
#endregion
dr[7] = item.vendor_name_simple;
#region 運送方式
uint freight_set = item.freight_set;
switch (freight_set)
{
case 1:
dr[8] = "常溫";
break;
case 2:
dr[8] = "冷凍";
break;
case 3:
dr[8] = "常溫免運";
break;
case 4:
dr[8] = "冷凍免運";
break;
case 5:
dr[8] = "冷藏";
break;
case 6:
dr[8] = "冷藏免運";
break;
default:
dr[8] = freight_set;
break;
}
#endregion
dr[9] = processingTime(item.estimated_delivery_date);
dr[10] = processingTime(item.estimated_arrival_date);
#region 預計到貨時段
switch (item.estimated_arrival_period)
{
case 0:
dr[11] = "不限時";
break;
case 1:
dr[11] = "12:00以前";
break;
case 2:
dr[11] = "12:00-17:00";
break;
case 3:
dr[11] = "17:00-20:00";
break;
default:
dr[11] = item.estimated_arrival_period;
break;
}
#endregion
// dr[11] = item.estimated_arrival_period;//--預計到貨時段
//dr[12] = item.delivery_date;
dr[13] = "";//--調度
if (item.warehouse_status != 0)
{
dr[13] = "調度";
}
dr[14] = item.delivery_code;//--物流單號
dr[15] = item.delivery_freight_cost;//--物流費
dr[16] = processingTime(item.order_pay_date);//--可出貨時間
dr[17] = processingTime(item.money_pay_date);//--付款時間
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();
}
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:[]}";
}
}
//add by yafeng0715j 2015-08-20 PM
public HttpResponseBase GetDeliverMasterList()
{
DateTime dt = DateTime.Now.AddDays(-1);
DateTime dtStart = Convert.ToDateTime(dt.ToString("yyyy-MM-dd 00:00:00"));
DateTime dtEnd = Convert.ToDateTime(dt.ToString("yyyy-MM-dd 23:59:59"));
string jsonStr = string.Empty;
try
{
DeliverMasterQuery query = new DeliverMasterQuery();
DateTime date;
if (DateTime.TryParse(Request.Params["time_start"], out date))
{
query.time_start = Convert.ToDateTime(date.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
query.time_start = dtStart;
}
if (DateTime.TryParse(Request.Params["time_end"], out date))
{
query.time_end = Convert.ToDateTime(date.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
query.time_end = dtEnd;
}
_ptersrc = new ParameterMgr(mySqlConnectionString);
List<Parametersrc> parametersrcList = _ptersrc.ReturnParametersrcList();
int sum2 = 0;
int sum92 = 0;
DataTable table = new DataTable();
table.Columns.Add("物流商", typeof(string));
table.Columns.Add("統倉包裹數", typeof(string));
table.Columns.Add("冷凍倉包裹數", typeof(string));
_DeliverMsterMgr = new DeliverMasterMgr(mySqlConnectionString);
for (int i = 0; i < parametersrcList.Count; i++)
{
Parametersrc par = parametersrcList[i];
query.delivery_store = uint.Parse(par.ParameterCode);
query.export_id = 2;
int sum1 = 0;
sum1 = _DeliverMsterMgr.GetDeliverMasterCount(query);
sum2 += sum1;
query.export_id = 92;
int sum = _DeliverMsterMgr.GetDeliverMasterCount(query);
sum92 += sum;
DataRow dr = table.NewRow();
dr[0] = parametersrcList[i].parameterName;
dr[1] = sum1;
dr[2] = sum;
if (sum1 != 0 || sum != 0)
{
table.Rows.Add(dr);
}
if (i == (parametersrcList.Count - 1))
{
dr[0] = "總計";
dr[1] = sum2;
dr[2] = sum92;
table.Rows.Add(dr);
}
}
jsonStr = "{success:true,data:" + JsonConvert.SerializeObject(table) + "}";//返回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;
}
/// <summary>
/// 出貨單期望到貨日
/// </summary>
/// <returns></returns>
// by zhaozhi0623j add at 20151110
public HttpResponseBase GetDeliverExpectArrivalList()
{
string json = string.Empty;
DeliverMasterQuery dmQuery = new DeliverMasterQuery();
_DeliverMsterMgr = new DeliverMasterMgr(mySqlConnectionString);
List<DeliverMasterQuery> dmList = new List<DeliverMasterQuery>();
try
{
dmQuery.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
dmQuery.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
#region 查詢條件
if (!string.IsNullOrEmpty(Request.Params["productMode"]))//type為0時，表示全部
{
dmQuery.type = Convert.ToUInt32(Request.Params["productMode"]);
}
if (!string.IsNullOrEmpty(Request.Params["freightType"]))//freight_set為0時，表示全部
{
dmQuery.freight_set = Convert.ToUInt32(Request.Params["freightType"]);
}
if (!string.IsNullOrEmpty(Request.Params["deliveryStatus"]))//delivery_status為10000時，表示全部狀態
{
dmQuery.delivery_status = Convert.ToUInt32(Request.Params["deliveryStatus"]);
}
if (!string.IsNullOrEmpty(Request.Params["deliverId"]))
{
dmQuery.deliver_id = Convert.ToUInt32(Request.Params["deliverId"]);
}
if (!string.IsNullOrEmpty(Request.Params["orderId"]))
{
dmQuery.order_id = Convert.ToInt32(Request.Params["orderId"]);
}
string vendorId_ro_name = Request.Params["vendorId_ro_name"];
if (!string.IsNullOrEmpty(vendorId_ro_name))
{
if (Regex.IsMatch(vendorId_ro_name, @"^[0-9]*$"))
{
dmQuery.vendor_id = Convert.ToUInt32(vendorId_ro_name);
}
else
{
dmQuery.vendor_name_full = vendorId_ro_name;
}
}
if (!string.IsNullOrEmpty(Request.Params["time_start"]))
{
dmQuery.time_start = Convert.ToDateTime(Convert.ToDateTime(Request.Params["time_start"]).ToString("yyyy-MM-dd "));
}
if (!string.IsNullOrEmpty(Request.Params["time_end"]))
{
dmQuery.time_end = Convert.ToDateTime(Convert.ToDateTime(Request.Params["time_end"]).ToString("yyyy-MM-dd "));
}
#endregion
int totalCount = 0;
dmList = _DeliverMsterMgr.GetDeliverExpectArriveList(dmQuery,out totalCount);
foreach (var item in dmList)
{
if (item.deliver_org_days == 0)
{
item.deliver_org_days_str = "";
}
else
{
item.deliver_org_days_str = CommonFunction.GetNetTime(item.deliver_org_days).ToString("yyyy-MM-dd");
}
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd";
json = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(dmList, Formatting.Indented, timeConverter) + "}";
}
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}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase GetDelayDeliverList()
{
string jsonStr = string.Empty;
try
{
DeliverMasterQuery query = new DeliverMasterQuery();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");
if (!string.IsNullOrEmpty(Request.Params["limit"]))
{
query.Limit = Convert.ToInt32(Request.Params["limit"]);
}
_DeliverMsterMgr = new DeliverMasterMgr(mySqlConnectionString);
int totalCount = 0;
DataTable _dt = _DeliverMsterMgr.GetDelayDeliverList(query, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd";// HH:mm:ss
jsonStr = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(_dt, 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;
}
public HttpResponseBase HuiruPiciAddwuliufei()
{
string json = string.Empty;//json字符串
string shipment = Request.Params["shipment"].ToString();
//int total = 0;
try
{
if (Request.Files["ImportFileMsg"] != null && Request.Files["ImportFileMsg"].ContentLength > 0)//判斷文件是否為空
{
HttpPostedFileBase excelFile = Request.Files["ImportFileMsg"];//獲取文件流
FileManagement fileManagement = new FileManagement();//實例化 FileManagement
string fileLastName = excelFile.FileName;
string newExcelName = Server.MapPath(excelPath) + "PiCiAddWuliufei" + fileManagement.NewFileName(excelFile.FileName);//處理文件名，獲取新的文件名
excelFile.SaveAs(newExcelName);//上傳文件
DataTable dt = new DataTable();
NPOI4ExcelHelper helper = new NPOI4ExcelHelper(newExcelName);
dt = helper.SheetData();
DataRow[] dr = dt.Select(); //定义一个DataRow数组,读取ds里面所有行
int rowsnum = dt.Rows.Count;
if (rowsnum > 0)//判斷是否是這個表
{
DeliverMasterQuery dmQuery = new DeliverMasterQuery();
StringBuilder str = new StringBuilder();
DataTable dtMaster = new DataTable();
DataTable ExcelDt = new DataTable();
string filenameExcel = string.Empty;
ExcelDt.Columns.Add("訂單編號", typeof(String));
ExcelDt.Columns.Add("物流單號", typeof(String));
ExcelDt.Columns.Add("物流費", typeof(String));
ExcelDt.Columns.Add("應收帳款", typeof(String));
ExcelDt.Columns.Add("異常信息", typeof(String));
int i = 0; int j = 0; int x = 0; string y = string.Empty;
int successcount = 0;
int failcount = 0;
string create_dtim = CommonFunction.DateTimeToString(DateTime.Now); //創建時間
int create_user = (System.Web.HttpContext.Current.Session["caller"] as Caller).user_id;
_DeliverMsterMgr = new DeliverMasterMgr(mySqlConnectionString);
#region 循環excel表中的數據 并判斷是否滿足條件和失敗的個數
for (int z = 0; z < dr.Length; z++)
{
x = Convert.ToInt32(dr[z][0].ToString());//訂單編號
y = dr[z][1].ToString();//托運單號
i = Convert.ToInt32(dr[z][2].ToString());//運送金額
j = Convert.ToInt32(dr[z][3].ToString());//代收金額--也就是購物應該付款總金額
dmQuery.delivery_code = y;//托運單號
dtMaster = _DeliverMsterMgr.GetMessageByDeliveryCode(dmQuery);
string errorstring = string.Empty;
bool behavior = true;
if (dtMaster.Rows.Count <= 0)
{
errorstring = "物流單號不存在";
behavior = false;
}
else if (Convert.ToInt32(dtMaster.Rows[0]["delivery_store"]) != Convert.ToInt32(shipment))//如果物流方式不對應
{
errorstring = "物流廠商不相符,此物流單對應物流為:" + dtMaster.Rows[0]["parameterName"].ToString();
behavior = false;
}
//else if (y != dtMaster.Rows[0]["delivery_code"].ToString())
//{
// errorstring = "物流單號不相符";
// behavior = false;
//}
else if (dtMaster.Rows[0]["order_id"].ToString() != x.ToString())
{
errorstring = "定單編號不相符,此物流單對應的定單編號為:" + dtMaster.Rows[0]["order_id"].ToString();
behavior = false;
}
else if (dtMaster.Rows.Count > 1)
{
errorstring = "物流單號重複";
behavior = false;
}
//10表示黑貓貨到付款 order_amount購物應付總金額(加運費,扣除扺用紅利等金額)
else if (Convert.ToInt32(dtMaster.Rows[0]["delivery_store"]) == 10 && j != Convert.ToInt32(dtMaster.Rows[0]["order_amount"]))
{
errorstring = "應收帳款金額不符";
behavior = false;
}
//4表示已出貨 9 表示待取貨
else if (Convert.ToInt32(dtMaster.Rows[0]["order_status"]) != 4 && Convert.ToInt32(dtMaster.Rows[0]["order_status"]) != 9)
{
_ptersrc = new ParameterMgr(mySqlConnectionString);
int types = Convert.ToInt32(dtMaster.Rows[0]["order_status"]);
string endresult = _ptersrc.GetOrderStatus(types);
errorstring = "出貨狀態異常：出貨狀態為" + endresult;
behavior = false;
}
if (behavior == true)//如果數據不存在這些問題 如果failcount大於0就表示匯入信息有不正確的
{
successcount = successcount + 1;
//DataRow Execldr = ExcelDt.NewRow();
//Execldr[0] = x;
//Execldr[1] = y;
//Execldr[2] = i;
//Execldr[3] = dtMaster.Rows[0]["order_amount"];
//Execldr[4] = "數據正常";
//ExcelDt.Rows.Add(Execldr);
str.AppendFormat(" set sql_safe_updates = 0;update deliver_master set delivery_freight_cost='{0}',creator='{1}',modified='{2}' where delivery_code='{3}' ;set sql_safe_updates = 1;", i, create_user, create_dtim, y);
}
else
{
failcount = failcount + 1;
DataRow Execldr = ExcelDt.NewRow();
Execldr[0] = x;
Execldr[1] = y;
Execldr[2] = i;
Execldr[3] = j;//dtMaster.Rows[0]["order_amount"];
Execldr[4] = errorstring;
ExcelDt.Rows.Add(Execldr);
//此次上傳資料有異請下載差異檔
}
}
#endregion
#region 判斷失敗個數 成功個數 當失敗個數大於0時 直接匯出excel
if (failcount > 0)//存在失敗的情況 直接匯出數據
{
string fileName = DateTime.Now.ToString("匯出批次上傳物流費不規則數據_yyyyMMddHHmm") + ".xls";
MemoryStream ms = ExcelHelperXhf.ExportDT(ExcelDt, "匯出批次上傳物流費不規則數據");
Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
Response.BinaryWrite(ms.ToArray());
return this.Response;
}
else if (failcount == 0 && successcount > 0)
{
if (_DeliverMsterMgr.Updatedeliveryfreightcost(str) > 0)
{
json = "{success:true,total:" + successcount + ",msg:\"" + "匯入成功" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
else
{
json = "{success:false}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
}
else
{
json = "{success:true,msg:\"" + "此表內沒有數據或數據有誤,請檢查后再次匯入!" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
#endregion
}
else
{
json = "{success:true,total:0,msg:\"" + "此表內沒有數據或數據有誤,請檢查后再次匯入!" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
}
else//當直接點擊時會產生,
{
json = "{success:true,msg:\"" + "請匯入批次上傳物流費表" + "\"}";
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
}
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,msg:\"" + ex.ToString() + "\"}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
public HttpResponseBase DeliversList()
{
string jsonStr = String.Empty;
StringBuilder sb = new StringBuilder();
List<DeliverMasterQuery> store = new List<DeliverMasterQuery>();
DeliverMasterQuery query = new DeliverMasterQuery();
#region 现在参数表没有的数据，先加进来
DataTable _dtDeliverCatStatus = new DataTable();
DataTable _dtLogisticsType = new DataTable();
#region _dtDeliverCatStatus
_dtDeliverCatStatus.Columns.Add("ParameterCode", typeof(String));
_dtDeliverCatStatus.Columns.Add("remark", typeof(String));
DataRow dr = _dtDeliverCatStatus.NewRow();
dr[0] = 1;
dr[1] = "順利送達";
_dtDeliverCatStatus.Rows.Add(dr);
dr = _dtDeliverCatStatus.NewRow();
dr[0] = 2;
dr[1] = "轉運中";
_dtDeliverCatStatus.Rows.Add(dr);
dr = _dtDeliverCatStatus.NewRow();
dr[0] = 3;
dr[1] = "配送中";
_dtDeliverCatStatus.Rows.Add(dr);
dr = _dtDeliverCatStatus.NewRow();
dr[0] = 4;
dr[1] = "配送中(當配下車) (當配上車)";
_dtDeliverCatStatus.Rows.Add(dr);
dr = _dtDeliverCatStatus.NewRow();
dr[0] = 5;
dr[1] = "取件中";
_dtDeliverCatStatus.Rows.Add(dr);
dr = _dtDeliverCatStatus.NewRow();
dr[0] = 6;
dr[1] = "已集貨";
_dtDeliverCatStatus.Rows.Add(dr);
dr = _dtDeliverCatStatus.NewRow();
dr[0] = 7;
dr[1] = "取消取件";
_dtDeliverCatStatus.Rows.Add(dr);
dr = _dtDeliverCatStatus.NewRow();
dr[0] = 8;
dr[1] = "未順利取件，請洽客服中心";
_dtDeliverCatStatus.Rows.Add(dr);
dr = _dtDeliverCatStatus.NewRow();
dr[0] = 9;
dr[1] = "暫置營業所保管中(請聯絡黑貓宅急便)";
_dtDeliverCatStatus.Rows.Add(dr);
dr = _dtDeliverCatStatus.NewRow();
dr[0] = 10;
dr[1] = "調查處理中";
_dtDeliverCatStatus.Rows.Add(dr);
dr = _dtDeliverCatStatus.NewRow();
dr[0] = 11;
dr[1] = "不在家.公司行號休息";
_dtDeliverCatStatus.Rows.Add(dr);
#endregion
#region _dtLogisticsType
_dtLogisticsType.Columns.Add("ParameterCode", typeof(String));
_dtLogisticsType.Columns.Add("remark", typeof(String));
dr = _dtLogisticsType.NewRow();
dr[0] = 1;
dr[1] = "大物流中心";
_dtLogisticsType.Rows.Add(dr);
dr = _dtLogisticsType.NewRow();
dr[0] = 2;
dr[1] = "配送中";
_dtLogisticsType.Rows.Add(dr);
dr = _dtLogisticsType.NewRow();
dr[0] = 3;
dr[1] = "大物流退貨";
_dtLogisticsType.Rows.Add(dr);
dr = _dtLogisticsType.NewRow();
dr[0] = 4;
dr[1] = "進店作業";
_dtLogisticsType.Rows.Add(dr);
dr = _dtLogisticsType.NewRow();
dr[0] = 5;
dr[1] = "進店退貨";
dr = _dtLogisticsType.NewRow();
dr = _dtLogisticsType.NewRow();
dr[0] = 6;
dr[1] = "取貨完成";
_dtLogisticsType.Rows.Add(dr);
#endregion
#endregion
#region 查詢條件
if (!string.IsNullOrEmpty(Request.Params["type"]))//出貨類別
{
query.types = Request.Params["type"];
}
if (!string.IsNullOrEmpty(Request.Params["delivery_status"]) && int.Parse(Request.Params["delivery_status"]) != -1)//出貨狀態
{
query.status = Request.Params["delivery_status"];
}
if (!string.IsNullOrEmpty(Request.Params["export_id"]) && int.Parse(Request.Params["export_id"]) != 0) //出貨方式
{
query.vendor_id = uint.Parse(Request.Params["export_id"]);
}
query.delivery_store = 0;
if (!string.IsNullOrEmpty(Request.Params["delivery_store"]) && int.Parse(Request.Params["delivery_store"]) != 0)//物流商
{
query.delivery_store = uint.Parse(Request.Params["delivery_store"]);
}
query.warehouse_status = -1;
if (!string.IsNullOrEmpty(Request.Params["warehouse_statu"]) && int.Parse(Request.Params["warehouse_statu"]) != -1) //調度狀態
{
query.warehouse_status = int.Parse(Request.Params["warehouse_statu"]);
}
query.priority = -1;
if (!string.IsNullOrEmpty(Request.Params["priority"]) && int.Parse(Request.Params["priority"]) != -1)//出貨篩選
{
query.priority = int.Parse(Request.Params["priority"]);
}
if (!string.IsNullOrEmpty(Request.Params["datequery"]) && int.Parse(Request.Params["datequery"]) != 0)//日期條件
{
if (!string.IsNullOrEmpty(Request.Params["time_start"]))//出貨日期
{
string s = Request.Params["time_start"];
query.time_start = DateTime.Parse(Request.Params["time_start"]);
}
if (!string.IsNullOrEmpty(Request.Params["time_end"]))//出貨日期
{
query.time_end = DateTime.Parse(Request.Params["time_end"]);
}
}
if (!string.IsNullOrEmpty(Request.Params["search"]))//搜索
{
query.vendor_name_simple = Request.Params["search"];
}
#endregion
try
{
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
_DeliverMsterMgr = new DeliverMasterMgr(mySqlConnectionString);
int totalCount = 0;
store = _DeliverMsterMgr.GetdeliverList(query, out totalCount);
#region 獲取物流業者列表，訂單狀態列表。然後根據判斷來<數據來自參數表>
List<Parametersrc> Shipment = new List<Parametersrc>();
_ptersrc = new ParameterMgr(mySqlConnectionString);
Shipment = _ptersrc.GetAllKindType("Deliver_Store");//物流業者
DataTable _dtShipment = new DataTable();
_dtShipment.Columns.Add("parametercode", typeof(String));
_dtShipment.Columns.Add("parameterName", typeof(String));
_dtShipment.Columns.Add("remark", typeof(String));
foreach (var item in Shipment)
{
dr = _dtShipment.NewRow();//--------------------------------------------------DataRow dr=...
dr[0] = item.ParameterCode;
dr[1] = item.parameterName;
dr[2] = item.remark;
_dtShipment.Rows.Add(dr);
}
Shipment = _ptersrc.GetAllKindType("order_status");//訂單狀態
DataTable _dtOrderStatus = new DataTable();
_dtOrderStatus.Columns.Add("parametercode", typeof(String));
_dtOrderStatus.Columns.Add("parameterName", typeof(String));
_dtOrderStatus.Columns.Add("remark", typeof(String));
foreach (var item in Shipment)
{
dr = _dtOrderStatus.NewRow();//------------------------------------------dr
dr[0] = item.ParameterCode;
dr[1] = item.parameterName;
dr[2] = item.remark;
_dtOrderStatus.Rows.Add(dr);
}
#endregion
foreach (var item in store)
{
//(計算數據，參考自admin/View/Delivers/index.ctp 第152~163行)
#region 計算逾期天數：(出貨時間-付款單成立日期+1)-4，出貨時間=空值，以當日計算。
DateTime Shipmenttime = DateTime.Now;//出貨日期
//if (!string.IsNullOrEmpty(item.delivery_date.ToString()))
//{
// Shipmenttime = item.delivery_date;
//}
if (!item.delivery_date.ToString("yyyy-MM-dd").Equals("0001-01-01"))
{
Shipmenttime = item.delivery_date;
}
TimeSpan s = DateTime.Parse(Shipmenttime.ToString("yyyy-MM-dd 23:59:59")) - DateTime.Parse(item.order_createtime.ToString("yyyy-MM-dd 23:59:59"));//時間跨度,因為捨棄的不一樣，計算的也不一樣
item.overdue_day = s.Days - 3;
#endregion
#region 把訂單狀態和物流業者提取賦值
DataRow[] rows = _dtShipment.Select("ParameterCode='" + item.delivery_store + "'");
foreach (DataRow row in rows)//篩選出的最多只有一條數據，如果有，把物流商的名稱傳遞過去，如果沒有，把物流商編號傳遞過去
{
item.ShipmentName = item.delivery_store.ToString();
if (!string.IsNullOrEmpty(row["ParameterCode"].ToString()))
{
item.ShipmentName = row["ParameterName"].ToString();
}
}
if (item.type == 1 || item.type == 2)//訂單狀態
{
rows = _dtOrderStatus.Select("ParameterCode='" + item.order_status + "'");
foreach (DataRow row in rows)//篩選出的最多只有一條數據，如果有，把物流商的名稱傳遞過去，如果沒有，把物流商編號傳遞過去
{
item.states = item.order_status.ToString();
if (!string.IsNullOrEmpty(row["ParameterCode"].ToString()))
{
item.states = row["remark"].ToString();
}
}
}
#endregion
#region 物流状态
item.LogisticsStatus = "";
if (item.logisticsType != 0)
{
if (item.delivery_store == 1 || item.delivery_store == 10)
{
rows = _dtDeliverCatStatus.Select("ParameterCode='" + item.logisticsType + "'");
foreach (DataRow row in rows)//篩選出的最多只有一條數據，如果有，把物流商的名稱傳遞過去，如果沒有，把物流商編號傳遞過去
{
if (!string.IsNullOrEmpty(row["ParameterCode"].ToString()))
{
item.LogisticsStatus = row["remark"].ToString();
}
}
}
else
{
rows = _dtLogisticsType.Select("ParameterCode='" + item.logisticsType + "'");
foreach (DataRow row in rows)//篩選出的最多只有一條數據，如果有，把物流商的名稱傳遞過去，如果沒有，把物流商編號傳遞過去
{
if (!string.IsNullOrEmpty(row["ParameterCode"].ToString()))
{
item.LogisticsStatus = row["remark"].ToString();
}
}
}
}
#endregion
}
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss ";
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;
}
/// <summary>
/// 驗證訂單編號和出貨單號的對應
/// </summary>
/// <returns></returns>
public HttpResponseBase JudgeOrdid()
{
string json = String.Empty;
DeliverMaster dm = new DeliverMaster();
List<DeliverMasterQuery> list = new List<DeliverMasterQuery>();
_DeliverMsterMgr = new DeliverMasterMgr(mySqlConnectionString);
int msg = 0;
try
{
string deliver_id = Request.Params["deliver_id"];
string D = deliver_id.Substring(0, 1);
if (D == "D")
{
deliver_id = deliver_id.Substring(1, deliver_id.Length - 1);
uint deliver; int ord;
if (uint.TryParse(deliver_id, out deliver))
{
dm.deliver_id = uint.Parse(deliver_id);
}
if (int.TryParse(Request.Params["order_id"], out ord))
{
dm.order_id = int.Parse(Request.Params["order_id"]);
}
if (dm.deliver_id != 0 && dm.order_id != 0)
{
list = _DeliverMsterMgr.JudgeOrdid(dm);
}
if (list.Count > 0)
{
foreach (var item in list)
{
if (item.delivery_status >= 3)
{
msg = 2;
}
}
json = "{success:true,msg:" + msg + ",data:" + JsonConvert.SerializeObject(list, Formatting.Indented) + "}";//返回json數據
}
else
{
dm.deliver_id = 0;
list = _DeliverMsterMgr.JudgeOrdid(dm);
if (list.Count > 0)
{
msg = 5;
json = "{success:true,msg:" + msg + ",data:" + JsonConvert.SerializeObject(list, Formatting.Indented) + "}";//返回json數據
}
else
{
json = "{success:true,msg:1}";
}
}
}
else
{
json = "{success:true,msg:3}";
}
}
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,msg:0}";
}
this.Response.Clear();
this.Response.Write(json.ToString());
this.Response.End();
return this.Response;
}
/// <summary>
/// 出貨確認列表
/// </summary>
/// <returns></returns>
public HttpResponseBase DeliverVerifyList()
{
DeliverMaster dm = new DeliverMaster();
_DeliverMsterMgr = new DeliverMasterMgr(mySqlConnectionString);
List<DeliverMasterQuery> stores = new List<DeliverMasterQuery>();
string json = string.Empty;
try
{
dm.deliver_id = uint.Parse(Request.Params["deliver_id"]);
dm.order_id = int.Parse(Request.Params["order_id"]);
int totalCount = 0;
stores = _DeliverMsterMgr.DeliverVerifyList(dm, out totalCount);
json = "{success:true,data:" + JsonConvert.SerializeObject(stores) + "}";//返回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;
}
