/// <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 function will retreive a user from the database based on the email value
/// </summary>
/// <param name="email">the email value</param>
/// <returns>the user retrieved from the database</returns>
public static User RetrieveUser(String email)
{
String queryCommand = "SELECT * FROM users.utilizatori WHERE email = :p_email";
NpgsqlParameter queryParameter = new NpgsqlParameter("p_email", email);
if (!PgSqlConnection.OpenConnection())
{
return(null);
}
DataTable result = PgSqlConnection.ExecuteReaderToDataTable(queryCommand, queryParameter);
//we close the connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
if (result != null && result.Rows.Count > 0)
{
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()
}
}
;
return(null);
}
/// <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 retrieve the account linked to the username-password or email-password
/// </summary>
/// <param name="loginController"></param>
/// <returns></returns>
public static User RetrieveUser(LoginController loginController)
{
String queryCommand = "SELECT * FROM users.utilizatori WHERE (nume_utilizator = :p_username OR email = :p_email) AND parola = :p_password AND activ";
NpgsqlParameter[] queryParameters =
{
new NpgsqlParameter("p_username", loginController.Username),
new NpgsqlParameter("p_email", loginController.Username),
new NpgsqlParameter("p_password", loginController.Password)
};
if (!PgSqlConnection.OpenConnection())
{
return(null);
}
DataTable result = PgSqlConnection.ExecuteReaderToDataTable(queryCommand, queryParameters);
//we close the connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
if (result != null && result.Rows.Count > 0)
{
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);
}
}
/// <summary>
/// this function will retrive the bank name from the glossary tables based upon the given bank account
/// </summary>
/// <param name="bankAccountController">the bank account controller</param>
public static void GetBankOfAccount(BankAccountController bankAccountController)
{
String queryCommand = "SELECT denumire FROM glossary.institutii_bancare WHERE cod_iban = :p_iban ";
NpgsqlParameter queryParameters = new NpgsqlParameter("p_iban", MentorBilling.Miscellaneous.BankFunctions.GetCodeFromIBAN(bankAccountController.Account));
if (!PgSqlConnection.OpenConnection())
{
return;
}
bankAccountController.Bank = PgSqlConnection.ExecuteScalar(queryCommand, queryParameters).ToString();
Miscellaneous.NormalConnectionClose(PgSqlConnection);
}
/// <summary>
/// this is the main function for retrieving the sysadmin rigths for a given user
/// </summary>
/// <param name="user">the given user</param>
/// <returns>the sysadmin rights</returns>
public static Boolean CheckAdministratorRights(User user)
{
String queryCommand = "SELECT sysadmin FROM users.utilizatori WHERE id = :p_user_id";
NpgsqlParameter queryParameter = new NpgsqlParameter("p_user_id", user.ID);
if (!PgSqlConnection.OpenConnection())
{
return(false);
}
return((Boolean)PgSqlConnection.ExecuteScalar(queryCommand, queryParameter) ?
Miscellaneous.NormalConnectionClose(PgSqlConnection):
Miscellaneous.ErrorConnectionClose(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 check if there is already an account bound to the current registers email adress
/// </summary>
/// <param name="registerController">the current register controller</param>
/// <returns>wether another account with the same username exists or not</returns>
public static Boolean CheckEmail(RegisterController registerController)
{
String sqlCommand = "SELECT COUNT(*) FROM users.utilizatori WHERE email = :p_email";
NpgsqlParameter npgsqlParameter = new NpgsqlParameter(":p_email", registerController.Email);
if (!PgSqlConnection.OpenConnection())
{
return(false);
}
Boolean result = (Int64)PgSqlConnection.ExecuteScalar(sqlCommand, npgsqlParameter) > 0;
Miscellaneous.NormalConnectionClose(PgSqlConnection);
return(result);
}
/// <summary>
/// this function will check if there is already an account with the current login controllers username or email
/// </summary>
/// <param name="loginController">the current login controller</param>
/// <returns>wether another account with the controllers username or email exists or not</returns>
public static Boolean CheckUsernameOrEmail(LoginController loginController)
{
String sqlCommand = "SELECT COUNT(*) FROM users.utilizatori WHERE nume_utilizator = :p_username OR email = :p_username";
NpgsqlParameter npgsqlParameter = new NpgsqlParameter(":p_username", loginController.Username);
if (!PgSqlConnection.OpenConnection())
{
return(false);
}
Boolean result = (Int64)PgSqlConnection.ExecuteScalar(sqlCommand, npgsqlParameter) > 0;
Miscellaneous.NormalConnectionClose(PgSqlConnection);
return(result);
}
/// <summary>
/// this function will login a given user in the log
/// </summary>
/// <param name="user">the user</param>
/// <returns>the state of the query</returns>
public static Boolean LoginUser(User user)
{
String queryCommand = "INSERT INTO log.log_utilizatori(utilizator_id,logged) " +
"VALUES(:p_user_id,:p_logged)";
NpgsqlParameter[] queryParameters =
{
new NpgsqlParameter("p_user_id", user.ID),
new NpgsqlParameter("p_logged", true)
};
if (!PgSqlConnection.OpenConnection())
{
return(false);
}
PgSqlConnection.ExecuteNonQuery(queryCommand, queryParameters);
return(Miscellaneous.NormalConnectionClose(PgSqlConnection));
}
/// <summary>
/// this function will retrieve a given users active subscription
/// </summary>
/// <param name="user">the given user</param>
/// <returns>the active subscription</returns>
public static Subscription GetSubscriptionForUser(User user)
{
//the select command that will retrieve the data from the server
String queryCommand = "SELECT au.id AS id, a.denumire AS name, au.valoare_lunara AS monthly_fee, " +
"a.id as subscription_type, a.explicatii AS explanations, " +
"au.ultima_plata AS last_payment, au.perioada_activa AS active_period " +
"FROM users.abonamente_utilizatori AS au " +
"LEFT JOIN users.abonamente AS a " +
"ON au.abonament_id = a.id " +
"WHERE au.utilizator_id = :p_user_id AND au.activ";
//the command parameters
NpgsqlParameter queryParameters = new NpgsqlParameter(":p_user_id", user.ID);
//if we fail to open the connection we return a null object
if (!PgSqlConnection.OpenConnection())
{
return(null);
}
//else we execute the reader on the table
DataTable result = PgSqlConnection.ExecuteReaderToDataTable(queryCommand, queryParameters);
//and close the connection once done
Miscellaneous.NormalConnectionClose(PgSqlConnection);
//afterwards we return the newly created subscription
if (result != null && result.Rows.Count > 0)
{
return new Subscription
{
ID = (Int64)result.Rows[0]["ID"],
Name = result.Rows[0]["NAME"].ToString(),
MonthlyFee = (Double)result.Rows[0]["MONTHLY_FEE"],
Explanations = result.Rows[0]["EXPLANATIONS"].ToString(),
ActivePeriod = (Int32)result.Rows[0]["ACTIVE_PERIOD"],
LastPayment = (DateTime)result.Rows[0]["LAST_PAYMENT"],
SubscriptionType = (Int64)result.Rows[0]["SUBSCRIPTION_TYPE"]
}
}
;
else
{
return(null);
}
}
/// <summary>
/// this function will check if a valid account is linked to the username-password or email-password
/// </summary>
/// <param name="loginController">the given login controller</param>
/// <returns></returns>
public static Boolean CheckAccountValidity(LoginController loginController)
{
String queryCommand = "SELECT COUNT(*) FROM users.utilizatori WHERE (nume_utilizator = :p_username OR email = :p_email) AND parola = :p_password AND activ";
NpgsqlParameter[] queryParameters =
{
new NpgsqlParameter("p_username", loginController.Username),
new NpgsqlParameter("p_email", loginController.Username),
new NpgsqlParameter("p_password", loginController.Password)
};
if (!PgSqlConnection.OpenConnection())
{
return(false);
}
Boolean result = (Int64)PgSqlConnection.ExecuteScalar(queryCommand, queryParameters) > 0;
Miscellaneous.NormalConnectionClose(PgSqlConnection);
return(result);
}
/// <summary>
/// this function will retrieve the group to which the user is part of
/// </summary>
/// <param name="user">the given user</param>
/// <returns>the Group</returns>
public static Group GetUserGroup(User user)
{
String queryCommand = "SELECT g.id AS id, g.denumire AS name, u.id AS admin_id," +
"u.nume_utilizator AS admin_username, u.nume AS admin_surname, " +
"u.prenume AS admin_name " +
"FROM users.grupuri_utilizatori AS gu " +
"LEFT JOIN users.grupuri AS g ON gu.grup_id = g.id " +
"LEFT JOIN users.utilizatori AS u ON u.id = g.administrator_grup " +
"WHERE gu.utilizator_id = :p_user_id AND gu.activ AND g.activ";
NpgsqlParameter queryParameter = new NpgsqlParameter("p_user_id", user.ID);
if (!PgSqlConnection.OpenConnection())
{
return(null);
}
DataTable result = PgSqlConnection.ExecuteReaderToDataTable(queryCommand, queryParameter);
Miscellaneous.NormalConnectionClose(PgSqlConnection);
if (result != null && result.Rows.Count > 0)
{
return new Group
{
ID = (Int64)result.Rows[0]["ID"],
Name = result.Rows[0]["NAME"].ToString(),
Administrator = new User
{
ID = (Int64)result.Rows[0]["ADMIN_ID"],
Username = result.Rows[0]["ADMIN_USERNAME"].ToString(),
Surname = result.Rows[0]["ADMIN_SURNAME"].ToString(),
Name = result.Rows[0]["ADMIN_NAME"].ToString()
}
}
}
;
else
{
return(null);
}
}
/// <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);
}
}
