/// <summary> /// 業績明細查詢 /// </summary> /// <param name="query"></param> /// <returns></returns> public List<VendorAccountCustom> VendorAccountDetailExport(VendorAccountDetailQuery query) { try { return _Ivendor.VendorAccountDetailExport(query); } catch (Exception ex) { throw new Exception("VendorAccountMonthMgr-->VendorAccountDetailExport-->" + ex.Message, ex); } }
/// <summary> /// 查詢供應商業績明細 /// </summary> /// <param name="store"></param> /// <param name="totalCount"></param> /// <returns></returns> public List<VendorAccountDetailQuery> GetVendorAccountMonthDetailList(VendorAccountDetailQuery store, out int totalCount) { try { return _Ivendor.GetVendorAccountMonthDetailList(store, out totalCount); } catch (Exception ex) { throw new Exception("VendorAccountMonthMgr-->GetVendorAccountMonthDetailList-->" + ex.Message, ex); } }
/// <summary> /// 查詢應稅、免稅金額計算 /// </summary> /// <param name="query"></param> /// <returns></returns> public DataTable GetTaxMoney(VendorAccountDetailQuery query) { try { return _Ivendor.GetTaxMoney(query); } catch (Exception ex) { throw new Exception("VendorAccountMonthMgr-->GetTaxMoney-->" + ex.Message, ex); } }
/// <summary> /// 查詢調度倉運費計算 /// </summary> /// <param name="query"></param> /// <returns></returns> public DataTable GetFreightMoney(VendorAccountDetailQuery query, out int tempFreightDelivery_Normal, out int tempFreightDelivery_Low) { try { return _Ivendor.GetFreightMoney(query, out tempFreightDelivery_Normal, out tempFreightDelivery_Low); } catch (Exception ex) { throw new Exception("VendorAccountMonthMgr-->GetFreightMoney-->" + ex.Message, ex); } }
/// <summary> /// 批次出貨單明細 /// </summary> /// <param name="query"></param> /// <returns></returns> public DataTable BatchOrderDetail(VendorAccountDetailQuery query) { try { return _Ivendor.BatchOrderDetail(query); } catch (Exception ex) { throw new Exception("VendorAccountMonthMgr-->BatchOrderDetail-->" + ex.Message, ex); } }
/// <summary> /// 總表明細 /// </summary> public void ExportVendorAccountMonthAll() { string json = string.Empty; DataTable _dt = new DataTable(); DataTable dtHZ = new DataTable(); VendorAccountDetailQuery query = new VendorAccountDetailQuery(); VendorAccountMonthQuery VAMQuery = new VendorAccountMonthQuery(); VendorQuery vendorQuery = new VendorQuery(); List<DataTable> Elist = new List<DataTable>(); List<string> NameList = new List<string>(); List<bool> comName = new List<bool>(); try { List<string> list = GetTime(uint.Parse(Request.Params["dateone"]), uint.Parse(Request.Params["datetwo"])); _IVAMMgr = new VendorAccountMonthMgr(mySqlConnectionString); VAMQuery.account_year = Convert.ToUInt32(Request.Params["dateOne"]); VAMQuery.account_month = Convert.ToUInt32(Request.Params["dateTwo"]); query.search_start_time = list[0]; query.search_end_time = list[1]; int tempFreightDelivery_Normal = 0; int tempFreightDelivery_Low = 0; for (int i = 1; i <= 34; i++) { dtHZ.Columns.Add("", typeof(String)); } //供應商信息 DataTable vendorDt = _IVAMMgr.GetVendorAccountMonthInfo(VAMQuery); DataTable dtPiCi = _IVAMMgr.BatchOrderDetail(query); for (int m = 0; m < vendorDt.Rows.Count; m++) { vendorQuery.vendor_id = Convert.ToUInt32(vendorDt.Rows[m]["vendor_id"]); VAMQuery.vendor_id = Convert.ToUInt32(vendorDt.Rows[m]["vendor_id"]); query.vendor_id = Convert.ToUInt32(vendorDt.Rows[m]["vendor_id"]); //調度倉運費 DataTable dt = _IVAMMgr.GetFreightMoney(query, out tempFreightDelivery_Normal, out tempFreightDelivery_Low); // 查供應商總帳 DataTable tempTemp = _IVAMMgr.GetVendorAccountMonthZongZhang(VAMQuery); //供應商信息 vendorQuery = _IVAMMgr.GetVendorInfoByCon(vendorQuery); dtHZ = GetTitle(dtHZ, vendorQuery, tempTemp, tempFreightDelivery_Normal, tempFreightDelivery_Low); List<VendorAccountCustom> liStore = _IVAMMgr.VendorAccountDetailExport(query); dtHZ = GetData(dtHZ, liStore, 2, tempTemp, tempFreightDelivery_Normal, tempFreightDelivery_Low);//1:單獨 2:全部 3:批次 } comName.Add(false); Elist.Add(dtHZ); NameList.Add("應付金額與商品明細"); DataTable dtYF = new DataTable(); if (dtPiCi.Rows.Count > 0) { dtYF.Columns.Add("批次出貨單號"); dtYF.Columns.Add("常溫商品總額"); dtYF.Columns.Add("低溫商品總額"); dtYF.Columns.Add("批次出貨明細"); dtYF.Columns.Add("廠商出貨單編號"); dtYF.Columns.Add("出貨時間"); dtYF.Columns.Add("付款單號"); for (int s = 0; s < dtPiCi.Rows.Count; s++) { DataRow yfDr = dtYF.NewRow(); yfDr[0] = dtPiCi.Rows[s]["code_num"]; yfDr[1] = dtPiCi.Rows[s]["normal_subtotal"]; yfDr[2] = dtPiCi.Rows[s]["hypothermia_subtotal"]; yfDr[3] = dtPiCi.Rows[s]["code_num"]; yfDr[4] = dtPiCi.Rows[s]["slave_id"]; yfDr[5] = dtPiCi.Rows[s]["deliver_time"]; yfDr[6] = dtPiCi.Rows[s]["order_id"]; dtYF.Rows.Add(yfDr); } DataRow tempdr1 = dtYF.NewRow(); tempdr1[0] = "常溫運費補貼:" + tempFreightDelivery_Normal; dtYF.Rows.Add(tempdr1); DataRow tempdr2 = dtYF.NewRow(); tempdr2[0] = "低溫運費補貼:" + tempFreightDelivery_Normal; dtYF.Rows.Add(tempdr2); comName.Add(true); Elist.Add(dtYF); NameList.Add("調度倉運費"); } if (dtHZ.Rows.Count > 0) { string fileName = "供應商對賬報表-" + VAMQuery.account_year + "-" + VAMQuery.account_month + ".xls"; MemoryStream ms = ExcelHelperXhf.ExportDTNoColumns(Elist, NameList, comName); Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); Response.BinaryWrite(ms.ToArray()); } else { Response.Write("匯出數據不存在"); } } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); json = "{success:false,totalCount:0,data:[]}"; } }
/// <summary> /// 批次匯出各供應商的業績明細 /// </summary> public HttpResponseBase AllExportVendorAccountMonthDetail() { string json = string.Empty; List<string> strPath = new List<string>(); VendorQuery vendorQuery = new VendorQuery(); try { List<string> list = GetTime(uint.Parse(Request.Params["dateone"]), uint.Parse(Request.Params["datetwo"])); _IVAMMgr = new VendorAccountMonthMgr(mySqlConnectionString); //Dictionary<int, DataTable> tempDT = new Dictionary<int, DataTable>(); VendorAccountMonthQuery VAMQueryTemp = new VendorAccountMonthQuery(); VAMQueryTemp.account_year = Convert.ToUInt32(Request.Params["dateOne"]); VAMQueryTemp.account_month = Convert.ToUInt32(Request.Params["dateTwo"]); DataTable vendorDt = _IVAMMgr.GetVendorAccountMonthInfo(VAMQueryTemp); for (int l = 0; l < vendorDt.Rows.Count; l++) { DataTable dtHZ = new DataTable(); VendorAccountDetailQuery query = new VendorAccountDetailQuery(); VendorAccountMonthQuery VAMQuery = new VendorAccountMonthQuery(); int tempFreightDelivery_Normal = 0; int tempFreightDelivery_Low = 0; vendorQuery.vendor_id = Convert.ToUInt32(vendorDt.Rows[l]["vendor_id"]); VAMQuery.vendor_id = Convert.ToUInt32(vendorDt.Rows[l]["vendor_id"]); VAMQuery.account_year = VAMQueryTemp.account_year; VAMQuery.account_month = VAMQueryTemp.account_month; query.vendor_id = Convert.ToUInt32(vendorDt.Rows[l]["vendor_id"]); query.search_start_time = list[0]; query.search_end_time = list[1]; //調度倉運費 DataTable dt = _IVAMMgr.GetFreightMoney(query, out tempFreightDelivery_Normal, out tempFreightDelivery_Low); // 查供應商總帳 DataTable tempTemp = _IVAMMgr.GetVendorAccountMonthZongZhang(VAMQuery); DataTable dtPiCi = _IVAMMgr.BatchOrderDetail(query); //tempDT.Add(0, tempTemp); //供應商信息 vendorQuery = _IVAMMgr.GetVendorInfoByCon(vendorQuery); for (int i = 1; i <= 34; i++) { dtHZ.Columns.Add("", typeof(String)); } dtHZ = GetTitle(dtHZ, vendorQuery, tempTemp, tempFreightDelivery_Normal, tempFreightDelivery_Low); List<VendorAccountCustom> liStore = _IVAMMgr.VendorAccountDetailExport(query); dtHZ = GetData(dtHZ, liStore, 3, tempTemp, tempFreightDelivery_Normal, tempFreightDelivery_Low);//1:單獨 2:全部 3:批次 List<DataTable> Elist = new List<DataTable>(); List<string> NameList = new List<string>(); List<bool> comName = new List<bool>(); comName.Add(false); Elist.Add(dtHZ); NameList.Add("對賬報表"); DataTable dtYF = GetDZ(dt, dtPiCi, tempFreightDelivery_Normal, tempFreightDelivery_Low); comName.Add(true); Elist.Add(dtYF); NameList.Add("調度倉運費"); if (liStore.Count > 0) { string fileName = vendorQuery.vendor_id + "-" + vendorQuery.vendor_code + "-供應商對帳報表" + vendorQuery.vendor_name_full + VAMQuery.account_year + "-" + VAMQuery.account_month + ".xls"; MemoryStream ms = ExcelHelperXhf.ExportDTNoColumns(Elist, NameList, comName); //MemoryStream m = new MemoryStream(); FileStream fs = new FileStream(Server.MapPath("../ImportUserIOExcel/" + fileName), FileMode.OpenOrCreate); BinaryWriter w = new BinaryWriter(fs); w.Write(ms.ToArray()); fs.Close(); ms.Close(); strPath.Add(Server.MapPath("../ImportUserIOExcel/" + fileName)); } } string strZipPath = Server.MapPath("../ImportUserIOExcel/供應商對賬報表.zip"); string strZipTopDirectoryPath = Server.MapPath("../ImportUserIOExcel/"); int intZipLevel = 6; string strPassword = ""; SharpZipLibHelp szlh = new SharpZipLibHelp(); szlh.Zip(strZipPath, strZipTopDirectoryPath, intZipLevel, strPassword, strPath); json = "{success:'true'}"; } 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 GetVendorAccountMonthDetail() { VendorAccountDetailQuery query = new VendorAccountDetailQuery(); List<VendorAccountDetailQuery> stores = new List<VendorAccountDetailQuery>(); List<string> list = GetTime(uint.Parse(Request.Params["dateone"]), uint.Parse(Request.Params["datetwo"])); query.vendor_id = uint.Parse(Request.Params["vendorid"]); query.search_start_time = list[0]; query.search_end_time = list[1]; string json = string.Empty; try { query.Start = Convert.ToInt32(Request.Params["start"] ?? "0");//用於分頁的變量 query.Limit = Convert.ToInt32(Request.Params["limit"] ?? "20");//用於分頁的變量 _IVAMMgr = new VendorAccountMonthMgr(mySqlConnectionString); _iupc = new IupcMgr(mySqlConnectionString); int totalCount = 0; stores = _IVAMMgr.GetVendorAccountMonthDetailList(query, out totalCount); foreach (var item in stores) { item.order_createdates = CommonFunction.GetNetTime(item.order_createdate); item.account_dates = CommonFunction.GetNetTime(item.account_date); item.slave_date_deliverys = CommonFunction.GetNetTime(item.slave_date_delivery); if (item.item_mode == 2) { item.buy_num *= item.parent_num; } item.search_start_time = query.search_start_time; item.search_end_time = query.search_end_time; if (item.item_mode == 1) { item.item_id = 0; item.upc_id = ""; } else { item.upc_id = _iupc.Getupc(item.item_id.ToString(), "1"); } } IsoDateTimeConverter timeConverter = new IsoDateTimeConverter(); //这里使用自定义日期格式,如果不使用的话,默认是ISO8601格式 timeConverter.DateTimeFormat = "yyyy-MM-dd HH:mm:ss"; //listUser是准备转换的对象 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 void ExportVendorAccountMonthDetail() { string json = string.Empty; DataTable _dt = new DataTable(); DataTable dtHZ = new DataTable(); VendorAccountDetailQuery query = new VendorAccountDetailQuery(); VendorAccountMonthQuery VAMQuery = new VendorAccountMonthQuery(); VendorQuery vendorQuery = new VendorQuery(); try { List<string> list = GetTime(uint.Parse(Request.Params["dateone"]), uint.Parse(Request.Params["datetwo"])); _IVAMMgr = new VendorAccountMonthMgr(mySqlConnectionString); vendorQuery.vendor_id = Convert.ToUInt32(Request.Params["vendorid"]); VAMQuery.vendor_id = Convert.ToUInt32(Request.Params["vendorid"]); VAMQuery.account_year = Convert.ToUInt32(Request.Params["dateOne"]); VAMQuery.account_month = Convert.ToUInt32(Request.Params["dateTwo"]); query.vendor_id = Convert.ToUInt32(Request.Params["vendorid"]); query.search_start_time = list[0]; query.search_end_time = list[1]; int tempFreightDelivery_Normal = 0; int tempFreightDelivery_Low = 0; for (int i = 1; i <= 34; i++) { dtHZ.Columns.Add("", typeof(String)); } //調度倉運費 DataTable dt = _IVAMMgr.GetFreightMoney(query, out tempFreightDelivery_Normal, out tempFreightDelivery_Low); // 查供應商總帳 DataTable tempTemp = _IVAMMgr.GetVendorAccountMonthZongZhang(VAMQuery); DataTable dtPiCi = _IVAMMgr.BatchOrderDetail(query); //供應商信息 vendorQuery = _IVAMMgr.GetVendorInfoByCon(vendorQuery); dtHZ = GetTitle(dtHZ, vendorQuery, tempTemp, tempFreightDelivery_Normal, tempFreightDelivery_Low); List<VendorAccountCustom> liStore = _IVAMMgr.VendorAccountDetailExport(query); dtHZ = GetData(dtHZ, liStore, 1, tempTemp, tempFreightDelivery_Normal, tempFreightDelivery_Low);//1:單獨 2:全部 3:批次 List<DataTable> Elist = new List<DataTable>(); List<string> NameList = new List<string>(); List<bool> comName = new List<bool>(); comName.Add(false); Elist.Add(dtHZ); NameList.Add("對賬報表"); DataTable dtYF = GetDZ(dt, dtPiCi, tempFreightDelivery_Normal, tempFreightDelivery_Low); comName.Add(true); Elist.Add(dtYF); NameList.Add("調度倉運費"); if (dtHZ.Rows.Count > 0) { string fileName = vendorQuery.vendor_id + "-" + vendorQuery.vendor_code + "-供應商對帳報表" + vendorQuery.vendor_name_full + VAMQuery.account_year + "-" + VAMQuery.account_month + ".xls"; MemoryStream ms = ExcelHelperXhf.ExportDTNoColumns(Elist, NameList, comName); Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName); Response.BinaryWrite(ms.ToArray()); } else { Response.Write("匯出數據不存在"); } } catch (Exception ex) { Log4NetCustom.LogMessage logMessage = new Log4NetCustom.LogMessage(); logMessage.Content = string.Format("TargetSite:{0},Source:{1},Message:{2}", ex.TargetSite.Name, ex.Source, ex.Message); logMessage.MethodName = System.Reflection.MethodBase.GetCurrentMethod().Name; log.Error(logMessage); json = "{success:false,totalCount:0,data:[]}"; } }
/// <summary> /// 查詢應稅、免稅金額計算 /// </summary> /// <param name="query"></param> /// <returns></returns> public DataTable GetTaxMoney(VendorAccountDetailQuery query) { StringBuilder sql = new StringBuilder(); StringBuilder sqlfrom = new StringBuilder(); try { sql.AppendFormat(" SELECT free_tax,tax_amount,tax_type from invoice_master_record WHERE invoice_attribute=1 and order_id in ({0})", query.orderIds); return _access.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("VendorAccountMonthDao-->VendorAccountDetailExport-->" + sql.ToString() + ex.Message, ex); } }
/// <summary> /// 批次出貨單明細 /// </summary> /// <param name="query"></param> /// <returns></returns> public DataTable BatchOrderDetail(VendorAccountDetailQuery query) { StringBuilder sql = new StringBuilder(); StringBuilder sqlfrom = new StringBuilder(); try { sql.AppendFormat(" select osd.slave_id,osm.slave_master_id,osm.code_num,osm.order_freight_normal,osm.order_freight_low,osm.normal_subtotal,osm.hypothermia_subtotal,"); sql.AppendFormat("osm.paper,osm.deliver_store,osm.deliver_code,FROM_UNIXTIME (osm.deliver_time,'%Y/%m/%d') as deliver_time,osm.deliver_note,osm.createdate,osm.creator,osm.on_check,om.order_id "); sqlfrom.AppendFormat(" from order_slave_detail osd left join order_slave os on osd.slave_id = os.slave_id left join "); sqlfrom.AppendFormat(" order_slave_master osm on osd.slave_master_id = osm.slave_master_id left join order_master om on om.order_id = os.order_id "); sqlfrom.AppendFormat(" where 1=1 and osm.deliver_time +(86400 * 10)>={0} and osm.deliver_time+(86400 * 10)<={1} and creator={2} order by osm.deliver_time", query.search_start_time, query.search_end_time, query.vendor_id); sql.AppendFormat(sqlfrom.ToString()); return _access.getDataTable(sql.ToString()); } catch (Exception ex) { throw new Exception("VendorAccountMonthDao-->VendorAccountDetailExport-->" + sql.ToString() + ex.Message, ex); } }
/// <summary> /// 查詢調度倉運費計算 /// </summary> /// <param name="query"></param> /// <returns></returns> public DataTable GetFreightMoney(VendorAccountDetailQuery query, out int tempFreightDelivery_Normal, out int tempFreightDelivery_Low) { tempFreightDelivery_Normal = 0; tempFreightDelivery_Low = 0; StringBuilder sql = new StringBuilder(); try { VendorQuery VQuery = new VendorQuery(); VQuery.vendor_id = query.vendor_id; VendorQuery vendorTemp = GetVendorInfoByCon(VQuery); sql.AppendFormat("select * , sum(normal_subtotal) as gnormal_subtotal , sum(hypothermia_subtotal) as ghypothermia_subtotal from order_slave_master where 1=1 "); sql.AppendFormat(" and deliver_time + (86400 * 10) >={0} and deliver_time + (86400 * 10) <={1} and creator = {2} group by deliver_time ", query.search_start_time, query.search_end_time, query.vendor_id); DataTable temp = _access.getDataTable(sql.ToString()); for (int i = 0; i < temp.Rows.Count; i++) { if (Convert.ToInt32(temp.Rows[i]["gnormal_subtotal"]) != 0) { if (vendorTemp.freight_normal_limit > Convert.ToInt32(temp.Rows[i]["gnormal_subtotal"])) { tempFreightDelivery_Normal += Convert.ToInt32(vendorTemp.freight_normal_money); } } if (Convert.ToInt32(temp.Rows[i]["ghypothermia_subtotal"]) != 0) { if (vendorTemp.freight_low_limit > Convert.ToInt32(temp.Rows[i]["ghypothermia_subtotal"])) { tempFreightDelivery_Low += Convert.ToInt32(vendorTemp.freight_low_money); } } //temp.Rows[i]["Temp_Freight_Delivery_Normal"] = tempFreightDelivery_Normal; //temp.Rows[i]["Temp_Freight_Delivery_Low"] = tempFreightDelivery_Low; } return temp; } catch (Exception ex) { throw new Exception("VendorAccountMonthDao-->GetFreightMoney-->" + sql.ToString() + ex.Message, ex); } }
public List<VendorAccountCustom> VendorAccountDetailExport(VendorAccountDetailQuery query) { StringBuilder sql = new StringBuilder(); StringBuilder sqlfrom = new StringBuilder(); _iupc = new IupcDao(connStr); try { // sql.AppendFormat(" select p.tax_type, vad.slave_id,vad.vendor_id,vad.order_id,vad.creditcard_1_percent,vad.creditcard_3_percent,vad.sales_limit, "); sql.AppendFormat(" select vad.slave_id,vad.vendor_id,vad.order_id,vad.sales_limit, "); sql.AppendFormat(" vad.bonus_percent,vad.freight_low_limit,vad.freight_low_money,vad.freight_normal_limit,vad.freight_normal_money, "); sql.AppendFormat("vad.freight_return_normal_money,vad.product_money,vad.product_cost,vad.money_creditcard_1,vad.money_creditcard_3,"); sql.AppendFormat("vad.freight_delivery_low,vad.freight_delivery_normal,vad.freight_return_low, "); //sql.AppendFormat("vad.freight_return_normal,vad.account_amount,FROM_UNIXTIME (vad.account_date,'%Y/%m/%d') account_date,vad.gift,vad.deduction,vad.bag_check_money,vad.freight_return_low_money,imr.free_tax,imr.total_amount,imr.tax_amount, "); sql.AppendFormat("vad.freight_return_normal,vad.account_amount,account_date,vad.gift,vad.deduction,vad.bag_check_money,vad.freight_return_low_money,imr.free_tax,imr.total_amount,imr.tax_amount, "); sql.AppendFormat("od.detail_id,od.slave_id,od.item_id,od.item_vendor_id,od.item_mode,od.product_freight_set,od.product_mode,od.product_name,od.product_spec_name,od.single_cost,od.deduct_account,od.parent_id,"); //sql.AppendFormat("od.single_price,od.buy_num,od.event_cost,od.single_money,od.detail_status,FROM_UNIXTIME (os.slave_date_delivery,'%Y/%m/%d') slave_date_delivery,os.slave_date_close,om.order_payment,"); sql.AppendFormat("od.single_price,od.buy_num,od.parent_num,od.event_cost,od.single_money,od.detail_status, slave_date_delivery,os.slave_date_close,om.order_payment,"); sql.AppendFormat(" od.deduct_bonus,od.deduct_welfare,od.deduct_happygo_money, "); //sql.AppendFormat(" FROM_UNIXTIME( om.order_createdate,'%Y/%m/%d') order_createdate,om.note_admin,od.bag_check_money as od_bag_check_money,tp.parameterName,tp1.parameterName as product_freight,tp2.remark order_status_name "); sql.AppendFormat(" order_createdate,om.note_admin,od.bag_check_money as od_bag_check_money "); sqlfrom.AppendFormat(" from order_detail od left join order_slave os on od.slave_id = os.slave_id "); sqlfrom.AppendFormat("left join vendor_account_detail vad on vad.slave_id = od.slave_id and vad.vendor_id = od.item_vendor_id "); sqlfrom.AppendFormat("left join order_master om on vad.order_id = om.order_id "); //sqlfrom.AppendFormat("left join vendor v ON vad.vendor_id = v.vendor_id "); // sqlfrom.AppendFormat("left join product_item pi ON pi.item_id = od.item_id "); //sqlfrom.AppendFormat("left join product p ON p.product_id = pi.product_id "); sqlfrom.AppendFormat("left join invoice_master_record imr on om.order_id=imr.order_id and imr.tax_type=1 and invoice_attribute=1 "); // sqlfrom.AppendFormat("left join (select * from t_parametersrc where parameterType='payment') tp on om.order_payment = tp.parameterCode "); //sqlfrom.AppendFormat("left join (select * from t_parametersrc where parameterType='product_freight') tp1 on od.product_freight_set = tp1.parameterCode "); //sqlfrom.AppendFormat("left join (select * from t_parametersrc where parameterType='order_status') tp2 on od.detail_status = tp2.parameterCode "); sqlfrom.AppendFormat(" where 1=1 and vad.vendor_id = {0} and vad.account_date >= {1} and vad.account_date <= {2} and od.detail_status <> 89 ", query.vendor_id, query.search_start_time, query.search_end_time); sqlfrom.AppendFormat(" order by account_date asc, order_id asc,vad.slave_id ASC, od.item_mode asc "); sql.AppendFormat(sqlfrom.ToString()); //return _access.getDataTable(sql.ToString()); IParametersrcImplDao _parameterDao = new ParametersrcDao(connStr); List<Parametersrc> parameterList = _parameterDao.QueryParametersrcByTypes("payment", "product_freight", "order_status"); List<VendorAccountCustom> list = _access.getDataTableForObj<Model.Custom.VendorAccountCustom>(sql.ToString()); IProductItemImplDao _itemDao = new ProductItemDao(connStr); IVendorImplDao _vendordao = new VendorDao(connStr); foreach (VendorAccountCustom q in list) { var alist = parameterList.Find(m => m.ParameterType == "payment" && m.ParameterCode == q.Order_Payment.ToString()); var blist = parameterList.Find(m => m.ParameterType == "product_freight" && m.ParameterCode == q.Product_Freight_Set.ToString()); var clist = parameterList.Find(m => m.ParameterType == "order_status" && m.ParameterCode == q.Detail_Status.ToString()); if (alist != null) { q.paymentname = alist.parameterName; } if (blist != null) { q.product_freight = blist.parameterName; } if (clist != null) { q.order_status_name = clist.remark; } Product p = _itemDao.GetTaxByItem(Convert.ToUInt32(q.Item_Id)); DataTable dt = _vendordao.GetVendorDetail(" and vendor_id=" + q.Item_Vendor_Id + " "); if (p != null) { q.tax_type = p.Tax_Type; } if (dt.Rows.Count != 0) { q.creditcard_1_percent = Convert.ToUInt32(dt.Rows[0]["creditcard_1_percent"].ToString()); // q.creditcard_3_percent = Convert.ToUInt32(dt.Rows[0]["creditcard_3_percent"].ToString()); } q.accountdate = Common.CommonFunction.GetNetTime(q.account_date); q.slavedate_delivery = Common.CommonFunction.GetNetTime(q.slave_date_delivery); q.ordercreatedate = Common.CommonFunction.GetNetTime(q.Order_Createdate); q.upc_id = _iupc.Getupc(q.Item_Id.ToString(), "1"); } return list; } catch (Exception ex) { throw new Exception("VendorAccountMonthDao-->VendorAccountDetailExport-->" + sql.ToString() + ex.Message, ex); } }
/// <summary> /// 查詢供應商業績明細 /// </summary> /// <param name="store"></param> /// <param name="totalCount"></param> /// <returns></returns> public List<VendorAccountDetailQuery> GetVendorAccountMonthDetailList(VendorAccountDetailQuery store, out int totalCount) { StringBuilder sql = new StringBuilder(); StringBuilder sqlfrom = new StringBuilder(); try { sql.AppendFormat(" select vad.account_amount,vad.account_date,vad.bag_check_money,vad.bonus_percent,vad.creditcard_1_percent,vad.creditcard_3_percent, "); sql.AppendFormat(" vad.deduction,vad.freight_delivery_low,vad.freight_delivery_normal,vad.freight_low_limit,vad.freight_low_money,vad.freight_normal_limit, "); sql.AppendFormat("vad.freight_normal_money,vad.freight_return_low,vad.freight_return_low_money,vad.freight_return_normal,vad.freight_return_normal_money,"); sql.AppendFormat("vad.gift,vad.money_creditcard_1,vad.money_creditcard_3, "); sql.AppendFormat("vad.order_id,vad.product_cost,vad.product_money,vad.sales_limit,vad.slave_id,vad.vendor_id,"); //sql.AppendFormat("vad.order_id,vad.product_cost,vad.product_money,vad.sales_limit,vad.slave_id,vad.vendor_id,"); sql.AppendFormat("od.detail_id,od.slave_id,od.item_id,od.product_freight_set,od.product_mode,od.product_name,"); sql.AppendFormat("od.product_spec_name,od.single_cost,od.single_price,od.buy_num,od.parent_num,od.detail_status,od.event_cost,od.single_money,"); sql.AppendFormat("os.slave_date_delivery,os.slave_date_close,om.order_payment,om.order_createdate,od.parent_id,od.pack_id,od.item_mode "); sql.AppendFormat(" from vendor_account_detail vad left join order_detail od on vad.vendor_id = od.item_vendor_id and vad.slave_id = od.slave_id "); sql.AppendFormat(" left join order_slave os on od.slave_id = os.slave_id left join order_master om on vad.order_id = om.order_id "); sqlfrom.AppendFormat(" where 1=1 and vad.vendor_id = {0} and vad.account_date >= {1} and vad.account_date <= {2} and od.detail_status <> 89 ", store.vendor_id, store.search_start_time, store.search_end_time); string str = string.Format(" SELECT count(*) AS search_total from vendor_account_detail vad left join order_detail od on vad.vendor_id = od.item_vendor_id and vad.slave_id = od.slave_id left join order_slave os on od.slave_id = os.slave_id left join order_master om on vad.order_id = om.order_id "); totalCount = 0; if (store.IsPage) { System.Data.DataTable _dt = _access.getDataTable(str + sqlfrom.ToString()); if (_dt != null && _dt.Rows.Count > 0) { totalCount = Convert.ToInt32(_dt.Rows[0]["search_total"]); } sqlfrom.AppendFormat(" order by account_date asc, order_id asc,od.item_mode asc "); sqlfrom.AppendFormat(" limit {0},{1};", store.Start, store.Limit); } List<VendorAccountDetailQuery> list = _access.getDataTableForObj<VendorAccountDetailQuery>(sql.ToString() + sqlfrom.ToString()); return list; } catch (Exception ex) { throw new Exception("VendorAccountMonthDao-->GetVendorAccountMonthDetailList-->" + sqlfrom.ToString() + ex.Message, ex); } }