tengusoldier

Creating reports in Python using data from a query and postgresql

Discussion created by tengusoldier on Dec 2, 2013
I am trying to code in python in order to get a report on prices depending on their taxes. the template is as follows:

04|85|%s|||||%s|||%s||||||||%s||||
22 columns total, first two columns always have 04 and 85 set, the third column is where the data from vat goes in while the other 3 columns (8th, 11th, and 19th). vat codes cannot be repeated and thus I added the SUM function to the query command but the hard part for me is juggling the price_subtotal and setting it in the correct column depending on the tax.

3236 = 0%tax
3237 = 16%tax
3238 = 11% tax
For example

if the query finds price_subtotal is 2,1,3 where the account_paid_id is 3237 and then finds another price_subtotal with 1,4 wherethe account_paid_id is 3738, the resuñlting report would be like this, assuming vat is dgfsasf.

04|85|dgfsasf|||||6|||5||||||||||||
vat went into the third column and all the values for the 8th column were added together as well asthe ones for the 11th, the 19th column was left empty as there wereno values with an account_paid_id of 3236 associated with the vat code "dgfsasf".

My question is, how to write code in python that defines that logic, set the data on that template using these rules and provide a proper report after it does so.


The code is as follows:



import psycopg2
import sys

con = None

try:

    con = psycopg2.connect(database='MyDb', user='me', password='ppassedd')

    cur = con.cursor()  
    cur.execute("

Select Account_Invoice_Line.price_subtotal,
right (Res_Partner.vat,length(Res_Partner.vat)-2)
from Account_Invoice_Line
inner join Res_Partner on Account_Invoice_Line.partner_id = Res_Partner.id
inner join Account_Invoice on Account_Invoice_Line.invoice_id = Account_Invoice.id
join Account_Invoice_Line_Tax
    join Account_Tax on Account_Invoice_Line_Tax.tax_id = Account_Tax.id
on Account_Invoice_Line.id = Account_Invoice_Line_Tax.invoice_line_id
where account_invoice.journal_id=2
and account_invoice.date_invoice >= '2013-01-01'
and account_invoice.date_invoice <= '2013-12-31'
and account_invoice.reconciled is TRUE
and account_invoice.amount_tax >= 0
and Account_Tax.account_paid_id in (3236, 3238, 3237)
")

rows = cur.fetchall()

     f=file("newfile.txt","w")
     for row in rows: f.write("04|85|%s|||||%s|||%s||||||||%s||||\n" % (row[1],row[0]))
     f.close()

    for row in rows:
        print row

except psycopg2.DatabaseError, e:
    print 'Error %s' % e  
    sys.exit(1)

finally:

    if con:
        con.close()

Outcomes