using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Data.Odbc;
using System.Text.RegularExpressions;
using System.Configuration;
using System.Globalization;
using System.Net;
using System.Security.Principal;

namespace CRMPortal
{
    public partial class _ViewProspect : System.Web.UI.Page
    {
        public SqlConnection sql = null;
        public DataTable prospects = new DataTable();
        public DataTable sqlCustomers = new DataTable();
        public ConnectionStringSettings sqlString = ConfigurationManager.ConnectionStrings["actionsql1"];
        public string cust = "";
        public DataTable notes = new DataTable();

        public string SPID = "";
        public string CID = "";

        //public TextBox tbEnterNote = new TextBox();
        //public Button btnSaveNote = new Button();

        protected void Page_Load(object sender, EventArgs e)
        {
            Logic.sqlDisconnect();
            if (Logic.isAdmin(Page.User.Identity.Name.ToUpper())) tbOwner.ReadOnly = false;
            SPID = Logic.getSPIDfromUID(Page.User.Identity.Name);
            
            

            if (!Page.IsPostBack)
            {
                populateProviders();
                cust = Request.QueryString["P"];

                if (cust != null && !cust.Equals(""))
                {
                    tbCustomerID.Text = cust;
                    sqlConnect();
                    sqlQuery();
                    if (prospects.Rows.Count > 0)
                    {
                        sqlFill();
                    }
                    sqlDisconnect();
                }
            }

            else
            {
                cust = tbCustomerID.Text;
                
                //sqlConnect();
                //sqlQuery();
                //sqlDisconnect();
            }
            lblError.Text = "";
            //if (Request.UserAgent.IndexOf("Android") > -1 || Request.UserAgent.IndexOf("iPhone") > -1 || Request.UserAgent.IndexOf("iPad") > -1)
            //{ //This is a mobile device
            //    tbFollowup.Attributes["readonly"] = "readonly";
            //}
            if (cust == null || cust.Equals(""))
            {
                //btnSave.Enabled = false;
                
            }
            else
            {
                //btnSave.Enabled = true;
                CID = tbCustomerID.Text;
                if (tbFollowup.Value.ToString().IndexOf('/') == 1) tbFollowup.Value = "0" + tbFollowup.Value;
                loadNotes();
                loadCalls();
            }
        }


        public void sqlConnect()
        {
            try
            {
                sql = new SqlConnection(sqlString.ConnectionString);
                sql.Open();
            }
            catch (Exception ex)
            {
                throw new Exception("Failed to open a SQL Connection: " + ex.Message);
            }
        }

        public void sqlQuery()
        {
            prospects.Clear();
            string query = "SELECT * FROM dbo.Prospects WHERE ID = '" + cust + "'";// OR NAME = @CUSTOMER;";
            SqlParameter c = new SqlParameter("@CUSTOMER", tbCustomer.Text);
            SqlCommand sqlCommand = new SqlCommand(query, sql);
            sqlCommand.Parameters.Add(c);
            SqlDataReader reader = sqlCommand.ExecuteReader();
            prospects.Load(reader);
            reader.Close();
        }

        public void populateProviders()
        {
            ddProvider.Items.Clear();

            sqlConnect();
            string query = "SELECT * FROM dbo.Providers";
            SqlCommand sqlCommand = new SqlCommand(query, sql);
            SqlDataReader reader = sqlCommand.ExecuteReader();
            DataTable p = new DataTable();
            p.Load(reader);
            reader.Close();

            ddProvider.Items.Clear();
            ddProvider.Items.Add("");
            foreach (DataRow d in p.Rows)
            {
                ddProvider.Items.Add(d[1].ToString());
            }
            sqlDisconnect();
        }

        //This where all the work be doin'
        public void sqlFill()
        {
            DataRow r = prospects.Rows[0];
            //if (Logic.getSPIDfromUID(Page.User.Identity.Name).Equals(r["Owner"].ToString()) || r["Owner"].ToString().Equals("") || r["Owner"].ToString().Equals("None") || Logic.isAdmin(Page.User.Identity.Name))
            if (true)
            {
                if (!Convert.IsDBNull(r["Name"]))
                    tbCustomer.Text = r["Name"].ToString();

                if (!Convert.IsDBNull(r["Address"]))
                    tbAddress.Text = r["Address"].ToString();

                if (!Convert.IsDBNull(r["Address2"]))
                    tbAddress2.Text = r["Address2"].ToString();

                if (!Convert.IsDBNull(r["City"]))
                    tbCity.Text = r["City"].ToString();

                if (!Convert.IsDBNull(r["State"]))
                    tbState.Text = r["State"].ToString();

                if (!Convert.IsDBNull(r["Zip"]))
                    tbZip.Text = r["Zip"].ToString();

                if (!Convert.IsDBNull(r["Phone"]))
                    tbPhone.Text = r["Phone"].ToString();

                if (!Convert.IsDBNull(r["ContactName"]))
                    tbContact.Text = r["ContactName"].ToString();

                if (!Convert.IsDBNull(r["ContactNumber"]))
                    tbContactNumber.Text = r["ContactNumber"].ToString();

                if (!Convert.IsDBNull(r["ContactCellNumber"]))
                    tbContactCellNumber.Text = r["ContactCellNumber"].ToString();

                if (!Convert.IsDBNull(r["ContactEmail"]))
                    tbContactEmail.Text = r["ContactEmail"].ToString();

                if (!Convert.IsDBNull(r["ContactLocation"]))
                    tbContactLocation.Text = r["ContactLocation"].ToString();

                if (!Convert.IsDBNull(r["Contact2Name"]))
                    tbContact2.Text = r["Contact2Name"].ToString();

                if (!Convert.IsDBNull(r["Contact2Number"]))
                    tbContactNumber2.Text = r["Contact2Number"].ToString();

                if (!Convert.IsDBNull(r["Contact2CellNumber"]))
                    tbContact2CellNumber.Text = r["Contact2CellNumber"].ToString();

                if (!Convert.IsDBNull(r["Contact2Email"]))
                    tbContactEmail2.Text = r["Contact2Email"].ToString();

                if (!Convert.IsDBNull(r["Contact2Location"]))
                    tbContactLocation2.Text = r["Contact2Location"].ToString();

                if (!Convert.IsDBNull(r["Contact3Name"]))
                    tbContact3.Text = r["Contact3Name"].ToString();

                if (!Convert.IsDBNull(r["Contact3Number"]))
                    tbContactNumber3.Text = r["Contact3Number"].ToString();

                if (!Convert.IsDBNull(r["Contact3CellNumber"]))
                    tbContact3CellNumber.Text = r["Contact3CellNumber"].ToString();

                if (!Convert.IsDBNull(r["Contact3Email"]))
                    tbContactEmail3.Text = r["Contact3Email"].ToString();

                if (!Convert.IsDBNull(r["Contact3Location"]))
                    tbContactLocation3.Text = r["Contact3Location"].ToString();

                if (!Convert.IsDBNull(r["LastModified"]))
                    lblModified.Text = "Last Modified: " + r["LastModified"].ToString();

                if (!Convert.IsDBNull(r["TimeUsed"]))
                    ddTimeUsed.SelectedValue = r["TimeUsed"].ToString();

                if (!Convert.IsDBNull(r["TireTypes"]))
                {
                    foreach (string s in ((string)r["TireTypes"]).Split(','))
                    {
                        if (s.Equals("1")) cbPass.Checked = true;
                        if (s.Equals("2")) cbLT.Checked = true;
                        if (s.Equals("3")) cbMT.Checked = true;
                        if (s.Equals("4")) cbOTR.Checked = true;
                        if (s.Equals("5")) cbOffroad.Checked = true;
                        if (s.Equals("6")) cbForklift.Checked = true;
                        if (s.Equals("7")) cbRetread.Checked = true;
                        if (s.Equals("8")) cbFoam.Checked = true;
                        if (s.Equals("9")) cbFleet.Checked = true;
                    }
                }

                if (!Convert.IsDBNull(r["NumVehicles"]))
                    tbNumVehicles.Text = r["NumVehicles"].ToString();

                if (!Convert.IsDBNull(r["TireProvider"]))
                {
                    tbProvider.Text = r["TireProvider"].ToString();
                    bool found = false;
                    int ind = 0;
                    foreach (ListItem i in ddProvider.Items)
                    {
                        if (i.Value.ToUpper().Equals(tbProvider.Text.ToUpper()))
                        {
                            ddProvider.SelectedIndex = ind;//i.Value.ToUpper();
                            found = true;
                        }
                        ind++;
                    }
                    if (!found)
                    {
                        ddProvider.SelectedValue = "";
                    }
                }
                if (!Convert.IsDBNull(r["Owner"]))
                    tbOwner.Text = Logic.getNAMEfromSPID (r["Owner"].ToString());

                if (!Convert.IsDBNull(r["Followup"]))
                    tbFollowup.Value = r["Followup"].ToString().Substring(0, 9);
            }
            else
            {
                Label t = new Label();
                t.Height = System.Web.UI.WebControls.Unit.Percentage(25);
                t.Width = System.Web.UI.WebControls.Unit.Percentage(50);
                //t.Attributes["width"] = "50%";
                //t.Attributes["height"] = "25%";
                t.Text = "You are not the owner of this prospect.\n" +
                    "If you believe this is an error, please provide technical support with your username and the ID (" + r["ID"] + ") of this prospect.";
                Page.Controls.Clear();
                Page.Controls.Add(t);
            }

        }


        public void sqlDisconnect()
        {
            try
            {
                sql.Close();
            }
            catch { }
        }

        protected void tbCustomerID_TextChanged(object sender, EventArgs e)
        {
            cust = tbCustomerID.Text;
        }

        protected void btnView_Click(object sender, EventArgs e)
        {
            Response.Redirect("~/ViewProspect.aspx?P=" + tbCustomerID.Text);
        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            bool insert = false; 
            CultureInfo ci = new CultureInfo("en-US");
            string successMessage = "Prospect Saved.";            

            DateTime when = new DateTime();

            if (tbCustomer.Text.Equals(""))
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "SaveError", "ShowError('Missing Prospect Name', 'Please enter a Prospect Name before saving this prospect.');", true);
                return;
            }

            try
            {
                if (!tbFollowup.Value.Equals(""))
                {
                    when = DateTime.Parse(tbFollowup.Value, ci);
                }
                else
                {
                    when = DateTime.Now;
                }
                if (when.Year < 2000 || when.Year > 2050)
                {
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "FollowupDateError", "ShowError('Invalid Followup Date', 'Followup date was entered incorrectly (" + tbFollowup.Value + "). Please select another date.');", true);
                    tbFollowup.Value = "";
                    return;
                }
            }
            catch
            {

                //Response.Write("<h1>Followup date was entered in an incorrect format. Format should be MM/DD/YYYY </h1>");
                try
                {
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "FollowupDateError", "ShowError('Invalid Followup Date', 'Followup date was entered incorrectly. Please select a different date.');", true);
                    return;
                }
                catch (Exception ex)
                {
                    throw new Exception("PostExec: " + ex.Message);
                }
            }

            if (!Logic.spExists(Page.User.Identity.Name))
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "FollowupDateError", "ShowError('Unauthorized', 'You are not authorized to add prospects.');", true);
                return;
            }

            sqlConnect();
            sqlQuery();
            SqlParameter Owner = new SqlParameter("@Owner", Logic.getSPIDfromUID(Page.User.Identity.Name));
            string query = "";
            if (prospects.Rows.Count > 0)
            {
                /*
                if (!tbOwner.Text.Equals("None") && !tbOwner.Text.Equals("") && !tbOwner.Text.Equals(Logic.getNAMEfromUID(Page.User.Identity.Name)) && !Logic.isAdmin(Page.User.Identity.Name))
                {
                    Response.Write("You are not the owner/salesperson of this prospect");
                    try
                    {
                        Page.ClientScript.RegisterStartupScript(this.GetType(), "FollowupDateError", "window.alert('You are not the owner/salesperson of this prospect');", true);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("PostExec: " + ex.Message);
                    }
                    return;
                }
                */
                query = "UPDATE dbo.Prospects SET Name=@Name, Address=@Address, Address2=@Address2, City=@City, State=@State, " +
                    "Zip=@Zip, Phone=@Phone, ContactName=@ContactName, ContactNumber=@ContactNumber, ContactEmail=@ContactEmail, ContactLocation=@ContactLocation," +
                    "Contact2Name=@Contact2Name, Contact2Number=@Contact2Number,  Contact2Email=@Contact2Email, Contact2Location=@Contact2Location,Contact3Name=@Contact3Name, Contact3Number=@Contact3Number, Contact3Email=@Contact3Email, Contact3Location=@Contact3Location, " +
                    "LastModified=@LastModified, TireTypes=@TireTypes, TimeUsed=@TimeUsed, NumVehicles=@NumVehicles, " +
                    "TireProvider=@TireProvider, Followup=@Followup, Owner=@Owner, ModifiedBy=@ModifiedBy, ContactCellNumber=@ContactCellNumber, Contact2CellNumber=@Contact2CellNumber, Contact3CellNumber=@Contact3CellNumber WHERE ID=@ID";

                if (!tbOwner.Text.Equals("None"))
                {
                    Owner = new SqlParameter("@Owner", Logic.getSPIDfromNAME(tbOwner.Text));
                }
                    
            }
            else
            {
                insert = true;
                query = "INSERT INTO dbo.Prospects (Name, Address, Address2, City, State, Zip, Phone, ContactName, " +
                    "ContactNumber, ContactEmail, ContactLocation, Contact2Name, Contact2Number, Contact2Email, Contact2Location, "+
                    "Contact3Name, Contact3Number, Contact3Email, Contact3Location, LastModified, TireTypes, " +
                    "TimeUsed, NumVehicles, TireProvider, Followup, Owner, ModifiedBy, ContactCellNumber, Contact2CellNumber, Contact3CellNumber) Output Inserted.ID VALUES " +
                    "(@Name, @Address, @Address2, @City, @State, @Zip, @Phone, @ContactName, @ContactNumber, @ContactEmail, @ContactLocation, " +
                    "@Contact2Name, @Contact2Number, @Contact2Email, @Contact2Location, @Contact3Name, @Contact3Number, @Contact3Email, @Contact3Location, " +
                    "@LastModified, @TireTypes, @TimeUsed, @NumVehicles, @TireProvider, @Followup, @Owner, @ModifiedBy, @ContactCellNumber, @Contact2CellNumber, @Contact3CellNumber)";

                
            }
            SqlCommand sqlCommand = new SqlCommand(query, sql);

            SqlParameter name = new SqlParameter("@Name", tbCustomer.Text);
            SqlParameter address = new SqlParameter("@Address", tbAddress.Text);
            SqlParameter city = new SqlParameter("@City", tbCity.Text);
            SqlParameter State = new SqlParameter("@State", tbState.Text);
            SqlParameter Zip = new SqlParameter("@Zip", tbZip.Text);
            SqlParameter Phone = new SqlParameter("@Phone", tbPhone.Text);
            SqlParameter Contact = new SqlParameter("@ContactName", tbContact.Text);
            SqlParameter ContactNumber = new SqlParameter("@ContactNumber", tbContactNumber.Text);
            SqlParameter ContactCellNumber = new SqlParameter("@ContactCellNumber", tbContactCellNumber.Text);
            SqlParameter Contact2 = new SqlParameter("@Contact2Name", tbContact2.Text);
            SqlParameter Contact2Number = new SqlParameter("@Contact2Number", tbContactNumber2.Text);
            SqlParameter Contact2CellNumber = new SqlParameter("@Contact2CellNumber", tbContact2CellNumber.Text);
            SqlParameter Contact3 = new SqlParameter("@Contact3Name", tbContact3.Text);
            SqlParameter Contact3Number = new SqlParameter("@Contact3Number", tbContactNumber3.Text);
            SqlParameter Contact3CellNumber = new SqlParameter("@Contact3CellNumber", tbContact3CellNumber.Text);
            //SqlParameter Notes = new SqlParameter("@Notes", tbNotes.Text);
            SqlParameter LastModified = new SqlParameter("@LastModified", DateTime.Now);
            SqlParameter TireTypes = new SqlParameter("@TireTypes", getTireTypeString());
            SqlParameter TimeUsed = new SqlParameter("@TimeUsed", ddTimeUsed.SelectedValue);
            SqlParameter NumVehicles = new SqlParameter("@NumVehicles", (tbNumVehicles.Text.Equals("") ? 0 : Convert.ToInt32(tbNumVehicles.Text)));
            SqlParameter Provider = new SqlParameter("@TireProvider", (ddProvider.SelectedValue.ToString()));
            SqlParameter Followup = new SqlParameter("@Followup", when.Year < 100 ? DateTime.Now : when);
            SqlParameter ID = new SqlParameter("@ID", cust);

            //Response.Write(tbFollowup.Text);

            sqlCommand.Parameters.Add(name);
            sqlCommand.Parameters.Add(address);
            sqlCommand.Parameters.Add(city);
            sqlCommand.Parameters.Add(State);
            sqlCommand.Parameters.Add(Zip);
            sqlCommand.Parameters.Add(Phone);
            sqlCommand.Parameters.Add(Contact);
            sqlCommand.Parameters.Add(Contact2);
            sqlCommand.Parameters.Add(Contact3);
            sqlCommand.Parameters.Add(ContactNumber);
            sqlCommand.Parameters.Add(Contact2Number);
            sqlCommand.Parameters.Add(Contact3Number);
            sqlCommand.Parameters.Add(ContactCellNumber);
            sqlCommand.Parameters.Add(Contact2CellNumber);
            sqlCommand.Parameters.Add(Contact3CellNumber);
            sqlCommand.Parameters.Add(LastModified);
            sqlCommand.Parameters.Add(TireTypes);
            sqlCommand.Parameters.Add(TimeUsed);
            sqlCommand.Parameters.Add(NumVehicles);
            sqlCommand.Parameters.Add(Provider);
            sqlCommand.Parameters.Add(Followup);
            sqlCommand.Parameters.Add(ID);
            sqlCommand.Parameters.Add(Owner);
            sqlCommand.Parameters.AddWithValue("@ModifiedBy", SPID);
            sqlCommand.Parameters.AddWithValue("@ContactEmail", tbContactEmail.Text);
            sqlCommand.Parameters.AddWithValue("@ContactLocation", tbContactLocation.Text);
            sqlCommand.Parameters.AddWithValue("@Contact2Email", tbContactEmail2.Text);
            sqlCommand.Parameters.AddWithValue("@Contact2Location", tbContactLocation2.Text);
            sqlCommand.Parameters.AddWithValue("@Contact3Email", tbContactEmail3.Text);
            sqlCommand.Parameters.AddWithValue("@Contact3Location", tbContactLocation3.Text);
            sqlCommand.Parameters.AddWithValue("@Address2", tbAddress2.Text);
            int worked = 0;

            if (insert)
            {
                SqlDataReader reader = sqlCommand.ExecuteReader();
                worked = reader.HasRows ? 1 : 0;
                reader.Read();
                cust = reader.GetInt32(0).ToString();
                tbCustomerID.Text = cust;
            }
            else
            {
                try
                {
                    sqlConnect();
                }
                catch { }
                try
                {
                    worked = sqlCommand.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    when = DateTime.Now;
                    sqlCommand.Parameters.Remove(Followup);
                    sqlCommand.Parameters.AddWithValue("@Followup", DateTime.Now);

                }
            }

            if (worked > 0)
            {
                lblError.Visible = true;
                lblError.Text = "Record Updated";
            }
            else
            {
                lblError.Visible = true;
                lblError.Text = "Record Update Failed";
            }
            sqlDisconnect();
            //saveNote();
            if (cbAppt.Checked)
            {
                cbAppt.Checked = false;
                System.Threading.Thread.Sleep(100);
                EWS ews = new EWS();
                sqlConnect();
                sqlQuery();
                sqlDisconnect();
                Logic.saveNote("Appointment made for " + Logic.getNAMEfromUID(Page.User.Identity.Name) + " at " + when.ToShortDateString() + " " + when.ToShortTimeString(), CID, SPID, "Appointment", false);
                System.Threading.Thread.Sleep(100);
                loadNotes();
                ews.makeAppointment(Request.LogonUserIdentity.User, prospects.Rows[0]);
                successMessage += " Appointment created for " + Logic.getNAMEfromUID(Page.User.Identity.Name) + ".";
            }
            if (!tbEnterNote.Text.Equals(""))
            {
                try
                {
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "myScript", "SaveNote();", true);
                }
                catch (Exception ex)
                {
                    throw new Exception("PostExec: " + ex.Message);
                }
            }
            sqlConnect();
            sqlQuery();
            if (prospects.Rows.Count > 0)
            {
                sqlFill();
            }
            sqlDisconnect();
            if (newCallAfterSave.Value.Equals("true"))
            {
                Page.ClientScript.RegisterStartupScript(this.GetType(), "NewCall", "NewCall();", true);
            }
        }

        public string getTireTypeString()
        {
            string s = "";
            if (cbPass.Checked) s += ",1";
            if (cbLT.Checked) s += ",2";
            if (cbMT.Checked) s += ",3";
            if (cbOTR.Checked) s += ",4";
            if (cbOffroad.Checked) s += ",5";
            if (cbForklift.Checked) s += ",6";
            if (cbRetread.Checked) s += ",7";
            if (cbFoam.Checked) s += ",8";
            if (cbFleet.Checked) s += ",9";
            return s;
        }

        private void loadCalls()
        {
            sqlConnect();
            string query = "SELECT * FROM dbo.Calls WHERE CustomeriD = @CUSTOMER AND ASA = 0 ORDER BY CallDate DESC;";
            SqlCommand cmd = new SqlCommand(query, sql);
            cmd.Parameters.AddWithValue("@CUSTOMER", (cust != null ? cust : tbCustomer.Text));
            SqlDataReader reader = cmd.ExecuteReader();
            DataTable calls = new DataTable();
            calls.Load(reader);
            reader.Close();
            callsTable.Rows.Clear();

            HtmlTableRow sr = new HtmlTableRow();
            HtmlTableCell sc = new HtmlTableCell();

            sc.ColSpan = 3;
            //sc.Attributes.Add("class", "ListCell");

            sr.Controls.Add(sc);
            sr.Attributes.Add("runat", "server");
            //sc.Controls.Add(tbEnterNote);
            //sc.Controls.Add(btnSaveNote);
            //callsTable.Controls.Add(sr);

            foreach (DataRow r in calls.Rows)
            {
                HtmlTableCell dc = new HtmlTableCell();
                HtmlTableCell nc = new HtmlTableCell();
                HtmlTableCell noteTypeCell = new HtmlTableCell();
                HtmlTableCell uc = new HtmlTableCell();
                HtmlTableRow row = new HtmlTableRow();

                dc.InnerHtml = "<p align='center'>" + ((DateTime)r["CallDate"]).ToString("MM/dd/yyyy") + "</p>";
                //dc.Attributes.Add("class", "ListCell");
                dc.Attributes.Add("width", "15%");

                nc.InnerText = (string)r["CallNote"];
                //nc.Attributes.Add("class", "ListCell");

                //noteTypeCell.InnerText = r["TimeUsed"].ToString();
                //noteTypeCell.Attributes.Add("class", "ListCell");

                uc.InnerHtml = "<b>Salesperson</b>: " + Logic.getNAMEfromSPID(SPID) + "<br/>" +
                    "<b>Contact</b>: " + r["CallContactName"] + "<br/>" +
                    "<b>Call Type</b>: " + r["TimeUsed"].ToString() + "<br/>";
                //uc.Attributes.Add("class", "ListCell");

                row.Controls.Add(dc);
                row.Controls.Add(nc);
                row.Controls.Add(uc);
                //row.Controls.Add(noteTypeCell);

                callsTable.Controls.Add(row);
            }
            sqlDisconnect();
        }


        private void loadNotes()
        {
            sqlConnect();

            string query = "SELECT * FROM dbo.Notes WHERE noteOwnerID = @CUSTOMER ORDER BY noteTime DESC;";
            SqlParameter c = new SqlParameter("@CUSTOMER", (cust != null ? cust : tbCustomer.Text));
            SqlCommand sqlCommand = new SqlCommand(query, sql);
            sqlCommand.Parameters.Add(c);
            SqlDataReader reader = sqlCommand.ExecuteReader();
            notes.Clear();
            notes.Load(reader);
            reader.Close();
            /*
            tbEnterNote.MaxLength = 1023;
            tbEnterNote.ID = "tbEnterNote";
            tbEnterNote.Width = System.Web.UI.WebControls.Unit.Percentage(75);
            
            btnSaveNote.Text = "Save New Note";
            btnSaveNote.ID = "btnSaveNote";
            btnSaveNote.Click += btnSaveNote_Click;
            btnSaveNote.Width = System.Web.UI.WebControls.Unit.Percentage(20);
            */
            notesTable.Rows.Clear();

            HtmlTableRow sr = new HtmlTableRow();
            HtmlTableCell sc = new HtmlTableCell();
            sc.ColSpan = 3;
            sc.Attributes.Add("class", "ListCell");

            sr.Controls.Add(sc);
            sr.Attributes.Add("runat", "server");
            //sc.Controls.Add(tbEnterNote);
            //sc.Controls.Add(btnSaveNote);

            //notesTable.Controls.Add(sr);

            foreach (DataRow r in notes.Rows)
            {
                HtmlTableCell dc = new HtmlTableCell();
                HtmlTableCell nc = new HtmlTableCell();
                HtmlTableCell noteTypeCell = new HtmlTableCell();
                HtmlTableRow row = new HtmlTableRow();

                dc.InnerHtml = "<p align='center'>" + ((DateTime)r["noteTime"]).ToString() + "</p>";
                //dc.Attributes.Add("class", "ListCell");
                dc.Attributes.Add("width", "15%");
                nc.InnerText = (string)r["note"];
                //nc.Attributes.Add("class", "ListCell");
                noteTypeCell.InnerText = r["NoteType"].ToString();
                //noteTypeCell.Attributes.Add("class", "ListCell");
                row.Controls.Add(dc);
                row.Controls.Add(nc);
                row.Controls.Add(noteTypeCell);

                notesTable.Controls.Add(row);
            }
            sqlDisconnect();
        }

        private void saveNote()
        {
            if (tbEnterNote.Text.Equals(""))
            {
                return;
            }
            sqlConnect();
            string query = "INSERT INTO dbo.Notes (note, noteTime, noteOwnerID) VALUES (@NOTE, @NOTETIME, @NOTEOWNER)";
            SqlParameter note = new SqlParameter("@NOTE", tbEnterNote.Text);
            SqlParameter noteTime = new SqlParameter("@NOTETIME", DateTime.Now);
            SqlParameter noteOwner = new SqlParameter("@NOTEOWNER", Convert.ToInt64((cust != null ? cust : tbCustomerID.Text)));
            SqlCommand sqlCommand = new SqlCommand(query, sql);
            sqlCommand.Parameters.Add(note);
            sqlCommand.Parameters.Add(noteTime);
            sqlCommand.Parameters.Add(noteOwner);
            sqlCommand.ExecuteNonQuery();
            sqlDisconnect();
        }

        protected void btnSaveNote_Click(object sender, EventArgs e)
        {
            saveNote();
        }

        public void customerQuery()
        {
            sqlCustomers.Clear();
            string query = "SELECT * FROM dbo.Customers WHERE ASAID = '" + cust + "' OR NAME = @CUSTOMER;";
            SqlParameter c = new SqlParameter("@CUSTOMER", tbCustomer.Text);
            SqlCommand sqlCommand = new SqlCommand(query, sql);
            sqlCommand.Parameters.Add(c);
            SqlDataReader reader = sqlCommand.ExecuteReader();
            sqlCustomers.Load(reader);
            reader.Close();
        }

        protected void btnConvert_Click(object sender, EventArgs e)
        {
            if (!Logic.spExists(Page.User.Identity.Name))
            {
                try
                {
                    Page.ClientScript.RegisterStartupScript(this.GetType(), "Error", "ShowError('Unauthorized', 'You are not authorized to convert prospects');", true);
                }
                catch (Exception ex)
                {
                    throw new Exception("PostExec: " + ex.Message);
                }
                return;
            }

            sqlConnect();

            string query = "SELECT * FROM dbo.Customers WHERE ASAID = @ASAID;";
            SqlParameter a = new SqlParameter("@ASAID", tbASAID.Text);
            SqlCommand sqlCommand = new SqlCommand(query, sql);
            sqlCommand.Parameters.Add(a);
            SqlDataReader reader = sqlCommand.ExecuteReader();
            if (reader.HasRows)
            {
                lblError.Text = "Existing customer with ASA ID: " + tbASAID.Text + " already exists in CRM.";
                lblError.Visible = true;
                sqlDisconnect();
                return;
            }
            sqlDisconnect();

            OdbcConnection odbc = new OdbcConnection();
            odbc.ConnectionString = ConfigurationManager.AppSettings["DSN_Connection"];
            string odbcCommand = "SELECT * FROM CUSMAS WHERE CUST = ?";
            OdbcParameter ao = new OdbcParameter("@ASAID", tbASAID.Text);

            OdbcDataAdapter odbcAdapter = new OdbcDataAdapter(odbcCommand, odbc);
            odbcAdapter.SelectCommand.Parameters.Add("@ASAID", OdbcType.NVarChar, 9).Value = tbASAID.Text;

            DataTable cd = new DataTable();
            odbc.Open();
            odbcAdapter.Fill(cd);

            if (cd.Rows.Count < 1)
            {
                odbc.Close();
                //lblError.Text = "Customer with ASA ID: " + tbASAID.Text + " does not exist.";
                Page.ClientScript.RegisterStartupScript(this.GetType(), "FollowupDateError", "ShowError('Customer not found', 'ASA Customer with ID "+tbASAID.Text+" does not exist in ASA.');", true);
                return;
            }
            else
            {
                DataRow r = cd.Rows[0];
                tbCustomer.Text = r[2].ToString();
                tbAddress.Text = r[3].ToString();
                tbCity.Text = r[5].ToString();
                tbState.Text = r[6].ToString();
                tbZip.Text = r[7].ToString();
                tbPhone.Text = r[8].ToString();
            }
            sqlConnect();

            CultureInfo ci = new CultureInfo("en-US");

            SqlParameter Owner = new SqlParameter("@Owner", SPID);

            query = "INSERT INTO dbo.Customers (Name, Address, Address2, City, State, Zip, Phone, ContactName, " +
                    "ContactNumber, ContactEmail, ContactLocation, Contact2Name, Contact2Number, Contact2Email, Contact2Location, Contact3Name, Contact3Number, "+
                    "Contact3Email, Contact3Location, LastModified, TireTypes, " +
                    "TimeUsed, NumVehicles, TireProvider, Followup, ASAID, Owner, ContactCellNumber, Contact2CellNumber, Contact3CellNumber) OUTPUT INSERTED.ID VALUES " +
                    "(@Name, @Address, @Address2, @City, @State, @Zip, @Phone, @ContactName, @ContactNumber, @ContactEmail, @ContactLocation, " +
                    "@Contact2Name, @Contact2Number, @Contact2Email, @Contact2Location, @Contact3Name, @Contact3Number, @Contact3Email, @Contact3Location, " +
                    "@LastModified, @TireTypes, @TimeUsed, @NumVehicles, @TireProvider, @Followup, @ASAID, @Owner, @ContactCellNumber, @Contact2CellNumber, @Contact3CellNumber)";

            sqlCommand = new SqlCommand(query, sql);

            SqlParameter name = new SqlParameter("@Name", tbCustomer.Text);
            SqlParameter address = new SqlParameter("@Address", tbAddress.Text);
            SqlParameter city = new SqlParameter("@City", tbCity.Text);
            SqlParameter State = new SqlParameter("@State", tbState.Text);
            SqlParameter Zip = new SqlParameter("@Zip", tbZip.Text);
            SqlParameter Phone = new SqlParameter("@Phone", tbPhone.Text);
            SqlParameter Contact = new SqlParameter("@ContactName", tbContact.Text);
            SqlParameter ContactNumber = new SqlParameter("@ContactNumber", tbContactNumber.Text);
            SqlParameter ContactCellNumber = new SqlParameter("@ContactCellNumber", tbContactCellNumber.Text);
            SqlParameter Contact2 = new SqlParameter("@Contact2Name", tbContact2.Text);
            SqlParameter Contact2Number = new SqlParameter("@Contact2Number", tbContactNumber2.Text);
            SqlParameter Contact2CellNumber = new SqlParameter("@Contact2CellNumber", tbContact2CellNumber.Text);
            SqlParameter Contact3 = new SqlParameter("@Contact3Name", tbContact3.Text);
            SqlParameter Contact3Number = new SqlParameter("@Contact3Number", tbContactNumber3.Text);
            SqlParameter Contact3CellNumber = new SqlParameter("@Contact3CellNumber", tbContact3CellNumber.Text);
            SqlParameter LastModified = new SqlParameter("@LastModified", DateTime.Now);
            SqlParameter TireTypes = new SqlParameter("@TireTypes", getTireTypeString());
            SqlParameter TimeUsed = new SqlParameter("@TimeUsed", ddTimeUsed.SelectedValue);
            SqlParameter NumVehicles = new SqlParameter("@NumVehicles", (tbNumVehicles.Text.Equals("") ? 0 : Convert.ToInt32(tbNumVehicles.Text)));
            SqlParameter Provider = new SqlParameter("@TireProvider", (ddProvider.SelectedValue.Equals("Other") ? tbProvider.Text : ddProvider.SelectedValue));
            SqlParameter Followup = new SqlParameter("@Followup", tbFollowup.Value.Equals("") ? DateTime.Now : DateTime.Parse(tbFollowup.Value, ci));
            SqlParameter ASAID = new SqlParameter("@ASAID", tbASAID.Text);

            sqlCommand.Parameters.AddWithValue("@ContactEmail", tbContactEmail.Text);
            sqlCommand.Parameters.AddWithValue("@Contact2Email", tbContactEmail2.Text);
            sqlCommand.Parameters.AddWithValue("@Contact3Email", tbContactEmail3.Text);

            sqlCommand.Parameters.AddWithValue("@ContactLocation", tbContactLocation.Text);
            sqlCommand.Parameters.AddWithValue("@Contact2Location", tbContactLocation2.Text);
            sqlCommand.Parameters.AddWithValue("@Contact3Location", tbContactLocation3.Text);

            sqlCommand.Parameters.AddWithValue("@Address2", tbAddress2.Text);
            //Response.Write(tbFollowup.Text);

            sqlCommand.Parameters.Add(name);
            sqlCommand.Parameters.Add(address);
            sqlCommand.Parameters.Add(city);
            sqlCommand.Parameters.Add(State);
            sqlCommand.Parameters.Add(Zip);
            sqlCommand.Parameters.Add(Phone);
            sqlCommand.Parameters.Add(Contact);
            sqlCommand.Parameters.Add(Contact2);
            sqlCommand.Parameters.Add(Contact3);
            sqlCommand.Parameters.Add(ContactNumber);
            sqlCommand.Parameters.Add(Contact2Number);
            sqlCommand.Parameters.Add(Contact3Number);
            sqlCommand.Parameters.Add(ContactCellNumber);
            sqlCommand.Parameters.Add(Contact2CellNumber);
            sqlCommand.Parameters.Add(Contact3CellNumber);
            sqlCommand.Parameters.Add(LastModified);
            sqlCommand.Parameters.Add(TireTypes);
            sqlCommand.Parameters.Add(TimeUsed);
            sqlCommand.Parameters.Add(NumVehicles);
            sqlCommand.Parameters.Add(Provider);
            sqlCommand.Parameters.Add(Followup);
            sqlCommand.Parameters.Add(ASAID);
            sqlCommand.Parameters.Add(Owner);

            int customer_id = (int)sqlCommand.ExecuteScalar();
            int worked = customer_id;

            if (worked > 0)
            {
                sqlConnect();
                lblError.Visible = true;
                lblError.Text = "New Prospect converted to Existing Customer Prospect";
                query = "USE CRM; UPDATE dbo.Notes SET noteOwnerASAID = @CUSTOMERID WHERE noteOwnerID = @ID;";
                query += "UPDATE dbo.Appointments SET CustomerID = @CUSTOMERID, ASA = 1 WHERE CustomerID = @ID AND ASA = 0;";
                query += "UPDATE dbo.Calls SET CustomerID = @CUSTOMERID, ASA = 1 WHERE CustomerID = @ID AND ASA = 0;";
                query += "DELETE FROM dbo.Prospects WHERE ID = @ID;";
                sqlCommand = new SqlCommand(query, sql);
                sqlCommand.Parameters.AddWithValue("@ID", tbCustomerID.Text);
                sqlCommand.Parameters.AddWithValue("@CUSTOMERID", tbASAID.Text);
                sqlCommand.ExecuteNonQuery();
                Response.Redirect("~/ViewCustomer.aspx?CUST=" + tbASAID.Text);
            }
            else
            {
                lblError.Visible = true;
                lblError.Text = "Record Conversion Failed";
            }
            //sqlDisconnect();
            //saveNote();
            if (cbAppt.Checked)
            {
                cbAppt.Checked = false;
                System.Threading.Thread.Sleep(100);
                EWS ews = new EWS();
                sqlConnect();
                sqlQuery();
                //sqlDisconnect();

                tbEnterNote.Text = "Appointment made for " + Logic.getNAMEfromUID(Page.User.Identity.Name) + " at " + ((DateTime)(tbFollowup.Value.Equals("") ? DateTime.Now : DateTime.Parse(tbFollowup.Value, ci))).ToShortDateString()+" "+((DateTime)(tbFollowup.Value.Equals("") ? DateTime.Now : DateTime.Parse(tbFollowup.Value, ci))).ToShortTimeString();
                saveNote();
                tbEnterNote.Text = "";

                ews.makeAppointment(Request.LogonUserIdentity.User, sqlCustomers.Rows[0]);
            }
            if (!tbEnterNote.Text.Equals(""))
            {
                if (!tbEnterNote.Text.Equals(""))
                {
                    try
                    {
                        Page.ClientScript.RegisterStartupScript(this.GetType(), "myScript", "SaveNote();", true);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("PostExec: " + ex.Message);
                    }
                }
            }
        }
    }
}