public static void SaveNewAccountInDb(AbstractAccount account)
{
string typeOfAccount;
Type type = account.GetType();
typeOfAccount = (type.Name == "CheckingAccount") ? "CA" : "SA";
string sql = "INSERT INTO Account (idCustomer,accountNumber,amount,type, ceiling, overdraft, savingsRate) "
+ " VALUES (@idCustomer,@accountNumber,@amount,@type,@ceiling,@overdraft,@savingsRate);SELECT CAST(SCOPE_IDENTITY() AS int)";
IEnumerable <SqlParameter> parameters = new List <SqlParameter>
{
new SqlParameter("@idCustomer", account.IdCustomer),
new SqlParameter("@accountNumber", account.AccountNumber),
new SqlParameter("@amount", account.Amount),
new SqlParameter("@type", typeOfAccount),
new SqlParameter("@ceiling", account.Ceiling),
new SqlParameter("@overdraft", account.Overdraft),
new SqlParameter("@savingsRate", account.SavingsRate)
};
int lastIdInserted = ExecuteQueryWithID(sql, parameters);
UpdateAccountNumberInAccount(lastIdInserted, "FR" + lastIdInserted);
}
public static bool IsCurrentCustomerAuthorizedOnAccount(AbstractAccount account)
{
if (account.IdCustomer == Program.currentCustomer.IdCustomer)
{
return(true);
}
string sql = "SELECT COUNT([idCustomer]) AS nbCustomer FROM AccountAuthorizedCustomers WHERE idAccount = '@idAccount' " +
"AND idCustomer = '@idCustomer'";
SqlCommand cmd = new SqlCommand();
cmd.Connection = GetConnexion;
cmd.CommandText = sql;
cmd.Parameters.Add(new SqlParameter("@idAccount", account.IdAccount));
cmd.Parameters.Add(new SqlParameter("@idCustomer", Program.currentCustomer.IdCustomer));
int nbCustomer = 0;
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
nbCustomer = reader.GetInt32(reader.GetOrdinal("nbCustomer"));
}
}
if (nbCustomer == 0)
{
return(false);
}
return(true);
}
public override bool isDebitAuthorized(AbstractAccount accountDestination)
{
if (IdCustomer == Program.currentCustomer.IdCustomer)
{
return(true);
}
return(false);
}
public override bool isDebitAuthorized(AbstractAccount accountOrigin)
{
if (accountOrigin.IsDebitAuthorized == true)
{
return(true);
}
return(false);
}
public static void UpdateAmountInAccount(AbstractAccount account)
{
string sql = "UPDATE Account SET amount = '@amount' WHERE idAccount = '@idAccount'";
IEnumerable <SqlParameter> parameters = new List <SqlParameter>
{
new SqlParameter("@amount", account.Amount),
new SqlParameter("@idAccount", account.IdAccount)
};
ExecuteQuery(sql, parameters);
}
public static AbstractAccount GetAccountFromDB(string accountNumber)
{
string sql = "SELECT idCustomer,idAccount,amount, type, isDebitAuthorized, creationDate, ceiling, overdraft,savingsRate " +
"FROM [Account] WHERE accountNumber = @accountNumber";
SqlCommand cmd = new SqlCommand();
cmd.Connection = GetConnexion;
cmd.CommandText = sql;
cmd.Parameters.Add(new SqlParameter("@accountNumber", accountNumber));
int idCustomer = 0;
int idAccount = 0;
decimal amount = 0;
string type = "";
bool isDebitAuthorized = true;
decimal ceiling = 0;
decimal overdraft = 0;
decimal savingsRate = 0;
DateTime creationDate = new DateTime();
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
idCustomer = reader.GetInt32(reader.GetOrdinal("idCustomer"));
idAccount = reader.GetInt32(reader.GetOrdinal("idAccount"));
amount = reader.GetDecimal(reader.GetOrdinal("amount"));
type = reader.GetString(reader.GetOrdinal("type"));
isDebitAuthorized = reader.GetBoolean(reader.GetOrdinal("isDebitAuthorized"));
creationDate = reader.GetDateTime(reader.GetOrdinal("creationDate"));
ceiling = reader.GetDecimal(reader.GetOrdinal("ceiling"));
overdraft = reader.GetDecimal(reader.GetOrdinal("overdraft"));
savingsRate = reader.GetDecimal(reader.GetOrdinal("savingsRate"));
}
}
AbstractAccount resultAccount = AccountFactory.Create(type);
resultAccount.AccountNumber = accountNumber;
resultAccount.Amount = amount;
resultAccount.CreationDate = creationDate;
resultAccount.IdAccount = idAccount;
resultAccount.IdCustomer = idCustomer;
resultAccount.IsDebitAuthorized = isDebitAuthorized;
resultAccount.Ceiling = ceiling;
resultAccount.Overdraft = overdraft;
resultAccount.SavingsRate = savingsRate;
return(resultAccount);
}
public void MakeNewTransaction(decimal amount, AbstractAccount accountOrigin, AbstractAccount accountDestination, DateTime?startDate = null, DateTime?endDate = null, int periodicity = 0)
{
AbstractTransaction currentTransaction = AbstractTransaction.Create(startDate, endDate);
currentTransaction.AccountOrigin = accountOrigin.IdAccount;
currentTransaction.AccountDestination = accountDestination.IdAccount;
currentTransaction.Amount = amount;
currentTransaction.Periodicity = periodicity;
currentTransaction.TransactionDate = DateTime.Now;
DBQuery.InsertTransaction(currentTransaction);
List <TransferMoney> transfertList = currentTransaction.GetTransferts();
Console.WriteLine("We do the transfer");
DBQuery.SaveNewTransferInDb(transfertList);
}
static void RunInstantTransferCommand(DoInstantTransferOptions opts)
{
if (Customer.IsAccountOwner(currentCustomer.IdCustomer, opts.AccountIdOrigin))
{
AbstractAccount accountOrigin = DBQuery.GetAccountFromDB(opts.AccountIdOrigin);
AbstractAccount accountDestination = DBQuery.GetAccountFromDB(opts.AccountIdDestination);
if (accountOrigin.CanBeDebited(opts.AmountToTransfer, accountDestination) && accountOrigin.isMoneyEnough(opts.AmountToTransfer) && accountDestination.CanBeCredited(opts.AmountToTransfer) && accountDestination.isTransferNotReachingCeiling(opts.AmountToTransfer))
{
currentCustomer.MakeNewTransaction(opts.AmountToTransfer, accountOrigin, accountDestination);
}
}
else
{
IO.DisplayWarning("You can't make this instant transfer, you aren't the account owner!");
}
}
static void RunDefferedTransferCommand(DoDefferedTransferOptions opts)
{
if (Customer.IsAccountOwner(currentCustomer.IdCustomer, opts.AccountIdOrigin))
{
AbstractAccount accountOrigin = DBQuery.GetAccountFromDB(opts.AccountIdOrigin);
AbstractAccount accountDestination = DBQuery.GetAccountFromDB(opts.AccountIdDestination);
if (accountOrigin.CanBeDebited(opts.AmountToTransfer, accountDestination) && accountDestination.CanBeCredited(opts.AmountToTransfer))
{
currentCustomer.MakeNewTransaction(opts.AmountToTransfer, accountOrigin, accountDestination, DateTime.Parse(opts.DefferedDate));
}
}
else
{
IO.DisplayWarning("You can't make this deferred transfer, you aren't the account owner!");
}
}
public override bool CanBeDebited(decimal amountToTransfer, AbstractAccount accountDestination)
{
if (isDebitAuthorized(this))
{
return(true);
}
else if (Customer.IsAccountOwner(Program.currentCustomer.IdCustomer, accountDestination.AccountNumber) == false)
{
IO.DisplayWarning(this.AccountNumber + " can not credit " + accountDestination.AccountNumber);
return(false);
}
else
{
IO.DisplayWarning(this.AccountNumber + " is not authorized to debit " + accountDestination.AccountNumber);
return(false);
}
}
public static List <AbstractAccount> GetAccountsCustomer(int idCustomer)
{
string sql = " SELECT[idAccount],[idCustomer],[accountNumber],[amount],[type],[isDebitAuthorized],[creationDate], [ceiling], [overdraft], [savingsRate] FROM Account " +
"WHERE idCustomer = @idCustomer";
SqlCommand cmd = new SqlCommand();
cmd.Connection = GetConnexion;
cmd.CommandText = sql;
cmd.Parameters.Add(new SqlParameter("@idCustomer", idCustomer));
List <AbstractAccount> ListAccountsCustomer = new List <AbstractAccount>();
using (DbDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
string typeAccount = reader.GetString(reader.GetOrdinal("type"));;
AbstractAccount currentCustomerAccount = AccountFactory.Create(typeAccount);
currentCustomerAccount.IdAccount = reader.GetInt32(reader.GetOrdinal("idAccount"));
currentCustomerAccount.IdCustomer = reader.GetInt32(reader.GetOrdinal("idCustomer"));
currentCustomerAccount.AccountNumber = reader.GetString(reader.GetOrdinal("accountNumber"));
currentCustomerAccount.Amount = reader.GetDecimal(reader.GetOrdinal("amount"));
currentCustomerAccount.IsDebitAuthorized = reader.GetBoolean(reader.GetOrdinal("isDebitAuthorized"));
currentCustomerAccount.CreationDate = reader.GetDateTime(reader.GetOrdinal("creationDate"));
currentCustomerAccount.Ceiling = reader.GetDecimal(reader.GetOrdinal("ceiling"));
currentCustomerAccount.Overdraft = reader.GetDecimal(reader.GetOrdinal("overdraft"));
currentCustomerAccount.SavingsRate = reader.GetDecimal(reader.GetOrdinal("savingsRate"));
ListAccountsCustomer.Add(currentCustomerAccount);
}
}
return(ListAccountsCustomer);
}
}
public override bool CanBeDebited(decimal amountToTransfer, AbstractAccount accountDestination)
{
return(true);
}
public static List <AbstractTransaction> GetTransactionFromIdAccount(AbstractAccount currentIdAccount)
{
string sql = "SELECT [transaction].idTransaction, [transaction].idOriginAccount, [transaction].idDestinationAccount, [transaction].amount, [transaction].transactionDate, [transaction].beginDate, [transaction].endDate, [transaction].periodicity FROM [Transaction] INNER JOIN Account ON[Transaction].idOriginAccount = Account.idAccount WHERE Account.idAccount = " + currentIdAccount + ";";
return(GetTransactionFromDB(sql));
}
public static void SaveDB(AbstractAccount account)
{
IO.DisplayInformation("Account saved in DataBase");
}
public abstract bool isDebitAuthorized(AbstractAccount accountDestination);
public abstract bool CanBeDebited(decimal amountToTransfer, AbstractAccount accountDestination);