/// <summary>
        /// this function generates the initial subscription for an account
        /// </summary>
        /// <param name="user">the newly created user</param>
        /// <returns>the state of the command</returns>
        public static Boolean GenerateInactiveSubscription(User user)
        {
            //we get the subscription id for the inactive subscription
            Int64 currentSubsciptionID = (Int64)Settings.Subscriptions.SubscriptionSettings.Subscriptions.InactiveSubscription;

            #region Action Log
            String Action  = "Initializat abonamentul inactiv pentru utilizatorul " + user.Email;
            String Command = String.Format("INSERT INTO users.abonamente_utilizatori(utilizator_id, abonament_id) " +
                                           "VALUES({0},{1})", user.ID, currentSubsciptionID);
            String IP = IPFunctions.GetWANIp();
            #endregion
            //we create the command for the query
            String queryCommand = "INSERT INTO users.abonamente_utilizatori(utilizator_id,abonament_id) " +
                                  "VALUES(:p_user_id,:p_subscription_id)";
            //set the values of the parameters
            NpgsqlParameter[] queryParameters =
            {
                new NpgsqlParameter("p_user_id",         user.ID),
                new NpgsqlParameter("p_subscription_id", currentSubsciptionID)
            };
            //if the connection fails we return false
            if (!PgSqlConnection.OpenConnection())
            {
                return(false);
            }
            //else we execute the command
            PgSqlConnection.ExecuteNonQuery(queryCommand, queryParameters);
            //we also log the action on the same connection
            ActionLog.LogAction(Action, IP, Command, PgSqlConnection);
            //and return true
            return(Miscellaneous.NormalConnectionClose(PgSqlConnection));
        }
 /// <summary>
 /// this is the main function for updating the password
 /// </summary>
 /// <param name="user">the user for which we will update the password</param>
 /// <param name="resetPasswordController">the password</param>
 /// <returns>the state of the query</returns>
 public static Boolean UpdatePassword(User user, ResetPasswordController resetPasswordController)
 {
     #region LogAction
     //the main Action for the log
     String Action = String.Format("Sa actualizat parola utilizatorului cu emailul {0}", user.Email);
     //the main command format for the log
     String Command = String.Format("UPDATE users.utilizatori SET parola = {0} WHERE id = {1}", resetPasswordController.Password, user.ID);
     //then we will create a new ipFunctions to get the WanIP
     String IP = IPFunctions.GetWANIp();
     #endregion
     //the query string
     String queryCommand = "UPDATE users.utilizatori SET parola = :p_password WHERE id = :p_user_id";
     //the query parameters
     NpgsqlParameter[] queryParamaters =
     {
         new NpgsqlParameter("p_password", resetPasswordController.Password),
         new NpgsqlParameter("p_user_id",  user.ID)
     };
     //we attempt to open the connection
     if (!PgSqlConnection.OpenConnection())
     {
         return(false);
     }
     //we execute the update command
     PgSqlConnection.ExecuteNonQuery(queryCommand, queryParamaters);
     //log the action
     ActionLog.LogAction(Action, Command, IP, PgSqlConnection);
     //before closing the connection
     return(Miscellaneous.NormalConnectionClose(PgSqlConnection));
 }
        /// <summary>
        /// this function will activate the trial subscription for an account
        /// </summary>
        /// <param name="user">the user for which the trial will activate</param>
        /// <returns>the state of the command</returns>
        public static Boolean ActivateTrialSubscription(User user)
        {
            //this function can only ocur when a client has an inactive subscription
            //we get the ID for the inactive subscription
            Int64 currentSubscriptionID = (Int64)Settings.Subscriptions.SubscriptionSettings.Subscriptions.InactiveSubscription;
            //then the ID for the trial subscription
            Int64 newSubscriptionID = (Int64)Settings.Subscriptions.SubscriptionSettings.Subscriptions.ActiveTrialSubscription;

            //we prepare the action log
            #region Action Log
            //set the action
            String Action = "Activat abonamentul de trial pentru utilizatorul " + user.Email;
            //retrieve the IP
            String IP = IPFunctions.GetWANIp();
            //then format the command
            String command = String.Format("UPDATE users.abonamente_utilizatori " +
                                           "SET abonament_id = {0}" +
                                           " ultima_plata = {1} " +
                                           "WHERE utilizator_id = {2} AND abonament_id = {3}",
                                           newSubscriptionID,
                                           DateTime.Now,
                                           user.ID,
                                           currentSubscriptionID
                                           );
            #endregion
            //we set the queryCommand
            String queryCommand = "UPDATE users.abonamente_utilizatori " +
                                  "SET abonament_id = :p_new_subscription," +
                                  " ultima_plata = :p_new_date " +
                                  "WHERE utilizator_id = :p_user_id AND abonament_id = :p_old_subscription";
            //and initialize the parameters
            NpgsqlParameter[] queryParameters =
            {
                new NpgsqlParameter("p_new_subscription", newSubscriptionID),
                new NpgsqlParameter("p_old_subscription", currentSubscriptionID),
                new NpgsqlParameter("p_user_id",          user.ID),
                new NpgsqlParameter("p_new_date",         DateTime.Now)
            };
            //if we fail to open the connection we return false;
            if (!PgSqlConnection.OpenConnection())
            {
                return(false);
            }
            //if not we execute the command with the attached parameters
            PgSqlConnection.ExecuteNonQuery(queryCommand, queryParameters);
            //and log the action on the same connection
            ActionLog.LogAction(Action, IP, command, PgSqlConnection);
            //before returning true;
            return(Miscellaneous.NormalConnectionClose(PgSqlConnection));
        }
 /// <summary>
 /// this function will register a new user in the database and then return it
 /// </summary>
 /// <param name="registerController">the register controller for the new user</param>
 /// <returns>the newly added user</returns>
 public static User RegisterUser(RegisterController registerController)
 {
     #region LogAction
     //the action for the log
     String Action = "A fost inregistrat un nou utilizator la adresa de email: " + registerController.Email;
     //First we format the command to register
     String command = String.Format("INSERT INTO users.utilizatori(nume_utilizator,email,parola,nume,prenume) " +
                                    "VALUES({0},{1},{2},{3},{4}) RETURNING *",
                                    registerController.Username,
                                    registerController.Email,
                                    registerController.Password,
                                    registerController.Surname,
                                    registerController.Name
                                    );
     //then we will create a new ipFunctions for the httpContextAccessor
     String IP = IPFunctions.GetWANIp();
     #endregion
     //the insert returning command will return a single column based on the new insert
     String queryCommand = "INSERT INTO users.utilizatori(nume_utilizator,email,parola,nume,prenume) " +
                           "VALUES(:p_username,:p_email,:p_password,:p_surname,:p_name) " +
                           "RETURNING *";
     //we bind the parameters to the registerController properties
     NpgsqlParameter[] queryParameters =
     {
         new NpgsqlParameter("p_username", registerController.Username),
         new NpgsqlParameter("p_email",    registerController.Email),
         new NpgsqlParameter("p_password", registerController.Password),
         new NpgsqlParameter("p_surname",  registerController.Surname),
         new NpgsqlParameter("p_name",     registerController.Name)
     };
     //if we are unable to connect to the database we return a null object
     //this should never happen since they will be on the same server though better safe than sorry
     if (!PgSqlConnection.OpenConnection())
     {
         return(null);
     }
     //once done we run the sqlCommand and retrieve the values to a new DataTable
     DataTable result = PgSqlConnection.ExecuteReaderToDataTable(queryCommand, queryParameters);
     //once that is done we close the f*****g connection
     Miscellaneous.NormalConnectionClose(PgSqlConnection);
     //we will log the current action
     ActionLog.LogAction(Action, IP, command);
     //before initializing a new user from the dataTable
     if (result != null && result.Rows.Count > 0)
     {
         //then finally return the new user
         return new User
                {
                    ID       = (Int64)result.Rows[0]["ID"],
                    Username = result.Rows[0]["NUME_UTILIZATOR"].ToString(),
                    Email    = result.Rows[0]["EMAIL"].ToString(),
                    Name     = result.Rows[0]["PRENUME"].ToString(),
                    Surname  = result.Rows[0]["NUME"].ToString()
                }
     }
     ;
     else
     {
         return(null);
     }
 }