dengyu
/
DS7_Interface
1
0
Fork 0
Code Issues Pull Requests Projects Releases Wiki Activity
You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
master
Branches Tags
${ item.name }
Create tag ${ searchTerm }
Create branch ${ searchTerm }
from 'master'
${ noResults }
DS7_Interface/DSWeb/Webservice/DsWebServiceDAL.cs

1591 lines
90 KiB
C#
Raw Permalink Blame History

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using HcUtility.Comm;
using System.Data.SqlClient;
using WebSqlHelper;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DSWeb.EntityDA;
using DSWeb.DsWebService.XmlManifest;
using System.IO;
using System.Xml;
using System.Xml.Serialization;
namespace DSWeb.DsWebService.DAL.DsWebService
{
public class DsWebServiceDAL
{
public static DBResult AddBill(Manifest XObj, string Senderop, string companyid, string SenderHandphone, string SenderEmail, string SenderFax,string filename,bool AfterDoc)
{
var result = new DBResult();
var MessageID = "";
Database db = DatabaseFactory.CreateDatabase();
using (var conn = db.CreateConnection())
{
conn.Open();
var tran = conn.BeginTransaction();
try
{
var cmdInsert =
db.GetSqlStringCommand(
"insert into op_custom_manifest(GID,CORPID,XmlMessageID,XmlFunctionCode,XmlMessageType,XmlSenderID,XmlReceiverID,XmlSendTime,"
+"XmlVersion,XmlSenderName,XmlExitCustomsOffice,Agent,Carrier,Voyno,TransType,TransID,Vessel,PORTDISCHAGEID,ETADATETIME"
+",ETDDATETIME,MBLNO,CHANGEREASON1,CHANGEREASON2,CHANGEREASON3,TRANSTERMS,HBLNO,KGS,LOADPORTID,LOADPORTDATE,UNLOADPORTID,"
+"UNLOADPORTDATE,RECEIPTPLACEID,RECEIPTPLACE,TRANSHIPMENTID,TRANSITDESTINATION,ROUNTCOUNTRYID,GOODCONSIGNEPLACE,FREIGHTPAYMENT,"
+"PKGS,KINDPKGS,GROSSWEIGHT,CONSIGNEE,CONSIGNEEADDR,CONSIGNEECITY,CONSIGNEEPRIVEID,CONSIGNEEPRIVENAME,CONSIGNEEPOST,"
+"CONSINGEECOUNTRY,SHIPPER,SHIPPERADDR,SHIPPERCITY,SHIPPERPRIVEID,SHIPPERPRIVENAME,SHIPPERPOST,SHIPPERCOUNTRY"
+",NOTIFYPARTY,NOTIFYPARTYADDR,NOTIFYPARTYCITY,NOTIFYPARTYPRIVEID,NOTIFYPARTYPRIVENAME,NOTIFYPARTYPOST,NOTIFYPARTYCOUNTRY,"
+ "UNDGContact,UNDGContactNum,UNDGContactCommType,REMARKS,BsStatus,SenderOp,SenderHANDPHONE,SENDERFAX,SENDEREMAIL,ErrorText,RESAVETIME,ZipFileName) "
+"values (@GID,@CORPID,@XmlMessageID,@XmlFunctionCode,@XmlMessageType,@XmlSenderID,@XmlReceiverID,@XmlSendTime,"
+"@XmlVersion,@XmlSenderName,@XmlExitCustomsOffice,@Agent,@Carrier,@Voyno,@TransType,@TransID,@Vessel,@PORTDISCHAGEID,@ETADATETIME"
+",@ETDDATETIME,@MBLNO,@CHANGEREASON1,@CHANGEREASON2,@CHANGEREASON3,@TRANSTERMS,@HBLNO,@KGS,@LOADPORTID,@LOADPORTDATE,@UNLOADPORTID,"
+"@UNLOADPORTDATE,@RECEIPTPLACEID,@RECEIPTPLACE,@TRANSHIPMENTID,@TRANSITDESTINATION,@ROUNTCOUNTRYID,@GOODCONSIGNEPLACE,@FREIGHTPAYMENT,"
+"@PKGS,@KINDPKGS,@GROSSWEIGHT,@CONSIGNEE,@CONSIGNEEADDR,@CONSIGNEECITY,@CONSIGNEEPRIVEID,@CONSIGNEEPRIVENAME,@CONSIGNEEPOST,"
+"@CONSINGEECOUNTRY,@SHIPPER,@SHIPPERADDR,@SHIPPERCITY,@SHIPPERPRIVEID,@SHIPPERPRIVENAME,@SHIPPERPOST,@SHIPPERCOUNTRY"
+",@NOTIFYPARTY,@NOTIFYPARTYADDR,@NOTIFYPARTYCITY,@NOTIFYPARTYPRIVEID,@NOTIFYPARTYPRIVENAME,@NOTIFYPARTYPOST,@NOTIFYPARTYCOUNTRY,"
+ "@UNDGContact,@UNDGContactNum,@UNDGContactCommType,@REMARKS,@BsStatus,@SenderOp,@SenderHANDPHONE,@SENDERFAX,@SENDEREMAIL,@ErrorText,@RESAVETIME,@ZipFileName) ");
var cmdInsertctn =
db.GetSqlStringCommand(
@"insert into op_custom_manifest_ctn(GID,BSNO,CNTRNO,CNTRSIZE,CNTRCONSIGNOR,ISEMPTY,SEALNO)
values (@GID,@BSNO,@CNTRNO,@CNTRSIZE,@CNTRCONSIGNOR,@ISEMPTY,@SEALNO) ");
var cmdInsertcargo =
db.GetSqlStringCommand(
@"insert into op_custom_manifest_cargo(GID,BSNO,ITEMNO,PKGS,PKGSTYPE,MARKS,GOODSNAME,UNDGCode,HSCODE,REMARKS,GROSSWEIGHT,CNTRNO)
values (@GID,@BSNO,@ITEMNO,@PKGS,@PKGSTYPE,@MARKS,@GOODSNAME,@UNDGCode,@HSCODE,@REMARKS,@GROSSWEIGHT,@CNTRNO) ");
if (XObj != null)
{
XmlConsignment[] Consignment = XObj.Declaration.Consignment;
for (int i = 0; i < Consignment.Length; i++)
{
var BSNO = Guid.NewGuid().ToString();
cmdInsert.Parameters.Clear();
db.AddInParameter(cmdInsert, "@GID", DbType.String, BSNO);
db.AddInParameter(cmdInsert, "@CORPID", DbType.String, companyid);
MessageID = XObj.Head.MessageID;
db.AddInParameter(cmdInsert, "@XmlMessageID", DbType.String, MessageID);
db.AddInParameter(cmdInsert, "@XmlFunctionCode", DbType.String, XObj.Head.FunctionCode);
db.AddInParameter(cmdInsert, "@XmlMessageType", DbType.String, XObj.Head.MessageType);
db.AddInParameter(cmdInsert, "@XmlSenderID", DbType.String, XObj.Head.SenderID);
db.AddInParameter(cmdInsert, "@XmlReceiverID", DbType.String, XObj.Head.ReceiverID);
db.AddInParameter(cmdInsert, "@XmlSendTime", DbType.String, XObj.Head.FunctionCode);
db.AddInParameter(cmdInsert, "@XmlVersion", DbType.String, XObj.Head.Version);
db.AddInParameter(cmdInsert, "@XmlSenderName", DbType.String, XObj.Declaration.RepresentativePerson.Name);
db.AddInParameter(cmdInsert, "@XmlExitCustomsOffice", DbType.String, XObj.Declaration.ExitCustomsOffice.ID);
db.AddInParameter(cmdInsert, "@Agent", DbType.String, XObj.Declaration.Agent.ID);
db.AddInParameter(cmdInsert, "@Carrier", DbType.String, XObj.Declaration.Carrier.ID);
db.AddInParameter(cmdInsert, "@Voyno", DbType.String, XObj.Declaration.BorderTransportMeans.JourneyID);
db.AddInParameter(cmdInsert, "@TransType", DbType.String, XObj.Declaration.BorderTransportMeans.TypeCode);
db.AddInParameter(cmdInsert, "@TransID", DbType.String, XObj.Declaration.BorderTransportMeans.ID);
db.AddInParameter(cmdInsert, "@Vessel", DbType.String, XObj.Declaration.BorderTransportMeans.Name);
db.AddInParameter(cmdInsert, "@PORTDISCHAGEID", DbType.String, XObj.Declaration.BorderTransportMeans.FirstArrivalLocationID);
db.AddInParameter(cmdInsert, "@ETADATETIME", DbType.String, XObj.Declaration.BorderTransportMeans.ArrivalDateTime);
db.AddInParameter(cmdInsert, "@ETDDATETIME", DbType.String, XObj.Declaration.BorderTransportMeans.DepartureDateTime);
db.AddInParameter(cmdInsert, "@MBLNO", DbType.String, Consignment[i].TransportContractDocument.ID);
List<String> ChangeReasonCode = Consignment[i].TransportContractDocument.ChangeReasonCode;
var z = 1;
if (ChangeReasonCode.Count != 0)
{
foreach (string s in ChangeReasonCode)
{
if (z == 1)
{
db.AddInParameter(cmdInsert, "@CHANGEREASON1", DbType.String, s);
}
if (z == 2)
{
db.AddInParameter(cmdInsert, "@CHANGEREASON2", DbType.String, s);
}
if (z == 3)
{
db.AddInParameter(cmdInsert, "@CHANGEREASON3", DbType.String, s);
}
z = z + 1;
}
}
if (z == 1)
{
db.AddInParameter(cmdInsert, "@CHANGEREASON1", DbType.String, "");
db.AddInParameter(cmdInsert, "@CHANGEREASON2", DbType.String, "");
db.AddInParameter(cmdInsert, "@CHANGEREASON3", DbType.String, "");
}
if (z == 2)
{
db.AddInParameter(cmdInsert, "@CHANGEREASON2", DbType.String, "");
db.AddInParameter(cmdInsert, "@CHANGEREASON3", DbType.String, "");
}
if (z == 3)
{
db.AddInParameter(cmdInsert, "@CHANGEREASON3", DbType.String, "");
}
db.AddInParameter(cmdInsert, "@TRANSTERMS", DbType.String, Consignment[i].TransportContractDocument.ConditionCode);
if (Consignment[i].AssociatedTransportDocument!=null)
db.AddInParameter(cmdInsert, "@HBLNO", DbType.String, Consignment[i].AssociatedTransportDocument.ID);
else
db.AddInParameter(cmdInsert, "@HBLNO", DbType.String,"");
db.AddInParameter(cmdInsert, "@KGS", DbType.String, Consignment[i].GrossVolumeMeasure);
db.AddInParameter(cmdInsert, "@LOADPORTID", DbType.String, Consignment[i].LoadingLocation.ID);
db.AddInParameter(cmdInsert, "@LOADPORTDATE", DbType.String, Consignment[i].LoadingLocation.LoadingDate);
db.AddInParameter(cmdInsert, "@UNLOADPORTID", DbType.String, Consignment[i].UnloadingLocation.ID);
db.AddInParameter(cmdInsert, "@UNLOADPORTDATE", DbType.String, Consignment[i].UnloadingLocation.ArrivalDate);
db.AddInParameter(cmdInsert, "@RECEIPTPLACEID", DbType.String, Consignment[i].GoodsReceiptPlace.ID);
db.AddInParameter(cmdInsert, "@RECEIPTPLACE", DbType.String, Consignment[i].GoodsReceiptPlace.Name);
if (Consignment[i].TranshipmentLocation!=null)
db.AddInParameter(cmdInsert, "@TRANSHIPMENTID", DbType.String, Consignment[i].TranshipmentLocation.ID);
else
db.AddInParameter(cmdInsert, "@TRANSHIPMENTID", DbType.String,"");
if (Consignment[i].TransitDestination!=null)
db.AddInParameter(cmdInsert, "@TRANSITDESTINATION", DbType.String, Consignment[i].TransitDestination.ID);
else
db.AddInParameter(cmdInsert, "@TRANSITDESTINATION", DbType.String, "");
db.AddInParameter(cmdInsert, "@ROUNTCOUNTRYID", DbType.String,"");
if (Consignment[i].GoodsConsignedPlace!=null)
db.AddInParameter(cmdInsert, "@GOODCONSIGNEPLACE", DbType.String, Consignment[i].GoodsConsignedPlace.ID);
else
db.AddInParameter(cmdInsert, "@GOODCONSIGNEPLACE", DbType.String,"");
db.AddInParameter(cmdInsert, "@FREIGHTPAYMENT", DbType.String, Consignment[i].FreightPayment.MethodCode);
db.AddInParameter(cmdInsert, "@PKGS", DbType.String, Consignment[i].ConsignmentPackaging.QuantityQuantity);
db.AddInParameter(cmdInsert, "@KINDPKGS", DbType.String, Consignment[i].ConsignmentPackaging.TypeCode);
db.AddInParameter(cmdInsert, "@GROSSWEIGHT", DbType.String, Consignment[i].GrossVolumeMeasure);
db.AddInParameter(cmdInsert, "@CONSIGNEE", DbType.String, Consignment[i].Consignee.Name);
db.AddInParameter(cmdInsert, "@CONSIGNEEADDR", DbType.String, Consignment[i].Consignee.Address.Line);
db.AddInParameter(cmdInsert, "@CONSIGNEECITY", DbType.String, Consignment[i].Consignee.Address.CityName);
db.AddInParameter(cmdInsert, "@CONSIGNEEPRIVEID", DbType.String, Consignment[i].Consignee.Address.CountrySubEntityID);
db.AddInParameter(cmdInsert, "@CONSIGNEEPRIVENAME", DbType.String, Consignment[i].Consignee.Address.CountrySubEntityName);
db.AddInParameter(cmdInsert, "@CONSIGNEEPOST", DbType.String, Consignment[i].Consignee.Address.PostcodeID);
db.AddInParameter(cmdInsert, "@CONSINGEECOUNTRY", DbType.String, Consignment[i].Consignee.Address.CountryCode);
db.AddInParameter(cmdInsert, "@SHIPPER", DbType.String, Consignment[i].Consignor.Name);
db.AddInParameter(cmdInsert, "@SHIPPERADDR", DbType.String, Consignment[i].Consignor.Address.Line);
db.AddInParameter(cmdInsert, "@SHIPPERCITY", DbType.String, Consignment[i].Consignor.Address.CityName);
db.AddInParameter(cmdInsert, "@SHIPPERPRIVEID", DbType.String, Consignment[i].Consignor.Address.CountrySubEntityID);
db.AddInParameter(cmdInsert, "@SHIPPERPRIVENAME", DbType.String, Consignment[i].Consignor.Address.CountrySubEntityName);
db.AddInParameter(cmdInsert, "@SHIPPERPOST", DbType.String, Consignment[i].Consignor.Address.PostcodeID);
db.AddInParameter(cmdInsert, "@SHIPPERCOUNTRY", DbType.String, Consignment[i].Consignor.Address.CountryCode);
db.AddInParameter(cmdInsert, "@NOTIFYPARTY", DbType.String, Consignment[i].NotifyParty.Name);
db.AddInParameter(cmdInsert, "@NOTIFYPARTYADDR", DbType.String, Consignment[i].NotifyParty.Address.Line);
db.AddInParameter(cmdInsert, "@NOTIFYPARTYCITY", DbType.String, Consignment[i].NotifyParty.Address.CityName);
db.AddInParameter(cmdInsert, "@NOTIFYPARTYPRIVEID", DbType.String, Consignment[i].NotifyParty.Address.CountrySubEntityID);
db.AddInParameter(cmdInsert, "@NOTIFYPARTYPRIVENAME", DbType.String, Consignment[i].NotifyParty.Address.CountrySubEntityName);
db.AddInParameter(cmdInsert, "@NOTIFYPARTYPOST", DbType.String, Consignment[i].NotifyParty.Address.PostcodeID);
db.AddInParameter(cmdInsert, "@NOTIFYPARTYCOUNTRY", DbType.String, Consignment[i].NotifyParty.Address.CountryCode);
if (Consignment[i].UNDGContact != null)
{
db.AddInParameter(cmdInsert, "@UNDGContact", DbType.String, Consignment[i].UNDGContact.Name);
db.AddInParameter(cmdInsert, "@UNDGContactNum", DbType.String, Consignment[i].UNDGContact.Communication.ID);
db.AddInParameter(cmdInsert, "@UNDGContactCommType", DbType.String, Consignment[i].UNDGContact.Communication.TypeID);
}
else
{
db.AddInParameter(cmdInsert, "@UNDGContact", DbType.String, "");
db.AddInParameter(cmdInsert, "@UNDGContactNum", DbType.String,"");
db.AddInParameter(cmdInsert, "@UNDGContactCommType", DbType.String, "");
}
if ( XObj.Declaration.AdditionalInformation!=null)
db.AddInParameter(cmdInsert, "@REMARKS", DbType.String, XObj.Declaration.AdditionalInformation.Content);
else
db.AddInParameter(cmdInsert, "@REMARKS", DbType.String, "");
db.AddInParameter(cmdInsert, "@BsStatus", DbType.String,"已发送");
db.AddInParameter(cmdInsert, "@SenderOp", DbType.String, Senderop);
db.AddInParameter(cmdInsert, "@SenderHANDPHONE", DbType.String,SenderHandphone);
db.AddInParameter(cmdInsert, "@SENDERFAX", DbType.String, SenderFax);
db.AddInParameter(cmdInsert, "@SENDEREMAIL", DbType.String, SenderEmail);
db.AddInParameter(cmdInsert, "@ErrorText", DbType.String, "");
db.AddInParameter(cmdInsert, "@RESAVETIME", DbType.DateTime,DateTime.Now);
db.AddInParameter(cmdInsert, "@ZipFileName", DbType.String, filename);
db.ExecuteNonQuery(cmdInsert, tran);
XmlTransportEquipment[] TransportEquipment = Consignment[i].TransportEquipment;
for (int j = 0; j < TransportEquipment.Length; j++)
{
cmdInsertctn.Parameters.Clear();
db.AddInParameter(cmdInsertctn, "@GID", DbType.String, Guid.NewGuid().ToString());
db.AddInParameter(cmdInsertctn, "@BSNO", DbType.String, BSNO);
db.AddInParameter(cmdInsertctn, "@CNTRNO", DbType.String, TransportEquipment[j].EquipmentIdentification.ID);
db.AddInParameter(cmdInsertctn, "@CNTRSIZE", DbType.String, TransportEquipment[j].CharacteristicCode);
db.AddInParameter(cmdInsertctn, "@CNTRCONSIGNOR", DbType.String, TransportEquipment[j].SupplierPartyTypeCode);
db.AddInParameter(cmdInsertctn, "@ISEMPTY", DbType.String, TransportEquipment[j].FullnessCode);
db.AddInParameter(cmdInsertctn, "@SEALNO", DbType.String, TransportEquipment[j].SealID);
db.ExecuteNonQuery(cmdInsertctn, tran);
}
XmlConsignmentItem[] ConsignmentItem = Consignment[i].ConsignmentItem;
for (int m = 0; m < ConsignmentItem.Length; m++)
{
cmdInsertcargo.Parameters.Clear();
db.AddInParameter(cmdInsertcargo, "@GID", DbType.String, Guid.NewGuid().ToString());
db.AddInParameter(cmdInsertcargo, "@BSNO", DbType.String, BSNO);
db.AddInParameter(cmdInsertcargo, "@ITEMNO", DbType.Int16, ConsignmentItem[m].SequenceNumeric);
db.AddInParameter(cmdInsertcargo, "@PKGS", DbType.Decimal, ConsignmentItem[m].ConsignmentItemPackaging.QuantityQuantity);
db.AddInParameter(cmdInsertcargo, "@PKGSTYPE", DbType.String, ConsignmentItem[m].ConsignmentItemPackaging.TypeCode);
db.AddInParameter(cmdInsertcargo, "@MARKS", DbType.String, ConsignmentItem[m].ConsignmentItemPackaging.MarksNumbers);
db.AddInParameter(cmdInsertcargo, "@GOODSNAME", DbType.String, ConsignmentItem[m].Commodity.CargoDescription);
db.AddInParameter(cmdInsertcargo, "@UNDGCode", DbType.String, ConsignmentItem[m].Commodity.UNDGCode);
db.AddInParameter(cmdInsertcargo, "@HSCODE", DbType.String, ConsignmentItem[m].Commodity.TariffClassificationCode);
if (ConsignmentItem[m].AdditionalInformation!=null)
db.AddInParameter(cmdInsertcargo, "@REMARKS", DbType.String, ConsignmentItem[m].AdditionalInformation.Content);
else
db.AddInParameter(cmdInsertcargo, "@REMARKS", DbType.String, "");
db.AddInParameter(cmdInsertcargo, "@GROSSWEIGHT", DbType.Decimal, ConsignmentItem[m].GoodsMeasure.GrossMassMeasure);
db.AddInParameter(cmdInsertcargo, "@CNTRNO", DbType.String, ConsignmentItem[m].EquipmentIdentification.ID);
db.ExecuteNonQuery(cmdInsertcargo, tran);
}
if (AfterDoc)
{
var cmdInsertAft =
db.GetSqlStringCommand(
"insert into op_custom_manifest(GID,CORPID,XmlMessageID,XmlFunctionCode,XmlMessageType,XmlSenderID,XmlReceiverID,XmlSendTime,"
+ "XmlVersion,XmlSenderName,XmlExitCustomsOffice,Agent,Carrier,Voyno,TransType,TransID,Vessel,PORTDISCHAGEID,ETADATETIME"
+ ",ETDDATETIME,MBLNO,CHANGEREASON1,CHANGEREASON2,CHANGEREASON3,TRANSTERMS,HBLNO,KGS,LOADPORTID,LOADPORTDATE,UNLOADPORTID,"
+ "UNLOADPORTDATE,RECEIPTPLACEID,RECEIPTPLACE,TRANSHIPMENTID,TRANSITDESTINATION,ROUNTCOUNTRYID,GOODCONSIGNEPLACE,FREIGHTPAYMENT,"
+ "PKGS,KINDPKGS,GROSSWEIGHT,CONSIGNEE,CONSIGNEEADDR,CONSIGNEECITY,CONSIGNEEPRIVEID,CONSIGNEEPRIVENAME,CONSIGNEEPOST,"
+ "CONSINGEECOUNTRY,SHIPPER,SHIPPERADDR,SHIPPERCITY,SHIPPERPRIVEID,SHIPPERPRIVENAME,SHIPPERPOST,SHIPPERCOUNTRY"
+ ",NOTIFYPARTY,NOTIFYPARTYADDR,NOTIFYPARTYCITY,NOTIFYPARTYPRIVEID,NOTIFYPARTYPRIVENAME,NOTIFYPARTYPOST,NOTIFYPARTYCOUNTRY,"
+ "UNDGContact,UNDGContactNum,UNDGContactCommType,REMARKS,BsStatus,SenderOp,SenderHANDPHONE,SENDERFAX,SENDEREMAIL,ErrorText,RESAVETIME,ZipFileName) "
+ " Select '" + Guid.NewGuid().ToString() + "',CORPID,'A" +MessageID+ "','0',XmlMessageType,XmlSenderID,XmlReceiverID,XmlSendTime,"
+ "XmlVersion,XmlSenderName,XmlExitCustomsOffice,Agent,Carrier,Voyno,TransType,TransID,Vessel,PORTDISCHAGEID,ETADATETIME"
+ ",ETDDATETIME,MBLNO,CHANGEREASON1,CHANGEREASON2,CHANGEREASON3,TRANSTERMS,HBLNO,KGS,LOADPORTID,LOADPORTDATE,UNLOADPORTID,"
+ "UNLOADPORTDATE,RECEIPTPLACEID,RECEIPTPLACE,TRANSHIPMENTID,TRANSITDESTINATION,ROUNTCOUNTRYID,GOODCONSIGNEPLACE,FREIGHTPAYMENT,"
+ "PKGS,KINDPKGS,GROSSWEIGHT,CONSIGNEE,CONSIGNEEADDR,CONSIGNEECITY,CONSIGNEEPRIVEID,CONSIGNEEPRIVENAME,CONSIGNEEPOST,"
+ "CONSINGEECOUNTRY,SHIPPER,SHIPPERADDR,SHIPPERCITY,SHIPPERPRIVEID,SHIPPERPRIVENAME,SHIPPERPOST,SHIPPERCOUNTRY"
+ ",NOTIFYPARTY,NOTIFYPARTYADDR,NOTIFYPARTYCITY,NOTIFYPARTYPRIVEID,NOTIFYPARTYPRIVENAME,NOTIFYPARTYPOST,NOTIFYPARTYCOUNTRY,"
+ "UNDGContact,UNDGContactNum,UNDGContactCommType,REMARKS,BsStatus,SenderOp,SenderHANDPHONE,SENDERFAX,SENDEREMAIL,ErrorText,RESAVETIME,ZipFileName"
+ " from op_custom_manifest where GID='" + BSNO + "' ");
db.ExecuteNonQuery(cmdInsertAft, tran);
}
}
}
result = new DBResult();
result.Success = true;
result.Message = "成功";
tran.Commit();
}
catch (Exception)
{
tran.Rollback();
result.Success = false;
result.Message = "保存出现错误，请重试或联系系统管理员";
return result;
}
}
result.Success = true;
result.Message = "保存成功";
return result;
}
public static String LoadBillStatus(string Mblno)
{
var strSql = new StringBuilder();
strSql.Append("SELECT CH_ID,MBLNO,CNTRNO,SEALNO,DATESTR,VOYNO,[STATUS],[FILENAME],ISPOSTED,CREATETIME");
strSql.Append(" FROM op_custom_status ");
strSql.Append(" Where MBLNO='" + Mblno + "' and (ISPOSTED=0 or ISPOSTED is null) ");
strSql.Append(" order by CREATETIME ");
DataSet ds = new DataSet();
Database db = DatabaseFactory.CreateDatabase();
ManifestStatus ManifestStatus = new ManifestStatus();
var billstatuslist =new List<XmlBillStatus>();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
const string UpdateSql = "update op_custom_status set IsPosted=1 where CH_ID=@CH_ID";
var cmdUpdate = db.GetSqlStringCommand(UpdateSql);
while (reader.Read())
{
XmlBillStatus billstatus = new XmlBillStatus();
billstatus.CH_ID =Convert.ToString(reader["CH_ID"]);
billstatus.MBLNO = Convert.ToString(reader["MBLNO"]);
if (reader["CNTRNO"] != DBNull.Value)
billstatus.CNTRNO = Convert.ToString(reader["CNTRNO"]);
else
billstatus.CNTRNO = " ";
billstatus.SEALNO = Convert.ToString(reader["SEALNO"]);
billstatus.DATESTR = Convert.ToString(reader["DATESTR"]);
billstatus.VOYNO = Convert.ToString(reader["VOYNO"]);
billstatus.STATUS = Convert.ToString(reader["STATUS"]);
billstatus.FILENAME = Convert.ToString(reader["FILENAME"]);
billstatuslist.Add(billstatus);
cmdUpdate.Parameters.Clear();
db.AddInParameter(cmdUpdate, "@CH_ID", DbType.Int32, reader["CH_ID"]);
db.ExecuteNonQuery(cmdUpdate);
}
reader.Close();
}
ManifestStatus.BillStatus = billstatuslist;
if (ManifestStatus != null)
{
var filename = Guid.NewGuid().ToString();
var FileToZip = @"d:\Manifest\" + filename + ".XML";
using (StreamWriter sw = new StreamWriter(FileToZip))
{
XmlSerializer xs = new XmlSerializer(ManifestStatus.GetType());
xs.Serialize(sw, ManifestStatus);
}
XmlDocument xobj = new XmlDocument();
xobj.Load(FileToZip);
if (File.Exists(FileToZip))
{
File.Delete(FileToZip);
}
return Xml2String(xobj);
}
else return "";
}
public static SeaiManifest LoadSeaiBs(string LoginName, string LoginPass, string Mobile, string Mblno,int start, int limit)
{
SeaiManifest SeaiManifest = new SeaiManifest();
if (LoginName != "qdtaize" || LoginPass != "EBBE3242-D49E-4398-BBFE-0133CA655EB5") {
SeaiManifest.ERROMSG = "账号密码不正确";
return SeaiManifest;
}
T_ALL_DA T_ALL_DA = new T_ALL_DA();
var CUSTOMERNAME = T_ALL_DA.GetStrSQL("SHOWNAME", "SELECT top 1 SHORTNAME SHOWNAME FROM info_client Where GID IN (SELECT LINKID FROM info_client_contact WHERE MOBILE='" + Mobile + "' AND ISNULL(ISSTOP,0)=0 ) ");
if (CUSTOMERNAME == "")
{
SeaiManifest.ERROMSG = "查询不到此联系人的相关信息";
return SeaiManifest;
}
var ispc=false;
if (Mblno != "")
{
var MBLNOSTR = T_ALL_DA.GetStrSQL("MBLNO", "SELECT TOP 1 MBLNO FROM OP_SEAI B LEFT JOIN op_apply_detail D ON (D.BSNO=B.BSNO) WHERE (B.MBLNO='" + Mblno + "' OR D.PONO='" + Mblno + "') AND B.CUSTOMERNAME='" + CUSTOMERNAME + "' ");
if (MBLNOSTR == "")
{
SeaiManifest.ERROMSG = "查询不到相关业务信息";
return SeaiManifest;
}
else {
if (MBLNOSTR != Mblno) ispc = true;
}
}
var strSql = new StringBuilder();
if (Mblno != "")
{
strSql.Append("SELECT BSNO,MBLNO,CUSTOMERNAME,VESSEL,VOYNO,ETD,ETA ");
strSql.Append(" FROM OP_SEAI ");
if (ispc)
strSql.Append(" Where CUSTOMERNAME='" + CUSTOMERNAME + "' AND BSNO IN (SELECT BSNO FROM op_apply_detail WHERE PONO='" + Mblno + "' ) ");
else
strSql.Append(" Where CUSTOMERNAME='" + CUSTOMERNAME + "' AND MBLNO='" + Mblno + "' ");
}
else {
strSql.Append("SELECT top 10 BSNO,MBLNO,CUSTOMERNAME,VESSEL,VOYNO,ETD,ETA ");
strSql.Append(" FROM OP_SEAI ");
strSql.Append(" Where CUSTOMERNAME='" + CUSTOMERNAME + "' order by ETD DESC ");
}
// Database db = DatabaseFactory.CreateDatabase();
// var XmlSeaiBsList = new List<XmlSeaiBs>();
// using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
// {
// while (reader.Read())
// {
// XmlSeaiBs SeaiBs = new XmlSeaiBs();
// SeaiBs.MBLNO = Convert.ToString(reader["MBLNO"]);
// SeaiBs.VESSEL = Convert.ToString(reader["VESSEL"]);
// SeaiBs.VOYNO = Convert.ToString(reader["VOYNO"]);
// if (reader["ETA"] != DBNull.Value)
// SeaiBs.ETD= Convert.ToString(reader["ETA"]);
// else
// SeaiBs.ETD= " ";
// if (reader["ETD"] != DBNull.Value)
// SeaiBs.ETA = Convert.ToString(reader["ETD"]);
// else
// SeaiBs.ETA = " ";
// SeaiBs.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
// var SeaiBsGoodsDetailList = new List<XmlSeaiBsGoodsDetail>();
// var strGoodsSql = new StringBuilder();
// strGoodsSql.Append("SELECT GOODSCODE,GOODSNAME,PKGS,PICINO,PONO,GOODSSTANDARD,KINDPKGS,KGS FROM op_apply_detail ");
// strGoodsSql.Append(" WHERE BSNO='" + Convert.ToString(reader["BSNO"]) + "' ");
// using (IDataReader Goodsreader = db.ExecuteReader(CommandType.Text, strGoodsSql.ToString()))
// {
// while (Goodsreader.Read())
// {
// XmlSeaiBsGoodsDetail SeaiBsGoodsDetail = new XmlSeaiBsGoodsDetail();
// SeaiBsGoodsDetail.GOODSCODE = Convert.ToString(Goodsreader["GOODSCODE"]);
// SeaiBsGoodsDetail.GOODSNAME = Convert.ToString(Goodsreader["GOODSNAME"]);
// SeaiBsGoodsDetail.PKGS = Convert.ToString(Goodsreader["PKGS"]);
// SeaiBsGoodsDetail.PICINO = Convert.ToString(Goodsreader["PICINO"]);
// SeaiBsGoodsDetail.PONO = Convert.ToString(Goodsreader["PONO"]);
// SeaiBsGoodsDetail.GOODSSTANDARD = Convert.ToString(Goodsreader["GOODSSTANDARD"]);
// SeaiBsGoodsDetail.KINDPKGS = Convert.ToString(Goodsreader["KINDPKGS"]);
// SeaiBsGoodsDetail.KGS = Convert.ToString(Goodsreader["KGS"]);
// SeaiBsGoodsDetailList.Add(SeaiBsGoodsDetail);
// }
// Goodsreader.Close();
// }
// SeaiBs.GOODSDETAIL = SeaiBsGoodsDetailList;
// var BSSTATUSList = new List<XmlSeaiBsStatus>();
// var strStatusSql = new StringBuilder();
// strStatusSql.Append("SELECT STATUS,COMPTIME FROM op_status ");
// strStatusSql.Append(" WHERE BSNO='" + Convert.ToString(reader["BSNO"]) + "' ");
// using (IDataReader Statusreader = db.ExecuteReader(CommandType.Text, strStatusSql.ToString()))
// {
// while (Statusreader.Read())
// {
// XmlSeaiBsStatus BSSTATUS = new XmlSeaiBsStatus();
// BSSTATUS.BSSTATUS = Convert.ToString(Statusreader["STATUS"]);
// BSSTATUS.BSSTATUSTIME = Convert.ToString(Statusreader["COMPTIME"]);
// BSSTATUSList.Add(BSSTATUS);
// }
// Statusreader.Close();
// }
// SeaiBs.BSSTATUS = BSSTATUSList;
// var wmsinlist = new List<XmlSeaiBsGoodsWmsIn>();
// var strwmsinSql = new StringBuilder();
// strwmsinSql.Append("select w.WMSNO,W.WMSDATE,W.CUSTOMERNAME,W.BLNO MBLNO,w.STORAGENAME,i.GOODSCODE,i.GOODSNAME,i.GOODSMODEL,i.GOODSPACK,i.GOODSRKSL,i.GOODSSTOCK,i.GOODSPACKSTOCK,i.PICIHAO PICINO,i.CONTRACTNO PONO ");
// strwmsinSql.Append(" from wms_in i left join wms w on (w.ASSOCIATEDNO=i.ASSOCIATEDNO) ");
// strwmsinSql.Append(" WHERE w.BLNO='" + Convert.ToString(reader["MBLNO"]) + "' AND W.CUSTOMERNAME='" + CUSTOMERNAME + "' ");
// if (ispc)
// strwmsinSql.Append(" and i.CONTRACTNO='" + Mblno + "' ");
// using (IDataReader Wmsinreader = db.ExecuteReader(CommandType.Text, strwmsinSql.ToString()))
// {
// while (Wmsinreader.Read())
// {
// XmlSeaiBsGoodsWmsIn WMSIN = new XmlSeaiBsGoodsWmsIn();
// WMSIN.PONO = Convert.ToString(Wmsinreader["PONO"]);
// WMSIN.PICINO = Convert.ToString(Wmsinreader["PICINO"]);
// WMSIN.WMSDATE = Convert.ToString(Wmsinreader["WMSDATE"]);
// WMSIN.WMSNO = Convert.ToString(Wmsinreader["WMSNO"]);
// WMSIN.GOODSNAME = Convert.ToString(Wmsinreader["GOODSNAME"]);
// WMSIN.GOODSCODE = Convert.ToString(Wmsinreader["GOODSCODE"]);
// WMSIN.GOODSPACK = Convert.ToString(Wmsinreader["GOODSPACK"]);
// WMSIN.GOODSPACKSTOCK = Convert.ToString(Wmsinreader["GOODSPACKSTOCK"]);
// WMSIN.GOODSRKSL = Convert.ToString(Wmsinreader["GOODSRKSL"]);
// WMSIN.GOODSSTOCK = Convert.ToString(Wmsinreader["GOODSSTOCK"]);
// WMSIN.STORAGENAME = Convert.ToString(Wmsinreader["STORAGENAME"]);
// wmsinlist.Add(WMSIN);
// }
// Wmsinreader.Close();
// }
// SeaiBs.WMSIN = wmsinlist;
// var wmsoutlist = new List<XmlSeaiBsGoodsWmsOut>();
// var strwmsoutSql = new StringBuilder();
// strwmsoutSql.Append("select w.WMSNO,W.WMSDATE,W.CUSTOMERNAME,W.BLNO MBLNO,w.STORAGENAME,i.GOODSCODE,i.GOODSNAME,i.GOODSMODEL,i.GOODSPACK,i.GOODSRKSL,i.GOODSSTOCK,i.GOODSPACKSTOCK,i.PICIHAO PICINO,i.CONTRACTNO PONO ");
// strwmsoutSql.Append(" from wms_in i left join wms w on (w.ASSOCIATEDNO=i.ASSOCIATEDNO) ");
// strwmsoutSql.Append(" WHERE w.BLNO='111111' AND W.CUSTOMERNAME='" + CUSTOMERNAME + "' ");
// if (ispc)
// strwmsoutSql.Append(" and i.CONTRACTNO='" + Mblno + "' ");
// using (IDataReader Wmsoutreader = db.ExecuteReader(CommandType.Text, strwmsoutSql.ToString()))
// {
// while (Wmsoutreader.Read())
// {
// XmlSeaiBsGoodsWmsOut WMSOUT = new XmlSeaiBsGoodsWmsOut();
// WMSOUT.PONO = Convert.ToString(Wmsoutreader["PONO"]);
// WMSOUT.PICINO = Convert.ToString(Wmsoutreader["PICINO"]);
// WMSOUT.OUTDATE = Convert.ToString(Wmsoutreader["OUTDATE"]);
// WMSOUT.WMSOUTNO = Convert.ToString(Wmsoutreader["WMSOUTNO"]);
// WMSOUT.GOODSNAME = Convert.ToString(Wmsoutreader["GOODSNAME"]);
// WMSOUT.GOODSCODE = Convert.ToString(Wmsoutreader["GOODSCODE"]);
// WMSOUT.GOODSPFSL = Convert.ToString(Wmsoutreader["GOODSPFSL"]);
//// WMSOUT.GOODSPACKSTOCK = Convert.ToString(Wmsoutreader["GOODSPACKSTOCK"]);
// WMSOUT.GOODSPACKPFSL = Convert.ToString(Wmsoutreader["GOODSPACKPFSL"]);
// WMSOUT.TRUCKNO = Convert.ToString(Wmsoutreader["TRUCKNO"]);
// wmsoutlist.Add(WMSOUT);
// }
// Wmsoutreader.Close();
// }
// SeaiBs.WMSOUT = wmsoutlist;
// XmlSeaiBsList.Add(SeaiBs);
// }
// reader.Close();
// }
// SeaiManifest.SeaiBs = XmlSeaiBsList;
return SeaiManifest;
}
#region 海运出口数据
public static SeaeManifest GetSeaeBsDataList(string LoginName, string LoginPass, string Mobile, string Mblno, int start, int limit)
{
SeaeManifest SeaeManifest = new SeaeManifest();
if (LoginName != "qdtaize" || LoginPass != "EBBE3242-D49E-4398-BBFE-0133CA655EB5")
{
SeaeManifest.ERROMSG = "账号密码不正确";
return SeaeManifest;
}
T_ALL_DA T_ALL_DA = new T_ALL_DA();
var CUSTOMERNAME = "";
var COMPANYEMPOLYEE = T_ALL_DA.GetStrSQL("GID", "SELECT top 1 GID FROM user_action Where ACTIONID='4B19971E-FA7F-4528-89F3-4F740CE3D8D5' AND USERID IN (SELECT USERID FROM user_baseinfo WHERE MOBILE='" + Mobile + "' ) ");
if (COMPANYEMPOLYEE == "")
{
// SeaiManifest.ERROMSG = "查询不到此联系人的相关信息";
// return SeaiManifest;
CUSTOMERNAME = T_ALL_DA.GetStrSQL("SHOWNAME", "SELECT top 1 SHORTNAME SHOWNAME FROM info_client Where GID IN (SELECT LINKID FROM info_client_contact WHERE MOBILE='" + Mobile + "' AND ISNULL(ISSTOP,0)=0 ) ");
if (CUSTOMERNAME == "")
{
SeaeManifest.ERROMSG = "查询不到此联系人的相关信息";
return SeaeManifest;
}
}
var ispc = false;
if (Mblno != "")
{
var MBLNOSTR = T_ALL_DA.GetStrSQL("MBLNO", "SELECT TOP 1 MBLNO FROM OP_SEAE B WHERE (B.MBLNO like '%" + Mblno + "%' ) ");
if (MBLNOSTR == "")
{
SeaeManifest.ERROMSG = "查询不到相关业务信息";
return SeaeManifest;
}
else
{
if (MBLNOSTR != Mblno) ispc = true;
}
}
var strSql = new StringBuilder();
strSql.Append(@"SELECT * from (SELECT row_number() over (");
strSql.Append(" order by B.ETD desc");
strSql.Append(@") as num , ");
strSql.Append(" B.BSNO,B.MBLNO,(SELECT DESCRIPTION FROM info_client WHERE SHORTNAME=B.CUSTOMERNAME) CUSTOMERNAME,B.VESSEL,B.VOYNO,B.CARRIER,B.CNTRTOTAL,B.ETD,B.ETA, ");
strSql.Append(" B.BLFRT,B.DESTINATION,B.ISSUETYPE,B.YARD,B.YARDATTN,B.YARDTEL,B.CLOSEDOCDATE,B.CLOSINGDATE,B.OP,B.SALE,B.SHIPAGENCY,B.PKGS,B.KGS,B.CBM,B.KINDPKGS,B.CUSTSERVICE ");
strSql.Append(",(SELECT TOP 1 OFFICEPHONE FROM user_baseinfo WHERE USERID IN (SELECT GID FROM [USER] WHERE SHOWNAME=B.OP)) as OPTEL");
strSql.Append(",(SELECT TOP 1 OFFICEPHONE FROM user_baseinfo WHERE USERID IN (SELECT GID FROM [USER] WHERE SHOWNAME=B.SALE)) as SALETEL");
strSql.Append(",(SELECT TOP 1 OFFICEPHONE FROM user_baseinfo WHERE USERID IN (SELECT GID FROM [USER] WHERE SHOWNAME=B.CUSTSERVICE)) as CUSTSERVICETEL");
strSql.Append(",(SELECT TOP 1 STATUS FROM OP_STATUS WHERE [STATUS] in ('重箱返场','海关放行','装船出运') and ISNULL(ISOPEN,0)=1 and BSNO=B.BSNO ORDER BY COMPTIME DESC,INPUTTIME DESC) as OPSTATUS");
strSql.Append(",(SELECT TOP 1 COMPTIME FROM OP_STATUS WHERE [STATUS] in ('重箱返场','海关放行','装船出运') and ISNULL(ISOPEN,0)=1 and BSNO=B.BSNO ORDER BY COMPTIME DESC,INPUTTIME DESC) as COMPTIME");
strSql.Append(" FROM OP_SEAE B ");
strSql.Append(" Where B.ETD>='2019-11-01' and isnull(B.MBLNO,'')<>'' ");
if (COMPANYEMPOLYEE == "")
{
strSql.Append(" and B.CUSTOMERNAME='" + CUSTOMERNAME + "' ");
}
if (Mblno != "")
{
//if (ispc)
// strSql.Append(" AND D.PONO='" + Mblno + "' ");
//else
strSql.Append(" AND (B.MBLNO like '%" + Mblno + "%' )");
}
strSql.Append(@")as t ");
strSql.Append(string.Format("where t.num>{0} and t.num<={1} order by t.num ", start, start + limit));
Database db = DatabaseFactory.CreateDatabase();
var XmlSeaeBsList = new List<XmlSeaeBs>();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
XmlSeaeBs SeaiBs = new XmlSeaeBs();
SeaiBs.MBLNO = Convert.ToString(reader["MBLNO"]);
SeaiBs.VESSEL = Convert.ToString(reader["VESSEL"]);
SeaiBs.VOYNO = Convert.ToString(reader["VOYNO"]);
if (reader["ETD"] != DBNull.Value)
SeaiBs.ETD = Convert.ToDateTime(reader["ETD"]).ToString("yyyy-MM-dd");
else
SeaiBs.ETD = " ";
if (reader["ETA"] != DBNull.Value)
SeaiBs.ETA = Convert.ToDateTime(reader["ETA"]).ToString("yyyy-MM-dd");
else
SeaiBs.ETA = " ";
if (SeaiBs.ETD == "1900-01-01") SeaiBs.ETD = "";
if (SeaiBs.ETA == "1900-01-01") SeaiBs.ETA = "";
SeaiBs.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
SeaiBs.CARRIER = Convert.ToString(reader["CARRIER"]);
SeaiBs.CNTRTOTAL = Convert.ToString(reader["CNTRTOTAL"]);
SeaiBs.BLFRT = Convert.ToString(reader["BLFRT"]);
SeaiBs.DESTINATION = Convert.ToString(reader["DESTINATION"]);
SeaiBs.ISSUETYPE = Convert.ToString(reader["ISSUETYPE"]);
SeaiBs.YARD = Convert.ToString(reader["YARD"]);
SeaiBs.YARDATTN = Convert.ToString(reader["YARDATTN"]) + '-' + Convert.ToString(reader["YARDTEL"]);
if (reader["CLOSINGDATE"] != DBNull.Value)
SeaiBs.CLOSINGDATE = Convert.ToDateTime(reader["CLOSINGDATE"]).ToString("yyyy-MM-dd HH:mm:ss");
if (reader["CLOSEDOCDATE"] != DBNull.Value)
SeaiBs.CLOSEDOCDATE = Convert.ToDateTime(reader["CLOSEDOCDATE"]).ToString("yyyy-MM-dd HH:mm:ss");
SeaiBs.OPATTN = Convert.ToString(reader["OP"]) + '-' + Convert.ToString(reader["OPTEL"]);
SeaiBs.SALEATTN = Convert.ToString(reader["SALE"]) + '-' + Convert.ToString(reader["SALETEL"]);
SeaiBs.CUSTSERVICE = Convert.ToString(reader["CUSTSERVICE"]);
SeaiBs.CUSTSERVICETEL = Convert.ToString(reader["CUSTSERVICETEL"]);
SeaiBs.SHIPAGENCY = Convert.ToString(reader["SHIPAGENCY"]);
SeaiBs.PKGS = Convert.ToString(reader["PKGS"]);
//SeaiBs.PICINO = Convert.ToString(reader["PICINO"]);
//SeaiBs.PONO = Convert.ToString(reader["PONO"]);
//SeaiBs.GOODSSTANDARD = Convert.ToString(reader["GOODSSTANDARD"]);
SeaiBs.KINDPKGS = Convert.ToString(reader["KINDPKGS"]);
SeaiBs.KGS = Convert.ToString(reader["KGS"]);
SeaiBs.CBM = Convert.ToString(reader["CBM"]);
if (Convert.ToString(reader["OPSTATUS"]) == "重箱返场")
{
SeaiBs.BSSTATUS = "回箱";
if (reader["COMPTIME"] != DBNull.Value)
SeaiBs.BSSTATUSTIME = Convert.ToDateTime(reader["COMPTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
}
if (Convert.ToString(reader["OPSTATUS"]) == "海关放行")
{
SeaiBs.BSSTATUS = "放行";
if (reader["COMPTIME"] != DBNull.Value)
SeaiBs.BSSTATUSTIME = Convert.ToDateTime(reader["COMPTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
}
if (Convert.ToString(reader["OPSTATUS"]) == "装船出运")
{
SeaiBs.BSSTATUS = "开船";
if (reader["COMPTIME"] != DBNull.Value)
SeaiBs.BSSTATUSTIME = Convert.ToDateTime(reader["COMPTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
}
SeaiBs.BSSTATUSDESCRIPTION = Convert.ToString(reader["OPSTATUS"]);
if (SeaiBs.BSSTATUS == "")
SeaiBs.BSSTATUSDESCRIPTION = "未收到货物状态信息";
XmlSeaeBsList.Add(SeaiBs);
}
reader.Close();
}
SeaeManifest.SeaeBs = XmlSeaeBsList;
var strSql2 = new StringBuilder();
strSql2.Append("SELECT COUNT(*) ");
strSql2.Append(" FROM OP_SEAE B ");
strSql2.Append(" Where B.ETD>='2019-11-01' and isnull(B.MBLNO,'')<>'' ");
if (COMPANYEMPOLYEE == "")
{
strSql2.Append(" and B.CUSTOMERNAME='" + CUSTOMERNAME + "' ");
}
if (Mblno != "")
{
//if (ispc)
// strSql.Append(" AND D.PONO='" + Mblno + "' ");
//else
strSql2.Append(" AND (B.MBLNO like '%" + Mblno + "%' )");
}
int cnt = 0;
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql2.ToString()))
{
while (reader.Read())
{
cnt = Convert.ToInt32(reader[0]);
}
}
SeaeManifest.SeaeBs = XmlSeaeBsList;
SeaeManifest.RECCOUNT = cnt;
return SeaeManifest;
}
public static SeaeManifest GetSeaeBsData(string LoginName, string LoginPass, string Mobile, string Mblno)
{
SeaeManifest SeaeManifest = new SeaeManifest();
if (LoginName != "qdtaize" || LoginPass != "EBBE3242-D49E-4398-BBFE-0133CA655EB5")
{
SeaeManifest.ERROMSG = "账号密码不正确";
return SeaeManifest;
}
if (Mblno == "")
{
SeaeManifest.ERROMSG = "提单号号不能为空";
return SeaeManifest;
}
T_ALL_DA T_ALL_DA = new T_ALL_DA();
var CUSTOMERNAME = "";
var COMPANYEMPOLYEE = T_ALL_DA.GetStrSQL("GID", "SELECT top 1 GID FROM user_action Where ACTIONID='4B19971E-FA7F-4528-89F3-4F740CE3D8D5' AND USERID IN (SELECT USERID FROM user_baseinfo WHERE MOBILE='" + Mobile + "' ) ");
if (COMPANYEMPOLYEE == "")
{
// SeaiManifest.ERROMSG = "查询不到此联系人的相关信息";
// return SeaiManifest;
CUSTOMERNAME = T_ALL_DA.GetStrSQL("SHOWNAME", "SELECT top 1 SHORTNAME SHOWNAME FROM info_client Where GID IN (SELECT LINKID FROM info_client_contact WHERE MOBILE='" + Mobile + "' AND ISNULL(ISSTOP,0)=0 ) ");
if (CUSTOMERNAME == "")
{
SeaeManifest.ERROMSG = "查询不到此联系人的相关信息";
return SeaeManifest;
}
}
var ispc = false;
if (Mblno != "")
{
// var MBLNOSTR = T_ALL_DA.GetStrSQL("MBLNO", "SELECT TOP 1 MBLNO FROM OP_SEAE B WHERE (B.MBLNO like '%" + Mblno + "%' ) AND B.CUSTOMERNAME='" + CUSTOMERNAME + "' ");
var MBLNOSTR = T_ALL_DA.GetStrSQL("MBLNO", "SELECT TOP 1 MBLNO FROM OP_SEAE B WHERE (B.MBLNO like '%" + Mblno + "%' ) ");
if (MBLNOSTR == "")
{
SeaeManifest.ERROMSG = "查询不到相关业务信息";
return SeaeManifest;
}
else
{
if (MBLNOSTR != Mblno) ispc = true;
}
}
var strSql = new StringBuilder();
strSql.Append("SELECT TOP 1 B.BSNO,B.MBLNO,(SELECT DESCRIPTION FROM info_client WHERE SHORTNAME=B.CUSTOMERNAME) CUSTOMERNAME,B.VESSEL,B.VOYNO,B.CARRIER,B.CNTRTOTAL,B.ETD,B.ETA, ");
strSql.Append(" B.BLFRT,B.DESTINATION,B.ISSUETYPE,B.YARD,B.YARDATTN,B.YARDTEL,B.CLOSEDOCDATE,B.CLOSINGDATE,B.OP,B.CUSTSERVICE,B.SHIPAGENCY,B.PKGS,B.KGS,B.CBM,B.KINDPKGS ");
strSql.Append(",(SELECT TOP 1 OFFICEPHONE FROM user_baseinfo WHERE USERID IN (SELECT GID FROM [USER] WHERE SHOWNAME=B.OP)) as OPTEL");
strSql.Append(",(SELECT TOP 1 OFFICEPHONE FROM user_baseinfo WHERE USERID IN (SELECT GID FROM [USER] WHERE SHOWNAME=B.CUSTSERVICE)) as CUSTSERVICETEL");
strSql.Append(",(SELECT TOP 1 STATUS FROM OP_STATUS WHERE ISNULL(ISOPEN,0)=1 and BSNO=B.BSNO ORDER BY COMPTIME DESC,INPUTTIME DESC) as OPSTATUS");
strSql.Append(",(SELECT TOP 1 COMPTIME FROM OP_STATUS WHERE ISNULL(ISOPEN,0)=1 and BSNO=B.BSNO ORDER BY COMPTIME DESC,INPUTTIME DESC) as COMPTIME");
strSql.Append(" FROM OP_SEAE B ");
strSql.Append(" Where B.MBLNO='" + Mblno + "' ");
if (COMPANYEMPOLYEE == "")
{
strSql.Append(" and B.CUSTOMERNAME='" + CUSTOMERNAME + "' ");
}
Database db = DatabaseFactory.CreateDatabase();
var XmlSeaeBsList = new List<XmlSeaeBs>();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
XmlSeaeBs SeaiBs = new XmlSeaeBs();
SeaiBs.MBLNO = Convert.ToString(reader["MBLNO"]);
SeaiBs.VESSEL = Convert.ToString(reader["VESSEL"]);
SeaiBs.VOYNO = Convert.ToString(reader["VOYNO"]);
if (reader["ETD"] != DBNull.Value)
SeaiBs.ETD =Convert.ToDateTime(reader["ETD"]).ToString("yyyy-MM-dd");
else
SeaiBs.ETD = " ";
if (reader["ETA"] != DBNull.Value)
SeaiBs.ETA = Convert.ToDateTime(reader["ETA"]).ToString("yyyy-MM-dd");
else
SeaiBs.ETA = " ";
if (SeaiBs.ETD == "1900-01-01") SeaiBs.ETD = "";
if (SeaiBs.ETA == "1900-01-01") SeaiBs.ETA = "";
SeaiBs.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
SeaiBs.CARRIER = Convert.ToString(reader["CARRIER"]);
SeaiBs.CNTRTOTAL = Convert.ToString(reader["CNTRTOTAL"]);
SeaiBs.BLFRT = Convert.ToString(reader["BLFRT"]);
SeaiBs.DESTINATION = Convert.ToString(reader["DESTINATION"]);
SeaiBs.ISSUETYPE = Convert.ToString(reader["ISSUETYPE"]);
SeaiBs.YARD = Convert.ToString(reader["YARD"]);
SeaiBs.YARDATTN = Convert.ToString(reader["YARDATTN"]) + '-' + Convert.ToString(reader["YARDTEL"]);
if (reader["CLOSINGDATE"] != DBNull.Value)
SeaiBs.CLOSINGDATE = Convert.ToDateTime(reader["CLOSINGDATE"]).ToString("yyyy-MM-dd HH:mm:ss");
if (reader["CLOSEDOCDATE"] != DBNull.Value)
SeaiBs.CLOSEDOCDATE = Convert.ToDateTime(reader["CLOSEDOCDATE"]).ToString("yyyy-MM-dd HH:mm:ss");
SeaiBs.OPATTN = Convert.ToString(reader["OP"]) + '-' + Convert.ToString(reader["OPTEL"]);
SeaiBs.CUSTSERVICE = Convert.ToString(reader["CUSTSERVICE"]);
SeaiBs.CUSTSERVICETEL =Convert.ToString(reader["CUSTSERVICETEL"]);
SeaiBs.SHIPAGENCY = Convert.ToString(reader["SHIPAGENCY"]);
SeaiBs.PKGS = Convert.ToString(reader["PKGS"]);
//SeaiBs.PICINO = Convert.ToString(reader["PICINO"]);
//SeaiBs.PONO = Convert.ToString(reader["PONO"]);
//SeaiBs.GOODSSTANDARD = Convert.ToString(reader["GOODSSTANDARD"]);
SeaiBs.KINDPKGS = Convert.ToString(reader["KINDPKGS"]);
SeaiBs.KGS = Convert.ToString(reader["KGS"]);
SeaiBs.CBM = Convert.ToString(reader["CBM"]);
if (Convert.ToString(reader["OPSTATUS"]) == "重箱返场")
{
SeaiBs.BSSTATUS = "回箱";
if (reader["COMPTIME"] != DBNull.Value)
SeaiBs.BSSTATUSTIME = Convert.ToDateTime(reader["COMPTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
}
if (Convert.ToString(reader["OPSTATUS"]) == "海关放行")
{
SeaiBs.BSSTATUS = "放行";
if (reader["COMPTIME"] != DBNull.Value)
SeaiBs.BSSTATUSTIME = Convert.ToDateTime(reader["COMPTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
}
if (Convert.ToString(reader["OPSTATUS"]) == "装船出运")
{
SeaiBs.BSSTATUS = "开船";
if (reader["COMPTIME"] != DBNull.Value)
SeaiBs.BSSTATUSTIME = Convert.ToDateTime(reader["COMPTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
}
SeaiBs.BSSTATUSDESCRIPTION = Convert.ToString(reader["OPSTATUS"]);
if (SeaiBs.BSSTATUS == "")
SeaiBs.BSSTATUSDESCRIPTION = "未收到货物状态信息";
var BSSTATUSList = new List<XmlSeaiBsStatus>();
var strStatusSql = new StringBuilder();
strStatusSql.Append("SELECT STATUS,COMPTIME FROM op_status ");
strStatusSql.Append(" WHERE ISNULL(ISOPEN,0)=1 and BSNO='" + Convert.ToString(reader["BSNO"]) + "' ORDER BY COMPTIME ");
using (IDataReader Statusreader = db.ExecuteReader(CommandType.Text, strStatusSql.ToString()))
{
while (Statusreader.Read())
{
if (Convert.ToString(Statusreader["STATUS"]) == "重箱返场")
{
XmlSeaiBsStatus BSSTATUS = new XmlSeaiBsStatus();
BSSTATUS.BSSTATUS ="回箱";
if (Statusreader["COMPTIME"] != DBNull.Value)
BSSTATUS.BSSTATUSTIME = Convert.ToDateTime(Statusreader["COMPTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
BSSTATUS.BSSTATUSDESCRIPTION = Convert.ToString(Statusreader["STATUS"]);
BSSTATUSList.Add(BSSTATUS);
}
if (Convert.ToString(Statusreader["STATUS"]) == "海关放行")
{
XmlSeaiBsStatus BSSTATUS = new XmlSeaiBsStatus();
BSSTATUS.BSSTATUS = "放行";
if (Statusreader["COMPTIME"] != DBNull.Value)
BSSTATUS.BSSTATUSTIME = Convert.ToDateTime(Statusreader["COMPTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
BSSTATUS.BSSTATUSDESCRIPTION = Convert.ToString(Statusreader["STATUS"]);
BSSTATUSList.Add(BSSTATUS);
}
if (Convert.ToString(Statusreader["STATUS"]) == "装船出运")
{
XmlSeaiBsStatus BSSTATUS = new XmlSeaiBsStatus();
BSSTATUS.BSSTATUS = "开船";
if (Statusreader["COMPTIME"] != DBNull.Value)
BSSTATUS.BSSTATUSTIME = Convert.ToDateTime(Statusreader["COMPTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
BSSTATUS.BSSTATUSDESCRIPTION = Convert.ToString(Statusreader["STATUS"]);
BSSTATUSList.Add(BSSTATUS);
}
}
Statusreader.Close();
}
SeaiBs.BSSTATUSLIST = BSSTATUSList;
XmlSeaeBsList.Add(SeaiBs);
}
reader.Close();
}
SeaeManifest.SeaeBs = XmlSeaeBsList;
return SeaeManifest;
}
#endregion
#region 进口数据
public static SeaiManifest GetSeaiBsDataList(string LoginName, string LoginPass, string Mobile, string Mblno, int start, int limit)
{
SeaiManifest SeaiManifest = new SeaiManifest();
if (LoginName != "qdtaize" || LoginPass != "EBBE3242-D49E-4398-BBFE-0133CA655EB5")
{
SeaiManifest.ERROMSG = "账号密码不正确";
return SeaiManifest;
}
T_ALL_DA T_ALL_DA = new T_ALL_DA();
var CUSTOMERNAME = "";
var COMPANYEMPOLYEE = T_ALL_DA.GetStrSQL("GID", "SELECT top 1 GID FROM user_action Where ACTIONID='4B19971E-FA7F-4528-89F3-4F740CE3D8D5' AND USERID IN (SELECT USERID FROM user_baseinfo WHERE MOBILE='" + Mobile + "' ) ");
if (COMPANYEMPOLYEE == "")
{
// SeaiManifest.ERROMSG = "查询不到此联系人的相关信息";
// return SeaiManifest;
CUSTOMERNAME = T_ALL_DA.GetStrSQL("SHOWNAME", "SELECT top 1 SHORTNAME SHOWNAME FROM info_client Where GID IN (SELECT LINKID FROM info_client_contact WHERE MOBILE='" + Mobile + "' AND ISNULL(ISSTOP,0)=0 ) ");
if (CUSTOMERNAME == "")
{
CUSTOMERNAME = "演示公司";
// SeaiManifest.ERROMSG = "查询不到此联系人的相关信息";
// return SeaiManifest;
}
}
var ispc = false;
if (Mblno != "")
{
var MBLNOSTR = T_ALL_DA.GetStrSQL("MBLNO", "SELECT TOP 1 MBLNO FROM OP_SEAI B LEFT JOIN op_apply_detail D ON (D.BSNO=B.BSNO) WHERE (B.MBLNO like '%" + Mblno + "%' OR D.PONO like '%" + Mblno + "%' OR D.GOODSNAME LIKE '%"+Mblno+"%' ) ");
if (MBLNOSTR == "")
{
SeaiManifest.ERROMSG = "查询不到相关业务信息";
return SeaiManifest;
}
else
{
if (MBLNOSTR != Mblno) ispc = true;
}
}
var strSql = new StringBuilder();
strSql.Append(@"SELECT * from (SELECT row_number() over (");
strSql.Append(" order by B.ETD desc");
strSql.Append(@") as num , ");
strSql.Append(" B.BSNO,B.MBLNO,(SELECT DESCRIPTION FROM info_client WHERE SHORTNAME=B.CUSTOMERNAME) CUSTOMERNAME,B.VESSEL,B.VOYNO,B.ETD,B.ETA,D.GOODSCODE,D.GOODSNAME,D.GOODSNUM PKGS,D.PICINO,D.PONO,D.GOODSSTANDARD,D.KINDPKGS,isnull(D.KGS,0) KGS ");
strSql.Append(",(SELECT TOP 1 STATUS FROM OP_STATUS WHERE STATUS in ('已到港','海关放行','商检放行') and BSNO=B.BSNO ORDER BY COMPTIME DESC,INPUTTIME DESC) as OPSTATUS");
strSql.Append(",(SELECT TOP 1 COMPTIME FROM OP_STATUS WHERE STATUS in ('已到港','海关放行','商检放行') and BSNO=B.BSNO ORDER BY COMPTIME DESC,INPUTTIME DESC) as COMPTIME");
strSql.Append(",(SELECT TOP 1 COMPTIME FROM OP_STATUS WHERE STATUS='海关放行' and BSNO=B.BSNO ORDER BY COMPTIME DESC,INPUTTIME DESC) as CUSTOMTIME");
strSql.Append(",(select TOP 1 WMSDATE from wms_in i left join wms w on (w.ASSOCIATEDNO=i.ASSOCIATEDNO) where i.blno=B.MBLNO and i.CONTRACTNO=D.PONO and w.customername=B.CUSTOMERNAME ) AS WMSDATE");
strSql.Append(",(select TOP 1 w.STORAGENAME from wms_in i left join wms w on (w.ASSOCIATEDNO=i.ASSOCIATEDNO) where i.blno=B.MBLNO and i.CONTRACTNO=D.PONO and w.customername=B.CUSTOMERNAME ) AS STORAGENAME");
strSql.Append(",CASE WHEN ISNULL(d.PONO,'')='' THEN 0 ELSE ISNULL((select SUM(isnull(GOODSPFSL_OUT,0)) from VW_WMS_DETAIL where CONTRACTNO=d.PONO ),0) END AS GOODSSTOCK");
strSql.Append(" FROM op_apply_detail d left join OP_SEAI B ON (B.BSNO=D.BSNO)");
strSql.Append(" Where B.ETD>='2019-11-01' ");
if (COMPANYEMPOLYEE == "")
{
strSql.Append(" and B.CUSTOMERNAME='" + CUSTOMERNAME + "' ");
}
if (Mblno != "") {
//if (ispc)
// strSql.Append(" AND D.PONO='" + Mblno + "' ");
//else
strSql.Append(" AND (B.MBLNO like '%" + Mblno + "%' or D.PONO like '%" + Mblno + "%' or D.GOODSNAME like '%"+Mblno+"%' )");
}
strSql.Append(@")as t ");
strSql.Append(string.Format("where t.num>{0} and t.num<={1} order by t.num ", start, start + limit));
Database db = DatabaseFactory.CreateDatabase();
var XmlSeaiBsList = new List<XmlSeaiBs>();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
XmlSeaiBs SeaiBs = new XmlSeaiBs();
SeaiBs.MBLNO = Convert.ToString(reader["MBLNO"]);
SeaiBs.VESSEL = Convert.ToString(reader["VESSEL"]);
SeaiBs.VOYNO = Convert.ToString(reader["VOYNO"]);
if (reader["ETA"] != DBNull.Value)
SeaiBs.ETD = Convert.ToDateTime(reader["ETA"]).ToString("yyyy-MM-dd");
else
SeaiBs.ETD = " ";
if (reader["ETD"] != DBNull.Value)
SeaiBs.ETA = Convert.ToDateTime(reader["ETD"]).ToString("yyyy-MM-dd");
else
SeaiBs.ETA = " ";
if (SeaiBs.ETD == "1900-01-01") SeaiBs.ETD = "";
SeaiBs.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
SeaiBs.GOODSCODE = Convert.ToString(reader["GOODSCODE"]);
SeaiBs.GOODSNAME = Convert.ToString(reader["GOODSNAME"]);
SeaiBs.PKGS = Convert.ToString(reader["PKGS"]);
SeaiBs.PICINO = Convert.ToString(reader["PICINO"]);
SeaiBs.PONO = Convert.ToString(reader["PONO"]);
SeaiBs.GOODSSTANDARD = Convert.ToString(reader["GOODSSTANDARD"]);
SeaiBs.KINDPKGS = Convert.ToString(reader["KINDPKGS"]);
SeaiBs.KGS = (Convert.ToDecimal(reader["KGS"]) - Convert.ToDecimal(reader["GOODSSTOCK"])).ToString();
SeaiBs.BSSTATUS = Convert.ToString(reader["OPSTATUS"]);
if (reader["COMPTIME"] != DBNull.Value)
SeaiBs.BSSTATUSTIME = Convert.ToDateTime(reader["COMPTIME"]).ToString("yyyy-MM-dd") + " 00:00:00";
SeaiBs.STORAGENAME = Convert.ToString(reader["STORAGENAME"]);
if (reader["CUSTOMTIME"] != DBNull.Value)
SeaiBs.CUSTOMTIME = Convert.ToDateTime(reader["CUSTOMTIME"]).ToString("yyyy-MM-dd") + " 00:00:00";
if (reader["WMSDATE"] != DBNull.Value)
SeaiBs.WMSDATE = Convert.ToDateTime(reader["WMSDATE"]).ToString("yyyy-MM-dd") + " 00:00:00";
if (SeaiBs.BSSTATUS=="")
SeaiBs.BSSTATUSDESCRIPTION = "未收到货物到港信息";
if (SeaiBs.BSSTATUS == "已到港")
{
SeaiBs.BSSTATUSDESCRIPTION = "货物已到港";
if (reader["CUSTOMTIME"] != DBNull.Value) {
SeaiBs.BSSTATUS = "海关放行";
SeaiBs.BSSTATUSDESCRIPTION = "货物已海关放行";
SeaiBs.BSSTATUSTIME = Convert.ToDateTime(reader["CUSTOMTIME"]).ToString("yyyy-MM-dd") + " 00:00:00";
}
}
if (SeaiBs.BSSTATUS == "海关放行")
SeaiBs.BSSTATUSDESCRIPTION = "货物已海关放行";
if (SeaiBs.BSSTATUS == "商检放行")
{
SeaiBs.BSSTATUSDESCRIPTION = "货物已商检放行";
if (reader["COMPTIME"] != DBNull.Value)
SeaiBs.BSSTATUSTIME = Convert.ToDateTime(reader["COMPTIME"]).ToString("yyyy-MM-dd")+" 00:00:00";
}
//if (SeaiBs.BSSTATUS == "已入库")
// SeaiBs.BSSTATUSDESCRIPTION = "货物已入" + Convert.ToString(reader["STORAGENAME"]) + "库";
//if (SeaiBs.BSSTATUS == "已出库")
// SeaiBs.BSSTATUSDESCRIPTION = "货物已出库";
var strwmsStatusSql = new StringBuilder();
strwmsStatusSql.Append("SELECT GOODSRKSL,STORAGENAME,WMSDATE FROM VW_WMS_DETAIL_NULL ");
strwmsStatusSql.Append(" WHERE BLNO='" + SeaiBs.MBLNO + "' and CONTRACTNO='" + SeaiBs.PONO + "' ORDER BY WMSDATE ");
using (IDataReader Statusreader = db.ExecuteReader(CommandType.Text, strwmsStatusSql.ToString()))
{
while (Statusreader.Read())
{
if (Statusreader["WMSDATE"] != DBNull.Value)
{
SeaiBs.BSSTATUS = "已入库";
SeaiBs.BSSTATUSTIME = Convert.ToDateTime(Statusreader["WMSDATE"]).ToString("yyyy-MM-dd HH:mm:ss");
SeaiBs.BSSTATUSDESCRIPTION = "货物已入" + Convert.ToString(Statusreader["STORAGENAME"]) + "库";
}
}
Statusreader.Close();
}
var strwmsoutStatusSql = new StringBuilder();
strwmsoutStatusSql.Append("SELECT SUM(GOODSPFSL_OUT) GOODSPFSL_OUT,MAX(DODATE_OUT) AS DODATE_OUT FROM VW_WMS_DETAIL ");
strwmsoutStatusSql.Append(" WHERE BLNO='" + SeaiBs.MBLNO + "' and CONTRACTNO='" + SeaiBs.PONO + "' ORDER BY DODATE_OUT DESC");
using (IDataReader Statusreader = db.ExecuteReader(CommandType.Text, strwmsoutStatusSql.ToString()))
{
while (Statusreader.Read())
{
if (Statusreader["DODATE_OUT"] != DBNull.Value)
{
SeaiBs.BSSTATUS = "已出库";
if (Statusreader["DODATE_OUT"] != DBNull.Value)
{
SeaiBs.BSSTATUSTIME = Convert.ToDateTime(Statusreader["DODATE_OUT"]).ToString("yyyy-MM-dd HH:mm:ss");
}
SeaiBs.BSSTATUSDESCRIPTION = "已提货" + Math.Round(Convert.ToDecimal(Statusreader["GOODSPFSL_OUT"]), 3, MidpointRounding.AwayFromZero) + "吨。";
}
}
Statusreader.Close();
}
XmlSeaiBsList.Add(SeaiBs);
}
reader.Close();
}
SeaiManifest.SeaiBs = XmlSeaiBsList;
var strSql2 = new StringBuilder();
strSql2.Append("SELECT COUNT(B.BSNO) ");
strSql2.Append(" FROM op_apply_detail d left join OP_SEAI B ON (B.BSNO=D.BSNO) ");
strSql2.Append(" Where B.ETD>='2019-11-01' ");
if (COMPANYEMPOLYEE == "")
{
strSql2.Append(" and B.CUSTOMERNAME='" + CUSTOMERNAME + "' ");
}
if (Mblno != "")
{
//if (ispc)
// strSql.Append(" AND D.PONO='" + Mblno + "' ");
//else
strSql2.Append(" AND (B.MBLNO like '%" + Mblno + "%' or D.PONO like '%" + Mblno + "%' or D.GOODSNAME like '%" + Mblno + "%' )");
}
int cnt = 0;
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql2.ToString()))
{
while (reader.Read())
{
cnt = Convert.ToInt32(reader[0]);
}
}
SeaiManifest.RECCOUNT = cnt;
return SeaiManifest;
}
public static SeaiManifest GetSeaiBsData(string LoginName, string LoginPass, string Mobile, string Mblno)
{
SeaiManifest SeaiManifest = new SeaiManifest();
if (LoginName != "qdtaize" || LoginPass != "EBBE3242-D49E-4398-BBFE-0133CA655EB5")
{
SeaiManifest.ERROMSG = "账号密码不正确";
return SeaiManifest;
}
if (Mblno == "")
{
SeaiManifest.ERROMSG = "PO号不能为空";
return SeaiManifest;
}
T_ALL_DA T_ALL_DA = new T_ALL_DA();
var CUSTOMERNAME = "";
var COMPANYEMPOLYEE= T_ALL_DA.GetStrSQL("GID", "SELECT top 1 GID FROM user_action Where ACTIONID='4B19971E-FA7F-4528-89F3-4F740CE3D8D5' AND USERID IN (SELECT USERID FROM user_baseinfo WHERE MOBILE='" + Mobile + "' ) ");
if (COMPANYEMPOLYEE == "")
{
// SeaiManifest.ERROMSG = "查询不到此联系人的相关信息";
// return SeaiManifest;
CUSTOMERNAME = T_ALL_DA.GetStrSQL("SHOWNAME", "SELECT top 1 SHORTNAME SHOWNAME FROM info_client Where GID IN (SELECT LINKID FROM info_client_contact WHERE MOBILE='" + Mobile + "' AND ISNULL(ISSTOP,0)=0 ) ");
if (CUSTOMERNAME == "")
{
CUSTOMERNAME = "演示公司";
//SeaiManifest.ERROMSG = "查询不到此联系人的相关信息";
//return SeaiManifest;
}
}
var ispc = false;
var MBLNOSTR = "";
if (Mblno != "")
{
MBLNOSTR = T_ALL_DA.GetStrSQL("MBLNO", "SELECT TOP 1 MBLNO FROM OP_SEAI B LEFT JOIN op_apply_detail D ON (D.BSNO=B.BSNO) WHERE D.PONO='" + Mblno + "' ");
if (MBLNOSTR == "")
{
SeaiManifest.ERROMSG = "查询不到相关业务信息";
return SeaiManifest;
}
else
{
if (MBLNOSTR != Mblno) ispc = true;
}
}
var strSql = new StringBuilder();
strSql.Append("SELECT TOP 1 B.BSNO,B.MBLNO,(SELECT DESCRIPTION FROM info_client WHERE SHORTNAME=B.CUSTOMERNAME) CUSTOMERNAME,B.VESSEL,B.VOYNO,B.ETD,B.ETA,D.GOODSCODE,D.GOODSNAME,D.GOODSNUM PKGS,D.PICINO,D.PONO,D.GOODSSTANDARD,D.KINDPKGS,isnull(D.KGS,0) KGS ");
strSql.Append(",(SELECT TOP 1 STATUS FROM OP_STATUS WHERE STATUS in ('已到港','海关放行','商检放行') and BSNO=B.BSNO ORDER BY COMPTIME DESC,INPUTTIME DESC) as OPSTATUS");
strSql.Append(",(SELECT TOP 1 COMPTIME FROM OP_STATUS WHERE STATUS in ('已到港','海关放行','商检放行') and BSNO=B.BSNO ORDER BY COMPTIME DESC,INPUTTIME DESC) as COMPTIME");
strSql.Append(",(SELECT TOP 1 COMPTIME FROM OP_STATUS WHERE STATUS='海关放行' and BSNO=B.BSNO ORDER BY COMPTIME DESC,INPUTTIME DESC) as CUSTOMTIME");
strSql.Append(",(select TOP 1 w.STORAGENAME from wms_in i left join wms w on (w.ASSOCIATEDNO=i.ASSOCIATEDNO) where i.blno=B.MBLNO and i.CONTRACTNO=D.PONO and w.customername=B.CUSTOMERNAME ) AS STORAGENAME");
// strSql.Append(",ISNULL((select SUM(isnull(GOODSPFSL_OUT,0)) from VW_WMS_DETAIL where CONTRACTNO=d.PONO ),0) AS GOODSSTOCK");
strSql.Append(",CASE WHEN ISNULL(d.PONO,'')='' THEN 0 ELSE ISNULL((select SUM(isnull(GOODSPFSL_OUT,0)) from VW_WMS_DETAIL where CONTRACTNO=d.PONO ),0) END AS GOODSSTOCK");
strSql.Append(" FROM op_apply_detail d left join OP_SEAI B ON (B.BSNO=D.BSNO)");
strSql.Append(" Where D.PONO='" + Mblno + "' ");
if (COMPANYEMPOLYEE == "")
{
strSql.Append(" AND B.CUSTOMERNAME='" + CUSTOMERNAME + "' ");
}
Database db = DatabaseFactory.CreateDatabase();
var XmlSeaiBsList = new List<XmlSeaiBs>();
using (IDataReader reader = db.ExecuteReader(CommandType.Text, strSql.ToString()))
{
while (reader.Read())
{
XmlSeaiBs SeaiBs = new XmlSeaiBs();
SeaiBs.MBLNO = Convert.ToString(reader["MBLNO"]);
SeaiBs.VESSEL = Convert.ToString(reader["VESSEL"]);
SeaiBs.VOYNO = Convert.ToString(reader["VOYNO"]);
if (reader["ETA"] != DBNull.Value)
SeaiBs.ETD = Convert.ToDateTime(reader["ETA"]).ToString("yyyy-MM-dd");
else
SeaiBs.ETD = " ";
if (reader["ETD"] != DBNull.Value)
SeaiBs.ETA = Convert.ToDateTime(reader["ETD"]).ToString("yyyy-MM-dd");
else
SeaiBs.ETA = " ";
if (SeaiBs.ETD == "1900-01-01") SeaiBs.ETD = "";
SeaiBs.CUSTOMERNAME = Convert.ToString(reader["CUSTOMERNAME"]);
SeaiBs.GOODSCODE = Convert.ToString(reader["GOODSCODE"]);
SeaiBs.GOODSNAME = Convert.ToString(reader["GOODSNAME"]);
SeaiBs.PKGS = Convert.ToString(reader["PKGS"]);
SeaiBs.PICINO = Convert.ToString(reader["PICINO"]);
SeaiBs.PONO = Convert.ToString(reader["PONO"]);
SeaiBs.GOODSSTANDARD = Convert.ToString(reader["GOODSSTANDARD"]);
SeaiBs.KINDPKGS = Convert.ToString(reader["KINDPKGS"]);
SeaiBs.KGS = (Convert.ToDecimal(reader["KGS"]) - Convert.ToDecimal(reader["GOODSSTOCK"])).ToString();
SeaiBs.BSSTATUS = Convert.ToString(reader["OPSTATUS"]);
if (reader["COMPTIME"] != DBNull.Value)
SeaiBs.BSSTATUSTIME = Convert.ToDateTime(reader["COMPTIME"]).ToString("yyyy-MM-dd") + " 00:00:00";
SeaiBs.STORAGENAME = Convert.ToString(reader["STORAGENAME"]);
if (SeaiBs.BSSTATUS == "")
SeaiBs.BSSTATUSDESCRIPTION = "未收到货物到港信息";
if (SeaiBs.BSSTATUS == "已到港")
{
SeaiBs.BSSTATUSDESCRIPTION = "货物已到港";
if (reader["CUSTOMTIME"] != DBNull.Value)
{
SeaiBs.BSSTATUS = "海关放行";
SeaiBs.BSSTATUSDESCRIPTION = "货物已海关放行";
SeaiBs.BSSTATUSTIME = Convert.ToDateTime(reader["CUSTOMTIME"]).ToString("yyyy-MM-dd") + " 00:00:00";
}
}
if (SeaiBs.BSSTATUS == "海关放行")
SeaiBs.BSSTATUSDESCRIPTION = "货物已海关放行";
if (SeaiBs.BSSTATUS == "商检放行")
SeaiBs.BSSTATUSDESCRIPTION = "货物已商检放行";
var strwmsStatusSql = new StringBuilder();
strwmsStatusSql.Append("SELECT GOODSRKSL,STORAGENAME,WMSDATE FROM VW_WMS_DETAIL_NULL ");
strwmsStatusSql.Append(" WHERE BLNO='" + SeaiBs.MBLNO + "' and CONTRACTNO='" + SeaiBs.PONO + "' ORDER BY WMSDATE ");
using (IDataReader Statusreader = db.ExecuteReader(CommandType.Text, strwmsStatusSql.ToString()))
{
while (Statusreader.Read())
{
if (Statusreader["WMSDATE"] != DBNull.Value)
{
SeaiBs.BSSTATUS = "已入库";
SeaiBs.BSSTATUSTIME = Convert.ToDateTime(Statusreader["WMSDATE"]).ToString("yyyy-MM-dd HH:mm:ss");
SeaiBs.BSSTATUSDESCRIPTION = "货物已入" + Convert.ToString(Statusreader["STORAGENAME"]) + "库";
}
}
Statusreader.Close();
}
var strwmsoutStatusSql = new StringBuilder();
strwmsoutStatusSql.Append("SELECT SUM(GOODSPFSL_OUT) GOODSPFSL_OUT,MAX(DODATE_OUT) AS DODATE_OUT FROM VW_WMS_DETAIL ");
strwmsoutStatusSql.Append(" WHERE BLNO='" + SeaiBs.MBLNO + "' and CONTRACTNO='" + SeaiBs.PONO + "' ORDER BY DODATE_OUT DESC");
using (IDataReader Statusreader = db.ExecuteReader(CommandType.Text, strwmsoutStatusSql.ToString()))
{
while (Statusreader.Read())
{
if (Statusreader["DODATE_OUT"] != DBNull.Value)
{
SeaiBs.BSSTATUS = "已出库";
SeaiBs.BSSTATUSTIME = Convert.ToDateTime(Statusreader["DODATE_OUT"]).ToString("yyyy-MM-dd HH:mm:ss");
SeaiBs.BSSTATUSDESCRIPTION = "已提货" + Math.Round(Convert.ToDecimal(Statusreader["GOODSPFSL_OUT"]), 3, MidpointRounding.AwayFromZero) + "吨。";
}
}
Statusreader.Close();
}
//if (SeaiBs.BSSTATUS == "已入库")
// SeaiBs.BSSTATUSDESCRIPTION = "货物已入" + Convert.ToString(reader["STORAGENAME"])+"库";
//if (SeaiBs.BSSTATUS == "已出库")
// SeaiBs.BSSTATUSDESCRIPTION = "货物已出库";
var BSSTATUSList = new List<XmlSeaiBsStatus>();
var strStatusSql = new StringBuilder();
strStatusSql.Append("SELECT STATUS,COMPTIME FROM op_status ");
strStatusSql.Append(" WHERE STATUS in ('已到港','海关放行','商检放行') and BSNO='" + Convert.ToString(reader["BSNO"]) + "' ORDER BY COMPTIME ");
using (IDataReader Statusreader = db.ExecuteReader(CommandType.Text, strStatusSql.ToString()))
{
while (Statusreader.Read())
{
if (Statusreader["COMPTIME"] != DBNull.Value)
{
XmlSeaiBsStatus BSSTATUS = new XmlSeaiBsStatus();
BSSTATUS.BSSTATUS = Convert.ToString(Statusreader["STATUS"]);
BSSTATUS.BSSTATUSTIME = Convert.ToDateTime(Statusreader["COMPTIME"]).ToString("yyyy-MM-dd HH:mm:ss");
if (BSSTATUS.BSSTATUS == "")
BSSTATUS.BSSTATUSDESCRIPTION = "未收到货物到港信息";
if (BSSTATUS.BSSTATUS == "已到港")
BSSTATUS.BSSTATUSDESCRIPTION = "货物已到港";
if (BSSTATUS.BSSTATUS == "海关放行")
BSSTATUS.BSSTATUSDESCRIPTION = "货物已海关放行";
if (BSSTATUS.BSSTATUS == "商检放行")
BSSTATUS.BSSTATUSDESCRIPTION = "货物已商检放行";
//if (BSSTATUS.BSSTATUS == "已入库")
// BSSTATUS.BSSTATUSDESCRIPTION = "货物已入" + Convert.ToString(reader["STORAGENAME"]) + "库";
//if (BSSTATUS.BSSTATUS == "已出库")
// BSSTATUS.BSSTATUSDESCRIPTION = "货物已出库";
BSSTATUSList.Add(BSSTATUS);
}
}
Statusreader.Close();
}
var strwmsStatusSql2 = new StringBuilder();
strwmsStatusSql2.Append("SELECT GOODSRKSL,STORAGENAME,WMSDATE FROM VW_WMS_DETAIL_NULL ");
strwmsStatusSql2.Append(" WHERE BLNO='" + MBLNOSTR + "' and CONTRACTNO='" + Mblno + "' ORDER BY WMSDATE ");
using (IDataReader Statusreader = db.ExecuteReader(CommandType.Text, strwmsStatusSql2.ToString()))
{
while (Statusreader.Read())
{
if (Statusreader["WMSDATE"] != DBNull.Value)
{
XmlSeaiBsStatus BSSTATUS = new XmlSeaiBsStatus();
BSSTATUS.BSSTATUS ="已入库";
BSSTATUS.BSSTATUSTIME = Convert.ToDateTime(Statusreader["WMSDATE"]).ToString("yyyy-MM-dd HH:mm:ss");
BSSTATUS.BSSTATUSDESCRIPTION = "货物已入库,入库吨数" + Math.Round(Convert.ToDecimal(Statusreader["GOODSRKSL"]),3,MidpointRounding.AwayFromZero) + "吨.已入" + Convert.ToString(Statusreader["STORAGENAME"]) + "库";
BSSTATUSList.Add(BSSTATUS);
}
}
Statusreader.Close();
}
var strwmsoutStatusSql2 = new StringBuilder();
strwmsoutStatusSql2.Append("SELECT GOODSSTOCK,STORAGENAME_OUT,GOODSPFSL_OUT,DODATE_OUT,TRUCKNO_OUT FROM VW_WMS_DETAIL ");
strwmsoutStatusSql2.Append(" WHERE BLNO='" + MBLNOSTR + "' and CONTRACTNO='" + Mblno + "' ORDER BY DODATE_OUT ");
using (IDataReader Statusreader = db.ExecuteReader(CommandType.Text, strwmsoutStatusSql2.ToString()))
{
while (Statusreader.Read())
{
if (Statusreader["DODATE_OUT"] != DBNull.Value)
{
XmlSeaiBsStatus BSSTATUS = new XmlSeaiBsStatus();
BSSTATUS.BSSTATUS = "已出库";
BSSTATUS.BSSTATUSTIME = Convert.ToDateTime(Statusreader["DODATE_OUT"]).ToString("yyyy-MM-dd HH:mm:ss");
if (Convert.ToString(Statusreader["TRUCKNO_OUT"])!="")
BSSTATUS.BSSTATUSDESCRIPTION = "车牌号" + Convert.ToString(Statusreader["TRUCKNO_OUT"]) + ",";
BSSTATUS.BSSTATUSDESCRIPTION = BSSTATUS.BSSTATUSDESCRIPTION + "提货" + Math.Round(Convert.ToDecimal(Statusreader["GOODSPFSL_OUT"]), 3, MidpointRounding.AwayFromZero) + "吨。";
if (reader["GOODSSTOCK"] != DBNull.Value)
{
if (Convert.ToDecimal(reader["GOODSSTOCK"]) <= 0) BSSTATUS.BSSTATUSDESCRIPTION = BSSTATUS.BSSTATUSDESCRIPTION + " 已清库";
}
BSSTATUSList.Add(BSSTATUS);
}
}
Statusreader.Close();
}
SeaiBs.BSSTATUSLIST = BSSTATUSList;
XmlSeaiBsList.Add(SeaiBs);
}
reader.Close();
}
SeaiManifest.SeaiBs = XmlSeaiBsList;
return SeaiManifest;
}
#endregion
public static string Xml2String(XmlDocument XDoc)
{
MemoryStream Stream= new MemoryStream();
XmlTextWriter Writer = new XmlTextWriter(Stream, null);
Writer.Formatting = Formatting.Indented;
XDoc.Save(Writer);
StreamReader sr = new StreamReader(Stream,System.Text.Encoding.UTF8);
Stream.Position = 0;
string XmlString = sr.ReadToEnd();
sr.Close();
Stream.Close();
return XmlString;
}
/**/
/// <summary>
/// 压缩文件
/// </summary>
/// <param name="FileToZip">要进行压缩的文件名</param>
/// <param name="ZipedFile">压缩后生成的压缩文件名</param>
/// <returns></returns>
public static bool ZipFile(string FileToZip, string ZipedFile, String Password)
{
////如果文件没有找到，则报错
//if (!File.Exists(FileToZip))
//{
// throw new System.IO.FileNotFoundException("指定要压缩的文件: " + FileToZip + " 不存在!");
//}
////FileStream fs = null;
//FileStream ZipFile = null;
//ZipOutputStream ZipStream = null;
//ZipEntry ZipEntry = null;
//bool res = true;
//try
//{
// ZipFile = File.OpenRead(FileToZip);
// byte[] buffer = new byte[ZipFile.Length];
// ZipFile.Read(buffer, 0, buffer.Length);
// ZipFile.Close();
// ZipFile = File.Create(ZipedFile);
// ZipStream = new ZipOutputStream(ZipFile);
// //ZipStream.Password = Password;
// ZipEntry = new ZipEntry(Path.GetFileName(FileToZip));
// ZipEntry.DateTime = DateTime.Now;
// ZipStream.PutNextEntry(ZipEntry);
// ZipStream.SetLevel(6);
// ZipStream.Write(buffer, 0, buffer.Length);
//}
//catch
//{
// res = false;
//}
//finally
//{
// if (ZipEntry != null)
// {
// ZipEntry = null;
// }
// if (ZipStream != null)
// {
// ZipStream.Finish();
// ZipStream.Close();
// }
// if (ZipFile != null)
// {
// ZipFile.Close();
// ZipFile = null;
// }
// GC.Collect();
// GC.Collect(1);
//}
return true;
}
public static bool ZipFile2(string FileToZip, string ZipedFile ,int CompressionLevel, int BlockSize){
////如果文件没有找到，则报错
//if (! System.IO.File.Exists(FileToZip))
//{
// throw new System.IO.FileNotFoundException("The specified file " + FileToZip + " could not be found. Zipping aborderd");
//}
//System.IO.FileStream StreamToZip = new System.IO.FileStream(FileToZip,System.IO.FileMode.Open , System.IO.FileAccess.Read);
//System.IO.FileStream ZipFile = System.IO.File.Create(ZipedFile);
//ZipOutputStream ZipStream = new ZipOutputStream(ZipFile);
//ZipEntry ZipEntry = new ZipEntry("ZippedFile");
//ZipStream.PutNextEntry(ZipEntry);
//ZipStream.SetLevel(CompressionLevel);
//byte[] buffer = new byte[BlockSize];
//System.Int32 size =StreamToZip.Read(buffer,0,buffer.Length);
//ZipStream.Write(buffer,0,size);
//try
//{
// while (size < StreamToZip.Length)
// {
// int sizeRead =StreamToZip.Read(buffer,0,buffer.Length);
// ZipStream.Write(buffer,0,sizeRead);
// size += sizeRead;
// }
// return true;
//}
//catch(System.Exception ex)
//{
// throw ex;
//}
//ZipStream.Finish();
//ZipStream.Close();
//StreamToZip.Close();
return true;
}
/**/
/// <summary>
/// 压缩文件 和 文件夹
/// </summary>
/// <param name="FileToZip">待压缩的文件或文件夹，全路径格式</param>
/// <param name="ZipedFile">压缩后生成的压缩文件名，全路径格式</param>
/// <returns></returns>
//public static bool Zip(String FileToZip, String ZipedFile, String Password)
//{
// if (Directory.Exists(FileToZip))
// {
// return ZipFileDictory(FileToZip, ZipedFile, Password);
// }
// else if (File.Exists(FileToZip))
// {
// return ZipFile(FileToZip, ZipedFile, Password);
// }
// else
// {
// return false;
// }
//}
}
}
Reference in New Issue View Git Blame Copy Permalink