private CorrectableReportingEntity_Type GetReportingEntity(ExcelPackage package)
{
var repEnt = new CorrectableReportingEntity_Type();
repEnt.Entity = GetOrganisationPartyType(EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, "CoverPage", "B6")), // CountryCode_Type.ZA, // (S: CoverPage; Cells: B7)
EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, "CoverPage", "B7")), // CountryCode_Type.ZA, // (S: CoverPage; Cells: B8)
GetExcelStringValue(package, "CoverPage", "B8"), //"9000051715", // (S: CoverPage; Cells: B9)
EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, "CoverPage", "B9")), // CountryCode_Type.ZA, // (S: CoverPage; Cells: B10)
GetExcelStringValue(package, "CoverPage", "B10"), //"1918/000095/06", // (S: CoverPage; Cells: B11)
GetExcelStringValue(package, "CoverPage", "B11"), //"Barloworld Limited", // (S: CoverPage; Cells: B12)
EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, "CoverPage", "B12")), // CountryCode_Type.ZA, // (S: CoverPage; Cells: B13)
GetExcelStringValue(package, "CoverPage", "B13").Split(';'), // new object[] { "61 Katherine Street", "Sandton", "2196" }, // (S: CoverPage; Cells: B14) "61 Katherine Street;Sandton;2196" (Split on ;)
EnumLookup.GetOECDLegalAddressTypeEnumType(GetExcelStringValue(package, "CoverPage", "B14"))); //OECDLegalAddressType_EnumType.OECD304);// (S: CoverPage; Cells: B15)
repEnt.ReportingRole = EnumLookup.GetCbcReportingRoleEnumType(GetExcelStringValue(package, "CoverPage", "B16")); // CbcReportingRole_EnumType.CBC701; // (S: CoverPage; Cells: B17)
var docTypeIndic = GetExcelStringValue(package, "CoverPage", "B17");
var docRefId = GetExcelStringValue(package, "CoverPage", "B18");
var corrDocRefId = GetExcelStringValue(package, "CoverPage", "B19");
var corrMessageRefId = GetExcelStringValue(package, "CoverPage", "B3");
repEnt.DocSpec = GetDocSpec(package, docTypeIndic, docRefId, corrDocRefId, corrMessageRefId);
return(repEnt);
}
private CorrectableReportingEntity_Type GetReportingEntity(ExcelPackage package)
{
var repEnt = new CorrectableReportingEntity_Type();
var resCountryCode = EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, "CoverPage", "B6"));
var tinIssueBy = EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, "CoverPage", "B7"));
var tinValue = GetExcelStringValue(package, "CoverPage", "B8");
var OrgInTypeIssueBy = EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, "CoverPage", "B9"));
var orgInTypeValue = GetExcelStringValue(package, "CoverPage", "B10");
var nameOrg = GetExcelStringValue(package, "CoverPage", "B11");
var addCountryCode = EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, "CoverPage", "B12"));
var adds = GetExcelStringValue(package, "CoverPage", "B13").Split(';');
var legAddType = EnumLookup.GetOECDLegalAddressTypeEnumType(GetExcelStringValue(package, "CoverPage", "B14"));
repEnt.Entity = GetOrganisationPartyType(resCountryCode, tinIssueBy, tinValue, OrgInTypeIssueBy, orgInTypeValue, nameOrg, addCountryCode, adds, legAddType);
repEnt.ReportingRole = EnumLookup.GetCbcReportingRoleEnumType(GetExcelStringValue(package, "CoverPage", "B16")); // CbcReportingRole_EnumType.CBC701; // (S: CoverPage; Cells: B17)
var docTypeIndic = GetExcelStringValue(package, "CoverPage", "B17");
var docRefId = GetExcelStringValue(package, "CoverPage", "B18");
var corrDocRefId = GetExcelStringValue(package, "CoverPage", "B19");
var corrMessageRefId = GetExcelStringValue(package, "CoverPage", "B3");
repEnt.DocSpec = GetDocSpec(package, docTypeIndic, docRefId, corrDocRefId, corrMessageRefId);
repEnt.NameMNEGroup = GetExcelStringValue(package, "CoverPage", "B15");
var reportEndString = GetExcelStringValue(package, "CoverPage", "B5");
var reportEnd = DateTime.Parse(reportEndString);
var reportStart = reportEnd.AddYears(-1);
reportStart = reportStart.AddDays(1);
repEnt.ReportingPeriod = new ReportingEntity_TypeReportingPeriod()
{
StartDate = reportStart,
EndDate = reportEnd
};
return(repEnt);
}
private CountryCode_Type[] GetAdditionalInfoResCountryCodes(string commaDelimetedCountryCode)
{
List <CountryCode_Type> codeList = new List <CountryCode_Type>();
var array = commaDelimetedCountryCode.Split(',');
foreach (var arr in array)
{
var val = arr.Trim();
if (string.IsNullOrEmpty(val))
{
continue;
}
codeList.Add(EnumLookup.GetCountryCodeEnumType(val));
}
return(codeList.ToArray());
}
private List <CorrectableCbcReport_Type> GetCbcReports(ExcelPackage package)
{
var cbcReports = new List <CorrectableCbcReport_Type>();
// Some Loop
foreach (var recCountryCls in receivingCountryClass)
{
var cbcRep = new CorrectableCbcReport_Type();
cbcRep.ResCountryCode = EnumLookup.GetCountryCodeEnumType(recCountryCls.CountryCode);
var docTypeIndic = GetExcelStringValue(package, "SUMMARY", "M" + recCountryCls.RowNumber);
var docRefId = GetExcelStringValue(package, "SUMMARY", "N" + recCountryCls.RowNumber);
var corrDocRefId = GetExcelStringValue(package, "SUMMARY", "O" + recCountryCls.RowNumber);
var corrMessageRefId = GetExcelStringValue(package, "CoverPage", "B3");
cbcRep.DocSpec = GetDocSpec(package, docTypeIndic, docRefId, corrDocRefId, corrMessageRefId);
// Summary
cbcRep.Summary = GetSummary(currCode_Type.ZAR,
GetExcelStringValue(package, "SUMMARY", "K" + recCountryCls.RowNumber),
GetExcelStringValue(package, "SUMMARY", "C" + recCountryCls.RowNumber),
GetExcelStringValue(package, "SUMMARY", "D" + recCountryCls.RowNumber),
GetExcelStringValue(package, "SUMMARY", "L" + recCountryCls.RowNumber),
GetExcelStringValue(package, "SUMMARY", "E" + recCountryCls.RowNumber),
GetExcelStringValue(package, "SUMMARY", "G" + recCountryCls.RowNumber),
GetExcelStringValue(package, "SUMMARY", "F" + recCountryCls.RowNumber),
GetExcelStringValue(package, "SUMMARY", "H" + recCountryCls.RowNumber),
GetExcelStringValue(package, "SUMMARY", "J" + recCountryCls.RowNumber),
GetExcelStringValue(package, "SUMMARY", "I" + recCountryCls.RowNumber));
// Const Entities
cbcRep.ConstEntities = GetConstituentEntities(package, recCountryCls).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)
{
var cellValue = GetExcelStringValue(package, workbookName, "A" + rowNumber);
if (string.IsNullOrEmpty(cellValue))
{
break;
}
var constEntity = new ConstituentEntity_Type();
var bizActivities = new List <CbcBizActivityType_EnumType>();
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)),
EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, workbookName, "E" + rowNumber)),
GetExcelStringValue(package, workbookName, "D" + rowNumber),
EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, workbookName, "C" + rowNumber)),
GetExcelStringValue(package, workbookName, "B" + rowNumber),
GetExcelStringValue(package, workbookName, "A" + rowNumber),
EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, workbookName, "K" + rowNumber)),
GetExcelStringValue(package, workbookName, "J" + rowNumber).Split(';'),
EnumLookup.GetOECDLegalAddressTypeEnumType(GetExcelStringValue(package, workbookName, "L" + rowNumber)));
// TODO - It doesnt want to serialize this
constEntity.IncorpCountryCode = EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, workbookName, "F" + rowNumber));
constEntity.IncorpCountryCodeSpecified = true;
constEntity.OtherEntityInfo = GetExcelStringValue(package, workbookName, "I" + rowNumber);
constEntities.Add(constEntity);
rowNumber++;
}
this.ConstituentEntitiesSummaries.Add(new ConstituentEntitiesSummary {
CountryCode = receivingCountryClass.CountryCode, ConstituentEntityCount = rowNumber - 2
});
return(constEntities);
}
private MessageSpec_Type GetMessageSpec(ExcelPackage package)
{
var messageSpec = new MessageSpec_Type();
this.receivingCountryClass = new List <ReceivingCountryClass>();
// 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 = new CountryCode_Type[] { CountryCode_Type.ZA };
// MessageType
messageSpec.MessageType = MessageType_EnumType.CBC;
// Language
messageSpec.Language = LanguageCode_Type.EN;
messageSpec.LanguageSpecified = true;
// Warning
messageSpec.Warning = GetExcelStringValue(package, "CoverPage", "B20");
// Contact
messageSpec.Contact = GetExcelStringValue(package, "CoverPage", "B1"); // (S: CoverPage; Cells:B1)
// MessageRefId
messageSpec.MessageRefId = GetExcelStringValue(package, "CoverPage", "B2");// "ZA2019BAW"; // (S: CoverPage; Cells:B2)
messageSpec.CorrMessageRefId = new string[] { GetExcelStringValue(package, "CoverPage", "B3") };
// MessageTypeIndic
messageSpec.MessageTypeIndic = EnumLookup.GetCbcMessageTypeIndicEnumType(GetExcelStringValue(package, "CoverPage", "B4")); // CbcMessageTypeIndic_EnumType.CBC401; // (S: CoverPage; Cells:B3)
messageSpec.MessageTypeIndicSpecified = true;
// ReportingPeriod
// messageSpec.ReportingPeriod = new DateTime(GetExcelIntValue(package, "CoverPage", "B5").Value, GetExcelIntValue(package, "CoverPage", "B6").Value, GetExcelIntValue(package, "CoverPage", "B7").Value); // new DateTime(2019, 9, 30); // (S: CoverPage; Cells:B4, B5, B6)
messageSpec.ReportingPeriod = DateTime.Parse(GetExcelStringValue(package, "CoverPage", "B5"));
messageSpec.Timestamp = DateTime.Now;
// messageSpec.SendingEntityIN = null; // TODO Check with SARS
// messageSpec.CorrMessageRefId = null; // This data element is not used for CbC reporting
return(messageSpec);
}
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)
{
var cellValue = GetExcelStringValue(package, workbookName, "A" + rowNumber);
if (string.IsNullOrEmpty(cellValue))
{
break;
}
var constEntity = new ConstituentEntity_Type();
var bizActivities = new List <CbcBizActivityType_EnumType>();
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();
var resCountryCode = EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, workbookName, "G" + rowNumber));
var tinIssueBy = EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, workbookName, "E" + rowNumber));
var tinValue = GetExcelStringValue(package, workbookName, "D" + rowNumber);
var OrgInTypeIssueBy = EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, workbookName, "C" + rowNumber));
var orgInTypeValue = GetExcelStringValue(package, workbookName, "B" + rowNumber);
var nameOrg = GetExcelStringValue(package, workbookName, "A" + rowNumber);
var addCountryCode = EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, workbookName, "K" + rowNumber));
var adds = GetExcelStringValue(package, workbookName, "J" + rowNumber).Split(';');
var legAddType = EnumLookup.GetOECDLegalAddressTypeEnumType(GetExcelStringValue(package, workbookName, "L" + rowNumber));
constEntity.ConstEntity = GetOrganisationPartyType(resCountryCode, tinIssueBy, tinValue, OrgInTypeIssueBy, orgInTypeValue, nameOrg, addCountryCode, adds, legAddType);
constEntity.IncorpCountryCode = EnumLookup.GetCountryCodeEnumType(GetExcelStringValue(package, workbookName, "F" + rowNumber));
constEntity.IncorpCountryCodeSpecified = true;
constEntity.OtherEntityInfo = GetExcelStringValue(package, workbookName, "I" + rowNumber);
constEntity.Role = UltimateParentEntityRole_EnumType.CBC801;
constEntity.RoleSpecified = true;
constEntities.Add(constEntity);
rowNumber++;
}
this.ConstituentEntitiesSummaries.Add(new ConstituentEntitiesSummary {
CountryCode = receivingCountryClass.CountryCode, ConstituentEntityCount = rowNumber - 2
});
return(constEntities);
}
