using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using AIAHTML5.API.Properties;
using AIAHTML5.API.Constants;
using log4net;
using System.Data;
using System.Data.SqlClient;
using Newtonsoft.Json;
using System.Collections;

namespace AIAHTML5.API.Models
{
    public class DBModel
    {
        static string dbConnectionString = System.Configuration.ConfigurationManager.AppSettings["AIADatabaseV5Context"];


        private static readonly ILog logger = LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);


        public DBModel() { }


        protected static DataSet GetDataFromStoredProcedure(string commandText)
        {

            logger.Debug(" Inside GetSQLData for command text = " + commandText);
            DataSet ds = null;
            SqlConnection conn = new SqlConnection(dbConnectionString);
            SqlCommand cmd = new SqlCommand(commandText, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            ds = new DataSet();
            da.Fill(ds);

            return ds;
        }

        internal ArrayList GetAllModules()
        {
            logger.Debug(" Inside GetAllModules");

            ArrayList arrUserModules = new ArrayList();

            Hashtable userModuleHash = new Hashtable();

            string sp = DBConstants.GET_ALL_MODULES;

            DataSet ds = DBModel.GetDataFromStoredProcedure(sp);

            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];

                foreach (DataRow drModule in dt.Rows)
                {
                    userModuleHash = new Hashtable();
                    userModuleHash.Add(AIAConstants.KEY_ID, drModule["Id"]);
                    userModuleHash.Add(AIAConstants.KEY_NAME, drModule["Name"]);
                    userModuleHash.Add(AIAConstants.KEY_SLUG, drModule["Slug"]);
                    arrUserModules.Add(userModuleHash);
                }
            }

            return arrUserModules;
        }

        internal static User GetUserDetailsByLoginId(string loginId)
        {
            logger.Debug(" Inside GetUserDetailsByLoginId for LoginId = " + loginId);

            User objUser = null;
            DBModel objModel = new DBModel();


            SqlConnection conn = new SqlConnection(dbConnectionString);
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter da = new SqlDataAdapter();
            SqlParameter param;
            DataSet ds = new DataSet();

            cmd.Connection = conn;
            cmd.CommandText = DBConstants.GET_USER_DELAILS_BY_LOGIN_ID;
            cmd.CommandType = CommandType.StoredProcedure;

            param = new SqlParameter("@sLoginId", loginId);
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.String;
            cmd.Parameters.Add(param);

            da.SelectCommand = cmd;
            DataTable dt = new DataTable();
            da.Fill(dt);


            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    int tempVal;
                    DateTime date;
                    objUser = new User();

                    objUser.Id = Convert.ToInt32(dr["Id"]);
                    objUser.FirstName = dr["FirstName"].ToString();
                    objUser.LastName = dr["LastName"].ToString();
                    objUser.EmailId = dr["EmailId"].ToString();
                    objUser.LoginId = dr["LoginId"].ToString();
                    objUser.Password = dr["Password"].ToString();
                    objUser.SecurityQuestionId = Int32.TryParse(dr["SecurityQuestionId"].ToString(), out tempVal) ? tempVal : (int?)null;
                    objUser.SecurityAnswer = dr["SecurityAnswer"].ToString(); ;
                    objUser.CreatorId = Int32.TryParse(dr["CreatorId"].ToString(), out tempVal) ? tempVal : (int?)null;
                    objUser.CreationDate = Convert.ToDateTime(dr["CreationDate"]);
                    objUser.DeactivationDate = DateTime.TryParse(dr["DeactivationDate"].ToString(), out date) ? date : (DateTime?)null;
                    objUser.ModifierId = Int32.TryParse(dr["ModifierId"].ToString(), out tempVal) ? tempVal : (int?)null;
                    objUser.ModifiedDate = DateTime.TryParse(dr["ModifiedDate"].ToString(), out date) ? date : (DateTime?)null;
                    objUser.UserTypeId = Convert.ToInt32(dr["UserTypeId"]);
                    objUser.UserType = objModel.GetUserTypeStringById(Convert.ToInt32(dr["UserTypeId"]));
                    objUser.IsActive = Convert.ToBoolean(dr["IsActive"]);
                }
            }


            return objUser;
        }

        internal Hashtable GetLicenseDetailByUserId(int userId)
        {
            logger.Debug(" Inside GetUserLicenseDetailByUserId for UserId = " + userId);

            Hashtable hash = new Hashtable();

            SqlConnection conn = new SqlConnection(dbConnectionString);
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter adapter;
            SqlParameter param;
            DataSet ds = new DataSet();

            cmd.Connection = conn;
            cmd.CommandText = DBConstants.GET_LICENSE_DETAILS_BY_USER_ID;
            cmd.CommandType = CommandType.StoredProcedure;

            param = new SqlParameter("@iUserId", userId);
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.Int32;
            cmd.Parameters.Add(param);

            adapter = new SqlDataAdapter(cmd);
            adapter.Fill(ds);

            if (ds != null && ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    hash.Add(AIAConstants.LICENSE_KEY_ID, ds.Tables[0].Rows[0][0]);
                    hash.Add(AIAConstants.EDITION_KEY_ID, ds.Tables[0].Rows[0][1]);
                }
            }

            return hash;
        }

        internal ArrayList GetUserModulesByLicenseId(int licenseId)
        {
            logger.Debug(" Inside GetUserModulesByLicenseId for LicenseId = " + licenseId);

            ArrayList userModulelist = new ArrayList();

            Hashtable modulesHash;
            DataSet ds = new DataSet();

            SqlConnection conn = new SqlConnection(dbConnectionString);
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter adapter;
            SqlParameter param;

            cmd.Connection = conn;
            cmd.CommandText = DBConstants.GET_USER_MODULES_BY_LICENSE_ID;
            cmd.CommandType = CommandType.StoredProcedure;

            param = new SqlParameter("@iLicenseId", licenseId);
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.Int32;
            cmd.Parameters.Add(param);

            adapter = new SqlDataAdapter(cmd);
            adapter.Fill(ds);


            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        modulesHash = new Hashtable();
                        modulesHash.Add(AIAConstants.KEY_ID, dr["Id"]);
                        modulesHash.Add(AIAConstants.KEY_NAME, dr["Title"]);
                        modulesHash.Add(AIAConstants.KEY_SLUG, dr["Slug"]);
                        userModulelist.Add(modulesHash);
                    }
                }
            }


            return userModulelist;
        }

        protected string GetUserTypeStringById(int userTypeId)
        {
            string userType = string.Empty;

            switch (userTypeId)
            {
                case (int)UserType.SUPER_ADMIN:
                    userType = User.SUPER_ADMIN;
                    break;
                case (int)UserType.GENERAL_ADMIN:
                    userType = User.GENERAL_ADMIN;
                    break;
                case (int)UserType.DISTRICT_ADMIN:
                    userType = User.DISTRICT_ADMIN;
                    break;
                case (int)UserType.CLIENT_ADMIN:
                    userType = User.CLIENT_ADMIN;
                    break;
                case (int)UserType.SINGLE_USER:
                    userType = User.SINGLE_USER;
                    break;
                case (int)UserType.CONCURRENT_USER:
                    userType = User.CONCURRENT_USER;
                    break;
                case (int)UserType.RESELLER:
                    userType = User.RESELLER;
                    break;
                case (int)UserType.TEST_ACCOUNT:
                    userType = User.TEST_ACCOUNT;
                    break;
                case (int)UserType.SITE_USER:
                    userType = User.SITE_USER;
                    break;
            }
            return userType;
        }

        internal static User GetUserDetailsByEmailId(string emailId)
        {
            logger.Debug(" Inside GetUserDetailsByEmailId for emailId = " + emailId);

            User objUser = null;
            DBModel objModel = new DBModel();

            SqlConnection conn = new SqlConnection(dbConnectionString);
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter adapter;
            SqlParameter param;
            DataSet ds = new DataSet();

            cmd.Connection = conn;
            cmd.CommandText = DBConstants.GET_USER_DETAILS_BY_EMAILID;
            cmd.CommandType = CommandType.StoredProcedure;

            param = new SqlParameter("@sEmailId", emailId);
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.String;
            cmd.Parameters.Add(param);

            adapter = new SqlDataAdapter(cmd);
            adapter.Fill(ds);


            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        int tempVal;
                        DateTime date;
                        objUser = new User();

                        objUser.Id = Convert.ToInt32(dr["Id"]);
                        objUser.FirstName = dr["FirstName"].ToString();
                        objUser.LastName = dr["LastName"].ToString();
                        objUser.EmailId = dr["EmailId"].ToString();
                        objUser.LoginId = dr["LoginId"].ToString();
                        objUser.Password = dr["Password"].ToString();
                        objUser.SecurityQuestionId = Int32.TryParse(dr["SecurityQuestionId"].ToString(), out tempVal) ? tempVal : (int?)null;
                        objUser.SecurityAnswer = dr["SecurityAnswer"].ToString(); ;
                        objUser.CreatorId = Int32.TryParse(dr["CreatorId"].ToString(), out tempVal) ? tempVal : (int?)null;
                        objUser.CreationDate = Convert.ToDateTime(dr["CreationDate"]);
                        objUser.DeactivationDate = DateTime.TryParse(dr["DeactivationDate"].ToString(), out date) ? date : (DateTime?)null;
                        objUser.ModifierId = Int32.TryParse(dr["ModifierId"].ToString(), out tempVal) ? tempVal : (int?)null;
                        objUser.ModifiedDate = DateTime.TryParse(dr["ModifiedDate"].ToString(), out date) ? date : (DateTime?)null;
                        objUser.UserTypeId = Convert.ToInt32(dr["UserTypeId"]);
                        objUser.UserType = objModel.GetUserTypeStringById(Convert.ToInt32(dr["UserTypeId"]));
                        objUser.IsActive = Convert.ToBoolean(dr["IsActive"]);
                    }
                }
            }

            return objUser;
        }

        internal static int UpdateUserPassword(dynamic userInfo, string loginId, string emailId)
        {
            logger.Debug(" Inside UpdateUserPassword for LoginId: " + loginId + ", EmailId: " + emailId);

            int result = 0;

            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(dbConnectionString);
                SqlCommand cmd = new SqlCommand();
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = DBConstants.UPDATE_USER_PASSWORD;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@sLoginId", loginId);
                cmd.Parameters.AddWithValue("@sEmailId", emailId);
                cmd.Parameters.AddWithValue("@sNewPassword", userInfo["newPassword"].ToString());
                result = cmd.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {
                conn.Close();
                logger.Fatal("Exception in UpdateUserPassword for LoginId: " + loginId + ", EmailId: " + emailId + ", Exception= " + ex.Message + ", STACKTRACE= " + ex.StackTrace);
                throw;
            }
            finally
            {
                conn.Dispose();
            }
            return result;
        }

        internal LicenseSubscriptionDetails GetLicenseSubscriptionDetailsByLicenseId(int licenseId)
        {
            logger.Debug(" Inside GetLicenseSubscriptionDetailsByLicenseId for LicenseId = " + licenseId);

            LicenseSubscriptionDetails licenseSubscriptionDetails = null;

            SqlConnection conn = new SqlConnection(dbConnectionString);
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter adapter;
            SqlParameter param;
            DataSet ds = new DataSet();

            cmd.Connection = conn;
            cmd.CommandText = DBConstants.GET_SUBSCRIPTION_DETAILS_BY_LICENSE_ID;
            cmd.CommandType = CommandType.StoredProcedure;

            param = new SqlParameter("@iLicenseId", licenseId);
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.Int32;
            cmd.Parameters.Add(param);

            adapter = new SqlDataAdapter(cmd);
            adapter.Fill(ds);

            if (ds != null && ds.Tables.Count > 0)
            {
                licenseSubscriptionDetails = new LicenseSubscriptionDetails();
                DataTable dt = ds.Tables[0];

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        int tempVal;
                        DateTime date;
                        licenseSubscriptionDetails.Id = Convert.ToInt32(dr["Id"]);
                        licenseSubscriptionDetails.LicenseId = Convert.ToInt32(dr["LicenseId"]);
                        licenseSubscriptionDetails.SubscriptionPlanId = Int32.TryParse(dr["SubscriptionPlanId"].ToString(), out tempVal) ? tempVal : (int?)null;
                        licenseSubscriptionDetails.SubscriptionValidFrom = DateTime.TryParse(dr["SubscriptionValidFrom"].ToString(), out date) ? date : (DateTime?)null;
                        licenseSubscriptionDetails.SubscriptionValidThrough = DateTime.TryParse(dr["SubscriptionValidThrough"].ToString(), out date) ? date : (DateTime?)null;
                        licenseSubscriptionDetails.RenewalDate = DateTime.TryParse(dr["RenewalDate"].ToString(), out date) ? date : (DateTime?)null;
                        licenseSubscriptionDetails.PaymentMode = dr["PaymentMode"].ToString();
                        licenseSubscriptionDetails.TotalAmount = Convert.ToDouble(dr["TotalAmount"]);
                        licenseSubscriptionDetails.AmountPaid = Convert.ToDouble(dr["AmountPaid"]);
                        licenseSubscriptionDetails.AmountPending = Convert.ToDouble(dr["AmountPending"]);
                        licenseSubscriptionDetails.NoOfImages = Convert.ToInt32(dr["NoofImages"]);
                    }
                }
            }



            return licenseSubscriptionDetails;
        }

        internal License GetLicenseDetailsByLicenseId(int licenseId)
        {
            logger.Debug(" inside GetLicenseDetailsByLicenseId for LicenseId = " + licenseId);

            License license = null;

            SqlConnection conn = new SqlConnection(dbConnectionString);
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter adapter;
            SqlParameter param;
            DataSet ds = new DataSet();

            cmd.Connection = conn;
            cmd.CommandText = DBConstants.GET_LICENSE_DETAILS_BY_ID;
            cmd.CommandType = CommandType.StoredProcedure;

            param = new SqlParameter("@Id", licenseId);
            param.Direction = ParameterDirection.Input;
            param.DbType = DbType.Int32;
            cmd.Parameters.Add(param);

            adapter = new SqlDataAdapter(cmd);
            adapter.Fill(ds);

            if (ds != null && ds.Tables.Count > 0)
            {

                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    license = new License();

                    foreach (DataRow dr in dt.Rows)
                    {
                        DateTime date;
                        license.Id = Convert.ToInt32(dr["Id"]);
                        license.AccountNumber = dr["AccountNumber"].ToString();
                        license.LicenseeFirstName = dr["LicenseeFirstName"].ToString();
                        license.LicenseeLastName = dr["LicenseeLastName"].ToString();
                        license.LicenseTypeId = Convert.ToInt32(dr["LicenseTypeId"]);
                        license.InstitutionName = dr["InstitutionName"].ToString();
                        license.Address1 = dr["Address1"].ToString();
                        license.Address2 = dr["Address2"].ToString();
                        license.CountryId = Convert.ToInt32(dr["CountryId"]);
                        license.StateId = Convert.ToInt32(dr["StateId"]);
                        license.City = dr["City"].ToString();
                        license.Zip = dr["Zip"].ToString();
                        license.Phone = dr["Phone"].ToString();
                        license.EmailId = dr["EmailId"].ToString();
                        license.TotalLogins = Convert.ToInt32(dr["TotalLogins"]);
                        license.AccountTypeId = Convert.ToInt32(dr["AccountTypeId"]);
                        license.IsActive = Convert.ToBoolean(dr["IsActive"]);
                        license.IsDistrictSiteLicense = Convert.ToBoolean(dr["IsDistrictSiteLicense"]);
                        license.CreationDate = Convert.ToDateTime(dr["CreationDate"]);
                        license.ModifiedDate = DateTime.TryParse(dr["ModifiedDate"].ToString(), out date) ? date : (DateTime?)null;
                        license.NoOfRenewals = Convert.ToInt32(dr["NoOfRenewals"]);
                        license.IsTermAccepted = Convert.ToBoolean(dr["IsTermsAccepted"]);
                        license.ProductId = dr["ProductId"].ToString();
                    }
                }
            }



            return license;
        }

        internal static int UpdateLicenseTermStatus(string accountNumber)
        {
            logger.Debug(" inside UpdateLicenseTermStatus for AccountNumber = " + accountNumber);

            int result = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(dbConnectionString);
                SqlCommand cmd = new SqlCommand();
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = DBConstants.UPDATE_LICENSE_TERM_STATUS;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@sAccountNumber", accountNumber);
                result = cmd.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {
                logger.Fatal("Exception in UpdateLicenseTermStatus for AccountNumber =" + accountNumber + ", Exception= " + ex.Message + ", STACKTRACE= " + ex.StackTrace);
                throw;
            }
            finally
            {
                conn.Dispose();
            }
            return result;
        }

        internal static ArrayList GetTermsAndConditions()
        {
            logger.Debug(" inside GetTermsAndConditions");

            ArrayList arrTermsAndConditions = new ArrayList();


            Hashtable contentHash = null;
            string str = string.Empty;
            string spName = DBConstants.GET_TERMS_AND_CONDITIONS;
            DataSet ds = DBModel.GetDataFromStoredProcedure(spName);

            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        contentHash = new Hashtable();
                        contentHash.Add(AIAConstants.KEY_TITLE, dr["Title"]);
                        contentHash.Add(AIAConstants.KEY_CONTENT, dr["Content"]);
                        arrTermsAndConditions.Add(contentHash);
                    }
                }
            }


            return arrTermsAndConditions;
        }

        internal int InsertLoginDetails(int userId)
        {
            logger.Debug(" inside InsertLoginDetails for UserId= " + userId);

            int result = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(dbConnectionString);
                SqlCommand cmd = new SqlCommand();
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = DBConstants.INSERT_LOGIN_DETAIL;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@iUserId", userId);
                result = cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                logger.Fatal("Exception in InsertLoginDetails for UserId= " + userId + ", Exception= " + ex.Message + ", STACKTRACE=" + ex.StackTrace);
                throw;
            }
            finally
            {
                conn.Dispose();
            }

            return result;
        }

        internal int InsertIncorrectLoginAttempts(int userId)
        {
            logger.Debug(" inside InsertIncorrectLoginAttempts for UserId= " + userId);

            int result = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(dbConnectionString);
                SqlCommand cmd = new SqlCommand();
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = DBConstants.INSERT_INCORRECT_LOGIN_ATTEMPTS;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@iUserId", userId);
                result = cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                logger.Fatal("Exception in InsertIncorrectLoginAttempts for UserId= " + userId + ", Exception= " + ex.Message + ", STACKTRACE= " + ex.StackTrace);
                throw;
            }
            finally
            {
                conn.Dispose();
            }
            return result;
        }

        internal int GetIncorrectLoginAttempts(int userId)
        {
            logger.Debug(" inside GetIncorrectLoginAttempts for UserId = " + userId);

            int count = 0;

            SqlConnection conn = new SqlConnection(dbConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = DBConstants.GET_INCORRECT_LOGIN_ATTEMPTS;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@iUserId", userId);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);

            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        foreach (DataColumn dc in dt.Columns)
                        {
                            count = Convert.ToInt32(dr[dc]);
                        }
                    }
                }
            }


            return count;
        }

        internal int UpdateIncorrectLoginAttempts(int userId)
        {
            logger.Debug(" inside UpdateIncorrectLoginAttempts for UserId= " + userId);

            int result = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(dbConnectionString);
                SqlCommand cmd = new SqlCommand();
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = DBConstants.UPDATE_INCORRECT_LOGIN_ATTEMPTS;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@iUserId", userId);
                result = cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                logger.Fatal("Exception in UpdateIncorrectLoginAttempts for UserId= " + userId + ", Exception= " + ex.Message + ", STACKTRACE=" + ex.StackTrace);
                throw;
            }
            finally
            {
                conn.Dispose();
            }
            return result;
        }

        public int DeleteIncorrectLoginAttempts(int userId)
        {
            logger.Debug(" inside DeleteIncorrectLoginAttempts for UserId= " + userId);

            int result = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(dbConnectionString);
                SqlCommand cmd = new SqlCommand();
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = DBConstants.DELETE_INCORRECT_LOGIN_ATTEMPTS;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@iUserId", userId);
                result = cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                logger.Fatal("Exception in DeleteIncorrectLoginAttempts for UserId= " + userId + ", Exception= " + ex.Message + ", STACKTRACE= " + ex.StackTrace);
                throw;
            }
            finally
            {
                conn.Dispose();
            }
            return result;
        }

        protected ArrayList GetLoginFailureCauses()
        {
            logger.Debug(" Inside GetLoginFailureCauses");

            ArrayList failureCauseList = new ArrayList();

            Hashtable fcHash = null;

            string sp = DBConstants.GET_ALL_LOGIN_FAILURE_CAUSES;

            DataSet ds = DBModel.GetDataFromStoredProcedure(sp);

            if (ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];

                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow drFailureCause in dt.Rows)
                    {
                        fcHash = new Hashtable();
                        fcHash.Add(AIAConstants.KEY_ID, drFailureCause["Id"]);
                        fcHash.Add(AIAConstants.KEY_DESCRIPTION, drFailureCause["Description"]);
                        failureCauseList.Add(fcHash);
                    }
                }
            }

            return failureCauseList;
        }

        internal int InsertUserLoginLog(string accountNumber, Int16 failureId, string referalUrl, string edition, string httpReferer)
        {
            logger.Debug(" inside InsertUserLoginLog for AccountNumber= " + accountNumber);

            int result = 0;
            SqlConnection conn = null;
            try
            {
                conn = new SqlConnection(dbConnectionString);
                SqlCommand cmd = new SqlCommand();
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = DBConstants.INSERT_LOGIN_ERROR_LOG;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@nvAccountNumber", accountNumber);
                cmd.Parameters.AddWithValue("@dtLogDate", DateTime.Now);
                cmd.Parameters.AddWithValue("@tiFailureId", failureId);
                cmd.Parameters.AddWithValue("@nvReferalUrl", referalUrl);
                cmd.Parameters.AddWithValue("@nvEdition", edition);
                cmd.Parameters.AddWithValue("@nvHttpReferer", httpReferer);
                result = cmd.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                logger.Fatal("Exception in InsertUserLoginLog for AccountNumber= " + accountNumber + ", Exception= " + ex.Message + ", STACKTRACE= " + ex.StackTrace);
                throw;
            }
            finally
            {
                conn.Dispose();
            }
            return result;
        }

        internal BlockedUser GetUserBlockedStatusByUserId(int userId)
        {
            logger.Debug(" inside GetUserBlockedStatusByUserId for UserId= " + userId);

            BlockedUser blockedUser = null;
            DataTable dt = null;

            SqlConnection conn = new SqlConnection(dbConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = DBConstants.GET_BLOCKED_USER_BY_USER_ID;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@userId", userId);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            dt = new DataTable();
            da.Fill(dt);

            if (dt != null && dt.Rows.Count > 0)
            {
                blockedUser = new BlockedUser();
                foreach (DataRow dr in dt.Rows)
                {
                    blockedUser.Id = Convert.ToInt32(dr["Id"]);
                    blockedUser.FirstName = dr["FirstName"].ToString();
                    blockedUser.LastName = dr["LastName"].ToString();
                    blockedUser.EmailId = dr["EmailId"].ToString();
                    blockedUser.LoginId = dr["LoginId"].ToString();
                    blockedUser.Password = dr["Password"].ToString();
                    blockedUser.AccountNumber = dr["AccountNumber"].ToString();
                    blockedUser.LoginTime = Convert.ToDateTime(dr["LoginTime"]);
                }
            }


            return blockedUser;
        }

        protected ArrayList GetBlockedUsersByUserType(int userTypeId)
        {
            logger.Debug(" inside GetBlockedUsersByUserType for UserTypeId= " + userTypeId);

            ArrayList blockedUsersList = new ArrayList();
            BlockedUser blockedUser = null;
            DataTable dt = null;

            SqlConnection conn = new SqlConnection(dbConnectionString);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandText = DBConstants.GET_BLOCKED_USERS_BY_USER_TYPE;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@iUserTypeId", userTypeId);
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;
            dt = new DataTable();
            da.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    blockedUser = new BlockedUser();

                    blockedUser.Id = Convert.ToInt32(dr["Id"]);
                    blockedUser.FirstName = dr["FirstName"].ToString();
                    blockedUser.LastName = dr["LastName"].ToString();
                    blockedUser.EmailId = dr["EmailId"].ToString();
                    blockedUser.LoginId = dr["LoginId"].ToString();
                    blockedUser.Password = dr["Password"].ToString();
                    blockedUser.AccountNumber = dr["AccountNumber"].ToString();
                    blockedUser.LoginTime = Convert.ToDateTime(dr["LoginTime"]);

                    blockedUsersList.Add(blockedUser);
                }
            }



            return blockedUsersList;
        }

        internal static int UnblockUser(int userId)
        {
            int result = 0;
            DBModel objModel = new DBModel();

            result = objModel.DeleteIncorrectLoginAttempts(userId);

            return result;
        }

        internal static bool ValidateUserAuthenticity(string username, string password, User user)
        {
            logger.Debug(" Inside ValidateUserAuthenticity for Username = " + username + ", Password: " + password);

            bool result = false;

            try
            {
                if ((string.Equals(username.ToUpper(), user.LoginId.ToUpper())) && (string.Equals(password, user.Password)))
                {
                    result = true;
                }
                else
                {
                    result = false;
                }
            }
            catch (SqlException ex)
            {
                logger.Fatal("Exception in ValidateUserAuthenticity for Username = " + username + ", Password: " + password + ", Exception= " + ex.Message + ", STACKTRACE= " + ex.StackTrace);
                throw;
            }

            return result;
        }
    }
}