using System; using System.Data; using System.Collections.Generic; using System.Text; using DSWeb.Areas.Import.Models.ImportTrade; using Microsoft.Practices.EnterpriseLibrary.Data; using DSWeb.Areas.CommMng.Models; using System.Web; using System.Data.Common; using DSWeb.TruckMng.Comm.Cookie; using WebSqlHelper; using HcUtility.Core; using System.IO; using System.Data.SqlClient; using DSWeb.Areas.Import.Controllers; using DSWeb.Areas.CommMng.DAL; namespace DSWeb.Areas.Import.DAL.ImportTrade { public partial class ImportSyncDAL { #region 同步信息方法 static public List GetSyncList(string strCondition, string sort) { //合同信息/商品信息 ——每合同一行 var strSql = new StringBuilder(); strSql.Append(" select Local_ContractNo,ContractNo "); strSql.Append(" ,(select HTH from Import_main where contractno=p.Local_ContractNo)HTHRef "); strSql.Append(" ,HTH,Billno,ContainerNo,SealNo,ShipCompany_id,Vessel,Voyage,Ex_sailingdate "); strSql.Append(" ,dbo.trimdate(Sailingdate) Sailingdate,dbo.trimdate(ArrivalDate) ArrivalDate "); strSql.Append(" ,dbo.trimdate(RecDate) RecDate,dbo.trimdate(tax_date) tax_date,dbo.trimdate(tax_paydate) tax_paydate,Sampling_need "); strSql.Append(" ,dbo.trimdate(SamplingDate_Act) SamplingDate_Act,dbo.trimdate(CustomsReleaseDate) CustomsReleaseDate,PortDays,dbo.trimdate(inspection_date) inspection_date "); strSql.Append(" ,dbo.trimdate(ReceiveDate) ReceiveDate,Received,(case isnull(Received,0) when 0 then '未同步' else '已同步' end) ReceivedRef "); strSql.Append(" from import_main_public p "); if (!string.IsNullOrEmpty(strCondition)) { strSql.Append(" where " + strCondition); } return SetSyncList(strSql); } private static List SetSyncList(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { ImportSyncmb data = new ImportSyncmb(); #region Set DB data to Object data.Local_ContractNo = Convert.ToString(reader["Local_ContractNo"]); data.ContractNo = Convert.ToString(reader["ContractNo"]); data.HTHRef = Convert.ToString(reader["HTHRef"]); data.HTH = Convert.ToString(reader["HTH"]); data.BillNo = Convert.ToString(reader["Billno"]); data.ContainerNo = Convert.ToString(reader["ContainerNo"]); data.SealNo = Convert.ToString(reader["SealNo"]); data.ShipCompany_id = Convert.ToString(reader["ShipCompany_id"]); 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.RecDate = Convert.ToString(reader["RecDate"]); data.tax_date = Convert.ToString(reader["tax_date"]); data.tax_paydate = Convert.ToString(reader["tax_paydate"]); data.Sampling_need = Convert.ToString(reader["Sampling_need"]); data.SamplingDate_Act = Convert.ToString(reader["SamplingDate_Act"]); data.CustomsReleaseDate = Convert.ToString(reader["CustomsReleaseDate"]); data.PortDays = Convert.ToString(reader["PortDays"]); data.inspection_date = Convert.ToString(reader["inspection_date"]); data.ReceiveDate = Convert.ToString(reader["ReceiveDate"]); data.Received = Convert.ToString(reader["Received"]); data.ReceivedRef = Convert.ToString(reader["ReceivedRef"]); #endregion headList.Add(data); } reader.Close(); } return headList; } static public List GetSync(string ContractNo, string Local_ContractNo) { //合同信息/商品信息 ——每合同一行 var strSql = new StringBuilder(); /* strSql.Append(" select '提单号' fieldname,(select billno from Import_main_public where ContractNo='"+Local_ContractNo+"') newValue "); strSql.Append(" ,(select billno from Import_main where ContractNo='"+ContractNo+"') oldValue "); strSql.Append(" ,case when (select billno from Import_main_public where ContractNo='" + Local_ContractNo + "')=(select billno from Import_main where ContractNo='" + ContractNo + "') "); strSql.Append(" then 1 else 0 end isSame "); */ //var _s=getsql(Local_ContractNo, ContractNo,"提单号","billno"); strSql.Append(getsql(ContractNo, Local_ContractNo, "提单号", "billno")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "箱号", "ContainerNo")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "铅封号", "SealNo")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "船公司", "ShipCompany_id")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "用证公司", "company", "(select name from company where gid=p.company)")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "船名", "Vessel")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "航次", "Voyage")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "预计开船日", "Ex_sailingdate")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "开船日", "Sailingdate")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "到港日", "ArrivalDate")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "回空日期", "RecDate")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "出税日期", "tax_date")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "付税日期", "tax_paydate")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "海关验货日", "SamplingDate_Act")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "海关放行日", "CustomsReleaseDate")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "港口天数", "PortDays")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "商检验货日", "inspection_date")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "合同号", "HTH")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "贸易商(国外销售者)", "seller")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "购货方", "buyer")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "双抬头", "STT")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "国家代码", "countryid", "(select country from code_country where COUNTRYID=p.COUNTRYID)")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "接单时间", "creattime")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "审单时间", "Audittime")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "业务流程状态", "MainState", "(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=3 and EnumValueID=MainState)")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "是否打印发票", "printed")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "是否有自动证", "AutoAPP")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "提单时间", "BillTime")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "免箱期", "Freetime")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "成交方式", "TransactionMethod", "(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=1 and EnumValueID=TransactionMethod)")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "付款方式", "PaymentMethods", "(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=4 and EnumValueID=PaymentMethods)")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "是否已交保证金", "SecurityDeposit", "(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=0 and EnumValueID=SecurityDeposit)")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "海关确定抽检日期", "SamplingDate_Plan")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "报检日期", "inspection_Signup_date")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "报检号", "inspection_no")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "卫生许可证(ciq)", "CIQ_licence")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "卫生证是否可查", "CIQ_canbesearch", "(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=0 and EnumValueID=CIQ_canbesearch)")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "备注1", "remark")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "目的港", "port", "(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=10 and EnumValueID=port)")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "付货款时间(尾款)", "Paydate")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "电放时间", "E_Billtime")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "换单时间", "HDtime")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "备注2", "remark_2")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "备注3", "remark_3")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "备注4", "remark_4")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "海关是否验货", "Sampling_need", "(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=0 and EnumValueID=Sampling_need)")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "报关单号", "DeclareNumber")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "是否商检慢检", "Inspection_Storage", "(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=0 and EnumValueID=Inspection_Storage)")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "送货日", "DeliveryDate")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "送货地址", "DeliveryAddress")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "送货联系人", "Contacter")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "送货联系人电话", "Tel")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "溢短装", "Overfilled")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "单据是否完备", "ReceiptStatus")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "寄单情况", "Receiptremark")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "付货款时间(预付)", "Prepaydate")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "订货方(中间商)", "TRADINGAGENCY")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "库存清空", "KCClear")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "收单据模式", "RCVMode", "(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=23 and EnumValueID=RCVMode)")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "木质包装", "MZBZ")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "提单提交方式", "BillType", "(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=6 and EnumValueID=BillType)")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "靠泊码头", "Dock")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "报关行", "Customs_broker")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "报检行", "inspection_broker")); //strSql.Append(" union all "); //strSql.Append(getdatesql(ContractNo, Local_ContractNo, "分公司接单日期", "CreateTime_2")); //strSql.Append(" union all "); //strSql.Append(getdatesql(ContractNo, Local_ContractNo, "分公司转单日期", "ResendTime")); //strSql.Append(" union all "); //strSql.Append(getdatesql(ContractNo, Local_ContractNo, "XXXXXXX", "BuzType")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "商检收货单位", "Inspection_Buyer")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "商检收货单位联系人", "Inspection_Contacter")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "商检收货单位联系电话", "Inspection_Tel")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "商检收货单位地址", "Inspection_DeliveryAddress")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "已有合同", "HaveContract")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "有商品标签", "HaveCargoMark")); //strSql.Append(" union all "); //strSql.Append(getdatesql(ContractNo, Local_ContractNo, "XXXXXXX", "Supplier_Billno")); //strSql.Append(" union all "); //strSql.Append(getdatesql(ContractNo, Local_ContractNo, "XXXXXXX", "Other_Billno")); //strSql.Append(" union all "); //strSql.Append(getdatesql(ContractNo, Local_ContractNo, "XXXXXXX", "Agent")); //strSql.Append(" union all "); //strSql.Append(getdatesql(ContractNo, Local_ContractNo, "XXXXXXX", "Buy_Type")); //strSql.Append(" union all "); //strSql.Append(getdatesql(ContractNo, Local_ContractNo, "XXXXXXX", "Buy_OP")); //strSql.Append(" union all "); //strSql.Append(getdatesql(ContractNo, Local_ContractNo, "单据快递单号", "Bill_ExpressNo")); //strSql.Append(" union all "); //strSql.Append(getdatesql(ContractNo, Local_ContractNo, "XXXXXXX", "Agent_paydate")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "是否已做样本清单", "HAVESAMPLE")); //strSql.Append(" union all "); //strSql.Append(getdatesql(ContractNo, Local_ContractNo, "XXXXXXX", "ISBONDED")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "样本清单确认", "CHECKSAMPLE")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "箱型", "CTN")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "国外寄单快递号", "BILLTRANCNO")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "是否锁汇", "LOCK_EXCHANGERATE")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "锁汇汇率", "EXCHANGERATE")); strSql.Append(" union all "); strSql.Append(getdatesql(ContractNo, Local_ContractNo, "启封/整改放行日", "Unsealdate")); strSql.Append(" union all "); strSql.Append(getsql(ContractNo, Local_ContractNo, "是否整改", "ISREFORM")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "本地合同序列号", "Local_ContractNo")); //strSql.Append(" union all "); //strSql.Append(getdatesql(ContractNo, Local_ContractNo, "信息公开日期", "publicDate")); //strSql.Append(" union all "); //strSql.Append(getdatesql(ContractNo, Local_ContractNo, "信息接收日期", "ReceiveDate")); //strSql.Append(" union all "); //strSql.Append(getsql(ContractNo, Local_ContractNo, "是否已接收", "Received")); return SetSync(strSql); } static private string getsql ( string ContractNo, string Local_ContractNo, string fieldname, string field) { return getsql(ContractNo, Local_ContractNo, fieldname, field, field); } static private string getsql ( string ContractNo, string Local_ContractNo, string fieldname, string field, string fieldRef ) { var strSql = new StringBuilder(); strSql.Append(" select '" + fieldname + "' fieldname,'" + field + "' field "); strSql.Append(" ,convert(varchar(200),(select " + fieldRef + " from Import_main_public p where ContractNo='" + ContractNo + "')) fieldRef "); strSql.Append(" ,(select convert(varchar(200)," + field + ") from Import_main_public where ContractNo='" + ContractNo + "') newValue "); strSql.Append(" ,(select convert(varchar(200)," + fieldRef + ") from Import_main p where ContractNo='" + Local_ContractNo + "') oldValue "); strSql.Append(" ,case when Convert(varchar(200),(select " + field + " from Import_main_public where ContractNo='" + ContractNo + "')) "); strSql.Append(" =Convert(varchar(200),(select " + field + " from Import_main where ContractNo='" + Local_ContractNo + "')) "); strSql.Append(" then 1 else 0 end isSame "); return strSql.ToString(); } static private string getdatesql(string ContractNo, string Local_ContractNo, string fieldname, string field) { var strSql = new StringBuilder(); strSql.Append(" select '" + fieldname + "' fieldname,'" + field + "' field,(select dbo.trimdate(" + field + ") from Import_main_public where ContractNo='" + ContractNo + "') fieldRef "); strSql.Append(" ,(select dbo.trimdate(" + field + ") from Import_main_public where ContractNo='" + ContractNo + "') newValue "); strSql.Append(" ,(select dbo.trimdate(" + field + ") from Import_main where ContractNo='" + Local_ContractNo + "') oldValue "); strSql.Append(" ,case when (select dbo.trimdate(" + field + ") from Import_main_public where ContractNo='" + ContractNo + "')=(select dbo.trimdate(" + field + ") from Import_main where ContractNo='" + Local_ContractNo + "') "); strSql.Append(" then 1 else 0 end isSame "); return strSql.ToString(); } private static List SetSync(StringBuilder strSql) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { Syncmb data = new Syncmb(); #region Set DB data to Object data.FieldName = Convert.ToString(reader["FieldName"]); data.Field = Convert.ToString(reader["Field"]); data.NewValueRef = Convert.ToString(reader["FieldRef"]); data.NewValue = Convert.ToString(reader["NewValue"]); data.OldValue = Convert.ToString(reader["OldValue"]); data.isSame = Convert.ToString(reader["isSame"]); #endregion headList.Add(data); } reader.Close(); } return headList; } static public int SaveSync(string SQL) { var strSql = new StringBuilder(); strSql.Append(SQL); var _count = 0; Database db = DatabaseFactory.CreateDatabase(); _count = db.ExecuteNonQuery(CommandType.Text, strSql.ToString()); return _count; } static public List GetSync_Local ( string Local_ContractNo ) { //合同信息/商品信息 var strSql = new StringBuilder(); strSql.Append(" SELECT M.[id],M.[ContractNo],M.[Local_ContractNo],[HTH],[seller],M.[company],comp.name as companyname,[buyer],[STT], "); strSql.Append(" M.[countryid],[creator],[Auditor],[MainState],[printed],[AutoAPP],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(" case [BillTime] when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar, [BillTime],23) end as BillTime, "); strSql.Append(" case [Ex_sailingdate] when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar, [Ex_sailingdate],23) end as Ex_sailingdate, "); strSql.Append(" case [Sailingdate] when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar, [Sailingdate],23) end as Sailingdate , "); strSql.Append(" case [ArrivalDate] when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar , [ArrivalDate],23) end as ArrivalDate "); strSql.Append(" ,[ContainerNo],[SealNo],[TransactionMethod],[PaymentMethods] "); strSql.Append(" ,[SecurityDeposit], M.TRADINGAGENCY, "); strSql.Append(" dbo.trimdate(CustomsReleaseDate) as CustomsReleaseDate, "); strSql.Append(" case [SamplingDate_Plan] when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar ,[SamplingDate_Plan],23) end as SamplingDate_Plan, "); strSql.Append(" case [SamplingDate_Act] when '1900-01-01' then '' else CONVERT(varchar ,[SamplingDate_Act],23) end as SamplingDate_Act, "); strSql.Append(" case [inspection_Signup_date] when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar ,[inspection_Signup_date],23) end as inspection_Signup_date "); strSql.Append(" ,[inspection_no],cg.FactoryNo, "); strSql.Append(" case [inspection_date] when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar ,[inspection_date],23) end as inspection_date "); strSql.Append(" ,cg.id as cgid ,cg.name,c.country, convert(bigint ,M.TimeMark) as TimeMark , "); strSql.Append(" case [creattime] when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar , [creattime],23) end as creattime , "); strSql.Append(" case [Audittime] when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar , [Audittime],23) end as Audittime , "); strSql.Append(" Freetime,CIQ_licence,CIQ_canbesearch,dbo.F_YN(convert(int,CIQ_canbesearch)) CIQ_canbesearchRef , ");// strSql.Append(" case [tax_date] when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar(12) , [tax_date],23) end as tax_date , "); strSql.Append(" case [tax_paydate] when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar(12) , [tax_paydate],23) end as 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(" case M.Paydate when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar , M.Paydate,23) end as Paydate , "); strSql.Append(" case M.Prepaydate when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar , M.Prepaydate,23) end as Prepaydate , "); strSql.Append(" case M.E_Billtime when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar , M.E_Billtime,23) end as E_Billtime , "); strSql.Append(" case M.HDtime when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar , M.HDtime,23) end as HDtime , "); strSql.Append(" case M.DeliveryDate when '1900-01-01 00:00:00.000' then '' else CONVERT(varchar , M.DeliveryDate,23) end as 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(" ,case when isnull((select sum(isnull(amount,0)) from ch_fee where bsno=m.contractno and FEESTATUS in(10,11)),0)=0 then 1 "); strSql.Append(" else case when isnull((select sum(isnull(amount, 0)) from ch_fee where bsno = m.contractno and FEESTATUS in (10, 11)), 0) > 0 "); strSql.Append(" and isnull((select sum(isnull(amount,0)) from ch_fee where bsno = m.contractno and FEESTATUS in(10, 11)),0) "); strSql.Append(" - isnull((select sum(isnull(settlement, 0) * isnull(EXCHANGERATE, 1)) from ch_fee "); strSql.Append(" where bsno in (select F_NO from IMPORT_FINANCE_BODY where BSNO = M.ContractNo) and feetype = 1),0) > 0 then 3 else 9 end end isRongZi "); */ 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.AGENT"); strSql.Append(" ,dbo.trimtime(m.CreateTime_2) CreateTime_2,dbo.trimtime(m.ResendTime) ResendTime,M.Dock,M.Customs_broker,M.inspection_broker "); strSql.Append(" FROM [dbo].[Import_main_public] 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(Local_ContractNo)) { strSql.Append(" where Local_ContractNo='" + Local_ContractNo + "'"); } return SetData_Local(strSql); } private static List SetData_Local ( StringBuilder strSql ) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { ImportUploadmb data = new ImportUploadmb(); #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.Local_ContractNo = Convert.ToString(reader["Local_ContractNo"]); data.HTH = Convert.ToString(reader["HTH"]); 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.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.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.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"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 公开费用名称 static public List GetChFee_Pub ( string BSNO ) { //合同信息/商品信息 ——每合同一行 var strSql = new StringBuilder(); strSql.Append(" select GID,BSNO,(SELECT HTH FROM IMPORT_MAIN_PUBLIC WHERE ContractNo=BSNO) HTH,CUSTOMERNAME,FEENAME "); strSql.Append(" ,FEETYPE,case FEETYPE when 1 then '应收' else '应付' end FEETYPERef,UNITPRICE,QUANTITY,AMOUNT,CURRENCY "); strSql.Append(" ,EXCHANGERATE,settlement,dbo.trimdate(SETTLEDATE) SETTLEDATE,0 FEESTATUS "); strSql.Append(" ,'' FeeStatus_Ref,REMARK "); strSql.Append(" from CH_FEE_Public "); if (!string.IsNullOrEmpty(BSNO)) { strSql.Append(" where BSNO='" + BSNO+"'"); } return SetChFee_Pub(strSql); } static public List GetChFee_Local ( string BSNO ) { //合同信息/商品信息 ——每合同一行 var strSql = new StringBuilder(); strSql.Append(" select GID,BSNO,(SELECT HTH FROM IMPORT_MAIN WHERE ContractNo=BSNO) HTH,CUSTOMERNAME,FEENAME "); strSql.Append(" ,FEETYPE,case FEETYPE when 1 then '应收' else '应付' end FEETYPERef,UNITPRICE,QUANTITY,AMOUNT,CURRENCY "); strSql.Append(" ,EXCHANGERATE,settlement,'' SETTLEDATE,FEESTATUS "); strSql.Append(" ,(select EnumValueName from tSysEnumValue where LangId=0 and EnumTypeID=99024 and EnumValueID=FeeStatus) as FeeStatus_Ref,REMARK "); strSql.Append(" from CH_FEE "); if (!string.IsNullOrEmpty(BSNO)) { strSql.Append(" where BSNO='" + BSNO + "'"); } return SetChFee_Pub(strSql); } private static List SetChFee_Pub ( StringBuilder strSql ) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { FeeSyncmb data = new FeeSyncmb(); #region Set DB data to Object data.GID = Convert.ToString(reader["GID"]); data.BSNO = Convert.ToString(reader["BSNO"]); data.HTH = Convert.ToString(reader["HTH"]); data.FEETYPE = Convert.ToString(reader["FEETYPE"]); data.FEETYPERef = Convert.ToString(reader["FEETYPERef"]); data.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]); 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.CURRENCY = Convert.ToString(reader["CURRENCY"]); data.EXCHANGERATE = Convert.ToString(reader["EXCHANGERATE"]); data.SETTLEMENT = Convert.ToString(reader["SETTLEMENT"]); data.SETTLEDATE = Convert.ToString(reader["SETTLEDATE"]); data.FEESTATUS = Convert.ToString(reader["FEESTATUS"]); data.FEESTATUS_REF = Convert.ToString(reader["FEESTATUS_REF"]); data.REMARK = Convert.ToString(reader["REMARK"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 公开的商品信息 static public List GetCargoList_Pub ( string ContractNo ) { //合同信息/商品信息 ——每合同一行 var strSql = new StringBuilder(); strSql.Append(" select c.*,ci.code+'_'+ci.name as cicodeandname,ciq.ciqcode+'_'+ciq.ciqname as ciqcodeandname from Import_Cargo_Public 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 "); if (!string.IsNullOrEmpty(ContractNo)) { strSql.Append(" where ContractNo='" + ContractNo + "'"); } return SetCargoPub(strSql); } static public List GetCargoList_Local ( string ContractNo ) { //合同信息/商品信息 ——每合同一行 var strSql = new StringBuilder(); strSql.Append(" select c.*,ci.code+'_'+ci.name as cicodeandname,ciq.ciqcode+'_'+ciq.ciqname as ciqcodeandname 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(" where ContractNo='" + ContractNo + "'"); return SetCargoPub(strSql); } private static List SetCargoPub ( StringBuilder strSql ) { var headList = new List(); Database db = DatabaseFactory.CreateDatabase(); using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString())) { while (reader.Read()) { CargoSyncmb data = new CargoSyncmb(); #region Set DB data to Object 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.Ename = Convert.ToString(reader["Ename"]); data.UNIT = Convert.ToString(reader["UNIT"]); //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_limit = Convert.ToString(reader["price_limit"]); //data.Amount = Convert.ToString(reader["Amount"]); //data.tax_Amount = Convert.ToString(reader["tax_Amount"]); data.BoxCount = Convert.ToString(reader["BoxCount"]); data.BoxWeight = Convert.ToString(reader["BoxWeight"]); data.Exporter = Convert.ToString(reader["Exporter"]); data.FactoryNo = Convert.ToString(reader["FactoryNo"]); data.Productiondate = Convert.ToString(reader["Productiondate"]); data.currid = Convert.ToString(reader["currid"]); //data.prepayments = Convert.ToString(reader["prepayments"]); //data.balance = Convert.ToString(reader["balance"]); //data.paypart = Convert.ToString(reader["paypart"]); //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_1 = Convert.ToString(reader["tax_1"]); //data.tax_zz_1 = Convert.ToString(reader["tax_zz_1"]); //data.Declarenumber = Convert.ToString(reader["Declarenumber"]); //data.Exchangerate = Convert.ToString(reader["Exchangerate"]); //data.Exchangerate_Customs = Convert.ToString(reader["Exchangerate_Customs"]); //data.PreProportion = Convert.ToString(reader["PreProportion"]); //data.Amount_Writeoffs = Convert.ToString(reader["Amount_Writeoffs"]); data.sequence = Convert.ToString(reader["sequence"]); //data.SBYS = Convert.ToString(reader["SBYS"]); data.BZTCHNO = Convert.ToString(reader["BZTCHNO"]); data.CargoType = Convert.ToString(reader["CargoType"]); data.KINDPKGS = Convert.ToString(reader["KINDPKGS"]); //data.Weight_Act = Convert.ToString(reader["Weight_Act"]); //data.BoxCount_Act = Convert.ToString(reader["BoxCount_Act"]); //data.ExpirationDate = Convert.ToString(reader["ExpirationDate"]); //data.AGENCRATE = Convert.ToString(reader["AGENCRATE"]); //data.MOREORLESS = Convert.ToString(reader["MOREORLESS"]); data.Remark = Convert.ToString(reader["Remark"]); //data.SPECIFICATIONS = Convert.ToString(reader["SPECIFICATIONS"]); data.cicodeandname = Convert.ToString(reader["cicodeandname"]); data.ciqcodeandname = Convert.ToString(reader["ciqcodeandname"]); #endregion headList.Add(data); } reader.Close(); } return headList; } #endregion #region 根据BSNO判断是否已存在费用 public static bool CheckHasFeeWithBSNO (string bsno) { string sql = @"select count(*) from ch_fee where BSNO = '" + bsno + "' AND FEENAME in ('空运费','杂费','声明价值附加费','地面运费')"; Database db = DatabaseFactory.CreateDatabase(); int cnt = Convert.ToInt32(db.ExecuteScalar(CommandType.Text,sql)); return cnt>0; } #endregion } }