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