383 lines
17 KiB
Python
383 lines
17 KiB
Python
from django.db import transaction
|
|
from django.db import connection
|
|
|
|
from .models import AcumuladoMes, Registro, CuotasCondominio, Movimiento, Condomino, CuentaBanco, Asiento
|
|
|
|
from catalogos.models import CuentaContable, PeriodoCorte
|
|
|
|
from ciec.procesos import dictfetchall, execsql, styles_workbook
|
|
|
|
from datetime import datetime, timedelta
|
|
|
|
import pandas as pd
|
|
|
|
def run_estado_cuenta(condominos, filename):
|
|
consulta = '''
|
|
select r.id, c.depto, c.poseedor, r.fecha as FECHA, r.descripcion as DESCRIPCION,
|
|
r.haber as CARGOS, r.debe as DEPOSITOS, r.saldo as SALDO
|
|
from condominio_coyoacan_registro r,
|
|
condominio_coyoacan_condomino c
|
|
where r.condomino_id = c.id
|
|
and c.id = {id}
|
|
order by r.fecha desc, r.id desc
|
|
'''
|
|
writer = pd.ExcelWriter(filename,
|
|
engine='xlsxwriter',
|
|
datetime_format='dd/mmm/yyyy',
|
|
date_format='dd/mmm/yyyy')
|
|
for c in condominos:
|
|
q = consulta.format(id = c['id'])
|
|
rows = execsql(q)
|
|
df = pd.DataFrame(rows)
|
|
df['cargos'] = df['cargos'].astype(float)
|
|
df['depositos'] = df['depositos'].astype(float)
|
|
df['saldo'] = df['saldo'].astype(float)
|
|
df = df.drop(columns = ['id', 'depto', 'poseedor'])
|
|
df = df.rename(columns = {'fecha' : 'Fecha',
|
|
'descripcion' : 'Descripcion',
|
|
'cargos' : 'Cargos',
|
|
'depositos' : 'Depositos',
|
|
'saldo' : 'Saldo'
|
|
})
|
|
hoja = c['depto']
|
|
dfR = df.rename_axis(None)
|
|
i = len(dfR.index) + 10
|
|
j = len(dfR.index) + 7
|
|
dfR.to_excel(writer, sheet_name = hoja, startrow = 5)
|
|
titulo = " CONDOMINIO COYOACAN 1045 "
|
|
subtitulo = " ESTADO DE CUENTA DEL DEPARTAMENTO {depto} {poseedor} ".format(depto = hoja,
|
|
poseedor = c['poseedor'])
|
|
subtitulob = " SALDO AL {} POR $ {:,}".format('CORTE', c['adeudo'])
|
|
workbook = writer.book
|
|
worksheet = writer.sheets[hoja]
|
|
|
|
#formatos = styles_workbook(workbook)
|
|
|
|
f_titulos = workbook.add_format({'bold': True, 'align': 'center', 'valign': 'vcenter'})
|
|
f_celdas = workbook.add_format({'align': 'center', 'valign': 'vcenter'})
|
|
f_bordes = workbook.add_format({'bottom': 1})
|
|
f_descripcion = workbook.add_format({'align': 'left', 'valign': 'vjustify'})
|
|
f_numeros = workbook.add_format({'num_format': '#,##0.00', 'valign': 'vcenter'})
|
|
f_fechas = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'num_format' : 'dd/mmm/yyyy' })
|
|
f_cabeceras = workbook.add_format({'bold': True, 'text_wrap': True, 'valign': 'top', 'align' : 'center', 'fg_color': '#D7E4BC', 'border': 1})
|
|
f_condicion1 = workbook.add_format({'bold': True, 'bg_color': '#FFC7CE', 'font_color': '#9C0006'})
|
|
f_condicion2 = workbook.add_format({'bold': True, 'bg_color': '#C6EFCE', 'font_color': '#006100'})
|
|
f_ocultar = workbook.add_format({'font_color': '#FFFFFF'})
|
|
|
|
worksheet.set_column('B:B', 15, f_fechas)
|
|
worksheet.set_column('D:F', 15, f_numeros)
|
|
worksheet.set_column('C:C', 50, f_descripcion)
|
|
worksheet.merge_range('B2:F2', titulo, f_titulos)
|
|
worksheet.merge_range('B3:F3', subtitulo, f_titulos)
|
|
worksheet.merge_range('B4:F4', subtitulob, f_titulos)
|
|
|
|
worksheet.conditional_format('D7:D' + str(i),
|
|
{'type' : 'cell',
|
|
'criteria' : '=',
|
|
'value' : 0,
|
|
'format' : f_ocultar
|
|
})
|
|
|
|
worksheet.conditional_format('E7:E' + str(i),
|
|
{'type' : 'cell',
|
|
'criteria' : '=',
|
|
'value' : 0,
|
|
'format' : f_ocultar
|
|
})
|
|
|
|
worksheet.conditional_format('B7:C' + str(i),
|
|
{'type' : 'formula',
|
|
'criteria' : '=$E7>0',
|
|
'format' : f_condicion2
|
|
})
|
|
|
|
worksheet.conditional_format('B4:B4',
|
|
{'type' : 'formula',
|
|
'criteria' : '=$F$7>0',
|
|
'format' : f_condicion1
|
|
})
|
|
|
|
worksheet.conditional_format('B4:B4',
|
|
{'type' : 'formula',
|
|
'criteria' : '=$F$7<=0',
|
|
'format' : f_condicion2
|
|
})
|
|
|
|
for col_num, value in enumerate(dfR.columns.values):
|
|
worksheet.write(5, col_num + 1, value, f_cabeceras)
|
|
|
|
worksheet.conditional_format('B7:N' + str(i),
|
|
{'type': 'cell',
|
|
'criteria': '<>',
|
|
'value': '""',
|
|
'format': f_bordes})
|
|
|
|
worksheet.set_portrait()
|
|
worksheet.set_paper(1)
|
|
worksheet.center_horizontally()
|
|
#worksheet.center_vertically()
|
|
worksheet.repeat_rows(0, 5)
|
|
worksheet.print_area('B7:G' + str(i))
|
|
worksheet.fit_to_pages(1, 0)
|
|
#worksheet.hide_gridlines(0)
|
|
worksheet.set_header('&LCIEC Cuotas, Ingresos y Egresos Condominales &Rciec.adanisoft.com')
|
|
worksheet.set_footer('&CPágina &P de &N &RImpreso el &D')
|
|
|
|
writer.close()
|
|
return filename.getvalue()
|
|
|
|
def run_detalle_ingresos_egresos(fecha_inicial, fecha_final, filename):
|
|
consulta = '''
|
|
select min(fecha_movimiento) as fecha_inicio,
|
|
max(fecha_movimiento) as fecha_fin,
|
|
count(cc.num_cuenta) as cuantos,
|
|
cc.num_cuenta as cuenta,
|
|
cc.descripcion as conepto,
|
|
comentario as subconcepto,
|
|
case when sum(m.deposito) > 0 then sum(d.monto) else 0 end as deposito,
|
|
case when sum(m.retiro) > 0 then sum(d.monto) else 0 end as retiro
|
|
from condominio_coyoacan_detalle_movimiento d,
|
|
condominio_coyoacan_movimiento m,
|
|
cuenta_contable cc
|
|
where d.movimiento_id = m.id
|
|
and d.cuenta_contable_id = cc.id
|
|
and m.fecha_movimiento >= '{ini}'
|
|
and m.fecha_movimiento <= '{fin}'
|
|
group by cc.descripcion, comentario, cc.num_cuenta order by 4,5,6
|
|
'''
|
|
consulta = consulta.format(ini = fecha_inicial.strftime('%Y-%m-%d'), fin = fecha_final.strftime('%Y-%m-%d'))
|
|
rows = execsql(consulta)
|
|
df = pd.DataFrame(rows)
|
|
df['deposito'] = df['deposito'].astype(float)
|
|
df['retiro'] = df['retiro'].astype(float)
|
|
df = df.drop(columns = ['cuenta'])
|
|
df = df.rename(columns = { 'fecha_inicio': 'Fecha inicio',
|
|
'fecha_fin' : 'Fecha final',
|
|
'cuantos' : 'Cantidad',
|
|
'concepto' : 'Concepto',
|
|
'subconcepto' : 'subConcepto',
|
|
'deposito' : 'Deposito',
|
|
'retiro' : 'Retiro'
|
|
})
|
|
writer = pd.ExcelWriter(filename, engine='xlsxwriter', datetime_format='dd/mm/yyyy', date_format='dd/mm/yyyy')
|
|
titulo = "CONDOMINIO COYOACAN 1045"
|
|
subtitulo = "DETALLE DE INGRESOS Y EGRESOS DESDE EL {} HASTA EL {}".format(fecha_inicial.strftime('%d/%m/%Y'), fecha_final.strftime('%d/%m/%Y'))
|
|
dfR = df.rename_axis(None)
|
|
dfR.to_excel(writer, sheet_name = 'HOJA', startrow = 5)
|
|
i = len(dfR.index) + 10
|
|
j = len(dfR.index) + 7
|
|
workbook = writer.book
|
|
worksheet = writer.sheets['HOJA']
|
|
#
|
|
f_titulos = workbook.add_format({'bold': True, 'align': 'center', 'valign': 'vcenter'})
|
|
f_celdas = workbook.add_format({'align': 'center', 'valign': 'vcenter'})
|
|
f_bordes = workbook.add_format({'bottom': 1})
|
|
f_descripcion = workbook.add_format({'align': 'left', 'valign': 'vjustify'})
|
|
f_numeros = workbook.add_format({'num_format': '#,##0.00', 'valign': 'vcenter'})
|
|
f_fechas = workbook.add_format({'align': 'center', 'valign': 'vcenter', 'num_format' : 'dd/mmm/yyyy' })
|
|
f_cabeceras = workbook.add_format({'bold': True, 'text_wrap': True, 'valign': 'top', 'align' : 'center', 'fg_color': '#D7E4BC', 'border': 1})
|
|
f_condicion1 = workbook.add_format({'bold': True, 'bg_color': '#FFC7CE', 'font_color': '#9C0006'})
|
|
f_condicion2 = workbook.add_format({'bold': True, 'bg_color': '#C6EFCE', 'font_color': '#006100'})
|
|
f_ocultar = workbook.add_format({'font_color': '#FFFFFF'})
|
|
|
|
worksheet.set_column('B:C', 15, f_fechas )
|
|
worksheet.set_column('G:H', 15, f_numeros )
|
|
worksheet.set_column('E:E', 40)
|
|
worksheet.set_column('F:F', 20)
|
|
worksheet.set_column('D:D', 10, f_celdas)
|
|
worksheet.merge_range('B2:I2', titulo, f_titulos)
|
|
worksheet.merge_range('B3:I3', subtitulo, f_titulos)
|
|
worksheet.write('G' + str(j), '=SUM(G7:G{row})'.format(row=j-1))
|
|
worksheet.write('H' + str(j), '=SUM(H7:H{row})'.format(row=j-1))
|
|
|
|
for col_num, value in enumerate(dfR.columns.values):
|
|
worksheet.write(5, col_num + 1, value, f_cabeceras )
|
|
|
|
worksheet.conditional_format('B7:N' + str(i),
|
|
{'type': 'cell',
|
|
'criteria': '<>',
|
|
'value': '""',
|
|
'format': f_bordes})
|
|
|
|
worksheet.set_portrait()
|
|
worksheet.set_paper(1)
|
|
worksheet.center_horizontally()
|
|
#worksheet.center_vertically()
|
|
worksheet.repeat_rows(0, 5)
|
|
worksheet.print_area('B7:I' + str(i))
|
|
worksheet.fit_to_pages(1, 0)
|
|
#worksheet.hide_gridlines(0)
|
|
worksheet.set_header('&LCIEC Cuotas, Ingresos y Egresos Condominales &Rciec.adanisoft.com')
|
|
worksheet.set_footer('&CPágina &P de &N &RImpreso el &D')
|
|
|
|
writer.close()
|
|
return filename.getvalue()
|
|
|
|
def run_acum_coyoacan(condominio, shortname):
|
|
print(" generando acumulados %s " % shortname)
|
|
with transaction.atomic():
|
|
borrado = ''
|
|
nq1 = ''
|
|
nq2 = ''
|
|
#
|
|
# Borra acumulados
|
|
if shortname == "COYOACAN":
|
|
borrado = 'delete from condominio_coyoacan_acumulado_mes'
|
|
nq1 = '''
|
|
select nombre, clabe as cuenta, cb.saldo_inicial, min(fecha_movimiento) as fecha
|
|
from condominio_coyoacan_movimiento m,
|
|
condominio_coyoacan_cuenta_banco cb,
|
|
periodo_corte p,
|
|
condominio c
|
|
where cb.id = m.cuenta_banco_id
|
|
and m.fecha_movimiento >= p.fecha_inicial
|
|
and m.fecha_movimiento <= p.fecha_final
|
|
and c.nombre = 'COYOACAN'
|
|
and p.id = 10
|
|
group by 1,2,3
|
|
order by 4,2
|
|
'''
|
|
nq2 = '''
|
|
select 'Condominio' as nombre, clabe as cuenta, to_char(fecha_movimiento,'MM-YYYY') as mes,
|
|
min(fecha_movimiento) as fec_ini,
|
|
max(fecha_movimiento) as fec_fin,
|
|
round(sum(deposito),2) as depositos,
|
|
round(sum(retiro),2) as retiros,
|
|
round(sum(deposito)-sum(retiro),2) as diferencia
|
|
from condominio_coyoacan_movimiento m,
|
|
condominio_coyoacan_cuenta_banco cb,
|
|
periodo_corte p
|
|
where cb.id = m.cuenta_banco_id
|
|
and fecha_movimiento >= p.fecha_inicial
|
|
and fecha_movimiento <= p.fecha_final
|
|
and p.id = 10
|
|
group by 1,2,3
|
|
order by 2,4,3
|
|
'''
|
|
#print(borrado,nq1,nq2)
|
|
n = execsql(borrado)
|
|
#
|
|
# Trae saldo inicial de cada cuenta
|
|
saldo_condominio = 0
|
|
rows = execsql(nq1)
|
|
print(rows)
|
|
#
|
|
# Por cada cuenta
|
|
for r in rows:
|
|
saldo = float(r['saldo_inicial'])
|
|
#
|
|
# Agrega depositos y retiros por cuenta y mes
|
|
rows2 = execsql(nq2)
|
|
for r2 in rows2:
|
|
if r2['cuenta'] == r['cuenta']:
|
|
saldo = round(saldo + float(r2['depositos']) - float(r2['retiros']), 2)
|
|
|
|
print(r2['cuenta'], r2['mes'], r2['depositos'], r2['retiros'], saldo)
|
|
|
|
reg = AcumuladoMes(cuenta_banco=r2['cuenta'], \
|
|
mes=r2['mes'], \
|
|
fecha_inicial=r2['fec_ini'], \
|
|
fecha_final=r2['fec_fin'], \
|
|
depositos=r2['depositos'], \
|
|
retiros=r2['retiros'], \
|
|
saldo=saldo)
|
|
reg.save()
|
|
if r['cuenta'] != '000000000000000000':
|
|
saldo_condominio = saldo_condominio + saldo
|
|
# print(saldo_condominio)
|
|
#
|
|
# Actualiza saldo en periodos
|
|
oPer = PeriodoCorte.objects.get(id=10)
|
|
oPer.saldo_final = saldo_condominio
|
|
oPer.save()
|
|
|
|
def run_determinacionSaldos_coyoacan(condomino):
|
|
print(" determinando saldos %s " % condomino.depto)
|
|
with transaction.atomic():
|
|
# tipo = TipoMovimiento.objects.get(id=21)
|
|
# prop = TipoMovimiento.objects.get(id=30)
|
|
cuenta = CuentaContable.objects.get(id=82)
|
|
# prove = Proveedore.objects.get(id=1)
|
|
#
|
|
# Borra asientos
|
|
n = execsql('delete from condominio_coyoacan_registro where condomino_id = %s' % condomino.id)
|
|
#
|
|
# Agrega adeudo inicial
|
|
if not condomino.depto == '0000':
|
|
ade = condomino.adeudo_inicial
|
|
sal = 0
|
|
deb = 0
|
|
sal = sal + deb - ade
|
|
# adeudo = condomino.adeudo_inicial
|
|
reg_i = Registro(fecha=condomino.fecha_adeudo_inicial, \
|
|
descripcion='SALDO INICIAL A LA FECHA', \
|
|
debe=deb, \
|
|
haber=ade, \
|
|
saldo=sal, \
|
|
cuenta_contable=cuenta, \
|
|
condomino=condomino)
|
|
reg_i.save()
|
|
#
|
|
# Agrega adeudos por cuotas
|
|
rows = CuotasCondominio.objects.all().order_by('mes_inicial')
|
|
for r in rows:
|
|
delta = (r.mes_final - r.mes_inicial)
|
|
# print(r.descripcion,r.mes_inicial,r.mes_final,r.monto,r.cuenta_contable,delta.days)
|
|
condom = r.condomino.filter(depto__contains=condomino.depto)
|
|
if condom:
|
|
base = r.mes_inicial
|
|
for x in range(0, delta.days + 1):
|
|
fecha = base + timedelta(days=x)
|
|
if fecha.day == 1:
|
|
reg_a = Registro(fecha=fecha, \
|
|
descripcion='CARGO {}'.format(r.descripcion), \
|
|
debe=0, \
|
|
haber=r.monto, \
|
|
saldo=0, \
|
|
cuenta_contable=r.cuenta_contable, \
|
|
condomino=condomino)
|
|
reg_a.save()
|
|
#
|
|
# Agrega depositos por movimiento de banco
|
|
if not condomino.depto == '0000':
|
|
movtos = Movimiento.objects.filter(condomino__id=condomino.id).order_by('fecha_movimiento', 'id')
|
|
for m in movtos:
|
|
reg_m = Registro(fecha=m.fecha_movimiento, \
|
|
descripcion=m.descripcion, \
|
|
debe=m.deposito, \
|
|
haber=0, \
|
|
saldo=0, \
|
|
cuenta_contable=r.cuenta_contable, \
|
|
condomino=condomino)
|
|
reg_m.save()
|
|
asientos = Asiento.objects.filter(condomino_id=condomino.id).order_by('fecha', 'id')
|
|
for a in asientos:
|
|
reg_a = Registro(fecha=a.fecha, \
|
|
descripcion=a.descripcion, \
|
|
debe=a.debe, \
|
|
haber=a.haber, \
|
|
saldo=a.saldo, \
|
|
cuenta_contable=a.cuenta_contable, \
|
|
condomino=condomino)
|
|
reg_a.save()
|
|
# Recalcula saldos
|
|
if not condomino.depto == '0000':
|
|
sal = 0
|
|
car = 0
|
|
dep = 0
|
|
rec = Registro.objects.filter(condomino__id=condomino.id).order_by('fecha', 'id')
|
|
for rr in rec:
|
|
car = car + rr.haber
|
|
dep = dep + rr.debe
|
|
sal = sal + rr.haber - rr.debe
|
|
rr.saldo = sal
|
|
rr.save()
|
|
|
|
reg_c = Condomino.objects.get(id=condomino.id)
|
|
reg_c.cargos = car
|
|
reg_c.abonos = dep
|
|
reg_c.saldo = sal
|
|
reg_c.save()
|
|
|
|
|