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);
}
}
public IupcMgr(string connectionString)
{
_iupcdao = new IupcDao(connectionString);
}
