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);
}
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);
}