def getLast10Donations(outputInfo):
mycursor.execute("SELECT * FROM donations, donors WHERE donations.donor_id = donors.id ORDER BY donations.created_at DESC LIMIT 10;")
rows = mycursor.fetchall()
rows = prettyRows(rows)
outputInfo.delete("1.0", "end")
outputInfo.insert(tk.END, rows)
def removeDonation(anonymousVarRermove, amountEntryRemove, typeDonationEntryRemove, donorFirstNameEntryRemove, donorLastNameEntryRemove, sideText2Remove):
if anonymousVarRermove.get() == 0:
donorFirstName = donorFirstNameEntryRemove.get()
donorLastName = donorLastNameEntryRemove.get()
else:
donorFirstName = "anonymous"
donorLastName = "anonymous"
amount = amountEntryRemove.get()
typeDonation = typeDonationEntryRemove.get()
try:
mycursor.execute("SELECT id FROM donors WHERE firstname = '" + donorFirstName + "' and lastname = '" + donorLastName + "';")
donor_id = mycursor.fetchone()[0]
mycursor.execute("DELETE FROM donations WHERE amount = " + amount + " and type = '" + typeDonation + "' and donor_id = " + str(donor_id) + " LIMIT 1;")
amountEntryRemove.delete(0,'end')
typeDonationEntryRemove.delete(0,'end')
donorFirstNameEntryRemove.delete(0, 'end')
donorLastNameEntryRemove.delete(0, 'end')
sideText2Remove.set("donation successfully removed from database")
except:
sideText2Remove.set("could not find donation or donor")
def getAllAnonymousDonations(outputInfo):
mycursor.execute("SELECT * FROM donations WHERE donor_id = (SELECT id FROM donors WHERE firstname = 'anonymous' and lastname = 'anonymous');")
rows = mycursor.fetchall()
rows = prettyRows(rows)
outputInfo.delete("1.0", "end")
outputInfo.insert(tk.END, rows)
def getAllDonations(outputInfo):
mycursor.execute("SELECT * FROM donations, donors WHERE donations.donor_id = donors.id;")
rows = mycursor.fetchall()
rows = prettyRows(rows)
outputInfo.delete("1.0", "end")
outputInfo.insert(tk.END, rows)
def getAllInfoDonors(outputInfo):
mycursor.execute("SELECT * FROM donors;")
rows = mycursor.fetchall()
rows = prettyRows(rows)
outputInfo.delete("1.0", "end")
outputInfo.insert(tk.END, rows)
def searchCustomDonor(firstNameEntry, lastNameEntry, outputInfo):
firstName = firstNameEntry.get()
lastName = lastNameEntry.get()
mycursor.execute("SELECT * FROM donors, donations WHERE donations.donor_id = donors.id and firstname = '" + firstName + "' and lastname = '" + lastName + "';")
rows = mycursor.fetchall()
rows = prettyRows(rows)
outputInfo.delete("1.0", "end")
outputInfo.insert(tk.END, rows)
def submitInfoDonor(firstnameEntry, lastnameEntry, professionEntry, countryEntry, sideText1):
firstname, lastname, profession, country = parseInfomationDonor(firstnameEntry.get(), lastnameEntry.get(), professionEntry.get(), countryEntry.get())
try:
mycursor.execute("INSERT INTO donors (firstname, lastname, profession, country) VALUES (" + firstname + ", " +
lastname + ", " +
profession + ", " +
country + ")")
firstnameEntry.delete(0,'end')
lastnameEntry.delete(0,'end')
professionEntry.delete(0, 'end')
countryEntry.delete(0, 'end')
sideText1.set("data successfully added to the table")
except:
sideText1.set("error occured")
def removeDonor(firstnameEntry, lastnameEntry, sideText1Remove):
firstname = firstnameEntry.get()
lastname = lastnameEntry.get()
if firstname == "anonymous" and lastname == "anonymous":
sideText1Remove.set("Cannot remove this donor")
return
try:
mycursor.execute("SELECT id FROM donors WHERE firstname = '" + firstname + "' and lastname = '" + lastname + "';")
donor_id = mycursor.fetchone()
if donor_id is None:
sideText1Remove.set("could not find donor in database")
return
mycursor.execute("UPDATE donations SET donor_id = 1 WHERE donor_id = " + str(donor_id[0]) + ";")
mycursor.execute("DELETE FROM donors WHERE firstname = '" + firstname + "' and lastname = '" + lastname + "';")
firstnameEntry.delete(0,'end')
lastnameEntry.delete(0, 'end')
sideText1Remove.set("donor successfully removed from database")
except:
sideText1Remove.set("error occured")
def submitInfoDonation(anonymousVar, amountEntry, typeDonationEntry, donorFirstNameEntry, donorLastNameEntry, sideText2):
amount, typeDonation, donorFirstName, donorLastName = parseInformationDonation(anonymousVar.get(), amountEntry.get(), typeDonationEntry.get(), donorFirstNameEntry.get(), donorLastNameEntry.get())
try:
mycursor.execute("(SELECT id FROM donors WHERE firstname = " + donorFirstName + " and lastname = " + donorLastName + ")")
donor_id = mycursor.fetchone()[0]
mycursor.execute("INSERT INTO donations (amount, type, donor_id) VALUES (" + amount + ", " +
typeDonation + ", " +
str(donor_id) + " )")
amountEntry.delete(0,'end')
typeDonationEntry.delete(0,'end')
donorFirstNameEntry.delete(0, 'end')
donorLastNameEntry.delete(0, 'end')
sideText2.set("data successfully added to the table")
except:
sideText2.set("error occured")
def setUpDatabase():
"""
This method sets up the database if it does not aleady exists. It creates the database itself,
it creates the tables, the anonymous entry so that we can add anonymous donations and lastly
the two triggers related to the donors.
"""
# creates the database if not existant
mycursor.execute("CREATE DATABASE IF NOT EXISTS donations_db;")
mycursor.execute("use donations_db;")
# create tables for database
mycursor.execute("CREATE TABLE IF NOT EXISTS Donors (id INT AUTO_INCREMENT PRIMARY KEY," +
"firstname VARCHAR(255) NOT NULL," +
"lastname VARCHAR(255) NOT NULL," +
"profession VARCHAR(255) DEFAULT 'not specified'," +
"country VARCHAR(255) DEFAULT 'not specified'," +
"number_donations INT DEFAULT 0," +
"total_gifted INT DEFAULT 0," +
"created_at TIMESTAMP DEFAULT NOW()," +
"UNIQUE (firstname, lastname))")
mycursor.execute("CREATE TABLE IF NOT EXISTS Donations (id INT AUTO_INCREMENT PRIMARY KEY," +
"amount DECIMAL(65, 2) NOT NULL," +
"type VARCHAR(255) DEFAULT 'not specified'," +
"donor_id INT NOT NULL,"
"created_at TIMESTAMP DEFAULT NOW()," +
"FOREIGN KEY(donor_id) REFERENCES donors(id))")
# this is the anonymous donor for the anonymous donations
mycursor.execute("INSERT IGNORE INTO donors (firstname, lastname) VALUES ('anonymous', 'anonymous')")
# the trigger about the number of donations for each user
mycursor.execute("DROP TRIGGER IF EXISTS number_donation_add")
mycursor.execute("CREATE TRIGGER number_donation_add AFTER INSERT ON donations " +
"FOR EACH ROW " +
"BEGIN " +
"UPDATE donors " +
"SET "+
"number_donations = number_donations + 1 " +
"WHERE donors.id = NEW.donor_id; " +
"END;")
mycursor.execute("DROP TRIGGER IF EXISTS number_donation_del")
mycursor.execute("CREATE TRIGGER number_donation_del AFTER DELETE ON donations " +
"FOR EACH ROW " +
"BEGIN " +
"UPDATE donors " +
"SET "+
"number_donations = number_donations - 1 " +
"WHERE donors.id = OLD.donor_id; " +
"END;")
# trigger for counting the toal each donor has donated
mycursor.execute("DROP TRIGGER IF EXISTS total_donated_add")
mycursor.execute("CREATE TRIGGER total_donated_add AFTER INSERT ON donations " +
"FOR EACH ROW " +
"BEGIN " +
"UPDATE donors " +
"SET "+
"total_gifted = total_gifted + NEW.amount " +
"WHERE donors.id = NEW.donor_id; " +
"END;")
mycursor.execute("DROP TRIGGER IF EXISTS total_donated_del")
mycursor.execute("CREATE TRIGGER total_donated_del AFTER DELETE ON donations " +
"FOR EACH ROW " +
"BEGIN " +
"UPDATE donors " +
"SET "+
"total_gifted = total_gifted - OLD.amount " +
"WHERE donors.id = OLD.donor_id; " +
"END;")