//----------------------------------------------------------------------------------------------------------------------------------------------------
//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


    }
}