|
|
using System;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Text;
using System.Collections;
public partial class _Default : System.Web.UI.Page
{
System.Data.SqlClient.SqlConnection Conn;
string DbConnection = "Data Source=127.0.0.1\\SQLEXPRESS;Initial Catalog=MMF;User Id=ops;Password=megan$$$999";
Hashtable Params = new Hashtable();
string OutputTitle="";
string OutputSubTitle="";
string OutputOddRow = "<tr style=\"background-color:grey\"><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>";
string OutputEvenRow = "<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>";
string OutputHeaderRow = "<tr><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>";
string OutputFooterRow = "<tr><td>Totals</td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>";
int Cols = 0;
string GetParam(string Name)
{
Name = Name.Substring(1); // take off @
if(Request.Cookies[Name] != null)
return(Request.Cookies[Name].Value);
if(Session[Name] != null)
return(Session[Name].ToString());
/*
if(Request.Form[Name] != null)
return(Request.Form[Name]);
if(Request.QueryString[Name] != null)
return (Request.QueryString[Name]);
*/
return(null);
}
string SubstParams(string s)
{
for (int iii = 1; iii < 10; iii++)
{
string ii = iii.ToString();
if (Params.ContainsKey(ii) == false)
break;
Hashtable h = (Hashtable)Params[ii];
string ParamName = h["name"].ToString();
string Val = Request.Form[ParamName];
s = s.Replace(ParamName, Val);
}
return (s);
}
string PadRow(string s)
{
s = s.Substring(0, s.Length - 5); // strip </tr>
return (s += "<td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>");
}
protected bool ParseSP(string SP)
{
System.Data.DataTable dt = new DataTable();
// get the source code for the SP
System.Data.SqlClient.SqlDataAdapter a = new System.Data.SqlClient.SqlDataAdapter("exec sp_helptext "+SP, Conn);
a.Fill(dt);
int i = 0;
foreach (DataRow dr in dt.Rows)
{
string Line = dr[0].ToString().Trim();
// we are looking for parameters and special comments before "AS"
if (Line.ToLower().StartsWith("create"))
continue;
if (Line.ToLower().StartsWith("as"))
return (true);
if (Line.Length == 0)
continue;
if (Line[0] == '@')
{
// we have a parameter
i++;
Hashtable h = new Hashtable();
int j = Line.IndexOf("-- enum:");
if (j >= 0)
{
// we have "-- enum: ", so what follows is the stored proc to call to generate the dropdown list
h["enum"] = Line.Substring(j + 8).Trim();
Line = Line.Substring(0, j);
}
j = Line.IndexOf("=");
if (j >= 0)
{
// we have a default value to display
string Default = Line.Substring(j + 1).Trim();
Line = Line.Substring(0, j);
if (Default[Default.Length - 1] == ',')
Default = Default.Substring(0, Default.Length - 1);
if (Default[0] == '\'')
Default = Default.Substring(1);
if (Default[Default.Length-1] == '\'')
Default = Default.Substring(0, Default.Length - 1);
Default = Default.Replace("''", "'");
h["default"] = Default;
}
// get rid of any extra spaces
Line = Line.Replace(" ", " ");
Line = Line.Replace(" ", " ");
Line = Line.Replace(" ", " ");
Line = Line.Replace(" ", " ");
string[] Parts = Line.Split(new char[] { ' ' });
h["name"] = Parts[0];
h["type"] = Parts[1];
Params[i.ToString()] = h;
continue;
}
string LLine = Line.ToLower();
if(LLine.StartsWith("-- oddrow:"))
{
OutputOddRow = PadRow(Line.Substring(10).Trim());
continue;
}
if(LLine.StartsWith("-- evenrow:"))
{
OutputEvenRow = PadRow(Line.Substring(11).Trim());
continue;
}
if (LLine.StartsWith("-- headerrow:"))
{
OutputHeaderRow = PadRow(Line.Substring(13).Trim());
continue;
}
if (LLine.StartsWith("-- footerrow:"))
{
OutputFooterRow = PadRow(Line.Substring(13).Trim());
continue;
}
if (LLine.StartsWith("-- title:"))
{
OutputTitle = Line.Substring(9).Trim();
continue;
}
if(LLine.StartsWith("-- subtitle:"))
{
OutputSubTitle = Line.Substring(12).Trim();
continue;
}
}
return (true);
}
string BuildDropDownText(string SP)
{
StringBuilder sb = new StringBuilder();
System.Data.DataTable dt = new DataTable();
System.Data.SqlClient.SqlDataAdapter a = new System.Data.SqlClient.SqlDataAdapter(SP, Conn);
a.Fill(dt);
foreach (DataRow dr in dt.Rows)
sb.Append("<option value=\""+System.Web.HttpUtility.HtmlEncode(dr[0].ToString())+"\">"+System.Web.HttpUtility.HtmlEncode(dr[1].ToString())+"</option>\r\n");
return(sb.ToString());
}
protected void Page_Load(object sender, EventArgs e)
{
Response.Expires = 0;
this.pnlParams.Visible = false;
this.pnlResults.Visible = false;
string SP = Request.QueryString["sp"];
if (SP == null)
{
Response.Write("Missing SP parameter");
Response.End();
}
Conn = new System.Data.SqlClient.SqlConnection(DbConnection);
Conn.Open();
if (this.IsPostBack == false)
{
this.pnlParams.Visible=true;
StringBuilder TableData = new StringBuilder();
// get the input parameters
if (ParseSP(SP) == false)
{
Response.Write("Could not parse stored proc");
Response.End();
}
this.lblParamTitle.Text = OutputTitle;
this.hidSP.Value = SP;
this.hidTitle.Value = OutputTitle;
this.hidSubTitle.Value = OutputSubTitle;
this.hidRowEven.Value = OutputEvenRow;
this.hidRowOdd.Value = OutputOddRow;
this.hidRowHeader.Value = OutputHeaderRow;
this.hidRowFooter.Value = OutputFooterRow;
for(int i=1; i<10; i++)
{
string ii = i.ToString();
if(Params.ContainsKey(ii) == false)
break;
Hashtable h = (Hashtable)Params[ii];
string ParamName = h["name"].ToString();
string ParamDisplayName = ParamName.Replace("_", " ").Substring(1);
string Val = GetParam(ParamName);
if (Val != null)
{
TableData.Append("<input type=\"hidden\" name=\"" + ParamName + "\" value=\"" + System.Web.HttpUtility.HtmlEncode(Val) + "\" />\r\n");
continue;
}
TableData.Append("<tr>\r\n<td>"+ParamDisplayName+"</td>\r\n<td>\r\n");
if(h.Contains("enum"))
{
TableData.Append("<select name="+ParamName+">\r\n");
// we're building a dropdown list
TableData.Append(BuildDropDownText(h["enum"].ToString()));
TableData.Append("</select>\r\n");
if(h.Contains("default"))
TableData.Append("<script>SetDefaultValue('"+ParamName+"', '"+h["default"]+"');</script>\r\n");
TableData.Append("</td></tr>\r\n");
continue;
}
string Default = "";
if(h.Contains("default"))
Default = h["default"].ToString();
string Type = h["type"].ToString();
if (Type == "datetime")
{
TableData.Append("<input type=\"text\" name=\"" + ParamName + "\" readonly=\"readonly\" value=\"" + Default + "\">\r\n");
TableData.Append("<img src=\"cal/calendaricon.gif\" height=\"17\" width=\"17\" border=\"0\"\r\n");
TableData.Append(" onClick=\"popUpCalendar(this, document.getElementById('" + ParamName + "'), 'yyyy/mm/dd', 0, 0)\">\r\n");
continue;
}
string OnKeyDown = "";
switch(Type)
{
case "int":
case "bigint":
OnKeyDown = " onkeydown=\"NumOnly(this, false);\" ";
break;
case "money":
case "real":
case "float":
OnKeyDown = " onkeydown=\"NumOnly(this, true);\" ";
break;
}
TableData.Append("<input type=\"text\" name=\"" + ParamName + "\" id=\"" + ParamName + "\" value=\"" + System.Web.HttpUtility.HtmlEncode(Default) + "\" " + OnKeyDown + "/>\r\n</td>\r\n</tr>\r\n");
}
this.litParams.Text = TableData.ToString();
Conn.Close();
}
}
string[] SplitString(string s)
{
ArrayList ar = new ArrayList();
int Start = 0;
while (true)
{
int i = s.IndexOf("</td>", Start+1);
if (i == -1)
{
ar.Add(s.Substring(Start));
break;
}
ar.Add(s.Substring(Start, i - Start));
Start = i;
}
string[] Parts = new string[ar.Count];
for (int ii = 0; ii < ar.Count; ii++)
Parts[ii] = ar[ii].ToString();
return (Parts);
}
string[] Dup(string[] s)
{
string [] x = new string[s.Length];
for (int i = 0; i < s.Length; i++)
x[i] = s[i];
return (x);
}
string JoinString(string[] s, int Cols)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i <= Cols; i++)
{
sb.Append(s[i]);
}
return (sb.ToString()+"</td></tr>");
}
string Gen(bool IsExcel)
{
Conn = new System.Data.SqlClient.SqlConnection(DbConnection);
Conn.Open();
System.Data.DataTable dt = new DataTable();
// build the string to execute
StringBuilder Exec = new StringBuilder("exec "+this.hidSP.Value+" ");
// this application allows for up to 10 parameters
for(int iii=1; iii<10; iii++)
{
string ii = iii.ToString();
if(Params.ContainsKey(ii) == false)
break;
Hashtable h = (Hashtable)Params[ii];
string ParamName = h["name"].ToString();
string parmval = Request.Form[ParamName];
// figure out if the parameter value needs to be quoted
switch(h["type"].ToString())
{
case "bigint":
case "int":
case "money":
break;
default:
parmval = "'"+parmval+"'";
break;
}
Exec.Append(ParamName+"="+parmval+",");
}
string ExecX = Exec.ToString();
ExecX = ExecX.Substring(0, ExecX.Length - 1);
// now we have a string like like:
// exec procname @parmname1=3, @parmname2='abc'
// execute the stored proc
System.Data.SqlClient.SqlDataAdapter a = new System.Data.SqlClient.SqlDataAdapter(ExecX, Conn);
a.Fill(dt);
int i = 0;
StringBuilder sb = new StringBuilder();
// create the templates for the rows
string[] OddRow = SplitString(this.hidRowOdd.Value);
string[] EvenRow = SplitString(this.hidRowEven.Value);
string[] HeaderRow = SplitString(this.hidRowHeader.Value);
string[] FooterRow = SplitString(this.hidRowFooter.Value);
// build the buckets for the (possible) footer formulas
long[] iAccum = new long[FooterRow.Length];
double[] dAccum = new double[FooterRow.Length];
System.Decimal[] mAccum = new System.Decimal[FooterRow.Length];
string[] AccumType = new string[FooterRow.Length];
bool HaveAccum = false;
// initialize them
for (int kkk = 0; kkk < iAccum.Length; kkk++)
{
iAccum[kkk] = 0;
mAccum[kkk] = 0.00m;
dAccum[kkk] = 0.00;
AccumType[kkk] = "";
}
// see if there are any formulas
for(int kkk=0; kkk<iAccum.Length; kkk++)
{
string rrr = FooterRow[kkk];
if(rrr.Contains("formula="))
{
if(rrr.Contains("formula=\"avg\""))
{
AccumType[kkk] = "avg";
HaveAccum = true;
continue;
}
if(rrr.Contains("formula=\"sum\""))
{
AccumType[kkk] = "sum";
HaveAccum = true;
continue;
}
if(rrr.Contains("formula=\"count\""))
{
AccumType[kkk] = "count";
HaveAccum = true;
continue;
}
}
}
int Rows = dt.Rows.Count;
if (Rows == 0)
{
return("<tr><td align=\"center\">No data</td></tr>\r\n");
}
// now start processing the rows
foreach (DataRow dr in dt.Rows)
{
string [] ThisRow;
i++;
if (i == 1)
{
// need to put out the colum names
ThisRow = Dup(HeaderRow);
int jj = 1; //0'th column is the row #
foreach (DataColumn dc in dr.Table.Columns)
{
Cols++;
// if the column content is ?, then use the column name from the DB, otherwise use the text from the header row
if(ThisRow[jj].EndsWith("?"))
ThisRow[jj] = ThisRow[jj].Substring(0, ThisRow[jj].Length-1)+System.Web.HttpUtility.HtmlEncode(dc.ColumnName);
jj++;
}
sb.Append(JoinString(ThisRow, Cols) + "\r\n");
}
ThisRow = i % 2 == 0 ? Dup(EvenRow) : Dup(OddRow);
// replace the text "row" in the first column with the line number
ThisRow[0] = ThisRow[0].Replace("row", i.ToString());
for (int j = 0; j < Cols; j++)
{
string item = dr[j].ToString();
// if the column value starts with "<span" or "<a " the value is already html encoded, if not then encode it
if(!(item.StartsWith("<span") || item.StartsWith("<a ")))
item = System.Web.HttpUtility.HtmlEncode(item);
ThisRow[j+1] += item;
// if there is footer formula then do the appropriate thing
string aType = AccumType[j + 1]; // the db column number is one behind the table column because of the row # in 1st table col
if (aType != "")
{
switch (aType)
{
case "count":
iAccum[j+1]++;
break;
case "avg":
case "sum":
try
{
double amt = double.Parse(item);
dAccum[j+1] += amt;
}
catch(System.Exception ex)
{
;
}
break;
}
}
}
sb.Append(JoinString(ThisRow, Cols) + "\r\n");
}
// process any formulas
if(HaveAccum)
{
for(int qqq=0;qqq<Cols;qqq++)
{
string val="";
if(AccumType[qqq] != "")
{
switch(AccumType[qqq])
{
case "count":
val = iAccum[qqq].ToString();
break;
case "avg":
val = (dAccum[qqq]/i).ToString();
break;
case "sum":
val = dAccum[qqq].ToString();
break;
}
}
FooterRow[qqq] += val;
}
string FooterData = JoinString(FooterRow, Cols) + "\r\n";
if (IsExcel)
FooterData = FooterData.Replace("formula=\"", "formulax=\""); // excel get confused by the "formula" attribute, so change it
sb.Append(FooterData);
}
Conn.Close();
return(sb.ToString());
}
protected void btnGenerate_Click(object sender, EventArgs e)
{
ParseSP(this.hidSP.Value);
this.pnlResults.Visible = true;
// inject the title
this.lblDataTitle.Text = this.hidTitle.Value;
// inject the subtitle after substituting and parameters, for example if the subtitle was
// @Start_Date - @End_Date
// the result would be like:
// 2008/01/01 - 2008/01/31
this.lblDataSubTitle.Text = SubstParams(this.hidSubTitle.Value);
this.litData.Text = Gen(false);
}
protected void btnExcel_Click(object sender, EventArgs e)
{
ParseSP(this.hidSP.Value);
this.pnlParams.Visible = true;
Response.Clear();
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment; filename=Report.xls");
// must generate the data first so we know how many columns there are
string GenData = Gen(true);
string sCols = (Cols+1).ToString(); // also include row number column
Response.Write("<table border=\"1\" style=\"border-collapse:collapse\">\r\n"+
"<tr><td colspan=\""+sCols+"\" align='center'><h4>"+System.Web.HttpUtility.HtmlEncode(this.hidTitle.Value)+"</h4></td></tr>\r\n"+
"<tr><td colspan=\"" + sCols + "\" align='center'><h5>" + System.Web.HttpUtility.HtmlEncode(SubstParams(this.hidSubTitle.Value)) + "</h5></td></tr>\r\n" +
GenData +
"\r\n</table>\r\n");
Response.End();
}
}
|
By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.
If a file you wish to view isn't highlighted, and is a text file (not binary), please
let us know and we'll add colourisation support for it.
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.
Submit an article or tip