예제 #1
0
    def loadSubgrpdb(self):
        self.sheets["substance groups"] = self.createSheet("substance groups")
        self.subgrpdb = Subgrpdb(self.edb)
        page = self.getPage(
            ["read", self.domain, self.edb.user, self.edb.name, "subgrpdb"])
        self.subgrpdb.read(fileObject=page)
        #            filename="/local_disk/dvlp/airviro/loairviro/test/subgrp.out")

        substances = []
        for subgrpInd, subgrp in self.subgrpdb.subgrps.items():
            for subst in subgrp.substances:
                substName = self.subdb.substNames[subst]
                if substName not in substances:
                    substances.append(substName)

        header1 = ("", "")
        header2 = ("Index", "Name")
        for substName in substances:
            header1 += (substName, "", "")
            header2 += ("Slope", "Offset", "Unit")

        out = (header1, header2)
        for subgrpInd, subgrp in self.subgrpdb.subgrps.items():
            #list names of substances in subgrp
            substInSubgrp = [self.subdb[s] for s in subgrp.substances]
            row = (subgrp.index, subgrp.name)
            for substName in substances:
                if substName in substInSubgrp:
                    substIndex = self.subdb.substIndices[substName]
                    slope = subgrp.substances[substIndex]["slope"]
                    offset = subgrp.substances[substIndex]["offset"]
                    unit = subgrp.substances[substIndex]["unit"]
                else:
                    slope = ""
                    offset = ""
                    unit = ""
                row += (slope, offset, unit)
            out += (row, )

        cellRange = self.sheets["substance groups"].getCellRangeByPosition(
            0, 0,
            len(header1) - 1,
            len(out) - 1)
        cellRange.setDataArray(out)
예제 #2
0
    def loadSubgrpdb(self):
        self.sheets["substance groups"] = self.createSheet("substance groups")
        self.subgrpdb=Subgrpdb(self.edb)      
        page=self.getPage(["read",self.domain,self.edb.user,self.edb.name,
                           "subgrpdb"])
        self.subgrpdb.read(fileObject=page)
#            filename="/local_disk/dvlp/airviro/loairviro/test/subgrp.out")
        
        substances=[]
        for subgrpInd,subgrp in self.subgrpdb.subgrps.items():
            for subst in subgrp.substances:
                substName=self.subdb.substNames[subst]
                if substName not in substances:
                    substances.append(substName)

        header1=("","")
        header2=("Index","Name")
        for substName in substances:
            header1+=(substName,"","")
            header2+=("Slope","Offset","Unit")

        out=(header1,header2)
        for subgrpInd,subgrp in self.subgrpdb.subgrps.items():
            #list names of substances in subgrp
            substInSubgrp=[self.subdb[s] for s in subgrp.substances]
            row=(subgrp.index,subgrp.name)
            for substName in substances:
                if substName in substInSubgrp:
                    substIndex=self.subdb.substIndices[substName]
                    slope=subgrp.substances[substIndex]["slope"]
                    offset=subgrp.substances[substIndex]["offset"]
                    unit=subgrp.substances[substIndex]["unit"]
                else:
                    slope=""
                    offset=""
                    unit=""
                row+=(slope,offset,unit)
            out+=(row,)
                    
        cellRange=self.sheets[
            "substance groups"].getCellRangeByPosition(0,0,
                                                       len(header1)-1,len(out)-1)
        cellRange.setDataArray(out)
예제 #3
0
def main():
    #-----------Setting up and unsing option parser-----------------------
    parser=OptionParser(usage= usage, version=version)
    
    parser.add_option("-u",'--user',
                      action="store",dest="user",
                      help="Name of target edb user")

    parser.add_option("-e","--edb",
                      action="store",dest="edb",
                      help="Name of target edb")

    parser.add_option("-i","--infile",
                      action="store",dest="infile",
                      help="Input csv file")
    
 #   parser.add_option("-y","--year",
 #                     action="store",dest="year",
 #                     help="Only store sources for given year")
    
    parser.add_option("-v",dest='loglevel',
                      action="store_const",default=get_loglevel(),
                      help="produce verbose output")

    parser.add_option("-t", "--template",
                      action="store",dest="cf",default=None,
                      help="Generate default controlfile")

    parser.add_option("-o", "--outfile",
                      action="store",dest="outfile",default=None,
                      help="Name of outfiles (without extension)")

    parser.add_option("-d","--delimiter",
                      action="store",dest="delimiter",default="\t",
                      help="Delimiter used in csv-file")

    parser.add_option("-c","--filterCol",
                      action="store",dest="filterCol",
                      help="Header of column to use as filter")

    parser.add_option("-f","--filterVal",
                      action="store",dest="filterVal",
                      help="Value to use in filter")


#    parser.add_option("-g", "--geocodeRasterDir",
#                      action="store",dest="geocodeRasterDir",default=None,
#                      help="Directory with geocode rasters")
    


    (options, args) = parser.parse_args()
    
    #--------------------Init logger-----------------------
#     rootLogger = logger.RootLogger(level=options.loglevel)
    logging.basicConfig(
            format='%(levelname)s:%(name)s: %(message)s',
            level=options.loglevel,
    )
    global log
#     log = rootLogger.getLogger(sys.argv[0])
    log = logging.getLogger(parser.prog)

    #-----------------Validating options-------------------

    if options.cf is not None:
        generateCf(path.abspath(options.cf),controlFileTemplate)
        log.info("Wrote default controlfile")
        return 1

    if options.user is None:
        log.error("Need to specify -u <user>")
        return 1
    if options.edb is None:
        log.error("Need to specify -e <edb>")
        return 1
#    if options.year is None:
#        log.error("Need to specify -y <year>")
#        return 1
#    if len(options.year)!=4:
#        log.error("Year should be given with four digits")
#        return 1


    if len(args)!=1:
        log.error("Controlfile should be given as argument")
        return 1

    dmn=Domain()
    edb=Edb(dmn,options.user,options.edb)
    if not edb.exists():
        log.error("Edb %s does not exist" %options.edb)
        return 1

    log.info("Parsing controlfile")

    cf=ControlFile(args[0])
    cdbPars=re.compile("companydb\.par\.(\w*?):").findall(cf.content)
    fdbPars=re.compile("facilitydb\.par\.(\w*?):").findall(cf.content)
    sdbPars=re.compile("sourcedb\.par\.(\w*?):").findall(cf.content)
    substEmisNr=re.compile("sourcedb\.subst_emis\.([0-9]*)\.emis").findall(cf.content)
    subgrpEmisNr=re.compile("sourcedb\.subgrp_emis\.([0-9]*)\.emis").findall(cf.content)

    cdbCols={}
    cdbDefaults={}
    for par in cdbPars:
        cdbCols[par]=cf.findString("companydb.par.%s:" %par)
        cdbDefaults[par]=cf.findString("companydb.par.%s.default:" %par,
                                       optional=True,default=None)
    fdbCols={}
    fdbDefaults={}
    for par in fdbPars:
        fdbCols[par]=cf.findString("facilitydb.par.%s:" %par)
        fdbDefaults[par]=cf.findString("facilitydb.par.%s.default:" %par,
                                       optional=True,default=None)

    sdbCols={}
    sdbDefaults={}
    for par in sdbPars:
        sdbCols[par]=cf.findString("sourcedb.par.%s:" %par)
        sdbDefaults[par]=cf.findString("sourcedb.par.%s.default:" %par,
                                       optional=True,default=None)

    substEmisCols={}
    substEmisDefaults={}
    if substEmisNr is not None:
        for emisNr in substEmisNr:
            cols={}
            defaults={}
            emisPars=re.compile("sourcedb\.subst_emis\.%s\.(\w*?):" %(emisNr)).findall(cf.content)
            emisDefaultPars=re.compile(
                "sourcedb\.subst_emis\.%s\.(\w*?)\.default:" %(emisNr)).findall(cf.content)
            if emisPars is not None:
                for par in emisPars:            
                    cols[par]=cf.findString("sourcedb.subst_emis.%s.%s:" %(emisNr,par))        
            if emisDefaultPars is not None:        
                for par in emisDefaultPars:    
                    defaults[par]=cf.findString("sourcedb.subst_emis.%s.%s.default:" %(emisNr,par),
                                                optional=True,default=None)
            substEmisCols[emisNr]=cols
            substEmisDefaults[emisNr]=defaults

    subgrpEmisCols={}
    subgrpEmisDefaults={}
    if subgrpEmisNr is not None:
        for emisNr in subgrpEmisNr:
            cols={}
            defaults={}
            emisPars=re.compile("sourcedb\.subgrp_emis\.%s\.(\w*?):" %(emisNr)).findall(cf.content)
            emisDefaultPars=re.compile(
                "sourcedb\.subgrp_emis\.%s\.(\w*?)\.default:" %(emisNr)).findall(cf.content)
            if emisPars is not None:
                for par in emisPars:            
                    cols[par]=cf.findString("sourcedb.subgrp_emis.%s.%s:" %(emisNr,par))        
            if emisDefaultPars is not None:        
                for par in emisDefaultPars:    
                    defaults[par]=cf.findString("sourcedb.subgrp_emis.%s.%s.default:" %(emisNr,par),
                                                optional=True,default=None)
            subgrpEmisCols[emisNr]=cols
            subgrpEmisDefaults[emisNr]=defaults
        

    log.info("Reading subdb...")
    subdb=Subdb(edb)
    subdb.read()

    log.info("Reading companydb...")
    companydb=Companydb(edb)
    companydb.read()

    log.info("Reading sourcedb...")
#     source_stream = SourceStream(edb, 'w')
    source_stream = open(options.outfile, 'w')
    source_writer = ModelWriter(source_stream,encoding="HP Roman8")


    log.info("Reading facilitydb...")
    facilitydb=Facilitydb(edb)
    facilitydb.read()


    log.info("Reading subgrpdb")
    subgrpdb=Subgrpdb(edb)

    subgrpdb.read()

    log.info("Reading edb.rsrc")
    rsrc=Rsrc(edb.rsrcPath())
    
    acCodeTables=[]
    for i in range(rsrc.numberOfCodeTrees("ac")):
        acCodeTables.append(CodeTable(rsrc.path,codeType="ac",codeIndex=i+1))

    gcCodeTables=[]
    for i in range(rsrc.numberOfCodeTrees("gc")):
        gcCodeTables.append(CodeTable(rsrc.path,codeType="gc",codeIndex=i+1))
        

    geocodeRasters=[]
    rast1=Raster()
    rast1.read("/usr/airviro/data/geo/topdown/dynamicRasters/dynamic__GEOCODE__1.txt")
    rast2=Raster()
    rast2.read("/usr/airviro/data/geo/topdown/dynamicRasters/dynamic__GEOCODE__2.txt")
    geocodeRasters.append(rast1)
    geocodeRasters.append(rast2)

    log.info("Reading csv-file")
    table=DataTable()
    table.read(options.infile,delimiter=options.delimiter,encoding="ISO-8859-15")

    if options.filterCol is not None:
        if options.filterCol not in table.colIndex:
            log.error("Filter column header not found in table")
            sys.exit(1)

    invalid=False
    nFiltered=0
    nRows=0

    log.info("Processing rows")
    for rowInd,row in enumerate(table.data):
        nRows+=1
        if options.filterCol is not None:
            filterVal=row[table.colIndex[options.filterCol]]
            if options.filterVal!=str(filterVal):
                nFiltered+=1
                continue

        comp = Company()
        for par in comp.parOrder:
            val=cdbDefaults.get(par,None)
            if par in cdbCols:
                colId=cdbCols[par]
                try:
                    tableVal=row[table.colIndex[colId]]
                except KeyError:
                    log.error(
                        "No column with header %s, columns: %s" %(
                            colId,str(table.listIds())))
                if tableVal is not None:
                    val = tableVal
            if val is not None:
                #Too long names are truncated
                if par=="NAME" and len(val)>45:
                    val=val[:45]
                comp[par]=val

        fac = Facility()
        for par in fac.parOrder:
            val=fdbDefaults.get(par,None)
            if par in fdbCols:
                colId=fdbCols[par]
                tableVal=row[table.colIndex[colId]]
                if tableVal is not None:
                    val = tableVal
            if val is not None:
                #Too long names are truncated
                if par=="NAME" and len(val)>45:
                    val=val[:45]
                fac[par]=val

        src = Source()       
        for par in ["X1", "Y1","X2","Y2",
                    "PX","PY","NAME","INFO","INFO2","DATE","CHANGED",
                    "CHIMNEY HEIGHT","GASTEMPERATURE","GAS FLOW",
                    "SEARCHKEY1","SEARCHKEY2","SEARCHKEY3",
                    "SEARCHKEY4","SEARCHKEY5","CHIMNEY OUT","CHIMNEY IN",
                    "HOUSE WIDTH","HOUSE HEIGHT","NOSEGMENTS","BUILD_WIDTHS",
                    "BUILD_HEIGHTS","BUILD_LENGTHS","BUILD_DISTFARWALL",
                    "BUILD_CENTER","GEOCODE","FORMULAMACRO","ALOB"]:
            val=sdbDefaults.get(par,None)
            if par in sdbCols:
                colId=sdbCols[par]
                tableVal=row[table.colIndex[colId]]
                if tableVal is not None:
                    val = tableVal
            if val is not None:
                #validate code
                if par=="GEOCODE" and val is not None:
                    gcList=val.split()
                    for codeIndex,code in enumerate(gcList):
                        if not gcCodeTables[codeIndex].hasCode(code):
                            log.error("Invalid geo code %s on row %i" %(code,rowInd))
                            invalid=True
                #Too long names are truncated
                if par=="NAME" and len(val)>45:
                    val=val[:45]

                #Store in src object and convert to correct type
                src._fieldvalues[par] = lazy_parse(
                    src, par, val)

        gc1=geocodeRasters[0].getVal(src.get_coord()[0],src.get_coord()[1])
        gc2=geocodeRasters[1].getVal(src.get_coord()[0],src.get_coord()[1])
        src.GEOCODE = [str(int(gc1)) + "." + str(int(gc2))]

        for emisNr,emis in substEmisCols.items():
            substEmis={"unit":None,"ac":None,"substance":None,"emis":None}

            for par in substEmis.keys():
                if par in emis:
                    substEmis[par]=row[table.colIndex[emis[par]]]
                else:
                    try:
                        substEmis[par]=substEmisDefaults[emisNr][par]
                    except KeyError:
                        log.error(
                            "Need to specify column or default value for subgrp emis %i" %emisNr)

            
            substInd=subdb.substIndex(substEmis["substance"])
            if substInd is None:
                log.error("Invalid substance name %s on row %i" %(
                        substEmis["substance"],rowInd))
                sys.exit(1)

            try:
                unit=rsrc.sub[substEmis["unit"]]
            except KeyError:
                log.error("Invalid unit name %s on row %i" %(emis["unit"],rowInd))
                sys.exit(1)

            acList=substEmis["ac"].split('\\')[0].split()
            for codeIndex,code in enumerate(acList):
#                 if code == "2.A.4.2":
#                     import pdb; pdb.set_trace()
                refCode = acCodeTables[codeIndex].checkCode(code)
                if refCode == "-":
                    log.error("Invalid activity code %s on row %i" %(code,rowInd))
                    sys.exit(1)
                if refCode != code:
                    acList[codeIndex] = refCode
            substEmis["ac"] = acList
            
            if substEmis["emis"] is not None and substEmis["emis"]!="0":
                try:
                    emis = src.add_emission()
                    emis.UNIT = substEmis["unit"] 
                    emis.ACTCODE = substEmis["ac"]  # needs re-formatting
                    emis.EMISSION = float(substEmis["emis"])
                    emis.SUBSTANCE = substInd

                    emis.auto_adjust_unit(edb) 


                except:            
#                     print substEmis
#                     log.error("Invalid substance emission on row %i" %rowInd)
                    invalid=True
                    src.EMISSION=src.EMISSION[:-1]


        for emis in subgrpEmisCols.values():
            subgrpEmis={"unit":None,"ac":None,"name":None,"emis":None}
            for par in subgrpEmis.keys():
                if par in emis:
                    subgrpEmis[par]=row[table.colIndex[emis[par]]]
                else:
                    try:
                        subgrpEmis[par]=subgrpEmisDefaults[emisNr][par]
                    except KeyError:
                        log.error(
                            "Need to specify column or default value for subgrp emis %i" %emisNr)

            #validating subgrp name
            try:                        
                subgrp=subgrpdb.getByName(subgrpEmis["name"])
            except KeyError:
                log.error("Invalid subgrp name %s on row %i" %(subgrpEmis["name"],rowInd))
                invalid=True

            #validating subgrp emis unit
            try:                
                unitFactor=rsrc.subGrpEm[subgrpEmis["unit"]]
            except KeyError:
                log.error("Invalid unit %s for subgrp emission on row %i" %(
                        subgrpEmis["unit"],rowInd))
                invalid=True

            #validating subgrp activity code
            acList=subgrpEmis["ac"].split()
            for codeIndex,code in enumerate(acList):
                refCode = acCodeTables[codeIndex].checkCode(code)
                if refCode == "-":
                    log.error("Invalid activity code %s on row %i" %(code,rowInd))
                    invalid=True
                    break
                if refCode != code:
                    acList[codeIndex] = refCode
            substEmis["ac"] = acList

            try:
                src.addSubgrpEmis(subgrp.index,emis=subgrpEmis["emis"],unit=subgrpEmis["unit"],
                                  ac=subgrpEmis["ac"])
            except:            
                log.error("Invalid subgrp emission on row %i" %rowInd)
                invalid=True

        companydb.append(comp,force=True)
        facilitydb.append(fac,force=True)
        source_writer.write(src)
#         sourcedb.append(src)

    if invalid:
        log.info("No output written due to validation errors")
        sys.exit(0)
    if len(companydb.items)>0:
        if options.outfile is None:
            log.info("Writing companydb")
        else:
            log.info("Writing company db to file")
        companydb.write(filename=options.outfile+".companydb")

    if len(facilitydb.items)>0:
        if options.outfile is  None:
            log.info("Writing facilitydb")
        else:
            log.info("Writing facilitydb to file")
        facilitydb.write(filename=options.outfile+".facilitydb")

#     if len(sourcedb.sources)>0:
#         if options.outfile is None:
#             log.info("Writing sourcedb")
#         else:
#             log.info("Writing sourcedb to file")
#     sourcedb.write(filename=options.outfile+".sourcedb")

    if options.filterCol is not None:
        log.info("Filtered out %i out of %i" %(nFiltered,nRows))
예제 #4
0
class LoadEdb(XJobExecutor, unohelper.Base):
    """
    Load an Airviro edb into a number of spreadsheets
    """
    def __init__(self, ctx):
        self.ctx = ctx
        self.desktop = self.ctx.ServiceManager.createInstanceWithContext(
            "com.sun.star.frame.Desktop", self.ctx)
        self.doc = self.desktop.getCurrentComponent()
        self.sheets = {
            "sources": None,
            "substance groups": None,
            "emfac": None,
            "substances": None,
            "units": None
        }
        self.edb = None
        self.subdb = None
        self.subgrpdb = None
        self.host = None
        self.auth = None
        self.user = None
        self.edb = None
        self.domain = None

    def login(self, host, domain, user, pw):
        if "http://" not in host:
            host = "http://" + host

        #todo: get authenticator via https
        self.auth = "gmX=QxTLA3Cz3=9aacZk0k9Zae_FW8Kznq+9B_7_i3i0"
        self.domain = domain
        self.user = user
        self.host = host

    def setEdb(self, domainName, userName, edbName):
        self.edb = Edb(domainName, userName, edbName)

    def getPage(self, argList):
        req = "%s/cgi-bin/iairviro/do.cgi?%s&loAirviro.cgi&%s" % (
            self.host, self.auth, "&".join(argList))
        page = urllib2.urlopen(req)
        return page

    def loadSubdb(self):
        self.sheets["substances"] = self.createSheet("substances")
        self.subdb = Subdb(self.edb)
        page = self.getPage(
            ["read", self.domain, self.edb.user, self.edb.name, "subdb_0"])

        #        self.subdb.readSubstances(
        #            filename="/local_disk/dvlp/airviro/loairviro/test/substances.out",
        #            keepEmpty=True)
        self.subdb.readSubstances(fileObject=page, keepEmpty=True)
        out = (("Index", "Substance name"), )
        indices = sorted(self.subdb.substNames.keys())
        for i, ind in enumerate(indices):
            substName = self.subdb.substNames[ind]
            out += ((ind, substName), )

        cellRange = self.sheets["substances"].getCellRangeByPosition(
            0, 0, 1,
            len(out) - 1)
        cellRange.setDataArray(out)

    def loadSubgrpdb(self):
        self.sheets["substance groups"] = self.createSheet("substance groups")
        self.subgrpdb = Subgrpdb(self.edb)
        page = self.getPage(
            ["read", self.domain, self.edb.user, self.edb.name, "subgrpdb"])
        self.subgrpdb.read(fileObject=page)
        #            filename="/local_disk/dvlp/airviro/loairviro/test/subgrp.out")

        substances = []
        for subgrpInd, subgrp in self.subgrpdb.subgrps.items():
            for subst in subgrp.substances:
                substName = self.subdb.substNames[subst]
                if substName not in substances:
                    substances.append(substName)

        header1 = ("", "")
        header2 = ("Index", "Name")
        for substName in substances:
            header1 += (substName, "", "")
            header2 += ("Slope", "Offset", "Unit")

        out = (header1, header2)
        for subgrpInd, subgrp in self.subgrpdb.subgrps.items():
            #list names of substances in subgrp
            substInSubgrp = [self.subdb[s] for s in subgrp.substances]
            row = (subgrp.index, subgrp.name)
            for substName in substances:
                if substName in substInSubgrp:
                    substIndex = self.subdb.substIndices[substName]
                    slope = subgrp.substances[substIndex]["slope"]
                    offset = subgrp.substances[substIndex]["offset"]
                    unit = subgrp.substances[substIndex]["unit"]
                else:
                    slope = ""
                    offset = ""
                    unit = ""
                row += (slope, offset, unit)
            out += (row, )

        cellRange = self.sheets["substance groups"].getCellRangeByPosition(
            0, 0,
            len(header1) - 1,
            len(out) - 1)
        cellRange.setDataArray(out)

    def loadEmfacdb(self):
        self.sheets["emission factors"] = self.createSheet("emission factors")
        self.emfacdb = Emfacdb(self.edb)
        page = self.getPage(
            ["read", self.domain, self.edb.user, self.edb.name, "emfacdb"])
        self.emfacdb.read(fileObject=page)
        #            filename="/local_disk/dvlp/airviro/loairviro/test/emfac.out")
        nvars = [len(emfac.vars) for emfac in self.emfacdb.activities.values()]
        nvars = max(nvars)

        firstRow = 0
        for emfacInd, emfac in self.emfacdb.activities.items():
            out = []
            out.append(("Index:", emfacInd))
            out.append(("Name:", emfac.name))
            out.append(("Substance:", self.subdb[emfac.subst]))
            varIndices = []
            varNames = []
            varTypes = []
            formula = emfac.formula
            for i in range(1, len(emfac.vars) + 1):
                formula.replace("X%i" % i, emfac.vars[i].name)
                varIndices.append(str(i))
                varNames.append(emfac.vars[i].name)
                varTypes.append(emfac.vars[i].type)
            out.append(("Formula", formula))
            out.append(("Variable index:", ) + tuple(varIndices))
            out.append(("Variable names:", ) + tuple(varNames))
            out.append(("Variable types:", ) + tuple(varTypes))
            out.append(("", ))
            out.append(("", ))

            lastRow = firstRow + len(out) - 1
            lastCol = len(varIndices)

            #Make so all rows have the same length
            #Needed to use setDataArray
            for i in range(len(out)):
                completeRow = ("", ) * (lastCol - (len(out[i]) - 1))
                out[i] += completeRow

            cellRange = self.sheets["emission factors"].getCellRangeByPosition(
                0, firstRow, lastCol, lastRow)
            firstRow = lastRow + 1
            cellRange.setDataArray(tuple(out))

    def createSheet(self, name):
        try:
            sheets = self.doc.getSheets()
        except Exception:
            raise TypeError("Model retrived was not a spreadsheet")

        #TODO: warning dialogue before removing sheet
        if sheets.hasByName(name):
            sheets.removeByName(name)

        pos = sheets.getCount()
        sheets.insertNewByName(name, pos)
        return sheets.getByIndex(pos)

    def loadSources(self):
        self.sheets["sources"] = self.createSheet("sources")
        sourcedb = Sourcedb(self.edb)

        page = self.getPage(
            ["read", self.domain, self.edb.user, self.edb.name, "sourcedb"])
        #filename="/local_disk/dvlp/airviro/loairviro/test/TR_ships.out"

        substEmis = {}
        substAlob = {}
        subgrpEmis = {}
        subgrpAlob = {}
        activityEmis = {}
        activityAlob = {}
        srcAlobs = {}

        #Reading sources
        batchInd = 0
        while sourcedb.read(fileObject=page, accumulate=True, batchSize=1):
            self.updateProgressDlg("Loading point and area sources",
                                   "read batch %i" % batchInd)
            #print "Read batch %i" %batchInd
            batchInd += 1

        self.updateProgressDlg("Loading point and area sources",
                               "writing sources to sheet")
        #To present sources in a table with a nice header, it is necessary
        #to list all substances, alobs, subgroups, emfacs and variables that are
        #used in the edb. This is because each alob, substance etc. should be
        #shown only once in the header
        for src in sourcedb.sources:
            #Accumulate all alobs into a list
            for alob in src.ALOBOrder:
                srcAlobs[alob] = None

            #store all substances and their alobs in a dict
            for substInd, emis in src.subst_emis:
                if substInd not in substEmis:
                    substEmis[substInd] = {"alob": {}}
                for alob in emis.ALOBOrder:
                    substEmis[substInd]["alob"][alob] = None

            #store all substance groups and their alobs in a dict
            for subgrpInd, emis in src.subgrp_emis.items():
                if subgrpInd not in subgrpEmis:
                    subgrpEmis[subgrpInd] = {"alob": {}}
                for alob in emis.ALOBOrder:
                    subgrpEmis[subgrpInd]["alob"][alob] = None

            #Accumulate all activities and included alobs
            for emfacInd, emis in src.activity_emis.items():
                if emfacInd not in activityEmis:
                    activityEmis[emfacInd] = {"alob": {}, "var": {}}
                for varInd, varVal in emis["VARLIST"]:
                    #vars should also be indexed
                    activityEmis[emfacInd]["var"][varInd] = None
                for alob in emis.ALOBOrder:
                    activityEmis[emfacInd]["alob"][alob] = None

        #Writing header
        header0 = ()
        header = ()
        srcAlobInd = {}

        for parName in src.parOrder:
            header += (parName, )
        header0 += ("Static parameters", )
        header0 += (len(header) - len(header0)) * ("", )
        alobKeys = srcAlobs.keys()
        alobKeys.sort()
        for alobKey in alobKeys:
            header += (alobKey, )
        if src["ALOB"] > 0:
            header0 += ("ALOBs", )
            header0 += (len(header) - len(header0)) * ("", )

        for substInd in substEmis:
            substName = self.subdb.substNames[substInd]
            header += ("Emission", "Time variation", "Unit", "Macro",
                       "Activity code")
            header0 += ("Substance:", substName)
            header0 += (len(header) - len(header0)) * ("", )
            alobKeys = substEmis[substInd]["alob"].keys()
            alobKeys.sort()
            for alobKey in alobKeys:
                row += [src.ALOB.get(alobKey, "")]

            for alob in alobKeys:
                header += (alob, )
            if len(alobKeys) > 0:
                header0 += ("ALOBs", )
                header0 += (len(header) - len(header0)) * ("", )

        for subgrpInd in subgrpEmis:
            subgrp = self.subgrpdb.subgrps[subgrpInd]
            header += ("Activity", "Time variation", "Unit", "Activity code")
            header0 += ("Substance group:", subgrp.name)
            header0 += (len(header) - len(header0)) * ("", )

            alobKeys = subgrpEmis[subgrpInd]["alob"].keys()
            alobKeys.sort()
            for alobKey in alobKeys:
                header += (alobKey, )
            if subgrpEmis[subgrpInd]["alob"] > 0:
                header0 += ("ALOBs", )
                header0 += (len(header) - len(header0)) * ("", )

        for emfacInd in activityEmis:
            emfac = self.emfacdb[emfacInd]
            header += ("Time variation", )
            for varInd, var in emfac.vars.items():
                header += (var.name, )
            header0 += ("Emfac:", emfac.name)
            header0 += (len(header) - len(header0)) * ("", )

            header += ("Activity code", )
            alobKeys = activityEmis[emfacInd]["alob"].keys()
            alobKeys.sort()
            for alobKey in alobKeys:
                header += (alobKey, )
            if len(alobKeys):
                header0 += ("ALOBs", )
                header0 += (len(header) - len(header0)) * ("", )
        header0 += (len(header) - len(header0)) * ("", )

        firstCol = 0
        for colInd, val in enumerate(header0[1:]):
            if val != "":
                bottomCellRange = self.sheets[
                    "sources"].getCellRangeByPosition(firstCol, 1, colInd - 2,
                                                      1)

                bottomBorder = bottomCellRange.BottomBorder
                bottomBorder.OuterLineWidth = 30
                bottomCellRange.BottomBorder = bottomBorder
                firstCol = colInd - 2

        out = [header0, header]
        for src in sourcedb.sources:
            row = []
            for par in src.parOrder:
                row += [unicode(src[par])]

            #Write alobs for sources
            alobKeys = srcAlobs.keys()
            alobKeys.sort()
            for alobKey in alobKeys:
                row += [src.ALOB.get(alobKey, "")]

            #write substance emissions with alobs
            for substInd in substEmis:
                alobKeys = substEmis[substInd]["alob"].keys()
                alobKeys.sort()
                if substInd in src.subst_emis:
                    emis = src.subst_emis[substInd]
                    row += [
                        emis["EMISSION"], emis["TIMEVAR"], emis["UNIT"],
                        emis["MACRO"], emis["ACTCODE"]
                    ]
                    for alobKey in alobKeys:
                        row += [emis.ALOB.get(alobKey, "")]
                else:
                    row += ["", "", "", "", ""]  #empty cells for substance
                    row += [""] * len(alobKeys)

            #write substance group emissions with alobs
            for subgrpInd in subgrpEmis:
                alobKeys = subgrpEmis[subgrpInd]["alob"].keys()
                alobKeys.sort()
                if subgrpInd in src.subst_emis:
                    emis = src.subgrp_emis[subgrpInd]
                    row += [
                        emis["ACTIVITY"], emis["TIMEVAR"], emis["UNIT"],
                        emis["ACTCODE"]
                    ]
                    for alobKey in alobKeys:
                        row += [emis.ALOB.get(alobKey, "")]
                else:
                    row += ["", "", "", ""]  #empty cells for substance group
                    row += [""] * len(alobKeys)

            #write emfac emissions with variables and alobs
            for emfacInd in activityEmis:
                alobKeys = activityEmis[emfacInd]["alob"].keys()
                alobKeys.sort()
                varKeys = activityEmis[emfacInd]["var"].keys()
                varKeys.sort()
                if emfacInd in src.activity_emis:
                    emis = src.activity_emis[emfacInd]
                    row += [emis["TIMEVAR"]]
                    varVals = [var[1] for var in emis["VARLIST"]]
                    row += varVals
                    row += [emis["ACTCODE"]]
                    for alobKey in alobKeys:
                        row += [emis.ALOB.get(alobKey, "")]
                else:
                    row += 2 * [""] + [""] * len(varKeys) + [""
                                                             ] * len(alobKeys)
            out.append(tuple(row))
        cellRange = self.sheets["sources"].getCellRangeByPosition(
            0, 0,
            len(header) - 1,
            len(out) - 1)
        cellRange.setDataArray(tuple(out))

    def trigger(self, args=''):
        """Called by addon's UI controls or service:URL"""
        self.createProgressDlg()
        self.setEdb("shipair", "sjov", "BS_2011_e1")
        self.updateProgressDlg("Loading edb: authenticating", "")
        self.login("www.shipair.smhi.se", "shipair", "airviro", "dummypw")
        self.updateProgressDlg("Loading substance list and searchkeys", "")
        self.loadSubdb()
        print "Loaded subdb"
        self.updateProgressDlg("Loading substance groups", "")
        self.loadSubgrpdb()
        print "Loaded subgrpdb"
        self.updateProgressDlg("Loading emission factors", "")
        self.loadEmfacdb()
        print "Loaded emfacdb"
        self.updateProgressDlg("Loading point and area sources", "")
        self.loadSources()
        self.updateProgressDlg("Edb loaded successfully!", "")
        sleep(1)
        print "Loaded sources"
        self.progressContainer.dispose()

    def createProgressDlg(self):
        parentwin = self.doc.CurrentController.Frame.ContainerWindow
        dialogModel = self.ctx.ServiceManager.createInstanceWithContext(
            'com.sun.star.awt.UnoControlDialogModel', self.ctx)
        FramePosSize = self.doc.CurrentController.Frame.ContainerWindow.PosSize
        WindowWidth = FramePosSize.Width
        dialogModel.Width = 200
        dialogModel.Height = 100
        dialogModel.PositionX = (WindowWidth / 2.2) - 105
        dialogModel.PositionY = 30
        dialogModel.Title = 'Loading sources'

        statusTxtModel = addControl(
            'com.sun.star.awt.UnoControlFixedTextModel', dialogModel, 6, 2,
            190, 20, 'Initializing', 'statusLabel')

        counterTxtModel = addControl(
            'com.sun.star.awt.UnoControlFixedTextModel', dialogModel, 6, 30,
            190, 50, '0', 'counterLabel')

        self.progressContainer = self.ctx.ServiceManager.createInstanceWithContext(
            'com.sun.star.awt.UnoControlDialog', self.ctx)

        self.progressContainer.setModel(dialogModel)
        self.progressContainer.setVisible(True)

        self.statusControl = self.progressContainer.getControl(
            'statusLabel').getModel()
        self.counterControl = self.progressContainer.getControl(
            'counterLabel').getModel()

    def updateProgressDlg(self, statusStr, counterStr):
        self.statusControl.Label = statusStr
        self.counterControl.Label = counterStr
예제 #5
0
def main():
    #-----------Setting up and unsing option parser-----------------------
    parser=OptionParser(usage= usage, version=version)
    
    parser.add_option("-u",'--user',
                      action="store",dest="user",
                      help="Name of target edb user")

    parser.add_option("-e","--edb",
                      action="store",dest="edb",
                      help="Name of target edb")

    parser.add_option("-y","--year",
                       action="store",dest="year",
                       help="Cut out for given year")
    
    parser.add_option("-f","--file",
                      action="store",dest="file",
                      help="File to get data from")
    
    parser.add_option("-l", "--loglevel",
                      action="store",dest="loglevel",default=2,
                      help="Sets the loglevel (0-3 where 3=full logging)")

    (options, args) = parser.parse_args()


    
    #--------------------Init logger-----------------------
    rootLogger = logger.RootLogger(level=options.loglevel)
    global log
    log = rootLogger.getLogger(sys.argv[0])

    #-----------------Validating options-------------------

    if options.user is None:
        log.error("Need to specify -u <user>")
        return 1
    if options.edb is None:
        log.error("Need to specify -e <edb>")
        return 1
    if options.year is None:
         log.error("Need to specify -y <year>")
         return 1
    if options.file is None:
        log.error("Need to specify -f <file>")
        return 1
#     if len(options.suffix)>4:
#         log.error("Limit your suffix length to 4 characters")
#         return 1

    if len(options.year)!=4:
        log.error("Year should be given with four digits")
        return 1

    dmn=Domain()
    edb=Edb(dmn,options.user,options.edb)
    subgrpdb=Subgrpdb(edb)
    rf = codecs.open(options.file,"r","ISO-8859-15")
    species={}
    tmp=tempfile.NamedTemporaryFile(suffix=".tmp",dir=dmn.tmpDir())
    emisKey={}
    try:
        cmd="subdb -0 -o "+tmp.name
        returnCode,outMsg,errMsg=utilities.execute(cmd)
        if returnCode!=0:
            raise IOError("Error while running:\n" + cmd + "\nstdout: "+outMsg+"\nstderr: "+errMsg)
        f=codecs.open(tmp.name,"r","HP Roman8")
#         content=f.read()
        for line in f.read().split('\n'):
            if line == "":
                break
            line=line.split()
            if line[1] == '""' or line[1] == '"-"':
                continue
            emisKey[line[1][1:-1].lower()] = line[0]
        f.close()
    except IOError:
        msg="stdout: "+outMsg+"\nstderr: "+errMsg
        raise IOError("Could not run: "+cmd+"\n"+msg)

    for line in rf:
        if "Average of em" in line:
            line = line.split("\t")
            line[-1]=line[-1][:-1]
            i=0
            for spec in line[4:]:
                i+=1
                if len(spec) < 13:
                    continue
                if spec[13:].lower() == "pm25":
                    species[i]="pm2.5"
                elif spec[13:].lower() == "bap":
                    species[i]="benzo_a_pyrene"
                elif spec[13:].lower() == "diox":
                    species[i]="dioxine"
                elif spec[13:].lower() == "pah":
                    species[i]="pah-4"
                else:
                    species[i]=spec[13:].lower()
            continue
        line = line[:-1].split('\t')
        if line[3] != options.year:
            continue
        name=line[0]

        i=0
        substances={}
        for emFactor in line[4:]:
            i+=1
            if emFactor == "":
                continue
            if species[i] in emisKey:
#                print "Now doing species "+species[i]
                index=int(emisKey[species[i]])
                emFactor = float(emFactor)
                if species[i] == "pah-4" or species[i] == "benzo_a_pyrene":
                    emFactor *= 10** -6
                if species[i]=="dioxine":
                    emFactor *= 10** -9
                if emFactor < 10** -5:
                    unit="g/TJ"
                    emFactor*= 10**6
                elif emFactor < 10** -2:
                    unit="kg/TJ"
                    emFactor*= 10**3
                elif emFactor < 10:
                    unit="ton/TJ"
                else:
                    unit="Gg/TJ"
                    emFactor*= 10** -3

                substances[index]={"slope":emFactor,"offset":0,"unit":unit}
            else:
                print species[i] + " missing"
        subgrpdb.add_subgrp(name,substances)


#     subgrpdb.read()
    subgrpdb.write_to_file("test.txt")
예제 #6
0
class LoadEdb(XJobExecutor,unohelper.Base):
    """
    Load an Airviro edb into a number of spreadsheets
    """
    def __init__(self,ctx):
        self.ctx = ctx
        self.desktop=self.ctx.ServiceManager.createInstanceWithContext(
            "com.sun.star.frame.Desktop", self.ctx )
        self.doc=self.desktop.getCurrentComponent()
        self.sheets={"sources":None,"substance groups":None,
                     "emfac":None,"substances":None, "units":None}
        self.edb=None
        self.subdb=None
        self.subgrpdb=None
        self.host=None
        self.auth=None
        self.user=None
        self.edb=None
        self.domain=None

    def login(self,host,domain,user,pw):
        if "http://" not in host:
            host="http://"+host
                    
        #todo: get authenticator via https        
        self.auth ="gmX=QxTLA3Cz3=9aacZk0k9Zae_FW8Kznq+9B_7_i3i0"
        self.domain=domain
        self.user=user
        self.host=host

    def setEdb(self,domainName,userName,edbName):
        self.edb=Edb(domainName,userName,edbName)

    def getPage(self,argList):
        req = "%s/cgi-bin/iairviro/do.cgi?%s&loAirviro.cgi&%s"%(self.host,
                                                                self.auth,
                                                                "&".join(argList))
        page = urllib2.urlopen(req)
        return page
        
    def loadSubdb(self):
        self.sheets["substances"] = self.createSheet("substances")
        self.subdb=Subdb(self.edb)
        page=self.getPage(["read",self.domain,self.edb.user,self.edb.name,
                           "subdb_0"])
        
#        self.subdb.readSubstances(
#            filename="/local_disk/dvlp/airviro/loairviro/test/substances.out",
#            keepEmpty=True)
        self.subdb.readSubstances(fileObject=page,keepEmpty=True)
        out=(("Index","Substance name"),)
        indices=sorted(self.subdb.substNames.keys())
        for i,ind in enumerate(indices):
            substName=self.subdb.substNames[ind]
            out+=((ind,substName),)

        cellRange=self.sheets[
            "substances"].getCellRangeByPosition(0,0,1,len(out)-1)
        cellRange.setDataArray(out)

    def loadSubgrpdb(self):
        self.sheets["substance groups"] = self.createSheet("substance groups")
        self.subgrpdb=Subgrpdb(self.edb)      
        page=self.getPage(["read",self.domain,self.edb.user,self.edb.name,
                           "subgrpdb"])
        self.subgrpdb.read(fileObject=page)
#            filename="/local_disk/dvlp/airviro/loairviro/test/subgrp.out")
        
        substances=[]
        for subgrpInd,subgrp in self.subgrpdb.subgrps.items():
            for subst in subgrp.substances:
                substName=self.subdb.substNames[subst]
                if substName not in substances:
                    substances.append(substName)

        header1=("","")
        header2=("Index","Name")
        for substName in substances:
            header1+=(substName,"","")
            header2+=("Slope","Offset","Unit")

        out=(header1,header2)
        for subgrpInd,subgrp in self.subgrpdb.subgrps.items():
            #list names of substances in subgrp
            substInSubgrp=[self.subdb[s] for s in subgrp.substances]
            row=(subgrp.index,subgrp.name)
            for substName in substances:
                if substName in substInSubgrp:
                    substIndex=self.subdb.substIndices[substName]
                    slope=subgrp.substances[substIndex]["slope"]
                    offset=subgrp.substances[substIndex]["offset"]
                    unit=subgrp.substances[substIndex]["unit"]
                else:
                    slope=""
                    offset=""
                    unit=""
                row+=(slope,offset,unit)
            out+=(row,)
                    
        cellRange=self.sheets[
            "substance groups"].getCellRangeByPosition(0,0,
                                                       len(header1)-1,len(out)-1)
        cellRange.setDataArray(out)

    def loadEmfacdb(self):
        self.sheets["emission factors"] = self.createSheet("emission factors")
        self.emfacdb=Emfacdb(self.edb)
        page=self.getPage(["read",self.domain,self.edb.user,self.edb.name,
                           "emfacdb"])
        self.emfacdb.read(fileObject=page)
#            filename="/local_disk/dvlp/airviro/loairviro/test/emfac.out")
        nvars=[len(emfac.vars) for emfac in self.emfacdb.activities.values()]
        nvars=max(nvars)

        firstRow=0
        for emfacInd,emfac in self.emfacdb.activities.items():
            out=[]
            out.append(("Index:",emfacInd))
            out.append(("Name:",emfac.name))
            out.append(("Substance:",self.subdb[emfac.subst]))
            varIndices=[]
            varNames=[]
            varTypes=[]
            formula=emfac.formula
            for i in range(1,len(emfac.vars)+1):
                formula.replace("X%i" %i,emfac.vars[i].name)
                varIndices.append(str(i))
                varNames.append(emfac.vars[i].name)
                varTypes.append(emfac.vars[i].type)
            out.append(("Formula",formula))
            out.append(("Variable index:",)+tuple(varIndices))
            out.append(("Variable names:",)+tuple(varNames))
            out.append(("Variable types:",)+tuple(varTypes))
            out.append(("",))
            out.append(("",))

            lastRow=firstRow+len(out)-1
            lastCol=len(varIndices)

            #Make so all rows have the same length
            #Needed to use setDataArray
            for i in range(len(out)):
                completeRow=("",)*(lastCol-(len(out[i])-1))
                out[i]+=completeRow

            cellRange=self.sheets[
                "emission factors"].getCellRangeByPosition(0,firstRow,
                                                           lastCol,lastRow)
            firstRow=lastRow+1
            cellRange.setDataArray(tuple(out))
        
                
    def createSheet(self,name):
        try:
            sheets = self.doc.getSheets()
        except Exception:
            raise TypeError("Model retrived was not a spreadsheet")

        #TODO: warning dialogue before removing sheet
        if sheets.hasByName(name):
            sheets.removeByName(name)
            
        pos = sheets.getCount()
        sheets.insertNewByName(name, pos)
        return sheets.getByIndex(pos)
        
    def loadSources(self):        
        self.sheets["sources"] = self.createSheet("sources")
        sourcedb=Sourcedb(self.edb)

        page=self.getPage(["read",self.domain,self.edb.user,self.edb.name,
                           "sourcedb"])
        #filename="/local_disk/dvlp/airviro/loairviro/test/TR_ships.out"
        
        substEmis={}
        substAlob={}
        subgrpEmis={}
        subgrpAlob={}
        activityEmis={}
        activityAlob={}
        srcAlobs={}

        #Reading sources
        batchInd=0
        while sourcedb.read(fileObject=page,accumulate=True,batchSize=1):
            self.updateProgressDlg(
                "Loading point and area sources","read batch %i" %batchInd)
            #print "Read batch %i" %batchInd
            batchInd+=1

        self.updateProgressDlg(
            "Loading point and area sources","writing sources to sheet")
        #To present sources in a table with a nice header, it is necessary
        #to list all substances, alobs, subgroups, emfacs and variables that are
        #used in the edb. This is because each alob, substance etc. should be
        #shown only once in the header
        for src in sourcedb.sources:
            #Accumulate all alobs into a list
            for alob in src.ALOBOrder:
                srcAlobs[alob]=None

            #store all substances and their alobs in a dict
            for substInd,emis in src.subst_emis:
                if substInd not in substEmis:
                    substEmis[substInd]={"alob":{}}
                for alob in emis.ALOBOrder:
                    substEmis[substInd]["alob"][alob]=None

            #store all substance groups and their alobs in a dict
            for subgrpInd,emis in src.subgrp_emis.items():
                if subgrpInd not in subgrpEmis:
                    subgrpEmis[subgrpInd]={"alob":{}}
                for alob in emis.ALOBOrder:
                    subgrpEmis[subgrpInd]["alob"][alob]=None

            #Accumulate all activities and included alobs
            for emfacInd,emis in src.activity_emis.items():
                if emfacInd not in activityEmis:
                    activityEmis[emfacInd]={"alob":{},"var":{}}
                for varInd,varVal in emis["VARLIST"]:
                    #vars should also be indexed
                    activityEmis[emfacInd]["var"][varInd]=None
                for alob in emis.ALOBOrder:
                    activityEmis[emfacInd]["alob"][alob]=None

        #Writing header
        header0=()
        header=()
        srcAlobInd={}

        for parName in src.parOrder:
            header+=(parName,)
        header0+=("Static parameters",)
        header0+=(len(header)-len(header0))*("",)
        alobKeys=srcAlobs.keys()
        alobKeys.sort()
        for alobKey in alobKeys:
            header+=(alobKey,)
        if src["ALOB"]>0:
            header0+=("ALOBs",)
            header0+=(len(header)-len(header0))*("",)

        for substInd in substEmis:
            substName=self.subdb.substNames[substInd]
            header+=("Emission","Time variation","Unit","Macro","Activity code")
            header0+=("Substance:",substName)
            header0+=(len(header)-len(header0))*("",)
            alobKeys=substEmis[substInd]["alob"].keys()
            alobKeys.sort()
            for alobKey in alobKeys:
                row+=[src.ALOB.get(alobKey,"")]

            for alob in alobKeys:
                header+=(alob,)
            if len(alobKeys)>0:
                header0+=("ALOBs",)
                header0+=(len(header)-len(header0))*("",)

        for subgrpInd in subgrpEmis:
            subgrp=self.subgrpdb.subgrps[subgrpInd]
            header+=("Activity","Time variation","Unit","Activity code")
            header0+=("Substance group:",subgrp.name)
            header0+=(len(header)-len(header0))*("",)

            alobKeys=subgrpEmis[subgrpInd]["alob"].keys()
            alobKeys.sort()
            for alobKey in alobKeys:
                header+=(alobKey,)
            if subgrpEmis[subgrpInd]["alob"]>0:
                header0+=("ALOBs",)
                header0+=(len(header)-len(header0))*("",)

                
        for emfacInd in activityEmis:
            emfac=self.emfacdb[emfacInd]
            header+=("Time variation",)
            for varInd,var in emfac.vars.items():
                header+=(var.name,)
            header0+=("Emfac:",emfac.name)
            header0+=(len(header)-len(header0))*("",)
                
            header+=("Activity code",)
            alobKeys=activityEmis[emfacInd]["alob"].keys()
            alobKeys.sort()
            for alobKey in alobKeys:
                header+=(alobKey,)
            if len(alobKeys):
                header0+=("ALOBs",)
                header0+=(len(header)-len(header0))*("",)
        header0+=(len(header)-len(header0))*("",)

        firstCol=0
        for colInd,val in enumerate(header0[1:]):
            if val !="":
                bottomCellRange=self.sheets[
                    "sources"].getCellRangeByPosition(firstCol,1,colInd-2,1)
                
                bottomBorder = bottomCellRange.BottomBorder
                bottomBorder.OuterLineWidth = 30
                bottomCellRange.BottomBorder = bottomBorder
                firstCol=colInd-2
                
        out=[header0,header]
        for src in sourcedb.sources:
            row=[]
            for par in src.parOrder:
                row+=[unicode(src[par])]

            #Write alobs for sources
            alobKeys=srcAlobs.keys()
            alobKeys.sort()
            for alobKey in alobKeys:
                row+=[src.ALOB.get(alobKey,"")]

            #write substance emissions with alobs
            for substInd in substEmis:
                alobKeys=substEmis[substInd]["alob"].keys()
                alobKeys.sort()
                if substInd in src.subst_emis:
                    emis=src.subst_emis[substInd]
                    row+=[emis["EMISSION"],
                          emis["TIMEVAR"],
                          emis["UNIT"],
                          emis["MACRO"],
                          emis["ACTCODE"]]
                    for alobKey in alobKeys:
                        row+=[emis.ALOB.get(alobKey,"")]
                else:
                    row+=["","","","",""] #empty cells for substance
                    row+=[""]*len(alobKeys)

            #write substance group emissions with alobs
            for subgrpInd in subgrpEmis:
                alobKeys=subgrpEmis[subgrpInd]["alob"].keys()
                alobKeys.sort()
                if subgrpInd in src.subst_emis:
                    emis=src.subgrp_emis[subgrpInd]
                    row+=[emis["ACTIVITY"],
                          emis["TIMEVAR"],
                          emis["UNIT"],
                          emis["ACTCODE"]]
                    for alobKey in alobKeys:
                        row+=[emis.ALOB.get(alobKey,"")]
                else:
                    row+=["","","",""] #empty cells for substance group
                    row+=[""]*len(alobKeys)

            #write emfac emissions with variables and alobs
            for emfacInd in activityEmis:
                alobKeys=activityEmis[emfacInd]["alob"].keys()
                alobKeys.sort()
                varKeys=activityEmis[emfacInd]["var"].keys()
                varKeys.sort()
                if emfacInd in src.activity_emis:
                    emis=src.activity_emis[emfacInd]
                    row+=[emis["TIMEVAR"]]
                    varVals = [var[1] for var in emis["VARLIST"]]
                    row+=varVals
                    row+=[emis["ACTCODE"]]
                    for alobKey in alobKeys:
                        row+=[emis.ALOB.get(alobKey,"")]
                else:
                    row+=2*[""]+[""]*len(varKeys)+[""]*len(alobKeys)
            out.append(tuple(row))
        cellRange=self.sheets[
            "sources"].getCellRangeByPosition(0,0,len(header)-1,len(out)-1)
        cellRange.setDataArray(tuple(out))
        
    def trigger(self,args=''):
        """Called by addon's UI controls or service:URL"""
        self.createProgressDlg()
        self.setEdb("shipair","sjov","BS_2011_e1")
        self.updateProgressDlg("Loading edb: authenticating","")
        self.login("www.shipair.smhi.se","shipair","airviro","dummypw")
        self.updateProgressDlg("Loading substance list and searchkeys","")
        self.loadSubdb()
        print "Loaded subdb"
        self.updateProgressDlg("Loading substance groups","")
        self.loadSubgrpdb()
        print "Loaded subgrpdb"
        self.updateProgressDlg("Loading emission factors","")
        self.loadEmfacdb()
        print "Loaded emfacdb"
        self.updateProgressDlg("Loading point and area sources","")
        self.loadSources()
        self.updateProgressDlg("Edb loaded successfully!","")
        sleep(1)
        print "Loaded sources"
        self.progressContainer.dispose()
        
    def createProgressDlg(self):
        parentwin=self.doc.CurrentController.Frame.ContainerWindow
        dialogModel = self.ctx.ServiceManager.createInstanceWithContext(
            'com.sun.star.awt.UnoControlDialogModel', self.ctx)
        FramePosSize = self.doc.CurrentController.Frame.ContainerWindow.PosSize
        WindowWidth = FramePosSize.Width
        dialogModel.Width = 200
        dialogModel.Height = 100
        dialogModel.PositionX = (WindowWidth/2.2) - 105
        dialogModel.PositionY = 30
        dialogModel.Title = 'Loading sources'

        statusTxtModel = addControl('com.sun.star.awt.UnoControlFixedTextModel',
                               dialogModel, 6, 2, 190, 20, 'Initializing',
                               'statusLabel')

        counterTxtModel = addControl('com.sun.star.awt.UnoControlFixedTextModel',
                             dialogModel, 6, 30, 190, 50, '0',
                             'counterLabel')
        
        self.progressContainer = self.ctx.ServiceManager.createInstanceWithContext('com.sun.star.awt.UnoControlDialog', self.ctx)
        
        self.progressContainer.setModel(dialogModel)
        self.progressContainer.setVisible(True)

        self.statusControl = self.progressContainer.getControl('statusLabel').getModel()
        self.counterControl = self.progressContainer.getControl('counterLabel').getModel()

    def updateProgressDlg(self,statusStr,counterStr):
        self.statusControl.Label=statusStr
        self.counterControl.Label=counterStr