using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Odbc;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;

namespace CRMPortal
{
    static class Logic
    {
        static string admins = ConfigurationManager.AppSettings["siteAdmin"];

        public static OdbcConnection odbc = null;
        public static OdbcDataReader odbcReader = null;
        public static OdbcDataAdapter odbcAdapter = null;
        public static OdbcCommand odbcCmd = null;
        public static string odbcString = ConfigurationManager.AppSettings["DSN_Connection"];

        public static SqlConnection sql = null;
        public static ConnectionStringSettings sqlString = ConfigurationManager.ConnectionStrings["actionsql1"];

        public static SqlConnection getSql()
        {
            SqlConnection ns = new SqlConnection(sqlString.ConnectionString);
            return ns;
        }

        public static bool isAdmin(string s)
        {
            foreach (string a in admins.Split(';'))
                if (a.ToUpper().Equals(s.ToUpper())) return true;
            return false;
        }

        public static bool spExists(string adname)
        {
            sqlConnect();
            bool ret = false;
            string query = "USE CRM; SELECT TOP 1 * FROM dbo.Users WHERE UPPER(ADNAME)=UPPER(@ADNAME)";
            SqlCommand cmd = new SqlCommand(query, sql);
            cmd.Parameters.AddWithValue("@ADNAME", adname);
            SqlDataReader reader = cmd.ExecuteReader();
            cmd.Dispose();
            if (reader.HasRows) ret = true;
            else ret = false;
            reader.Close();
            reader.Dispose();
            return ret;
        }

        public static string getSpByCust(string c)
        {
            sqlConnect();
            string squery = "USE CRM; SELECT TOP 1 * FROM (SELECT ASAID, Owner FROM dbo.Customers WHERE ASAID=@CUST) cust INNER JOIN dbo.Users users ON users.UserID = cust.Owner";
            SqlCommand cmd = new SqlCommand(squery, sql);
            cmd.Parameters.Add(new SqlParameter("@CUST", c));
            SqlDataReader reader = cmd.ExecuteReader();
            DataTable sd = new DataTable();
            sd.Load(reader);
            reader.Close();
            //sqlDisconnect();
            cmd.Dispose();
            reader.Dispose();
            if (sd.Rows.Count > 0)
            {
                return sd.Rows[0]["Name"].ToString();
            }

            else
            {
                odbc = new OdbcConnection();
                odbc.ConnectionTimeout = 0;
                odbc.ConnectionString = odbcString;
                odbc.Open();

                string query = "SELECT * FROM SLMFIL INNER JOIN CUSMAS ON SLMFIL.SLSTO = CUSMAS.STORE AND SLMFIL.SLNUM = CUSMAS.SLM AND CUSMAS.CUST = '" + c + "'";
                OdbcDataAdapter odbcAdapter = new OdbcDataAdapter(query, odbc);
                DataTable d = new DataTable();
                odbcAdapter.Fill(d);
                odbc.Close();
                if (d.Rows.Count < 1) return "";

                sqlConnect();
                squery = "USE CRM; SELECT TOP 1 * FROM dbo.Users WHERE ASASTR=@STORE AND ASASLM=@NUM";
                cmd = new SqlCommand(squery, sql);
                cmd.Parameters.Add(new SqlParameter("@STORE", d.Rows[0]["SLSTO"]));
                cmd.Parameters.Add(new SqlParameter("@NUM", d.Rows[0]["SLNUM"]));
                reader = cmd.ExecuteReader();
                sd = new DataTable();
                sd.Load(reader);
                reader.Close();
                //sqlDisconnect();
                cmd.Dispose();
                reader.Dispose();

                if (sd.Rows.Count < 1) return d.Rows[0]["SLNAM"].ToString();
                else return sd.Rows[0]["Name"].ToString();
            }
            return "";
        }

        public static string getUIDfromSPID(string spid)
        {
            if (spid.Equals("-100")) return "None";
            sqlConnect();
            int spi;
            if (!int.TryParse(spid, out spi)) spi = 16;
            string query = "USE CRM; SELECT TOP 1 * FROM dbo.Users WHERE UserID=@SPID";
            SqlCommand cmd = new SqlCommand(query, sql);
            cmd.Parameters.Add(new SqlParameter("@SPID", spi));
            SqlDataReader reader = cmd.ExecuteReader();
            DataTable d = new DataTable();
            d.Load(reader);
            reader.Close();
            cmd.Dispose();
            reader.Dispose();
            if (d.Rows.Count < 1) return "None";
            else return d.Rows[0]["ASASLM"].ToString();
        }

        public static string getSPIDfromNAME(string n)
        {
            sqlConnect();
            string query = "USE CRM; SELECT TOP 1 * FROM dbo.Users WHERE Name=@name";
            SqlCommand cmd = new SqlCommand(query, sql);
            cmd.Parameters.Add(new SqlParameter("@name", n));
            SqlDataReader reader = cmd.ExecuteReader();
            DataTable d = new DataTable();
            d.Load(reader);
            reader.Close();
            //sqlDisconnect();
            cmd.Dispose();
            reader.Dispose();

            if (d.Rows.Count < 1)
            {
                sqlConnect();
                odbc = new OdbcConnection();
                odbc.ConnectionString = odbcString;
                odbc.Open();

                string oquery = "SELECT * FROM SLMFIL WHERE SLNAM='"+n+"'";
                OdbcDataAdapter odbcAdapter = new OdbcDataAdapter(oquery, odbc);
                DataTable od = new DataTable();
                odbcAdapter.Fill(od);
                odbc.Close();

                string iquery = "INSERT INTO dbo.Users (ASASLM, ASASTR, Name) VALUES (@asaslm, @asastr, @name)";
                cmd = new SqlCommand(iquery, sql);
                cmd.Parameters.Add(new SqlParameter("@asaslm", od.Rows[0]["SLNUM"].ToString()));
                cmd.Parameters.Add(new SqlParameter("@asastr", od.Rows[0]["SLSTO"].ToString()));
                cmd.Parameters.Add(new SqlParameter("@name", n));
                cmd.ExecuteNonQuery();
                cmd.Dispose();

                iquery = "USE CRM; SELECT TOP 1 * FROM dbo.Users WHERE ASASLM=@asaslm AND ASASTR=@asastr AND Name=@name";
                cmd = new SqlCommand(iquery, sql);
                cmd.Parameters.Add(new SqlParameter("@asaslm", od.Rows[0]["SLNUM"].ToString()));
                cmd.Parameters.Add(new SqlParameter("@asastr", od.Rows[0]["SLSTO"].ToString()));
                cmd.Parameters.Add(new SqlParameter("@name", n));
                reader = cmd.ExecuteReader();
                d = new DataTable();
                d.Load(reader);
                reader.Close();

                //sqlDisconnect();
                return d.Rows[0]["UserID"].ToString();
            }
            //sqlDisconnect();
            return d.Rows[0]["UserID"].ToString();
            
        }

        public static string getNAMEfromSLM(string slm)
        {
            odbc = new OdbcConnection();
            odbc.ConnectionString = odbcString;
            odbc.Open();

            string oquery = "SELECT SLNAM FROM SLMFIL WHERE SLNUM='"+slm+"'";
            OdbcCommand cmd = new OdbcCommand(oquery, odbc);
            DataTable od = new DataTable();
            OdbcDataReader r = cmd.ExecuteReader();
            string name = "";
            while (r.Read())
            {
                name = r.GetString(0);
            }
            odbc.Close();
            return name;
        }

        public static string getNAMEfromSPID(string spid)
        {
            if (spid.Equals("-100")) return "None";
            sqlConnect();
            int spi;
            if (!int.TryParse(spid, out spi)) spi = 16;
            string query = "USE CRM; SELECT TOP 1 * FROM dbo.Users WHERE UserID=@SPID";
            SqlCommand cmd = new SqlCommand(query, sql);
            cmd.Parameters.Add(new SqlParameter("@SPID", spi));
            SqlDataReader reader = cmd.ExecuteReader();
            DataTable d = new DataTable();
            d.Load(reader);
            reader.Close();
            cmd.Dispose();
            reader.Dispose();
            //sqlDisconnect();
            if (d.Rows.Count < 1) return "None";
            else return d.Rows[0]["Name"].ToString();
        }

        public static string getSPIDfromUID(string uid)
        {
            sqlConnect();
            uid = uid.ToUpper();
            string query = "USE CRM; SELECT TOP 1 * FROM dbo.Users WHERE UPPER(ADNAME)=UPPER(@UID)";
            SqlCommand cmd = new SqlCommand(query, sql);
            cmd.Parameters.Add(new SqlParameter("@UID", uid));
            SqlDataReader reader = cmd.ExecuteReader();
            DataTable d = new DataTable();
            d.Load(reader);
            reader.Close();
            cmd.Dispose();
            reader.Dispose();
            //sqlDisconnect();
            if (d.Rows.Count < 1) return "";
            else return d.Rows[0]["UserID"].ToString();
        }

        /*
        public static string getUIDfromSPID(string spid)
        {
            sqlConnect();
            string query = "USE CRM; SELECT TOP 1 * FROM dbo.Users WHERE UserID=@SPID";
            SqlCommand cmd = new SqlCommand(query, sql);
            cmd.Parameters.Add(new SqlParameter("@SPID", spid));
            SqlDataReader reader = cmd.ExecuteReader();
            DataTable d = new DataTable();
            d.Load(reader);
            reader.Close();
            cmd.Dispose();
            reader.Dispose();
            //sqlDisconnect();
            if (d.Rows.Count < 1) return "";
            else return d.Rows[0]["ADNAME"].ToString();
        }
        */

        public static bool uidExists(int uid)
        {
            sqlConnect();
            string query = "USE CRM; SELECT TOP 1 * FROM dbo.Users WHERE UserID=@UID";
            SqlCommand cmd = new SqlCommand(query, sql);
            cmd.Parameters.Add(new SqlParameter("@UID", uid));
            SqlDataReader reader = cmd.ExecuteReader();
            DataTable d = new DataTable();
            d.Load(reader);
            reader.Close();
            cmd.Dispose();
            reader.Dispose();
            //sqlDisconnect();
            if (d.Rows.Count < 1) return false;
            else return true;

        }

        public static string getNAMEfromUID(string uid)
        {
            sqlConnect();
            string query = "USE CRM; SELECT TOP 1 * FROM dbo.Users WHERE ADNAME=@UID";
            SqlCommand cmd = new SqlCommand(query, sql);
            cmd.Parameters.Add(new SqlParameter("@UID", uid));
            SqlDataReader reader = cmd.ExecuteReader();
            DataTable d = new DataTable();
            d.Load(reader);
            reader.Close();
            cmd.Dispose();
            reader.Dispose();
            //sqlDisconnect();
            return d.Rows[0]["Name"].ToString();
        }

        public static void convertASAtoCUST(string asaid)
        {
            odbc = new OdbcConnection();
            odbc.ConnectionString = odbcString;
            odbc.Open();

            string query = "SELECT * FROM CUSMAS WHERE CUST = '" + asaid + "'";
            OdbcDataAdapter odbcAdapter = new OdbcDataAdapter(query, odbc);
            DataTable d = new DataTable();
            odbcAdapter.Fill(d);
            odbc.Close();
            if (d.Rows.Count < 1) return;
            DataRow r = d.Rows[0];
            sqlConnect();
            string squery = "USE CRM; SELECT TOP 1 * FROM dbo.Customers WHERE ASAID=@ID";
            SqlCommand cmd = new SqlCommand(squery, sql);
            cmd.Parameters.Add(new SqlParameter("@ID", asaid));
            SqlDataReader reader = cmd.ExecuteReader();
            DataTable sd = new DataTable();
            sd.Load(reader);
            reader.Close();
            cmd.Dispose();
            reader.Dispose();
            if (sd.Rows.Count < 1)
            {
                sqlConnect();
                squery = "INSERT INTO dbo.Customers (ASAID, NAME, ADDRESS, CITY, STATE, ZIP, PHONE, OWNER) VALUES (@ID, @NAME, @ADDRESS, @CITY, @STATE, @ZIP, @PHONE, @OWNER)";
                cmd = new SqlCommand(squery, sql);
                cmd.Parameters.Add(new SqlParameter("@ID", asaid));
                cmd.Parameters.Add(new SqlParameter("@NAME", r["NAME"]));
                cmd.Parameters.Add(new SqlParameter("@ADDRESS", r["ADDR1"]));
                cmd.Parameters.Add(new SqlParameter("@CITY", r["CITY"]));
                cmd.Parameters.Add(new SqlParameter("@STATE", r["STATE"]));
                cmd.Parameters.Add(new SqlParameter("@ZIP", r["ZIP"]));
                cmd.Parameters.Add(new SqlParameter("@PHONE", r["PHONE"]));
                cmd.Parameters.Add(new SqlParameter("@OWNER", getSPIDfromNAME(getSpByCust(asaid))));
                cmd.ExecuteNonQuery();
            }
            //sqlDisconnect();
            return;
        }

        public static ArrayList GetEntryTypes()
        {
            ArrayList types = new ArrayList();
            sqlConnect();
            string query = "USE CRM; SELECT * FROM dbo.EntryType";
            SqlCommand cmd = new SqlCommand(query, sql);
            SqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
                types.Add(reader.GetString(0));
            reader.Dispose();
            //sqlDisconnect();
            return types;
        }

        public static void saveNote(string noteText, string noteOwner, string salesPerson, string noteType, bool isASA)
        {
            if (noteText.Equals(""))
            {
                return;
            }
            sqlConnect();
            string query = "INSERT INTO dbo.Notes (note, noteTime, "+(isASA ? "noteOwnerASAID" : "noteOwnerID") +", noteType) VALUES (@NOTE, @NOTETIME, @NOTEOWNER, @NOTETYPE)";
            SqlCommand sqlCommand = new SqlCommand(query, sql);
            sqlCommand.Parameters.AddWithValue("@NOTE", noteText);
            sqlCommand.Parameters.AddWithValue("@NOTETIME", DateTime.Now);
            sqlCommand.Parameters.AddWithValue("@NOTEOWNER", noteOwner);
            sqlCommand.Parameters.AddWithValue("@NOTETYPE", noteType);
            sqlCommand.ExecuteNonQuery();
            //sqlDisconnect();
        }

        public static void saveCall(string callNote, int UID, int CID, string contactName, string contactPhone, string timeUsed, DateTime callDate, bool isASA)
        {
            sqlConnect();
            string query = "INSERT INTO dbo.Calls (CustomerID, UserID, ASA, CallNote, CallDate, CallContactName, CallContactPhone, TimeUsed, TimeCreated) VALUES (@CUSTOMERID, @USERID, @ASA, @CALLNOTE, @CALLDATE, @CONTACTNAME, @CONTACTPHONE, @TIMEUSED, @TIMECREATED)";
            SqlCommand sqlCommand = new SqlCommand(query, sql);
            sqlCommand.Parameters.AddWithValue("@CUSTOMERID", CID);
            sqlCommand.Parameters.AddWithValue("@USERID", UID);
            sqlCommand.Parameters.AddWithValue("@ASA", isASA);
            sqlCommand.Parameters.AddWithValue("@CALLNOTE", callNote);
            sqlCommand.Parameters.AddWithValue("@CALLDATE", callDate);
            sqlCommand.Parameters.AddWithValue("@CONTACTNAME", contactName);
            sqlCommand.Parameters.AddWithValue("@CONTACTPHONE", contactPhone);
            sqlCommand.Parameters.AddWithValue("@TIMEUSED", timeUsed);
            sqlCommand.Parameters.AddWithValue("@TIMECREATED", DateTime.Now);
            sqlCommand.ExecuteNonQuery();
            //sqlDisconnect();
        }

        public static DateTime GetNextAppointment(string id, string asa)
        {
            using (SqlConnection s = getSql())
            {
                s.Open();
                string query = "USE CRM; SELECT TOP 1 AppointmentDate FROM Appointments WHERE CustomerID = @ID AND ASA = @ASA";
                DataTable results = new DataTable("Results");
                SqlCommand cmd = new SqlCommand(query, s);
                cmd.Parameters.AddWithValue("@ID", id);
                cmd.Parameters.AddWithValue("@ASA", asa);
                SqlDataReader r = cmd.ExecuteReader();
                results.Load(r);
                r.Close();
                r.Dispose();
                cmd.Dispose();

                DateTime when = new DateTime();
                if (results.Rows.Count == 0) return when;
                when = (DateTime)results.Rows[0]["AppointmentDate"];
                return when;
            }
        }

        public static DateTime GetNextRecurringCall(string id, string asa)
        {
            using (SqlConnection s = getSql())
            {
                s.Open();
                string query = "USE CRM; SELECT TOP 1 CallDate, Interval FROM Calls WHERE CustomerID = @ID AND ASA = @ASA AND Interval IS NOT NULL AND Interval <> '' ORDER BY CallDate DESC";
                DataTable results = new DataTable("Results");
                SqlCommand cmd = new SqlCommand(query, s);
                cmd.Parameters.AddWithValue("@ID", id);
                cmd.Parameters.AddWithValue("@ASA", asa);
                SqlDataReader r = cmd.ExecuteReader();
                results.Load(r);
                r.Close();
                r.Dispose();
                cmd.Dispose();

                query = "USE CRM; SELECT TOP 1 CallDate FROM CALLS WHERE CustomerID = @ID AND ASA = @ASA ORDER BY CallDate DESC;";
                cmd = new SqlCommand(query, s);
                cmd.Parameters.AddWithValue("@ID", id);
                cmd.Parameters.AddWithValue("@ASA", asa);
                r = cmd.ExecuteReader();
                DateTime lastCall = new DateTime();
                while (r.Read())
                {
                    lastCall = r.GetDateTime(0);
                }
                r.Close();
                r.Dispose();
                cmd.Dispose();

                s.Close();

                DateTime when = new DateTime();
                if (results.Rows.Count == 0) return when;

                when = (DateTime)results.Rows[0]["CallDate"];
                string interval = results.Rows[0]["Interval"].ToString().ToUpper();

                if (interval.Equals("WEEKLY"))
                {
                    when = lastCall.AddDays(7);
                }
                if (interval.Equals("BI-WEEKLY"))
                {
                    when = lastCall.AddDays(14);
                }
                if (interval.Equals("MONTHLY"))
                {
                    when = lastCall.AddMonths(1);
                }
                if (interval.Equals("BI-MONTHLY"))
                {
                    when = lastCall.AddMonths(2);
                }
                if (interval.Equals("SIX MONTHS"))
                {
                    when = lastCall.AddMonths(6);
                }
                if (interval.Equals("ANNUALLY"))
                {
                    when = lastCall.AddYears(1);
                }

                return when;
            }
        }

        public static DateTime GetNextScheduledCall(string id)
        {
            Logic.sqlConnect();

            string query = "USE CRM; SELECT TOP 1 CallDate, Interval FROM Calls WHERE CustomerID = @ID  AND Interval IS NOT NULL AND Interval <> '' ORDER BY CallDate DESC";            
            DataTable results = new DataTable("Results");  
            SqlCommand cmd = new SqlCommand(query, Logic.sql);            
            cmd.Parameters.AddWithValue("@ID", id);            
            SqlDataReader r = cmd.ExecuteReader();            
            results.Load(r);
            r.Close();
            string query2 = "USE CRM; SELECT TOP 1 AppointmentDate FROM Appointments WHERE CustomerID = @ID";
            DataTable results2 = new DataTable("Results2");
            SqlCommand cmd2 = new SqlCommand(query2, Logic.sql);
            cmd2.Parameters.AddWithValue("@ID", id);
            SqlDataReader r2 = cmd2.ExecuteReader();
            results2.Load(r2);

            r.Close();
            r2.Close();
            r.Dispose();
            r2.Dispose();
            cmd.Dispose();
            cmd2.Dispose();
            //Logic.sqlDisconnect();

            DateTime when = new DateTime();

            DateTime whenCall = results.Rows.Count>0?(DateTime)results.Rows[0]["CallDate"]:new DateTime();
            DateTime whenAppt = results2.Rows.Count>0?(DateTime)results2.Rows[0]["AppointmentDate"]:new DateTime();

            if (whenCall.Year > 2000 && whenAppt.Year > 2000)
            {
                if (whenCall < whenAppt) when = whenCall;
                else when = whenAppt;
            }

            if (whenCall.Year > 2000 && whenAppt.Year < 2000) when = whenCall;
            if (whenAppt.Year > 2000 && whenCall.Year < 2000) when = whenAppt;

            if (when.Year < 2000 || whenCall.Year < 2000) return when;

            string interval = results.Rows[0]["Interval"].ToString().ToUpper();

            if (interval.Equals("")) return when;

            DateTime now = DateTime.Now;

            if (interval.Equals("WEEKLY"))
            {
                while (when < new DateTime(now.Year, now.Month, now.Day))
                {
                    when = when.AddDays(7);
                }
                return when;
            }
            if (interval.Equals("BI-WEEKLY"))
            {
                while (when < new DateTime(now.Year, now.Month, now.Day))
                {
                    when = when.AddDays(14);
                }
                return when;
            }
            if (interval.Equals("MONTHLY"))
            {
                while (when < new DateTime(now.Year, now.Month, now.Day))
                {
                    when = when.AddMonths(1);
                }
                return when;
            }
            if (interval.Equals("BI-MONTHLY"))
            {
                while (when < new DateTime(now.Year, now.Month, now.Day))
                {
                    when = when.AddMonths(2);
                }
                return when;
            }
            if (interval.Equals("SIX MONTHS"))
            {
                while (when < new DateTime(now.Year, now.Month, now.Day))
                {
                    when = when.AddMonths(6);
                }
                return when;
            }
            if (interval.Equals("ANNUALLY"))
            {
                while (when < new DateTime(now.Year, now.Month, now.Day))
                {
                    when = when.AddYears(1);
                }
                return when;
            }

            return when;
        }



        public static void sqlConnect()
        {
            if (sql == null)
            {
                sql = new SqlConnection(sqlString.ConnectionString);
            }
            try
            {
                sql.Open();
            }
            catch { }
        }

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

        public static void EnsureAdminTable()
        {
            SqlConnection sql = new SqlConnection(ConfigurationManager.ConnectionStrings["actionsql1"].ConnectionString);
            sql.Open();
            string query = "USE CRM; IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'AdminSettings')) BEGIN CREATE TABLE [dbo].[AdminSettings]([SettingName] [varchar](50) NOT NULL,[SettingValue] [text] NULL,CONSTRAINT [PK_AdminSettings] PRIMARY KEY CLUSTERED ([SettingName] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] END";
            SqlCommand cmd = new SqlCommand(query, sql);
            cmd.ExecuteNonQuery();
            sql.Close();
        }
    }
}