using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using DSWeb.Areas.Import.Models.XXH;
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.Collections;
using System.Xml;
using DSWeb.Areas.CommMng.DAL;
using HcUtility.Comm;
using InvokeWebService;
using System.IO;
using System.Xml.Serialization;
using System.Linq;
using DSWeb.TruckMng.Helper.Repository;
using DSWeb.TruckMng.Helper;
using System.Data.SqlClient;
using DSWeb.Areas.Import.DAL.ImportTrade;
using DSWeb.Areas.Import.Models.PortOperate;
using DSWeb.EntityDA;

namespace DSWeb.Areas.Import.DAL.XXH
{
    public partial class XXHDAL
    {
        #region 合同基础信息列表

        static public List<XXHmb> GetDataList(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 dbo.trimdate(preArrivalDate) preArrivalDate ,dbo.trimdate(M.MODIFIEDTIME) MODIFIEDTIME ,M.MODIFIEDUSER,M.InventoryState,M.[id],M.[ContractNo],[HTH],[seller],M.[company],M.PORTLOAD,comp.name as companyname,[buyer],[STT],(select top 1 cc.NAME from company cc where cc.GID = M.CORPID) CORPNAME, ");
            strSql.Append(" M.[countryid],[creator],(select CODENAME+'-'+SHOWNAME from [user] where [CODENAME]=M.Creator) RECEIVE_MAN,[Auditor],[MainState],[printed],[AutoAPP],[AutoAPPApplyId],[AutoAPPNum],[ApplyDate],M.OP, ");
            strSql.Append(" (select showname from [user] where codename=M.auditor) AuditorREF, ");

            strSql.Append(" dbo.F_YN(convert(int,AutoAPP)) AutoAPPRef, ");
            strSql.Append(" dbo.F_YN(convert(int,printed)) printedRef, ");
            strSql.Append(" [ShipCompany_id], [BillNo] ,[BillType],[Vessel],[Voyage], ci.code as cicode,ci.name as ciname,cg.[weight]/1000 [weight], ");
            strSql.Append(" dbo.trimdate(BillTime) BillTime,  ");
            strSql.Append(" dbo.trimdate(Ex_sailingdate) Ex_sailingdate,  ");
            strSql.Append(" dbo.trimdate(Sailingdate) Sailingdate  ,  ");
            strSql.Append(" dbo.trimdate(ArrivalDate) ArrivalDate   ");
            strSql.Append("  ,[ContainerNo],[SealNo],[TransactionMethod],[PaymentMethods]  ");
            strSql.Append("  ,[SecurityDeposit], M.TRADINGAGENCY,  ");
            strSql.Append(" dbo.trimdate(CustomsReleaseDate) CustomsReleaseDate,  ");
            strSql.Append(" dbo.trimdate(InspectionReleaseDate) InspectionReleaseDate,  ");
            strSql.Append(" dbo.trimdate(SamplingDate_Plan) SamplingDate_Plan, ");
            strSql.Append(" dbo.trimdate(SamplingDate_Act) SamplingDate_Act, ");
            strSql.Append(" dbo.trimdate(inspection_Signup_date) inspection_Signup_date ");
            strSql.Append("  ,[inspection_no],cg.FactoryNo,cg.ICP,  ");
            strSql.Append(" dbo.trimdate(inspection_date) inspection_date  ");
            strSql.Append("  ,cg.id as cgid ,cg.name,c.country, convert(bigint ,M.TimeMark) as TimeMark ,  ");
            strSql.Append(" dbo.trimdate(creattime) creattime  ,  ");
            strSql.Append(" dbo.trimdate(Audittime) Audittime  ,  ");
            strSql.Append(" Freetime,CIQ_licence,CIQ_canbesearch,dbo.F_YN(convert(int,CIQ_canbesearch)) CIQ_canbesearchRef ,   ");//
            strSql.Append(" dbo.trimdate(tax_date) tax_date  ,  ");
            strSql.Append(" dbo.trimdate(tax_paydate) tax_paydate ,  ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=3 and EnumValueID=M.MainState) as MainstateRef,  ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=10 and EnumValueID=M.port) as portRef,  ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=5 and EnumValueID=M.ShipCompany_id) as ShipCompanyRef,  ");
            strSql.Append(" M.port, M.remark,ap.AppNo,ap.ValidDate ,M.Writeoffs,M.ReceiptStatus,M.Receiptremark,dbo.F_YN(convert(int,ReceiptStatus)) ReceiptStatusRef, ");
            strSql.Append(" dbo.F_YN(convert(int,Writeoffs)) WriteoffsRef ,M.OwnApp, ");
            strSql.Append(" M.FinanceStatus,M.remark_2,M.remark_3,M.remark_4,M.Sampling_need,dbo.F_YN(convert(int,Sampling_need)) Sampling_needREF ,M.DeclareNumber,M.Inspection_Storage,M.DeliveryAddress,M.Contacter,M.Tel,M.Overfilled, ");
            strSql.Append(" dbo.trimdate(M.Paydate) Paydate  ,  ");
            strSql.Append(" dbo.trimdate(M.Prepaydate) Prepaydate  ,  ");
            strSql.Append(" dbo.trimdate(M.E_Billtime) E_Billtime  ,  ");
            strSql.Append(" dbo.trimdate(M.HDtime) HDtime  ,  ");
            strSql.Append(" dbo.trimdate(M.DeliveryDate) DeliveryDate  ");
            strSql.Append("  ,M.ACCDATE,M.ContractStatus,ap.AppName,M.KCClear,M.RCVMode, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=23 and EnumValueID=M.RCVMode) as RCVModeREF  ");
            strSql.Append(" ,case when ap.remark like '%自贸%' then ap.remark else '' end ZMQ ");
            strSql.Append(" ,M.MZBZ, (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=0 and EnumValueID=M.MZBZ) as MZBZREF ");
            strSql.Append(" ,dbo.trimdate(RecDate) Recdate,PortDays,ap.remark appremark ");
            strSql.Append(" ,m.buztype,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=27 and EnumValueID=M.buztype) buztypeREF");
            strSql.Append(" ,(select sum(weight) from Import_appstate where cargo_id=cg.id) APPWEIGHT");
            strSql.Append(" ,(select description from info_client where shortname=seller ) sellerfullname");
            strSql.Append(" ,cg.Exporter,M.Inspection_Buyer");
            strSql.Append(" ,IC.tel Inspection_tel,IC.EMAIL Inspection_Email,IC.ADDR Inspection_DeliveryAddress ");
            strSql.Append(" ,CHIEF Inspection_Contacter,EDICODE Inspection_Code,HaveContract,HaveCargoMark,cg.Productiondate,cg.BZTCHNO ");
            strSql.Append(" ,(select sum(weight) from import_appstate where cargo_id=cg.id) appusedweight ");
            strSql.Append(" ,isnull(m.HAVESAMPLE,0) HAVESAMPLE,isnull(m.CHECKSAMPLE,0) CHECKSAMPLE,cg.price,cg.price_agio,M.CTN,M.BILLTRANCNO,cg.SBYS ");
            strSql.Append(" ,(select top 1 dbo.trimdate(RECEIVE_DATE) from op_receipt where bsno=M.contractno and m.BillType=0) BILL_RECEIVE_DATE ");
            strSql.Append(" ,(select top 1 dbo.trimdate(SEND_DATE) from op_receipt where bsno=M.contractno and m.BillType=0) BILL_SEND_DATE ");
            strSql.Append(" ,LOCK_EXCHANGERATE,M.EXCHANGERATE M_EXCHANGERATE ");

            strSql.Append(" ,dbo.trimdate(m.Unsealdate) Unsealdate,M.ISREFORM");

            strSql.Append(" ,Convert(varchar(20),Convert(numeric(18,2),isnull((select sum(isnull(amount,0)) from ch_fee where bsno=m.contractno and FEESTATUS in(10,11)),0)))  ");
            strSql.Append(" + '/' + Convert(varchar(20), Convert(numeric(18, 2), isnull((select sum(isnull(settlement, 0) * isnull(EXCHANGERATE, 1)) ");
            strSql.Append(" from ch_fee where bsno in (select F_NO from IMPORT_FINANCE_BODY where BSNO = M.ContractNo) and feetype = 1), 0))) isRongZi ");

            strSql.Append(" ,M.MBLNOREPEAT,M.AGENT,M.AGENT2,M.AGENT3");
            strSql.Append(" ,dbo.trimtime(m.CreateTime_2) CreateTime_2,dbo.trimtime(m.ResendTime) ResendTime,M.Dock,M.Customs_broker,M.inspection_broker ");
            strSql.Append(" ,TRADECOUNTRY,(select country from code_country where countryid=TRADECOUNTRY) TRADECOUNTRY_REF,dbo.trimdate(CIQDATE) CIQDATE,isnull(VSIGN,'') VSIGN,M.ClearanceNo,M.zhuanjian_date");
            strSql.Append(" ,(case M.InventoryState WHEN 0 THEN '过车' WHEN 1 THEN '直送' WHEN 2 THEN '入库' WHEN 3 THEN '部分过车' ELSE '' END) InventoryStateREF");
            strSql.Append(" ,dbo.trimdate(M.HTDATE) HTDATE");


            strSql.Append("  FROM [dbo].[Import_main] M ");
            strSql.Append("  left join code_country c on m.countryid=c.countryid  ");
            strSql.Append("  left join import_cargo cg on cg.contractno=M.contractno  ");
            strSql.Append("  left join import_cargoinfo ci on ci.id=cg.cargoinfo_id  ");
            strSql.Append("  left join company comp on comp.gid=M.company  ");
            strSql.Append("  left join import_appstate ast on ast.cargo_id=cg.id  ");
            strSql.Append("  left join import_approval ap on ap.id=ast.app_id ");
            strSql.Append("  left join vmsappused vau on vau.app_id=ap.id ");
            strSql.Append("  left join info_client IC on IC.shortname=M.Inspection_Buyer ");
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition.Replace("~", ""));//str.Replace("b","**");
            }
            var sortstring = DatasetSort.Getsortstring(sort);
            if (!string.IsNullOrEmpty(sortstring))
            {
                strSql.Append(" order by " + sortstring);
            }
            else
            {
                strSql.Append(" order by M.contractno ");
            }
            return SetData(strSql);
        }

        static public XXHmb GetData(string condition, string userid, string username, string companyid)
        {
            var _t = "";
            var list = GetDataList(condition,userid,username,companyid,_t);
            if (list.Count > 0)
                return list[0];

            return new XXHmb();
        }

        private static List<XXHmb> SetData ( StringBuilder strSql )
        {
            var headList = new List<XXHmb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    XXHmb data = new XXHmb();
                    #region Set DB data to Object
                    data.cgid = Convert.ToString(reader["cgid"]);
                    data.name = Convert.ToString(reader["name"]);
                    data.country = Convert.ToString(reader["country"]);
                    data.weight = Convert.ToString(reader["weight"]);
                    data.id = Convert.ToDecimal(reader["id"]);
                    data.ContractNo = Convert.ToString(reader["ContractNo"]);
                    data.HTH = Convert.ToString(reader["HTH"]);
                    data.HTDATE = Convert.ToString(reader["HTDATE"]);
                    data.seller = Convert.ToString(reader["seller"]);
                    data.buyer = Convert.ToString(reader["buyer"]);
                    data.STT = Convert.ToString(reader["STT"]);
                    data.countryid = Convert.ToString(reader["countryid"]);
                    data.creator = Convert.ToString(reader["creator"]);
                    data.RECEIVE_MAN = Convert.ToString(reader["RECEIVE_MAN"]);
                    data.Auditor = Convert.ToString(reader["Auditor"]);
                    data.AuditorREF = Convert.ToString(reader["AuditorREF"]);
                    data.Mainstate = Convert.ToString(reader["Mainstate"]);
                    data.MainstateRef = Convert.ToString(reader["MainstateRef"]);
                    data.Printed = Convert.ToString(reader["Printed"]);
                    data.AutoAPP = Convert.ToString(reader["AutoAPP"]);
                    data.AutoAPPApplyId = Convert.ToString(reader["AutoAPPApplyId"]);
                    data.AutoAPPNum = Convert.ToString(reader["AutoAPPNum"]);
                    if(reader["ApplyDate"].ToString()!="")
                        data.ApplyDate = Convert.ToDateTime(reader["ApplyDate"]);
                    data.ShipCompany_id = Convert.ToString(reader["ShipCompany_id"]);
                    data.BillNo = Convert.ToString(reader["BillNo"]);
                    data.BillType = Convert.ToString(reader["BillType"]);
                    data.BillTime = Convert.ToString(reader["BillTime"]);
                    data.Vessel = Convert.ToString(reader["Vessel"]);
                    data.Voyage = Convert.ToString(reader["Voyage"]);
                    data.Ex_sailingdate = Convert.ToString(reader["Ex_sailingdate"]);
                    data.Sailingdate = Convert.ToString(reader["Sailingdate"]);
                    data.ArrivalDate = Convert.ToString(reader["ArrivalDate"]);
                    //字段过滤器（判断是否存在该列）
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'CORPNAME'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                        data.CORPNAME = Convert.ToString(reader["CORPNAME"]);
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'PORTLOAD'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                        data.PORTLOAD = Convert.ToString(reader["PORTLOAD"]);
                    //字段过滤器（判断是否存在该列）
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'preArrivalDate'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                    {
                        if (!string.IsNullOrEmpty(reader["preArrivalDate"].ToString()))
                            data.preArrivalDate = Convert.ToString(reader["preArrivalDate"]);
                    }

                    //字段过滤器（判断是否存在该列）
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'InventoryState'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                    {
                        if (!string.IsNullOrEmpty(reader["InventoryState"].ToString()))
                            data.InventoryState = Convert.ToInt32(reader["InventoryState"]);
                        data.InventoryStateRef = Convert.ToString(reader["InventoryStateRef"]);

                    }

                    //字段过滤器（判断是否存在该列）
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'MODIFIEDUSER'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                    {
                        if (!string.IsNullOrEmpty(reader["MODIFIEDUSER"].ToString()))
                            data.MODIFIEDUSER = Convert.ToString(reader["MODIFIEDUSER"]);
                    }

                    //字段过滤器（判断是否存在该列）
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'MODIFIEDTIME'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                    {
                        if (!string.IsNullOrEmpty(reader["MODIFIEDTIME"].ToString()))
                            data.MODIFIEDTIME = Convert.ToString(reader["MODIFIEDTIME"]);
                    }
                    data.ContainerNo = Convert.ToString(reader["ContainerNo"]);
                    data.SealNo = Convert.ToString(reader["SealNo"]);
                    data.TransactionMethod = Convert.ToString(reader["TransactionMethod"]);
                    data.PaymentMethods = Convert.ToString(reader["PaymentMethods"]);
                    data.SecurityDeposit = Convert.ToString(reader["SecurityDeposit"]);

                    data.SamplingDate_Plan = Convert.ToString(reader["SamplingDate_Plan"]);
                    data.SamplingDate_Act = Convert.ToString(reader["SamplingDate_Act"]);
                    data.CustomsReleaseDate = Convert.ToString(reader["CustomsReleaseDate"]);
                    data.InspectionReleaseDate = Convert.ToString(reader["InspectionReleaseDate"]);
                    data.inspection_Signup_date = Convert.ToString(reader["inspection_Signup_date"]);
                    data.inspection_no = Convert.ToString(reader["inspection_no"]);
                    data.inspection_date = Convert.ToString(reader["inspection_date"]);
                    data.TimeMark = Convert.ToDecimal(reader["TimeMark"]);
                    data.cicode = Convert.ToString(reader["cicode"]);
                    data.ciname = Convert.ToString(reader["ciname"]);

                    data.company = Convert.ToString(reader["company"]);
                    data.companyname = Convert.ToString(reader["companyname"]);
                    data.creattime = Convert.ToString(reader["creattime"]);
                    data.Audittime = Convert.ToString(reader["Audittime"]);
                    data.Freetime = Convert.ToString(reader["Freetime"]);
                    data.CIQ_licence = Convert.ToString(reader["CIQ_licence"]);
                    data.CIQ_canbesearch = Convert.ToString(reader["CIQ_canbesearch"]);
                    data.CIQ_canbesearchRef = Convert.ToString(reader["CIQ_canbesearchRef"]);
                    data.tax_date = Convert.ToString(reader["tax_date"]);
                    data.tax_paydate = Convert.ToString(reader["tax_paydate"]);
                    data.port = Convert.ToString(reader["port"]);
                    data.remark = Convert.ToString(reader["remark"]);
                    data.portRef = Convert.ToString(reader["portRef"]);
                    data.AppNo = Convert.ToString(reader["AppNo"]);
                    data.ValidDate = Convert.ToString(reader["ValidDate"]);
                    data.Writeoffs = Convert.ToString(reader["Writeoffs"]);
                    data.WriteoffsRef = Convert.ToString(reader["WriteoffsRef"]);

                    data.AutoAPPRef = Convert.ToString(reader["AutoAPPRef"]);
                    data.printedRef = Convert.ToString(reader["printedRef"]);
                    data.ShipCompanyRef = Convert.ToString(reader["ShipCompanyRef"]);

                    data.Paydate = Convert.ToString(reader["Paydate"]);
                    data.Prepaydate = Convert.ToString(reader["Prepaydate"]);
                    data.E_Billtime = Convert.ToString(reader["E_Billtime"]);
                    data.HDtime = Convert.ToString(reader["HDtime"]);
                    data.FinanceStatus = Convert.ToString(reader["FinanceStatus"]);
                    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.Sampling_need = Convert.ToString(reader["Sampling_need"]);
                    data.Sampling_needREF = Convert.ToString(reader["Sampling_needREF"]);
                    data.DeclareNumber = Convert.ToString(reader["DeclareNumber"]);
                    data.Inspection_Storage = Convert.ToString(reader["Inspection_Storage"]);
                    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.Overfilled = Convert.ToString(reader["Overfilled"]);
                    data.OwnApp = Convert.ToString(reader["OwnApp"]);
                    data.ReceiptStatus = Convert.ToString(reader["ReceiptStatus"]);
                    data.ReceiptStatusRef = Convert.ToString(reader["ReceiptStatusRef"]);
                    data.Receiptremark = Convert.ToString(reader["Receiptremark"]);

                    data.TRADINGAGENCY = Convert.ToString(reader["TRADINGAGENCY"]);
                    data.FactoryNo = Convert.ToString(reader["FactoryNo"]);
                    data.ACCDATE = Convert.ToString(reader["ACCDATE"]);
                    data.ContractStatus = Convert.ToString(reader["ContractStatus"]);
                    data.KCClear = Convert.ToString(reader["KCClear"]);

                    data.AppName = Convert.ToString(reader["AppName"]);
                    data.OP = Convert.ToString(reader["OP"]);

                    data.RCVMode = Convert.ToString(reader["RCVMode"]);
                    data.RCVModeREF = Convert.ToString(reader["RCVModeREF"]);
                    data.ZMQ = Convert.ToString(reader["ZMQ"]);
                    data.MZBZ = Convert.ToString(reader["MZBZ"]);
                    data.MZBZREF = Convert.ToString(reader["MZBZREF"]);
                    data.RecDate = Convert.ToString(reader["RecDate"]);
                    data.PortDays = Convert.ToString(reader["PortDays"]);
                    data.APPREMARK = Convert.ToString(reader["APPREMARK"]);
                    data.BUZTYPE = Convert.ToString(reader["BUZTYPE"]);
                    data.BUZTYPEREF = Convert.ToString(reader["BUZTYPEREF"]);
                    data.APPWEIGHT = Convert.ToString(reader["APPWEIGHT"]);

                    data.SELLERFULLNAME = Convert.ToString(reader["SELLERFULLNAME"]);

                    data.Exporter = Convert.ToString(reader["Exporter"]);

                    data.Inspection_Contacter = Convert.ToString(reader["Inspection_Contacter"]);
                    data.Inspection_Tel = Convert.ToString(reader["Inspection_Tel"]);
                    data.Inspection_DeliveryAddress = Convert.ToString(reader["Inspection_DeliveryAddress"]);

                    data.Inspection_Buyer = Convert.ToString(reader["Inspection_Buyer"]);
                    data.Inspection_Code = Convert.ToString(reader["Inspection_Code"]);
                    data.Inspection_Email = Convert.ToString(reader["Inspection_Email"]);

                    data.HaveContract = Convert.ToString(reader["HaveContract"]);
                    data.HaveCargoMark = Convert.ToString(reader["HaveCargoMark"]);

                    data.Productiondate = Convert.ToString(reader["Productiondate"]);
                    data.BZTCHNO = Convert.ToString(reader["BZTCHNO"]);
                    data.appusedweight = Convert.ToString(reader["appusedweight"]);

                    data.HAVESAMPLE = Convert.ToString(reader["HAVESAMPLE"]);
                    data.CHECKSAMPLE = Convert.ToString(reader["CHECKSAMPLE"]);

                    data.price = Convert.ToString(reader["price"]);
                    data.price_agio = Convert.ToString(reader["price_agio"]);

                    data.CTN = Convert.ToString(reader["CTN"]);
                    data.BILLTRANCNO = Convert.ToString(reader["BILLTRANCNO"]);

                    data.SBYS = Convert.ToString(reader["SBYS"]);
                    data.BILL_RECEIVE_DATE = Convert.ToString(reader["BILL_RECEIVE_DATE"]);
                    data.BILL_SEND_DATE = Convert.ToString(reader["BILL_SEND_DATE"]);
                    data.LOCK_EXCHANGERATE = Convert.ToString(reader["LOCK_EXCHANGERATE"]);
                    data.EXCHANGERATE = Convert.ToString(reader["M_EXCHANGERATE"]);
                    data.Unsealdate = Convert.ToString(reader["Unsealdate"]);
                    data.ISREFORM = Convert.ToString(reader["ISREFORM"]);
                    data.MBLNOREPEAT = Convert.ToString(reader["MBLNOREPEAT"]);
                    //data.SBYS = Convert.ToString(reader["SBYS"]);
                    data.LOADFINISH = "1";

                    data.Agent = Convert.ToString(reader["AGENT"]);
                    data.Agent2 = Convert.ToString(reader["AGENT2"]);
                    data.Agent3 = Convert.ToString(reader["AGENT3"]);

                    data.CreateTime_2 = Convert.ToString(reader["CreateTime_2"]);
                    data.ResendTime = Convert.ToString(reader["ResendTime"]);
                    data.Dock = Convert.ToString(reader["Dock"]);
                    data.Customs_broker = Convert.ToString(reader["Customs_broker"]);
                    data.inspection_broker = Convert.ToString(reader["inspection_broker"]);

                    data.TRADECOUNTRY = Convert.ToString(reader["TRADECOUNTRY"]);
                    data.TRADECOUNTRY_REF = Convert.ToString(reader["TRADECOUNTRY_REF"]);
                    data.CIQDATE = Convert.ToString(reader["CIQDATE"]);
                    data.VSIGN = Convert.ToString(reader["VSIGN"]);
                    data.ClearanceNo = Convert.ToString(reader["ClearanceNo"]);

                    data.ZHUANJIAN_DATE =reader["zhuanjian_date"]==DBNull.Value?"":Convert.ToString(reader["zhuanjian_date"]);
                 
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }

        #endregion

        #region 合同基础信息列表__天津用

        static public List<XXH_singlemb> GetDataList_single ( string strCondition, string sort,string uid ,int start,int limit,out int total)
        {              //合同信息/商品信息  ——每合同一行
            var strSql = new StringBuilder();
            strSql.Append(" SELECT ");
            strSql.Append("(select c.CODENAME from user_company uc join company c on c.GID=uc.COMPANYID where USERID = '"+uid+"' ) comcode , ");
            strSql.Append("(select c.allowOpenMark2 from user_company uc join company c on c.GID=uc.COMPANYID where USERID = '" + uid + "' ) OpenMark2 , ");
            strSql.Append(" M.[id],M.[ContractNo],[HTH],[seller],M.[company],comp.name as companyname,[buyer],[STT], ");
            strSql.Append(" M.[countryid],M.[tradecountry],c2.country tradecountry_ref,[creator],[Auditor],(select showname from [user] where codename=M.auditor) AuditorREF,[MainState],[printed],[AutoAPP],[AutoAPPApplyId],[AutoAPPNum],[ApplyDate],dbo.F_YN(convert(int,AutoAPP)) AutoAPPRef, ");
            strSql.Append(" dbo.F_YN(convert(int,printed)) printedRef, [ShipCompany_id], [BillNo] ,[BillType],[Vessel],[Voyage], ");
            strSql.Append(" dbo.trimdate(BillTime) BillTime,			dbo.trimdate(Ex_sailingdate) Ex_sailingdate, ");
            strSql.Append(" dbo.trimdate(m.Sailingdate) Sailingdate,	dbo.trimdate(ArrivalDate) ArrivalDate, ");
            strSql.Append(" [ContainerNo],[SealNo],[TransactionMethod],[PaymentMethods],[SecurityDeposit],dbo.F_str(M.contractno) name, ");
            strSql.Append(" dbo.trimdate(CustomsReleaseDate) CustomsReleaseDate,dbo.trimdate(SamplingDate_Plan) SamplingDate_Plan, ");
            strSql.Append(" dbo.trimdate(SamplingDate_Act) SamplingDate_Act,dbo.trimdate(inspection_Signup_date) inspection_Signup_date,[inspection_no], ");
            strSql.Append(" dbo.trimdate(inspection_date) inspection_date,c.country,convert(bigint ,M.TimeMark) as TimeMark , ");
            strSql.Append(" dbo.trimdate(creattime) creattime ,		dbo.trimdate(Audittime) Audittime , ");
            strSql.Append(" Freetime,CIQ_licence,CIQ_canbesearch,dbo.F_YN(convert(int,CIQ_canbesearch)) CIQ_canbesearchRef , ");
            strSql.Append(" dbo.trimdate(tax_date) tax_date ,			dbo.trimdate(tax_paydate) tax_paydate , ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=3 and EnumValueID=M.MainState) as MainstateRef, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=10 and EnumValueID=M.port) as portRef, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=5 and EnumValueID=M.ShipCompany_id) as ShipCompanyRef, ");
            strSql.Append(" M.port, M.remark,M.Writeoffs,M.ReceiptStatus,M.Receiptremark,dbo.F_YN(convert(int,ReceiptStatus)) ReceiptStatusRef, ");
            strSql.Append(" dbo.F_YN(convert(int,Writeoffs)) WriteoffsRef ,M.OwnApp,M.FinanceStatus,M.remark_2,M.remark_3,M.remark_4, ");
            strSql.Append(" M.Sampling_need,M.DeclareNumber,M.Inspection_Storage,case M.Inspection_Storage when 0 then '否/快检' when 1 then '是/慢检' end Inspection_StorageREF,M.DeliveryAddress,M.Contacter,M.Tel,M.Overfilled, ");
           // strSql.Append(" dbo.trimdate(Paydate) Paydate, convert(varchar,dbo.trimdate(M.E_Billtime),23) E_Billtime , ");
            strSql.Append(" dbo.trimdate(M.E_Billtime) E_Billtime , ");
            strSql.Append(" dbo.trimdate(HDtime) HDtime ,dbo.trimdate(DeliveryDate) DeliveryDate , ");
            strSql.Append(" dbo.f_FactoryNo(M.contractno) FactoryNo,dbo.f_count(M.contractno) boxcount, ");
            strSql.Append(" dbo.f_pay_Amount_single(m.contractno) amount,dbo.f_weight(M.contractno) [weight], ");
            strSql.Append(" dbo.f_price(M.contractno) price,dbo.f_validdate(M.contractno) validdate, ");
            strSql.Append(" '' cicode,'' ciname,'' AppNo ,M.TRADINGAGENCY,dbo.trimtime(M.createtime_2) createtime_2,dbo.trimtime(M.resendtime) resendtime");
            strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=6 and EnumValueID=M.Billtype) as BilltypeREF ");
            strSql.Append(" ,M.MZBZ, (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=23 and EnumValueID=M.MZBZ) as MZBZREF ");
            strSql.Append(" ,dbo.trimtime(M.OPBillTime) OPBillTime,M.Dock,M.Customs_broker,M.inspection_broker ");
            strSql.Append(" ,(select top 1 Receive_date from OP_Receipt where BSNO=m.contractno and receipttype='税单') Shuidan_Time ");
            strSql.Append(" ,(select top 1 Receive_date from OP_Receipt where BSNO=m.contractno and receipttype='检验检疫证明') Jianyizheng_Time ");
            strSql.Append(" ,(select top 1 Receive_date from OP_Receipt where BSNO=m.contractno and receipttype='自动证') AutoAPP_Time ");
            strSql.Append(" ,(select top 1 Receive_date from OP_Receipt where BSNO=m.contractno and receipttype='核销单(内部)') HXD_Time ");
            strSql.Append(" ,dbo.trimdate( M.Paydate) as Paydate ");
            strSql.Append(" ,dbo.trimdate( M.Prepaydate) as Prepaydate ,M.CTN,M.BILLTRANCNO,'' SBYS");
            strSql.Append(" ,(select top 1 dbo.trimdate(RECEIVE_DATE) from op_receipt where bsno=M.contractno and m.BillType=0) BILL_RECEIVE_DATE ");
            strSql.Append(" ,(select top 1 dbo.trimdate(SEND_DATE) from op_receipt where bsno=M.contractno and m.BillType=0) BILL_SEND_DATE ");
            strSql.Append(" ,dbo.trimdate(m.Unsealdate) Unsealdate,(select name from company where gid = M.agent )  AGENT,M.AGENT AGENTID,M.AGENT2,M.AGENT3,M.OP,comp2.openCustom openCustom ");
            strSql.Append(" ,dbo.trimdate(M.HTDATE) HTDATE  ");
            strSql.Append(" FROM [dbo].[Import_main] M  ");
            strSql.Append(" left join code_country c on m.countryid=c.countryid ");
            strSql.Append(" left join code_country c2 on m.tradecountry=c2.countryid ");
            strSql.Append(" left join company comp2 on comp2.gid=M.Agent ");
            strSql.Append(" left join company comp on comp.gid=M.company ");

            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.contractno ");
            }
            int totalcnt =0;
            StringBuilder sb = PagerHelper.PageSQL(strSql.ToString(), start, limit, out totalcnt);
            total = totalcnt;
            return SetData_single(sb);
        }

        private static List<XXH_singlemb> SetData_single ( StringBuilder strSql )
        {
            var headList = new List<XXH_singlemb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    XXH_singlemb data = new XXH_singlemb();
                    #region Set DB data to Object
                    //data.cgid = Convert.ToString(reader["cgid"]);
                    data.name = Convert.ToString(reader["name"]);
                    data.country = Convert.ToString(reader["country"]);
                    data.weight = Convert.ToString(reader["weight"]);

                    data.id = Convert.ToDecimal(reader["id"]);
                    data.ContractNo = Convert.ToString(reader["ContractNo"]);
                    data.HTH = Convert.ToString(reader["HTH"]);
                    data.HTDATE = Convert.ToString(reader["HTDATE"]);
                    data.seller = Convert.ToString(reader["seller"]);
                    data.companyname = Convert.ToString(reader["companyname"]);
                    //openCustom-是否可以看到客户信息0：否（默认） 1：是 老单没关联代理公司获取不到该字段，则为可见
                    if (reader["openCustom"] == null)
                    {
                        data.buyer = Convert.ToString(reader["buyer"]);
                    }else if(reader["comcode"].ToString()=="XXH"){
                        //青岛公司的员工（公司代码为xxh）则有可见的权限
                        data.buyer = Convert.ToString(reader["buyer"]);
                    }
                    else
                    {
                        int openCustom = Convert.ToInt32(reader["openCustom"]);
                        if (openCustom == 0)
                        {
                            data.buyer = "";
                        }
                        else
                        {
                            data.buyer = Convert.ToString(reader["buyer"]);
                        }
                    }
                   
                    data.STT = Convert.ToString(reader["STT"]);
                    data.countryid = Convert.ToString(reader["countryid"]);
                    data.TRADECOUNTRY = Convert.ToString(reader["TRADECOUNTRY"]);
                    data.TRADECOUNTRY_REF = Convert.ToString(reader["TRADECOUNTRY_REF"]);

                    data.creator = Convert.ToString(reader["creator"]);
                    data.Auditor = Convert.ToString(reader["Auditor"]);
                    data.AuditorREF = Convert.ToString(reader["AuditorREF"]);
                    data.Mainstate = Convert.ToString(reader["Mainstate"]);
                    data.MainstateRef = Convert.ToString(reader["MainstateRef"]);
                    data.Printed = Convert.ToString(reader["Printed"]);
                    data.AutoAPP = Convert.ToString(reader["AutoAPP"]);
                    data.AutoAPPApplyId = Convert.ToString(reader["AutoAPPApplyId"]);
                    data.AutoAPPNum = Convert.ToString(reader["AutoAPPNum"]);
                    if (reader["ApplyDate"].ToString() != "")
                        data.ApplyDate = Convert.ToDateTime(reader["ApplyDate"]);

                    data.ShipCompany_id = Convert.ToString(reader["ShipCompany_id"]);
                    data.BillNo = Convert.ToString(reader["BillNo"]);
                    data.BillType = Convert.ToString(reader["BillType"]);
                    data.BillTime = Convert.ToString(reader["BillTime"]);
                    data.Vessel = Convert.ToString(reader["Vessel"]);
                    data.Voyage = Convert.ToString(reader["Voyage"]);
                    data.Ex_sailingdate = Convert.ToString(reader["Ex_sailingdate"]);
                    data.Sailingdate = Convert.ToString(reader["Sailingdate"]);
                    data.ArrivalDate = Convert.ToString(reader["ArrivalDate"]);

                    data.ContainerNo = Convert.ToString(reader["ContainerNo"]);
                    data.SealNo = Convert.ToString(reader["SealNo"]);
                    data.TransactionMethod = Convert.ToString(reader["TransactionMethod"]);
                    data.PaymentMethods = Convert.ToString(reader["PaymentMethods"]);


                    data.SecurityDeposit = Convert.ToString(reader["SecurityDeposit"]);

                    data.SamplingDate_Plan = Convert.ToString(reader["SamplingDate_Plan"]);
                    data.SamplingDate_Act = Convert.ToString(reader["SamplingDate_Act"]);
                    data.CustomsReleaseDate = Convert.ToString(reader["CustomsReleaseDate"]);
                   
                    data.inspection_Signup_date = Convert.ToString(reader["inspection_Signup_date"]);
                    data.inspection_no = Convert.ToString(reader["inspection_no"]);
                    data.inspection_date = Convert.ToString(reader["inspection_date"]);
                    data.TimeMark = Convert.ToDecimal(reader["TimeMark"]);
                    data.cicode = Convert.ToString(reader["cicode"]);
                    data.ciname = Convert.ToString(reader["ciname"]);

                    data.company = Convert.ToString(reader["company"]);

                    data.creattime = Convert.ToString(reader["creattime"]);
                    data.Audittime = Convert.ToString(reader["Audittime"]);
                    data.Freetime = Convert.ToString(reader["Freetime"]);
                    data.CIQ_licence = Convert.ToString(reader["CIQ_licence"]);
                    data.CIQ_canbesearch = Convert.ToString(reader["CIQ_canbesearch"]);
                    data.CIQ_canbesearchRef = Convert.ToString(reader["CIQ_canbesearchRef"]);
                    data.tax_date = Convert.ToString(reader["tax_date"]);
                    data.tax_paydate = Convert.ToString(reader["tax_paydate"]);
                    data.port = Convert.ToString(reader["port"]);
                    data.remark = Convert.ToString(reader["remark"]);
                    data.portRef = Convert.ToString(reader["portRef"]);
                    data.AppNo = Convert.ToString(reader["AppNo"]);
                    data.ValidDate = Convert.ToString(reader["ValidDate"]);
                    data.Writeoffs = Convert.ToString(reader["Writeoffs"]);
                    data.WriteoffsRef = Convert.ToString(reader["WriteoffsRef"]);

                    data.AutoAPPRef = Convert.ToString(reader["AutoAPPRef"]);
                    data.printedRef = Convert.ToString(reader["printedRef"]);
                    data.ShipCompanyRef = Convert.ToString(reader["ShipCompanyRef"]);

                    data.Paydate = Convert.ToString(reader["Paydate"]);
                    data.E_Billtime = Convert.ToString(reader["E_Billtime"]);
                    data.HDtime = Convert.ToString(reader["HDtime"]);
                    data.FinanceStatus = Convert.ToString(reader["FinanceStatus"]);
                    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.Sampling_need = Convert.ToString(reader["Sampling_need"]);
                    data.DeclareNumber = Convert.ToString(reader["DeclareNumber"]);
                    data.Inspection_Storage = Convert.ToString(reader["Inspection_Storage"]);
                    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.Overfilled = Convert.ToString(reader["Overfilled"]);
                    data.OwnApp = Convert.ToString(reader["OwnApp"]);
                    data.ReceiptStatus = Convert.ToString(reader["ReceiptStatus"]);
                    data.ReceiptStatusRef = Convert.ToString(reader["ReceiptStatusRef"]);
                    data.Receiptremark = Convert.ToString(reader["Receiptremark"]);
                    data.FactoryNo = Convert.ToString(reader["FactoryNo"]);
                    data.boxcount = Convert.ToString(reader["boxcount"]);
                    data.amount = Convert.ToString(reader["amount"]);
                    data.weight = Convert.ToString(reader["weight"]);
                    data.price = Convert.ToString(reader["price"]);
                    data.CreateTime_2 = Convert.ToString(reader["CreateTime_2"]);
                    data.ResendTime = Convert.ToString(reader["ResendTime"]);
                    data.Inspection_StorageREF = Convert.ToString(reader["Inspection_StorageREF"]);
                    data.BilltypeREF = Convert.ToString(reader["BillTypeREF"]);
                    data.MZBZ = Convert.ToString(reader["MZBZ"]);
                    data.MZBZREF = Convert.ToString(reader["MZBZREF"]);
                    data.OPBillTime = Convert.ToString(reader["OPBillTime"]);
                    data.Dock = Convert.ToString(reader["Dock"]);
                    data.Customs_broker = Convert.ToString(reader["Customs_broker"]);
                    data.inspection_broker = Convert.ToString(reader["inspection_broker"]);

                    data.Shuidan_Time = Convert.ToString(reader["Shuidan_Time"]);
                    data.Jianyizheng_Time = Convert.ToString(reader["Jianyizheng_Time"]);
                    data.AutoAPP_Time = Convert.ToString(reader["AutoAPP_Time"]);
                    data.HXD_Time = Convert.ToString(reader["HXD_Time"]);

                    data.Paydate = Convert.ToString(reader["Paydate"]);
                    data.Prepaydate = Convert.ToString(reader["Prepaydate"]);
                    data.CTN = Convert.ToString(reader["CTN"]);
                    data.BILLTRANCNO = Convert.ToString(reader["BILLTRANCNO"]);

                    data.SBYS = Convert.ToString(reader["SBYS"]);
                    data.BILL_RECEIVE_DATE = Convert.ToString(reader["BILL_RECEIVE_DATE"]);
                    data.BILL_SEND_DATE = Convert.ToString(reader["BILL_SEND_DATE"]);
                    data.Unsealdate = Convert.ToString(reader["Unsealdate"]);

                    data.Agent = Convert.ToString(reader["AGENT"]);
                    data.Agent2 = Convert.ToString(reader["AGENT2"]);
                    data.Agent3 = Convert.ToString(reader["AGENT3"]);

                    data.OP = Convert.ToString(reader["OP"]);
                    data.AgentId = Convert.ToString(reader["AgentId"]);
                    data.AllowOpenMark2 = Convert.ToInt32(reader["OpenMark2"]);
                    
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }

        #endregion

        #region 合同基础信息列表——包括全部商品信息

        static public List<XXH_ALLmb> 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 dbo.trimdate(preArrivalDate) preArrivalDate,M.InventoryState,cg.pay_prepayments,cg.pay_balance,M.[id],M.[ContractNo],[HTH],[seller],M.[company],comp.name as companyname,[buyer],[STT],(select top 1 cc.NAME from company cc where cc.GID = M.CORPID) CORPNAME,(select CODENAME+'-'+SHOWNAME from [user] where [CODENAME]=M.Creator) RECEIVE_MAN, ");//1-14
            strSql.Append(" M.[countryid],[creator],[Auditor],(select showname from [user] where codename=M.auditor) AuditorREF,[MainState],[printed],[AutoAPP],M.OP, ");//15-22
            strSql.Append(" dbo.F_YN(convert(int,AutoAPP)) AutoAPPRef, ");//23
            strSql.Append(" dbo.F_YN(convert(int,printed)) printedRef, ");//24
            strSql.Append(" [ShipCompany_id], [BillNo] ,[BillType],[Vessel],[Voyage], ci.code as cicode,ci.name as ciname,cg.[weight]/1000 [weight_t],cg.[weight], ");//25-33
            strSql.Append(" dbo.trimdate(BillTime) BillTime,  ");//34
            strSql.Append(" dbo.trimdate(Ex_sailingdate) Ex_sailingdate,  ");
            strSql.Append(" dbo.trimdate(Sailingdate) Sailingdate  ,  ");
            strSql.Append(" dbo.trimdate(ArrivalDate) ArrivalDate   ");//37
            strSql.Append("  ,[ContainerNo],[SealNo],[TransactionMethod],[PaymentMethods]  ");//41
            strSql.Append("  ,[SecurityDeposit],M.TRADINGAGENCY,   ");
            strSql.Append(" dbo.trimdate(CustomsReleaseDate) CustomsReleaseDate,  ");
            strSql.Append(" dbo.trimdate(SamplingDate_Plan) SamplingDate_Plan, ");
            strSql.Append(" dbo.trimdate(SamplingDate_Act) SamplingDate_Act, ");
            strSql.Append(" dbo.trimdate(inspection_Signup_date) inspection_Signup_date ");
            strSql.Append("  ,[inspection_no],  ");
            strSql.Append(" dbo.trimdate(inspection_date) inspection_date  ");
            strSql.Append("  ,cg.id as cgid ,cg.name,c.country, convert(bigint ,M.TimeMark) as TimeMark ,  ");
            strSql.Append(" dbo.trimdate(creattime) creattime  ,  ");
            strSql.Append(" dbo.trimdate(Audittime) Audittime  ,  ");
            strSql.Append(" Freetime,CIQ_licence,CIQ_canbesearch,dbo.F_YN(convert(int,CIQ_canbesearch)) CIQ_canbesearchRef ,   ");
            strSql.Append(" dbo.trimdate(tax_date) tax_date  ,  ");
            strSql.Append(" dbo.trimdate(tax_paydate) tax_paydate ,  ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=3 and EnumValueID=M.MainState) as MainstateRef,  ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=10 and EnumValueID=M.port) as portRef,  ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=5 and EnumValueID=M.ShipCompany_id) as ShipCompanyRef,  ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=1 and EnumValueID=M.TransactionMethod) as TransactionMethodRef, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=4 and EnumValueID=M.PaymentMethods) as PaymentMethodsRef, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=6 and EnumValueID=M.Billtype) as BilltypeRef, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=16 and EnumValueID=M.Financestatus) as FinancestatusREF, ");
            strSql.Append(" M.port, M.remark,ap.AppNo,ap.ValidDate ,M.Writeoffs,M.ReceiptStatus,M.Receiptremark,dbo.F_YN(convert(int,ReceiptStatus)) ReceiptStatusRef, ");
            strSql.Append(" dbo.F_YN(convert(int,Writeoffs)) WriteoffsRef ,M.OwnApp, ");
            strSql.Append(" M.FinanceStatus,M.remark_2,M.remark_3,M.remark_4,M.Sampling_need,M.DeclareNumber,M.Inspection_Storage,M.DeliveryAddress,M.Contacter,M.Tel,M.Overfilled, ");
            strSql.Append(" dbo.f_yn( Inspection_Storage) Inspection_StorageREF, ");
            strSql.Append(" dbo.trimdate( M.Paydate) as Paydate, ");
            strSql.Append(" dbo.trimdate( M.Prepaydate) as Prepaydate, ");
            strSql.Append(" dbo.trimdate( M.E_Billtime) as E_Billtime,  ");
            strSql.Append(" dbo.trimdate( M.HDtime) as HDtime ,  ");
            strSql.Append(" dbo.trimdate( M.DeliveryDate) as DeliveryDate, ");
            strSql.Append(" cg.m_weight/1000 m_weight,cg.price,cg.price_agio,cg.price_limit,cg.amount,cg.amount*cg.Exchangerate RMBAmount ,cg.tax_amount,cg.Exchangerate_Customs*cg.tax_amount tax_amount_RMB,cg.pay_amount,curr.codename,  ");
            strSql.Append(" cg.boxcount,cg.boxweight,cg.exporter,cg.FactoryNo,cg.productiondate,cg.prepayments,cg.balance,cg.baolv,  ");
            strSql.Append(" cg.tariff,cg.tax,cg.tax_zz,cg.exchangerate,cg.exchangerate_customs,cg.tax_1,cg.tax_zz_1,cg.tax_2,cg.tax_zz_2,  ");
            strSql.Append(" M.RCVMode,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=23 and EnumValueID=M.RCVMode) as RCVModeREF  ");
            strSql.Append(" ,dbo.F_YN(convert(int,Sampling_need)) Sampling_needREF  ");
            strSql.Append(" ,case when ap.remark like '%自贸%' then ap.remark else '' end ZMQ ");//自贸区
            strSql.Append(" ,M.MZBZ, (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=23 and EnumValueID=M.MZBZ) as MZBZREF ");
            strSql.Append(" ,dbo.trimdate(RecDate) Recdate,PortDays,M.DeliveryDate,M.DeliveryAddress,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=27 and EnumValueID=M.buztype) buztypeREF ");
            strSql.Append(" ,m.Inspection_Buyer ,M.ACCDATE  ");

            strSql.Append(" ,Convert(varchar(20),Convert(numeric(18,2),isnull((select sum(isnull(amount,0)) from ch_fee where bsno=m.contractno and FEESTATUS in(10,11)),0)))  ");
            strSql.Append(" + '/' + Convert(varchar(20), Convert(numeric(18, 2), isnull((select sum(isnull(settlement, 0) * isnull(EXCHANGERATE, 1)) ");
            strSql.Append(" from ch_fee where bsno in (select F_NO from IMPORT_FINANCE_BODY where BSNO = M.ContractNo) and feetype = 1), 0))) isRongZi ");

            strSql.Append(" ,isnull(m.CHECKSAMPLE,0) CHECKSAMPLE,M.CTN,M.BILLTRANCNO,cg.SBYS ");
            strSql.Append(" ,(select top 1 dbo.trimdate(RECEIVE_DATE) from op_receipt where bsno=M.contractno and m.BillType=0) BILL_RECEIVE_DATE ");
            strSql.Append(" ,(select top 1 dbo.trimdate(SEND_DATE) from op_receipt where bsno=M.contractno and m.BillType=0) BILL_SEND_DATE ");
            strSql.Append(" ,LOCK_EXCHANGERATE,M.EXCHANGERATE M_EXCHANGERATE ");
            strSql.Append(" ,dbo.trimdate(m.Unsealdate) Unsealdate,M.ISREFORM,ic.InspectionNo");
            strSql.Append(" ,TRADECOUNTRY,(select country from code_country where countryid=TRADECOUNTRY) TRADECOUNTRY_REF,dbo.trimdate(CIQDATE) CIQDATE,isnull(VSIGN,'') VSIGN");
            strSql.Append(" ,M.ClearanceNo");
            strSql.Append(" ,(case M.InventoryState WHEN 0 THEN '过车' WHEN 1 THEN '直送' WHEN 2 THEN '入库' WHEN 3 THEN '部分过车' ELSE '' END) InventoryStateREF");
            strSql.Append(" ,dbo.trimdate(M.HTDATE) HTDATE   ");

            //商品标签
            strSql.Append(" ,(select top 1 TAGPICURL from Import_Cargoname where hs_id =cg.cargoinfo_id and ciq_id = cg.cargociq_id and ENNAME = cg.Ename and CNNAME = cg.name ");
            strSql.Append(" and FactoryNoGID in (select GID from Import_Factoryno where FACTORYNO = cg.FactoryNo) and isnull(TAGPICURL,'')<> '' ) TAGPICURL  ");

            strSql.Append(" ,dbo.trimdate(InspectionReleaseDate) InspectionReleaseDate ");

            strSql.Append("    FROM [dbo].[Import_main] M   ");
            strSql.Append("  left join code_country c on m.countryid=c.countryid  ");
            strSql.Append("  left join import_cargo cg on cg.contractno=M.contractno  ");
            strSql.Append("  left join import_cargoinfo ci on ci.id=cg.cargoinfo_id  ");
            strSql.Append("  left join company comp on comp.gid=M.company  ");
            strSql.Append("  left join import_appstate ast on ast.cargo_id=cg.id  ");
            strSql.Append("  left join import_approval ap on ap.id=ast.app_id  ");
            strSql.Append("  left join [code_currency] curr on curr.gid=cg.currid ");
            strSql.Append("  left join Info_client ic on cg.Exporter =ic.shortname ");

            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }

            strSql.Append(" union all select ");
            strSql.Append("'','',0,0,'','','','','','','','','','','','','','','','','','','','','','','','','','','',   ");
            strSql.Append(" sum(cg.[weight])/1000 [weight_t],sum(cg.[weight])[weight], ");
            strSql.Append(" '','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','',  ");
            strSql.Append(" '','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','',  ");
            strSql.Append(" sum(cg.m_weight/1000) ,0,0,0,sum(cg.amount) ,sum(cg.amount*cg.Exchangerate), ");
            strSql.Append(" sum(cg.tax_amount) ,sum(cg.tax_amount*cg.Exchangerate_Customs), sum(cg.pay_amount) , '',  sum(cg.boxcount),0,'','',  ");
            strSql.Append(" '',sum(cg.prepayments),sum(cg.balance),0,  '',0,0,0,0,sum(cg.tax_1),  ");
            strSql.Append(" sum(cg.tax_zz_1),sum(cg.tax_2),sum(cg.tax_zz_2),'','','','','','','','','','','','','','1','0','','','','','',0,'','','','','','','','','','' ");
            strSql.Append(" ,'','','' ");
            strSql.Append(" FROM [dbo].[Import_main] M  ");
            strSql.Append(" left join code_country c on m.countryid=c.countryid  ");
            strSql.Append(" left join import_cargo cg on cg.contractno=M.contractno  ");
            strSql.Append(" left join import_cargoinfo ci on ci.id=cg.cargoinfo_id  ");
            strSql.Append(" left join company comp on comp.gid=M.company  ");
            strSql.Append(" left join import_appstate ast on ast.cargo_id=cg.id  ");
            strSql.Append(" left join import_approval ap on ap.id=ast.app_id  ");
            strSql.Append(" left join [code_currency] curr on curr.gid=cg.currid  ");
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }
            return SetData_ALL(strSql);
        }

        static public XXH_ALLmb 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 XXH_ALLmb();
        }

        private static List<XXH_ALLmb> SetData_ALL ( StringBuilder strSql )
        {
            var headList = new List<XXH_ALLmb>();
            Database db = DatabaseFactory.CreateDatabase();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = strSql.ToString();
            cmd.CommandTimeout = 120000;   //要加这一句
            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    XXH_ALLmb data = new XXH_ALLmb();
                    #region Set DB data to Object
                    data.cgid = Convert.ToString(reader["cgid"]);
                    data.name = Convert.ToString(reader["name"]);
                    data.country = Convert.ToString(reader["country"]);
                    data.weight_t = Convert.ToString(reader["weight_t"]);
                    data.weight = Convert.ToString(reader["weight"]);
                    //字段过滤器（判断是否存在该列）
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'CORPNAME'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                        data.CORPNAME = Convert.ToString(reader["CORPNAME"]);
                    //字段过滤器（判断是否存在该列）
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'preArrivalDate'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                    {
                        if(!string.IsNullOrEmpty(reader["preArrivalDate"].ToString()))
                            data.preArrivalDate = Convert.ToString(reader["preArrivalDate"]);
                    }
                        
                    //字段过滤器（判断是否存在该列）
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'InventoryState'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                    {
                        if (!string.IsNullOrEmpty(reader["InventoryState"].ToString()))
                            data.InventoryState = Convert.ToInt32(reader["InventoryState"]);

                        data.InventoryStateRef = Convert.ToString(reader["InventoryStateRef"]);

                    }

                    //字段过滤器（判断是否存在该列）
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'RECEIVE_MAN'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                    {
                        if (!string.IsNullOrEmpty(reader["RECEIVE_MAN"].ToString()))
                            data.RECEIVE_MAN = Convert.ToString(reader["RECEIVE_MAN"]);
                    }


                    data.id = Convert.ToDecimal(reader["id"]);
                    data.ContractNo = Convert.ToString(reader["ContractNo"]);
                    data.HTH = Convert.ToString(reader["HTH"]);
                    data.HTDATE = Convert.ToString(reader["HTDATE"]);

                    data.seller = Convert.ToString(reader["seller"]);
                    data.companyname = Convert.ToString(reader["companyname"]);
                    data.buyer = Convert.ToString(reader["buyer"]);
                    data.STT = Convert.ToString(reader["STT"]);
                    data.countryid = Convert.ToString(reader["countryid"]);
                    data.creator = Convert.ToString(reader["creator"]);
                    data.Auditor = Convert.ToString(reader["Auditor"]);
                    data.AuditorREF = Convert.ToString(reader["AuditorREF"]);
                    data.Mainstate = Convert.ToString(reader["Mainstate"]);
                    data.MainstateRef = Convert.ToString(reader["MainstateRef"]);
                    data.Printed = Convert.ToString(reader["Printed"]);
                    data.AutoAPP = Convert.ToString(reader["AutoAPP"]);

                    data.ShipCompany_id = Convert.ToString(reader["ShipCompany_id"]);
                    data.BillNo = Convert.ToString(reader["BillNo"]);
                    data.BillType = Convert.ToString(reader["BillType"]);
                    data.BillTime = Convert.ToString(reader["BillTime"]);
                    data.Vessel = Convert.ToString(reader["Vessel"]);
                    data.Voyage = Convert.ToString(reader["Voyage"]);
                    data.Ex_sailingdate = Convert.ToString(reader["Ex_sailingdate"]);
                    data.Sailingdate = Convert.ToString(reader["Sailingdate"]);
                    data.ArrivalDate = Convert.ToString(reader["ArrivalDate"]);

                    data.ContainerNo = Convert.ToString(reader["ContainerNo"]);
                    data.SealNo = Convert.ToString(reader["SealNo"]);
                    data.TransactionMethod = Convert.ToString(reader["TransactionMethod"]);
                    data.PaymentMethods = Convert.ToString(reader["PaymentMethods"]);
                    data.TransactionMethodRef = Convert.ToString(reader["TransactionMethodRef"]);
                    data.PaymentMethodsRef = Convert.ToString(reader["PaymentMethodsRef"]);

                    data.SecurityDeposit = Convert.ToString(reader["SecurityDeposit"]);

                    data.SamplingDate_Plan = Convert.ToString(reader["SamplingDate_Plan"]);
                    data.SamplingDate_Act = Convert.ToString(reader["SamplingDate_Act"]);
                    data.CustomsReleaseDate = Convert.ToString(reader["CustomsReleaseDate"]);
                    data.inspection_Signup_date = Convert.ToString(reader["inspection_Signup_date"]);
                    data.inspection_no = Convert.ToString(reader["inspection_no"]);
                    data.inspection_date = Convert.ToString(reader["inspection_date"]);
                    data.TimeMark = Convert.ToDecimal(reader["TimeMark"]);
                    data.cicode = Convert.ToString(reader["cicode"]);
                    data.ciname = Convert.ToString(reader["ciname"]);

                    data.company = Convert.ToString(reader["company"]);

                    data.creattime = Convert.ToString(reader["creattime"]);
                    data.Audittime = Convert.ToString(reader["Audittime"]);
                    data.Freetime = Convert.ToString(reader["Freetime"]);
                    data.CIQ_licence = Convert.ToString(reader["CIQ_licence"]);
                    data.CIQ_canbesearch = Convert.ToString(reader["CIQ_canbesearch"]);
                    data.CIQ_canbesearchRef = Convert.ToString(reader["CIQ_canbesearchRef"]);
                    data.tax_date = Convert.ToString(reader["tax_date"]);
                    data.tax_paydate = Convert.ToString(reader["tax_paydate"]);
                    data.port = Convert.ToString(reader["port"]);
                    data.remark = Convert.ToString(reader["remark"]);
                    data.portRef = Convert.ToString(reader["portRef"]);
                    data.AppNo = Convert.ToString(reader["AppNo"]);
                    data.ValidDate = Convert.ToString(reader["ValidDate"]);
                    data.Writeoffs = Convert.ToString(reader["Writeoffs"]);
                    data.WriteoffsRef = Convert.ToString(reader["WriteoffsRef"]);

                    data.AutoAPPRef = Convert.ToString(reader["AutoAPPRef"]);
                    data.printedRef = Convert.ToString(reader["printedRef"]);
                    data.ShipCompanyRef = Convert.ToString(reader["ShipCompanyRef"]);

                    data.Paydate = Convert.ToString(reader["Paydate"]);
                    data.Prepaydate = Convert.ToString(reader["Prepaydate"]);
                    data.E_Billtime = Convert.ToString(reader["E_Billtime"]);
                    data.HDtime = Convert.ToString(reader["HDtime"]);
                    data.FinanceStatus = Convert.ToString(reader["FinanceStatus"]);
                    data.FinanceStatusREF = Convert.ToString(reader["FinanceStatusREF"]);
                    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.Sampling_need = Convert.ToString(reader["Sampling_need"]);
                    data.Sampling_needREF = Convert.ToString(reader["Sampling_needREF"]);

                    data.DeclareNumber = Convert.ToString(reader["DeclareNumber"]);
                    data.Inspection_Storage = Convert.ToString(reader["Inspection_Storage"]);
                    data.Inspection_StorageREF = Convert.ToString(reader["Inspection_StorageREF"]);
                    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.Overfilled = Convert.ToString(reader["Overfilled"]);
                    data.OwnApp = Convert.ToString(reader["OwnApp"]);
                    data.ReceiptStatus = Convert.ToString(reader["ReceiptStatus"]);
                    data.ReceiptStatusRef = Convert.ToString(reader["ReceiptStatusRef"]);
                    data.Receiptremark = Convert.ToString(reader["Receiptremark"]);

                    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.codename = Convert.ToString(reader["codename"]);
                    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.prepayments = Convert.ToString(reader["prepayments"]);
                    data.balance = Convert.ToString(reader["balance"]);
                    //字段过滤器（判断是否存在该列）
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'pay_prepayments'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                    {
                        data.pay_prepayments = Convert.ToString(reader["pay_prepayments"]);
                       
                    }
                    //字段过滤器（判断是否存在该列 InspectionReleaseDate 商检放行日期）
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'InspectionReleaseDate'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                    {
                        data.InspectionReleaseDate = Convert.ToString(reader["InspectionReleaseDate"]);
                    }
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'pay_balance'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                    {
                        
                        data.pay_balance = Convert.ToString(reader["pay_balance"]);
                    }

                    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.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.RMBAmount = Convert.ToString(reader["RMBAmount"]);

                    data.BilltypeREF = Convert.ToString(reader["BilltypeREF"]);
                    data.OP = Convert.ToString(reader["OP"]);
                    data.ZMQ = Convert.ToString(reader["ZMQ"]);
                    data.RecDate = Convert.ToString(reader["RecDate"]);
                    data.PortDays = Convert.ToString(reader["PortDays"]);
                    data.RCVModeREF = Convert.ToString(reader["RCVModeREF"]);
                    data.BUZTYPEREF = Convert.ToString(reader["BUZTYPEREF"]);
                    data.DeliveryDate = Convert.ToString(reader["DeliveryDate"]);
                    data.DeliveryAddress = Convert.ToString(reader["DeliveryAddress"]);

                    data.Inspection_Buyer = Convert.ToString(reader["Inspection_Buyer"]);
                    data.TRADINGAGENCY = Convert.ToString(reader["TRADINGAGENCY"]);
                    data.IsRongZi = Convert.ToString(reader["IsRongZi"]);

                    data.ACCDATE = Convert.ToString(reader["ACCDATE"]);

                    data.CHECKSAMPLE = Convert.ToString(reader["CHECKSAMPLE"]);
                    data.CTN = Convert.ToString(reader["CTN"]);
                    data.BILLTRANCNO = Convert.ToString(reader["BILLTRANCNO"]);
                    data.SBYS = Convert.ToString(reader["SBYS"]);
                    data.BILL_RECEIVE_DATE = Convert.ToString(reader["BILL_RECEIVE_DATE"]);
                    data.BILL_SEND_DATE = Convert.ToString(reader["BILL_SEND_DATE"]);

                    data.LOCK_EXCHANGERATE = Convert.ToString(reader["LOCK_EXCHANGERATE"]);
                    data.EXCHANGERATE = Convert.ToString(reader["M_EXCHANGERATE"]);
                    data.Unsealdate = Convert.ToString(reader["Unsealdate"]);
                    data.ISREFORM = Convert.ToString(reader["ISREFORM"]);
                    data.InspectionNo = Convert.ToString(reader["InspectionNo"]);

                    data.TRADECOUNTRY = Convert.ToString(reader["TRADECOUNTRY"]);
                    data.TRADECOUNTRY_REF = Convert.ToString(reader["TRADECOUNTRY_REF"]);
                    data.CIQDATE = Convert.ToString(reader["CIQDATE"]);
                    data.VSIGN = Convert.ToString(reader["VSIGN"]);
                    data.ClearanceNo = Convert.ToString(reader["ClearanceNo"]);


                    //reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'TAGPICURL'";
                    //if (reader.GetSchemaTable().DefaultView.Count > 0)
                    //    data.TAGPICURL = Convert.ToString(reader["TAGPICURL"]);
                    data.TAGPICURL = Convert.ToString(BasicDataRefDAL.getValue(reader, "TAGPICURL", "string"));
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }

        #endregion

        #region 合同基础信息列表——包括全部商品信息 增加天津誉恒用货代字段

        static public List<XXH_ALLmb> GetDataList_ALL_HD ( string strCondition, string sort )
        {              //合同信息/商品信息  ——每合同一行
            var strSql = new StringBuilder();

            strSql.Append(" SELECT M.[id],M.[ContractNo],[HTH],[seller],M.[company],comp.name as companyname,[buyer],[STT], ");
            strSql.Append(" M.[countryid],[creator],[Auditor],[MainState],[printed],[AutoAPP],M.OP, ");
            strSql.Append(" dbo.F_YN(convert(int,AutoAPP)) AutoAPPRef, ");
            strSql.Append(" dbo.F_YN(convert(int,printed)) printedRef, ");
            strSql.Append(" [ShipCompany_id], [BillNo] ,[BillType],[Vessel],[Voyage], ci.code as cicode,ci.name as ciname,cg.[weight]/1000 [weight_t],cg.[weight], ");
            strSql.Append(" dbo.trimdate(BillTime) BillTime,  ");
            strSql.Append(" dbo.trimdate(Ex_sailingdate) Ex_sailingdate,  ");
            strSql.Append(" dbo.trimdate(Sailingdate) Sailingdate  ,  ");
            strSql.Append(" dbo.trimdate(ArrivalDate) ArrivalDate   ");
            strSql.Append("  ,[ContainerNo],[SealNo],[TransactionMethod],[PaymentMethods]  ");
            strSql.Append("  ,[SecurityDeposit],M.TRADINGAGENCY,   ");
            strSql.Append(" dbo.trimdate(CustomsReleaseDate) CustomsReleaseDate,  ");
            strSql.Append(" dbo.trimdate(SamplingDate_Plan) SamplingDate_Plan, ");
            strSql.Append(" dbo.trimdate(SamplingDate_Act) SamplingDate_Act, ");
            strSql.Append(" dbo.trimdate(inspection_Signup_date) inspection_Signup_date ");
            strSql.Append("  ,[inspection_no],  ");
            strSql.Append(" dbo.trimdate(inspection_date) inspection_date  ");
            strSql.Append("  ,cg.id as cgid ,cg.name,c.country, convert(bigint ,M.TimeMark) as TimeMark ,  ");
            strSql.Append(" dbo.trimdate(creattime) creattime  ,  ");
            strSql.Append(" dbo.trimdate(Audittime) Audittime  ,  ");
            strSql.Append(" Freetime,CIQ_licence,CIQ_canbesearch,dbo.F_YN(convert(int,CIQ_canbesearch)) CIQ_canbesearchRef ,   ");
            strSql.Append(" dbo.trimdate(tax_date) tax_date  ,  ");
            strSql.Append(" dbo.trimdate(tax_paydate) tax_paydate ,  ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=3 and EnumValueID=M.MainState) as MainstateRef,  ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=10 and EnumValueID=M.port) as portRef,  ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=5 and EnumValueID=M.ShipCompany_id) as ShipCompanyRef,  ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=1 and EnumValueID=M.TransactionMethod) as TransactionMethodRef, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=4 and EnumValueID=M.PaymentMethods) as PaymentMethodsRef, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=6 and EnumValueID=M.Billtype) as BilltypeRef, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=16 and EnumValueID=M.Financestatus) as FinancestatusREF, ");
            strSql.Append(" M.port, M.remark,ap.AppNo,ap.ValidDate ,M.Writeoffs,M.ReceiptStatus,M.Receiptremark,dbo.F_YN(convert(int,ReceiptStatus)) ReceiptStatusRef, ");
            strSql.Append(" dbo.F_YN(convert(int,Writeoffs)) WriteoffsRef ,M.OwnApp, ");
            strSql.Append(" M.FinanceStatus,M.remark_2,M.remark_3,M.remark_4,M.Sampling_need,M.DeclareNumber,M.Inspection_Storage,M.DeliveryAddress,M.Contacter,M.Tel,M.Overfilled, ");
            strSql.Append(" dbo.f_yn( Inspection_Storage) Inspection_StorageREF, ");
            strSql.Append(" dbo.trimdate( M.Paydate) as Paydate, ");
            strSql.Append(" dbo.trimdate( M.Prepaydate) as Prepaydate, ");
            strSql.Append(" dbo.trimdate( M.E_Billtime) as E_Billtime,  ");
            strSql.Append(" CONVERT(varchar ,[HDtime],20) HDtime ,  ");
            strSql.Append(" dbo.trimdate( M.DeliveryDate) as DeliveryDate, ");
            strSql.Append(" cg.m_weight/1000 m_weight,cg.price,cg.price_agio,cg.price_limit,cg.amount,cg.amount*cg.Exchangerate RMBAmount ,cg.tax_amount,cg.Exchangerate_Customs*cg.tax_amount tax_amount_RMB,cg.pay_amount,curr.codename,  ");
            strSql.Append(" cg.boxcount,cg.boxweight,cg.exporter,cg.FactoryNo,cg.productiondate,cg.prepayments,cg.balance,cg.baolv,  ");
            strSql.Append(" cg.tariff,cg.tax,cg.tax_zz,cg.exchangerate,cg.exchangerate_customs,cg.tax_1,cg.tax_zz_1,cg.tax_2,cg.tax_zz_2,  ");
            strSql.Append(" M.RCVMode,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=23 and EnumValueID=M.RCVMode) as RCVModeREF  ");
            strSql.Append(" ,dbo.F_YN(convert(int,Sampling_need)) Sampling_needREF  ");
            strSql.Append(" ,case when ap.remark like '%自贸%' then ap.remark else '' end ZMQ ");//自贸区
            strSql.Append(" ,M.MZBZ, (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=23 and EnumValueID=M.MZBZ) as MZBZREF ");
            strSql.Append(" ,dbo.trimdate(RecDate) Recdate,PortDays,M.DeliveryDate,M.DeliveryAddress,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=27 and EnumValueID=M.buztype) buztypeREF ");
            strSql.Append(" ,m.Inspection_Buyer ,M.ACCDATE  ");

            strSql.Append(" ,Convert(varchar(20),Convert(numeric(18,2),isnull((select sum(isnull(amount,0)) from ch_fee where bsno=m.contractno and FEESTATUS in(10,11)),0)))  ");
            strSql.Append(" + '/' + Convert(varchar(20), Convert(numeric(18, 2), isnull((select sum(isnull(settlement, 0) * isnull(EXCHANGERATE, 1)) ");
            strSql.Append(" from ch_fee where bsno in (select F_NO from IMPORT_FINANCE_BODY where BSNO = M.ContractNo) and feetype = 1), 0))) isRongZi ");

            strSql.Append(" ,isnull(m.CHECKSAMPLE,0) CHECKSAMPLE,M.CTN,M.BILLTRANCNO,cg.SBYS ");
            strSql.Append(" ,(select top 1 dbo.trimdate(RECEIVE_DATE) from op_receipt where bsno=M.contractno and m.BillType=0) BILL_RECEIVE_DATE ");
            strSql.Append(" ,(select top 1 dbo.trimdate(SEND_DATE) from op_receipt where bsno=M.contractno and m.BillType=0) BILL_SEND_DATE ");
            strSql.Append(" ,LOCK_EXCHANGERATE,M.EXCHANGERATE M_EXCHANGERATE ");
            strSql.Append(" ,dbo.trimdate(m.Unsealdate) Unsealdate,M.ISREFORM,ic.InspectionNo");
            strSql.Append(" ,Supplier_Billno,dbo.trimdate(ResendTime) ResendTime,dbo.trimtime(ResendTime2) ResendTime2,ShipAgent, CONVERT(varchar ,[BillReleaseDate],20) BillReleaseDate ");
            strSql.Append(" ,IsLBill,LBillShipAgent,IsInnerLine,InnerLinePort,YARD,dbo.trimdate(PriceLimitDate) PriceLimitDate ");
            strSql.Append(" ,PriceLimitRemark,ClearanceNo,dbo.trimtime(ClearanceDate) ClearanceDate,dbo.trimdate(TaxRecvDate) TaxRecvDate ");
            strSql.Append(" ,dbo.trimdate(GetBoxDate) GetBoxDate,IsRec,dbo.trimdate(FUNDDATE_1) FUNDDATE_1,M.TaxRecvRemark,M.TaxPayRemark");
            strSql.Append(" ,dbo.trimdate(M.HTDATE) HTDATE  ");
            strSql.Append("    FROM [dbo].[Import_main] M   ");
            strSql.Append("  left join code_country c on m.countryid=c.countryid  ");
            strSql.Append("  left join import_cargo cg on cg.contractno=M.contractno  ");
            strSql.Append("  left join import_cargoinfo ci on ci.id=cg.cargoinfo_id  ");
            strSql.Append("  left join company comp on comp.gid=M.company  ");
            strSql.Append("  left join import_appstate ast on ast.cargo_id=cg.id  ");
            strSql.Append("  left join import_approval ap on ap.id=ast.app_id  ");
            strSql.Append("  left join [code_currency] curr on curr.gid=cg.currid ");
            strSql.Append("  left join Info_client ic on cg.Exporter =ic.shortname ");

            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }
            /*
            strSql.Append(" union all select ");
            strSql.Append(" '','','','','','','','','','','','','','','','','','','','','','','','',   ");
            strSql.Append(" sum(cg.[weight])/1000 [weight_t],sum(cg.[weight])[weight], ");
            strSql.Append(" '','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','',  ");
            strSql.Append(" '','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','',  ");
            strSql.Append(" sum(cg.m_weight/1000) ,0,0,0,sum(cg.amount) ,sum(cg.amount*cg.Exchangerate), ");
            strSql.Append(" sum(cg.tax_amount) ,sum(cg.tax_amount*cg.Exchangerate_Customs), sum(cg.pay_amount) , '',  sum(cg.boxcount),0,'','',  ");
            strSql.Append(" '',sum(cg.prepayments),sum(cg.balance),0,  '',0,0,0,0,sum(cg.tax_1),  ");
            strSql.Append(" sum(cg.tax_zz_1),sum(cg.tax_2),sum(cg.tax_zz_2),'','','','','','','','','','','','','','1','0','','','','','',0,'','','','' FROM [dbo].[Import_main] M  ");
            strSql.Append(" left join code_country c on m.countryid=c.countryid  ");
            strSql.Append(" left join import_cargo cg on cg.contractno=M.contractno  ");
            strSql.Append(" left join import_cargoinfo ci on ci.id=cg.cargoinfo_id  ");
            strSql.Append(" left join company comp on comp.gid=M.company  ");
            strSql.Append(" left join import_appstate ast on ast.cargo_id=cg.id  ");
            strSql.Append(" left join import_approval ap on ap.id=ast.app_id  ");
            strSql.Append(" left join [code_currency] curr on curr.gid=cg.currid  ");
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }*/
            return SetData_ALL_HD(strSql);
        }

        static public XXH_ALLmb GetData_ALL_HD ( string condition )
        {

            var list = GetDataList_ALL_HD(condition, "");
            if (list.Count > 0)
                return list[0];

            return new XXH_ALLmb();
        }

        private static List<XXH_ALLmb> SetData_ALL_HD ( StringBuilder strSql )
        {
            var headList = new List<XXH_ALLmb>();
            Database db = DatabaseFactory.CreateDatabase();
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = strSql.ToString();
            cmd.CommandTimeout = 120000;   //要加这一句
            using (IDataReader reader = db.ExecuteReader(cmd))
            {
                while (reader.Read())
                {
                    XXH_ALLmb data = new XXH_ALLmb();
                    #region Set DB data to Object
                    data.cgid = Convert.ToString(reader["cgid"]);
                    data.name = Convert.ToString(reader["name"]);
                    data.country = Convert.ToString(reader["country"]);
                    data.weight_t = Convert.ToString(reader["weight_t"]);
                    data.weight = Convert.ToString(reader["weight"]);

                    data.id = Convert.ToDecimal(reader["id"]);
                    data.ContractNo = Convert.ToString(reader["ContractNo"]);
                    data.HTH = Convert.ToString(reader["HTH"]);
                    data.HTDATE = Convert.ToString(reader["HTDATE"]);
                    data.seller = Convert.ToString(reader["seller"]);
                    data.companyname = Convert.ToString(reader["companyname"]);
                    data.buyer = Convert.ToString(reader["buyer"]);
                    data.STT = Convert.ToString(reader["STT"]);
                    data.countryid = Convert.ToString(reader["countryid"]);
                    data.creator = Convert.ToString(reader["creator"]);
                    data.Auditor = Convert.ToString(reader["Auditor"]);
                    data.Mainstate = Convert.ToString(reader["Mainstate"]);
                    data.MainstateRef = Convert.ToString(reader["MainstateRef"]);
                    data.Printed = Convert.ToString(reader["Printed"]);
                    data.AutoAPP = Convert.ToString(reader["AutoAPP"]);

                    data.ShipCompany_id = Convert.ToString(reader["ShipCompany_id"]);
                    data.BillNo = Convert.ToString(reader["BillNo"]);
                    data.BillType = Convert.ToString(reader["BillType"]);
                    data.BillTime = Convert.ToString(reader["BillTime"]);
                    data.Vessel = Convert.ToString(reader["Vessel"]);
                    data.Voyage = Convert.ToString(reader["Voyage"]);
                    data.Ex_sailingdate = Convert.ToString(reader["Ex_sailingdate"]);
                    data.Sailingdate = Convert.ToString(reader["Sailingdate"]);
                    data.ArrivalDate = Convert.ToString(reader["ArrivalDate"]);

                    data.ContainerNo = Convert.ToString(reader["ContainerNo"]);
                    data.SealNo = Convert.ToString(reader["SealNo"]);
                    data.TransactionMethod = Convert.ToString(reader["TransactionMethod"]);
                    data.PaymentMethods = Convert.ToString(reader["PaymentMethods"]);
                    data.TransactionMethodRef = Convert.ToString(reader["TransactionMethodRef"]);
                    data.PaymentMethodsRef = Convert.ToString(reader["PaymentMethodsRef"]);

                    data.SecurityDeposit = Convert.ToString(reader["SecurityDeposit"]);

                    data.SamplingDate_Plan = Convert.ToString(reader["SamplingDate_Plan"]);
                    data.SamplingDate_Act = Convert.ToString(reader["SamplingDate_Act"]);
                    data.CustomsReleaseDate = Convert.ToString(reader["CustomsReleaseDate"]);
                    data.inspection_Signup_date = Convert.ToString(reader["inspection_Signup_date"]);
                    data.inspection_no = Convert.ToString(reader["inspection_no"]);
                    data.inspection_date = Convert.ToString(reader["inspection_date"]);
                    data.TimeMark = Convert.ToDecimal(reader["TimeMark"]);
                    data.cicode = Convert.ToString(reader["cicode"]);
                    data.ciname = Convert.ToString(reader["ciname"]);

                    data.company = Convert.ToString(reader["company"]);

                    data.creattime = Convert.ToString(reader["creattime"]);
                    data.Audittime = Convert.ToString(reader["Audittime"]);
                    data.Freetime = Convert.ToString(reader["Freetime"]);
                    data.CIQ_licence = Convert.ToString(reader["CIQ_licence"]);
                    data.CIQ_canbesearch = Convert.ToString(reader["CIQ_canbesearch"]);
                    data.CIQ_canbesearchRef = Convert.ToString(reader["CIQ_canbesearchRef"]);
                    data.tax_date = Convert.ToString(reader["tax_date"]);
                    data.tax_paydate = Convert.ToString(reader["tax_paydate"]);
                    data.port = Convert.ToString(reader["port"]);
                    data.remark = Convert.ToString(reader["remark"]);
                    data.portRef = Convert.ToString(reader["portRef"]);
                    data.AppNo = Convert.ToString(reader["AppNo"]);
                    data.ValidDate = Convert.ToString(reader["ValidDate"]);
                    data.Writeoffs = Convert.ToString(reader["Writeoffs"]);
                    data.WriteoffsRef = Convert.ToString(reader["WriteoffsRef"]);

                    data.AutoAPPRef = Convert.ToString(reader["AutoAPPRef"]);
                    data.printedRef = Convert.ToString(reader["printedRef"]);
                    data.ShipCompanyRef = Convert.ToString(reader["ShipCompanyRef"]);

                    data.Paydate = Convert.ToString(reader["Paydate"]);
                    data.Prepaydate = Convert.ToString(reader["Prepaydate"]);
                    data.E_Billtime = Convert.ToString(reader["E_Billtime"]);
                    data.HDtime = Convert.ToString(reader["HDtime"]);
                    data.FinanceStatus = Convert.ToString(reader["FinanceStatus"]);
                    data.FinanceStatusREF = Convert.ToString(reader["FinanceStatusREF"]);
                    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.Sampling_need = Convert.ToString(reader["Sampling_need"]);
                    data.Sampling_needREF = Convert.ToString(reader["Sampling_needREF"]);

                    data.DeclareNumber = Convert.ToString(reader["DeclareNumber"]);
                    data.Inspection_Storage = Convert.ToString(reader["Inspection_Storage"]);
                    data.Inspection_StorageREF = Convert.ToString(reader["Inspection_StorageREF"]);
                    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.Overfilled = Convert.ToString(reader["Overfilled"]);
                    data.OwnApp = Convert.ToString(reader["OwnApp"]);
                    data.ReceiptStatus = Convert.ToString(reader["ReceiptStatus"]);
                    data.ReceiptStatusRef = Convert.ToString(reader["ReceiptStatusRef"]);
                    data.Receiptremark = Convert.ToString(reader["Receiptremark"]);

                    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.codename = Convert.ToString(reader["codename"]);
                    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.prepayments = Convert.ToString(reader["prepayments"]);
                    data.balance = Convert.ToString(reader["balance"]);
                    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.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.RMBAmount = Convert.ToString(reader["RMBAmount"]);

                    data.BilltypeREF = Convert.ToString(reader["BilltypeREF"]);
                    data.OP = Convert.ToString(reader["OP"]);
                    data.ZMQ = Convert.ToString(reader["ZMQ"]);
                    data.RecDate = Convert.ToString(reader["RecDate"]);
                    data.PortDays = Convert.ToString(reader["PortDays"]);
                    data.RCVModeREF = Convert.ToString(reader["RCVModeREF"]);
                    data.BUZTYPEREF = Convert.ToString(reader["BUZTYPEREF"]);
                    data.DeliveryDate = Convert.ToString(reader["DeliveryDate"]);
                    data.DeliveryAddress = Convert.ToString(reader["DeliveryAddress"]);

                    data.Inspection_Buyer = Convert.ToString(reader["Inspection_Buyer"]);
                    data.TRADINGAGENCY = Convert.ToString(reader["TRADINGAGENCY"]);
                    data.IsRongZi = Convert.ToString(reader["IsRongZi"]);

                    data.ACCDATE = Convert.ToString(reader["ACCDATE"]);

                    data.CHECKSAMPLE = Convert.ToString(reader["CHECKSAMPLE"]);
                    data.CTN = Convert.ToString(reader["CTN"]);
                    data.BILLTRANCNO = Convert.ToString(reader["BILLTRANCNO"]);
                    data.SBYS = Convert.ToString(reader["SBYS"]);
                    data.BILL_RECEIVE_DATE = Convert.ToString(reader["BILL_RECEIVE_DATE"]);
                    data.BILL_SEND_DATE = Convert.ToString(reader["BILL_SEND_DATE"]);

                    data.LOCK_EXCHANGERATE = Convert.ToString(reader["LOCK_EXCHANGERATE"]);
                    data.EXCHANGERATE = Convert.ToString(reader["M_EXCHANGERATE"]);
                    data.Unsealdate = Convert.ToString(reader["Unsealdate"]);
                    data.ISREFORM = Convert.ToString(reader["ISREFORM"]);
                    data.InspectionNo = Convert.ToString(reader["InspectionNo"]);

                    data.Supplier_Billno = Convert.ToString(reader["Supplier_Billno"]);
                    data.ResendTime = Convert.ToString(reader["ResendTime"]);
                    data.ResendTime2 = Convert.ToString(reader["ResendTime2"]);
                    data.ShipAgent = Convert.ToString(reader["ShipAgent"]);
                    data.BillReleaseDate = Convert.ToString(reader["BillReleaseDate"]);
                    data.IsLBill = Convert.ToString(reader["IsLBill"]);
                    data.LBillShipAgent = Convert.ToString(reader["LBillShipAgent"]);
                    data.IsInnerLine = Convert.ToString(reader["IsInnerLine"]);
                    data.InnerLinePort = Convert.ToString(reader["InnerLinePort"]);
                    data.YARD = Convert.ToString(reader["YARD"]);
                    data.PriceLimitDate = Convert.ToString(reader["PriceLimitDate"]);
                    data.PriceLimitRemark = Convert.ToString(reader["PriceLimitRemark"]);
                    data.ClearanceNo = Convert.ToString(reader["ClearanceNo"]);
                    data.ClearanceDate = Convert.ToString(reader["ClearanceDate"]);
                    data.TaxRecvDate = Convert.ToString(reader["TaxRecvDate"]);
                    data.GetBoxDate = Convert.ToString(reader["GetBoxDate"]);
                    data.IsRec = Convert.ToString(reader["IsRec"]);
                    data.FUNDDATE_1 = Convert.ToString(reader["FUNDDATE_1"]);

                    data.Supplier_Billno = Convert.ToString(reader["Supplier_Billno"]);
                    data.ResendTime = Convert.ToString(reader["ResendTime"]);
                    data.ShipAgent = Convert.ToString(reader["ShipAgent"]);
                    data.BillReleaseDate = Convert.ToString(reader["BillReleaseDate"]);
                    data.IsLBill = Convert.ToString(reader["IsLBill"]);
                    data.LBillShipAgent = Convert.ToString(reader["LBillShipAgent"]);
                    data.IsInnerLine = Convert.ToString(reader["IsInnerLine"]);
                    data.InnerLinePort = Convert.ToString(reader["InnerLinePort"]);
                    data.YARD = Convert.ToString(reader["YARD"]);
                    data.PriceLimitDate = Convert.ToString(reader["PriceLimitDate"]);
                    data.PriceLimitRemark = Convert.ToString(reader["PriceLimitRemark"]);
                    //data.ClearanceNo = Convert.ToString(reader["ClearanceNo"]);
                    data.ClearanceDate = Convert.ToString(reader["ClearanceDate"]);
                    data.TaxRecvDate = Convert.ToString(reader["TaxRecvDate"]);
                    data.GetBoxDate = Convert.ToString(reader["GetBoxDate"]);
                    //data.IsRec = Convert.ToString(reader["IsRec"]);
                    data.ResendTime2 = Convert.ToString(reader["ResendTime2"]);

                    data.TaxRecvRemark = Convert.ToString(reader["TaxRecvRemark"]);
                    data.TaxPayRemark = Convert.ToString(reader["TaxPayRemark"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }

        #endregion

        #region 港口操作 编辑模式

        static public List<XXH_ALLmb> GetEditList_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 M.preArrivalDate,M.InventoryState,M.[id],M.[ContractNo],[HTH],[seller],M.[company],comp.name as companyname,[buyer],[STT], (select top 1 cc.NAME from company cc where cc.GID = M.CORPID) CORPNAME,");
            strSql.Append(" M.[countryid],[creator],[Auditor],[MainState],[printed],[AutoAPP],M.OP, ");
            strSql.Append(" dbo.F_YN(convert(int,AutoAPP)) AutoAPPRef, ");
            strSql.Append(" dbo.F_YN(convert(int,printed)) printedRef, ");
            strSql.Append(" [ShipCompany_id], [BillNo] ,[BillType],[Vessel],[Voyage], ci.code as cicode,ci.name as ciname,cg.[weight]/1000 [weight], ");
            strSql.Append(" dbo.trimdate(BillTime) BillTime,  ");
            strSql.Append(" dbo.trimdate(Ex_sailingdate) Ex_sailingdate,  ");
            strSql.Append(" dbo.trimdate(Sailingdate) Sailingdate  ,  ");
            strSql.Append(" dbo.trimdate(ArrivalDate) ArrivalDate   ");
            strSql.Append("  ,[ContainerNo],[SealNo],[TransactionMethod],[PaymentMethods]  ");
            strSql.Append("  ,[SecurityDeposit],M.TRADINGAGENCY,   ");
            strSql.Append(" dbo.trimdate(CustomsReleaseDate) CustomsReleaseDate,  ");
            strSql.Append(" dbo.trimdate(InspectionReleaseDate) InspectionReleaseDate,  ");
            strSql.Append(" dbo.trimdate(SamplingDate_Plan) SamplingDate_Plan, ");
            strSql.Append(" dbo.trimdate(SamplingDate_Act) SamplingDate_Act, ");
            strSql.Append(" dbo.trimdate(inspection_Signup_date) inspection_Signup_date ");
            strSql.Append("  ,[inspection_no],  ");
            strSql.Append(" dbo.trimdate(inspection_date) inspection_date  ");
            strSql.Append("  ,cg.id as cgid ,cg.name,c.country, convert(bigint ,M.TimeMark) as TimeMark ,  ");
            strSql.Append(" dbo.trimdate(creattime) creattime  ,  ");
            strSql.Append(" dbo.trimdate(Audittime) Audittime  ,  ");
            strSql.Append(" Freetime,CIQ_licence,CIQ_canbesearch,dbo.F_YN(convert(int,CIQ_canbesearch)) CIQ_canbesearchRef ,   ");
            strSql.Append(" dbo.trimdate(tax_date) tax_date  ,  ");
            strSql.Append(" dbo.trimdate(tax_paydate) tax_paydate ,  ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=3 and EnumValueID=M.MainState) as MainstateRef,  ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=10 and EnumValueID=M.port) as portRef,  ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=5 and EnumValueID=M.ShipCompany_id) as ShipCompanyRef,  ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=1 and EnumValueID=M.TransactionMethod) as TransactionMethodRef, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=4 and EnumValueID=M.PaymentMethods) as PaymentMethodsRef, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=6 and EnumValueID=M.Billtype) as BilltypeRef, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=16 and EnumValueID=M.Financestatus) as FinancestatusREF, ");
            strSql.Append(" M.port, M.remark,ap.AppNo,ap.ValidDate ,M.Writeoffs,M.ReceiptStatus,M.Receiptremark,dbo.F_YN(convert(int,ReceiptStatus)) ReceiptStatusRef, ");
            strSql.Append(" dbo.F_YN(convert(int,Writeoffs)) WriteoffsRef ,M.OwnApp, ");
            strSql.Append(" M.FinanceStatus,M.remark_2,M.remark_3,M.remark_4,M.Sampling_need,M.DeclareNumber,M.Inspection_Storage,M.DeliveryAddress,M.Contacter,M.Tel,M.Overfilled, ");
            strSql.Append(" dbo.f_yn( Inspection_Storage) Inspection_StorageREF, ");
            strSql.Append(" dbo.trimdate(M.Paydate) Paydate, ");
            strSql.Append(" dbo.trimdate(M.Prepaydate) Prepaydate, ");
            strSql.Append(" dbo.trimdate(M.E_Billtime) E_Billtime,  ");
            strSql.Append(" dbo.trimdate(M.HDtime) HDtime ,  ");
            strSql.Append(" dbo.trimdate(M.DeliveryDate) DeliveryDate, ");
            strSql.Append(" cg.m_weight/1000 m_weight,cg.price,cg.price_agio,cg.price_limit,cg.amount,cg.amount*cg.Exchangerate RMBAmount ,cg.tax_amount,cg.Exchangerate_Customs*cg.tax_amount tax_amount_RMB,cg.pay_amount,curr.codename,  ");
            strSql.Append(" cg.boxcount,cg.boxweight,cg.exporter,cg.FactoryNo,cg.productiondate,cg.prepayments,cg.balance,cg.baolv,  ");
            strSql.Append(" cg.tariff,cg.tax,cg.tax_zz,cg.exchangerate,cg.exchangerate_customs,cg.tax_1,cg.tax_zz_1,cg.tax_2,cg.tax_zz_2,  ");
            strSql.Append(" M.RCVMode,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=23 and EnumValueID=M.RCVMode) as RCVModeREF  ");
            strSql.Append(" ,dbo.F_YN(convert(int,Sampling_need)) Sampling_needREF  ");
            strSql.Append(" ,case when ap.remark like '%自贸%' then ap.remark else '' end ZMQ ");//自贸区
            strSql.Append(" ,M.MZBZ, (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=23 and EnumValueID=M.MZBZ) as MZBZREF ");
            strSql.Append(" ,dbo.trimdate(RecDate) Recdate,PortDays,M.DeliveryDate,M.DeliveryAddress,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=27 and EnumValueID=M.buztype) buztypeREF ");
            strSql.Append(" ,m.Inspection_Buyer,M.ACCDATE   ");

            strSql.Append(" ,Convert(varchar(20),Convert(numeric(18,2),isnull((select sum(isnull(amount,0)) from ch_fee where bsno=m.contractno and FEESTATUS in(10,11)),0)))  ");
            strSql.Append(" + '/' + Convert(varchar(20), Convert(numeric(18, 2), isnull((select sum(isnull(settlement, 0) * isnull(EXCHANGERATE, 1)) ");
            strSql.Append(" from ch_fee where bsno in (select F_NO from IMPORT_FINANCE_BODY where BSNO = M.ContractNo) and feetype = 1), 0))) isRongZi ");

            strSql.Append(" ,isnull(m.CHECKSAMPLE,0) CHECKSAMPLE,M.CTN,M.BILLTRANCNO,cg.SBYS ");
            strSql.Append(" ,(select top 1 dbo.trimdate(RECEIVE_DATE) from op_receipt where bsno=M.contractno and m.BillType=0) BILL_RECEIVE_DATE ");
            strSql.Append(" ,(select top 1 dbo.trimdate(SEND_DATE) from op_receipt where bsno=M.contractno and m.BillType=0) BILL_SEND_DATE ");
            strSql.Append(" ,LOCK_EXCHANGERATE,M.EXCHANGERATE M_EXCHANGERATE");
            strSql.Append(" ,dbo.trimdate(m.Unsealdate) Unsealdate,M.ISREFORM");
            strSql.Append(" ,(case M.InventoryState WHEN 0 THEN '过车' WHEN 1 THEN '直送' WHEN 2 THEN '入库' WHEN 3 THEN '部分过车' ELSE '' END) InventoryStateREF");
            strSql.Append(" ,dbo.trimdate(M.HTDATE) HTDATE ");

            strSql.Append("  FROM [dbo].[Import_main] M   ");
            strSql.Append("  left join code_country c on m.countryid=c.countryid  ");
            strSql.Append("  left join import_cargo cg on cg.contractno=M.contractno  ");
            strSql.Append("  left join import_cargoinfo ci on ci.id=cg.cargoinfo_id  ");
            strSql.Append("  left join company comp on comp.gid=M.company  ");
            strSql.Append("  left join import_appstate ast on ast.cargo_id=cg.id  ");
            strSql.Append("  left join import_approval ap on ap.id=ast.app_id  ");
            strSql.Append("  left join [code_currency] curr on curr.gid=cg.currid ");

            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }

            return SetData_Edit_ALL(strSql);
        }


        private static List<XXH_ALLmb> SetData_Edit_ALL ( StringBuilder strSql )
        {
            var headList = new List<XXH_ALLmb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    XXH_ALLmb data = new XXH_ALLmb();
                    #region Set DB data to Object
                    data.cgid = Convert.ToString(reader["cgid"]);
                    data.name = Convert.ToString(reader["name"]);
                    data.country = Convert.ToString(reader["country"]);
                    data.weight = Convert.ToString(reader["weight"]);
                    //字段过滤器（判断是否存在该列）
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'CORPNAME'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                        data.CORPNAME = Convert.ToString(reader["CORPNAME"]);
                    //字段过滤器（判断是否存在该列）
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'preArrivalDate'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                    {
                        if (!string.IsNullOrEmpty(reader["preArrivalDate"].ToString()))
                            data.preArrivalDate = Convert.ToString(reader["preArrivalDate"]);
                    }

                    //字段过滤器（判断是否存在该列）
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'InspectionReleaseDate'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                    {
                        if (!string.IsNullOrEmpty(reader["InspectionReleaseDate"].ToString()))
                            data.InspectionReleaseDate = Convert.ToString(reader["InspectionReleaseDate"]);
                    }

                    //字段过滤器（判断是否存在该列）
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'InventoryState'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                    {
                        if (!string.IsNullOrEmpty(reader["InventoryState"].ToString()))
                            data.InventoryState = Convert.ToInt32(reader["InventoryState"]);
                        data.InventoryStateRef = Convert.ToString(reader["InventoryStateRef"]);

                    }
                    data.id = Convert.ToDecimal(reader["id"]);
                    data.ContractNo = Convert.ToString(reader["ContractNo"]);
                    data.HTH = Convert.ToString(reader["HTH"]);
                    data.HTDATE = Convert.ToString(reader["HTDATE"]);
                    data.seller = Convert.ToString(reader["seller"]);
                    data.companyname = Convert.ToString(reader["companyname"]);
                    data.buyer = Convert.ToString(reader["buyer"]);
                    data.STT = Convert.ToString(reader["STT"]);
                    data.countryid = Convert.ToString(reader["countryid"]);
                    data.creator = Convert.ToString(reader["creator"]);
                    data.Auditor = Convert.ToString(reader["Auditor"]);
                    data.Mainstate = Convert.ToString(reader["Mainstate"]);
                    data.MainstateRef = Convert.ToString(reader["MainstateRef"]);
                    data.Printed = Convert.ToString(reader["Printed"]);
                    data.AutoAPP = Convert.ToString(reader["AutoAPP"]);

                    data.ShipCompany_id = Convert.ToString(reader["ShipCompany_id"]);
                    data.BillNo = Convert.ToString(reader["BillNo"]);
                    data.BillType = Convert.ToString(reader["BillType"]);
                    data.BillTime = Convert.ToString(reader["BillTime"]);
                    data.Vessel = Convert.ToString(reader["Vessel"]);
                    data.Voyage = Convert.ToString(reader["Voyage"]);
                    data.Ex_sailingdate = Convert.ToString(reader["Ex_sailingdate"]);
                    data.Sailingdate = Convert.ToString(reader["Sailingdate"]);
                    data.ArrivalDate = Convert.ToString(reader["ArrivalDate"]);

                    data.ContainerNo = Convert.ToString(reader["ContainerNo"]);
                    data.SealNo = Convert.ToString(reader["SealNo"]);
                    data.TransactionMethod = Convert.ToString(reader["TransactionMethod"]);
                    data.PaymentMethods = Convert.ToString(reader["PaymentMethods"]);
                    data.TransactionMethodRef = Convert.ToString(reader["TransactionMethodRef"]);
                    data.PaymentMethodsRef = Convert.ToString(reader["PaymentMethodsRef"]);

                    data.SecurityDeposit = Convert.ToString(reader["SecurityDeposit"]);

                    data.SamplingDate_Plan = Convert.ToString(reader["SamplingDate_Plan"]);
                    data.SamplingDate_Act = Convert.ToString(reader["SamplingDate_Act"]);
                    data.CustomsReleaseDate = Convert.ToString(reader["CustomsReleaseDate"]);
                    data.inspection_Signup_date = Convert.ToString(reader["inspection_Signup_date"]);
                    data.inspection_no = Convert.ToString(reader["inspection_no"]);
                    data.inspection_date = Convert.ToString(reader["inspection_date"]);
                    data.TimeMark = Convert.ToDecimal(reader["TimeMark"]);
                    data.cicode = Convert.ToString(reader["cicode"]);
                    data.ciname = Convert.ToString(reader["ciname"]);

                    data.company = Convert.ToString(reader["company"]);

                    data.creattime = Convert.ToString(reader["creattime"]);
                    data.Audittime = Convert.ToString(reader["Audittime"]);
                    data.Freetime = Convert.ToString(reader["Freetime"]);
                    data.CIQ_licence = Convert.ToString(reader["CIQ_licence"]);
                    data.CIQ_canbesearch = Convert.ToString(reader["CIQ_canbesearch"]);
                    data.CIQ_canbesearchRef = Convert.ToString(reader["CIQ_canbesearchRef"]);
                    data.tax_date = Convert.ToString(reader["tax_date"]);
                    data.tax_paydate = Convert.ToString(reader["tax_paydate"]);
                    data.port = Convert.ToString(reader["port"]);
                    data.remark = Convert.ToString(reader["remark"]);
                    data.portRef = Convert.ToString(reader["portRef"]);
                    data.AppNo = Convert.ToString(reader["AppNo"]);
                    data.ValidDate = Convert.ToString(reader["ValidDate"]);
                    data.Writeoffs = Convert.ToString(reader["Writeoffs"]);
                    data.WriteoffsRef = Convert.ToString(reader["WriteoffsRef"]);

                    data.AutoAPPRef = Convert.ToString(reader["AutoAPPRef"]);
                    data.printedRef = Convert.ToString(reader["printedRef"]);
                    data.ShipCompanyRef = Convert.ToString(reader["ShipCompanyRef"]);

                    data.Paydate = Convert.ToString(reader["Paydate"]);
                    data.Prepaydate = Convert.ToString(reader["Prepaydate"]);
                    data.E_Billtime = Convert.ToString(reader["E_Billtime"]);
                    data.HDtime = Convert.ToString(reader["HDtime"]);
                    data.FinanceStatus = Convert.ToString(reader["FinanceStatus"]);
                    data.FinanceStatusREF = Convert.ToString(reader["FinanceStatusREF"]);
                    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.Sampling_need = Convert.ToString(reader["Sampling_need"]);
                    data.Sampling_needREF = Convert.ToString(reader["Sampling_needREF"]);

                    data.DeclareNumber = Convert.ToString(reader["DeclareNumber"]);
                    data.Inspection_Storage = Convert.ToString(reader["Inspection_Storage"]);
                    data.Inspection_StorageREF = Convert.ToString(reader["Inspection_StorageREF"]);
                    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.Overfilled = Convert.ToString(reader["Overfilled"]);
                    data.OwnApp = Convert.ToString(reader["OwnApp"]);
                    data.ReceiptStatus = Convert.ToString(reader["ReceiptStatus"]);
                    data.ReceiptStatusRef = Convert.ToString(reader["ReceiptStatusRef"]);
                    data.Receiptremark = Convert.ToString(reader["Receiptremark"]);


                    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.codename = Convert.ToString(reader["codename"]);
                    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.prepayments = Convert.ToString(reader["prepayments"]);
                    data.balance = Convert.ToString(reader["balance"]);
                    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.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.RMBAmount = Convert.ToString(reader["RMBAmount"]);

                    data.BilltypeREF = Convert.ToString(reader["BilltypeREF"]);
                    data.OP = Convert.ToString(reader["OP"]);
                    data.ZMQ = Convert.ToString(reader["ZMQ"]);
                    data.RecDate = Convert.ToString(reader["RecDate"]);
                    data.PortDays = Convert.ToString(reader["PortDays"]);
                    data.RCVModeREF = Convert.ToString(reader["RCVModeREF"]);
                    data.BUZTYPEREF = Convert.ToString(reader["BUZTYPEREF"]);
                    data.DeliveryDate = Convert.ToString(reader["DeliveryDate"]);
                    data.DeliveryAddress = Convert.ToString(reader["DeliveryAddress"]);

                    data.Inspection_Buyer = Convert.ToString(reader["Inspection_Buyer"]);
                    data.TRADINGAGENCY = Convert.ToString(reader["TRADINGAGENCY"]);
                    data.IsRongZi = Convert.ToString(reader["IsRongZi"]);

                    data.ACCDATE = Convert.ToString(reader["ACCDATE"]);

                    data.CHECKSAMPLE = Convert.ToString(reader["CHECKSAMPLE"]);
                    data.CTN = Convert.ToString(reader["CTN"]);
                    data.BILLTRANCNO = Convert.ToString(reader["BILLTRANCNO"]);
                    data.SBYS = Convert.ToString(reader["SBYS"]);
                    data.BILL_RECEIVE_DATE = Convert.ToString(reader["BILL_RECEIVE_DATE"]);
                    data.BILL_SEND_DATE = Convert.ToString(reader["BILL_SEND_DATE"]);
                    data.LOCK_EXCHANGERATE = Convert.ToString(reader["LOCK_EXCHANGERATE"]);
                    data.EXCHANGERATE = Convert.ToString(reader["M_EXCHANGERATE"]);
                    data.Unsealdate = Convert.ToString(reader["Unsealdate"]);
                    data.ISREFORM = Convert.ToString(reader["ISREFORM"]);
                    //data.REGISTRATIONNO = Convert.ToString(reader["REGISTRATIONNO"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region 合同基础信息列表——包括全部商品信息，但每合同仅一行

        static public List<XXH_ALLmb> GetDataList_ALL_single(string strCondition, string userid, string username, string companyid)
        {
            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 * from (SELECT row_number() over (");

            strSql.Append("  SELECT dbo.trimdate([preArrivalDate]) preArrivalDate ,M.[id],M.[ContractNo],[HTH],[seller],M.[company],comp.name as companyname,[buyer],[STT],(select top 1 cc.NAME from company cc where cc.GID = M.CORPID) CORPNAME,  ");
            strSql.Append(" M.[countryid],[creator],[Auditor],(select showname from [user] where codename=M.auditor) AuditorREF,[MainState],[printed],[AutoAPP],M.OP,  ");
            strSql.Append(" dbo.F_YN(convert(int,AutoAPP)) AutoAPPRef,  dbo.F_YN(convert(int,printed)) printedRef,  ");
            strSql.Append(" [ShipCompany_id], [BillNo] ,[BillType],[Vessel],[Voyage], '' cicode,'' ciname,(select SUM(weight)/1000 from import_cargo where contractno=M.contractno) [weight_t], ");
            strSql.Append(" (select SUM(weight) from import_cargo where contractno=M.contractno) [weight], ");
            strSql.Append(" dbo.trimdate([BillTime]) BillTime,   ");
            strSql.Append(" dbo.trimdate([Ex_sailingdate]) Ex_sailingdate,");
            strSql.Append(" dbo.trimdate([Sailingdate]) Sailingdate  ,  ");
            strSql.Append(" dbo.trimdate([ArrivalDate]) ArrivalDate  ,   ");
            strSql.Append(" [ContainerNo],[SealNo],[TransactionMethod],[PaymentMethods]    ,[SecurityDeposit],M.TRADINGAGENCY,    ");
            strSql.Append(" dbo.trimdate(CustomsReleaseDate) CustomsReleaseDate, ");
            strSql.Append(" dbo.trimdate([SamplingDate_Plan]) SamplingDate_Plan,  ");
            strSql.Append(" dbo.trimdate([SamplingDate_Act]) SamplingDate_Act,  ");
            strSql.Append(" dbo.trimdate([inspection_Signup_date]) inspection_Signup_date ");
            strSql.Append(" ,[inspection_no],   dbo.trimdate([inspection_date]) inspection_date ");
            strSql.Append(" ,0 cgid ,dbo.f_str(m.contractno) name,c.country, convert(bigint ,M.TimeMark) as TimeMark ,   ");
            strSql.Append(" dbo.trimdate([creattime]) creattime  ,  ");
            strSql.Append(" dbo.trimdate([Audittime]) Audittime  ,   ");
            strSql.Append(" Freetime,CIQ_licence,CIQ_canbesearch,dbo.F_YN(convert(int,CIQ_canbesearch)) CIQ_canbesearchRef ,    ");
            strSql.Append(" dbo.trimdate([tax_date]) tax_date  ,  ");
            strSql.Append(" dbo.trimdate([tax_paydate]) tax_paydate ,   ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=3 and EnumValueID=M.MainState) as MainstateRef, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=10 and EnumValueID=M.port) as portRef,   ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=5 and EnumValueID=M.ShipCompany_id) as ShipCompanyRef,  ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=1 and EnumValueID=M.TransactionMethod) as TransactionMethodRef, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=4 and EnumValueID=M.PaymentMethods) as PaymentMethodsRef, ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=6 and EnumValueID=M.Billtype) as BilltypeRef,  ");
            strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=16 and EnumValueID=M.Financestatus) as FinancestatusREF,  ");
            strSql.Append(" M.port, M.remark,(select top 1 ap.AppNo from import_approval ap left join import_appstate aps on(aps.app_id = ap.id) left join import_cargo cg on(cg.id = aps.cargo_id)");
            strSql.Append(" where cg.ContractNo =M.ContractNo) AppNo,'' ValidDate ,M.Writeoffs,M.ReceiptStatus,M.Receiptremark,dbo.F_YN(convert(int,ReceiptStatus)) ReceiptStatusRef, ");
            strSql.Append(" dbo.F_YN(convert(int,Writeoffs)) WriteoffsRef ,M.OwnApp,  M.FinanceStatus,M.remark_2,M.remark_3,M.remark_4,M.Sampling_need,M.DeclareNumber,");
            strSql.Append(" M.Inspection_Storage,M.DeliveryAddress,M.Contacter,M.Tel,M.Overfilled,  dbo.f_yn( Inspection_Storage) Inspection_StorageREF, ");
            strSql.Append(" dbo.trimdate(M.Paydate) Paydate,  ");
            strSql.Append(" dbo.trimdate(M.Prepaydate) Prepaydate, ");
            strSql.Append(" dbo.trimdate(M.E_Billtime) E_Billtime,   ");
            strSql.Append(" dbo.trimdate(M.HDtime) HDtime ,   ");
            strSql.Append(" dbo.trimdate(M.DeliveryDate) DeliveryDate  ");
            strSql.Append(" ,(select top 1 m_weight/1000 from import_cargo where contractno=m.contractno) m_weight");
            strSql.Append(" ,dbo.f_price(m.contractno) price,0 price_agio,0 price_limit ,dbo.f_amount(M.contractno) amount,");
            strSql.Append(" (select sum(amount*Exchangerate) from import_cargo where contractno=M.contractno) RMBAmount  ");
            strSql.Append(" ,(select sum(tax_amount) from import_cargo where contractno=M.contractno) tax_amount,");
            strSql.Append(" (select sum(Exchangerate_Customs*tax_amount) from import_cargo where contractno=M.contractno) tax_amount_RMB ");
            strSql.Append(" ,(select sum(pay_amount) from import_cargo where contractno=M.contractno) pay_amount,curr.codename,   ");
            strSql.Append(" dbo.F_count(M.contractno) boxcount,(select top 1 boxweight from import_cargo where contractno=m.contractno) boxweight ");
            strSql.Append(" ,(select top 1 exporter from import_cargo where contractno=m.contractno) exporter,dbo.f_FactoryNo(M.contractno) FactoryNo");
            strSql.Append(" ,dbo.f_productiondate(m.contractno) productiondate ,(select sum(prepayments) from import_cargo where contractno=M.contractno) prepayments");
            strSql.Append(" ,(select sum(balance) from import_cargo where contractno=M.contractno) balance");
            strSql.Append(" ,(select top 1 baolv from import_cargo where contractno=m.contractno) baolv   ");
            strSql.Append(" ,(select top 1 tariff from import_cargo where contractno=m.contractno) tariff");
            strSql.Append(" ,(select sum(tax) from import_cargo where contractno=M.contractno) tax");
            strSql.Append(" ,(select sum(tax_zz) from import_cargo where contractno=M.contractno) tax_zz  ");
            strSql.Append(" ,(select top 1 exchangerate from import_cargo where contractno=m.contractno) exchangerate");
            strSql.Append(" ,(select top 1 exchangerate_customs from import_cargo where contractno=m.contractno) exchangerate_customs  ");
            strSql.Append(" ,(select sum(tax_1) from import_cargo where contractno=M.contractno) tax_1,");
            strSql.Append(" (select sum(tax_zz_1) from import_cargo where contractno=M.contractno) tax_zz_1,  ");
            strSql.Append(" (select sum(tax_2) from import_cargo where contractno=M.contractno) tax_2,");
            strSql.Append(" (select sum(tax_zz_2) from import_cargo where contractno=M.contractno) tax_zz_2,   ");
            strSql.Append(" M.RCVMode,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=23 and EnumValueID=M.RCVMode) as RCVModeREF   ");
            strSql.Append(" ,dbo.F_YN(convert(int,Sampling_need)) Sampling_needREF   ,0 ZMQ  ,M.MZBZ");
            strSql.Append(" , (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=23 and EnumValueID=M.MZBZ) as MZBZREF  ");
            strSql.Append(" ,dbo.trimdate(RecDate) Recdate,PortDays,M.DeliveryDate,M.DeliveryAddress,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=27 and EnumValueID=M.buztype) buztypeREF ");
            strSql.Append(" ,m.Inspection_Buyer ,M.ACCDATE  ");

            strSql.Append(" ,Convert(varchar(20),Convert(numeric(18,2),isnull((select sum(isnull(amount,0)) from ch_fee where bsno=m.contractno and FEESTATUS in(10,11)),0)))  ");
            strSql.Append(" + '/' + Convert(varchar(20), Convert(numeric(18, 2), isnull((select sum(isnull(settlement, 0) * isnull(EXCHANGERATE, 1)) ");
            strSql.Append(" from ch_fee where bsno in (select F_NO from IMPORT_FINANCE_BODY where BSNO = M.ContractNo) and feetype = 1), 0))) isRongZi ");

            strSql.Append(" ,isnull(m.CHECKSAMPLE,0) CHECKSAMPLE,M.CTN,M.BILLTRANCNO,'' SBYS ");
            strSql.Append(" ,(select top 1 dbo.trimdate(RECEIVE_DATE) from op_receipt where bsno=M.contractno and m.BillType=0) BILL_RECEIVE_DATE ");
            strSql.Append(" ,(select top 1 dbo.trimdate(SEND_DATE) from op_receipt where bsno=M.contractno and m.BillType=0) BILL_SEND_DATE ");
            strSql.Append(" ,LOCK_EXCHANGERATE,M.EXCHANGERATE M_EXCHANGERATE");
            strSql.Append(" ,dbo.trimdate(m.Unsealdate) Unsealdate,M.ISREFORM,'' InspectionNo");
            strSql.Append(" ,TRADECOUNTRY,(select country from code_country where countryid=TRADECOUNTRY) TRADECOUNTRY_REF,dbo.trimdate(CIQDATE) CIQDATE,isnull(VSIGN,'') VSIGN");
            strSql.Append(" ,M.ClearanceNo,M.InventoryState,(case M.InventoryState WHEN 0 THEN '过车' WHEN 1 THEN '直送' WHEN 2 THEN '入库' WHEN 3 THEN '部分过车' ELSE '' END) InventoryStateREF");
            strSql.Append(",(select CODENAME + '-' + SHOWNAME from[user] where[CODENAME] = M.Creator) RECEIVE_MAN ");
            strSql.Append(" ,(select sum(pay_balance) from import_cargo where contractno=M.contractno) pay_balance ");
            strSql.Append(" ,dbo.trimdate(M.HTDATE) HTDATE  ");

            strSql.Append(" FROM [dbo].[Import_main] M     ");
            strSql.Append(" left join code_country c on m.countryid=c.countryid    ");
            strSql.Append(" left join company comp on comp.gid=M.company    ");
            strSql.Append(" left join [code_currency] curr on curr.gid=(select top  1 currid from import_cargo where contractno=M.contractno)  ");

            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }

            strSql.Append(" union all select '', '','','','','','','','','','','','','','','','','','','','','','','','','','',     ");
            strSql.Append(" convert(varchar(30),sum(cg.[weight])/1000) [weight_t],convert(varchar(30),sum(cg.[weight])) [weight],  '','','','','','','','','','','','','','','','','','','','','','','','','','','','','',''   ");
            strSql.Append(" ,'','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','',''  ");
            strSql.Append(" ,sum(cg.m_weight/1000) ,'',0,0,sum(cg.amount) ,sum(cg.amount*cg.Exchangerate),  sum(cg.tax_amount)  ");
            strSql.Append(" ,sum(cg.tax_amount*cg.Exchangerate_Customs), sum(cg.pay_amount) , '',  convert(varchar(10),sum(cg.boxcount)),0,'','' ");
            strSql.Append(" ,'',sum(cg.prepayments),sum(cg.balance),0,  '',0,0,0,0 ");
            strSql.Append(" ,sum(cg.tax_1),   sum(cg.tax_zz_1),sum(cg.tax_2),sum(cg.tax_zz_2),'','','','','','','','' ,'','','','','','1','0','','','','','',0,'','','','' ,'','','','','',-1,'','' ,sum(cg.pay_balance) ");
            strSql.Append(" ,'' ");

            strSql.Append(" FROM [dbo].[Import_main] M    ");
            strSql.Append(" left join code_country c on m.countryid=c.countryid    ");
            strSql.Append(" left join import_cargo cg on cg.contractno=M.contractno  ");
            strSql.Append(" left join import_cargoinfo ci on ci.id=cg.cargoinfo_id   ");
            strSql.Append(" left join company comp on comp.gid=M.company    ");
            strSql.Append(" left join import_appstate ast on ast.cargo_id=cg.id   ");
            strSql.Append(" left join import_approval ap on ap.id=ast.app_id    ");
            strSql.Append(" left join [code_currency] curr on curr.gid=cg.currid  ");
            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }
            return SetData_ALL(strSql);
        }

        #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 List<HTmb> GetDataList_HT ( string strCondition )
        {              //合同信息/商品信息
            var strSql = new StringBuilder();
            strSql.Append("  select count(*) [Count] from import_main ");

            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }
            return SetData_HT(strSql);
        }
        private static List<HTmb> SetData_HT ( StringBuilder strSql )
        {
            var headList = new List<HTmb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    HTmb data = new HTmb();
                    #region Set DB data to Object
                    //data.ContractNo = Convert.ToString(reader["ContractNo"]);
                    //data.HTH = Convert.ToString(reader["HTH"]);
                    data.Count = Convert.ToString(reader["Count"]);

                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region 查询费用数量

        static public List<HTmb> GetChfee ( string strCondition )
        {              //合同信息/商品信息
            var strSql = new StringBuilder();
            strSql.Append("  select count(*) [Count] from ch_fee ");

            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" where " + strCondition);
            }
            return SetChfee(strSql);
        }
        static public List<HTmb> GetSales ( string strCondition )
        {              //合同信息/商品信息
            var strSql = new StringBuilder();
            strSql.Append("  select count(*) [Count] from Import_Saledetail where exists(select 1 from import_sales where salesno=Import_Saledetail.salesno and isnull(mainstate,2)=2 ) ");

            if (!string.IsNullOrEmpty(strCondition))
            {
                strSql.Append(" and " + strCondition);
            }
            return SetChfee(strSql);
        }
        private static List<HTmb> SetChfee ( StringBuilder strSql )
        {
            var headList = new List<HTmb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    HTmb data = new HTmb();
                    #region Set DB data to Object
                    //data.ContractNo = Convert.ToString(reader["ContractNo"]);
                    //data.HTH = Convert.ToString(reader["HTH"]);
                    data.Count = Convert.ToString(reader["Count"]);

                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region  获取最近一次库存操作时间
        static public List<CZmb> GetLastCZ ( string strCondition, string id, string corpid, string cargo_id )
        {
            var strSql = new StringBuilder();
            strSql.Append(" select ik.czstate,ik.czdate,ik.contractno from import_kc ik ");
            strSql.Append(" where czstate in(1,6) and corpid=(select gid from company where codename='" + corpid + "') and ik.contractno='" + strCondition + "'  ");
            strSql.Append(" and cargo_id=" + cargo_id + " ");
            if (!string.IsNullOrEmpty(strCondition))
            {
                if (id != "")
                {
                    strSql.Append(" and ik.id<" + id + " ");
                }
                else
                {
                   
                }
            }
            strSql.Append("  order by czdate desc ");
            return SetLastCZ(strSql);
        }
        private static List<CZmb> SetLastCZ ( StringBuilder strSql )
        {
            var headList = new List<CZmb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    CZmb data = new CZmb();
                    #region Set DB data to Object
                    //data.ContractNo = Convert.ToString(reader["ContractNo"]);
                    //data.HTH = Convert.ToString(reader["HTH"]);
                    data.CZDate = Convert.ToString(reader["CZDate"]);
                    data.CZstate = Convert.ToString(reader["CZstate"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region  获取日期差
        static public int GetDays ( string date1, string CZDATE )
        {
            var strSql = new StringBuilder();
            strSql.Append(" select datediff(day,'" + date1 + "','" + CZDATE + "') days");


            return SetDays(strSql);
        }
        private static int SetDays ( StringBuilder strSql )
        {
            //var headList = new List<CZmb>();
            var days = 0;
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    CZmb data = new CZmb();
                    #region Set DB data to Object
                    //data.ContractNo = Convert.ToString(reader["ContractNo"]);
                    //data.HTH = Convert.ToString(reader["HTH"]);
                    days = Convert.ToInt32(reader["days"]) + 1;
                    //data.CZstate = Convert.ToString(reader["CZstate"]);
                    #endregion
                    //headList.Add(data);
                }
                reader.Close();
            }
            return days;
        }
        #endregion

        #region  通过箱号获取合同序列号
        static public string GetContractNo ( string ContainerNo )
        {
            var strSql = new StringBuilder();
            strSql.Append(" select contractno from import_main where ContainerNo like '%" + ContainerNo + "%'");


            return SetContractNo(strSql);
        }
        private static string SetContractNo ( StringBuilder strSql )
        {
            //var headList = new List<CZmb>();
            var contractno = "";
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    //CZmb data = new CZmb();
                    #region Set DB data to Object
                    //data.ContractNo = Convert.ToString(reader["ContractNo"]);
                    //data.HTH = Convert.ToString(reader["HTH"]);
                    contractno = Convert.ToString(reader["contractno"]);
                    //data.CZstate = Convert.ToString(reader["CZstate"]);
                    #endregion
                    //headList.Add(data);
                }
                reader.Close();
            }
            return contractno;
        }
        #endregion

        #region  通过合同序列号获取客户名称
        static public string GetCust ( string ContractNo )
        {
            var strSql = new StringBuilder();
            strSql.Append(" select Buyer from import_main where ContractNo='" + ContractNo + "'");


            return SetCust(strSql);
        }
        private static string SetCust ( StringBuilder strSql )
        {
            //var headList = new List<CZmb>();
            var Buyer = "";
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    //CZmb data = new CZmb();
                    #region Set DB data to Object
                    //data.ContractNo = Convert.ToString(reader["ContractNo"]);
                    //data.HTH = Convert.ToString(reader["HTH"]);
                    Buyer = Convert.ToString(reader["Buyer"]);
                    //data.CZstate = Convert.ToString(reader["CZstate"]);
                    #endregion
                    //headList.Add(data);
                }
                reader.Close();
            }
            return Buyer;
        }
        #endregion

        #region  通过合同序列号获取合同号
        static public string GetHTH ( string ContractNo )
        {
            var strSql = new StringBuilder();
            strSql.Append(" select HTH from import_main where ContractNo='" + ContractNo + "'");


            return SetHTH(strSql);
        }
        private static string SetHTH ( StringBuilder strSql )
        {
            //var headList = new List<CZmb>();
            var HTH = "";
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    //CZmb data = new CZmb();
                    #region Set DB data to Object
                    //data.ContractNo = Convert.ToString(reader["ContractNo"]);
                    //data.HTH = Convert.ToString(reader["HTH"]);
                    HTH = Convert.ToString(reader["HTH"]);
                    //data.CZstate = Convert.ToString(reader["CZstate"]);
                    #endregion
                    //headList.Add(data);
                }
                reader.Close();
            }
            return HTH;
        }
        #endregion

        #region  获取毛利
        static public List<MLmb> GetML ( string condition,string bsno )
        {              //获取毛利
            var strSql = new StringBuilder();

            strSql.Append(" select sum(RMBYS) RMBYS,sum(RMBYF) RMBYF,sum(RMBYS)-sum(RMBYF) RMBML, ");
            strSql.Append(" sum(isnull(USDYS,0)) USDYS,sum(isnull(USDYF,0)) USDYF,sum(isnull(USDYS,0)) -sum(isnull(USDYF,0)) USDML ");
            strSql.Append(" ,sum(isnull(RMBYS,0))-sum(isnull(RMBYF,0))+sum(isnull(USDRMBYS,0)) -sum(USDRMBYF) ML ");
            strSql.Append(" ,sum(isnull(FHPROFIT,0)) FHPROFIT,sum(isnull(LCPROFIT,0)) LCPROFIT,sum(isnull(OTPROFIT,0)) OTPROFIT ");
            strSql.Append(" from( ");
	
	        strSql.Append(" select sum(isnull(amount,0)) RMBYS,0 RMBYF,0 USDYS,0 USDYF,0 USDRMBYS,0 USDRMBYF ,0 FHPROFIT,0 LCPROFIT,0 OTPROFIT ");
            strSql.Append(" from ch_fee where feetype=1 and feestatus in (0,8,9,10,11) and " + condition + " and currency='RMB' ");
	        strSql.Append(" union all ");
            strSql.Append(" select 0,sum(isnull(amount,0)) ,0,0,0,0 ,0,0,0 from ch_fee where feetype=2 and feestatus in (0,8,9,10,11) and  " + condition + " and currency='RMB' ");
	        strSql.Append(" union all ");
            strSql.Append(" select 0,0,sum(isnull(amount,0)) ,0,0,0 ,0,0,0 from ch_fee where feetype=1 and feestatus in (0,8,9,10,11) and  " + condition + " and currency='USD' ");
	        strSql.Append(" union all ");
            strSql.Append(" select 0,0,0,sum(isnull(amount,0)),0,0 ,0,0,0  from ch_fee where feetype=2 and feestatus in (0,8,9,10,11) and  " + condition + " and currency='USD' ");
	        strSql.Append(" union all ");
            strSql.Append(" select 0,0,0,0,sum(isnull(amount,0)*EXCHANGERATE),0 ,0,0,0 from ch_fee where feetype=1 and feestatus in (0,8,9,10,11) and " + condition + " and currency='USD' ");
	        strSql.Append(" union all ");
            strSql.Append(" select 0,0,0,0,0,sum(isnull(amount,0)*EXCHANGERATE) ,0,0,0 from ch_fee where feetype=2 and feestatus in (0,8,9,10,11) and " + condition + " and currency='USD' ");
	        strSql.Append(" union all ");
            strSql.Append(" select 0,0,0,0,0,0,convert(numeric(18,2), FHPROFIT),convert(numeric(18,2), LCPROFIT),convert(numeric(18,2), OTPROFIT) from vxxhprofitsum where bsno='" + bsno + "'  ");
	        strSql.Append(" )t1 ");

            return SetML(strSql);
        }
        private static List<MLmb> SetML ( StringBuilder strSql )
        {
            var headList = new List<MLmb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    MLmb data = new MLmb();
                    #region Set DB data to Object
                    data.RMBYS = Convert.ToString(reader["RMBYS"]);
                    data.RMBYF = Convert.ToString(reader["RMBYF"]);
                    data.RMBML = Convert.ToString(reader["RMBML"]);
                    data.USDYS = Convert.ToString(reader["USDYS"]);
                    data.USDYF = Convert.ToString(reader["USDYF"]);
                    data.USDML = Convert.ToString(reader["USDML"]);
                    data.ML = Convert.ToString(reader["ML"]);

                    data.FHPROFIT = Convert.ToString(reader["FHPROFIT"]);
                    data.LCPROFIT = Convert.ToString(reader["LCPROFIT"]);
                    data.OTPROFIT = Convert.ToString(reader["OTPROFIT"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }

        #endregion

        #region  合同信息/商品信息
        static public List<AppCargomb> GetAppCargo ( string app_id, string cargo_id )
        {              //合同信息/商品信息
            var strSql = new StringBuilder();
            strSql.Append(" select case when((tt.id1=tt.id3) and (tt.id2=tt.id4)) then 1 else 0 end as match from ");
            strSql.Append(" (select a.cargoinfo_id id1,a.cargociq_id id2 , c.cargoinfo_id id3,c.cargociq_id id4 ");
            strSql.Append(" from import_approval a,import_cargo c where a.id=" + app_id + " and c.id=" + cargo_id + ")tt ");

            //if (!string.IsNullOrEmpty(strCondition))
            //{
            //    strSql.Append(" where " + strCondition);
            //}
            return SetAppCargo(strSql);
        }

        private static List<AppCargomb> SetAppCargo ( StringBuilder strSql )
        {
            var headList = new List<AppCargomb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    AppCargomb data = new AppCargomb();
                    #region Set DB data to Object
                    //data.ContractNo = Convert.ToString(reader["ContractNo"]);
                    //data.HTH = Convert.ToString(reader["HTH"]);
                    data.Match = Convert.ToString(reader["Match"]);

                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region  合同内的许可证使用统计
        static public List<HTStatemb> GetHTState ( string contractno )
        {              //合同信息/商品信息
            var strSql = new StringBuilder();
            strSql.Append(" select ap.appno,sum(st.weight) USEDWEIGHT from import_appstate st  ");
            strSql.Append(" left join import_approval ap on ap.id=st.app_id ");
            strSql.Append(" where contractno='" + contractno + "' group by ap.appno ");

            //if (!string.IsNullOrEmpty(strCondition))
            //{
            //    strSql.Append(" where " + strCondition);
            //}
            return SetHTState(strSql);
        }

        private static List<HTStatemb> SetHTState ( StringBuilder strSql )
        {
            var headList = new List<HTStatemb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    HTStatemb data = new HTStatemb();
                    #region Set DB data to Object
                    data.APPNO = Convert.ToString(reader["APPNO"]);
                    data.USEDWEIGHT = Convert.ToString(reader["USEDWEIGHT"]);

                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region  导入库存列表的excel
        /*
         用于新协航天津分公司录入
         * 规则如下
         * 1：出库/入库 库存操作为 出入库费 费用名为出入库费
         * 2：结转 库存操作为 结算(未出库) 费用名为冷藏费
         * excel格式如下：
         * 序号	起日期1	止日期2	仓库3	箱号4	商品名称5	操作类型6	件数7	重量(吨)8	单价9	金额10	备注11
            1	2014-12-01	2014-12-15	一洲鼎鲜	MMAU1129023	猪头	出库	1429 	25.722 	2	771.66	商定此处写库房名字
         */
        public enum RowState : uint
        {
            Insert,
            Update,
            None
        }
        public class Feemb : ModelObjectBase
        {
            private string _gid = string.Empty;
            private string _BSNO = string.Empty;
            private string _FeeName = string.Empty;
            private string _FeeType = string.Empty;
            private string _FeeStatus = string.Empty;
            private string _EnterOperator = string.Empty;
            private string _CustomerName = string.Empty;
            private string _UnitPrice = string.Empty;
            private string _Quantity = string.Empty;
            private string _Amount = string.Empty;
            private string _Currency = string.Empty;
            private string _ExchangeRate = string.Empty;
            private string _remark = string.Empty;
            private string _EnterDate = string.Empty;
            private string _WMSOUTBSNO = string.Empty;

            public string gid
            {
                get { return _gid; }
                set { _gid = value; }
            }
            public string BSNO
            {
                get { return _BSNO; }
                set { _BSNO = value; }
            }
            public string FeeName
            {
                get { return _FeeName; }
                set { _FeeName = value; }
            }
            public string FeeType
            {
                get { return _FeeType; }
                set { _FeeType = value; }
            }
            public string FeeStatus
            {
                get { return _FeeStatus; }
                set { _FeeStatus = value; }
            }
            public string EnterOperator
            {
                get { return _EnterOperator; }
                set { _EnterOperator = value; }
            }
            public string CustomerName
            {
                get { return _CustomerName; }
                set { _CustomerName = value; }
            }
            public string UnitPrice
            {
                get { return _UnitPrice; }
                set { _UnitPrice = value; }
            }
            public string Quantity
            {
                get { return _Quantity; }
                set { _Quantity = value; }
            }
            public string Amount
            {
                get { return _Amount; }
                set { _Amount = value; }
            }
            public string Currency
            {
                get { return _Currency; }
                set { _Currency = value; }
            }
            public string ExchangeRate
            {
                get { return _ExchangeRate; }
                set { _ExchangeRate = value; }
            }
            public string remark
            {
                get { return _remark; }
                set { _remark = value; }
            }
            public string EnterDate
            {
                get { return _EnterDate; }
                set { _EnterDate = value; }
            }
            public string WMSOUTBSNO
            {
                get { return _WMSOUTBSNO; }
                set { _WMSOUTBSNO = value; }
            }
        }

        public static bool ImportExcelData ( HttpRequestBase request, DataTable table, out string msg, out Int32 InsertCount,
                out Int32 UpdateCount, out string UnknownKF )
        {
            var isSucess = false;
            msg = "";
            //headList = new List<KCmb>();
            var corpid = CookieConfig.GetCookie_OrgCode(request);
            var userid = CookieConfig.GetCookie_UserId(request);
            if (table == null) throw new ArgumentNullException("table");

            var billNoList = new List<string>();
            Database db = DatabaseFactory.CreateDatabase();
            InsertCount = 0;
            UpdateCount = 0;
            UnknownKF = "";

            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                try
                {
                    var orgCode = CookieConfig.GetCookie_OrgCode(request);
                    var userCode = CookieConfig.GetCookie_UserCode(request);
                    var userName = CookieConfig.GetCookie_UserName(request);
                    var USERID = CookieConfig.GetCookie_UserId(request);
                    var _i = 0;
                    var CZDATE = "";
                    foreach (DataRow row in table.Rows)
                    {
                        _i++;
                        if (_i < 1) continue;
                        var _CZDATE = Convert.ToString(row[2]);
                        _CZDATE = _CZDATE.Replace("/", "-");
                        _CZDATE = _CZDATE.Replace(".", "-");
                        if (_CZDATE == "")
                        { }
                        else
                        {
                            CZDATE = _CZDATE;
                        }

                        var date1 = Convert.ToString(row[1]);
                        date1 = date1.Replace("/", "-");
                        date1 = date1.Replace(".", "-");

                        var Days = GetDays(date1, CZDATE);

                        var ContainerNo = Convert.ToString(row[4]);
                        ContainerNo = ContainerNo.Trim();
                        if (ContainerNo.Trim() == "")
                        {
                            continue;
                        }
                        var ContractNo = GetContractNo(ContainerNo);

                        var KFSTATE = Convert.ToString(row[3]);
                        KFSTATE = KFSTATE.Trim();
                        if (KFSTATE.Trim() == "")
                        {
                            continue;
                        }
                        var objKFNAME = db.ExecuteScalar(CommandType.Text, "select * from info_client where ISWAREHOUSE=1 and shortname='" + KFSTATE + "'");
                        var _KFSTATE = Convert.ToString(objKFNAME);
                        if (string.IsNullOrEmpty(_KFSTATE))
                        {
                            if (UnknownKF == "") { UnknownKF = "在如下合同中，有如下库房在系统中不存在，请添加后重新导入：" + GetHTH(ContractNo) + ":" + KFSTATE; }
                            else UnknownKF = UnknownKF + "/" + GetHTH(ContractNo) + ":" + KFSTATE;
                            continue;

                        }


                        DbTransaction idbTran = connection.BeginTransaction();

                        var cargo_id = Convert.ToString(row[5]);
                        var cz = Convert.ToString(row[6]);
                        var czstate = "";
                        var FeeName = "";
                        var InNum = "0";
                        var OutNum = "0";
                        switch (cz)
                        {
                            case "入库":
                                czstate = "1";
                                FeeName = "出入库费";
                                InNum = Convert.ToString(row[7]);
                                break;
                            case "出库":
                                czstate = "3";
                                FeeName = "出入库费";
                                OutNum = Convert.ToString(row[7]);
                                break;
                            case "结转":
                                czstate = "6";
                                FeeName = "冷藏费";
                                break;
                            default:
                                FeeName = "冷藏费";
                                break;
                        }

                        //var Num = Convert.ToString(row[7]);
                        var Quantity = Convert.ToString(row[8]);
                        var UnitPrice = Convert.ToString(row[9]);
                        var Amount = Convert.ToString(row[10]);
                        var text = Convert.ToString(row[11]);
                        var _RowState = RowState.Insert;
                        //var ContractNo = GetContractNo(ContainerNo);

                        string strCheckRow = "select top 1 GID from import_kc where ContractNo = '" + ContractNo + "' and czdate='" + CZDATE
                            + "' and czstate=" + czstate + "' and corpid='" + corpid + "'";
                        object statusObj = SqlHelper.ExecuteScalar(db.ConnectionString, CommandType.Text, strCheckRow, null);

                        if (statusObj == null)
                        {
                            _RowState = RowState.Insert;
                        }
                        else
                        {
                            _RowState = RowState.Update;
                        }
                        _RowState = RowState.Insert;
                        if (_RowState == RowState.Insert)
                        {
                            #region 库存数据生成

                            //var billNo = PubSysDAL.GetBillNo("0119");
                            billNoList.Add(ContainerNo);
                            //var gid = Guid.NewGuid().ToString("N").ToUpper();

                            const string insertSql = @"insert into Import_kc (ContractNo,CZDate,CZstate,kfstate,cargo_id,text,days,UnitPrice,
                                        Quantity,Amount,FeeName,InNum,OutNum,CORPID,ENTEROPERATOR,GID)
                                  values(@ContractNo,@CZDate,@CZstate,@kfstate,@cargo_id,@text,@days,@UnitPrice,
                                        @Quantity,@Amount,@FeeName,@InNum,@OutNum,@CORPID,@ENTEROPERATOR,@GID)";
                            //cargo_id,@cargo_id,
                            DbCommand cmd = db.GetSqlStringCommand(insertSql);

                            db.AddInParameter(cmd, "ContractNo", DbType.String, ContractNo);
                            db.AddInParameter(cmd, "CZDate", DbType.String, CZDATE);
                            db.AddInParameter(cmd, "CZstate", DbType.String, czstate);
                            db.AddInParameter(cmd, "kfstate", DbType.String, KFSTATE);
                            db.AddInParameter(cmd, "cargo_id", DbType.String, cargo_id);
                            db.AddInParameter(cmd, "text", DbType.String, text);
                            db.AddInParameter(cmd, "days", DbType.String, Days);
                            db.AddInParameter(cmd, "UnitPrice", DbType.String, UnitPrice);
                            db.AddInParameter(cmd, "Quantity", DbType.String, Quantity);
                            db.AddInParameter(cmd, "Amount", DbType.String, Amount);
                            db.AddInParameter(cmd, "FeeName", DbType.String, FeeName);
                            db.AddInParameter(cmd, "InNum", DbType.String, InNum);
                            db.AddInParameter(cmd, "OutNum", DbType.String, OutNum);
                            db.AddInParameter(cmd, "CORPID", DbType.String, corpid);
                            db.AddInParameter(cmd, "ENTEROPERATOR", DbType.String, userid);

                            var GID = Guid.NewGuid().ToString("N").ToUpper();
                            db.AddInParameter(cmd, "GID", DbType.String, GID);

                            db.ExecuteNonQuery(cmd, idbTran);
                            idbTran.Commit();

                            InsertCount++;

                            //插入费用
                            var Fee = new Feemb();
                            Fee.gid = Guid.NewGuid().ToString("N").ToUpper();
                            Fee.BSNO = ContractNo;
                            Fee.FeeName = FeeName;
                            Fee.FeeType = "1";//应收
                            Fee.FeeStatus = "1";//录入状态
                            Fee.EnterOperator = USERID;//录入状态
                            Fee.CustomerName = GetCust(ContractNo);
                            Fee.UnitPrice = UnitPrice;
                            Fee.Quantity = Quantity;
                            Fee.Amount = Amount;
                            Fee.Currency = "RMB";
                            Fee.ExchangeRate = "1";
                            Fee.remark = text;
                            Fee.EnterDate = CZDATE;
                            Fee.WMSOUTBSNO = GID;
                            if (InsertFee(Fee))
                            {
                                Fee.FeeType = "2";
                                Fee.CustomerName = KFSTATE;
                                Fee.gid = Guid.NewGuid().ToString("N").ToUpper();
                                Fee.CustomerName = "上海周送";
                                if (InsertFee(Fee))
                                    continue;
                            }

                            #endregion

                        }
                        else if (_RowState == RowState.Update)
                        {
                            #region 库存数据更新
                            /*
                            billNoList.Add(ContractNo);
                            //text=@text,
                            
                            const string UpdSql = @" Update Import_kc set days=@days,
                                    UnitPrice=@UnitPrice,Quantity=@Quantity,Amount=@Amount  
                                where contractno=@ContractNo and CZDATE=@CZDATE and CORPID=@CORPID";
                            DbCommand cmd = db.GetSqlStringCommand(UpdSql);

                            //db.AddInParameter(cmd, "ContractNo", DbType.String, ContractNo);
                            //db.AddInParameter(cmd, "CZDate", DbType.String, CZDATE);
                            db.AddInParameter(cmd, "days", DbType.String, Days);
                            db.AddInParameter(cmd, "UnitPrice", DbType.String, UnitPrice);
                            db.AddInParameter(cmd, "Quantity", DbType.String, Quantity);
                            db.AddInParameter(cmd, "Amount", DbType.String, Amount);
                            //db.AddInParameter(cmd, "CORPID", DbType.String, corpid);

                            db.ExecuteNonQuery(cmd, idbTran);
                            idbTran.Commit();
                            UpdateCount++;

                            //获取该条kc的gid
                            var GID = Convert.ToString(statusObj);

                            
                            //插入费用
                            var Fee = new Feemb();
                            Fee.gid = Guid.NewGuid().ToString("N").ToUpper();
                            Fee.BSNO = ContractNo;
                            switch (cz)
                            {
                                case "入库":
                                    Fee.FeeName = "出入库费";
                                    break;
                                case "出库":
                                    Fee.FeeName = "出入库费";
                                    break;
                                case "结转":
                                    Fee.FeeName = "冷藏费";
                                    break;
                                default:
                                    Fee.FeeName = "冷藏费";
                                    break;
                            }
                            Fee.FeeType = "1";//应收
                            Fee.FeeStatus = "1";//录入状态
                            Fee.EnterOperator = USERID;//录入状态
                            Fee.CustomerName = GetCust(ContractNo);
                            Fee.UnitPrice = UnitPrice;
                            Fee.Quantity = Quantity;
                            Fee.Amount = Amount;
                            Fee.Currency = "RMB";
                            Fee.ExchangeRate = "1";
                            Fee.remark = text;
                            Fee.EnterDate = CZDATE;
                            Fee.WMSOUTBSNO = GID;
                            if (InsertFee(Fee))
                            {
                                Fee.FeeType = "2";
                                Fee.CustomerName = KFSTATE;
                                Fee.gid = Guid.NewGuid().ToString("N").ToUpper();
                                Fee.CustomerName = "上海周送";
                                if (InsertFee(Fee))
                                    continue;
                            }
                            */
                            #endregion
                        }
                    }

                    #region 获取新生成的托单费用

                    foreach (var billNo in billNoList)
                    {
                        //var head = MsWl_HH_ZYDAL.GetData(" ContractNo='" + billNo + "'");
                        //headList.Add(head);
                    }

                    #endregion
                    isSucess = true;
                }
                catch (Exception exception)
                {
                    //idbTran.Rollback();
                    isSucess = false;
                    msg = exception.Message;
                }
            }

            return isSucess;
        }


        public static bool InsertFee ( Feemb Fee )
        {
            Database db = DatabaseFactory.CreateDatabase();
            var isSucess = false;
            var msg = "";

            string strCheckRow = "select * from ch_fee where WMSOUTBSNO = '" + Fee.WMSOUTBSNO + "' ";
            object statusObj = SqlHelper.ExecuteScalar(db.ConnectionString, CommandType.Text, strCheckRow, null);
            var Type = RowState.Update;
            if (statusObj == null)
            {
                Type = RowState.Insert;
            }

            using (DbConnection connection = db.CreateConnection())
            {
                connection.Open();
                try
                {
                    if (Type == RowState.Insert)
                    {
                        #region 插入费用

                        const string insertSql = @"insert into ch_fee (gid,BSNO,FeeName,FeeType,FeeStatus,EnterOperator,CustomerName,UnitPrice,Quantity,Amount
                                    ,Currency,ExchangeRate,remark,EnterDate,WMSOUTBSNO)
                                  values(@gid,@BSNO,@FeeName,@FeeType,@FeeStatus,@EnterOperator,@CustomerName,@UnitPrice,@Quantity,@Amount
                                    ,@Currency,@ExchangeRate,@remark,@EnterDate,@WMSOUTBSNO)";
                        //cargo_id,@cargo_id,
                        DbCommand cmd = db.GetSqlStringCommand(insertSql);
                        DbTransaction idbTran = connection.BeginTransaction();

                        db.AddInParameter(cmd, "gid", DbType.String, Fee.gid);
                        db.AddInParameter(cmd, "BSNO", DbType.String, Fee.BSNO);
                        db.AddInParameter(cmd, "FeeName", DbType.String, Fee.FeeName);
                        db.AddInParameter(cmd, "EnterDate", DbType.String, Fee.EnterDate);

                        db.AddInParameter(cmd, "FeeType", DbType.String, Fee.FeeType);
                        db.AddInParameter(cmd, "FeeStatus", DbType.String, Fee.FeeStatus);
                        db.AddInParameter(cmd, "EnterOperator", DbType.String, Fee.EnterOperator);
                        db.AddInParameter(cmd, "CustomerName", DbType.String, Fee.CustomerName);
                        db.AddInParameter(cmd, "UnitPrice", DbType.String, Fee.UnitPrice);
                        db.AddInParameter(cmd, "Quantity", DbType.String, Fee.Quantity);
                        db.AddInParameter(cmd, "Amount", DbType.String, Fee.Amount);
                        db.AddInParameter(cmd, "Currency", DbType.String, Fee.Currency);
                        db.AddInParameter(cmd, "ExchangeRate", DbType.String, Fee.ExchangeRate);
                        db.AddInParameter(cmd, "remark", DbType.String, Fee.remark);
                        db.AddInParameter(cmd, "WMSOUTBSNO", DbType.String, Fee.WMSOUTBSNO);

                        db.ExecuteNonQuery(cmd, idbTran);
                        idbTran.Commit();

                        #endregion

                    }
                    else if (Type == RowState.Update)
                    {
                        #region 费用生成,更新

                        const string UpdSql = @" Update ch_fee set FeeType=@FeeType,FeeStatus=@FeeStatus,EnterOperator=@EnterOperator,CustomerName=@CustomerName,
                                UnitPrice=@UnitPrice,Quantity=@Quantity,Amount=@Amount,Currency=@Currency,ExchangeRate=@ExchangeRate,remark=@remark
                                where WMSOUTBSNO=@WMSOUTBSNO and invoice=0 and settlement=0 and orderinvoice=0 and orderamount=0";
                        DbCommand cmd = db.GetSqlStringCommand(UpdSql);
                        DbTransaction idbTran = connection.BeginTransaction();

                        //db.AddInParameter(cmd, "BSNO", DbType.String, Fee.BSNO);
                        //db.AddInParameter(cmd, "FeeName", DbType.String, Fee.FeeName);
                        //db.AddInParameter(cmd, "EnterDate", DbType.String, Fee.EnterDate);
                        //db.AddInParameter(cmd, "FeeType", DbType.String, Fee.FeeType);

                        db.AddInParameter(cmd, "FeeStatus", DbType.String, Fee.FeeStatus);
                        db.AddInParameter(cmd, "EnterOperator", DbType.String, Fee.EnterOperator);
                        db.AddInParameter(cmd, "CustomerName", DbType.String, Fee.CustomerName);
                        db.AddInParameter(cmd, "UnitPrice", DbType.String, Fee.UnitPrice);
                        db.AddInParameter(cmd, "Quantity", DbType.String, Fee.Quantity);
                        db.AddInParameter(cmd, "Amount", DbType.String, Fee.Amount);
                        db.AddInParameter(cmd, "Currency", DbType.String, Fee.Currency);
                        db.AddInParameter(cmd, "ExchangeRate", DbType.String, Fee.ExchangeRate);
                        db.AddInParameter(cmd, "remark", DbType.String, Fee.remark);
                        db.AddInParameter(cmd, "WMSOUTBSNO", DbType.String, Fee.WMSOUTBSNO);

                        db.ExecuteNonQuery(cmd, idbTran);
                        idbTran.Commit();

                        //idbTran.Commit();
                        #endregion
                    }


                    #region 获取新生成的托单费用


                    #endregion
                    isSucess = true;
                }
                catch (Exception exception)
                {
                    //idbTran.Rollback();
                    isSucess = false;
                    msg = exception.Message;
                }
            }
            return isSucess;
        }
        #endregion

        #region  明细部分
        #region  单据表 Receipt
        static public List<Receiptmb> GetReceiptList ( string Condition )
        {
            var strSql = new StringBuilder();
            strSql.Append("SELECT [id],[ContractNo],[Receiptid],t1.enumvaluename as receiptname,");
            strSql.Append("[ReceiptNo],[state],t2.enumvaluename as statename, ");
            strSql.Append("dbo.trimdate([Send_date]) [Send_date], ");
            strSql.Append("dbo.trimdate([receive_date]) [receive_date], ");
            strSql.Append("dbo.trimdate([repeat_date]) [repeat_date], ");
            strSql.Append("[trancer],[trancNo],[remark] FROM Import_receipt ");
            strSql.Append("left join tsysenumvalue t1 on t1.enumtypeid=2 and t1.enumvalueid=Receiptid ");
            strSql.Append("left join tsysenumvalue t2 on t2.enumtypeid=7 and t2.enumvalueid=state ");
            if (!string.IsNullOrEmpty(Condition))
            {
                strSql.Append(" where " + Condition);
            }
            return SetReceipt(strSql);
        }
        private static List<Receiptmb> SetReceipt ( StringBuilder strSql )
        {
            var headList = new List<Receiptmb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {                    /*if (reader["LrDate"] != DBNull.Value)
                        data.LrDate = Convert.ToDateTime(reader["LrDate"]);*/
                    Receiptmb data = new Receiptmb();
                    #region Set DB data to Object
                    data.id = Convert.ToString(reader["id"]);
                    data.ContractNo = Convert.ToString(reader["ContractNo"]);
                    data.Receiptid = Convert.ToString(reader["Receiptid"]);
                    data.ReceiptName = Convert.ToString(reader["ReceiptName"]);
                    data.ReceiptNo = Convert.ToString(reader["ReceiptNo"]);
                    data.state = Convert.ToString(reader["state"]);
                    data.statename = Convert.ToString(reader["statename"]);
                    data.Send_date = Convert.ToString(reader["Send_date"]);
                    data.receive_date = Convert.ToString(reader["receive_date"]);
                    data.repeat_date = Convert.ToString(reader["repeat_date"]);
                    data.trancer = Convert.ToString(reader["trancer"]);
                    data.trancNo = Convert.ToString(reader["trancNo"]);
                    data.remark = Convert.ToString(reader["remark"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region  货物表 cargo
        static public List<Cargomb> 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(" (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,c.tariff, ");
            //strSql.Append(" (select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=9000 and EnumValueID=cc.tariff) as tariff, ");

            strSql.Append(" tax,isnull(c.tax_cl,0) tax_cl, ");
            strSql.Append(" c.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,CargoType,SPECIFICATIONS,c.SHIJIGUANSHUI,c.SHIJIZENGZHISHUI,C.Remark ");

            strSql.Append(" ,(select TAGPICURL from Import_Cargoname where hs_id =c.cargoinfo_id and ciq_id = c.cargociq_id and ENNAME = c.Ename and CNNAME = c.name ");
            strSql.Append(" and FactoryNoGID in (select GID from Import_Factoryno where FACTORYNO = c.FactoryNo) and isnull(TAGPICURL,'')<> '' ) TAGPICURL  ");

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

        static public List<Cargomb> GetCargoList_Audit ( string Condition )
        {
            var strSql = new StringBuilder();
            strSql.Append(" SELECT c.[id],c.[GID],c.[ContractNo],c.[cargoinfo_id],c.[cargociq_id],c.[name],c.[Ename],c.[UNIT],c.[U_PRICE] ");
            strSql.Append(" ,c.[weight],c.[weight]/1000 as [U_weight],c.[m_weight],c.[t_weight],c.[price],c.[price_agio],c.[price_limit],c.[Amount],c.[tax_Amount] ");
            strSql.Append(" ,c.[pay_Amount],c.[BoxCount],c.[BoxWeight],c.[Exporter],c.[FactoryNo],c.[ICP],c.[Productiondate],c.[currid] ");
            strSql.Append(" ,c.[prepayments],c.[balance],c.[pay_prepayments],c.[pay_balance],c.[paypart],c.[baolv],c.[tariff],c.[tax],c.[tax_zz],c.[Declarenumber] ");
            strSql.Append(" ,c.[Exchangerate],c.[FinanceStatus],c.[Exchangerate_Customs],convert(numeric(18,2),c.tax_amount*c.Exchangerate_Customs) tax_amount_RMB,  ");
            strSql.Append(" c.PreProportion,c.RecProportion,c.AMOUNT_WRITEOFFS,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=11 and EnumValueID=c.unit) as UnitRef, ");
            strSql.Append(" 1000 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, ");

            //case cc.tariff when 0 then ci.tax_zhg when 1 then ci.tax_pt end tax,
            strSql.Append(" isnull(ci.tax_cl,0) tax_cl, ");
            strSql.Append(" 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,c.sequence ");
            strSql.Append(" ,SBYS,BZTCHNO,CargoType,SPECIFICATIONS,c.SHIJIGUANSHUI,c.SHIJIZENGZHISHUI,C.Remark,C.AGENCRATE ");

            strSql.Append(" ,(select TAGPICURL from Import_Cargoname where hs_id =c.cargoinfo_id and ciq_id = c.cargociq_id and ENNAME = c.Ename and CNNAME = c.name ");
            strSql.Append(" and FactoryNoGID in (select GID from Import_Factoryno where FACTORYNO = c.FactoryNo) and isnull(TAGPICURL,'')<> '' ) TAGPICURL  ");

            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<Cargomb> SetCargo ( StringBuilder strSql )
        {
            var headList = new List<Cargomb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    Cargomb data = new Cargomb();
                    #region Set DB data to Object
                    data.id = Convert.ToString(reader["id"]);
                    data.GID = Convert.ToString(reader["GID"]);
                    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.ICP = Convert.ToString(reader["ICP"]);
                    data.Productiondate = Convert.ToString(reader["Productiondate"]);
                    data.currid = Convert.ToString(reader["currid"]);
                    //     data.currRef = Convert.ToString(reader["currRef"]);
                    //字段过滤器（判断是否存在该列）
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'RecProportion'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                        data.RecProportion = Convert.ToString(reader["RecProportion"]);
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 't_weight'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                        data.t_weight = Convert.ToString(reader["t_weight"]);

                    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.tax_cl = Convert.ToString(reader["tax_cl"]);
                    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.CargoType = Convert.ToString(reader["CargoType"]);
                    data.SPECIFICATIONS = Convert.ToString(reader["SPECIFICATIONS"]);
                    //字段过滤器（判断是否存在该列）
                    reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'AGENCRATE'";
                    if (reader.GetSchemaTable().DefaultView.Count > 0)
                        data.AGENCRATE= Convert.ToString(reader["AGENCRATE"]);
                    //data.SHIJIGUANSHUI = Convert.ToString(reader["SHIJIGUANSHUI"]);
                    //data.SHIJIZENGZHISHUI = Convert.ToString(reader["SHIJIZENGZHISHUI"]);
                    //data.XIANJIAJINE = Convert.ToString(reader["XIANJIAJINE"]);
                    //data.BAOZHENGJIN = Convert.ToString(reader["BAOZHENGJIN"]);
                    //data.ZHIBAOJIN = Convert.ToString(reader["ZHIBAOJIN"]);
                    //data.ZONGJINE = Convert.ToString(reader["ZONGJINE"]);
                    data.REMARK = Convert.ToString(reader["REMARK"]);
                    data.TAGPICURL = Convert.ToString(reader["TAGPICURL"]);

                    data.AGENCRATE = BasicDataRefDAL.getValue(reader, "AGENCRATE","string").ToString();
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }

        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),0) WEIGHT_REMAIN  ");
            strSql.Append(" ,isnull(boxcount_act,0)-isnull((select sum(isnull(BOXCOUNT,0)) from import_saledetail where CARGO_GID=c.gid),0) BOXCOUNT_REMAIN  ");
            strSql.Append(" ,ISLOCKED,dbo.trimdate(c.ExpirationDate) ExpirationDate ");
            //strSql.Append(" ,c.AGENCRATE,c.MOREORLESS ");
            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.ICP = Convert.ToString(reader["ICP"]);
                    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.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"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region  许可证/货物关系表
        static public List<Appstatemb> GetAppstate ( string Condition )
        {
            var strSql = new StringBuilder();
            strSql.Append(" select at.id,at.ContractNo,ap.appno,at.app_id,ci.name,at.cargo_id, ");
            strSql.Append(" ap.[weight] appweight,au.remain appremain,au.canbeused canbeused,ap.validdate, ");
            strSql.Append(" ct.country,at.[weight],at.[cancellation], ");
            strSql.Append(" (select EnumValueName from tSysEnumValue ");
            strSql.Append(" where LangId=0 and EnumTypeID=13 and EnumValueID=at.cancellation) as cancellationRef, ");
            strSql.Append(" dbo.trimdate([cancellation_date]) cancellation_date ");
            //
            strSql.Append(" ,ca.name+'_'+convert(varchar,ca.SEQUENCE)+' 重量:'+convert(varchar(12),convert(float,ca.[weight]/1000)) cargoname ");
            strSql.Append(" ,ca.sequence ,at.Remark");

            strSql.Append(" from import_appstate at ");
            strSql.Append(" left join import_approval ap on ap.id=at.app_id ");
            strSql.Append(" left join code_country ct on ct.countryid=ap.countryid ");
            strSql.Append(" left join import_cargoinfo ci on ap.cargoinfo_id=ci.id ");
            strSql.Append(" left join vmsappused au on au.app_id=ap.id ");
            strSql.Append(" left join import_cargo ca on ca.id=at.cargo_id ");
            if (!string.IsNullOrEmpty(Condition))
            {
                strSql.Append(" where " + Condition);
            }
            strSql.Append(" order by ca.sequence ");
            return SetAppstate(strSql);
        }
        private static List<Appstatemb> SetAppstate ( StringBuilder strSql )
        {
            var headList = new List<Appstatemb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    Appstatemb data = new Appstatemb();
                    #region Set DB data to Object
                    data.id = Convert.ToString(reader["id"]);
                    data.ContractNo = Convert.ToString(reader["ContractNo"]);
                    data.appno = Convert.ToString(reader["appno"]);
                    data.app_id = Convert.ToString(reader["app_id"]);
                    data.name = Convert.ToString(reader["name"]);
                    data.cargo_id = Convert.ToString(reader["cargo_id"]);
                    data.cargoname = Convert.ToString(reader["cargoname"]);
                    data.appweight = Convert.ToString(reader["appweight"]);
                    data.appremain = Convert.ToString(reader["appremain"]);
                    data.canbeused = Convert.ToString(reader["canbeused"]);
                    data.validdate = Convert.ToString(reader["validdate"]);
                    data.country = Convert.ToString(reader["country"]);
                    data.weight = Convert.ToString(reader["weight"]);
                    data.cancellation = Convert.ToString(reader["cancellation"]);
                    data.cancellationRef = Convert.ToString(reader["cancellationRef"]);
                    data.cancellation_date = Convert.ToString(reader["cancellation_date"]);
                    data.SEQUENCE = Convert.ToString(reader["SEQUENCE"]);
                    data.REMARK = Convert.ToString(reader["REMARK"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region  许可证表 用于向关系表提供可选的许可证
        static public List<Appstatemb> GetApp ( string Condition )
        {
            var strSql = new StringBuilder();
            strSql.Append(" select ap.appno,ap.id app_id,ci.name,ap.[weight] appweight,ap.company, ");
            strSql.Append(" au.remain appremain,au.canbeused canbeused,ap.validdate ,ct.country,ci.name, ");
            strSql.Append(" ap.appno+'/'+isnull(ap.SPECIFICATIONS,'')+' '+ap.AppName+'/用证:'+c.name+'/'+(select EnumValueName from tSysEnumValue ");
            strSql.Append(" where LangId=0 and EnumTypeID=10 and EnumValueID=ap.sljg) ");
            strSql.Append(" +'/'+ct.country+'/'+ci.name+'/剩余:'+convert(varchar(12),au.canbeused)+'吨/'+ap.validdate ");
            strSql.Append(" +'/'+isnull(ap.remark,'') info");
            strSql.Append(" from import_approval ap ");
            strSql.Append(" left join import_cargoinfo ci on ap.cargoinfo_id=ci.id ");
            strSql.Append(" left join vmsappused au on au.app_id=ap.id  ");
            strSql.Append(" left join code_country ct on ct.countryid=ap.countryid  ");
            strSql.Append(" left join company c on c.GID=ap.company ");
            /*select ap.appno,ap.id app_id,ci.name,ap.[weight] appweight,c.name companyname,ap.company,
au.remain appremain,au.canbeused canbeused,ap.validdate ,ct.country,ap.remark
from import_approval ap 
left join import_cargoinfo ci on ap.cargoinfo_id=ci.id
left join vmsappused au on au.app_id=ap.id 
left join code_country ct on ct.countryid=ap.countryid 
left join company c on c.GID=ap.company*/
            if (!string.IsNullOrEmpty(Condition))
            {
                strSql.Append(" where " + Condition);
            }
            return SetApp(strSql);
        }
        private static List<Appstatemb> SetApp ( StringBuilder strSql )
        {
            var headList = new List<Appstatemb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    Appstatemb data = new Appstatemb();
                    #region Set DB data to Object
                    data.appno = Convert.ToString(reader["appno"]);
                    data.app_id = Convert.ToString(reader["app_id"]);
                    data.name = Convert.ToString(reader["name"]);
                    data.company = Convert.ToString(reader["company"]);
                    data.appweight = Convert.ToString(reader["appweight"]);
                    data.appremain = Convert.ToString(reader["appremain"]);
                    data.canbeused = Convert.ToString(reader["canbeused"]);
                    data.validdate = Convert.ToString(reader["validdate"]);
                    data.country = Convert.ToString(reader["country"]);
                    data.info = Convert.ToString(reader["info"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region  库存表 记录货物流转过程
        static public List<KCmb> GetKC ( string Condition )
        {
            var strSql = new StringBuilder();
            strSql.Append(" select k.id,k.contractno,k.cargo_id ");
            strSql.Append(" ,c.name+'_'+convert(varchar,c.sequence)+' 重量:'+convert(varchar(12),convert(float,c.[weight]/1000)) cargoname,K.czstate, ");
            strSql.Append(" k.innum,k.outnum,k.kfstate,k.czdate,k.[text], ");
            strSql.Append(" t1.enumvaluename as czref,k.kfstate as kfref ,K.days,K.FeeName,K.UnitPrice, ");
            strSql.Append(" K.Quantity,K.Amount,K.Sended");
            strSql.Append(" ,K.TRUCKNO,K.TRUCKTEL,K.CORPID,K.ENTEROPERATOR,K.PLEDGE");
            strSql.Append(" from [Import_KC] K ");
            strSql.Append(" left join import_cargo c on c.id=k.cargo_id ");
            strSql.Append(" left join tsysenumvalue t1 on t1.enumtypeid=8 and t1.enumvalueid=K.czstate ");
            //strSql.Append(" left join tsysenumvalue t2 on t2.enumtypeid=9 and t2.enumvalueid=K.kfstate ");
            if (!string.IsNullOrEmpty(Condition))
            {
                strSql.Append(" where " + Condition);
            }
            return SetKC(strSql);
        }
        private static List<KCmb> SetKC ( StringBuilder strSql )
        {
            var headList = new List<KCmb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    KCmb data = new KCmb();
                    #region Set DB data to Object
                    data.id = Convert.ToString(reader["id"]);
                    data.ContractNo = Convert.ToString(reader["ContractNo"]);
                    data.cargo_id = Convert.ToString(reader["cargo_id"]);
                    data.cargoname = Convert.ToString(reader["cargoname"]);
                    data.czstate = Convert.ToString(reader["czstate"]);
                    data.innum = Convert.ToString(reader["innum"]);
                    data.outnum = Convert.ToString(reader["outnum"]);
                    data.kfstate = Convert.ToString(reader["kfstate"]);
                    data.kfref = Convert.ToString(reader["kfref"]);
                    data.czref = Convert.ToString(reader["czref"]);
                    data.czdate = Convert.ToString(reader["czdate"]);
                    data.text = Convert.ToString(reader["text"]);

                    data.days = Convert.ToString(reader["days"]);
                    data.FeeName = Convert.ToString(reader["FeeName"]);
                    data.UnitPrice = Convert.ToString(reader["UnitPrice"]);
                    data.Quantity = Convert.ToString(reader["Quantity"]);
                    data.Amount = Convert.ToString(reader["Amount"]);
                    data.Sended = Convert.ToString(reader["Sended"]);
                    data.TRUCKNO = Convert.ToString(reader["TRUCKNO"]);
                    data.TRUCKTEL = Convert.ToString(reader["TRUCKTEL"]);
                    data.CORPID = Convert.ToString(reader["CORPID"]);
                    data.ENTEROPERATOR = Convert.ToString(reader["ENTEROPERATOR"]);
                    data.PLEDGE = Convert.ToString(reader["PLEDGE"]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region  将某合同的合同号清空 (在将合同标记为删除时调用)
        static public int ClearHTH ( string ContractNo )
        {
            var strSql = new StringBuilder();
            strSql.Append(" update import_main set HTH='',remark=remark+' 原合同号:'+HTH where contractno = '" + ContractNo + "'");
            return setKCStart(strSql);//ExecuteNonQuery
        }

        #endregion

        #region  初始化库存参数
        static public int KCStart ( string cargo_id, string corpcode, string userid )
        {
            var strSql = new StringBuilder();
            strSql.Append(" delete from [Import_KC] where cargo_id in( " + cargo_id + ")");
            strSql.Append(" insert into [Import_KC] ");
            strSql.Append(" (ContractNo,cargo_id,CZState,InNum,OutNum,KFstate,CZdate,CORPID,deptgid) ");
            strSql.Append(" (select [ContractNo],convert(varchar(50),id) [cargo_id],1 [CZState] ");
            strSql.Append(" ,boxcount as [InNum],0 [OutNum],0 kfstate,(getdate()) CZdate ");
            strSql.Append(" ,(select gid from company where codename='" + corpcode + "'),(select deptgid from vw_user where userid='" + userid + "') ");
            strSql.Append(" from import_cargo where id in( " + cargo_id + ") )");
            return setKCStart(strSql);//ExecuteNonQuery
        }
        private static int setKCStart ( StringBuilder strSql )
        {
            var _count = 0;
            Database db = DatabaseFactory.CreateDatabase();
            _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString());

            return _count;
        }
        #endregion

        #region 获取入库时间
        static public int GetKCEnterDate ( string cargo_id )
        {
            var strSql = new StringBuilder();
            strSql.Append(" delete from [Import_KC] where cargo_id in( " + cargo_id + ")");
            strSql.Append(" insert into [Import_KC] ");
            strSql.Append(" select ");
            strSql.Append(" [ContractNo],id [cargo_id],0 [CZState] ,");
            strSql.Append(" boxcount as [InNum],0 [OutNum],0 kcstate,(getdate()) kcdate,'' [text] ");
            strSql.Append(" from import_cargo where id in( " + cargo_id + ")");
            return setKCEnterDate(strSql);//ExecuteNonQuery
        }
        private static int setKCEnterDate ( StringBuilder strSql )
        {
            var _count = 0;
            Database db = DatabaseFactory.CreateDatabase();
            _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString());

            return _count;
        }
        #endregion

        #region  初始化财务数据
        static public int CWStart ( string ContractNo )
        {
            var strSql = new StringBuilder();
            strSql.Append(" exec smsCWStart '" + ContractNo + "'");
            return setCWStart(strSql);//ExecuteNonQuery
        }
        private static int setCWStart ( StringBuilder strSql )
        {
            var _count = 0;
            Database db = DatabaseFactory.CreateDatabase();
            _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString());

            return _count;
        }
        #endregion

        #region  设定合同状态
        static public int SetStatus ( string ContractNo, string NewMainstate )
        {
            var strSql = new StringBuilder();
            if (NewMainstate == "7")
            {
                strSql.Append(" update import_main set Mainstate= " + NewMainstate + ",accdate='" + DateTime.Now.ToString() + "' where ContractNo='" + ContractNo + "'");
            }
            else
            {
                strSql.Append(" update import_main set Mainstate= " + NewMainstate + " where ContractNo='" + ContractNo + "'");
            }
            strSql.Append(" select * from import_main where Mainstate= " + NewMainstate + " and ContractNo='" + ContractNo + "'");
            return setKCStart(strSql);//ExecuteNonQuery
        }
        #endregion

        #region  获取字段值，用于过滤条件
        static public List<Fieldmb> GetField ( string tablename, string fieldid, string fieldvalue )
        {
            var strSql = new StringBuilder();

            strSql.Append("select distinct " + fieldvalue + ", " + fieldid + " from " + tablename);

            return SetField(strSql, fieldid, fieldvalue);
        }
        private static List<Fieldmb> SetField ( StringBuilder strSql, string fieldid, string fieldvalue )
        {
            var headList = new List<Fieldmb>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    Fieldmb data = new Fieldmb();
                    #region Set DB data to Object
                    data.FieldId = Convert.ToString(reader[fieldid]);
                    data.FieldValue = Convert.ToString(reader[fieldvalue]);
                    #endregion
                    headList.Add(data);
                }
                reader.Close();
            }
            return headList;
        }
        #endregion

        #region  生成结算数据ch_fee_do 已放弃 改用原有结算逻辑
        /*
        static public int Settlement(int newStatus,string GId)
        {
            var strSql = new StringBuilder();
            strSql.Append(" declare @feeid varchar(50)");
            strSql.Append(" declare @newStatus int");
            strSql.Append(" set @feeid ='" + GId + "'");
            strSql.Append(" set @newStatus =" + newStatus);
            strSql.Append(" exec smsSettlement @feeid,@newStatus");
            return setSettlement(strSql);//ExecuteNonQuery
        }
        private static int setSettlement(StringBuilder strSql)
        {
            var _count = 0;
            Database db = DatabaseFactory.CreateDatabase();
            _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString());

            return _count;
        } */
        #endregion

        #region  删除用证信息，在将合同标记为删除时使用
        static public int DELUseApp ( string ContractNo )
        {
            var strSql = new StringBuilder();
            strSql.Append(" delete from [import_appstate] where contractno =( '" + ContractNo + "')");

            var _count = 0;
            Database db = DatabaseFactory.CreateDatabase();
            _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString());

            return _count;
        }

        #endregion

        #endregion

        #region 参照部分

        internal static List<XXHcountryDAL> GetCountry ( )
        {
            var strSql = new StringBuilder();
            strSql.Append("SELECT ");
            strSql.Append("countryid,country,countryid+' '+country");
            strSql.Append("  from code_country");

            var dataList = new List<XXHcountryDAL>();
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {
                while (reader.Read())
                {
                    var data = new XXHcountryDAL();

                    #region Set DB data to Object

                    data.countryid = Convert.ToString(reader["countryid"]);
                    data.country = Convert.ToString(reader["country"]);
                    data.country = Convert.ToString(reader["country"]);

                    #endregion

                    dataList.Add(data);
                }
                reader.Close();
            }

            return dataList;
        }

        #endregion


        #region 上传信息至import_main_public表

        static public int UploadInfo ( string idList )
        {
            var strSql = new StringBuilder();
            //////////////////上传业务信息
            strSql.Append(" delete from import_main_public where id in (" + idList + ")  ");

            strSql.Append(" insert into Import_main_public (  ");
            strSql.Append(" id,ContractNo,HTH,company,seller,buyer,STT,countryid,creattime,Audittime,MainState,printed,AutoAPP,ShipCompany_id,Billno,BillType,BillTime,Vessel ");
            strSql.Append(" ,Voyage,Ex_sailingdate,Sailingdate,ArrivalDate,ContainerNo,SealNo,Freetime,TransactionMethod,PaymentMethods,SecurityDeposit,CustomsReleaseDate,SamplingDate_Plan,SamplingDate_Act ");
            strSql.Append(" ,inspection_Signup_date,inspection_no,inspection_date,CIQ_licence,CIQ_canbesearch,tax_date,tax_paydate,remark,port,Writeoffs,Paydate,E_Billtime,HDtime,FinanceStatus ");
            strSql.Append(" ,remark_2,remark_3,remark_4,Sampling_need,DeclareNumber,Inspection_Storage,DeliveryDate,DeliveryAddress,Contacter,Tel,Overfilled,OwnApp,ReceiptStatus,Receiptremark,Prepaydate ");
            strSql.Append(" ,TRADINGAGENCY,ACCDATE,ContractStatus,KCClear,RCVMode,ISVOU,VOUCHERNO,OP,MZBZ,RecDate,PortDays,OPBillTime,Dock,Customs_broker,inspection_broker,CreateTime_2,ResendTime,BuzType ");
            strSql.Append(" ,Inspection_Buyer,Inspection_Contacter,Inspection_Tel,Inspection_DeliveryAddress,HaveContract,HaveCargoMark,CORPID,DEPTID,Supplier_Billno,Other_Billno,Agent,Buy_Type,Buy_OP ");
            strSql.Append(" ,Bill_ExpressNo,HAVESAMPLE,ISBONDED,CHECKSAMPLE,CTN,BILLTRANCNO,LOCK_EXCHANGERATE,EXCHANGERATE,Unsealdate,ISREFORM )  ");
            strSql.Append(" select id,ContractNo,HTH,company,seller,buyer,STT,countryid,creattime,Audittime,MainState,printed,AutoAPP,AutoAPPApplyId,AutoAPPNum,ApplyDate,ShipCompany_id,Billno,BillType,BillTime,Vessel ");
            strSql.Append(" ,Voyage,Ex_sailingdate,Sailingdate,ArrivalDate,ContainerNo,SealNo,Freetime,TransactionMethod,PaymentMethods,SecurityDeposit,CustomsReleaseDate,SamplingDate_Plan,SamplingDate_Act ");
            strSql.Append(" ,inspection_Signup_date,inspection_no,inspection_date,CIQ_licence,CIQ_canbesearch,tax_date,tax_paydate,remark,port,Writeoffs,Paydate,E_Billtime,HDtime,FinanceStatus ");
            strSql.Append(" ,remark_2,remark_3,remark_4,Sampling_need,DeclareNumber,Inspection_Storage,DeliveryDate,DeliveryAddress,Contacter,Tel,Overfilled,OwnApp,ReceiptStatus,Receiptremark,Prepaydate ");
            strSql.Append(" ,TRADINGAGENCY,ACCDATE,ContractStatus,KCClear,RCVMode,ISVOU,VOUCHERNO,OP,MZBZ,RecDate,PortDays,OPBillTime,Dock,Customs_broker,inspection_broker,CreateTime_2,ResendTime,BuzType ");
            strSql.Append(" ,Inspection_Buyer,Inspection_Contacter,Inspection_Tel,Inspection_DeliveryAddress,HaveContract,HaveCargoMark,CORPID,DEPTID,Supplier_Billno,Other_Billno,Agent,Buy_Type,Buy_OP ");
            strSql.Append(" ,Bill_ExpressNo,HAVESAMPLE,ISBONDED,CHECKSAMPLE,CTN,BILLTRANCNO,LOCK_EXCHANGERATE,EXCHANGERATE,Unsealdate,ISREFORM  ");
            strSql.Append(" from Import_main m where id in (" + idList + ")  ");

            //////////////////上传图片文档信息
            //上传前删除所有该业务的图片信息
            strSql.Append(" delete from Receipt_Doc_Public where BSNO in (select contractno from import_main where id in (" + idList + ") ) ");

            strSql.Append(" insert into Receipt_Doc_Public (  ");
            strSql.Append(" GID,RECEIPTTYPE,BSNO,LOCAL_BSNO,URL,Driect_URL )");
            strSql.Append(" select GID,RECEIPTTYPE,BSNO,'',URL,Driect_URL ");
            strSql.Append(" from Receipt_Doc where BSNO in (select contractno from import_main where id in (" + idList + ") ) ");

            strSql.Append(" delete from Import_cargo_public where ContractNo in (select contractno from import_main where id in (" + idList + ") ) ");

            strSql.Append(" INSERT INTO Import_cargo_Public ");
            strSql.Append(" ([ContractNo],[cargoinfo_id],[cargociq_id],[name],[Ename],[UNIT],[U_PRICE],[U_weight],[weight],[m_weight] ");
            strSql.Append(" ,[price],[price_limit],[Amount],[tax_Amount],[BoxCount],[BoxWeight],[Exporter],[FactoryNo],[Productiondate] ");
            strSql.Append(" ,[currid],[prepayments],[balance],[paypart],[baolv],[tariff],[tax],[tax_zz],[Declarenumber],[Exchangerate],[FinanceStatus] ");
            strSql.Append(" ,[Exchangerate_Customs],[PreProportion],[Amount_Writeoffs],[sequence] ");
            strSql.Append(" ,[SBYS],[BZTCHNO],[GID],[CargoType],[KINDPKGS],[Weight_Act],[BoxCount_Act],[isLocked],[productiondate_max] ");
            strSql.Append(" ,[productiondate_min],[ExpirationDate],[AGENCRATE],[MOREORLESS],[Remark] ");
            strSql.Append(" ,[SPECIFICATIONS],[price_agio],[pay_amount],tax_1,tax_zz_1) ");
            strSql.Append(" SELECT  [ContractNo],[cargoinfo_id],[cargociq_id],[name],[Ename],[UNIT],[U_PRICE],[U_weight],[weight],[m_weight] ");
            strSql.Append(" ,[price],[price_limit],[Amount],[tax_Amount],[BoxCount],[BoxWeight],[Exporter],[FactoryNo],[Productiondate] ");
            strSql.Append(" ,(select codename from code_currency cc where cc.gid=currid ) currid,[prepayments],[balance],[paypart],[baolv],[tariff],[tax],[tax_zz],[Declarenumber],[Exchangerate],[FinanceStatus] ");
            strSql.Append(" ,[Exchangerate_Customs],[PreProportion],[Amount_Writeoffs],[sequence] ");
            strSql.Append(" ,[SBYS],[BZTCHNO],[GID],[CargoType],[KINDPKGS],[Weight_Act],[BoxCount_Act],[isLocked],[productiondate_max] ");
            strSql.Append(" ,[productiondate_min],[ExpirationDate],[AGENCRATE],[MOREORLESS],[Remark] ");
            strSql.Append(" ,[SPECIFICATIONS],0,[Amount],tax_1,tax_zz_1 ");
            strSql.Append(" FROM [Import_cargo] where contractno in (select contractno from import_main where id in (" + idList + ") ) ");

            var _count = 0;
            Database db = DatabaseFactory.CreateDatabase();
            _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString());

            return _count;
        }

        static public int UploadFee ( string idList )
        {
            var strSql = new StringBuilder();

            //////////////////上传费用信息
            //2015-12-16 改为上传前删除所有该业务的费用。

            strSql.Append(" delete from CH_FEE_Public where bsno in ");
            strSql.Append(" ( select contractno from import_main m where id in(" + idList + ") ) ");

            strSql.Append(" insert into CH_FEE_Public ");
            strSql.Append(" (GID,Local_GID,BSNO,FEETYPE,Local_BSNO,CUSTOMERNAME,FEENAME,UNITPRICE ");
            strSql.Append(" ,QUANTITY,AMOUNT,CURRENCY,EXCHANGERATE,SETTLEMENT,settledate,REMARK )");
            strSql.Append(" select GID,'',bsno,FEETYPE,'',CUSTOMERNAME,FEENAME,UNITPRICE ");
            strSql.Append(" ,QUANTITY,AMOUNT,CURRENCY,EXCHANGERATE,SETTLEMENT ");
            strSql.Append(" ,(select CREATETIME from ch_fee_do where CATEGORY in(1,2,3,8,9) and feeid =ch_fee.gid) settledate,REMARK");
            strSql.Append(" from ch_fee   ");
            strSql.Append(" where bsno in ");
            strSql.Append(" (select contractno from import_main m where  id in(" + idList + ")) ");
            strSql.Append(" and customername=(select buyer from import_main where contractno=ch_fee.bsno)");

            var _count = 0;
            Database db = DatabaseFactory.CreateDatabase();
            _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString());

            return _count;
        }

        #endregion

        #region 分别获取Doc,fee,import表数据

        static public DBResult GetImport ( string ThisCompanyName, string condition )
        {

            Hashtable ht = new Hashtable();
            ht.Add("COMPANYCODE", ThisCompanyName);
            //ht.Add("FARCODE", "0276");
            //改为取本公司的中文简称

            var result = new DBResult();
            result.Success = true;
            var bodyList = new List<ImportUploadmb>();
            try
            {
                //XmlDocument xx = BasicDataRefDAL.WebSvcCaller.QuerySoapWebService("http://localhost:54972/webservice/DsWebService.asmx", "ReceiveImportInfo", ht);
                //object resultXml = xx;

                //string url = "http://localhost:54972/webservice/DsWebService.asmx";
                string url = "http://120.27.53.37:54972/webservice/DsWebService.asmx";

                string[] args = new string[2];
                args[0] = ThisCompanyName;
                args[1] = condition;
                object resultXml = WebServiceHelper.InvokeWebService(url, "ReceiveImportInfo_direct", args);
                if (resultXml == "")
                {
                    result.Success = false;
                    result.Message = "";
                }
                StringReader rdr = new StringReader(resultXml.ToString());
                XmlSerializer serializer = new XmlSerializer(typeof(ImportInfo));
                ImportInfo xmlDoc = (ImportInfo)serializer.Deserialize(rdr);

                //var MessageID = "";
                //var rd = new object();
                XmlImport[] ImportList = xmlDoc.ImportList.Import;

                var iList = new List<ImportUploadmb2>();
                foreach (XmlImport Import in ImportList)
                {
                    var importUpload = new ImportUploadmb2();
                    importUpload.ContractNo = Import.ContractNo;
                    importUpload.HTH = Import.HTH;
                    importUpload.BillNo = Import.Billno;
                    importUpload.ContainerNo = Import.ContainerNo;
                    importUpload.SealNo = Import.SealNo;
                    importUpload.ShipCompany_id = Import.ShipCompany_id;
                    importUpload.company = Import.company;
                    importUpload.Vessel = Import.Vessel;
                    importUpload.Voyage = Import.Voyage;
                    importUpload.Ex_sailingdate = Import.Ex_sailingdate;
                    importUpload.Sailingdate = Import.Sailingdate;
                    importUpload.ArrivalDate = Import.ArrivalDate;
                    importUpload.RecDate = Import.RecDate;
                    importUpload.tax_date = Import.tax_date;
                    importUpload.tax_paydate = Import.tax_paydate;
                    importUpload.Sampling_need = Import.Sampling_need;
                    importUpload.SamplingDate_Act = Import.SamplingDate_Act;
                    importUpload.CustomsReleaseDate = Import.CustomsReleaseDate;
                    importUpload.PortDays = Import.PortDays;
                    importUpload.inspection_date = Import.inspection_date;
                    importUpload.received = "0";

                    importUpload.seller = Import.seller;
                    importUpload.buyer = Import.buyer;
                    importUpload.STT = Import.STT;
                    importUpload.countryid = Import.countryid;
                    importUpload.creattime = Import.creattime;
                    importUpload.Audittime = Import.Audittime;
                    importUpload.Mainstate = Import.MainState;
                    importUpload.Printed = Import.printed;
                    importUpload.AutoAPP = Import.AutoAPP;
                    importUpload.BillTime = Import.BillTime;
                    importUpload.Freetime = Import.Freetime;
                    importUpload.TransactionMethod = Import.TransactionMethod;
                    importUpload.PaymentMethods = Import.PaymentMethods;
                    importUpload.SecurityDeposit = Import.SecurityDeposit;
                    importUpload.SamplingDate_Plan = Import.SamplingDate_Plan;
                    importUpload.inspection_Signup_date = Import.inspection_Signup_date;
                    importUpload.inspection_no = Import.inspection_no;
                    importUpload.CIQ_licence = Import.CIQ_licence;
                    importUpload.CIQ_canbesearch = Import.CIQ_canbesearch;
                    importUpload.remark = Import.remark;
                    importUpload.port = Import.port;
                    importUpload.Paydate = Import.Paydate;
                    importUpload.E_Billtime = Import.E_Billtime;
                    importUpload.HDtime = Import.HDtime;
                    importUpload.remark_2 = Import.remark_2;
                    importUpload.remark_3 = Import.remark_3;
                    importUpload.remark_4 = Import.remark_4;
                    importUpload.Sampling_need = Import.Sampling_need;
                    importUpload.DeclareNumber = Import.DeclareNumber;
                    importUpload.Inspection_Storage = Import.Inspection_Storage;
                    importUpload.DeliveryDate = Import.DeliveryDate;
                    importUpload.DeliveryAddress = Import.DeliveryAddress;
                    importUpload.Contacter = Import.Contacter;
                    importUpload.Tel = Import.Tel;
                    importUpload.Overfilled = Import.Overfilled;
                    importUpload.ReceiptStatus = Import.ReceiptStatus;
                    importUpload.Receiptremark = Import.Receiptremark;
                    importUpload.Prepaydate = Import.Prepaydate;
                    importUpload.TRADINGAGENCY = Import.TRADINGAGENCY;
                    importUpload.KCClear = Import.KCClear;
                    importUpload.RCVMode = Import.RCVMode;
                    importUpload.MZBZ = Import.MZBZ;
                    importUpload.Inspection_Buyer = Import.Inspection_Buyer;
                    importUpload.Inspection_Contacter = Import.Inspection_Contacter;
                    importUpload.Inspection_Tel = Import.Inspection_Tel;
                    importUpload.Inspection_DeliveryAddress = Import.Inspection_DeliveryAddress;
                    importUpload.HaveContract = Import.HaveContract;
                    importUpload.HaveCargoMark = Import.HaveCargoMark;
                    importUpload.HAVESAMPLE = Import.HAVESAMPLE;
                    importUpload.CHECKSAMPLE = Import.CHECKSAMPLE;
                    importUpload.CTN = Import.CTN;
                    importUpload.BILLTRANCNO = Import.BILLTRANCNO;
                    importUpload.LOCK_EXCHANGERATE = Import.LOCK_EXCHANGERATE;
                    importUpload.EXCHANGERATE = Import.EXCHANGERATE;
                    importUpload.Unsealdate = Import.Unsealdate;
                    importUpload.ISREFORM = Import.ISREFORM;

                    importUpload.BillType = Import.BillType;
                    importUpload.Receiptremark = Import.Receiptremark;
                    importUpload.CreateTime_2 = Import.CreateTime_2;
                    importUpload.ResendTime = Import.ResendTime;
                    importUpload.Dock = Import.Dock;
                    importUpload.Customs_broker = Import.Customs_broker;
                    importUpload.inspection_broker = Import.inspection_broker;

                    if (Exist(Import.ContractNo))
                    {
                        importUpload.received = "1";
                        importUpload.DbOperationType = DbOperationType.DbotUpd;
                    }
                    else
                    {
                        importUpload.DbOperationType = DbOperationType.DbotIns;
                    }
                    iList.Add(importUpload);
                }


                var modb = new ModelObjectRepository();
                result = modb.SaveHead(
                       ModelObjectConvert<ImportUploadmb2>.ToModelObjectList(iList)
                      );

                return result;

            }
            catch (Exception e)
            {
                result.Success = false;
                result.Message = e.Message;
            }

            return result;

        }

        static public DBResult GetFee ( string ThisCompanyName, string condition )
        {

            Hashtable ht = new Hashtable();
            ht.Add("COMPANYCODE", ThisCompanyName);
            

            var result = new DBResult();
            result.Success = true;
            var bodyList = new List<SyncFee>();
            try
            {
                //XmlDocument xx = BasicDataRefDAL.WebSvcCaller.QuerySoapWebService("http://localhost:54972/webservice/DsWebService.asmx", "ReceiveImportInfo", ht);
                //object resultXml = xx;

                //string url = "http://localhost:54972/webservice/DsWebService.asmx";
                string url = "http://120.27.53.37:54972/webservice/DsWebService.asmx";

                string[] args = new string[2];
                args[0] = ThisCompanyName;
                args[1] = condition;
                object resultXml = WebServiceHelper.InvokeWebService(url, "ReceiveFee_direct", args);
                if (resultXml == "")
                {
                    result.Success = false;
                    result.Message = "";
                }
                StringReader rdr = new StringReader(resultXml.ToString());
                XmlSerializer serializer = new XmlSerializer(typeof(SyncFee));
                SyncFee xmlDoc = (SyncFee)serializer.Deserialize(rdr);

                //var MessageID = "";
                //var rd = new object();
                XmlFee[] FeeList = xmlDoc.SyncFeeList.Fee;
                var _iList = new List<FeeSyncmb>();
                var iList = new List<FeeSyncmb>();
                foreach (XmlFee Fee in FeeList)
                {
                    var data = new FeeSyncmb();

                    data.GID = Fee.GID;
                    data.Local_GID = Fee.Local_GID;
                    data.BSNO = Fee.BSNO;
                    data.FEETYPE = Fee.FEETYPE;
                    data.Local_BSNO = Fee.Local_BSNO;
                    data.CUSTOMERNAME = Fee.CUSTOMERNAME;
                    data.FEENAME = Fee.FEENAME;
                    data.UNITPRICE = Fee.UNITPRICE;
                    data.QUANTITY = Fee.QUANTITY;
                    data.AMOUNT = Fee.AMOUNT;
                    data.CURRENCY = Fee.CURRENCY;
                    data.EXCHANGERATE = Fee.EXCHANGERATE;
                    data.SETTLEDATE = Fee.SETTLEDATE;
                    data.SETTLEMENT = Fee.SETTLEMENT;
                    data.REMARK = Fee.REMARK;
                    data.DbOperationType = DbOperationType.DbotDel;

                    //DelSyncDetail(Fee.GID, "ch_fee_Public");
                    iList.Add(data);
                }


                var modb = new ModelObjectRepository();

                foreach (var _d in iList)
                {
                    _d.DbOperationType = DbOperationType.DbotDel;
                }
                result = modb.SaveHead(
                       ModelObjectConvert<FeeSyncmb>.ToModelObjectList(iList)
                       );

                foreach (var _d in iList)
                {
                    _d.DbOperationType = DbOperationType.DbotIns;
                }
                result = modb.SaveHead(
                       ModelObjectConvert<FeeSyncmb>.ToModelObjectList(iList)
                      );

                return result;

            }
            catch (Exception e)
            {
                result.Success = false;
                result.Message = e.Message;
            }
            return result;
        }

        static public DBResult GetDoc ( string ThisCompanyName ,string condition)
        {

            Hashtable ht = new Hashtable();
            ht.Add("COMPANYCODE", ThisCompanyName );

            var result = new DBResult();
            result.Success = true;
            var bodyList = new List<DocSyncmb>();
            try
            {

                //string url = "http://localhost:54972/webservice/DsWebService.asmx";
                string url = "http://120.27.53.37:54972/webservice/DsWebService.asmx";

                string[] args = new string[2];
                args[0] = ThisCompanyName;
                args[1] = condition;
                object resultXml = WebServiceHelper.InvokeWebService(url, "ReceiveDoc_direct", args);
                if (resultXml == "") {
                    result.Success = false;
                    result.Message = "";
                }
                StringReader rdr = new StringReader(resultXml.ToString());
                XmlSerializer serializer = new XmlSerializer(typeof(SyncReceiptDoc));
                SyncReceiptDoc xmlDoc = (SyncReceiptDoc)serializer.Deserialize(rdr);

                //var MessageID = "";
                //var rd = new object();
                XmlDoc[] DocList = xmlDoc.DocList.Doc;

                var iList = new List<DocSyncmb>();
                foreach (XmlDoc Doc in DocList)
                {
                    var data = new DocSyncmb();


                    data.GID = Doc.GID;
                    data.RECEIPTTYPE = Doc.RECEIPTTYPE;
                    data.BSNO = Doc.BSNO;
                    //data.LOCAL_BSNO = Doc.LOCAL_BSNO;
                    data.URL = Doc.URL;
                    data.Driect_URL = Doc.Driect_URL;

                    data.DbOperationType = DbOperationType.DbotDel;

                    iList.Add(data);
                    //DelSyncDetail(Doc.GID, "Receipt_Doc_Public");
                }

                var modb = new ModelObjectRepository();
                result = modb.SaveHead(
                       ModelObjectConvert<DocSyncmb>.ToModelObjectList(iList)
                      );

                foreach (var _d in iList)
                {
                    _d.DbOperationType = DbOperationType.DbotIns;
                }

                result = modb.SaveHead(
                       ModelObjectConvert<DocSyncmb>.ToModelObjectList(iList)
                      );

                return result;

            }
            catch (Exception e)
            {
                result.Success = false;
                result.Message = e.Message;
            }

            return result;

        }

        static public DBResult GetCargo ( string ThisCompanyName, string condition )
        {

            Hashtable ht = new Hashtable();
            ht.Add("COMPANYCODE", ThisCompanyName);

            var result = new DBResult();
            result.Success = true;
            var bodyList = new List<DocSyncmb>();
            try
            {

                //string url = "http://localhost:54972/webservice/DsWebService.asmx";
                string url = "http://120.27.53.37:54972/webservice/DsWebService.asmx";

                string[] args = new string[2];
                args[0] = ThisCompanyName;
                args[1] = condition;
                object resultXml = WebServiceHelper.InvokeWebService(url, "ReceiveCargo_direct", args);
                if (resultXml == "")
                {
                    result.Success = false;
                    result.Message = "";
                }
                StringReader rdr = new StringReader(resultXml.ToString());
                XmlSerializer serializer = new XmlSerializer(typeof(SyncCargo));
                SyncCargo xmlCargo = (SyncCargo)serializer.Deserialize(rdr);

                //var MessageID = "";
                //var rd = new object();
                XmlCargo[] CargoList = xmlCargo.SyncCargoList.Cargo;

                var iList = new List<CargoSyncmb>();
                foreach (XmlCargo Cargo in CargoList)
                {
                    var data = new CargoSyncmb();

                    data.GID = Cargo.GID;
                    data.ContractNo = Cargo.ContractNo;
                    data.cargoinfo_id = Cargo.cargoinfo_id;
                    data.cargociq_id = Cargo.cargociq_id;
                    data.Name = Cargo.Name;
                    data.Ename = Cargo.Ename;
                    data.UNIT = Cargo.UNIT;
                    data.U_PRICE = Cargo.U_PRICE;
                    data.U_weight = Cargo.U_weight;
                    data.weight = Cargo.weight;
                    data.m_weight = Cargo.m_weight;
                    data.price = Cargo.price;
                    data.price_limit = Cargo.price_limit;
                    data.Amount = Cargo.Amount;
                    data.pay_Amount = Cargo.pay_Amount;
                    data.tax_Amount = Cargo.tax_Amount;
                    if (Cargo.BoxCount == "") 
                        data.BoxCount = "0";
                    else
                        data.BoxCount = Cargo.BoxCount;

                    if (Cargo.BoxWeight == "")
                        data.BoxWeight = "0";
                    else
                        data.BoxWeight = Cargo.BoxWeight;

                    data.Exporter = Cargo.Exporter;
                    data.FactoryNo = Cargo.FactoryNo;
                    data.Productiondate = Cargo.Productiondate;
                    data.currid = Cargo.currid;
                    data.prepayments = Cargo.prepayments;
                    data.balance = Cargo.balance;
                    data.paypart = Cargo.paypart;
                    data.baolv = Cargo.baolv;
                    data.tariff = Cargo.tariff;
                    data.tax = Cargo.tax;
                    data.tax_zz = Cargo.tax_zz;
                    data.tax_1 = Cargo.tax_1;
                    data.tax_zz_1 = Cargo.tax_zz_1;
                    data.tax_2 = Cargo.tax_1;
                    data.tax_zz_2 = Cargo.tax_zz_1;
                    data.Declarenumber = Cargo.Declarenumber;
                    data.Exchangerate = Cargo.Exchangerate;
                    data.Exchangerate_Customs = Cargo.Exchangerate_Customs;
                    data.PreProportion = Cargo.PreProportion;
                    data.Amount_Writeoffs = Cargo.Amount_Writeoffs;
                    data.sequence = Cargo.sequence;
                    data.SBYS = Cargo.SBYS;
                    data.BZTCHNO = Cargo.BZTCHNO;
                    data.CargoType = Cargo.CargoType;
                    data.KINDPKGS = Cargo.KINDPKGS;

                    if (Cargo.Weight_Act == "")
                        data.Weight_Act = "0";
                    else
                        data.Weight_Act = Cargo.Weight_Act;

                    if (Cargo.BoxCount_Act == "")
                        data.BoxCount_Act = "0";
                    else
                        data.BoxCount_Act = Cargo.BoxCount_Act;

                    data.ExpirationDate = Cargo.ExpirationDate;

                    if (Cargo.AGENCRATE == "")
                        data.AGENCRATE = "0";
                    else
                        data.AGENCRATE = Cargo.AGENCRATE;

                    if (Cargo.MOREORLESS == "")
                        data.MOREORLESS = "0";
                    else
                        data.MOREORLESS = Cargo.MOREORLESS;

                    data.Remark = Cargo.Remark;
                    data.SPECIFICATIONS = Cargo.SPECIFICATIONS;

                    iList = new List<CargoSyncmb>();
                    iList.Add(data);

                    var modb = new ModelObjectRepository();

                    foreach (var _d in iList)
                    {
                        if (Exist_Cargo(_d.GID) == true)
                        {
                            _d.DbOperationType = DbOperationType.DbotUpd;
                        }
                        else
                        {
                            _d.DbOperationType = DbOperationType.DbotIns;
                        }
                    }

                    result = modb.SaveHead(
                           ModelObjectConvert<CargoSyncmb>.ToModelObjectList(iList)
                          );

                    if (result.Success == true)
                    {

                    }
                    else {
                        //return result;
                    }
                }
                /*
                var modb = new ModelObjectRepository();

                foreach (var _d in iList)
                {
                    if (Exist_Cargo(_d.GID) == true)
                    {
                        _d.DbOperationType = DbOperationType.DbotUpd;
                    }
                    else
                    {
                        _d.DbOperationType = DbOperationType.DbotIns;
                    }
                }

                result = modb.SaveHead(
                       ModelObjectConvert<CargoSyncmb>.ToModelObjectList(iList)
                      );

                return result;
                */
            }
            catch (Exception e)
            {
                result.Success = false;
                result.Message = e.Message;
            }

            return result;

        }

        private static Boolean Exist ( string ContractNo )
        {
            var strCheck = new StringBuilder();

            strCheck.Append(" select ContractNo");//0未提交
            strCheck.Append(" from import_main_public where ContractNo = '" + ContractNo + "'");
            DataSet ds = new DataSet();
            Database db_check = DatabaseFactory.CreateDatabase();
            var _C = "";

            using (IDataReader reader = db_check.ExecuteReader(CommandType.Text, strCheck.ToString()))
            {
                while (reader.Read())
                {
                    _C = Convert.ToString(reader["ContractNo"]);
                }
                reader.Close();
            }


            if (_C == "") { return false; }
            else return true;
        }

        private static Boolean Exist_Cargo ( string GID )
        {
            var strCheck = new StringBuilder();

            strCheck.Append(" select GID");//0未提交
            strCheck.Append(" from import_CARGO_public where GID = '" + GID+"' ");
            DataSet ds = new DataSet();
            Database db_check = DatabaseFactory.CreateDatabase();
            var _C = "";

            using (IDataReader reader = db_check.ExecuteReader(CommandType.Text, strCheck.ToString()))
            {
                while (reader.Read())
                {
                    _C = Convert.ToString(reader["GID"]);
                }
                reader.Close();
            }


            if (_C == "") { return false; }
            else return true;
        }

        /*static public int DelSyncDetail(string GID,string tablename)
        {
            var strSql = new StringBuilder();
            strSql.Append(" delete from " + tablename + " where GID ='" + GID + "'");//Receipt_Doc_Public
            //strSql.Append(" delete from " + tablename + " where GID ='" + GID + "'");//CH_FEE_Public
            var _count = 0;
            Database db = DatabaseFactory.CreateDatabase();
            _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString());

            return _count;
        }*/


        #endregion


        public static string ContractNo { get; set; }


        static public DBResult SetPrice ( string Price_1, string Price_2, string Price_3, string Pay_Amount_2, string GIdSql )
        {
            var result = new DBResult();
            using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
            {
                try
                {
                    var sSql = "";
                    if (Price_1 != "")
                    {
                        if (Convert.ToDouble(Price_1) >= 0)
                        {
                            sSql = "update tmswl_HH_ZY set Price_1=" + Price_1;

                            sSql = sSql + ",Amount_1=(case BillingType when 0 then Tons*distance*" + Price_1 + " when 1 then distance*" + Price_1 + " end )";
                        }
                    }
                    if (Price_2 != "")
                    {
                        if (Convert.ToDouble(Price_2) >= 0)
                        {
                            if (sSql == "")
                            {
                                sSql = "update tmswl_HH_ZY set Price_2=" + Price_2 + ",Pay_Amount_2=(case BillingType when 0 then Tons*distance*" + Price_2 + " when 1 then distance*" + Price_2 + " end )";
                            }
                            else if (Pay_Amount_2 == "")
                            {
                                sSql = sSql + ", Price_2=" + Price_2 + ",Pay_Amount_2=(case BillingType when 0 then Tons*distance*" + Price_2 + " when 1 then distance*" + Price_2 + " end )";
                            }
                            else
                            {
                                sSql = sSql + ", Price_2=" + Price_2;
                            }
                        }
                    }
                    /*
                    if (Price_3 != "")
                    {
                        if (Convert.ToDouble(Price_2) >= 0)
                        {
                            if (sSql == "")
                            {
                                sSql = "update tmswl_HH_ZY set Price_2=" + Price_2 + ",Pay_Amount_2=(case BillingType when 0 then Tons*distance*" + Price_2 + " when 1 then distance*" + Price_2 + " end )";
                            }
                            else if (Pay_Amount_2 == "")
                            {
                                sSql = sSql + ", Price_2=" + Price_2 + ",Pay_Amount_2=(case BillingType when 0 then Tons*distance*" + Price_2 + " when 1 then distance*" + Price_2 + " end )";
                            }
                            else
                            {
                                sSql = sSql + ", Price_2=" + Price_2;
                            }
                        }
                    }*/
                    if (Pay_Amount_2 != "")
                    {
                        if (Convert.ToDouble(Pay_Amount_2) >= 0)
                        {
                            if (sSql == "")
                            {
                                sSql = "update tmswl_HH_ZY set Pay_Amount_2=" + Pay_Amount_2;
                            }
                            else
                            {
                                sSql = sSql + ", Pay_Amount_2=" + Pay_Amount_2;
                            }
                        }
                    }
                    sSql = sSql + " where " + GIdSql;

                    SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSql, null);

                    //事务提交
                    sqlTran.Commit();
                    result.Success = true;
                    result.Message = "保存成功";

                }
                catch (Exception execError)
                {
                    result.Success = false;
                    result.Message = "保存出现错误，请重试或联系系统管理员";
                    throw execError;
                }
                finally
                {
                    SqlHelper.CloseConnection();
                }
            }
            using (SqlTransaction sqlTran = SqlHelper.BeginTransaction(SqlHelper.ConnectionStringLocalTransaction))
            {
                try
                {
                    var sSql = "";
                    sSql = " update tmswl_HH_ZY set tax=Pay_Amount_2*0.045,Amount_2=Pay_Amount_2-Pay_Amount_2*0.045 ";
                    sSql = sSql + " where TruckNo in(select truckno from tmswltruck where property=2) and " + GIdSql;
                    sSql = sSql + " update tmswl_HH_ZY set tax=0,Amount_2=Pay_Amount_2 ";
                    sSql = sSql + " where TruckNo in(select truckno from tmswltruck where property in(0,1)) and " + GIdSql;
                    SqlHelper.ExecuteNonQuery(sqlTran, CommandType.Text, sSql, null);

                    //事务提交
                    sqlTran.Commit();
                    result.Success = true;
                    result.Message = "保存成功";

                }
                catch (Exception execError)
                {
                    result.Success = false;
                    result.Message = "保存出现错误，请重试或联系系统管理员";
                    throw execError;
                }
                finally
                {
                    SqlHelper.CloseConnection();
                }
            }
            return result;
        }

        public static bool SetBatchRate(string GIDS, string Rate)
        {
            var iError = 0;
            string strSql = "exec Proc_XXH_BatchRate  '" + GIDS + "','" + Rate + "' ";
            Database db = DatabaseFactory.CreateDatabase();
            using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
            {

                while (reader.Read())
                {
                    iError = Convert.ToInt16(reader["result"]);

                    break;
                }

                reader.Close();
            }


            if (iError == 0)
            {
                return true;

            }

            else
            {
                return false;
            }
        }


    }
}