unit YMS_NBOCEAN_EDI;
interface
uses
Windows, Messages, SysUtils, Classes, DB, ADODB, ybADOQuery, ybDbUtils,
ybEDIBaseClasses, DateUtils, ybConnection, YBDBDesc, ybAppMsgUtils,
StrUtils,Variants, cxSSheet, cxSSTypes, dialogs,
ComObj;
type
TYBNBOCEANEDIBuilder = class(TYBEDIBuilder)
private
FGateInConts: TStrings; // 进门集装箱列表;
FGateOutConts: TStrings; // 出门集装箱列表;
FEDIText: TStrings; // 生成报文;
FEditTypes: TStrings;
FNBOCEANYardName: string; // 报文中堆场代码;
FSendMark: string;
FManHourPriceQuery: TADOQuery;
FEdiTypes: array of string;
FEdiFromService: Boolean;
FType: string;
FStream: TMemoryStream;
//function GetNBOCEANYardName: string;
protected
function GetCustName: string; override;
function GetEdiTypeCount: Integer; override;
function GetEdiTypes(Index: Integer): string; override;
procedure DoAfterSaveToMail; override;
procedure RemindWhenDataHasProblem;override;
function BuildGateInEdi(BeginDate, EndDate: TDateTime; aContNo: string):string;
function BuildGateOutEdi(BeginDate, EndDate: TDateTime; aContNo: string):string;
public
constructor Create; override;
destructor Destroy; override;
function BuildEDIText(AType, ATdh: string; BeginDate, EndDate:TDateTime): string;override;
property SendMark: string read FSendMark write FSendMark;
procedure SaveEdiToFtp(AType, ATdh: string; aBeginDate, aEndDate: TDateTime);
end;
implementation
const
FOOTER_LINE = '99:%d'''; //总行数,包括本行
procedure WriteLog(msg: string);
var
dtime: string;
date:string;
logFile: TextFile;
Filename: string;
begin
//将操作写到日志表中,便于出问题时查找
try
if True then
begin
dtime := FormatDateTime('yyyy-mm-dd hh:mm:ss zzz', Now);
date:= FormatDateTime('yyyy-mm-dd',Now);
Filename := ExtractFilePath(ParamStr(0))+'yblog_EDI\' + date+'Log.txt';
if not DirectoryExists(ExtractFilePath(ParamStr(0))+'yblog_EDI\') then ForceDirectories(ExtractFilePath(ParamStr(0))+'yblog_EDI\');
AssignFile(logFile, Filename);
if not FileExists(Filename) then
begin
Rewrite(logFile);
writeln(logFile, dtime + ':' + msg);
CloseFile(logFile);
end
else
begin
Append(logFile);
if (msg = '') then
writeln(logFile, ' ')
else
writeln(logFile, dtime + ':' + msg);
CloseFile(logFile);
end;
end;
except
end;
end;
function TYBNBOCEANEDIBuilder.BuildGateInEdi(BeginDate, EndDate: TDateTime; aContNo: string): string;
var
lQuery: TADOQuery;
s, lYardCode, lFormat, lJCCCH: string;
begin
FEDIText.Clear;
lQuery := TADOQuery.Create(nil);
try
lYardCode := ybGetSysValue(Connection, 'CY.NBOCEAN EDI堆场代码 ');
if lYardCode = '' then
begin
lYardCode := '';
ybSetSysValue('CY.NBOCEAN EDI堆场代码', lYardCode, Connection);
end;
lJCCCH := ybGetSysValue(Connection, 'CY.NBOCEAN EDI是否添加车号行数信息');
if lJCCCH = '' then
begin
lJCCCH := '0';
ybSetSysValue('CY.NBOCEAN EDI是否添加车号行数信息', lJCCCH, Connection);
end;
lFormat := ybGetSysValue(Connection, 'CY NBOCEAN进出EDI第一行格式');
if lFormat = '' then
begin
lFormat := '0';
ybSetSysValue('CY NBOCEAN进出EDI第一行格式', lFormat, Connection);
end;
lQuery.Connection := Connection;
lQuery.SQL.Text := 'SELECT JCJZX_CM,JCJZX_HC,JCJZX_JZXBH,JCJZX_CC,JCJZX_XX,JCJZX_SBJJD,JCJZX_TDH,JCJZX_JCRQ,JCJZX_CH FROM JCJZX with (nolock)';
lQuery.SQL.Add(' WHERE JCJZX_JCRQ >= :P1 AND JCJZX_JCRQ < :P2 ');
lQuery.SQL.Add(' AND JCJZX_CGS = ''NBOS'' ');
lQuery.SQL.Add('ORDER BY JCJZX_CM, JCJZX_HC DESC');
lQuery.Parameters[0].Value := BeginDate;
lQuery.Parameters[1].Value := EndDate;
lQuery.Open;
if not lQuery.IsEmpty then
begin
if lFormat = '3' then
FEDIText.Add(Format('00:CODECO:GATE-IN REPORT:9:%s:CNTJN:%s''',
[lYardCode, FormatDateTime('yyyymmddhhnn',Now)]))
else
FEDIText.Add(Format('00:CODECO:GATE-IN REPORT:9:%s:NBOCEANNB:%s:CNBLG:CNBLG''',
[lYardCode, FormatDateTime('yyyymmddhhnn',Now)]));
while not lQuery.Eof do
begin
FEDIText.Add(Format('10::%s:%s:NBOCEAN''',
[lQuery.FieldByName('JCJZX_CM').AsString, lQuery.FieldByName('JCJZX_HC').AsString]));
FEDIText.Add(Format('50:%s:%s:E:%s:%s:%s:::%s::''',
[lQuery.FieldByName('JCJZX_JZXBH').AsString,
lQuery.FieldByName('JCJZX_CC').AsString + StringReplace(lQuery.FieldByName('JCJZX_XX').AsString, 'GP', 'DC', [rfReplaceAll]),
'I',
lQuery.FieldByName('JCJZX_SBJJD').AsString,
lQuery.FieldByName('JCJZX_TDH').AsString,
FormatDateTime('yyyymmddhhnn',lQuery.FieldByName('JCJZX_JCRQ').AsDateTime)]));
if lJCCCH = '1' then
FEDIText.Add(Format('52:3:%s::NONE''',
[lQuery.FieldByName('JCJZX_CH').AsString]));
lQuery.Next;
end;
FEDIText.Add(Format('99:%d''',[FEDIText.Count + 1]));
//Result := True;
end;
if FEdiFromService then
begin
if FEDIText.Count = 2 then
FEDIText.Text := '';
WriteLog('FEdiFromService');
FStream.Clear;
FEDIText.SaveToStream(FStream);
EdiFileName := Format('EDI-%s-IN-%s', [lYardCode, FormatDateTime('yyyymmddhhss', Now)]) + '.txt';
end
else
EdiFileName := EdiFilePath + '\' + Format('EDI-%s-IN-%s', [lYardCode, FormatDateTime('yyyymmddhhss', Now)]) + '.txt';
WriteLog('EdiFileName=');
WriteLog(EdiFileName);
FEDIText.SaveToFile(EdiFileName);
finally
lQuery.Free;
end;
end;
function TYBNBOCEANEDIBuilder.BuildGateOutEdi(BeginDate, EndDate: TDateTime;aContNo: string): string;
var
lQuery, lQueryJG, lQueryCy: TADOQuery;
lMNO, s, cc1, cc2, lYardCode, lFormat, lJCCCH, lSpecialFormat, lCCYY, lSFQFCCYY: string;
begin
FEDIText.Clear;
lQuery := TADOQuery.Create(nil);
lQueryJG := TADOQuery.Create(nil);
lQueryCy := TADOQuery.Create(nil);
lMNO := FormatDateTime('yyyymmddhhnn', Now);
try
lQuery.Connection := Connection;
lQueryCy.Connection := Connection;
lSpecialFormat := ybGetSysValue(Connection, 'CY NBOCEAN进出场特殊格式');
if lSpecialFormat = '' then
begin
lSpecialFormat := '0';
ybSetSysValue('CY NBOCEAN进出场特殊格式', lSpecialFormat, Connection, '[0]其他地区格式[1]宁波珉钧使用格式');
end;
lFormat := ybGetSysValue(Connection, 'CY NBOCEAN进出EDI第一行格式');
if lFormat = '' then
begin
lFormat := '0';
ybSetSysValue('CY NBOCEAN进出EDI第一行格式', lFormat, Connection);
end;
lJCCCH := ybGetSysValue(Connection, 'CY.NBOCEAN EDI是否添加车号行数信息');
if lJCCCH = '' then
begin
lJCCCH := '0';
ybSetSysValue('CY.NBOCEAN EDI是否添加车号行数信息', lJCCCH, Connection);
end;
lQuery.SQL.Text := 'SELECT CCJZX_TXYY,CCJZX_CCCD,CCJZX_TDH,CCJZX_CM,CCJZX_HC,JCJZX_ZZ,';
lQuery.SQL.Add('CCJZX_JZXBH,CCJZX_CC,CCJZX_XX,CCJZX_SBJJDH,CCJZX_TDH,CCJZX_CCRQ,CCJZX_CH FROM CCJZX with (nolock)');
lQuery.SQL.Add('JOIN JCJZX ON JCJZX_FLOWID=CCJZX_FLOWID');
lQuery.SQL.Add('WHERE CCJZX_CCRQ >= :P1 AND CCJZX_CCRQ < :P2 ');
lQuery.SQL.Add(' AND CCJZX_CGS = ''NBOS''');
if aContNo <> '' then
lQuery.SQL.Add(' AND CharIndex(CCJZX_JZXBH, ' + QuotedStr(aContNo) + ') > 0');
lQuery.SQL.Add('ORDER BY CCJZX_CM, CCJZX_HC DESC');
lQuery.Parameters[0].Value := BeginDate;
lQuery.Parameters[1].Value := EndDate;
lQuery.Open;
lYardCode := ybGetSysValue(Connection, 'CY.NBOCEAN EDI堆场代码 ');
if lYardCode = '' then
begin
lYardCode := 'ZHD';
ybSetSysValue('CY.NBOCEAN EDI堆场代码', lYardCode, Connection);
end;
lSFQFCCYY := ybGetSysValue(Connection, 'CY.NBOCEAN 是否区分出场原因');
if lSFQFCCYY = '' then
begin
lSFQFCCYY := '0';
ybSetSysValue('CY.NBOCEAN 是否区分出场原因', lSFQFCCYY, Connection); //中储要求
end;
if not lQuery.IsEmpty then
begin
if lFormat = '0' then
FEDIText.Add(Format('00:CODECO:GATE-OUT REPORT:9:%s:CNTJN:%s''', [lYardCode, lMNO]))
else
FEDIText.Add(Format('00:CODECO:GATE-OUT REPORT:9:%s:NBOCEANNB:%s:CNBLG:CNBLG''', [lYardCode, lMNO]));
while not lQuery.Eof do
begin
FEDIText.Add(Format('10::%s:%s:NBOCEAN''',
[lQuery.FieldByName('CCJZX_CM').AsString, lQuery.FieldByName('CCJZX_HC').AsString]));
begin
FEDIText.Add(Format('50:%s:%s:%s:%s:%s:%s::::%s:''',
[lQuery.FieldByName('CCJZX_JZXBH').AsString,
lQuery.FieldByName('CCJZX_CC').AsString + StringReplace(lQuery.FieldByName('CCJZX_XX').AsString, 'GP', 'DC', [rfReplaceAll]),
cc1,
cc2,
lQuery.FieldByName('CCJZX_SBJJDH').AsString,
lQuery.FieldByName('CCJZX_TDH').AsString,
FormatDateTime('yyyymmddhhnn',lQuery.FieldByName('CCJZX_CCRQ').AsDateTime)]));
end;
lQuery.Next;
end;
if lJCCCH = '1' then
FEDIText.Add(Format('52:3:%s::NONE''',[lQuery.FieldByName('CCJZX_CH').AsString]));
lQuery.Next;
end;
if (not lQueryJG.IsEmpty) OR (not lQuery.IsEmpty) then
begin
FEDIText.Add(Format('99:%d''',[FEDIText.Count + 1]));
end;
if FEdiFromService then
begin
if FEDIText.Count = 2 then
FEDIText.Text := '';
FStream.Clear;
FEDIText.SaveToStream(FStream);
EdiFileName := Format('EDI-%s-OUT-%s', [lYardCode, FormatDateTime('yyyymmddhhss', Now)]) + '.txt';
end
else
EdiFileName := EdiFilePath + '\' + Format('EDI-%s-OUT-%s', [lYardCode, FormatDateTime('yyyymmddhhss', Now)]) + '.txt';
FEDIText.SaveToFile(EdiFileName);
finally
lQuery.Free;
lQueryJG.Free;
end;
end;
function TYBNBOCEANEDIBuilder.BuildEDIText(AType, ATdh: string; BeginDate, EndDate:TDateTime): string;
begin
FEDIText.Clear;
FStream := TMemoryStream.Create;
if AType = '进场(新)' then
Result := BuildGateInEDI(BeginDate, EndDate,ATdh)
else if AType = '出场(新)' then
Result := BuildGateOutEDI(BeginDate, EndDate,ATdh)
end;
constructor TYBNBOCEANEDIBuilder.Create;
begin
inherited;
FSendMark := '0';
FEditTypes := TStringList.Create;
FEditTypes.Add('进场(新)');
FEditTypes.Add('出场(新)');
FEDIText := TStringList.Create;
FGateInConts := TStringList.Create;
FGateOutConts := TStringList.Create;
end;
destructor TYBNBOCEANEDIBuilder.Destroy;
begin
FEditTypes.Free;
FreeAndNil(FManHourPriceQuery);
FEDIText.Free;
FGateInConts.Free;
FGateOutConts.Free;
inherited;
end;
function TYBNBOCEANEDIBuilder.GetCustName: string;
begin
Result := 'NBOCEAN';
end;
function TYBNBOCEANEDIBuilder.GetEdiTypeCount: Integer;
begin
Result := FEditTypes.Count;
end;
function TYBNBOCEANEDIBuilder.GetEdiTypes(Index: Integer): string;
begin
Result := FEditTypes.Strings[Index];
end;
procedure TYBNBOCEANEDIBuilder.DoAfterSaveToMail;
var
lQry: TYBADOQuery;
i: Integer;
begin
lQry := TYBADOQuery.Create(nil);
try
lQry.Connection := Connection;
for i := 0 to FGateInConts.Count - 1 do
begin
lQry.SQL.Text :='update JCJZX set JCJZX_EDISendMark = 1 where JCJZX_GUID = ' + QuotedStr(FGateInConts[i]);
lQry.ExecSQL;
end;
FGateInConts.Clear;
for i := 0 to FGateOutConts.Count - 1 do
begin
lQry.SQL.Text :='update CCJZX set CCJZX_EDISendMark = 1 WHERE CCJZX_GUID = ' + QuotedStr(FGateOutConts[i]);
lQry.ExecSQL;
end;
FGateOutConts.Clear;
finally
lQry.Free;
end;
end;
procedure TYBNBOCEANEDIBuilder.SaveEdiToFtp(AType, ATdh: string; aBeginDate,
aEndDate: TDateTime);
var
lStream: TMemoryStream;
lStrLst: TStringList;
lMainconn: TAdoconnection;
lFtpQuery: TYBADOQuery;
FHost, FUsername, FPassword, FDirName, FPort, lSenderCode,FDire: string;
begin
lMainconn := ybgetmainconn(connection);
lStrLst := TStringList.Create;
lStream := TMemoryStream.Create;
lFtpQuery := TYBADOQuery.Create(nil);
try
lFtpQuery.Connection := lMainconn;
lFtpQuery.SQL.Text := 'SELECT * FROM EDIFTPLOG WHERE 1 = 2';
lFtpQuery.Open;
FSendMark:='1';
lStrLst.Text := BuildEDIText(AType, ATdh, aBeginDate, aEndDate);
if lStrLst.Count > 3 then
begin
lSenderCode := ybGetSysValue(Connection, 'CY.NBOCEAN EDI发送方');
if lSenderCode = '' then
begin
lSenderCode := 'XXXX';
ybSetSysValue('CY.NBOCEAN EDI发送方', lSenderCode, Connection);
end;
FHost := ybGetSysValue(Connection, 'CY.NBOCEAN EDI.FTP地址');
if FHost = '' then
begin
FHost := 'www.onto.cn';
ybSetSysValue('CY.NBOCEAN EDI.FTP地址', FHost, Connection);
end;
FUsername := ybGetSysValue(Connection, 'CY.NBOCEAN EDI.FTP用户');
if FUsername = '' then
begin
FUsername := 'ftp_tj_zgwl';
ybSetSysValue('CY.NBOCEAN EDI.FTP用户', FUsername, Connection);
end;
FPassword := ybGetSysValue(Connection, 'CY.NBOCEAN EDI.FTP密码');
if FPassword = '' then
begin
FPassword := 'l0Y3b1dB';
ybSetSysValue('CY.NBOCEAN EDI.FTP密码', FPassword, Connection);
end;
FPort := ybGetSysValue(Connection, 'CY.NBOCEAN EDI.FTP端口');
if FPort = '' then
begin
FPort := '21';
ybSetSysValue('CY.NBOCEAN EDI.FTP端口', FPort, Connection);
end;
FDire := ybGetSysValue(Connection, 'CY.NBOCEAN EDI.FTP目录');
if FDire = '' then
begin
FDire := 'in';
ybSetSysValue('CY.NBOCEAN EDI.FTP目录', FDire, Connection);
end;
lStream.Clear;
lStream.Seek(0, 0);
lStrLst.SaveToStream(lStream);
lFtpQuery.Append;
lFtpQuery.FieldValues['FL_GUID'] := CreateClassID;
lFtpQuery.FieldByName('FL_DZ').AsString := FHost;
lFtpQuery.FieldByName('FL_USERNAME').AsString := FUsername;
lFtpQuery.FieldByName('FL_PASSWORD').AsString := FPassword;
lFtpQuery.FieldByName('FL_DIR').AsString := FDirName;
lFtpQuery.FieldByName('FL_PORT').AsString := FPort;
lFtpQuery.FieldByName('FL_Dir').AsString := FDire;
lFtpQuery.FieldValues['FL_CTIME'] := Now;
lFtpQuery.FieldValues['FL_SCSJ'] := Now;
if AType = '进场(新)' then
lFtpQuery.FieldValues['FL_MSXX'] := 'NBOCEANGateIn'
else if AType = '出场(新)' then
lFtpQuery.FieldValues['FL_MSXX'] := 'NBOCEANGateOut';
if AType = '进场(新)' then
lFtpQuery.FieldValues['FL_WJM'] :=
'NBOCEANGateIn' + FormatDateTime('yyyymmddhhnn', Now) + '.txt'
else if AType = '出场(新)' then
lFtpQuery.FieldValues['FL_WJM'] :=
'NBOCEANGateOut' + FormatDateTime('yyyymmddhhnn', Now) + '.txt'
else
lFtpQuery.FieldValues['FL_WJM'] :=
'NBOCEAN' + FormatDateTime('yyyymmddhhnn', Now) + '.txt';
lFtpQuery.FieldValues['FL_EDIQSSJ'] := aBeginDate;
lFtpQuery.FieldValues['FL_EDIJZSJ'] := aEndDate;
TMemoField(lFtpQuery.FieldByName('FL_WJNR')).LoadFromStream(lStream);
lFtpQuery.Post;
DoAfterSaveToMail;
end;
finally
lStream.Free;
lStrLst.free;
lFtpQuery.Free;
end;
end;
procedure TYBNBOCEANEDIBuilder.RemindWhenDataHasProblem;
var
lQry: TYBADOQuery;
lMailBuilder: TYBCustomDBMailBuilder;
lStrLst: TStringList;
begin
try
lQry := TYBADOQuery.Create(nil);
lMailBuilder := TYBCustomDBMailBuilder.Create;
lStrLst := TStringList.Create;
try
lQry.Connection := Connection;
lQry.SQL.Text := 'select * from JCJZX where JCJZX_JCCZT=''预进场'' and JCJZX_CGS=''NBOCEAN''';
lQry.Open;
while not lQry.Eof do
begin
lStrLst.Add(lQry.fieldByName('JCJZX_JZXBH').AsString);
lQry.Next;
end;
if lStrLst.Count > 0 then
begin
lMailBuilder.Conn := ybgetmainconn(connection);
lMailBuilder.SenderUserName := 'admin';
lMailBuilder.Content := lStrLst.CommaText;
//增加接收者
lMailBuilder.AddReceiver('reminder',Self.GetRemindReceiverEmail);
//题目
lMailBuilder.Subject := 'EDI可疑提醒';
//处理标识
lMailBuilder.ProcID := 'EDI可疑提醒';
lMailBuilder.ProcNote1 := DateTimeToStr(Now);
lMailBuilder.ProcNote2 := DateTimeToStr(Now);
lMailBuilder.ProcNote3 := '';
lMailBuilder.ProcNote4 := 'YB';
//生成数据库待发送邮件
lMailBuilder.SendToDBServer;
end;
finally
lQry.Free;
lMailBuilder.Free;
lStrLst.free;
end;
except
end;
end;
其中BuildGateOutEdi函数导出生成文件时,会生成两个文件,文件名分别为NBOCEAN出场(新)20251013134938261和EDI-ZHD-OUT-202510131338,帮我检查下是哪部分代码生成的NBOCEAN出场(新)20251013134938261的文件