/// <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);
}
}