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