using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Collections;
using System.Data.SqlClient;
using System.Data;
using AIAHTML5.Server.Constants;

namespace AIAHTML5.Server.Models
{
    public class DbModel
    {
        String dbConnectionString = System.Configuration.ConfigurationManager.AppSettings["AIADatabaseV5Context"];
        SqlConnection connection;
        SqlCommand command;

        private DataSet GetSQLData(string commandText, bool isSp)
        {
            connection = new SqlConnection(dbConnectionString);
            if (isSp)
            {
                command = new SqlCommand(commandText, connection);
                command.CommandType = CommandType.StoredProcedure;
            }
            else
            {
                command = new SqlCommand(commandText, connection);
            }
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = command;
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }

        private DataSet GetAllEditionForLicense(int licenseId)
        {
            connection = new SqlConnection(dbConnectionString);
            command = new SqlCommand();
            command.Connection = connection;
            command.CommandText = "GetAllEditionForLicense";
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.AddWithValue("@iLicenseId", licenseId).DbType = DbType.Int32;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = command;
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds;
        }

        /// <summary>
        /// This function use to get all account type
        /// </summary>
        /// <returns>Array of all account type List</returns>
        public ArrayList GetAllAccountTypes()
        {
            ArrayList arrAccountType = new ArrayList();
            Hashtable hasAccountType = null;
            hasAccountType = new Hashtable();
            hasAccountType.Add(AdminConstant.KEY_ID, 0);
            hasAccountType.Add(AdminConstant.KEY_TITLE, AdminConstant.TITLE_SELECT);
            arrAccountType.Add(hasAccountType);

            DataSet ds = GetSQLData("EC_GetAccountTypeList", true);
            DataTable dt = ds.Tables[0];

            foreach (DataRow drActType in dt.Rows)
            {
                hasAccountType = new Hashtable();
                hasAccountType.Add(AdminConstant.KEY_ID, drActType["Id"]);
                hasAccountType.Add(AdminConstant.KEY_TITLE, drActType["Title"]);
                arrAccountType.Add(hasAccountType);
            }
            return arrAccountType;
        }

        /// <summary>
        /// This function use to get all user type
        /// </summary>
        /// <returns>array of all User Type</returns>
        public ArrayList GetUserType()
        {
            ArrayList arrUserType = new ArrayList();
            Hashtable hasUserType = null;
            hasUserType = new Hashtable();
            hasUserType.Add(AdminConstant.KEY_ID, 0);
            hasUserType.Add(AdminConstant.KEY_TITLE, AdminConstant.TITLE_ALL);
            arrUserType.Add(hasUserType);

            DataSet ds = GetSQLData("", false); //Stored procedure not found
            DataTable dt = ds.Tables[0];

            foreach (DataRow drUserType in dt.Rows)
            {
                hasUserType = new Hashtable();
                hasUserType.Add(AdminConstant.KEY_ID, drUserType["Id"]);
                hasUserType.Add(AdminConstant.KEY_TITLE, drUserType["Title"]);
                arrUserType.Add(hasUserType);
            }
            return arrUserType;
        }

        /// <summary>
        /// This function used to get all countries list
        /// </summary>
        /// <returns>Array of all country List</returns>
        public ArrayList GetAllCountries()
        {
            ArrayList arrCountry = new ArrayList();
            Hashtable hasCountry = null;
            hasCountry = new Hashtable();
            hasCountry.Add(AdminConstant.KEY_ID, 0);
            hasCountry.Add(AdminConstant.KEY_TITLE, AdminConstant.TITLE_SELECT);
            arrCountry.Add(hasCountry);

            ////Disconnected -------------------
            //connection = new SqlConnection(dbConnsectionString);
            //command = new SqlCommand("EC_GetCountryList", connection);
            //command.CommandType = CommandType.StoredProcedure;
            //SqlDataAdapter da = new SqlDataAdapter();
            //da.SelectCommand = command;
            //DataSet ds = new DataSet();
            //da.Fill(ds);
            //DataTable dt = ds.Tables["Country"];

            //foreach (DataRow drCountry in dt.Rows)
            //{
            //    hasCountry = new Hashtable();
            //    hasCountry.Add("Id", drCountry["Id"]);
            //    hasCountry.Add("Title", drCountry["CountryName"]);
            //    arrCountry.Add(hasCountry);
            //}
            //return arrCountry;

            ////Connected
            //connection = new SqlConnection(dbConnsectionString);
            //command = new SqlCommand();
            //command.Connection = connection;
            //command.CommandType = CommandType.StoredProcedure;
            //command.CommandText = "EC_GetCountryList";
            //connection.Open();
            //reader = command.ExecuteReader();
            //while (reader.Read())
            //{
            //    hasCountry = new Hashtable();
            //    hasCountry.Add("Id", reader["Id"]);
            //    hasCountry.Add("Title", reader["CountryName"]);
            //    arrCountry.Add(hasCountry);
            //}
            //connection.Close();

            DataSet ds = GetSQLData("EC_GetCountryList", true);
            DataTable dt = ds.Tables[0];

            foreach (DataRow drCountry in dt.Rows)
            {
                hasCountry = new Hashtable();
                hasCountry.Add(AdminConstant.KEY_ID, drCountry["Id"]);
                hasCountry.Add(AdminConstant.KEY_TITLE, drCountry["CountryName"]);
                arrCountry.Add(hasCountry);
            }
            return arrCountry;
        }

        /// <summary>
        /// This function used to get all states (U.S.)
        /// </summary>
        /// <returns>Array of all state List</returns>
        public ArrayList GetAllUSStates()
        {
            ArrayList arrState = new ArrayList();
            Hashtable hasState = null;
            hasState = new Hashtable();
            hasState.Add(AdminConstant.KEY_ID, 0);
            hasState.Add(AdminConstant.KEY_TITLE, AdminConstant.TITLE_SELECT);
            arrState.Add(hasState);

            DataSet ds = GetSQLData("EC_GetStateList", true);
            DataTable dt = ds.Tables[0];

            foreach (DataRow drState in dt.Rows)
            {
                hasState = new Hashtable();
                hasState.Add(AdminConstant.KEY_ID, drState["Id"]);
                hasState.Add(AdminConstant.KEY_TITLE, drState["StateName"]);
                arrState.Add(hasState);
            }

            return arrState;
        }

        /// <summary>
        /// This function used to get all edition type with "All" at the 0 index
        /// </summary>
        /// <returns>array of all Edition Type</returns>
        public ArrayList GetAllEditionTypes()
        {
            ArrayList arrEditionType = new ArrayList();
            Hashtable hasEditionType = null;
            hasEditionType = new Hashtable();
            hasEditionType.Add(AdminConstant.KEY_ID, 0);
            hasEditionType.Add(AdminConstant.KEY_TITLE, AdminConstant.TITLE_ALL);
            arrEditionType.Add(hasEditionType);

            DataSet ds = GetSQLData("getEditionData", true);
            DataTable dt = ds.Tables[0];

            foreach (DataRow drEdition in dt.Rows)
            {
                hasEditionType = new Hashtable();
                hasEditionType.Add(AdminConstant.KEY_ID, drEdition["Id"]);
                hasEditionType.Add(AdminConstant.KEY_TITLE, drEdition["Title"]);
                arrEditionType.Add(hasEditionType);
            }
            return arrEditionType;
        }

        /// <summary>
        /// This function used to get all Secuirty Question type
        /// </summary>
        /// <returns>array of all security question</returns>
        public ArrayList GetSecuirtyQuestionsList()
        {
            ArrayList arrQuestionType = new ArrayList();
            Hashtable hasQuestionType = new Hashtable();
            hasQuestionType.Add(AdminConstant.KEY_ID, 0);
            hasQuestionType.Add(AdminConstant.KEY_TITLE, AdminConstant.TITLE_SELECT);
            arrQuestionType.Add(hasQuestionType);
            DataSet ds = GetSQLData("EC_GetSecurityQuestionList", true);
            DataTable dt = ds.Tables[0];

            foreach (DataRow drSQ in dt.Rows)
            {
                hasQuestionType = new Hashtable();
                hasQuestionType.Add(AdminConstant.KEY_ID, drSQ["Id"]);
                hasQuestionType.Add(AdminConstant.KEY_TITLE, drSQ["Title"]);
                arrQuestionType.Add(hasQuestionType);
            }
            return arrQuestionType;
        }

        /// <summary>
        /// This function use to get all License type
        /// </summary>
        /// <returns>array of License Types</returns>
        public ArrayList GetAllLicenseType()
        {
            //ArrayList arrLicenseType = new ArrayList();
            //Hashtable hasLicenseType = null;
            //hasLicenseType = new Hashtable();
            //hasLicenseType.Add(HelperConst.KEY_ID, 0);
            //hasLicenseType.Add(HelperConst.KEY_TITLE, HelperConst.TITLE_ALL);
            //arrLicenseType.Add(hasLicenseType);

            ArrayList arrLicenseType = new ArrayList();
            Hashtable hasLicenseType = null;
            hasLicenseType = new Hashtable();
            hasLicenseType.Add(AdminConstant.KEY_ID, 0);
            hasLicenseType.Add(AdminConstant.KEY_TITLE, AdminConstant.TITLE_ALL);
            arrLicenseType.Add(hasLicenseType);

            string sqlCommand = "SELECT lic.Id, lic.Title FROM LicenseType lic WHERE isActive=1";

            DataSet ds = GetSQLData(sqlCommand, false);
            DataTable dt = ds.Tables[0];

            foreach (DataRow drLicenseType in dt.Rows)
            {
                hasLicenseType = new Hashtable();
                hasLicenseType.Add(AdminConstant.KEY_ID, drLicenseType["Id"]);
                hasLicenseType.Add(AdminConstant.KEY_TITLE, drLicenseType["Title"]);
                arrLicenseType.Add(hasLicenseType);
            }
            return arrLicenseType;
        }

        public int UpdateUserProfile(int intUserID, string strEmailID, string strFirstName, string strLastName) //, int intLicenseId
        {
            int rowsUpdated = 0;
            int userId = 0;
            string fName = string.Empty;
            string lName = string.Empty;
            string email = string.Empty;

            //foreach (KeyValuePair<string, object> obj in userInfo)
            //{
            //    if (obj.Key == "")
            //        userId = Convert.ToInt32(obj.Value);
            //    if (obj.Key == "")
            //        fName = obj.Value.ToString();
            //    if (obj.Key == "")
            //        lName = obj.Value.ToString();
            //    if (obj.Key == "EMAIL")
            //        email = obj.Value.ToString();
            //}

                try
            {
                using (connection = new SqlConnection(dbConnectionString))
                {
                    using (command = connection.CreateCommand())
                    //new SqlCommand("UPDATE AIAUser SET FirstName = @FirstName, LastName = @LastName, EmailId = @EmailId, ModifierId = @ModifierId, ModifiedDate = GETDATE() WHERE(Id = @iUserId)", connection))
                    {
                        command.CommandText = "UPDATE AIAUser SET FirstName = @FirstName, LastName = @LastName, EmailId = @EmailId, ModifierId = @ModifierId, ModifiedDate = GETDATE() WHERE(Id = @iUserId)";
                        command.Parameters.AddWithValue("@FirstName", strFirstName);
                        command.Parameters.AddWithValue("@LastName", strLastName);
                        command.Parameters.AddWithValue("@EmailId", strEmailID);
                        command.Parameters.AddWithValue("@ModifierId", intUserID);
                        command.Parameters.AddWithValue("@iUserId", intUserID);

                        connection.Open();
                        rowsUpdated = command.ExecuteNonQuery(); //rows number of record got updated
                        connection.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
                rowsUpdated = 0;
            }
            return rowsUpdated;
        }

        public int UpdateUserProfile(int intUserID, string strEmailID, string strFirstName, string strLastName, int intLicenseId)
        {
            int rowsUpdated = 0;

            try
            {
                using (connection = new SqlConnection(dbConnectionString))
                {
                    connection.Open();
                    using (SqlCommand cmd =
                        new SqlCommand("UPDATE AIAUser SET FirstName = @FirstName, LastName = @LastName, EmailId = @EmailId, ModifierId = @ModifierId, ModifiedDate = GETDATE() WHERE(Id = @iUserId)", connection))
                    {
                        cmd.Parameters.AddWithValue("@FirstName", strFirstName);
                        cmd.Parameters.AddWithValue("@LastName", strLastName);
                        cmd.Parameters.AddWithValue("@EmailId", strEmailID);
                        cmd.Parameters.AddWithValue("@ModifierId", intUserID);
                        cmd.Parameters.AddWithValue("@iUserId", intUserID);

                        rowsUpdated = cmd.ExecuteNonQuery();

                        //rows number of record got updated
                    }
                }
            }
            catch (SqlException ex)
            {
                rowsUpdated = 0;
            }
            return rowsUpdated;
        }
    }
}