/// <summary>
/// this function will update a bank account
/// </summary>
/// <param name="bankAccount">the given bank account</param>
public static void UpdateBankAccountByAccount(BankAccount bankAccount)
{
#region ActionLog
//we initialy generate the action log and command
String LogAction = $"S-a actualizat contul bancar cu ID: {bankAccount.ID}";
String LogCommand = "UPDATE seller.conturi_bancare_furnizori " +
$"SET banca = {bankAccount.Bank} " +
$"WHERE cont = {bankAccount.Account}";
//before retrieving the ip of the current instance
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
//we generate the query command
String QueryCommand = "UPDATE seller.conturi_bancare_furnizori " +
"SET banca = :p_bank, activ = true " +
"WHERE cont = :p_account";
//and set the query parameters.
List <NpgsqlParameter> QueryParameters = new List <NpgsqlParameter>()
{
new NpgsqlParameter(":p_account", bankAccount.Account),
new NpgsqlParameter(":p_bank", bankAccount.Bank)
};
//before checking if we can open the connection
if (!PgSqlConnection.OpenConnection())
{
return;
}
//we execute the non-query
PgSqlConnection.ExecuteNonQuery(QueryCommand, QueryParameters);
//and log the action
ActionLog.LogAction(LogAction, LogCommand, IP, PgSqlConnection);
//before closing the connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
}
/// <summary>
/// this function will update a complete settings list for a given user
/// </summary>
/// <param name="user">the given user</param>
/// <param name="settings">the complete settings list</param>
public void UpdateSettingsValueForUser(User user, List <Setting> settings)
{
#region Log Action
//the specific log action
String logAction = $"Actualizat valoarea tuturor setarilor pentru utilizatorul {user.DisplayName}";
//we use the string builder to make the command faster
StringBuilder commandBuilder = new StringBuilder(250 * settings.Count);
//we generate the Computer IP
String IP = Miscellaneous.IPFunctions.GetWANIp();
#endregion
foreach (Setting setting in settings)
{
#region Action Log
//we generate the log command for each inser command
commandBuilder.Append("UPDATE setari_utilizatori " +
$"SET valoare_setare = {setting.Value} " +
$"WHERE utilizator_id = {user.ID} AND setare_id = {setting.ID}");
#endregion
SetariUtilizatori setareUtilizator = base.SetariUtilizatori.Where(element => element.UtilizatorId == user.ID && element.SetareId == setting.ID).FirstOrDefault();
setareUtilizator.ValoareSetare = setting.GetStringValue;
base.Update(setareUtilizator);
}
base.LogActiuni.Add(ActionLog.LogAction(logAction, IP, commandBuilder.ToString()));
}
/// <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 update a given specific menu item into the database
/// </summary>
/// <param name="user">the given user</param>
/// <param name="menuItem">the specific menu item</param>
public static void UpdateSpecificMenuSettingForUser(User user, MenuItem menuItem)
{
#region ActionLog
//the main log display
String logAction = $"S-a actualizat starea setari {menuItem.MenuDisplay} pentru utilizatorul {user.DisplayName}";
String logCommand = $"UPDATE settings.meniu_utilizator SET activ = {menuItem.IsActive} " +
$"WHERE utilizator_id = {user.ID} AND inregistrare_meniu = {menuItem.MenuItemID}";
//the local element IP
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
//the update command for the query
String queryCommand = "UPDATE settings.meniu_utilizator SET activ = :p_activ " +
"WHERE utilizator_id = :p_user_id AND inregistrare_meniu = :p_record_id";
//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);
//we also log the current action
ActionLog.LogAction(logAction, IP, user, logCommand, PgSqlConnection);
//lets have happy functions without forgetting to close the
Miscellaneous.NormalConnectionClose(PgSqlConnection);
}
/// <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>
/// update a given seller by its fiscal code
/// </summary>
/// <param name="seller">the given seller</param>
/// <param name="user">the logged in user for the instance</param>
public void UpdateSellerByFiscalCode(ObjectStructures.Invoice.Seller seller, User user)
{
#region ActionLog
//the log action detailing the command
String LogAction = $"S-au actualizat datele societatii cu denumirea {seller.Name}";
//the formatted query command
String LogCommand = "UPDATE seller.furnizori " +
$"SET denumire = {seller.Name}, " +
$"nr_registru_comert = {seller.CommercialRegistryNumber}, " +
$"capital_social = {seller.JointStock}, " +
$"sediul = {seller.Headquarters}, " +
$"punct_lucru = {seller.WorkPoint}, " +
$"telefon = {seller.Phone}, " +
$"email = {seller.Email}, " +
$"WHERE cod_fiscal = {seller.FiscalCode}";
//the instance ip adress
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
Furnizori furnizor = base.Furnizori.Where(element => element.CodFiscal == seller.FiscalCode && element.UtilizatorId == user.ID).FirstOrDefault();
seller.DumpIntoDatabaseObject(furnizor);
base.Update(furnizor);
base.LogActiuni.Add(ActionLog.LogAction(LogAction, IP, LogCommand));
base.SaveChanges();
}
/// <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 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
MeniuUtilizator meniuUtilizator = new MeniuUtilizator
{
UtilizatorId = user.ID,
InregistrareMeniu = menuItem.MenuItemID,
Activ = menuItem.IsActive
};
base.MeniuUtilizator.Add(meniuUtilizator);
base.LogActiuni.Add(ActionLog.LogAction(logAction, IP, logCommand));
base.SaveChanges();
}
/// <summary>
/// update the seller with the given logo
/// </summary>
/// <param name="logo">the given logo</param>
/// <param name="seller">the seller</param>
public static void AddLogoToSeller(Logo logo, ObjectStructures.Invoice.Seller seller)
{
#region Action Log
//the log action detailing the event
String LogAction = $"S-a adaugat/modificat logo-ul la societatea cu denumirea {seller.Name}";
//the query formatted command
String LogCommand = $"UPDATE seller.furnizori SET sigla = {logo.LogoBase.Take(10)} WHERE id = {seller.ID}";
//the IP of the user instance
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
//the update command
String QueryCommand = "UPDATE seller.furnizori SET sigla = :p_logo WHERE id = :p_seller_id";
//the command parameters
List <NpgsqlParameter> QueryParameters = new List <NpgsqlParameter>
{
new NpgsqlParameter("p_seller_id", seller.ID),
new NpgsqlParameter()
{
ParameterName = "p_logo",
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bytea,
Value = logo.LogoBase
}
};
//we check the connection
if (!PgSqlConnection.OpenConnection())
{
return;
}
//and execute the update command
PgSqlConnection.ExecuteNonQuery(QueryCommand, QueryParameters);
//before logging the command
ActionLog.LogAction(LogAction, LogCommand, IP, PgSqlConnection);
//and closing the connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
}
/// <summary>
/// update a given seller by id
/// </summary>
/// <param name="seller">the given seller</param>
public void UpdateSellerByID(ObjectStructures.Invoice.Seller seller)
{
#region ActionLog
//the log action detailing the event
String LogAction = $"S-au actualizat datele societatii cu denumirea {seller.Name}";
//the formatted log command
//for safety reasons it does not contain the image
String LogCommand = "UPDATE seller.furnizori " +
$"SET denumire = {seller.Name}, " +
$"nr_registru_comert = {seller.CommercialRegistryNumber}, " +
$"capital_social = {seller.JointStock}, " +
$"sediul = {seller.Headquarters}, " +
$"punct_lucru = {seller.WorkPoint}, " +
$"telefon = {seller.Phone}, " +
$"email = {seller.Email}, " +
$"WHERE id = {seller.ID}";
//the ip of the instance user
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
Furnizori furnizor = base.Furnizori.Find(seller.ID);
seller.DumpIntoDatabaseObject(furnizor);
base.Update(furnizor);
base.LogActiuni.Add(ActionLog.LogAction(LogAction, IP, LogCommand));
base.SaveChanges();
}
/// <summary>
/// update a given seller by id
/// </summary>
/// <param name="seller">the given seller</param>
public static void UpdateSellerByID(ObjectStructures.Invoice.Seller seller)
{
#region ActionLog
//the log action detailing the event
String LogAction = $"S-au actualizat datele societatii cu denumirea {seller.Name}";
//the formatted log command
//for safety reasons it does not contain the image
String LogCommand = "UPDATE seller.furnizori " +
$"SET denumire = {seller.Name}, " +
$"nr_registru_comert = {seller.CommercialRegistryNumber}, " +
$"capital_social = {seller.JointStock}, " +
$"sediul = {seller.Headquarters}, " +
$"punct_lucru = {seller.WorkPoint}, " +
$"telefon = {seller.Phone}, " +
$"email = {seller.Email}, " +
$"WHERE id = {seller.ID}";
//the ip of the instance user
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
//the query update command string
String QueryCommand = "UPDATE seller.furnizori " +
"SET denumire = :p_name, " +
"nr_registru_comert = :p_registry, " +
"capital_social = :p_joint_stock, " +
"sediul = :p_headquarters, " +
"punct_lucru = :p_adress, " +
"telefon = :p_phone, " +
"email = :p_email, " +
"sigla = :p_logo" +
"WHERE id = :p_seller_id";
//the complete list of query parameters
List <NpgsqlParameter> QueryParameters = new List <NpgsqlParameter>
{
new NpgsqlParameter("p_name", seller.Name),
new NpgsqlParameter("p_registry", seller.CommercialRegistryNumber),
new NpgsqlParameter("p_joint_stock", seller.JointStock),
new NpgsqlParameter("p_headquarters", seller.Headquarters),
new NpgsqlParameter("p_adress", seller.WorkPoint),
new NpgsqlParameter("p_phone", seller.Phone),
new NpgsqlParameter("p_email", seller.Email),
new NpgsqlParameter("p_seller_id", seller.ID),
new NpgsqlParameter()
{
ParameterName = "p_logo",
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bytea,
Value = seller.Logo.LogoBase
}
};
//we check the connection
if (!PgSqlConnection.OpenConnection())
{
return;
}
//and execute the non-query
PgSqlConnection.ExecuteNonQuery(QueryCommand, QueryParameters);
//then also log the action on the same query
ActionLog.LogAction(LogAction, LogCommand, IP, PgSqlConnection);
//then close the connection
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>
/// update a given seller by its fiscal code
/// </summary>
/// <param name="seller">the given seller</param>
public static void UpdateSellerByFiscalCode(ObjectStructures.Invoice.Seller seller)
{
#region ActionLog
//the log action detailing the command
String LogAction = $"S-au actualizat datele societatii cu denumirea {seller.Name}";
//the formatted query command
String LogCommand = "UPDATE seller.furnizori " +
$"SET denumire = {seller.Name}, " +
$"nr_registru_comert = {seller.CommercialRegistryNumber}, " +
$"capital_social = {seller.JointStock}, " +
$"sediul = {seller.Headquarters}, " +
$"punct_lucru = {seller.WorkPoint}, " +
$"telefon = {seller.Phone}, " +
$"email = {seller.Email}, " +
$"WHERE cod_fiscal = {seller.FiscalCode}";
//the instance ip adress
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
//the query command
String QueryCommand = "UPDATE seller.furnizori " +
"SET denumire = :p_name, " +
"nr_registru_comert = :p_registry, " +
"capital_social = :p_joint_stock, " +
"sediul = :p_headquarters, " +
"punct_lucru = :p_adress, " +
"telefon = :p_phone, " +
"email = :p_email, " +
"sigla = :p_logo " +
"WHERE cod_fiscal = :p_fiscal_code";
//the complete parameter list
List <NpgsqlParameter> QueryParameters = new List <NpgsqlParameter>
{
new NpgsqlParameter("p_name", seller.Name),
new NpgsqlParameter("p_registry", seller.CommercialRegistryNumber),
new NpgsqlParameter("p_fiscal_code", seller.FiscalCode),
new NpgsqlParameter("p_joint_stock", seller.JointStock),
new NpgsqlParameter("p_headquarters", seller.Headquarters),
new NpgsqlParameter("p_adress", seller.WorkPoint),
new NpgsqlParameter("p_phone", seller.Phone),
new NpgsqlParameter("p_email", seller.Email),
new NpgsqlParameter()
{
ParameterName = "p_logo",
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bytea,
Value = seller.Logo.LogoBase
}
};
//we atempt to open the connection
if (!PgSqlConnection.OpenConnection())
{
return;
}
//execute the non query
PgSqlConnection.ExecuteNonQuery(QueryCommand, QueryParameters);
//log the event
ActionLog.LogAction(LogAction, LogCommand, IP, PgSqlConnection);
//and close the connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
}
/// <summary>
/// this function will update a given specific menu item into the database
/// </summary>
/// <param name="user">the given user</param>
/// <param name="menuItems">the specific menu item</param>
public static void UpdateMenuSettingsForUser(User user, List <MenuItem> menuItems)
{
#region ActionLog
//the main log display
String logAction = $"S-a actualizat starea setarilor pentru utilizatorul {user.DisplayName}";
//the log command will be updated for each item
StringBuilder logCommand = new StringBuilder(250 * menuItems.Count);
//the local element IP
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
//the query command
StringBuilder queryCommand = new StringBuilder(250 * menuItems.Count);
//the parameter list
List <NpgsqlParameter> queryParameters = new List <NpgsqlParameter>();
foreach (MenuItem menuItem in menuItems)
{
#region ActionLog
logCommand.Append($"UPDATE settings.meniu_utilizator SET activ = {menuItem.IsActive} " +
$"WHERE utilizator_id = {user.ID} AND inregistrare_meniu = {menuItem.MenuItemID};");
#endregion
//the update command for the query
queryCommand.Append(String.Format("UPDATE settings.meniu_utilizator SET activ = :p_activ_{0} ",
menuItem.MenuItemID) +
String.Format("WHERE utilizator_id = :p_user_id_{0} AND inregistrare_meniu = :p_record_id_{0};" + Environment.NewLine,
menuItem.MenuItemID));
//we instantiate the query parameter
NpgsqlParameter[] commandParameters =
{
new NpgsqlParameter(
String.Format("p_user_id_{0}", menuItem.MenuItemID)
, user.ID),
new NpgsqlParameter(
String.Format("p_record_id_{0}", menuItem.MenuItemID)
, menuItem.MenuItemID),
new NpgsqlParameter(
String.Format("p_activ_{0}", menuItem.MenuItemID)
, menuItem.IsActive)
};
queryParameters.AddRange(commandParameters);
}
//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.ToString(), queryParameters);
//we also log the current action
ActionLog.LogAction(logAction, IP, user, logCommand.ToString(), PgSqlConnection);
//lets have happy functions without forgetting to close the
Miscellaneous.NormalConnectionClose(PgSqlConnection);
}
/// <summary>
/// this function will generate the menu setting for a given user with one query
/// </summary>
/// <param name="user">the given user</param>
/// <param name="menuItems">the complete list of menu items</param>
public static void GenerateMenuSettingsForUser(User user, List <MenuItem> menuItems)
{
#region ActionLog
//the specific log action
String logAction = String.Format("S-au generat setarile generale din meniu pentru utilizatorul {0}",
user.ID);
//we generate the log Command
StringBuilder logCommand = new StringBuilder(250 * menuItems.Count);
//we generate the Computer IP
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
//we initialize an empty command
StringBuilder queryCommand = new StringBuilder(250 * menuItems.Count);
//and an empty parameter list
List <NpgsqlParameter> queryParameters = new List <NpgsqlParameter>();
//then foreach element in the list
foreach (MenuItem element in menuItems)
{
#region ActionLog
//we generate the log command for each inser command
logCommand.Append(String.Format("INSERT INTO settings.meniu_utilizator(utilizator_id, inregistrare_meniu, activ) " +
"VALUES({0},{1},{2});" + Environment.NewLine,
user.ID,
element.MenuItemID,
element.IsActive));
#endregion
//we add a specific insert command for the element
queryCommand.Append("INSERT INTO settings.meniu_utilizator(utilizator_id,inregistrare_meniu,activ) " +
String.Format("VALUES(:p_user_id_{0},:p_record_id_{0},:p_activ_{0});" + Environment.NewLine, element.MenuItemID));
//then we generate the specific parameter lists for the newly added command
NpgsqlParameter[] commadParameters =
{
new NpgsqlParameter(String.Format("p_user_id_{0}", element.MenuItemID), user.ID),
new NpgsqlParameter(String.Format("p_record_id_{0}", element.MenuItemID), element.MenuItemID),
new NpgsqlParameter(String.Format("p_activ_{0}", element.MenuItemID), element.IsActive)
};
//once that is done we add the newly created parameters to the list
queryParameters.AddRange(commadParameters);
}
//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.ToString(), queryParameters);
//we will log the multi-insert
ActionLog.LogAction(logAction, IP, user, logCommand.ToString(), PgSqlConnection);
//and as always never forget to close the connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
}
/// <summary>
/// this function will update a complete settings list for a given user
/// </summary>
/// <param name="user">the given user</param>
/// <param name="settings">the complete settings list</param>
public static void UpdateSettingsValueForUser(User user, List <Setting> settings)
{
#region Log Action
//the specific log action
String logAction = $"Actualizat valoarea tuturor setarilor pentru utilizatorul {user.DisplayName}";
//we generate the log Command
StringBuilder logCommand = new StringBuilder(250 * settings.Count);
//we generate the Computer IP
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
//the update command for a single setting
StringBuilder QueryCommand = new StringBuilder(300 * settings.Count);
//the query parameters for a single setting
List <NpgsqlParameter> QueryParameters = new List <NpgsqlParameter>();
//we iterate the settings list to create our query
foreach (Setting setting in settings)
{
#region Action Log
//we generate the log command for each inser command
logCommand.Append("UPDATE setari_utilizatori " +
$"SET valoare_setare = {setting.Value} " +
$"WHERE utilizator_id = {user.ID} AND setare_id = {setting.ID}");
#endregion
//the update command for a single setting
QueryCommand.Append("UPDATE settings.setari_utilizatori " +
String.Format("SET valoare_setare = :p_value_{0} ", setting.ID) +
String.Format("WHERE utilizator_id = :p_user_id_{0} AND setare_id = :p_setting_id_{0}", setting.ID));
//we add the query Parameters to the command
QueryParameters.AddRange(new List <NpgsqlParameter>()
{
new NpgsqlParameter(String.Format("p_value_{0}", setting.ID), setting.GetStringValue),
new NpgsqlParameter(String.Format("p_user_id_{0}", setting.ID), user.ID),
new NpgsqlParameter(String.Format("p_setting_id_{0}", 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.ToString(), QueryParameters);
//we will log the multi-insert
ActionLog.LogAction(logAction, IP, user, logCommand.ToString(), PgSqlConnection);
//and as always never forget to close the connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
}
/// <summary>
/// this function will add a new seller to the database and link it to the given user
/// </summary>
/// <param name="user">the given user</param>
/// <param name="seller">the new seller</param>
public void AddSellerToUser(User user, ObjectStructures.Invoice.Seller seller)
{
#region ActionLog
//the log action will contain an explanation of the command
String LogAction = $"S-a adaugat o noua firma cu denumirea {seller.Name} pentru utilizatorul {user.ID}";
//the formatted log command
String LogCommand = "INSERT INTO seller.furnizori(denumire,nr_registru_comert,cod_fiscal,capital_social,sediul,punct_lucru,telefon,email,utilizator_id) " +
$"VALUES({seller.Name},{seller.CommercialRegistryNumber},{seller.FiscalCode},{seller.JointStock},{seller.Headquarters},{seller.WorkPoint},{seller.Phone},{seller.Email},{user.ID}) " +
"ON CONFLICT(cod_fiscal) " +
$"DO UPDATE SET denumire = {seller.Name}, " +
$"nr_registru_comert = {seller.CommercialRegistryNumber}, " +
$"capital_social = {seller.JointStock}, " +
$"sediul = {seller.Headquarters}, " +
$"punct_lucru = {seller.WorkPoint}, " +
$"telefon = {seller.Phone}, " +
$"email = {seller.Email}, " +
$"utilizator_id = {user.ID} " +
"RETURNING id";
//the instance IP
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
//we generate a new object over furnizor
var furnizor = new Furnizori
{
Denumire = seller.Name,
NrRegistruComert = seller.CommercialRegistryNumber,
CodFiscal = seller.FiscalCode,
CapitalSocial = seller.JointStock,
Sediul = seller.Headquarters,
PunctLucru = seller.WorkPoint,
Telefon = seller.Phone,
Email = seller.Email,
Sigla = seller.Logo.LogoBase
};
//add it to the context
base.Furnizori.Add(furnizor);
//set the newly linked id back to the seller object
seller.ID = furnizor.Id;
//we also log the action
base.LogActiuni.Add(ActionLog.LogAction(LogAction, IP, LogCommand));
//and save the context changes
base.SaveChanges();
}
/// <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 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
SetariUtilizatori setareUtilizator = base.SetariUtilizatori.Where(element => element.UtilizatorId == user.ID && element.SetareId == setting.ID).FirstOrDefault();
setareUtilizator.ValoareSetare = setting.GetStringValue;
base.Update(setareUtilizator);
base.LogActiuni.Add(ActionLog.LogAction(logAction, IP, logCommand));
base.SaveChanges();
}
/// <summary>
/// this function will update a given specific menu item into the database
/// </summary>
/// <param name="user">the given user</param>
/// <param name="menuItem">the specific menu item</param>
public void UpdateSpecificMenuSettingForUser(User user, MenuItem menuItem)
{
#region ActionLog
//the main log display
String logAction = $"S-a actualizat starea setari {menuItem.MenuDisplay} pentru utilizatorul {user.DisplayName}";
String logCommand = $"UPDATE settings.meniu_utilizator SET activ = {menuItem.IsActive} " +
$"WHERE utilizator_id = {user.ID} AND inregistrare_meniu = {menuItem.MenuItemID}";
//the local element IP
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
MeniuUtilizator meniuUtilizator = base.MeniuUtilizator.Where(element => element.UtilizatorId == user.ID && element.InregistrareMeniu == menuItem.MenuItemID).FirstOrDefault();
meniuUtilizator.Activ = menuItem.IsActive;
base.Update(meniuUtilizator);
base.LogActiuni.Add(ActionLog.LogAction(logAction, IP, logCommand));
base.SaveChanges();
}
/// <summary>
/// update the seller with the given logo
/// </summary>
/// <param name="logo">the given logo</param>
/// <param name="seller">the seller</param>
public void AddLogoToSeller(Logo logo, ObjectStructures.Invoice.Seller seller)
{
#region Action Log
//the log action detailing the event
String LogAction = $"S-a adaugat/modificat logo-ul la societatea cu denumirea {seller.Name}";
//the query formatted command
String LogCommand = $"UPDATE seller.furnizori SET sigla = {logo.LogoBase.Take(10)} WHERE id = {seller.ID}";
//the IP of the user instance
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
var furnizor = base.Furnizori.Find(seller.ID);
furnizor.Sigla = seller.Logo.LogoBase;
base.Update(furnizor);
base.LogActiuni.Add(ActionLog.LogAction(LogAction, IP, LogCommand));
base.SaveChanges();
}
/// <summary>
/// this function will update a given specific menu item into the database
/// </summary>
/// <param name="user">the given user</param>
/// <param name="menuItems">the specific menu item</param>
public void UpdateMenuSettingsForUser(User user, List <MenuItem> menuItems)
{
#region ActionLog
//the main log display
String logAction = $"S-a actualizat starea setarilor pentru utilizatorul {user.DisplayName}";
//the local element IP
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
foreach (MenuItem menuItem in menuItems)
{
var element = base.MeniuUtilizator.Where(item => item.UtilizatorId == user.ID && item.InregistrareMeniu == menuItem.MenuItemID).FirstOrDefault();
element.Activ = menuItem.IsActive;
base.Update(element);
}
base.LogActiuni.Add(ActionLog.LogAction(logAction, IP));
base.SaveChanges();
}
/// <summary>
/// Log the Control that made the call and its value
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
public void LogAction(object sender, EventArgs e)
{
if (!(sender is Form || sender is ButtonBase || sender is DataGridView)) //Tailor this line to suit your needs
{ //dont log control events if its a Maintenance Form and its not in Edit mode
if (_frmType.BaseType.ToString().Contains("frmMaint")) //This is strictly specific to my project - you will need to rewrite this line and possible the line above too. That's all though...
{
PropertyInfo pi = _frmType.GetProperty("IsEditing");
bool isEditing = (bool)pi.GetValue(_frm, null);
if (!isEditing)
{
return;
}
}
}
StackTrace stackTrace = new StackTrace();
StackFrame[] stackFrames = stackTrace.GetFrames();
var eventType = stackFrames[2].GetMethod().Name;//This depends usually its the 1st Frame but in this particular framework (CSLA) its 2
ActionLog.LogAction(_frm.Name, ((Control)sender).Name, eventType, GetSendingCtrlValue(((Control)sender), eventType));
}
/// <summary>
/// this function will generate the menu setting for a given user with one query
/// </summary>
/// <param name="user">the given user</param>
/// <param name="menuItems">the complete list of menu items</param>
public void GenerateMenuSettingsForUser(User user, List <MenuItem> menuItems)
{
#region ActionLog
//the specific log action
String logAction = String.Format("S-au generat setarile generale din meniu pentru utilizatorul {0}",
user.ID);
//we generate the Computer IP
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
base.MeniuUtilizator.AddRange(menuItems.Select(element => new MeniuUtilizator
{
UtilizatorId = user.ID,
InregistrareMeniu = element.MenuItemID,
Activ = element.IsActive
}
));
base.LogActiuni.Add(ActionLog.LogAction(logAction, IP));
base.SaveChanges();
}
/// <summary>
/// this function will update a bank account
/// </summary>
/// <param name="bankAccount">the given bank account</param>
public void UpdateBankAccountByAccount(BankAccount bankAccount)
{
#region ActionLog
//we initialy generate the action log and command
String LogAction = $"S-a actualizat contul bancar cu ID: {bankAccount.ID}";
String LogCommand = "UPDATE seller.conturi_bancare_furnizori " +
$"SET banca = {bankAccount.Bank} " +
$"WHERE cont = {bankAccount.Account}";
//before retrieving the ip of the current instance
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
ConturiBancareFurnizori contBancarFurnizor = base.ConturiBancareFurnizori.Where(element => element.Cont == bankAccount.Account).FirstOrDefault();
contBancarFurnizor.Banca = bankAccount.Bank;
contBancarFurnizor.Cont = bankAccount.Account;
contBancarFurnizor.Activ = true;
base.Update(contBancarFurnizor);
base.LogActiuni.Add(ActionLog.LogAction(LogAction, IP, LogCommand));
base.SaveChanges();
}
/// <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);
}
/// <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 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>
void AddNewBankAccountForSellerWithoutSave(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 = Miscellaneous.IPFunctions.GetWANIp();
#endregion
ConturiBancareFurnizori contBancarFurnizor = new ConturiBancareFurnizori
{
FurnizorId = seller.ID,
Cont = bankAccount.Account,
Banca = bankAccount.Bank
};
base.ConturiBancareFurnizori.Add(contBancarFurnizor);
base.LogActiuni.Add(ActionLog.LogAction(LogAction, IP, LogCommand));
bankAccount.ID = contBancarFurnizor.Id;
}
/// <summary>
/// this function will add a new seller to the database and link it to the given user
/// </summary>
/// <param name="user">the given user</param>
/// <param name="seller">the new seller</param>
public static void AddSellerToUser(User user, ObjectStructures.Invoice.Seller seller)
{
#region ActionLog
//the log action will contain an explanation of the command
String LogAction = $"S-a adaugat o noua firma cu denumirea {seller.Name} pentru utilizatorul {user.ID}";
//the formatted log command
String LogCommand = "INSERT INTO seller.furnizori(denumire,nr_registru_comert,cod_fiscal,capital_social,sediul,punct_lucru,telefon,email,utilizator_id) " +
$"VALUES({seller.Name},{seller.CommercialRegistryNumber},{seller.FiscalCode},{seller.JointStock},{seller.Headquarters},{seller.WorkPoint},{seller.Phone},{seller.Email},{user.ID}) " +
"ON CONFLICT(cod_fiscal) " +
$"DO UPDATE SET denumire = {seller.Name}, " +
$"nr_registru_comert = {seller.CommercialRegistryNumber}, " +
$"capital_social = {seller.JointStock}, " +
$"sediul = {seller.Headquarters}, " +
$"punct_lucru = {seller.WorkPoint}, " +
$"telefon = {seller.Phone}, " +
$"email = {seller.Email}, " +
$"utilizator_id = {user.ID} " +
"RETURNING id";
//the instance IP
String IP = MentorBilling.Miscellaneous.IPFunctions.GetWANIp();
#endregion
//the insert query command
//for any possible error we use the on conflict over the unique key
String QueryCommand = "INSERT INTO seller.furnizori(denumire,nr_registru_comert,cod_fiscal,capital_social,sediul,punct_lucru,telefon,email,sigla,utilizator_id) " +
"VALUES(:p_name,:p_registry,:p_fiscal_code,:p_joint_stock,:p_headquarters,:p_adress,:p_phone,:p_email,:p_logo,:p_user_id) " +
"ON CONFLICT(cod_fiscal) " +
"DO UPDATE SET denumire = :p_name, " +
"nr_registru_comert = :p_registry, " +
"capital_social = :p_joint_stock, " +
"sediul = :p_headquarters, " +
"punct_lucru = :p_adress, " +
"telefon = :p_phone, " +
"email = :p_email, " +
"sigla = :p_logo, " +
"utilizator_id = :p_user_id " +
"RETURNING id";
//we initialize the parameter list
List <NpgsqlParameter> QueryParameters = new List <NpgsqlParameter>
{
new NpgsqlParameter("p_name", seller.Name),
new NpgsqlParameter("p_registry", seller.CommercialRegistryNumber),
new NpgsqlParameter("p_fiscal_code", seller.FiscalCode),
new NpgsqlParameter("p_joint_stock", seller.JointStock),
new NpgsqlParameter("p_headquarters", seller.Headquarters),
new NpgsqlParameter("p_adress", seller.WorkPoint),
new NpgsqlParameter("p_phone", seller.Phone),
new NpgsqlParameter("p_email", seller.Email),
//the logo parameter is special for it needs the specific dataType cast
//implicit cast doesn't queite work for ByteA
new NpgsqlParameter()
{
ParameterName = "p_logo",
NpgsqlDbType = NpgsqlTypes.NpgsqlDbType.Bytea,
Value = seller.Logo.LogoBase
},
new NpgsqlParameter("p_user_id", user.ID)
};
//we check to see if we can open the connection
if (!PgSqlConnection.OpenConnection())
{
return;
}
//the execute the query as a scalar for we require the ID
seller.ID = (Int32)PgSqlConnection.ExecuteScalar(QueryCommand, QueryParameters);
//then we piggy-back the log on the same active connection
ActionLog.LogAction(LogAction, LogCommand, IP, PgSqlConnection);
//and close the connection
Miscellaneous.NormalConnectionClose(PgSqlConnection);
}
