<?php

//cabecera cuentas
$sql = 'select * from cn_balance_det_cue where id_periodo_contable=1 and cuenta=' . $cuenta . ' ';

$records = CN_TABLE::executeSQL($sql);

//tabla principal
echo "<table border='1' width='750' >";

echo "<tr>
<th width='10%'>Fecha</th>
<th width='5%'>Tip. Com.</th>
<th width='5%'>CAD</th>
<th width='30%'>Descripcion</th>
<th width='5%'>Soporte</th>
<th width='15%'>Debito</th>
<th width='15%'>Credito</th>
<th width='15%'>Saldo</th>
</tr>";

foreach ($records as $valueCuentas) {
    if ($valueCuentas['id_cia'] == 1 && $valueCuentas['id_cen_op'] == 16 && $valueCuentas['id_vigencia'] == 2011) {
        echo "<tr>";
        echo "<td>" . $valueCuentas['cuenta'] . '</td>
            <td colspan=\'4\' >' . $valueCuentas['nombre_cuenta'] . "</td>";
        if (strlen($valueCuentas['cuenta']) == 6) {
            //saldo de la subcuenta
            $sql2 = "SELECT SUM(SALDO_ANTERIOR) as saldo_anterior, SUM(SALDO_ACTUAL) as saldo_actual
                    FROM CN_SALDO
                    WHERE SUBCUENTA='" . $valueCuentas["cuenta"] . "'
                    AND ID_VIGENCIA = " . $valueCuentas["id_vigencia"] . "
                    AND ID_PERIODO_CONTABLE = " . $valueCuentas["id_periodo_contable"] . "
                    AND ID_CIA = " . $valueCuentas["id_cia"] . "
                    AND ID_CEN_OP =" . $valueCuentas["id_cen_op"];
            $records2 = CN_TABLE::executeSQL($sql2);
            echo "<td colspan='3' align='right'>" . $records2[0]["saldo_actual"] . "</td>";

            //auxiliares y terceros de la subcuenta
            $sql_aux_ter = "SELECT
                        ID_AUXILIAR,
                        COD_AUX,
                        NOMBRE AS NOMBRE_AUX,
                        null as ID_MAE_TER,
                        null as NITOCC,
                        null as NOMBRE_TER
                        FROM CN_AUXILIAR
                        WHERE ID_AUXILIAR IN (
                            SELECT ID_AUXILIAR
                            FROM CN_SALDO
                            WHERE SUBCUENTA='" . $valueCuentas["cuenta"] . "'
                            AND ID_VIGENCIA = 2011
                            AND ID_PERIODO_CONTABLE = 1
                            AND ID_MAE_TER IS NULL
                            AND ID_CIA = " . $valueCuentas["id_cia"] . "
                            AND ID_CEN_OP =" . $valueCuentas["id_cen_op"] . "
                        )
                        union
                        SELECT
                        null as ID_AUXILIAR,
                        null as COD_AUX,
                        null as NOMBRE_AUX,
                        ID_MAE_TER,
                        NITOCC,
                        NOMBRE AS NOMBRE_TER
                        FROM CN_MAE_TER
                        WHERE ID_MAE_TER IN (
                            SELECT ID_MAE_TER
                            FROM CN_SALDO
                            WHERE SUBCUENTA='" . $valueCuentas["cuenta"] . "'
                            AND ID_VIGENCIA = 2011
                            AND ID_PERIODO_CONTABLE = 1
                            AND ID_AUXILIAR IS NULL
                            AND ID_CIA = " . $valueCuentas["id_cia"] . "
                            AND ID_CEN_OP =" . $valueCuentas["id_cen_op"] . "
                        )
                        UNION
                        SELECT
                         aux.ID_AUXILIAR,
                         aux.COD_AUX,
                         aux.NOMBRE AS NOMBRE_AUX,
                         ter.ID_MAE_TER,
                         ter.NITOCC,
                         ter.NOMBRE AS NOMBRE_TER
                        FROM CN_SALDO sal

                        JOIN CN_MAE_TER ter ON
                        sal.ID_MAE_TER = ter.ID_MAE_TER AND
                        sal.ID_CIA = ter.ID_CIA

                        JOIN CN_AUXILIAR aux ON
                        sal.ID_AUXILIAR = aux.ID_AUXILIAR AND
                        sal.ID_CIA = aux.ID_CIA

                        WHERE sal.SUBCUENTA='" . $valueCuentas["cuenta"] . "'
                        AND sal.ID_VIGENCIA = 2011
                        AND sal.ID_PERIODO_CONTABLE = 1
                        AND ID_CIA = " . $valueCuentas["id_cia"] . "
                        AND ID_CEN_OP =" . $valueCuentas["id_cen_op"] . "
                        AND sal.ID_AUXILIAR = 957";


            $records_aux_ter = CN_TABLE::executeSQL($sql_aux_ter);

            echo "</tr>";
            //tabla para los auxiliares y terceros
            echo "<tr>";
            echo "<td colspan='8' width='100%' >";
            echo "<table border='1' width='100%' >";
            //recorrer cada auxiliar y tercero
            foreach ($records_aux_ter as $valueAuxTer) {
                echo "<tr>";

                echo "<td>" . $valueAuxTer['cod_aux'] . '</td>';
                echo "<td>" . $valueAuxTer['nombre_aux'] . '</td>';
                echo "<td>" . $valueAuxTer['nitocc'] . '</td>';
                echo "<td>" . $valueAuxTer['nombre_ter'] . '</td>';

                echo "</tr>";


                if (isset($valueAuxTer["id_mae_ter"], $valueAuxTer)) {
                    $whereTercero = " = " . $valueAuxTer["id_mae_ter"];
                } else {
                    $whereTercero = " is null ";
                }

                /*
                 * Consulta para los movimientos del auxiliar y tercero
                 */
                $sql_movimientos = "SELECT
                                    cad.FECHA_CAD, tc.NOM_CORTO,
                                    mov.ID_CAD, 
                                    doc.DESCR_MTO,
                                    doc.DOC_SOPORTE,
                                    mov.DEBITO,
                                    mov.CREDITO
                                    FROM CN_MOVIMIENTO_CONTABLE mov

                                    JOIN CN_CONTROL_CAD cad
                                     ON cad.ID_CIA = mov.ID_CIA
                                    AND cad.ID_CAD = mov.ID_CAD
                                    AND cad.ID_CEN_OP = mov.ID_CEN_OP
                                    AND cad.ID_VIGENCIA = mov.ID_VIGENCIA

                                    JOIN CN_TIPO_COMPROBANTE tc
                                     ON cad.ID_CIA = tc.ID_CIA
                                    AND cad.ID_TIPO_COMPROBANTE = tc.ID_TIPO_COMPROBANTE

                                    JOIN CN_DOCUMENTO doc
                                     ON doc.ID_CIA = mov.ID_CIA
                                    AND doc.ID_CAD = mov.ID_CAD
                                    AND doc.ID_DOCUMENTO = mov.ID_DOCUMENTO
                                    AND doc.ID_CEN_OP = mov.ID_CEN_OP
                                    AND doc.ID_VIGENCIA = mov.ID_VIGENCIA

                                    JOIN CN_MAE_TER terdoc
                                     ON terdoc.ID_CIA = doc.ID_CIA
                                    AND terdoc.ID_MAE_TER = doc.ID_MAE_TER

                                    WHERE mov.ID_CIA = " . $valueCuentas["id_cia"] . "
                                    AND mov.ID_CEN_OP = " . $valueCuentas["id_cen_op"] . "
                                    AND mov.ID_VIGENCIA = 2011
                                    AND cad.ID_PERIODO_CONTABLE = 1
                                    AND mov.ID_AUXILIAR = " . $valueAuxTer["id_auxiliar"] . "
                                    AND mov.ID_MAE_TER " . $whereTercero . "

                                    ORDER BY
                                    mov.ID_MOV_CONT";

                $records_movimientos = CN_TABLE::executeSQL($sql_movimientos);

                //tabla para los movimientos
                echo "<tr>";
                echo "<td colspan='4' >";
                echo "<table border='1' width='100%' >";

                //recorrer los movimientos
                foreach ($records_movimientos as $valueMov) {

                    echo "<tr>";
                    $fecha = explode(" ", $valueMov['fecha_cad']);
                    echo "<td width='10%'>" . $fecha[0] . '</td>';
                    echo "<td width='5%'>" . $valueMov['nom_corto'] . '</td>';
                    echo "<td width='5%'>" . $valueMov['id_cad'] . '</td>';
                    echo "<td width='30%'>" . $valueMov['descr_mto'] . '</td>';
                    echo "<td width='5%'>" . $valueMov['doc_soporte'] . '</td>';

                    $debito = "$" . number_format($valueMov['debito'], 2, ',', '.');
                    echo "<td width='15%' align='right'>" . $debito . '</td>';
                    $credito = "$" . number_format($valueMov['credito'], 2, ',', '.');
                    echo "<td width='15%' align='right'>" . $credito . '</td>';
                    echo "<td width='15%'> </td>";

                    echo "</tr>";
                }

                $sql_mov_tot = "SELECT
                                     SUM(mov.DEBITO) as DEBITO,
                                     SUM(mov.CREDITO) as CREDITO,
                                     CASE cmay.NATURALEZA
                                      WHEN 'D' THEN SUM(mov.DEBITO - mov.CREDITO)
                                      ELSE SUM(mov.CREDITO - mov.DEBITO)
                                      END AS SALDO
                                    FROM CN_MOVIMIENTO_CONTABLE mov

                                    JOIN CN_CONTROL_CAD cad
                                     ON cad.ID_CIA = mov.ID_CIA
                                    AND cad.ID_CAD = mov.ID_CAD
                                    AND cad.ID_CEN_OP = mov.ID_CEN_OP
                                    AND cad.ID_VIGENCIA = mov.ID_VIGENCIA

                                    JOIN CN_TIPO_COMPROBANTE tc
                                     ON cad.ID_CIA = tc.ID_CIA
                                    AND cad.ID_TIPO_COMPROBANTE = tc.ID_TIPO_COMPROBANTE

                                    JOIN CN_DOCUMENTO doc
                                     ON doc.ID_CIA = mov.ID_CIA
                                    AND doc.ID_CAD = mov.ID_CAD
                                    AND doc.ID_DOCUMENTO = mov.ID_DOCUMENTO
                                    AND doc.ID_CEN_OP = mov.ID_CEN_OP
                                    AND doc.ID_VIGENCIA = mov.ID_VIGENCIA

                                    JOIN CN_MAE_TER terdoc
                                     ON terdoc.ID_CIA = doc.ID_CIA
                                    AND terdoc.ID_MAE_TER = doc.ID_MAE_TER

                                    JOIN CN_CUENTA_MAYOR cmay
                                     ON cmay.ID_CIA = mov.ID_CIA
                                    AND cmay.ID_CUENTA_MAYOR = mov.ID_CUENTA_MAYOR

                                    WHERE mov.ID_CIA = " . $valueCuentas["id_cia"] . "
                                    AND mov.ID_CEN_OP = " . $valueCuentas["id_cen_op"] . "
                                    AND mov.ID_VIGENCIA = 2011
                                    AND cad.ID_PERIODO_CONTABLE = 1
                                    AND mov.ID_AUXILIAR = 957
                                    AND mov.ID_MAE_TER = 3015

                                    GROUP BY
                                    cmay.NATURALEZA";

                $records_mov_tot = CN_TABLE::executeSQL($sql_mov_tot);

                echo "<tr>";
                echo "<td> Subtotal Mes: </td>";
                echo "<td>" . $valueCuentas['id_vigencia'] . "</td>";
                echo "<td>" . $records_mov_tot[0]['debito'] . "</td>";
                echo "<td>" . $records_mov_tot[0]['credito'] . "</td>";
                echo "<td>" . $records_mov_tot[0]['saldo'] . "</td>";
                echo "</tr>";

                echo "</table>";
                echo "</td>";
                echo "</tr>";
            }

            echo "</table>";
            echo "</td>";
        }
        echo "</tr>";
    }
}
echo "</table>";