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