The file will be automatically generated from D365 using a batch job.
The trial balance file should include all dimensions. (Main-Dept-Cost center-Business unit-Value stream)
/// contract class to export file in azure blob storag
/// </summary>
[DataContractAttribute,
SysOperationContractProcessing(classStr(NPHFloQastExportUIBuilder))]
internal final class ExportContract extends SysOperationAttributedDataContractInfo
{
private FromDate fromDate;
private ToDate toDate;
private DateCode period;
private Name primaryDimensionFocus;
private DimensionSetName dimensionSetName;
[DataMemberAttribute("@SYS2168")]
public FromDate parmFromDate(FromDate _fromDate = fromDate)
{
fromDate = _fromDate;
return _fromDate;
}
[DataMemberAttribute("@SYS22882")]
public ToDate parmToDate(ToDate _toDate = toDate)
{
toDate = _toDate;
return _toDate;
}
[DataMemberAttribute("@SYS22717")]
public DateCode parmPeriod(DateCode _period = period)
{
period = _period;
return _period;
}
[DataMemberAttribute("@SYS83218")]
public DimensionSetName parmDimensionSetName(DimensionSetName _dimensionSetName = dimensionSetName)
{
dimensionSetName = _dimensionSetName;
return dimensionSetName;
}
}
Create Controller class
internal final class ExportController extends SysOperationServiceController
{
public static void main(Args args)
{
ExportController sysOperationController = ExportController::construct();
sysOperationController.parmClassName(classStr(ExportService));
sysOperationController.parmMethodName(methodStr(ExportService, operation));
sysOperationController.parmExecutionMode(SysOperationExecutionMode::Synchronous);
sysOperationController.startOperation();
}
private static ExportController construct()
{
ExportController controller = new ExportController();
controller.parmDialogCaption("Caption");
return controller;
}
public ClassDescription caption()
{
return "Caption";
}
protected boolean canRunInNewSession()
{
return true;
}
/// <summary>
///
/// </summary>
/// <param name = "_dialogCaption"></param>
/// <returns></returns>
public LabelType parmDialogCaption(LabelType _dialogCaption = dialogCaption)
{
return "Caption";
}
}
Crate UI builder class
internal final class ExportUIBuilder extends SysOperationAutomaticUIBuilder
{
DialogField dialogPeriodName;
DialogField dialogFromDate;
DialogField dialogToDate;
DialogField dialogDimset ;
private DateCode period;
private FromDate fromDate;
private ToDate toDate;
private DimensionSetName dimensionSetName;
ExportContract dataContract;
public void build()
{
Dialog dialogLocal = dialog as Dialog;
dataContract = this.getContract();
dialogPeriodName = this.addDialogField(methodstr(ExportContract,parmPeriod),dataContract);
dialogPeriodName.label("@SYS22717");
dialogPeriodName.lookupButton(2);
dialogFromDate = this.addDialogField(methodstr(ExportContract,parmFromDate),dataContract);
dialogToDate = this.addDialogField(methodstr(ExportContract,parmToDate),dataContract);
dialogDimset = this.addDialogField(methodstr(ExportContract,parmDimensionSetName),dataContract);
}
public boolean periodModified(FormStringControl _control)
{
DateCode dateCode ;
LedgerPeriodCode ledgerPeriodCode;
dialogPeriodName.value(_control.valueStr());
dateCode = dialogPeriodName.value();
ledgerPeriodCode = ledgerPeriodCode::find(dateCode);
dialogFromDate.value(ledgerPeriodCode.fromDate());
dialogToDate.value(ledgerPeriodCode.toDate());
return true;
}
[Hookable(false)]
public void postRun()
{
super();
dialogPeriodName.value(this.getContract().parmPeriod());
dialogPeriodName.fieldControl().mandatory(true);
dialogPeriodName.registerOverrideMethod(
methodStr(FormStringControl, modified),
methodStr(ExportUIBuilder, periodModified),
this);
dialogFromDate.value(this.getContract().parmFromDate());
dialogFromDate.allowEdit(false);
dialogToDate.value(this.getContract().parmToDate());
dialogToDate.allowEdit(false);
dialogDimset.value(this.getContract().parmDimensionSetName());
}
private ExportContract getContract()
{
if (dataContract == null)
{
dataContract = this.dataContractObject() as ExportContract;
}
return dataContract;
}
}
/// <summary>
/// 15933 - Flocast integration
/// exports file in azure blob storage
/// </summary>
using Microsoft.WindowsAzure.Storage;
using System.Text;
create service class
internal final class ExportService
{
int records ;
Filename currentFileName ;
date startDate ;
date endDate ;
PeriodName periodName ;
LedgerParameters ledgerParameters ;
DimensionSetName dimensionSetName ;
public void new()
{
LedgerPeriodCode ledgerPeriodCode ;
super();
ledgerPeriodCode = ledgerPeriodCode::find("CP");
startDate = ledgerPeriodCode.fromDate() ;
endDate = ledgerPeriodCode.toDate() ;
periodName = ledgerPeriodCode.Code ;
}
public void operation(ExportContract _dataContract)
{
System.Exception ex;
periodName = _dataContract.parmPeriod() ;
startDate = _dataContract.parmFromDate() ;
endDate = _dataContract.parmToDate() ;
dimensionSetName = _dataContract.parmDimensionSetName() ;
ledgerParameters = LedgerParameters::find();
Filename filename = ledgerParameters.FileName ? ledgerParameters.FileName : "GLAccounts";
str timestamp = strReplace(time2str(timeNow(),TimeSeparator::Colon, TimeSeparator::Colon),":","_");
currentFileName = strFmt("%1_%2_%3_%4.csv",filename,
date2str(startDate,321,2,DateSeparator::None,2,DateSeparator::None,4),
date2str(endDate,321,2,DateSeparator::None,2,DateSeparator::None,4),
timestamp);
//time2str(timeNow(),TimeSeparator::Colon, TimeSeparator::Colon));
try
{
this.runExport();
info(strFmt("@Newport:NPHFloQastRecords",records, currentFileName));
}
catch (Exception::CLRError)
{
ex = CLRInterop::getLastException();
if (ex != null)
{
error(ex.ToString());
}
}
catch (Exception::CodeAccessSecurity)
{
error("@Newport:NPHFloQastFileIssue");
}
}
TextStreamIo openFile()
{
FileIOPermission perm;
;
info("file: " + currentFileName);
perm = new FileIOPermission(currentFilename, "w");
if (perm == null)
{
return null;
}
perm.assert();
TextStreamIo fileOut = TextStreamIo::constructForWrite();
return fileOut;
}
void runExport()
{
DimensionHierarchy dimHier ;
LedgerTrialBalanceTmp ledgerTrialBalanceTmp ;
LedgerTrialBalanceListPageBalanceParms balanceParameters ;
List postingLayers = new List(Types::Enum);
postingLayers.addEnd(currentOperationsTax::Current);
dimHier = DimensionHierarchy::findByTypeAndName(DimensionHierarchyType::Focus, dimensionSetName);
balanceParameters = LedgerTrialBalanceListPageBalanceParms::construct();
balanceParameters.loadDefault();
balanceParameters.parmPostingLayers(postingLayers);
balanceParameters.parmStartDate(startDate);
balanceParameters.parmEndDate(endDate);
balanceParameters.parmDimensionSetHierarchy(dimHier);
//info(strFmt("@SYS2168" + "%1", startDate));
//info(strFmt("@SYS22882" + "%1", endDate));
select firstOnly RecId from LedgerTrialBalanceTmp;
LedgerTrialBalanceTmp::calculateBalances_V2(
ledgerTrialBalanceTmp,
dimHier.Name,
balanceParameters.getStartDate(),
balanceParameters.getEndDate(),
balanceParameters.getIncludeOpeningPeriods(),
balanceParameters.getIncludeClosingAdjustments(),
balanceParameters.getIncludeClosingTransactions(),
balanceParameters.getPostingLayers(),
true);
this.writeFile(ledgerTrialBalanceTmp);
}
// 15933_Flocast integration
private void writeFile(LedgerTrialBalanceTmp _ledgerTrialBalanceTmp)
{
str recordStr ;
MainAccount mainAccount ;
AccountCategory accountCategory ;
str hasActivity ;
container con;
recordStr = strFmt("CostCenter,MainAccount,BusinessUnit,ValueStrem,Department,Name,OpeningBalance,Debit,Credit,ClosingBalance");
con += recordStr;
while select _ledgerTrialBalanceTmp
//join mainAccount where mainAccount.MainAccountId == _ledgerTrialBalanceTmp.DimensionValues[0]
{
accountCategory = mainAccount.accountCategory(false,"");
if (this.isSuspended(mainAccount))
{
continue;
}
if (_ledgerTrialBalanceTmp.AmountCredit == 0 && _ledgerTrialBalanceTmp.AmountDebit == 0)
{
hasActivity = "FALSE";
}
else
{
hasActivity = "TRUE";
}
recordStr = strFmt("%1,%2,%3,%4,%5,%6,%7,%8,%9,%10",
_ledgerTrialBalanceTmp.DimensionValues[1],
_ledgerTrialBalanceTmp.DimensionValues[2],
_ledgerTrialBalanceTmp.DimensionValues[3],
_ledgerTrialBalanceTmp.DimensionValues[4],
_ledgerTrialBalanceTmp.DimensionValues[5],
_ledgerTrialBalanceTmp.PrimaryFocusDescription,
num2Str(_ledgerTrialBalanceTmp.OpeningBalance,16,2,DecimalSeparator::Dot, ThousandSeparator::None),
num2Str(_ledgerTrialBalanceTmp.AmountDebit,16,2,DecimalSeparator::Dot, ThousandSeparator::None),
num2Str(_ledgerTrialBalanceTmp.AmountCredit,16,2,DecimalSeparator::Dot, ThousandSeparator::None),
num2Str(_ledgerTrialBalanceTmp.EndingBalance,16,2,DecimalSeparator::Dot, ThousandSeparator::None)
);
con += recordStr;
records++;
recordStr = "";
}
//Info(strFmt("%1", con));
this.uploadFileToAzureBlob(con);
}
Suspended isSuspended(MainAccount _mainAccount)
{
DimensionAttributeValue dimAttrValue;
DimensionAttribute mainAccountDimAttribute;
Suspended isMainAccountSuspended;
mainAccountDimAttribute.RecId = DimensionAttribute::getWellKnownDimensionAttribute(DimensionAttributeType::MainAccount);
dimAttrValue = DimensionAttributeValue::findByDimensionAttributeAndEntityInst(mainAccountDimAttribute.RecId, _mainAccount.RecId, true);
isMainAccountSuspended = dimAttrValue.IsSuspended;
return isMainAccountSuspended;
}
public void uploadFileToAzureBlob(container _lineData)
{
var storageCredentials = new Microsoft.WindowsAzure.Storage.Auth.StorageCredentials(ledgerParameters.StorageAccountName, ledgerParameters.FloQastAzureStorageAccountKey);
CloudStorageAccount storageAccount = new Microsoft.WindowsAzure.Storage.CloudStorageAccount(storageCredentials, true);
Microsoft.WindowsAzure.Storage.Blob.CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
Microsoft.WindowsAzure.Storage.Blob.CloudBlobContainer container1 = blobClient.GetContainerReference(ledgerParameters.FloQastBlobStorageEndpointContainerName);
Microsoft.WindowsAzure.Storage.Blob.CloudBlockBlob blockBlob = container1.GetBlockBlobReference(currentFileName);
StringBuilder csvData = new StringBuilder();
for(int j = 1; j<= conLen(_lineData); j++)
{
csvData.AppendLine(con2Str(conPeek(_lineData,j)));
}
str dataToUpload = csvData.ToString();
using (var ms = new System.IO.MemoryStream(System.Text.Encoding::get_UTF8().GetBytes(dataToUpload)))
{
blockBlob.UploadFromStream(ms,null,null,null);
}
}
}
Add parameters for Azure Account
