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') ]]>