public static string Secondheader(DataSet ds, string report, string section) { List <KPI> totals = Datamethods.Getsecondtotals_ForReport(report, section); int tables = ds.Tables.Count; StringBuilder HTMLtoRender = new StringBuilder(); if (totals.Count > 0) { HTMLtoRender.AppendFormat("<table class='table table-striped' align ='center' width='100%' style=\"font:15px/20px arial,sans-serif\">", 100000); HTMLtoRender.Append("<tr>"); foreach (KPI kp in totals) { foreach (DataTable dt in ds.Tables) { if (dt.Columns.Contains(kp.Columnnames.Split(',')[0])) { double main = ds.Tables[0].AsEnumerable().Sum(s => s.Field <int>(kp.Columnnames.Split(',')[0])); double pp = ds.Tables[0].AsEnumerable().Sum(s => s.Field <int>(kp.Columnnames.Split(',')[1])); double ans = Math.Round(((pp - main) / pp) * 100, 2); HTMLtoRender.Append("<td style='width:100px;height:50px;color:whitesmoke;background-color:gray'>"); HTMLtoRender.Append("<CENTER><b>" + kp.KpiName + "</b></CENTER>"); HTMLtoRender.Append("</br><CENTER><b>" + (100 - ans).ToString() + "</b></CENTER>"); HTMLtoRender.Append("</td>"); } } } HTMLtoRender.Append("</tr>"); HTMLtoRender.Append("</table>"); } return(HTMLtoRender.ToString()); }
// this method will determine what operation need to be done on a table public static DataSet Combinetables(DataSet ds, string name, string section) { bool hasvalue; List <TableJoin> tJ = Datamethods.Table_Joins(name, section, out hasvalue); DataSet newdata = new DataSet(); try { if (hasvalue == true) { foreach (TableJoin table in tJ) { switch (table.Operation) { case "Join": DataTable one = Tableoperations.myJoinMethod(ds.Tables[table.table1], ds.Tables[table.table2], table.Commoncolumn, table.Commoncolumn); DataTable gottam = one.Copy(); newdata.Tables.Add(gottam); break; case "Merge": DataTable two = new DataTable(); two.Merge(ds.Tables[table.table1], false, MissingSchemaAction.Add); two.Merge(ds.Tables[table.table2], false, MissingSchemaAction.Add); newdata.Tables.Add(two); break; case "NO": DataTable three = ds.Tables[table.table1]; DataTable four = three.Copy(); newdata.Tables.Add(four); break; } } } else { newdata = ds; } } catch { } return(newdata); }
public static string GenerateKPIReport(DataSet ds, string report, int UserId) { IEnumerable <tbl_KPI> SlA = Datamethods.GetKPI_Site(UserId); List <KPI> kpi = Datamethods.GetKPI_ForReport(report, "Kpi"); StringBuilder HTMLtoRender = new StringBuilder(); try { HTMLtoRender.Append("<p><b>KPI Vs SLA:</b></P>"); HTMLtoRender.Append("<table width='100%' style='border-style: dotted'>"); foreach (KPI k in kpi) { HTMLtoRender.Append("<tr>"); HTMLtoRender.Append("<td colspan='3'>"); HTMLtoRender.Append("<CENTER><h3>" + k.KpiName + "</h3></CENTER>"); HTMLtoRender.Append("</td>"); HTMLtoRender.Append("</tr>"); HTMLtoRender.Append("<tr>"); HTMLtoRender.Append("<td style='width:200px'>"); HTMLtoRender.Append("SLA =<b>" + GetSlarate(k.KpiName, SlA) + "</b>"); HTMLtoRender.Append("</td>"); HTMLtoRender.Append("<td style='width:200px'>"); double main = ds.Tables[0].AsEnumerable().Sum(s => s.Field <int>(k.Columnnames.Split(',')[0])); double kp = ds.Tables[0].AsEnumerable().Sum(s => s.Field <int>(k.Columnnames.Split(',')[1])); double ans = Math.Round(((kp - main) / kp) * 100, 2); HTMLtoRender.Append("KPI =<b>" + Math.Round((100 - ans), 2).ToString() + "</b>%"); HTMLtoRender.Append("</td>"); HTMLtoRender.Append("<td style='width:100px'>"); HTMLtoRender.Append("Diff =<b>" + ans.ToString() + "</b>%"); HTMLtoRender.Append("</td>"); HTMLtoRender.Append("</tr>"); } HTMLtoRender.Append("</table>"); } catch { } return(HTMLtoRender.ToString()); }
public static string Createheader(DataSet ds, string report, string section) { List <ColumnTotals> totals = Datamethods.GetHeaderValues(report, section); int tables = ds.Tables.Count; StringBuilder HTMLtoRender = new StringBuilder("<table class='table table-striped' align ='center' width='100%' style=\"font:15px/20px arial,sans-serif\">", 100000); try { HTMLtoRender.Append("<tr>"); int i = 0; foreach (ColumnTotals c1 in totals) { foreach (DataTable dt in ds.Tables) { if (dt.Columns.Contains(c1.ColumnName.Split('~')[0])) { if (!HTMLtoRender.ToString().Contains(c1.Friendlyname)) { Color color = colurs[i]; string myHexString = String.Format("#{0:X2}{1:X2}{2:X2}", color.R, color.G, color.B); HTMLtoRender.Append("<td style='width:100px;height:50px;color:whitesmoke;background-color:" + myHexString + " '>"); HTMLtoRender.Append("<CENTER><h2>" + c1.Friendlyname + "</h2></CENTER>"); switch (c1.Totaltype) { case "Sum": if (c1.ColumnName.ToLower().Contains("cost")) { HTMLtoRender.Append("</br><CENTER><h2>" + Math.Round(dt.AsEnumerable().Sum(s => s.Field <decimal>(c1.ColumnName)), 2).ToString() + "</h2></CENTER>"); } else { HTMLtoRender.Append("</br><CENTER><h2>" + dt.AsEnumerable().Sum(s => s.Field <int>(c1.ColumnName)).ToString() + "</h2></CENTER>"); } break; case "Field": HTMLtoRender.Append("</br><CENTER><h2>" + Convert.ToString(dt.Rows[0][c1.ColumnName]) + "</h2></CENTER>"); break; case "Minus": int toatalminus = 0; if (dt.Columns.Contains(c1.ColumnName.Split('~')[0])) { toatalminus = dt.AsEnumerable().Sum(s => s.Field <int>(c1.ColumnName.Split('~')[0])); } if (dt.Columns.Contains(c1.ColumnName.Split('~')[1])) { toatalminus = toatalminus - dt.AsEnumerable().Sum(s => s.Field <int>(c1.ColumnName.Split('~')[1])); } HTMLtoRender.Append("</br><CENTER><h2>" + Convert.ToString(toatalminus) + "</h2></CENTER>"); break; case "Percentage1": int percent1 = 0, percent2 = 1; if (dt.Columns.Contains(c1.ColumnName.Split('~')[0])) { percent1 = dt.AsEnumerable().Sum(s => s.Field <int>(c1.ColumnName.Split('~')[0])); } if (dt.Columns.Contains(c1.ColumnName.Split('~')[1])) { percent2 = dt.AsEnumerable().Sum(s => s.Field <int>(c1.ColumnName.Split('~')[1])); } if (percent1 != 0 && percent2 != 0) { HTMLtoRender.Append("</br><CENTER><h2>" + Math.Round((Convert.ToDouble(percent1) / Convert.ToDouble(percent2)) * 100, 2).ToString() + "</h2></CENTER>"); } break; case "Percentage2": int percent11 = 0, percent22 = 1; if (dt.Columns.Contains(c1.ColumnName.Split('~')[0])) { percent11 = dt.AsEnumerable().Sum(s => s.Field <int>(c1.ColumnName.Split('~')[0])); } if (dt.Columns.Contains(c1.ColumnName.Split('~')[1])) { percent22 = dt.AsEnumerable().Sum(s => s.Field <int>(c1.ColumnName.Split('~')[1])); } if (percent11 != 0 && percent22 != 0) { HTMLtoRender.Append("</br><CENTER><h2>" + (100 - Math.Round((Convert.ToDouble(percent11) / Convert.ToDouble(percent22)) * 100, 2)).ToString() + "</h2></CENTER>"); } break; case "Count": HTMLtoRender.Append("</br><CENTER><h2>" + dt.Rows.Count.ToString() + "</h2></CENTER>"); break; case "Avg": HTMLtoRender.Append("</br><CENTER><h2>" + Math.Round(dt.AsEnumerable().Average(s => s.Field <int>(c1.ColumnName)), 2).ToString() + "</h2></CENTER>"); break; case "Avg Duration": int val = Convert.ToInt32(Math.Round(dt.AsEnumerable().Average(s => s.Field <int>(c1.ColumnName)))); TimeSpan ts = new TimeSpan(0, 0, val); HTMLtoRender.Append("</br><CENTER><h2>" + ts.ToString() + "</h2></CENTER>"); break; case "Total Duration": if (c1.ColumnName.Contains("~")) { int toatalsum = 0; foreach (string str in c1.ColumnName.Split('~')) { toatalsum += Convert.ToInt32(dt.AsEnumerable().Sum(s => s.Field <int>(str))); } TimeSpan ts1 = new TimeSpan(0, 0, toatalsum); HTMLtoRender.Append("</br><CENTER><h2>" + ts1.ToString() + "</h2></CENTER>"); } else { int val1 = Convert.ToInt32(dt.AsEnumerable().Sum(s => s.Field <int>(c1.ColumnName))); TimeSpan ts1 = new TimeSpan(0, 0, val1); HTMLtoRender.Append("</br><CENTER><h2>" + ts1.ToString() + "</h2></CENTER>"); } break; default: break; } HTMLtoRender.Append("</td>"); i++; } } else { if (c1.Friendlyname == "BREAK") { HTMLtoRender.Append("</tr>"); HTMLtoRender.Append("<tr>"); break; } } } } HTMLtoRender.Append("</tr>"); } catch { } HTMLtoRender.Append("</table>"); return(HTMLtoRender.ToString()); }
// This method will create HTML file and inserts into portfolio reports..... public static void GenerateHTMLReport(Schedule report) { // Getting report data DataSet tables = ExtecuteReport(report); if (tables.Tables[0].Rows.Count >= 1 || report.StoredProcedureName.Contains("spSelectDepartmentalBreakdownreportLevel")) { TextWriter twWriter = new StreamWriter("c:\\temp\\" + report.Selectedname + "-" + report.ReportName + DateTime.Now.ToString("ddMMyy") + "-" + report.ID + ".html"); string[] filt = report.ListofFilters.Split('=', ','); try { twWriter.Write("<meta http-equiv='Content-Type' content='text/html'; charset='utf-8'>"); twWriter.Write("<script language='javascript' type='text/javascript' src='https://www.sentelsolutions.com/Scripts/jquery-1.4.2.min.js'></script>"); twWriter.Write("<link href='https://www.sentelsolutions.com/bootstrap/css/bootstrap.css' rel='stylesheet' />"); twWriter.Write("<script src='https://www.sentelsolutions.com/bootstrap/Scripts/bootstrap.min.js'></script>"); if (Otherreports(report.ReportingSection) == false) { twWriter.Write("</br>"); twWriter.Write("</br>"); twWriter.Write("<CENTER><h1>" + report.ReportName + "</h1></CENTER>"); twWriter.Write("</br>"); twWriter.Write("</br>"); twWriter.Write("<b>Schedule Name: " + " " + report.Selectedname + "</b>"); twWriter.Write("</br>"); twWriter.Write("</br>"); twWriter.Write("<b>Date range: " + " " + report.ListofFilters.Split('=', ',')[5].TrimStart('\'').TrimEnd('\'') + " TO " + report.ListofFilters.Split('=', ',')[7].TrimStart('\'').TrimEnd('\'') + "</b>");//"+ Fromdate.ToString()+" - " Todate.ToString()+"</b>"); twWriter.Write("</br>"); twWriter.Write("</br>"); twWriter.Write("<div id='Linearcalllist' runat='server' style='border: 1px solid black;overflow: auto;'>"); if (report.StoredProcedureName.Contains("spSelectDepartmentalBreakdownreportLevel")) { // The level is hardcodeed for debug .... //twWriter.Write(HTMLReports.Departmentalreport(tables, GetColumns(report.Columns),"4")); HTMLReports.Departmentalreport(twWriter, tables, GetColumns(report.Columns), report.ListofFilters.Split('=', ',')[93], GetLevels(tables), Listoftotals(report.Totals)); } else { if (tables.Tables.Count >= 3) { int i = 0; foreach (DataTable dt in tables.Tables) { List <string> columns = new List <string>(); foreach (DataColumn ds in dt.Columns) { columns.Add(ds.ColumnName); } twWriter.Write("<b>" + report.Columns.Split(',')[i] + "</b>"); twWriter.Write(HTMLReports.BindHTMLdata(dt, columns, Listoftotals(report.Totals))); twWriter.Write("</br>"); i++; } } else { twWriter.Write(HTMLReports.BindHTMLdata(tables.Tables[0], GetColumns(report.Columns), Listoftotals(report.Totals))); } } twWriter.Write("</div>"); if (report.GraphBindings != string.Empty) { int repid = 0; foreach (string graph in report.GraphBindings.Split('-')) { GraphicalReport.GenerateGraph("Z:\\inetpub\\wwwroot\\proimages\\" + report.Selectedname + "-" + report.ReportName + DateTime.Now.ToString("ddMMyy") + repid.ToString() + "-" + report.ID + ".png", tables.Tables[0], graph, report.ReportName, report.Selectedname + "-" + report.ReportName + DateTime.Now.ToString("ddMMyy") + repid.ToString() + "-" + report.ID + ".png", report.GraphType); twWriter.WriteLine("<div align='center'><img runat='server' width ='900px' src='http://www.dev.sentelcallmanagerpro.com//images/" + report.Selectedname + "-" + report.ReportName + DateTime.Now.ToString("ddMMyy") + repid.ToString() + "-" + report.ID + ".png" + "'/></div>"); repid++; } } } else { switch (report.ReportingSection) { #region "KPI" case "Kpi": twWriter.Write("</br>"); twWriter.Write("</br>"); twWriter.Write("<CENTER><h1>" + report.ReportName + "</h1></CENTER>"); twWriter.Write("</br>"); twWriter.Write("</br>"); twWriter.Write("<b>Schedule Name: " + " " + report.Selectedname + "</b>"); twWriter.Write("</br>"); twWriter.Write("</br>"); twWriter.Write("<b>Date range: " + " " + report.ListofFilters.Split('=', ',')[5].TrimStart('\'').TrimEnd('\'') + " TO " + report.ListofFilters.Split('=', ',')[7].TrimStart('\'').TrimEnd('\'') + "</b>"); //"+ Fromdate.ToString()+" - " Todate.ToString()+"</b>"); twWriter.Write("</br>"); twWriter.Write("</br>"); // Header twWriter.Write(Header.Createheader(tables, report.ReportName, "Kpi")); //Sub headers...( this step is stopped for this release .....) // twWriter.Write(Header.Secondheader(tables, report.ReportName, "Kpi")); //Table body twWriter.Write(HTMLReports.BindHTMLdata(tables.Tables[0], GetColumns(report.Columns), Listoftotals(report.Totals))); // Performance Analysis twWriter.Write(KPI.GenerateKPIReport(tables, report.ReportName, report.UserId)); // Graph List <GraphHeaders> Grphe = Datamethods.GetGraphHeadernames(report.GraphHeaders); if (Grphe.Count == 0) { int repid = 0; foreach (string graph in report.GraphBindings.Split('-')) { GraphicalReport.GenerateGraph("Z:\\inetpub\\wwwroot\\proimages\\" + report.Selectedname + "-" + report.ReportName + DateTime.Now.ToString("ddMMyy") + repid.ToString() + "-" + report.ID + ".png", tables.Tables[0], graph, report.ReportName, report.Selectedname + "-" + report.ReportName + DateTime.Now.ToString("ddMMyy") + repid.ToString() + "-" + report.ID + ".png", report.GraphType); twWriter.WriteLine("<div align='center'><img runat='server' width ='900px' src='http://www.dev.sentelcallmanagerpro.com//images/" + report.Selectedname + "-" + report.ReportName + DateTime.Now.ToString("ddMMyy") + repid.ToString() + "-" + report.ID + ".png" + "'/></div>"); repid++; } } else { int repid = 0; foreach (string graph in report.GraphBindings.Split('-')) { foreach (GraphHeaders gr in Grphe) { if (graph.Contains(gr.ColumnName)) { for (int i = 0; i < tables.Tables.Count; i++) { if (tables.Tables[i].Columns.Contains(gr.ColumnName)) { twWriter.WriteLine("<b>" + gr.Headername + "<b>"); GraphicalReport.GenerateGraph("Z:\\inetpub\\wwwroot\\proimages\\" + report.Selectedname + "-" + report.ReportName + DateTime.Now.ToString("ddMMyy") + repid.ToString() + "-" + report.ID + ".png", tables.Tables[i], graph, report.ReportName, report.Selectedname + "-" + report.ReportName + DateTime.Now.ToString("ddMMyy") + repid.ToString() + "-" + report.ID + ".png", report.GraphType); twWriter.WriteLine("<div align='center'><img runat='server' width ='900px' src='http://www.dev.sentelcallmanagerpro.com//images/" + report.Selectedname + "-" + report.ReportName + DateTime.Now.ToString("ddMMyy") + repid.ToString() + "-" + report.ID + ".png" + "'/></div>"); } } } repid++; } } } break; #endregion #region "Dashboard" case "Dashboar": twWriter.Write("</br>"); twWriter.Write("</br>"); twWriter.Write("<CENTER><h1>" + report.ReportName + "</h1></CENTER>"); twWriter.Write("</br>"); twWriter.Write("</br>"); twWriter.Write("<b>Schedule Name: " + " " + report.Selectedname + "</b>"); twWriter.Write("</br>"); twWriter.Write("</br>"); twWriter.Write("<b>Date range: " + " " + report.ListofFilters.Split('=', ',')[5].TrimStart('\'').TrimEnd('\'') + " TO " + report.ListofFilters.Split('=', ',')[7].TrimStart('\'').TrimEnd('\'') + "</b>"); //"+ Fromdate.ToString()+" - " Todate.ToString()+"</b>"); twWriter.Write("</br>"); twWriter.Write("</br>"); // Tables operation....... tables = Tableoperations.Combinetables(tables, report.ReportName, "Dashboar"); // Header twWriter.Write(Header.Createheader(tables, report.ReportName, "Dashboar")); // if (report.Columns.Length > 5) { twWriter.Write(HTMLReports.BindHTMLdata(tables.Tables[0], GetColumns(report.Columns), Listoftotals(report.Totals))); } List <GraphHeaders> Grphead = Datamethods.GetGraphHeadernames(report.GraphHeaders); // Graph if (report.GraphBindings != string.Empty) { int repid = 0; foreach (string graph in report.GraphBindings.Split('-')) { foreach (GraphHeaders gr in Grphead) { if (graph.Contains(gr.ColumnName)) { for (int i = 0; i < tables.Tables.Count; i++) { if (tables.Tables[i].Columns.Contains(gr.ColumnName)) { string format = report.GraphType; if (graph.Split(',').Count() > 2) { format = "Column"; } twWriter.WriteLine("<b>" + gr.Headername + "<b>"); GraphicalReport.GenerateGraph("Z:\\inetpub\\wwwroot\\proimages\\" + report.Selectedname + "-" + report.ReportName + DateTime.Now.ToString("ddMMyy") + repid.ToString() + "-" + report.ID + ".png", tables.Tables[i], graph, report.ReportName, report.Selectedname + "-" + report.ReportName + DateTime.Now.ToString("ddMMyy") + repid.ToString() + "-" + report.ID + ".png", format); twWriter.WriteLine("<div align='center'><img runat='server' width ='900px' src='http://www.dev.sentelcallmanagerpro.com//images/" + report.Selectedname + "-" + report.ReportName + DateTime.Now.ToString("ddMMyy") + repid.ToString() + "-" + report.ID + ".png" + "'/></div>"); } } } repid++; } } } break; #endregion #region "Invoice" case "Invoice": // Generate Header twWriter.Write(Invoice.GenerateUCDInvoiceHeader(tables, report)); // Generate Tables twWriter.Write("<div id='Linearcalllist' runat='server' style='border: 1px solid black;overflow: auto;'>"); twWriter.Write(Invoice.GenerateUCDInvoiceReport(tables, report, Listoftotals(report.Totals))); twWriter.Write("</div>"); break; #endregion } } SendEmail.InsertPortfoliodetails("c:\\temp\\" + report.Selectedname + "-" + report.ReportName + DateTime.Now.ToString("ddMMyy") + "-" + report.ID + ".html", report.EmailAddresses, report.Portfolioreportid, report.ID); } catch { ReportStatus(report.ID, "ERROR"); } finally { twWriter.Close(); } } else { // will send service team no data availabe message ReportStatus(report.ID, "No Data"); } }