Beispiel #1
0
        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);
            }
        }
Beispiel #3
0
        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);
        }