private DataTable applyParameters()
{
DataTable myDT = new DataTable(); //Temporary table to bind ListView to.
string searchOption = coreSearchOption.SelectedValue;
//Build the dictionaries
buildDictionaries();
//Build the plsql command.
string oraParams = "";
//string oraQry = "SELECT * FROM ALARMS";
string oraQry = "SELECT /*+PARALLEL(4)*/ ";
foreach (KeyValuePair <string, string> queryEntry in queryDict)
{
oraQry += queryEntry.Value + " AS \"" + queryEntry.Key + "\",";
}
//remove the last comma from the string and then stap
oraQry = oraQry.TrimEnd(',') + " FROM HIS.ALARMS";
string oraOrd = " ORDER BY CHRONO DESC";
OracleCommand oraCmd = new OracleCommand();
//For use with time
long epochStart = 0;
long epochEnd = 0;
//PARAMETER: CHRONO.START
if (!txtParamDTStart.Text.Equals(""))
{
DateTime dtStart;
if (!DateTime.TryParse(txtParamDTStart.Text, out dtStart))
{
lblAlarms.Text = "Invalid Start Date!";
}
else
{
epochStart = ((long)(dtStart.ToUniversalTime() - new DateTime(1970, 1, 1)).TotalMilliseconds);
Console.WriteLine("EpochStart: " + epochStart);
oraParams += (oraParams.Equals("") ? "" : " AND ") + "CHRONO >= :dtStart";
oraCmd.Parameters.Add(new OracleParameter("dtStart", epochStart));
}
}
//PARAMETER: CHRONO.END
if (!txtParamDTEnd.Text.Equals(""))
{
DateTime dtEnd;
if (!DateTime.TryParse(txtParamDTEnd.Text, out dtEnd))
{
lblAlarms.Text = "Invalid End Date!";
}
else
{
//double epochEnd = (double)1000 * ((int)(dtEnd - new DateTime(1970, 1, 1)).TotalSeconds);
epochEnd = ((long)(dtEnd.ToUniversalTime() - new DateTime(1970, 1, 1)).TotalMilliseconds);
oraParams += (oraParams.Equals("") ? "" : " AND ") + "CHRONO <= :dtEnd";
oraCmd.Parameters.Add(new OracleParameter("dtEnd", epochEnd));
}
}
//perform a time check to see if the dtSTart and dtEnd exceeds 60 days or 5.184e+9 milliseconds
//If it exceeds it, then display error and inform user
if ((!txtParamDTEnd.Text.Equals("")) && (!txtParamDTStart.Text.Equals("")))
{
if (epochEnd - epochStart > TWO_MONTH_TIME_LIMIT)
{
lblAlarms.Text = "Invalid Range. Range must be within two months";
return(null);
}
}
//PARAMETER: LOGLIST
if (!txtParamLogList.Text.Equals(""))
{
//Get the name of the 'site' or 'filter' from the user. This is in the txtParamLogList text field
string logList = txtParamLogList.Text;
//If it is a filter, then apply filter conditions
if (SiteFactory.getFilters().ContainsKey(logList))
{
oraParams += (oraParams.Equals("") ? "" : " AND ") + SiteFactory.getFilters()[logList];
}
//otherwise, it is a site and should be handled as such
else
{
string prefix = SiteFactory.getPrefixFromSiteName(logList);
oraParams += (oraParams.Equals("") ? "" : " AND ") + "LOGLIST = :loglist";
oraCmd.Parameters.Add(new OracleParameter("loglist", "ALM" + prefix));
}
}
//PARAMETER: NAME
if (!txtParamName.Text.Equals(""))
{
// If the user selected to search by tag name
if (coreSearchOption.SelectedValue.Equals(NAME_OPTION))
{
oraParams += (oraParams.Equals("") ? "" : " AND ") + "lower(NAME) LIKE lower(:tagname)";
//oraCmd.Parameters.Add(new OracleParameter("tagname", txtParamName.Text));
}
//If the user sleected to search by Turbine Name
else if (coreSearchOption.SelectedValue.Equals(TURBINE_OPTION))
{
oraParams += (oraParams.Equals("") ? "" : " AND ") + "lower(SATT3) LIKE lower(:tagname)";
//oraCmd.Parameters.Add(new OracleParameter("tagname", txtParamName.Text));
}
// If the user decided to search by description
else
{
oraParams += (oraParams.Equals("") ? "" : " AND ") + "lower(TITLE) LIKE lower(:tagname)";
//oraCmd.Parameters.Add(new OracleParameter("tagname", txtParamName.Text));
}
oraCmd.Parameters.Add(new OracleParameter("tagname", txtParamName.Text));
}
//Finish constructing parameters and queries.
if (oraParams != null)
{
if (!oraParams.Equals(""))
{
oraParams = " WHERE " + oraParams;
}
}
oraCmd.CommandText = oraQry + oraParams + oraOrd;
//Response.Write(oraCmd); return;
//Spit out debug of commands and parameters.
lblDebug.Text += "\n<br />" + oraCmd.CommandText;
foreach (OracleParameter p in oraCmd.Parameters)
{
lblDebug.Text += "\n<br />" + p.ParameterName + ": " + p.Value;
}
//Build the connection string.
string oraCstr = ORALCE_SOURCE;
myDT = oraQueryTable(oraCmd, oraCstr);
clearDictionaries();
return(postTableQueryEdits(myDT));
}