238 lines
6.9 KiB
Plaintext
238 lines
6.9 KiB
Plaintext
F8L Exception -- 15 Functions:
|
|
|
|
1. Add New User
|
|
** LOCATION: new_account
|
|
Add New Account (checking/savings)
|
|
INSERT INTO account (username,balance,acctype)
|
|
VALUES ('$userName','$balance','$accountType')
|
|
|
|
** LOCATION: new_loan
|
|
Add New Loan
|
|
INSERT INTO loan (username, amount, balance, interestrate, dateopened, paymentDueDate)
|
|
VALUES ('$userName', '$balance', '$balance', .1050, Now(), Now() + INTERVAL 30 DAY)
|
|
|
|
** LOCATION: new_creditcard
|
|
Add New CreditCard
|
|
INSERT INTO creditcard (username, maxLimit, dateopened, paymentDueDate)
|
|
VALUES ('$userName', '$limit', Now(), Now() + INTERVAL 30 DAY)
|
|
|
|
2. Change Password
|
|
Reset Password
|
|
** LOCATION: change_password
|
|
|
|
3. View Account Statement (checking/savings)
|
|
*** LOCATION: inc_userFunctions
|
|
*** FUNCTION NAME: function getChecking($Login)
|
|
*** FUNCTION NAME: function getSavings($Login)
|
|
*** FUNCTION NAME: function getCredit($Login)
|
|
*** FUNCTION NAME: function getLoan($Login)
|
|
|
|
View Checking and Savings
|
|
SELECT * from account WHERE username='$userName'
|
|
|
|
View Loan
|
|
SELECT * from loan WHERE username='$userName'
|
|
|
|
View Credit Card
|
|
SELECT * from creditcard WHERE username='$userName'
|
|
|
|
|
|
4. Checking/Savings Overdraft Fee (Trigger)
|
|
charge $25 fee if balance dips below zero
|
|
DELIMITER //
|
|
CREATE TRIGGER overdraftfee
|
|
BEFORE UPDATE ON account
|
|
FOR EACH ROW
|
|
BEGIN
|
|
IF NEW.balance < 0 THEN
|
|
SET NEW.balance= NEW.balance - 25;
|
|
INSERT INTO transaction(username, accid, transtype, toID, acctype, amount)
|
|
VALUES(NEW.username, NEW.accid, 'Overdraft Fee', NEW.accid, NEW.acctype, 25);
|
|
END IF;
|
|
END //
|
|
DELIMITER ;
|
|
|
|
|
|
5. View Existing User Accounts and # of Existing Accounts
|
|
*** LOCATION: inc_adminFunctions
|
|
*** FUNCTION NAME: function getUserAccounts()
|
|
|
|
SELECT acctype, count(*) FROM(
|
|
SELECT username,acctype FROM account
|
|
UNION
|
|
SELECT username, acctype FROM creditcard
|
|
UNION
|
|
SELECT username,acctype FROM loan) acl
|
|
GROUP BY acctype;
|
|
|
|
|
|
6. Late Credit Card Payment (Trigger)
|
|
charge $10 fee for every late CC payment
|
|
DELIMITER //
|
|
CREATE TRIGGER lateccfee
|
|
BEFORE UPDATE ON CreditCard
|
|
FOR EACH ROW BEGIN
|
|
IF New.balance<Old.balance AND Old.paymentDueDate<CURDATE() THEN
|
|
SET New.balance=New.balance+10;
|
|
INSERT INTO transaction(username, accid, transtype, toID, acctype, amount)
|
|
VALUES(NEW.username, NEW.creditid, 'CC Late Payment Fee', NEW.creditid, NEW.acctype, 10);
|
|
END IF;
|
|
END //
|
|
DELIMITER ;
|
|
|
|
*** To test, first find past due credit card accounts:
|
|
SELECT username,accID,balance FROM creditcard WHERE paymentDueDate<CURDATE();
|
|
|
|
|
|
7. Low Balance (Admin - Stored Procedure)
|
|
generate a list of users (username and email) with accounts that have balances <$200
|
|
*** LOCATION: inc_adminFunctions
|
|
*** FUNCTION NAME: getLowBalance($num)
|
|
|
|
DROP PROCEDURE IF EXISTS getLowBalance;
|
|
DELIMITER //
|
|
CREATE PROCEDURE getLowBalance(IN num DOUBLE)
|
|
BEGIN
|
|
SELECT username, acctype, balance
|
|
FROM account
|
|
where balance <= num;
|
|
END //
|
|
DELIMITER ;
|
|
|
|
|
|
8. Daily Login (Admin - Stored Procedure)
|
|
generate a list of users who logged in today
|
|
*** LOCATION: inc_userFunctions
|
|
*** FUNCTION NAME: function login($username)
|
|
|
|
DELIMITER //
|
|
CREATE PROCEDURE logUser(IN user VARCHAR(30))
|
|
BEGIN
|
|
INSERT INTO log(username) VALUES(user);
|
|
END //
|
|
DELIMITER ;
|
|
|
|
|
|
9. Loyalty Program (Admin - Stored Procedure)
|
|
generate a list of users with more than $10,000 combined balance
|
|
and have been customer for over 5 years
|
|
*** LOCATION: inc_adminFunctions
|
|
*** FUNCTION NAME: getLoyalCustomers()
|
|
|
|
DROP PROCEDURE IF EXISTS loyaltyProgram;
|
|
DELIMITER //
|
|
CREATE PROCEDURE loyaltyProgram (OUT offer VARCHAR(30))
|
|
BEGIN
|
|
SELECT username FROM users
|
|
WHERE openDate<'2009-11-30'
|
|
AND username IN
|
|
(SELECT username FROM account
|
|
GROUP BY username
|
|
HAVING sum(balance)>10000);
|
|
END //
|
|
DELIMITER ;
|
|
|
|
CALL loyaltyProgram(@mylist);
|
|
SELECT @mylist;
|
|
|
|
10. List User Accounts
|
|
*** LOCATION: inc_adminFunctions
|
|
*** FUNCTION NAME: getNumAccounts()
|
|
|
|
SELECT a.username, a.acctype, numofcredit, numofloan
|
|
FROM account a
|
|
LEFT JOIN
|
|
(SELECT username, count(*) as numofcredit
|
|
FROM creditcard
|
|
GROUP BY username) c on a.username=c.username
|
|
LEFT JOIN
|
|
(SELECT username, count(*) as numofloan
|
|
FROM loan
|
|
GROUP BY username) l on c.username=l.username
|
|
ORDER BY a.username ASC;
|
|
|
|
11. Offer Credit Card > (Admin)
|
|
examines all accounts.
|
|
if account is greater than $5,000, offer a credit card.
|
|
*** LOCATION: inc_adminFunctions
|
|
*** FUNCTION NAME: getOfferCC($num)
|
|
|
|
DROP PROCEDURE IF EXISTS offerCreditCard;
|
|
DELIMITER //
|
|
CREATE PROCEDURE offerCreditCard(IN amount DOUBLE)
|
|
BEGIN
|
|
SELECT DISTINCT username, total FROM account
|
|
WHERE username IN
|
|
(SELECT username, sum(balance) as total FROM account GROUP BY username HAVING total >amount)
|
|
AND username NOT IN
|
|
(SELECT username FROM creditcard);
|
|
END //
|
|
DELIMITER ;
|
|
|
|
12. Archive Transaction table (Stored Procedure)
|
|
manually run a procedure that archives transactions older than 30 days
|
|
*** LOCATION: inc_adminFunctions
|
|
*** FUNCTION NAME: function archiveTransaction()
|
|
|
|
DROP PROCEDURE IF EXISTS archiveTransaction;
|
|
DELIMITER //
|
|
CREATE PROCEDURE archiveTransaction()
|
|
BEGIN
|
|
INSERT INTO archive(accid, username, acctype, transtype, amount, toid, date)
|
|
SELECT accid, username, acctype, transtype, amount, toid, curdate()
|
|
FROM transaction;
|
|
UPDATE transaction set updatedat = CURDATE();
|
|
END //
|
|
DELIMITER ;
|
|
|
|
|
|
13. Delete Inactive Checking/Savings Accounts (Admin)
|
|
delete all Ch/Sa accounts that have $0 balance and have not been accessed in 90 days
|
|
this delete will cascade through the Transactions table
|
|
DELIMITER //
|
|
CREATE TRIGGER deleteInactive
|
|
AFTER INSERT ON log
|
|
FOR EACH ROW BEGIN
|
|
DELETE from users
|
|
WHERE username IN
|
|
(SELECT username
|
|
FROM log
|
|
GROUP BY username
|
|
HAVING max(logindate) + INTERVAL 90 DAY < CURDATE()) and username IN
|
|
(SELECT username
|
|
FROM account
|
|
GROUP BY username
|
|
HAVING sum(balance) = 0);
|
|
END //
|
|
DELIMITER ;
|
|
|
|
|
|
14. Increase Credit Card Limit (Admin)
|
|
DROP PROCEDURE IF EXISTS increaseCCLimit;
|
|
DELIMITER //
|
|
CREATE PROCEDURE increaseCCLimit(IN amount DOUBLE)
|
|
BEGIN
|
|
SELECT c.username , c.maxlimit, sum(a.balance) as totalbalance
|
|
FROM account a right join creditcard c on a.username=c.username
|
|
GROUP BY username
|
|
HAVING totalbalance > amount;
|
|
END //
|
|
DELIMITER ;
|
|
|
|
15. Monthly Transactions Tally (Admin)
|
|
show the sum of all deposits and withdraws for one month
|
|
DROP PROCEDURE IF EXISTS monthlyDeposit;
|
|
DELIMITER //
|
|
CREATE PROCEDURE monthlyDeposit(IN aDate DATE)
|
|
BEGIN
|
|
SELECT SUM(amount) as total
|
|
FROM transaction
|
|
WHERE transtype='Deposit' and MONTH(transdate)=MONTH(aDate) and YEAR(transdate)=YEAR(aDate);
|
|
END //
|
|
DELIMITER ;
|
|
|
|
16. Display User Transactions Sorted By Date
|
|
getChecking($username);
|
|
getSavings($username);
|
|
getCredit($username);
|
|
getLoan($username); |