def exportHouseholds(self, project, filename):
database = Database()
database.open()
query = '''SELECT hhid, householdname, dateofcollection FROM households
WHERE pid=%s''' % project.pid
households = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for household in households:
householdline = '''INSERT INTO households(pid,hhid,householdname,dateofcollection)
VALUES({pid},%s, '%s', '%s')<endl>\n''' % (household[0], household[1], household[2])
ihmFile.write(householdline)
ihmFile.close()
self.exportHouseholdCharacteristics(project, filename)
self.exportHouseholdAssets(project, filename)
self.exportHouseholdExpenditure(project, filename)
self.exportHouseholdCropIncome(project, filename)
self.exportHouseholdLivestockIncome(project, filename)
self.exportHouseholdWildfoodsIncome(project, filename)
self.exportHouseholdEmploymentIncome(project, filename)
self.exportHouseholdTransfersIncome(project, filename)
self.exportHouseholdMembers(project, filename)
class GlobalHouseholdCharacteristicsManager:
def __init__(self):
self.database = Database()
def getGlobalHouseholdCharacteristic(self, charid=0, charname=""):
char = GlobalHouseholdCharacteristic(charid, charname)
return char
def addGlobalHouseholdCharacteristic(self, charname, datatype):
char = GlobalHouseholdCharacteristic(0, charname, datatype)
return char
def editGlobalHouseholdCharacteristic(self, charid, charname, datatype):
char = GlobalHouseholdCharacteristic(charid)
char.setData(charname, datatype)
def delGlobalHouseholdCharacteristic(self, charid="", charname=""):
query = "DELETE FROM globalhouseholdcharacteristics WHERE id=%i OR characteristic='%s' " % (charid, charname)
self.database.open()
self.database.execUpdateQuery(query)
self.database.close()
def getGlobalHouseholdCharacteristics(self):
query = "SELECT id FROM globalhouseholdcharacteristics"
self.database.open()
rows = self.database.execSelectQuery(query)
self.database.close()
chars = []
for row in rows:
charid = row[0]
char = GlobalHouseholdCharacteristic(charid)
chars.append(char)
return chars
def getProjectsMatching(self, pid="", ptitle=""):
SQLcondition = ""
if ( pid != "" ):
SQLcondition = " WHERE pid=%s" % pid
if ( ptitle != "" ):
if ( SQLcondition == "" ):
SQLcondition = " WHERE projectname LIKE '%" + "%s" % ( ptitle ) + "%'"
else:
SQLcondition = SQLcondition + " OR projectname LIKE '%" + "%s" % ( ptitle ) + "%'"
query = ''' SELECT pid FROM projects%s''' % ( SQLcondition )
database = Database()
database.open()
rows = database.execSelectQuery( query )
database.close()
projects = []
for row in rows:
pid = row[0]
project = Project(pid)
projects.append( project )
return projects
def logIhmTransfer(self, pid, pid_access, projectname, startdate, currency ):
query = '''INSERT INTO transferlog(pid,pid_access,projectname,datecollected,currency)
VALUES(%s,%s,'%s','%s','%s')''' % (pid, pid_access, projectname, startdate, currency)
database = Database()
database.open()
database.execUpdateQuery( query )
database.close()
def submit_answer(cls, answer_text, student_id, assigment_id):
db = Database()
task_type = db.get("SELECT Task_type FROM Assignments WHERE id=(?)",
(assigment_id, ))[0][0]
if task_type == "Personal":
cls.submit_personal_answer(answer_text, student_id, assigment_id)
elif task_type == "Team":
cls.submit_team_answer(answer_text, student_id, assigment_id)
db.close()
def test_execDefinitionQuery(self):
self.helper.setup_clean_db()
database = Database()
database.open()
database.execDefinitionQuery('create table simples (test int)')
database.close()
# and just to prove it's there to put something into.
database.open()
database.execUpdateQuery('insert into simples values (3)')
database.close()
def get_student_by_id(cls, idx):
db = Database()
query = """SELECT * FROM Student WHERE id =(?)"""
person = db.get(query, (idx, ))[0]
print(person)
person_object = Student(person[0], person[1], person[2], person[3],
person[4], person[5], person[6], person[7],
person[9], person[10])
db.close()
return person_object
def get_list_of_mentors(cls):
list_of_mentors = []
db = Database()
query = """SELECT * FROM Mentor;"""
for person in db.get(query):
person_object = Mentor(person[0], person[1], person[2], person[3],
person[4], person[5], person[6], person[7])
list_of_mentors.append(person_object)
db.close()
return list_of_mentors
def get_list_of_employees(cls):
list_of_employees = []
db = Database()
query = """SELECT * FROM Employee;"""
for person in db.get(query):
person_object = Employee(person[0], person[1], person[2],
person[3], person[4], person[5],
person[6], person[7])
list_of_employees.append(person_object)
db.close()
return list_of_employees
def get_list_of_students(cls):
list_of_students = []
db = Database()
query = """SELECT * FROM Student;"""
for person in db.get(query):
person_object = Student(person[0], person[1], person[2], person[3],
person[4], person[5], person[6], person[7],
person[9], person[10])
list_of_students.append(person_object)
db.close()
return list_of_students
def save(self):
db = Database()
values = (self.name, self.surname, self.email, self.date_of_birth,
self.city, self.phone, self.login, self.user_id)
if not self.user_id:
values = values[:-1]
query = """INSERT INTO Employee(Name, Surname, Email, Date_of_birth, City, Phone, Login) VALUES (?,?,?,?,?,?,?);"""
else:
query = """UPDATE Employee SET Name=(?), Surname=(?), Email=(?), Date_of_birth=(?), City=(?), Phone=(?), Login=(?) WHERE id =(?);"""
db.set(query, values)
db.close()
def getProjects(self):
query = "SELECT pid FROM projects"
database = Database()
database.open()
rows = database.execSelectQuery( query )
database.close()
projects = []
for row in rows:
pid = row[0]
project = Project(pid)
projects.append( project )
return projects
def test_execUpdateQuery(self):
self.helper.setup_clean_db()
database = Database()
database.open()
database.execUpdateQuery("""
insert into projects
(projectname, startdate, enddate, description, currency)
values
('test', '2012-06-04', '2013-07-03', 'a simple test', 'GBP')""")
query = 'select * from projects'
self.assertEqual([(2, u'test', datetime.date(2012, 6, 4),
datetime.date(2013, 7, 3), u'a simple test', u'GBP')],
database.execSelectQuery(query))
database.close()
def get_assigment_by_id(task_id):
db = Database()
assigment_data = db.get("SELECT * FROM Assignments WHERE id=(?)",
(task_id, ))[0]
answers = db.get(
"SELECT * FROM Answers WHERE Assignment_ID=(?)",
(assigment_data[0], )) # get answers for assigment with a[0]-id
answers = [
Answer(d[0], d[1], d[2], d[3], d[4], d[5], d[6]) for d in answers
]
assigment_object = Assigment(assigment_data[0], assigment_data[1],
assigment_data[2], answers)
db.close()
return assigment_object
def submit_personal_answer(cls, answer_text, student_id, assigment_id):
db = Database()
is_answer_exist = db.get(
"SELECT EXISTS (SELECT * FROM Answers WHERE Student_ID=(?) AND Assignment_ID=(?))",
(student_id, assigment_id))
if is_answer_exist[0][0]:
db.set(
"UPDATE Answers SET Answer_text=(?) WHERE Student_ID=(?) AND Assignment_ID=(?)",
(answer_text, student_id, assigment_id))
else:
db.set(
"INSERT INTO Answers(Answer_text, Student_ID, Assignment_ID) VALUES (?,?,?)",
(answer_text, student_id, assigment_id))
db.close()
def getGlobalPersonCharacteristics(self):
query = "SELECT id FROM globalpersonalcharacteristics"
database = Database()
atabase.open()
rows = database.execSelectQuery( query )
database.close()
chars = []
for row in rows:
charid = row[0]
char = GlobalPersonCharacteristic(charid)
chars.append( char )
return chars
def submit_team_answer(cls, answer_text, student_id, assigment_id):
db = Database()
team_id = db.get("SELECT Team_ID FROM Student WHERE id=(?)",
(student_id, ))[0][0]
is_answer_exist = db.get(
"SELECT EXISTS (SELECT * FROM Answers WHERE Team_ID=(?) AND Assignment_ID=(?))",
(team_id, assigment_id))
if is_answer_exist[0][0]:
db.set(
"UPDATE Answers SET Answer_text=(?) WHERE Team_ID=(?) AND Assignment_ID=(?)",
(answer_text, team_id, assigment_id))
else:
db.set(
"INSERT INTO Answers(Answer_text, Team_ID, Assignment_ID) VALUES (?,?,?)",
(answer_text, team_id, assigment_id))
db.close()
def exportProjectIncomeSources(self, project, filename):
database = Database()
database.open()
query = '''SELECT incomesource, incometype FROM projectincomesources
WHERE pid=%s''' % project.pid
incomes = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for inc in incomes:
incomeline = '''INSERT INTO projectincomesources(pid,incomesource,incometype)
VALUES({pid},'%s','%s')<endl>\n''' % (inc[0], inc[1])
ihmFile.write(incomeline)
ihmFile.close()
def get_list_of_assigments():
db = Database()
assigments_data_list = db.get("SELECT * FROM Assignments")
assigments_objects_list = []
for a in assigments_data_list:
answers = db.get(
"SELECT * FROM Answers WHERE Assignment_ID=(?)",
(a[0], )) # get answers for assigment with a[0]-id
answers = [
Answer(d[0], d[1], d[2], d[3], d[4], d[5], d[6])
for d in answers
]
assigments_objects_list.append(Assigment(a[0], a[1], a[2],
answers))
db.close()
return assigments_objects_list
def delCorrespondingIhmProject(self, projectname, startdate, currency):
''' Delete if the project was transfered before '''
query = '''SELECT projects.pid FROM projects, transferlog WHERE projects.pid=transferlog.pid
AND transferlog.projectname='%s' AND transferlog.datecollected='%s'
AND transferlog.currency='%s' ''' % (projectname, startdate, currency)
db = Database()
db.open()
records = db.execSelectQuery( query )
for record in records:
pid = record[0]
query = '''DELETE FROM projects WHERE pid=%s''' % pid
db.execUpdateQuery( query )
db.close()
def exportHouseholdAssets(self, project, filename):
database = Database()
database.open()
query = '''SELECT hhid, assetcategory, assettype, unitofmeasure, unitcost, totalunits FROM assets
WHERE pid=%s''' % project.pid
assets = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for asset in assets:
assetline = '''INSERT INTO assets (pid, hhid, assetcategory, assettype, unitofmeasure, unitcost, totalunits )
VALUES({pid},%s,'%s','%s','%s',%s,%s)<endl>\n''' % (asset[0], asset[1], asset[2], asset[3], asset[4], asset[5])
ihmFile.write(assetline)
ihmFile.close()
def exportProjectStandardOfLiving(self, project, filename):
database = Database()
database.open()
query = '''SELECT summary, scope, gender, agebottom, agetop, item, costperyear FROM standardofliving
WHERE pid=%s''' % project.pid
stdLvs = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for stdLv in stdLvs:
stdLvline = '''INSERT INTO standardofliving (pid, summary, scope, gender, agebottom, agetop, item, costperyear )
VALUES({pid},'%s','%s','%s',%s,%s,'%s',%s)<endl>\n''' % (stdLv[0], stdLv[1], stdLv[2], stdLv[3], stdLv[4], stdLv[5], stdLv[6])
ihmFile.write(stdLvline)
ihmFile.close()
def exportProjectAssets(self, project, filename):
database = Database()
database.open()
query = '''SELECT assetname, assettype FROM projectassets
WHERE pid=%s''' % project.pid
assets = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for asset in assets:
assetline = '''INSERT INTO projectassets(pid,assetname,assettype)
VALUES({pid},'%s','%s')<endl>\n''' % (asset[0], asset[1])
ihmFile.write(assetline)
ihmFile.close()
def exportHouseholdCharacteristics(self, project, filename):
database = Database()
database.open()
query = '''SELECT hhid, characteristic, charvalue FROM householdcharacteristics
WHERE pid=%s''' % project.pid
chars = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for char in chars:
charline = '''INSERT INTO householdcharacteristics (pid,hhid, characteristic, charvalue )
VALUES({pid},%s,'%s','%s')<endl>\n''' % (char[0], char[1], char[2])
ihmFile.write(charline)
ihmFile.close()
def exportHouseholdEmploymentIncome(self, project, filename):
database = Database()
database.open()
query = '''SELECT hhid, incomesource, foodtypepaid, unitofmeasure, unitspaid, incomekcal, cashincome FROM employmentincome
WHERE pid=%s''' % project.pid
items = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for item in items:
empline = '''INSERT INTO employmentincome(pid, hhid, incomesource, foodtypepaid, unitofmeasure, unitspaid, incomekcal, cashincome)
VALUES({pid},%s,'%s','%s','%s',%s,%s,%s)<endl>\n''' % (item[0], item[1], item[2], item[3], item[4], item[5], item[6])
ihmFile.write(empline)
ihmFile.close()
def exportHouseholdWildfoodsIncome(self, project, filename):
database = Database()
database.open()
query = '''SELECT hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed FROM wildfoods
WHERE pid=%s''' % project.pid
items = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for item in items:
wfline = '''INSERT INTO wildfoods(pid, hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed)
VALUES({pid},%s,'%s','%s',%s,%s,%s,%s,%s)<endl>\n''' % (item[0], item[1], item[2], item[3], item[4], item[5], item[6], item[7])
ihmFile.write(wfline)
ihmFile.close()
def exportHouseholdCropIncome(self, project, filename):
database = Database()
database.open()
query = '''SELECT hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed FROM cropincome
WHERE pid=%s''' % project.pid
crops = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for crop in crops:
cropline = '''INSERT INTO cropincome(pid, hhid, incomesource, unitofmeasure, unitsproduced, unitssold, unitprice, otheruses, unitsconsumed)
VALUES({pid},%s,'%s','%s',%s,%s,%s,%s,%s)<endl>\n''' % (crop[0], crop[1], crop[2], crop[3], crop[4], crop[5], crop[6], crop[7])
ihmFile.write(cropline)
ihmFile.close()
def exportProjectDiet(self, project, filename):
database = Database()
database.open()
query = '''SELECT fooditem, unitofmeasure, percentage, priceperunit FROM diet
WHERE pid=%s''' % project.pid
diets = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for diet in diets:
dietline = '''INSERT INTO diet (pid, fooditem,unitofmeasure,percentage, priceperunit )
VALUES({pid},'%s','%s',%s,%s)<endl>\n''' % (diet[0], diet[1], diet[2], diet[3])
ihmFile.write(dietline)
ihmFile.close()
def existsCorrespondingIhmProject(self, projectname, startdate, currency):
''' Checks if the project was transfered before '''
query = '''SELECT projects.pid FROM projects, transferlog WHERE projects.pid=transferlog.pid
AND transferlog.projectname='%s' AND transferlog.datecollected='%s'
AND transferlog.currency='%s' ''' % (projectname, startdate, currency)
db = Database()
db.open()
records = db.execSelectQuery( query )
exists = False
if len(records) == 1:
exists = True
db.close()
return exists
def exportProjectCharacteristics(self, project, filename):
database = Database()
database.open()
query = '''SELECT characteristic, chartype, datatype FROM projectcharacteristics
WHERE pid=%s''' % project.pid
chars = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for char in chars:
charline = '''INSERT INTO projectcharacteristics (pid, characteristic, chartype, datatype )
VALUES({pid},'%s','%s',%s)<endl>\n''' % (char[0], char[1], char[2])
ihmFile.write(charline)
ihmFile.close()
def exportHouseholdTransfersIncome(self, project, filename):
database = Database()
database.open()
query = '''SELECT hhid, sourcetype, sourceoftransfer, cashperyear, foodtype, unitofmeasure, unitsgiven,
unitsconsumed, unitssold, priceperunit FROM transfers
WHERE pid=%s''' % project.pid
items = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for item in items:
transline = '''INSERT INTO transfers(pid, hhid, sourcetype, sourceoftransfer, cashperyear, foodtype, unitofmeasure, unitsgiven,
unitsconsumed, unitssold, priceperunit) VALUES({pid},%s,'%s','%s',%s,'%s','%s',%s,
%s,%s,%s)<endl>\n''' % (item[0], item[1], item[2], item[3], item[4], item[5], item[6], item[7], item[8], item[9])
ihmFile.write(transline)
ihmFile.close()
def importIhmProjectData(self, project, filename):
dbfile = file(filename, 'r')
contents = dbfile.read()
dbfile.close()
queries = contents.split('<endl>\n')
database = Database()
database.open()
for index in range(len(queries)):
if index != 0: # ignore the first row containing project (already created above)
query = queries[index].strip()
if ( query.startswith("INSERT") ):
query = query.replace("{pid}", str(project.pid) )
print str(index) + " " + query
database.execUpdateQuery( query )
database.close()
def exportHouseholdMembers(self, project, filename):
database = Database()
database.open()
query = '''SELECT hhid, personid, yearofbirth, headofhousehold, sex, education, periodaway, reason, whereto
FROM householdmembers
WHERE pid=%s''' % project.pid
members = database.execSelectQuery( query )
database.close()
ihmFile = open(filename, 'a')
for member in members:
memberline = '''INSERT INTO householdmembers(pid, hhid, personid, yearofbirth, headofhousehold, sex, education, periodaway, reason, whereto)
VALUES({pid},%s, '%s', %s,'%s','%s','%s',%s,'%s','%s')<endl>\n''' % (member[0], member[1], member[2], member[3],
member[4], member[5], member[6], member[7], member[8])
ihmFile.write(memberline)
ihmFile.close()
self.exportPersonalCharacteristics(project, filename)
def login(cls, login, passw):
db = Database()
query = ("""
SELECT * from (SELECT id, name, Login, Password, 'Student' as user_type FROM Student
UNION
SELECT id, Name, Login, Password, 'Mentor' as user_type FROM Mentor
UNION
SELECT id, Name, Login, Password, 'Employee' as user_type FROM Employee
UNION
SELECT id, Name, Login, Password, 'Manager' as user_type FROM Manager)
where Login = ? and Password = ?
""")
# awesome query, I love union <3
values = ((login, passw))
user = db.get(query, values)
if not user:
return
user = db.get(query, values)[0]
db.close()
user_dict = {'id': user[0], 'name': user[1], 'type': user[4]}
return user_dict
def test_close(self):
database = Database()
database.open()
database.close()
def delete(self):
db = Database()
query = """DELETE FROM Mentor WHERE id =(?)"""
db.set(query, (self.user_id, ))
db.close()
def delGlobalCharacteristic(self, charname=""):
database = Database()
query = "DELETE FROM globalcharacteristics WHERE characteristic='%s' " % ( charname )
database.open()
database.execUpdateQuery( query )
database.close()
def deleteProject(self, pid):
query = "DELETE FROM projects WHERE pid=%s" % pid
database = Database()
database.open()
database.execUpdateQuery( query )
database.close()
class CurrencyManager( object ):
"""Manages currencies.
Allows adding, editing, deleting and retrieval of currencies.
"""
def __init__( self ) :
self.database = Database()
self.currencies = None
self.currency_names = None
self.getCurrencies()
def existsCurrency(self, name):
"""Check if currency 'name' exists
"""
currency = Currency(currencyname=name)
if not currency.name :
return False
else:
return True
def getCurrencyByID(self, currencyid):
"""return currency currencyid from the database
(returns by currency id)
"""
currency = Currency(currencyid)
if not currency.name :
raise CurrencyError(
"Can't get Currency id %r which does not exist" % currencyid )
return currency
def getCurrencyByName(self, name):
"""return currency currencyname from the database
(returns by currency name)
"""
currency = Currency(currencyname=name)
if not currency.name :
raise CurrencyError(
"Can't get Currency '%r' which does not exist" % name )
return currency
def addCurrency(self, currencyname, abbreviation, symbol ):
"""Adds currency 'currencyname' to the currencies database.
"""
currency = Currency( 0 , currencyname, abbreviation, symbol )
self.getCurrencies() # update currency list
return currency
def editCurrency(self, currencyid, currencyname, abbreviation, symbol ):
"""Edit currency 'currencyid' providing new name, abbreviation, symbol.
"""
currency = Currency(currencyid)
if not currency.name :
raise CurrencyError(
"Can't find Currency id %r which does not exist" % currencyid )
currency.editData( currencyname, abbreviation, symbol )
self.getCurrencies() # update currency list
return
def delCurrency(self, currencyid):
"""Delete currency 'currencyid' from the database.
"""
currency = Currency(currencyid)
if not currency.name :
raise CurrencyError(
"Can't delete Currency id %r which does not exist" %currencyid )
query = "DELETE FROM currencies WHERE id=%i " % ( currencyid )
self.database.open()
self.database.execUpdateQuery( query )
self.database.close()
self.getCurrencies() # update currency list
return
def getCurrencies(self):
"""Return a list of currencies stored in the database
At same time set self.currencies to this list.
"""
query = "SELECT id FROM currencies"
self.database.open()
rows = self.database.execSelectQuery( query )
self.database.close()
self.currencies = list()
for row in rows:
curr_id = row[0]
currency = Currency(curr_id)
self.currencies.append( currency )
self.currency_names = [ x.name for x in self.currencies ]
return self.currencies
def delGlobalPersonCharacteristic(self, charid="", charname=""):
query = "DELETE FROM globalpersonalcharacteristics WHERE id=%i OR characteristic='%s' " % ( charid, charname )
database = Database()
database.open()
database.execUpdateQuery( query )
database.close()
def get_answer_by_id(cls, id):
db = Database()
task_data = db.get("SELECT * FROM Answers WHERE id=(?)", (id, ))[0]
return Answer(*task_data)
db.close()
def save(self):
db = Database()
db.set("UPDATE Answers SET Grade=(?),Grade_date=(?) WHERE ID=(?)",
(self.grade, self.grade_date, self.id))
db.close()
def add_new_assigment(name, task_type):
db = Database()
db.set("INSERT INTO Assignments(name, task_type) VALUES (?,?)",
(name, task_type))
db.close()
