public void subCreateArray(string strTableName)
{
string strSQL = "SELECT * FROM tblBgColumns WHERE Column_vcTableName = '" + strTableName + "' ORDER BY Column_intSequence ASC";
arlColumns = new ArrayList();
int intCount = 0;
if (objDB.blnOpenResultSet(strSQL) == true)
{
while (objDB.blnResultsMoveNextRow() == true)
{
udsColumns objC = new udsColumns();
objC.strColumnName = objDB.objResultsValue("Column_vcColumnName").ToString();
objC.strColumnClass = objDB.objResultsValue("Column_vcDataClass").ToString();
objC.strHeaderText = objDB.objResultsValue("Column_vcHeaderText").ToString();
objC.strHeaderClass = objDB.objResultsValue("Column_vcHeaderClass").ToString();
objC.strDataType = objDB.objResultsValue("Column_vcDataType").ToString();
objC.intColumnSequence = Convert.ToInt16(objDB.objResultsValue("Column_intSequence"));
objC.strFormat = objDB.objResultsValue("Column_vcFormatString").ToString();
objC.strAlign = objDB.objResultsValue("Column_vcAlign").ToString();
objC.strTotal = objDB.objResultsValue("Column_vcTotal").ToString();
if (objC.strTotal != "N")
{
blnDisplayTotals = true;
}
arlColumns.Add(objC);
intCount++;
}
}
arrTotals = new double[intCount];
}
/// <summary>
/// function to execute query and return json from resultset
/// </summary>
/// <param name="strQuery"></param>
/// <returns></returns>
public string getJsonData(string strQuery)
{
StringBuilder jsonData = new StringBuilder();
if (objDB.blnOpenResultSet(strQuery) == true)
{
jsonData.Append("<script type='text/javascript'>");
jsonData.Append(" var data = '[");
while (objDB.blnResultsMoveNextRow() == true)
{
jsonData.Append("{");
for (int intCount = 0; intCount < arlColumns.Count; intCount++)
{
udsColumns objC = (udsColumns)arlColumns[intCount];
jsonData.Append("\"" + objC.strColumnName + "\":");
jsonData.Append("\"" + objDB.objResultsValue(objC.strColumnName).ToString() + "\"");
jsonData.Append(",");
}
jsonData = jsonData.Remove(jsonData.ToString().LastIndexOf(","), 1);
jsonData.Append("},");
}
jsonData = jsonData.Remove(jsonData.ToString().LastIndexOf(","), 1);
jsonData.Append("]'");
jsonData.Append("</script>");
}
objDB.blnCloseConnection();
return(jsonData.ToString());
}
public HtmlTable htbDisplayVerticalData(string strQuery)
{
HtmlTable htbReturn = new HtmlTable();
HtmlTableRow htrRow = new HtmlTableRow();
string strValue = "";
if (objDB.blnOpenResultSet(strQuery) == true)
{
while (objDB.blnResultsMoveNextRow() == true)
{
for (int intCount = 0; intCount < arlColumns.Count; intCount++)
{
htrRow = new HtmlTableRow();
udsColumns objC = (udsColumns)arlColumns[intCount];
HtmlTableCell htcCell = new HtmlTableCell();
htcCell.InnerText = objC.strHeaderText;
htcCell.ID = "tc_" + objC.strHeaderText;
htcCell.Attributes.Add("class", objC.strHeaderClass);
htcCell.Attributes.Add("style", "text-align:left");
htrRow.Controls.Add(htcCell);
strValue = objDB.objResultsValue(objC.strColumnName).ToString();
string strBackgroundColor;
double dblToAdd = 0;
if (objDB.objResultsValue("BackgroundColor") == null)
{
strBackgroundColor = "#FFFFFF";
}
else
{
strBackgroundColor = objDB.objResultsValue("BackgroundColor").ToString();
}
switch (objC.strTotal)
{
case "S":
dblToAdd = double.Parse(objDB.objResultsValue(objC.strColumnName).ToString());
break;
case "C":
dblToAdd = 1;
break;
}
arrTotals[intCount] = arrTotals[intCount] + dblToAdd;
if (strValue == "")
{
strValue = " ";
}
htrRow.Controls.Add(htcDisplayRecords(strValue, objC.strDataType, objC.strFormat, objC.strColumnClass, objC.strAlign));
htrRow.BgColor = strBackgroundColor;
htrRow.Attributes.Add("onmouseover", "this.style.backgroundColor='#CCCCCC';");
htrRow.Attributes.Add("onmouseout", "this.style.backgroundColor='" + strBackgroundColor + "';");
htbReturn.Controls.Add(htrRow);
}
}
}
objDB.blnCloseConnection();
return(htbReturn);
}
public HtmlTable htbDisplayData(string strQuery, string strWidth)
{
HtmlTable htbReturn = new HtmlTable();
HtmlTableRow htrRow = new HtmlTableRow();
bool blnDisplayHeader = false;
string strValue = "";
int intRowCount = 0;
if (strWidth != "")
{
htbReturn.Style.Add("width", strWidth);
}
for (int intCount = 0; intCount < arlColumns.Count; intCount++)
{
udsColumns objC = (udsColumns)arlColumns[intCount];
if (objC.strHeaderText.Length > 0)
{
blnDisplayHeader = true;
break;
}
}
if (blnDisplayHeader == true)
{
for (int intCount = 0; intCount < arlColumns.Count; intCount++)
{
udsColumns objC = (udsColumns)arlColumns[intCount];
HtmlTableCell htcCell = new HtmlTableCell();
htcCell.InnerText = objC.strHeaderText;
htcCell.ID = "tc_" + objC.strHeaderText;
htcCell.Attributes.Add("class", objC.strHeaderClass);
htcCell.Attributes.Add("style", "text-align:center");
htrRow.Controls.Add(htcCell);
}
htbReturn.Controls.Add(htrRow);
}
if (objDB.blnOpenResultSet(strQuery) == true)
{
if (objDB.blnHasRecords == false)
{
htrRow = new HtmlTableRow();
HtmlTableCell tdCell = new HtmlTableCell();
tdCell.InnerText = "----------------NO RECORD FOUND----------------------";
tdCell.ColSpan = arlColumns.Count;
tdCell.Attributes.Add("style", "text-align:center;color:#FF0000");
htrRow.Controls.Add(tdCell);
htbReturn.Controls.Add(htrRow);
return(htbReturn);
}
while (objDB.blnResultsMoveNextRow() == true)
{
intRowCount++;
htrRow = new HtmlTableRow();
for (int intCount = 0; intCount < arlColumns.Count; intCount++)
{
udsColumns objC = (udsColumns)arlColumns[intCount];
strValue = objDB.objResultsValue(objC.strColumnName).ToString();
string strBackgroundColor;
double dblToAdd = 0;
if (objDB.strResultsColName(0) != "BackgroundColor")
{
strBackgroundColor = "#FFFFFF";
}
else
{
strBackgroundColor = objDB.objResultsValue("BackgroundColor").ToString();
}
switch (objC.strTotal)
{
case "S":
dblToAdd = double.Parse(objDB.objResultsValue(objC.strColumnName).ToString());
break;
case "C":
dblToAdd = 1;
break;
case "A":
dblToAdd = double.Parse(objDB.objResultsValue(objC.strColumnName).ToString());
break;
}
arrTotals[intCount] = arrTotals[intCount] + dblToAdd;
if (strValue == "")
{
strValue = " ";
}
htrRow.Controls.Add(htcDisplayRecords(strValue, objC.strDataType, objC.strFormat, objC.strColumnClass, objC.strAlign));
htrRow.BgColor = strBackgroundColor;
htrRow.Attributes.Add("onmouseover", "this.style.backgroundColor='#CCCCCC';");
htrRow.Attributes.Add("onmouseout", "this.style.backgroundColor='" + strBackgroundColor + "';");
}
htbReturn.Controls.Add(htrRow);
}
}
objDB.blnCloseConnection();
if (blnDisplayTotals == true)
{
htrRow = new HtmlTableRow();
for (int intCount = 0; intCount < arlColumns.Count; intCount++)
{
udsColumns objC = (udsColumns)arlColumns[intCount];
HtmlTableCell htcCell = new HtmlTableCell();
switch (objC.strTotal)
{
case "S":
strValue = arrTotals[intCount].ToString(objC.strFormat);
break;
case "C":
strValue = arrTotals[intCount].ToString(objC.strFormat);
break;
case "A":
strValue = ((arrTotals[intCount]) / (intRowCount)).ToString(objC.strFormat);
break;
default:
strValue = " ";
break;
}
if (intCount == 0)
{
strValue = "Total : ";
}
htcCell.InnerHtml = strValue;
htcCell.ID = "tc_" + objC.strColumnName;
htcCell.Attributes.Add("class", objC.strHeaderClass);
htcCell.Attributes.Add("style", "text-align:center");
htrRow.Controls.Add(htcCell);
}
htbReturn.Controls.Add(htrRow);
}
return(htbReturn);
}
public List <HtmlTable> htbDisplayData(DataSet objDataSet, string strWidth)
{
List <HtmlTable> htbReturn = new List <HtmlTable>();
HtmlTableRow htrRow = new HtmlTableRow();
List <HtmlTableRow> objRow = new List <HtmlTableRow>();
bool blnDisplayHeader = false;
string strValue = "";
int intRowCount = 0;
for (int intTable = 0; intTable < objDataSet.Tables.Count; intTable++)
{
double[] arrColumn = new double[arrTotals.Length];
htbReturn.Add(new HtmlTable());
objRow = new List <HtmlTableRow>();
htbReturn[intTable].CellSpacing = 0;
htbReturn[intTable].Border = 1;
//int intRowCount = 0;
//int intCount = 0;
for (int intCount = 0; intCount < arlColumns.Count; intCount++)
{
udsColumns objC = (udsColumns)arlColumns[intCount];
if (objC.strHeaderText.Length > 0)
{
blnDisplayHeader = true;
break;
}
}
if (blnDisplayHeader == true)
{
if (strWidth != "")
{
htbReturn[intTable].Style.Add("width", strWidth);
}
htrRow = new HtmlTableRow();
for (int intCount = 0; intCount < arlColumns.Count; intCount++)
{
udsColumns objC = (udsColumns)arlColumns[intCount];
HtmlTableCell htcCell = new HtmlTableCell();
htcCell.InnerText = objC.strHeaderText;
htcCell.ID = "tc_" + objC.strHeaderText;
htcCell.Attributes.Add("class", objC.strHeaderClass);
htcCell.Attributes.Add("style", "text-align:center");
htrRow.Controls.Add(htcCell);
}
htbReturn[intTable].Controls.Add(htrRow);
}
if (objDataSet.Tables[intTable].Rows.Count > 0)
{
DataTable objDataTable = objDataSet.Tables[intTable];
foreach (DataRow Row in objDataTable.Rows)
{
htrRow = new HtmlTableRow();
for (int intColumn = 0; intColumn < arlColumns.Count; intColumn++)
{
udsColumns objC = (udsColumns)arlColumns[intColumn];
string strColumnName = objC.strColumnName.ToString();
string strColumnValue = Row[strColumnName].ToString();
string strBackgroundColor;
double dblToAdd = 0;
if (objDataSet.Tables[0].Columns[0].ColumnName.ToString() == "BackgroundColor")
{
strBackgroundColor = Row["BackgroundColor"].ToString();
}
else
{
strBackgroundColor = "#FFFFFF";
}
switch (objC.strTotal)
{
case "S":
dblToAdd = double.Parse(Row[strColumnName].ToString());
break;
case "C":
dblToAdd = 1;
break;
case "A":
dblToAdd = double.Parse(Row[strColumnName].ToString());
break;
}
arrColumn[intColumn] = arrColumn[intColumn] + dblToAdd;
if (strValue == "")
{
strValue = " ";
}
htrRow.Controls.Add(htcDisplayRecords(strColumnValue, objC.strDataType, objC.strFormat, objC.strColumnClass, objC.strAlign));
htrRow.BgColor = strBackgroundColor;
htrRow.Attributes.Add("onmouseover", "this.style.backgroundColor='#CCCCCC';");
htrRow.Attributes.Add("onmouseout", "this.style.backgroundColor='" + strBackgroundColor + "';");
}
htbReturn[intTable].Controls.Add(htrRow);
}
if (blnDisplayTotals == true)
{
htrRow = new HtmlTableRow();
for (int intCount = 0; intCount < arlColumns.Count; intCount++)
{
udsColumns objC = (udsColumns)arlColumns[intCount];
HtmlTableCell htcCell = new HtmlTableCell();
switch (objC.strTotal)
{
case "S":
strValue = arrColumn[intCount].ToString(objC.strFormat);
break;
case "C":
strValue = arrColumn[intCount].ToString(objC.strFormat);
break;
case "A":
strValue = ((arrColumn[intCount]) / (intRowCount)).ToString(objC.strFormat);
break;
default:
strValue = " ";
break;
}
if (intCount == 0)
{
strValue = "Total : ";
}
htcCell.InnerHtml = strValue;
htcCell.ID = "tc_" + objC.strColumnName;
htcCell.Attributes.Add("class", objC.strHeaderClass);
htcCell.Attributes.Add("style", "text-align:center");
htrRow.Controls.Add(htcCell);
}
htbReturn[intTable].Controls.Add(htrRow);
}
}
else
{
htrRow = new HtmlTableRow();
HtmlTableCell tdCell = new HtmlTableCell();
tdCell.InnerText = "----------------NO RECORD FOUND----------------------";
tdCell.ColSpan = arlColumns.Count;
tdCell.Attributes.Add("style", "text-align:center;color:#FF0000");
htrRow.Controls.Add(tdCell);
htbReturn[intTable].Controls.Add(htrRow);
return(htbReturn);
}
//htbReturn[intTable].Controls.Add(new LiteralControl("<br/>"));
//HtmlTable htbBreak = new HtmlTable();
//htrRow = new HtmlTableRow();
//for (int intCount = 0; intCount < arlColumns.Count; intCount++)
//{
// udsColumns objC = (udsColumns)arlColumns[intCount];
// HtmlTableCell htcCell = new HtmlTableCell();
// strValue = " ";
// htcCell.InnerHtml = strValue;
// htcCell.ID = "tc_" + objC.strColumnName;
// //htcCell.Attributes.Add("class", objC.strHeaderClass);
// //htcCell.Attributes.Add("style", "text-align:center");
// htrRow.Controls.Add(htcCell);
//}
////htbBreak.Controls.Add(htrRow);
//htbReturn[intTable].Controls.Add(htrRow);
}
return(htbReturn);
}