/// <summary>
/// this function will update a solitary setting with the new setting value for a given user
/// </summary>
/// <param name="user">the given user</param>
/// <param name="setting">the setting</param>
public static void UpdateSettingValueForUser(User user, Setting setting)
{
#region Log Action
//the specific log action
String logAction = $"Actualizat valoarea setarii {setting.SettingDisplay} pentru utilizatorul {user.DisplayName}";
//we generate the log Command
String logCommand = "UPDATE setari_utilizatori " +
$"SET valoare_setare = {setting.GetStringValue} " +
$"WHERE utilizator_id = {user.ID} AND setare_id = {setting.ID}";
//we generate the Computer IP
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
//the update command for a single setting
String QueryCommand = "UPDATE settings.setari_utilizatori " +
"SET valoare_setare = :p_value " +
"WHERE utilizator_id = :p_user_id AND setare_id = :p_setting_id";
//the query parameters for a single setting
List <NpgsqlParameter> QueryParameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter(":p_value", setting.GetStringValue),
new NpgsqlParameter(":p_user_id", user.ID),
new NpgsqlParameter(":p_setting_id", setting.ID)
};
//if we are unable to connect to the server we abandon execution
if (!PgSqlConnection.OpenConnection())
{
return;
}
//else we call the execution of the procedure
PgSqlConnection.ExecuteScalar(QueryCommand, QueryParameters);
//we will log the multi-insert
ActionLog.LogAction(logAction, IP, user, logCommand, PgSqlConnection);
//and as always never forget to close the connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
}
/// <summary>
/// this function will add a new bank account to a given seller by piggy-backing on an already active connection
/// </summary>
/// <param name="seller">the given seller</param>
/// <param name="bankAccount">the bank account controller</param>
/// <param name="posgreSqlConnection">the active connection</param>
public static void AddNewBankAccountForSeller(ObjectStructures.Invoice.Seller seller, BankAccount bankAccount, PostgreSqlConnection posgreSqlConnection)
{
#region ActionLog
//we generate the log Action
String LogAction = $"Adaugat un nou cont bancar la banca {bankAccount.Bank} pentru societatea {seller.Name}";
//we also generate the command
String LogCommand = "INSERT INTO seller.conturi_bancare_furnizori(furnizor_id,cont,banca) " +
$"VALUES({seller.ID}, {bankAccount.Account}, {bankAccount.Bank}) " +
"ON CONFLICT(cont) " +
$"DO UPDATE SET banca= {bankAccount.Bank} RETURNING id";
//and get the instance IP
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
//we generate the query command string
String QueryCommand = "INSERT INTO seller.conturi_bancare_furnizori(furnizor_id,cont,banca) " +
"VALUES(:p_seller_id, :p_account, :p_bank) " +
"ON CONFLICT(cont) " +
"DO UPDATE SET banca= :p_bank, activ = true " +
"RETURNING id";
//and then set the parameters for the query
List <NpgsqlParameter> QueryParameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter(":p_seller_id", seller.ID),
new NpgsqlParameter(":p_account", bankAccount.Account),
new NpgsqlParameter(":p_bank", bankAccount.Bank)
};
//we execute the command returning the ID over an already active connection
bankAccount.ID = (Int32)posgreSqlConnection.ExecuteScalar(QueryCommand, QueryParameters);
//and log the command
ActionLog.LogAction(LogAction, LogCommand, IP, PgSqlConnection);
}
/// <summary>
/// this function will retrieve the logo of a given seller
/// </summary>
/// <param name="seller">the logo</param>
/// <returns>the logo object</returns>
public static Logo GetLogoOfSeller(ObjectStructures.Invoice.Seller seller)
{
//the query select command
String QueryCommand = "SELECT sigla FROM seller.furnizori WHERE id = :p_seller AND activ";
//the query parameter
NpgsqlParameter QueryParameter = new NpgsqlParameter("p_seller", seller.ID);
//once we have the query command and parameters we check to see if we are able to open the connection
if (!PgSqlConnection.OpenConnection())
{
return(null);
}
//we retrive the logo as a ByteArray
Byte[] value = (Byte[])PgSqlConnection.ExecuteScalar(QueryCommand, QueryParameter);
//we close the connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
//and return a new Logo over the
return(new Logo(value));
}
/// <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 function will create a new registry in the database for specific user settings
/// </summary>
/// <param name="user">the specific user</param>
/// <param name="menuItem">the menu item</param>
public static void GenerateSpecificMenuSettingForUser(User user, MenuItem menuItem)
{
#region ActionLog
//we format the log action for the element
String logAction = String.Format("S-a generat setarea specifica pentru inregistrare {0} din meniu pentru utilizatorul {1}",
menuItem.MenuDisplay,
user.DisplayName);
//we generate the log Command
String logCommand = String.Format("INSERT INTO settings.meniu_utilizator(utilizator_id, inregistrare_meniu, activ) " +
"VALUES({0},{1},{2})",
user.ID,
menuItem.MenuItemID,
menuItem.IsActive);
//we generate the Computer IP
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
//we write the sqlQuery
String queryCommand = "INSERT INTO settings.meniu_utilizator(utilizator_id,inregistrare_meniu,activ) " +
"VALUES(:p_user_id,:p_record_id,:p_activ)";
//we instantiate the query parameter
NpgsqlParameter[] queryParameters =
{
new NpgsqlParameter("p_user_id", user.ID),
new NpgsqlParameter("p_record_id", menuItem.MenuItemID),
new NpgsqlParameter("p_activ", menuItem.IsActive)
};
//if we are unable to connect to the server we abandon execution
if (!PgSqlConnection.OpenConnection())
{
return;
}
//else we call the execution of the procedure
PgSqlConnection.ExecuteScalar(queryCommand, queryParameters);
//and log the action
ActionLog.LogAction(logAction, IP, user, logCommand, PgSqlConnection);
//and as always never forget to close the connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
}
/// <summary>
/// this function will check if there is already an account with the current register controllers username
/// </summary>
/// <param name="registerController">the current register controller</param>
/// <returns>wether another account with the same username exists or not</returns>
public static Boolean CheckUsername(RegisterController registerController)
{
String sqlCommand = "SELECT COUNT(*) FROM users.utilizatori WHERE nume_utilizator = :p_username";
NpgsqlParameter npgsqlParameter = new NpgsqlParameter(":p_username", registerController.Username);
if (!PgSqlConnection.OpenConnection())
{
return(false);
}
Boolean result = (Int64)PgSqlConnection.ExecuteScalar(sqlCommand, npgsqlParameter) > 0;
Miscellaneous.NormalConnectionClose(PgSqlConnection);
return(result);
}
/// <summary>
/// this function will retrieve the registy number based on the given fiscal code
/// </summary>
/// <param name="FiscalCode">the given fiscal code</param>
/// <returns>the registry number linked to the given fiscal code</returns>
public static String GetRegistryNumberForFiscalCode(Int32 FiscalCode)
{
//the query select command
String QueryCommand = "SELECT cod_inmatriculare_registru_comert FROM fiscal_entity.entitati_fiscale WHERE cod_fiscal = :p_fiscal_code";
//the query parameters
NpgsqlParameter QueryParameters = new NpgsqlParameter(":p_fiscal_code", FiscalCode);
//we check if we are able to open a connection
if (!PgSqlConnection.OpenConnection())
{
return(String.Empty);
}
//the result value as a string
String result = PgSqlConnection.ExecuteScalar(QueryCommand, QueryParameters).ToString();
//never ever kids forget to close a connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
//and finally return the result
return(result);
}
/// <summary>
/// this function will add a bew bank account for the given seller to the database
/// </summary>
/// <param name="seller">the given seller</param>
/// <param name="bankAccount">the new bank account</param>
public static void AddNewBankAccountForSeller(ObjectStructures.Invoice.Seller seller, BankAccount bankAccount)
{
#region ActionLog
//we generate the log action
String LogAction = $"Adaugat un nou cont bancar la banca {bankAccount.Bank} pentru societatea {seller.Name}";
//and the log command
String LogCommand = "INSERT INTO seller.conturi_bancare_furnizori(furnizor_id,cont,banca) " +
$"VALUES({seller.ID}, {bankAccount.Account}, {bankAccount.Bank}) " +
"ON CONFLICT(cont) " +
$"DO UPDATE SET banca= {bankAccount.Bank} RETURNING id";
//before retriving the ip
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
//we get the insert command for the bank account
String QueryCommand = "INSERT INTO seller.conturi_bancare_furnizori(furnizor_id,cont,banca) " +
"VALUES(:p_seller_id, :p_account, :p_bank) " +
"ON CONFLICT(cont) " +
"DO UPDATE SET banca= :p_bank, activ = true" +
"RETURNING id";
//and set the parameters
List <NpgsqlParameter> QueryParameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter(":p_seller_id", seller.ID),
new NpgsqlParameter(":p_account", bankAccount.Account),
new NpgsqlParameter(":p_bank", bankAccount.Bank)
};
//we check if we can open the connection
if (!PgSqlConnection.OpenConnection())
{
return;
}
//and if we can we execute the query
bankAccount.ID = (Int32)PgSqlConnection.ExecuteScalar(QueryCommand, QueryParameters);
//log it on the same connection
ActionLog.LogAction(LogAction, LogCommand, IP, PgSqlConnection);
//before closing the connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
}