public void CreateSavingAccount(int client_id, decimal amount, decimal ceiling)
{
DateTime openingDate = DateTime.Now;
if (amount <= ceiling)
{
string queryString = $"INSERT INTO SavingAccounts (client_id, amount, rate, ceiling, openingDate) " +
$"VALUES ({client_id}, {amount}, {Interest}, {ceiling},'{openingDate}');";
ConnectionDB.NonQuerySQL(queryString);
string queryStringID = $"SELECT id FROM CurrentAccounts WHERE client_id={client_id};";
int currentAccountId = ConnectionDB.ReturnID(queryStringID);
string queryStringAddInDonator = $"Declare @ClientId int " +
$"SELECT @ClientId = client_id FROM Donator WHERE client_id = {client_id} " +
$"IF(@ClientId IS NULL) " +
$"BEGIN " +
$"INSERT INTO Donator(client_id, donatorCA_id) VALUES({client_id}, {currentAccountId}) " +
$"END ;";
ConnectionDB.NonQuerySQL(queryStringAddInDonator);
}
else
{
Console.WriteLine("You can' t create a savign account when the amount is bigger than the ceiling");
}
}
public static void ModifyPassword()
{
int ID = Person.ID;
Console.WriteLine("Enter your password : "******"SELECT id FROM Person WHERE id = '{ID}' AND password ='******';";
int id = ConnectionDB.ReturnID(queryString);
if (ID == id)
{
Console.WriteLine("Enter your new password : "******"UPDATE Person SET password = '******' WHERE id = '{ID}';";
ConnectionDB.NonQuerySQL(newqueryString);
}
else
{
Console.WriteLine("password is wrong");
}
}
public void WithdrawMoney(double amount)
{
int client_id = ID;
string queryString1 = $"SELECT amount FROM CurrentAccounts WHERE client_id={ client_id};";
decimal currentAmount = ConnectionDB.ReturnDecimal(queryString1);
string queryString2 = $"SELECT overdraft FROM CurrentAccounts WHERE client_id={ client_id};";
decimal overdraft = ConnectionDB.ReturnDecimal(queryString2);
string queryString3 = $"SELECT id FROM CurrentAccounts WHERE client_id={ client_id};";
int currentAccountID = ConnectionDB.ReturnID(queryString3);
if (Convert.ToDouble(currentAmount - overdraft) >= amount)
{
DateTime dateOp = DateTime.Now;
string queryString = $"UPDATE CurrentAccounts SET amount = (amount - {amount}) WHERE client_id = { client_id }; " +
$"INSERT INTO \"Transaction\" (currentAccount_id, transactionType, amount, executionDate, \"status\") " +
$"VALUES({currentAccountID}, 'withdraw', {amount}, '{dateOp}', \'done\')";
ConnectionDB.NonQuerySQL(queryString);
}
else
{
Console.WriteLine("Not enough money on current account.");
}
}
public void DeleteClient(int client_id, int destinaryAccount)
{
EmptySavingAccount(client_id);
EmptyCurrentAccount(client_id, destinaryAccount);
string queryString = $"UPDATE Person SET IsActive = 0 WHERE id = {client_id}";
ConnectionDB.NonQuerySQL(queryString);
}
public void CreateCurrentAccount(int client_id, double amount, decimal overdraft)
{
DateTime openingDate = DateTime.Today;
string queryString = $"INSERT INTO CurrentAccounts (client_id, amount, overdraft, openingDate) " +
$" VALUES ({client_id}, {amount}, {overdraft}, '{openingDate}');";
ConnectionDB.NonQuerySQL(queryString);
}
public void CreateClient(string name, double amount, decimal overdraft)
{
PasswordGenerator();
string password = CryptPassword(Password);
string queryString = $"INSERT INTO Person (name, password) VALUES ('{name}', '{password}');";
ConnectionDB.NonQuerySQL(queryString);
queryString = $"SELECT id FROM Person WHERE name = '{name}' AND password = '******';";
int client_id = ConnectionDB.ReturnID(queryString);
CurrentAccount currentAccount = new CurrentAccount();
currentAccount.CreateCurrentAccount(client_id, amount, overdraft);
}
public void QueryTransferFromSavingToCurrent(int emitterId, int beneficiaryId, double amount, DateTime executionDate)
{
string checkSavingAccountContent = $"SELECT amount FROM SavingAccounts WHERE id = {emitterId}";
decimal savingAccountAmount = ConnectionDB.ReturnDecimal(checkSavingAccountContent);
if ((Convert.ToDouble(savingAccountAmount) - amount) >= 0)
{
string queryString =
$"INSERT INTO \"Transaction\" (savingAccount_id, transactionType, beneficiaryCurrentAccount_id, amount, executionDate, status) " +
$"VALUES (" +
$"{emitterId}, " +
$"\'Money Transfer\', " +
$"{beneficiaryId}, " +
$"{amount}, " +
$"\'{executionDate}\'," +
$"\'pending\');";
ConnectionDB.NonQuerySQL(queryString);
}
}
public void QueryTransferFromCurrentToSaving(int emitterId, int beneficiaryId, double amount, DateTime firstExecution, DateTime lastExecution, Int32 interval)
{
string checkCurrentAccountContent = $"SELECT amount FROM CurrentAccounts WHERE id = {emitterId}";
decimal CurrentAccountContent = ConnectionDB.ReturnDecimal(checkCurrentAccountContent);
string getCurrentAccountOverdraft = $"SELECT overdraft FROM CurrentAccounts WHERE id = {emitterId}";
decimal CurrentAccountOverdraft = ConnectionDB.ReturnDecimal(getCurrentAccountOverdraft);
string checkSavingAccountContent = $"SELECT amount FROM SavingAccounts WHERE id = {beneficiaryId}";
decimal SavingAccountContent = ConnectionDB.ReturnDecimal(checkSavingAccountContent);
string checkSavingAccountCeiling = $"SELECT ceiling FROM SavingAccounts WHERE id = {beneficiaryId}";
decimal SavingAccountCeiling = ConnectionDB.ReturnDecimal(checkSavingAccountCeiling);
bool IsValidDonator = Client.AddFromBeneficiary(emitterId, beneficiaryId);
if (Convert.ToDouble(CurrentAccountContent - CurrentAccountOverdraft) >= amount && IsValidDonator)
{
if (((Convert.ToDouble(SavingAccountContent)) + amount) < Convert.ToDouble(SavingAccountCeiling))
{
string queryString =
$"INSERT INTO \"Transaction\" (currentAccount_id, transactionType, beneficiarySavingAccount_id, amount, executionDate, lastExecutionDate, intervalDays, status) " +
$"VALUES (" +
$"{emitterId}, " +
$"'Money Transfer', " +
$"{beneficiaryId}, " +
$"{amount}, " +
$"'{firstExecution}'," +
$"'{lastExecution}'," +
$"{interval}," +
$"'pending');";
ConnectionDB.NonQuerySQL(queryString);
}
else
{
Console.WriteLine($"Impossible transfer. Transfer would exceed ceiling limit.");
}
}
else
{
Console.WriteLine($"There is not enough money on current account to perform transfer");
}
}
public void QueryTransferFromSavingToCurrent(int emitterId, int beneficiaryId, double amount, DateTime firstExecution, DateTime lastExecution, Int32 interval)
{
string checkSavingAccountContent = $"SELECT amount FROM SavingAccounts WHERE id = {emitterId}";
decimal SavingAccountContent = ConnectionDB.ReturnDecimal(checkSavingAccountContent);
if ((Convert.ToDouble(SavingAccountContent) - amount) >= 0)
{
string queryString =
$"INSERT INTO \"Transaction\" (savingAccount_id, transactionType, beneficiaryCurrentAccount_id, amount, executionDate, lastExecutionDate, intervalDays, status) " +
$"VALUES (" +
$"{emitterId}, " +
$"'Money Transfer', " +
$"{beneficiaryId}, " +
$"{amount}, " +
$"'{firstExecution}'," +
$"'{lastExecution}'," +
$"{interval}," +
$"'pending');";
ConnectionDB.NonQuerySQL(queryString);
}
}
public void QueryTransferFromCurrentToCurrent(int emitterId, int beneficiaryId, double amount, DateTime firstExecution, DateTime lastExecution, Int32 interval)
{
int debitClient_id = Person.ID;
string checkCurrentAccountContent = $"SELECT amount FROM CurrentAccounts WHERE id = {emitterId}";
decimal CurrentAccountContent = ConnectionDB.ReturnDecimal(checkCurrentAccountContent);
string getCurrentAccountOverdraft = $"SELECT overdraft FROM CurrentAccounts WHERE id = {emitterId}";
decimal CurrentAccountOverdraft = ConnectionDB.ReturnDecimal(getCurrentAccountOverdraft);
if (Convert.ToDouble(CurrentAccountContent + CurrentAccountOverdraft) > amount)
{
string queryString =
$"INSERT INTO \"Transaction\" (currentAccount_id, transactionType, beneficiaryCurrentAccount_id, amount, executionDate, lastExecutionDate, intervalDays, status) " +
$"VALUES (" +
$"{emitterId}, " +
$"'Money Transfer', " +
$"{beneficiaryId}, " +
$"{amount}, " +
$"'{firstExecution}'," +
$"'{lastExecution}'," +
$"{interval}," +
$"'pending');";
ConnectionDB.NonQuerySQL(queryString);
}
}
public static void AddDonators(int donatorCurrentAccountId)
{
string queryStringDonator = $"INSERT INTO Donator (client_id, donatorCA_id) VALUES ({Client.ID},{donatorCurrentAccountId});";
ConnectionDB.NonQuerySQL(queryStringDonator);
}
public void DeleteSavingAccount(int account_id)
{
string queryString = $"DELETE FROM SavingAccounts WHERE id ='{account_id}';";
ConnectionDB.NonQuerySQL(queryString);
}
