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); }