private void PopulateSheet(List <NotableApplication> apps, List <DeveloperNames> developers) //, DateTime? startTime, DateTime? endTime)
{
var hlpr = new DHReports(string.Empty);
var ws = Excel.Workbook.Worksheets[1];
var sql = CreateSQL(apps, developers, StartTime, EndTime);
DataSet ds = hlpr.GetDataSetFromSQL(sql);
var rowId = 0;
//Table 0 is detail
var dt = ds.Tables[0];
for (int i = 0; i < dt.Rows.Count; i++)
{
rowId = i + 3;
ws.Cells[rowId, 1].Value = dt.Rows[i]["Application Name"].GetNotDBNull();
WriteNumberCell(ws, rowId, 2, dt.Rows[i]["Hours"].GetNotDBNullInt());
ws.Cells[rowId, 3].Value = dt.Rows[i]["Minutes"].GetNotDBNullInt();
ws.Cells[rowId, 4].Value = dt.Rows[i]["Seconds"].GetNotDBNullInt();
ws.Cells[rowId, 5].Value = dt.Rows[i]["UserDisplayName"];
//ws.Cells[rowId, 5].Value = GetPrjPath((string)ws.Cells[rowId, 1].Value, apps);
}
if (ds.Tables[1].Rows.Count > 0)
{
ws.Cells[rowId + 2, 1].Value = ds.Tables[1].Rows[0]["Total"].GetNotDBNull();
WriteNumberCell(ws, rowId + 2, 2, ds.Tables[1].Rows[0]["Hours"].GetNotDBNullInt());
ws.Cells[rowId + 2, 3].Value = ds.Tables[1].Rows[0]["Minutes"].GetNotDBNullInt();
ws.Cells[rowId + 2, 4].Value = ds.Tables[1].Rows[0]["Seconds"].GetNotDBNullInt();
}
Excel.SaveAs(ExcelFile);
Excel.Dispose();
}
//private void PopulateSheet(List<ReportProjects> projects, List<DeveloperNames> developers, DateTime? startTime, DateTime? endTime)
private void PopulateSheet(List <ProjectNameAndSync> projects, List <DeveloperNames> developers, DateTime?startTime, DateTime?endTime)
{
try
{
var lastProject = string.Empty;
var hlpr = new DHReports(string.Empty);
var ws = Excel.Workbook.Worksheets[1];
var sql = CreateSQL(projects, developers, StartTime, EndTime);
DataSet ds = hlpr.GetDataSetFromSQL(sql);
var rowId = 0;
// grand total ctrs
var mins = 0;
var hrs = 0;
var secs = 0;
// sub total counters
var subMins = 0;
var subHrs = 0;
var subSecs = 0;
// temp ctrs
var tmpSecs = 0;
var tmpMins = 0;
var tmpHrs = 0;
//Table 0 is detail
var dt = ds.Tables[0];
rowId = 3;
for (int i = 0; i < dt.Rows.Count; i++)
{
var dr = dt.Rows[i];
if (i == 0)
{
lastProject = dt.Rows[i]["Project Name"].GetNotDBNull();
ws.Cells[rowId, 1].Value = $"Project: {lastProject}";
ws.Cells[rowId, 1].Style.Font.Bold = true;
ws.Cells[rowId, 1].Style.Font.UnderLine = true;
rowId++;
}
var thisRowSecs = dr["Seconds"].GetNotDBNullInt();
var thisRowMins = dr["Minutes"].GetNotDBNullInt();
var thisRowHours = dr["Hours"].GetNotDBNullInt();
if (lastProject != dr["Project Name"].GetNotDBNull())
{
// compute ctrs for sub total and put in the sheet
tmpSecs = 0;
tmpMins = subMins + Math.DivRem(subSecs, 60, out tmpSecs);
tmpHrs = subHrs + Math.DivRem(subMins, 60, out tmpMins);
ws.Cells[rowId, 1].Value = "Sub Total";
ws.Cells[rowId, 1].Style.Font.Bold = true;
WriteNumberCell(ws, rowId, 2, tmpHrs);
ws.Cells[rowId, 3].Value = tmpMins;
ws.Cells[rowId, 4].Value = tmpSecs;
rowId += 2;
lastProject = dr["Project Name"].GetNotDBNull();
rowId++;
ws.Cells[rowId, 1].Value = $"Project: {lastProject}";
ws.Cells[rowId, 1].Style.Font.Bold = true;
ws.Cells[rowId, 1].Style.Font.UnderLine = true;
rowId++;
subHrs = subMins = subSecs = 0; // reset ctrs for next user
}
// mow deal with current row data
ws.Cells[rowId, 1].Value = dr["Project Name"].GetNotDBNull();
WriteNumberCell(ws, rowId, 2, thisRowHours);
ws.Cells[rowId, 3].Value = thisRowMins;
ws.Cells[rowId, 4].Value = thisRowSecs;
ws.Cells[rowId, 5].Value = dr["UserDisplayName"];
//ws.Cells[rowId, 6].Value = GetPrjPath((string)ws.Cells[rowId, 1].Value, projects);
rowId++;
// accumulate ctrs this user
subMins += thisRowMins;
subHrs += thisRowHours;
subSecs += thisRowSecs;
// accumulate ctrs for grand total
hrs += thisRowHours;
mins += thisRowMins;
secs += thisRowSecs;
}
// running out of dt rows may have left us with time unreported for
// last user
if (subHrs != 0 || subMins != 0 || subSecs != 0)
{
//secs += subSecs;
//mins += subMins;
//hrs += subHrs;
tmpSecs = 0;
tmpMins = subMins + Math.DivRem(subSecs, 60, out tmpSecs);
tmpHrs = subHrs + Math.DivRem(subMins, 60, out tmpMins);
rowId++;
ws.Cells[rowId, 1].Value = "Sub Total";
ws.Cells[rowId, 1].Style.Font.Bold = true;
WriteNumberCell(ws, rowId, 2, tmpHrs);
ws.Cells[rowId, 3].Value = tmpMins;
ws.Cells[rowId, 4].Value = tmpSecs;
rowId += 1;
}
tmpSecs = 0;
tmpMins = mins + Math.DivRem(secs, 60, out tmpSecs);
tmpHrs = hrs + Math.DivRem(mins, 60, out tmpMins);
ws.Cells[rowId + 2, 1].Value = "Grand Total";
ws.Cells[rowId + 2, 1].Style.Font.Bold = true;
WriteNumberCell(ws, rowId + 2, 2, tmpHrs);
ws.Cells[rowId + 2, 3].Value = tmpMins;
ws.Cells[rowId + 2, 4].Value = tmpSecs;
Excel.SaveAs(ExcelFile);
Excel.Dispose();
}
catch (Exception ex)
{
_ = new LogError(ex, false, "ProjectReportByProject.PopulateSheet");
}
}
private void PopulateSheet(List <ProjectNameAndSync> projects, List <DeveloperNames> developers, DateTime?startTime, DateTime?endTime)
{
try
{
var lastProject = string.Empty;
var hlpr = new DHReports(string.Empty);
var ws = Excel.Workbook.Worksheets[1];
var sql = CreateSQL(projects, developers, StartTime, EndTime);
DataSet ds = hlpr.GetDataSetFromSQL(sql);
var rowId = 0;
// grand total ctrs
var mins = 0;
var hrs = 0;
var secs = 0;
// sub total counters
var subMins = 0;
var subHrs = 0;
var subSecs = 0;
// temp ctrs
var tmpSecs = 0;
var tmpMins = 0;
var tmpHrs = 0;
//Table 0 is detail
var dt = ds.Tables[0];
rowId = 3;
for (int i = 0; i < dt.Rows.Count; i++)
{
var dr = dt.Rows[i];
string appName = dr["appname"].GetNotDBNull();
var nameObj = AppList.Find(x => x.AppName == appName);
appName = nameObj != null && !string.IsNullOrWhiteSpace(nameObj.AppFriendlyName) ? nameObj.AppFriendlyName : appName;
if (i == 0)
{
lastProject = dt.Rows[i]["Project Name"].GetNotDBNull();
ws.Cells[rowId, 1].Value = $"Project: {lastProject}";
ws.Cells[rowId, 1].Style.Font.Bold = true;
ws.Cells[rowId, 1].Style.Font.UnderLine = true;
rowId++;
}
var thisRowSecs = dr["Seconds"].GetNotDBNullInt();
var thisRowMins = dr["Minutes"].GetNotDBNullInt();
var thisRowHours = dr["Hours"].GetNotDBNullInt();
if (lastProject != dr["Project Name"].GetNotDBNull())
{
// compute ctrs for sub total and put in the sheet
tmpSecs = 0;
tmpMins = subMins + Math.DivRem(subSecs, 60, out tmpSecs);
tmpHrs = subHrs + Math.DivRem(subMins, 60, out tmpMins);
ws.Cells[rowId, 1].Value = "Sub Total";
ws.Cells[rowId, 1].Style.Font.Bold = true;
ws.Cells[rowId, 2].Value = tmpHrs;
ws.Cells[rowId, 3].Value = tmpMins;
ws.Cells[rowId, 4].Value = tmpSecs;
rowId += 2;
lastProject = dr["Project Name"].GetNotDBNull();
rowId++;
ws.Cells[rowId, 1].Value = $"Project: {lastProject}";
ws.Cells[rowId, 1].Style.Font.Bold = true;
ws.Cells[rowId, 1].Style.Font.UnderLine = true;
rowId++;
subHrs = subMins = subSecs = 0; // reset ctrs for next user
}
// now deal with current row data
ws.Cells[rowId, 1].Value = dr["Project Name"].GetNotDBNull();
WriteNumberCell(ws, rowId, 2, thisRowHours);
ws.Cells[rowId, 3].Value = thisRowMins;
ws.Cells[rowId, 4].Value = thisRowSecs;
ws.Cells[rowId, 5].Value = dr["UserDisplayName"];
ws.Cells[rowId, 6].Value = appName; //dr["appname"].GetNotDBNull().ToUpper();
rowId++;
// accumulate ctrs this user
subMins += thisRowMins;
subHrs += thisRowHours;
subSecs += thisRowSecs;
// accumulate ctrs for grand total
hrs += thisRowHours;
mins += thisRowMins;
secs += thisRowSecs;
}
// running out of dt rows may have left us with time unreported for
// last user
if (subHrs != 0 || subMins != 0 || subSecs != 0)
{
tmpSecs = 0;
tmpMins = subMins + Math.DivRem(subSecs, 60, out tmpSecs);
tmpHrs = subHrs + Math.DivRem(subMins, 60, out tmpMins);
rowId++;
ws.Cells[rowId, 1].Value = "Sub Total";
ws.Cells[rowId, 1].Style.Font.Bold = true;
WriteNumberCell(ws, rowId, 2, tmpHrs);
ws.Cells[rowId, 3].Value = tmpMins;
ws.Cells[rowId, 4].Value = tmpSecs;
rowId += 1;
}
tmpSecs = 0;
tmpMins = mins + Math.DivRem(secs, 60, out tmpSecs);
tmpHrs = hrs + Math.DivRem(mins, 60, out tmpMins);
ws.Cells[rowId + 2, 1].Value = "Time Grand Total";
ws.Cells[rowId + 2, 1].Style.Font.Bold = true;
WriteBoldNumberCell(ws, rowId + 2, 2, tmpHrs);
WriteBoldNumberCell(ws, rowId + 2, 3, tmpMins);
WriteBoldNumberCell(ws, rowId + 2, 4, tmpSecs);
// let's play with some number
int totSlnSeconds = tmpSecs + (tmpMins * 60) + (tmpHrs * 3600);
// the time portion of the report is complete, write out the files part
// write the header Row
rowId += 5;
lastProject = string.Empty;
dt = ds.Tables[1];
if (dt.Rows.Count > 0)
{
//CreateHdrBoldStyle(ws, HdrRange);
WriteBoldCell(ws, rowId, 1, "DevProjectName");
WriteBoldCell(ws, rowId, 2, "Relative FileName");
WriteBoldCell(ws, rowId, 3, "CodeLines");
WriteBoldCell(ws, rowId, 4, "CommentLines");
WriteBoldCell(ws, rowId, 5, "BlankLines");
WriteBoldCell(ws, rowId, 6, "DesignerLines");
WriteBoldCell(ws, rowId, 7, "AllLines");
WriteBoldCell(ws, rowId, 8, "UpdateCount (For Binary Files = Rebuilds)");
rowId++;
string currProject;
rowId++;
for (int i = 0; i < dt.Rows.Count; i++)
{
var dr = dt.Rows[i];
if (i.Equals(0))
{
lastProject = dr["DevProjectName"].GetNotDBNull();
//WriteBoldCell(ws, rowId, 1, lastProject);
//rowId++;
}
currProject = (string)dr["DevProjectName"];
if (lastProject != currProject)
{
// compute ctrs for sub total and put in the sheet
lastProject = currProject;
WriteBoldCell(ws, rowId, 1, "Sub Total");
WriteNumberCell(ws, rowId, 3, subCodeLines);
WriteNumberCell(ws, rowId, 4, subCommentLines);
WriteNumberCell(ws, rowId, 5, subBlankLines);
WriteNumberCell(ws, rowId, 6, subDesignerLines);
WriteNumberCell(ws, rowId, 7, subTotalAllLines);
rowId += 2;
subCodeLines = subCommentLines = subBlankLines = subDesignerLines = subTotalAllLines = 0;
}
// now deal with current row
WriteLinesRow(dr, ws, rowId);
rowId++;
}
// running out of dt rows may have left us with time unreported for
// last user
if (subCodeLines != 0 || subCommentLines != 0 || subBlankLines != 0 || subDesignerLines != 0)
{
WriteBoldCell(ws, rowId, 1, "Sub Total");
WriteNumberCell(ws, rowId, 3, subCodeLines);
WriteNumberCell(ws, rowId, 4, subCommentLines);
WriteNumberCell(ws, rowId, 5, subBlankLines);
WriteNumberCell(ws, rowId, 6, subDesignerLines);
WriteNumberCell(ws, rowId, 7, subTotalAllLines);
rowId += 2;
}
// print grand totals
WriteBoldCell(ws, rowId, 1, "Code Lines Grand Totals");
WriteBoldNumberCell(ws, rowId, 3, totCodeLines);
WriteBoldNumberCell(ws, rowId, 4, totCommentLines);
WriteBoldNumberCell(ws, rowId, 5, totBlankLines);
WriteBoldNumberCell(ws, rowId, 6, totDesignerLines);
WriteBoldNumberCell(ws, rowId, 7, totTotalAllLines);
// now, continue playing with numbers
// designer generated lines are not free b/c the develope
// used the designer to build the form, etc. and the generated
// lines were a result of that work
var secsPerTotalLines = totSlnSeconds / totTotalAllLines;
var secsPerCodeLine = totSlnSeconds / totCodeLines;
rowId += 2;
WriteBoldCell(ws, rowId, 1, "Seconds Per All Type Lines:");
WriteBoldNumberCell(ws, rowId, 7, secsPerTotalLines);
rowId++;
WriteBoldCell(ws, rowId, 1, "Seconds Per Just Code Lines:");
WriteBoldNumberCell(ws, rowId, 3, secsPerCodeLine);
// get elapsed time from the project/solution
var stTime = (DateTime)ds.Tables[2].Rows[0]["StartTime"];
var eTime = (DateTime)ds.Tables[3].Rows[0]["EndTime"];
var elpTime = ((eTime - stTime).TotalDays) / 7 * 5 * 8 * 3600;
TimeSpan elapsed = eTime.Subtract(stTime);
// Get number of days ago.
double daysAgo = elapsed.TotalDays / 7 * 5;
var sd = stTime.ToString("MM/dd/yyyy");
var ed = eTime.ToString("MM/dd/yyyy");
WriteBoldCell(ws, ++rowId, 1, $"Coding Started: {sd}");
WriteBoldCell(ws, ++rowId, 1, $"Last Coding: {ed}");
WriteBoldCell(ws, ++rowId, 1, $"Elapsed Work Days: {Math.Truncate(daysAgo)}");
}
Excel.SaveAs(ExcelFile);
Excel.Dispose();
}
catch (Exception ex)
{
_ = new LogError(ex.Message, false, "ProjectDetail.PopulateSheet");
}
}