Sample Source Code
//************************************************************************
// *
// Aero Engine Controls *
// *
//************************************************************************
//
// TITLE:- AECData - Serial Number Lookup Application
// IDENTITY:- Default.aspx.cs
// WRITTEN BY:- R.J.Tyler
// MODIFIED BY:-
// DATE:- 25-JAN-2013
//
//
//
// ©2011 - 2013 Rolls-Royce Goodrich Engine Control Systems Limited.
// The data contained in this document, is the property of Rolls-Royce Goodrich
// Engine Control Systems Limited ("the Company") and it may not be copied or
// communicated to a third party, or used for any purpose, other than that for
// which it was supplied, without the Company's prior written consent.
//
//************************************************************************
// *
// Change History of Default.aspx.cs *
// *
//************************************************************************
//
// The Serial Number lookup application allows test results for any
// serial number stored within the IntraStage database to be viewed
// in a web browser.
//
// On entering a Serial Number (or sub-string) the Application returns
// a list of matching serial number instances, associated event details
// and a hierarchical grid of the relevant tests results for each event
// in the database
//
//
// Version Author Date Change
// Description
//
// 00A R.Tyler 26-JAN-2010 1. Original
// 00B R.Tyler 08-JUN-2010 2. Published on Intra2 as Trial
// 00C R.Tyler/J.Melling 04-OCT-2011 3. Rework of Default.aspx.cs logic
// 00D R.Tyler 15-NOV-2011 4. Prepare for Eworq baseline
// 00E R.Tyler 21-MAY-2012 5. Improve Operation and Features
// 00F R.Tyler 15-OCT-2012 6. Added 'AddMeta' option to menu
// 00G R.Tyler 12-NOV-2012 7. Added Strip Line Logic
// 00H R.Tyler 25-JAN-2013 8. Added Multi Event Enum Selection
// 00I R.Tyler 01-MAY-2013 9. Added SPC Limits Output, Link to DMS
//
//************************************************************************
//
using DBauer.Web.UI.WebControls;
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
namespace Search
{
public partial class Search : System.Web.UI.Page
{
#region A1 - Search class Parameters
///
// A1 - Parameters for the Search Class
///
/////////////////////////////////////////////////////////////////////////////////////
// A2 - General
private String cmdString = "";
private SqlConnection conn;
private GlobalParam.AccessSiteDb currentSiteAccess;
// User entered parameter handling
private string remoteSN = "";
private string remoteEID = "none";
private string remotePFStatus = "empty";
String requiredSortOpt = "-1";
// A4
private string snString = "";
private string trimmedsnString = "";
// A5
private const string eventTableName = "EventItems";
// A7
private const string testTableName = "TestItems";
// B4
protected DBauer.Web.UI.WebControls.HierarGrid HG1;
// B9
private SqlDataAdapter daEventList;
private SqlDataAdapter daEvDetailList;
private DataSet dsEventList;
// B11
private SqlDataAdapter daEventEnumID;
private const string eventEnumTableName = "EventEnumItems";
public String sortOptCookieValue = "0";
public String lUserSelectedOrderByRequest = "void";
#endregion
#region A2 - Page_Load method
///
/// A2 - Page_Load method
///
/////////////////////////////////////////////////////////////////////////////////////
protected void Page_Load(object sender, EventArgs e)
{
// create instance of Master event
Master.SearchMenuClickEvent += new mst2Delegate(mstMCEClick);
if (!ClientScript.IsStartupScriptRegistered("diagsortopt"))
{
ClientScript.RegisterStartupScript(typeof(Page), "diagsortopt", "dlgopen4();", true);
}
GlobalParam.currentPageFileName = new FileInfo(this.Request.Url.LocalPath).Name;
// determine the current database in use - for this website
string sessDbType = ConfigurationManager.AppSettings["ThisWebSite"];
// determine the current database in use - for this website
GlobalFunct.ServerSelect(sessDbType);
// if any parameters present in invoke line
if (Request.QueryString.Count >= 1)
{
// if access to this viewer page is by URL - store the full request query string for checking
String cmdStringRaw = Request.QueryString.ToString();
cmdString = cmdStringRaw.ToLower();
// if serial number parameter present in invoke line (also allow setting of the orderby cookie)
if ((Request.QueryString["SN"] != null) || (Request.QueryString["orderby"] != null))
{
// no action required
}
else
{
GlobalFunct.WebMsgBox.Show(" Please provide a serial number in the URL (e.g &sn=$$$ ) ");
return;
}
}
// if first time through
if (!IsPostBack)
{
if (!String.IsNullOrEmpty(Request.QueryString["sb"]))
{
// store the hide side barflag
if (cmdString.Contains("sb"))
{
String hideSBLower = Request.QueryString["sb"].ToLower().ToString();
String trimmedHideSb = hideSBLower.Trim();
// hide the left side tool bar, uses passed parameter logic "&sb=hide"
if (trimmedHideSb == "hide")
{
// apply page class to enable side hidebar
if (currentSiteAccess == 0) // mech database access
{
((HtmlGenericControl)Master.FindControl("PageBody")).Attributes.Add("class", "mechsite sp hidesb");
leftbar.Visible = false;
}
else
{
((HtmlGenericControl)Master.FindControl("PageBody")).Attributes.Add("class", "elecsite sp hidesb");
leftbar.Visible = false;
}
}
else
{
leftbar.Visible = true;
((HtmlGenericControl)Master.FindControl("PageBody")).Attributes.Remove("hidesb");
// apply Focus, as the sidebar is in view
ClientScript.RegisterStartupScript(typeof(Page), "SetFocus", "");
}
}
}
else
{
// improved method of control focus -- SNEntryTextBox.Focus();
ClientScript.RegisterStartupScript(typeof(Page), "SetFocus", "");
}
// if serial number parameter present in invoke line
if (Request.QueryString["SN"] != null)
{
// store the serial number
if (cmdString.Contains("sn"))
{
remoteSN = Request.QueryString["sn"].ToString();
HRemoteSN.Value = remoteSN;
}
// store the event id
if (cmdString.Contains("eventid"))
{
remoteEID = Request.QueryString["eventid"].ToString();
}
else if (cmdString.Contains("eid"))
{
remoteEID = Request.QueryString["eid"].ToString();
}
else
{
remoteEID = "";
}
// store the pass fail status
if (cmdString.Contains("pf"))
{
String lRemotePFStatus = Request.QueryString["pf"].ToString();
remotePFStatus = GlobalFunct.UppercaseFirst(lRemotePFStatus);
}
else
{
string localPFStatus = "0"; //set PassFailFilter to "None"
rblPassFail.Items[Convert.ToInt32(localPFStatus)].Selected = true;
}
// check for the existence of a cookie
if (Request.Cookies["SortPref"] != null)
{
//requestcode = Convert.ToInt32(Request.Cookies["SortPref"].Value);
sortOptCookieValue = Request.Cookies["SortPref"].Value;
}
else
{
sortOptCookieValue = "-1";
}
// Set user selected orderby preference - check and write to cookie if requested
SetSortByPreference(sortOptCookieValue);
// if serial number string present carryout db search
if (remoteSN != "")
{
SNEntryTextBox.Text = remoteSN;
DoSearch(remoteEID);
}
try
{
// handle user supplied (url) parameters
if ((remoteEID != null) && (remoteEID != ""))
{
if (remoteEID != "none")
{
if (remotePFStatus != "empty")
{
if (rblPassFail.Items.FindByValue(remotePFStatus) != null)
{
rblPassFail.Items.FindByValue(remotePFStatus).Selected = true;
LoadPassFailSelection(remotePFStatus);
}
else if (rblPassFail.Items.FindByText(remotePFStatus) != null)
{
rblPassFail.Items.FindByText(remotePFStatus).Selected = true;
LoadPassFailSelection(remotePFStatus);
}
}
ddlEventRecord.ClearSelection();
if (ddlEventRecord.Items.FindByValue(remoteEID) != null)
{
ddlEventRecord.Items.FindByValue(remoteEID).Selected = true;
// generate hierar grid table
LoadTableData(Convert.ToInt32(ddlEventRecord.SelectedValue));
string EID = remoteEID;
// store EID input string
HEventRecIdentity.Value = EID;
}
else
{
GlobalFunct.WebMsgBox.Show("The Event Identifier has not been found in the lookup table for the current Serial Number. Ref: D351");
return;
}
}
}
}
catch (Exception ex)
{
if (GlobalFunct.IsCriticalException(ex))
if (GlobalParam.showXMLerrors) { throw; }
else
{
GlobalFunct.WebMsgBox.Show("A database connection error has occurred. Ref:D353. Detail:" + ex.Message);
}
}
}
}
else
{
/// postback in progress
// get javascript post back parameter
String PostBackParameter = Request["__EVENTARGUMENT"];
// Reading from hidden variables stored from search page
String stringToCheck = "SortOption";
if (!String.IsNullOrEmpty(PostBackParameter))
{
if (PostBackParameter.Contains("bHMenu"))
{
// PostBackParameter will contain 'bHMenu' or point click (numeric) parameters
// no processing required
}
else if (PostBackParameter.Contains(stringToCheck))
{
String[] splitEventArg = new String[2];
splitEventArg = PostBackParameter.Split(',');
if ((splitEventArg != null) || (splitEventArg.Length > 1))
{
// first parameter can be discarded
requiredSortOpt = splitEventArg[1];
}
else
{
requiredSortOpt = splitEventArg[0];
}
//SetSortByPreference(requiredSortOpt);
SelectSortBy(requiredSortOpt);
// reload the page applying new sort option
DoSearch("");
}
}
}
}
#endregion
#region A3 - SearchButton_Click
///
/// A3 - Serial Number Lookup on button-click
///
/// //////////////////////////////////////////////////////////////////////////////////
protected void SearchButton_Click(object sender, EventArgs e)
{
DoSearch("");
}
#endregion
#region A4 - DoSearch
///
/// A4 - Carry out a serial number search
///
/// //////////////////////////////////////////////////////////////////////////////////
private void DoSearch(string remoteEID)
{
// Table for AssetSN/AssetID
DataTable dtSerialNumbers = new DataTable();
// Reset the SN re-set flag to enable new paging model
HSerialReset.Value = "true";
dtSerialNumbers = null;
// store the user input SN string
snString = SNEntryTextBox.Text;
trimmedsnString = snString.Trim();
//if the string is empty - clean the drop down lists for serial number and event
if (trimmedsnString == "")
{
// clear data from all controls
ClearGridPage();
return;
}
try
{
// create AssetSN/AssetID Table
dtSerialNumbers = this.CreateAssetSNTable(SNEntryTextBox.Text, cblFilter.Items[0].Selected, cblFilter.Items[1].Selected, cblFilter.Items[2].Selected);
if (dtSerialNumbers.Rows.Count == 0)
{
GlobalFunct.WebMsgBox.Show(" No serial numbers found which match your criteria.");
// clear data from all controls
ClearGridPage();
return;
}
else
{
// populate the Serial Number drop down list
this.ddlAssetSN.DataSource = dtSerialNumbers;
this.ddlAssetSN.DataBind();
if (remoteEID != "")
{
this.ddlAssetSN.Items.FindByValue(GetAssetIDFromEventID(remoteEID)).Selected = true;
}
}
LoadEventList(Convert.ToInt32(ddlAssetSN.SelectedValue));
}
catch (Exception ex)
{
if (GlobalFunct.IsCriticalException(ex))
if (GlobalParam.showXMLerrors) { throw; }
else
{
GlobalFunct.WebMsgBox.Show("An error has occurred. Ref:D486. Detail:" + ex.Message);
}
}
finally
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
#endregion
#region A5 - CreateAssetSNTable
///
/// A5 - CreateAssetSNTable - Create the Asset Serial Number Table based on
/// current SN entered by the user. Returns an AssetID + AssetSN table
///
/////////////////////////////////////////////////////////////////////////////////////
public DataTable CreateAssetSNTable(string currentSN, bool SerialNumberMOFilter, bool SerialNumberMRFilter, bool SerialNumberCROFilter)
{
DataSet dsAssetItems = new DataSet();
// store query string locally
string SQLSNTable = "";
// declare return container object
DataTable dtSerialNumbers = new DataTable();
dtSerialNumbers = null;
// define parameters used in command object
SqlParameter param = new SqlParameter();
param.ParameterName = "@localCurrSN";
// handle user input spaces
string localCurrSN = currentSN.Trim();
conn = GlobalParam.DBConnect();
// handle user input string gaps
if (conn != null)
{
localCurrSN.Replace(" ", "%");
SQLSNTable = "SELECT DISTINCT AssetSN, AssetID FROM AssetItems ";
SQLSNTable = SQLSNTable + " WHERE AssetSN LIKE ('%" + @localCurrSN + "%') ";
SQLSNTable = SQLSNTable + " AND exists (Select AssetID from tmpRptAssetEvent)";
// if the MO filter checkbox in checked state
if (!SerialNumberMOFilter)
{
SQLSNTable = SQLSNTable + " AND [AssetSN] not like 'MO%'";
}
if (!SerialNumberMRFilter)
{
SQLSNTable = SQLSNTable + " AND [AssetSN] not like 'MR%'";
}
if (!SerialNumberCROFilter)
{
SQLSNTable = SQLSNTable + " AND [AssetSN] not like 'CRO%'";
}
// additional query clause to handle disposed (hidden) data
SQLSNTable = SQLSNTable + " AND ACTIVE = 1";
// sort with latest sn at the top of the list
SQLSNTable = SQLSNTable + " ORDER BY AssetSN DESC";
// instantiate a new command with a query and connection
SqlCommand cmd = new SqlCommand(SQLSNTable, conn);
// initialise SqlDataAdapter with the query/connection command
SqlDataAdapter daAssetItems = new SqlDataAdapter(cmd);
// add parameter to the adapter object
daAssetItems.SelectCommand.Parameters.Add(new SqlParameter("@localCurrSN", localCurrSN));
// fill in insert, update, and delete commands
SqlCommandBuilder cmdBldr = new SqlCommandBuilder(daAssetItems);
try
{
conn.Open();
// fill the dataset
daAssetItems.Fill(dsAssetItems, eventTableName);
int numberOfRows = daAssetItems.Fill(dsAssetItems, "AssetSN");
dtSerialNumbers = dsAssetItems.Tables["AssetSN"];
}
catch (Exception ex)
{
if (GlobalFunct.IsCriticalException(ex))
if (GlobalParam.showXMLerrors) { throw; }
else
{
GlobalFunct.WebMsgBox.Show("A database connection error has occurred. Ref:D582. Detail:" + ex.Message);
}
}
finally
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
} // no available connection!
// return AssetID + AssetSN table
return dtSerialNumbers;
}
#endregion
#region A6 - EventDetChangeUpdateGrid
///
/// A6 - EventDetChangeUpdateGrid - Update the data grid with new event details
///
/////////////////////////////////////////////////////////////////////////////////////
private void EventDetChangeUpdateGrid(string EventRecordIdentity)
{
// instantiate new Data Sets
DataSet dsTestRunDet = new DataSet();
DataTable dtTestRunDet = new DataTable();
DataSet dsChartDet = new DataSet();
try
{
int EventId = Convert.ToInt32(EventRecordIdentity);
// query string
string SQLquery;
StringBuilder sbTestRunDet;
sbTestRunDet = new StringBuilder();
// build query string
sbTestRunDet.Append(" SELECT IS_MetaData.MetaName AS MName, IS_MetaData.MetaValue AS MValue");
sbTestRunDet.Append(" FROM IS_MetaData INNER JOIN IS_MetaEnum ");
sbTestRunDet.Append(" ON IS_MetaData.MetaEnumID = IS_MetaEnum.MetaEnumID ");
sbTestRunDet.Append(" WHERE (((IS_MetaData.LocalID) = " + EventId + ") ");
sbTestRunDet.Append(" AND IS_MetaEnum.MetaEnumName = 'Events')");
SQLquery = sbTestRunDet.ToString();
// set up database link
conn = GlobalParam.DBConnect();
// init SqlDataAdapter with connection and select command
daEvDetailList = new SqlDataAdapter(SQLquery, conn);
conn.Open();
// fill the dataset
daEvDetailList.Fill(dsTestRunDet, testTableName);
// populate the grid when data is available
if (dsTestRunDet.Tables[0].Rows.Count > 0)
{
// populate a grid view with the data returned
dgEventDetail.DataSource = dsTestRunDet;
// bind the data to the grid
dgEventDetail.DataBind();
}
else
{
// bind an empty row
dtTestRunDet = dsTestRunDet.Tables[0];
dtTestRunDet.Rows.Add();
dgEventDetail.DataSource = dtTestRunDet;
dgEventDetail.DataBind();
}
// end if
}
catch (Exception ex)
{
if (GlobalFunct.IsCriticalException(ex))
if (GlobalParam.showXMLerrors) { throw; }
else
{
GlobalFunct.WebMsgBox.Show("An Error has occurred. Ref:D667. Detail:" + ex.Message);
}
}
finally
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
#endregion
#region A7 - CreateEventsTable
///
/// A7 - CreateEventsTable - Events Table creation of Results Records
///
/// //////////////////////////////////////////////////////////////////////////////////
private DataTable CreateEventsTable(long assetID)
{
// declare DataTable
DataTable dtEventRecordList = new DataTable();
if (assetID != -1)
{
// query string
string SQLEventsTable;
// set up database link
conn = GlobalParam.DBConnect();
// instantiate a new DataSet
dsEventList = new DataSet();
// build query string
SQLEventsTable = "SELECT convert(nvarchar(20),tmpRptAssetEvent.EventTimeStamp,113) + ' - ' + ProcessEnum.Name + ' - ' + EventEnum.Name + ' - ' + ResultEnum.ResultEnumName as [EventRecord],tmpRptAssetEvent.EventID as [EventID],";
SQLEventsTable = SQLEventsTable + " tmpRptAssetEvent.EventTimeStamp, ProcessEnum.Name AS ProcessName, EventEnum.Name AS EventName, EventEnum.Description, AssetItems.AssetSN, ResultEnum.ResultEnumName, [ModelEnum].PartNo";
SQLEventsTable = SQLEventsTable + " FROM (AssetItems INNER JOIN (((ProcessEnum INNER JOIN tmpRptAssetEvent ON ProcessEnum.ProcessEnumID = tmpRptAssetEvent.ProcessEnumID)";
SQLEventsTable = SQLEventsTable + " INNER JOIN ProductLineEnum ON ProcessEnum.ProductLineEnumID = ProductLineEnum.ProductLineEnumID)";
SQLEventsTable = SQLEventsTable + " INNER JOIN EventEnum ON tmpRptAssetEvent.EventEnumID = EventEnum.EventEnumID) ON AssetItems.AssetID = tmpRptAssetEvent.AssetID)";
SQLEventsTable = SQLEventsTable + " INNER JOIN modelEnum ON tmpRptAssetEvent.modelenum = modelenum.modelEnumid";
SQLEventsTable = SQLEventsTable + " INNER JOIN ResultEnum ON tmpRptAssetEvent.EventResult = ResultEnum.ResultEnumID";
SQLEventsTable = SQLEventsTable + " WHERE (((tmpRptAssetEvent.AssetID) = " + assetID + ")) ";
SQLEventsTable = SQLEventsTable + " ORDER BY [EventTimeStamp] DESC";
// init SqlDataAdapter with connection and select command
daEventList = new SqlDataAdapter(SQLEventsTable, conn);
try
{
conn.Open();
// fill the dataset
daEventList.Fill(dsEventList, eventTableName);
int numberOfRows = daEventList.Fill(dsEventList, "EventRecord");
dtEventRecordList = dsEventList.Tables["EventRecord"];
// get part number information for display to user
Object partNo = dtEventRecordList.Rows[0]["PartNo"];
String PartNumString = Convert.ToString(partNo);
// get process name information for display to user
Object procName = dtEventRecordList.Rows[0]["ProcessName"];
String lProcNameString = Convert.ToString(procName);
// get process name information for display to user
Object eventId = dtEventRecordList.Rows[0]["EventID"];
String lEventIdString = Convert.ToString(eventId);
PartNumLabel.Text = " " + PartNumString + " ";
}
catch (Exception ex)
{
if (GlobalFunct.IsCriticalException(ex))
if (GlobalParam.showXMLerrors) { throw; }
else
{
GlobalFunct.WebMsgBox.Show("A database connection error has occurred. Ref:D745. Detail:" + ex.Message);
}
}
finally
{
if (conn != null)
{
conn.Close();
conn.Dispose();
}
}
}
else
{
// AssetID has not been supplied
dtEventRecordList = null;
}
// return the EventRecord - 9 fields
return dtEventRecordList;
}
#endregion
#region A8 - GetEventDetails
///
/// A8 - GetEventDetails - Events detail string build for user screen
///
/// //////////////////////////////////////////////////////////////////////////////////
private string GetEventDetails(long eventID)
{
string EventInfo = "";
if (eventID != -1)
{
// query string
string SQLEventDetails;
// set up database link
conn = GlobalParam.DBConnect();
// instantiate a new DataSet
dsEventList = new DataSet();
SQLEventDetails = "Select '| Event Items | |||
|---|---|---|---|
| Event Detail: | ' + PE.Name + ' - ' + EE.Name"; SQLEventDetails = SQLEventDetails + "+ ' | DateTime: | ' + convert(nvarchar(20), TMP.EventTimeStamp,113)"; SQLEventDetails = SQLEventDetails + "+ ' |
| Station: | ' + StationName"; SQLEventDetails = SQLEventDetails + "+ ' | Operator: | ' + case when US.FirstName = 'Unknown' then '' else US.FirstName + ' ' end + US.LastName"; SQLEventDetails = SQLEventDetails + "+ ' |
| Test Duration: | '"; SQLEventDetails = SQLEventDetails + "+ right('0' + rtrim(convert(char(2), convert(int,ExecTime) / (60 * 60))), 2)"; SQLEventDetails = SQLEventDetails + "+ ':' + right('0' + rtrim(convert(char(2), (convert(int,ExecTime) / 60) % 60)), 2) + ':'"; SQLEventDetails = SQLEventDetails + "+ right('0' + rtrim(convert(char(2), convert(int,ExecTime) % 60)),2)"; SQLEventDetails = SQLEventDetails + "+ ' | Result: | '"; SQLEventDetails = SQLEventDetails + "+ upper(RE.ResultEnumName) + ' |