Fichiers générés non conformes
Ferméf894009 Messages postés 17217 Date d'inscription dimanche 25 novembre 2007 Statut Membre Dernière intervention 27 décembre 2024 - 16 oct. 2022 à 08:00
Bonjour,
Dans le cadre de mon activité, nous avons mis en place cette macro qui est sensée générer des fichiers au format json que nous utilisons pour nos tests. Seulement, seuls quelques données sont pris en compte.
Extrait de code :
Option Explicit
'########################################################################
'# W. SACQUEPEE
'# 27/11/2017
'# Generation of JSON files for OSM Mock
'########################################################################
'----------------------------------------------------
' Public structures
'----------------------------------------------------
' Constants
'----------------------------------------------------
'---- Maximal number of formats
Const CST_MAX_FMT_NBR = 50
'---- Maximal number of timezones
Const CST_MAX_TMZ_NBR = 200
'---- Maximal number of entities
Const CST_MAX_ENT_NBR = 40
'---- Maximal number of configurations
Const CST_MAX_CFG_NBR = 1000
'---- Maximal number of accounts
Const CST_MAX_ACC_NBR = 1000
'---- Maximal number of counterparties
Const CST_MAX_CTP_NBR = 1000
'---- Maximal number of accounts per configuration
Const CST_MAX_ACCPERCFG_NBR = 25
'---- Maximal number of remitter counterpart per configuration
Const CST_MAX_CTPPERCFG_NBR = 25
'---- Maximal number of product per configuration
Const CST_MAX_PDTPERCFG_NBR = 25
'---- Maximal number of Product
Const CST_MAX_PDT_NBR = 200
'--- ProductType Intraday CACIB
Const CST_PT_INTRADAY_CACIB = "Intraday CACIB"
'--- ProductCode Intraday CACIB
Const CST_PC_INTRADAY_CACIB = "ITD-ACCT-RPT-CACIB"
'--- ProductType End Of Period CACIB
Const CST_PT_EOD_CACIB = "End Of Period CACIB"
'--- ProductType PSR SCT
Const CST_PT_PSR_SCT = "PSR-SCT"
'--- ProductType PSR SCT CBI
'Const CST_PT_PSR_SCT_CBI = "PSR-SCT-CBI"
'--- ProductType PSR FCT CBI
'Const CST_PT_PSR_FCT_CBI = "PSR-FCT-CBI"
'--- ProductType PSR XCT CBI
'Const CST_PT_PSR_XCT_CBI = "PSR-XCT-CBI"
'--- ProductType PSR SDD B2B CBI
'Const CST_PT_PSR_SDD_BCBI = "PSR-SDD-B2B-CBI"
'--- ProductType PSR SDD CORE CBI
'Const CST_PT_PSR_SDD_CCBI = "PSR-SDD-CORE-CBI"
'--- ProductCode Intraday CACIB
Const CST_PC_EOD_CACIB = "EOP-ACCT-STMT-CACIB"
'--- ProductType BSB
Const CST_PT_BSB = "BSB"
'--- Product Type DBT-ADV
Const CST_PT_DBT_ADV = "DBT-ADV"
'--- Product CRDT-ADV
Const CST_PT_CRDT_ADV = "CRDT-ADV"
'--- Option SENT
Const CST_OPT_SENT = "SENT"
'---- Indentation management
Public strIndent As String
Public iNbrElem(20) As Integer 'Number of elements per level (to detect the first one and add a coma before)
' 0.1 Timezones
'----------------------------------------------------
Type TYP_Timezone
strCode As String
strUTC As String
End Type
Dim iTimezoneNbr As Integer
Dim TMZ(CST_MAX_TMZ_NBR) As TYP_Timezone
Const CST_TIMEZONE_TAB = "0. Timezone"
' 0.2 Formats
'----------------------------------------------------
Type TYP_Format
strMnemonic As String
strCode As String
strName As String
End Type
Dim iFormatNbr As Integer
Dim FMT(CST_MAX_FMT_NBR) As TYP_Format
Const CST_FORMAT_TAB = "Format"
' 1. Entities
'----------------------------------------------------
Type TYP_Entity
strCode As String
strLabel As String
strTimeZone As String
strBICCode As String
strESFCode As String
strUECode As String
strISOCode As String
strDN As String
strUTCOffset As Variant
End Type
Dim iEntityNbr As Integer
Dim ENT(CST_MAX_ENT_NBR) As TYP_Entity
Const CST_ENTITY_TAB = "1. Entity"
' 2. Counterparts
'----------------------------------------------------
Type TYP_Counterpart
strName As String
strRTSId As String
strLocalId As String
strRICOSId As String
End Type
Dim iCounterpartNbr As Integer
Dim CTP(CST_MAX_CTP_NBR) As TYP_Counterpart
Const CST_COUNTERPART_TAB = "2. Counterpart"
' 3. Accounts
'----------------------------------------------------
Type TYP_Account
strName As String
iEntityLine As Integer 'Foreign key for management entity
iCounterpartLine As Integer 'Foreign key for related counterpart
strSystemId As String
strIBAN As Variant
strBBAN As Variant
strCurrency As String
bolIsCACIB As Boolean 'used for passthrough
bolNotInOIL As Boolean
strLocalAccountId As String
strAccountType As String
strAccountTypeName As String
End Type
Dim iAccountNbr As Integer
Dim ACC(CST_MAX_ACC_NBR) As TYP_Account
Const CST_ACCOUNT_TAB = "3. Account"
' 4. OSM Configs
'----------------------------------------------------
Type TYP_Config
strConfigId As String
strFormat As String
strFormatType As String
strChannel As String
iChannelEntityLine As Integer 'FK : Entity of the channel
strCustomerBIC As String
strCustomerDN As String
strCurrency As String
strIntradayFrequency As String ' Secondary options for intraday
strEODFrequency As String ' Secondary options for End of Day
strLanguage As String
strThreshold As String
strPaymentScope As String
strAccountFormat As String
strContent As String
strEmptyReport As String
strTransactionCode As String
strTimeZone As String
strUTC As String
iFormatLine As Integer 'FK to format line
strAccountGroup As String
strCounterpartGroup As String
strRQTType As String
strRQTValue As String
strPSRRestitutionMode As String
strPSRRemitterCounterpartName As String
strPSRRemitterCounterpartRtsId As String
strPSRRemitterCounterpartRicosId As String
strPSRRemitterCounterpartLocalId As String
strPSRPostClearingReject As String
strBSBProductUniqueId As String
iCounterpartLine As Integer 'Foreign key of the subscription counterpart
End Type
Dim iConfigNbr As Integer
Dim CFG(CST_MAX_CFG_NBR) As TYP_Config
' Table of the accounts related to the subscription
Dim iCFG_ACC_Line(CST_MAX_CFG_NBR, CST_MAX_ACCPERCFG_NBR) As Integer
' Table of the counterparts related to the subscription (for PSR call 1)
Dim iCFG_CTP_Line(CST_MAX_CFG_NBR, CST_MAX_CTPPERCFG_NBR) As Integer
' Table of the products related to the subscreiption (for BSB call 1)
Dim iCFG_PDT_Line(CST_MAX_PDT_NBR, CST_MAX_PDTPERCFG_NBR) As Integer
Const CST_CONFIG_TAB = "4. OSM"
' 5. Product
'----------------------------------------------------
Type TYP_Product
strConfigId As String
strType As String
strId As String
strUniqueId As String
End Type
Dim iProductNbr As Integer
Dim PDT(CST_MAX_PDT_NBR) As TYP_Product
Const CST_PRODUCT_TAB = "5. Product"
'#### Global text constants
' There should be no labels in the code,
' especially if a label can be found in several places with the same meaning.
' Here are global constants that can be shared in several subroutines
Public Const CST_LBL_REQUEST = "request"
Public Const CST_LBL_RESPONSE = "response"
Public Const CST_LBL_CALITRACK = "calitrack"
Public Const CST_LBL_OSM_MOCK = "osm-mock"
Public Const CST_LBL_METHOD = "method"
Public Const CST_LBL_NAME = "name"
Public Const CST_LBL_CASE = "case"
Public Const CST_LBL_CONFIG_LIST = "ConfigList"
Public Const vbQuote = """"
'###########################################################################
Sub LoadTimezones()
'-----------------------------------------------------------
'---- Loads the timezones tab into a VBA table
'-----------------------------------------------------------
iTimezoneNbr = 2
Sheets(CST_TIMEZONE_TAB).Select
While Cells(iTimezoneNbr, 1) <> ""
TMZ(iTimezoneNbr).strCode = Cells(iTimezoneNbr, 1)
TMZ(iTimezoneNbr).strUTC = Cells(iTimezoneNbr, 2)
iTimezoneNbr = iTimezoneNbr + 1
Wend
iTimezoneNbr = iTimezoneNbr - 1
End Sub
Sub LoadFormats()
'-----------------------------------------------------------
'---- Loads the timezones tab into a VBA table
'-----------------------------------------------------------
iFormatNbr = 2
Sheets(CST_FORMAT_TAB).Select
While Cells(iFormatNbr, 1) <> ""
FMT(iFormatNbr).strMnemonic = Cells(iFormatNbr, 1)
FMT(iFormatNbr).strCode = Cells(iFormatNbr, 2)
FMT(iFormatNbr).strName = Cells(iFormatNbr, 3)
iFormatNbr = iFormatNbr + 1
Wend
iFormatNbr = iFormatNbr - 1
End Sub
Sub LoadProduct()
'-----------------------------------------------------------
'---- Loads the product tab into a VBA table
'-----------------------------------------------------------
iProductNbr = 2
Sheets(CST_PRODUCT_TAB).Select
While Cells(iProductNbr, 1) <> ""
PDT(iProductNbr).strConfigId = Cells(iProductNbr, 1)
PDT(iProductNbr).strType = Cells(iProductNbr, 2)
PDT(iProductNbr).strId = Cells(iProductNbr, 3)
PDT(iProductNbr).strUniqueId = Cells(iProductNbr, 4)
iProductNbr = iProductNbr + 1
Wend
iProductNbr = iProductNbr - 1
End Sub
Sub LoadEntities()
'-----------------------------------------------------------
'---- Loads the entity tab into a VBA table
'-----------------------------------------------------------
iEntityNbr = Range("Ent.Code").Row + 1
Sheets(CST_ENTITY_TAB).Select
While Cells(iEntityNbr, 1) <> ""
ENT(iEntityNbr).strCode = Range("Ent.Code").Offset(iEntityNbr - 1, 0)
ENT(iEntityNbr).strLabel = Range("Ent.Label").Offset(iEntityNbr - 1, 0)
ENT(iEntityNbr).strTimeZone = Range("Ent.Timezone").Offset(iEntityNbr - 1, 0)
ENT(iEntityNbr).strBICCode = Range("Ent.BICCode").Offset(iEntityNbr - 1, 0)
ENT(iEntityNbr).strESFCode = Range("Ent.ESFCode").Offset(iEntityNbr - 1, 0)
ENT(iEntityNbr).strUECode = Range("Ent.UECode").Offset(iEntityNbr - 1, 0)
ENT(iEntityNbr).strISOCode = Range("Ent.ISOCode").Offset(iEntityNbr - 1, 0)
ENT(iEntityNbr).strDN = Range("Ent.DN").Offset(iEntityNbr - 1, 0)
ENT(iEntityNbr).strUTCOffset = Range("Ent.UTCOffset").Offset(iEntityNbr - 1, 0)
iEntityNbr = iEntityNbr + 1
Wend
iEntityNbr = iEntityNbr - 1
End Sub
Sub LoadCounterparts()
'-----------------------------------------------------------
'---- Loads the counterparty tab into a VBA table
'-----------------------------------------------------------
Dim i As Integer
iCounterpartNbr = Range("Ctp.Name").Row + 1
Sheets(CST_COUNTERPART_TAB).Select
While Cells(iCounterpartNbr, 1) <> ""
CTP(iCounterpartNbr).strName = Range("Ctp.Name").Offset(iCounterpartNbr - 1, 0)
CTP(iCounterpartNbr).strRTSId = Range("Ctp.RTSId").Offset(iCounterpartNbr - 1, 0)
CTP(iCounterpartNbr).strLocalId = Range("Ctp.LocalId").Offset(iCounterpartNbr - 1, 0)
CTP(iCounterpartNbr).strRICOSId = Range("Ctp.RicosId").Offset(iCounterpartNbr - 1, 0)
'---- Check for duplicates of RTS Ids
If iCounterpartNbr > 2 Then
For i = 2 To iCounterpartNbr - 1
If CTP(iCounterpartNbr).strRTSId = CTP(i).strRTSId Then
Print #2, "Critical : Duplication of RTS Id #" & CTP(iCounterpartNbr).strRTSId & _
" in line #" & iCounterpartNbr & " with line #" & i
End If
If CTP(iCounterpartNbr).strLocalId = CTP(i).strLocalId Then
Print #2, "Critical : Duplication of Local Id #" & CTP(iCounterpartNbr).strLocalId & _
" in line #" & iCounterpartNbr & " with line #" & i
End If
If CTP(iCounterpartNbr).strRICOSId = CTP(i).strRICOSId And CTP(iCounterpartNbr).strRICOSId <> "" Then
Print #2, "Critical : Duplication of RICOS Id #" & CTP(iCounterpartNbr).strRICOSId & _
" in line #" & iCounterpartNbr & " with line #" & i
End If
Next i
End If
iCounterpartNbr = iCounterpartNbr + 1
Wend
iCounterpartNbr = iCounterpartNbr - 1
End Sub
Sub LoadAccounts()
'-----------------------------------------------------------
'---- Loads the Accounts tab into a VBA table
'-----------------------------------------------------------
Dim i As Integer
Dim varCellContent As Variant
Dim intFirst As Integer
intFirst = Range("Acc.Name").Row + 1
iAccountNbr = intFirst
Sheets(CST_ACCOUNT_TAB).Select
While Cells(iAccountNbr, 1) <> ""
ACC(iAccountNbr).strName = Range("Acc.Name").Offset(iAccountNbr - intFirst + 1, 0)
'----- Foreign keys
ACC(iAccountNbr).iCounterpartLine = Range("Acc.CounterpartyLine").Offset(iAccountNbr - intFirst + 1, 0)
ACC(iAccountNbr).iEntityLine = Range("Acc.EntityLine").Offset(iAccountNbr - intFirst + 1, 0)
ACC(iAccountNbr).strSystemId = Range("Acc.SystemId").Offset(iAccountNbr - intFirst + 1, 0)
ACC(iAccountNbr).strIBAN = Range("Acc.IBAN").Offset(iAccountNbr - intFirst + 1, 0)
ACC(iAccountNbr).strBBAN = Range("Acc.BBAN").Offset(iAccountNbr - intFirst + 1, 0)
ACC(iAccountNbr).strCurrency = Range("Acc.Currency").Offset(iAccountNbr - intFirst + 1, 0)
varCellContent = Range("Acc.IsCACIB").Offset(iAccountNbr - intFirst + 1, 0)
If varCellContent = True Or varCellContent = False Then
ACC(iAccountNbr).bolIsCACIB = varCellContent
Else
Print #2, "Critical : Wrong value for field 'IsCACIB', Line #" & iAccountNbr
End If
varCellContent = Range("Acc.notInOIL").Offset(iAccountNbr - intFirst + 1, 0)
If varCellContent = True Or varCellContent = False Then
ACC(iAccountNbr).bolNotInOIL = varCellContent
Else
Print #2, "Critical : Wrong value for field 'notInOIL', Line #" & iAccountNbr
End If
ACC(iAccountNbr).strLocalAccountId = Range("Acc.LocalAccountId").Offset(iAccountNbr - intFirst + 1, 0)
ACC(iAccountNbr).strAccountType = Range("Acc.Type").Offset(iAccountNbr - intFirst + 1, 0)
ACC(iAccountNbr).strAccountTypeName = Range("Acc.TypeName").Offset(iAccountNbr - intFirst + 1, 0)
If iAccountNbr > 2 Then
For i = intFirst To iAccountNbr - 1
If ACC(iAccountNbr).strSystemId = ACC(i).strSystemId Then
Print #2, "Critical : Duplication of System Id #" & ACC(i).strSystemId & _
" in line #" & iAccountNbr & " with line #" & i & ". => Duplicates in call 1 file will result."
End If
If ACC(iAccountNbr).strIBAN = ACC(i).strIBAN And ACC(i).strIBAN <> "" Then
Print #2, "Critical : Duplication of IBAN #" & ACC(i).strIBAN & _
" in line #" & iAccountNbr & " with line #" & i
End If
Next i
End If
iAccountNbr = iAccountNbr + 1
Wend
iAccountNbr = iAccountNbr - 1
End Sub
Sub LoadConfigs()
'-----------------------------------------------------------
'---- Loads the Configurations tab into a VBA table
'-----------------------------------------------------------
Dim varCellContent As Variant
Dim iLigne As Integer
Dim intFirst As Integer
iConfigNbr = 1
intFirst = Range("Cfg.Id").Row - 1
iLigne = intFirst
Sheets(CST_CONFIG_TAB).Select
While Range("Cfg.Id").Offset(iLigne - intFirst + 1, 0) <> ""
'---- Cfg.Acc.Index is the Number of the account line for the given config
If Range("Cfg.Acc.Index").Offset(iLigne - intFirst + 1, 0) = 1 Then
'---- The line is the first of the configuration, all settings must be read.
' If not, read only the reference of the accounts
CFG(iConfigNbr).strConfigId = Range("Cfg.Id").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strFormat = Range("Cfg.Format").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strFormatType = Range("Cfg.FormatType").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strChannel = Range("Cfg.Channel").Offset(iLigne - intFirst + 1, 0)
varCellContent = Range("Cfg.ChannelEntityLine").Offset(iLigne - intFirst + 1, 0)
If IsNumeric(varCellContent) Then
CFG(iConfigNbr).iChannelEntityLine = varCellContent
End If
varCellContent = Range("Cfg.Counterpart.Line").Offset(iLigne - intFirst + 1, 0)
If IsNumeric(varCellContent) Then
CFG(iConfigNbr).iCounterpartLine = varCellContent
End If
CFG(iConfigNbr).strCustomerBIC = Range("Cfg.CustomerBIC").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strCustomerDN = Range("Cfg.CustomerDN").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strIntradayFrequency = Range("Cfg.IntradayFrequency").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strEODFrequency = Range("Cfg.EODFrequency").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strLanguage = Range("Cfg.Language").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strThreshold = Range("cfg.Threshold").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strPaymentScope = Range("cfg.PaymentScope").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strAccountFormat = Range("Cfg.AccountFormat").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strContent = Range("Cfg.Content").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strEmptyReport = Range("Cfg.EmptyReport").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strTransactionCode = Range("Cfg.TransactionCode").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strTimeZone = Range("Cfg.TimeZone").Offset(iLigne - intFirst + 1, 0)
varCellContent = Range("Cfg.UTC").Offset(iLigne - intFirst + 1, 0)
If Not IsError(varCellContent) Then
CFG(iConfigNbr).strUTC = varCellContent
Else
Print #2, "Critical : UTC time not defined for config #", iConfigNbr
End If
varCellContent = Range("Cfg.FormatLine").Offset(iLigne - intFirst + 1, 0)
If IsNumeric(varCellContent) Then
CFG(iConfigNbr).iFormatLine = varCellContent
Else
Print #2, "Critical : Format line not found, Config #", iConfigNbr
End If
CFG(iConfigNbr).strAccountGroup = Range("Cfg.GroupByAccount").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strCounterpartGroup = Range("Cfg.GroupByCounterparty").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strPSRRestitutionMode = Range("cfg.PSR_RestitutionMode").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strPSRRemitterCounterpartName = Range("cfg.PSR_RemitterCounterpartName").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strPSRRemitterCounterpartRtsId = Range("cfg.PSR_RemitterCounterpartRtsId").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strPSRRemitterCounterpartRicosId = Range("cfg.PSR_RemitterCounterpartRicosId").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strPSRRemitterCounterpartLocalId = Range("cfg.PSR_RemitterCounterpartLocalId").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strPSRPostClearingReject = Range("cfg.PSR_PostClearingReject").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strRQTType = Range("Cfg.RQTType").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strRQTValue = Range("Cfg.RQTValue").Offset(iLigne - intFirst + 1, 0)
CFG(iConfigNbr).strBSBProductUniqueId = Range("cfg.BSB_ProductUniqueId").Offset(iLigne - intFirst + 1, 0)
iConfigNbr = iConfigNbr + 1
End If
'---- Add the related list of accounts
iCFG_ACC_Line(iConfigNbr - 1, Range("Cfg.Acc.Index").Offset(iLigne - intFirst + 1, 0)) = Val(Range("Cfg.Acc.Line").Offset(iLigne - intFirst + 1, 0))
iLigne = iLigne + 1
Wend
iConfigNbr = iConfigNbr - 1
End Sub
Function strBIC11(strBIC As String)
'---- Converts to BIC 11 if needed, adding 'XXX' to the current address
Select Case Len(strBIC)
Case 8
strBIC11 = strBIC & "XXX"
Case 11
strBIC11 = strBIC
Case Else
strBIC11 = "ERROR"
End Select
End Function
Function GenerateTypeValueSection(strType As String, strValue As String, strIndent As String) As String
'-----------------------------------------------------------
'#### returns the section with "type" / "value" keywords
'-----------------------------------------------------------
GenerateTypeValueSection = strIndent & "{" & vbCrLf
GenerateTypeValueSection = GenerateTypeValueSection & _
strIndent & vbTab & AddQuotes("type") & ": " & _
AddQuotes(strType) & "," & vbCrLf
GenerateTypeValueSection = GenerateTypeValueSection & _
strIndent & vbTab & AddQuotes("value") & ": " & _
AddQuotes(strValue) & vbCrLf
GenerateTypeValueSection = GenerateTypeValueSection & strIndent & "}"
End Function
Function GenerateSettingSection(strName As String, strDataType As String, _
strValue As String, strIndent As String) As String
'-----------------------------------------------------------
'#### returns the section with "name" / "dataType" / "value" keywords
'-----------------------------------------------------------
GenerateSettingSection = strIndent & "{" & vbCrLf
GenerateSettingSection = GenerateSettingSection & _
strIndent & vbTab & AddQuotes(CST_LBL_NAME) & ": " & AddQuotes(strName) & "," & vbCrLf
GenerateSettingSection = GenerateSettingSection & _
strIndent & vbTab & AddQuotes("dataType") & ": " & AddQuotes(strDataType) & "," & vbCrLf
GenerateSettingSection = GenerateSettingSection & _
strIndent & vbTab & AddQuotes("value") & ": " & AddQuotes(strValue) & vbCrLf
GenerateSettingSection = GenerateSettingSection & strIndent & "}"
End Function
Function GenerateCounterpartySection(iCtp As Integer, strTitle As String, strIndent As String) As String
'-----------------------------------------------------------
'#### returns the Counterparty section for a givent Counterrparty Id and the expected title
'-----------------------------------------------------------
' "channel_counterpart": {
' "name": "SEB SA",
' "rts_id": "0000000353",
' "ricos_id": "SC0000000106",
' "local_id": "0005761000"
' },
GenerateCounterpartySection = strIndent & AddQuotes(strTitle) & " : {" & vbCrLf
GenerateCounterpartySection = GenerateCounterpartySection & strIndent & vbTab & _
AddQuotes(CST_LBL_NAME) & " : " & _
AddQuotes(CTP(iCtp).strName) & "," & vbCrLf
GenerateCounterpartySection = GenerateCounterpartySection & strIndent & vbTab & _
AddQuotes("rts_id") & " : " & _
AddQuotes(Format(CTP(iCtp).strRTSId, "0000000000")) & "," & vbCrLf
If CTP(iCtp).strRICOSId <> "" Then
GenerateCounterpartySection = GenerateCounterpartySection & strIndent & vbTab & _
AddQuotes("ricos_id") & " : " & _
AddQuotes(CTP(iCtp).strRICOSId) & "," & vbCrLf
End If
GenerateCounterpartySection = GenerateCounterpartySection & strIndent & vbTab & _
AddQuotes("local_id") & " : " & _
AddQuotes(Format(CTP(iCtp).strLocalId, "0000000000")) & vbCrLf
GenerateCounterpartySection = GenerateCounterpartySection & strIndent & "}"
End Function
Function GenerateEntitySection(iEntity As Integer, strIndent As String) As String
'-----------------------------------------------------------
'#### returns the entity section for a givent Entity Id
'-----------------------------------------------------------
'{
' "name": "CA-CIB (PARIS)",
GenerateEntitySection = strIndent & "{" & vbCrLf
GenerateEntitySection = GenerateEntitySection & strIndent & vbTab & _
AddQuotes(CST_LBL_NAME) & " : " & _
AddQuotes(ENT(iEntity).strLabel) & "," & vbCrLf
' "aliases": [
' {
' "type": "Entity",
' "value": "14000"
' },
GenerateEntitySection = GenerateEntitySection & strIndent & vbTab & AddQuotes("aliases") & " : [" & vbCrLf
GenerateEntitySection = GenerateEntitySection & _
GenerateTypeValueSection("Entity", Format(ENT(iEntity).strCode, "00000"), strIndent & vbTab) & "," & vbCrLf
' {
' "type": "ESF Code",
' "value": "0845"
' },
GenerateEntitySection = GenerateEntitySection & _
GenerateTypeValueSection("ESF Code", Format(ENT(iEntity).strESFCode, "0000"), strIndent & vbTab) & "," & vbCrLf
' {
' "type": "UE Code",
' "value": "0010"
' },
GenerateEntitySection = GenerateEntitySection & _
GenerateTypeValueSection("UE Code", Format(ENT(iEntity).strUECode, "0000"), strIndent & vbTab) & "," & vbCrLf
' {
' "type": "ISO Code",
' "value": "FR"
' },
GenerateEntitySection = GenerateEntitySection & _
GenerateTypeValueSection("ISO Code", ENT(iEntity).strISOCode, strIndent & vbTab) & "," & vbCrLf
' {
' "type": "BIC",
' "value": "BSUIFRPP"
' },
GenerateEntitySection = GenerateEntitySection & _
GenerateTypeValueSection("BIC", ENT(iEntity).strBICCode, strIndent & vbTab) & "," & vbCrLf
' {
' "type": "Distinguished Name",
' "value": "ou=bsuifrpp,ou=corp,o=agrifrpp,o=swift"
' }
']
'}
GenerateEntitySection = GenerateEntitySection & _
GenerateTypeValueSection("Distinguished Name", ENT(iEntity).strDN, strIndent & vbTab) & vbCrLf
GenerateEntitySection = GenerateEntitySection & strIndent & vbTab & "]" & vbCrLf
GenerateEntitySection = GenerateEntitySection & strIndent & "}"
End Function
Function GenerateFullConfig(iCfg As Integer, strIndent As String) As String
'-----------------------------------------------------------
'#### Generate all settings of a given configuration Id
'-----------------------------------------------------------
Dim iCounterpartLine As Integer
Dim iAccountLine As Integer
Dim i As Integer, j As Integer
Dim iAccountSize As Integer
'--- Check for duplicated entities for all accounts
Dim iListEntityLine(CST_MAX_ENT_NBR + 3) As Integer
Dim bolFound As Boolean
Dim iEntityLine As Integer
'---- Labels
' There should be no label in the code, anyway the first version of this program had to be done quickly...
' Here are local constants that are not shared elsewhere
Const CST_LBL_CONF_ID = "configuration_id"
Const CST_LBL_ACC_ENT_SCOPE = "accounts_entities_scope"
Const CST_LBL_MAJ_STRING = "String"
Const CST_LBL_MIN_STRING = "string"
'#### 1. Generate first part with global settings
GenerateFullConfig = strIndent & "{" & vbCrLf
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & _
AddQuotes(CST_LBL_CONF_ID) & ": " & _
AddQuotes(CFG(iCfg).strConfigId) & "," & vbCrLf
'#### 2. Accounts entities scope
i = 1
'---- Check if at list one valid account has been related to the configuration
If iCFG_ACC_Line(iCfg, i) = 0 Then
' NOK, send an error
Print #2, "Critical : No account related to the config #" & CFG(iCfg).strConfigId
Else
' OK, keep one line per different entity
'[
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & _
AddQuotes(CST_LBL_ACC_ENT_SCOPE) & ": [" & vbCrLf
While iCFG_ACC_Line(iCfg, i) > 0 And i < CST_MAX_ACCPERCFG_NBR + 1
' Calculate the entity of the accounts
iAccountLine = iCFG_ACC_Line(iCfg, i)
iEntityLine = ACC(iAccountLine).iEntityLine
'---- Check if the entity is already referenced
bolFound = False
If i = 1 Then
iListEntityLine(1) = iEntityLine
Else
j = 1
While iListEntityLine(j) > 0
If iListEntityLine(j) = iEntityLine Then
bolFound = True
End If
j = j + 1
Wend
iListEntityLine(j) = iEntityLine
End If
If (i > 1) And Not bolFound Then
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & "," & vbCrLf
End If
' ENTITY_BLOCKS { xxx }
If Not bolFound Then
GenerateFullConfig = GenerateFullConfig & GenerateEntitySection(iEntityLine, strIndent & vbTab) & vbCrLf
End If
i = i + 1
Wend
']
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & "]," & vbCrLf
End If
'#### 3. Channel & counterpart
' "channel" :
' { -- Channels
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & _
AddQuotes("channel") & ": {" & vbCrLf
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & _
AddQuotes(CST_LBL_NAME) & ": " & _
AddQuotes(CFG(iCfg).strChannel & "_" & CFG(iCfg).strConfigId) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & _
AddQuotes("protocol_code") & ": " & _
AddQuotes(CFG(iCfg).strChannel) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & _
AddQuotes("direction") & ": " & _
AddQuotes("Incoming/Outgoing") & "," & vbCrLf
iCounterpartLine = CFG(iCfg).iCounterpartLine
If iCounterpartLine = 0 Then
GenerateFullConfig = GenerateFullConfig & vbCrLf
Print #2, "Error : No valid counterpart for config " & CFG(iCfg).strConfigId
Else
'---- If a counterparty has been defined, display the full section
GenerateFullConfig = GenerateFullConfig & _
GenerateCounterpartySection(iCounterpartLine, "channel_counterpart", strIndent & vbTab) & "," & vbCrLf
End If
'---- Entity of the channel
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & _
AddQuotes("entity") & ":" & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateEntitySection(CFG(iCfg).iChannelEntityLine, strIndent & vbTab & vbTab) & "," & vbCrLf
'#### Channel settings
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & _
AddQuotes("channel_settings") & ": [" & vbCrLf
'---- Depending on the channel, create the dedicated syntax and key
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab
Select Case CFG(iCfg).strChannel
Case "SNFI"
' Swift FileAct
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("Customer Distinguished Name", CST_LBL_MAJ_STRING, CFG(iCfg).strCustomerDN, strIndent & vbTab & vbTab) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("Entity Distinguished Name", CST_LBL_MAJ_STRING, ENT(CFG(iCfg).iChannelEntityLine).strDN, strIndent & vbTab & vbTab) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("Customer BIC", CST_LBL_MAJ_STRING, CFG(iCfg).strCustomerBIC, strIndent & vbTab & vbTab) & vbCrLf
Case "SNMS"
' Swift FIN
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("Customer BIC", CST_LBL_MAJ_STRING, CFG(iCfg).strCustomerBIC, strIndent & vbTab & vbTab) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("Entity BIC", CST_LBL_MAJ_STRING, ENT(CFG(iCfg).iChannelEntityLine).strBICCode, strIndent & vbTab & vbTab) & vbCrLf
Case "HHEB"
' EBICS
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("Customer ID", CST_LBL_MAJ_STRING, CTP(CFG(iCfg).iCounterpartLine).strRTSId, strIndent & vbTab & vbTab) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("Host ID", CST_LBL_MAJ_STRING, ENT(CFG(iCfg).iChannelEntityLine).strBICCode, strIndent & vbTab & vbTab) & vbCrLf
Case "OPNT"
' Optim.Net => "Optim Subscription ID" = dummy
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("Optim Subscription ID", CST_LBL_MAJ_STRING, "0123ABCDE", strIndent & vbTab & vbTab) & "," & vbCrLf
' Optim.Net => "Customer Ricos SC"
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("Customer Ricos SC", CST_LBL_MAJ_STRING, CTP(CFG(iCfg).iCounterpartLine).strRICOSId, strIndent & vbTab & vbTab)
Case Else
' Others (Host to host) => CustomerID
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("CustomerID", CST_LBL_MAJ_STRING, CFG(iCfg).strCustomerBIC, strIndent & vbTab & vbTab)
End Select
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & "]," & vbCrLf
' "aliases": []
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & """aliases"": []" & vbCrLf
' } -- Channels
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & "}," & vbCrLf
'#### 4. format & its sub-sections
' { - format
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & _
AddQuotes("format") & ": {" & vbCrLf
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & _
AddQuotes("code") & ": " & _
AddQuotes(FMT(CFG(iCfg).iFormatLine).strCode) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & _
AddQuotes(CST_LBL_NAME) & ": " & _
AddQuotes(FMT(CFG(iCfg).iFormatLine).strName) & vbCrLf
' } - format
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & _
"}," & vbCrLf
'#### 5. Product Type
If Len(CFG(iCfg).strRQTValue) > 1 Then
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & _
AddQuotes("product_code") & ": {" & vbCrLf
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & _
AddQuotes("name") & ": " & _
AddQuotes(CFG(iCfg).strRQTType) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & _
AddQuotes("value") & ": " & _
AddQuotes(CFG(iCfg).strRQTValue) & vbCrLf
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & _
"}," & vbCrLf
End If
'#### 6. Accounts & its sub-sections
' [ - Accounts
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & _
AddQuotes("accounts") & ": [" & vbCrLf
'---- Check if at list one valid account has been related to the configuration
i = 1
If iCFG_ACC_Line(iCfg, i) > 0 Then
While iCFG_ACC_Line(iCfg, i) > 0 And i <= CST_MAX_ACCPERCFG_NBR
iAccountLine = iCFG_ACC_Line(iCfg, i)
' Calculate the entity of the account
iEntityLine = ACC(iAccountLine).iEntityLine
' Calculate the counterpart of the account
iCounterpartLine = ACC(iAccountLine).iCounterpartLine
' {
' "Account Identifier": "022000114145",
' "IBAN": "FR7631489000100022000114145",
' "BIC": "BSUIFRPPXXX",
' "Currency": "EUR",
If i > 1 Then
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & _
"," & vbCrLf
End If
' { - Account
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & _
"{" & vbCrLf
'---- Caution, size is 11 for France and 14 for ISIS world
If ENT(iEntityLine).strCode = "14000" Then
iAccountSize = 11
Else
iAccountSize = 14
End If
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & _
AddQuotes("AccountNumber") & ": " & _
AddQuotes(Format(ACC(iAccountLine).strSystemId, String(iAccountSize, "0"))) & "," & vbCrLf
'---- IBAN
If ACC(iAccountLine).strIBAN <> "" Then
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & _
AddQuotes("IBAN") & ": " & _
AddQuotes(ACC(iAccountLine).strIBAN) & "," & vbCrLf
End If
'---- BBAN
If ACC(iAccountLine).strBBAN <> "" Then
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & _
AddQuotes("Local") & ": " & _
AddQuotes(ACC(iAccountLine).strBBAN) & "," & vbCrLf
End If
'---- BIC
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & _
AddQuotes("bic") & ": " & _
AddQuotes(ENT(iEntityLine).strBICCode) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & _
AddQuotes("currency") & ": " & _
AddQuotes(ACC(iAccountLine).strCurrency) & "," & vbCrLf
' "Counterpart":{
' "Name": "AUCHAN",
' "Local ID": "0000001",
' "RTS ID": "0000001"
' },
GenerateFullConfig = GenerateFullConfig & _
GenerateCounterpartySection(iCounterpartLine, "counterpart", strIndent & vbTab & vbTab) & "," & vbCrLf
' "Entity" :
' {
' "Entity Name" : "CACIB (PARIS)",
' .....
' }
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & _
AddQuotes("entity") & ": " & vbCrLf
GenerateFullConfig = GenerateFullConfig & GenerateEntitySection(iEntityLine, strIndent & vbTab & vbTab) & "," & vbCrLf
'---- IsCACIB - Used for passthrough (3rd party banks)
If ACC(iAccountLine).bolIsCACIB Then
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & _
AddQuotes("is_cacib") & ": true" & vbCrLf
Else
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & vbTab & _
AddQuotes("is_cacib") & ": false" & vbCrLf
End If
' } - Account
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & "}" & vbCrLf
i = i + 1
Wend
End If
' ] - Accounts
GenerateFullConfig = GenerateFullConfig & strIndent & "]," & vbCrLf
'#### 7. agents
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & _
AddQuotes("agents") & ": []," & vbCrLf
'#### 8. Settings
' "settings": [
' {
' "FREQUENCY": "['8am','12pm','6pm']",
' "LANGUAGE": "ENGLISH",
' "ACCOUNT_FORMAT": "IBAN",
' "CONTENT": "DIFFERENTIAL",
' "EMPTY_REPORT": "NOT_SENT",
' "TRANSACTION_CODE": "HARMONIZED",
' "TIMEZONE": "Europe/Paris",
' "REPORT_GROUPING_PER_ACCOUNT": "MULTIPLE_ACCOUNTS",
' "REPORT_GROUPING_PER_COUNTERPARTY": "MULTIPLE_COUNTERPARTIES"
' }
' ]
' [ {
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & _
AddQuotes("settings") & ": [" & vbCrLf
'---- The section structure depends on the kind of format (Intraday, EOD)
Select Case CFG(iCfg).strFormatType
Case CST_PT_INTRADAY_CACIB
'---- Parameter for OPTIM (CRLYSGxxx), concerns both Intraday and End of Period
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection(Range("OptimBICRecipientKey"), CST_LBL_MIN_STRING, Range("OptimBICRecipientValue"), strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("FREQUENCY", CST_LBL_MIN_STRING, CFG(iCfg).strIntradayFrequency, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("TRANSACTION_CODE", CST_LBL_MIN_STRING, CFG(iCfg).strTransactionCode, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("LANGUAGE", CST_LBL_MIN_STRING, CFG(iCfg).strLanguage, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("REPORT_GROUPING_PER_COUNTERPARTY", CST_LBL_MIN_STRING, CFG(iCfg).strCounterpartGroup, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("ACCOUNT_FORMAT", CST_LBL_MIN_STRING, CFG(iCfg).strAccountFormat, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("REPORT_GROUPING_PER_ACCOUNT", CST_LBL_MIN_STRING, CFG(iCfg).strAccountGroup, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("CONTENT", CST_LBL_MIN_STRING, CFG(iCfg).strContent, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("EMPTY_REPORT", CST_LBL_MIN_STRING, CFG(iCfg).strEmptyReport, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("TIMEZONE", CST_LBL_MIN_STRING, CFG(iCfg).strTimeZone, strIndent) & vbCrLf
Case CST_PT_EOD_CACIB
'---- Parameter for OPTIM (CRLYSGxxx), concerns both Intraday and End of Period
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection(Range("OptimBICRecipientKey"), CST_LBL_MIN_STRING, Range("OptimBICRecipientValue"), strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("FREQUENCY", CST_LBL_MIN_STRING, CFG(iCfg).strEODFrequency, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("TRANSACTION_CODE", CST_LBL_MIN_STRING, CFG(iCfg).strTransactionCode, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("LANGUAGE", CST_LBL_MIN_STRING, CFG(iCfg).strLanguage, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("REPORT_GROUPING_PER_COUNTERPARTY", CST_LBL_MIN_STRING, CFG(iCfg).strCounterpartGroup, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("ACCOUNT_FORMAT", CST_LBL_MIN_STRING, CFG(iCfg).strAccountFormat, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("REPORT_GROUPING_PER_ACCOUNT", CST_LBL_MIN_STRING, CFG(iCfg).strAccountGroup, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("EMPTY_REPORT", CST_LBL_MIN_STRING, CFG(iCfg).strEmptyReport, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("TIMEZONE", CST_LBL_MIN_STRING, CFG(iCfg).strTimeZone, strIndent) & vbCrLf
Case CST_PT_PSR_SCT
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("REMITTER_COUNTERPART_NAME", CST_LBL_MIN_STRING, CFG(iCfg).strPSRRemitterCounterpartName, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("REMITTER_COUNTERPART_RTSID", CST_LBL_MIN_STRING, CFG(iCfg).strPSRRemitterCounterpartRtsId, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("REMITTER_COUNTERPART_RICOSID", CST_LBL_MIN_STRING, CFG(iCfg).strPSRRemitterCounterpartRicosId, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("REMITTER_COUNTERPART_LOCALID", CST_LBL_MIN_STRING, CFG(iCfg).strPSRRemitterCounterpartLocalId, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("RESTITUTION_MODE", CST_LBL_MIN_STRING, CFG(iCfg).strPSRRestitutionMode, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("LANGUAGE", CST_LBL_MIN_STRING, CFG(iCfg).strLanguage, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("POST_CLEARING_REJECT", CST_LBL_MIN_STRING, CFG(iCfg).strPSRPostClearingReject, strIndent) & vbCrLf
Case CST_PT_BSB
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("LANGUAGE", CST_LBL_MIN_STRING, CFG(iCfg).strLanguage, strIndent) & vbCrLf
Case CST_PT_DBT_ADV
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("PAYMENT_SCOPE", CST_LBL_MIN_STRING, CFG(iCfg).strPaymentScope, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("THRESHOLD", CST_LBL_MIN_STRING, CFG(iCfg).strThreshold, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("LANGUAGE", CST_LBL_MIN_STRING, CFG(iCfg).strLanguage, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("ACCOUNT_FORMAT", CST_LBL_MIN_STRING, CFG(iCfg).strAccountFormat, strIndent) & vbCrLf
Case CST_PT_CRDT_ADV
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("PAYMENT_SCOPE", CST_LBL_MIN_STRING, CFG(iCfg).strPaymentScope, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("THRESHOLD", CST_LBL_MIN_STRING, CFG(iCfg).strThreshold, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("LANGUAGE", CST_LBL_MIN_STRING, CFG(iCfg).strLanguage, strIndent) & "," & vbCrLf
GenerateFullConfig = GenerateFullConfig & _
GenerateSettingSection("ACCOUNT_FORMAT", CST_LBL_MIN_STRING, CFG(iCfg).strAccountFormat, strIndent) & vbCrLf
End Select
' ]
GenerateFullConfig = GenerateFullConfig & strIndent & vbTab & _
"]" & vbCrLf
'#### Close JSON structure
GenerateFullConfig = GenerateFullConfig & strIndent & "}" & vbCrLf
End Function
Sub GenerateCall2File()
'-----------------------------------------------------------
'#### Generates the file for OSM call 2
'-----------------------------------------------------------
Dim i As Integer
Dim strIndent As String
Dim strPath As String
Dim strSuffix As String
strPath = ThisWorkbook.FullName
i = InStrRev(strPath, "\")
strPath = Left(strPath, i)
strSuffix = Sheets("Generation").Cells(4, 2)
'---- Output file
Open strPath & "OSMCall2_GetConfig" & strSuffix & ".json" For Output As #1
'---- Print header
Print #1, "{"
Print #1, vbTab & AddQuotes(CST_LBL_CALITRACK) & ": " & _
AddQuotes("MIC1-2907: Account Statement - Intraday - MT942 - OSM Call 2") & ","
Print #1, vbTab & AddQuotes(CST_LBL_OSM_MOCK) & ": {"
Print #1, vbTab & vbTab & AddQuotes(CST_LBL_METHOD) & ": ["
Print #1, vbTab & vbTab & vbTab & "{"
Print #1, vbTab & vbTab & vbTab & _
AddQuotes(CST_LBL_NAME) & ": " & _
AddQuotes("getOsmCall2") & ","
Print #1, vbTab & vbTab & vbTab & AddQuotes(CST_LBL_CASE) & ": ["
strIndent = vbTab & vbTab & vbTab & vbTab
'---- Loop on Local accounts
For i = 1 To iConfigNbr
' Write request parameter
Print #1, strIndent & "{"
Print #1, vbTab & strIndent & AddQuotes(CST_LBL_REQUEST) & ": {"
Print #1, vbTab & vbTab & strIndent & _
AddQuotes("ConfigurationId") & ": " & _
AddQuotes(CFG(i).strConfigId)
Print #1, vbTab & strIndent & "},"
' Write answer
Print #1, vbTab & strIndent & AddQuotes(CST_LBL_RESPONSE) & ": {"
'---- Display full settings of config number i
Print #1, vbTab & vbTab & strIndent & _
AddQuotes(CST_LBL_CONFIG_LIST) & ": [" & _
AddQuotes(CFG(i).strConfigId) & "]"
Print #1, vbTab & strIndent & "}"
Print #1, strIndent & "}";
If i <> iConfigNbr Then
Print #1, ", "
Else
Print #1, ""
End If
Next i
'---- Print footer
Print #1, vbTab & vbTab & vbTab & vbTab & "]"
Print #1, vbTab & vbTab & vbTab & "}"
Print #1, vbTab & vbTab & "]"
Print #1, vbTab & "}"
Print #1, "}"
Close #1
End Sub
Sub GenerateCall1File()
'-----------------------------------------------------------
'#### Generates the file for OSM call 1
'-----------------------------------------------------------
Dim iAcct As Integer
' For the current account, list of all related subscriptions
Dim i As Integer
Dim j As Integer
Dim iCfgLst(CST_MAX_CFG_NBR) As Integer
Dim bolFirst As Boolean
Dim strIndent As String
Dim strPath As String
Dim strSuffix As String
'{
' "calitrack": "MIC1-2878: Account Statement - Intraday - MT942 - OSM Call 1",
' "osm-mock": {
' "method": [
' {
' "name": "getOsmCall1",
' "case": [
strPath = ThisWorkbook.FullName
i = InStrRev(strPath, "\")
strPath = Left(strPath, i)
strSuffix = Sheets("Generation").Cells(4, 2)
bolFirst = True
'---- Output file
Open strPath & "OSMCall1_Check" & strSuffix & ".json" For Output As #1
'---- Print header
Print #1, "{"
Print #1, vbTab & AddQuotes(CST_LBL_CALITRACK) & ": " & _
AddQuotes("MIC1-2878: Account Statement - Intraday - MT942 - OSM Call 1") & ","
Print #1, vbTab & AddQuotes(CST_LBL_OSM_MOCK) & ": {"
Print #1, vbTab & vbTab & AddQuotes(CST_LBL_METHOD) & ": ["
Print #1, vbTab & vbTab & vbTab & "{"
Print #1, vbTab & vbTab & vbTab & _
AddQuotes(CST_LBL_NAME) & ": " & _
AddQuotes("getOsmCall1") & ","
Print #1, vbTab & vbTab & vbTab & AddQuotes(CST_LBL_CASE) & ": ["
strIndent = vbTab & vbTab & vbTab & vbTab
'---- Loop on all possible accounts
For iAcct = 3 To iAccountNbr
Call GenerateCall2Subsection(iAcct, CST_PT_INTRADAY_CACIB, CST_PC_INTRADAY_CACIB, strIndent, bolFirst)
Call GenerateCall2Subsection(iAcct, CST_PT_EOD_CACIB, CST_PC_EOD_CACIB, strIndent, bolFirst)
Next iAcct
' ]
' }
' ]
' }
'}
'---- Print footer
Print #1, vbTab & vbTab & vbTab & vbTab & "]"
Print #1, vbTab & vbTab & vbTab & "}"
Print #1, vbTab & vbTab & "]"
Print #1, vbTab & "}"
Print #1, "}"
Close #1
End Sub
Sub GenerateCall2Subsection(iAcct As Integer, strProductType As String, strProductCode As String, _
ByRef strIndent As String, ByRef bolFirst As Boolean)
Dim i As Integer
Dim j As Integer
Dim iCfgLst(CST_MAX_CFG_NBR) As Integer
Dim iCfg As Integer
'---- For each account, loop on all configurations and check if they are attached to it
' The result is stored in table "iCfgLst"
i = 0
For iCfg = 1 To iConfigNbr
j = 1
While j <= CST_MAX_ACCPERCFG_NBR And iCFG_ACC_Line(iCfg, j) <> 0
If iCFG_ACC_Line(iCfg, j) = iAcct And CFG(iCfg).strFormatType = strProductType Then
i = i + 1
iCfgLst(i) = iCfg
End If
j = j + 1
Wend
Next iCfg
'---- if at least one accout is found, add the relevant set of lines in the file
If i > 0 Then
If Not bolFirst Then
Print #1, strIndent & ","
End If
bolFirst = False
'### @WS - 28/03/2018 - New structure
'{
' "request": {
' "AccountType": "AccountNumber",
' "AccountID": "00223366847",
' "AccountBIC": "BSUIFRPPXXX",
' "ProductCategory": "Account Statement",
' "ProductType": "ITD-ACCT-RPT-CACIB"
' },
Print #1, strIndent & "{"
Print #1, strIndent & vbTab & AddQuotes(CST_LBL_REQUEST) & ": {"
' @WS - 28/03/2018 - Change of settings to be aligned with OIL
' Print #1, strIndent & vbTab & vbTab & _
' AddQuotes("AccountType") & ": " & _
' AddQuotes("Local") & ", "
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("AccountType") & ": " & _
AddQuotes("AccountNumber") & ", "
' @WS - 28/03/2018 - Change of structure for real french 11 chars / ISIS accounts 14 chars
If ENT(ACC(iAcct).iEntityLine).strCode = "14000" Then
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("AccountId") & ": " & _
AddQuotes(Format(ACC(iAcct).strSystemId, String(11, "0"))) & ","
Else
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("AccountId") & ": " & _
AddQuotes(Format(ACC(iAcct).strSystemId, String(14, "0"))) & ","
End If
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("AccountBIC") & ": " & _
AddQuotes(ENT(ACC(iAcct).iEntityLine).strBICCode) & ","
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("ProductCategory") & ": " & _
AddQuotes("Account Statement") & ","
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("ProductType") & ": " & _
AddQuotes(strProductCode) & "},"
' "response": {
' "ConfigList": [
' "R1",
' "R6"
' ]
' }
'}
Print #1, strIndent & vbTab & AddQuotes(CST_LBL_RESPONSE) & ": {"
Print #1, strIndent & vbTab & vbTab & AddQuotes(CST_LBL_CONFIG_LIST) & ": ["
For j = 1 To i
If j > 1 Then Print #1, ","
Print #1, strIndent & vbTab & vbTab & vbTab & AddQuotes(CFG(iCfgLst(j)).strConfigId);
Next j
Print #1, "]"
Print #1, strIndent & vbTab & "}"
Print #1, strIndent & "}"
End If
End Sub
'-------- ABT-20181207-START
Sub GeneratePSRCall1File()
'-----------------------------------------------------------
'#### Generates the file for specific OSM call 1 for PSR
'-----------------------------------------------------------
Dim iCpty As Integer
' For the current counterparty, list of all related configurations
Dim i As Integer
Dim j As Integer
Dim iCfgLst(CST_MAX_CFG_NBR) As Integer
Dim bolFirst As Boolean
Dim strIndent As String
Dim strPath As String
Dim strSuffix As String
Dim iCfg As Integer
'{
' "calitrack": "Payment Status Report - PSR SCT - Specific OSM PSR Call 1",
' "osm-mock": {
' "method": [
' {
' "name": "getOsmCall1ForPsr",
' "case": [
strPath = ThisWorkbook.FullName
i = InStrRev(strPath, "\")
strPath = Left(strPath, i)
strSuffix = Sheets("Generation").Cells(4, 2)
bolFirst = True
'---- Output file
Open strPath & "OSMCall1_PSR_Check" & strSuffix & ".json" For Output As #1
'---- Print header
Print #1, "{"
Print #1, vbTab & AddQuotes(CST_LBL_CALITRACK) & ": " & _
AddQuotes("Payment Status Report - PSR SCT - Specific OSM PSR Call 1") & ","
Print #1, vbTab & AddQuotes(CST_LBL_OSM_MOCK) & ": {"
Print #1, vbTab & vbTab & AddQuotes(CST_LBL_METHOD) & ": ["
Print #1, vbTab & vbTab & vbTab & "{"
Print #1, vbTab & vbTab & vbTab & _
AddQuotes(CST_LBL_NAME) & ": " & _
AddQuotes("getOsmCall1ForPsr") & ","
Print #1, vbTab & vbTab & vbTab & AddQuotes(CST_LBL_CASE) & ": ["
strIndent = vbTab & vbTab & vbTab & vbTab
'---- Loop on all possible counterparties
For iCpty = 3 To iCounterpartNbr
'--Display-- Print #2, "##### CTP(iCpty).strName: " & CTP(iCpty).strName & _
'--Display-- " CTP(iCpty).strRTSId: " & CTP(iCpty).strRTSId & _
'--Display-- " CTP(iCpty).strLocalId : " & CTP(iCpty).strLocalId & _
'--Display-- " CTP(iCpty).strRICOSId: " & CTP(iCpty).strRICOSId
'---- Loop on all relevant configs
iCfg = 1
i = 0
While iCfg <= iConfigNbr
'--Display-- Print #2, "####### CFG(iCfg).strPSRRemitterCounterpartName:" & CFG(iCfg).strPSRRemitterCounterpartName & _
'--Display-- " CFG(iCfg).strFormatType: " & CFG(iCfg).strFormatType & " " & CST_PT_PSR_SCT
If CTP(iCpty).strName = CFG(iCfg).strPSRRemitterCounterpartName _
And CFG(iCfg).strFormatType = CST_PT_PSR_SCT Then
i = i + 1
iCfgLst(i) = iCfg
End If
iCfg = iCfg + 1
Wend
'---- if at least one counterpart is found, add the relevant set of lines in the file
If i > 0 Then
If Not bolFirst Then
Print #1, strIndent & ","
End If
bolFirst = False
'-------- ABT-20190114-START --- creation of 3 specific requests/responses, one per type of remitter counterpart since a request parameter cannot be null when the mock is called
'---- 1 - Call with rts ID
'{
' "request": {
' "remitterCounterpartType": "rts",
' "remitterCounterpartValue": "9900000001",
' "ProductType": "PSR-SCT"
' },
Print #1, strIndent & "{"
Print #1, strIndent & vbTab & AddQuotes(CST_LBL_REQUEST) & ": {"
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("remitterCounterpartType") & ": " & AddQuotes("rts") & ","
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("remitterCounterpartValue") & ": " & _
AddQuotes(Format(CTP(iCpty).strRTSId, String(10, "0"))) & ","
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("ProductType") & ": " & _
AddQuotes(CST_PT_PSR_SCT) & "}" & ","
' "response": {
' "ConfigList": [
' "R1",
' "R6"
' ]
' }
'}
Print #1, strIndent & vbTab & AddQuotes(CST_LBL_RESPONSE) & ": {"
Print #1, strIndent & vbTab & vbTab & AddQuotes(CST_LBL_CONFIG_LIST) & ": ["
For j = 1 To i
If j > 1 Then Print #1, ","
Print #1, strIndent & vbTab & vbTab & vbTab & AddQuotes(CFG(iCfgLst(j)).strConfigId);
Next j
Print #1, "]"
Print #1, strIndent & vbTab & "}"
Print #1, strIndent & "},"
'---- 2 - Call with ricos ID
'{
' "request": {
' "remitterCounterpartType": "ricos",
' "remitterCounterpartValue": "SC9000021988",
' "ProductType": "PSR-SCT"
' },
Print #1, strIndent & "{"
Print #1, strIndent & vbTab & AddQuotes(CST_LBL_REQUEST) & ": {"
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("remitterCounterpartType") & ": " & AddQuotes("ricos") & ","
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("remitterCounterpartValue") & ": " & _
AddQuotes(Format(CTP(iCpty).strRICOSId, String(12, "0"))) & ","
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("ProductType") & ": " & _
AddQuotes(CST_PT_PSR_SCT) & "}" & ","
' "response": {
' "ConfigList": [
' "R1",
' "R6"
' ]
' }
'}
Print #1, strIndent & vbTab & AddQuotes(CST_LBL_RESPONSE) & ": {"
Print #1, strIndent & vbTab & vbTab & AddQuotes(CST_LBL_CONFIG_LIST) & ": ["
For j = 1 To i
If j > 1 Then Print #1, ","
Print #1, strIndent & vbTab & vbTab & vbTab & AddQuotes(CFG(iCfgLst(j)).strConfigId);
Next j
Print #1, "]"
Print #1, strIndent & vbTab & "}"
Print #1, strIndent & "},"
'---- 3 - Call with local ID
'{
' "request": {
' "remitterCounterpartType": "local",
' "remitterCounterpartValue": "9800000001",
' "ProductType": "PSR-SCT"
' },
Print #1, strIndent & "{"
Print #1, strIndent & vbTab & AddQuotes(CST_LBL_REQUEST) & ": {"
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("remitterCounterpartType") & ": " & AddQuotes("local") & ","
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("remitterCounterpartValue") & ": " & _
AddQuotes(Format(CTP(iCpty).strLocalId, String(10, "0"))) & ","
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("ProductType") & ": " & _
AddQuotes(CST_PT_PSR_SCT) & "}" & ","
' "response": {
' "ConfigList": [
' "R1",
' "R6"
' ]
' }
'}
Print #1, strIndent & vbTab & AddQuotes(CST_LBL_RESPONSE) & ": {"
Print #1, strIndent & vbTab & vbTab & AddQuotes(CST_LBL_CONFIG_LIST) & ": ["
For j = 1 To i
If j > 1 Then Print #1, ","
Print #1, strIndent & vbTab & vbTab & vbTab & AddQuotes(CFG(iCfgLst(j)).strConfigId);
Next j
Print #1, "]"
Print #1, strIndent & vbTab & "}"
Print #1, strIndent & "}"
'-------- ABT-20190114-END ---
End If
Next iCpty
'---- Print footer
Print #1, vbTab & vbTab & vbTab & vbTab & "]"
Print #1, vbTab & vbTab & vbTab & "}"
Print #1, vbTab & vbTab & " ]"
Print #1, vbTab & "}"
Print #1, "}"
Close #1
End Sub
'------- ABT-20181207-END
Sub GenerateBSBCall1File()
'-----------------------------------------------------------
'#### Generates the file for specific OSM call 1 for BSB
'-----------------------------------------------------------
Dim iPdty As Integer
Dim i As Integer
Dim j As Integer
Dim iCfgLst(CST_MAX_PDT_NBR) As Integer
Dim bolFirst As Boolean
Dim strIndent As String
Dim strPath As String
Dim strSuffix As String
Dim iCfg As Integer
'{
' "calitrack": "Payment Status Report - BSB - Specific OSM BSB Call 1",
' "osm-mock": {
' "method": [
' {
' "name": "getSubscriptionByProductUniqueId",
' "case": [
strPath = ThisWorkbook.FullName
i = InStrRev(strPath, "\")
strPath = Left(strPath, i)
strSuffix = Sheets("Generation").Cells(4, 2)
bolFirst = True
'---- Output file
Open strPath & "OSMCall1_BSB_Check" & strSuffix & ".json" For Output As #1
'---- Print header
Print #1, "{"
Print #1, vbTab & AddQuotes(CST_LBL_CALITRACK) & ": " & _
AddQuotes("Payment Status Report - BSB - Specific OSM BSB Call 1") & ","
Print #1, vbTab & AddQuotes(CST_LBL_OSM_MOCK) & ": {"
Print #1, vbTab & vbTab & AddQuotes(CST_LBL_METHOD) & ": ["
Print #1, vbTab & vbTab & vbTab & "{"
Print #1, vbTab & vbTab & vbTab & _
AddQuotes(CST_LBL_NAME) & ": " & _
AddQuotes("getSubscriptionByProductUniqueId") & ","
Print #1, vbTab & vbTab & vbTab & AddQuotes(CST_LBL_CASE) & ": ["
strIndent = vbTab & vbTab & vbTab & vbTab
'---- Loop on all possible products
For iPdty = 2 To iProductNbr
'--Display-- " PDT(iPdty).strUniqueId: " & PDT(iPdty).strUniqueId
'---- Loop on all relevant configs
iCfg = 1
i = 0
While iCfg <= iConfigNbr
If PDT(iPdty).strUniqueId = CFG(iCfg).strBSBProductUniqueId _
And CFG(iCfg).strFormatType = CST_PT_BSB Then
i = i + 1
iCfgLst(i) = iCfg
End If
iCfg = iCfg + 1
Wend
'---- if at least one counterpart is found, add the relevant set of lines in the file
If i > 0 Then
If Not bolFirst Then
Print #1, strIndent & ","
End If
bolFirst = False
'{
' "request": {
' "product_unique_id": "13658"
' },
Print #1, strIndent & "{"
Print #1, strIndent & vbTab & AddQuotes(CST_LBL_REQUEST) & ":{"
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("product_unique_id") & ": " & _
AddQuotes(Format(PDT(iPdty).strUniqueId, String(5, "0"))) & "}" & ","
' "response" {
' "ConfigList": [
' "BSB_00001"
' ]
' }
'}
Print #1, strIndent & vbTab & AddQuotes(CST_LBL_RESPONSE) & ": {"
Print #1, strIndent & vbTab & vbTab & AddQuotes(CST_LBL_CONFIG_LIST) & ": ["
For j = 1 To i
If j > 1 Then Print #1, ","
Print #1, strIndent & vbTab & vbTab & vbTab & AddQuotes(CFG(iCfgLst(j)).strConfigId);
Next j
Print #1, "]"
Print #1, strIndent & vbTab & "}"
Print #1, strIndent & "}"
End If
Next iPdty
' ]
' }
' ]
' }
'}
'---- Print footer
Print #1, vbTab & vbTab & vbTab & "]"
Print #1, vbTab & vbTab & vbTab & "}"
Print #1, vbTab & vbTab & "]"
Print #1, vbTab & "}"
Print #1, "}"
Close #1
End Sub
Sub GenerateEmptyCallFile_old()
'-----------------------------------------------------------
'#### Generates the file for OSM empty call
'-----------------------------------------------------------
' Previous version that generates file per UTC time
'-----------------------------------------------------------
Dim strIndent As String
Dim strPath As String
Dim strSuffix As String
Dim i As Integer
Dim sTimeZone As Single
Dim strUTC As String
Dim strUTCOut As String
Dim strbolFirst As Boolean
Dim bolFirst As Boolean
Dim iCfg As Integer
'---- List of relevant configuration that have the same UTC time and "SENT" option
Dim iLstCfg(CST_MAX_CFG_NBR) As Integer
'{
' "calitrack": "OSM Empty Report Call",
' "osm-mock": {
' "method": [
' {
' "name": "getOsmEmptyReportCall",
' "case": [
strPath = ThisWorkbook.FullName
i = InStrRev(strPath, "\")
strPath = Left(strPath, i)
strSuffix = Sheets("Generation").Cells(4, 2)
bolFirst = True
'---- Output file
Open strPath & "OSMCall3_Empty" & strSuffix & ".json" For Output As #1
'---- Print header
Print #1, "{"
Print #1, vbTab & _
AddQuotes(CST_LBL_CALITRACK) & ": " & _
AddQuotes("OSM Empty Report Call") & ","
Print #1, vbTab & AddQuotes(CST_LBL_OSM_MOCK) & ": {"
Print #1, vbTab & vbTab & AddQuotes(CST_LBL_METHOD) & ": ["
Print #1, vbTab & vbTab & vbTab & "{"
Print #1, vbTab & vbTab & vbTab & _
AddQuotes(CST_LBL_NAME) & ": " & _
AddQuotes("getOsmEmptyReportCall") & ","
Print #1, vbTab & vbTab & vbTab & AddQuotes(CST_LBL_CASE) & ": ["
strIndent = vbTab & vbTab & vbTab & vbTab
'---- Loop on possible timezones
For sTimeZone = -11 To 12 Step 0.25
'---- Calculate the UTC string that should be searched
strUTC = "UTC/GMT"
strUTCOut = "UTC"
If sTimeZone > 0 Then
strUTC = strUTC & " +" & Format(sTimeZone / 24, "hh:mm")
strUTCOut = strUTCOut & "+" & Format(sTimeZone / 24, "hh:mm")
Else
If sTimeZone < 0 Then
strUTC = strUTC & " -" & Format(sTimeZone / 24, "hh:mm")
strUTCOut = strUTCOut & "-" & Format(sTimeZone / 24, "hh:mm")
Else
End If
End If
'---- Loop on all relevant configs
iCfg = 1
i = 0
While iCfg <= iConfigNbr
If (CFG(iCfg).strUTC = strUTC) And (CFG(iCfg).strEmptyReport = CST_OPT_SENT) Then
i = i + 1
iLstCfg(i) = iCfg
End If
iCfg = iCfg + 1
Wend
'---- If at least one config has been found, add a new section in the mock file
If i > 0 Then
If Not bolFirst Then
Print #1, strIndent & ","
End If
bolFirst = False
'{
' "request": {
' "ProductCategory": "Account Statement",
' "ProductType": "Intraday CACIB",
' "EmptyReport": "SENT",
' "Timezone Offset": "UTC+02:00"
' },
Print #1, strIndent & "{"
Print #1, strIndent & vbTab & AddQuotes(CST_LBL_REQUEST) & ":{"
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("ProductCategory") & ": " & _
AddQuotes("Account Statement") & ","
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("ProductType") & ": " & _
AddQuotes(CST_PC_INTRADAY_CACIB) & ","
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("Empty report") & ": " & _
AddQuotes(CST_OPT_SENT) & ","
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("Timezone Offset") & ": " & _
AddQuotes(strUTCOut) & "},"
' "response": {
' "ConfigList": [
' "R1",
' "R26"
' ]
' }
'},
Print #1, strIndent & vbTab & AddQuotes(CST_LBL_RESPONSE) & ":{"
Print #1, strIndent & vbTab & vbTab & AddQuotes(CST_LBL_CONFIG_LIST) & ": [";
For iCfg = 1 To i
If iCfg > 1 Then
Print #1, ",";
End If
Print #1, AddQuotes(CFG(iLstCfg(iCfg)).strConfigId);
Next iCfg
Print #1, "]"
Print #1, strIndent & vbTab & "}"
Print #1, strIndent & "}"
End If
Next sTimeZone
' ]
' }
' ]
' }
'}
'---- Print footer
Print #1, vbTab & vbTab & vbTab & vbTab & "]"
Print #1, vbTab & vbTab & vbTab & "}"
Print #1, vbTab & vbTab & "]"
Print #1, vbTab & "}"
Print #1, "}"
Close #1
End Sub
Sub GenerateEmptyCallFile()
'-----------------------------------------------------------
'#### Generates the file for OSM empty call
'-----------------------------------------------------------
' New version that generates file per Timezone
'-----------------------------------------------------------
Dim strIndent As String
Dim strPath As String
Dim strSuffix As String
Dim i As Integer
Dim iTimeZone As Single
Dim strbolFirst As Boolean
Dim bolFirst As Boolean
Dim iCfg As Integer
'---- List of relevant configuration that have the same UTC time and "SENT" option
Dim iLstCfg(CST_MAX_CFG_NBR) As Integer
'{
' "calitrack": "OSM Empty Report Call",
' "osm-mock": {
' "method": [
' {
' "name": "getOsmEmptyReportCall",
' "case": [
strPath = ThisWorkbook.FullName
i = InStrRev(strPath, "\")
strPath = Left(strPath, i)
strSuffix = Sheets("Generation").Cells(4, 2)
bolFirst = True
'---- Output file
Open strPath & "OSMCall3_Empty" & strSuffix & ".json" For Output As #1
'---- Print header
Print #1, "{"
Print #1, vbTab & _
AddQuotes(CST_LBL_CALITRACK) & ": " & _
AddQuotes("OSM Empty Report Call") & ","
Print #1, vbTab & AddQuotes(CST_LBL_OSM_MOCK) & ": {"
Print #1, vbTab & vbTab & AddQuotes(CST_LBL_METHOD) & ": ["
Print #1, vbTab & vbTab & vbTab & "{"
Print #1, vbTab & vbTab & vbTab & _
AddQuotes(CST_LBL_NAME) & ": " & _
AddQuotes("getOsmEmptyReportCall") & ","
Print #1, vbTab & vbTab & vbTab & AddQuotes(CST_LBL_CASE) & ": ["
strIndent = vbTab & vbTab & vbTab & vbTab
'---- Loop on possible timezones
For iTimeZone = 2 To iTimezoneNbr
'---- Loop on all relevant configs
iCfg = 1
i = 0
While iCfg <= iConfigNbr
If CFG(iCfg).strTimeZone = TMZ(iTimeZone).strCode _
And CFG(iCfg).strEmptyReport = CST_OPT_SENT _
And CFG(iCfg).strFormatType = CST_PT_INTRADAY_CACIB Then
i = i + 1
iLstCfg(i) = iCfg
End If
iCfg = iCfg + 1
Wend
'---- If at least one config has been found, add a new section in the mock file
If i > 0 Then
If Not bolFirst Then
Print #1, strIndent & ","
End If
bolFirst = False
'{
' "request": {
' "ProductCategory": "Account Statement",
' "ProductType": "Intraday CACIB",
' "EmptyReport": "SENT",
' "Timezone Offset": "Europe/Paris"
' },
Print #1, strIndent & "{"
Print #1, strIndent & vbTab & AddQuotes(CST_LBL_REQUEST) & ":{"
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("ProductCategory") & ": " & _
AddQuotes("Account Statement") & ","
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("ProductType") & ": " & _
AddQuotes(CST_PC_INTRADAY_CACIB) & ","
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("EmptyReport") & ": " & _
AddQuotes(CST_OPT_SENT) & ","
Print #1, strIndent & vbTab & vbTab & _
AddQuotes("Timezone") & ": " & _
AddQuotes(TMZ(iTimeZone).strCode) & "},"
' "response": {
' "ConfigList": [
' "R1",
' "R26"
' ]
' }
'},
Print #1, strIndent & vbTab & AddQuotes(CST_LBL_RESPONSE) & ":{"
Print #1, strIndent & vbTab & vbTab & AddQuotes(CST_LBL_CONFIG_LIST) & ": [";
For iCfg = 1 To i
If iCfg > 1 Then
Print #1, ",";
End If
Print #1, AddQuotes(CFG(iLstCfg(iCfg)).strConfigId);
Next iCfg
Print #1, "]"
Print #1, strIndent & vbTab & "}"
Print #1, strIndent & "}"
End If
Next iTimeZone
' ]
' }
' ]
' }
'}
'---- Print footer
Print #1, vbTab & vbTab & vbTab & vbTab & "]"
Print #1, vbTab & vbTab & vbTab & "}"
Print #1, vbTab & vbTab & "]"
Print #1, vbTab & "}"
Print #1, "}"
Close #1
End Sub
Sub GenerateDataFile()
'-----------------------------------------------------------
'#### Generates the file for OSM Data (Exports all data for all configs)
'-----------------------------------------------------------
Dim i As Integer
Dim strIndent As String
Dim strPath As String
Dim strSuffix As String
strPath = ThisWorkbook.FullName
i = InStrRev(strPath, "\")
strPath = Left(strPath, i)
strSuffix = Sheets("Generation").Cells(4, 2)
'---- Output file
Open strPath & "OSMData" & strSuffix & ".json" For Output As #1
'---- Print header
Print #1, "{"
Print #1, vbTab & AddQuotes("osm-mock-data") & " : ["
strIndent = vbTab & vbTab & vbTab & vbTab
'---- Loop on Local accounts
For i = 1 To iConfigNbr
' Write request parameter
'---- Display full settings of config number i
Print #1, GenerateFullConfig(i, vbTab)
If i <> iConfigNbr Then
Print #1, ", "
Else
Print #1, ""
End If
Next i
'---- Print footer
Print #1, vbTab & "]"
Print #1, "}"
Close #1
End Sub
'#############################################################
'# New indentation magagement / Functions used for OIL Account mock
'#############################################################
Function Indent(iNbr As Integer)
Dim i As Integer
Indent = ""
If iNbr = 0 Then Exit Function
For i = 1 To iNbr
Indent = Indent & " "
Next i
End Function
Sub AddSection(strType As String, Optional varName As Variant)
If iNbrElem(Len(strIndent) + 1) <> 0 Then
Print #1, ",";
End If
If Not IsMissing(varName) Then
Print #1, vbCrLf & Indent(Len(strIndent)) & AddQuotes(varName) & ": " & strType;
Else
Print #1, vbCrLf & Indent(Len(strIndent)) & strType;
End If
strIndent = strIndent & strType
iNbrElem(Len(strIndent) + 1) = 0
End Sub
Sub CloseSection()
On Error Resume Next
Select Case Right(strIndent, 1)
Case "{"
Print #1, vbCrLf & Indent(Len(strIndent) - 1) & "}";
Case "["
Print #1, vbCrLf & Indent(Len(strIndent) - 1) & "]";
End Select
strIndent = Left(strIndent, Len(strIndent) - 1)
iNbrElem(Len(strIndent) + 1) = iNbrElem(Len(strIndent) + 1) + 1
End Sub
Sub AddSon(strKey As String, ByVal strValue As String, bolIsString As Boolean)
If iNbrElem(Len(strIndent) + 1) <> 0 Then
Print #1, ",";
End If
If bolIsString Then
Print #1, vbCrLf & Indent(Len(strIndent)) & _
AddQuotes(strKey) & ": " & _
AddQuotes(strValue);
Else
Print #1, vbCrLf & Indent(Len(strIndent)) & _
AddQuotes(strKey) & ": " & _
strValue;
End If
iNbrElem(Len(strIndent) + 1) = iNbrElem(Len(strIndent) + 1) + 1
End Sub
Function AddQuotes(ByVal strLabel As String) As String
'-----------------------------------------------------------
' Adds doubles quotes to a string
'-----------------------------------------------------------
AddQuotes = vbQuote & strLabel & vbQuote
End Function
Sub GenerateOilAccountsFile()
'-----------------------------------------------------------
' Generation of OIL accounts mock
'-----------------------------------------------------------
Dim i As Integer
Dim strIndent As String
Dim strPath As String
Dim strSuffix As String
strPath = ThisWorkbook.FullName
i = InStrRev(strPath, "\")
strPath = Left(strPath, i)
strSuffix = Sheets("Generation").Cells(4, 2)
'---- Initialize indentation
strIndent = ""
For i = 1 To 20
iNbrElem(i) = 0
Next i
'---- Output file
Open strPath & "OILAccountsFile" & strSuffix & ".json" For Output As #1
'---- Print header
' {
AddSection "{"
AddSon "calitrack", "MIC1-5806: Account Statement - Common module for validation of OSM accounts data (Part 1: calls to OIL referential)", True
' {
AddSection "{", "oil-accounts-mock-data"
' [
AddSection "[", "method"
' {
AddSection "{"
AddSon "name", "getOilAccountsCall", True
' [
AddSection "[", "case"
'---- Loop on Local accounts
For i = Range("Acc.CounterpartyName").Row + 1 To iAccountNbr
'---- Manage case of accounts that are declared in OSM but not in OIL
If Not (ACC(i).bolNotInOIL) Then
'---- Mock Input settings
' {
AddSection "{"
' {
AddSection "{", "request"
'---- Default size depends on the country. 11 for France and 14 for ISIS
If Format(ENT(ACC(i).iEntityLine).strCode, "00000") = "14000" Then
AddSon "accountNumber", Format(ACC(i).strSystemId, String(11, "0")), True
Else
AddSon "accountNumber", Format(ACC(i).strSystemId, String(14, "0")), True
End If
AddSon "entityCode", Format(ENT(ACC(i).iEntityLine).strCode, "00000"), True
' }
CloseSection
'---- Mock answer
' {
AddSection "{", "response"
AddSon "accountName", ACC(i).strName, True
'---- Default size depends on the country. 11 for France and 14 for ISIS
If Format(ENT(ACC(i).iEntityLine).strCode, "00000") = "14000" Then
AddSon "accountNumber", Format(ACC(i).strSystemId, String(11, "0")), True
Else
AddSon "accountNumber", Format(ACC(i).strSystemId, String(14, "0")), True
End If
AddSon "accountType", ACC(i).strAccountType, True
AddSon "accountTypeLabel", ACC(i).strAccountTypeName, True
' [
AddSection "[", "blockingAttributes"
' {
AddSection "{"
AddSon "code", "Dummy", True
AddSon "creditBlocked", "false", False
AddSon "debitBlocked", "false", False
AddSon "description", "Dummy", True
' }
CloseSection
' ]
CloseSection
AddSon "clientLocalId", Format(CTP(ACC(i).iCounterpartLine).strLocalId, "0000000000"), True
AddSon "currencyCode", ACC(i).strCurrency, True
AddSon "entityCode", Format(ENT(ACC(i).iEntityLine).strCode, "00000"), True
AddSon "iban", ACC(i).strIBAN, True
AddSon "localAccountNumber", Format(ACC(i).strLocalAccountId, "00000000000"), True
AddSon "status", "STATUS_UNKNOWN", True
' }
CloseSection
' }
CloseSection
End If
Next i
'---- Print footer
' ]
CloseSection
' }
CloseSection
' }
CloseSection
' ]
CloseSection
' }
CloseSection
' }
CloseSection
Close #1
End Sub
Function GetAllConfigs(strLocalId As String, strProductType As String) As String
'---- Used as a formula to retrieve all subscriptions
' with the current account attached in the Accounts tab
Dim i As Integer
Dim strLastProduct As String
i = 1
GetAllConfigs = ""
While Range("Cfg.Id").Offset(i, 0) <> ""
If Range("Cfg.FormatType").Offset(i, 0) <> "" Then
strLastProduct = Range("Cfg.FormatType").Offset(i, 0)
End If
If Range("Cfg.SystemAccountId").Offset(i, 0) = strLocalId _
And strLastProduct = strProductType Then
If GetAllConfigs <> "" Then
GetAllConfigs = GetAllConfigs & ", "
End If
GetAllConfigs = GetAllConfigs & Range("Cfg.Id").Offset(i, 0)
End If
i = i + 1
Wend
End Function
Sub DisplayConfigsPerAccounts()
'---- In the acccounts tab, add the list of configurations per accounts
Dim i As Integer
i = 1
While Range("Acc.SystemId").Offset(i, 0) <> ""
Range("Acc.IntConfigs").Offset(i, 0) = GetAllConfigs(Range("Acc.SystemId").Offset(i, 0), CST_PT_INTRADAY_CACIB)
Range("Acc.EODConfigs").Offset(i, 0) = GetAllConfigs(Range("Acc.SystemId").Offset(i, 0), CST_PT_EOD_CACIB)
i = i + 1
Wend
End Sub
Sub GenerateAllCalls()
'-----------------------------------------------------------
' Main routine.
' Loads all tables in memory and creates all the files
'-----------------------------------------------------------
Dim i As Integer
Dim strPath As String, strSuffix As String
strPath = ThisWorkbook.FullName
i = InStrRev(strPath, "\")
strPath = Left(strPath, i)
strSuffix = Sheets("Generation").Cells(4, 2)
'---- Error log
Open strPath & "OSMCalls" & strSuffix & ".log" For Output As #2
'---- Load the reference tables
' 0.1 Timezones
Print #2, "##### 0.1 Check on Timezones"
Print #2, ""
Call LoadTimezones
' 0.2 Formats
Print #2, "##### 0.2 Check on Formats"
Print #2, ""
Call LoadFormats
' 1. Entities
Print #2, "##### 1 Check on Entities"
Print #2, ""
Call LoadEntities
' 2. Counterparts
Print #2, ""
Print #2, "##### 2 Check on Counterparts"
Print #2, ""
Call LoadCounterparts
' 3. Accounts
Print #2, ""
Print #2, "##### 3 Check on Accounts"
Print #2, ""
Call LoadAccounts
' 4. Configs
Print #2, ""
Print #2, "##### 4 Check on Configs"
Print #2, ""
Call LoadConfigs
' 5. Product
Print #2, ""
Print #2, "##### 5 Check on Product"
Print #2, ""
Call LoadProduct
'---- Generation of OSM data
Print #2, ""
Print #2, "##### OSM data generation"
Print #2, ""
Call GenerateDataFile
'---- Generation of OSM call 1
Print #2, ""
Print #2, "##### OSM Call 1 (for accounting report) generation"
Print #2, ""
Call GenerateCall1File
'-------- ABT-28181207-START
'---- Generation of OSM call 1 specific for PSR
Print #2, ""
Print #2, "##### OSM Call 1 (for PSR) generation"
Print #2, ""
Call GeneratePSRCall1File
'------- ABT-28181207-END
'---- Generation of OSM call 1 specific for BSB
Print #2, ""
Print #2, "##### OSM Call 1 (for BSB) generation"
Print #2, ""
Call GenerateBSBCall1File
'---- Generation of OSM call 2
Print #2, ""
Print #2, "##### OSM Call 2 generation"
Print #2, ""
Call GenerateCall2File
'---- Generate Empty call (call 3)
Print #2, ""
Print #2, "##### OSM Empty Call (3) generation"
Print #2, ""
Call GenerateEmptyCallFile
'---- Generation of OIL call to Account reference tables
Print #2, ""
Print #2, "##### OIL Account Reference tables"
Print #2, ""
Call GenerateOilAccountsFile
'---- Close error log
Close #2
'---- Recalculate list of configs per account
Call DisplayConfigsPerAccounts
MsgBox "Files successfully generated in the current Excel file directory.", vbInformation
End Sub
Windows / Chrome 87.0.4280.141
- Fichiers générés non conformes
- Wetransfer gratuit fichiers lourd - Guide
- Renommer plusieurs fichiers en même temps - Guide
- Explorateur de fichiers - Guide
- Fichiers bin - Guide
- Fichiers epub - Guide
1 réponse
16 oct. 2022 à 08:00
Bonjour,
Un fichier avec des donnees bidons mais avec les noms de feuilles, les noms de plage ect..... est indispensable pour vous aider
Pour transmettre un fichier,
Veillez a ce qu'il n'y ait PAS DE DONNEES CONFIDENTIELLES
il faut passer par un site de pièce jointe tel que cjoint.com
Allez sur ce site : http://cjoint.com
Clic sur parcourir,
Cherche ton fichier,
clic sur ouvrir,
Clic sur "Créer le lien cjoint",
Copier le lien,
Revenir ici le coller dans une réponse...