using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Web.Services;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Configuration;

namespace CRMPortal
{
    public partial class Admin : System.Web.UI.Page
    {
        private SqlConnection sql = null;
        private ConnectionStringSettings sqlString = ConfigurationManager.ConnectionStrings["actionsql1"];

        public DataTable customers = new DataTable();
        public DataTable prospects = new DataTable();

        private string siteAdmin = ConfigurationManager.AppSettings["siteAdmin"];

        protected void Page_Load(object sender, EventArgs e)
        {
            //if (!siteAdmin.Equals(Page.User.Identity.Name.ToUpper()))
            if (!Logic.isAdmin(Page.User.Identity.Name))
            {
                Response.Write("You are not authorized to access this page.");
                Response.Redirect("~/NotAllowed.html");
                return;
            }

            if (Request.QueryString.Count > 0)
            {
                System.Collections.Specialized.NameValueCollection qs = Request.QueryString;
                if (qs["A"] != null)
                {
                    if (qs["A"].Equals("D")) //Delete
                    {
                        string type = qs["T"];
                        string id = qs["I"];
                        string query = "";
                        if (type.Equals("C")) query = "DELETE FROM dbo.Customers WHERE ASAID = @ID";
                        if (type.Equals("P")) query = "DELETE FROM dbo.Prospects WHERE ID = @ID";

                        SqlParameter spID = new SqlParameter("@ID", id);
                        sqlConnect();
                        SqlCommand cmd = new SqlCommand(query, sql);
                        cmd.Parameters.Add(spID);
                        int i = cmd.ExecuteNonQuery();
                        sqlDisconnect();
                        if (i > 0) Response.Write("Record Deleted");
                        else Response.Write("Error Deleting Record");
                    }

                    if (qs["A"].Equals("U")) //Unlock
                    {
                        string type = qs["T"];
                        string id = qs["I"];
                        string query = "";
                        if (type.Equals("C")) query = "UPDATE dbo.Customers SET Owner='-100' WHERE ASAID = @ID";
                        if (type.Equals("P")) query = "UPDATE dbo.Prospects SET Owner='-100' WHERE ID = @ID";
                        sqlConnect();
                        SqlCommand cmd = new SqlCommand(query, sql);
                        SqlParameter spID = new SqlParameter("@ID", id);
                        cmd.Parameters.Add(spID);
                        int i = cmd.ExecuteNonQuery();
                        sqlDisconnect();
                        if (i > 0) Response.Write("Record Unlocked");
                        else Response.Write("Error Unlocking Record");
                    }

                    if (qs["A"].Equals("R")) //Reassign
                    {
                        Response.Redirect("Reassign.aspx?T="+qs["T"]+"&I="+qs["I"]);
                    }
                }
            }

            lblError.Text = "";
            if (!Page.IsPostBack)
            {
                sqlQuery();
            }
        }

        public void sqlConnect()
        {

            sql = new SqlConnection(sqlString.ConnectionString);
            sql.Open();
        }

        public void userQuery()
        {
            string query = "USE CRM; SELECT * FROM dbo.Users";
            SqlCommand cmd = new SqlCommand(query, sql);
            SqlDataReader reader = cmd.ExecuteReader();
            DataTable d = new DataTable();
            d.Load(reader);
            reader.Close();

            HtmlTableRow ar = new HtmlTableRow();
            HtmlTableCell ar1 = new HtmlTableCell();
            ar1.InnerHtml = "<a class='btn btn-default btn-sm' href='AdminEditUser.aspx?U=-1'>ADD NEW USER</a>";
            //ar1.Attributes["class"] = "ListCell";
            ar.Controls.Add(ar1);
            tblUsers.Controls.Add(ar);

            foreach (DataRow r in d.Rows)
            {
                HtmlTableCell name = new HtmlTableCell();
                HtmlTableCell username = new HtmlTableCell();
                HtmlTableCell str = new HtmlTableCell();
                HtmlTableCell slm = new HtmlTableCell();
                HtmlTableCell uid = new HtmlTableCell();
                HtmlTableCell but = new HtmlTableCell();

                name.InnerText = r["Name"].ToString();
                //name.Attributes["class"] = "ListCell";
                username.InnerText = r["ADNAME"].ToString();
                //username.Attributes["class"] = "ListCell";
                str.InnerText = r["ASASTR"].ToString();
                //str.Attributes["class"] = "ListCell";
                slm.InnerText = r["ASASLM"].ToString();
                //slm.Attributes["class"] = "ListCell";
                uid.InnerText = r["UserID"].ToString();
                //uid.Attributes["class"] = "ListCell";
                but.InnerHtml = "<a href='AdminEditUser.aspx?A=E&U=" + r["UserID"].ToString() + "'>Edit</a>&nbsp&nbsp/&nbsp&nbsp<a href='AdminDeleteUser.aspx?U="+r["UserID"].ToString()+"'>Delete</a>";
                //but.Attributes["class"] = "ListCell";

                HtmlTableRow row = new HtmlTableRow();
                row.Controls.Add(name);
                row.Controls.Add(username);
                row.Controls.Add(str);
                row.Controls.Add(slm);
                row.Controls.Add(uid);
                row.Controls.Add(but);

                tblUsers.Controls.Add(row);
            }

        }

        public void providerQuery()
        {
            string query = "USE CRM; SELECT * FROM dbo.Providers";
            SqlCommand cmd = new SqlCommand(query, sql);
            SqlDataReader reader = cmd.ExecuteReader();
            DataTable d = new DataTable();
            d.Load(reader);
            reader.Close();

            HtmlTableRow ar = new HtmlTableRow();
            HtmlTableCell ar1 = new HtmlTableCell();
            ar1.InnerHtml = "<a class='btn btn-default btn-sm' href='AdminEditProvider.aspx?A=E&P=-1'>ADD NEW PROVIDER</a>";
            //ar1.Attributes["class"] = "ListCell";
            ar.Controls.Add(ar1);
            tblProviders.Controls.Add(ar);

            foreach (DataRow r in d.Rows)
            {
                HtmlTableCell name = new HtmlTableCell();
                HtmlTableCell id = new HtmlTableCell();
                HtmlTableCell but = new HtmlTableCell();
                name.InnerText = r["Provider"].ToString();
                //name.Attributes["class"] = "ListCell";
                id.InnerText = r["ID"].ToString();
                //id.Attributes["class"] = "ListCell";
                but.InnerHtml = "<a href='AdminEditProvider.aspx?A=E&P=" + r["ID"].ToString() + "'>Edit</a> / <a href='AdminEditProvider.aspx?A=D&P="+r["ID"].ToString() + "'>Delete</a>";
                //but.Attributes["class"] = "ListCell";
                HtmlTableRow row = new HtmlTableRow();
                row.Controls.Add(name);
                row.Controls.Add(id);
                row.Controls.Add(but);
                tblProviders.Controls.Add(row);

            }
        }

        public void tableQuery(string table)
        {
            string query = "";
            if (table.Equals("prospects")) query = "USE CRM; SELECT TOP 100 * FROM dbo.Prospects WHERE UPPER(Name) LIKE @Name";
            if (table.Equals("customers")) query = "USE CRM; SELECT TOP 100 * FROM dbo.Customers WHERE UPPER(Name) LIKE @Name";
            if (!tbContactName.Text.Equals("")) query += " AND ContactName LIKE @Contact";
            if (!tbContactPhone.Text.Equals("")) query += " AND ContactNumber LIKE @Phone";
            if (!tbOwner.Text.Equals("")) query += " AND Owner LIKE @Owner";
            query += " ORDER BY Name ASC";
            SqlCommand cmd = new SqlCommand(query, sql);

            SqlParameter name = new SqlParameter("@Name", "%" + tbProspectName.Text + "%");
            SqlParameter contact = new SqlParameter("@Contact", "%" + tbContactName.Text + "%");
            SqlParameter phone = new SqlParameter("@Phone", "%" + tbContactPhone.Text + "%");
            SqlParameter owner = new SqlParameter("@Owner", "%" + tbOwner.Text + "%");

            cmd.Parameters.Add(name);
            cmd.Parameters.Add(contact);
            cmd.Parameters.Add(phone);
            cmd.Parameters.Add(owner);

            SqlDataReader reader = cmd.ExecuteReader();
            DataTable d = null;
            if (table.Equals("prospects")) { prospects.Load(reader); reader.Close(); d = prospects; }
            if (table.Equals("customers")) { customers.Load(reader); reader.Close(); d = customers; }

            if (d.Rows.Count > 0)
            {
                foreach (DataRow r in d.Rows)
                {
                    HtmlTableRow tr = new HtmlTableRow();
                    HtmlTableCell tdName = new HtmlTableCell();
                    HtmlTableCell tdContact = new HtmlTableCell();
                    HtmlTableCell tdPhone = new HtmlTableCell();
                    HtmlTableCell tdOwner = new HtmlTableCell();
                    HtmlTableCell buttons = new HtmlTableCell();

                    if (table.Equals("prospects"))
                        tdName.InnerHtml = "<a href=\"ViewProspect.aspx?p=" + r["ID"].ToString();

                    if (table.Equals("customers"))
                        tdName.InnerHtml = "<a href=\"ViewCustomer.aspx?CUST=" + r["ASAID"].ToString();
                            
                    tdName.InnerHtml += "\">" + r["Name"].ToString() + "</a>";
                    //tdName.Attributes["class"] = "ListCell";

                    tdContact.InnerHtml = r["ContactName"].ToString();
                    //tdContact.Attributes["class"] = "ListCell";

                    tdPhone.InnerHtml = r["ContactNumber"].ToString();
                    //tdPhone.Attributes["class"] = "ListCell";

                    tdOwner.InnerHtml = Logic.getNAMEfromSPID(r["Owner"].ToString());
                    //tdOwner.Attributes["class"] = "ListCell";

                    buttons.InnerHtml = @"<a href='Admin.aspx?A=U&T=&I='>Unlock</a>&nbsp&nbsp/&nbsp&nbsp<a href='Admin.aspx?A=R&T=&I='>Reassign</a>&nbsp&nbsp/&nbsp&nbsp<a href='Admin.aspx?A=D&T=&I='>Delete</a>";
                    //buttons.Attributes["class"] = "ListCell";

                    if (table.Equals("prospects"))
                    {
                        buttons.InnerHtml = buttons.InnerHtml.Replace("&T=", "&T=P");
                        buttons.InnerHtml = buttons.InnerHtml.Replace("&I=", "&I="+r["ID"].ToString());
                    }
                    if (table.Equals("customers"))
                    {
                        buttons.InnerHtml = buttons.InnerHtml.Replace("&T=", "&T=C");
                        buttons.InnerHtml = buttons.InnerHtml.Replace("&I=", "&I="+r["ASAID"].ToString());
                    }

                    tr.Cells.Add(tdName);
                    tr.Cells.Add(tdContact);
                    tr.Cells.Add(tdPhone);
                    tr.Cells.Add(tdOwner);
                    tr.Cells.Add(buttons);

                    if (table.Equals("prospects")) tblProspects.Rows.Add(tr);
                    if (table.Equals("customers")) tblCustomers.Rows.Add(tr);
                }
            }

        }

        public void sqlQuery()
        {
            sqlConnect();

            tableQuery("prospects");
            tableQuery("customers");
            userQuery();
            providerQuery();
            sqlDisconnect();
        }

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

        protected void btnQuery_Click(object sender, EventArgs e)
        {
            sqlQuery();
        }

        protected void btnAdd_Click(object sender, EventArgs e)
        {
            Response.Redirect("~/AdminEditUser.aspx?U=-1");
        }

        [WebMethod]
        public static string SetQOTW(string q)
        {
            Logic.EnsureAdminTable();
            Logic.sqlConnect();

            string query = "DELETE FROM AdminSettings WHERE SettingName = 'QOTW'; INSERT INTO AdminSettings (SettingName, SettingValue) VALUES ('QOTW', @VALUE);";
            SqlCommand cmd = new SqlCommand(query, Logic.sql);
            cmd.Parameters.AddWithValue("@VALUE", q);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch { }
            /*
            query = "UPDATE AdminSettings SET SettingValue = @VALUE WHERE SettingName = 'QOTW'";
            cmd = new SqlCommand(query, Logic.sql);
            cmd.Parameters.AddWithValue("@VALUE", q);
            try
            {
                cmd.ExecuteNonQuery();
            }
            catch { }
             */

            return _Default.GetQOTW();
        }

        
    }
}