search

henrybravos / micro_report_ecocont

microservice report for ecocont
1 stars 1 forks source link

Libro diario #11

Closed henrybravos closed 2 months ago

henrybravos commented 2 months ago

SELECT o.id AS id, concat(o.cuo, '-', d.tipo) AS i2, d.identificador AS i3, p2.codigo AS i4, p2.denominacion AS denominacion, d.unidad_operacion AS i5, d.centro_costo AS i6, tm.codigo AS i7, td.codigo AS i8, p.numero AS i9, tc.codigo AS i10, o.serie AS i11, o.correlativo AS i12, o.fecha_contable AS i13, o.fecha_vencimiento AS i14, o.fecha_emision AS i15, pg.fecha AS i15pg, o.glosa AS i16o, pg.glosa AS i16, o.glosa_referencia AS i17, d.debe AS i18, d.haber AS i19, o.dato_estructurado AS i20, d.estado AS i21, o.estado_le AS estado_le, o.tipo_cambio AS tipo_cambio, o.codigo_libro AS codigo_libro, o.periodo AS periodo, o.cuo AS cuo, o.observaciones AS observaciones, d.pcge_id AS pcge_id, pg.tipo_cambio AS p_tipo_cambio FROM operaciones o INNER JOIN diarios d ON o.id = d.operacion_id LEFT JOIN pagos pg ON d.referencia_id = pg.id LEFT JOIN personas p ON o.persona_asociado_id = p.id LEFT JOIN t_documentos td ON p.documento_id = td.id LEFT JOIN t_comprobantes tc ON o.comprobante_id = tc.id INNER JOIN t_monedas tm ON o.moneda_id = tm.id INNER JOIN pcge p2 ON d.pcge_id = p2.id INNER JOIN locales l ON o.local_id = l.id WHERE l.empresa_id = $1 AND SUBSTRING(d.periodo, 1, 4) = $2 AND SUBSTRING(d.periodo, 6, 8) = $3 AND (d.identificador not like 'C%' AND o.glosa <> 'Cierre de Cuentas de Balance') AND o.comprobante_id::text <> ALL(ARRAY['a6062ae0-15a4-11ec-8fec-77a5f80a0a28', '1daedb70-a779-11eb-84c1-40b0344a6892']) AND o.tipo_operacion::text <> ALL(ARRAY['5913663b-a77a-11eb-8923-40b0344a6892', '59133fcc-a77a-11eb-8919-40b0344a6892']) AND d.deleted_at IS NULL AND o.deleted_at IS NULL ORDER BY i4, o.fecha_emision, i2, d.identificador

henrybravos commented 2 months ago

CREATE INDEX idx_diarios_periodo_identificador_operacion_id ON diarios(SUBSTRING(periodo, 1, 4), SUBSTRING(periodo, 6, 8), identificador, operacion_id) WHERE deleted_at IS NULL;

CREATE INDEX idx_operaciones_local_moneda_comprobante_tipo_glosa ON operaciones(local_id, moneda_id, comprobante_id, tipo_operacion, glosa) WHERE deleted_at IS NULL;

CREATE INDEX idx_locales_empresa_id ON locales(empresa_id, id);

CREATE INDEX idx_pagos_id ON pagos(id); CREATE INDEX idx_personas_id ON personas(id);

CREATE INDEX idx_diarios_periodo_substring ON diarios((SUBSTRING(periodo, 1, 4)), (SUBSTRING(periodo, 6, 8))) WHERE deleted_at IS NULL;

VACUUM ANALYZE;