public int SavePpmData(PersonModel person, ProjectModel project, AssignmentModel projAssign)
{
int ret = 0;
using (MySqlConnection conn = GetConnection())
{
conn.Open();
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT process_ppm_data(@FullName,@Email,@PersonnelNo,@Status,@Project,@ProjStart,@ProjEnd,@ProjUtil)";
cmd.Parameters.Add("@FullName", MySqlDbType.VarChar, 45).Value = person.Name;
cmd.Parameters.Add("@Email", MySqlDbType.VarChar, 320).Value = person.Email;
cmd.Parameters.Add("@PersonnelNo", MySqlDbType.Int32).Value = person.PersonnelNo;
cmd.Parameters.Add("@Status", MySqlDbType.VarChar, 45).Value = projAssign.AssignmentStatus;
cmd.Parameters.Add("@Project", MySqlDbType.VarChar, 45).Value = project.ProjectName;
cmd.Parameters.Add("@ProjStart", MySqlDbType.Date).Value = projAssign.ProjectStartDate;
cmd.Parameters.Add("@ProjEnd", MySqlDbType.Date).Value = projAssign.ProjectEndDate;
cmd.Parameters.Add("@ProjUtil", MySqlDbType.Double).Value = projAssign.Utilisation;
ret = (int)cmd.ExecuteScalar();
conn.Close();
}
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);
}