def writeSpreadsheetReport(self,reporttable,reporttype):
        
        book = Workbook(encoding="utf-8") 
        sheet1 = book.add_sheet("Household Disposable Income")
        style1 = easyxf('font: name Arial;''font: bold True;')


        #Write Table Title
        reptitle = "%s" % reporttype
        sheet1.write(0, 0, reptitle, style1) 
        sheet1.col(0).width = 2500
        
        #write Headers
        sheet1.write(2, 0, 'hhid', style1)
        
        if reporttype=='Standard of Living':
            
            sheet1.write(2, 1, 'DIncome - Below STOL', style1)
            sheet1.write(2, 2, 'DIncome - Above STOL', style1)
            sheet1.col(1).width = 6000
            sheet1.col(2).width = 6000
        else:
            sheet1.col(1).width = 4500
            sheet1.write(2, 1, 'Disposable Income', style1)

        #write Data 
        myrow = 3
        for row in reporttable:
            hhid = row[0]
            householdDI = row[1]

            sheet1.write(myrow, 0, int(hhid))
            
            
            if reporttype=='Standard of Living':
                positionmarker = row[2]
                if positionmarker == 0:
                    sheet1.write(myrow, 2, float(householdDI))
                    sheet1.write(myrow, 1, 0)
                else:
                    sheet1.write(myrow, 1, float(householdDI))
                    sheet1.write(myrow, 2, 0)
            else:
                sheet1.write(myrow, 1, float(householdDI))
                
            myrow = myrow + 1
                
        folder = "outputs/spreadsheets/income_sources/"
        filename =  folder + "openihm_DisposableIncome-" + str(time()) + ".xls"
        book.save(filename)
       
        completionmessage = '''Report Table Spreadsheet Saved As open-ihm/''' + str(filename) +'''\n\nClick OK to open the spreadsheet. This may take a few seconds. '''
        QtGui.QMessageBox.information(None, 'Report Table', completionmessage)
        filepath= os.path.relpath(filename,start='.')
        
        os.system(filepath)
    def writeSpreadsheetReport(self,reporttable,reporttype):
        
        book = Workbook(encoding="utf-8") 
        sheet1 = book.add_sheet("Households By Income Source")
        style1 = easyxf('font: name Arial;''font: bold True;')

        #Write Table Title
        reptitle = "%s" % reporttype
        sheet1.write(0, 0, reptitle, style1) 
        sheet1.col(0).width = 1500
        #write Headers
        i = 1
        if len(reporttable)!=0:
            row = reporttable[0]
            keylist = row.keys()
            keylist.sort()
            for key in keylist:
                if key !='incometotal':
                    if key =='hhid':
                        sheet1.write(2, 0, key, style1)
                        #sheet1.col(0).width = 1500
                    else:
                        sheet1.write(2, i, key,style1)
                        sheet1.col(i).width = 5500
                        i = i + 1

            #write Data 
            myrow = 3
            mycol = 1
            for row in reporttable:
                for key in keylist:
                    if key !='incometotal':
                    #for key in row.keys():
                        if row[key]:
                            value = row[key]
                        else:
                            value = 0
                        if key =='hhid':
                            sheet1.write(myrow, 0, float(value))
                        else:
                            sheet1.write(myrow, mycol, float(value))
                            mycol = mycol + 1
                mycol =1
                myrow = myrow + 1
                
        folder = "outputs/spreadsheets/income_sources/"
        filename =  folder + "openihm_incomesources-" + str(time()) + ".xls"
        book.save(filename)
       
        completionmessage = '''Report Table Spreadsheet Saved As open-ihm/''' + str(filename) +'''\n\nClick OK to open the spreadsheet. This may take a few seconds. '''
        QtGui.QMessageBox.information(None, 'Report Table', completionmessage)
        filepath= os.path.relpath(filename,start='.')
        
        os.system(filepath)
    def writeSpreadsheetReport(self,reporttable,simreporttable):
        reporttype = 'Disposable Income Simulation'
        
        book = Workbook(encoding="utf-8") 
        sheet1 = book.add_sheet("Household Disposable Income")
        style1 = easyxf('font: name Arial;''font: bold True;')


        #Write Table Title
        reptitle = "%s" % reporttype
        sheet1.write(0, 0, reptitle, style1) 
        sheet1.col(0).width = 2500
        
        #write Headers
        sheet1.write(2, 0, 'hhid', style1)
        
        sheet1.write(2, 1, 'Normal DIncome', style1)
        sheet1.write(2, 2, 'Simulation DIncome', style1)
        sheet1.col(1).width = 6000
        sheet1.col(2).width = 6000

        #write Data 
        myrow = 3
        counter = len(reporttable)
        for i in range(0,counter):
                       
            hhid = reporttable[i][0]
            householdDI = reporttable[i][1]
            simhouseholdDI = simreporttable[i][1]

            sheet1.write(myrow, 0, int(hhid))
            
            sheet1.write(myrow, 1, float(householdDI))
            sheet1.write(myrow, 2, float(simhouseholdDI))
            myrow = myrow + 1
                
        folder = "outputs/spreadsheets/income_sources/"
        filename =  folder + "openihm_SimulatedDisposableIncome-" + str(time()) + ".xls"
        book.save(filename)
       
        completionmessage = '''Report Table Spreadsheet Saved As open-ihm/''' + str(filename) +'''\n\nClick OK to open the spreadsheet. This may take a few seconds. '''
        QtGui.QMessageBox.information(None, 'Report Table', completionmessage)
        filepath= os.path.relpath(filename,start='.')
        
        os.system(filepath)
    def writeSpreadsheetReport(self,selectedHouseholds,householdMembership,householdAssets,householdCashIncome,householdFoodIncome,householdBudgetSummary):
        book = Workbook(encoding="utf-8")
        style1 = easyxf('font: name Arial;''font: bold True;' 'border: left thick, top thick, right thick, bottom thick')
        style2 = easyxf('font: name Arial;''font: colour ocean_blue;''font: bold True;''border: left thin, top thin, right thin, bottom thin')
        style3 = easyxf('font: name Arial;''border: left thin, top thin, right thin, bottom thin')
        style4 = easyxf('font: name Arial;''font: colour green;''font: bold True;''border: left thick, top thick, right thick, bottom thick')

        sheetNumber = 1
        listIndex = 0

        for hid in selectedHouseholds:
            sheetName = 'sheet' + '%s' % sheetNumber
            sheetNumber = sheetNumber + 1
            sheetTitle = 'Household Number ' + '%s' % hid
            sheetName = book.add_sheet(sheetTitle)
            
            sectionrow = self.writePageTitleSection(sheetName,style1)
            sectionrow = self.writeHouseholdMembership(householdMembership[listIndex],sheetName,sectionrow,style1,style2,style3)
            sectionrow = self.writeAssetDetails(householdAssets[listIndex],sheetName,sectionrow,style1,style2,style3)
            sectionrow = self.writeHouseholdIncome(householdCashIncome[listIndex],householdFoodIncome[listIndex],sheetName,sectionrow,style1,style2,style3)
            self.writeFinalSummary(householdBudgetSummary[listIndex],sheetName,sectionrow,style1,style2,style3)
            listIndex = listIndex + 1
            
        self.saveReport(book)
Esempio n. 5
0
    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")