7:30 – 5:30

• Working on writing a script that does something with Dong’s data

Inserting data for months – no invoice though

jf = ScriptFacades()

table = jf.dbQuery('project_portfolio', 'SELECT uid from obligations_outlays where project_id=1')
tableData = table.getTable()
print tableData[1:]

for uid in tableData[1:]:
    for month in xrange(1,25):
        monthName = 'month_'+str(month)
        query = 'UPDATE obligations_outlays set '+str(monthName)+' = '+str(random.randint(1000,2000)*month)+' where uid = '+str(uid[0])
        print query
        jf.dbQuery('project_portfolio', query)

Getting data and publishing

# This method converts a DbTable (A list of lists) into an Apache SOLR xml document
def solrXmlFromListList(doc, root, headers, table):
    for row in table:
        rowNode = doc.createElement('doc')
        root.appendChild(rowNode)
        #print row
        for i in xrange(len(headers)):
            #print str(headers[i])+" = "+str(row[i])
            fieldNode = doc.createElement('field')
            fieldNode.setAttribute('name', headers[i])
            textNode = doc.createTextNode(str(row[i]))
            fieldNode.appendChild(textNode)
            rowNode.appendChild(fieldNode)

# Strip off a column based on its name
def stripColumn(headers, table, columnName):
    newRow = []
    for item in headers:
        if item != columnName:
            newRow.append(item)
    newTable = [];
    newTable.append(newRow)
    for row in table:
        newRow = []
        for i in xrange(len(headers)):
            if headers[i] != columnName:
                newRow.append(row[i])
        newTable.append(newRow)
    return newTable

# Change month_xx to a year/date string
def mapDates(row, year):
    for i in xrange(len(row)):
        if row[i].count("month_") > 0:
            splits = row[i].split('_');
            month = (int(splits[1])-1)%12
            addYear = (int(splits[1])-1)/12
            row[i] = str(month+1)+"-1-"+str(year+addYear)
            #print row[i]

#################### 'main'

jf = ScriptFacades()
table = jf.dbQuery('project_portfolio', 'SELECT * from obligations_outlays where project_id=1 and year=2010')
tableData = table.getTable()
newTable = stripColumn(tableData[:1][0], tableData[1:], 'uid')

mapDates(newTable[:1][0], 2010)

impl = getDOMImplementation()
myDoc = impl.createDocument(None, "add", None)
rootElement = myDoc.documentElement
solrXmlFromListList(myDoc, rootElement, newTable[:1][0], newTable[1:])
#print myDoc.toprettyxml()
jf.publishXml("/obligation_outlays_proj_1_year_2010.xml", myDoc.toprettyxml())