def executeQuery(self,query):
'''Execute queries under adult equivalent calculations '''
databaseConnector = Database()
databaseConnector.open()
result = databaseConnector.execSelectQuery( query )
databaseConnector.close()
return result
def executeSelectQuery(self,query):
'''Run Select Query'''
dbconnector = Database()
dbconnector.open()
recset = dbconnector.execSelectQuery(query)
dbconnector.close()
return recset
def executeQuery(self,query):
'''run various select queries'''
dbconnector = Database()
dbconnector.open()
recordset = dbconnector.execSelectQuery(query)
dbconnector.close()
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 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 getReportHouseholdIDs(self,query):
reporthouseholdIDs=[]
databaseConnector = Database()
if query !='':
databaseConnector.open()
reporthouseholdIDs = databaseConnector.execSelectQuery( query )
databaseConnector.close()
return reporthouseholdIDs
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_insert():
"""insert data"""
database = Database(PATH)
database.insert(TABLE, ["msg", "tags"], ["hehe", "no use"])
database.insert(TABLE, ["msg", "tags"],
["meeting at 9:00", "meeting time"])
database.insert(TABLE, ["msg", "tags"], ["what?", "meeting time"])
assert database.search(TABLE, 'id', 2) is not None
assert len(database.search(TABLE, 'tags', 'meeting time')) == 2
database.close()
def test_execSelectQuery(self):
self.helper.setup_clean_db()
self.helper.execute_instruction("""
insert into projects
(projectname, startdate, enddate, description, currency)
values
('test', 2012-06-04, 2013-07-03, 'a simple test', 'GBP')""")
database = Database()
query = 'select * from projects'
database.open()
self.assertEqual([(2, u'test', None, None, u'a simple test', u'GBP')],
database.execSelectQuery(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 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 total_week_water(db_file, table, timestamp):
database = Database(db_file)
if not database.table_exists(table):
database.close()
return None
query_response = database.query("SELECT timestamp, total FROM " + table +
" ORDER BY timestamp ASC;")
database.close()
for i in range(len(query_response)):
if is_same_week(query_response[i][0], timestamp):
if i == 0:
return query_response[-1][1]
return query_response[-1][1] - query_response[i][1]
return 0
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 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 test_create():
"""create table"""
try:
os.remove(PATH)
except FileNotFoundError:
pass
database = Database(PATH)
primary_key = PrimaryKey.id_as_primary()
fields = [
Field("msg", "TEXT"),
Field("tags", "CHAR(50)"),
Field('time',
'CHAR(30)',
others=[f'DEFAULT (\'{str(datetime.now())}\')'])
]
database.create_table(TABLE, primary_key, fields)
assert TABLE in database.tables.keys()
database.close()
def highest_source(db_file, table):
database = Database(db_file)
if not database.table_exists(table):
database.close()
return None
sources = [
x[0]
for x in database.query("SELECT DISTINCT source FROM " + table + ";")
]
highest = 0
highest_source = sources[0]
for source in sources:
source_total = database.query("SELECT total FROM " + table +
" WHERE source='" + source +
"' ORDER BY timestamp ASC;")[-1][0]
if source_total > highest:
highest_source = source
highest = source_total
return highest_source
def total_weeks_water(db_file, table):
database = Database(db_file)
if not database.table_exists(table):
database.close()
return None
query_response = database.query("SELECT timestamp, total FROM " + table +
" ORDER BY timestamp ASC;")
database.close()
weeks = [0]
for i in range(len(query_response)):
if not is_same_week(query_response[i][0],
query_response[weeks[-1]][0]):
weeks[-1] = query_response[i - 1][1] - query_response[weeks[-1]][1]
weeks.append(i)
if i == len(query_response) - 1:
weeks.append(query_response[i][1] - query_response[i - 1][1])
break
if i == len(query_response):
weeks[-1] = query_response[i][1] - query_response[weeks[-1]][1]
return weeks
def total_day_water(db_file, table, date):
database = Database(db_file)
if not database.table_exists(table):
database.close()
return None
query_response = database.query("SELECT timestamp, total FROM " + table +
" ORDER BY timestamp ASC;")
database.close()
start_index = 0
end_index = -1
flag = False
for i in range(len(query_response)):
if datetime.fromtimestamp(
query_response[i][0]).date() == date and not flag:
start_index = i
flag = True
elif datetime.fromtimestamp(
query_response[i][0]).date() != date and flag:
end_index = i - 1
return query_response[end_index][1] - query_response[start_index][
1] if start_index != 0 else query_response[end_index][1]
return query_response[-1][1] if flag else 0
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 test_update():
"""update"""
database = Database(PATH)
database.update(TABLE, ["msg"], ["meeting at 8:00"], "id", 2)
assert database.search(TABLE, 'id', 2)[0][1] == "meeting at 8:00"
database.close()
rooms = badi.get_rooms()
for room in rooms:
flat = badi.populate_flat(room)
if badi.filter.check_requirements(badi.filters_config, flat):
db.insert(flat, "badi")
rooms = idealista.get_rooms()
for room in rooms:
flat = idealista.populate_flat(room)
if idealista.filter.check_requirements(idealista.filters_config, flat):
db.insert(flat, "idealista")
rooms = fotocasa.get_rooms()
for room in rooms:
flat = fotocasa.populate_flat(room)
if fotocasa.filter.check_requirements(fotocasa.filters_config, flat):
db.insert(flat, "fotocasa")
providers = ['badi', 'idealista', 'fotocasa']
for provider in providers:
for flat in db.select_news(provider):
message = slack.create_message(flat)
slack.send_message(message)
db.update_readed(flat['id'], provider)
db.close()
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 test_delete():
database = Database(PATH)
assert len(database.search(TABLE, 'id', 1)) == 1
database.delete(TABLE, "id", 1)
assert len(database.search(TABLE, 'id', 1)) == 0
database.close()
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 test_close(self):
database = Database()
database.open()
database.close()
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 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 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()
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 = '''SHOW columns FROM %s''' %(self.pcharstable)
self.database.open()
pchars = self.database.execSelectQuery(query)
self.database.close()
return pchars
def getHouseholdCharacteristics(self):
query = '''SHOW columns FROM %s''' %(self.hcharstable)
self.database.open()
hchars = self.database.execSelectQuery(query)
self.database.close()
return hchars
def buildQueries(self,incometype):
'''Build queries for getting project income sources'''
query = '''SELECT * FROM projectincomesources WHERE incometype='%s' ''' % incometype
return query
def getincomeSources(self,query):
'''run income source select queries'''
dbconnector = Database()
dbconnector.open()
recordset = dbconnector.execSelectQuery(query)
dbconnector.close()
return recordset
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
#projectid = self.pid
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
#Basic Details for Household Members
sheet2 = book.add_sheet("Template")
sheet2.write(1, 0, "HouseholdMembers", style1)
sheet2.write(2, 0, "Sex", style2)
sheet2.write(2, 1, "Age", style2)
sheet2.write(2, 2, "YearofBirth", style2)
sheet2.write(2, 3, "HouseholdHead", style2)
#Basic Details for Household Members
sheet3 = book.add_sheet("Income Sources")
sheet3.write(1, 0, "Crop Types", style1)
sheet3.write(1, 2, "Employment Types", style2)
sheet3.write(1, 4, "Livestock Types", style2)
sheet3.write(1, 6, "Transfer Types", style2)
sheet3.write(1, 8, "Wild Food Types", style2)
#set column width for sheet3
for i in range(0,10):
sheet3.col(i).width = 6000
#get personal and household characteristics, configured for current project
pchars = self.getPersonalCharacteristics()
hchars = self.getHouseholdCharacteristics()
#section for extended personal characteristics
sheet2.write(8, 0, "PersonalCharacteristics", style1)
col = 0
for char in pchars:
value = char[0]
typep = char[1]
if value!='pid' and value !='hhid':
stringvar = 'varchar'
boolvar = 'enum'
intvar = 'bigint'
doublevar ='double'
if typep.startswith(tuple(stringvar)):
vartype ='String'
elif typep.startswith(tuple(boolvar)):
vartype ='Yes/No'
elif typep.startswith(tuple(intvar)):
vartype ='Integer'
elif typep.startswith(tuple(doublevar)):
vartype ='Double'
sheet2.write(9, col, value, style2)
sheet2.write(10, col, vartype, style3)
col = col + 1
#section for household characteristics
sheet2.write(17, 0, "HouseholdCharacteristics", style1)
col = 0
for char in hchars:
value = char[0]
typep = char[1]
if value !='pid' and value !='hhid':
stringvar = 'varchar'
boolvar = 'enum'
intvar = 'bigint'
doublevar ='double'
if typep.startswith(tuple(stringvar)):
vartype ='String'
elif typep.startswith(tuple(boolvar)):
vartype ='Yes/No'
elif typep.startswith(tuple(intvar)):
vartype ='Integer'
elif typep.startswith(tuple(doublevar)):
vartype ='Double'
sheet2.write(18, col, value, style2)
sheet2.write(19, col, vartype, style3)
col = col + 1
headerrow = 25
itemrow = 26
#Assets
sheet2.write(headerrow, 0, "Assets", style1)
sheet2.write(itemrow, 0, "Category", style2)
sheet2.write(itemrow, 1, "Type", style2)
sheet2.write(itemrow, 2, "Unit", style2)
sheet2.write(itemrow, 3, "UnitCost", style2)
sheet2.write(itemrow, 4, "Units", style2)
#Expenditure
headerrow = headerrow + 11
itemrow = itemrow + 11
sheet2.write(headerrow, 0, "Expenditure", style1)
sheet2.write(itemrow, 0, "Type", style2)
sheet2.write(itemrow, 1, "Unit", style2)
sheet2.write(itemrow, 2, "KCalPerUnit", style2)
sheet2.write(itemrow, 3, "UnitCost", style2)
sheet2.write(itemrow, 4, "Units", style2)
#Crop Income
headerrow = headerrow + 11
itemrow = itemrow + 11
sheet2.write(headerrow, 0, "Crops", style1)
sheet2.write(itemrow, 0, "Name", style2)
sheet2.write(itemrow, 1, "Unit", style2)
sheet2.write(itemrow, 2, "UnitsProduced", style2)
sheet2.write(itemrow, 3, "UnitsSold", style2)
sheet2.write(itemrow, 4, "UnitPrice", style2)
sheet2.write(itemrow, 5, "OtherUses", style2)
sheet2.write(itemrow, 6, "UnitsConsumed", style2)
#Livestock
headerrow = headerrow + 11
itemrow = itemrow + 11
sheet2.write(headerrow, 0, "Livestock", style1)
sheet2.write(itemrow, 0, "Name", style2)
sheet2.write(itemrow, 1, "Unit", style2)
sheet2.write(itemrow, 2, "UnitsProduced", style2)
sheet2.write(itemrow, 3, "UnitsSold", style2)
sheet2.write(itemrow, 4, "UnitPrice", style2)
sheet2.write(itemrow, 5, "OtherUses", style2)
sheet2.write(itemrow, 6, "UnitsConsumed", style2)
#WildFoods
headerrow = headerrow + 11
itemrow = itemrow + 11
sheet2.write(headerrow, 0, "WildFoods", style1)
sheet2.write(itemrow, 0, "Name", style2)
sheet2.write(itemrow, 1, "Unit", style2)
sheet2.write(itemrow, 2, "UnitsProduced", style2)
sheet2.write(itemrow, 3, "UnitsSold", style2)
sheet2.write(itemrow, 4, "UnitPrice", style2)
sheet2.write(itemrow, 5, "OtherUses", style2)
sheet2.write(itemrow, 6, "UnitsConsumed", style2)
#Employment
headerrow = headerrow + 11
itemrow = itemrow + 11
sheet2.write(headerrow, 0, "Employment", style1)
sheet2.write(itemrow, 0, "Type", style2)
sheet2.write(itemrow, 1, "FoodPaid", style2)
sheet2.write(itemrow, 2, "Unit", style2)
sheet2.write(itemrow, 3, "UnitsPaid", style2)
sheet2.write(itemrow, 4, "Kcals", style2)
sheet2.write(itemrow, 5, "CashIncome", style2)
#Social Transfers
headerrow = headerrow + 11
itemrow = itemrow + 11
sheet2.write(headerrow, 0, "SocialTransfer", style1)
sheet2.write(itemrow, 0, "TransferSource", style2)
sheet2.write(itemrow, 1, "CashPerYear", style2)
sheet2.write(itemrow, 2, "FoodType", style2)
sheet2.write(itemrow, 3, "Unit", style2)
sheet2.write(itemrow, 4, "UnitsConsumed", style2)
sheet2.write(itemrow, 5, "UnitsSold", style2)
sheet2.write(itemrow, 6, "PricePerUnit", style2)
#Transfers from Organisations
headerrow = headerrow + 11
itemrow = itemrow + 11
sheet2.write(headerrow, 0, "TransferFromOrganisations", style1)
sheet2.write(itemrow, 0, "TransferSource", style2)
sheet2.write(itemrow, 1, "CashPerYear", style2)
sheet2.write(itemrow, 2, "FoodType", style2)
sheet2.write(itemrow, 3, "Unit", style2)
sheet2.write(itemrow, 4, "UnitsConsumed", style2)
sheet2.write(itemrow, 5, "UnitsSold", style2)
sheet2.write(itemrow, 6, "PricePerUnit", style2)
#set column width for sheet2
for i in range(0,7):
sheet2.col(i).width = 6000
# Income sources
incometypes = ['crops','employment','livestock','transfers','wildfoods']
col = 0
print 'there'
for i in range (0,5):
incometype = incometypes[i]:
row = 1
query = self.buildQueries(incometype)
print incometype
recordset = self.getincomeSources(query)
for row in recordset:
cellvalue = row[0]
sheet3.write(row, col, cellvalue, style1)
row = row + 1
col = col + 2
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")
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")
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)
database.execUpdateQuery(query)
#self.insertValuesInDB(hhid,hholdname,datevisited,pid)
database.close()
'''for sheet in range(2,book.sheets):
huseid = sheet.name
projectsheet = book.sheet_by_index(0)
projectid = projectsheet.name'''
#def insertValuesInDB(hhid,hholdname,datevisited,pid):
#query ='''REPLACE INTO households (hhid,householdname,dateofcollection,pid) VALUES (%s,'%s',%s,%s)''' % (hhid,hholdname,datevisited,pid)
#database.execUpdateQuery(query)
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 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()
class HouseholdsByCharacteristics:
def __init__(self):
self.database = Database()
def buildPCharacteristicsQuery(self,pcharacteristics, tablename,projectid):
''' build query for selecting households that meet selected personal characteristics from the report interface'''
houseid = tablename + '.hhid'
basequery = '''SELECT households.pid,households.hhid, households.householdname
FROM households,personalcharacteristics WHERE households.pid=%s AND households.hhid = personalcharacteristics.hhid AND households.pid=personalcharacteristics.pid''' % projectid
for currentcharacteristic in pcharacteristics:
#currentcharacteristic = 'personalcharacteristics' + '.' + '%s' % characteristic
basequery = basequery + " AND personalcharacteristics.characteristic ='%s' AND personalcharacteristics.charvalue='Yes'" % (currentcharacteristic)
basequery = basequery + " GROUP BY households.pid,households.hhid"
print basequery
return basequery
def buildHCharacteristicsQuery(self,hcharacteristics, tablename,projectid):
''' build query for selecting households that meet selected household characteristics from the report interface'''
#settingsmanager = ReportsSettingsManager()
houseid = tablename + '.hhid'
basequery = basequery = '''SELECT households.pid,households.hhid, households.householdname
FROM households,householdcharacteristics WHERE households.pid=%s AND households.hhid = householdcharacteristics.hhid AND households.pid=householdcharacteristics.pid''' % projectid
for currentcharacteristic in hcharacteristics:
#currentcharacteristic = 'householdcharacteristics' + '.' + '%s' % characteristic
basequery = basequery + " AND householdcharacteristics.characteristic ='%s' AND householdcharacteristics.charvalue='Yes'" % (currentcharacteristic)
basequery = basequery + " GROUP BY households.pid,households.hhid"
print basequery
return basequery
def getReportTable(self,projectid,pcharselected,hcharselected,pquery,hquery):
''' generate report tables'''
pcharstable = self.getPcharacteristicsTable(pquery)
hcharstable = self.getHcharacteristicsTable(hquery)
x = len(pcharstable)
y = len(hcharstable)
reporttable = []
if (x ==0 and y== 0)or (x == 0 and pcharselected !=0)or (y == 0 and hcharselected !=0):
return reporttable
elif (x !=0 and y != 0):
query = ''' SELECT * FROM (%s UNION ALL %s) AS tbl GROUP BY tbl.hhid HAVING COUNT(*) = 2''' % (pquery,hquery)
reporttable = self.getFinalReportTableData(query)
print reporttable
return reporttable
elif (x == 0 and pcharselected ==0):
return hcharstable
elif (y == 0 and hcharselected ==0):
return pcharstable
def getPcharacteristicsTable(self,pquery):
''' get households where selected personal characteristics from the interface are met'''
self.database.open()
print pquery
ptable = self.database.execSelectQuery( pquery )
self.database.close()
return ptable
def getHcharacteristicsTable(self,hquery):
''' get households where selected household characteristics from the interface are met'''
self.database.open()
htable = self.database.execSelectQuery( hquery )
self.database.close()
return htable
def getFinalReportTableData(self, query):
'''get reporttable where user has selected both household and personal characteristics as output criteria'''
self.database.open()
reporttable = self.database.execSelectQuery( query )
self.database.close()
return reporttable
def getHouseholdsForReport(self):
pass
