Пример #1
0
        public FileReaderResults AddDistReport(SkillsMatrixDB database, string filePath, out List <string> unknownUsers)
        {
            FileReaderResults ret = FileReaderResults.FileReaderUnknownError;

            unknownUsers = new List <string>();

            Excel.Application xlApp       = new Excel.Application();
            Excel.Workbook    xlWorkbook  = xlApp.Workbooks.Open(filePath);
            Excel._Worksheet  xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets[1];

            Excel.Range xlRange = xlWorksheet.UsedRange;

            int rowCount   = xlRange.Rows.Count;
            int colCount   = xlRange.Columns.Count;
            int currentRow = 1;

            int matches = 0;

            try
            {
                //iterate over the rows and columns
                //excel is not zero based!!
                for (int col = 1; col <= colCount; col++)
                {
                    Excel.Range cell = (Excel.Range)xlWorksheet.Cells[currentRow, col];
                    if (cell.Value2 != null)
                    {
                        if (DistColumns.Contains(cell.Value2))
                        {
                            matches++;
                        }
                    }
                    Marshal.FinalReleaseComObject(cell);
                }

                if (matches == DistColumns.Count)
                {
                    ret = FileReaderResults.FileReaderSuccess;
                }
                else
                {
                    ret = FileReaderResults.FileReaderWrongFormat;
                }

                if (ret == FileReaderResults.FileReaderSuccess)
                {
                    for (currentRow++; currentRow <= rowCount; currentRow++)
                    {
                        PersonModel   person = new PersonModel();
                        LocationModel locn   = new LocationModel();


                        for (int col = 1; col <= colCount; col++)
                        {
                            Excel.Range cell = (Excel.Range)xlWorksheet.Cells[currentRow, col];

                            DistItem colIdx = (DistItem)(col - 1);

                            switch (colIdx)
                            {
                            case DistItem.MgrFullName:
                                // we only process people who are in the database already, so no need to get the manager
                                //                               person.ManagerName = cell.Value2.ToString();
                                break;

                            case DistItem.DistFullName:
                                person.Name = cell.Value2.ToString();
                                break;

                            case DistItem.DistLocation:
                                string locStr   = cell.Value2.ToString();
                                string hashCode = ComputeSha256Hash(locStr);
                                if (LocationMap.ContainsKey(hashCode))
                                {
                                    locn.LocationName = LocationMap[hashCode];
                                }
                                else
                                {
                                    locn.LocationName = "Unknown";
                                }
                                break;
                            }
                            Marshal.FinalReleaseComObject(cell);
                        }

                        if (database.SaveDistData(person, locn) != 1)
                        {
                            ret = FileReaderResults.FileReaderUnknownUser;
                            unknownUsers.Add(person.Name);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ret = FileReaderResults.FileReaderUnknownError;
            }
            finally
            {
                //close and release
                Marshal.FinalReleaseComObject(xlRange);
                Marshal.FinalReleaseComObject(xlWorksheet);

                //close and release
                xlWorkbook.Close();
                Marshal.FinalReleaseComObject(xlWorkbook);


                //quit and release
                xlApp.Quit();
                Marshal.FinalReleaseComObject(xlApp);

                // take the trash out
                CleanupExcel();
            }


            return(ret);
        }
Пример #2
0
        public FileReaderResults AddManagerSkillsReport(SkillsMatrixDB database, string filePath)
        {
            FileReaderResults ret = FileReaderResults.FileReaderUnknownError;

            Excel.Application xlApp       = new Excel.Application();
            Excel.Workbook    xlWorkbook  = xlApp.Workbooks.Open(filePath);
            Excel._Worksheet  xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets[1];

            Excel.Range xlRange = xlWorksheet.UsedRange;

            int rowCount   = xlRange.Rows.Count;
            int colCount   = xlRange.Columns.Count;
            int currentRow = 1;

            int matches = 0;

            //iterate over the rows and columns
            //excel is not zero based!!
            for (int col = 1; col <= colCount; col++)
            {
                Excel.Range cell = (Excel.Range)xlWorksheet.Cells[currentRow, col];
                if (cell.Value2 != null)
                {
                    if (ManagerColumns.Contains(cell.Value2))
                    {
                        matches++;
                    }
                }
                Marshal.FinalReleaseComObject(cell);
            }

            if (matches == ManagerColumns.Count)
            {
                ret = FileReaderResults.FileReaderSuccess;
            }
            else
            {
                ret = FileReaderResults.FileReaderWrongFormat;
            }

            try
            {
                if (ret == FileReaderResults.FileReaderSuccess)
                {
                    for (currentRow++; currentRow <= rowCount; currentRow++)
                    {
                        SkillsModel skill  = new SkillsModel();
                        PersonModel person = new PersonModel();

                        for (int col = 1; col <= colCount; col++)
                        {
                            Excel.Range cell = (Excel.Range)xlWorksheet.Cells[currentRow, col];

                            ManagerItem colIdx = (ManagerItem)(col - 1);

                            switch (colIdx)
                            {
                            case ManagerItem.ManagerName:
                                person.ManagerName = cell.Value2.ToString();
                                break;

                            case ManagerItem.FullName:
                                person.Name = cell.Value2.ToString();
                                break;

                            case ManagerItem.PersonnelNo:
                                person.PersonnelNo = Int32.Parse(cell.Value2.ToString());
                                break;

                            case ManagerItem.Email:
                                person.Email = cell.Value2.ToString();
                                break;

                            case ManagerItem.SkillName:
                                skill.SkillName = cell.Value2.ToString();
                                break;

                            case ManagerItem.SkillGroupName:
                                skill.GroupName = cell.Value2.ToString();
                                break;

                            case ManagerItem.SkillGroupParentName:
                                skill.GroupParentName = cell.Value2.ToString();
                                break;

                            case ManagerItem.RequiredSkill:
                                if (cell.Value2 != null)
                                {
                                    skill.RequiredSkillLevel = cell.Value2.ToString();
                                }
                                else
                                {
                                    skill.RequiredSkillLevel = "Not Set";
                                }
                                break;

                            case ManagerItem.HeldSkill:
                                if (cell.Value2 != null)
                                {
                                    skill.HeldSkillLevel = cell.Value2.ToString();
                                }
                                else
                                {
                                    skill.HeldSkillLevel = "Not Set";
                                }
                                break;
                            }
                            Marshal.FinalReleaseComObject(cell);
                        }

                        if (database.SaveManagerSkill(person, skill) != 1)
                        {
                            ret = FileReaderResults.FileReaderErrorProcessingFile;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ret = FileReaderResults.FileReaderUnknownError;
            }
            finally
            {
                //close and release
                Marshal.FinalReleaseComObject(xlRange);
                Marshal.FinalReleaseComObject(xlWorksheet);

                //close and release
                xlWorkbook.Close();
                Marshal.FinalReleaseComObject(xlWorkbook);


                //quit and release
                xlApp.Quit();
                Marshal.FinalReleaseComObject(xlApp);

                // take the trash out
                CleanupExcel();
            }

            return(ret);
        }
Пример #3
0
        public FileReaderResults AddPpmReport(SkillsMatrixDB database, string filePath, out List <string> unknownUsers)
        {
            FileReaderResults ret = FileReaderResults.FileReaderUnknownError;

            unknownUsers = new List <string>();

            Excel.Application xlApp       = new Excel.Application();
            Excel.Workbook    xlWorkbook  = xlApp.Workbooks.Open(filePath);
            Excel.Worksheet   xlWorksheet = (Excel.Worksheet)xlWorkbook.Sheets[1];

            Excel.Range xlRange = xlWorksheet.UsedRange;

            int rowCount   = xlRange.Rows.Count;
            int colCount   = xlRange.Columns.Count;
            int currentRow = 1;

            int matches = 0;

            //iterate over the rows and columns
            //excel is not zero based!!
            for (int col = 1; col <= colCount; col++)
            {
                Excel.Range cell = (Excel.Range)xlWorksheet.Cells[currentRow, col];
                if (cell.Value2 != null)
                {
                    if (PpmColumns.Contains(cell.Value2))
                    {
                        matches++;
                    }
                }
                Marshal.FinalReleaseComObject(cell);
            }

            if (matches == PpmColumns.Count)
            {
                ret = FileReaderResults.FileReaderSuccess;
            }
            else
            {
                ret = FileReaderResults.FileReaderWrongFormat;
            }

            try
            {
                if (ret == FileReaderResults.FileReaderSuccess)
                {
                    var    path        = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "ppm_errors.txt");
                    string currUser    = "";
                    string currProj    = "";
                    double currUtilise = 0;



                    for (currentRow++; currentRow <= rowCount; currentRow++)
                    {
                        ProjectModel    project               = new ProjectModel();
                        PersonModel     person                = new PersonModel();
                        AssignmentModel projAssign            = new AssignmentModel();
                        int             numUtilisations       = 0;
                        double          cumulativeUtilisation = 0;


                        for (int col = 1; col <= colCount; col++)
                        {
                            Excel.Range cell = (Excel.Range)xlWorksheet.Cells[currentRow, col];



                            if ((col - 1) <= (int)PpmItem.PpmMgr)
                            {
                                PpmItem colIdx = (PpmItem)(col - 1);
                                try
                                {
                                    switch (colIdx)
                                    {
                                    case PpmItem.PpmMgr:
                                        // manager doesn't seem to be populated, so ignore it (we don't pass it to the DB function)
                                        //                               person.ManagerName = cell.Value2.ToString();
                                        break;

                                    case PpmItem.PpmFullName:
                                        person.Name = cell.Value2.ToString();
                                        break;

                                    case PpmItem.PpmPersonnelNo:
                                        person.PersonnelNo = Int32.Parse(cell.Value2.ToString());
                                        break;

                                    case PpmItem.PpmEmail:
                                        person.Email = cell.Value2.ToString();
                                        break;

                                    case PpmItem.PpmClient:
                                        project.ProjectName = cell.Value2.ToString();
                                        break;

                                    case PpmItem.PpmStart:
                                        projAssign.ProjectStartDate = DateTime.Parse(cell.Value.ToString());
                                        break;

                                    case PpmItem.PpmEnd:
                                        projAssign.ProjectEndDate = DateTime.Parse(cell.Value.ToString());
                                        break;

                                    case PpmItem.PpmStatus:
                                        projAssign.AssignmentStatus = cell.Value2.ToString();
                                        break;
                                    }
                                }
                                catch (Exception ex)
                                {
                                    ret = FileReaderResults.FileReaderErrorProcessingFile;
                                }
                            }
                            else
                            {
                                double utilVal = (double)cell.Value2;

                                if (utilVal > 0)
                                {
                                    numUtilisations++;
                                    cumulativeUtilisation += utilVal;
                                }
                            }
                            Marshal.FinalReleaseComObject(cell);
                        }
                        if (numUtilisations > 0)
                        {
                            projAssign.Utilisation = (cumulativeUtilisation / numUtilisations);
                        }
                        else
                        {
                            projAssign.Utilisation = 0;
                        }
                        if (String.Compare(currUser, person.Name) == 0)
                        {
                            if (String.Compare(currProj, project.ProjectName) == 0)
                            {
                                currUtilise           += projAssign.Utilisation;
                                projAssign.Utilisation = currUtilise;
                            }
                            else
                            {
                                currUtilise = 0;
                                currProj    = project.ProjectName;
                            }
                        }
                        else
                        {
                            currUser    = person.Name;
                            currProj    = project.ProjectName;
                            currUtilise = projAssign.Utilisation;
                        }
                        if (database.SavePpmData(person, project, projAssign) != 1)
                        {
                            ret = FileReaderResults.FileReaderUnknownUser;
                            unknownUsers.Add(person.Name);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                ret = FileReaderResults.FileReaderUnknownError;
            }
            finally
            {
                Marshal.FinalReleaseComObject(xlRange);
                Marshal.FinalReleaseComObject(xlWorksheet);

                //close and release
                xlWorkbook.Close();
                Marshal.FinalReleaseComObject(xlWorkbook);


                //quit and release
                xlApp.Quit();
                Marshal.FinalReleaseComObject(xlApp);

                // take the trash out
                CleanupExcel();
            }

            return(ret);
        }