private SheetGroups ApplyCascade(SheetGroups sheetGroup, SheetConfig config) { List <GroupValue> lastGroupValues = null; foreach (var group in config.Groups) { var rows = sheetGroup.RowGroups.ToList(); if (group.ReverseOrder) { rows.Reverse(); } foreach (var rowGroups in rows) { foreach (var groupValue in rowGroups.GroupValues.Where(g => g.GroupFieldName == group.FieldName)) { if (lastGroupValues == null) { continue; } if (!groupValue.IsInherited) { continue; } var shouldClear = lastGroupValues.Any(g => //If last section has a row that g.IsInherited == false && //is a section row g.GroupFieldName != groupValue.GroupFieldName && //and is different from current section g.HierarchyOrder < groupValue.HierarchyOrder //and the hierarchy is less (further up) than current section ); //Inherit from previous value if (shouldClear) { groupValue.Value = ""; } else { groupValue.Value = lastGroupValues.Find(g => g.GroupFieldName == groupValue.GroupFieldName).Value; } } lastGroupValues = rowGroups.GroupValues; } } return(sheetGroup); }
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); }