Beispiel #1
0
        public int SaveDistData(PersonModel person, LocationModel locn)
        {
            int ret = 0;

            using (MySqlConnection conn = GetConnection())
            {
                conn.Open();
                MySqlCommand cmd = conn.CreateCommand();

                cmd.CommandText = "SELECT process_dist_data(@FullName,@Location)";

                cmd.Parameters.Add("@FullName", MySqlDbType.VarChar, 45).Value = person.Name;
                cmd.Parameters.Add("@Location", MySqlDbType.VarChar, 45).Value = locn.LocationName;


                ret = (int)cmd.ExecuteScalar();
                conn.Close();
            }
            return(ret);
        }
Beispiel #2
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);
        }