/// <summary>
/// this function will update the local menu with the settings from the database server for the specific user
/// </summary>
/// <param name="user">the specific user</param>
/// <param name="menu">the instance menu</param>
public static void UpdateLocalUserMenu(User user, Menu menu)
{
//we write the sqlQuery
String QueryCommand = "SELECT * FROM settings.meniu_utilizator WHERE utilizator_id = :p_user_id";
//we instantiate the query parameter
NpgsqlParameter QueryParameter = new NpgsqlParameter("p_user_id", user.ID);
//if we are unable to connect to the server we abandon execution
if (!PgSqlConnection.OpenConnection())
{
return;
}
//we then retrieve the dataTable
DataTable result = PgSqlConnection.ExecuteReaderToDataTable(QueryCommand, QueryParameter);
//let us not be morons and close the connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
//we check if the result has values
if (result != null && result.Rows.Count > 0)
{
//we iterate the menu items
foreach (MenuItem menuItem in menu.UserMenu)
{
//and set their active values
menuItem.IsActive = result.AsEnumerable()
.Where(r => r.Field <Int32>("ID") == menuItem.MenuItemID)
.Select(r => r.Field <Boolean>("ACTIV"))
.FirstOrDefault();
}
}
}
/// <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 generate the Initial Settings for the user
/// </summary>
/// <param name="user">the given user</param>
public static void GenerateInitialUserSettings(User user)
{
#region ActionLog
//we format the log action for the element
String logAction = $"S-au generat setarile initiale pentru utilizatorul {user.DisplayName}";
//we generate the log Command
String logCommand = "INSERT INTO setari_utilizatori(utilizator_id, setare_id, valoare_setare) " +
$"SELECT {user.ID}, id, valoare_initiala FROM setari";
//we generate the Computer IP
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
//we write the sqlQuery
String QueryCommand = "INSERT INTO settings.setari_utilizatori(utilizator_id, setare_id, valoare_setare) " +
"SELECT :p_user_id, id, valoare_initiala FROM settings.setari";
//we instantiate the query parameter
NpgsqlParameter QueryParameter = new NpgsqlParameter("p_user_id", user.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.ExecuteNonQuery(QueryCommand, QueryParameter);
//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 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 log a given action
/// </summary>
/// <param name="Action">the given action</param>
public static void LogAction(String Action)
{
String QueryCommand = "INSERT INTO log.log_actiuni(actiune) VALUES(:p_action)";
NpgsqlParameter QueryParameter = new NpgsqlParameter("p_action", Action);
if (!PgSqlConnection.OpenConnection())
{
return;
}
PgSqlConnection.ExecuteNonQuery(QueryCommand, QueryParameter);
}
/// <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 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 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 retrieve the complete list of viable bank accounts for a given seller
/// </summary>
/// <param name="seller">the given seller</param>
/// <returns>the valid bank account list</returns>
public static List <BankAccount> GetBankAccountsForSeller(ObjectStructures.Invoice.Seller seller)
{
//the select query
String QueryCommand = "SELECT * " +
"FROM seller.conturi_bancare_furnizori " +
"WHERE furnizor_id = :p_seller_id and activ";
//the query parameters
NpgsqlParameter QueryParameter = new NpgsqlParameter(":p_seller_id", seller.ID);
//if the connection fails to open we return a null list
if (!PgSqlConnection.OpenConnection())
{
return(null);
}
//we retrieve the query result into a DataTable
DataTable result = PgSqlConnection.ExecuteReaderToDataTable(QueryCommand, QueryParameter);
//we close the connection before leaving the method
Miscellaneous.NormalConnectionClose(PgSqlConnection);
//then check if the result contains any elements
if (result != null && result.Rows.Count > 0)
{
//if there are valid elements we cast the dataTable to the structure
return(result.AsEnumerable().Select(row => new BankAccount
{
ID = row.Field <Int32>("ID"),
Bank = row.Field <String>("BANCA"),
Account = row.Field <String>("CONT")
}).ToList());
}
//else we return null
else
{
return(null);
}
}
/// <summary>
/// this function will get a complete list of sellers for the given user
/// </summary>
/// <param name="user">the given user</param>
/// <returns>a list of sellers</returns>
public static List <ObjectStructures.Invoice.Seller> GetSellersForUser(User user)
{
//we initialize the query select command
String QueryCommand = "SELECT * FROM seller.furnizori WHERE utilizator_id = :p_user_id AND activ";
//set the solo query parameter
NpgsqlParameter QueryParameter = new NpgsqlParameter("p_user_id", user.ID);
//if we fail to open the connection we return a null object
if (!PgSqlConnection.OpenConnection())
{
return(null);
}
//if not we retrieve the query results into a DataTable
DataTable result = PgSqlConnection.ExecuteReaderToDataTable(QueryCommand, QueryParameter);
//once that is done we close the connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
//if the result contains at least one entry
if (result != null && result.Rows.Count > 0)
{
//we use linq select to transform the dataTable to a list of Seller objects
return(result.AsEnumerable().Select(row => new ObjectStructures.Invoice.Seller
{
ID = row.Field <Int32>("ID"),
Name = row.Field <String>("DENUMIRE"),
CommercialRegistryNumber = row.Field <String>("NR_REGISTRU_COMERT"),
FiscalCode = row.Field <String>("COD_FISCAL"),
Headquarters = row.Field <String>("SEDIUL"),
WorkPoint = row.Field <String>("PUNCT_LUCRU"),
Phone = row.Field <String>("TELEFON"),
Email = row.Field <String>("EMAIL"),
Logo = new Logo(row.Field <Byte[]>("SIGLA"))
}).ToList());
}
else
{
return(null);
}
}
/// <summary>
/// this function will log a given action to a specific IP Adress
/// </summary>
/// <param name="Action">the given action</param>
/// <param name="IP">the ip adress</param>
/// <param name="connection">the open connection received from the caller</param>
public static void LogAction(String Action, String IP, PostgreSqlConnection connection)
{
String QueryCommand = "INSERT INTO log.log_actiuni(actiune,ip_actiune) VALUES(:p_action,:p_ip)";
NpgsqlParameter[] QueryParameters =
{
new NpgsqlParameter("p_action", Action),
new NpgsqlParameter("p_ip", IP)
};
if (!(connection.connection.State == System.Data.ConnectionState.Open) && !connection.OpenConnection())
{
return;
}
connection.ExecuteNonQuery(QueryCommand, QueryParameters);
}
/// <summary>
/// this function will log a given action to a specific user on a specific ip adress the database command
/// </summary>
/// <param name="Action">the given action</param>
/// <param name="IP">the specific ip adress</param>
/// <param name="User">the specific user</param>
/// <param name="Command">the database command</param>
/// <param name="connection">the open connection received from the caller</param>
public static void LogAction(String Action, String IP, User User, String Command, PostgreSqlConnection connection)
{
String QueryCommand = "INSERT INTO log.log_actiuni(actiune,ip_actiune,utilizator_id,comanda) VALUES(:p_action,:p_ip,:p_user_id,:p_command)";
NpgsqlParameter[] QueryParameters =
{
new NpgsqlParameter("p_action", Action),
new NpgsqlParameter("p_ip", IP),
new NpgsqlParameter("p_user_id", User.ID),
new NpgsqlParameter("p_command", Command)
};
if (!(connection.connection.State == System.Data.ConnectionState.Open) && !connection.OpenConnection())
{
return;
}
connection.ExecuteNonQuery(QueryCommand, QueryParameters);
}
/// <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);
}