/// <summary> /// this function will update the given seller with its database info using its fiscal /// </summary> /// <param name="seller">the given seller</param> public static void UpdateLocalSellerByFiscalCode(ObjectStructures.Invoice.Seller seller) { //the select query String QueryCommand = "SELECT * FROM seller.furnizori WHERE cod_fiscal = :p_seller_fiscal_code"; //the sole parameter NpgsqlParameter QueryParameter = new NpgsqlParameter("p_seller_fiscal_code", seller.FiscalCode); //we check the connection if (!PgSqlConnection.OpenConnection()) { return; } //before returning the reader result to a dataTable DataTable result = PgSqlConnection.ExecuteReaderToDataTable(QueryCommand, QueryParameter); //once we have the table we close the connection Miscellaneous.NormalConnectionClose(PgSqlConnection); //we check if we have any viable values and if we do we update the given seller if (result != null && result.Rows.Count > 0) { seller.ID = result.Rows[0].Field <Int32>("ID"); seller.Name = result.Rows[0].Field <String>("DENUMIRE"); seller.CommercialRegistryNumber = result.Rows[0].Field <String>("NR_REGISTRU_COMERT"); seller.FiscalCode = result.Rows[0].Field <String>("COD_FISCAL"); seller.Headquarters = result.Rows[0].Field <String>("SEDIUL"); seller.WorkPoint = result.Rows[0].Field <String>("PUNCT_LUCRU"); seller.Phone = result.Rows[0].Field <String>("TELEFON"); seller.Email = result.Rows[0].Field <String>("EMAIL"); seller.Logo = new Logo(result.Rows[0].Field <Byte[]>("SIGLA")); } }
/// <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> /// 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> /// 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 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> /// 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> /// this function will update the given seller with its database info using its ID as a link /// </summary> /// <param name="seller">the given seller</param> public static void UpdateLocalSellerByID(ObjectStructures.Invoice.Seller seller) { //the select query command String QueryCommand = "SELECT * FROM seller.furnizori WHERE id = :p_seller"; //the sole query parameter NpgsqlParameter QueryParameter = new NpgsqlParameter("p_seller", seller.ID); //we try to open the connection if (!PgSqlConnection.OpenConnection()) { return; } //then we will execute the query as a reader to the datatable DataTable result = PgSqlConnection.ExecuteReaderToDataTable(QueryCommand, QueryParameter); //and close the connection Miscellaneous.NormalConnectionClose(PgSqlConnection); //if the table has at least one viable result if (result != null && result.Rows.Count > 0) { seller.ID = result.Rows[0].Field <Int32>("ID"); seller.Name = result.Rows[0].Field <String>("DENUMIRE"); seller.CommercialRegistryNumber = result.Rows[0].Field <String>("NR_REGISTRU_COMERT"); seller.FiscalCode = result.Rows[0].Field <String>("COD_FISCAL"); seller.Headquarters = result.Rows[0].Field <String>("SEDIUL"); seller.WorkPoint = result.Rows[0].Field <String>("PUNCT_LUCRU"); seller.Phone = result.Rows[0].Field <String>("TELEFON"); seller.Email = result.Rows[0].Field <String>("EMAIL"); seller.Logo = new Logo(result.Rows[0].Field <Byte[]>("SIGLA")); } }
/// <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 add an entire list of bank accounts to the given seller /// </summary> /// <param name="seller">the given seller</param> /// <param name="bankAccounts">the list of bank account</param> public void AddBankAccountsForSeller(ObjectStructures.Invoice.Seller seller, List <BankAccount> bankAccounts) { foreach (var element in bankAccounts) { AddNewBankAccountForSellerWithoutSave(seller, element); } base.SaveChanges(); }
/// <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 List <BankAccount> GetBankAccountsForSeller(ObjectStructures.Invoice.Seller seller) { return(base.ConturiBancareFurnizori.Where(element => element.FurnizorId == seller.ID && (element.Activ ?? false)) .Select(element => new BankAccount { ID = element.Id, Bank = element.Banca, Account = element.Cont }).ToList()); }
/// <summary> /// this function will add an entire list of bank accounts to the given seller /// </summary> /// <param name="seller">the given seller</param> /// <param name="bankAccounts">the list of bank account</param> public static void AddBankAccountsForSeller(ObjectStructures.Invoice.Seller seller, List <BankAccount> bankAccounts) { //we attemp to open the connection if (!PgSqlConnection.OpenConnection()) { return; } //before iterating the element is the bankAccounts list foreach (BankAccount bankAccount in bankAccounts) { //and call the insert function with the active connection AddNewBankAccountForSeller(seller, bankAccount, PgSqlConnection); } //once all is done we 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> /// 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 retrieve the logo of a given seller /// </summary> /// <param name="seller">the logo</param> /// <returns>the logo object</returns> public static Logo GetLogoOfSeller(ObjectStructures.Invoice.Seller seller) { //the query select command String QueryCommand = "SELECT sigla FROM seller.furnizori WHERE id = :p_seller AND activ"; //the query parameter NpgsqlParameter QueryParameter = new NpgsqlParameter("p_seller", seller.ID); //once we have the query command and parameters we check to see if we are able to open the connection if (!PgSqlConnection.OpenConnection()) { return(null); } //we retrive the logo as a ByteArray Byte[] value = (Byte[])PgSqlConnection.ExecuteScalar(QueryCommand, QueryParameter); //we close the connection Miscellaneous.NormalConnectionClose(PgSqlConnection); //and return a new Logo over the return(new Logo(value)); }
/// <summary> /// this function will 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 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 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 update the given seller from the context based on its id value /// </summary> /// <param name="seller">the given seller</param> public void UpdateLocalSellerByID(ObjectStructures.Invoice.Seller seller) { seller.ConsumeDatabaseObject(base.Furnizori.Find(seller.ID)); }
/// <summary> /// this function will retrieve the logo of a given seller /// </summary> /// <param name="seller">the logo</param> /// <returns>the logo object</returns> public Logo GetLogoOfSeller(ObjectStructures.Invoice.Seller seller) { return(new Logo(base.Furnizori.Find(seller.ID).Sigla)); }
/// <summary> /// this function will update the given seller from the context based on its fiscal code value /// </summary> /// <param name="seller">the given seller</param> public void UpdateLocalSellerByFiscalCode(ObjectStructures.Invoice.Seller seller) { seller.ConsumeDatabaseObject(base.Furnizori.Where(element => element.CodFiscal == seller.FiscalCode).ToList().FirstOrDefault()); }
/// <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); }