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