clear()
= \'%s\' and od.date < \'%s\') group by p.canonical_name order by p.canonical_name' % (global_begin_date, global_end_date))
more = resultSetOD.firstRecord()
while more:
ingresos[resultSetOD.current.getField('canonical_name')[0:40]] = {}
more = resultSetOD.nextRecord()
resultSetAD = alienDocumentDataSource.createRawResultSet('select p.canonical_name from _product_data as p, alien_item as ai, alien_product_item as api, alien_document ad, _document_type_data dt where api.alien_item = ai.id and api.product = p.id and ai.alien_document = ad.id and ad.alien_document_type = dt.id and ad.alien_document_type in (2,10,11,18,19,20,33,34,35) and (not ad.is_void or ad.is_void is null) and (ad.date >= \'%s\' and ad.date < \'%s\') group by p.canonical_name order by p.canonical_name' % (global_begin_date, global_end_date))
more = resultSetAD.firstRecord()
while more:
egresos[resultSetAD.current.getField('canonical_name')[0:40]] = {}
more = resultSetAD.nextRecord()
resultSetODT = ownDocumentTaxDataSource.createRawResultSet('select t.name from own_document od, own_document_tax odt, _tax_data t where od.id = odt.own_document and odt.tax = t.id and od.own_document_type in (4,8,9,14,15,16,30,31,32) and (not od.is_void or od.is_void is null) and (od.date >= \'%s\' and od.date < \'%s\') group by t.name order by name' % (global_begin_date, global_end_date))
more = resultSetODT.firstRecord()
while more:
impuestos_ingresos[resultSetODT.current.getField('name')[0:40]] = {}
more = resultSetODT.nextRecord()
resultSetADT = alienDocumentTaxDataSource.createRawResultSet('select t.name from alien_document ad, alien_document_tax adt, _tax_data t where ad.id = adt.alien_document and adt.tax = t.id and ad.alien_document_type in (2,10,11,18,19,20,33,34,35) and (not ad.is_void or ad.is_void is null) and (ad.date >= \'%s\' and ad.date < \'%s\') group by t.name order by name' % (global_begin_date, global_end_date))
more = resultSetADT.firstRecord()
while more:
impuestos_egresos[resultSetADT.current.getField('name')[0:40]] = {}
more = resultSetADT.nextRecord()
#### Ingresos #####################################
for i in range(1,13):
date = global_begin_date[0:4]+'-%s-01'
if i < 10:
date = date % ("0"+str(i))
else:
date = date % str(i)
begin_date = date
date = global_begin_date[0:4]+'-%s-01'
if i == 12:
date = global_end_date
elif i+1 < 10:
date = date % ("0"+str(i+1))
else:
date = date % str(i+1)
end_date = date
resultSetOD = ownDocumentDataSource.createRawResultSet('select p.canonical_name, sum((opi.unit_price * opi.qty - oi.discount) * CASE dt.sign WHEN true THEN -1 ELSE 1 END) as amount from _product_data as p, _price_data pce, own_item as oi, own_product_item as opi, own_document od, _document_type_data dt where opi.own_item = oi.id and opi.price = pce.id and pce.product = p._table and p._end_t is null and oi.own_document = od.id and od.own_document_type = dt.id and od.own_document_type in (4,8,9,14,15,16,30,31,32) and (not od.is_void or od.is_void is null) and (od.date >= \'%s\' and od.date < \'%s\') group by p.canonical_name order by p.canonical_name' % (begin_date, end_date))
more = resultSetOD.firstRecord()
while more:
ingreso_aux = str(resultSetOD.current.getField('canonical_name'))[0:40]
mes_amount = ('%6.0f' % hack.a2f(resultSetOD.current.getField('amount'),4)).strip().rjust(6)
ingresos[ingreso_aux][meses[i-1]] = mes_amount
more = resultSetOD.nextRecord()
resultSetODT = ownDocumentTaxDataSource.createRawResultSet('select t.name, sum(odt.amount) as amount from own_document od, own_document_tax odt, _tax_data t where od.id = odt.own_document and odt.tax = t.id and od.own_document_type in (4,8,9,14,15,16,30,31,32) and (not od.is_void or od.is_void is null) and (od.date >= \'%s\' and od.date < \'%s\') group by t.name order by name' % (begin_date, end_date))
more = resultSetODT.firstRecord()
while more:
ingreso_aux = str(resultSetODT.current.getField('name'))[0:40]
mes_amount = ('%6.0f' % hack.a2f(resultSetODT.current.getField('amount'),4)).strip().rjust(6)
impuestos_ingresos[ingreso_aux][meses[i-1]] = mes_amount
more = resultSetODT.nextRecord()
### Egresos ################################################
for i in range(1,13):
date = global_begin_date[0:4]+'-%s-01'
if i < 10:
date = date % ("0"+str(i))
else:
date = date % str(i)
begin_date = date
date = global_begin_date[0:4]+'-%s-01'
if i == 12:
date = global_end_date
elif i+1 < 10:
date = date % ("0"+str(i+1))
else:
date = date % str(i+1)
end_date = date
resultSetAD = alienDocumentDataSource.createRawResultSet('select p.canonical_name, sum((api.unit_price * api.qty - ai.discount) * CASE dt.sign WHEN true THEN 1 ELSE -1 END) as amount from _product_data as p, alien_item as ai, alien_product_item as api, alien_document ad, _document_type_data dt where api.alien_item = ai.id and api.product = p.id and ai.alien_document = ad.id and ad.alien_document_type = dt.id and ad.alien_document_type in (2,10,11,18,19,20,33,34,35) and (not ad.is_void or ad.is_void is null) and (ad.date >= \'%s\' and ad.date < \'%s\') group by p.canonical_name order by p.canonical_name' % (begin_date, end_date))
more = resultSetAD.firstRecord()
while more:
egreso_aux = str(resultSetAD.current.getField('canonical_name'))[0:40]
mes_amount = ('%6.0f' % hack.a2f(resultSetAD.current.getField('amount'),4)).strip().rjust(6)
egresos[egreso_aux][meses[i-1]] = mes_amount
more = resultSetAD.nextRecord()
resultSetADT = alienDocumentTaxDataSource.createRawResultSet('select t.name, sum(adt.amount) as amount from alien_document ad, alien_document_tax adt, _tax_data t where ad.id = adt.alien_document and adt.tax = t.id and ad.alien_document_type in (2,10,11,18,19,20,33,34,35) and (not ad.is_void or ad.is_void is null) and (ad.date >= \'%s\' and ad.date < \'%s\') group by t.name order by name' % (begin_date, end_date))
more = resultSetADT.firstRecord()
while more:
egreso_aux = str(resultSetADT.current.getField('name'))[0:40]
mes_amount = ('%6.0f' % hack.a2f(resultSetADT.current.getField('amount'),4)).strip().rjust(6)
impuestos_egresos[egreso_aux][meses[i-1]] = mes_amount
more = resultSetADT.nextRecord()
### Construcción del informe
## Ingresos
totalXmes = 0
row_aux = {}
ingresos_aux = ingresos.keys()
ingresos_aux.sort()
for ingreso in ingresos_aux:
row_aux['income'] = ingreso
for mes in ingresos[ingreso].keys():
amount_aux = ('%6.0f' % hack.a2f(ingresos[ingreso][mes],4)).strip().rjust(6)
totalXmes += hack.a2f(amount_aux)
row_aux[mes] = amount_aux
row_aux['total'] = ('%10.0f' % hack.a2f(totalXmes)).strip().rjust(6)
totalXmes = 0
resultStateInf.income.income_item.add_row(**row_aux)
row_aux = {}
row_aux = {}
ingresos_aux = impuestos_ingresos.keys()
ingresos_aux.sort()
for ingreso in ingresos_aux:
row_aux['income'] = ingreso
for mes in impuestos_ingresos[ingreso].keys():
amount_aux = ('%6.0f' % hack.a2f(impuestos_ingresos[ingreso][mes],4)).strip().rjust(6)
totalXmes += hack.a2f(amount_aux)
row_aux[mes] = amount_aux
row_aux['total'] = ('%10.0f' % hack.a2f(totalXmes)).strip().rjust(6)
totalXmes = 0
resultStateInf.income.income_item.add_row(**row_aux)
row_aux = {}
## Cierre de totales de ingreso
resultSetTotalNeto = ownDocumentDataSource.createRawResultSet('select sum(od.net_total * CASE dt.sign WHEN true THEN -1 ELSE 1 END) as amount from own_document od, _document_type_data dt where od.own_document_type = dt.id and od.own_document_type in (4,8,9,14,15,16,30,31,32) and (not od.is_void or od.is_void is null) and (od.date >= \'%s\' and od.date < \'%s\') ' % (global_begin_date, global_end_date))
resultSetTotalNeto.firstRecord()
total = hack.a2f(resultSetTotalNeto.current.getField('amount'),4)
resultSetTotalImpuestos = ownDocumentDataSource.createRawResultSet('select sum(odt.amount * CASE dt.sign WHEN true THEN -1 ELSE 1 END) as amount from own_document od, own_document_tax odt, _document_type_data dt where od.id = odt.own_document and od.own_document_type = dt.id and od.own_document_type in (4,8,9,14,15,16,30,31,32) and (not od.is_void or od.is_void is null) and (od.date >= \'%s\' and od.date < \'%s\') ' % (global_begin_date, global_end_date))
resultSetTotalImpuestos.firstRecord()
total += hack.a2f(resultSetTotalImpuestos.current.getField('amount'),4)
resultStateInf.totales.page_header(total=('%6.0f' % total).strip().rjust(6))
resultSetTotalCobrado = ownDocumentDataSource.createRawResultSet("select sum(odp.amount * CASE dt.sign WHEN true THEN -1 ELSE 1 END) as amount from own_document_proceeding odp, own_document od, _document_type_data dt where od.id = odp.own_document and od.own_document_type = dt.id and od.own_document_type in (4,8,9,14,15,16,30,31,32) and (not od.is_void or od.is_void is null) and (od.date >= \'%s\' and od.date < \'%s\') " % (global_begin_date, global_end_date))
more = resultSetTotalCobrado.firstRecord()
resultStateInf.totales.page_header(total_cobrado=('%6.0f' % hack.a2f(resultSetTotalCobrado.current.getField('amount'),4)).strip().rjust(6))
resultStateInf.totales.page_header(total_adeudado=('%6.0f' % (total - hack.a2f(resultSetTotalCobrado.current.getField('amount'),4))).strip().rjust(6))
## Egresos
row_aux = {}
egresos_aux = egresos.keys()
egresos_aux.sort()
for egreso in egresos_aux:
row_aux['egreso'] = egreso
for mes in egresos[egreso].keys():
amount_aux = ('%6.0f' % hack.a2f(egresos[egreso][mes],4)).strip().rjust(6)
totalXmes += hack.a2f(amount_aux)
row_aux[mes] = amount_aux
row_aux['total'] = ('%10.0f' % hack.a2f(totalXmes)).strip().rjust(6)
totalXmes = 0
resultStateInf.egreso.egreso_item.add_row(**row_aux)
row_aux = {}
row_aux = {}
egresos_aux = impuestos_egresos.keys()
egresos_aux.sort()
for egreso in egresos_aux:
row_aux['egreso'] = egreso
for mes in impuestos_egresos[egreso].keys():
amount_aux = ('%6.0f' % hack.a2f(impuestos_egresos[egreso][mes],4)).strip().rjust(6)
totalXmes += hack.a2f(amount_aux)
row_aux[mes] = amount_aux
row_aux['total'] = ('%10.0f' % hack.a2f(totalXmes)).strip().rjust(6)
totalXmes = 0
resultStateInf.egreso.egreso_item.add_row(**row_aux)
row_aux = {}
## Cierre de totales de egreso
begin_date = str(printBlock.printBox.yearEntry).strip()+"-01-01"
end_date = str(hack.a2f(printBlock.printBox.yearEntry,0)+1).strip()+"-01-01"
resultSetTotalNeto = alienDocumentDataSource.createRawResultSet('select sum(ad.net_total * CASE dt.sign WHEN true THEN 1 ELSE -1 END) as amount from alien_document ad, _document_type_data dt where ad.alien_document_type = dt.id and ad.alien_document_type in (2,10,11,18,19,20,33,34,35) and (not ad.is_void or ad.is_void is null) and (ad.date >= \'%s\' and ad.date < \'%s\') ' % (global_begin_date, global_end_date))
resultSetTotalNeto.firstRecord()
total = hack.a2f(resultSetTotalNeto.current.getField('amount'),4)
resultSetTotalImpuestos = alienDocumentDataSource.createRawResultSet('select sum(adt.amount * CASE dt.sign WHEN true THEN 1 ELSE -1 END) as amount from alien_document ad, alien_document_tax adt, _document_type_data dt where ad.id = adt.alien_document and ad.alien_document_type = dt.id and ad.alien_document_type in (2,10,11,18,19,20,33,34,35) and (not ad.is_void or ad.is_void is null) and (ad.date >= \'%s\' and ad.date < \'%s\') ' % (global_begin_date, global_end_date))
resultSetTotalImpuestos.firstRecord()
total += hack.a2f(resultSetTotalImpuestos.current.getField('amount'),4)
resultStateInf.totales_egr.page_header(total=('%6.0f' % total).strip().rjust(6))
resultSetTotalCobrado = alienDocumentDataSource.createRawResultSet('select sum(adp.amount * CASE dt.sign WHEN true THEN 1 ELSE -1 END) as amount from alien_document_proceeding adp, alien_document ad, _document_type_data dt where ad.id = adp.alien_document and ad.alien_document_type = dt.id and ad.alien_document_type in (2,10,11,18,19,20,33,34,35) and (not ad.is_void or ad.is_void is null) and (ad.date >= \'%s\' and ad.date < \'%s\') ' % (global_begin_date, global_end_date))
more = resultSetTotalCobrado.firstRecord()
resultStateInf.totales_egr.page_header(total_pagado=('%6.0f' % hack.a2f(resultSetTotalCobrado.current.getField('amount'),4)).strip().rjust(6))
resultStateInf.totales_egr.page_header(total_adeudado=('%6.0f' % (total - hack.a2f(resultSetTotalCobrado.current.getField('amount'),4))).strip().rjust(6))
callTrigger('endWaitingModeTrigger')
printing.previewPrint([resultStateInf.render()],'xless -fn vga')
]]>