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