private IEnumerable <ExchangeRate> Read(IExcelDataReader reader, Sheet sheet)
{
var config = sheet.Config.ExchangeRates;
if (string.IsNullOrEmpty(config.HeaderIdentifier.Column))
{
yield break;
}
var rowNumber = 0;
var header = new HeaderLocator(config.HeaderIdentifier);
while (reader.Read())
{
rowNumber++;
if (!header.Found)
{
header.Check(reader);
continue;
}
var currency = GetValue(reader, config.CurrencyColumn);
var rate = GetValue(reader, config.ExchangeRateColumn);
if (string.IsNullOrEmpty(currency))
{
continue;
}
var parsedRate = 0m;
var success = Decimal.TryParse(rate, out parsedRate);
if (!success)
{
continue;
}
yield return(new ExchangeRate()
{
Currency = currency, Rate = parsedRate
});
}
}
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());
}
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);
}
}
private SheetGroups GetSheetGroups(IExcelDataReader reader, SheetConfig config, int sheetNumber)
{
var sheetGroups = new SheetGroups()
{
SheetNumber = sheetNumber,
RowGroups = new List <RowGroups>()
};
var rowNumber = 0;
var header = new HeaderLocator(config.HeaderIdentifier);
while (reader.Read())
{
rowNumber++;
if (!header.Found)
{
header.Check(reader);
continue;
}
var rowGroups = new RowGroups(rowNumber);
//Check groupings
int hierarchyOrder = 0;
foreach (var group in config.Groups)
{
hierarchyOrder++;
var groupValue = new GroupValue(group.FieldName, hierarchyOrder);
//Check for match
var isMatch = true;
foreach (var identifier in group.Identifiers)
{
var value = CommissionImportReader.GetValue(reader, identifier.Column) ?? "";
if (Regex.Matches(value, identifier.Value).Count == 0)
{
isMatch = false;
}
}
if (isMatch)
{
var value = CommissionImportReader.GetValue(reader, group.Column) ?? "";
if (!string.IsNullOrEmpty(group.Formatter))
{
var match = Regex.Match(value, group.Formatter);
if (match.Success)
{
value = match.Value;
}
}
groupValue.Value = value;
groupValue.IsInherited = false;
}
rowGroups.GroupValues.Add(groupValue);
}
sheetGroups.RowGroups.Add(rowGroups);
}
;
return(sheetGroups);
}
