<?php
include_once("../../../php/sessionStart.php"); 
include_once("../../../php/db/dbExec.php");
include_once("../../../php/fpdf/functions.php");
include_once('../../../php/functions.php');
include_once('../../../php/maxExecTime.php');

$empCod=utf8_decode($_SESSION["empCod"]);
$empNom=utf8_decode($_SESSION["empNom"]);
$empNomc=utf8_decode($_SESSION["empNomc"]);

$selMes=$_GET["selMes"];
$selMesNom=$_GET["selMesNom"];
$numAno=$_GET["numAno"];
$selBan=$_GET["selBan"];
$selBanNom=$_GET["selBanNom"];
$selBanCtab=$_GET["selBanCtab"];
$numAjus=$_GET["numAjus"];

if ($selMes == 12) {
  $selMesi = 1;
  $numAnoi = $numAno + 1;
} else {
  $selMesi = $selMes + 1;
  $numAnoi = $numAno;
}   
$numDiai = 1;
$Fecini = (strval($numAnoi)."-".strval($selMesi)."-".strval($numDiai));

$lineWidth=19.3;
$lineHeight=0.35;

# QUERYS
$sMova0cd = "SELECT ma_emp,ma_ban,
            SUM(ma_debe) AS ma_sdebe, 
            SUM(ma_haber) AS ma_shaber 
            FROM mov_acu WHERE ma_emp='$empCod' AND ma_ban='$selBan' AND ma_fec<'$Fecini' AND ma_stat='C' GROUP BY '$empCod','$selBan'";

$sMovAcu0 = "SELECT ma_emp,ma_ban,
            SUM(ma_debe) AS ma_sdebe, 
            SUM(ma_haber) AS ma_shaber 
            FROM mov_acu WHERE ma_emp='$empCod' AND ma_ban='$selBan' AND ma_fec>='$Fecini' GROUP BY '$empCod','$selBan'";

$sMovAcu = "SELECT *,
            TRIM(ma_bene) AS ma_bene,
            TRIM(ma_con) AS ma_con 
            FROM mov_acu WHERE ma_emp='$empCod' AND ma_ban='$selBan' AND ma_tipo='004' AND ma_fec<'$Fecini' AND ma_stat='C' AND ma_haber!=0.00 ORDER BY ma_fec,ma_tipo";
    
$sSaldob = "SELECT * FROM saldob WHERE salb_emp='$empCod' AND salb_num=$selBan";

$sBancos = "SELECT *,
            TRIM(ban_nom) AS ban_nom,
            TRIM(ban_ctab) AS ban_ctab 
            FROM bancos WHERE ban_emp='$empCod' AND ban_num=$selBan";

$sConcilia = "SELECT * FROM concilia WHERE con_emp='$empCod' AND con_ban='$selBan' AND con_ano=$numAno AND con_mes=$selMes";

$sConci_det = "SELECT * FROM conci_det WHERE cd_emp='$empCod' AND cd_ban='$selBan' AND cd_ano=$numAno AND cd_mes=$selMes";

# EJECUCION DE QUERYS
$db = new dbExec();

$select["mov_acu"] = $db->query($sMovAcu0);
$recma0 = $db->fetchAssoc($select['mov_acu']);
$sDebemov=$recma0["ma_sdebe"];
$sHabermov=$recma0["ma_shaber"];

$select["mov_acu"] = $db->query($sMova0cd);
$recma0 = $db->fetchAssoc($select['mov_acu']);
$sumCheques=$recma0["ma_shaber"];
$sumMovi=$recma0["ma_sdebe"];

$select["bancos"]=$db->query($sBancos);
$recban = $db->fetchAssoc($select['bancos']);
$banNom=$recban["ban_nom"];
$banCtab=$recban["ban_ctab"];

$select["saldob"]=$db->query($sSaldob);
$recsal = $db->fetchAssoc($select['saldob']);
$Saldo=$recsal["salb_saldo"];

$salConta=($Saldo-$sDebemov)+$sHabermov;
$salCuenta=($salConta+$sumCheques)-($sumMovi+$numAjus);
$Saldo=0;

# Para saber si esta cerrada la conciliacion
$select["concilia"] = $db->query($sConcilia);
$fetchAssoc["concilia"] = $db->fetchAssoc($select["concilia"]);
if ($db->numRows($select["concilia"]) == 0) {
   $statcon = false;
} else {
   $statcon = true;
}  


class generatePDF extends PDF_MC_Table {
    function Header() {
        global $empNom, $lineHeight, $lineWidth,$selMes, $selMesNom,$numAno,$selBan,$sumCheques,$sumMovi,$SalConta,$salCuenta,$banNom,$banCtab,$numAjus, $statcon;
        $func = new functions();
        $this->SetMargins(1.35, 1, 1.35);
        $this->Cell(0, 0, "", 0, 1);

        $this->SetFont("Arial","B",11);
        $this->MultiAlignCell($lineWidth, $lineHeight, $empNom,0,1,"L");
        $this->SetFont("","I",8);
        $this->SetY(.5);
        $this->Cell(0,1,utf8_decode("PÁGINA ").$this->PageNo(),0,1,"C");

        $this->SetFont("","B",10);
        $this->Cell($lineWidth, $lineHeight, utf8_decode("CONCILIACION BANCARIA CORRESPONDIENTE A ".trim($selMesNom)." del ".$numAno),0,1,"L");
        $this->Cell($lineWidth, $lineHeight, utf8_decode("BANCO: ".$banNom."  CUENTA: ".$banCtab),0,1,"L");
        IF (!$statcon) {
          $this->Cell($lineWidth, $lineHeight, "CONCILIACION ABIERTA",0,1,"L");
        } else {
          $this->Cell($lineWidth, $lineHeight, "CONCILIACION CERRADA",0,1,"L");
        }    
        $this->Ln();        
        $this->SetFont("","B",11);
        $this->Cell($lineWidth/1.2, $lineHeight, "Cheques en Circulacion: ",0,0,"L");
        $this->Ln();        
        $this->Ln();        
        $this->SetFont("","B",9);        
        $header = array("Cheque No.","Fecha","Beneficiario","Valor");
        $this->SetFont("","B");
        $this->SetWidths(array(2.1,2.2,12,3));
        $this->SetAligns(array("C","C","C","C"));
        $this->Row($header, $lineHeight);
        $this->SetFont("", "", 9);
        $this->SetAligns(array("L","L","L","R"));

    }
    
    function Footer() {
        // Posición: a 1,5 cm del final
        $func = new functions();

        $this->SetY(-1.5);
        $this->SetFont("Arial","I",6);
        $this->Cell(0,1,strftime("%d/%m/%Y %T"),0,0,"L");
    }
}

# GENERAR PDF
$pdf = new generatePDF("L","cm", array(22, 28));
$pdf->AddPage();

$debe = 0;
$haber = 0;


if (!$statcon) {
    $select["mov_acu"] = $db->query($sMovAcu);
    while ($rec = $db->fetchAssoc($select['mov_acu'])) {
        $body = array(
            $rec["ma_num"],
            strftime("%d/%m/%Y", strtotime($rec["ma_fec"])),
            $rec["ma_bene"],
            (round($rec["ma_haber"],2) != 0) ? number_format($rec["ma_haber"], 2, '.', ',') : "",
            );
         $pdf->RowBorderHidden($body, $lineHeight);
    }
} else {
    $select["conci_det"] = $db->query($sConci_det);
    while ($rec = $db->fetchAssoc($select['conci_det'])) {
        $body = array(
            $rec["cd_num"],
            strftime("%d/%m/%Y", strtotime($rec["cd_fec"])),
            $rec["cd_bene"],
            (round($rec["cd_haber"],2) != 0) ? number_format($rec["cd_haber"], 2, '.', ',') : "",
            );
         $pdf->RowBorderHidden($body, $lineHeight);
    }
}

$pdf->Ln($lineHeight*3);
$pdf->SetFont("","B",11);

$pdf->line($pdf->GetX()+7.5, $pdf->GetY(), 21.3, $pdf->GetY());
$pdf->SetAligns(array("","","R","R"));
$lineHeight=.50;
IF (!$statcon) {
    $body = array("","",utf8_decode("Saldo Según Estado de Cuenta Banco:          "),
        number_format($salCuenta,2, '.', ','),
    ); 
    $pdf->RowBorderHidden($body, $lineHeight);
    $body = array("","",utf8_decode("(-)     Cheques en Circulación          "),
        number_format($sumCheques,2, '.', ','),
    ); 
    $pdf->RowBorderHidden($body, $lineHeight);
    $body = array("","",utf8_decode("(+)   Depositos en Circulacion          "),
        number_format($sumMovi,2, '.', ','),
    ); 
    $pdf->RowBorderHidden($body, $lineHeight);
    $body = array("","","(+) Ajustes Varios          ",
        number_format($numAjus,2, '.', ','),
    );  
    $pdf->RowBorderHidden($body, $lineHeight);
    $body = array("","",utf8_decode("Saldo según Contabilidad:          "),
        number_format($salConta,2, '.', ','),
    ); 
    $pdf->RowBorderHidden($body, $lineHeight);
} else {
    $select["concilia"] = $db->query($sConcilia);
    $fetchAssoc["concilia"] = $db->fetchAssoc($select["concilia"]);
    $body = array("","",utf8_decode("Saldo Según Estado de Cuenta Banco:          "),
        number_format($fetchAssoc["concilia"]["con_salban"],2, '.', ','),
    ); 
    $pdf->RowBorderHidden($body, $lineHeight);
    $body = array("","",utf8_decode("(-)     Cheques en Circulación          "),
        number_format($fetchAssoc["concilia"]["con_cir"],2, '.', ','),
    ); 
    $pdf->RowBorderHidden($body, $lineHeight);
    $body = array("","",utf8_decode("(+)   Depositos en Circulacion          "),
        number_format($fetchAssoc["concilia"]["con_depcir"],2, '.', ','),
    ); 
    $pdf->RowBorderHidden($body, $lineHeight);
    $body = array("","","(+) Ajustes Varios          ",
        number_format($fetchAssoc["concilia"]["con_ajus"],2, '.', ','),
    );  
    $pdf->RowBorderHidden($body, $lineHeight);
    $body = array("","",utf8_decode("Saldo según Contabilidad:          "),
        number_format($fetchAssoc["concilia"]["con_salem"],2, '.', ','),
    ); 
    $pdf->RowBorderHidden($body, $lineHeight);
}    
$pdf->line($pdf->GetX()+7.5, $pdf->GetY(), 21.3, $pdf->GetY());
$pdf->line($pdf->GetX()+7.5, $pdf->GetY()+.075, 21.3, $pdf->GetY()+.075);

$db->close();

$pdf->Output(utf8_decode("Conciliacion").".pdf","I");
?>