private string GetAmountIncludingVATValue(IExcelDataReader reader, SheetConfig config, List <ExchangeRate> exchangeRates, string vat, decimal vatRate)
{
var amountIncludingVAT = GetCurrency(reader, FieldNames.AmountIncludingVAT, config, exchangeRates);
var amountExcludingVAT = "";
if (!string.IsNullOrWhiteSpace(config.AmountIdentifier.Column))
{
var index = ExcelUtils.ColumnToIndex(config.AmountIdentifier.Column);
var amountIdentifier = Utils.GetValue(reader, index);
var amount = GetCurrency(reader, FieldNames.Amount, config, exchangeRates);
var match = Regex.Match(amountIdentifier, config.AmountIdentifier.Value);
if (config.AmountIdentifier.Type == AmountIdentifier.AmountIdentifierTypeIncludingVat)
{
if (match.Success)
{
amountIncludingVAT = amount;
}
else
{
amountExcludingVAT = amount;
}
}
else
{
if (match.Success)
{
amountExcludingVAT = amount;
}
else
{
amountIncludingVAT = amount;
}
}
}
if (string.IsNullOrWhiteSpace(amountIncludingVAT))
{
var amountExcludingVatString = !string.IsNullOrWhiteSpace(amountExcludingVAT) ? amountExcludingVAT : GetCurrency(reader, FieldNames.AmountExcludingVAT, config, exchangeRates);
var amountExcludingVat = 0m;
var success = Decimal.TryParse(amountExcludingVatString, out amountExcludingVat);
if (!success)
{
return("");
}
if (string.IsNullOrWhiteSpace(vat))
{
vat = Decimal.Round(amountExcludingVat * (vatRate / 100m), 2).ToString();
}
amountIncludingVAT = Decimal.Round(amountExcludingVat + Decimal.Parse(vat), 2).ToString();
}
return(amountIncludingVAT);
}
public HeaderLocator(Identifier identifier)
{
_identifier = identifier;
HeaderColumnIndex = ExcelUtils.ColumnToIndex(_identifier.Column);
Found = false || HeaderColumnIndex == -1;
}
private int?GetFieldIndex(FieldNames fieldName, SheetConfig config)
{
var name = Enum.GetName(typeof(FieldNames), fieldName);
var field = config.Fields.FirstOrDefault(f => f.Name == name);
if (field == null)
{
return(null);
}
return(ExcelUtils.ColumnToIndex(field.Column));
}
private List <int> GetCommissionIndexes()
{
var commissionTypeIndexes = new List <int>();
if (string.IsNullOrWhiteSpace(_sheet.Config.CommissionTypes.MappingTemplate))
{
return(commissionTypeIndexes);
}
return(MappingTemplate.Parse(_sheet.Config.CommissionTypes.MappingTemplate)
.Select(c => ExcelUtils.ColumnToIndex(c))
.ToList());
}
private decimal GetVATRate(IExcelDataReader reader, SheetConfig config, decimal defaultVatRate)
{
if (!config.VatRates.Any())
{
return(defaultVatRate);
}
foreach (var rate in config.VatRates)
{
var index = ExcelUtils.ColumnToIndex(rate.Column);
var value = Utils.GetValue(reader, index).TrimWhiteSpace();
if (value.IgnoreCaseEquals(rate.Value))
{
return(rate.Rate);
}
}
return(defaultVatRate);
}
public string GetValue(IExcelDataReader reader, string column)
{
var index = ExcelUtils.ColumnToIndex(column);
return(Utils.GetValue(reader, index));
}
private IEnumerable <ImportCommission> Read(IExcelDataReader reader, Sheet sheet, SheetGroups sheetGroups, SheetExchangeRates sheetExchangeRates)
{
var config = sheet.Config;
var rowNumber = 0;
var header = new HeaderLocator(config.HeaderIdentifier);
while (reader.Read())
{
rowNumber++;
if (!header.Found)
{
header.Check(reader);
continue;
}
var groupValues = new List <GroupValue>();
if (sheetGroups != null)
{
groupValues = sheetGroups.RowGroups.Single(r => r.RowNumber == rowNumber).GroupValues;
}
//Continue if this is a section row
if (groupValues.Any(v => !v.IsInherited))
{
continue;
}
//Ignore row if any of the primary field values are empty
var requiredFields = config.Fields.Where(f => Fields.PrimaryFieldNames().Any(p => p == f.Name));
var anyMissingRequiredFields = requiredFields.Any(field =>
{
var fieldValue = Utils.GetValue(reader, ExcelUtils.ColumnToIndex(field.Column));
return(string.IsNullOrWhiteSpace(fieldValue));
});
if (anyMissingRequiredFields)
{
continue;
}
var vatRate = GetVATRate(reader, config, _vatRate);
var commission = new ImportCommission();
commission.PolicyNumber = GetValue(reader, FieldNames.PolicyNumber, config);
var commissionTypeValue = GetCommissionTypeValue(reader, config, groupValues);
commission.CommissionTypeValue = commissionTypeValue;
commission.CommissionTypeCode = GetCommissionTypeCode(commissionTypeValue, config);
commission.LastName = GetValue(reader, FieldNames.LastName, config);
commission.DateOfBirth = GetDate(reader, FieldNames.DateOfBirth, config);
commission.FirstName = GetValue(reader, FieldNames.FirstName, config);
commission.IdNumber = GetValue(reader, FieldNames.IdNumber, config);
commission.Initials = GetValue(reader, FieldNames.Initials, config);
commission.FullName = GetValue(reader, FieldNames.FullName, config);
commission.Currency = GetValue(reader, FieldNames.Currency, config);
commission.VAT = GetCurrency(reader, FieldNames.VAT, config, sheetExchangeRates.ExchangeRates);
commission.AmountIncludingVAT = GetAmountIncludingVATValue(reader, config, sheetExchangeRates.ExchangeRates, commission.VAT, vatRate);
var brokerFullName = _brokerFullName;
if (string.IsNullOrWhiteSpace(brokerFullName))
{
brokerFullName = GetGroupValue(groupValues, GroupFieldNames.BrokerFullName);
}
if (string.IsNullOrWhiteSpace(brokerFullName))
{
brokerFullName = GetValue(reader, FieldNames.BrokerFullName, config);
}
commission.BrokerFullName = brokerFullName;
if (string.IsNullOrWhiteSpace(commission.VAT))
{
var amountIncludingVat = 0m;
var success = Decimal.TryParse(commission.AmountIncludingVAT, out amountIncludingVat);
if (!success)
{
continue;
}
commission.VAT = Decimal.Round(amountIncludingVat - (amountIncludingVat / ((vatRate / 100m) + 1m)), 2).ToString();
}
commission.AmountIncludingVAT = Exchange(sheetExchangeRates.ExchangeRates, commission.Currency, commission.AmountIncludingVAT);
commission.VAT = Exchange(sheetExchangeRates.ExchangeRates, commission.Currency, commission.VAT);
yield return(commission);
}
}
public static string GetCommissionTypeValue(IExcelDataReader reader, SheetConfig config, List <GroupValue> groupValues)
{
var parts = MappingTemplate.Parse(config.CommissionTypes.MappingTemplate);
if (!parts.Any())
{
return(config.CommissionTypes.DefaultCommissionTypeCode);
}
var values = new List <string>();
foreach (var part in parts)
{
string value = "";
if (part == CommissionTypes.GROUP_COMMISSION_TYPE)
{
value = GetGroupValue(groupValues, GroupFieldNames.CommissionType);
}
else
{
var column = MappingTemplate.GetColumn(part);
var index = ExcelUtils.ColumnToIndex(column);
value = Utils.GetValue(reader, index);
//Update value is substring is defined
if (MappingTemplate.IsSubstring(part))
{
try
{
var subStringIndex = MappingTemplate.GetSubStringIndexes(part);
var startIndex = subStringIndex[0] - 1;
var length = subStringIndex[1] - subStringIndex[0] + 1;
value = value.Substring(startIndex, length);
}
catch { }
}
//Update value is regex is defined
if (MappingTemplate.IsRegex(part))
{
try
{
var regex = MappingTemplate.GetRegex(part);
var match = Regex.Match(value, regex, RegexOptions.IgnoreCase);
if (match.Success)
{
value = match.Value;
}
else
{
value = "";
}
}
catch { }
}
}
values.Add(value);
}
return(MappingTemplate.Format(values));
}
public IEnumerable <string> Read(Stream stream)
{
var groupLoader = new CommissionGroupLoader();
var sheetGroups = groupLoader.LoadForSheet(_sheet, stream);
var commissionTypes = new List <string>();
using (var reader = ExcelReaderFactory.CreateReader(stream))
{
var sheetNumber = 0;
do
{
//Increment the sheet number
sheetNumber++;
if (_sheet.Position != sheetNumber)
{
continue;
}
var rowNumber = 0;
var header = new HeaderLocator(_sheet.Config.HeaderIdentifier);
while (reader.Read())
{
rowNumber++;
if (!header.Found)
{
header.Check(reader);
continue;
}
var groupValues = new List <GroupValue>();
if (sheetGroups != null)
{
groupValues = sheetGroups.RowGroups.Single(r => r.RowNumber == rowNumber).GroupValues;
}
//Ignore row if any of the primary field values are empty
var requiredFields = _sheet.Config.Fields.Where(f => Fields.PrimaryFieldNames().Any(p => p == f.Name));
var anyMissingRequiredFields = requiredFields.Any(field =>
{
var fieldValue = Utils.GetValue(reader, ExcelUtils.ColumnToIndex(field.Column));
return(string.IsNullOrWhiteSpace(fieldValue));
});
if (anyMissingRequiredFields)
{
continue;
}
var value = CommissionImportReader.GetCommissionTypeValue(reader, _sheet.Config, groupValues);
commissionTypes.Add(value);
}
} while (reader.NextResult());
}
return(commissionTypes.Distinct().ToList());
}