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