def __init__(self, database: Database, tname='Messages'):
"""params:
database: Database instance
tname: name of table to store information"""
self.database = database
self.tname = tname
self.primary_key = PrimaryKey.id_as_primary().name
self.fields = MsgWithTag.to_fields() # no auto-increment id
# create table
if tname not in database.get_all_tables_name():
database.create_table(tname, PrimaryKey.id_as_primary(),
self.fields)
def read_sclasses(self, database: Database):
"""Auslesen der Wertungsklassen in die Datenbank
Args:
database (Database): Datenbank
"""
try:
self.set_worksheet(SCLASS_WORKSHEET)
reader = TableReader(self, SCLASS_HEADER, SCLASS_COLUMNS, SCLASS_REQUIRED)
database.clear_sclasses()
reader.read(lambda row, row_data: database.append_sclass(row_data))
except:
logging.exception("Fehler beim Lesen der Wertungsklassen aus der Einstellungsdatei.")
def read_stations(self, database: Database):
"""Auslesen der Stationen in die Datenbanl
Args:
database (Database): Datenbank
"""
try:
self.set_worksheet(STATION_WORKSHEET)
reader = TableReader(self, STATION_HEADER, STATION_COLUMNS, STATION_REQUIRED)
database.clear_stations()
reader.read(lambda row, row_data: database.append_station(row_data))
except:
logging.exception("Fehler beim Lesen der Stationen aus der Einstellungsdatei.")
def read_groups(self, database: Database):
"""Auslesen der Gruppen in die Datenabk
Args:
database (Database): Datenbank
"""
try:
self.set_worksheet(GROUP_WORKSHEET)
reader = TableReader(self, GROUP_HEADER, GROUP_COLUMNS, GROUP_REQUIRED)
database.clear_groups()
reader.read(lambda row, row_data: database.append_group(row_data))
except:
logging.exception("Fehler beim Lesen der Gruppen aus der Einstellungsdatei.")
def _mw_update(self, db, query, vals, etha=1e-4):
# directed query value
y = query.value(db)
r = (vals < y) * y + (vals >= y) * (1 - y)
# next Database
new_xp = np.multiply(np.exp(-etha * r), db.data)
new_x = normalize(new_xp, ord=1)
new_db = Database(new_x, 'probability', db.uni)
new_db._norm = db.norm
return new_db
def setup_database():
# intialize empty database
db = Database()
# create 'Team' table
db.create_team_table()
# create 'Player' table
# db.create_player_table()
# create 'GameStats' table
# db.create_gamestats_table()
return db
def handle_validate(update, context):
"""Handle the validation action by creating the series and the edge"""
query = update.callback_query
series_id = query.data[2:]
show_details = get_series_by_id(series_id)
db = Database.instance()
# Create the series document
# TODO Add more data
series = db.add_series(show_details["imdbID"], show_details["Title"],
show_details["Year"], show_details["Poster"],
show_details["totalSeasons"])
# Get the user document
user_id = update.callback_query.message.chat.id
user = db.users_col[user_id]
# Create an edge between the documents
if db.follow_series(user, series):
# Send success message
text = "You are now following *{}*[.]({})".format(
show_details["Title"], show_details["Poster"])
else:
text = "You already follow this *{}*. You can see the series you are following using the /followed command." \
.format(show_details["Title"])
query.edit_message_text(text, parse_mode=ParseMode.MARKDOWN)
def test_get_all_id_and_tags():
"""test get all id and tags"""
interface = DataTransfer(Database(PATH))
all_data = interface.get_all_id_and_tags()
assert len(all_data) == 2
assert all_data[0][1] == 'test 2'
assert all_data[1][1] == 'test 3'
def handle_get_seasons(update, context):
"""Handle selection of the watched season of a show"""
query = update.callback_query
# Get seasons of the show
db = Database.instance()
seasons = db.get_seasons_by_series_id(query.data[10:])
series = db.get_show_by_id(query.data[10:])
# Create button menu
button_list = [
InlineKeyboardButton("Season {}".format(season["number"]),
callback_data="isWatching" + season["_key"])
for season in seasons
]
button_list.append(InlineKeyboardButton("Cancel", callback_data="cancel"))
reply_markup = InlineKeyboardMarkup(build_menu(button_list, 2))
# Edit message text and ask user to choose a season
# TODO add all info in our arango DB (plot, actors, ...) and print it
new_text = "*{}* - {}\n\nSelect the season that you watched:".format(
series["title"], series["year"])
query.edit_message_text(new_text,
parse_mode=ParseMode.MARKDOWN,
reply_markup=reply_markup)
def test_save_msg(msg='hahah', tag='test tag', talker='admin',
expiry=None, time=None, force_create=True):
"""test save a message"""
interface = DataTransfer(Database(PATH))
msg = MsgWithTag(msg, tag, talker, expiry, time)
interface.save_msg(msg, force_create=force_create)
assert interface.get_msg_by_id(1) is not None
def handle_progress(update, context):
query = update.callback_query
show_id = query.data[8:]
user_id = query.message.chat.id
# Get progress for one show
db = Database.instance()
progress = db.get_progress(user_id, show_id)
show = db.get_show_by_id(show_id)
header = 'Here is the summary for all seasons of *{}*\n\n'.format(
show.title)
text = ''
for season in progress:
tab = progress[season]
if tab:
text += 'Season {}: '.format(season)
for ep in tab:
text += 'E{}/'.format(ep)
text = text[:-1]
text += '\n'
if text == '\n':
text = "It seems like you haven't seen any episode!"
query.edit_message_text(text=header + text, parse_mode=ParseMode.MARKDOWN)
def handle_is_watching(update, context):
"""Handle selection of the watched episode of a season of a show"""
query = update.callback_query
season_id = query.data[10:]
# Get episodes of the season
db = Database.instance()
episodes = db.get_episodes_by_season_id(season_id)
nb_episodes = len(episodes)
# Create button menu
button_list = [
InlineKeyboardButton("Episode {}".format(episode["number"]),
callback_data="logEpisode" + episode["_key"])
for episode in episodes
]
button_list.append(
InlineKeyboardButton("Episode {}".format(nb_episodes + 1),
callback_data="makeAndLog" + season_id + '.' +
str(nb_episodes + 1)))
button_list.append(InlineKeyboardButton("Cancel", callback_data="cancel"))
reply_markup = InlineKeyboardMarkup(build_menu(button_list, 2))
query.edit_message_text(text="Select the episode that you watched:",
reply_markup=reply_markup)
def test_del_by_time():
"""test delete by time stamp"""
interface = DataTransfer(Database(PATH))
data_num = len(interface.get_all_msgs())
test_save_msg(time=datetime.now() - timedelta(days=3))
interface.del_msg_by_timedelta(timedelta(days=3))
assert len(interface.get_all_msgs()) == data_num
def total_water(db_file, table):
database = Database(db_file)
if not database.table_exists(table):
database.close()
return None
query_response = database.query("SELECT total FROM " + table +
" ORDER BY total DESC;")
database.close()
return query_response[0][0]
def test_create():
"""create tag_controller"""
for path in [PATH, CONFIG]:
try:
os.remove(path)
except FileNotFoundError:
pass
controller = TagController(DataTransfer(Database(PATH, CONFIG)))
def test_not_reply():
"""not saying to the bot"""
controller = TagController(DataTransfer(Database(PATH, CONFIG)))
reply = controller.handle_msg(quoted='null',
msg='null',
talker='me',
to_bot=False)
assert not reply
def __init__(self):
# datapath = str(os.path.dirname(__file__))
# path = datapath + "/data/hilde_data.json"
# if not os.path.isfile(path):
# path = str(os.getcwd() + "/data/hilde_data.json")
# with open(path, 'r') as file:
# self.data = json.load(file)
# if "tfidf" not in data:
# print("[tfidfbot] tfidf not found in data...")
# self.corrected = { "test test": "Test?" }
# else:
# self.corrected = self.data["tfidf"]
self.db = Database()
self.tokenize_cache = {}
def test_create():
"""create table"""
for path in [PATH, 'database_config.pkl']:
try:
os.remove(path)
except FileNotFoundError:
pass
interface = DataTransfer(Database(PATH))
def test_get_msgs_by_time_range():
"""test get msgs by time range"""
interface = DataTransfer(Database(PATH))
start = datetime.now() + timedelta(days=10)
end = start + timedelta(days=20)
for i in range(10):
test_save_msg(time=start + timedelta(days=i))
assert len(interface.get_msgs_by_time_range(start, end)) == 10
class TriggerData:
def __init__(self):
self._db_file = Path.cwd() / 'config.db'
self._database = Database(str(self._db_file.resolve()))
self._table = 'triggers'
def import_data(self):
result = self._database.select(self._table)
def load(cls, folder: str) -> Competition:
"""Laden der Veranstaltung
Args:
folder (str): Ordner der Veranstaltung
Returns:
Competition: Veranstaltung
"""
errors = ErrorCollector()
# Ordner überprüfen
if not os.path.isdir(folder):
logging.error("Angegebner Ordner %s existiert nicht.", folder)
return None
# Einstellungen lesen
settings = SettingsTable(folder)
settings.open()
if errors.has_error():
errors.remove()
return None
# Datenbank erstellen, Gruppen und Stationen auslesen
database = Database(settings)
settings.read_groups(database)
settings.read_stations(database)
settings.read_sclasses(database)
if errors.has_error():
errors.remove()
return None
# Meldung auslesen
attendees_table = AttendeesTable(folder, settings, database)
attendees_table.open()
if errors.has_error():
errors.remove()
return None
database.do_grouping()
if errors.has_error():
errors.remove()
return None
return Competition(folder, settings, database)
def test_get_all_msgs():
"""test get all messages"""
test_save_msg('second', 'test 2', 'admin2', datetime(2020, 3, 26))
test_save_msg('third', 'test 3')
interface = DataTransfer(Database(PATH))
all_msg = interface.get_all_msgs()
_, msg = all_msg[0]
assert len(all_msg) == 2
assert msg.expiry == datetime(2020, 3, 26)
def test_wrong_cron_format():
"""test timed delete with wrong format"""
controller = TagController(DataTransfer(Database(PATH)))
ifreply = controller.handle_msg(quoted=None,
msg=f'{KEY_DELETE} {KEY_SPLIT}*-*-0-0-0-1',
talker='me',
to_bot=True)
assert ifreply
assert controller.reply == reply.parse_datetime_error()
def test_delete_by_time():
"""test delete by time range"""
database = Database(PATH)
data_num = len(database.select(TABLE))
start = datetime.now()
database.insert(
TABLE, ["msg", "tags", 'time'],
["delete by time", "create time", f'{str(datetime.now())}'])
end = datetime.now()
database.delete_by_time(TABLE, 'time', start, end)
assert len(database.select(TABLE)) == data_num
def load_scores(scoring_method, unweighted, unstressed):
method_mean, method_addition = SCORING_METHODS[scoring_method]
sql = "select word, score from scores where \
unweighted = {} and unstressed = {} \
and method_mean = {} and method_addition = {};".format(
unweighted, unstressed, method_mean, method_addition
)
return Database.fetch(sql)
def handle_create_episode(update, context):
query = update.callback_query
episode_id = query.data[10:]
# Get episodes of the season
db = Database.instance()
db.add_episode(episode_id)
# Link the new episode with the user (HAS_SEEN relation)
handle_log_episode(update, context)
def test_expiry():
"""test expiry of messages"""
interface = DataTransfer(Database(PATH))
controller = TagController(interface)
data_num = len(interface.get_all_msgs())
expiry = datetime.now() + timedelta(seconds=1)
msg = f'test expiry, {KEY_EXPIRY}{KEY_SPLIT}{str(expiry)}'
controller.handle_msg(quoted='second', msg=msg, talker='me', to_bot=True)
sleep(1)
assert data_num == len(interface.get_all_msgs())
def test_update():
"""test insert duplicated message with different tag"""
interface = DataTransfer(Database(PATH))
test_save_msg(msg='update message', tag='test update')
data_num = len(interface.get_all_msgs())
test_save_msg(msg='update message', tag='test update 2', force_create=False)
all_msg = interface.get_all_msgs()
assert len(all_msg) == data_num
assert all_msg[-1][1].msg == 'update message'
assert all_msg[-1][1].tags == 'test update 2'
def test_save_msg():
"""test save messaga"""
interface = DataTransfer(Database(PATH))
controller = TagController(interface)
ifreply = controller.handle_msg(quoted='first',
msg='test save msg',
talker='me',
to_bot=True)
assert ifreply
assert controller.reply == reply.save_msg_success()
assert len(interface.get_all_msgs()) == 1
def __init__(self, data_path='database.db', config_path='config.pkl'):
"""params:
data_path: path of database file,
config_path: path of database config file, which
contains auto-created information"""
self.data_path = data_path
self.config_path = config_path
self.interface = DataTransfer(Database(data_path, config_path))
self.question_answering = QuestionAnswering(self.interface)
self.tag_controller = TagController(self.interface)
self.display = Display(self.interface)
def test_delete():
"""test delete message"""
interface = DataTransfer(Database(PATH))
controller = TagController(interface)
data_num = len(interface.get_all_msgs())
ifreply = controller.handle_msg(quoted=None,
msg=f'{KEY_DELETE} {KEY_SPLIT}1',
talker='me',
to_bot=True)
assert ifreply
assert controller.reply == reply.del_msg_success(1)
assert len(interface.get_all_msgs()) == data_num - 1
def load_phonemes(unweighted, unstressed):
next_phonemes = 'next_phonemes_unweighted' if unweighted else 'next_phonemes'
sql = "select phoneme, {} from phonemes where unstressed = {};".format(
next_phonemes, unstressed
)
results = Database.fetch(sql)
output = {}
for phoneme, next_phonemes in results:
output[phoneme] = json.loads(next_phonemes)
return output
def executeQuery(self,query):
'''Execute queries under adult equivalent calculations '''
databaseConnector = Database()
databaseConnector.open()
result = databaseConnector.execSelectQuery( query )
databaseConnector.close()
return result
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'
# FIXME: the None's look hinky.
self.assertEqual([(2, u'test', None, None, u'a simple test', u'GBP')],
database.execSelectQuery(query))
database.close()
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 executeSelectQuery(self,query):
'''Run Select Query'''
dbconnector = Database()
dbconnector.open()
recset = dbconnector.execSelectQuery(query)
dbconnector.close()
return recset
def getincomeSources(self,query):
'''run various select queries'''
dbconnector = Database()
dbconnector.open()
print query
recordset = dbconnector.execSelectQuery(query)
dbconnector.close()
return recordset
def executeQuery(self,query):
'''run various select queries'''
dbconnector = Database()
dbconnector.open()
recordset = dbconnector.execSelectQuery(query)
dbconnector.close()
return recordset
def getReportHouseholdIDs(self,query):
reporthouseholdIDs=[]
databaseConnector = Database()
if query !='':
databaseConnector.open()
reporthouseholdIDs = databaseConnector.execSelectQuery( query )
databaseConnector.close()
return reporthouseholdIDs
def readProjectHouseholdsData(self,book):
'''Import Project Households'''
sheet1 = book.sheet_by_index(0)
# Start Block of code for importing a project's households
database = Database()
database.open()
for row in range(2,sheet1.nrows):
values = []
for col in range(sheet1.ncols):
skiprow =False
cell = sheet1.cell(row,col)
cellvalue = cell.value
#cellvalue = sheet1.cell(row,col).value
if cellvalue =='':
#if cellvalue =='' or (col ==3 and cell.ctype!=3):
skiprow =True
break
else:
if col == 2:
if cell.ctype == 3: #date
date_value = xldate_as_tuple(cell.value,book.datemode)
cellvalue = date(*date_value[:3])
else:
cellvalue = datetime.strptime(cellvalue, "%d-%m-%Y").strftime('%Y-%m-%d')
values.append(cellvalue)
if skiprow ==True:
continue
else:
hhid = values[0]
hholdname = values[1]
datevisited = values[2]
pid= sheet1.name
testquery ='''SELECT hhid,pid FROM households WHERE hhid='%s' AND pid =%s ''' % (hhid,self.pid)
numrows =self.checkRecordExistence(testquery)
if numrows ==0:
query ='''INSERT INTO households (hhid,householdname,dateofcollection,pid) VALUES ('%s','%s','%s',%s)''' % (hhid,hholdname,datevisited,pid)
else:
query ='''UPDATE households SET hhid='%s',householdname='%s',dateofcollection='%s',pid=%s
WHERE hhid='%s' AND pid =%s ''' % (hhid,hholdname,datevisited,pid,hhid,pid)
database.execUpdateQuery(query)
database.close()
def readBasicMemberDetails(self, householdsheet, row_index):
# print book.nsheets
start_row_index = row_index + 1
empty_cell_count = 0
hhid = householdsheet.name
print hhid
database = Database()
database.open()
for current_row_index in range(start_row_index, householdsheet.nrows):
values = []
for col_index in range(0, 4):
cellvalue = householdsheet.cell(current_row_index, col_index).value
print cellvalue
if cellvalue == "":
empty_cell_count = empty_cell_count + 1
cellvalue = None
if col_index > 0 and valueisdigit() == False:
cellvalue = None
if col_index == 3 and (cellvalue == 1 or cellvalue == "yes"):
cellvalue = "Yes"
else:
cellvalue = "No"
values.append(cellvalue)
if empty_cell_count == 4 or value == "PersonalCharacteristics": # check if entire row is empty
break
else:
sex = values[0]
age = values[1]
yearofbirth = values[2]
hhead = values[3]
personid = str(sex) + str(age)
query = """REPLACE INTO householdmembers (personid,hhid,headofhousehold,yearofbirth,sex,pid)
VALUES ('%s',%s,'%s',%s,'%s','%s',%s)""" % (
personid,
hhid,
hhead,
yearofbirth,
self.pid,
)
print query
database.execUpdateQuery(query)
empty_cell_count = 0
database.close()
def checkRecordExistence(self,testquery):
'''Test if a record with some given primary key already exists'''
database = Database()
database.open()
testrecset = database.execSelectQuery(testquery)
numrows =0
for row in testrecset:
numrows = numrows + 1
database.open()
return numrows
def readHCharacteristicsData(self,householdsheet,row_index):
'''Import Data on Household Characteristics'''
field_row_index = row_index + 1
datatype_row_index = row_index + 2
start_row_index = row_index + 3
empty_cell_count = 0
hhid = householdsheet.name
database = Database()
database.open()
#determine number of columns for pcharacteristics
columns = 0
datafields=[]
fielddatatypes=[]
for col_index in range (0,householdsheet.ncols):
datafieldvalue = householdsheet.cell(field_row_index,col_index).value
fieldtype = str(householdsheet.cell(datatype_row_index,col_index).value)
if datafieldvalue!='':
datafields.append(datafieldvalue)
fielddatatypes.append(fieldtype)
columns = columns + 1
else:
break
empty_cell_count =0
for current_row_index in range(start_row_index, householdsheet.nrows):
values = []
for col_index in range(0,columns):
exitmain = False
personid =''
cellvalue = str(householdsheet.cell(current_row_index,col_index).value)
datatype = str(householdsheet.cell(datatype_row_index,col_index).value)
if cellvalue == 'Assets':
exitmain = True
break
if cellvalue == '':
cellvalue ='NULL'
empty_cell_count = empty_cell_count + 1
if datatype=='Double':
try:
cellvalue = float(cellvalue)
except ValueError:
cellvalue = 0
elif datatype=='Integer':
try:
cellvalue = int(cellvalue)
except ValueError:
cellvalue = 0
elif datatype=='Yes/No':
try:
cellvalue = int(cellvalue)
except:
pass
tempvalue = str(cellvalue)
tempvalue = tempvalue.strip()
if tempvalue == '1' or tempvalue.lower() =='yes' or tempvalue.lower() =='y':
cellvalue = 'Yes'
else:
cellvalue = 'No'
values.append(cellvalue)
if exitmain == True or empty_cell_count==columns:
break
else:
for dataindex in range (0,len(datafields)):
paramlist=[]
characteristic = datafields[dataindex]
charvalue= values[dataindex]
testquery='''SELECT * from householdcharacteristics WHERE hhid='%s' AND pid=%s AND characteristic='%s' ''' %(hhid,self.pid,characteristic)
numrows = self.checkRecordExistence(testquery)
paramlist = (hhid,datafields[dataindex],values[dataindex])
if numrows == 0:
query = self.buildHCharInsertQuery(paramlist)
else:
query= '''DELETE FROM householdcharacteristics WHERE hhid='%s' AND pid=%s AND characteristic='%s' ''' %(hhid,self.pid,characteristic)
database.execUpdateQuery(query)
query = self.buildHCharInsertQuery(paramlist)
database.execUpdateQuery(query)
database.close()
def insertSartUpValues(self):
'''Insert food energy requirements by age and sex into table lookup_energy_needs'''
database = Database()
database.open()
deleteQuery = '''DELETE FROM lookup_energy_needs'''
database.execUpdateQuery(deleteQuery)
insertQuery = '''INSERT INTO lookup_energy_needs (age,kCalNeedM,kCalNeedF) VALUES
(0,820,820),
(1,820,820),
(2,1150,1150),
(3,1350,1350),
(4,1550,1550),
(5,1550,1550),
(6,1850,1750),
(7,1850,1750),
(8,1850,1750),
(9,1850,1750),
(10,2100,1800),
(11,2100,1800),
(12,2200,1950),
(13,2200,1950),
(14,2400,2100),
(15,2400,2100),
(16,2650,2150),
(17,2650,2150)'''
database.execUpdateQuery(insertQuery)
insertQuery = "INSERT INTO lookup_energy_needs (age,kCalNeedM,kCalNeedF) VALUES (18,2600,2600)"
for i in range(19,30):
insertQuery = insertQuery + ",(%s,2600,2600) " % i
database.execUpdateQuery(insertQuery)
insertQuery = "INSERT INTO lookup_energy_needs (age,kCalNeedM,kCalNeedF) VALUES (30,2500,2050)"
for i in range(31,60):
insertQuery = insertQuery + ",(%s,2500,2050) " % i
database.execUpdateQuery(insertQuery)
insertQuery = "INSERT INTO lookup_energy_needs (age,kCalNeedM,kCalNeedF) VALUES (60,2100,1850)"
for i in range(61,100):
insertQuery = insertQuery + ",(%s,2100,1850) " % i
database.execUpdateQuery(insertQuery)
database.close()
def readEmploymentData(self,householdsheet,row_index):
'''Import Employment Data'''
start_row_index = row_index + 2
empty_cell_count = 0
hhid = householdsheet.name
database = Database()
database.open()
for current_row_index in range(start_row_index, householdsheet.nrows):
values = []
for col_index in range(0,6):
exitmain = False
digitvalue = True
skiprow = False
cellvalue = str(householdsheet.cell(current_row_index,col_index).value)
if cellvalue == 'SocialTransfer':
exitmain = True
break
if col_index == 0 and cellvalue=='':
skiprow = True
break
if col_index!=0 and cellvalue == '':
empty_cell_count = empty_cell_count + 1
cellvalue = 'NULL'
if (col_index >=3 and col_index <=5):
try:
cellvalue = round(float(cellvalue),2)
digitvalue = True
except ValueError:
digitvalue = False
if digitvalue == False:
cellvalue = 0
values.append(cellvalue)
if exitmain == True:
break
else:
if skiprow == True: #check if at least three cell in row or cell for expenditurety are empty
continue
else:
employmenttype = values[0]
foodpaid = values[1]
unit = values[2]
unitspaid = values[3]
kcals = values[4]
cashincome = values[5]
testquery = '''SELECT * FROM employmentincome WHERE hhid='%s' AND incomesource='%s' AND pid =%s''' %(hhid,employmenttype,self.pid)
numrows = self.checkRecordExistence(testquery)
if numrows ==0:
query ='''INSERT INTO employmentincome (hhid,incomesource,foodtypepaid,unitofmeasure,unitspaid,incomekcal,cashincome,pid)
VALUES ('%s','%s','%s','%s',%s,%s,%s,%s)''' % (hhid,employmenttype,foodpaid,unit,unitspaid,kcals,cashincome,self.pid)
else:
query = '''UPDATE employmentincome SET hhid='%s',incomesource='%s',foodtypepaid='%s',unitofmeasure='%s',unitspaid=%s,incomekcal=%s,cashincome=%s,pid=%s
WHERE hhid='%s' AND incomesource='%s' AND pid =%s''' % (hhid,employmenttype,foodpaid,unit,unitspaid,kcals,cashincome,self.pid,hhid,employmenttype,self.pid)
database.execUpdateQuery(query)
empty_cell_count = 0
database.close()
def test_databaseExists(self):
database = Database()
assert database.databaseExists()
def test_close(self):
database = Database()
database.open()
database.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 test_databaseServerRunning(self):
database = Database()
assert database.databaseServerRunning()
def readTransferData(self,householdsheet,row_index,incometype):
'''Import data on social and Organisational Transfers'''
start_row_index = row_index + 2
empty_cell_count = 0
hhid = householdsheet.name
database = Database()
database.open()
for current_row_index in range(start_row_index, householdsheet.nrows):
values = []
for col_index in range(0,7):
exitmain = False
digitvalue = True
skiprow = False
cellvalue = str(householdsheet.cell(current_row_index,col_index).value)
if incometype== 'SocialTransfer' and cellvalue == 'TransferFromOrganisations':
#if cellvalue == 'TransferFromOrganisations':
exitmain = True
break
if col_index == 0 and cellvalue=='':
skiprow = True
break
if col_index!=0 and cellvalue == '':
empty_cell_count = empty_cell_count + 1
cellvalue='NotSet'
if col_index ==1 or(col_index >=4 and col_index <=7):
try:
cellvalue = float(cellvalue)
digitvalue = True
except ValueError:
digitvalue = False
if digitvalue == False:
cellvalue = 0
values.append(cellvalue)
if exitmain == True:
break
else:
if skiprow==True: #check if four cell in row or cell for expenditurety are empty
continue
else:
transfersource = values[0]
cash = values[1]
foodtype = values[2]
unit = values[3]
unitsconsumed = values[4]
unitssold= values[5]
unitprice= values[6]
if incometype=='SocialTransfer':
sourcetype='Internal'
elif incometype=='TransferFromOrganisations':
sourcetype='External'
testquery = '''SELECT * from transfers WHERE hhid='%s' AND pid=%s AND sourcetype='%s' AND sourceoftransfer='%s' ''' %(hhid,self.pid,sourcetype,transfersource)
numrows = self.checkRecordExistence(testquery)
if numrows ==0:
query ='''INSERT INTO transfers (hhid,sourcetype,sourceoftransfer,cashperyear,foodtype,unitofmeasure,unitsconsumed,unitssold,priceperunit,pid)
VALUES ('%s','%s','%s',%s,'%s','%s',%s,%s,%s,%s)''' % (hhid,sourcetype,transfersource,cash,foodtype,unit,unitsconsumed,unitssold,unitprice,self.pid)
else:
query ='''UPDATE transfers SET hhid='%s',sourcetype='%s',sourceoftransfer='%s',cashperyear=%s,foodtype='%s',unitofmeasure='%s',unitsconsumed=%s,unitssold=%s,priceperunit=%s,pid=%s
WHERE hhid='%s' AND pid=%s AND sourcetype='%s' AND sourceoftransfer='%s' ''' % (hhid,sourcetype,transfersource,cash,foodtype,unit,unitsconsumed,unitssold,unitprice,self.pid,hhid,self.pid,sourcetype,transfersource)
database.execUpdateQuery(query)
empty_cell_count = 0
database.close()
def test_open(self):
database = Database()
database.open()
def readCropAndFoodsIncomeData(self,householdsheet,row_index,incometype):
'''Import Data for Crop, Livestock, and Wildfood Income'''
start_row_index = row_index + 2
empty_cell_count = 0
hhid = householdsheet.name
database = Database()
database.open()
for current_row_index in range(start_row_index, householdsheet.nrows):
values = []
for col_index in range(0,7):
exitmain = False
digitvalue = True
skiprow = False
cellvalue = str(householdsheet.cell(current_row_index,col_index).value)
if incometype== 'Crops-C':
if cellvalue == 'Livestock-C':
exitmain = True
break
elif incometype== 'Livestock-C':
if cellvalue == 'Wildfoods-C':
exitmain = True
break
elif incometype== 'Wildfoods-C':
if cellvalue == 'Employment':
exitmain = True
break
if col_index == 0 and cellvalue=='':
skiprow = True
break
if col_index!=0 and cellvalue == '':
empty_cell_count = empty_cell_count + 1
cellvalue='NULL'
if (col_index >=2 and col_index <=6):
try:
cellvalue = float(cellvalue)
digitvalue = True
except ValueError:
digitvalue = False
if digitvalue == False:
cellvalue = 0
values.append(cellvalue)
if exitmain == True:
break
else:
if skiprow==True: #check if four cell in row or cell for expenditurety are empty
continue
else:
name = values[0]
unit = values[1]
unitsproduced = values[2]
unitssold = values[3]
unitprice = values[4]
otheruses = values[5]
unitsconsumed = values[6]
if incometype=='Crops-C':
tablename='cropincome'
elif incometype=='Livestock-C':
tablename='livestockincome'
elif incometype=='Wildfoods-C':
tablename='wildfoods'
testquery =''' SELECT * FROM %s WHERE hhid='%s' AND incomesource='%s' AND pid=%s ''' % (tablename,hhid,name,self.pid)
numrows = self.checkRecordExistence(testquery)
if numrows ==0:
query ='''INSERT INTO %s (hhid,incomesource,unitofmeasure,unitsproduced,unitssold,unitprice,otheruses,unitsconsumed,pid)
VALUES (%s,'%s','%s',%s,%s,%s,%s,%s,%s)''' % (tablename,hhid,name,unit,unitsproduced,unitssold,unitprice,otheruses,unitsconsumed,self.pid)
else:
query ='''UPDATE %s SET hhid='%s',incomesource='%s',unitofmeasure='%s',unitsproduced=%s,unitssold=%s,unitprice=%s,otheruses=%s,unitsconsumed=%s,pid=%s
WHERE hhid='%s' AND incomesource='%s' AND pid=%s ''' % (tablename,hhid,name,unit,unitsproduced,unitssold,unitprice,otheruses,unitsconsumed,self.pid,hhid,name,self.pid)
database.execUpdateQuery(query)
empty_cell_count = 0
database.close()
from xlrd import open_workbook,cellname,xldate_as_tuple
from datetime import date
from data.database import Database
book = open_workbook('projects.xls')
sheet1 = book.sheet_by_index(0)
# Start Block of code for importing a project's households
database = Database()
database.open()
for row in range(2,sheet1.nrows):
values = []
for col in range(sheet1.ncols):
value = sheet1.cell(row,col).value
cell = sheet1.cell(row,col)
if cell.ctype == 3: #date
date_value = xldate_as_tuple(cell.value,book.datemode)
value = date(*date_value[:3])
else:
value = cell.value
values.append(value)
hhid = values[0]
hholdname = values[1]
datevisited = values[2]
pid= sheet1.name
query ='''REPLACE INTO households (hhid,householdname,dateofcollection,pid) VALUES (%s,'%s','%s',%s)''' % (hhid,hholdname,datevisited,pid)
def readBasicMemberDetails(self,householdsheet,row_index):
'''Import Data on Basic Personal Characteristics: - Sex,Age,year Of Birth, and household headship status'''
start_row_index = row_index + 2
empty_cell_count = 0
hhid = householdsheet.name
database = Database()
database.open()
for current_row_index in range(start_row_index, householdsheet.nrows):
values = []
for col_index in range(0,5):
exitmain = False
skiprow =False
cellvalue = householdsheet.cell(current_row_index,col_index).value
if cellvalue == 'PersonalCharacteristics':
exitmain = True
break
if (col_index == 0 or col_index ==1) and cellvalue=='':
skiprow =True
break
try:
cellvalue = int(cellvalue)
digitvalue = True
except ValueError:
digitvalue = False
if cellvalue == '':
empty_cell_count = empty_cell_count + 1
cellvalue = 'NULL'
if (col_index ==1 or col_index ==2 or col_index ==4) and digitvalue == False:
cellvalue = 0
if col_index == 3 and (cellvalue == 1 or cellvalue.lower() =='yes' or cellvalue.lower() =='y'):
cellvalue = 'Yes'
elif col_index == 3 and (cellvalue != 1 or cellvalue.lower() !='yes' or cellvalue.lower() !='y'):
cellvalue = 'No'
values.append(cellvalue)
if exitmain == True:
break
else:
if empty_cell_count == 4 or skiprow == True: #check if entire row is empty
continue
else:
sex = str(values[0]).strip()
age = values[1]
if values[2] ==0 and age !=0:
yearofbirth = date.today().year - values[1]
elif values[2] ==0 and age ==0:
yearofbirth = date.today().year
else:
yearofbirth = values[2]
hhead = values[3]
if sex.lower() == 'male' or sex.lower() == 'm':
personid = 'm' + str(age)
sex = 'Male'
elif sex.lower() == 'female' or sex.lower() == 'f':
personid = 'f' + str(age)
sex='Female'
pidvalue = personid
periodaway = values[4]
testquery ='''SELECT * FROM householdmembers WHERE hhid='%s' AND personid ='%s' AND pid =%s ''' % (hhid,pidvalue,self.pid)
numrows =self.checkRecordExistence(testquery)
if numrows ==0:
query ='''INSERT INTO householdmembers (personid,hhid,headofhousehold,yearofbirth,sex,periodaway,pid)
VALUES ('%s','%s','%s',%s,'%s',%s,%s)''' % (personid,hhid,hhead,yearofbirth,sex,periodaway,self.pid)
else:
#personid = personid + '_' + str(numrows+1)
query = ''' UPDATE householdmembers SET headofhousehold='%s',yearofbirth=%s,sex='%s',periodaway=%s
WHERE personid='%s' AND hhid='%s' AND pid=%s''' % (hhead,yearofbirth,sex,periodaway,personid,hhid,self.pid)
database.execUpdateQuery(query)
empty_cell_count = 0
database.close()
def readAssetData(self,householdsheet,row_index):
'''Import Asset Data'''
start_row_index = row_index + 2
empty_cell_count = 0
hhid = householdsheet.name
database = Database()
database.open()
for current_row_index in range(start_row_index, householdsheet.nrows):
values = []
for col_index in range(0,5):
digitvalue = True
skiprow = False
exitmain = False
cellvalue = str(householdsheet.cell(current_row_index,col_index).value)
if cellvalue == 'Expenditure':
exitmain = True
break
if col_index == 0 and cellvalue=='':
skiprow =True
break
if cellvalue == '':
empty_cell_count = empty_cell_count + 1
cellvalue = 'NULL'
if (col_index ==3 or col_index ==4):
try:
cellvalue = float(cellvalue)
digitvalue = True
except ValueError:
digitvalue = False
if digitvalue == False:
cellvalue = 0
values.append(cellvalue)
if exitmain == True:
break
else:
if empty_cell_count >= 5 or skiprow == True: #check if entire row is empty
continue
else:
category = values[0]
assettype = values[1]
unit = values[2]
unitcost = values[3]
units = values[4]
testquery ='''SELECT * FROM assets WHERE hhid='%s' AND assetcategory='%s' AND assettype='%s' AND pid =%s ''' % (hhid,category,assettype,self.pid)
numrows =self.checkRecordExistence(testquery)
if numrows ==0:
query ='''INSERT INTO assets (hhid,assetcategory,assettype,unitofmeasure,unitcost,totalunits,pid)
VALUES ('%s','%s','%s','%s',%s,%s,%s)''' % (hhid,category,assettype,unit,unitcost,units,self.pid)
else:
query ='''UPDATE assets SET hhid='%s',assetcategory='%s',assettype='%s',unitofmeasure='%s',unitcost=%s,totalunits=%s,pid=%s
WHERE hhid='%s' AND assetcategory='%s' AND assettype='%s' AND pid =%s ''' % (hhid,category,assettype,unit,unitcost,units,self.pid,hhid,category,assettype,self.pid)
database.execUpdateQuery(query)
empty_cell_count = 0
database.close()
def readExpenditureData(self,householdsheet,row_index):
'''Import Expenditure Data'''
start_row_index = row_index + 2
empty_cell_count = 0
hhid = householdsheet.name
database = Database()
database.open()
for current_row_index in range(start_row_index, householdsheet.nrows):
values = []
for col_index in range(0,5):
exitmain = False
digitvalue = True
skiprow = False
cellvalue = str(householdsheet.cell(current_row_index,col_index).value)
if cellvalue == 'Crops-C':
exitmain = True
break
if col_index == 0 and cellvalue=='':
skiprow = True
break
if col_index!=0 and cellvalue == '':
empty_cell_count = empty_cell_count + 1
cellvalue = 'NULL'
if (col_index >=2 and col_index <=4):
try:
cellvalue = float(cellvalue)
digitvalue = True
except ValueError:
digitvalue = False
if digitvalue == False:
cellvalue = 0
values.append(cellvalue)
if exitmain == True:
break
else:
if skiprow == True: #check if at least three cell in row or cell for expenditurety are empty
continue
else:
expendituretype = values[0]
unit = values[1]
kcalperunit = values[2]
unitcost = values[3]
units = values[4]
testquery ='''SELECT * FROM expenditure WHERE hhid='%s' AND exptype='%s' AND pid =%s''' % (hhid,expendituretype,self.pid)
numrows = self.checkRecordExistence(testquery)
if numrows ==0:
query ='''INSERT INTO expenditure (hhid,exptype,unitofmeasure,priceperunit,kcalperunit,totalunits,pid)
VALUES ('%s','%s','%s',%s,%s,%s,%s)''' % (hhid,expendituretype,unit,unitcost,kcalperunit,units,self.pid)
else:
query='''UPDATE expenditure SET hhid='%s',exptype='%s',unitofmeasure='%s',priceperunit=%s,kcalperunit=%s,totalunits=%s,pid=%s
WHERE hhid='%s' AND exptype='%s' AND pid =%s ''' % (hhid,expendituretype,unit,unitcost,kcalperunit,units,self.pid,hhid,expendituretype,self.pid)
database.execUpdateQuery(query)
empty_cell_count = 0
database.close()
def __init__(self,projectid):
self.database = Database()
self.pcharstable = 'p' + str(projectid) +'PersonalCharacteristics'
self.hcharstable = 'p' + str(projectid) +'HouseholdCharacteristics'
self.pid = projectid
self.config = Config.dbinfo().copy()
class DataEntrySheets:
def __init__(self,projectid):
self.database = Database()
self.pcharstable = 'p' + str(projectid) +'PersonalCharacteristics'
self.hcharstable = 'p' + str(projectid) +'HouseholdCharacteristics'
self.pid = projectid
self.config = Config.dbinfo().copy()
def getPersonalCharacteristics(self):
query = '''SELECT characteristic, datatype FROM projectcharacteristics WHERE pid=%s and chartype='Personal' ''' %(self.pid)
self.database.open()
pchars = self.database.execSelectQuery(query)
self.database.close()
return pchars
def getHouseholdCharacteristics(self):
query = '''SELECT characteristic, datatype FROM projectcharacteristics WHERE pid=%s and chartype='Household' ''' %(self.pid)
self.database.open()
hchars = self.database.execSelectQuery(query)
self.database.close()
return hchars
# Income sources
def buildQueries(self,incometype):
query = '''SELECT incomesource FROM projectincomesources WHERE incometype='%s' AND pid=%s''' % (incometype,self.pid)
return query
def getProjectCropsFoods(self,incometype,projectincomes):
#incomesourcelist = ','.join(projectincomes)
incomes = []
recordset = []
if len(projectincomes)!= 0:
for income in projectincomes:
tempname = "'" + income[0] + "'"
incomes.append(tempname)
incomesourcelist = ','.join(incomes)
query = '''SELECT name, unitofmeasure FROM setup_foods_crops WHERE category='%s' AND name in (%s)''' % (incometype,incomesourcelist)
print query
recordset = self.getincomeSources(query)
return recordset
def getincomeSources(self,query):
'''run various select queries'''
dbconnector = Database()
dbconnector.open()
print query
recordset = dbconnector.execSelectQuery(query)
dbconnector.close()
return recordset
def getCropsFoodsIncomeSourceDetails(self,incometype):
'''Get Income-source Names and Units of Measure for Crops, Livestocks, and Wildfoods, to be displayed in data entry spreadsheet'''
incomesquery = self.buildQueries(incometype)
projectincomes = self.getincomeSources(incomesquery)
incomesourcedetails = self.getProjectCropsFoods(incometype,projectincomes)
return incomesourcedetails
def getprojetAssets(self):
query = '''SELECT assettype, assetname FROM projectassets WHERE pid=%s ORDER BY assettype, assetname''' % self.pid
self.database.open()
assets = self.database.execSelectQuery(query)
self.database.close()
return assets
def getProjectSocialTransfers(self):
query = '''SELECT incomesource FROM projectincomesources WHERE incometype ='Social Transfers' AND pid=%s ORDER BY incomesource''' % self.pid
self.database.open()
transfers = self.database.execSelectQuery(query)
self.database.close()
return transfers
def getProjectOfficialTransfers(self):
query = '''SELECT incomesource FROM projectincomesources WHERE incometype ='Official Transfers' AND pid=%s ORDER BY incomesource''' % self.pid
self.database.open()
transfers = self.database.execSelectQuery(query)
self.database.close()
return transfers
def populateSocialTranfers(self,book,style1,style2,row):
recordset = self.getProjectSocialTransfers()
sheet = book.get_sheet(3)
col = 0
#set section Headings
sheet.write(row, col, "SocialTransfer", style1)
row = row + 1
transferheadings = ["TransferSource","CashPerYear","FoodType","Unit","UnitsConsumed","UnitsSold","PricePerUnit"]
for itemheader in transferheadings:
sheet.write(row, col, itemheader, style2)
col = col + 1
row = row +1
#write transfer sources
col = 0
for rec in recordset:
celvalue = rec[col]
sheet.write(row, col, celvalue)
row = row + 1
row = row + 4 # set space between Income source type sections
return row
def populateOfficialTranfers(self,book,style1,style2,row):
recordset = self.getProjectOfficialTransfers()
sheet = book.get_sheet(3)
col = 0
#set section Headings
sheet.write(row, col, "TransferFromOrganisations", style1)
row = row + 1
transferheadings = ["TransferSource","CashPerYear","FoodType","Unit","UnitsConsumed","UnitsSold","PricePerUnit"]
for itemheader in transferheadings:
sheet.write(row, col, itemheader, style2)
col = col + 1
row = row +1
#write transfer sources
col = 0
for rec in recordset:
celvalue = rec[col]
sheet.write(row, col, celvalue)
row = row + 1
row = row + 4 # set space between Income source type sections
return row
def getAssetUnitOfMeasure(self,unitfld, tblname,assetfld,assettype):
unitofmeasure =""
query = '''SELECT %s FROM %s WHERE %s='%s' ''' % (unitfld, tblname,assetfld,assettype)
self.database.open()
assets = self.database.execSelectQuery(query)
for item in assets:
unitofmeasure = item[0]
self.database.close()
return unitofmeasure
def getAssetList(self):
assets = []
assets = self.getprojetAssets()
finalassetlist = []
for row in assets:
templist = []
for item in row:
templist.append(item)
if templist [0] =='Crops':
tblname, assetfld, unitfld = "setup_crops", "foodtype", "measuringunit"
elif templist [0] =='Land':
tblname, assetfld, unitfld = "setup_landtypes", "landtype", "unitofmeasure"
elif templist [0] =='Livestock':
tblname, assetfld, unitfld = "setup_livestock", "incomesource", "unitofmeasure"
elif templist [0] =='Tradable Goods':
tblname, assetfld, unitfld = "setup_tradablegoods", "tradablegoodtype", "unitofmeasure"
elif templist [0] =='Trees':
tblname, assetfld, unitfld = "setup_treetypes", "treetype", "measuringunit"
unitofmeasure = self.getAssetUnitOfMeasure(unitfld, tblname,assetfld,templist[1])
templist.append(unitofmeasure)
listtuple = tuple(templist)
finalassetlist.append(listtuple)
return finalassetlist
def addProjectAssets(self,book,style1,style2):
''' Populate Sheet 3 with Assets for a selected project'''
sheet3 = book.add_sheet("New Assets")
headings = ["Category","Type","Unit","UnitCost","NumberOfUnits"]
col = 0
row = 0
sheet3.write(row, col, "Assets", style1)
row = row + 1
for itemheader in headings:
sheet3.write(row, col, itemheader, style2)
col = col + 1
row = row +1
#set column width for sheet4 - Asset Columns
for i in range(0,5):
sheet3.col(i).width = 6000
def populateProjectAssetssection(self,book,style1,style2,row):
headings = ["Category","Type","Unit","UnitCost","TotalUnits"]
col = 0
sheet = book.get_sheet(3)
for itemheader in headings:
sheet.write(row, col, itemheader, style2)
col = col + 1
row = row +1
recordset = self.getAssetList()
for rec in recordset:
col = 0
assettype = rec[0]
assetname = rec[1]
unitofmeasure = rec[2]
sheet.write(row, col, assettype)
col = col + 1
sheet.write(row, col, assetname)
col = col + 1
sheet.write(row, col, unitofmeasure)
row = row + 1
return row
def writeFoodsCropsHeaders(self,sectionheading,headerrow,book,style1,style2):
headings = ["Name","Unit","UnitsProduced","UnitsSold","UnitPrice","OtherUses","UnitsConsumed"]
col = 0
sheet = book.get_sheet(3)
#if sectionheading=='Livestock':
sectionheading = sectionheading + '-C'
sheet.write(headerrow, col, sectionheading,style1)
headerrow = headerrow +1
for itemheader in headings:
sheet.write(headerrow, col, itemheader, style2)
col = col + 1
headerrow = headerrow +1
return headerrow
def populateFoodsCropsSections(self,book,style1,style2,row):
''' Populate data Entry Sheet with Crops,Livestock,Wildfoods for a selected project'''
incometypes = ['Crops','Livestock','Wildfoods']
col = 0
for incometype in incometypes:
#set section Headings
row = self.writeFoodsCropsHeaders(incometype,row,book,style1,style2)
#get incomesource details and write in spreadsheet
recordset = self.getCropsFoodsIncomeSourceDetails(incometype.lower())
sheet = book.get_sheet(3)
for rec in recordset:
for col in range (0,2):
celvalue = rec[col]
sheet.write(row, col, celvalue)
row = row + 1
row = row + 4 # set space between Income source type sections
return row
def writeEmploymentSectionHeaders(self,headerrow,book,style1,style2):
headings = ["Type","FoodPaid","Unit","UnitsPaid","Kcals","CashIncome"]
col = 0
sheet = book.get_sheet(3)
sheet.write(headerrow, 0, "Employment", style1)
headerrow = headerrow +1
for itemheader in headings:
sheet.write(headerrow, col, itemheader, style2)
col = col + 1
headerrow = headerrow +1
return headerrow
def getProjectEmploymentTypes(self):
incometype = 'employment'
query = self.buildQueries(incometype)
recordset = self.getincomeSources(query)
return recordset
def populateEmployemntDetails(self,row,book,style1,style2):
row = self.writeEmploymentSectionHeaders(row,book,style1,style2)
recordset = self.getProjectEmploymentTypes()
sheet = book.get_sheet(3)
col = 0
for rec in recordset:
celvalue = rec[col]
sheet.write(row, col, celvalue)
row = row + 1
row = row + 4 # set space between Income source type sections
return row
def populateIncomeSourcesSheet(self,book,style1,style2):
''' Populate Sheet 3 with Headers for ebtry of new income sources users may find during field visits'''
#Income Sources
sheet2 = book.add_sheet("New Income Sources")
incometypes = ['Crops','Livestock','Wildfoods']
col = 0
headerrow = 0
for incometype in incometypes:
#set section Headings
headings = ["Name","Unit","UnitsProduced","UnitsSold","UnitPrice","OtherUses","UnitsConsumed"]
col = 0
sheet2.write(headerrow, 0,incometype , style1)
headerrow = headerrow +1
for itemheader in headings:
sheet2.write(headerrow, col, itemheader, style2)
col = col + 1
headerrow = headerrow +11
#Write Employment Headings
employmentheadings = ["Type","FoodPaid","Unit","UnitsPaid","Kcals","CashIncome"]
col = 0
sheet2.write(headerrow, 0, "Employment", style1)
headerrow = headerrow +1
for itemheader in employmentheadings:
sheet2.write(headerrow, col, itemheader, style2)
col = col + 1
headerrow = headerrow +11
#Write Transfer Headers
incometypes = ['Social Transfers','Official Transfers']
col = 0
for incometype in incometypes:
#set section Headings
transferheadings = ["TransferSource","CashPerYear","FoodType","Unit","UnitsConsumed","UnitsSold","PricePerUnit"]
col = 0
sheet2.write(headerrow, 0,incometype , style1)
headerrow = headerrow +1
for itemheader in transferheadings:
sheet2.write(headerrow, col, itemheader, style2)
col = col + 1
headerrow = headerrow +11
sheet2.write(headerrow, 0, "")
#set column width for sheet1
for i in range(0,7):
sheet2.col(i).width = 6000
def writeDataSheets(self):
book = Workbook(encoding="utf-8")
#set style for headers
style1 = easyxf('font: name Arial;''font: bold True;')
style2 = easyxf('font: name Arial;''font: colour ocean_blue;''font: bold True;''border: left thick, top thick, right thick, bottom thick')
style3 = easyxf('font: name Arial;''font: colour green;''font: bold True;''border: left thick, top thick, right thick, bottom thick')
#create sheet for entering project households
sheettitle = "%s" % self.pid
sheet1 = book.add_sheet(sheettitle)
sheet1.write(0, 0, "Project Households", style1)
sheet1.write(1, 0, "HouseholdNumber", style2)
sheet1.write(1, 1, "HouseholdName", style2)
sheet1.write(1, 2, "DateVisited", style2)
#set column width for sheet1
for i in range(0,3):
sheet1.col(i).width = 6000
self.populateIncomeSourcesSheet(book,style1,style2)
self.addProjectAssets(book,style1,style2)
#Basic Details for Household Members
sheet4 = book.add_sheet("HouseholdID")
sheet4.write(1, 0, "HouseholdMembers", style1)
sheet4.write(2, 0, "Sex", style2)
sheet4.write(2, 1, "Age", style2)
sheet4.write(2, 2, "YearofBirth", style2)
sheet4.write(2, 3, "HouseholdHead", style2)
sheet4.write(2, 4, "PeriodAway", style2)
#get personal and household characteristics, configured for current project
pchars = self.getPersonalCharacteristics()
hchars = self.getHouseholdCharacteristics()
#section for extended personal characteristics
col = 0
sheet4.write(8, 0, "PersonalCharacteristics", style1)
sheet4.write(9, col, 'personid', style2)
sheet4.write(10, col, 'String', style3)
col = col + 1
for char in pchars:
value = char[0]
chartype = char[1]
vartype =''
if value!='pid' and value !='hhid':
if chartype == 1:
vartype ='Yes/No'
elif chartype == 2:
vartype ='Integer'
elif chartype == 3:
vartype ='String'
elif chartype == 4:
vartype ='Double'
sheet4.write(9, col, value, style2)
sheet4.write(10, col, vartype, style3)
col = col + 1
#section for household characteristics
sheet4.write(17, 0, "HouseholdCharacteristics", style1)
col = 0
for char in hchars:
value = char[0]
chartype = char[1]
vartype =''
if value!='pid' and value !='hhid':
if chartype == 1:
vartype ='Yes/No'
elif chartype == 2:
vartype ='Integer'
elif chartype == 3:
vartype ='String'
elif chartype == 4:
vartype ='Double'
sheet4.write(18, col, value, style2)
sheet4.write(19, col, vartype, style3)
col = col + 1
headerrow = 25
itemrow = 26
#Assets
sheet4.write(headerrow, 0, "Assets", style1)
headerrow = headerrow + 1
headerrow = self.populateProjectAssetssection(book,style1,style2,headerrow)
#Expenditure
headerrow = headerrow + 5
sheet4.write(headerrow, 0, "Expenditure", style1)
headerrow = headerrow + 1
sheet4.write(headerrow, 0, "Type", style2)
sheet4.write(headerrow, 1, "Unit", style2)
sheet4.write(headerrow, 2, "KCalPerUnit", style2)
sheet4.write(headerrow, 3, "UnitCost", style2)
sheet4.write(headerrow, 4, "Units", style2)
#Crop, Livestock, and Wildfood Income
headerrow = headerrow + 11
headerrow = self.populateFoodsCropsSections(book,style1,style2,headerrow)
#Employment
headerrow = self.populateEmployemntDetails(headerrow,book,style1,style2)
#Social Transfers
headerrow = self.populateSocialTranfers(book,style1,style2,headerrow)
#Transfers from Organisations
headerrow = self.populateOfficialTranfers(book,style1,style2,headerrow)
#set column width for sheet2
for i in range(0,7):
sheet4.col(i).width = 6000
folder = "inputs/"
filename = folder + "dataEntrySheet-ProjectID-" + str(self.pid) + ".xls"
book.save(filename)
completionmessage = '''Template Saved As open-ihm/''' + str(filename) +'''\n\nClick OK to open the spreadsheet. This may take a few seconds. '''
QtGui.QMessageBox.information(None, 'Data Entry Template', completionmessage)
#
os.system(os.path.curdir + "\\inputs\\dataEntrySheet-ProjectID-" + str(self.pid) + ".xls")
def insertSartUpValues(self):
database = Database()
database.open()
query = '''REPLACE INTO setup_foods_crops (name,category,energyvalueperunit) VALUES ('Sorghum - whole','crops', %s) ,
('Millet, whole', 'crops', %s),
('Sorghum flour', 'crops', %s),
('Wheat flour', 'crops', %s),
('Millet meal', 'crops', %s),
('Cassava fresh', 'crops', %s),
('Potato sweet', 'crops', %s),
('Cashew nut', 'crops', %s),
('Groundnut fresh', 'crops', %s),
('Leaves- dark green', 'crops', %s),
('Leaves- medium', 'crops', %s),
('Leaves - light green','crops', %s) ,
('Onion', 'crops', %s) ,
('Pumpkin', 'crops', %s) ,
('Tomato', 'crops', %s),
('Banana', 'crops', %s),
('Cashew apple', 'crops', %s) ,
('Mango', 'crops', %s),
('Papaya', 'crops', %s) ,
('Vegetable oils', 'crops', %s) ,
('Termites', 'wildfoods', %s),
('Milk, cow', 'livestock', %s) ,
('Milk, goat', 'livestock', %s) ,
('Milk, sheep', 'livestock', %s) ,
('Mice', 'wildfoods', %s),
('Rice', 'crops', %s) ,
('Ground beans', 'crops', %s) ,
('Beef', 'livestock', %s) ,
('Eggs(Hens & ducks)','livestock',%s) ,
('Meat, goat', 'livestock', %s) ,
('Meat, sheep', 'livestock', %s) ,
('Meat, poultry', 'livestock', %s),
('Meat, pig', 'livestock', %s) ,
('Soya', 'crops', %s),
('Nzama(Bambara groundnut)','crops', %s) ,
('Baobab fruit', 'wildfoods', %s) ,
('Fish', 'wildfoods', %s),
('Tamarind', 'wildfoods', %s) ,
('Okra', 'crops', %s),
('Sweet potatoes', 'crops',%s),
('Brinjal', 'crops', %s),
('Coconut(ripe nut)','wildfoods', %s) ,
('Fish(freshwater)','wildfoods', %s) ,
('Gourd', 'crops', %s) ,
('Guava', 'wildfoods', %s),
('Lentils', 'crops', %s),
('Mustard', 'crops', %s),
('Potato', 'crops', %s) ,
('Radish', 'crops', %s) ,
('Red Amaranth(leaf)','wildfoods', %s) ,
('Sugar, white', 'crops', %s) ,
('Cabbage', 'crops', %s) ,
('Groundnut, dry', 'crops', %s) ,
('Avocado, flesh', 'crops', %s) ,
('Bambara groundnut', 'crops',%s) ,
('Chillies, hot, dried', 'crops',%s) ,
('coco-yam', 'crops', %s) ,
('Cowpea', 'crops', %s) ,
('Green maize, cob','crops',%s) ,
('Millet, bullrush','crops',%s) ,
('Pigeon peas', 'crops', %s) ,
('Pigeon pea, green', 'crops',%s) ,
('sesame', 'crops', %s) ,
('Mango, medium', 'crops', %s) ,
('Maize', 'crops', %s)''' % (3550,3630,3530,3460,3650,1530,1140,5900,3320,480,280,330,480,360,200,1160,
560,630,390,9000,1480,640,710,1080,1340,3540,3670,2020,75,1450,1490,1390,3710,
3820,3670,560,500,3040,330,1140,280,400,950,480,630,3390,5440,1140,180,280,
4000,230,5790,1650,3670,2910,1000,3400,492,3630,3280,2110,5920,63,3420)
database.execUpdateQuery(query)
database.close()
def load_words():
return Database.fetch("select * from words;")
