protected void Page_Load(object sender, EventArgs e)
{
QueryBuilderManager qb = new QueryBuilderManager();
Trace.Warn("pageload", "pageload");
if (Session["report"] != null)
{
ArrayList re = new ArrayList();
re = (ArrayList)Session["report"];
foreach (object re1 in re)
{
CompanyReport cr = new CompanyReport();
cr = (CompanyReport)re1;
string qbdesc = String.Empty;
DataTable d = qb.QBManager(cr.idfield, cr.Params);
qbdesc = DatabaseConnection.SqlScalar("SELECT DESCRIPTION FROM QB_CUSTOMERQUERY WHERE ID=" + cr.idfield);
if (d.Rows.Count > 0)
{
switch (cr.Type)
{
case 0:
htmlcontact(d, cr.Finalize, cr.itemPage, qbdesc, cr.morerecord);
break;
case 1:
HtmlList(d, cr.Finalize, qbdesc, cr.morerecord);
break;
}
}
else
{
report = "No data";
}
Session["report"] = null;
}
Response.Write("<body onload=\"printpage();\">");
Response.Write(report);
Response.Write("</body>");
}
}
private void FillResult()
{
bool ViewOn = (RadioResult.SelectedValue == "HTML");
string delimiter = String.Empty;
if (!ViewOn)
{
HttpContext.Current.Items.Add("render", "no");
}
QueryBuilderManager qb = new QueryBuilderManager();
Hashtable Params = new Hashtable();
string sqlHash = "SELECT QB_CUSTOMERQUERYPARAMFIELDS.IDFIELD,QB_ALL_FIELDS.FIELD,QB_ALL_FIELDS.FIELDTYPE,QB_CUSTOMERQUERYPARAMFIELDS.IDTABLE " +
"FROM QB_CUSTOMERQUERYPARAMFIELDS " +
"INNER JOIN QB_ALL_FIELDS ON QB_CUSTOMERQUERYPARAMFIELDS.IDFIELD = QB_ALL_FIELDS.ID " +
"WHERE QB_CUSTOMERQUERYPARAMFIELDS.IDQUERY=" + int.Parse(Session["QueryID"].ToString());
DataSet dsQB = DatabaseConnection.CreateDataset(sqlHash);
foreach (DataRow s in dsQB.Tables[0].Rows)
{
if ((int)s[3] > 0)
{
switch (s[2].ToString())
{
case "8":
Params.Add(s[1].ToString().ToLower(), Request.Form["Param_" + s[0].ToString()] + "|" + Request.Form["Param2_" + s[0].ToString()]);
break;
case "12":
Params.Add(s[1].ToString().ToLower(), Request.Form["Param_" + s[0].ToString()] + "|" + Request.Form["Paramt_" + s[0].ToString()]);
break;
case "1":
case "13":
case "2":
if (Request.Form["Param_" + s[0].ToString()].Length > 0)
{
if (Request.Form["Param_" + s[0].ToString()].IndexOf("-1") < 0)
{
Params.Add(s[1].ToString().ToLower(), Request.Form["Param_" + s[0].ToString()]);
}
}
else
{
if (Request.Form["SParam_" + s[0].ToString()].IndexOf("-1") < 0)
{
Params.Add(s[1].ToString().ToLower(), Request.Form["SParam_" + s[0].ToString()]);
}
}
break;
case "4":
Params.Add(s[1].ToString().ToLower(), (Request.Form["Param_" + s[0].ToString()] != null) ? Request.Form["SParam_-" + s[0].ToString()] : "false");
break;
case "14":
Params.Add(s[1].ToString().ToLower(), Request.Form["Paramt_" + s[0].ToString()]);
break;
default:
Params.Add(s[1].ToString().ToLower(), Request.Form["Param_" + s[0].ToString()]);
break;
}
Trace.Warn("params-" + s[1].ToString().ToLower(), Request.Form["Param_" + s[0].ToString()]);
}
else
{
DataRow freeParameter = DatabaseConnection.CreateDataset("SELECT * FROM ADDEDFIELDS WHERE ID=" + s[0].ToString()).Tables[0].Rows[0];
switch (freeParameter["Type"].ToString())
{
case "4":
Params.Add("-" + freeParameter["Name"].ToString(), (Request.Form["SParam_-" + s[0].ToString()] != null) ? Request.Form["SParam_-" + s[0].ToString()] : "false");
break;
case "1":
case "2":
Params.Add("-" + freeParameter["Name"].ToString(), Request.Form["Param_-" + s[0].ToString()]);
break;
}
Trace.Warn("params-" + "-" + freeParameter["Name"].ToString(), Request.Form["Param_" + s[0].ToString()]);
}
}
DataTable d = qb.QBManager(Convert.ToInt32(Session["QueryID"]), Params);
if (d.Rows.Count > 0)
{
StringBuilder sb = new StringBuilder();
if (!ViewOn)
{
switch (RadioResult.SelectedValue)
{
case "CSV1":
delimiter = ",";
break;
case "CSV2":
delimiter = ";";
break;
case "PDF":
case "RTF":
delimiter = ((char)2) + "";
break;
default:
delimiter = ",";
break;
}
}
if (ViewOn)
{
sb.Append("<table class=\"normal\" id=\"unique_id\" width=\"98%\" border=1 cellspacing=0 bgcolor=#ffffff><tr>");
}
int errocount = 0;
try
{
foreach (DataColumn cc in d.Columns)
{
if (ViewOn)
{
sb.AppendFormat("<th>{0}</th>", (cc.ColumnName.StartsWith("{+}") || cc.ColumnName.StartsWith("{t}")) ? cc.ColumnName.Substring(3, cc.ColumnName.Length - 3) : cc.ColumnName);
}
else
{
sb.AppendFormat("\"{0}\"{1}", (cc.ColumnName.StartsWith("{+}") || cc.ColumnName.StartsWith("{t}")) ? cc.ColumnName.Substring(3, cc.ColumnName.Length - 3) : cc.ColumnName, delimiter);
}
errocount++;
}
}catch (Exception ex)
{
string error = "DEBUG CHECK\r\n" + Session["QueryID"].ToString() + "\r\n";
foreach (DictionaryEntry de in Params)
{
error += de.Key + " - " + de.Value + "\r\n";
}
foreach (DataColumn cc in d.Columns)
{
error += ">" + cc.ColumnName + "\r\n";
}
error += errocount;
throw new Exception(ex.Message + "\r\n" + error);
}
if (ViewOn)
{
sb.Append("</tr>");
}
else
{
if (RadioResult.SelectedValue == "PDF" || RadioResult.SelectedValue == "RTF")
{
if (sb.Length > 0)
{
sb.Remove(sb.Length - 1, 1);
}
sb.Append((char)1);
}
else
{
if (sb.Length > 0)
{
sb.Remove(sb.Length - 1, 1);
}
sb.Append("\r\n");
}
}
double[] sum = new double[d.Columns.Count];
bool[] sumType = new bool[d.Columns.Count];
bool boolSum = false;
foreach (DataRow dr in d.Rows)
{
int indexSum = 0;
if (ViewOn)
{
sb.Append("<tr>");
}
foreach (DataColumn cc in d.Columns)
{
if (cc.ColumnName.StartsWith("{+}") || cc.ColumnName.StartsWith("{t}"))
{
double sumtoadd = 0;
try
{
sumtoadd = Convert.ToDouble(dr[cc.ColumnName]);
}
catch
{
sumtoadd = 0;
}
sum[indexSum] += sumtoadd;
sumType[indexSum] = (cc.ColumnName.StartsWith("{t}")); // true = tempo , false = numerico
boolSum = true;
}
string timeToPrint = String.Empty;
if (cc.ColumnName.StartsWith("{t}"))
{
int duration = 0;
try
{
duration = Convert.ToInt32(dr[cc.ColumnName]);
}
catch
{
duration = 0;
}
if (duration > 0)
{
if (duration < 60)
{
timeToPrint = "00:" + ((duration > 9) ? duration.ToString() : "0" + duration.ToString());
}
else
{
timeToPrint = ((Convert.ToInt32(duration / 60) > 9) ? Convert.ToInt32(duration / 60).ToString() : "0" + Convert.ToInt32(duration / 60).ToString()) + ":" +
((Convert.ToInt32(duration % 60) > 9) ? Convert.ToInt32(duration % 60).ToString() : "0" + Convert.ToInt32(duration % 60).ToString());
}
}
}
if (ViewOn)
{
if (cc.ColumnName.StartsWith("{t}"))
{
sb.AppendFormat("<td valign=top>{0} </td>", timeToPrint);
}
else
if (dr[cc.ColumnName].ToString().StartsWith("Mottxt"))
{
sb.AppendFormat("<td valign=top>{0} </td>", Root.rm.GetString(dr[cc.ColumnName].ToString()));
}
else
{
sb.AppendFormat("<td valign=top>{0} </td>", dr[cc.ColumnName]);
}
}
else
{
if (dr[cc.ColumnName].ToString().Length > 0)
{
if (cc.ColumnName.StartsWith("{t}"))
{
sb.AppendFormat("\"{0}\"{1}", timeToPrint, delimiter);
}
else
{
sb.AppendFormat("\"{0}\"{1}", dr[cc.ColumnName], delimiter);
}
}
else
{
sb.AppendFormat("{0}", delimiter);
}
}
indexSum++;
}
if (ViewOn)
{
sb.Append("</tr>");
}
else
{
if (RadioResult.SelectedValue == "PDF" || RadioResult.SelectedValue == "RTF")
{
sb.Remove(sb.Length - 1, 1);
sb.Append((char)1);
}
else
{
sb.Remove(sb.Length - 1, 1);
sb.Append("\r\n");
}
}
}
if (boolSum)
{
if (ViewOn)
{
sb.Append("<tr>");
for (int i = 0; i < sum.Length; i++)
{
sb.Append("<td>");
if (sum[i] > 0)
{
if (sumType[i])
{
int duration = Convert.ToInt32(sum[i]);
string timeToPrint = String.Empty;
if (duration > 0)
{
if (duration < 60)
{
timeToPrint = "00:" + ((duration > 9) ? duration.ToString() : "0" + duration.ToString());
}
else
{
timeToPrint = ((Convert.ToInt32(duration / 60) > 9) ? Convert.ToInt32(duration / 60).ToString() : "0" + Convert.ToInt32(duration / 60).ToString()) + ":" +
((Convert.ToInt32(duration % 60) > 9) ? Convert.ToInt32(duration % 60).ToString() : "0" + Convert.ToInt32(duration % 60).ToString());
}
}
sb.Append("<b>" + timeToPrint + "</b>");
}
else
{
sb.Append("<b>" + sum[i].ToString() + "</b>");
}
}
else
{
sb.Append(" ");
}
sb.Append("</td>");
}
sb.Append("</tr>");
}
else
{
for (int i = 0; i < sum.Length; i++)
{
if (sum[i] > 0)
{
if (sumType[i])
{
int duration = Convert.ToInt32(sum[i]);
string timeToPrint = String.Empty;
if (duration > 0)
{
if (duration < 60)
{
timeToPrint = "00:" + ((duration > 9) ? duration.ToString() : "0" + duration.ToString());
}
else
{
timeToPrint = ((Convert.ToInt32(duration / 60) > 9) ? Convert.ToInt32(duration / 60).ToString() : "0" + Convert.ToInt32(duration / 60).ToString()) + ":" +
((Convert.ToInt32(duration % 60) > 9) ? Convert.ToInt32(duration % 60).ToString() : "0" + Convert.ToInt32(duration % 60).ToString());
}
}
sb.AppendFormat("\"{0}\"{1}", timeToPrint, delimiter);
}
else
{
sb.AppendFormat("\"{0}\"{1}", sum[i].ToString(), delimiter);
}
}
else
{
sb.AppendFormat("{0}", delimiter);
}
}
sb.Remove(sb.Length - 1, 1);
sb.Append("\r\n");
}
}
if (ViewOn)
{
sb.Append("</table>");
if (RadioResult.SelectedValue == "HTML")
{
sb.Append("</table><script src=\"/js/reporthtml.js\"></script>");
QBResult.Text = sb.ToString();
}
}
else if (RadioResult.SelectedValue == "Excel")
{
ExportUtils eu = new ExportUtils();
DataSet dt = new DataSet();
dt.Tables.Add(d);
eu.ExportDataSet(dt, "report", ExportUtils.DataSetExportType.ExcelXML, HttpContext.Current);
return;
}
else if (RadioResult.SelectedValue == "XML")
{
ExportUtils eu = new ExportUtils();
DataSet dt = new DataSet();
dt.Tables.Add(d);
eu.ExportDataSet(dt, "report", ExportUtils.DataSetExportType.XML, HttpContext.Current);
return;
}
else if (RadioResult.SelectedValue == "ADO")
{
ExportUtils eu = new ExportUtils();
DataSet dt = new DataSet();
dt.Tables.Add(d);
eu.ExportDataSet(dt, "report", ExportUtils.DataSetExportType.ADORecordSet, HttpContext.Current);
return;
}
else
{
if (RadioResult.SelectedValue != "PDF" && RadioResult.SelectedValue != "RTF")
{
Response.AddHeader("Content-Disposition", "attachment; filename=Result.csv");
Response.AddHeader("Expires", "Thu, 01 Dec 1994 16:00:00 GMT ");
Response.AddHeader("Pragma", "nocache");
Response.ContentType = "application/vnd.ms-excel";
Response.Write(sb.ToString());
return;
}
else if (RadioResult.SelectedValue == "PDF")
{
PDFReport p = new PDFReport();
MemoryStream MyStream = p.PDFRender(d);
Response.AddHeader("Content-Disposition", "attachment; filename=report.pdf");
Response.CacheControl = "Private";
Response.AddHeader("Pragma", "No-Cache");
Response.ContentType = "application/pdf";
Response.BinaryWrite(MyStream.GetBuffer());
Response.End();
return;
}
else
{
PDFReport p = new PDFReport();
MemoryStream MyStream = p.RTFRender(d);
Response.AddHeader("Content-Disposition", "attachment; filename=report.rtf");
Response.CacheControl = "Private";
Response.AddHeader("Pragma", "No-Cache");
Response.ContentType = "application/rtf";
Response.BinaryWrite(MyStream.GetBuffer());
Response.End();
return;
}
}
}
else
{
QBResult.ForeColor = Color.Red;
QBResult.CssClass = "normal";
QBResult.Text = Root.rm.GetString("QBUtxt26");
}
}