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