def delete(self):
learning_material = str(request.form['learning-material'])
if learning_material == "Boy that Italian family over there sure is quiet":
db = Database()
db.delete_collection()
return "dropped"
return "not dropped"
def on_click_bt_on_sell(self):
from model.database import Database
db = Database()
db.update_player_on_sell(p_id=self.player.id, on_sell=1)
from model.my_player_store import my_player_store
my_player_store.update_from_db()
self.ON_SELL_PLAYER.emit()
def on_click_bt_delete(self):
from model.database import Database
db = Database()
db.delete_my_player(p_id=self.player.id)
from model.my_player_store import my_player_store
my_player_store.update_from_db()
self.ON_DELETE_PLAYER.emit()
def on_click_favorite(self):
from model.database import Database
db = Database()
favorite = 0 if self.player.favorite else 1
self.player.favorite = favorite
db.update_favorite(p_id=self.player.id, favorite=favorite)
self.update_ui()
self.ON_FAVORITE_CHANGED.emit()
def get_mentor_by_id(cls, idx):
db = Database()
query = """SELECT * FROM Mentor WHERE id =(?)"""
person = db.get(query, (idx, ))[0]
print(person)
new_mentor = Mentor(person[0], person[1], person[2], person[3],
person[4], person[5], person[6], person[7])
return new_mentor
def get_employee_by_id(cls, idx):
db = Database()
query = """SELECT * FROM Employee WHERE id =(?)"""
person = db.get(query, (idx, ))[0]
print(person)
new_employee = Employee(person[0], person[1], person[2], person[3],
person[4], person[5], person[6], person[7])
return new_employee
def on_click_save(self):
from model.database import Database
db = Database()
p_id = self.player.id
solding_price = int(self.sell_edit.text().replace(" ", ""))
db.update_player_solding(p_id, solding_price)
from model.my_player_store import my_player_store
my_player_store.update_from_db()
self.close()
def keyPressEvent(self, e):
if type(e) == QKeyEvent and e.key() == Qt.Key_F1:
c.ech += 1
if c.ech > 3:
c.ech = 1
from model.database import Database
db = Database()
db.update_constant(c.ech)
self.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 main():
db = Database(mode='PROD', echo=True)
if not db.has_tables():
db.make_tables()
with open('.smurfs.txt') as f:
for line in f:
fetch_and_store_summoner(line.strip(), db)
session = db.SessionMaker()
print(session.query(GameStats, GameStats.champion).all())
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
class eenheidController:
def __init__(self):
"""
:initialiseren van all klas variabelen
:param db: Hier wordt een nieuwe Database aangemaakt
"""
self.db = Database()
"""
Hier worden alle eenheden, met de bijbehorende variabelen, uit de database gehaalt.
Deze waarden worden daarna in een list gezet.
:return: de eenheden die uit de database zijn gehaalt in een list.
"""
def haal_eenheden(self):
# 0: id, 1: name, 2: type, 3: sensitivity, 4: measure_freq, 5: share_freq, 6: datetime_added, 7: manual, 8: port
resultaat = self.db.select(
"SELECT id, name, type, port, measure_freq, sensitivity FROM j_units"
)
eenheden = []
for a in resultaat:
eenheden.append(eenheid(a[0], a[1], a[2], a[3], a[4], a[5]))
for t in eenheden:
t.setup()
return eenheden
"""
Maakt een nieuwe eenheid aan met meegegeven variabelen.
:param name: de naam van de eenheid.
:param type: het type van de eenheid.
:param sensitivity: de bovengrens van de eenheid.
:param measure_freq: de meet frequentie van de eenheid.
:param manual: de mode van de eenheid.
:param port: de poort waarop de eenheid is aangesloten.
"""
def nieuwe_eenheid(self, name, type, sensitivity, measure_freq, share_freq,
manual, port):
q = "INSERT INTO j_units(name, type, sensitivity, measure_freq, share_freq, datetime_added, manual, port) VALUES (%s, %s, %s, %s, $s, CURRENT_TIMESTAMP, %s, %s)"
p = (name, type, int(sensitivity), int(measure_freq), int(share_freq),
int(manual), port)
self.db.insert(q, p)
"""
Verwijderd een eenheid met het meegegeven id.
:param id: het id van de eenheid.
"""
def verwijder_eenheid(self, id):
q = "DELETE FROM j_units WHERE id = '%s'"
self.db.delete(q, (id, ))
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
async def setUpClass(cls):
telegram_client = TelegramClient()
# A hack to ensure dispatcher exist
Dispatcher._set_singleton( # pylint: disable=W0212
telegram_client.updater.dispatcher) # pylint: disable=W0212
database_handle = Database(testing=True)
database_handle.drop_testing_database()
cls.client = Client(database_handle, telegram_client)
cls.client.start()
cls.user = TelegramUser()
await cls.user.start()
await cls.user.setup_chat()
def get(self, clientId, month):
db = Database(None)
df = Dataframe(None)
query = {"clientId": clientId, "month": month}
result = db.doQuery(self.db, self.coll, query)
orders = df.createDf(result)
if len(result) > 0:
orders = df.deleteCol(orders, "_id")
return df.toJson(orders, True)
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 on_click_save(self):
from model.database import Database
db = Database()
from utils.price import get_max_buy_price
p_id = self.player.id
buy_price = int(self.buy_edit.text().replace(" ", ""))
max_buy_price = get_max_buy_price(self.player.price)
db.add_my_player(player_id=p_id,
buy_price=buy_price,
max_buy_price=max_buy_price)
from model.my_player_store import my_player_store
my_player_store.update_from_db()
self.close()
def update_price(progress_bar):
value_progress_bar = 0
progress_bar.setValue(value_progress_bar)
player_store.players = []
for page_index in range(0, 100):
url = "https://www.futwiz.com/en/fifa19/players?page=" + str(
page_index)
query = requests.get(url)
page = query.content
html_data = BeautifulSoup(page, features="html.parser")
rows = html_data.find_all("tr", attrs={"table-row"})
for row in rows:
p = Player()
player = row.find("td", attrs={"player"})
tds = row.find_all("td", width=True)
price = None
for td in tds:
if td["width"] == "30":
price = td.string.strip()
price = int(price.replace(",", ""))
if price == 0:
continue
ovr = row.find("td", attrs={"ovr"})
gen = ovr.a.div.div.string
_type = ovr.a.div["class"][1]
_type = _type.replace("otherversion19-", "")
nom = player.find("p", attrs={"name"}).a.b.string
nom = nom.replace("'", "")
team = player.find("p", attrs={"team"})
index = 0
club = None
championnat = None
for string in team.strings:
if string and (not string.isspace()):
if index == 0:
club = string
if index == 2:
championnat = string
index += 1
p.nom = nom
p.club = club
p.championnat = championnat
p.gen = gen
p._type = _type
p.price = price
player_store.players.append(p)
from model.database import Database
db = Database()
db.update_price(nom=nom, price=price, type=_type, gen=gen)
value_progress_bar += 1
progress_bar.setValue(value_progress_bar)
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 update_from_db(self):
self.my_players = []
from model.my_player import MyPlayer
from model.database import Database
db = Database()
my_players = db.get_my_players()
for player in my_players:
new_my_player = MyPlayer(player_id=player[0],
buy_price=player[1],
max_buy_price=player[2],
on_sell=player[3],
id=player[4],
solding_price=player[5])
self.my_players.append(new_my_player)
def main():
''' Main function '''
database_handle = Database()
telegram_client = TelegramUserClient(database_handle)
client = Client(database_handle, telegram_client)
client.start()
client.idle()
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 save(self):
connector = None
try:
connector = Database.connector()
cursor = connector.cursor()
try:
sql = "insert into " + Model.table_name(self) + " ("
for k, v in Model.attributes(self).items():
value = getattr(self, k)
if k == "id" and value is Column:
continue
sql += k + ","
sql = sql[0:-1] + ") values("
for k, v in Model.attributes(self).items():
value = getattr(self, k)
if k == "id" and value is Column:
continue
if isinstance(getattr(self, k), int):
sql += str(value) + ","
else:
if value is not None and value.__class__ is not Column:
sql += "\"" + str(value) + "\","
else:
sql += "null,"
sql = sql[0:-1] + ")"
cursor.execute(sql)
connector.commit()
pass
except Exception as e:
print('type:' + str(type(e)))
print('args:' + str(e.args))
finally:
cursor.close()
finally:
connector.close()
def new_database(self):
ret = self.mainAppWindow.gamestateController.unsaved_changes()
if(not ret == QMessageBox.Cancel):
file_dialog = QFileDialog()
filename = file_dialog.getSaveFileName(self.mainAppWindow, self.mainAppWindow.trUtf8('Create New PGN'), \
None, 'PGN (*.pgn)', QFileDialog.DontUseNativeDialog)
if(filename):
if(not filename.endswith(".pgn")):
filename = filename + ".pgn"
self.model.gamestate.last_save_dir = QFileInfo(filename).dir().absolutePath()
db = Database(filename)
db.create_new_pgn()
self.mainAppWindow.save.setEnabled(False)
self.model.database = db
self.model.user_settings.active_database = db.filename
self.mainAppWindow.moves_edit_view.setFocus()
def public(self, **kwargs):
self.__assert_argument("callback", kwargs)
self.__assert_argument("maxCount", kwargs)
entries = Database.get_messages(kwargs["maxCount"])
return self.__get_jsonp_response(kwargs["callback"], entries)
def new_database(self):
ret = self.mainAppWindow.gamestateController.unsaved_changes()
if(not ret == QMessageBox.Cancel):
file_dialog = QFileDialog()
filename = file_dialog.getSaveFileName(self.mainAppWindow, self.mainAppWindow.trUtf8('Create New PGN'), \
None, 'PGN (*.pgn)', QFileDialog.DontUseNativeDialog)
if(filename):
if(not filename.endswith(".pgn")):
filename = filename + ".pgn"
self.model.gamestate.last_save_dir = QFileInfo(filename).dir().absolutePath()
db = Database(filename)
db.create_new_pgn()
self.mainAppWindow.save.setEnabled(False)
self.model.database = db
self.model.user_settings.active_database = db.filename
self.mainAppWindow.moves_edit_view.setFocus()
def index(self):
temp_vars = dict()
temp_vars.update({
'terms':
Database().query(table_name="terms", ordered_by="t_date desc")
})
return template.Template.self_render_template("sites/index.html",
temp_vars)
def on_click_edit(self):
from model.database import Database
db = Database()
p_id = self.player.id
if self.dropdown_status.currentText() == "En cours":
on_sell = 0
else:
on_sell = 1
buy_price = int(self.buy_edit.text().replace(" ", ""))
solding_price = int(self.solding_edit.text().replace(" ", ""))
db.update_my_player(p_id=p_id,
buy_price=buy_price,
solding_price= solding_price,
on_sell=on_sell)
from model.my_player_store import my_player_store
my_player_store.update_from_db()
self.close()
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 open_database(self):
dialog = QFileDialog()
if(self.model.gamestate.last_open_dir != None):
dialog.setDirectory(self.model.gamestate.last_open_dir)
filename = dialog.getOpenFileName(self.mainAppWindow.chessboard_view, self.mainAppWindow.trUtf8('Open PGN'), \
None, 'PGN (*.pgn)', QFileDialog.DontUseNativeDialog)
if filename:
db = Database(filename)
db.init_from_pgn(self.mainAppWindow,self.mainAppWindow.trUtf8("Scanning PGN File..."))
self.model.database = db
self.model.user_settings.active_database = db.filename
selectedGame = 0
if(db.no_of_games() > 1):
dlg = DialogBrowsePgn(db)
if dlg.exec_() == QDialog.Accepted:
items = dlg.table.selectedItems()
selectedGame = int(items[0].text())-1
else:
selectedGame = None
if(not selectedGame == None and db.no_of_games() > 0):
loaded_game = db.load_game(selectedGame)
self.model.gamestate.current = loaded_game
self.mainAppWindow.chessboard_view.update()
self.mainAppWindow.chessboard_view.emit(SIGNAL("statechanged()"))
self.mainAppWindow.save.setEnabled(False)
self.mainAppWindow.setLabels()
self.mainAppWindow.moves_edit_view.setFocus()
self.model.gamestate.last_open_dir = QFileInfo(filename).dir().absolutePath()
self.model.gamestate.init_game_tree(self.mainAppWindow)
self.mainAppWindow.moves_edit_view.setFocus()
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 __init__(self):
db = Database({
"type": 'postgresql',
"host": 'fg-cn-sandman1.cs.upb.de',
"port": '5432',
"database": 'decaf_storage',
"user": '******',
"password": '******'
})
#db.drop_all()
db.init_db()
# Configure logging
log_file = LOGFILE
logger = logging.getLogger(__name__)
logger.setLevel(logging.DEBUG)
fh = logging.FileHandler(log_file)
logger.addHandler(fh)
self.storage = Storage(db, logger=logger)
def cli(ctx, db_host, db_port, db_password, db_username):
try:
logger.info(f"Connecting to mysql database {db_username}@{db_host}:{db_port}")
db = Database(host=db_host, port=db_port, username=db_username, password=db_password)
logger.info("Connected to database successfully.")
ctx.obj = db
except Exception as e:
click.echo("Unable to connect to the database")
click.echo(e)
sys.exit(1)
def __init__(self):
db = Database({
"type": 'postgresql',
"host": 'fg-cn-decaf-head1.cs.upb.de',
"port": '5432',
"database": 'decaf_storage',
"user": '******',
"password": '******'
})
#db.drop_all()
db.init_db()
# Configure logging
log_file = LOGFILE
logger = logging.getLogger(__name__)
logger.setLevel(logging.DEBUG)
fh = logging.FileHandler(log_file)
logger.addHandler(fh)
self.storage = Storage(db, logger=logger)
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
class TestTableExistence(unittest.TestCase):
@classmethod
def setUpClass(self):
self.db = Database('TEST')
self.db.make_tables()
@classmethod
def tearDownClass(self):
self.db.destroy_tables()
def tearDown(self):
self.db.truncate_tables()
def test_when_all_tables_exist(self):
assert 'game_stats' in self.db.Meta.tables.keys()
assert 'game' in self.db.Meta.tables.keys()
assert 'summoner' in self.db.Meta.tables.keys()
assert 'summoner_name' in self.db.Meta.tables.keys()
def test_all_tables_have_rigth_columns(self):
t = TableTester(self.db)
test = t.has_next_table()
while test:
test()
test = t.has_next_table()
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 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 TestTableExistence(unittest.TestCase):
@classmethod
def setUpClass(self):
self.db = Database('TEST')
self.db.make_tables()
@classmethod
def tearDownClass(self):
self.db.destroy_tables()
def tearDown(self):
self.db.truncate_tables()
def test_when_all_tables_exist(self):
assert 'game_stats' in self.db.Meta.tables.keys()
assert 'game' in self.db.Meta.tables.keys()
assert 'summoner' in self.db.Meta.tables.keys()
assert 'summoner_name' in self.db.Meta.tables.keys()
def test_all_tables_have_rigth_columns(self):
t = TableTester(self.db)
test = t.has_next_table()
while test:
test()
test = t.has_next_table()
def open_database(self):
dialog = QFileDialog()
if self.model.gamestate.last_open_dir != None:
dialog.setDirectory(self.model.gamestate.last_open_dir)
filename = dialog.getOpenFileName(
self.mainAppWindow.chessboard_view,
self.mainAppWindow.trUtf8("Open PGN"),
None,
"PGN (*.pgn)",
QFileDialog.DontUseNativeDialog,
)
if filename:
db = Database(filename)
db.init_from_pgn(self.mainAppWindow, self.mainAppWindow.trUtf8("Scanning PGN File..."))
self.model.database = db
self.model.user_settings.active_database = db.filename
selectedGame = 0
if db.no_of_games() > 1:
dlg = DialogBrowsePgn(db)
if dlg.exec_() == QDialog.Accepted:
items = dlg.table.selectedItems()
selectedGame = int(items[0].text()) - 1
else:
selectedGame = None
if not selectedGame == None and db.no_of_games() > 0:
loaded_game = db.load_game(selectedGame)
self.model.gamestate.current = loaded_game
self.mainAppWindow.chessboard_view.update()
self.mainAppWindow.chessboard_view.emit(SIGNAL("statechanged()"))
self.mainAppWindow.save.setEnabled(False)
self.mainAppWindow.setLabels()
self.mainAppWindow.moves_edit_view.setFocus()
self.model.gamestate.last_open_dir = QFileInfo(filename).dir().absolutePath()
self.model.gamestate.init_game_tree(self.mainAppWindow)
self.mainAppWindow.moves_edit_view.setFocus()
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 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 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 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 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 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 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 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 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 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 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 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 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 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 setUpClass(self):
self.db = Database('TEST')
self.db.make_tables()
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 __init__(self):
self.database = Database()