ADAM / AIAHTML5

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;
using AIAHTML5.Server.Models;
using Newtonsoft.Json;

namespace AIAHTML5.Server.Models
{
    public class DbModel
    {
        String dbConnectionString = System.Configuration.ConfigurationManager.AppSettings["AIADatabaseV5Context"];
        User user = new User();

        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();
                        connection.Close();
                        //rows number of record got updated
                    }
                }
            }
            catch (SqlException ex)
            {
                rowsUpdated = 0;
            }
            return rowsUpdated;
        }

        public string GetUserInfoByIDandLoginId(int intUserId, string sLoginId)
        {
            string result = string.Empty;
            try
            {

                using (connection = new SqlConnection(dbConnectionString))
                {
                    if (connection.State == ConnectionState.Closed)
                    {
                        connection.Open();
                        result = "Opened";
                    }
                    string query = "SELECT ISNULL(CreationDate,'') as CreationDate, CreatorId, ISNULL(DeactivationDate, '') as DeactivationDate, EmailId, FirstName, Id, IsActive, LastName, LoginId, ISNULL(ModifiedDate, '') as ModifiedDate , ModifierId, Password, SecurityAnswer, SecurityQuestionId, UserTypeId FROM AIAUser WHERE(Id = " + intUserId + ") AND (LoginId = '" + sLoginId + "')";
                    using (command = connection.CreateCommand())
                    {
                        command.CommandText = "SELECT ISNULL(CreationDate,'') as CreationDate, CreatorId, ISNULL(DeactivationDate, '') as DeactivationDate, EmailId, FirstName, Id, IsActive, LastName, LoginId, ISNULL(ModifiedDate, '') as ModifiedDate , ModifierId, Password, SecurityAnswer, SecurityQuestionId, UserTypeId FROM AIAUser WHERE(Id = @Id) AND(LoginId = @sLoginName)";
                        command.Parameters.AddWithValue("@Id", intUserId);
                        command.Parameters.AddWithValue("@sLoginName", sLoginId);
                        command.ExecuteNonQuery();
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (!reader.Read())
                                throw new InvalidOperationException("No records were returned.");
                            if (reader.Read())
                                throw new InvalidOperationException("Multiple records were returned.");
                            else
                            {
                                user.Id = Convert.ToInt32(reader["Id"]);
                                user.FirstName = Convert.ToString(reader["FirstName"]);
                                user.LastName = Convert.ToString(reader["LastName"]);
                                user.EmailId = Convert.ToString(reader["EmailId"]);
                                user.CreationDate = Convert.ToDateTime(reader["CreationDate"]);
                                user.CreatorId = Convert.ToInt32(reader["CreatorId"]);
                                user.DeactivationDate = Convert.ToDateTime(reader["DeactivationDate"]);
                                user.LoginId = Convert.ToString(reader["LoginId"]);
                                user.Password = Convert.ToString(reader["Password"]);
                                user.SecurityAnswer = Convert.ToString(reader["SecurityAnswer"]);
                                user.SecurityQuestionId = Convert.ToInt32(reader["SecurityQuestionId"]);
                                user.UserTypeId = Convert.ToInt32(reader["UserTypeId"]);
                                user.IsActive = Convert.ToBoolean(reader["IsActive"]);

                                //if (reader.HasRows)
                                //{
                                //    string json = JsonConvert.SerializeObject(reader, Formatting.Indented);

                                //    result = json;
                                //}

                                result = JsonConvert.SerializeObject(user);
                            }
                            reader.Close();
                        }

                    }
                    connection.Close();
                }
                //using (command =
                //        new SqlCommand("SELECT ISNULL(CreationDate,'') as CreationDate, CreatorId, ISNULL(DeactivationDate, '') as DeactivationDate, EmailId, FirstName, Id, IsActive, LastName, LoginId, ISNULL(ModifiedDate, '') as ModifiedDate , ModifierId, Password, SecurityAnswer, SecurityQuestionId, UserTypeId FROM AIAUser WHERE(Id = @Id) AND(LoginId = @sLoginName)", connection))
                //    {
                //        command.Parameters.AddWithValue("@Id", intUserId);
                //        command.Parameters.AddWithValue("@sLoginName", sLoginId);
                //        //connection.Open();
                //        command.ExecuteNonQuery();
                //        SqlDataReader userReader = command.ExecuteReader();

                //        using (userReader)
                //        {
                //            while (userReader.Read())
                //            {
                //                User user = new User();
                //                user.Id = Convert.ToInt32(userReader["Id"]);
                //                user.FirstName = userReader["FirstName"].ToString().Trim();
                //                user.LastName = userReader["LastName"].ToString().Trim();
                //                user.EmailId = userReader["EmailId"].ToString().Trim();
                //                user.LoginId = userReader["LoginId"].ToString().Trim();
                //                user.Password = userReader["Password"].ToString().Trim();
                //                user.SecurityQuestionId = Convert.ToInt32(userReader["SecurityQuestionId"]);
                //                user.SecurityAnswer = userReader["SecurityAnswer"].ToString().Trim();
                //                user.CreatorId = Convert.ToInt32(userReader["CreatorId"]);
                //                user.CreationDate = Convert.ToDateTime(userReader["CreationDate"]);
                //                user.DeactivationDate = Convert.ToDateTime(userReader["DeactivationDate"]);
                //                user.ModifierId = Convert.ToInt32(userReader["ModifierId"]);
                //                user.ModifiedDate = Convert.ToDateTime(userReader["ModifiedDate"]);
                //                user.UserTypeId = Convert.ToInt32(userReader["UserTypeId"]);
                //                user.IsActive = Convert.ToBoolean(userReader["IsActive"]);

                //                //var r = Serialize(userReader);
                //                string json = JsonConvert.SerializeObject(userReader, Formatting.Indented);

                //                result = json;
                //            }
                //        }
                //    }
                //}
            }
            catch (SqlException ex)
            { }
            return result;
        }

        public string GetUserInfoByIDandLoginId2(int intUserId, string sLoginId)
        {
            string res = string.Empty;
            string query = "SELECT ISNULL(CreationDate,'') as CreationDate, CreatorId, ISNULL(DeactivationDate, '') as DeactivationDate, EmailId, FirstName, Id, IsActive, LastName, LoginId, ISNULL(ModifiedDate, '') as ModifiedDate , ModifierId, Password, SecurityAnswer, SecurityQuestionId, UserTypeId FROM AIAUser WHERE(Id = " + intUserId + ") AND (LoginId = '" + sLoginId + "')";
            connection = new SqlConnection(dbConnectionString);
            command = new SqlCommand(query, connection);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = command;
            DataTable dt = new DataTable();
            da.Fill(dt);
            connection.Close();
            string JSONresult;
            JSONresult = JsonConvert.SerializeObject(dt);
            return JSONresult;
        }

        public int UpdateUserID(int iUserId, string sLoginId)
        {
            int rowsUpdated = 0;

            try
            {
                using (connection = new SqlConnection(dbConnectionString))
                {                    
                    using (command = connection.CreateCommand())
                    {
                        command.CommandText = "UPDATE AIAUser SET LoginId= @sNewLoginId, ModifierId=@iModifierUserId WHERE (id = @iUserId)";
                        command.Parameters.AddWithValue("@iUserId", iUserId);
                        command.Parameters.AddWithValue("@iModifierUserId", iUserId);
                        command.Parameters.AddWithValue("@sNewLoginId", sLoginId);
                        connection.Open();
                        rowsUpdated = command.ExecuteNonQuery();    //rows number of record got updated
                        connection.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
                rowsUpdated = 0;
            }
            return rowsUpdated;
        }

        public string GetUserDetailsByLoginIDandPassword(string sLoginId, string sPassword)
        {
            string uDetail = string.Empty;

            try
            {
                String query = "SELECT CreationDate, CreatorId, DeactivationDate, EmailId, FirstName, Id, IsActive, LastName, LoginId, ModifiedDate, ModifierId, Password, SecurityAnswer, SecurityQuestionId, UserTypeId FROM AIAUser WHERE(LoginId = '" + sLoginId + "') AND(Password = '" + sPassword + "')";
                //connection = new SqlConnection(dbConnectionString);
                //command = new SqlCommand(query, connection);
                //connection.Open();
                //SqlDataReader reader = command.ExecuteReader();
                //while (reader.Read())
                //{
                //    if(reader.HasRows)
                //        uDetail = JsonConvert.SerializeObject(reader, Formatting.Indented);
                //}

                using (connection = new SqlConnection(dbConnectionString))
                {
                    connection.Open();

                    //string query = "SELECT ISNULL(CreationDate,'') as CreationDate, CreatorId, ISNULL(DeactivationDate, '') as DeactivationDate, EmailId, FirstName, Id, IsActive, LastName, LoginId, ISNULL(ModifiedDate, '') as ModifiedDate , ModifierId, Password, SecurityAnswer, SecurityQuestionId, UserTypeId FROM AIAUser WHERE(Id = " + intUserId + ") AND (LoginId = '" + sLoginId + "')";
                    //using (command = connection.CreateCommand())
                    using (command = connection.CreateCommand())
                    {
                        command.CommandText = "SELECT CreationDate, CreatorId, DeactivationDate, EmailId, FirstName, Id, IsActive, LastName, LoginId, ModifiedDate, ModifierId, Password, SecurityAnswer, SecurityQuestionId, UserTypeId FROM AIAUser WHERE (LoginId = @iLoginId) AND (Password = @sPassword)";
                        command.Parameters.AddWithValue("@iLoginId", sLoginId);
                        command.Parameters.AddWithValue("@sPassword", sPassword);
                        command.CommandType = CommandType.Text;
                        SqlDataReader reader = command.ExecuteReader();
                        if (reader.HasRows)
                        {
                            string json = JsonConvert.SerializeObject(reader, Formatting.Indented);

                            uDetail = json;
                        }
                    }
                }
                connection.Close();                
            }

            catch (SqlException ex)
            {

            }

            return uDetail;
        }

        public int UpdateUserPassword(int iUserId, string sPassword )
        {
            int rowsUpdated = 0;

            try
            {
                using (connection = new SqlConnection(dbConnectionString))
                {
                    using (command = connection.CreateCommand())
                    {
                        command.CommandText = "UPDATE AIAUser SET Password = @sPassword, ModifierId = @iModifierUserId WHERE(id = @iUserId)";
                        command.Parameters.AddWithValue("@iUserId", iUserId);
                        command.Parameters.AddWithValue("@iModifierUserId", iUserId);
                        command.Parameters.AddWithValue("@sPassword", sPassword);
                        connection.Open();
                        rowsUpdated = command.ExecuteNonQuery();    //rows number of record got updated
                        connection.Close();
                    }
                }
            }
            catch (SqlException ex)
            {
                rowsUpdated = 0;
            }
            return rowsUpdated;
        }
    }
}