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