private CorrectableReportingEntity_Type GetReportingEntity(ExcelPackage package)
{
var repEnt = new CorrectableReportingEntity_Type();
repEnt.Entity = GetOrganisationPartyType(EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, "CoverPage", "B7")), // CountryCode_Type.ZA, // (S: CoverPage; Cells: B7)
EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, "CoverPage", "B8")), // CountryCode_Type.ZA, // (S: CoverPage; Cells: B8)
GetExcelStringValue(package, "CoverPage", "B9"), //"9000051715", // (S: CoverPage; Cells: B9)
EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, "CoverPage", "B10")), // CountryCode_Type.ZA, // (S: CoverPage; Cells: B10)
GetExcelStringValue(package, "CoverPage", "B11"), //"1918/000095/06", // (S: CoverPage; Cells: B11)
GetExcelStringValue(package, "CoverPage", "B12"), //"Barloworld Limited", // (S: CoverPage; Cells: B12)
EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, "CoverPage", "B13")), // CountryCode_Type.ZA, // (S: CoverPage; Cells: B13)
GetExcelStringValue(package, "CoverPage", "B14").Split(';'), // new object[] { "61 Katherine Street", "Sandton", "2196" }, // (S: CoverPage; Cells: B14) "61 Katherine Street;Sandton;2196" (Split on ;)
EnumLookup.GetOECDLegalAddressTypeEnumType(GetExcelStringValue(package, "CoverPage", "B15"))); //OECDLegalAddressType_EnumType.OECD304);// (S: CoverPage; Cells: B15)
repEnt.ReportingRole = EnumLookup.GetCbcReportingRoleEnumType(GetExcelStringValue(package, "CoverPage", "B17")); // CbcReportingRole_EnumType.CBC701; // (S: CoverPage; Cells: B17)
repEnt.NameMNEGroup = GetExcelStringValue(package, "CoverPage", "B16"); // "Barloworld Limited"; // (S: CoverPage; Cells: B16)
repEnt.ReportingPeriod = new ReportingEntity_TypeReportingPeriod()
{
StartDate = new DateTime(GetExcelIntValue(package, "CoverPage", "B18").Value, GetExcelIntValue(package, "CoverPage", "B19").Value, GetExcelIntValue(package, "CoverPage", "B20").Value), // new DateTime(2018, 10, 1), // (S: CoverPage; Cells: B18, B19, B20)
EndDate = new DateTime(GetExcelIntValue(package, "CoverPage", "B21").Value, GetExcelIntValue(package, "CoverPage", "B22").Value, GetExcelIntValue(package, "CoverPage", "B23").Value), // new DateTime(2019, 09, 30) // (S: CoverPage; Cells: B21, B22, B23)
};
repEnt.DocSpec = gloabalDocSpec;
return(repEnt);
}
private void StartWork()
{
// xsd.exe CbcXML_v1.0.1.xsd /Classes oecdtypes_v4.1.xsd /Classes isocbctypes_v1.0.1.xsd
var cbc_oecd = new CBC_OECD();
ExcelPackage.LicenseContext = OfficeOpenXml.LicenseContext.NonCommercial;
using (var package = new ExcelPackage(xlsxFile))
{
gloabalDocSpec = new DocSpec_Type
{
DocTypeIndic = EnumLookup.GetOECDDocTypeIndicEnumType(GetExcelStringValue(package, "CoverPage", "B24")), // (S: CoverPage; Cells: B24)
DocRefId = GetExcelStringValue(package, "CoverPage", "B25"), // "ZA2018DOCBAW", // (S: CoverPage; Cells: B25)
CorrDocRefId = "",
CorrMessageRefId = ""
};
cbc_oecd.version = "1.0";
cbc_oecd.MessageSpec = GetMessageSpec(package);
cbc_oecd.CbcBody = GetCbcBodies(package).ToArray();
}
}
private List <CorrectableCbcReport_Type> GetCbcReports(ExcelPackage package)
{
var cbcReports = new List <CorrectableCbcReport_Type>();
// Some Loop
// for (int i = 0; i < 10; i++) // (S: SUMMARY; Cells: B Count)
foreach (var receivingCountryClass in receivingCountryClass)
{
var cbcRep = new CorrectableCbcReport_Type();
cbcRep.ResCountryCode = EnumLookup.GetCountryCodeEnumType(receivingCountryClass.CountryCode); // CountryCode_Type.AO; // (S: SUMMARY; Cells: A)
cbcRep.DocSpec = gloabalDocSpec;
// Summary
// (S: SUMMARY; Cells: ,K ,C ,D ,L ,E ,G ,F ,H ,J ,I)
// cbcRep.Summary = GetSummary(currCode_Type.ZAR, "766604888.40", "-69810833.20", "-1217694218.00", "363", "-217527466.40", "509150539.20", "-771290869.60", "-1280441408.80", "55195379.39", "53236237.67");
cbcRep.Summary = GetSummary(currCode_Type.ZAR,
GetExcelStringValue(package, "SUMMARY", "K" + receivingCountryClass.RowNumber),
GetExcelStringValue(package, "SUMMARY", "C" + receivingCountryClass.RowNumber), // "-69810833.20",
GetExcelStringValue(package, "SUMMARY", "D" + receivingCountryClass.RowNumber), // "-1217694218.00",
GetExcelStringValue(package, "SUMMARY", "L" + receivingCountryClass.RowNumber), // "363",
GetExcelStringValue(package, "SUMMARY", "E" + receivingCountryClass.RowNumber), // "-217527466.40",
GetExcelStringValue(package, "SUMMARY", "G" + receivingCountryClass.RowNumber), // "509150539.20",
GetExcelStringValue(package, "SUMMARY", "F" + receivingCountryClass.RowNumber), // "-771290869.60",
GetExcelStringValue(package, "SUMMARY", "H" + receivingCountryClass.RowNumber), // "-1280441408.80",
GetExcelStringValue(package, "SUMMARY", "J" + receivingCountryClass.RowNumber), // "55195379.39",
GetExcelStringValue(package, "SUMMARY", "I" + receivingCountryClass.RowNumber)); // "53236237.67");
// Const Entities
cbcRep.ConstEntities = GetConstituentEntities(package, receivingCountryClass).ToArray();
cbcReports.Add(cbcRep);
}
return(cbcReports);
}
private List <ConstituentEntity_Type> GetConstituentEntities(ExcelPackage package, ReceivingCountryClass receivingCountryClass)
{
var workbookName = "CE_" + receivingCountryClass.CountryCode;
var constEntities = new List <ConstituentEntity_Type>();
int rowNumber = 2;
while (true)
{
// (S: CE_XX like CE_AO; Cells: X Count)
var cellValue = GetExcelStringValue(package, workbookName, "A" + rowNumber);
if (string.IsNullOrEmpty(cellValue))
{
break;
}
// // Loop
//for (int i = 0; i < 10; i++) // (S: CE_XX like CE_AO; Cells: X Count)
//{
var constEntity = new ConstituentEntity_Type();
var bizActivities = new List <CbcBizActivityType_EnumType>();
//for (int bizA = 0; bizA < 10; bizA++) // (S: CE_XX; Cells: X Count)
//{
// bizActivities.Add(CbcBizActivityType_EnumType.CBC505); // (S: CE_XX; Cells: H)
//}
var excelActValue = GetExcelStringValue(package, workbookName, "H" + rowNumber);
var actCodes = excelActValue.Split(';');
foreach (var actCode in actCodes)
{
if (!string.IsNullOrEmpty(actCode))
{
bizActivities.Add(EnumLookup.GetCbcBizActivityTypeEnumType(actCode));
}
}
constEntity.BizActivities = bizActivities.ToArray();
constEntity.ConstEntity = GetOrganisationPartyType(EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, workbookName, "G" + rowNumber)), // CountryCode_Type.AO, // (S: CE_XX; Cells: G)
EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, workbookName, "E" + rowNumber)), // CountryCode_Type.AO, // (S: CE_XX; Cells: E)
GetExcelStringValue(package, workbookName, "D" + rowNumber), //"5410000595", // (S: CE_XX; Cells: D)
EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, workbookName, "C" + rowNumber)), // CountryCode_Type.AO, // (S: CE_XX; Cells: C)
GetExcelStringValue(package, workbookName, "B" + rowNumber), //"1996.1", // (S: CE_XX; Cells: B)
GetExcelStringValue(package, workbookName, "A" + rowNumber), //"Barloworld Equipamentos Angola Limitada", // (S: CE_XX; Cells: A)
EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, workbookName, "K" + rowNumber)), // CountryCode_Type.AO, // (S: CE_XX; Cells: K)
GetExcelStringValue(package, workbookName, "J" + rowNumber).Split(';'), //new object[] { "", "" }, // (S: CE_XX; Cells: J)
EnumLookup.GetOECDLegalAddressTypeEnumType(GetExcelStringValue(package, workbookName, "L" + rowNumber))); // OECDLegalAddressType_EnumType.OECD304); // (S: CE_XX; Cells: L)
constEntity.IncorpCountryCode = EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, workbookName, "F" + rowNumber)); // CountryCode_Type.AO; // (S: CE_XX; Cells: F)
constEntity.IncorpCountryCodeSpecified = true;
constEntity.OtherEntityInfo = GetExcelStringValue(package, workbookName, "I" + rowNumber); // ""; // (S: CE_XX; Cells: I)
constEntity.Role = EnumLookup.GetUltimateParentEntityRoleEnumType(GetExcelStringValue(package, workbookName, "M" + rowNumber)); // UltimateParentEntityRole_EnumType.CBC803; // (S: CE_XX; Cells: M)
constEntity.RoleSpecified = true;
constEntities.Add(constEntity);
rowNumber++;
}
return(constEntities);
}
private MessageSpec_Type GetMessageSpec(ExcelPackage package)
{
var messageSpec = new MessageSpec_Type();
// cbc_oecd.MessageSpec.SendingEntityIN
messageSpec.TransmittingCountry = CountryCode_Type.ZA;
// ReceivingCountry
List <CountryCode_Type> ReceivingCountries = new List <CountryCode_Type>();
var rowNumber = 2;
while (true)
{
var cellValue = GetExcelStringValue(package, "SUMMARY", "A" + rowNumber);
if (string.IsNullOrEmpty(cellValue))
{
break;
}
ReceivingCountries.Add(EnumLookup.GetCountryCodeEnumType(cellValue)); // (S:SUMMARY - Cells:A)
this.receivingCountryClass.Add(new ReceivingCountryClass
{
RowNumber = rowNumber,
CountryCode = cellValue
});
rowNumber++;
}
messageSpec.ReceivingCountry = ReceivingCountries.ToArray();
// MessageType
messageSpec.MessageType = MessageType_EnumType.CBC;
// Language
messageSpec.Language = LanguageCode_Type.EN;
// Warning
messageSpec.Warning = "";
// Contact
messageSpec.Contact = GetExcelStringValue(package, "CoverPage", "B1"); // (S: CoverPage; Cells:B1)
// MessageRefId
messageSpec.MessageRefId = GetExcelStringValue(package, "CoverPage", "B2");// "ZA2019BAW"; // (S: CoverPage; Cells:B2)
// MessageTypeIndic
messageSpec.MessageTypeIndic = EnumLookup.GetCbcMessageTypeIndicEnumType(GetExcelStringValue(package, "CoverPage", "B3")); // CbcMessageTypeIndic_EnumType.CBC401; // (S: CoverPage; Cells:B3)
//// CorrMessageRefId
//messageSpec.CorrMessageRefId = new string[];
// ReportingPeriod
messageSpec.ReportingPeriod = new DateTime(GetExcelIntValue(package, "CoverPage", "B4").Value, GetExcelIntValue(package, "CoverPage", "B5").Value, GetExcelIntValue(package, "CoverPage", "B6").Value); // new DateTime(2019, 9, 30); // (S: CoverPage; Cells:B4, B5, B6)
messageSpec.Timestamp = DateTime.Now;
// TODO: messageSpec.SendingEntityIN = null;
// TODO: messageSpec.CorrMessageRefId = null;
return(messageSpec);
}
