dengyu
/
DS7
1
0
Fork 0
Code Issues Pull Requests Projects 1 Releases Wiki Activity
You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
dev
DS7_HYRD
YingHang
ShenZhenCHJY
DS7_JinGang
QDHHYT
master
临时发布20240103
DS7_JingHan
DS7Zhanhao
HuanHaiYunTong
shenzhenchjy
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'DS7Zhanhao'
${ noResults }
DS7/DSWeb/Import/DAL/ImportTrade/ImportTradeDAL.cs

1377 lines
85 KiB
C#
Raw Permalink Normal View History Unescape Escape

1
2 years ago
using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using DSWeb.Areas.Import.Models.ImportTrade;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DSWeb.Areas.CommMng.Models;
using System.Web;
using System.Data.Common;
using DSWeb.TruckMng.Comm.Cookie;
using WebSqlHelper;
using HcUtility.Core;
using System.IO;
using System.Data.SqlClient;
using DSWeb.MvcShipping.Models.WMS;
using DSWeb.EntityDA;
namespace DSWeb.Areas.Import.DAL.ImportTrade
{
public partial class ImportTradeDAL
{
#region 合同基础信息列表——包括全部商品信息
static public List<ImportTrademb> GetDataList_ALL(string strCondition, string userid, string username, string companyid, string sort)
{ //合同信息/商品信息 ——每合同一行
var rangstr = GetRangDAStr("index", userid, username, companyid);
if (!string.IsNullOrEmpty(rangstr))
{
if (!string.IsNullOrEmpty(strCondition))
{
strCondition = strCondition + " and " + rangstr;
}
else
{
strCondition = rangstr;
}
}
var strSql = new StringBuilder();
strSql.Append(" select c.GID,c.SEQUENCE,M.id,M.Contractno,M.HTH ");
strSql.Append(" ,M.Mainstate,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=28 and EnumValueID=M.MainState) as MainStateRef ");
strSql.Append(" ,isnull(c.weight_act,0) weight_act ");
strSql.Append(" ,isnull((select sum(weight) from import_saledetail where cargo_gid=c.gid and (select mainstate from import_sales where salesno=import_saledetail.salesno ) <>2 ),0) Selled_Ref ");
strSql.Append(" ,isnull(c.weight_act,0)-isnull((select sum(weight) from import_saledetail where cargo_gid=c.gid and (select mainstate from import_sales where salesno=import_saledetail.salesno ) <>2 ),0) weight_remain ");
strSql.Append(" ,isnull(c.boxcount_act,0) boxcount_act ");
strSql.Append(" ,isnull(c.boxcount_act,0)-isnull((select sum(boxcount) from import_saledetail where cargo_gid=c.gid and (select mainstate from import_sales where salesno=import_saledetail.salesno ) <>2 ),0) boxcount_remain ");
strSql.Append(" ,m.Supplier_Billno,Other_Billno ");
strSql.Append(" ,OP,Agent,Agent2,Agent3,seller,'当前货权' Owner_Ref,Buy_Type,Buy_Op,m.buyer,dbo.trimdate(creattime) creattime ");
strSql.Append(" ,m.countryid ,(select substring(country,0,(charindex('(',country)) ) from code_country where countryid=m.countryid) Country_Ref ");
strSql.Append(" ,port,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=10 and EnumValueID=M.port) port_Ref ");
strSql.Append(" ,ShipCompany_id,ShipCompany_id as ShipCompany_Ref ,Vessel,Voyage,dbo.trimdate(Sailingdate) Sailingdate,dbo.trimdate(m.Arrivaldate) Arrivaldate ");
strSql.Append(" ,BILLNO,m.ContainerNo,m.SealNo,dbo.trimdate(Billtime) Billtime,dbo.trimdate(Audittime) Audittime,Receiptremark,Bill_ExpressNo ");
/* //向代理商付预付款日期
strSql.Append(" ,(select min(CREATETIME) from ch_fee_do where feeid in(select gid from ch_fee where bsno=m.contractno and CUSTOMERNAME=m.Agent and feename='预付款')) Prepaydate ");
//向代理商付尾款日期
strSql.Append(" ,(select min(CREATETIME) from ch_fee_do where feeid in(select gid from ch_fee where bsno=m.contractno and CUSTOMERNAME=m.Agent and feename in ('尾款','购货款'))) Balancepaydate ");
*/
strSql.Append(" ,isnull(dbo.trimtime((select min(settletime) from ch_Fee_settlement where billno in(select billno from ch_fee_do where feeid in(select gid from ch_fee where bsno=m.contractno and (CUSTOMERNAME=m.Agent or CUSTOMERNAME=m.Agent2 or CUSTOMERNAME=m.Agent3) and feename='预付款') and CATEGORY in(1,2,3,8,9)))),'') paydate_ref ");
strSql.Append(" ,isnull(dbo.trimtime((select min(settletime) from ch_Fee_settlement where billno in(select billno from ch_fee_do where feeid in(select gid from ch_fee where bsno=m.contractno and (CUSTOMERNAME=m.Agent or CUSTOMERNAME=m.Agent2 or CUSTOMERNAME=m.Agent3) and feename in('尾款','购货款')) and CATEGORY in(1,2,3,8,9)))),'') paydate_b_ref ");
// ,'' paydate_ref
strSql.Append(", dbo.trimdate(Agents_paydate) Agents_paydate,dbo.trimdate(Agents_b_paydate) Agents_b_paydate,c.price_limit,c.amount,c.pay_amount,c.Exchangerate*c.pay_amount pay_amount_RMBes ");
strSql.Append(" ,c.currid Currency,c.price,dbo.trimdate(c.productiondate_max) productiondate_max,dbo.trimdate(c.productiondate_min) productiondate_min ");
strSql.Append(" ,case isnull(c.weight,0) when 0 then 0 else (c.Exchangerate*c.pay_amount)/c.weight end price_RMBes ");
strSql.Append(" ,TransactionMethod,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=1 and EnumValueID=M.TransactionMethod) as TransactionMethodRef ");
strSql.Append(" ,overfilled,datediff(day,c.productiondate_min,getdate()) cargoDays "); //货龄天数
strSql.Append(" ,c.PreProportion,c.prepayments,(c.prepayments*c.Exchangerate) prepayments_RMBes ");
strSql.Append(" ,case dbo.F_sumTaxAmount(M.contractno) when 0 then 0 else 0-(dbo.f_fee(M.ContractNo,M.Agent,'RMB','预付款')+dbo.f_fee(M.ContractNo,M.Agent2,'RMB','预付款')+dbo.f_fee(M.ContractNo,M.Agent3,'RMB','预付款'))*dbo.f_cargoamount(c.gid)/dbo.f_mainamount(c.contractno) end prepayments_RMB ");
strSql.Append(" ,case isnull(c.prepayments,0) when 0 then 0 else case dbo.F_sumTaxAmount(M.contractno) when 0 then 0 else 0-(((dbo.f_fee(M.ContractNo,M.Agent,'RMB','预付款')+dbo.f_fee(M.ContractNo,M.Agent2,'RMB','预付款')+dbo.f_fee(M.ContractNo,M.Agent3,'RMB','预付款')))/c.prepayments )end end Exchange_Pre ");
//向代理商付预付款日期
strSql.Append(" ,dbo.trimtime((select min(PAYABLETIME) from ch_fee_payapplication where billno in(select billno from ch_fee_do where feeid in(select gid from ch_fee where bsno=m.contractno and (CUSTOMERNAME=m.Agent or CUSTOMERNAME=m.Agent2 or CUSTOMERNAME=m.Agent3) and feename='预付款') and CATEGORY in(4,5)))) Prepaydate_Payapp ");
strSql.Append(" ,dbo.trimtime((select min(settletime) from ch_Fee_settlement where billno in(select billno from ch_fee_do where feeid in(select gid from ch_fee where bsno=m.contractno and (CUSTOMERNAME=m.Agent or CUSTOMERNAME=m.Agent2 or CUSTOMERNAME=m.Agent3) and feename='预付款') and CATEGORY in(1,2,3,8,9)))) Prepaydate_sett ");
//代理商对外预付款日期
strSql.Append(" ,dbo.trimtime((select min(settledate) from CH_FEE_Public where BSNO in (select contractno from import_main_public where Local_ContractNo=m.ContractNo) and FEENAME='预付款')) Prepaydate_Agent ");
strSql.Append(" ,c.Balance ,(c.Balance *c.Exchangerate) Balance_RMBes ");
strSql.Append(" ,case dbo.F_sumTaxAmount(M.contractno) when 0 then 0 else 0-(dbo.f_fee(M.ContractNo,M.Agent,'RMB','尾款')+dbo.f_fee(M.ContractNo,M.Agent2,'RMB','尾款')+dbo.f_fee(M.ContractNo,M.Agent3,'RMB','尾款'))*dbo.f_cargoamount(c.gid)/dbo.f_mainamount(c.contractno) end Balance_RMB ");
//+dbo.f_fee(M.ContractNo,M.Agent,'RMB','购货差额')+dbo.f_fee(M.ContractNo,M.Agent2,'RMB','购货差额')+dbo.f_fee(M.ContractNo,M.Agent3,'RMB','购货差额')
strSql.Append(" ,case isnull(c.Balance,0) when 0 then 0 else case dbo.F_sumTaxAmount(M.contractno) when 0 then 0 else 0-((dbo.f_fee(M.ContractNo,M.Agent,'RMB','尾款')+dbo.f_fee(M.ContractNo,M.Agent2,'RMB','尾款')+dbo.f_fee(M.ContractNo,M.Agent3,'RMB','尾款'))*dbo.f_cargoamount2(c.gid)/dbo.f_mainamount2(c.contractno))/c.Balance end end Exchange_Balance ");
//+dbo.f_fee(M.ContractNo,M.Agent,'RMB','购货差额')+dbo.f_fee(M.ContractNo,M.Agent2,'RMB','购货差额')+dbo.f_fee(M.ContractNo,M.Agent3,'RMB','购货差额')
//向代理商付尾款日期
strSql.Append(" ,dbo.trimtime((select min(PAYABLETIME) from ch_fee_payapplication where billno in(select billno from ch_fee_do where feeid in(select gid from ch_fee where bsno=m.contractno and (CUSTOMERNAME=m.Agent or CUSTOMERNAME=m.Agent2 or CUSTOMERNAME=m.Agent3) and feename in ('尾款','购货款')) and CATEGORY in(4,5)))) Balancepaydate ");
strSql.Append(" ,dbo.trimtime((select min(settletime) from ch_Fee_settlement where billno in(select billno from ch_fee_do where feeid in(select gid from ch_fee where bsno=m.contractno and (CUSTOMERNAME=m.Agent or CUSTOMERNAME=m.Agent2 or CUSTOMERNAME=m.Agent3) and feename in ('尾款','购货款')) and CATEGORY in(1,2,3,8,9)))) Balancepaydate_sett ");
//strSql.Append(" ,dbo.trimtime((select min(CREATETIME) from ch_fee_do where feeid in(select gid from ch_fee where bsno=m.contractno and CUSTOMERNAME=m.Agent and feename in ('尾款','购货款')) and CATEGORY in(4,5))) Balancepaydate ");
//strSql.Append(" ,dbo.trimtime((select min(CREATETIME) from ch_fee_do where feeid in(select gid from ch_fee where bsno=m.contractno and CUSTOMERNAME=m.Agent and feename in ('尾款','购货款')) and CATEGORY in(1,2,3,8,9))) Balancepaydate_sett ");
//代理商对外付尾款日期
strSql.Append(" ,dbo.trimtime((select min(settledate) from CH_FEE_Public where BSNO in (select contractno from import_main_public where Local_ContractNo=m.ContractNo) and FEENAME in ('尾款','购货款'))) Balancepaydate_Agent ");
//strSql.Append(" ,isnull(dbo.trimdate((select min(settledate) from CH_FEE_Public where BSNO in (select contractno from import_main_public where Local_ContractNo=m.ContractNo) and FEENAME in ('尾款','购货款'))),'') ");
//strSql.Append(" + isnull(dbo.trimdate((select min(settledate) from CH_FEE_Public where BSNO in (select contractno from import_main_public where Local_ContractNo=m.ContractNo) and FEENAME='预付款')),'') Agents_paydate ");
strSql.Append(" ,c.tax_1 tax_1_es,c.tax_zz_1 tax_zz_1_es ");
strSql.Append(" ,0-dbo.f_fee(M.ContractNo,M.Agent,'RMB','关税')-dbo.f_fee(M.ContractNo,M.Agent2,'RMB','关税')-dbo.f_fee(M.ContractNo,M.Agent3,'RMB','关税') tax_1 ,0-dbo.f_fee(M.ContractNo,M.Agent,'RMB','增值税')-dbo.f_fee(M.ContractNo,M.Agent2,'RMB','增值税')-dbo.f_fee(M.ContractNo,M.Agent3,'RMB','增值税') tax_zz_1 ");
strSql.Append(" ,0-(dbo.f_fee(M.ContractNo,M.Agent,'RMB','补税')+dbo.f_fee(M.ContractNo,M.Agent,'RMB','退税'))*dbo.f_cargoamount(c.gid)/dbo.f_mainamount(c.contractno) tax_bt ");
strSql.Append(" ,(select dbo.[F_GetFee](c.contractno,2,'关税')+dbo.[F_GetFee](c.contractno,2,'增值税')+dbo.[F_GetFee](c.contractno,2,'补税')-dbo.[F_GetFee](c.contractno,1,'退税')) tax ");
strSql.Append(" ,(c.Exchangerate*c.pay_amount+c.tax_1+c.tax_zz_1) Allcostes ");
strSql.Append(" ,(dbo.[F_GetFee](M.contractno,2,'预付款')+dbo.[F_GetFee](M.contractno,2,'尾款')+dbo.[F_GetFee](M.contractno,2,'购货款') ");//+dbo.[F_GetFee](M.contractno,2,'购货差额')
strSql.Append(" +dbo.[F_GetFee](c.contractno,2,'关税')+dbo.[F_GetFee](c.contractno,2,'增值税')+dbo.[F_GetFee](c.contractno,2,'补税')-dbo.[F_GetFee](c.contractno,1,'退税')+dbo.[F_GetFee](c.contractno,2,'代理费'))*dbo.f_cargoamount(c.gid)/dbo.f_mainamount(c.contractno) AllCost ");
strSql.Append(" ,(dbo.F_GetFee2(M.ContractNo,2,'*')-dbo.[F_GetFee](M.contractno,2,'预付款')-dbo.[F_GetFee](M.contractno,2,'尾款')-dbo.[F_GetFee](M.contractno,2,'购货款')-dbo.[F_GetFee](M.contractno,2,'购货差额') ");
strSql.Append(" -dbo.[F_GetFee](c.contractno,2,'关税')-dbo.[F_GetFee](c.contractno,2,'增值税')-dbo.[F_GetFee](c.contractno,2,'补税')+dbo.[F_GetFee](c.contractno,1,'退税')-dbo.[F_GetFee](c.contractno,2,'代理费'))*dbo.f_cargoamount(c.gid)/dbo.f_mainamount(c.contractno) AllPayment ");
strSql.Append(" ,(dbo.F_GetFee_sett(m.ContractNo,2,'*')-dbo.F_GetFee_sett(m.ContractNo,1,'*'))*dbo.f_cargoamount(c.gid)/dbo.f_mainamount(c.contractno) FundsUsed ");
strSql.Append(" ,BillType,C.CargoType,c.name CargoName ");
strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=6 and EnumValueID=M.Billtype) as BilltypeREF ");
strSql.Append(" ,c.Ename,c.BoxCount,c.KINDPKGS,c.weight,c.Exporter,c.factoryno, convert(bigint ,M.TimeMark) as TimeMark ");
strSql.Append(" ,M.creator,M.remark,M.remark_2,M.remark_3,M.remark_4,M.remark_5,M.remark_6 ");
strSql.Append(" ,(select top 1 costprice_ex from ImportTrade_Cargo where gid=c.gid) BasePricees ");
strSql.Append(" ,(select top 1 costprice from ImportTrade_Cargo where gid=c.gid) BasePrice ");//预计成本价 成本价
//strSql.Append(" ,ic.costprice BasePrice,ic.costprice_ex BasePricees ");//预计成本价 成本价
strSql.Append(" ,dbo.trimdate(tax_date) tax_date,dbo.trimdate(tax_paydate) tax_paydate,Sampling_need,M.DeclareNumber ");
strSql.Append(" ,dbo.trimdate(SamplingDate_Plan) SamplingDate_Plan,dbo.trimdate(SamplingDate_Act) SamplingDate_Act ");
strSql.Append(" ,dbo.trimdate(CustomsReleaseDate) CustomsReleaseDate,dbo.trimdate(RecDate) RecDate ");
strSql.Append(" ,PortDays,dbo.trimdate(inspection_date) inspection_date,dbo.trimdate(DeliveryDate) DeliveryDate,DeliveryAddress,Contacter,Tel ");
strSql.Append(" ,dbo.trimdate(Unsealdate) Unsealdate ");
strSql.Append(" ,case c.SEQUENCE when 1 then (select isnull(sum(amount),0) from CH_FEE_Public where BSNO in (select contractno from import_main_public where Local_ContractNo=m.ContractNo) and feetype=1 ) ");
strSql.Append(" -(select isnull(sum(amount),0) from CH_FEE_Public where BSNO in (select contractno from import_main_public where Local_ContractNo=m.ContractNo) and feetype=2 ) else 0 end public_Amount "); //代理商应收
strSql.Append(" ,case c.SEQUENCE when 1 then(select isnull(sum(settlement),0) from CH_FEE_Public where BSNO in (select contractno from import_main_public where Local_ContractNo=m.ContractNo) and feetype=1 ) ");
strSql.Append(" -(select isnull(sum(settlement),0) from CH_FEE_Public where BSNO in (select contractno from import_main_public where Local_ContractNo=m.ContractNo) and feetype=2 ) else 0 end public_settlement ");//代理商已收
strSql.Append(" ,case c.SEQUENCE when 1 then(select isnull(sum(amount-settlement),0) from CH_FEE_Public where BSNO in (select contractno from import_main_public where Local_ContractNo=m.ContractNo) and feetype=1 ) ");
strSql.Append(" -(select isnull(sum(amount-settlement),0) from CH_FEE_Public where BSNO in (select contractno from import_main_public where Local_ContractNo=m.ContractNo) and feetype=2 ) else 0 end public_remain ");//代理商未收
strSql.Append(" ,case c.SEQUENCE when 1 then(select isnull(sum(amount),0) from ch_fee where bsno=m.contractno and (CUSTOMERNAME=m.Agent or CUSTOMERNAME=m.Agent2 or CUSTOMERNAME=m.Agent3) and FEETYPE=2) ");
strSql.Append(" -(select isnull(sum(amount),0) from ch_fee where bsno=m.contractno and (CUSTOMERNAME=m.Agent or CUSTOMERNAME=m.Agent2 or CUSTOMERNAME=m.Agent3) and FEETYPE=1) else 0 end LOCAL_AMOUNT ");//本地对代理商应付
strSql.Append(" ,case c.SEQUENCE when 1 then(select isnull(sum(settlement),0) from ch_fee where bsno=m.contractno and (CUSTOMERNAME=m.Agent or CUSTOMERNAME=m.Agent2 or CUSTOMERNAME=m.Agent3) and FEETYPE=2) ");
strSql.Append(" -(select isnull(sum(settlement),0) from ch_fee where bsno=m.contractno and (CUSTOMERNAME=m.Agent or CUSTOMERNAME=m.Agent2 or CUSTOMERNAME=m.Agent3) and FEETYPE=1) else 0 end LOCAL_SETTLEMENT ");//本地对代理商已付
strSql.Append(" ,case c.SEQUENCE when 1 then(select isnull(sum(amount-settlement),0) from ch_fee where bsno=m.contractno and (CUSTOMERNAME=m.Agent or CUSTOMERNAME=m.Agent2 or CUSTOMERNAME=m.Agent3) and FEETYPE=2) ");
strSql.Append(" -(select isnull(sum(amount-settlement),0) from ch_fee where bsno=m.contractno and (CUSTOMERNAME=m.Agent or CUSTOMERNAME=m.Agent2 or CUSTOMERNAME=m.Agent3) and FEETYPE=1) else 0 end LOCAL_REMAIN ");//本地对代理商未付
strSql.Append(" ,case c.SEQUENCE when 1 then (select isnull(sum(amount-settlement),0) from CH_FEE_Public where BSNO in (select contractno from import_main_public where Local_ContractNo=m.ContractNo) and feetype=1 ) ");
strSql.Append(" -(select isnull(sum(amount-settlement),0) from CH_FEE_Public where BSNO in (select contractno from import_main_public where Local_ContractNo=m.ContractNo) and feetype=2 ) ");
strSql.Append(" -(select isnull(sum(amount-settlement),0) from ch_fee where bsno=m.contractno and (CUSTOMERNAME=m.Agent or CUSTOMERNAME=m.Agent2 or CUSTOMERNAME=m.Agent3) and FEETYPE=2) ");
strSql.Append(" +(select isnull(sum(amount-settlement),0) from ch_fee where bsno=m.contractno and (CUSTOMERNAME=m.Agent or CUSTOMERNAME=m.Agent2 or CUSTOMERNAME=m.Agent3) and FEETYPE=1) else 0 end DZCE ");//对账差额
strSql.Append(" ,M.LOCK_EXCHANGERATE,isnull(M.EXCHANGERATE,1) EXCHANGERATE,dbo.trimdate(c.ExpirationDate) ExpirationDate,dbo.trimdate(Ex_sailingdate) Ex_sailingdate ");
strSql.Append(" ,dbo.F_ImportTrade_SealInfo(c.GID) SALEINFO,c.SPECIFICATIONS ,c.WEIGHT_HT,c.BOXCOUNT_HT ");
//strSql.Append(" ,w.GOODSRKSL weight_Ref,w.STORAGENAME,w.GOODSPACK,w.GOODSPACKSTOCK GOODSPACKSTOCK_Ref,w.GOODSSTOCK GOODSSTOCK_Ref,datediff(day,w.wmsdate,getdate())+1 StoreDays ");//库龄天数
strSql.Append(",isnull((select sum(GOODSRKSL) from wms where Cargo_gid=c.gid and isnull(ISDELETE,0)=0),0) weight_Ref ");
strSql.Append(",isnull((select top 1 STORAGENAME from wms where Cargo_gid=c.gid and isnull(ISDELETE,0)=0),0) STORAGENAME ");
strSql.Append(",isnull((select sum(GOODSPACK) from wms where Cargo_gid=c.gid and isnull(ISDELETE,0)=0),0) GOODSPACK ");
strSql.Append(",isnull((select sum(GOODSPACKSTOCK) from wms where Cargo_gid=c.gid and isnull(ISDELETE,0)=0),0) GOODSPACKSTOCK_Ref ");
strSql.Append(",isnull((select sum(GOODSSTOCK) from wms where Cargo_gid=c.gid and isnull(ISDELETE,0)=0),0) GOODSSTOCK_Ref ");
strSql.Append(",isnull(datediff(day,(select top 1 wmsdate from wms where Cargo_gid=c.gid and isnull(ISDELETE,0)=0),getdate())+1,0) StoreDays ");
strSql.Append(",dbo.trimdate((select top 1 wmsdate from wms where Cargo_gid=c.gid and isnull(ISDELETE,0)=0)) wmsdate ");
strSql.Append(",M.Inspection_Storage ");//,dbo.trimdate(c.STOCKDATE_1) STOCKDATE_1,dbo.trimdate(c.STOCKDATE_2) STOCKDATE_2,dbo.trimdate(FUNDDATE_1) FUNDDATE_1,dbo.trimdate(FUNDDATE_2) FUNDDATE_2");
strSql.Append(" ,dbo.[F_DeliveryInfo_2](c.gid) DeliveryInfo,dbo.F_PayCompany(m.contractno) PAYCOMPANY,c.MOREORLESS ");
strSql.Append(" ,M.FreeTime,dbo.trimdate(M.prepaydate) prepaydate,dbo.trimdate(M.Paydate) Paydate ");
strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=11 and EnumValueID=c.unit) as UnitRef,c.U_weight ");
strSql.Append(",c.ExpirationDates,M.ispinxiang ");
strSql.Append(" from import_main M ");
strSql.Append(" left join import_cargo c on c.ContractNo=m.ContractNo ");
//strSql.Append(" left join wms w on w.Cargo_gid=C.gid and w.isdelete=0 ");
//strSql.Append(" left join ImportTrade_Cargo ic on ic.gid=c.gid ");
if (!string.IsNullOrEmpty(strCondition))
{
strSql.Append(" where " + strCondition);
}
var sortstring = DatasetSort.Getsortstring(sort);
if (!string.IsNullOrEmpty(sortstring))
{
strSql.Append(" order by " + sortstring);
}
else
{
strSql.Append(" order by M.HTH ");
}
return SetData_ALL(strSql);
}
static public ImportTrademb GetData_ALL(string condition, string userid, string username, string companyid)
{
var list = GetDataList_ALL(condition,userid,username,companyid,"");
if (list.Count > 0)
return list[0];
return new ImportTrademb();
}
private static List<ImportTrademb> SetData_ALL(StringBuilder strSql)
{
var headList = new List<ImportTrademb>();
Database db = DatabaseFactory.CreateDatabase();
SqlCommand cmd =new SqlCommand();
cmd.CommandText =strSql.ToString();
cmd.CommandTimeout = 120000; //要加这一句
using (IDataReader reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
ImportTrademb data = new ImportTrademb();
#region Set DB data to Object
data.ispinxiang = reader["ispinxiang"] == DBNull.Value ? "0" : reader["ispinxiang"].ToString() ;
data.id = Convert.ToDecimal(reader["id"]);
data.ContractNo = Convert.ToString(reader["Contractno"]);
data.HTH = Convert.ToString(reader["HTH"]);
data.Mainstate = Convert.ToString(reader["Mainstate"]);
data.MainstateRef = Convert.ToString(reader["MainStateRef"]);
data.Selled_Ref = Convert.ToString(reader["Selled_Ref"]);
data.Supplier_Billno = Convert.ToString(reader["Supplier_Billno"]);
data.Other_Billno = Convert.ToString(reader["Other_Billno"]);
data.OP = Convert.ToString(reader["OP"]);
data.Agent = Convert.ToString(reader["Agent"]);
data.Agent2 = Convert.ToString(reader["Agent2"]);
data.Agent3 = Convert.ToString(reader["Agent3"]);
data.seller = Convert.ToString(reader["Seller"]);
data.Owner_Ref = Convert.ToString(reader["Owner_Ref"]);
data.Buy_Type = Convert.ToString(reader["Buy_Type"]);
data.Buy_Op = Convert.ToString(reader["Buy_Op"]);
data.buyer = Convert.ToString(reader["buyer"]);
data.creattime = Convert.ToString(reader["Creattime"]);
data.countryid = Convert.ToString(reader["countryid"]);
data.Country_Ref = Convert.ToString(reader["Country_Ref"]);
data.port = Convert.ToString(reader["Port"]);
data.portRef = Convert.ToString(reader["port_Ref"]);
data.ShipCompany_id = Convert.ToString(reader["shipcompany_id"]);
data.shipcompany_Ref = Convert.ToString(reader["shipcompany_Ref"]);
data.Vessel = Convert.ToString(reader["Vessel"]);
data.Voyage = Convert.ToString(reader["Voyage"]);
data.Sailingdate = Convert.ToString(reader["Sailingdate"]);
data.ArrivalDate = Convert.ToString(reader["Arrivaldate"]);
data.BillNo = Convert.ToString(reader["BILLNO"]);
data.ContainerNo = Convert.ToString(reader["ContainerNo"]);
data.SealNo = Convert.ToString(reader["SealNo"]);
data.BillTime = Convert.ToString(reader["Billtime"]);
data.Audittime = Convert.ToString(reader["Audittime"]);
data.Receiptremark = Convert.ToString(reader["Receiptremark"]);
data.Bill_ExpressNo = Convert.ToString(reader["Bill_ExpressNo"]);
data.paydate_ref = Convert.ToString(reader["paydate_ref"]);
data.paydate_b_ref = Convert.ToString(reader["paydate_b_ref"]);
data.Agents_paydate = Convert.ToString(reader["Agents_paydate"]);
data.Agents_b_paydate = Convert.ToString(reader["Agents_b_paydate"]);
data.BillType = Convert.ToString(reader["BillType"]);
data.BillTypeREF = Convert.ToString(reader["BilltypeREF"]);
data.CargoType = Convert.ToString(reader["CargoType"]);
data.CargoName = Convert.ToString(reader["CargoName"]);
data.Ename = Convert.ToString(reader["Ename"]);
data.BoxCount = Convert.ToString(reader["BoxCount"]);
data.KINDPKGS = Convert.ToString(reader["KINDPKGS"]);
data.Weight = Convert.ToString(reader["Weight"]);
data.weight_Ref = Convert.ToString(reader["weight_Ref"]);
data.Exporter = Convert.ToString(reader["Exporter"]);
data.factoryno = Convert.ToString(reader["factoryno"]);
data.productiondate_min = Convert.ToString(reader["productiondate_min"]);
data.productiondate_max = Convert.ToString(reader["productiondate_max"]);
data.TransactionMethod = Convert.ToString(reader["TransactionMethod"]);
data.TransactionMethodRef = Convert.ToString(reader["TransactionMethodRef"]);
data.price_limit = Convert.ToString(reader["price_limit"]);
data.amount = Convert.ToString(reader["amount"]);
data.pay_amount = Convert.ToString(reader["pay_amount"]);
data.pay_amount_RMBes = Convert.ToString(reader["pay_amount_RMBes"]);
data.Currency = Convert.ToString(reader["Currency"]);
data.Price = Convert.ToString(reader["Price"]);
data.price_RMBes = Convert.ToString(reader["price_RMBes"]);
data.STORAGENAME = Convert.ToString(reader["STORAGENAME"]);
data.GOODSPACK = Convert.ToString(reader["GOODSPACK"]);
data.Overfilled = Convert.ToString(reader["Overfilled"]);
data.GOODSPACKSTOCK_Ref = Convert.ToString(reader["GOODSPACKSTOCK_Ref"]);
data.GOODSSTOCK_Ref = Convert.ToString(reader["GOODSSTOCK_Ref"]);
data.StoreDays = Convert.ToString(reader["StoreDays"]);
data.CargoDays = Convert.ToString(reader["CargoDays"]);
data.PreProportion = Convert.ToString(reader["PreProportion"]);
data.Prepayments = Convert.ToString(reader["Prepayments"]);
data.prepayments_RMBes = Convert.ToString(reader["prepayments_RMBes"]);
data.prepayments_RMB = Convert.ToString(reader["prepayments_RMB"]);
data.Exchange_Pre = Convert.ToString(reader["Exchange_Pre"]);
data.Prepaydate_Payapp = Convert.ToString(reader["Prepaydate_Payapp"]);
data.Prepaydate_Agent = Convert.ToString(reader["Prepaydate_Agent"]);
data.Balance = Convert.ToString(reader["Balance"]);
data.Balance_RMBes = Convert.ToString(reader["Balance_RMBes"]);
data.Balance_RMB = Convert.ToString(reader["Balance_RMB"]);
data.Exchange_Balance = Convert.ToString(reader["Exchange_Balance"]);
data.Balancepaydate = Convert.ToString(reader["Balancepaydate"]);
data.Balancepaydate_Agent = Convert.ToString(reader["Balancepaydate_Agent"]);
data.Prepaydate_sett = Convert.ToString(reader["Prepaydate_sett"]);
data.Balancepaydate_sett = Convert.ToString(reader["Balancepaydate_sett"]);
data.AllCost = Convert.ToString(reader["AllCost"]);
data.AllPayment = Convert.ToString(reader["AllPayment"]);
data.FundsUsed = Convert.ToString(reader["FundsUsed"]);
data.BasePrice = Convert.ToString(reader["BasePrice"]);
data.TimeMark = Convert.ToDecimal(reader["TimeMark"]);
data.creator = Convert.ToString(reader["creator"]);
data.remark = Convert.ToString(reader["remark"]);
data.remark_2 = Convert.ToString(reader["remark_2"]);
data.remark_3 = Convert.ToString(reader["remark_3"]);
data.remark_4 = Convert.ToString(reader["remark_4"]);
data.remark_5 = Convert.ToString(reader["remark_5"]);
data.remark_6 = Convert.ToString(reader["remark_6"]);
data.AllCostes = Convert.ToString(reader["AllCostes"]);
data.BasePricees = Convert.ToString(reader["BasePricees"]);
data.tax_1 = Convert.ToString(reader["tax_1"]);
data.tax_zz_1 = Convert.ToString(reader["tax_zz_1"]);
data.tax_1_es = Convert.ToString(reader["tax_1"]);
data.tax_zz_1_es = Convert.ToString(reader["tax_zz_1"]);
data.tax_date = Convert.ToString(reader["tax_date"]);
data.tax_paydate = Convert.ToString(reader["tax_paydate"]);
data.Sampling_need = Convert.ToString(reader["Sampling_need"]);
data.DeclareNumber = Convert.ToString(reader["DeclareNumber"]);
data.SamplingDate_Plan = Convert.ToString(reader["SamplingDate_Plan"]);
data.SamplingDate_Act = Convert.ToString(reader["SamplingDate_Act"]);
data.CustomsReleaseDate = Convert.ToString(reader["CustomsReleaseDate"]);
data.RecDate = Convert.ToString(reader["RecDate"]);
data.PortDays = Convert.ToString(reader["PortDays"]);
data.inspection_date = Convert.ToString(reader["inspection_date"]);
data.Unsealdate = Convert.ToString(reader["Unsealdate"]);
data.DeliveryDate = Convert.ToString(reader["DeliveryDate"]);
data.DeliveryAddress = Convert.ToString(reader["DeliveryAddress"]);
data.Contacter = Convert.ToString(reader["Contacter"]);
data.Tel = Convert.ToString(reader["Tel"]);
data.GID = Convert.ToString(reader["GID"]);
data.SEQUENCE = Convert.ToString(reader["SEQUENCE"]);
data.PUBLIC_AMOUNT = Convert.ToString(reader["PUBLIC_AMOUNT"]);
data.PUBLIC_SETTLEMENT = Convert.ToString(reader["PUBLIC_SETTLEMENT"]);
data.PUBLIC_REMAIN = Convert.ToString(reader["PUBLIC_REMAIN"]);
data.LOCAL_AMOUNT = Convert.ToString(reader["LOCAL_AMOUNT"]);
data.LOCAL_SETTLEMENT = Convert.ToString(reader["LOCAL_SETTLEMENT"]);
data.LOCAL_REMAIN = Convert.ToString(reader["LOCAL_REMAIN"]);
data.WEIGHT_ACT = Convert.ToString(reader["WEIGHT_ACT"]);
data.WEIGHT_REMAIN = Convert.ToString(reader["WEIGHT_REMAIN"]);
data.BOXCOUNT_ACT = Convert.ToString(reader["BOXCOUNT_ACT"]);
data.BOXCOUNT_REMAIN = Convert.ToString(reader["BOXCOUNT_REMAIN"]);
data.WEIGHT_HT = Convert.ToString(reader["WEIGHT_HT"]);
data.BOXCOUNT_HT = Convert.ToString(reader["BOXCOUNT_HT"]);
data.DZCE = Convert.ToString(reader["DZCE"]);
data.LOCK_EXCHANGERATE = Convert.ToString(reader["LOCK_EXCHANGERATE"]);
data.ExpirationDate = Convert.ToString(reader["ExpirationDate"]);
data.Ex_sailingdate = Convert.ToString(reader["Ex_sailingdate"]);
data.EXCHANGERATE = Convert.ToString(reader["EXCHANGERATE"]);
data.SALEINFO = Convert.ToString(reader["SALEINFO"]);
data.SPECIFICATIONS = Convert.ToString(reader["SPECIFICATIONS"]);
data.Inspection_Storage = Convert.ToString(reader["Inspection_Storage"]);
data.WMSDATE = Convert.ToString(reader["WMSDATE"]);
data.DeliveryInfo = Convert.ToString(reader["DeliveryInfo"]);
data.ExpirationDates = Convert.ToInt32(reader["ExpirationDates"] == DBNull.Value ? "0" : reader["ExpirationDates"]);
//data.STOCKDATE_1 = Convert.ToString(reader["STOCKDATE_1"]);
//data.STOCKDATE_2 = Convert.ToString(reader["STOCKDATE_2"]);
//data.FUNDDATE_1 = Convert.ToString(reader["FUNDDATE_1"]);
//data.FUNDDATE_2 = Convert.ToString(reader["FUNDDATE_2"]);
data.PAYCOMPANY = Convert.ToString(reader["PAYCOMPANY"]);
data.MOREORLESS= Convert.ToString(reader["MOREORLESS"]);
if (Convert.ToString(reader["BoxCount"]) != "" && Convert.ToString(reader["BOXCOUNT_ACT"]) != "")
data.MOREORLESSCOUNT = Convert.ToString(Convert.ToDecimal(reader["BoxCount"]) - Convert.ToDecimal(reader["BOXCOUNT_ACT"]));
else data.MOREORLESSCOUNT = "0";
data.Freetime = Convert.ToString(reader["Freetime"]);
data.Prepaydate = Convert.ToString(reader["Prepaydate"]);
data.Paydate = Convert.ToString(reader["Paydate"]);
data.UnitRef = Convert.ToString(reader["UnitRef"]);
data.U_weight = Convert.ToString(reader["U_weight"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
#endregion
#region 合同基础信息列表——包括全部商品信息，但每合同仅一行
/*
static public List<ImportTrademb> GetDataList_ALL_single ( string strCondition )
{ //合同信息/商品信息 ——每合同一行
return SetData_ALL(strSql);
}*/
#endregion
#region 明细部分
#region 货物表 cargo
static public List<Cargomb2> GetCargoList(string Condition)
{
var strSql = new StringBuilder();
strSql.Append(" SELECT c.*,convert(numeric(18,2),c.tax_amount*c.Exchangerate_Customs) tax_amount_RMB, ");
strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=11 and EnumValueID=c.unit) as UnitRef, ");
strSql.Append(" (select verno from tSysEnumValue where LangId=0 and EnumTypeID=11 and EnumValueID=c.unit) as UnitRate, ");
strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=12 and EnumValueID=c.paypart) as paypartRef ");
strSql.Append(" ,ci.code as cicode,ci.name as ciname,curr.codename,curr.defaultrate ");
strSql.Append(" ,ci.code+'_'+ci.name as cicodeandname ");
strSql.Append(" ,ciq.ciqcode as ciqcode,ciq.ciqname as ciqname ");
strSql.Append(" ,ciq.ciqcode+'_'+ciq.ciqname as ciqcodeandname ");
strSql.Append(" ,c.name+'_'+convert(varchar,c.sequence)+' 重量:'+convert(varchar(12),convert(float,c.[weight]/1000)) name_ref ");
strSql.Append(" ,isnull(weight_act,0)-isnull((select sum(isnull(weight,0)) from import_saledetail where CARGO_GID=c.gid and (select mainstate from import_sales where salesno=import_saledetail.salesno ) <>2 ),0) WEIGHT_REMAIN ");
strSql.Append(" ,isnull(boxcount_act,0)-isnull((select sum(isnull(BOXCOUNT,0)) from import_saledetail where CARGO_GID=c.gid and (select mainstate from import_sales where salesno=import_saledetail.salesno ) <>2 ),0) BOXCOUNT_REMAIN ");
strSql.Append(" ,dbo.trimdate(ExpirationDate) ExpirationDate ");
strSql.Append(" ,AGENCRATE,MOREORLESS,SPECIFICATIONS,Exchangerate_Cust,AGENCRATE_Cust ");
strSql.Append(" ,dbo.trimdate(c.STOCKDATE_1) STOCKDATE_1,dbo.trimdate(c.STOCKDATE_2) STOCKDATE_2 ");
strSql.Append(" FROM [Import_cargo] c ");
strSql.Append(" left join [Import_cargoinfo] ci on c.[cargoinfo_id] = ci.id ");
strSql.Append(" left join [Import_cargociq] ciq on c.[cargociq_id] = ciq.id ");
strSql.Append(" left join [code_currency] curr on curr.gid=c.currid ");
strSql.Append(" left join [import_main] m on M.contractno=c.contractno ");
strSql.Append(" left join [code_country] cc on cc.countryid=m.countryid ");
if (!string.IsNullOrEmpty(Condition))
{
strSql.Append(" where " + Condition);
}
strSql.Append(" order by c.sequence " );
return SetCargo(strSql);
}
private static List<Cargomb2> SetCargo(StringBuilder strSql)
{
var headList = new List<Cargomb2>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
Cargomb2 data = new Cargomb2();
#region Set DB data to Object
data.id = Convert.ToString(reader["id"]);
data.ContractNo = Convert.ToString(reader["ContractNo"]);
data.cargoinfo_id = Convert.ToString(reader["cargoinfo_id"]);
data.cargociq_id = Convert.ToString(reader["cargociq_id"]);
data.name = Convert.ToString(reader["name"]);
data.Unit = Convert.ToString(reader["Unit"]);
data.UnitRate = Convert.ToString(reader["UnitRate"]);
data.U_PRICE = Convert.ToString(reader["U_PRICE"]);
data.U_weight = Convert.ToString(reader["U_weight"]);
data.weight = Convert.ToString(reader["weight"]);
data.m_weight = Convert.ToString(reader["m_weight"]);
data.price = Convert.ToString(reader["price"]);
data.price_agio = Convert.ToString(reader["price_agio"]);
data.price_limit = Convert.ToString(reader["price_limit"]);
data.Amount = Convert.ToString(reader["Amount"]);
data.tax_Amount = Convert.ToString(reader["tax_Amount"]);
data.tax_Amount_RMB = Convert.ToString(reader["tax_Amount_RMB"]);
data.pay_Amount = Convert.ToString(reader["pay_Amount"]);
data.BoxCount = Convert.ToString(reader["BoxCount"]);
data.BoxWeight = Convert.ToString(reader["BoxWeight"]);
data.Exporter = Convert.ToString(reader["Exporter"]);
data.FactoryNo = Convert.ToString(reader["FactoryNo"]);
data.Productiondate_min = Convert.ToString(reader["Productiondate_min"]);
data.Productiondate_max = Convert.ToString(reader["Productiondate_max"]);
data.currid = Convert.ToString(reader["currid"]);
// data.currRef = Convert.ToString(reader["currRef"]);
data.prepayments = Convert.ToString(reader["prepayments"]);
data.balance = Convert.ToString(reader["balance"]);
data.Paypart = Convert.ToString(reader["Paypart"]);
data.cicode = Convert.ToString(reader["cicode"]);
data.ciname = Convert.ToString(reader["ciname"]);
data.cicodeandname = Convert.ToString(reader["cicodeandname"]);
data.ciqcode = Convert.ToString(reader["ciqcode"]);
data.ciqname = Convert.ToString(reader["ciqname"]);
data.ciqcodeandname = Convert.ToString(reader["ciqcodeandname"]);
data.UnitRef = Convert.ToString(reader["UnitRef"]);
data.paypartRef = Convert.ToString(reader["paypartRef"]);
data.codename = Convert.ToString(reader["codename"]);
data.defaultrate = Convert.ToString(reader["defaultrate"]);
data.baolv = Convert.ToString(reader["baolv"]);
data.tariff = Convert.ToString(reader["tariff"]);
data.tax = Convert.ToString(reader["tax"]);
data.tax_zz = Convert.ToString(reader["tax_zz"]);
data.Declarenumber = Convert.ToString(reader["Declarenumber"]);
data.Exchangerate = Convert.ToString(reader["Exchangerate"]);
data.Exchangerate_Customs = Convert.ToString(reader["Exchangerate_Customs"]);
data.tax_1 = Convert.ToString(reader["tax_1"]);
data.tax_zz_1 = Convert.ToString(reader["tax_zz_1"]);
data.tax_2 = Convert.ToString(reader["tax_2"]);
data.tax_zz_2 = Convert.ToString(reader["tax_zz_2"]);
data.PreProportion = Convert.ToString(reader["PreProportion"]);
data.AMOUNT_WRITEOFFS = Convert.ToString(reader["AMOUNT_WRITEOFFS"]);
data.NAME_REF = Convert.ToString(reader["NAME_REF"]);
data.SEQUENCE = Convert.ToString(reader["SEQUENCE"]);
data.Ename = Convert.ToString(reader["Ename"]);
data.SBYS = Convert.ToString(reader["SBYS"]);
data.BZTCHNO = Convert.ToString(reader["BZTCHNO"]);
data.GID = Convert.ToString(reader["GID"]);
data.CargoType = Convert.ToString(reader["CargoType"]);
data.KINDPKGS = Convert.ToString(reader["KINDPKGS"]);
data.WEIGHT_ACT = Convert.ToString(reader["WEIGHT_ACT"]);
data.BOXCOUNT_ACT = Convert.ToString(reader["BOXCOUNT_ACT"]);
data.WEIGHT_REMAIN = Convert.ToString(reader["WEIGHT_REMAIN"]);
data.BOXCOUNT_REMAIN = Convert.ToString(reader["BOXCOUNT_REMAIN"]);
data.ExpirationDate = Convert.ToString(reader["ExpirationDate"]);
data.AGENCRATE = Convert.ToString(reader["AGENCRATE"]);
data.MOREORLESS = Convert.ToString(reader["MOREORLESS"]);
data.SPECIFICATIONS = Convert.ToString(reader["SPECIFICATIONS"]);
data.Exchangerate_Cust = Convert.ToString(reader["Exchangerate_Cust"]);
data.AGENCRATE_Cust = Convert.ToString(reader["AGENCRATE_Cust"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
#endregion
static public List<Cargomb2> GetCargoList2 ( string Condition )
{
var strSql = new StringBuilder();
strSql.Append(" SELECT c.*,convert(numeric(18,2),c.tax_amount*c.Exchangerate_Customs) tax_amount_RMB, ");
strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=11 and EnumValueID=c.unit) as UnitRef, ");
strSql.Append(" (select verno from tSysEnumValue where LangId=0 and EnumTypeID=11 and EnumValueID=c.unit) as UnitRate, ");
strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=12 and EnumValueID=c.paypart) as paypartRef, ");
// strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=16 and EnumValueID=c.Financestatus) as FinancestatusRef, ");
strSql.Append(" ci.code as cicode,ci.name as ciname,curr.codename,curr.defaultrate, ");
strSql.Append(" ci.code+'_'+ci.name as cicodeandname, ");
strSql.Append(" ciq.ciqcode as ciqcode,ciq.ciqname as ciqname, ");
strSql.Append(" ciq.ciqcode+'_'+ciq.ciqname as ciqcodeandname, ");
strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 ");
strSql.Append(" and EnumTypeID=9000 and EnumValueID=cc.tariff) as tariff, ");
strSql.Append(" case cc.tariff when 0 then ci.tax_zhg when 1 then ci.tax_pt end tax, ");
strSql.Append(" ci.tax_zz,c.tax_1,c.tax_2,c.tax_zz_1,c.tax_zz_2 ");
strSql.Append(" ,c.name+'_'+convert(varchar,c.sequence)+' 重量:'+convert(varchar(12),convert(float,c.[weight]/1000)) name_ref ");
strSql.Append(" ,Ename,SBYS,BZTCHNO,c.GID,c.CargoType,c.KINDPKGS ");
strSql.Append(" ,isnull(weight_act,0)-isnull((select sum(isnull(weight,0)) from import_saledetail where CARGO_GID=c.gid and (select mainstate from import_sales where salesno=import_saledetail.salesno ) <>2 ),0) WEIGHT_REMAIN ");
strSql.Append(" ,isnull(boxcount_act,0)-isnull((select sum(isnull(BOXCOUNT,0)) from import_saledetail where CARGO_GID=c.gid and (select mainstate from import_sales where salesno=import_saledetail.salesno ) <>2 ),0) BOXCOUNT_REMAIN ");
strSql.Append(" ,ISLOCKED,dbo.trimdate(c.ExpirationDate) ExpirationDate,C.SPECIFICATIONS,c.Exchangerate_Cust,c.AGENCRATE_Cust ");
//strSql.Append(" ,c.AGENCRATE,c.MOREORLESS ");
strSql.Append(" ,dbo.trimdate(c.STOCKDATE_1) STOCKDATE_1,dbo.trimdate(c.STOCKDATE_2) STOCKDATE_2 ");
strSql.Append(" FROM [Import_cargo] c ");
strSql.Append(" left join [Import_cargoinfo] ci on c.[cargoinfo_id] = ci.id ");
strSql.Append(" left join [Import_cargociq] ciq on c.[cargociq_id] = ciq.id ");
strSql.Append(" left join [code_currency] curr on curr.gid=c.currid ");
strSql.Append(" left join [import_main] m on M.contractno=c.contractno ");
strSql.Append(" left join [code_country] cc on cc.countryid=m.countryid ");
if (!string.IsNullOrEmpty(Condition))
{
strSql.Append(" where " + Condition);
}
strSql.Append(" order by c.sequence ");
return SetCargo2(strSql);
}
private static List<Cargomb2> SetCargo2 ( StringBuilder strSql )
{
var headList = new List<Cargomb2>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
Cargomb2 data = new Cargomb2();
#region Set DB data to Object
data.id = Convert.ToString(reader["id"]);
data.ContractNo = Convert.ToString(reader["ContractNo"]);
data.cargoinfo_id = Convert.ToString(reader["cargoinfo_id"]);
data.cargociq_id = Convert.ToString(reader["cargociq_id"]);
data.name = Convert.ToString(reader["name"]);
data.Unit = Convert.ToString(reader["Unit"]);
data.UnitRate = Convert.ToString(reader["UnitRate"]);
data.U_PRICE = Convert.ToString(reader["U_PRICE"]);
data.U_weight = Convert.ToString(reader["U_weight"]);
data.weight = Convert.ToString(reader["weight"]);
data.m_weight = Convert.ToString(reader["m_weight"]);
data.price = Convert.ToString(reader["price"]);
data.price_agio = Convert.ToString(reader["price_agio"]);
data.price_limit = Convert.ToString(reader["price_limit"]);
data.Amount = Convert.ToString(reader["Amount"]);
data.tax_Amount = Convert.ToString(reader["tax_Amount"]);
data.tax_Amount_RMB = Convert.ToString(reader["tax_Amount_RMB"]);
data.pay_Amount = Convert.ToString(reader["pay_Amount"]);
data.BoxCount = Convert.ToString(reader["BoxCount"]);
data.BoxWeight = Convert.ToString(reader["BoxWeight"]);
data.Exporter = Convert.ToString(reader["Exporter"]);
data.FactoryNo = Convert.ToString(reader["FactoryNo"]);
data.Productiondate = Convert.ToString(reader["Productiondate"]);
data.Productiondate_min = Convert.ToString(reader["Productiondate_min"]);
data.Productiondate_max = Convert.ToString(reader["Productiondate_max"]);
data.currid = Convert.ToString(reader["currid"]);
// data.currRef = Convert.ToString(reader["currRef"]);
data.prepayments = Convert.ToString(reader["prepayments"]);
data.balance = Convert.ToString(reader["balance"]);
data.pay_prepayments = Convert.ToString(reader["pay_prepayments"]);
data.pay_balance = Convert.ToString(reader["pay_balance"]);
data.Paypart = Convert.ToString(reader["Paypart"]);
data.cicode = Convert.ToString(reader["cicode"]);
data.ciname = Convert.ToString(reader["ciname"]);
data.cicodeandname = Convert.ToString(reader["cicodeandname"]);
data.ciqcode = Convert.ToString(reader["ciqcode"]);
data.ciqname = Convert.ToString(reader["ciqname"]);
data.ciqcodeandname = Convert.ToString(reader["ciqcodeandname"]);
data.UnitRef = Convert.ToString(reader["UnitRef"]);
data.paypartRef = Convert.ToString(reader["paypartRef"]);
data.codename = Convert.ToString(reader["codename"]);
data.defaultrate = Convert.ToString(reader["defaultrate"]);
data.baolv = Convert.ToString(reader["baolv"]);
data.tariff = Convert.ToString(reader["tariff"]);
data.tax = Convert.ToString(reader["tax"]);
data.tax_CL = Convert.ToString(reader["tax_CL"]);
data.tax_zz = Convert.ToString(reader["tax_zz"]);
data.Declarenumber = Convert.ToString(reader["Declarenumber"]);
data.Exchangerate = Convert.ToString(reader["Exchangerate"]);
data.Exchangerate_Customs = Convert.ToString(reader["Exchangerate_Customs"]);
data.tax_1 = Convert.ToString(reader["tax_1"]);
data.tax_zz_1 = Convert.ToString(reader["tax_zz_1"]);
data.tax_2 = Convert.ToString(reader["tax_2"]);
data.tax_zz_2 = Convert.ToString(reader["tax_zz_2"]);
data.PreProportion = Convert.ToString(reader["PreProportion"]);
data.AMOUNT_WRITEOFFS = Convert.ToString(reader["AMOUNT_WRITEOFFS"]);
data.NAME_REF = Convert.ToString(reader["NAME_REF"]);
data.SEQUENCE = Convert.ToString(reader["SEQUENCE"]);
data.Ename = Convert.ToString(reader["Ename"]);
data.SBYS = Convert.ToString(reader["SBYS"]);
data.BZTCHNO = Convert.ToString(reader["BZTCHNO"]);
data.GID = Convert.ToString(reader["GID"]);
data.CargoType = Convert.ToString(reader["CargoType"]);
data.KINDPKGS = Convert.ToString(reader["KINDPKGS"]);
data.WEIGHT_ACT = Convert.ToString(reader["WEIGHT_ACT"]);
data.WEIGHT_REMAIN = Convert.ToString(reader["WEIGHT_REMAIN"]);
data.BOXCOUNT_ACT = Convert.ToString(reader["BOXCOUNT_ACT"]);
data.BOXCOUNT_REMAIN = Convert.ToString(reader["BOXCOUNT_REMAIN"]);
data.WEIGHT_HT = Convert.ToString(reader["WEIGHT_HT"]);
data.BOXCOUNT_HT = Convert.ToString(reader["BOXCOUNT_HT"]);
data.ISLOCKED = Convert.ToString(reader["ISLOCKED"]);
data.ExpirationDate = Convert.ToString(reader["ExpirationDate"]);
data.AGENCRATE = Convert.ToString(reader["AGENCRATE"]);
data.MOREORLESS = Convert.ToString(reader["MOREORLESS"]);
data.pay_prepayments = Convert.ToString(reader["pay_prepayments"]);
data.pay_balance = Convert.ToString(reader["pay_balance"]);
data.SPECIFICATIONS = Convert.ToString(reader["SPECIFICATIONS"]);
data.Exchangerate_Cust = Convert.ToString(reader["Exchangerate_Cust"]);
data.AGENCRATE_Cust = Convert.ToString(reader["AGENCRATE_Cust"]);
data.STOCKDATE_1 = Convert.ToString(reader["STOCKDATE_1"]);
data.STOCKDATE_2 = Convert.ToString(reader["STOCKDATE_2"]);
data.ExpirationDates = Convert.ToInt32(reader["ExpirationDates"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
static public List<Cargomb3> GetCargoList3 ( string Condition )
{
var strSql = new StringBuilder();
strSql.Append(" SELECT c.*,convert(numeric(18,2),c.tax_amount*c.Exchangerate_Customs) tax_amount_RMB, ");
strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=11 and EnumValueID=c.unit) as UnitRef, ");
strSql.Append(" (select verno from tSysEnumValue where LangId=0 and EnumTypeID=11 and EnumValueID=c.unit) as UnitRate, ");
strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=12 and EnumValueID=c.paypart) as paypartRef, ");
// strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=16 and EnumValueID=c.Financestatus) as FinancestatusRef, ");
strSql.Append(" ci.code as cicode,ci.name as ciname,curr.codename,curr.defaultrate, ");
strSql.Append(" ci.code+'_'+ci.name as cicodeandname, ");
strSql.Append(" ciq.ciqcode as ciqcode,ciq.ciqname as ciqname, ");
strSql.Append(" ciq.ciqcode+'_'+ciq.ciqname as ciqcodeandname, ");
strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 ");
strSql.Append(" and EnumTypeID=9000 and EnumValueID=cc.tariff) as tariff, ");
strSql.Append(" case cc.tariff when 0 then ci.tax_zhg when 1 then ci.tax_pt end tax, ");
strSql.Append(" ci.tax_zz,c.tax_1,c.tax_2,c.tax_zz_1,c.tax_zz_2 ");
strSql.Append(" ,c.name+'_'+convert(varchar,c.sequence)+' 重量:'+convert(varchar(12),convert(float,c.[weight]/1000)) name_ref ");
strSql.Append(" ,Ename,SBYS,BZTCHNO,c.GID,c.CargoType,c.KINDPKGS ");
strSql.Append(" ,isnull(weight_act,0)-isnull((select sum(isnull(weight,0)) from import_saledetail where CARGO_GID=c.gid and (select mainstate from import_sales where salesno=import_saledetail.salesno ) <>2 ),0) WEIGHT_REMAIN ");
strSql.Append(" ,isnull(boxcount_act,0)-isnull((select sum(isnull(BOXCOUNT,0)) from import_saledetail where CARGO_GID=c.gid and (select mainstate from import_sales where salesno=import_saledetail.salesno ) <>2 ),0) BOXCOUNT_REMAIN ");
strSql.Append(" ,ISLOCKED,dbo.trimdate(c.ExpirationDate) ExpirationDate,C.SPECIFICATIONS,c.Exchangerate_Cust,c.AGENCRATE_Cust ");
strSql.Append(" ,dbo.trimdate(c.STOCKDATE_1) STOCKDATE_1,dbo.trimdate(c.STOCKDATE_2) STOCKDATE_2 ");
strSql.Append(" ,m.HTH,m.ContainerNo,[dbo].[F_ImportTrade_SealInfo](c.gid) SALEINFO,m.buyer ");
strSql.Append(" ,M.Mainstate,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=28 and EnumValueID=M.MainState) as MainStateRef ");
strSql.Append(" ,M.Inspection_Storage,dbo.trimdate(M.inspection_date) inspection_date ");
strSql.Append(" ,dbo.trimdate(M.Unsealdate) Unsealdate ");
strSql.Append(" FROM [Import_cargo] c ");
strSql.Append(" left join [Import_cargoinfo] ci on c.[cargoinfo_id] = ci.id ");
strSql.Append(" left join [Import_cargociq] ciq on c.[cargociq_id] = ciq.id ");
strSql.Append(" left join [code_currency] curr on curr.gid=c.currid ");
strSql.Append(" left join [import_main] m on M.contractno=c.contractno ");
strSql.Append(" left join [code_country] cc on cc.countryid=m.countryid ");
if (!string.IsNullOrEmpty(Condition))
{
strSql.Append(" where " + Condition);
}
strSql.Append(" order by c.sequence ");
return SetCargo3(strSql);
}
private static List<Cargomb3> SetCargo3 ( StringBuilder strSql )
{
var headList = new List<Cargomb3>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
Cargomb3 data = new Cargomb3();
#region Set DB data to Object
data.id = Convert.ToString(reader["id"]);
data.ContractNo = Convert.ToString(reader["ContractNo"]);
data.cargoinfo_id = Convert.ToString(reader["cargoinfo_id"]);
data.cargociq_id = Convert.ToString(reader["cargociq_id"]);
data.name = Convert.ToString(reader["name"]);
data.Unit = Convert.ToString(reader["Unit"]);
data.UnitRate = Convert.ToString(reader["UnitRate"]);
data.U_PRICE = Convert.ToString(reader["U_PRICE"]);
data.U_weight = Convert.ToString(reader["U_weight"]);
data.weight = Convert.ToString(reader["weight"]);
data.m_weight = Convert.ToString(reader["m_weight"]);
data.price = Convert.ToString(reader["price"]);
data.price_agio = Convert.ToString(reader["price_agio"]);
data.price_limit = Convert.ToString(reader["price_limit"]);
data.Amount = Convert.ToString(reader["Amount"]);
data.tax_Amount = Convert.ToString(reader["tax_Amount"]);
data.tax_Amount_RMB = Convert.ToString(reader["tax_Amount_RMB"]);
data.pay_Amount = Convert.ToString(reader["pay_Amount"]);
data.BoxCount = Convert.ToString(reader["BoxCount"]);
data.BoxWeight = Convert.ToString(reader["BoxWeight"]);
data.Exporter = Convert.ToString(reader["Exporter"]);
data.FactoryNo = Convert.ToString(reader["FactoryNo"]);
data.Productiondate = Convert.ToString(reader["Productiondate"]);
data.Productiondate_min = Convert.ToString(reader["Productiondate_min"]);
data.Productiondate_max = Convert.ToString(reader["Productiondate_max"]);
data.currid = Convert.ToString(reader["currid"]);
// data.currRef = Convert.ToString(reader["currRef"]);
data.prepayments = Convert.ToString(reader["prepayments"]);
data.balance = Convert.ToString(reader["balance"]);
data.pay_prepayments = Convert.ToString(reader["pay_prepayments"]);
data.pay_balance = Convert.ToString(reader["pay_balance"]);
data.Paypart = Convert.ToString(reader["Paypart"]);
data.cicode = Convert.ToString(reader["cicode"]);
data.ciname = Convert.ToString(reader["ciname"]);
data.cicodeandname = Convert.ToString(reader["cicodeandname"]);
data.ciqcode = Convert.ToString(reader["ciqcode"]);
data.ciqname = Convert.ToString(reader["ciqname"]);
data.ciqcodeandname = Convert.ToString(reader["ciqcodeandname"]);
data.UnitRef = Convert.ToString(reader["UnitRef"]);
data.paypartRef = Convert.ToString(reader["paypartRef"]);
data.codename = Convert.ToString(reader["codename"]);
data.defaultrate = Convert.ToString(reader["defaultrate"]);
data.baolv = Convert.ToString(reader["baolv"]);
data.tariff = Convert.ToString(reader["tariff"]);
data.tax = Convert.ToString(reader["tax"]);
data.tax_zz = Convert.ToString(reader["tax_zz"]);
data.Declarenumber = Convert.ToString(reader["Declarenumber"]);
data.Exchangerate = Convert.ToString(reader["Exchangerate"]);
data.Exchangerate_Customs = Convert.ToString(reader["Exchangerate_Customs"]);
data.tax_1 = Convert.ToString(reader["tax_1"]);
data.tax_zz_1 = Convert.ToString(reader["tax_zz_1"]);
data.tax_2 = Convert.ToString(reader["tax_2"]);
data.tax_zz_2 = Convert.ToString(reader["tax_zz_2"]);
data.PreProportion = Convert.ToString(reader["PreProportion"]);
data.AMOUNT_WRITEOFFS = Convert.ToString(reader["AMOUNT_WRITEOFFS"]);
data.NAME_REF = Convert.ToString(reader["NAME_REF"]);
data.SEQUENCE = Convert.ToString(reader["SEQUENCE"]);
data.Ename = Convert.ToString(reader["Ename"]);
data.SBYS = Convert.ToString(reader["SBYS"]);
data.BZTCHNO = Convert.ToString(reader["BZTCHNO"]);
data.GID = Convert.ToString(reader["GID"]);
data.CargoType = Convert.ToString(reader["CargoType"]);
data.KINDPKGS = Convert.ToString(reader["KINDPKGS"]);
data.WEIGHT_ACT = Convert.ToString(reader["WEIGHT_ACT"]);
data.WEIGHT_REMAIN = Convert.ToString(reader["WEIGHT_REMAIN"]);
data.BOXCOUNT_ACT = Convert.ToString(reader["BOXCOUNT_ACT"]);
data.BOXCOUNT_REMAIN = Convert.ToString(reader["BOXCOUNT_REMAIN"]);
data.WEIGHT_HT = Convert.ToString(reader["WEIGHT_HT"]);
data.BOXCOUNT_HT = Convert.ToString(reader["BOXCOUNT_HT"]);
data.ISLOCKED = Convert.ToString(reader["ISLOCKED"]);
data.ExpirationDate = Convert.ToString(reader["ExpirationDate"]);
data.AGENCRATE = Convert.ToString(reader["AGENCRATE"]);
data.MOREORLESS = Convert.ToString(reader["MOREORLESS"]);
data.pay_prepayments = Convert.ToString(reader["pay_prepayments"]);
data.pay_balance = Convert.ToString(reader["pay_balance"]);
data.SPECIFICATIONS = Convert.ToString(reader["SPECIFICATIONS"]);
data.Exchangerate_Cust = Convert.ToString(reader["Exchangerate_Cust"]);
data.AGENCRATE_Cust = Convert.ToString(reader["AGENCRATE_Cust"]);
data.STOCKDATE_1 = Convert.ToString(reader["STOCKDATE_1"]);
data.STOCKDATE_2 = Convert.ToString(reader["STOCKDATE_2"]);
data.HTH = Convert.ToString(reader["HTH"]);
data.ContainerNo = Convert.ToString(reader["ContainerNo"]);
data.SALEINFO = Convert.ToString(reader["SALEINFO"]);
data.buyer = Convert.ToString(reader["buyer"]);
data.Mainstate = Convert.ToString(reader["Mainstate"]);
data.MainStateRef = Convert.ToString(reader["MainStateRef"]);
data.Inspection_Storage = Convert.ToString(reader["Inspection_Storage"]);
data.inspection_date = Convert.ToString(reader["inspection_date"]);
data.Unsealdate = Convert.ToString(reader["Unsealdate"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
#endregion
static public List<HTHmb> GetHTH()
{ //合同信息/商品信息 ——每合同一行
var strSql = new StringBuilder();
strSql.Append(" select ContractNo,HTH ");
strSql.Append(" from import_main ");
return SetHTH(strSql);
}
private static List<HTHmb> SetHTH(StringBuilder strSql)
{
var headList = new List<HTHmb>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
HTHmb data = new HTHmb();
#region Set DB data to Object
data.HTH = Convert.ToString(reader["HTH"]);
data.ContractNo = Convert.ToString(reader["ContractNo"]);
#endregion
headList.Add(data);
}
reader.Close();
}
return headList;
}
#region 锁定货物信息，生成库存/以及反向操作相关
static public int SetCargoLock ( string CARGO_GID, string WMSDATE )
{
var strSql = new StringBuilder();
strSql.Append(" update [Import_cargo] ");
strSql.Append(" set ISLOCKED=1,STOCKDATE_1='" + WMSDATE + "' where GID ='" + CARGO_GID + "'");
//strSql.Append(" update import_main set mainstatus ");
var _count = 0;
Database db = DatabaseFactory.CreateDatabase();
_count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString());
return _count;
}
static public int SetCargoUnLock ( string CARGO_GID )
{
var strSql = new StringBuilder();
strSql.Append(" update [Import_cargo] ");
strSql.Append(" set ISLOCKED=0 where GID ='" + CARGO_GID + "'");
var _count = 0;
Database db = DatabaseFactory.CreateDatabase();
_count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString());
return _count;
}
#endregion
#region 插入费率区间
static public int SetWMSRATE ( string STORAGENAME, string USERID, string BSNO, string CUSTOMERNAME,string DODATE )
{
var strSql = new StringBuilder();
strSql.Append(" INSERT INTO [wms_rate] ([GID],[FEEGRADE],[FEESCALE],[FEEPRICE],[FEEUNIT],[CREATEUSER],[CREATETIME] ");
strSql.Append(" ,[MODIFIEDUSER],[MODIFIEDTIME],[REMARK],[BSNO],[CUSTOMERNAME],[FEETYPE] ");
strSql.Append(" ,[ENDBILLINGDATE],[ADDPRICE],[RATETYPE],[FEENAME],[ISCHANGE],[CAPPRICE]) ");
strSql.Append(" VALUES (newid(),1,0,(select max(price_pay) from [WMSPrice] where CUSTOMERNAME='" + STORAGENAME + "' and PRICEdate=(select max(pricedate) from [WMSPrice] where CUSTOMERNAME='" + STORAGENAME + "' and pricedate<'" + DODATE + "'))");
strSql.Append(" ,'',(select showname from [user] where gid='" + USERID + "'),getdate() ");
strSql.Append(" ,(select showname from [user] where gid='" + USERID + "'),getdate(),'' ");
strSql.Append(" ,'" + BSNO + "','" + CUSTOMERNAME + "',1,getdate(),0,1,'仓储费',0,0 )");
var _count = 0;
Database db = DatabaseFactory.CreateDatabase();
_count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString());
return _count;
}
#endregion
#region 获取出库单
public static int GetCargoOut ( string CARGO_GID )
{ //合同信息/商品信息
var strSql = new StringBuilder();
strSql.Append(" select count(*) [Count] from wms_out_detail where inbsno in(select gid from wms where cargo_gid='"
+ CARGO_GID + "' and isnull(isdelete,0)=0) ");
var _count = 0;
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
_count = Convert.ToInt16(reader["Count"]);
}
reader.Close();
}
return _count;
}
public static int GetLocked(string CARGO_GID)
{ //合同信息/商品信息
var strSql = new StringBuilder();
strSql.Append(" select count(*) Count from wms where islock=1 and cargo_gid='" + CARGO_GID + "'");
var _count = 0;
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
_count = Convert.ToInt16(reader["Count"]);
}
reader.Close();
}
return _count;
}
#endregion
#region 通过CARGO_GID获取WMS的GID
public static List<WMSmb> GetWMSGID(string CARGO_GID)
{ //合同信息/商品信息
var strSql = new StringBuilder();
strSql.Append(" select GID from wms where CARGO_GID='" + CARGO_GID + "' ");
//var GID = "";
var WMSmbList = new List<WMSmb>();
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
WMSmb data= new WMSmb();
data.GID = Convert.ToString(reader["GID"]);
WMSmbList.Add(data);
}
reader.Close();
}
return WMSmbList;
}
#endregion
#region 上传图片附件
public static bool UpdateFileData(HttpRequestBase request, string filename, string name, string FTPPATH, string GID, out string msg)
{
var isSucess = false;
msg = "";
Stream stream = new FileStream(filename, FileMode.Open);
byte[] data = new byte[stream.Length];
stream.Read(data, 0, data.Length);
stream.Close();
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
const string insertSql = @"update Receipt_Doc set URL=@name,DRIECT_URL=@FTPPATH where GID=@GID";
SqlParameter[] updateParms = new SqlParameter[] {
new SqlParameter("@GID",SqlDbType.VarChar),
new SqlParameter("@FTPPATH",SqlDbType.VarChar),
new SqlParameter("@name",SqlDbType.VarChar)
};
updateParms[0].Value = GID;
updateParms[1].Value = FTPPATH;
updateParms[2].Value = name;
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, insertSql, updateParms);
sqlTran.Commit();
isSucess = true;//状态为1表示插入成功
}
catch (Exception execError)
{
isSucess = false;//有异常,插入失败
sqlTran.Rollback();
msg = execError.Message;
}
finally
{
SqlHelper.CloseConnection();
}
}
return isSucess;
}
static public int SaveFileList(IList<ReceiptDocmb> tempFileEntities, string tempBSNO)
{
int iResult = 0;
/*
const string SQL_INSERT = @"insert info_files (GID,PID,UPLOADEMPLY,UPLOADDATE,FILENAME,FILETYPE) VALUES(@GID,@PID,@UPLOADEMPLY,@UPLOADDATE,@FILENAME,@FILETYPE) ";
const string SQL_UPDATE = @"update info_files SET FILENAME=@FILENAME where GID=@GID";
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
foreach (ReceiptDocmb fileEntity in tempFileEntities)
{
if (fileEntity.BSNO == "*" || fileEntity.BSNO == "")
{
SqlParameter[] insertParms = new SqlParameter[] {
new SqlParameter("@GID",SqlDbType.VarChar),
new SqlParameter("@PID",SqlDbType.VarChar),
new SqlParameter("@UPLOADEMPLY",SqlDbType.VarChar),
new SqlParameter("@UPLOADDATE",SqlDbType.VarChar),
new SqlParameter("@FILENAME",SqlDbType.VarChar),
new SqlParameter("@FILETYPE",SqlDbType.VarChar)
};
insertParms[0].Value = fileEntity.GID;
insertParms[1].Value = tempBSNO;
insertParms[2].Value = fileEntity.UPLOADEMPLY;
insertParms[3].Value = fileEntity.UPLOADDATE;
insertParms[4].Value = fileEntity.FILENAME;
insertParms[5].Value = fileEntity.FILETYPE;
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_INSERT, insertParms);
}
else
{
SqlParameter[] updateParms = new SqlParameter[] {
new SqlParameter("@FILENAME",SqlDbType.VarChar)
};
updateParms[0].Value = fileEntity.GID;
updateParms[1].Value = tempBSNO;
updateParms[2].Value = fileEntity.UPLOADEMPLY;
updateParms[3].Value = fileEntity.UPLOADDATE;
updateParms[4].Value = fileEntity.FILENAME;
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, SQL_UPDATE, updateParms);
}
}
sqlTran.Commit();
iResult = 1;//状态为1表示插入成功
}
catch (Exception execError)
{
iResult = -1;//有异常,插入失败
sqlTran.Rollback();
iResult = -2;//插入异常,事务已回滚成功
throw execError;
}
finally
{
SqlHelper.CloseConnection();
}
}
*/
return iResult;
}
static public int DeleteUploadFile(string tempGId)
{
int iResult = 0;
using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
try
{
var strSql = new StringBuilder();
strSql.Append("Delete from Receipt_Doc where ");
strSql.Append(" GId in (" + tempGId + ")");
SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, strSql.ToString());
sqlTran.Commit();
iResult = 1;//状态为1表示插入成功
}
catch (Exception execError)
{
iResult = -1;//有异常,插入失败
sqlTran.Rollback();
iResult = -2;//插入异常,事务已回滚成功
throw execError;
}
finally
{
SqlHelper.CloseConnection();
}
}
return iResult;
}
#endregion
#region 权限范围
public static string GetRangDAStr(string tb, string userid, string username, string companyid)
{
string str = "";
var strSql = new StringBuilder();
strSql.Append("SELECT ");
strSql.Append(" VISIBLERANGE,OPERATERANGE,AUTHORITYID ");
strSql.Append(" from VW_User_Authority ");
strSql.Append(" where [NAME]='modImport' and USERID=(SELECT TOP 1 GID FROM [USER] WHERE CODENAME='" + userid + "') and ISDELETE=0");
string visiblerange = "4";
string operaterange = "4";
string AUTHORITYID = "";
Database db = DatabaseFactory.CreateDatabase();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
visiblerange = Convert.ToString(reader["VISIBLERANGE"]);
operaterange = Convert.ToString(reader["OPERATERANGE"]);
AUTHORITYID = Convert.ToString(reader["AUTHORITYID"]);
break;
}
reader.Close();
}
if (visiblerange == "4")
{
str = "1=2";
}
else if (visiblerange == "3")
{
str = " (M.OP='" + username + "' OR M.creator='" + userid + "' OR M.Auditor='" + userid + "' )";
}
else if (visiblerange == "2")
{
if (tb == "index")
{
var rangeDa = new RangeDA();
var deptid = rangeDa.GetDEPTGID(userid);
str = " (M.OP in (select showname from vw_user where deptgid='" + deptid + "') OR M.creator in (select userid from vw_user where deptgid='" + deptid + "') OR M.Auditor in (select userid from vw_user where deptgid='" + deptid + "') )";
//var userstr = new StringBuilder();
//userstr.Append(" select SHOWNAME from [user] where GID in (select USERID from user_company where COMPANYID='" + companyid + "') and GID in (select userid from user_baseinfo where DEPTNAME='" + deptname + "')");
//Database userdb = DatabaseFactory.CreateDatabase();
//using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString()))
//{
// str = "";
// while (reader.Read())
// {
// if (str == "")
// {
// str = " (B.OP='" + Convert.ToString(reader["SHOWNAME"]) + "' OR B.SALE='" + Convert.ToString(reader["SHOWNAME"]) + "' OR B.CUSTSERVICE='" + Convert.ToString(reader["SHOWNAME"]) + "' OR B.DOC='" + Convert.ToString(reader["SHOWNAME"]) + "' OR B.FRCUSTSERVICE='" + Convert.ToString(reader["SHOWNAME"]) + "'";
// }
// else
// {
// str = str + " or B.OP='" + Convert.ToString(reader["SHOWNAME"]) + "' OR B.SALE='" + Convert.ToString(reader["SHOWNAME"]) + "' OR B.CUSTSERVICE='" + Convert.ToString(reader["SHOWNAME"]) + "' OR B.DOC='" + Convert.ToString(reader["SHOWNAME"]) + "' OR B.FRCUSTSERVICE='" + Convert.ToString(reader["SHOWNAME"]) + "'";
// };
// }
// str = str + ")";
// reader.Close();
//}
}
else
{
str = " UPPER(M.CORPID)='" + companyid + "'";
}
}
else if (visiblerange == "5")
{
if (tb == "index")
{
var userstr = new StringBuilder();
userstr.Append(" select COMPANYID from user_authority_range_company where userid='" + userid + "' and AUTHORITYID='" + AUTHORITYID + "' and VISIBLERANGE=1");
Database userdb = DatabaseFactory.CreateDatabase();
using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString()))
{
str = "";
while (reader.Read())
{
if (str == "")
{
str = " (M.Corpid='" + Convert.ToString(reader["COMPANYID"]) + "'";
}
else
{
str = str + " or M.Corpid='" + Convert.ToString(reader["COMPANYID"]) + "'";
};
}
str = str + ")";
reader.Close();
}
}
else
{
str = " UPPER(M.Corpid)='" + companyid + "'";
}
}
else if (visiblerange == "6")
{
if (tb == "index")
{
var userstr = new StringBuilder();
userstr.Append(" select OPID,(select SHOWNAME from [user] where GID=user_authority_range_op.OPID) SHOWNAME from user_authority_range_op where userid='" + userid + "' and AUTHORITYID='" + AUTHORITYID + "' and VISIBLERANGE=1");
Database userdb = DatabaseFactory.CreateDatabase();
using (IDataReader reader = userdb.ExecuteReader(CommandType.Text, userstr.ToString()))
{
str = "";
while (reader.Read())
{
if (str == "")
{
str = " (M.OP='" + Convert.ToString(reader["SHOWNAME"]) + "' OR M.creator='" + Convert.ToString(reader["OPID"]) + "' OR M.Auditor='" + Convert.ToString(reader["OPID"]) + "' ";
}
else
{
str = str + " or M.OP='" + Convert.ToString(reader["SHOWNAME"]) + "' OR M.creator='" + Convert.ToString(reader["OPID"]) + "' OR M.Auditor='" + Convert.ToString(reader["OPID"]) + "'";
};
}
str = str + ")";
reader.Close();
}
}
else
{
str = " UPPER(M.Corpid)='" + companyid + "'";
}
}
else if (visiblerange == "1")
{
str = " UPPER(M.Corpid)='" + companyid + "'";
}
return str;
}
#endregion
#region 生成补税退税
static public int KCStart ( string ContractNo, string tax, string tax_zz )
{
Database db = DatabaseFactory.CreateDatabase();
using (DbConnection connection = db.CreateConnection())
//using (SqlTransaction idbTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
{
connection.Open();
var 关税补税 = 0M;
var 增值税补税 = 0M;
try
{
var _关税 = db.ExecuteScalar(CommandType.Text, "select sum(amount) from ch_fee where BSNO='" + ContractNo + "' and feetype=2 and (feename='关税' or (feename='补税' and remark like '%关税%') )");
var 关税 = Convert.ToDecimal(_关税);
if (Convert.ToDecimal(tax) > 关税)
{
关税补税 = Convert.ToDecimal(tax) - 关税;
}
var _增值税 = db.ExecuteScalar(CommandType.Text, "select sum(amount) from ch_fee where BSNO='" + ContractNo + "' and feetype=2 and (feename='增值税' or (feename='补税' and remark like '%增值税%') )");
var 增值税 = Convert.ToDecimal(_增值税);
if (Convert.ToDecimal(tax_zz) > 增值税)
{
增值税补税 = Convert.ToDecimal(tax) - 增值税;
}
DbTransaction idbTran = connection.BeginTransaction();
/*
if (关税补税 >0)
{
#region 数据生成
var modb = new ModelObjectRepository();
DBResult result = modb.SaveComm("BsNo", SALESNO,
ModelObjectConvert<MsChFee_Sales>.ToModelObjectList(chfeeBodyList),
ModelObjectConvert<MsChFee_Sales>.ToModelObjectList(chfeeDelBodyList)
);
//刷新父窗口上的父节点
var jsonRespose = new JsonResponse
{
Success = result.Success,
Message = result.Message,
//Data = XXHDAL.GetData("M.ContractNo='" + head.ContractNo + "'")
};
return new ContentResult() { Content = JsonConvert.Serialize(jsonRespose) };
}
*/
// #endregion
}
//isSucess = true;
catch (Exception e)
{
//idbTran.Rollback();
//isSucess = false;
//msg = exception.Message;
}
}
return 0;
}
#endregion
}
}