public async Task <IActionResult> Run( [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest request, ILogger log) { Guid organisationId = Guid.Parse(request.Query["organisationId"]); Guid commissionStatementId = Guid.Parse(request.Query["commissionStatementId"]); Guid commissionStatementTemplateId = Guid.Parse(request.Query["commissionStatementTemplateId"]); var scope = new ScopeOptions(organisationId, Guid.Empty, Guid.Empty, Scope.Organisation); var statement = await CommissionStatementService.GetCommissionStatement(scope, commissionStatementId); if (statement == null) { return(new NotFoundObjectResult(commissionStatementId)); } var path = new CommissionStatementPath(scope.OrganisationId, commissionStatementId); var files = await FileStorageService.GetFilesAsync(path); if (!files.Any()) { return(Utils.GetBadRequestObject("Reimport failed as there are no existing statement files.", commissionStatementId.ToString())); } var queryOptions = new CommissionStatementTemplateQueryOptions("", "", 0, 0); queryOptions.CompanyId.Add(statement.CompanyId.Value); queryOptions.Date = statement.Date; var templates = (await CommissionStatementTemplateService.GetTemplates(queryOptions)).Items; if (!templates.Any(t => t.Id == commissionStatementTemplateId)) { return(Utils.GetBadRequestObject("Reimport failed as the commissionStatementTemplateId is not valid.", commissionStatementTemplateId.ToString())); } var template = await CommissionStatementTemplateService.GetTemplate(commissionStatementTemplateId); await CommissionStatementService.DeleteCommissions(scope, commissionStatementId); var result = new ImportResult(); foreach (var fileInfo in files) { using (var stream = new MemoryStream()) { await FileStorageService.GetFile(fileInfo.Url, stream); var vatRate = await DirectoryLookupService.GetVATRate(statement.Date ?? DateTime.Now); var reader = new CommissionImportReader(template.Config, vatRate); var items = reader.Read(stream); result = await CommissionImportService.ImportCommissions(scope, commissionStatementId, items); } } return(new OkObjectResult(result)); }
public void Read_NegateValues() { var sheetConfig = new SheetConfig() { //No header HeaderIdentifier = new Identifier() { Column = "", Value = "" }, Fields = new List <Field>() { new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.PolicyNumber), Column = "A" }, new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.AmountIncludingVAT), Column = "B", NegateValue = true }, }, CommissionTypes = new CommissionTypes() { MappingTemplate = "", DefaultCommissionTypeCode = "unknown" } }; var sheet = new Sheet(); sheet.Position = 1; sheet.Config = sheetConfig; var config = new Config(); config.Sheets = new List <Sheet>() { sheet }; var bytes = System.Convert.FromBase64String(NegateValues_Base64.STRING); var stream = new MemoryStream(bytes); var reader = new CommissionImportReader(config, _vatRate); var commissions = reader.Read(stream).ToList(); Assert.Equal(2, commissions.Count); var actual = commissions[0]; Assert.Equal("123456", actual.PolicyNumber); Assert.Equal("115", actual.AmountIncludingVAT); Assert.Equal("15", actual.VAT); actual = commissions[1]; Assert.Equal("654321", actual.PolicyNumber); Assert.Equal("-200", actual.AmountIncludingVAT); Assert.Equal(-26.09m, Decimal.Parse(actual.VAT)); }
public void Read_ExchangeRates() { var sheetConfig = new SheetConfig() { //No header HeaderIdentifier = new Identifier() { Column = "", Value = "" }, Fields = new List <Field>() { new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.PolicyNumber), Column = "A" }, new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.AmountIncludingVAT), Column = "B" }, new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.Currency), Column = "C" }, }, CommissionTypes = new CommissionTypes() { MappingTemplate = "", DefaultCommissionTypeCode = "unknown" }, ExchangeRates = new ExchangeRates() { HeaderIdentifier = new Identifier() { Column = "A", Value = "Fund Currency" }, CurrencyColumn = "A", ExchangeRateColumn = "B", }, }; var sheet = new Sheet(); sheet.Position = 1; sheet.Config = sheetConfig; var config = new Config(); config.Sheets = new List <Sheet>() { sheet }; var bytes = System.Convert.FromBase64String(ExchangeRates_Base64.STRING); var stream = new MemoryStream(bytes); var reader = new CommissionImportReader(config, _vatRate); var commissions = reader.Read(stream).ToList(); Assert.Equal(5, commissions.Count); //Additional 2 are bad records var actual = commissions[0]; Assert.Equal("123456", actual.PolicyNumber); Assert.Equal("1200", actual.AmountIncludingVAT); Assert.Equal(156.48m, Decimal.Parse(actual.VAT)); actual = commissions[1]; Assert.Equal("654321", actual.PolicyNumber); Assert.Equal("3000", actual.AmountIncludingVAT); Assert.Equal(391.35m, Decimal.Parse(actual.VAT)); actual = commissions[2]; Assert.Equal("987654", actual.PolicyNumber); Assert.Equal("3600", actual.AmountIncludingVAT); Assert.Equal(469.56m, Decimal.Parse(actual.VAT)); }
public void Read_CommissionTypeSubstring() { var sheetConfig = new SheetConfig() { //No header HeaderIdentifier = new Identifier() { Column = "", Value = "" }, Fields = new List <Field>() { new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.PolicyNumber), Column = "A" }, new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.AmountIncludingVAT), Column = "B" }, new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.VAT), Column = "C" }, new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.BrokerFullName), Column = "K" } }, CommissionTypes = new CommissionTypes() { MappingTemplate = "D(1-6)", DefaultCommissionTypeCode = "unknown", Types = new List <CommissionType>() { new CommissionType() { CommissionTypeCode = "code_1", Value = "code_1" }, new CommissionType() { CommissionTypeCode = "code_2", Value = "code_2" }, new CommissionType() { CommissionTypeCode = "code_3", Value = "code_3" } } } }; var sheet = new Sheet(); sheet.Position = 1; sheet.Config = sheetConfig; var config = new Config(); config.Sheets = new List <Sheet>() { sheet }; var bytes = System.Convert.FromBase64String(CommissionTypeSubstring_Base64.STRING); var stream = new MemoryStream(bytes); var reader = new CommissionImportReader(config, _vatRate); var commissions = reader.Read(stream).ToList(); Assert.Equal(3, commissions.Count); var actual = commissions[0]; Assert.Equal("123456", actual.PolicyNumber); Assert.Equal("100", actual.AmountIncludingVAT); Assert.Equal("14", actual.VAT); Assert.Equal("code_1", actual.CommissionTypeCode); Assert.Equal("code_1", actual.CommissionTypeValue); Assert.Equal("brokerFullName_1", actual.BrokerFullName); actual = commissions[1]; Assert.Equal("654321", actual.PolicyNumber); Assert.Equal("200", actual.AmountIncludingVAT); Assert.Equal("15", actual.VAT); Assert.Equal("code_2", actual.CommissionTypeCode); Assert.Equal("code_2", actual.CommissionTypeValue); Assert.Equal("brokerFullName_2", actual.BrokerFullName); actual = commissions[2]; Assert.Equal("987654", actual.PolicyNumber); Assert.Equal("300", actual.AmountIncludingVAT); Assert.Equal("16", actual.VAT); Assert.Equal("code_3", actual.CommissionTypeCode); Assert.Equal("code_3", actual.CommissionTypeValue); Assert.Equal("brokerFullName_3", actual.BrokerFullName); }
public void Read_GroupingsReverseOrder() { var sheetConfig = new SheetConfig() { HeaderIdentifier = new Identifier() { Column = "A", Value = "Policy Number" }, Fields = new List <Field>() { new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.PolicyNumber), Column = "B" }, new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.AmountIncludingVAT), Column = "C" }, }, CommissionTypes = new CommissionTypes() { MappingTemplate = "GRP_CT;D", DefaultCommissionTypeCode = "unknown", Types = new List <CommissionType>() { new CommissionType() { CommissionTypeCode = "type_1", Value = "comType3;code_1" }, new CommissionType() { CommissionTypeCode = "type_2", Value = "comType3;code_3" }, new CommissionType() { CommissionTypeCode = "type_3", Value = "comType1;code_2" }, new CommissionType() { CommissionTypeCode = "type_4", Value = "comType1;code_3" }, new CommissionType() { CommissionTypeCode = "type_5", Value = "comType2;code_2" }, new CommissionType() { CommissionTypeCode = "type_6", Value = "comType2;code_1" }, } }, Groups = new List <Group>() { new Group() { FieldName = "BrokerFullName", Column = "A", Formatter = ".+?(?= : Broker)", //Take everything before ': Broker' ReverseOrder = false, Identifiers = new List <Identifier>() { new Identifier() { Column = "A", Value = ": Broker" }, //Contains ': Broker' new Identifier() { Column = "B", Value = "^(?![\\s\\S])" }, //Empy text } }, new Group() { FieldName = "CommissionType", Column = "A", ReverseOrder = true, Identifiers = new List <Identifier>() { new Identifier() { Column = "A", Value = "\\b" }, //Any word new Identifier() { Column = "B", Value = "\\d" }, //Any number new Identifier() { Column = "C", Value = "^(?![\\s\\S])" }, //Empy text new Identifier() { Column = "D", Value = "^(?![\\s\\S])" }, //Empy text } }, }, }; var sheet = new Sheet(); sheet.Position = 1; sheet.Config = sheetConfig; var config = new Config(); config.Sheets = new List <Sheet>() { sheet }; var bytes = System.Convert.FromBase64String(GroupingsReverseOrder_Base64.STRING); var stream = new MemoryStream(bytes); var reader = new CommissionImportReader(config, _vatRate); var commissions = reader.Read(stream).ToList(); Assert.Equal(7, commissions.Count); var actual = commissions[0]; Assert.Equal("11111", actual.PolicyNumber); Assert.Equal("100", actual.AmountIncludingVAT); Assert.Equal("Dean van Niekerk", actual.BrokerFullName); Assert.Equal("type_6", actual.CommissionTypeCode); actual = commissions[1]; Assert.Equal("22222", actual.PolicyNumber); Assert.Equal("200", actual.AmountIncludingVAT); Assert.Equal("Dean van Niekerk", actual.BrokerFullName); Assert.Equal("type_5", actual.CommissionTypeCode); actual = commissions[2]; Assert.Equal("33333", actual.PolicyNumber); Assert.Equal("300", actual.AmountIncludingVAT); Assert.Equal("Dean van Niekerk", actual.BrokerFullName); Assert.Equal("type_4", actual.CommissionTypeCode); actual = commissions[3]; Assert.Equal("44444", actual.PolicyNumber); Assert.Equal("400", actual.AmountIncludingVAT); Assert.Equal("Dean van Niekerk", actual.BrokerFullName); Assert.Equal("type_3", actual.CommissionTypeCode); actual = commissions[4]; Assert.Equal("55555", actual.PolicyNumber); Assert.Equal("500", actual.AmountIncludingVAT); Assert.Equal("Marc Bormann", actual.BrokerFullName); Assert.Equal("type_3", actual.CommissionTypeCode); actual = commissions[5]; Assert.Equal("66666", actual.PolicyNumber); Assert.Equal("600", actual.AmountIncludingVAT); Assert.Equal("Marc Bormann", actual.BrokerFullName); Assert.Equal("type_2", actual.CommissionTypeCode); actual = commissions[6]; Assert.Equal("77777", actual.PolicyNumber); Assert.Equal("700", actual.AmountIncludingVAT); Assert.Equal("Marc Bormann", actual.BrokerFullName); Assert.Equal("type_1", actual.CommissionTypeCode); }
public void Read_DefaultTemplate() { var sheetConfig = new SheetConfig() { //No header HeaderIdentifier = new Identifier() { Column = "", Value = "" }, Fields = new List <Field>() { new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.PolicyNumber), Column = "A" }, new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.AmountIncludingVAT), Column = "B" }, new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.VAT), Column = "C" }, new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.LastName), Column = "E" }, new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.DateOfBirth), Column = "F" }, new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.FirstName), Column = "G" }, new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.IdNumber), Column = "H" }, new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.Initials), Column = "I" }, new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.FullName), Column = "J" }, new Field() { Name = Enum.GetName(typeof(FieldNames), FieldNames.BrokerFullName), Column = "K" } }, CommissionTypes = new CommissionTypes() { MappingTemplate = "D", DefaultCommissionTypeCode = "unknown", Types = new List <CommissionType>() { new CommissionType() { CommissionTypeCode = "code_1", Value = "code_1" }, new CommissionType() { CommissionTypeCode = "code_2", Value = "code_2" }, new CommissionType() { CommissionTypeCode = "code_3", Value = "code_3" } } } }; var sheet = new Sheet(); sheet.Position = 1; sheet.Config = sheetConfig; var config = new Config(); config.Sheets = new List <Sheet>() { sheet }; var bytes = System.Convert.FromBase64String(DefaultTemplate_Base64.STRING); var stream = new MemoryStream(bytes); var reader = new CommissionImportReader(config, _vatRate); var commissions = reader.Read(stream).ToList(); //Check we are testing all fields var importCommissionProps = typeof(ImportCommission).GetProperties(); Assert.Equal(importCommissionProps.Count() - 4, sheetConfig.Fields.Count()); //minus 4 for Id, Currency, CommissionTypeValue and CommissionTypeCode Assert.Equal(3, commissions.Count); var actual = commissions[0]; Assert.Equal("123456", actual.PolicyNumber); Assert.Equal("100", actual.AmountIncludingVAT); Assert.Equal("14", actual.VAT); Assert.Equal("code_1", actual.CommissionTypeCode); Assert.Equal("code_1", actual.CommissionTypeValue); Assert.Equal("ln_1", actual.LastName); Assert.Equal("1982-10-03", actual.DateOfBirth); Assert.Equal("fn_1", actual.FirstName); Assert.Equal("8210035032082", actual.IdNumber); Assert.Equal("ini_1", actual.Initials); Assert.Equal("fullName_1", actual.FullName); Assert.Equal("brokerFullName_1", actual.BrokerFullName); actual = commissions[1]; Assert.Equal("654321", actual.PolicyNumber); Assert.Equal("200", actual.AmountIncludingVAT); Assert.Equal("15", actual.VAT); Assert.Equal("code_2", actual.CommissionTypeCode); Assert.Equal("code_2", actual.CommissionTypeValue); Assert.Equal("ln_2", actual.LastName); Assert.Equal("", actual.DateOfBirth); Assert.Equal("fn_2", actual.FirstName); Assert.Equal("8210035032082", actual.IdNumber); Assert.Equal("ini_2", actual.Initials); Assert.Equal("fullName_2", actual.FullName); Assert.Equal("brokerFullName_2", actual.BrokerFullName); actual = commissions[2]; Assert.Equal("987654", actual.PolicyNumber); Assert.Equal("300", actual.AmountIncludingVAT); Assert.Equal("16", actual.VAT); Assert.Equal("unknown", actual.CommissionTypeCode); Assert.Equal("", actual.CommissionTypeValue); Assert.Equal("ln_3", actual.LastName); Assert.Equal("1982-10-05", actual.DateOfBirth); Assert.Equal("fn_3", actual.FirstName); Assert.Equal("", actual.IdNumber); Assert.Equal("ini_3", actual.Initials); Assert.Equal("fullName_3", actual.FullName); Assert.Equal("brokerFullName_3", actual.BrokerFullName); }
public async Task <IActionResult> Import(Guid commissionStatementId, [FromQuery] Guid commissionStatementTemplateId, [FromQuery] Guid?userId) { var scope = AuthenticationService.GetScope(User); var file = Request.Form.Files.FirstOrDefault(); if (file == null) { return(BadRequest()); } var statement = await CommissionStatementService.GetCommissionStatement(scope, commissionStatementId); var template = await CommissionStatementTemplateService.GetTemplate(commissionStatementTemplateId); string brokerFullName = null; if (template.BrokerSpecific) { if (!userId.HasValue) { return(this.BadRequestMessage("UserId required for broker specific templates.")); } var user = await UserService.GetUser(scope, userId.Value); if (user == null) { return(this.BadRequestMessage("Invalid UserId.")); } brokerFullName = $"{user.FirstName} {user.LastName}"; } var config = template.Config; var result = new ImportResult(); using (var stream = file.OpenReadStream()) { var vatRate = await DirectoryLookupService.GetVATRate(statement.Date ?? DateTime.Now); var reader = new CommissionImportReader(config, vatRate, brokerFullName); var items = reader.Read(stream); result = await CommissionImportService.ImportCommissions(scope, commissionStatementId, items); } if (result.UnknownCommissionTypeValues.Any()) { using (var stream = file.OpenReadStream()) { var attachment = GetEmailAttachment(file, stream); await SendUnkownCommissionTypesEmail(result, scope, commissionStatementId, template, attachment); } } if (!result.Results.Any(r => r.Success)) { using (var stream = file.OpenReadStream()) { var attachment = GetEmailAttachment(file, stream); await SendZeroEntriesEmail(scope, commissionStatementId, template, attachment); } } if (result.Results.Any()) { using (var stream = file.OpenReadStream()) { var path = new CommissionStatementFilePath(scope.OrganisationId, commissionStatementId, file.FileName); await FileStorageService.AddFileAsync(path, stream); } } return(Ok(result)); }
public async Task <IActionResult> Reimport(Guid commissionStatementId, [FromQuery] Guid commissionStatementTemplateId, [FromQuery] Guid?userId) { var scope = AuthenticationService.GetScope(User); var statement = await CommissionStatementService.GetCommissionStatement(scope, commissionStatementId); if (statement == null) { return(NotFound()); } var path = new CommissionStatementDirectoryPath(scope.OrganisationId, commissionStatementId); var fileInfoList = await FileStorageService.GetFileInfoListAsync(path); if (!fileInfoList.Any()) { return(this.BadRequestMessage("Reimport failed as there are no existing statement files.")); } var queryOptions = new CommissionStatementTemplateQueryOptions("", "", 0, 0); queryOptions.CompanyId.Add(statement.CompanyId.Value); queryOptions.Date = statement.Date; var templates = (await CommissionStatementTemplateService.GetTemplates(queryOptions)).Items; if (!templates.Any(t => t.Id == commissionStatementTemplateId)) { return(this.BadRequestMessage("Reimport failed as the commissionStatementTemplateId is not valid.")); } var template = await CommissionStatementTemplateService.GetTemplate(commissionStatementTemplateId); string brokerFullName = null; if (template.BrokerSpecific) { if (!userId.HasValue) { return(this.BadRequestMessage("UserId required for broker specific templates.")); } var user = await UserService.GetUser(scope, userId.Value); if (user == null) { return(this.BadRequestMessage("Invalid UserId.")); } brokerFullName = $"{user.FirstName} {user.LastName}"; } await CommissionStatementService.DeleteCommissions(scope, commissionStatementId); var result = new ImportResult(); foreach (var fileInfo in fileInfoList) { using (var stream = new MemoryStream()) { await FileStorageService.GetFile(fileInfo.Url, stream); var vatRate = await DirectoryLookupService.GetVATRate(statement.Date ?? DateTime.Now); var reader = new CommissionImportReader(template.Config, vatRate, brokerFullName); var items = reader.Read(stream); result = await CommissionImportService.ImportCommissions(scope, commissionStatementId, items); if (result.UnknownCommissionTypeValues.Any()) { stream.Position = 0; using (var copy = new MemoryStream()) { await stream.CopyToAsync(copy); copy.Position = 0; var attachment = GetEmailAttachmentFromCloud(fileInfo, copy); await SendUnkownCommissionTypesEmail(result, scope, commissionStatementId, template, attachment); } } if (!result.Results.Any(r => r.Success)) { stream.Position = 0; using (var copy = new MemoryStream()) { await stream.CopyToAsync(copy); copy.Position = 0; var attachment = GetEmailAttachmentFromCloud(fileInfo, copy); await SendZeroEntriesEmail(scope, commissionStatementId, template, attachment); } } } } return(Ok(result)); }