/// <summary> /// 供應商後台:訂單管理>供應商調度出貨>批次檢貨明細列印 /// </summary> /// <returns></returns> public HttpResponseBase PickingPrintList() { BLL.gigade.Model.Vendor vendor = Session["vendor"] as BLL.gigade.Model.Vendor; uint vendor_id = vendor.vendor_id; string json = String.Empty; StringBuilder sb = new StringBuilder(); List<OrderDetailQuery> store = new List<OrderDetailQuery>(); OrderDetailQuery query = new OrderDetailQuery(); try { if (!string.IsNullOrEmpty(Request.Params["rowIDs"])) { string detail_id = Request.Params["rowIDs"]; detail_id = detail_id.TrimEnd(','); sb.AppendFormat(" AND os.slave_id in ({0})", detail_id); } query.Vendor_Id = vendor_id; query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量 _OrderDetailMgr = new OrderDetailMgr(mySqlConnectionString); int totalCount = 0; store = _OrderDetailMgr.VendorPickPrint(query, out totalCount, sb.ToString());//查询出供應商出貨單 #region 為了監視數據,方便後期維護 //DataTable dtH = new DataTable(); //dtH.Columns.Add("item_id", typeof(String)); //dtH.Columns.Add("product_name", typeof(string)); //dtH.Columns.Add("order_id", typeof(String)); //dtH.Columns.Add("order_name", typeof(String)); //dtH.Columns.Add("delivery_name", typeof(String)); //dtH.Columns.Add("buy_num", typeof(String)); //dtH.Columns.Add("single_price", typeof(String)); //dtH.Columns.Add("item_code", typeof(String)); //dtH.Columns.Add("note_order", typeof(String)); //foreach (var item in store) //{ // DataRow dr = dtH.NewRow(); // dr[0] = item.Item_Id; // dr[1] = item.Product_Name; // dr[2] = item.Order_Id; // dr[3] = item.Order_Name; // dr[4] =item.Delivery_Name; // dr[5] = item.Buy_Num; // dr[6] =item.Single_Price; // dr[7] = item.Item_Code; // dr[8] =item.Note_Order; // dtH.Rows.Add(dr); //} #endregion Dictionary<string, List<OrderDetailQuery>> MergeDate = new Dictionary<string, List<OrderDetailQuery>>();//在這裡合併數據 //(合併數據,計算這些數據參照自vendor.gigade100.com/order/all_order_deliver_detail_print.php 第59~108行) #region 測試一次 foreach (var item in store)//原數據循環 { if (item.Combined_Mode >= 1 && item.item_mode == 1)//提取组合商品,计算价格和数量 continue; string item_id = item.Item_Id.ToString(); if (item.item_mode == 2)//子商品 { item.Buy_Num = item.Buy_Num * item.parent_num; item.Single_Price = item.Single_Price * item.parent_num; } else //母商品 { item.Single_Price = item.Single_Price * item.Buy_Num; } if (!MergeDate.Keys.Contains(item_id))//不存在商品編號的添加 { List<OrderDetailQuery> s = new List<OrderDetailQuery>(); MergeDate.Add(item_id, s); } MergeDate[item_id].Add(item);//通過商品編號把相同商品房在同一個集合里 } StringBuilder html_table = new StringBuilder();//匯出html頁面 html_table.AppendFormat(@"<div> <table style='border:1px solid; text-align:center'>");//550//滚动条overflow:auto; html_table.AppendFormat("<tr style='background-color:Lime'><td style='height:50px;border:1px solid;'>商品編號</td><td style='border:1px solid; '>商品名稱</td><td style='border:1px solid;'>數量</td>"); html_table.AppendFormat("<td style='border:1px solid;'>廠商自訂編號</td><td style='border:1px solid;'>付款單編號</td><td style='border:1px solid;'>訂購人</td>"); html_table.AppendFormat(" <td style='border:1px solid;'>收件人</td><td style='border:1px solid;'>數量</td><td style='border:1px solid;'>總價</td><td style='border:1px solid;'>備註</td></tr>"); // int subtotal = 1; foreach (var item in MergeDate.Keys)//這個循環商品編號,然後再循環同一種編號下面的集合 { uint SumNo = 0;//計算總數量 foreach (var items in MergeDate[item])//循環同一種商品編號下面的集合 { SumNo += items.Buy_Num; } int r = 0; foreach (var items in MergeDate[item])//循環同一種商品編號下面的集合 { int row = MergeDate[item].Count;//合併的列 //if (subtotal % 28 == 0) //{ // html_table.AppendFormat("<tr style='page-break-after:always;'>");// //} //else //{ html_table.AppendFormat("<tr>"); //} if (r == 0)//合併數據 { html_table.AppendFormat("<td rowspan='{0}' style='border:1px solid;'>{1}</td>", row, items.Item_Id);//商品編號 items.Product_Name = items.Product_Name.Replace("\n", ""); html_table.AppendFormat("<td rowspan='{0}' style='border:1px solid;'>{1}</td>", row, items.Product_Name);//商品名稱 html_table.AppendFormat("<td rowspan='{0}' style='border:1px solid;'>{1}</td>", row, SumNo);//數量 html_table.AppendFormat("<td rowspan='{0}' style='border:1px solid;'>{1}</td>", row, items.Item_Code);//廠商自訂編號 } html_table.AppendFormat("<td style='border:1px solid;'>{0}</td>", items.Order_Id);//付款單編號 html_table.AppendFormat("<td style='border:1px solid;'>{0}</td>", items.Order_Name);//訂購人 html_table.AppendFormat("<td style='border:1px solid;'>{0}</td>", items.Delivery_Name);//收件人 html_table.AppendFormat("<td style='border:1px solid;'>{0}</td>", items.Buy_Num);//數量 html_table.AppendFormat("<td style='border:1px solid;'>{0}</td>", items.Single_Price);//總價 items.Note_Order = items.Note_Order.Replace("\n", ""); html_table.AppendFormat("<td style='border:1px solid;'>{0}</td>", items.Note_Order);//備註 //subtotal += 1; html_table.AppendFormat("</tr>"); r++; } } #endregion html_table.AppendFormat("</table></div>"); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd"; string n = html_table.ToString(); json = "{success:true,msg:\"" + html_table.ToString() + "\"}"; } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); json = "{success:false,msg:0}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }
public HttpResponseBase GetNewOrderRevenueList() { string json = string.Empty; try { OrderDetailQuery query = new OrderDetailQuery(); #region 前置查詢條件 if (!string.IsNullOrEmpty(Request.Params["Brand_Id"]))/*品牌編號*/ { query.Brand_Id_In = Request.Params["Brand_Id"]; } if (!string.IsNullOrEmpty(Request.Params["product_manage"]))//管理人員 {//獲取管理人 query.product_manage = int.Parse(Request.Params["product_manage"]); } if (!string.IsNullOrEmpty(Request.Params["dateOne"])) { query.time_start = CommonFunction.GetPHPTime(DateTime.Parse(Request.Params["dateOne"]).ToString("yyyy/MM/dd 00:00:00")); } if (!string.IsNullOrEmpty(Request.Params["dateTwo"])) { query.time_end = CommonFunction.GetPHPTime(DateTime.Parse(Request.Params["dateTwo"]).ToString("yyyy/MM/dd 23:59:59")); } long start = query.time_start;//開始時間 long end = query.time_end;//結束時間 if (!string.IsNullOrEmpty(Request.Params["Channel_Id"]))//賣場 { query.channel = int.Parse(Request.Params["Channel_Id"]); } if (!string.IsNullOrEmpty(Request.Params["slave_status"]))//訂單狀態 { query.Status = int.Parse(Request.Params["slave_status"]); } if (!string.IsNullOrEmpty(Request.Params["order_payment"]))//付款方式 { query.order_payment = int.Parse(Request.Params["order_payment"]); } if (!string.IsNullOrEmpty(Request.Params["selecttype"]))//關鍵字查詢類型 { query.select_type = int.Parse(Request.Params["selecttype"]); if (!string.IsNullOrEmpty(Request.Params["searchcon"]))//關鍵字查詢內容 { query.select_con = Request.Params["searchcon"]; } } #endregion VendorBrandQuery Vendorbrandquery = new VendorBrandQuery(); Dictionary<uint, VendorBrandQuery> brands = new Dictionary<uint, VendorBrandQuery>();///字典----儲存供應商編號,供應商名稱和錧別,錧別編號的信息 Dictionary<uint, Dictionary<string, Dictionary<string, uint>>> brandDailyTotal = new Dictionary<uint, Dictionary<string, Dictionary<string, uint>>>();///字典,儲存錧別,每天的計算和每天的購物金 ///////////////brand_id//////////daysum,dayduct/////日期///值 List<VendorBrandQuery> aDB_Brand_Select = new List<VendorBrandQuery>(); _vbrand = new VendorBrandMgr(mySqlConnectionString); aDB_Brand_Select = _vbrand.GetBandList(Vendorbrandquery);//把錧別和供應商信息保存到字典里 for (int i = 0; i < aDB_Brand_Select.Count; i++) { if (!brands.Keys.Contains(aDB_Brand_Select[i].Brand_Id)) { brands.Add(aDB_Brand_Select[i].Brand_Id, aDB_Brand_Select[i]); } } bool CrossMonth = CommonFunction.GetNetTime(start).Month == CommonFunction.GetNetTime(end).Month ? false : true; string timelong = ""; while (start <= end)//時間格式化,用來保存每天的小計 { timelong += CommonFunction.GetNetTime(start).ToString("yyyy/MM/dd") + ","; start += 86400; } timelong = timelong.Substring(0, timelong.LastIndexOf(",")); string[] times = timelong.Split(','); Dictionary<string, uint> timetro = new Dictionary<string, uint>(); for (int i = 0; i < times.Count(); i++)//字典加上日期時間 { timetro.Add(times[i], 0); } Dictionary<string, Dictionary<string, uint>> daysum_deduct = new Dictionary<string, Dictionary<string, uint>>();//保存每個商品每日的小計和購物金 daysum_deduct.Add("daysum", timetro);//每日小計 daysum_deduct.Add("daydeduct", timetro);//每日小計 _orderDetailMgr = new OrderDetailMgr(mySqlConnectionString); List<OrderDetailQuery> ordertailList = new List<OrderDetailQuery>(); ordertailList = _orderDetailMgr.GetOrderDetailList(query);//通過查詢條件獲取的數據 //if (ordertailList.Count == 0) //{ // json = "{success:true,msg:\"" + "<div style='overflow:auto;text-align:center;width:1650px;height:550px;><p tyle='text-align:center'>~~暫無數據~~</p></div>" + "\"}"; //} //else //{ string[] quanxuan = query.Brand_Id_In.Split(','); foreach (uint item in brands.Keys) { if (!brandDailyTotal.Keys.Contains(item)) { daysum_deduct = new Dictionary<string, Dictionary<string, uint>>(); timetro = new Dictionary<string, uint>(); for (int a = 0; a < times.Count(); a++)//字典加上日期時間 { timetro.Add(times[a], 0); } daysum_deduct.Add("daysum", timetro);//每日小計 timetro = new Dictionary<string, uint>(); for (int a = 0; a < times.Count(); a++)//字典加上日期時間 { timetro.Add(times[a], 0); } daysum_deduct.Add("daydeduct", timetro);//購物金 brandDailyTotal.Add(item, daysum_deduct); } } for (int i = 0; i < ordertailList.Count; i++) { ordertailList[i].subtotal = ordertailList[i].Single_Money * ordertailList[i].Buy_Num; ordertailList[i].cost = (ordertailList[i].Event_Cost != 0 && ordertailList[i].Single_Cost != ordertailList[i].Single_Money) ? ordertailList[i].Event_Cost : ordertailList[i].Single_Cost; if (!brands.Keys.Contains(ordertailList[i].Brand_Id)) { VendorBrandQuery brand = new VendorBrandQuery(); brand.Vendor_Id = ordertailList[i].Vendor_Id; brand.Brand_Name = ordertailList[i].Brand_Name; brand.Brand_Id = ordertailList[i].Brand_Id; brand.vendor_name_simple = ordertailList[i].Vendor_Name_Simple; brands.Add(ordertailList[i].Brand_Id, brand); } if (!brandDailyTotal.Keys.Contains(ordertailList[i].Brand_Id)) { daysum_deduct = new Dictionary<string, Dictionary<string, uint>>(); timetro = new Dictionary<string, uint>(); for (int a = 0; a < times.Count(); a++)//字典加上日期時間 { timetro.Add(times[a], 0); } daysum_deduct.Add("daysum", timetro);//每日小計 timetro = new Dictionary<string, uint>(); for (int a = 0; a < times.Count(); a++)//字典加上日期時間 { timetro.Add(times[a], 0); } daysum_deduct.Add("daydeduct", timetro);//購物金 brandDailyTotal.Add(ordertailList[i].Brand_Id, daysum_deduct); } string time = CommonFunction.GetNetTime(ordertailList[i].Order_Createdate).ToString("yyyy/MM/dd"); brandDailyTotal[ordertailList[i].Brand_Id]["daysum"][time] += ordertailList[i].subtotal;//每個商品的小計 brandDailyTotal[ordertailList[i].Brand_Id]["daydeduct"][time] += ordertailList[i].Deduct_Bonus;//每個商品的購物金小計 } Dictionary<string, uint> daysum_allbrand = new Dictionary<string, uint>();//所有品牌的每日小計 Dictionary<string, uint> deductsum_allbrand = new Dictionary<string, uint>();//所有品牌的每日購物金 foreach (uint key in brandDailyTotal.Keys) { foreach (string time in brandDailyTotal[key]["daysum"].Keys)//循環每個品牌的每日小計,計算到所有品牌的每日小計 { if (!daysum_allbrand.Keys.Contains(time)) { daysum_allbrand.Add(time, brandDailyTotal[key]["daysum"][time]); } else { daysum_allbrand[time] += brandDailyTotal[key]["daysum"][time]; } } foreach (string time in brandDailyTotal[key]["daydeduct"].Keys)//循環每個品牌的每日購物金小計,計算到所有品牌的每日小計 { if (!deductsum_allbrand.Keys.Contains(time)) { deductsum_allbrand.Add(time, brandDailyTotal[key]["daydeduct"][time]); } else { deductsum_allbrand[time] += brandDailyTotal[key]["daydeduct"][time]; } } } StringBuilder html_table = new StringBuilder();//匯出html頁面 html_table.AppendFormat(@"<div style='overflow:auto;text-align:right;width:1590px;height:580px;'><table style='border:0px;'>"); html_table.AppendFormat("<thead style='text-align:center;border-bottom: 1px solid #ccc;color: #000;'><tr><td style='border-bottom: 1px solid #ccc;color: #000;line-height: 1.2em;white-space: nowrap;font-size: 12px; text-align: center; text-align: center;'> 品牌名稱 </td>"); html_table.AppendFormat("<td style='border-bottom: 1px solid #ccc;color: #000;line-height: 1.2em;white-space: nowrap;font-size: 12px; text-align: center; text-align: center;'> 供應商名稱 </td>"); string ym_last = ""; foreach (string it in timetro.Keys) { string ym = it.Substring(0, 7); if (string.IsNullOrEmpty(ym_last)) ym_last = ym; if (!ym_last.Equals(ym)) { html_table.AppendFormat("<td style= 'color: #c00;border-bottom: 1px solid #ccc;line-height: 1.2em;white-space: nowrap;font-size: 12px; text-align: center;'> {0} </td>", ym_last.Split('/')[1] + "月小計"); ym_last = ym; } html_table.AppendFormat("<td style='color: #000;border-bottom: 1px solid #ccc; text-align: center;'> {0} </td>", it); } if (CrossMonth) { html_table.AppendFormat("<td style= 'color: #c00;border-bottom: 1px solid #ccc;line-height: 1.2em;white-space: nowrap;font-size: 12px; text-align: center;'> {0} </td>", ym_last.Split('/')[1] + "月小計"); } html_table.AppendFormat("<td style= 'color: #c00;border-bottom: 1px solid #ccc;line-height: 1.2em;white-space: nowrap;font-size: 12px; text-align: center;'> 品牌總計 </td></tr>"); html_table.AppendFormat("<tr><td colspan='2' style= 'text-align: right;border-bottom: 1px solid #ccc;color: #000;'> 每日小計 </td>"); ym_last = ""; uint sum_monthly = 0; uint sum_year = 0; foreach (string it in daysum_allbrand.Keys) { string ym = it.Substring(0, 7); if (string.IsNullOrEmpty(ym_last)) ym_last = ym; if (!ym_last.Equals(ym)) { html_table.AppendFormat("<td style= 'border-bottom: 1px solid #ccc; text-align: right;color: #c00; '> {0} </td>", sum_monthly); ym_last = ym; sum_monthly = 0; } sum_monthly += daysum_allbrand[it]; sum_year += daysum_allbrand[it]; html_table.AppendFormat("<td style='text-align:right;border-bottom: 1px solid #ccc; '> {0} </td>", daysum_allbrand[it]); } if (CrossMonth) { html_table.AppendFormat("<td style= 'text-align:right;color: #c00;border-bottom: 1px solid #ccc; '> {0} </td>", sum_monthly); } html_table.AppendFormat("<td style= 'text-align:right;color: #c00;border-bottom: 1px solid #ccc; '> {0} </td></tr></thead>", sum_year); html_table.AppendFormat("<tbody>"); foreach (uint it in brandDailyTotal.Keys) { ym_last = ""; sum_monthly = 0; sum_year = 0; if (brands.Keys.Contains(it)) { html_table.AppendFormat("<tr><td style=' white-space: nowrap;text-align:left;line-height: 1.2em;font-size: 12px;'> {0} </td><td style='text-align:left; white-space: nowrap;line-height: 1.2em;font-size: 12px;'> {1} </td>", brands[it].Brand_Name, brands[it].vendor_name_simple); } else { html_table.AppendFormat("<tr><td> {0} </td><td > {1} </td>", " ", " "); } foreach (string ite in brandDailyTotal[it]["daysum"].Keys) { string ym = ite.Substring(0, 7); if (string.IsNullOrEmpty(ym_last)) ym_last = ym; if (!ym_last.Equals(ym)) { html_table.AppendFormat("<td style= 'text-align:right;color: #c00; white-space: nowrap;line-height: 1.2em;font-size: 12px;'> {0} </td>", sum_monthly); ym_last = ym; sum_monthly = 0; } sum_monthly += brandDailyTotal[it]["daysum"][ite]; sum_year += brandDailyTotal[it]["daysum"][ite]; html_table.AppendFormat("<td style='text-align:right;white-space: nowrap;line-height: 1.2em;font-size: 12px;'> {0} </td>", brandDailyTotal[it]["daysum"][ite]); } if (CrossMonth) { html_table.AppendFormat("<td style= 'text-align:right;color: #c00; white-space: nowrap;line-height: 1.2em;font-size: 12px;'> {0} </td>", sum_monthly); } html_table.AppendFormat("<td style= 'text-align:right;color: #c00; white-space: nowrap;line-height: 1.2em;font-size: 12px;'> {0} </td> </tr>", sum_year); } html_table.AppendFormat("</tbody></table>"); json = "{success:true,msg:\"" + html_table.ToString() + "\"}"; } // } 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; }
public HttpResponseBase GetOrderDetailList() { List<OrderDetailQuery> stores = new List<OrderDetailQuery>(); string json = string.Empty; try { OrderDetailQuery query = new OrderDetailQuery(); query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量 //付款單號 query.Order_Id = Convert.ToUInt32(Request.Params["OrderId"].ToString()); query.isChildItem = 0; _orderDetailMgr = new OrderDetailMgr(mySqlConnectionString); int totalCount = 0; stores = _orderDetailMgr.GetOrderDetailList(query, out totalCount); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; //listUser是准备转换的对象 //獲取供應商後臺登陸的vendorMd5加密 _vendorImp = new VendorMgr(mySqlConnectionString); foreach (OrderDetailQuery item in stores) { string str = _vendorImp.GetLoginId(Convert.ToInt32(item.Vendor_Id)); if (str != "") { HashEncrypt hmd5 = new HashEncrypt(); string mdlogin_id = hmd5.Md5Encrypt(str, "MD5"); item.VendorMd5 = hmd5.Md5Encrypt(mdlogin_id + str, "MD5"); } if (item.item_mode == 2) { item.subtotal = (item.Single_Money * item.parent_num)-uint.Parse(item.Deduct_Happygo_Money.ToString())-item.Deduct_Welfare-item.Deduct_Bonus; } else { item.subtotal = (item.Single_Money * item.Buy_Num) -uint.Parse( item.Deduct_Happygo_Money.ToString()) - item.Deduct_Welfare - item.Deduct_Bonus; } } json = "{success:true,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 List<OrderDetailQuery> GetOrderDetailList(OrderDetailQuery query) { try { return _orderDetailDao.GetOrderDetailList(query); } catch (Exception ex) { throw new Exception("OrderDetailMgr-->GetOrderDetailList-->" + ex.Message, ex); } }
public DataTable GetAmountDetial(OrderDetailQuery query,out int totalCount) { totalCount = 0; DataTable dt = new DataTable(); try { dt = _orderDetailDao.GetAmountDetial(query, out totalCount); if (dt != null && dt.Rows.Count > 0) { List<Parametersrc> parameterList = _parametersrcDao.SearchParameters("payment", "order_status", "product_mode"); foreach (DataRow dr in dt.Rows) { var alist = parameterList.Find(m => m.ParameterType == "payment" && m.ParameterCode == dr["order_payment"].ToString()); var blist = parameterList.Find(m => m.ParameterType == "order_status" && m.ParameterCode == dr["slave_status"].ToString()); if (alist != null) { dr["payment_name"] = alist.parameterName; } if (blist != null) { dr["slave_status_name"] = blist.remark; } int channel = dr["channel"].ToString() == "" ? 0 : Convert.ToInt32(dr["channel"].ToString()); Channel clist = new Channel(); if (channel == 0) { dr["channel_name_simple"] = ""; } else { clist = _channelDao.getSingleObj(channel); } if (clist != null) { dr["channel_name_simple"] = clist.channel_name_simple; } if (dr["order_createdate"] != null) { dr["order_createdate_format"] = CommonFunction.DateTimeToString(CommonFunction.GetNetTime(Convert.ToInt32(dr["order_createdate"].ToString()))); } if (dr["deduct_bonus"] != null) { dr["deducts"] = Convert.ToInt32(dr["deduct_bonus"].ToString()); } if (dr["money"] != null) { dr["amount"] = Convert.ToInt32(dr["money"].ToString()); } } } return dt; } catch (Exception ex) { throw new Exception("OrderMasterMgr-->GetAmountDetial-->" + ex.Message, ex); } }
/// <summary> /// 品牌營業額統計的數據 /// </summary> /// <param name="query"></param> /// <returns></returns> public List<OrderDetailQuery> GetOrderDetailList(OrderDetailQuery query) { StringBuilder sql = new StringBuilder(); StringBuilder sqlCondi = new StringBuilder(); try { sql.Append(" select od.single_money,od.buy_num,od.deduct_bonus,od.event_cost,od.single_cost, vb.brand_id,vb.brand_name,vb.vendor_id,v.vendor_name_simple,om.order_createdate "); sql.Append(" from order_detail od,order_slave os,vendor v,order_master om,product_item pi,product p,vendor_brand vb "); sql.Append(" where os.slave_id = od.slave_id AND os.order_id = om.order_id AND v.vendor_id = od.item_vendor_id AND pi.item_id = od.item_id AND p.product_id = pi.product_id AND vb.brand_id = p.brand_id AND item_mode in (0 , 1) "); if (!string.IsNullOrEmpty(query.Brand_Id_In)) { string[] quanxuan = query.Brand_Id_In.Split(','); if (!quanxuan.Contains("0")) { sqlCondi.AppendFormat(" and p.brand_id in ({0}) ", query.Brand_Id_In); } } if (query.product_manage != 0) { sqlCondi.AppendFormat(" AND v.product_manage='{0}' ", query.product_manage); } if (query.channel != 0) { sqlCondi.AppendFormat(" AND om.channel='{0}' ", query.channel); } switch (query.Status) { case -1: sqlCondi.AppendFormat(" and os.slave_status in(0,2,4,99,5,6)"); break; default: sqlCondi.AppendFormat(" and os.slave_status={0}", query.Status); break; } if (query.order_payment != 0) { sqlCondi.AppendFormat(" and om.order_payment={0}", query.order_payment); } if (!string.IsNullOrEmpty(query.select_con)) { switch (query.select_type) { case 1: sqlCondi.AppendFormat(" AND od.product_name LIKE '%{0}%'", query.select_con); break; case 2://會員編號 sqlCondi.AppendFormat(" AND om.user_id LIKE '%{0}%'", query.select_con); break; case 3: sqlCondi.AppendFormat(" om.order_name LIKE '%{0}%'", query.select_con); break; default: break; } } if (query.time_start > 0) { sqlCondi.AppendFormat(" and om.order_createdate >= '{0}' ", query.time_start); } if (query.time_end > 0) { sqlCondi.AppendFormat(" and om.order_createdate <= '{0}' ", query.time_end); } sql.Append(sqlCondi); sql.Append(" ORDER BY brand_sort, brand_name "); return _dbAccess.getDataTableForObj<OrderDetailQuery>(sql.ToString()); } catch (Exception ex) { throw new Exception("OrderDetailDao.GetOrderDetailList -->" + ex.Message + sql.ToString(), ex); } }
public List<OrderDetailQuery> DeliveryInformation(OrderDetailQuery query, string str) { try { return _orderDetailDao.DeliveryInformation(query, str); } catch (Exception ex) { throw new Exception("OrderDetailMgr-->DeliveryInformation" + ex.Message, ex); } }
/// <summary> /// 傳票明細,拆分細項。 /// chaojie1124j添加于 2014/12/17 06:10 PM /// </summary> /// <param name="query"></param> /// <returns></returns> public int split_detail(BLL.gigade.Model.Query.OrderDetailQuery query) {//www/Model/Deliver/第575行 split_detail StringBuilder sb = new StringBuilder(); MySqlCommand mySqlCmd = new MySqlCommand(); MySqlConnection mySqlConn = new MySqlConnection(connStr); try { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Closed) { mySqlConn.Open(); } mySqlCmd.Connection = mySqlConn; mySqlCmd.Transaction = mySqlConn.BeginTransaction(); mySqlCmd.CommandType = System.Data.CommandType.Text; OrderDetailQuery orderdetail = new OrderDetailQuery();// sb.Append(@"select detail_id,slave_id,item_id,item_vendor_id,product_freight_set,product_mode,product_name,product_spec_name,single_cost,"); sb.Append(" event_cost,single_price,single_money,deduct_bonus,deduct_welfare,deduct_happygo,deduct_happygo_money,deduct_account,"); sb.Append(" deduct_account_note,accumulated_bonus,accumulated_happygo,buy_num,detail_status,detail_note,item_code,arrival_status,delay_till,"); sb.Append(" lastmile_deliver_serial,lastmile_deliver_datetime,lastmile_deliver_agency,bag_check_money,channel_detail_id,combined_mode,"); sb.Append(" item_mode,parent_id,parent_name,parent_num,price_master_id,pack_id,sub_order_id,site_id,event_id,prepaid from order_detail"); sb.AppendFormat(" where detail_id='{0}';", query.Detail_Id); mySqlCmd.CommandText = sb.ToString(); orderdetail = _dbAccess.getSinggleObj<OrderDetailQuery>(sb.ToString()); sb.Clear(); uint Detail_Id = query.Detail_Id; if (orderdetail.Buy_Num < 2)//不可拆分 return 0; #region 拆分商品,并修改數據 uint buynum = orderdetail.Buy_Num; orderdetail.Buy_Num = buynum - 1; uint Deduct_Bonus = orderdetail.Deduct_Bonus; uint Deduct_Welfare = orderdetail.Deduct_Welfare; int Deduct_Happygo = orderdetail.Deduct_Happygo; int Accumulated_Bonus = orderdetail.Accumulated_Bonus; int Accumulated_Happygo = orderdetail.Accumulated_Happygo; orderdetail.Deduct_Bonus = uint.Parse(Math.Round((decimal)(orderdetail.Deduct_Bonus / orderdetail.Buy_Num * (buynum - 1)), 0, MidpointRounding.AwayFromZero).ToString()); orderdetail.Deduct_Welfare = uint.Parse(Math.Round((decimal)(orderdetail.Deduct_Welfare / orderdetail.Buy_Num * (buynum - 1)), 0, MidpointRounding.AwayFromZero).ToString()); orderdetail.Deduct_Happygo = int.Parse(Math.Round((decimal)(orderdetail.Deduct_Happygo / orderdetail.Buy_Num * (buynum - 1)), 0, MidpointRounding.AwayFromZero).ToString()); orderdetail.Accumulated_Bonus = int.Parse(Math.Round((decimal)(orderdetail.Accumulated_Bonus / orderdetail.Buy_Num * (buynum - 1)), 0, MidpointRounding.AwayFromZero).ToString()); orderdetail.Accumulated_Happygo = int.Parse(Math.Round((decimal)(orderdetail.Accumulated_Happygo / orderdetail.Buy_Num * (buynum - 1)), 0, MidpointRounding.AwayFromZero).ToString()); //保存 mySqlCmd.CommandText = UpOrderDetail(orderdetail);//601行 mySqlCmd.ExecuteNonQuery(); sb.Clear(); #endregion #region 拆分出來的,賦值并保存 sb.Append(" select serial_value from serial where serial_id=32;"); mySqlCmd.CommandText = sb.ToString(); orderdetail.Detail_Id = uint.Parse(mySqlCmd.ExecuteScalar().ToString()) + 1; orderdetail.Buy_Num = 1; orderdetail.Deduct_Bonus = Deduct_Bonus - orderdetail.Deduct_Bonus; orderdetail.Deduct_Welfare = Deduct_Welfare - orderdetail.Deduct_Welfare; orderdetail.Deduct_Happygo = Deduct_Happygo - orderdetail.Deduct_Happygo; orderdetail.Accumulated_Bonus = Accumulated_Bonus - orderdetail.Accumulated_Bonus; orderdetail.Accumulated_Happygo = Accumulated_Happygo - orderdetail.Accumulated_Happygo; mySqlCmd.CommandText = AddOrderDetail(orderdetail);//610行 mySqlCmd.ExecuteNonQuery(); sb.Clear(); #endregion ///INSERT INTO deliver_detail (deliver_id, detail_id) SELECT deliver_id, 612849 FROM deliver_detail AS `DeliverDetail` WHERE `detail_id` = 152551 /// #region 增加記錄deliver_detail sb.AppendFormat(@"INSERT INTO deliver_detail (deliver_id, detail_id,delivery_status) SELECT deliver_id, '{0}','{1}' FROM deliver_detail ", orderdetail.Detail_Id, 0); sb.AppendFormat(@" where detail_id='{0}';", Detail_Id); mySqlCmd.CommandText = sb.ToString(); mySqlCmd.ExecuteNonQuery(); sb.Clear(); #endregion mySqlCmd.Transaction.Commit(); return 1; } catch (Exception ex) { mySqlCmd.Transaction.Rollback(); throw new Exception("OrderDetailDao.no_delivery -->" + ex.Message + sb.ToString(), ex); } finally { if (mySqlConn != null && mySqlConn.State == System.Data.ConnectionState.Open) { mySqlConn.Close(); } } }
/// <summary> /// 返回新增的sql 语句 /// </summary> /// <param name="query"></param> /// <returns></returns> public string AddOrderDetail(OrderDetailQuery query) { query.Replace4MySQL(); StringBuilder strSql = new StringBuilder(@"insert into order_detail (detail_id,slave_id,item_id,item_vendor_id,product_freight_set,product_mode,product_name,product_spec_name,single_cost,"); strSql.Append("event_cost,single_price,single_money,deduct_bonus,deduct_welfare,deduct_happygo,deduct_happygo_money,deduct_account,deduct_account_note,"); strSql.Append("accumulated_bonus,accumulated_happygo,buy_num,detail_status,detail_note,item_code,arrival_status,delay_till,lastmile_deliver_serial,"); strSql.Append("lastmile_deliver_datetime,lastmile_deliver_agency,bag_check_money,channel_detail_id,combined_mode,item_mode,parent_id,parent_name,parent_num,"); strSql.Append("price_master_id,pack_id,sub_order_id,site_id,event_id,prepaid) values("); strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}',", query.Detail_Id, query.Slave_Id, query.Item_Id, query.Item_Vendor_Id, query.Product_Freight_Set); strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}',", query.Product_Mode, query.Product_Name, query.Product_Spec_Name, query.Single_Cost, query.Event_Cost); strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}',", query.Single_Price, query.Single_Money, query.Deduct_Bonus, query.Deduct_Welfare, query.Deduct_Happygo); strSql.AppendFormat("'{0}','{1}','{2}','{3}',", query.Deduct_Happygo_Money, query.Deduct_Account, query.Deduct_Account_Note, query.Accumulated_Bonus); strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}',", query.Accumulated_Happygo, query.Buy_Num, query.Detail_Status, query.Detail_Note, query.Item_Code); strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}',", query.Arrival_Status, query.Delay_Till, query.Lastmile_Deliver_Serial, query.Lastmile_Deliver_Datetime, query.Lastmile_Deliver_Agency); strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}',", query.Bag_Check_Money, query.Channel_Detail_Id, query.Combined_Mode, query.item_mode, query.Parent_Id); strSql.AppendFormat("'{0}','{1}','{2}','{3}','{4}',", query.parent_name, query.parent_num, query.price_master_id, query.pack_id, query.Sub_Order_Id); strSql.AppendFormat("'{0}','{1}','{2}');", query.Site_Id, query.event_id, query.Prepaid); return strSql.ToString(); }
//類別訂單明細匯出sql // public DataTable CagegoryDetialExport(OrderDetailQuery query) // { // StringBuilder sqlSingle = new StringBuilder(); // StringBuilder sql = new StringBuilder(); // StringBuilder sqlFather = new StringBuilder(); // StringBuilder sqlJoin1 = new StringBuilder(); // StringBuilder sqlJoin2 = new StringBuilder(); // StringBuilder sqlWhere = new StringBuilder(); // try // { // sql.Append(@"SELECT * from ( //SELECT order_id,SUM(money) as money,category_id,order_name,order_createdate,'' as order_createdate_format,order_payment,'' as payment_name,order_amount,order_status,'' as order_status_name, //item_id,slave_status,'' as slave_status_name,product_name,deduct_bonus,deduct_welfare,single_cost,bag_check_money,slave_date_close,'' as slave_date_close_format,'' as amount, //'' as cost_amount,product_mode,'' as product_mode_name,item_mode,delivery_name,delivery_address,detail_id,event_cost,product_id,deliver_id,delivery_code,delivery_store, //'' as deliver_name,buy_num,single_money from ("); // sqlSingle.AppendFormat(@"(SELECT om.order_id,pcs.category_id,om.order_name,od.buy_num,od.single_money,od.buy_num*od.single_money-od.deduct_bonus-od.deduct_welfare as money,om.order_createdate,om.order_payment, //om.order_amount,om.order_status,od.item_id,os.slave_status,od.product_name,od.deduct_bonus,od.deduct_welfare,od.single_cost,od.bag_check_money,os.slave_date_close, //od.product_mode,od.item_mode,om.delivery_name,om.delivery_address ,od.detail_id,od.event_cost,pi.product_id,dd.deliver_id,dm.delivery_code,dm.delivery_store FROM order_master om "); // sqlFather.AppendFormat(@"(SELECT om.order_id,pcs.category_id,om.order_name,od.buy_num,od.single_money,od.buy_num*od.single_money-od.deduct_bonus-od.deduct_welfare as money,om.order_createdate,om.order_payment, //om.order_amount,om.order_status,od.item_id,os.slave_status,od.product_name,od.deduct_bonus,od.deduct_welfare,od.single_cost,od.bag_check_money,os.slave_date_close, //od.product_mode,od.item_mode,om.delivery_name,om.delivery_address ,od.detail_id,od.event_cost,od.parent_id as product_id,dd.deliver_id,dm.delivery_code,dm.delivery_store FROM order_master om "); // sqlJoin1.AppendFormat(@" //LEFT JOIN order_slave os ON om.order_id=os.order_id //LEFT JOIN order_detail od ON os.slave_id=od.slave_id //INNER JOIN product_item pi ON od.item_id=pi.item_id //INNER JOIN product_category_set pcs ON pi.product_id=pcs.product_id //LEFT JOIN deliver_detail dd ON od.detail_id=dd.detail_id //LEFT JOIN deliver_master dm ON dd.deliver_id=dm.deliver_id //WHERE od.item_mode=0 AND pcs.category_id={0} //AND od.detail_status NOT IN(89,90,91) AND om.order_status NOT IN(90,91)", query.category_id); // sqlJoin2.AppendFormat(@" //LEFT JOIN order_slave os ON om.order_id=os.order_id //LEFT JOIN order_detail od ON os.slave_id=od.slave_id //INNER JOIN product_category_set pcs ON od.parent_id=pcs.product_id //LEFT JOIN deliver_detail dd ON od.detail_id=dd.detail_id //LEFT JOIN deliver_master dm ON dd.deliver_id=dm.deliver_id //WHERE od.item_mode=1 AND pcs.category_id={0} //AND od.detail_status NOT IN(89,90,91) AND om.order_status NOT IN(90,91)", query.category_id); // if (query.category_status != 0) // { // sqlWhere.AppendFormat(" AND om.money_collect_date > 0"); // } // if (query.date_stauts != 0) // { // if (query.date_start != DateTime.MinValue && query.date_end != DateTime.MinValue) // { // sqlWhere.AppendFormat(" AND om.order_createdate>='{0}' and om.order_createdate<='{1}'", CommonFunction.GetPHPTime(CommonFunction.DateTimeToString(query.date_start)), CommonFunction.GetPHPTime(CommonFunction.DateTimeToString(query.date_end))); // } // } // sqlWhere.AppendFormat(" GROUP BY od.detail_id "); // sql.Append(sqlSingle.ToString() + sqlJoin1.ToString() + sqlWhere.ToString() + ")UNION" + sqlFather.ToString() + sqlJoin2.ToString() + sqlWhere.ToString() + ") ) amount GROUP BY order_id) c_amount "); // if (query.c_money > 0 || query.c_money1 > 0) // { // sql.AppendFormat(" where money>={0} and money<={1} ", query.c_money, query.c_money1); // } // return _dbAccess.getDataTable(sql.ToString()); // } // catch (Exception ex) // { // throw new Exception("OrderMasterDao-->CategoryDetialExportInfo-->" + sqlSingle.ToString() + ex.Message, ex); // } // } public DataTable CagegoryDetialExport(OrderDetailQuery query) { StringBuilder sql = new StringBuilder(); StringBuilder sqlSingle = new StringBuilder(); StringBuilder sqlFather = new StringBuilder(); StringBuilder sqlJoin1 = new StringBuilder(); StringBuilder sqlJoin2 = new StringBuilder(); StringBuilder sqlWhere = new StringBuilder(); try { try { sql.Append(@" SELECT order_id,category_id,order_name,order_createdate,'' as order_createdate_format,order_payment,'' as payment_name,order_amount,order_status,'' as order_status_name, item_id,slave_status,'' as slave_status_name,product_name,deduct_bonus,deduct_welfare,single_cost,bag_check_money,slave_date_close,'' as slave_date_close_format,'' as amount, '' as cost_amount,product_mode,'' as product_mode_name,item_mode,delivery_name,delivery_address,detail_id,event_cost,product_id,deliver_id,delivery_code,delivery_store, '' as deliver_name,buy_num,single_money from ("); sqlSingle.Append(@" ( SELECT om.order_id,pcs.category_id,om.order_name,od.buy_num,od.single_money,om.order_createdate,om.order_payment, om.order_amount,om.order_status,od.item_id,os.slave_status,od.product_name,od.deduct_bonus,od.deduct_welfare,od.single_cost,od.bag_check_money,os.slave_date_close, od.product_mode,od.item_mode,om.delivery_name,om.delivery_address ,od.detail_id,od.event_cost,pi.product_id,dd.deliver_id,dm.delivery_code,dm.delivery_store FROM order_master om "); sqlFather.Append(@" ( SELECT om.order_id,pcs.category_id,om.order_name,od.buy_num,od.single_money,om.order_createdate,om.order_payment, om.order_amount,om.order_status,od.item_id,os.slave_status,od.product_name,od.deduct_bonus,od.deduct_welfare,od.single_cost,od.bag_check_money,os.slave_date_close, od.product_mode,od.item_mode,om.delivery_name,om.delivery_address ,od.detail_id,od.event_cost,od.parent_id as product_id,dd.deliver_id,dm.delivery_code,dm.delivery_store FROM order_master om"); sqlJoin1.AppendFormat(@" LEFT JOIN order_slave os ON om.order_id=os.order_id LEFT JOIN order_detail od ON os.slave_id=od.slave_id INNER JOIN product_item pi ON od.item_id=pi.item_id INNER JOIN product_category_set pcs ON pi.product_id=pcs.product_id LEFT JOIN deliver_detail dd ON od.detail_id=dd.detail_id LEFT JOIN deliver_master dm ON dd.deliver_id=dm.deliver_id WHERE od.item_mode=0 AND pcs.category_id='{0}' AND od.detail_status NOT IN(89,90,91) AND om.order_status NOT IN(90,91) AND om.order_id='{1}' )", query.category_id,query.Order_Id); sqlJoin2.AppendFormat(@" LEFT JOIN order_slave os ON om.order_id=os.order_id LEFT JOIN order_detail od ON os.slave_id=od.slave_id INNER JOIN product_category_set pcs ON od.parent_id=pcs.product_id LEFT JOIN deliver_detail dd ON od.detail_id=dd.detail_id LEFT JOIN deliver_master dm ON dd.deliver_id=dm.deliver_id WHERE od.item_mode=1 AND pcs.category_id='{0}' AND od.detail_status NOT IN(89,90,91) AND om.order_status NOT IN(90,91) AND om.order_id='{1}' )", query.category_id, query.Order_Id); sql.Append(sqlSingle.ToString() + sqlJoin1.ToString() + " UNION " + sqlFather.ToString() + sqlJoin2.ToString() + ") c_amount "); return _dbAccess.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("OrderMasterDao-->OrderDetialExportInfo-->" + sql.ToString() + ex.Message, ex); } } catch (Exception ex) { throw new Exception("OrderMasterDao-->CategoryDetialExportInfo-->" + sqlSingle.ToString() + ex.Message, ex); } }
public DataTable GetAmountDetial(OrderDetailQuery query ,out int totalCount) { StringBuilder sql = new StringBuilder(); StringBuilder sqlSingle = new StringBuilder(); StringBuilder sqlFather = new StringBuilder(); StringBuilder sqlSingleCount = new StringBuilder(); StringBuilder sqlFatherCount = new StringBuilder(); StringBuilder sqlWhere = new StringBuilder(); StringBuilder sqlCount = new StringBuilder(); totalCount = 0; DataTable dt = new DataTable(); try { sqlCount.AppendFormat("SELECT * from (SELECT lb.order_id,SUM(lb.money-lb.deduct_bonus-lb.deduct_welfare) as money from( "); sql.AppendFormat(@"SELECT * from (SELECT lb.order_id,order_name,SUM(money-lb.deduct_bonus-deduct_welfare) as money,SUM(lb.deduct_bonus+deduct_welfare) AS 'deduct_bonus',category_id,order_product_subtotal,order_amount,delivery_name,order_payment,'' as payment_name,slave_status,'' as slave_status_name,channel,'' as channel_name_simple,order_createdate,'' as order_createdate_format,'' as deducts,'' as amount from( "); sqlSingle.AppendFormat(@" (SELECT om.order_id,od.detail_id,om.order_name,od.single_money*buy_num as money,pcs.category_id ,om.order_payment, om.order_product_subtotal,om.order_amount,os.slave_status,om.channel,om.order_createdate, od.deduct_bonus,od.deduct_welfare,om.delivery_name FROM order_detail od INNER JOIN product_item pit USING(item_id) INNER JOIN order_slave os USING (slave_id) INNER JOIN order_master om USING (order_id) INNER JOIN product p USING (product_id) INNER JOIN product_category_set pcs USING(product_id) LEFT JOIN channel c ON om.channel=c.channel_id WHERE category_id={0} AND item_mode =0 AND od.detail_status NOT IN(89,90,91) AND om.order_status NOT IN(90,91)", query.category_id); sqlFather.AppendFormat(@"(SELECT om.order_id,od.detail_id,om.order_name,od.single_money*buy_num as money,pcs.category_id ,om.order_payment, om.order_product_subtotal,om.order_amount,os.slave_status,om.channel,om.order_createdate, od.deduct_bonus,od.deduct_welfare,om.delivery_name FROM order_detail od INNER JOIN order_slave os USING (slave_id) INNER JOIN order_master om USING (order_id) INNER JOIN product_category_set pcs ON od.parent_id=pcs.product_id LEFT JOIN channel c ON om.channel=c.channel_id WHERE category_id={0} AND item_mode =1 AND od.detail_status NOT IN(89,90,91) AND om.order_status NOT IN(90,91)", query.category_id); sqlSingleCount.AppendFormat(@" (SELECT om.order_id,om.order_name,od.detail_id,od.single_money*buy_num as money,od.deduct_bonus,od.deduct_welfare FROM order_detail od INNER JOIN product_item pit USING(item_id) INNER JOIN order_slave os USING (slave_id) INNER JOIN order_master om USING (order_id) INNER JOIN product p USING (product_id) INNER JOIN product_category_set pcs USING(product_id) WHERE category_id={0} AND item_mode =0 AND od.detail_status NOT IN(89,90,91) AND om.order_status NOT IN(90,91)", query.category_id); sqlFatherCount.AppendFormat(@"(SELECT om.order_id,om.order_name,od.detail_id,od.single_money*buy_num as money,od.deduct_bonus,od.deduct_welfare FROM order_detail od INNER JOIN order_slave os USING (slave_id) INNER JOIN order_master om USING (order_id) INNER JOIN product_category_set pcs ON od.parent_id=pcs.product_id WHERE category_id={0} AND item_mode =1 AND od.detail_status NOT IN(89,90,91) AND om.order_status NOT IN(90,91)", query.category_id); if (query.category_status != 0) { sqlWhere.AppendFormat(" AND om.money_collect_date > 0"); } if (query.date_stauts != 0) { if (query.date_start != DateTime.MinValue && query.date_end != DateTime.MinValue) { sqlWhere.AppendFormat(" AND om.order_createdate>='{0}' and om.order_createdate<='{1}'", CommonFunction.GetPHPTime(CommonFunction.DateTimeToString(query.date_start)), CommonFunction.GetPHPTime(CommonFunction.DateTimeToString(query.date_end))); } } string c_money = ""; if (query.c_money > 0 || query.c_money1 > 0) { c_money = " WHERE money>=" + query.c_money + " and money<=" + query.c_money1; } if (query.IsPage) { sqlCount.AppendFormat(sqlSingleCount.ToString() + sqlWhere.ToString() + ") UNION " + sqlFatherCount.ToString() + sqlWhere.ToString() + ") )lb "); sqlCount.AppendFormat(@" GROUP BY lb.order_id) amount {0} ", c_money); dt = _dbAccess.getDataTable(sqlCount.ToString()); if (dt != null && dt.Rows.Count > 0) { totalCount = dt.Rows.Count; } sql.AppendFormat(sqlSingle.ToString() + sqlWhere.ToString() + ") UNION " + sqlFather.ToString() + sqlWhere.ToString() + ") )lb "); sql.AppendFormat(@" GROUP BY lb.order_id ) amount {0} ", c_money); sql.AppendFormat(" limit {0},{1};", query.Start, query.Limit); } return _dbAccess.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("OrderDetailDao.GetAmountDetial -->" + ex.Message + sql.ToString(), ex); } }
/// <summary> /// 拆分細項 /// </summary> /// <returns></returns> public JsonResult split_detail()//Admin/Controller/DeliversController/第325行 split_detail { string json = string.Empty; OrderDetailQuery query = new OrderDetailQuery(); if (!string.IsNullOrEmpty(Request.Params["Detail_Id"])) { query.Detail_Id = uint.Parse(Request.Params["Detail_Id"]); } if (!string.IsNullOrEmpty(Request.Params["Deliver_Id"])) { query.Deliver_Id = uint.Parse(Request.Params["Deliver_Id"]); } _IOrderDetailMgr = new OrderDetailMgr(mySqlConnectionString); if (_IOrderDetailMgr.split_detail(query) == 1) { return Json(new { success = "true", msg = "" }); } else { return Json(new { success = "false", msg = "" }); } }
/// <summary> /// 出貨管理:檢索>傳票明細 /// </summary> /// <returns></returns> public HttpResponseBase SubPoenaDetailList() { int ticket_id = Convert.ToInt32(Request.Params["ticket_id"]); string jsonStr = String.Empty; List<OrderDetailQuery> store = new List<OrderDetailQuery>(); OrderDetailQuery query = new OrderDetailQuery(); List<OrderDetailQuery> na = new List<OrderDetailQuery>(); try { query.Ticket_Id = ticket_id; _IOrderDetailMgr = new OrderDetailMgr(mySqlConnectionString); int totalCount = 0; store = _IOrderDetailMgr.SubPoenaDetail(query); #region MyRegion // List<OrderDetailQuery> one_product = new List<OrderDetailQuery>(); //單一商品 // List<OrderDetailQuery> combination = new List<OrderDetailQuery>(); //組合商品 // List<OrderDetailQuery> combination_head = new List<OrderDetailQuery>(); //組合品名 // List<OrderDetailQuery> combination_tail = new List<OrderDetailQuery>(); //子商品名 // List<OrderDetailQuery> new_order_detail = new List<OrderDetailQuery>(); ; //新商品資料 // List<OrderDetailQuery> since_order = new List<OrderDetailQuery>(); //自出商品 // Dictionary<int, string> freight_set_map = new Dictionary<int, string>(); // freight_set_map.Add(1, "常溫"); // freight_set_map.Add(2, "冷凍"); // freight_set_map.Add(5, "冷藏"); // Dictionary<int, string> product_freight_set_mapping = new Dictionary<int, string>(); // product_freight_set_mapping.Add(1, "1"); // product_freight_set_mapping.Add(2, "2"); // product_freight_set_mapping.Add(3, "1"); // product_freight_set_mapping.Add(4, "2"); // product_freight_set_mapping.Add(5, "5"); // product_freight_set_mapping.Add(6, "5"); // foreach (var item in store) // { // if (item.Combined_Mode > 1) // { // if (item.item_mode == 1) // combination_head.Add(item); // else // combination_tail.Add(item); // } // else // { // uint freight = item.Product_Freight_Set; // item.Product_Freight_Set =uint.Parse(product_freight_set_mapping[int.Parse(item.Product_Freight_Set.ToString())]); // one_product.Add(item); // item.Product_Freight_Set = freight; // } // } // foreach (var item in combination_head) // { // uint freight = item.Product_Freight_Set; // item.Product_Freight_Set = uint.Parse(product_freight_set_mapping[int.Parse(item.Product_Freight_Set.ToString())]); // combination.Add(item); // item.Product_Freight_Set = freight; // foreach (var items in combination_tail) // { // if (item.Parent_Id == items.Parent_Id && item.pack_id == items.pack_id) // { // items.Buy_Num = items.Buy_Num * items.parent_num; // combination.Add(items); // } // } // } #endregion //(合併數據來自www/Model/Deliver.Php,第1391~1463行) #region 測試方法 List<Parametersrc> Shipment = new List<Parametersrc>(); _ptersrc = new ParameterMgr(mySqlConnectionString); Shipment = _ptersrc.GetAllKindType("order_status");//訂單狀態 Dictionary<string, string> _dtOrderStatus = new Dictionary<string, string>(); foreach (var item in Shipment) { _dtOrderStatus.Add(item.ParameterCode, item.remark); } Dictionary<int, string> freight_set_map = new Dictionary<int, string>(); freight_set_map.Add(1, "常溫"); freight_set_map.Add(2, "冷凍"); freight_set_map.Add(5, "冷藏"); Dictionary<int, string> product_freight_set_mapping = new Dictionary<int, string>(); product_freight_set_mapping.Add(1, "1"); product_freight_set_mapping.Add(2, "2"); product_freight_set_mapping.Add(3, "1"); product_freight_set_mapping.Add(4, "2"); product_freight_set_mapping.Add(5, "5"); product_freight_set_mapping.Add(6, "5"); Dictionary<string, List<OrderDetailQuery>> one_product = new Dictionary<string, List<OrderDetailQuery>>(); //單一商品 Dictionary<string, List<OrderDetailQuery>> combination = new Dictionary<string, List<OrderDetailQuery>>(); //組合商品 List<OrderDetailQuery> combination_head = new List<OrderDetailQuery>(); //組合品名 List<OrderDetailQuery> combination_tail = new List<OrderDetailQuery>(); //子商品名 List<OrderDetailQuery> new_order_detail = new List<OrderDetailQuery>(); ; //新商品資料 List<OrderDetailQuery> since_order = new List<OrderDetailQuery>(); //自出商品 since_order.AddRange(new_order_detail); int w = since_order.Count; string frest; #region 把所有的商品,把單一和組合拆分開 //把所有的商品,把單一和組合區分開,然後把組合的商品運送方式區分開(常溫冷凍冷藏) foreach (var item in store) { if (item.Combined_Mode > 1) { if (item.item_mode == 1) combination_head.Add(item); else combination_tail.Add(item); } else { frest = ""; frest = product_freight_set_mapping[int.Parse(item.Product_Freight_Set.ToString())]; if (!one_product.Keys.Contains(frest)) { List<OrderDetailQuery> s = new List<OrderDetailQuery>(); one_product.Add(frest, s); } one_product[frest].Add(item); //one_product.Add(frest, item); } } #endregion #region 把組合商品中拆分開子商品和父商品 //把組合商品拆分開,并計算子商品的數量,把子商品按照運送方式再次拆開 foreach (var item in combination_head) { frest = ""; frest = product_freight_set_mapping[int.Parse(item.Product_Freight_Set.ToString())]; if (!combination.Keys.Contains(frest)) { List<OrderDetailQuery> s = new List<OrderDetailQuery>(); combination.Add(frest, s); } //combination.Add(frest, item); combination[frest].Add(item); foreach (var items in combination_tail) { if (item.Parent_Id == items.Parent_Id && item.pack_id == items.pack_id) { items.Buy_Num = items.Buy_Num * items.parent_num; //combination.Add(frest, item); combination[frest].Add(items); } } } #endregion #region 把單一商品,組合商品,子商品 根據運送方式在此組合在一起 foreach (var item in freight_set_map) { ////List<OrderDetailQuery> s = new List<OrderDetailQuery>(); ////if (combination.ContainsKey(item.Key.ToString())) ////{ //// s.AddRange(combination[item.Key.ToString()]); ////} ////if (s.Count > 0) ////{ //// new_order_detail.AddRange(combination[item.Key.ToString()]); ////} ////else ////{ //// if (one_product.ContainsKey(item.Key.ToString())) //// { //// new_order_detail.AddRange(one_product[item.Key.ToString()]); //// } ////} if (one_product.ContainsKey(item.Key.ToString())) { new_order_detail.AddRange(one_product[item.Key.ToString()]); } if (combination.ContainsKey(item.Key.ToString())) { //new_order_detail.Add(combination[item.Key.ToString()]); new_order_detail.AddRange(combination[item.Key.ToString()]); } ///-------------------------------------------------- //string a = item.Key.ToString(); //if (combination[item.Key.ToString()].Count > 0) //{ // new_order_detail.AddRange(combination[item.Key.ToString()]); //} //else if (one_product[item.Key.ToString()].Count > 0) //{ // //new_order_detail.Add(combination[item.Key.ToString()]); // new_order_detail.AddRange(one_product[item.Key.ToString()]); //} } #endregion #region 這個根據什麼條件篩選出來 for (int i = 0; i < new_order_detail.Count; i++) { new_order_detail[i].Product_Freight_Set_Str = _dtOrderStatus[(new_order_detail[i].Detail_Status).ToString()]; if (new_order_detail[i].Combined_Mode >= 1 && new_order_detail[i].item_mode == 1) { continue; } else { na.Add(new_order_detail[i]); } } #endregion #endregion IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd"; jsonStr = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(na, Formatting.Indented, timeConverter) + "}";//返回json數據 } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); jsonStr = "{success:false,msg:0}"; } this.Response.Clear(); this.Response.Write(jsonStr.ToString()); this.Response.End(); return this.Response; }
public HttpResponseBase GetLeaveproductList() { BLL.gigade.Model.Vendor vendor = Session["vendor"] as BLL.gigade.Model.Vendor; uint vendor_id = vendor.vendor_id; StringBuilder sb = new StringBuilder(); OrderDetailQuery query = new OrderDetailQuery(); string jsonStr = String.Empty; try { if (Convert.ToInt32(Request.Params["serch_type"]) == 1)//等於1表示選擇了商品名稱 { if (!string.IsNullOrEmpty(Request.Params["searchcomment"].Trim())) { sb.AppendFormat(" AND od.product_name LIKE '%{0}%'", Request.Params["searchcomment"]); } } if (Convert.ToInt32(Request.Params["serch_time_type"]) == 1)//等於1表示選擇了出貨日期 { if (!string.IsNullOrEmpty(Request.Params["time_start"].Trim()) && !string.IsNullOrEmpty(Request.Params["time_end"].Trim())) { sb.AppendFormat("AND os.slave_date_delivery >= '{0}' AND os.slave_date_delivery <='{1}' ", CommonFunction.GetPHPTime(Request.Params["time_start"]), CommonFunction.GetPHPTime(Request.Params["time_end"])); } } //query.Search_Type = Convert.ToInt32(Request.Params["serch_type"]); //if (!string.IsNullOrEmpty(Request.Params["searchcomment"].Trim())) //{ // query.SearchComment = Request.Params["searchcomment"].Trim(); //} //query.Serch_Time_Type = Convert.ToInt32(Request.Params["serch_time_type"]); if (!string.IsNullOrEmpty(vendor_id.ToString())) { query.Vendor_Id = vendor_id; } query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "25");//用於分頁的變量 DataTable dt = new DataTable(); _OrderDetailMgr = new OrderDetailMgr(mySqlConnectionString); int totalCount = 0; dt = _OrderDetailMgr.GetLeaveproductList(query, out totalCount, sb.ToString());//查询出供應商出貨單 IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd"; jsonStr = "{success:true,totalCount:" + totalCount + ",data:" + JsonConvert.SerializeObject(dt, Formatting.Indented, timeConverter) + "}";//返回json數據 } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); jsonStr = "{success:false,msg:0}"; } this.Response.Clear(); this.Response.Write(jsonStr.ToString()); this.Response.End(); return this.Response; }
public string UpdateOrderDetailSome(OrderDetailQuery query) { StringBuilder sql = new StringBuilder(); try { sql.AppendFormat("update order_detail set detail_status=4 where slave_id='{0}' and parent_id='{1}' and pack_id='{2}'; ",query.Slave_Id,query.Parent_Id,query.pack_id); return sql.ToString(); } catch (Exception ex) { throw new Exception("OrderDetailDao.UpdateOrderDetailSome -->" + ex.Message + sql.ToString(), ex); } }
/// <summary> /// 修改reder_dail 表中的所有数据 /// </summary> /// <param name="query"></param> /// <returns></returns> public string UpOrderDetail(OrderDetailQuery query) { query.Replace4MySQL(); StringBuilder strSql = new StringBuilder(@"update order_detail set "); strSql.AppendFormat(" slave_id='{0}',item_id='{1}',item_vendor_id='{2}',product_freight_set='{3}',", query.Slave_Id, query.Item_Id, query.Item_Vendor_Id, query.Product_Freight_Set); strSql.AppendFormat(" product_mode='{0}',single_price='{1}',product_spec_name='{2}',single_cost='{3}',", query.Product_Mode, query.Single_Price, query.Product_Spec_Name, query.Single_Cost); strSql.AppendFormat(" event_cost='{0}',product_name='{1}',single_money='{2}',deduct_bonus='{3}',", query.Event_Cost, query.Product_Name, query.Single_Money, query.Deduct_Bonus); strSql.AppendFormat(" deduct_welfare='{0}',deduct_happygo='{1}',deduct_happygo_money='{2}',", query.Deduct_Welfare, query.Deduct_Happygo, query.Deduct_Happygo_Money); strSql.AppendFormat(" deduct_account='{0}',deduct_account_note='{1}',accumulated_bonus='{2}',", query.Deduct_Account, query.Deduct_Account_Note, query.Accumulated_Bonus); strSql.AppendFormat(" accumulated_happygo='{0}',buy_num='{1}',detail_status='{2}',", query.Accumulated_Happygo, query.Buy_Num, query.Detail_Status); strSql.AppendFormat(" delay_till='{0}',lastmile_deliver_serial='{1}',lastmile_deliver_datetime='{2}',", query.Delay_Till, query.Lastmile_Deliver_Serial, query.Lastmile_Deliver_Datetime); strSql.AppendFormat(" lastmile_deliver_agency='{0}',bag_check_money='{1}',channel_detail_id='{2}',", query.Lastmile_Deliver_Agency, query.Bag_Check_Money, query.Channel_Detail_Id); strSql.AppendFormat(" combined_mode='{0}',item_mode='{1}',parent_id='{2}',parent_name='{3}',", query.Combined_Mode, query.item_mode, query.Parent_Id, query.parent_name); strSql.AppendFormat(" parent_num='{0}',price_master_id='{1}',pack_id='{2}',sub_order_id='{3}',", query.parent_num, query.price_master_id, query.pack_id, query.Sub_Order_Id); strSql.AppendFormat(" site_id='{0}',event_id='{1}',prepaid='{2}' ", query.Site_Id, query.event_id, query.Prepaid); strSql.AppendFormat(" where detail_id='{0}';", query.Detail_Id); return strSql.ToString(); }
public string UpdateOrderDetail(OrderDetailQuery query) { StringBuilder sql = new StringBuilder(); try { sql.AppendFormat("update order_detail set detail_status=4 where detail_id='{0}'; ", query.Detail_Id); return sql.ToString(); } catch (Exception ex) { throw new Exception("OrderDetailDao.UpdateOrderDetail -->" + ex.Message + sql.ToString(), ex); } }
public List<OrderDetailQuery> DeliveryInformation(OrderDetailQuery query, String str = null) { StringBuilder sqlclomn = new StringBuilder();//要查詢的列 StringBuilder sqlcondition = new StringBuilder();//查詢條件 StringBuilder sqltable = new StringBuilder(); //要連接的表 StringBuilder sqlgroupby = new StringBuilder();//聚合條件 StringBuilder sqlsort = new StringBuilder();//排序條件 string sql = ""; try { sqlclomn.Append(@"SELECT od.detail_id,od.product_name,od.product_freight_set,od.product_spec_name,od.single_money "); sqlclomn.Append(",od.parent_name,od.parent_num,combined_mode,od.item_mode,od.buy_num "); sqltable.Append(" FROM order_detail od "); sqltable.Append(" LEFT JOIN order_slave os on od.slave_id=os.slave_id "); sqltable.Append(" LEFT JOIN vendor v on v.vendor_id=os.vendor_id "); sqlcondition.Append(" where 1=1 "); sqlcondition.AppendFormat(" and os.order_id = ( select order_id from order_slave where slave_id='{0}') ", query.Slave_Id); sqlcondition.AppendFormat(" and os.slave_id ='{0}' ", query.Slave_Id); sqlcondition.Append(" and od.product_mode in (1) "); sqlcondition.Append(" and od.detail_status = 2 ");//待出貨; sqlsort.Append(" ORDER BY os.slave_id asc ,product_freight_set asc, combined_mode ASC,item_mode asc "); if (query.Vendor_Id != 0) { sqlcondition.AppendFormat(" and os.vendor_id='{0}' ", query.Vendor_Id); } if (!string.IsNullOrEmpty(str)) { sqlcondition.Append(str); } sql = sqlclomn.ToString() + sqltable.ToString() + sqlcondition.ToString() + sqlsort.ToString(); } catch (Exception ex) { throw new Exception("OrderDetailDao.DeliveryInformation -->" + ex.Message + sqlclomn.ToString(), ex); } return _dbAccess.getDataTableForObj<OrderDetailQuery>(sql); }
public DataTable GetCategorySummary(OrderDetailQuery query) { StringBuilder sql_amount = new StringBuilder(); StringBuilder sql = new StringBuilder(); StringBuilder sqlSingle = new StringBuilder(); StringBuilder sqlFather = new StringBuilder(); DataTable dt = new DataTable(); try { sql.AppendFormat(@" SELECT category_id,SUM(amount) as amount from (SELECT order_id,SUM(amount) as amount,category_id from ("); sqlSingle.AppendFormat(@" SELECT DISTINCT om.order_id,pcs.category_id,od.detail_id,od.single_money*buy_num-od.deduct_bonus-od.deduct_welfare as amount from order_detail od INNER JOIN order_slave os ON os.slave_id=od.slave_id INNER JOIN order_master om ON om.order_id=os.order_id INNER JOIN product_item pi ON od.item_id=pi.item_id INNER JOIN product_category_set pcs ON pcs.product_id=pi.product_id WHERE od.item_mode =0 AND od.detail_status NOT IN(89,90,91) AND pcs.category_id={0} AND om.order_status NOT IN(90,91)", query.category_id); sqlFather.AppendFormat(@" SELECT DISTINCT om.order_id,pcs.category_id,od.detail_id,od.single_money*buy_num-od.deduct_bonus-od.deduct_welfare as money from order_detail od INNER JOIN order_slave os ON os.slave_id=od.slave_id INNER JOIN order_master om ON om.order_id=os.order_id INNER JOIN product_category_set pcs ON pcs.product_id=od.parent_id WHERE od.item_mode =1 AND od.detail_status NOT IN(89,90,91) AND pcs.category_id={0} AND om.order_status NOT IN(90,91)", query.category_id); if (query.category_status != 0) { sqlSingle.AppendFormat(" AND om.money_collect_date > 0"); sqlFather.AppendFormat(" AND om.money_collect_date > 0"); } if(query.date_stauts!=0) { if (query.date_start != DateTime.MinValue && query.date_end != DateTime.MinValue) { sqlSingle.AppendFormat(" AND om.order_createdate>='{0}' and om.order_createdate<='{1}'", CommonFunction.GetPHPTime(CommonFunction.DateTimeToString(query.date_start)), CommonFunction.GetPHPTime(CommonFunction.DateTimeToString(query.date_end))); sqlFather.AppendFormat(" AND om.order_createdate>='{0}' and om.order_createdate<='{1}'", CommonFunction.GetPHPTime(CommonFunction.DateTimeToString(query.date_start)), CommonFunction.GetPHPTime(CommonFunction.DateTimeToString(query.date_end))); } } sql_amount.Append(sql.ToString() + sqlSingle.ToString() + " UNION " + sqlFather.ToString() + " ) cate_amount GROUP BY order_id) c_amount "); if (query.c_money > 0 || query.c_money1 > 0) { sql_amount.AppendFormat(" WHERE amount>={0} and amount<={1};", query.c_money, query.c_money1); } return _dbAccess.getDataTable(sql_amount.ToString()); } catch (Exception ex) { throw new Exception("OrderDetailDao.GetCategorySummary -->" + ex.Message + sql.ToString(), ex); } }
public DataTable GetLeaveproductList(OrderDetailQuery query, out int totalCount, string sqlappend = null) { StringBuilder sqlcount = new StringBuilder(); StringBuilder sql = new StringBuilder(); StringBuilder sqlstr = new StringBuilder(); StringBuilder limitpage = new StringBuilder(); totalCount = 0; try { sqlcount.AppendFormat("select os.order_id "); sql.AppendFormat(@"SELECT os.order_id,od.detail_id ,CONCAT(od.product_name,od.product_spec_name) as product_spec_name_new, tp.remark as state, CASE od.item_mode WHEN 2 THEN od.buy_num*od.parent_num ELSE od.buy_num END as number_count, CASE os.slave_date_delivery when 0 THEN '未出貨' ELSE FROM_UNIXTIME(os.slave_date_delivery,'%Y/%m/%d %H:%i:%s') END as chuhuoriqi, CASE od.item_mode WHEN 0 THEN '單一' WHEN 1 THEN '父商品' ELSE '子商品' END AS comboxtype"); sqlstr.AppendFormat(@" FROM order_detail od RIGHT JOIN order_slave os on od.slave_id=os.slave_id LEFT JOIN t_parametersrc tp on od.detail_status=tp.parameterCode where tp.parameterType = 'order_status' and item_vendor_id = '{0}' AND od.product_mode = 2 AND od.slave_id = os.slave_id AND od.detail_status in (2,4,5,6,9) ", query.Vendor_Id); sqlstr.AppendFormat(sqlappend.ToString()); sqlstr.AppendFormat("ORDER BY od.detail_id DESC "); limitpage.AppendFormat("limit {0},{1}; ", query.Start, query.Limit); totalCount = _dbAccess.getDataTable(sqlcount.ToString() + sqlstr.ToString()).Rows.Count; return _dbAccess.getDataTable(sql.ToString() + sqlstr.ToString() + limitpage.ToString()); } catch (Exception ex) { throw new Exception("OrderDetailDao.GetLeaveproductList -->" + ex.Message + sql.ToString(), ex); } }
/// <summary> /// 查詢購買金額與應稅類型 /// </summary> /// <param name="query">查詢條件</param> /// <returns>購買金額與應稅類型列表</returns> public DataTable GetBuyAmountAndTaxType(OrderDetailQuery query) { try { return _orderDetailDao.GetBuyAmountAndTaxType(query); } catch (Exception ex) { throw new Exception("OrderDetailMgr-->GetBuyAmountAndTaxType-->" + ex.Message, ex); } }
public List<OrderDetailQuery> OrderVendorProducesList(OrderDetailQuery query, out int totalCount) { StringBuilder sql = new StringBuilder(); StringBuilder sqlCount = new StringBuilder(); StringBuilder sqlFrom = new StringBuilder(); StringBuilder sqlWhere = new StringBuilder(); totalCount = 0; try { sqlCount.Append(" SELECT count(om.order_id ) as totalCount "); sql.Append(" select os.order_id,v.vendor_name_simple,od.product_name,img.spec_image,od.product_spec_name,od.buy_num ,om.order_name,os.slave_status,para.remark as slave_status_string ,od.product_mode,pramode.remark as product_mode_string,os.slave_date_delivery,om.note_order,od.item_mode,od.parent_num "); sqlFrom.Append("from order_detail od LEFT JOIN order_slave os on os.slave_id=od.slave_id LEFT JOIN order_master om on os.order_id=om.order_id LEFT JOIN vendor v on v.vendor_id=os.vendor_id "); sqlFrom.Append(" left JOIN (select parameterCode,remark from t_parametersrc where parameterType='order_status') para ON para.parameterCode=os.slave_status "); sqlFrom.Append("LEFT JOIN (select parameterCode,remark from t_parametersrc where parameterType='product_mode') pramode on pramode.parameterCode=od.product_mode "); sqlFrom.Append(" LEFT JOIN (SELECT pim.item_id,pim.spec_id_1 , psc.spec_image FROM product_item pim left join product_spec psc ON pim.spec_id_1 = psc.spec_id) img on img.item_id=od.item_id "); sqlWhere.AppendFormat("where 1=1 and os.vendor_id={0} ", query.Vendor_Id); if (query.promodel == 1) { sqlWhere.AppendFormat(" and od.product_mode in (1) "); } else if (query.promodel == 2) { sqlWhere.AppendFormat(" and od.product_mode in (2,3) "); } //日期區間 if (query.date != 0) { sqlWhere.AppendFormat(" and om.order_date_pay>='{0}' and om.order_date_pay<='{1}' ", CommonFunction.GetPHPTime(query.start_time.ToString()), CommonFunction.GetPHPTime(query.end_time.ToString())); } //關鍵字查詢 if (query.select_type != 0) { sqlWhere.AppendFormat(" and od.product_name LIKE '%{0}%' ", query.select_con); } //訂單狀態 if (query.radiostatus == 4) { sqlWhere.Append(" and os.slave_status in (4,6,7) "); } else if (query.radiostatus != -1) { sqlWhere.AppendFormat(" and os.slave_status ={0} ", query.radiostatus); } else { sqlWhere.Append(" and os.slave_status in (2,4,99,6,7) "); } if (query.IsPage) { DataTable _dt = _dbAccess.getDataTable(sqlCount.ToString() + sqlFrom.ToString() + sqlWhere.ToString()); if (_dt != null && _dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0]["totalCount"]); } sqlWhere.AppendFormat(" ORDER BY os.slave_date_delivery DESC,om.order_createdate DESC limit {0},{1};", query.Start, query.Limit); } return _dbAccess.getDataTableForObj<OrderDetailQuery>(sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString()); } catch (Exception ex) { throw new Exception("OrderDetailDao-->OrderVendorProducesList -->" + ex.Message + sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString(), ex); } }
public List<OrderDetailQuery> GetCategorySummaryList(OrderDetailQuery query, out int totalCount, out int SumAmount) { try { SumAmount = 0; totalCount = 0; ProductCategory pc_query = new ProductCategory(); List<OrderDetailQuery> list = new List<OrderDetailQuery>(); pc_query.category_id = query.category_id; pc_query.Start = query.Start; pc_query.Limit = query.Limit; DataTable dt = _productCategoryDao.GetCagegoryIdsByIdAndFatherId(pc_query); if (dt != null && dt.Rows.Count > 0) { totalCount = dt.Rows.Count; for (int i = 0; i < dt.Rows.Count; i++) { if (!string.IsNullOrEmpty(dt.Rows[i]["category_id"].ToString())) { query.category_id = Convert.ToUInt32(dt.Rows[i]["category_id"]); DataTable model = _orderDetailDao.GetCategorySummary(query); OrderDetailQuery listRow = new OrderDetailQuery(); if (model != null && model.Rows.Count > 0) { listRow.category_name = dt.Rows[i]["category_name"].ToString(); listRow.category_id = query.category_id; if (!string.IsNullOrEmpty(model.Rows[0]["amount"].ToString())) { SumAmount += Convert.ToInt32(model.Rows[0]["amount"]); listRow.amount = Convert.ToInt32(model.Rows[0]["amount"]); } list.Add(listRow); } } } } if (query.Start + query.Limit > list.Count) { query.Limit = list.Count - query.Start; if (query.Limit < 0) { query.Start = 0; if (list.Count > 25) { query.Limit = 25; } else { query.Limit = list.Count; } } } list = list.GetRange(query.Start, query.Limit); return list; } catch (Exception ex) { throw new Exception("OrderMasterMgr-->GetCategorySummaryList-->" + ex.Message, ex); } }
public DataTable ProduceGroupCsv(OrderDetailQuery query) { StringBuilder sql = new StringBuilder(); StringBuilder sqlFrom = new StringBuilder(); StringBuilder sqlWhere = new StringBuilder(); try { sql.Append("select od.item_id,od.product_name,sum(od.buy_num) as total "); sqlFrom.Append(" from order_detail od "); sqlFrom.Append(" LEFT JOIN order_slave os on os.slave_id=od.slave_id "); sqlFrom.Append(" left JOIN vendor v on v.vendor_id=os.vendor_id "); sqlFrom.Append(" LEFT JOIN order_master om on os.order_id =om.order_id "); // sqlFrom.Append(""); sqlWhere.AppendFormat(" where od.item_mode!=1 and os.vendor_id={0} ", query.Vendor_Id); if (query.date != 0) { sqlWhere.AppendFormat(" and om.order_createdate>'{0}' and om.order_createdate<='{1}' ", CommonFunction.GetPHPTime(query.start_time.ToString()), CommonFunction.GetPHPTime(query.end_time.ToString())); } //關鍵字查詢 if (query.select_type != 0) { sqlWhere.AppendFormat(" and od.product_name LIKE '%{0}%' ", query.select_con); } //訂單狀態 if (query.radiostatus != -1) { sqlWhere.AppendFormat(" and os.slave_status ={0} ", query.radiostatus); } else { sqlWhere.Append(" and os.slave_status in (0,2,4,99) "); } sqlWhere.Append(" GROUP BY od.item_id; "); return _dbAccess.getDataTable(sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString()); } catch (Exception ex) { throw new Exception("OrderDetailDao-->ProduceGroupCsv -->" + ex.Message + sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString(), ex); } }
/// <summary> /// 更新定單狀態以便開立發票 /// </summary> /// <param name="order_id">定單編號</param> /// <param name="freight_normal">常溫</param> /// <param name="freight_low">低溫</param> /// <param name="status">操作狀態</param> /// <returns>是否操作成功</returns> public bool ModifyOrderInvoice(int order_id, int freight_normal, int freight_low, string status) { //todo:對應Invoice.php頁面中第23行實現modify_order_invoice方法 bool flag = false; OrderMasterQuery query = new OrderMasterQuery(); OrderMasterQuery model = new OrderMasterQuery(); query.Order_Id = Convert.ToUInt32(order_id); if (status == "modify") { //設定條件 invoice_master_record.invoice_number='is not null' query.status_description = "modify"; } model = orderMasterDao.GetOrderMasterInvoice(query); if (model != null) { if (model.Deduct_Card_Bonus > 0) { OrderDetailQuery queryOrderDetail = new OrderDetailQuery { Order_Id = Convert.ToUInt32(order_id) }; DataTable dtAmount = orderDetailDao.GetBuyAmountAndTaxType(queryOrderDetail); if (dtAmount!=null && dtAmount.Rows.Count>0) { int nTax = 0; int nFreeTax = 0; for (int i = 0; i < dtAmount.Rows.Count; i++) { if (dtAmount.Rows[i]["tax_type"]=="1") { //nTax=round } else if (dtAmount.Rows[i]["tax_type"]=="3") { } } } } } return flag; }
public DataTable ProduceGroupExcel(OrderDetailQuery query) { StringBuilder sql = new StringBuilder(); StringBuilder sqlFrom = new StringBuilder(); StringBuilder sqlWhere = new StringBuilder(); try { sql.Append(" select os.order_id,v.vendor_name_simple,od.product_name,od.product_spec_name,od.buy_num ,om.order_name,para.remark as slave_status_string ,os.slave_date_delivery,om.note_order,img.spec_image,os.slave_status,od.product_mode,pramode.remark as product_mode_string,od.item_mode,od.parent_num "); sqlFrom.Append("from order_detail od LEFT JOIN order_slave os on os.slave_id=od.slave_id LEFT JOIN order_master om on os.order_id=om.order_id LEFT JOIN vendor v on v.vendor_id=os.vendor_id "); sqlFrom.Append(" left JOIN (select parameterCode,remark from t_parametersrc where parameterType='order_status') para ON para.parameterCode=os.slave_status "); sqlFrom.Append("LEFT JOIN (select parameterCode,remark from t_parametersrc where parameterType='product_mode') pramode on pramode.parameterCode=od.product_mode "); sqlFrom.Append(" LEFT JOIN (SELECT pim.item_id,pim.spec_id_1 , psc.spec_image FROM product_item pim left join product_spec psc ON pim.spec_id_1 = psc.spec_id) img on img.item_id=od.item_id "); sqlWhere.AppendFormat("where 1=1 and os.vendor_id={0} ", query.Vendor_Id); //日期區間 if (query.date != 0) { sqlWhere.AppendFormat(" and om.order_date_pay>'{0}' and om.order_date_pay<='{1}' ", CommonFunction.GetPHPTime(query.start_time.ToString()), CommonFunction.GetPHPTime(query.end_time.ToString())); } //關鍵字查詢 if (query.select_type != 0) { sqlWhere.AppendFormat(" and od.product_name LIKE '%{0}%' ", query.select_con); } //訂單狀態 if (query.radiostatus != -1) { sqlWhere.AppendFormat(" and os.slave_status ={0} ", query.radiostatus); } else { sqlWhere.Append(" and os.slave_status in (0,2,4,99) "); } sqlWhere.AppendFormat(" ORDER BY om.order_createdate DESC limit 65500 ;"); return _dbAccess.getDataTable(sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString()); } catch (Exception ex) { throw new Exception("OrderDetailDao-->ProduceGroupExcel -->" + ex.Message + sql.ToString() + sqlFrom.ToString() + sqlWhere.ToString(), ex); } }
/// <summary> /// 品牌營業額統計匯出 /// </summary> /// <returns></returns> public void GetNewOrderRevenueExprot() { string json = string.Empty; try { OrderDetailQuery query = new OrderDetailQuery(); #region 前置查詢條件 if (!string.IsNullOrEmpty(Request.Params["Brand_Id"]))/*品牌編號*/ { query.Brand_Id_In = Request.Params["Brand_Id"]; } if (!string.IsNullOrEmpty(Request.Params["product_manage"]) && Request.Params["product_manage"] != "null")//管理人員 {//獲取管理人 query.product_manage = int.Parse(Request.Params["product_manage"]); } if (!string.IsNullOrEmpty(Request.Params["dateOne"])) { query.time_start = CommonFunction.GetPHPTime(DateTime.Parse(Request.Params["dateOne"]).ToString("yyyy/MM/dd 00:00:00")); } if (!string.IsNullOrEmpty(Request.Params["dateTwo"])) { query.time_end = CommonFunction.GetPHPTime(DateTime.Parse(Request.Params["dateTwo"]).ToString("yyyy/MM/dd 23:59:59")); } long start = query.time_start;//開始時間 long end = query.time_end;//結束時間 if (!string.IsNullOrEmpty(Request.Params["Channel_Id"]) && Request.Params["Channel_Id"] != "null")//賣場 { query.channel = int.Parse(Request.Params["Channel_Id"]); } if (!string.IsNullOrEmpty(Request.Params["slave_status"]) && Request.Params["slave_status"]!="null")//訂單狀態 { query.Status = int.Parse(Request.Params["slave_status"]); } if (!string.IsNullOrEmpty(Request.Params["order_payment"]) && Request.Params["order_payment"] != "null")//付款方式 { query.order_payment = int.Parse(Request.Params["order_payment"]); } if (!string.IsNullOrEmpty(Request.Params["selecttype"]))//關鍵字查詢類型 { query.select_type = int.Parse(Request.Params["selecttype"]); if (!string.IsNullOrEmpty(Request.Params["searchcon"]))//關鍵字查詢內容 { query.select_con = Request.Params["searchcon"]; } } #endregion VendorBrandQuery Vendorbrandquery = new VendorBrandQuery(); Dictionary<uint, VendorBrandQuery> brands = new Dictionary<uint, VendorBrandQuery>();///字典----儲存供應商編號,供應商名稱和錧別,錧別編號的信息 Dictionary<uint, Dictionary<string, Dictionary<string, uint>>> brandDailyTotal = new Dictionary<uint, Dictionary<string, Dictionary<string, uint>>>();///字典,儲存錧別,每天的計算和每天的購物金 ///////////////brand_id//////////daysum,dayduct/////日期///值 List<VendorBrandQuery> aDB_Brand_Select = new List<VendorBrandQuery>(); _vbrand = new VendorBrandMgr(mySqlConnectionString); aDB_Brand_Select = _vbrand.GetBandList(Vendorbrandquery);//把錧別和供應商信息保存到字典里 for (int i = 0; i < aDB_Brand_Select.Count; i++) { if (!brands.Keys.Contains(aDB_Brand_Select[i].Brand_Id)) { brands.Add(aDB_Brand_Select[i].Brand_Id, aDB_Brand_Select[i]); } } bool CrossMonth = CommonFunction.GetNetTime(start).Month == CommonFunction.GetNetTime(end).Month ? false : true; string timelong = ""; while (start <= end)//時間格式化,用來保存每天的小計 { timelong += CommonFunction.GetNetTime(start).ToString("yyyy/MM/dd") + ","; start += 86400; } timelong = timelong.Substring(0, timelong.LastIndexOf(",")); string[] times = timelong.Split(','); Dictionary<string, uint> timetro = new Dictionary<string, uint>(); for (int i = 0; i < times.Count(); i++)//字典加上日期時間 { timetro.Add(times[i], 0); } Dictionary<string, Dictionary<string, uint>> daysum_deduct = new Dictionary<string, Dictionary<string, uint>>();//保存每個商品每日的小計和購物金 daysum_deduct.Add("daysum", timetro);//每日小計 daysum_deduct.Add("daydeduct", timetro);//每日小計 _orderDetailMgr = new OrderDetailMgr(mySqlConnectionString); List<OrderDetailQuery> ordertailList = new List<OrderDetailQuery>(); DataTable _dt = new DataTable(); ordertailList = _orderDetailMgr.GetOrderDetailList(query);//通過查詢條件獲取的數據 string[] quanxuan = query.Brand_Id_In.Split(','); foreach (uint item in brands.Keys) { if (!brandDailyTotal.Keys.Contains(item)) { daysum_deduct = new Dictionary<string, Dictionary<string, uint>>(); timetro = new Dictionary<string, uint>(); for (int a = 0; a < times.Count(); a++)//字典加上日期時間 { timetro.Add(times[a], 0); } daysum_deduct.Add("daysum", timetro);//每日小計 timetro = new Dictionary<string, uint>(); for (int a = 0; a < times.Count(); a++)//字典加上日期時間 { timetro.Add(times[a], 0); } daysum_deduct.Add("daydeduct", timetro);//購物金 brandDailyTotal.Add(item, daysum_deduct); } } //if (ordertailList.Count > 0) //{ for (int i = 0; i < ordertailList.Count; i++) { ordertailList[i].subtotal = ordertailList[i].Single_Money * ordertailList[i].Buy_Num; ordertailList[i].cost = (ordertailList[i].Event_Cost != 0 && ordertailList[i].Single_Cost != ordertailList[i].Single_Money) ? ordertailList[i].Event_Cost : ordertailList[i].Single_Cost; if (!brands.Keys.Contains(ordertailList[i].Brand_Id)) { VendorBrandQuery brand = new VendorBrandQuery(); brand.Vendor_Id = ordertailList[i].Vendor_Id; brand.Brand_Name = ordertailList[i].Brand_Name; brand.Brand_Id = ordertailList[i].Brand_Id; brand.vendor_name_simple = ordertailList[i].Vendor_Name_Simple; brands.Add(ordertailList[i].Brand_Id, brand); } if (!brandDailyTotal.Keys.Contains(ordertailList[i].Brand_Id)) { daysum_deduct = new Dictionary<string, Dictionary<string, uint>>(); timetro = new Dictionary<string, uint>(); for (int a = 0; a < times.Count(); a++)//字典加上日期時間 { timetro.Add(times[a], 0); } daysum_deduct.Add("daysum", timetro);//每日小計 timetro = new Dictionary<string, uint>(); for (int a = 0; a < times.Count(); a++)//字典加上日期時間 { timetro.Add(times[a], 0); } daysum_deduct.Add("daydeduct", timetro);//購物金 brandDailyTotal.Add(ordertailList[i].Brand_Id, daysum_deduct); } string time = CommonFunction.GetNetTime(ordertailList[i].Order_Createdate).ToString("yyyy/MM/dd"); brandDailyTotal[ordertailList[i].Brand_Id]["daysum"][time] += ordertailList[i].subtotal;//每個商品的小計 brandDailyTotal[ordertailList[i].Brand_Id]["daydeduct"][time] += ordertailList[i].Deduct_Bonus;//每個商品的購物金小計 } Dictionary<string, uint> daysum_allbrand = new Dictionary<string, uint>();//所有品牌的每日小計 Dictionary<string, uint> deductsum_allbrand = new Dictionary<string, uint>();//所有品牌的每日購物金 foreach (uint key in brandDailyTotal.Keys) { foreach (string time in brandDailyTotal[key]["daysum"].Keys)//循環每個品牌的每日小計,計算到所有品牌的每日小計 { if (!daysum_allbrand.Keys.Contains(time)) { daysum_allbrand.Add(time, brandDailyTotal[key]["daysum"][time]); } else { daysum_allbrand[time] += brandDailyTotal[key]["daysum"][time]; } } foreach (string time in brandDailyTotal[key]["daydeduct"].Keys)//循環每個品牌的每日購物金小計,計算到所有品牌的每日小計 { if (!deductsum_allbrand.Keys.Contains(time)) { deductsum_allbrand.Add(time, brandDailyTotal[key]["daydeduct"][time]); } else { deductsum_allbrand[time] += brandDailyTotal[key]["daydeduct"][time]; } } } // _dt.Columns.Add("品牌名稱", typeof(String)); _dt.Columns.Add("供應商名稱", typeof(String)); string ym_last = ""; foreach (string it in timetro.Keys) { string ym = it.Substring(0, 7); if (string.IsNullOrEmpty(ym_last)) ym_last = ym; if (!ym_last.Equals(ym)) { _dt.Columns.Add(ym_last.Split('/')[1] + "月小計", typeof(String)); _dt.Columns.Add("購物金(" + ym_last.Split('/')[0] + "/" + ym_last.Split('/')[1] + "月)", typeof(String)); _dt.Columns.Add("扣除購物金(" + ym_last.Split('/')[0] + "/" + ym_last.Split('/')[1] + "月)", typeof(String)); ym_last = ym; } _dt.Columns.Add(it, typeof(String)); _dt.Columns.Add("購物金(" + it + ")", typeof(String)); _dt.Columns.Add("扣除購物金(" + it + ")", typeof(String)); } if (CrossMonth) { _dt.Columns.Add(ym_last.Split('/')[1] + "月小計", typeof(String)); _dt.Columns.Add("購物金(" + ym_last.Split('/')[0] + "/" + ym_last.Split('/')[1] + "月)", typeof(String)); _dt.Columns.Add("扣除購物金(" + ym_last.Split('/')[0] + "/" + ym_last.Split('/')[1] + "月)", typeof(String)); } _dt.Columns.Add("品牌總計", typeof(String)); _dt.Columns.Add(" 購物金總計", typeof(String)); _dt.Columns.Add(" 扣除購物金總計", typeof(String)); DataRow addrow = _dt.NewRow();//添加一行 addrow[0] = " "; addrow[1] = "每日小計"; int r = 2; ym_last = ""; uint sum_monthly = 0;//每月小計 uint deduct_monthly = 0;//每月購物金小計 uint sum_year = 0;//總計 uint deduct_year = 0;//購物金總計 foreach (string it in daysum_allbrand.Keys) { string ym = it.Substring(0, 7); if (string.IsNullOrEmpty(ym_last)) ym_last = ym; if (!ym_last.Equals(ym)) { addrow[r] = sum_monthly; r++; addrow[r] = deduct_monthly; r++; addrow[r] = sum_monthly - deduct_monthly; r++; ym_last = ym; sum_monthly = 0; deduct_monthly = 0; } sum_monthly += daysum_allbrand[it]; deduct_monthly += deductsum_allbrand[it]; sum_year += daysum_allbrand[it]; deduct_year += deductsum_allbrand[it]; addrow[r] = daysum_allbrand[it]; r++; addrow[r] = deductsum_allbrand[it]; r++; addrow[r] = daysum_allbrand[it] - deductsum_allbrand[it]; r++; } if (CrossMonth) { addrow[r] = sum_monthly; r++; addrow[r] = deduct_monthly; r++; addrow[r] = sum_monthly - deduct_monthly; r++; } addrow[r] = sum_year; r++; addrow[r] = deduct_year; r++; addrow[r] = sum_year - deduct_year; _dt.Rows.Add(addrow); foreach (uint it in brandDailyTotal.Keys) { ym_last = ""; sum_monthly = 0; deduct_monthly = 0; sum_year = 0; deduct_year = 0; r = 0; addrow = _dt.NewRow(); if (brands.Keys.Contains(it)) { addrow[r] = brands[it].Brand_Name; r++; addrow[r] = brands[it].vendor_name_simple; r++; } else { addrow[r] = " "; r++; addrow[r] = " "; r++; } foreach (string ite in brandDailyTotal[it]["daysum"].Keys) { string ym = ite.Substring(0, 7); if (string.IsNullOrEmpty(ym_last)) ym_last = ym; if (!ym_last.Equals(ym)) { addrow[r] = sum_monthly; r++; addrow[r] = deduct_monthly; r++; addrow[r] = sum_monthly - deduct_monthly; r++; ym_last = ym; sum_monthly = 0; deduct_monthly = 0; } sum_monthly += brandDailyTotal[it]["daysum"][ite]; sum_year += brandDailyTotal[it]["daysum"][ite]; deduct_monthly += brandDailyTotal[it]["daydeduct"][ite]; deduct_year += brandDailyTotal[it]["daydeduct"][ite]; addrow[r] = brandDailyTotal[it]["daysum"][ite]; r++; addrow[r] = brandDailyTotal[it]["daydeduct"][ite]; r++; addrow[r] = brandDailyTotal[it]["daysum"][ite] - brandDailyTotal[it]["daydeduct"][ite]; r++; } if (CrossMonth) { addrow[r] = sum_monthly; r++; addrow[r] = deduct_monthly; r++; addrow[r] = sum_monthly - deduct_monthly; r++; } addrow[r] = sum_year; r++; addrow[r] = deduct_year; r++; addrow[r] = sum_year - deduct_year; _dt.Rows.Add(addrow); } // } //if (_dt.Rows.Count > 0) //{ //string fileName = "品牌營業額匯出_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; //MemoryStream ms = ExcelHelperXhf.ExportDT(_dt, ""); //Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); //Response.BinaryWrite(ms.ToArray()); string fileName = "品牌營業額匯出_" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".csv"; //MemoryStream ms = ExcelHelperXhf.ExportDT(_dt, ""); //Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); //Response.BinaryWrite(ms.ToArray()); StringWriter sw = ExcelHelperXhf.SetCsvFromData(_dt, fileName); Response.Clear(); Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); Response.ContentType = "application/ms-excel"; Response.ContentEncoding = Encoding.Default; Response.Write(sw); Response.End(); //} //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); } }
/// <summary> /// 查詢購買金額與應稅類型 /// </summary> /// <param name="query">查詢條件</param> /// <returns>購買金額與應稅類型列表</returns> public DataTable GetBuyAmountAndTaxType(OrderDetailQuery query) { StringBuilder sbSql = new StringBuilder(); sbSql.Append("SELECT SUM(od.buy_num * od.single_money) as amount,tax_type "); sbSql.Append(" FROM order_detail od "); sbSql.Append(" INNER JOIN product_item pit USING(item_id) "); sbSql.Append(" INNER JOIN product p USING(product_id) "); sbSql.Append(" INNER JOIN order_slave os USING(slave_id) "); sbSql.Append(" INNER JOIN order_master om USING(order_id) "); sbSql.Append(" where 1=1 "); sbSql.AppendFormat(" and om.order_id = {0} ", query.Order_Id); sbSql.Append(" AND od.item_mode in (0,1) "); sbSql.Append(" group by tax_type "); try { return _dbAccess.getDataTable(sbSql.ToString()); } catch (Exception ex) { throw new Exception("OrderDetailDao-->GetBuyAmountAndTaxType-->" + ex.Message + sbSql.ToString(), ex); } }
public HttpResponseBase GetOrderChildList() { List<OrderDetailQuery> stores = new List<OrderDetailQuery>(); string json = string.Empty; try { OrderDetailQuery query = new OrderDetailQuery(); query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "5");//用於分頁的變量 //付款單號 query.Order_Id = Convert.ToUInt32(Request.Params["OrderId"].ToString()); query.Parent_Id = Convert.ToInt32(Request.Params["ParentId"].ToString()); query.pack_id = Convert.ToUInt32(Request.Params["PackId"].ToString()); query.isChildItem = 1; _orderDetailMgr = new OrderDetailMgr(mySqlConnectionString); int totalCount = 0; stores = _orderDetailMgr.GetOrderDetailList(query, out totalCount); IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; //listUser是准备转换的对象 foreach (var item in stores) {//訂單內容頁面查詢的是自商品信息需要*parent_num item.Buy_Num = item.Buy_Num * item.parent_num; } json = "{success:true,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; }
/// <summary> /// 供應商後台:訂單管理>供應商調度出貨>要出的貨物信息 /// </summary> /// <returns></returns> public HttpResponseBase AllOrderDeliver() { uint Normal_Subtotal = 0;//常溫商品總額-- uint Hypothermia_Subtotal = 0;//低溫商品總額--- uint All_Normal_Subtotal = 0;//當日常溫運費總額 uint All_Hypothermia_Subtotal = 0;//當日低溫運費總額 uint Order_Freight_Normal = 0;//常溫運費 uint Order_Freight_Low = 0;//低溫運費 uint All_Order_Freight_Normal = 0;//當日常溫運費-- uint All_Order_Freight_Low = 0;//當日低溫運費-- string json = string.Empty; try { string detail_id = ""; if (!string.IsNullOrEmpty(Request.Params["rowIDs"])) { detail_id = Request.Params["rowIDs"]; } detail_id = detail_id.TrimEnd(',');//去掉最後一個分割符號 string[] Sid_Row = detail_id.Split(',');//拆分 string Sid = Sid_Row[Sid_Row.Length - 1];//獲取最後一個計算出出貨時間 //通過sid去order_slave查詢order_id; //通過order_id查詢在order_master中order_date_pay。 OrderSlaveQuery query = new OrderSlaveQuery(); query.Slave_Id = uint.Parse(Sid); _IOrderSlaveMgr = new OrderSlaveMgr(mySqlConnectionString); DateTime time = _IOrderSlaveMgr.GetOrderDatePay(query).order_date_pay; long endtime = CommonFunction.GetPHPTime(time.ToString("yyyy/MM/dd 23:59:59")); uint vendor_id = (Session["vendor"] as BLL.gigade.Model.Vendor).vendor_id; //(計算這些數據參照自vendor.gigade100.com/order/all_order_deliver.php 第209~266行) #region 計算供應商當天的常溫和冷凍的總運費 List<OrderDetailQuery> query1 = new List<OrderDetailQuery>(); _OrderDetailMgr = new OrderDetailMgr(mySqlConnectionString); query1 = _OrderDetailMgr.GetOrderDetailToSum(vendor_id, endtime); foreach (var item in query1) { if (item.item_mode == 1) continue; if (item.Product_Freight_Set == PRODUCT_FREIGHT_NORMAL || item.Product_Freight_Set == PRODUCT_FREIGHT_NO_NORMAL)//常溫,常溫免運 { if (item.item_mode == 2)//組合商品 { All_Normal_Subtotal += item.Single_Price * item.parent_num; } else { All_Normal_Subtotal += item.Single_Price * item.Buy_Num; } } else if (item.Product_Freight_Set == PRODUCT_FREIGHT_LOW || item.Product_Freight_Set == PRODUCT_FREIGHT_NO_LOW) //冷凍,冷凍免運 { if (item.item_mode == 2)//組合商品 { All_Hypothermia_Subtotal += item.Single_Price * item.parent_num; } else { All_Hypothermia_Subtotal += item.Single_Price * item.Buy_Num; } } } #endregion #region 計算運費 OrderDetailQuery oddquery = new OrderDetailQuery(); oddquery.Vendor_Id = vendor_id; _OrderDetailMgr = new OrderDetailMgr(mySqlConnectionString); int total = 0; string sq = string.Format(" AND os.slave_id in ({0}) ORDER BY od.slave_id ASC ,od. combined_mode ASC , od.item_mode ASC ", detail_id); List<OrderDetailQuery> query2 = new List<OrderDetailQuery>(); oddquery.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 oddquery.Limit = Convert.ToInt32(Request.Params["limit"] ?? "10");//用於分頁的變量 query1 = _OrderDetailMgr.AllOrderDeliver(oddquery, out total, sq); //uint Normal_Subtotal = 0;//常溫商品總額-- //uint Hypothermia_Subtotal = 0;//低溫商品總額--- //uint All_Normal_Subtotal = 0;//當日常溫運費總額 //uint All_Hypothermia_Subtotal = 0;//當日低溫運費總額 //uint Order_Freight_Normal = 0;//常溫運費 //uint Order_Freight_Low = 0;//低溫運費 //uint All_Order_Freight_Normal = 0;//當日常溫運費-- //uint All_Order_Freight_Low = 0;//當日低溫運費-- foreach (var item in query1) { if (item.item_mode == 1) continue; //商品總額計算(當下出貨金額與運費) if (item.Product_Freight_Set == PRODUCT_FREIGHT_NORMAL || item.Product_Freight_Set == PRODUCT_FREIGHT_NO_NORMAL)//常溫,常溫免運 { if (item.item_mode == 2) { Normal_Subtotal += item.Single_Price * item.parent_num; item.Buy_Num = item.Buy_Num * item.parent_num; item.Single_Price = item.Single_Price * item.parent_num; //Normal_Subtotal += item.Single_Price * item.parent_num;//--- //item.Buy_Num = item.Buy_Num * item.parent_num;//------- //item.Single_Money = item.Single_Price * item.Buy_Num;//--------- } else { Normal_Subtotal += item.Single_Price * item.Buy_Num; item.Single_Money = item.Single_Price * item.Buy_Num; } } else if (item.Product_Freight_Set == PRODUCT_FREIGHT_LOW || item.Product_Freight_Set == PRODUCT_FREIGHT_NO_LOW)//低溫,低溫免運 { if (item.item_mode == 2) { Hypothermia_Subtotal += item.Single_Price * item.parent_num; item.Buy_Num = item.Buy_Num * item.parent_num; item.Single_Price = item.Single_Price * item.parent_num; //Hypothermia_Subtotal += item.Single_Price * item.parent_num;//-------- //item.Buy_Num = item.Buy_Num * item.parent_num;//------------ //item.Single_Money = item.Single_Price * item.Buy_Num;//--------- } else { Hypothermia_Subtotal += item.Single_Price * item.Buy_Num; item.Single_Money = item.Single_Price * item.Buy_Num; } } query2.Add(item); } _VendorMgr = new VendorMgr(mySqlConnectionString); BLL.gigade.Model.Vendor vendor = new BLL.gigade.Model.Vendor(); vendor.vendor_id = vendor_id; vendor = _VendorMgr.GetSingle(vendor); //當下運費計算 if (Normal_Subtotal != 0) { if (vendor.freight_normal_limit > Normal_Subtotal) { Order_Freight_Normal = vendor.freight_normal_money; } } if (Hypothermia_Subtotal != 0) { if (vendor.freight_low_limit > Hypothermia_Subtotal && Hypothermia_Subtotal > 0) { Order_Freight_Low = vendor.freight_low_money; } } //批次單總額運費 if (All_Normal_Subtotal != 0) { if (vendor.freight_normal_limit > All_Normal_Subtotal) { All_Order_Freight_Normal = vendor.freight_normal_money; } } if (All_Hypothermia_Subtotal != 0) { if (vendor.freight_low_limit > All_Order_Freight_Low) { All_Order_Freight_Low = vendor.freight_low_money; } } #endregion IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd"; json = "{success:true,Normal_Subtotal:" + Normal_Subtotal + ",Hypothermia_Subtotal:" + Hypothermia_Subtotal + ",Order_Freight_Normal:" + Order_Freight_Normal + ",Order_Freight_Low:" + Order_Freight_Low; json += ",All_Order_Freight_Normal:" + All_Order_Freight_Normal + ",All_Order_Freight_Low:" + All_Order_Freight_Low + ",data:" + JsonConvert.SerializeObject(query2, 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,msg:0}"; } this.Response.Clear(); this.Response.Write(json); this.Response.End(); return this.Response; }