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