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