SELECT 
    `bl`.`id` AS `balance_libro_id`,
    `pe`.`id` AS `period_id`,
    `pe`.`code` AS `periodo`,
    `pe`.`date_from` AS `Desde`,
    `bl`.`receipt_id` AS `receipt_id`,
    `bl`.`period_liquidation_id` AS `period_liquidation_id`,
    `bl`.`fecha_registracion` AS `fecha`,
    `bl`.`conta_tipo_asiento_id` AS `conta_tipo_asiento_id`,
    `ta`.`nombre` AS `asiento_de`,
    (case 
        when (`pc3`.`tipo_cuenta_id` = 1) then `pc3`.`nombre`
            else `pc4`.`nombre`
    END) AS `Rubro`,
    `bld`.`plan_cuenta_id` AS `plan_cuenta_id`,
    (CASE
        WHEN (`bld`.`saldo_id` = 1) THEN 'DEUDOR'
        ELSE 'ACREEDOR'
    END) AS `SALDO`,
    `bld`.`saldo_id` AS `saldo_id`,
    `bld`.`comentario` AS `comentario`,
    `bld`.`person_id` AS `person_id`,
    `ta`.`nombre` AS `asiento`,
    `pc`.`nombre` AS `cuenta`,
    `p`.`field_name1` AS `empresa`,
    sum((CASE
        WHEN (`bld`.`saldo_id` = 1) THEN `bld`.`monto`
        ELSE 0
    END)) AS `DEBE`,
    sum((CASE
        WHEN (`bld`.`saldo_id` = 2) THEN `bld`.`monto`
        ELSE 0
    END)) AS `HABER`
FROM
    `balance_libro_details` `bld`
    JOIN `balance_libro` `bl` ON `bld`.`balance_libro_id` = `bl`.`id`
    JOIN `conta_tipo_saldos` `ts` ON `bld`.`saldo_id` = `ts`.`id`
    JOIN `persons` `p` ON `bld`.`person_id` = `p`.`id`
    JOIN `periods` `pe` ON `bld`.`period_id` = `pe`.`id`
    JOIN `conta_tipo_asientos` `ta` ON `bl`.`conta_tipo_asiento_id` = `ta`.`id`
    left JOIN `conta_plan_cuenta` `pc` ON `bld`.`plan_cuenta_id` = `pc`.`id`
    left JOIN `conta_plan_cuenta` `pc1` ON `pc`.`padre` = `pc1`.`codigo`
    left JOIN `conta_plan_cuenta` `pc2` ON `pc1`.`padre` = `pc2`.`codigo`
    left JOIN `conta_plan_cuenta` `pc3` ON `pc2`.`padre` = `pc3`.`codigo`
    left JOIN `conta_plan_cuenta` `pc4` ON `pc3`.`padre` = `pc4`.`codigo`
-- where bld.person_id= 1634
group by `bl`.`id`,bl.conta_tipo_asiento_id,bld.plan_cuenta_id
ORDER BY `bl`.`id` , `bld`.`id` , `bld`.`saldo_id`,`pc`.id
