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 + "')  )";

                    str = " exists( select 1 from vw_user where deptgid='" + deptid + "' and(showname=M.OP OR userid=M.creator )) ";


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