This is a script I have at work, it is running fine (gives the output I want).
Any comments are very welcome.
It queries a database and gives out a reportlab pdf file with a balance sheet of
costs per department.
In the first version there were neither functions nor classes, it worked as
well, but with no reusability.
A second version I defined the functions very close to the ones found here, but
had lots of problems because the variables were all local to each function. So
I changed direction to this final version.
Maybe the matter here is not best suited for OOP. The class definition sounds
more like action than object...
I think the Query method is the peace of code that is more in need of
improvement...
Paulino
:::::::::::::::::::::::::::::::::::::::::
# -*- coding: latin-1 -*-
# Balancete por departamento
import sys, re
# Importa modulo MS SQL
import pymssql
con=pymssql.connect(host='gi-clsql', database='baandb',user='baan',
password='baan' )
cur=con.cursor()
# Importa os modulos ReportLab
from reportlab.platypus import *
from reportlab.lib.styles import ParagraphStyle
from reportlab.lib.units import cm
from reportlab.lib import colors
from reportlab.platypus.flowables import Flowable
def FormCGI() :
"""Cathes the values 'ano', 'mes' and 'dept' from an HTML form """
import cgi
import cgitb; cgitb.enable()
form=cgi.FieldStorage()
try :
ano=form["ano"].value
mes=form["mes"].value
dept=form["custo"].value
return ano, mes, dept
except KeyError :
print "Content-type: text/html\n\n"
print """<html><body>
<h3>Balancete por centro de custo</h3>
<p><b>É necessário preencher todos os campos</b></p>
<form action="balcc01.pyw" method="POST" target="principal">
Centro de custo: <input name="custo" type="text" /><br>
Exercicio: <input name="ano" type="text" value="2006"/>
Periodo até: <input name="mes" type="text" value="12"/>
<input type="submit" value="Listar" />
</body></html> """
sys.exit(0)
class BalanceteCusto:
""" Performs a query in a MS SQL database and returns a pdf file containing
a report of costs per department """
def __init__(self, ano, mes, dept):
self.ano = ano
self.mes = mes
self.dept = dept
self.ppistyle = ParagraphStyle('normal', fontName='helvetica',
fontSize=8 )
self.pag=( 21*cm, 29.7*cm)
self.PL=self.pag[0]
self.PA=self.pag[1] # Largura, altura
self.meses={1:'Janeiro', 2:'Fevereiro', 3:'Marco', 4:'Abril', 5:'Maio',
6:'Junho', 7:'Julho',
8:'Agosto', 9:'Setembro', 10:'Outubro', 11:'Novembro',
12:'Dezembro', 13:'Regularizacoes'}
self.titulo="BALANCETE POR CENTRO DE CUSTO"
self.pdf = 'Balancetecc%s.pdf' %(self.dept)
#### Definição dos formatos da tabela
self.st=[('ALIGN', (2, 0), (-1,-1), 'RIGHT') ,
('FONT', (0,1), (-1,-1), 'Helvetica', 8),
('FONT', (0,0), (-1, 0), 'Helvetica', 9),
('FONT', (0,-1), (-1, -1), 'Helvetica', 9),
('LINEBELOW', (0,0), (-1,0), 1, colors.blue),
('TOPPADDING', (0,1), (-1,-1), 1),
('BOTTOMPADDING', (0,1), (-1,-1), 1) ]
self.Query()
self.CriaPDF()
def Formata(self,num):
"""Formats numbers as the local settings"""
import locale
locale.setlocale(locale.LC_ALL, '')
if num == 0.0 : return ''
else : return locale.format("%.*f", (2, num), True)
def Pagina(self, canvas, doc) :
"""Sets the fixed content of the pages (reportlab)"""
# Cabeçalho
canvas.setFont('Helvetica', 13)
# Titulo centrado no papel
canvas.drawCentredString( self.PL / 2.0, self.PA - 2*cm, self.titulo)
canvas.setFont('Helvetica', 10)
#canvas.drawRightString(PL-2.3*cm, PA-3.1*cm, 'De '+dini+' a '+dfim)
canvas.drawString( 3.5*cm, self.PA - 2.6*cm, self.subtit1)
canvas.drawString( 3.5*cm, self.PA - 3.1*cm, self.subtit2)
# Rodape - lado direito
canvas.setFont('Helvetica', 8)
canvas.drawRightString( self.PL-60, 0.8*cm, 'Pagina %d' %(doc.page) )
canvas.line( 60, 30, self.PL-60, 30)
def Acumula(self, item, ctpai) :
"""Calculates grand-total and subtotals for the itermediate accounts"""
tdeb = self.acum[ctpai][1] + item[1]
tcred = self.acum[ctpai][2] + item[2]
self.acum[ctpai] = [ self.contas[ctpai], tdeb, tcred ]
def Desc(self, texto) :
"""Encodes text as required by reportlab"""
#return Paragraph( unicode( re.sub('&', '&', texto) , 'latin-1'),
style=ppistyle )
return unicode( texto, 'latin-1')
def Query( self ) :
cur.execute( """SELECT A.t_leac, SUM(A.t_fdam), SUM(A.t_fcam)
FROM ( SELECT c.t_leac, c.t_fdam, c.t_fcam
FROM ttfgld202100 c
WHERE c.t_cono=100 AND c.t_ptyp=1 AND c.t_year=%s AND c.t_prno<%d
AND c.t_dtyp=5 AND c.t_dimx='%s'
AND c.t_leac BETWEEN '6' AND '79999') A
GROUP BY A.t_leac """ % (self.ano, eval(self.mes)+1, self.dept ) )
valores=cur.fetchall()
# Obtem descritivo das contas e centro de custo
cur.execute("SELECT gld8.t_leac, gld8.t_desc FROM ttfgld008100 gld8 " )
self.contas={}
for i in cur.fetchall() :
self.contas[i[0].strip() ] = i[1].strip()
cur.execute("SELECT g10.t_desc FROM ttfgld010100 g10 WHERE g10.t_dtyp=5
AND g10.t_dimx='%s'" %(self.dept) )
self.deptdesc=cur.fetchone()
self.acum={}
self.final=[['Conta', 'Descricao', 'Debito', 'Credito', 'Saldo']]
saldo, tdeb, tcred = 0, 0, 0
for v in valores :
cta = v[0].strip()
saldo=saldo+v[1]-v[2]
tdeb=tdeb+v[1]
tcred=tcred+v[2]
self.acum[cta]=[self.contas[cta], v[1], v[2] ]
for n in range(1, len(cta)-1 ) :
if cta[:-n] in self.contas.keys() :
if cta[:-n] in self.acum.keys() : self.Acumula( v, cta[:-n])
else :
self.acum[ cta[:-n] ] = [ self.contas[cta[:-n]], 0, 0 ]
self.Acumula( v, cta[:-n])
ak=self.acum.keys()
ak.sort()
for v in ak :
linpdf=[ v, self.Desc( self.acum[v][0] ),
self.Formata( self.acum[v][1] ), self.Formata( self.acum[v][2]
), self.Formata( self.acum[v][1]-self.acum[v][2] ) ]
self.final.append(linpdf)
self.final.append( ['', 'Total', self.Formata(tdeb),
self.Formata(tcred), self.Formata(tdeb - tcred) ] )
def CriaPDF(self):
doc = SimpleDocTemplate(self.pdf, pagesize=self.pag, topMargin=3*cm,
bottomMargin=cm, rightMargin=2.5*cm, leftMargin=2.5*cm)
tab=Table( self.final, colWidths=(50, 180, 70, 70, 70), style=self.st,
repeatRows=1 )
story=[tab]
self.subtit1="Centro de custo: %s - %s " %(self.dept,
unicode(self.deptdesc[0], 'latin-1') )
self.subtit2="Ano %s, acumulado ate ao mes de %s" % (self.ano,
self.meses[eval(self.mes)] )
doc.build(story, onFirstPage=self.Pagina, onLaterPages=self.Pagina)
def GeraHTML(self):
print "Content-Type:text/html\n\n<html><body><h2>%s</h2>" %(self.titulo)
print '<a href="/%s" target="_blank">VISUALIZAR</a>' %(self.pdf)
print '</body></html>'
if __name__ == '__main__' :
for dpt in ('13', '14', '15') :
BalanceteCusto( '2006', '10', dpt)
con.close()
___________________________________________________________________
O SAPO já está livre de vírus com a Panda Software, fique você também!
Clique em: http://antivirus.sapo.pt
_______________________________________________
Tutor maillist - Tutor@python.org
http://mail.python.org/mailman/listinfo/tutor
