//---------------------------------------------------------------------------------------------------------------------------------------------------- //Program Listing for: assignments.aspx.cs //Project: asp.net //Namespace: cs //---------------------------------------------------------------------------------------------------------------------------------------------------- using System; using System.Collections; using System.ComponentModel; using System.Data; using System.Drawing; using System.Web; using System.Web.SessionState; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.HtmlControls; using Microsoft.Data.Odbc; namespace hearing_officer_info { /// <summary> /// Summary description for WebForm1. /// </summary> public class WebForm1 : System.Web.UI.Page { protected System.Web.UI.WebControls.Label Label1; protected System.Web.UI.WebControls.DropDownList ddlOfficers; protected System.Web.UI.WebControls.DataGrid dgrDays; protected System.Web.UI.WebControls.TextBox txtPassword; protected System.Web.UI.WebControls.Button Lookup; protected System.Web.UI.WebControls.Label lblInfo; protected System.Web.UI.WebControls.Label lblInfo2; protected System.Web.UI.WebControls.Label Label3; protected System.Web.UI.WebControls.Label Label2; /// <summary> /// get connection string for main foxpro database /// </summary> private string GetConnectionString() { string connstring = @"removed"; return connstring; } /// <summary> /// check if blank password /// </summary> private bool BlankPassword() { return (ddlOfficers.SelectedItem.Text.Trim() == "" || txtPassword.Text.Trim() == ""); } /// <summary> /// do a select and return a field value /// </summary> /// <param name="sql"> /// select string for query /// </param> /// <param name="fieldname"> /// field from select to get value for /// </param> /// <param name="returnvalue"> /// out string variable, returns the result or blank string /// </param> /// <returns> /// boolean, true if select returned one or more records /// </returns> private bool SingleValSelect(string sql, string fieldname, out string returnvalue) { string connstring = GetConnectionString(); Microsoft.Data.Odbc.OdbcConnection cn = new Microsoft.Data.Odbc.OdbcConnection(connstring); cn.Open(); OdbcCommand commandCheck = new OdbcCommand(sql, cn); // using a datareader for speed OdbcDataReader dataReadCheck = commandCheck.ExecuteReader(); bool result = false; returnvalue = ""; if (dataReadCheck.Read()) { result = true; if (fieldname != "") returnvalue = dataReadCheck[fieldname].ToString(); else returnvalue = ""; } dataReadCheck.Close(); cn.Close(); return result; } /// <summary> /// process the page returning results as appropriate /// </summary> private void ProcessPage() { if (BlankPassword()) { lblInfo.Text = "Invalid choice or blank password, retry!"; return; } string connstring = GetConnectionString(); Microsoft.Data.Odbc.OdbcConnection cn = new Microsoft.Data.Odbc.OdbcConnection(connstring); string sql; /// <remark> /// this first lookup validates the password against the hearing officer name via sql lookup /// note password obviously obfuscated for this web posting /// </remark> if (txtPassword.Text.Trim() == "@#$AASD") { sql = "select *, proper(clast) as clast2 from hrofficers " + "where proper(clast) = [" + ddlOfficers.SelectedItem.Text + "] order by clast"; } else { sql = "select *, proper(clast) as clast2 from hrofficers " + "where proper(clast) = [" + ddlOfficers.SelectedItem.Text + "] and " + "conlinepassword = [" + txtPassword.Text.Trim() + "] order by clast"; } string officer_id; /// <remark> /// attempt to get results for lookup (one way of checking password, if password /// is not valid it will return false, i.e. empty resultset /// </remark> if (SingleValSelect(sql, "hrofficer_id", out officer_id)) { string hearingOffID = officer_id; //lblInfo.Text = dataReadPasswordCheck["hrofficer_id"].ToString(); string sql_count_results; /// <remark> /// the special hearing officer lookup returns all hearing officers, not just one, /// for debugging and administration (assignment) purposes /// </remark> if (ddlOfficers.SelectedItem.Text.Substring(0, 7) == "Special") { sql_count_results = "SELECT cnt(*) as count " + " FROM azsboe!hrofficers INNER JOIN azsboe!hrlink" + " INNER JOIN azsboe!hrassign " + " ON Hrlink.hras_id = Hrassign.hras_id " + " ON Hrofficers.hrofficer_id = Hrlink.hrofficer_id " + " WHERE Hrassign.dhdate >= date() "; } /// <remark> /// otherwise do normal lookup for one officer only /// </remark> else { sql_count_results = "SELECT cnt(*) as count " + " FROM azsboe!hrofficers INNER JOIN azsboe!hrlink" + " INNER JOIN azsboe!hrassign " + " ON Hrlink.hras_id = Hrassign.hras_id " + " ON Hrofficers.hrofficer_id = Hrlink.hrofficer_id " + " WHERE Hrassign.dhdate >= date() AND hrofficers.hrofficer_id = " + hearingOffID; } string resultstring; SingleValSelect(sql_count_results, "count", out resultstring); // this returns the number of matches for upcoming assignments for this person int numRecords = Convert.ToInt32(resultstring, 10); // if no matches, show an appropriate message and stop if (numRecords == 0) { lblInfo2.Text = "<font color='blue'><b>No pending future hearings for this officer!</b></font>"; } // otherwise continue along, gather full results for lookup else { cn.Open(); string sql2; // special returns results for all officers if (ddlOfficers.SelectedItem.Text.Substring(0, 7) == "Special") { sql2 = "SELECT trim(Hrofficers.cfirst) + ' ' + trim(Hrofficers.clast) as Name, [<a target = '_blank' href='/cgi-bin/hrooms.pl#] + Hrassign.clocationid + ['>] + Hrassign.clocationid + [</a>] as Location, dtoc(Hrassign.dhdate) as Date, " + "Hrassign.cstart as Start, Hrassign.cend as End, Hrassign.ncountycode as County, iif(Hrassign.lpanel, [P], []) as Panel, " + "iif(Hrlink.lconfirmed, [<font color='green'><b>Yes</b></font>], [<font color='red'><b>No</b></font>]) as Confirmed, " + "iif(Hrlink.ldeclined, [<font color='green'><b>Yes</b></font>], [<font color='red'><b>No</b></font>]) as Declined, " + " [<a target = '_blank' href = '/confirm.pl?hr_assign=] + alltrim(str(hrlink.hras_id)) + [&hr_officer=] + alltrim(str(hrlink.hrofficer_id)) + [&hr_link=] + alltrim(str(hrlink.hrlink_id)) + ['> Confirm/Decline </a>] as Choice" + " FROM azsboe!hrofficers INNER JOIN azsboe!hrlink" + " INNER JOIN azsboe!hrassign " + " ON Hrlink.hras_id = Hrassign.hras_id " + " ON Hrofficers.hrofficer_id = Hrlink.hrofficer_id " + " WHERE Hrassign.dhdate >= date() " + " order by 3, 4 "; } // otherwise only one officers results else { sql2 = "SELECT trim(Hrofficers.cfirst) + ' ' + trim(Hrofficers.clast) as Name, [<a target = '_blank' href='/cgi-bin/hrooms.pl#] + Hrassign.clocationid + ['>] + Hrassign.clocationid + [</a>] as Location, dtoc(Hrassign.dhdate) as Date, " + "Hrassign.cstart as Start, Hrassign.cend as End, Hrassign.ncountycode as County, iif(Hrassign.lpanel, [P], []) as Panel, " + "iif(Hrlink.lconfirmed, [<font color='green'><b>Yes</b></font>], [<font color='red'><b>No</b></font>]) as Confirmed, " + "iif(Hrlink.ldeclined, [<font color='green'><b>Yes</b></font>], [<font color='red'><b>No</b></font>]) as Declined, " + " [<a target = '_blank' href = '/confirm.pl?hr_assign=] + alltrim(str(hrlink.hras_id)) + [&hr_officer=] + alltrim(str(hrlink.hrofficer_id)) + [&hr_link=] + alltrim(str(hrlink.hrlink_id)) + ['> Confirm/Decline </a>] as Choice" + " FROM azsboe!hrofficers INNER JOIN azsboe!hrlink" + " INNER JOIN azsboe!hrassign " + " ON Hrlink.hras_id = Hrassign.hras_id " + " ON Hrofficers.hrofficer_id = Hrlink.hrofficer_id " + " WHERE Hrassign.dhdate >= date() AND hrofficers.hrofficer_id = " + hearingOffID + " order by 3, 4 "; } // dynamically adjust height of results grid to reflect number of records obtained dgrDays.Height = 10 + 10 * numRecords; // bind query results to the dgrDays data grid, voila done OdbcCommand commandActualResults = new OdbcCommand(sql2, cn); OdbcDataReader dataReadActualResults = commandActualResults.ExecuteReader(); dgrDays.Visible = true; dgrDays.DataSource = dataReadActualResults; dgrDays.DataBind(); // pick up after yourself (mom says!) dataReadActualResults.Close(); } } else { // show appropriate error. // initially passwords are upper alpha of length 5, so 26**5 possibilities, for now this is decent // may add more length later. hearing officers receive their passwords from the automated // scheduling emails for now as a reminder. most are non-technical so I am keeping them // somewhat short and simply for convenience during early test at least. - Scott lblInfo.Text = "Invalid password, please retry!"; } // close connection cn.Close(); } /// <summary> /// normal page load event /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void Page_Load(object sender, System.EventArgs e) { // Put user code to initialize the page here // close informational text boxes lblInfo.Text = ""; lblInfo2.Text = ""; // make grid invisible for now dgrDays.Visible = false; // if first load, grab the hearing officers to the listbox if (!Page.IsPostBack) InitializeStateList(); // otherwise attempt to process lookup else ProcessPage(); } /// <summary> /// fill in the listbox with hearing officer names /// </summary> private void InitializeStateList() { string connstring = GetConnectionString(); Microsoft.Data.Odbc.OdbcDataAdapter da1 = new OdbcDataAdapter("select *, proper(clast) as clast2 from hrofficers order by clast", GetConnectionString()); DataSet mydataset = new DataSet(); try { da1.Fill(mydataset, "view1"); } catch { lblInfo.Text = "trouble with lookup, database may be down, please retry later."; mydataset = null; } finally { } if (mydataset != null) { DataTable table1 = mydataset.Tables[0]; if (table1 != null) { DataView dv = new DataView(table1); ddlOfficers.DataTextField = "clast2"; ddlOfficers.DataValueField = "hrofficer_id"; ddlOfficers.DataSource = dv; ddlOfficers.DataBind(); } else { lblInfo.Text = "trouble with lookup, database may be down, please retry later."; } } return; } #region Web Form Designer generated code override protected void OnInit(EventArgs e) { // // CODEGEN: This call is required by the ASP.NET Web Form Designer. // InitializeComponent(); base.OnInit(e); } /// <summary> /// Required method for Designer support - do not modify /// the contents of this method with the code editor. /// </summary> private void InitializeComponent() { this.Load += new System.EventHandler(this.Page_Load); } #endregion } }