public List<DeliverMasterQuery> GetDeliverMaster(DeliverMasterQuery dm)
{
try
{
return _IDeliverDetailDao.GetDeliverMaster(dm);
}
catch (Exception ex)
{
throw new Exception("DeliverDetailMgr-->GetDeliverMaster-->" + ex.Message, ex);
}
}
public List<DeliverMasterQuery> GetdeliverList(DeliverMasterQuery deliver, out int totalCount)
{
try
{
return _ideliver.GetdeliverList(deliver, out totalCount);
}
catch (Exception ex)
{
throw new Exception("DeliverMasterMgr-->GetdeliverList-->" + ex.Message, ex);
}
}
public DataTable GetMessageByDeliveryCode(DeliverMasterQuery dmQuery)
{
try
{
return _ideliver.GetMessageByDeliveryCode(dmQuery);
}
catch (Exception ex)
{
throw new Exception("DeliverMasterMgr-->GetMessageByDeliveryCode-->" + ex.Message, ex);
}
}
public HttpResponseBase GetReportManagementList()
{
string json = string.Empty;
DataTable _dt = new DataTable();
DeliverMasterQuery dmQuery = new DeliverMasterQuery();
dmQuery.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
dmQuery.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量
try
{
if (!string.IsNullOrEmpty(Request.Params["order_id"]))
{
dmQuery.sorder_id = Request.Params["order_id"];
}
if (!string.IsNullOrEmpty(Request.Params["deliver_id"]))
{
dmQuery.sdeliver_id = Request.Params["deliver_id"];
}
if (!string.IsNullOrEmpty(Request.Params["logistics_type"]))
{
dmQuery.logisticsType = Convert.ToInt32(Request.Params["logistics_type"]);
}
if (!string.IsNullOrEmpty(Request.Params["delivery_status"]))
{
dmQuery.ideliver_status = Convert.ToInt32(Request.Params["delivery_status"]);
}
dmQuery.deliver_store = Convert.ToInt32(Request.Params["shipment_id"]);
dmQuery.i_order_status = Convert.ToInt32(Request.Params["order_status_id"]);
dmQuery.payment = Convert.ToInt32(Request.Params["payment_id"]);
dmQuery.order_time_begin = Convert.ToDateTime(Convert.ToDateTime(Request.Params["dateStart"]).ToString("yyyy-MM-dd HH:mm:ss"));//建立時間
dmQuery.order_time_end = Convert.ToDateTime(Convert.ToDateTime(Request.Params["dateEnd"]).ToString("yyyy-MM-dd HH:mm:ss"));
int totalCount = 0;
_dt = _delverMgr.GetReportManagementList(dmQuery, out totalCount);
IsoDateTimeConverter timeConverter = new IsoDateTimeConverter();
//这里使用自定义日期格式，如果不使用的话，默认是ISO8601格式
timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
json = "{success:true,'msg':'user',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);
json = "{success:false,totalCount:0,data:[]}";
}
this.Response.Clear();
this.Response.Write(json);
this.Response.End();
return this.Response;
}
/// <summary>
/// 出貨管理>出貨查詢:列表頁面
/// </summary>
/// <param name="deliver">實體</param>
/// <param name="totalCount">分頁</param>
/// <returns>返回集合</returns>
public List<DeliverMasterQuery> GetdeliverList(DeliverMasterQuery deliver, out int totalCount)
{
StringBuilder sqlClomn = new StringBuilder();
StringBuilder sqlTable = new StringBuilder();
StringBuilder sqlCondition = new StringBuilder();
StringBuilder sqlSort = new StringBuilder();
string addsql = getSqlForDliverList(deliver);
try
{
totalCount = 0;
sqlClomn.Append(@"select dm.type,dm.deliver_id,dm.delivery_status,dm.freight_set,dm.ticket_id,dm.export_id,dm.delivery_store ");
sqlClomn.Append(@",dm.delivery_code,dm.delivery_freight_cost,dm.delivery_date,dm.arrival_date,dm.estimated_delivery_date ");
sqlClomn.Append(@",dm.estimated_arrival_date,dm.estimated_arrival_period,dm.delivery_name,dm.order_id ,v.vendor_name_simple");
sqlClomn.Append(@",t.warehouse_status,FROM_UNIXTIME(om.order_createdate) as order_createtime,om.order_status ");
sqlClomn.Append(@", (select ld.logisticsType from logistics_detail ld where ld.deliver_id=dm.deliver_id order by rid desc limit 1)as logisticsType ");
sqlTable.Append(@" from deliver_master dm ");
sqlTable.Append(@" left join vendor v on v.vendor_id=dm.export_id left join order_master om on om.order_id=dm.order_id left join ticket t on dm.ticket_id=t.ticket_id ");
sqlCondition.Append(@" where 1=1 ");
if (!string.IsNullOrEmpty(addsql))//查詢條件
{
sqlCondition.Append(addsql);
}
else
{
sqlCondition.Append(" and dm.type=1");
}
if (deliver.IsPage)
{
string sql = "select count(dm.deliver_id) as total_count " + sqlTable.ToString() + sqlCondition.ToString();
System.Data.DataTable _dt = _access.getDataTable(sql);
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = int.Parse(_dt.Rows[0]["total_count"].ToString());
}
sqlSort.Append(@" order by dm.deliver_id desc ");
sqlSort.AppendFormat(" limit {0},{1} ;", deliver.Start, deliver.Limit);
}
else
{
sqlSort.Append(@" order by dm.deliver_id desc ");
}
string sqlstr = sqlClomn.ToString() + sqlTable.ToString() + sqlCondition.ToString() + sqlSort.ToString();
return _access.getDataTableForObj<DeliverMasterQuery>(sqlstr);
}
catch (Exception ex)
{
throw new Exception(" DeliverMasterDao-->GetdeliverList-->" + ex.Message + sqlClomn.ToString() + sqlTable.ToString() + sqlCondition.ToString() + sqlSort.ToString(), ex);
}
}
/// <summary>
/// 出貨信息
/// </summary>
/// <param name="dm"></param>
/// <returns></returns>
public List<DeliverMasterQuery> GetDeliverMaster(DeliverMasterQuery dm)
{
StringBuilder sql = new StringBuilder();
sql.AppendLine(@"SELECT dm.deliver_id,dm.type,dm.freight_set,dm.delivery_store,");
sql.AppendLine(@" dm.delivery_code,dm.delivery_date,dm.sms_date,dm.estimated_delivery_date,");
sql.AppendLine(@" dm.estimated_arrival_date,dm.estimated_arrival_period,dm.delivery_name,");
sql.AppendLine(@" dm.delivery_mobile,dm.delivery_phone,dm.delivery_zip,dm.delivery_address,");
sql.AppendLine(@" dm.delivery_status,dm.order_id,om.note_order,om.holiday_deliver");
sql.AppendLine(@" from deliver_master dm LEFT JOIN order_master om on om.order_id=dm.order_id");
sql.AppendFormat(@" where dm.deliver_id='{0}' LIMIT 1;", dm.deliver_id);
try
{
return _access.getDataTableForObj<DeliverMasterQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("DeliverDetailDao.GetDeliverMaster-->" + ex.Message + sql.ToString(), ex);
}
}
/// <summary>
///出貨管理->可出貨列表頁
/// </summary>
/// <param name="query">Model</param>
/// <param name="totalCount">分頁</param>
/// <returns></returns>
public List<DeliverMasterQuery> GetTicketDetailList(DeliverMasterQuery query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCondi = new StringBuilder();
try
{
sql.Append(@" select dm.deliver_id,dm.order_id,dm.delivery_name,dm.estimated_delivery_date,dm.estimated_arrival_date,dm.estimated_arrival_period ,om.note_order ");
sqlCondi.Append(" from deliver_master dm ");
sqlCondi.Append(" left join order_master om on dm.order_id=om.order_id ");
sqlCondi.Append(" where 1=1 ");
if (query.type != 0)
{
sqlCondi.AppendFormat(" and dm.type='{0}' ", query.type);
}
if (query.export_id != 0)
{
sqlCondi.AppendFormat(" and dm.export_id='{0}' ", query.export_id);
}
if (query.freight_set != 0)
{
sqlCondi.AppendFormat(" and dm.freight_set='{0}' ", query.freight_set);
}
if (query.delivery_status != 0)
{
sqlCondi.AppendFormat(" and dm.delivery_status='{0}' ", query.delivery_status);
}
totalCount = 0;
if (query.IsPage)
{
DataTable _dt = _access.getDataTable("select count(dm.deliver_id) as totalCount " + sqlCondi.ToString());
if (_dt.Rows.Count > 0)
{
totalCount = int.Parse(_dt.Rows[0]["totalCount"].ToString());
}
sqlCondi.AppendFormat(" limit {0},{1} ", query.Start, query.Limit);
}
sql.Append(sqlCondi.ToString());
return _access.getDataTableForObj<DeliverMasterQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("DeliverMasterDao.GetTicketDetailList" + ex.Message + sql.ToString(), ex);
}
}
public HttpResponseBase ModifyDeliveryData()
{
string json = string.Empty;
OrderMasterQuery om = new OrderMasterQuery();
DeliverMasterQuery dm = new DeliverMasterQuery();
try
{
if (!string.IsNullOrEmpty(Request.Params["order_id"]))
{
om.Order_Id = Convert.ToUInt32(Request.Params["order_id"].ToString());
}
if (!string.IsNullOrEmpty(Request.Params["user_name"]))
{
om.delivery_name = Request.Params["user_name"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["user_gender"]))
{
om.user_gender = Convert.ToUInt32(Request.Params["user_gender"]);//delivery_gender
}
if (!string.IsNullOrEmpty(Request.Params["user_mobile"]))
{
om.Delivery_Mobile = Request.Params["user_mobile"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["user_phone"]))
{
om.Delivery_Phone = Request.Params["user_phone"].ToString();
}
if (!string.IsNullOrEmpty(Request.Params["user_zip"]))
{
om.Delivery_Zip = Convert.ToUInt32(Request.Params["user_zip"]);
}
if (!string.IsNullOrEmpty(Request.Params["user_address"]))
{
om.Delivery_Address = Request.Params["user_address"].ToString();
}
om.user_id = Convert.ToUInt32((Session["caller"] as Caller).user_id);
om.user_name = (Session["caller"] as Caller).user_username;
System.Net.IPAddress[] addlist = Dns.GetHostByName(Dns.GetHostName()).AddressList;
om.Order_Ipfrom = addlist[0].ToString();
_orderMasterMgr = new OrderMasterMgr(mySqlConnectionString);
json = _orderMasterMgr.ModifyDeliveryData(om);
}
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 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 List<DeliverMasterQuery> GetdeliverListCSV(DeliverMasterQuery deliver)
{
StringBuilder sqlClomn = new StringBuilder();
StringBuilder sqlTable = new StringBuilder();
StringBuilder sqlCondition = new StringBuilder();
StringBuilder sqlSort = new StringBuilder();
string addsql = getSqlForDliverList(deliver);
try
{
sqlClomn.Append(@"select dm.type,dm.deliver_id,dm.delivery_status,dm.freight_set,dm.ticket_id,dm.export_id,dm.delivery_store ");
sqlClomn.Append(@",dm.delivery_code,dm.delivery_freight_cost,dm.delivery_date,dm.arrival_date,dm.estimated_delivery_date ");
sqlClomn.Append(@",dm.estimated_arrival_date,dm.estimated_arrival_period,dm.delivery_name,dm.order_id ,v.vendor_name_simple");
sqlClomn.Append(@",FROM_UNIXTIME(om.order_createdate) as order_createtime,om.order_status ");
sqlClomn.Append(@", FROM_UNIXTIME(om.money_collect_date) as money_pay_date, FROM_UNIXTIME(om.order_date_pay)as order_pay_date ");
sqlTable.Append(@" from deliver_master dm,order_master om,vendor v ");
sqlCondition.Append(@" where 1=1 and dm.order_id = om.order_id and v.vendor_id= dm.export_id ");
if (deliver.warehouse_status != -1) //調度狀態
{
sqlTable.Append(@" ,ticket t ");
sqlCondition.Append(@" and dm.ticket_id = t.ticket_id ");
}
else
{
sqlClomn.Append(@",(SELECT warehouse_status FROM ticket WHERE 1=1 AND ticket_id =dm.ticket_id) as warehouse_status ");
}
if (!string.IsNullOrEmpty(addsql))//查詢條件
{
sqlCondition.Append(addsql);
}
else
{
sqlCondition.Append(" and dm.type=1");
}
//if (deliver.IsPage)
//{
// string sql = "select count(dm.deliver_id) as total_count " + sqlTable.ToString() + sqlCondition.ToString();
// System.Data.DataTable _dt = _access.getDataTable(sql);
// if (_dt != null && _dt.Rows.Count > 0)
// {
// totalCount = int.Parse(_dt.Rows[0]["total_count"].ToString());
// }
//sqlSort.Append(@" order by dm.deliver_id desc ");
// sqlSort.AppendFormat(" limit {0},{1} ;", deliver.Start, deliver.Limit);
//}
//else
//{
sqlSort.Append(@" order by dm.deliver_id desc ");
//}
string sql = sqlClomn.ToString() + sqlTable.ToString() + sqlCondition.ToString() + sqlSort.ToString();
return _access.getDataTableForObj<DeliverMasterQuery>(sql);
}
catch (Exception ex)
{
throw new Exception(" DeliverMasterDao-->GetdeliverList-->" + ex.Message + sqlClomn.ToString() + sqlTable.ToString() + sqlCondition.ToString() + sqlSort.ToString(), ex);
}
}
public object GetChannelOrderList(DeliverMasterQuery dmq, out int totalCount, int type = 0)
{
try
{
return _IDeliverDetailDao.GetChannelOrderList(dmq, out totalCount, type);
}
catch (Exception ex)
{
throw new Exception("DeliverDetailMgr-->GetChannelOrderList-->" + ex.Message, ex);
}
}
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:[]}";
}
}
public object GetChannelOrderList(DeliverMasterQuery dmq, out int totalCount, int type = 0)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlfromm = new StringBuilder();
StringBuilder sqlfrom = new StringBuilder();
StringBuilder sqlwhere = new StringBuilder();
totalCount = 0;
try
{
sql.AppendFormat(@"SELECT om.retrieve_mode,tp1.parameterName as sretrieve_mode,om.channel,channel.channel_name_simple AS schannel,");
sql.AppendFormat(@"om.channel_order_id,om.order_id as od_order_id,dm.export_id,v.vendor_name_simple AS sexport_id,dm.freight_set,dm.delivery_store,");
sql.AppendFormat(@"tp2.parameterName AS sdelivery_store,dm.deliver_id,dm.delivery_code,dm.delivery_date,od.sub_order_id,dd.delivery_status AS dd_status");
sqlfromm.AppendFormat(@" FROM order_master om ");
sqlfromm.AppendFormat(@"INNER JOIN deliver_master dm USING (order_id) ");
sqlfromm.AppendFormat(@"INNER JOIN deliver_detail dd USING (deliver_id) ");
sqlfromm.AppendFormat(@"INNER JOIN order_detail od USING (detail_id) ");
sqlfrom.AppendFormat(@" LEFT JOIN (SELECT parameterCode,parameterName FROM t_parametersrc where parameterType = 'retrieve_mode' ) tp1 ON om.retrieve_mode = tp1.parameterCode ");
sqlfrom.AppendFormat(@" LEFT JOIN (SELECT parameterCode,parameterName FROM t_parametersrc where parameterType = 'Deliver_Store' ) tp2 ON dm.delivery_store = tp2.parameterCode ");
sqlfrom.AppendFormat(@"LEFT JOIN channel channel ON channel.channel_id = om.channel ");
sqlfrom.AppendFormat(@"LEFT JOIN vendor v ON v.vendor_id = dm.export_id ");
sqlfrom.AppendFormat(@"WHERE 1 = 1");
if (dmq.od_order_id != 0)
{
sqlwhere.AppendFormat(@" AND om.order_id='{0}' ", dmq.od_order_id);
}
if (!string.IsNullOrEmpty(dmq.channel_order_id))
{
sqlwhere.AppendFormat(@" AND om.channel_order_id ='{0}' ", dmq.channel_order_id);
}
if (!string.IsNullOrEmpty(dmq.sub_order_id))
{
sqlwhere.AppendFormat(@" AND od.sub_order_id ='{0}' ", dmq.sub_order_id);
}
if (dmq.delivery_store != 0)
{
sqlwhere.AppendFormat(@" AND dm.delivery_store ='{0}'", dmq.delivery_store);
}
if (dmq.channel != 0)
{
sqlwhere.AppendFormat(@" AND om.channel ='{0}'", dmq.channel);
}
if (dmq.dd_status != -1)
{
sqlwhere.AppendFormat(@" AND dd.delivery_status ='{0}'", dmq.dd_status);
}
if (dmq.retrieve_mode != -1)
{
sqlwhere.AppendFormat(@" AND om.retrieve_mode ='{0}' ", dmq.retrieve_mode);
}
if (!string.IsNullOrEmpty(dmq.sqlwhere))
{
sqlwhere.AppendFormat(dmq.sqlwhere);
}
sql.Append(sqlfromm.ToString() + sqlfrom.ToString() + sqlwhere.ToString());
if (dmq.IsPage)
{
DataTable dt = _access.getDataTable("select count(om.order_id) " + sqlfromm.ToString() + " where 1=1 " + sqlwhere.ToString());
if (dt.Rows.Count > 0)
{
totalCount = int.Parse(dt.Rows[0][0].ToString());
}
}
if (type == 0)
{
sql.AppendFormat(" limit {0},{1} ;", dmq.Start, dmq.Limit);
return _access.getDataTableForObj<DeliverMasterQuery>(sql.ToString());
}
else
{
sql.Clear();
string outcsv = "select sub_order_id,delivery_code " + sqlfromm.ToString() + " where 1=1 " + sqlwhere.ToString();
sql.AppendFormat(outcsv);
return _access.getDataTable(sql.ToString());
}
}
catch (Exception ex)
{
throw new Exception(" DeliverDetailDao-->GetChannelOrderList-->" + ex.Message + sql.ToString(), ex);
}
}
/// <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;
}
//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;
}
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;
}
/// <summary>
/// 出貨管理->延遲出貨列表
/// </summary>
/// <returns></returns>
public DataTable GetDelayDeliverList(DeliverMasterQuery query, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlCondi = new StringBuilder();
try
{
sql.Append("select DATE(FROM_UNIXTIME(om.order_date_pay)) AS date_pay,count(deliver_id) AS count ");
sqlCondi.Append(" from deliver_master dm ");
sqlCondi.Append(" left join order_master om on dm.order_id=om.order_id ");
sqlCondi.Append(" where om.order_date_pay > 0 and dm.type in (1,2) and dm.delivery_status in(0, 1, 2, 5) group by date_pay order by date_pay asc ");//Php頁面admin/view/delivers/delay_delivers.ctp第151和152行，寫的固定查詢數據
totalCount = 0;
if (query.IsPage)
{
DataTable _dt = _access.getDataTable(sql.ToString() + sqlCondi.ToString());
if (_dt.Rows.Count > 0)
{
totalCount = _dt.Rows.Count;
}
sqlCondi.AppendFormat(" limit {0},{1} ", query.Start, query.Limit);
}
sql.Append(sqlCondi.ToString());
return _access.getDataTable(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("DeliverMasterDao-->GetDelayDeliverList-->" + ex.Message, ex);
}
}
///add by zhaozhi0623j 20151110 pm
/// <summary>
/// 根據出貨單編號更新期望到貨日期、時段
/// </summary>
/// <returns></returns>
public int UpdateExpectArrive(DeliverMasterQuery Query)
{
StringBuilder sbSql = new StringBuilder();
Query.Replace4MySQL();
try
{
sbSql.AppendFormat(@"update deliver_master set expect_arrive_date='{0}',expect_arrive_period='{1}' where deliver_id='{2}'",
Query.expect_arrive_date.ToString("yyyy-MM-dd"), Query.expect_arrive_period, Query.deliver_id);
return _access.execCommand(sbSql.ToString());
}
catch (Exception ex)
{
throw new Exception(" DeliverMasterDao-->UpdateExpectArrive-->" + ex.Message + sbSql.ToString(), ex);
}
}
public DataTable GetDeliveryMsgExcelList(DeliverMasterQuery deliver)
{
StringBuilder strcondition = new StringBuilder();
StringBuilder str = new StringBuilder();
DataTable dt = new DataTable();
DataTable dt_type = new DataTable();
try
{
if (deliver.time_type == 2)
{
strcondition.AppendFormat(" and om.order_createdate >='{0}' and om.order_createdate <='{1}' ", Common.CommonFunction.GetPHPTime(deliver.order_time_begin.ToString()), Common.CommonFunction.GetPHPTime(deliver.order_time_end.ToString()));
}
if (!string.IsNullOrEmpty(deliver.sorder_id))
{
strcondition.AppendFormat(" and om.order_id = '{0}' ", deliver.sorder_id);
}
if (!string.IsNullOrEmpty(deliver.sdeliver_id))
{
strcondition.AppendFormat(" and dm.deliver_id = '{0}' ", deliver.sdeliver_id);
}
if (deliver.deliver_store != 0)
{
strcondition.AppendFormat(" and dm.delivery_store='{0}' ", deliver.deliver_store);
}
if (deliver.i_order_status != -1)
{
strcondition.AppendFormat(" and om.order_status='{0}' ", deliver.i_order_status);
}
if (deliver.order_day != 0)
{
strcondition.AppendFormat(" and dm.deliver_org_days<='{0}' ", Common.CommonFunction.GetPHPTime(DateTime.Now.AddDays(deliver.order_day).ToString("yyyy-MM-dd 23:59:59")));//預計到貨日期且未到貨
}
if (deliver.i_slave_status != -1)
{
strcondition.AppendFormat(" and os.slave_status='{0}' ", deliver.i_slave_status);
}
if (deliver.i_detail_status != -1)
{
strcondition.AppendFormat(" and odt.detail_status='{0}' ", deliver.i_detail_status);
}
if (deliver.payment != 0)
{
strcondition.AppendFormat(" and om.order_payment='{0}' ", deliver.payment);
}
if (deliver.logisticsType != 0)
{
strcondition.AppendFormat(" and ld.logisticsTypes='{0}' ", deliver.logisticsType);
}
if (deliver.ideliver_status != -1)
{
strcondition.AppendFormat(" and dm.delivery_status='{0}' ", deliver.ideliver_status);
}
if (deliver.product_mode != 0)
{
strcondition.AppendFormat(" and pt.product_mode ='{0}' ", deliver.product_mode);
}
if (!string.IsNullOrEmpty(deliver.serch_where))
{
strcondition.AppendFormat(" and vd.vendor_id in ({0})", deliver.serch_where);
}
strcondition.AppendFormat(" )as mytable ");
if (!string.IsNullOrEmpty(deliver.t_days.ToString()) && deliver.t_days != -1)
{
strcondition.AppendFormat(" where overdue_day>='{0}' ", deliver.t_days);
}
str.AppendFormat(@"SELECT delivery_code,deliver_id,delivery_date,created,order_id,order_date,order_status,order_payment,delivery_store,
logisticsTypes,delivery_status,dvendor_name_simple,vendor_name_simple,freight_set,delivery_freight_cost,delivery_date_str,
overdue_day,
arrival_date,estimated_delivery_date
,estimated_arrival_date,estimated_arrival_period,delivery_name,product_name,note_order,note_admin,
buy_num,item_id,product_mode,deliver_master_date,slave_status,detail_status,product_id,detail_id
FROM (
SELECT dm.delivery_code,case dm.deliver_org_days when 0 then '-' else FROM_UNIXTIME(dm.deliver_org_days) end as delivery_date_str,dm.deliver_id,dm.created,om.order_id,DATE(FROM_UNIXTIME(om.order_createdate)) as order_date,dd.detail_id ,pt.product_id,om.order_status,om.order_payment,dm.delivery_store,
ld.logisticsTypes,dm.delivery_status,vds.vendor_name_simple as dvendor_name_simple,vd.vendor_name_simple,dm.freight_set,dm.delivery_freight_cost,
case ISNULL(dm.delivery_date) when TRUE then DATEDIFF(NOW(),DATE(FROM_UNIXTIME(om.order_date_pay))) ELSE datediff(dm.delivery_date,DATE(FROM_UNIXTIME(om.order_date_pay))) end as overdue_day,
dm.arrival_date,dm.estimated_delivery_date
,dm.estimated_arrival_date,dm.estimated_arrival_period,dm.delivery_name,odt.product_name,om.note_order,om.note_admin,
case odt.item_mode WHEN 2 THEN odt.parent_num * odt.buy_num ELSE odt.buy_num END as buy_num,pii.item_id,pt.product_mode,DATE(FROM_UNIXTIME(om.order_date_pay)) as deliver_master_date,dm.delivery_date,os.slave_status,odt.detail_status
FROM deliver_master dm
INNER JOIN deliver_detail dd on dd.deliver_id = dm.deliver_id
LEFT JOIN (SELECT max(logisticsType) as logisticsTypes, deliver_id fROM logistics_detail GROUP BY deliver_id ) as ld
on ld.deliver_id=dm.deliver_id
INNER JOIN order_detail odt on odt.detail_id=dd.detail_id
INNER JOIN order_slave os on os.slave_id=odt.slave_id
INNER JOIN order_master om on os.order_id=om.order_id
INNER JOIN product_item pii on pii.item_id =odt.item_id
INNER JOIN vendor vds on vds.vendor_id=dm.export_id
INNER JOIN vendor vd on vd.vendor_id=odt.item_vendor_id
INNER JOIN product pt on pii.product_id=pt.product_id where odt.item_mode !=1 ");
str.AppendFormat(strcondition.ToString());
dt = _access.getDataTable(str.ToString());
return dt;
}
catch (Exception ex)
{
throw new Exception("DeliverMasterDao.GetDeliveryMsgExcelList-->" + ex.Message + str.ToString() + strcondition.ToString(), ex);
}
}
public List<DeliverMasterQuery> GetNewDeliver(DeliverMasterQuery store, out int totalCount)
{
StringBuilder sql = new StringBuilder();
StringBuilder sqlcount = new StringBuilder();
try
{
sql.AppendLine(@"SELECT dm.deliver_id,dm.order_id,dm.ticket_id, CASE dm.type WHEN '1' THEN '統倉出貨' ELSE '供應商自行出貨' END AS types,");
sql.AppendLine(@"dm.export_id,dm.import_id,dm.freight_set,dm.delivery_status,");
sql.AppendLine(@"dm.delivery_name,dm.delivery_mobile,dm.delivery_phone,dm.delivery_zip,");
sql.AppendLine(@"dm.delivery_address,dm.delivery_store,tp.parameterName AS stores ,dm.delivery_code,dm.delivery_freight_cost,");
sql.AppendLine(@"dm.delivery_date,dm.sms_date,dm.arrival_date,dm.estimated_delivery_date,ps.parameterName AS states,");
sql.AppendLine(@"dm.estimated_arrival_date,dm.estimated_arrival_period,para.parameterName as 'estimated_arrival_period_str',dm.creator,");
sql.AppendLine(@"dm.verifier,dm.created,dm.modified,dm.export_flag,dm.data_chg,");
sql.AppendLine(@"v.vendor_id,v.vendor_name_full,v.vendor_name_simple");
sql.AppendLine(@"FROM deliver_master dm ");
sql.AppendLine(@"LEFT JOIN order_master om ON om.order_id=dm.order_id");
sql.AppendLine(@"LEFT JOIN vendor v ON v.vendor_id=dm.export_id");
sql.AppendLine(@"LEFT JOIN (SELECT parameterCode,parameterName FROM t_parametersrc WHERE parameterType='Deliver_Store') tp ON tp.parameterCode=dm.delivery_store");
sql.AppendLine(@"LEFT JOIN (SELECT parameterCode,parameterName FROM t_parametersrc WHERE parameterType='delivery_status') ps ON ps.parameterCode=dm.delivery_status");
sql.AppendLine(@" LEFT JOIN (select parameterCode,parameterName from t_parametersrc where parameterType='Estimated_Arrival_Period') para on para.parameterCode=dm.estimated_arrival_period ");
sql.AppendFormat(@"WHERE om.order_id={0} ORDER BY dm.delivery_date ASC", store.order_id);
sqlcount.AppendFormat(@"SELECT count(*) as search_total FROM deliver_master dm LEFT JOIN order_master om ON om.order_id=dm.order_id LEFT JOIN vendor v ON v.vendor_id=dm.export_id WHERE om.order_id={0}", store.order_id);
totalCount = 0;
if (store.IsPage)
{
System.Data.DataTable _dt = _access.getDataTable(sqlcount.ToString());
if (_dt != null && _dt.Rows.Count > 0)
{
totalCount = Convert.ToInt32(_dt.Rows[0]["search_total"]);
}
sql.AppendFormat(" limit {0},{1}", store.Start, store.Limit);
}
return _access.getDataTableForObj<DeliverMasterQuery>(sql.ToString());
}
catch (Exception ex)
{
throw new Exception("TabShowDao-->GetNewDeliver " + ex.Message + sql.ToString(), ex);
}
}
public int GetDeliverMasterCount(DeliverMasterQuery query)
{
StringBuilder sbStr = new StringBuilder("SELECT count(dm.deliver_id) FROM deliver_master dm ");
sbStr.AppendFormat("WHERE dm.delivery_status IN(2,3) AND dm.export_id={0} AND dm.delivery_store={1} AND dm.delivery_date BETWEEN '{2}' AND '{3}';", query.export_id, query.delivery_store, Common.CommonFunction.DateTimeToString(query.time_start), Common.CommonFunction.DateTimeToString(query.time_end));
return int.Parse(_access.getDataTable(sbStr.ToString()).Rows[0][0].ToString());
}
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>
/// 獲得出貨單期望到貨日list
/// </summary>
/// <returns></returns>
public List<DeliverMasterQuery> GetDeliverExpectArriveList(DeliverMasterQuery Query, out int totalCount)
{
StringBuilder finalSql = new StringBuilder();
StringBuilder sbSql = new StringBuilder();
StringBuilder fromSql = new StringBuilder();
StringBuilder conSql = new StringBuilder();
Query.Replace4MySQL();
try
{
sbSql.Append(@"select dm.deliver_id,dm.order_id,om.user_id,tp.parameterName as delivery_status_str,dm.type,dm.freight_set,v.vendor_name_full,
dm.estimated_delivery_date,dm.deliver_org_days,dm.estimated_arrival_period,
dm.expect_arrive_date,dm.expect_arrive_period ");
fromSql.Append(@"from deliver_master dm inner JOIN vendor v on v.vendor_id=dm.export_id inner JOIN order_master om on om.order_id=dm.order_id
LEFT JOIN (SELECT * from t_parametersrc where parameterType ='delivery_status') tp on tp.parameterCode=dm.delivery_status
where 1=1 ");
if (Query.type != 10000 && Query.type != 1000)
{
if (Query.type == 101)
{
conSql.AppendFormat(" and dm.type='{0}'", 101);
}
else
{
conSql.AppendFormat(" and dm.type='{0}'", Query.type);
}
}
if (Query.freight_set != 10000 && Query.freight_set != 1000)
{
conSql.AppendFormat(" and dm.freight_set='{0}'", Query.freight_set);
}
if (Query.delivery_status != 10000)
{
conSql.AppendFormat(" and dm.delivery_status='{0}'", Query.delivery_status);
}
if (Query.deliver_id != 0)
{
conSql.AppendFormat(" and dm.deliver_id='{0}'", Query.deliver_id);
}
if (Query.order_id != 0)
{
conSql.AppendFormat(" and dm.order_id='{0}'", Query.order_id);
}
if (Query.time_start != DateTime.MinValue && Query.time_end != DateTime.MinValue)
{
conSql.AppendFormat(" and dm.deliver_org_days between '{0}' and '{1}'",CommonFunction.GetPHPTime(Query.time_start.ToString("yyyy-MM-dd 00:00:00")), CommonFunction.GetPHPTime(Query.time_end.ToString("yyyy-MM-dd 23:59:59")));
}
//if (Query.time_end != DateTime.MinValue)
//{
// conSql.AppendFormat(" and dm.deliver_org_days <= '{0}'", Query.time_end.ToString("yyyy-MM-dd"));
// //BLL.gigade.Common.CommonFunction.DateTimeToString(Query.time_end)
//}
if (Query.vendor_id != 0)
{
conSql.AppendFormat(" and dm.export_id='{0}'",Query.vendor_id);
}
if (!string.IsNullOrEmpty(Query.vendor_name_full))
{
conSql.AppendFormat(" and v.vendor_name_full like '%{0}%'", Query.vendor_name_full);
}
finalSql.Append(sbSql.ToString() + fromSql.ToString() + conSql.ToString() + "order by deliver_id desc");
totalCount = 0;
if (Query.IsPage)
{
DataTable _dt = _access.getDataTable(" select dm.deliver_id " + fromSql.ToString() + conSql.ToString());
if (_dt.Rows.Count > 0)
{
totalCount = _dt.Rows.Count;
}
finalSql.AppendFormat(" limit {0},{1} ", Query.Start, Query.Limit);
}
return _access.getDataTableForObj<DeliverMasterQuery>(finalSql.ToString());
}
catch (Exception ex)
{
throw new Exception(" DeliverMasterDao-->GetDeliverExpectArriveList-->" + ex.Message + finalSql.ToString(), ex);
}
}
/// <summary>
/// 把查詢條件在這裡判斷並且返回查詢語句
/// </summary>
/// <param name="deliver"></param>
/// <returns></returns>
public string getSqlForDliverList(DeliverMasterQuery deliver)
{
StringBuilder sb = new StringBuilder();
try
{
#region 查詢條件
if (!string.IsNullOrEmpty(deliver.types))//出貨類別
{
sb.AppendFormat(" and dm.type='{0}' ", deliver.types);
}
if (!string.IsNullOrEmpty(deliver.status))//出貨狀態
{
sb.AppendFormat(" and dm.delivery_status='{0}' ", deliver.status);
}
if (deliver.vendor_id != 0) //出貨方式
{
sb.AppendFormat(" and v.vendor_id='{0}' ", deliver.vendor_id);
}
if (deliver.delivery_store != 0)//物流商
{
sb.AppendFormat(" and dm.delivery_store='{0}' ", deliver.delivery_store);
}
if (deliver.warehouse_status != -1) //調度狀態
{
sb.AppendFormat(" and t.warehouse_status='{0}' ", deliver.warehouse_status);
}
if (deliver.priority != -1)//出貨篩選
{
sb.AppendFormat(" and om.priority='{0}' ", deliver.priority);
}
if (deliver.time_start != DateTime.MinValue)//出貨日期
{
sb.AppendFormat(" and dm.delivery_date > '{0}' ", deliver.time_start.ToString("yyyy-MM-dd 23:59:59"));
}
if (deliver.time_end != DateTime.MinValue)//出貨日期
{
sb.AppendFormat(" and dm.delivery_date < '{0}' ", deliver.time_end.ToString("yyyy-MM-dd 23:59:59"));
}
if (!string.IsNullOrEmpty(deliver.vendor_name_simple))//搜索
{
sb.AppendFormat(" and (dm.deliver_id='{0}' or dm.delivery_code='{1}' ", deliver.vendor_name_simple, deliver.vendor_name_simple, deliver.vendor_name_simple);
sb.AppendFormat(" or dm.order_id='{0}' or dm.delivery_name like'%{1}%' ", deliver.vendor_name_simple, deliver.vendor_name_simple);
sb.AppendFormat(" or dm.delivery_mobile='{0}' or v.vendor_name_simple like '%{0}%') ", deliver.vendor_name_simple, deliver.vendor_name_simple);
}
#endregion
}
catch (Exception ex)
{
throw new Exception(" DeliverMasterDao-->getSqlForDliverList-->" + ex.Message + sb.ToString(), ex);
}
return sb.ToString();
}
public HttpResponseBase GetNewDeliver()
{
List<DeliverMasterQuery> stores = new List<DeliverMasterQuery>();
string json = string.Empty;
try
{
DeliverMasterQuery query = new DeliverMasterQuery();
query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量
query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "20");//用於分頁的變量
query.order_id = Convert.ToInt32(Request.Params["Order_Id"].ToString());
_tabshow = new TabShowMgr(mySqlConnectionString);
int totalCount = 0;
stores = _tabshow.GetNewDeliver(query, out totalCount);
foreach (var item in stores)
{
if (item.delivery_date.ToString("yyyy-MM-dd HH:mm:ss") == "0001-01-01 00:00:00")
{
item.delivery_date_str = "";
}
else
{
item.delivery_date_str = item.delivery_date.ToString("yyyy-MM-dd HH:mm:ss");
}
}
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;
}
public DataTable GetMessageByDeliveryCode(DeliverMasterQuery dmQuery)
{
StringBuilder sbSql = new StringBuilder();
try
{
sbSql.AppendFormat(@"SELECT dm.deliver_id, dm.delivery_status, dm.freight_set, dm.ticket_id, dm.export_id,
dm.delivery_store, dm.delivery_code, dm.delivery_date,tp.parameterName
, dm.estimated_arrival_period,
dm.delivery_name, dm.order_id, vd.vendor_name_simple,om.order_amount,om.order_status FROM deliver_master dm
LEFT JOIN order_master om on dm.order_id=om.order_id
LEFT JOIN vendor vd on dm.export_id=vd.vendor_id
LEFT JOIN (SELECT parameterName,parameterCode FROM t_parametersrc WHERE parameterType='Deliver_Store' ) as tp on tp.parameterCode=dm.delivery_store
WHERE dm.delivery_code='{0}'", dmQuery.delivery_code);
return _access.getDataTable(sbSql.ToString());
}
catch (Exception ex)
{
throw new Exception(" DeliverMasterDao-->GetMessageByDeliveryCode-->" + ex.Message + sbSql.ToString(), ex);
}
}
public void GetDeliversExcelList()
{
string json = string.Empty;
DataTable _dt = new DataTable();
DataTable dtHZ = new DataTable();
DeliverMasterQuery dmQuery = new DeliverMasterQuery();
try
{
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));
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));
if (!string.IsNullOrEmpty(Request.Params["order_id"]))
{
dmQuery.sorder_id = Request.Params["order_id"];
}
if (!string.IsNullOrEmpty(Request.Params["deliver_id"]))
{
dmQuery.sdeliver_id = Request.Params["deliver_id"];
}
if (!string.IsNullOrEmpty(Request.Params["logistics_type"]))
{
dmQuery.logisticsType = Convert.ToInt32(Request.Params["logistics_type"]);
}
if (!string.IsNullOrEmpty(Request.Params["delivery_status"]))
{
dmQuery.ideliver_status = Convert.ToInt32(Request.Params["delivery_status"]);
}
if (!string.IsNullOrEmpty(Request.Params["product_mode"]))
{
dmQuery.product_mode = Convert.ToInt32(Request.Params["product_mode"]);
}
if (!string.IsNullOrEmpty(Request.Params["serch_msg"]) && Request.Params["serch_msg"] != "null")
{
dmQuery.serch_msg = Convert.ToInt32(Request.Params["serch_msg"]);
}
if (!string.IsNullOrEmpty(Request.Params["serch_where"].Trim()))
{
dmQuery.serch_where = Request.Params["serch_where"].Trim();
}
if (!string.IsNullOrEmpty(Request.Params["order_day"]) && Request.Params["order_day"] != "null")
{
dmQuery.order_day = int.Parse(Request.Params["order_day"]);
}
if (!string.IsNullOrEmpty(Request.Params["t_days"]) && Request.Params["t_days"] != "null")
{
dmQuery.t_days = Convert.ToInt32(Request.Params["t_days"]);
}
else
{
dmQuery.t_days = -1;
}
dmQuery.time_type= Convert.ToInt32(Request.Params["serch_time"]);
dmQuery.deliver_store = Convert.ToInt32(Request.Params["shipment_id"]);
dmQuery.i_order_status = Convert.ToInt32(Request.Params["order_status_id"]);
dmQuery.i_slave_status = Convert.ToInt32(Request.Params["slave_status_id"]);
dmQuery.i_detail_status = Convert.ToInt32(Request.Params["detail_status_id"]);
dmQuery.payment = Convert.ToInt32(Request.Params["payment_id"]);
dmQuery.order_time_begin = Convert.ToDateTime(Convert.ToDateTime(Request.Params["dateStart"]).ToString("yyyy-MM-dd 00:00:00"));//建立時間
dmQuery.order_time_end = Convert.ToDateTime(Convert.ToDateTime(Request.Params["dateEnd"]).ToString("yyyy-MM-dd 23:59:59"));
_dt = _delverMgr.GetDeliveryMsgExcelList(dmQuery);
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[0] = _dt.Rows[i]["overdue_day"];
dr[1] = _dt.Rows[i]["order_date"];
dr[2] = _dt.Rows[i]["order_id"];
dr[3] = _dt.Rows[i]["dvendor_name_simple"];
dr[4] = _dt.Rows[i]["vendor_name_simple"];
dr[5] = _dt.Rows[i]["freight_set"];
dr[6] = _dt.Rows[i]["delivery_name"];
dr[7] = _dt.Rows[i]["item_id"];
dr[8] = _dt.Rows[i]["product_name"];
dr[9] = _dt.Rows[i]["product_mode"];
dr[10] = _dt.Rows[i]["buy_num"];
dr[11] = _dt.Rows[i]["order_payment"];
dr[12] = _dt.Rows[i]["order_status"];
dr[13] = _dt.Rows[i]["slave_status"];
dr[14] = _dt.Rows[i]["detail_status"];
dr[15] = _dt.Rows[i]["note_order"];
dr[16] = _dt.Rows[i]["note_admin"];
dr[17] = _dt.Rows[i]["delivery_status"];
dr[18] = _dt.Rows[i]["logisticsTypes"];
dr[19] = _dt.Rows[i]["deliver_master_date"];
if (Convert.ToInt32(_dt.Rows[i]["estimated_arrival_period"]) == 0)
{
dr[20] = _dt.Rows[i]["estimated_arrival_period"];
}
else if (Convert.ToInt32(_dt.Rows[i]["estimated_arrival_period"]) == 1)
{
dr[20] = "12:00以前";
}
else if (Convert.ToInt32(_dt.Rows[i]["estimated_arrival_period"]) == 2)
{
dr[20] = "12:00-17:00";
}
else if (Convert.ToInt32(_dt.Rows[i]["estimated_arrival_period"]) == 3)
{
dr[20] = "17:00-20:00";
}
else
{
dr[20] = Convert.ToInt32(_dt.Rows[i]["estimated_arrival_period"]);
}
dr[21] = _dt.Rows[i]["estimated_delivery_date"];
dr[22] = _dt.Rows[i]["estimated_arrival_date"];
dr[23] = _dt.Rows[i]["delivery_date"];
dr[24] = _dt.Rows[i]["arrival_date"];
dr[25] = _dt.Rows[i]["delivery_code"];
dr[26] = _dt.Rows[i]["delivery_store"];
dtHZ.Rows.Add(dr);
}
if (dtHZ.Rows.Count > 0)
{
string fileName = DateTime.Now.ToString("訂單細項報表_yyyyMMddHHmmss") + ".xls";
MemoryStream ms = ExcelHelperXhf.ExportDT(dtHZ, "訂單細項報表_" + DateTime.Now.ToString("yyyyMMddHHmmss"));
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:[]}";
}
}
public DataTable GetReportManagementList(DeliverMasterQuery deliver, out int totalCount)
{
DataTable dt = new DataTable();
StringBuilder str = new StringBuilder();
StringBuilder strCount = new StringBuilder();
StringBuilder strcondition = new StringBuilder();
try
{
strcondition.AppendFormat(" and om.order_createdate >='{0}' and om.order_createdate <='{1}' ", Common.CommonFunction.GetPHPTime(deliver.order_time_begin.ToString()), Common.CommonFunction.GetPHPTime(deliver.order_time_end.ToString()));
if (!string.IsNullOrEmpty(deliver.sorder_id))
{
strcondition.AppendFormat(" and om.order_id like '%{0}%' ", deliver.sorder_id);
}
if (!string.IsNullOrEmpty(deliver.sdeliver_id))
{
strcondition.AppendFormat(" and dm.deliver_id like '%{0}%' ", deliver.sdeliver_id);
}
if (deliver.deliver_store != 0)
{
strcondition.AppendFormat(" and dm.delivery_store='{0}' ", deliver.deliver_store);
}
if (deliver.i_order_status != -1)
{
strcondition.AppendFormat(" and om.order_status='{0}' ", deliver.i_order_status);
}
if (deliver.payment != 0)
{
strcondition.AppendFormat(" and om.order_payment='{0}' ", deliver.payment);
}
if (deliver.logisticsType != 0)
{
strcondition.AppendFormat(" and ld.logisticsTypes='{0}' ", deliver.logisticsType);
}
if (deliver.ideliver_status!= -1)
{
strcondition.AppendFormat(" and dm.delivery_status='{0}' ", deliver.ideliver_status );
}
str.AppendFormat(@"SELECT dm.delivery_code,dm.deliver_id,om.order_id,DATE(FROM_UNIXTIME(om.order_createdate)) as order_date,om.order_status,om.order_payment,dm.delivery_store,ld.logisticsTypes,dm.delivery_status
FROM deliver_master dm
LEFT JOIN order_master om on om.order_id=dm.order_id
LEFT JOIN (SELECT max(logisticsType) as logisticsTypes, deliver_id fROM logistics_detail GROUP BY deliver_id ) as ld
on ld.deliver_id=dm.deliver_id where 1=1 ");
strCount.AppendFormat(@"SELECT count(dm.deliver_id) as 'count' FROM deliver_master dm
LEFT JOIN order_master om on om.order_id=dm.order_id
LEFT JOIN (SELECT max(logisticsType) as logisticsTypes, deliver_id fROM logistics_detail GROUP BY deliver_id ) as ld on ld.deliver_id=dm.deliver_id
where 1=1 ");
str.AppendFormat(strcondition.ToString());
strCount.Append(strcondition);
totalCount = 0;
if (deliver.IsPage)
{
System.Data.DataTable _dt = _access.getDataTable(strCount.ToString());
int.TryParse(_dt.Rows[0]["count"].ToString(), out totalCount);
str.AppendFormat(" limit {0},{1}", deliver.Start, deliver.Limit);
}
dt = _access.getDataTable(str.ToString());
return dt;
}
catch (Exception ex)
{
throw new Exception("DeliverMasterDao.GetReportManagementList-->" + ex.Message + str.ToString() + strcondition.ToString(), ex);
}
}
public void ReportManagementExcelList()
{
string json = string.Empty;
DataTable _dt = new DataTable();
DataTable dtHZ = new DataTable();
DeliverMasterQuery dmQuery = new DeliverMasterQuery();
try
{
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));
if (!string.IsNullOrEmpty(Request.Params["order_id"]))
{
dmQuery.sorder_id = Request.Params["order_id"];
}
if (!string.IsNullOrEmpty(Request.Params["deliver_id"]))
{
dmQuery.sdeliver_id = Request.Params["deliver_id"];
}
if (!string.IsNullOrEmpty(Request.Params["delivery_status"]))
{
dmQuery.ideliver_status = Convert.ToInt32(Request.Params["delivery_status"]);
}
dmQuery.deliver_store = Convert.ToInt32(Request.QueryString["shipment_id"]);
dmQuery.i_order_status = Convert.ToInt32(Request.QueryString["order_status_id"]);
dmQuery.payment = Convert.ToInt32(Request.QueryString["payment_id"]);
dmQuery.order_time_begin = Convert.ToDateTime(Convert.ToDateTime(Request.Params["dateStart"]).ToString("yyyy-MM-dd 00:00:00"));//建立時間
dmQuery.order_time_end = Convert.ToDateTime(Convert.ToDateTime(Request.Params["dateEnd"]).ToString("yyyy-MM-dd 23:59:59"));
if (!string.IsNullOrEmpty(Request.Params["logistics_type"]))
{
dmQuery.logisticsType = Convert.ToInt32(Request.Params["logistics_type"]);
}
_dt = _delverMgr.ReportManagementExcelList(dmQuery);
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[0] = _dt.Rows[i]["deliver_id"];
dr[1] = _dt.Rows[i]["order_id"];
dr[2] = _dt.Rows[i]["delivery_code"];
dr[3] = _dt.Rows[i]["order_payment"];
dr[4] = _dt.Rows[i]["delivery_store"];
dr[5] = _dt.Rows[i]["order_status"];
dr[6] = _dt.Rows[i]["delivery_status"];
dr[7] = _dt.Rows[i]["logisticsTypes"];
dr[8] = Convert.ToDateTime(_dt.Rows[i]["order_date"]).ToString("yyyy-MM-dd HH:mm:ss");
dtHZ.Rows.Add(dr);
}
if (dtHZ.Rows.Count > 0)
{
string fileName = DateTime.Now.ToString("訂單物流狀態報表_yyyyMMddHHmmss") + ".xls";
MemoryStream ms = ExcelHelperXhf.ExportDT(dtHZ, "訂單物流狀態報表_" + DateTime.Now.ToString("yyyyMMddHHmmss"));
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:[]}";
}
}
public DataTable ReportManagementExcelList(DeliverMasterQuery deliver)
{
StringBuilder strcondition = new StringBuilder();
StringBuilder str = new StringBuilder();
DataTable dt = new DataTable();
DataTable dt_type = new DataTable();
try
{
strcondition.AppendFormat(" and om.order_createdate >='{0}' and om.order_createdate <='{1}' ", Common.CommonFunction.GetPHPTime(deliver.order_time_begin.ToString()), Common.CommonFunction.GetPHPTime(deliver.order_time_end.ToString()));
if (!string.IsNullOrEmpty(deliver.sorder_id))
{
strcondition.AppendFormat(" and om.order_id like '%{0}%' ", deliver.sorder_id);
}
if (!string.IsNullOrEmpty(deliver.sdeliver_id))
{
strcondition.AppendFormat(" and dm.deliver_id like '%{0}%' ", deliver.sdeliver_id);
}
if (deliver.deliver_store != 0)
{
strcondition.AppendFormat(" and dm.delivery_store='{0}' ", deliver.deliver_store);
}
if (deliver.i_order_status != -1)
{
strcondition.AppendFormat(" and om.order_status='{0}' ", deliver.i_order_status);
}
if (deliver.payment != 0)
{
strcondition.AppendFormat(" and om.order_payment='{0}' ", deliver.payment);
}
if (deliver.logisticsType != 0)
{
strcondition.AppendFormat(" and ld.logisticsTypes='{0}' ", deliver.logisticsType);
}
if (deliver.ideliver_status != -1)
{
strcondition.AppendFormat(" and dm.delivery_status='{0}' ", deliver.ideliver_status);
}
str.AppendFormat(@"SELECT dm.delivery_code,dm.deliver_id,om.order_id,DATE(FROM_UNIXTIME(om.order_createdate)) as order_date,om.order_status,om.order_payment,dm.delivery_store,ld.logisticsTypes,dm.delivery_status
FROM deliver_master dm
LEFT JOIN order_master om on om.order_id=dm.order_id
LEFT JOIN (SELECT max(logisticsType) as logisticsTypes, deliver_id fROM logistics_detail GROUP BY deliver_id ) as ld
on ld.deliver_id=dm.deliver_id where 1=1 ");
str.AppendFormat(strcondition.ToString());
dt = _access.getDataTable(str.ToString());
return dt;
}
catch (Exception ex)
{
throw new Exception("DeliverMasterDao.ReportManagementExcelList-->" + ex.Message + str.ToString() + strcondition.ToString(), ex);
}
}
