Exemple #1
0
 public ReportingItem(ReportingItem itemToCopy)
 {
     Project  = itemToCopy.Project;
     Date     = itemToCopy.Date;
     Reporter = itemToCopy.Reporter;
     Category = itemToCopy.Category;
 }
Exemple #2
0
        public static void ExtractTaskOutOfDescription(ReportingItem reportingItem)
        {
            if (reportingItem.Description != null)
            {
                //string[] taskIds = new string[] { "Story", "Task", "Test Case", "US", "User Story", "Bug", "Defect" };

                string description = reportingItem.Description.Trim();

                //bool isTaskCouldBeDefined = taskIds.Any(s => description.StartsWith(s));

                //if (isTaskCouldBeDefined)
                //{

                char[] arrayOfSeparators = { ' ', ':' };
                int    index             = description.IndexOf(':');
                if (index > 0)
                {
                    if (digits.Contains(description[index - 1]) ||
                        arrayOfSeparators.Contains(description[index - 1]) && index > 1 && digits.Contains(description[index - 2]))
                    {
                        reportingItem.Task        = description.Substring(0, index).Trim();
                        reportingItem.Description = description.Substring(index + 1).Trim();
                    }
                }
                //}
            }
        }
Exemple #3
0
        public static void ExtractHoursAtTheEndingOfString(string taskComment, ReportingItem toUpdateByParsing)
        {
            int length = taskComment.Length;
            int index;

            string timeToParse = "";

            char[] digits = new char[] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9' };

            char[] allowedForDoubleValue = new char [] { '0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '.', ',' };
            if (length > 0)
            {
                for (index = length - 1; index > -1; index--)
                {
                    if (!allowedForDoubleValue.Contains(taskComment[index]))
                    {
                        break;
                    }
                    else
                    {
                        timeToParse = taskComment[index] + timeToParse;
                    }
                }

                if (index < 0 || index + 1 >= length)
                {
                    toUpdateByParsing.Hours = null;
                    return;
                }

                bool containsAnyDigit = timeToParse.IndexOfAny(digits) > -1;
                if (containsAnyDigit)
                {
                    double parsedValue = double.Parse(timeToParse, CultureInfo.InvariantCulture);
                    toUpdateByParsing.Hours       = parsedValue;
                    toUpdateByParsing.Description = taskComment.Substring(0, index).TrimEnd(':', '-').TrimEnd();
                }
                else
                {
                    toUpdateByParsing.Description = taskComment;
                    toUpdateByParsing.Hours       = null;
                }
            }
            else
            {
                toUpdateByParsing.Description = null;
                toUpdateByParsing.Hours       = null;
            }
        }
Exemple #4
0
        public static List <ReportingItem> ParseComment(string comment, ReportingItem baseReportingItem)
        {
            //Story 123: lorem ipsum
            //Story - 123: lorem ipsum
            //Task 123: lorem ipsum
            //Task - 123: lorem ipsum
            //Test Case 123: lorem ipsum
            //Test Case-123: lorem ipsum
            //Test Case 22234: lorem ipsum
            //US 123: lorem ipsum
            //US - 123: lorem ipsum
            //User Story 22566: lorem ipsum
            //Bug 22565: lorem ipsum
            //Task 22777: lorem ipsum
            //Defect 456: lorem ipsum
            //Defect - 456: lorem ipsum
            //Bug 456: lorem ipsum
            //Bug - 456: lorem ipsum
            comment = comment.Trim();
            int length = comment.Length;

            List <ReportingItem> result = new List <ReportingItem>();

            string[] stringSeparators = { "\n\r", "\n" };

            int last_match_index = 0;

            List <string> tasks = new List <string>();

            for (int i = 0; i < length; i++)
            {
                foreach (var separator in stringSeparators)
                {
                    string commentSubracted = "";
                    if (i + separator.Length <= length)
                    {
                        string s = comment.Substring(i, separator.Length);
                        if (s.Equals(separator, StringComparison.InvariantCultureIgnoreCase))
                        {
                            commentSubracted = comment.Substring(last_match_index, i - last_match_index);
                            commentSubracted = commentSubracted.Trim().TrimStart('\n', '\r');
                            last_match_index = i + separator.Length;
                            if (commentSubracted.Length > 0)
                            {
                                tasks.Add(commentSubracted);
                            }
                        }
                    }
                    else
                    {
                        commentSubracted = comment.Substring(last_match_index);
                        commentSubracted = commentSubracted.Trim().TrimStart('\n', '\r');
                        if (commentSubracted.Length > 0)
                        {
                            tasks.Add(commentSubracted);
                        }
                    }
                }
            }

            if (!tasks.Any())
            {
                var reportingItemToAdd = new ReportingItem(baseReportingItem);
                if (comment != null)
                {
                    ExtractActivitiesFromPartOfComment(comment, reportingItemToAdd);
                    if (reportingItemToAdd.Description != null)
                    {
                        ExtractTaskOutOfDescription(reportingItemToAdd);
                    }
                    result.Add(reportingItemToAdd);
                }
            }

            foreach (var task in tasks)
            {
                var reportingItemToAdd = new ReportingItem(baseReportingItem);
                reportingItemToAdd.Hours = null;
                if (task != null)
                {
                    ExtractActivitiesFromPartOfComment(task, reportingItemToAdd);
                    if (reportingItemToAdd.Description != null)
                    {
                        ExtractTaskOutOfDescription(reportingItemToAdd);
                    }
                    result.Add(reportingItemToAdd);
                }
            }

            return(result);
        }
Exemple #5
0
        private static void ExtractActivitiesFromPartOfComment(string commentToParse, ReportingItem itemToUpdate)
        {
            string[] timeMarkers = { "h", "hour", "hours" };

            commentToParse = commentToParse.TrimEnd(' ', '\r', '\n');

            foreach (string timeMarker in timeMarkers)
            {
                if (commentToParse.EndsWith(timeMarker))
                {
                    commentToParse           = commentToParse.Remove(commentToParse.Length - timeMarker.Length).TrimEnd();
                    itemToUpdate.Description = commentToParse;

                    ExtractHoursAtTheEndingOfString(itemToUpdate.Description, itemToUpdate);
                    return;
                }
            }

            itemToUpdate.Description = commentToParse;
            return;
        }
Exemple #6
0
        static void Main(string[] args)
        {
            Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");

            //const string inputPath = @"C:\Users\ssurnin\Downloads\OneDrive_1_2-7-2020\Example - input.xlsx";
            //const string inputPath = @"D:\Sources\ETL_For_COINS\OneDrive_1_07.02.2020\Example - input.xlsx";
            string outputPathDirectory = Directory.GetCurrentDirectory();
            string inputPathDirectory  = Directory.GetCurrentDirectory();

            //const string outputPath = @"D:\Sources\ETL_For_COINS\OneDrive_1_07.02.2020\Output.xlsx";

            string[] files = Directory.GetFiles(inputPathDirectory, "Reported_Time_Details*.xlsx");
            Console.WriteLine("The number of files with \"*.xlsx\" is {0}.", files.Length);
            foreach (string file in files)
            {
                Console.WriteLine(file);

                //read the Excel file as byte array
                byte[] bin = File.ReadAllBytes(file);

                //create a new Excel package in a memorystream
                using (MemoryStream stream = new MemoryStream(bin))
                    using (ExcelPackage excelPackage = new ExcelPackage(stream))
                    {
                        //load worksheet
                        ExcelWorksheet worksheet = excelPackage.Workbook.Worksheets[0];

                        DateTime startDate = (DateTime)worksheet.Cells[4, 2].Value; //rows[3][1].Cast<DateTime>();
                        DateTime endDate   = (DateTime)worksheet.Cells[5, 2].Value; //rows[4][1].Cast<DateTime>();
                                                                                    //DateTime startDate = Program.GetDatetimeFromCell(worksheet.Cells[4, 2].Value); //rows[3][1].Cast<DateTime>();
                                                                                    //DateTime endDate = Program.GetDatetimeFromCell(worksheet.Cells[5, 2].Value); //rows[4][1].Cast<DateTime>();

                        if (worksheet != null)
                        {
                            int startIndex = -1;

                            //loop all rows
                            for (int i = worksheet.Dimension.Start.Row; i <= worksheet.Dimension.End.Row; i++)
                            {
                                if ((string)worksheet.Cells[i, 1].Value == "Project")
                                {
                                    startIndex = i + 1;
                                    break;
                                }
                            }

                            List <InputExcelRow> readRows      = new List <InputExcelRow>();
                            List <ReportingItem> reportedItems = new List <ReportingItem>();

                            for (int i = startIndex; i <= worksheet.Dimension.End.Row; i++)
                            {
                                string[] row = new string[9];

                                //loop all columns in a row
                                for (int j = worksheet.Dimension.Start.Column; j <= worksheet.Dimension.End.Column; j++)
                                {
                                    //add the cell data to the List
                                    if (worksheet.Cells[i, j].Value != null)
                                    {
                                        row[j - 1] = worksheet.Cells[i, j].Value.ToString();
                                    }
                                }
                                var input = new InputExcelRow(row);

                                var reportingItem = new ReportingItem(input);

                                List <ReportingItem> parsedTasks = new List <ReportingItem>()
                                {
                                    reportingItem
                                };
                                if (input.Comment != null)
                                {
                                    parsedTasks = ReportingItem.ParseComment(input.Comment, reportingItem);
                                }

                                readRows.Add(input);
                                reportedItems.AddRange(parsedTasks);
                            }

                            //print rows here
                            foreach (var item in reportedItems)
                            {
                                string itemToPrint = item.ToString();
                                Console.WriteLine(itemToPrint);

                                Console.WriteLine();
                                Console.WriteLine();
                            }

                            string outputPath = outputPathDirectory + "\\" + $"COINS CCCA Teams Timesheets {startDate.ToString("MMM")}'{startDate.ToString("yy")}.xlsx";
                            ExcelWrite(outputPath, reportedItems, startDate, endDate);

                            Console.WriteLine($"The output file is '{outputPath}'");
                        }
                    }
            }

            Console.WriteLine("Execution is finished");

            Console.ReadKey();
        }