This class handles the import of custom bank statements for S_Bank and P_Bank from their respective SharePoint folders as part of the financial integration process.
public
class ImportPaymentFileService extends SysOperationServiceBase
{
CompanyBankAccountId bankAccountId;
BankAccountTable bankAccountTable;
container outputCont;
InfologData infologData;
#define.CSVFormat('.csv')
/// <summary>
/// Executes the
process.
/// </summary>
/// <param name =
"_contract">Buffer of _ImportPaymentFileContract</param>
public void process ( _ImportPaymentFileContract _contract)
{
_DownloadFileFromSharepoint downloadFileFromSharepointdown;
infologData = conNull();
bankAccountId =
_contract.parmCompanyBankAccountId();
bankAccountTable = BankAccountTable::find(bankAccountId);
if(this.validateParameters())
{
downloadFileFromSharepointdown =
new _DownloadFileFromSharepoint();
downloadFileFromSharepointdown.parmSharePointURL(this.getSharePointURL(
_CustomFileImportStatus::None));
this.initiate(this.parmOutputCont(downloadFileFromSharepointdown.downloadFile()));
}
else
{
infologData = InfoLog.export();
_CustomStmtA uditLog::insertLog("", bankAccountTable.AccountID,
"", NoYes::No, NoYes::Yes, DateTimeUtil::getSystemDateTime(),
_CustomFileImportStatus::Error, infologData);
}
}
/// <summary>
/// Initiate the
process.
/// </summary>
/// <param name =
"_container">Container holds all the file
details</param>
public void initiate(container
_container)
{
System.IO.MemoryStream conMemoryStream;
if(conLen(_container) > 0)
{
for(int value = 1; value <= conLen(_container); value++)
{
container memoryCont = conPeek(_container, value);
conMemoryStream =
new System.IO.MemoryStream();
conMemoryStream = conPeek(memoryCont,
2);
str fileName = conPeek(memoryCont, 1);
str extension;
str path;
str name;
[path, name, extension]
= fileNameSplit(fileName);
Microsoft.Dynamics.AX.Framework.FileManagement.DocumentLocation documentLocation = conPeek(memoryCont,
3);
try
{
ttsbegin;
infologData = conNull();
BankStatementId bankStatementId;
AsciiStreamIo asciFile
= AsciiStreamIo::constructForRead(conMemoryStream);
if(asciFile)
{
if(asciFile.status())
{
throw error("@Deloitte: _FileError");
}
if(extension != #CSVFormat)
{
throw error("@Deloitte: _FormatError");
}
asciFile.inFieldDelimiter(',');
//asciFile.inRecordDelimiter('\r\n');
asciFile.inRecordDelimiter('\n');
}
switch(bankAccountTable. _CustomBankStmtAccount)
{
case _CustomBankStmtAccount:: S_bank:
bankStatementId = this.insert S_bankBankStatement(asciFile);
break;
case _CustomBankStmtAccount::P_bank_bank:
bankStatementId = this.insertP_bank_bankBankStatement(asciFile);
break;
default:
break;
}
if(!bankStatementId)
{
throw error("@Deloitte: _ServiceFileError", fileName);
}
this.moveFile( _CustomFileImportStatus::Archive, conMemoryStream, fileName,
documentLocation);
_CustomStmtAuditLog::insertLog(fileName,
bankAccountTable.AccountID, bankStatementId, NoYes::Yes, NoYes::No,
DateTimeUtil::getSystemDateTime(), _CustomFileImportStatus::Archive,
infologData);
info(strFmt("@Deloitte: _SuccessInfo", bankStatementId));
ttscommit;
}
catch
{
this.moveFile( _CustomFileImportStatus::Error, conMemoryStream, fileName,
documentLocation);
infologData = InfoLog.export();
_CustomStmtAuditLog::insertLog(fileName,
bankAccountTable.AccountID, "", NoYes::No, NoYes::Yes,
DateTimeUtil::getSystemDateTime(), _CustomFileImportStatus::Error,
infologData);
}
}
}
else
{
error(strFmt("@Deloitte: _NoFileError", this.getSharePointURL( _CustomFileImportStatus::None),
bankAccountTable.AccountID));
infologData = InfoLog.export();
_CustomStmtAuditLog::insertLog("",
bankAccountTable.AccountID, "", NoYes::No, NoYes::Yes,
DateTimeUtil::getSystemDateTime(), _CustomFileImportStatus::Error,
infologData);
}
}
/// <summary>
/// Get the sharepoint url
/// </summary>
/// <param name =
"_status"> _CustomFileImportStatus.</param>
/// <returns>sharepoint
url</returns>
private str getSharePointURL( _CustomFileImportStatus
_status)
{
DocuType docuType;
str sharepointURL;
str docuURL;
docuType =
DocuType::find(bankAccountTable. _DocuTypeId);
if(!docuType)
throw Error (strfmt(" _DocuError"));
docuURL =
docuType.sharePointUrl();
if(!docuURL)
{
throw Error(strFmt("@Deloitte: _URLError"), docuType.TypeId);
}
if(_status == _CustomFileImportStatus::Error &&
bankAccountTable. _ErrorSharePointPath)
{
sharepointURL = strFmt("@Deloitte: _URL",docuURL, bankAccountTable. _ErrorSharePointPath);
}
else if(_status
== _CustomFileImportStatus::Archive
&& bankAccountTable. _ArchiveSharepointPath)
{
sharepointURL = strFmt("@Deloitte: _URL",docuURL, bankAccountTable. _ArchiveSharepointPath);
}
else if(_status
== _CustomFileImportStatus::None
&& bankAccountTable. _InboundSharepointPath)
{
sharepointURL = strFmt("@Deloitte: _URL",docuURL, bankAccountTable. _InboundSharepointPath);
}
else
{
sharepointURL = docuURL;
}
return
sharepointURL;
}
/// <summary>
/// Get the connection string
/// </summary>
/// <param name =
"_status"> _CustomFileImportStatus.</param>
/// <returns>container based on
status</returns>
public container getConnectionDetails( _CustomFileImportStatus
_status)
{
_DownloadFileFromSharepoint downLoadFile
= _DownloadFileFromSharepoint::construct();
downLoadFile.parmSharePointURL(this.getSharePointURL(_status));
return
downLoadFile.getConnectionDetails(downLoadFile.connectionString());
}
/// <summary>
/// Upload the file to sharepoint
/// </summary>
/// <param name =
"_status"> _CustomFileImportStatus.</param>
/// <param name =
"_conMemoryStream">Memory strem of a file.</param>
/// <param name =
"_fileName">File name</param>
/// <param name =
"_documentLocation">location to move the file</param>
public void moveFile( _CustomFileImportStatus
status, System.IO.MemoryStream conMemoryStream, FileName _fileName,
Microsoft.Dynamics.AX.Framework.FileManagement.DocumentLocation
_documentLocation)
{
if(_conMemoryStream && _fileName)
{
container
binData;
Binary
binaryData;
System.Byte[]
localbinData;
System.IO.Stream
localStream;
Microsoft.Dynamics.AX.Framework.FileManagement.SharePointDocumentStorageProvider
saveprovider;
Microsoft.Dynamics.AX.Framework.FileManagement.DocumentLocation
savedocumentLocation = new
Microsoft.Dynamics.AX.Framework.FileManagement.DocumentLocation();
container
connectionCon = this.getConnectionDetails(_status);
str
extId = xUserInfo::getCurrentUserExternalId();
saveprovider = new
Microsoft.Dynamics.AX.Framework.FileManagement.SharePointDocumentStorageProvider(conPeek(connectionCon,
1), conPeek(connectionCon, 2), conPeek(connectionCon, 3), extId);//"cpaaustralia.sharepoint.com",
"sites/CPAD365FO", "Shared Documents/Upload", extId);
saveprovider.ProviderId = DocuStorageProviderType::SharePoint;
binaryData = Binary::constructFromMemoryStream(_conMemoryStream);
if(binaryData)
{
binData = binaryData.getContainer();
}
for(int i = 0; i < conLen(binData); i++)
{
localbinData = conPeek(binData, i+1);
localStream = new System.IO.MemoryStream(localbinData);
}
boolean fileExists = saveprovider.FileExists(_fileName);
if(fileExists)
{
if(localStream.Length > 0)
{
savedocumentLocation = saveprovider.SaveFileWithOverwrite(newGuid(), _fileName,
System.Web.MimeMapping::GetMimeMapping(_fileName), localStream);
saveprovider.DeleteFile(_documentLocation);
}
}
else
{
savedocumentLocation = saveprovider.SaveFile(newGuid(), _fileName,
System.Web.MimeMapping::GetMimeMapping(_fileName), localStream);
if(saveprovider.FileExists(_fileName))
{
saveprovider.DeleteFile(_documentLocation);
}
}
}
}
/// <summary>
/// Create S_bank bank statement
/// </summary>
/// <param name =
"_asciFile">Asci file.</param>
/// <returns>Bank Statement
id</returns>
private BankStatementId insert S_bankBankStatement(AsciiStreamIo
_asciFile)
{
FromDateTime
statementFromdate = DateTimeUtil::getSystemDateTime();
ToDateTime
statementTodate = DateTimeUtil::getSystemDateTime();
container
record;
RefRecId
bankStmtISOHeaderRecid;
BankStmtISOAccountStatement statement;
boolean
bankStatementHeaderCreated;
boolean
lineCreated;
container
statementLineCont;
int
row = 0;
str
chargeId;
while(!_asciFile.status())
{
record = _asciFile.read();
row += 1;
if(conLen(record) && row >=2)
{
statementLineCont += [record];
if(!bankStatementHeaderCreated)
{
statement = this.insertIntoBankStmtISOHeader(statementFromdate,
statementTodate);
bankStatementHeaderCreated = true;
bankStmtISOHeaderRecid = statement.RecId;
}
}
}
//insert
statement lines
if(conLen(statementLineCont) && bankStatementHeaderCreated)
{
this.insert S_bankBankStmtISOReportEntry(bankStmtISOHeaderRecid,
statementLineCont);
}
lineCreated =
this.updateStatementDate(statement.RecId, NoYes::Yes);
if(!lineCreated)
{
throw error ("@Deloitte: _LineError");
}
//insert cash
balance
if(statement)
{
this.insertIntoBankStmISOCash(statement);
}
return
statement.Identification;
}
/// <summary>
/// Update bank statement header
/// </summary>
/// <param name =
"_statementRecId">RecID of bank statement
header.</param>
/// <param name =
"_updateDate">Update date in header.</param>
/// <returns>True if lines
created;otherwise false</returns>
private boolean
updateStatementDate(RefRecId statementRecId, boolean updateDate)
{
BankStmtISOReportEntry reportEntryMax;
BankStmtISOReportEntry reportEntryMin;
int
totalLines;
BankStmtISOAccountStatement statement =
BankStmtISOAccountStatement::find(_statementRecId);
if(statement)
{
if(_updateDate)
{
select firstonly BookingDateTime from reportEntryMax
order by BookingDateTime desc
where reportEntryMax.BankStmtISOAccountStatement == _statementRecId;
select firstonly BookingDateTime from reportEntryMin
order by BookingDateTime asc
where reportEntryMin.BankStmtISOAccountStatement == _statementRecId;
statement.FromDateTime = reportEntryMin.BookingDateTime;
statement.ToDateTime = reportEntryMax.BookingDateTime;
}
statement.selectForUpdate(true);
totalLines = int642int(statement.calcTotalLines());
statement.TotalEntriesNumberOfEntries = totalLines;
ttsbegin;
statement.update();
ttscommit;
}
return
totalLines;
}
/// <summary>
/// Create S_bank bank statement lines
/// </summary>
/// <param name =
"_bankStmtISOHeaderRecid">RecID of bank statement
header.</param>
/// <param name =
"_statementLineCon">statement lines container.</param>
private void insert S_bankBankStmtISOReportEntry(RefRecId
bankStmtISOHeaderRecid, container statementLineCon)
{
BankStmtISOReportEntry reportEntry;
Amount
netAmount;
utcdatetime
lastBookingDateTime;
if(!_bankStmtISOHeaderRecid || conLen(_statementLineCon) < 0)
{
return;
}
for(int value =
1; value <= conLen(_statementLineCon) ; value++)
{
container statementLineValCon;
AmountCur chargeAmount;
DebitCredit chargeIndicator, amountIndicator;
AmountCur grossAmt;
utcdatetime bookingDateTime;
utcdatetime feeBookingDateTime;
container lineCon;
lineCon = conPeek(_statementLineCon, value);
statementLineValCon = this.returnRecordContainer(lineCon);
grossAmt = str2Num(conPeek(statementLineValCon, 7));
netAmount += str2Num(conPeek(statementLineValCon, 9));
bookingDateTime = this.getDateTime(conPeek(statementLineValCon, 1),
NoYes::No);
lastBookingDateTime = bookingDateTime;
amountIndicator = DebitCredit::Debit;
if(grossAmt < 0)
{
amountIndicator = DebitCredit::Credit;
}
//Aaditya Purohit, MMFS36: General S_bank
payment, 25/7/2024 -->
//CPA: NR: 20240901: FNO-756: CR S_bank
and P_bank_bank statements imported to FO
if(grossAmt > 0 && conPeek(statementLineValCon, 11) !=
"")
{
this.insert S_bankReportEntry(_bankStmtISOHeaderRecid, statementLineValCon,
grossAmt, amountIndicator, bookingDateTime, NoYes::No);
}
else if(grossAmt > 0 && conPeek(statementLineValCon, 11) ==
"")
{
this.insert S_bankReportEntry(_bankStmtISOHeaderRecid, statementLineValCon,
grossAmt, amountIndicator, bookingDateTime, NoYes::Yes);
}
else if(grossAmt < 0 && conPeek(statementLineValCon, 11) ==
"")
{
this.insert S_bankReportEntry(_bankStmtISOHeaderRecid, statementLineValCon,
grossAmt, amountIndicator, bookingDateTime, NoYes::Yes);
}
else if(grossAmt < 0 && conPeek(statementLineValCon, 11) !=
"")
{
this.insert S_bankReportEntry(_bankStmtISOHeaderRecid, statementLineValCon,
grossAmt, amountIndicator, bookingDateTime, NoYes::No);
}
//CPA: NR: 20240901: FNO-756: CR S_bank
and P_bank_bank statements imported to FO
chargeAmount = str2Num(conPeek(statementLineValCon, 8));
chargeIndicator = DebitCredit::Credit;
if(chargeAmount < 0)
{
chargeIndicator = DebitCredit::Debit;
}
if(chargeAmount != 0)
{
//feeBookingDateTime = this.getDateTime(conPeek(statementLineValCon, 1),
NoYes::No);
this.insert S_bankReportEntry(_bankStmtISOHeaderRecid, statementLineValCon,
chargeAmount, chargeIndicator, bookingDateTime, NoYes::Yes);
}
//Aaditya Purohit, MMFS36: General S_bank
payment, 25/7/2024 <--
}
if(netAmount)
{
BankStmtISOReportEntry
lastReportEntry;
lastReportEntry.clear();
lastReportEntry.initValue();
lastReportEntry.BankStmtISOAccountStatement = _bankStmtISOHeaderRecid;
lastReportEntry.BookingDateTime = lastBookingDateTime;
lastReportEntry.BankStatementLineStatus =
BankStatementLineStatus::Booked;
lastReportEntry.Amount = abs(netAmount);
lastReportEntry.AmountCreditDebitIndicator = DebitCredit::Credit;
if(netAmount < 0)
{
lastReportEntry.AmountCreditDebitIndicator = DebitCredit::Debit;
}
// add by ratna #MMFS-30 payout litne reference on
07-09-2024
lastReportEntry.EntryReference = strFmt("@Deloitte: S_bankPayout",date2Str(DateTimeUtil::date(lastBookingDateTime),123,DateDay::Digits2,DateSeparator::None,DateMonth::Digits2,DateSeparator::None,DateYear::Digits4));
//end by ranta #MMFS-30
lastReportEntry.insert();
lastReportEntry.reread();
this.insertIntoBankStatementLineBankDocument(lastReportEntry,
NoYes::Yes);
//BankStmtISOReportEntry::markNewBankDocument(lastReportEntry.RecId,
NoYes::Yes);
}
}
/// <summary>
/// Create S_bank bank statement lines
/// </summary>
/// <param name =
"_bankStmtISOHeaderRecid">RecID of bank statement
header.</param>
/// <param name =
"_valueCon">statement lines container.</param>
/// <param name =
"_amount">Gross amount.</param>
/// <param name =
"_indicator">Amount indicator.</param>
/// <param name =
"_bookingDatetime">line booking date time.</param>
/// <param name =
"_isNew">statement Is new document.</param>
private void insert S_bankReportEntry(RefRecId
bankStmtISOHeaderRecid, container valueCon, Amount amount, DebitCredit
indicator, utcdatetime bookingDatetime, NoYes isNew)
{
BankStmtISOReportEntry reportEntry;
container
valueCon = _valueCon;
if(conLen(valueCon))
{
reportEntry.clear();
reportEntry.initValue();
reportEntry.BankStmtISOAccountStatement = _bankStmtISOHeaderRecid;
//ratna, MMFS36: General S_bank payment,
16/8/2024 -->
reportEntry.EntryReference = conPeek(valueCon, 14);
reportEntry.BookingDateTime = _bookingDatetime;
reportEntry.BankStatementLineStatus = BankStatementLineStatus::Booked;
reportEntry.ReferenceNumber = conPeek(valueCon, 13);
reportEntry. _Source_Id = conPeek(valueCon, 15);
reportEntry. _AdjustmentNoteNum = conPeek(valueCon, 17);
reportEntry. _CPAAustraliaIdStr = conPeek(valueCon, 16);
reportEntry. _EnvironmentPlatform = conPeek(valueCon, 18);
//ratna , MMFS36: General S_bank
payment, 16/8/2024 <--
//reportEntry.BankDocumentNumber = conPeek(valueCon, 27);
reportEntry.Amount = abs(_amount);
reportEntry.AmountCreditDebitIndicator = DebitCredit::Debit;
reportEntry.AmountCreditDebitIndicator = _indicator;
//reportEntry.IsNewBankDocument = _isNew;
reportEntry.insert();
reportEntry.reread();
if (_isNew)
{
// comented by ratna #MMFS-27 double posting sub ledger issue related on
01-07-2024
// this.insertIntoBankStatementLineBankDocument(reportEntry, NoYes::No);
// End by ratna #MMFS-27
// The method below will update the IsNewBankDocument field
BankStmtISOReportEntry::markNewBankDocument(reportEntry.RecId, _isNew);
}
}
}
/// <summary>
/// Gets datetime
/// </summary>
/// <param name =
"_dateTimeStr">String date/time from csv.</param>
/// <param name =
"_isUTC">Is it utc string.</param>
/// <returns>utc date
time</returns>
private utcdatetime getDateTime(str
dateTimeStr, boolean isUTC)
{
List list =
new List(Types::String);
container packedList;
ListIterator iterator;
int
i;
str
dateStr;
str
timeStr;
str
newdateStr;
int
timeInt;
date
dateValue;
str
datetimeStr = _dateTimeStr;
utcdatetime dateTime;
if(datetimeStr)
{
list = Global::strSplit(datetimeStr," ");
iterator = new ListIterator(list);
while(iterator.more())
{
packedList += iterator.value();
iterator.next();
}
if(conLen(packedList)>0)
{
newdateStr = conPeek(packedList,1);
timeStr = conPeek(packedList,2);
}
timeInt = str2Time(timeStr);
dateValue = str2Date(newdateStr, 321);
}
if(_isUTC)
{
dateTime = DateTimeUtil::newDateTime(dateValue,
timeInt);
}
else
{
dateTime =
DateTimeUtil::removeTimeZoneOffset(DateTimeUtil::newDateTime(dateValue,
timeInt), DateTimeUtil::getUserPreferredTimeZone());
}
return
dateTime;
}
/// <summary>
/// Create P_bank_bank bank
statement
/// </summary>
/// <param name =
"_asciFile">Asci file.</param>
/// <returns>Bank Statement
id</returns>
private BankStatementId insertP_bank_bankBankStatement(AsciiStreamIo
_asciFile)
{
FromDateTime
statementFromdate;
ToDateTime
statementTodate;
container
record;
RefRecId
bankStmtISOHeaderRecid;
BankStmtISOAccountStatement statement;
boolean
bankStatementHeaderCreated;
container
statementLineCont;
boolean
lineCreated;
while(!_asciFile.status())
{
str firstColumn;
record = _asciFile.read();
if(conLen(record))
{
firstColumn = conPeek(record, 1);
firstColumn = strRem(firstColumn, '"\"');
if(firstColumn == 'SH' && !bankStatementHeaderCreated)
{
statementFromdate = this.getDateTime(conPeek(record, 2), NoYes::Yes);
statementTodate = this.getDateTime(conPeek(record, 3), NoYes::Yes);
statement = this.insertIntoBankStmtISOHeader(statementFromdate,
statementTodate, strRem(conPeek(record, 4), '"\"'));
bankStmtISOHeaderRecid = statement.RecId;
bankStatementHeaderCreated = true;
}
else if(firstColumn == 'SB')
{
statementLineCont += [record];
}
}
}
//insert
statement lines
if(conLen(statementLineCont) && bankStatementHeaderCreated)
{
this.insertIntoP_bank_bankBankStmtISOReportEntry(bankStmtISOHeaderRecid,
statem