Пример #1
0
        private ParseResult RequiredString(ParsingCell cell, int maxLength = 0, int minLength = 0)
        {
            var input  = CellValue(cell);
            var Result = new ParseResult();

            if (string.IsNullOrWhiteSpace(input))
            {
                Result.Errors.Add(AddCellError(cell, $"Field is required"));
            }
            else if ((maxLength > 0) && (input.Length > maxLength))
            {
                Result.Errors.Add(AddCellError(cell, $"Input string exceeds the maximum length of {maxLength}"));
            }
            else if ((minLength > 0) && (input.Length < minLength))
            {
                Result.Errors.Add(AddCellError(cell, $"Input string must be at least {minLength} characters"));
            }

            if (Result.InError == false)
            {
                Result.Result = input;
            }

            return(Result);
        }
Пример #2
0
        private ParseResult DelimetedDigitsOnly(ParsingCell cell, List <char> delimiters, int maxLength = 0, int minLength = 0, bool truncateSpaces = false)
        {
            // As of now, none of the Delimeted fields are Required
            var input  = CellValue(cell);
            var Result = new ParseResult();

            input = truncateSpaces ? input.Replace(" ", "") : input;

            if ((maxLength > 0) && (input.Length > maxLength))
            {
                Result.Errors.Add(AddCellError(cell, $"Input string exceeds the maximum length of {maxLength}"));
            }
            else if ((minLength > 0) && (input.Length < minLength) && (input.Length > 0))
            {
                Result.Errors.Add(AddCellError(cell, $"Input string must be at least {minLength} characters"));
            }

            if (!input.All(c => (((c >= '0') && (c <= '9')) || (delimiters.Contains(c)))))
            {
                Result.Errors.Add(AddCellError(cell, $"Input string contains characters other than digits and any of the desired delimiters"));
            }

            if (Result.InError == false)
            {
                Result.Result = string.IsNullOrWhiteSpace(input) ? null : input;
            }

            return(Result);
        }
Пример #3
0
        private ParseResult NonRequiredDigitsOnly(ParsingCell cell, int maxLength = 0, int minLength = 0)
        {
            var input  = CellValue(cell);
            var Result = new ParseResult();

            if ((maxLength > 0) && (input.Length > maxLength))
            {
                Result.Errors.Add(AddCellError(cell, $"Input string exceeds the maximum length of {maxLength}"));
            }
            else if ((minLength > 0) && (input.Length < minLength) && (input.Length > 0))
            {
                Result.Errors.Add(AddCellError(cell, $"Input string must be at least {minLength} characters"));
            }

            if (!input.All(c => ((c >= '0') && (c <= '9'))))
            {
                Result.Errors.Add(AddCellError(cell, $"Input string contains characters other than digits"));
            }

            if (Result.InError == false)
            {
                Result.Result = string.IsNullOrWhiteSpace(input) ? null : input;
            }

            return(Result);
        }
Пример #4
0
 private ParsingError AddCellError(ParsingCell cell, string message)
 {
     return(new ParsingError {
         Cell = cell,
         Message = message,
     });
 }
Пример #5
0
        private string CellValue(ParsingCell cell)
        {
            var Result = "";

            if (cell.Cell != null)
            {
                var cellType = cell.GetType();

                switch (cell.Cell.CellType)
                {
                case CellType.String:
                    Result = cell.Cell.StringCellValue.Trim();
                    break;

                case CellType.Numeric:
                    if (DateUtil.IsCellDateFormatted(cell.Cell))
                    {
                        DateTime   date  = cell.Cell.DateCellValue;
                        ICellStyle style = cell.Cell.CellStyle;
                        // Excel uses lowercase m for month whereas .Net uses uppercase
                        string format = style.GetDataFormatString().Replace('m', 'M');
                        Result = date.ToString(format);
                    }
                    else
                    {
                        Result = cell.Cell.NumericCellValue.ToString().Trim();
                    }
                    break;

                case CellType.Boolean:
                    Result = cell.Cell.BooleanCellValue ? "TRUE" : "FALSE";
                    break;

                case CellType.Error:
                    Result = FormulaError.ForInt(cell.Cell.ErrorCellValue).String;
                    break;
                }
            }

            return(Result);
        }
Пример #6
0
        private ParseResult RequiredDate(ParsingCell cell)
        {
            var      input   = CellValue(cell);
            var      Result  = new ParseResult();
            DateTime TheDate = DateTime.Now;

            if (string.IsNullOrWhiteSpace(input))
            {
                Result.Errors.Add(AddCellError(cell, $"Field is required"));
            }
            else if (!DateTime.TryParse(input, out TheDate))
            {
                Result.Errors.Add(AddCellError(cell, $"Unable to parse the given date"));
            }

            if (Result.InError == false)
            {
                Result.Result = TheDate.ToString("MM/dd/yyyy");
            }

            return(Result);
        }
Пример #7
0
 public ParsingError(ParsingCell cell, string message)
 {
     this.Cell    = cell;
     this.Message = message;
 }
Пример #8
0
        private Func <GUIContent> getSyntaxErrorContent(string message, string tooltip, ParsingCell cell)
        {
            switch (cell)
            {
            case ParsingCell.Label:
                if (this.LabelErrorContent == null)
                {
                    this.LabelErrorContent = new GUIContent(message);
                }

                this.LabelErrorContent.tooltip = tooltip;

                return(() => this.LabelErrorContent);

            case ParsingCell.Value:
                if (this.ValueErrorContent == null)
                {
                    this.ValueErrorContent = new GUIContent(message);
                }
                this.ValueErrorContent.tooltip = tooltip;

                return(() => this.ValueErrorContent);

            default:
                return(() => new GUIContent(message));
            }
        }
Пример #9
0
        private Delegate parseFunctionScript(string script, ParsingCell cell)
        {
            LambdaExpression scriptExpression = null;

            try
            {
                ScriptParser parser = new ScriptParser(script);

                scriptExpression = parser.Parse();

                if ((scriptExpression.Parameters?.Count ?? 0) > 0)
                {
                    if (scriptExpression.Parameters[0].Type == typeof(int))
                    {
                        object defaultValue = parser.ParameterSignatures[0].DefaultValue;
                        int    defaultParam = 0;

                        if (defaultValue != null && defaultValue is int)
                        {
                            defaultParam = (int)defaultValue;
                        }

                        switch (cell)
                        {
                        case ParsingCell.Label:
                            this.LabelIsVarPrecision     = true;
                            this.LabelVarPrecisionDigits = defaultParam;
                            break;

                        case ParsingCell.Value:
                            this.ValueIsVarPrecision     = true;
                            this.ValueVarPrecisionDigits = defaultParam;
                            break;

                        default:
                            throw new NotImplementedException();
                        }
                    }
                }

                Logging.PostDebugMessage(this, "Parsed expression '{0}' from '{1}'.", scriptExpression, script);

                return(scriptExpression.Compile());
            }
            catch (VOIDScriptSyntaxException sx)
            {
                return(this.getSyntaxErrorContent("Syntax Error", sx.Message, cell));
            }
            catch (InvalidOperationException iox)
            {
                return(this.getSyntaxErrorContent("Syntax Error",
                                                  string.Format("{0}: {1}", iox.GetType().Name, iox.Message), cell));
            }
            catch (FormatException fx)
            {
                return(this.getSyntaxErrorContent("Syntax Error",
                                                  string.Format("{0}: {1}", fx.GetType().Name, fx.Message), cell));
            }
            catch (VOIDScriptParserException px)
            {
                return(this.getSyntaxErrorContent("Parser Error", px.Message + " Please report!", cell));
            }
            catch (Exception ex)
            {
                Logging.PostErrorMessage(
                    "Compiler error processing VOIDScript line '{0}'.  Please report!\n{1}: {2}\n{3}",
                    script, ex.GetType().Name, ex.Message, ex.StackTrace);
                return(this.getSyntaxErrorContent("Compiler Error", ex.GetType().Name + ": " + ex.Message, cell));
            }
        }
Пример #10
0
        public FileStreamResult ProcessExcelFile(Stream stream)
        {
            var       size          = stream.Length;
            var       Errors        = new List <ParsingError>();
            var       NewRecords    = new List <Record>();
            var       TotalErrors   = 0;
            var       LatLongTypeID = 2; // enumProps.GetDBID(LocationTypes.LatLong);
            IWorkbook wb            = WorkbookFactory.Create(stream);

            try {
                IsError   = CreateErrorStyle(wb);
                IsComment = CreateCommentStyle(wb);
                ISheet sheet = wb.GetSheetAt(0);

                // Loop through each Row
                // Loop through each Column in the current row
                var UseDate = DateTime.UtcNow;

                // Row is zero based, skip row 0 (title row), 1 = Example, 2 = Validation
                for (var r = 3; r <= sheet.LastRowNum; r++)
                {
                    IRow row    = sheet.GetRow(r);
                    var  staged = new Record {
                        UploadedByID = UsersID,
                        UploadedDate = UseDate,
                        Active       = false,
                    };
                    var RowErrors = new List <ParsingError>();

                    for (var c = 2; c <= 24; c++)   // c = 2 (Column C to start), 24 (Column Y to end)
                                                    // # - Req - Title          - Validation
                                                    // -----------------------------------------------
                                                    // A - No  - Parsing Errors - None
                                                    // B - No  - Comments       - None
                                                    // C - Yes - Map Image Name - 5 digits only
                                                    // D - Yes - City, Village  - None (max 50)
                                                    // E - Yes - County         - Minimum length = 5 (max 50)
                                                    // F - Yes - Defunct Twp    - None (max 50)
                                                    // G - No  - Lot Number     - Numbers only, seperated by comma's, no spaces
                                                    // H - No  - Section        - "
                                                    // I - No  - Tract          - None
                                                    // J - No  - Range          - None
                                                    // K - Yes - Survey Date    - MM/DD/YYYY
                                                    // L - Yes - Surveyor Name  - None
                                                    // M - No  - Surveyor No.   - Digits only
                                                    // N - Yes - Address        - Full address
                                                    // O - No  - Cross Street   - None
                                                    // P - No  - Parcel Numbers - ###-##-###,###-##-###...
                                                    // Q - No  - Volume         - Digits only
                                                    // R - No  - Page           - "
                                                    // S - No  - AFN...         - None
                                                    // T - No  - Subdivision    - None
                                                    // U - No  - Subd. Lot      - Digits only, seperated by commas
                                                    // V - No  - Survey Name    - None
                                                    // W - No  - Location       - Latitude on Longitude seperated by commas
                                                    // X - No  - Client         - None
                                                    // Y - No  - Notes          - None

                    {
                        var pCell = new ParsingCell(row.GetCell(c), r, c);

                        if (c == 2)
                        {
                            // Column C: Map Image Name
                            var Check = RequiredString(pCell, 15, 5);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.MapImageName = Check.Result;
                            }
                        }
                        else if (c == 3)
                        {
                            // Column D: City, Village, Township
                            var Check = RequiredString(pCell, 25, 5);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.CityVillageTownship = Check.Result;
                            }
                        }
                        else if (c == 4)
                        {
                            // Column E: County
                            var Check = RequiredString(pCell, 15, 5);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.County = Check.Result;
                            }
                        }
                        else if (c == 5)
                        {
                            // Column F: Defunct Township
                            var Check = RequiredString(pCell, 20, 5);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.DefunctTownship = Check.Result.Replace("township", "");
                            }
                        }
                        else if (c == 6)
                        {
                            // Column G: Lot Numbers
                            var Check = DelimetedDigitsOnly(pCell, ',', 15, 0, true);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.LotNumbers = Check.Result;
                            }
                        }
                        else if (c == 7)
                        {
                            // Column H: Section
                            var Check = DelimetedDigitsOnly(pCell, ',', 15, 0, true);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.Section = Check.Result;
                            }
                        }
                        else if (c == 8)
                        {
                            // Column I: Tract
                            var Check = NonRequiredString(pCell, 15);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.Tract = Check.Result;
                            }
                        }
                        else if (c == 9)
                        {
                            // Column J: Range
                            var Check = NonRequiredString(pCell, 15);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.Range = Check.Result;
                            }
                        }
                        else if (c == 10)
                        {
                            // Column K: Survey Date
                            var Check = RequiredDate(pCell);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.SurveyDate = DateTime.Parse(Check.Result);
                            }
                        }
                        else if (c == 11)
                        {
                            // Column L: Surveyor Name
                            var Check = RequiredString(pCell, 25, 5);

                            // TODO: This Name needs to be transmuted to an actual ApplicationUser ID
                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.SurveyorName = Check.Result;
                                // TODO: Lookup the name in the security.AspNetUser table
                                // If found, add the ID to the staged.SurveyorID field
                            }
                        }
                        else if (c == 12)
                        {
                            // Column M: Surveyor Number
                            var Check = NonRequiredDigitsOnly(pCell, 5, 2);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.SurveyorNumber = Check.Result;
                            }
                        }
                        else if (c == 13)
                        {
                            // Column N: Address
                            var Check = RequiredString(pCell, 100, 5);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.Address = Check.Result;
                            }
                        }
                        else if (c == 14)
                        {
                            // Column O: Cross Street Name
                            var Check = NonRequiredString(pCell, 30);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.CrossStreet = Check.Result;
                            }
                        }
                        else if (c == 15)
                        {
                            // Column P: Parcel Numbers
                            var Check = DelimetedDigitsOnly(pCell, new List <char> {
                                ',', '-'
                            }, 30);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.ParcelNumbers = Check.Result;
                            }
                        }
                        else if (c == 16)
                        {
                            // Column Q: Volume
                            var Check = NonRequiredDigitsOnly(pCell, 8, 1);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                if (int.TryParse(Check.Result, out int value))
                                {
                                    staged.DeedVolume = value; // TODO: This needs to be a TryParse so we can handle overflows to long
                                }
                            }
                            // TODO: Idealy, the method (in this case) NonRequiredDigitsOnly will return a Null or push an Error
                        }
                        else if (c == 17)
                        {
                            // Column R: Page
                            var Check = NonRequiredDigitsOnly(pCell, 6, 1);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else if (int.TryParse(Check.Result, out int value))
                            {
                                staged.DeedPage = value; // TODO: This needs to be a TryParse so we can handle overflows to long
                            }
                        }
                        else if (c == 18)
                        {
                            // Column S: AFN
                            var Check = NonRequiredString(pCell, 18, 3);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.AutomatedFileNumber = Check.Result;
                            }
                        }
                        else if (c == 19)
                        {
                            // Column T: Subdivision
                            var Check = NonRequiredString(pCell, 50, 5);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.Subdivision = Check.Result;
                            }
                        }
                        else if (c == 20)
                        {
                            // Column U: Subdivision-Sublot
                            var Check = DelimetedDigitsOnly(pCell, ',', 10, 0, true);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.Sublot = Check.Result;
                            }
                        }
                        else if (c == 21)
                        {
                            // Column V: Survey Name
                            var Check = NonRequiredString(pCell, 50);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.SurveyName = Check.Result;
                            }
                        }
                        else if (c == 22)
                        {
                            // Column W: Location
                            var Check = DelimetedDigitsOnly(pCell, new List <char> {
                                ',', '.', '-'
                            }, 30, truncateSpaces: true);
                            int Parts = string.IsNullOrWhiteSpace(Check.Result) ? 0 : Check.Result.Split(',').Count();

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else if ((Parts == 1) || (Parts > 2))
                            {
                                RowErrors.Add(new ParsingError(pCell, "Invalid Format for Location"));
                            }
                            else if (!string.IsNullOrWhiteSpace(Check.Result))
                            {
                                var Loc = new Location {
                                    LocationTypeID = LatLongTypeID,
                                    Latitude       = Convert.ToDecimal(Check.Result.Split(',')[0]),
                                    Longitude      = Convert.ToDecimal(Check.Result.Split(',')[1])
                                };
                                staged.Location = Loc;
                            }
                        }
                        else if (c == 23)
                        {
                            // Column X: Client
                            var Check = NonRequiredString(pCell, 50);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.ClientName = Check.Result;
                            }
                        }
                        else if (c == 24)
                        {
                            // Column Y: Notes
                            var Check = NonRequiredString(pCell, 2000);

                            if (Check.InError)
                            {
                                RowErrors.AddRange(Check.Errors);
                            }
                            else
                            {
                                staged.Notes = Check.Result; // notes
                            }
                        }
                    } // foreach of the Columns

                    var CommentCell = row.GetCell(0) ?? row.CreateCell(0);

                    if (RowErrors.Count > 0)
                    {
                        CommentCell.SetCellValue(new XSSFRichTextString($"{RowErrors.Count} Errors:\n{string.Join("\n", RowErrors.Select(x => $"[{ColumnIndexToLetter(x.Cell.Column + 1)}] {x.Message}"))}"));
                        CommentCell.CellStyle = IsComment;

                        foreach (var error in RowErrors)
                        {
                            var cell = error.Cell.Cell == null?row.CreateCell(error.Cell.Column) : row.GetCell(error.Cell.Column);

                            cell.CellStyle = IsError;
                        }
                    }
                    else
                    {
                        NewRecords.Add(staged); // Only add the record if the row is valid
                        CommentCell.SetCellValue("Validation: OK");
                    }

                    Errors.AddRange(RowErrors);

                    if (RowErrors.Count > 0)
                    {
                        TotalErrors += RowErrors.Count;
                    }
                    else
                    {
                        // The row has validated successfully, check to see if it exists
                        var HasChodeMatches = DataContext.Records
                                              .Where(x => x.HashCode == staged.GetHashCode())
                                              .Select(x => x.ID)
                                              .ToList();

                        if (HasChodeMatches.Count > 0)
                        {
                            // Hash code found, should be already in the database
                            // Add to validation column
                            TotalErrors++;
                            var MatchIDs = "";
                            foreach (var match in HasChodeMatches)
                            {
                                MatchIDs += $"{match},";
                            }
                            CommentCell.SetCellValue($"Validation: OK - Found {HasChodeMatches.Count} Potential hashcode matches - IDs: {MatchIDs}");
                        }
                        else
                        {
                            var Matches = DataContext.Records
                                          .Where(x =>
                                                 x.MapImageName == staged.MapImageName &&
                                                 x.CityVillageTownship == staged.CityVillageTownship &&
                                                 x.State == staged.State &&
                                                 x.County == staged.County &&
                                                 x.DefunctTownship == staged.DefunctTownship &&
                                                 x.SurveyDate == staged.SurveyDate &&
                                                 x.Address == staged.Address
                                                 )
                                          .Select(x => x.ID)
                                          .ToList();

                            if (Matches.Count > 0)
                            {
                                TotalErrors++;
                                var MatchIDs = "";
                                foreach (var match in Matches)
                                {
                                    MatchIDs += $"{match},";
                                }
                                CommentCell.SetCellValue($"Validation: OK - Found {Matches.Count} Potential field matches - IDs: {MatchIDs}");
                            }
                        }
                    }
                } // foreach of the Rows
            } catch (Exception e) {
                Console.WriteLine($"Exceiption: {e.Message} - {e.StackTrace}");
            }

            if (TotalErrors > 0)
            {
                Console.WriteLine($"There were a total of {TotalErrors} errors.");
            }
            else
            {
                int WriteErrors = 0;
                var bob         = WriteErrors;
                Console.WriteLine($"Saving {NewRecords.Count} Records to the database.");

                ISheet sheet = wb.GetSheetAt(0);

                for (int index = 0; index < NewRecords.Count; index++)
                {
                    IRow row = sheet.GetRow(3 + index);

                    var r = NewRecords[index];
                    r.HashCode = r.GetHashCode();

                    try {
                        DataContext.Records.Add(r);
                        DataContext.SaveChanges(1);
                        row.GetCell(0).SetCellValue($"{row.GetCell(0).StringCellValue}, Save: OK - {r.ID}");
                    } catch (Exception e) {
                        row.GetCell(0).SetCellValue($"{row.GetCell(0).StringCellValue}, Save: Failed - {(e.InnerException != null ? e.InnerException.Message : e.Message)}");
                        WriteErrors++;
                    }
                } // foreach of the Records

                if (WriteErrors > 0)
                {
                    Console.WriteLine($"There were a total of {WriteErrors} Write errors.");
                }
            }

            var TheStream = new NPOIMemoryStream {
                AllowClose = false
            };

            try {
                wb.Write(TheStream);
            } catch (Exception e) {
                TheStream.Dispose();
                logger.LogError(1, e, "Unable to export Blank Template to Excel");
            }

            TheStream.Seek(0, SeekOrigin.Begin);
            TheStream.AllowClose = true;

            return(File(TheStream, "application/vnd.ms-excel", $"{DateTime.Now.ToString("yy-MMdd")}-Testing.xlsx"));
        }