webadmin.php

/var/www/enzatesting.onesolution.hk/xls_bal_cus_template.php

<?php
$formid = "Order";
require_once "inc/configure.php";
require_once "inc/phpexcel_1.7.7/PHPExcel.php";

if (
    havePermission("GNr") == false
    || havePermission("DPr") == false
    //|| havePermission("MDr")==false
    //|| havePermission("TDr")==false
) {

    myerror("Invalid Permission");
}

//--------temp function-------------------

function setCellValueByVariableName($worksheet, $variableName, $value)
{
    $cellCoordinate = findCellCoordinateByVariableName($worksheet, $variableName);

    if ($cellCoordinate !== null) {
        if (
            $variableName === 'ttl_ivc_amount' ||
            $variableName === 'ttl_payment_amount' ||
            $variableName === 'ttl_outstanding_bal_amount' ||
            $variableName === 'ttl_overdue_bal_amount' ||
            $variableName === 'ttl_deposit_used_amount' ||
            $variableName === 'ttl_cn_used_amount'
        ) {
            $worksheet->getStyle($cellCoordinate)->getNumberFormat()->setFormatCode('#,##0.00');
        }
        $worksheet->getCell($cellCoordinate)->setValue($value);
    } else {
        // Gérer le cas où la variable n'est pas trouvée
        echo "Variable not found: " . $variableName;
    }
}

function findCellCoordinateByVariableName($worksheet, $variableName)
{
    $highestRow = $worksheet->getHighestRow();
    $highestColumn = $worksheet->getHighestColumn();

    for ($row = 1; $row <= $highestRow; $row++) {
        for ($column = 'A'; $column <= $highestColumn; $column++) {
            $cellValue = $worksheet->getCell($column . $row)->getValue();
            if ($cellValue == '#' . $variableName) {
                return $column . $row;
            }
        }
    }

    return null; // La variable n'a pas été trouvée
}


/*--------------------------------END OF FUNCTIONS--------------------------------------*/

$refid = setDefaultReqVar("refid", '');

$refid = (int)setDefaultReqVar("refid", '');
//$editable = setDefaultReqVar("edit", 0);

if ($refid) {

    $sql = "SELECT * FROM master_customer WHERE refid = :refid";
    $sth = $dbh->prepare($sql);
    $sth->execute(array(':refid' => $refid));

    //    $sql = "SELECT * from ivc_cus WHERE refid = :refid";
    //    $sth = $dbh->prepare($sql);
    //    $sth->execute(  array(':refid' => $refid) );





} else {
    exit;
}
$row = $sth->fetch();
$header = $row;

$customer = $row;

//$customer = getDB_master_customer($row['ivc_customer']);
unset($sth);

@set_time_limit(0);
//ini_set('max_execution_time', '999');
ini_set('post_max_size', '10M');
ini_set('memory_limit', '1024M');

$objPHPexcel = PHPExcel_IOFactory::load('templates/bal_cus_template.xls');


// ------------------ Sheet Invoice ---------------------------------------------------------------------------------------------------------------------------------

$objWorksheet = $objPHPexcel->getSheetByName('Invoice');

$default_rowh = 16; //16pt~21pixel

/*
#cust_code                    A3
#company_name               A4
#launch_date                F2
#payment_terms              F3
#total_ivc_amount           A9
#total_payment_amount       B9
#ttl_outstanding_bal_amount C9
#ttl_overdue_bal_amount     D9
#ttl_deposit_used_amount    E9
#ttl_cn_used_amount         F9

*/

// ajout des informations du client sur l'excel

setCellValueByVariableName($objWorksheet, 'cust_code', html_entity_decode($customer['custcode'], ENT_QUOTES));
setCellValueByVariableName($objWorksheet, 'company_name', html_entity_decode($customer['companyname_en'], ENT_QUOTES));
setCellValueByVariableName($objWorksheet, 'launch_date', date_format(date_create($row['due_date']), 'l, F d, Y'));
setCellValueByVariableName($objWorksheet, 'payment_terms', html_entity_decode($customer['paymethod'] . " " . resolveCode_master_type_code('PAYMETHOD', $customer['paymethod']), ENT_QUOTES));



// récupération des données du client tableau récap

// Total Invoice Amount
$sql_total_invoices_amount = "SELECT SUM(ttl_price) AS total_amount 
                                                    FROM ivc_cus 
                                                    WHERE ivc_customer = :customer_code 
                                                      AND status <> 'FINISHED' 
                                                      AND ivc_nbr IS NOT NULL";
$sth_total_invoices_amount = $dbh->prepare($sql_total_invoices_amount);

$params[':customer_code'] = $customer['custcode'];
$sth_total_invoices_amount->execute($params);


$total_invoices_amount_result = $sth_total_invoices_amount->fetch();
// Assurez-vous que le résultat est non vide avant de l'afficher
$total_invoices_amount = !empty($total_invoices_amount_result) ? $total_invoices_amount_result['total_amount'] : 0;


// Total Payments Amount
$sql_total_payment_amount = "SELECT SUM(icp.payment_amount) AS total_amount
                                                        FROM ivc_cus_payment icp
                                                        JOIN ivc_cus ic ON icp.ivc_nbr = ic.ivc_nbr
                                                        WHERE ic.ivc_customer = :customer_code
                                                          AND icp.payment_type = 'Payment'
                                                        ";
$sth_total_payment_amount = $dbh->prepare($sql_total_payment_amount);

$params[':customer_code'] = $customer['custcode'];
$sth_total_payment_amount->execute($params);

$total_payment_amount_result = $sth_total_payment_amount->fetch();

// Assurez-vous que le résultat est non vide avant de l'afficher
$total_payment_amount = !empty($total_payment_amount_result) ? $total_payment_amount_result['total_amount'] : 0;



// Total Deposit Used Amount
$sql_total_deposit_amount = "SELECT SUM(icp.payment_amount) AS total_amount
                                                        FROM ivc_cus_payment icp
                                                        JOIN ivc_cus ic ON icp.ivc_nbr = ic.ivc_nbr
                                                        WHERE ic.ivc_customer = :customer_code
                                                          AND icp.payment_type = 'Deposit'
                                                        ";
$sth_total_deposit_amount = $dbh->prepare($sql_total_deposit_amount);

$params[':customer_code'] = $customer['custcode'];

$sth_total_deposit_amount->execute($params);
$total_deposit_amount_result = $sth_total_deposit_amount->fetch();

// Assurez-vous que le résultat est non vide avant de l'afficher
$total_deposit_amount = !empty($total_deposit_amount_result) ? $total_deposit_amount_result['total_amount'] : 0;


// Total Credit Note Used
$sql_total_creditNote_amount = "SELECT SUM(icp.payment_amount) AS total_amount
                                                        FROM ivc_cus_payment icp
                                                        JOIN ivc_cus ic ON icp.ivc_nbr = ic.ivc_nbr
                                                        WHERE ic.ivc_customer = :customer_code
                                                          AND icp.payment_type = 'CreditNote'
                                                        ";
$sth_total_creditNote_amount = $dbh->prepare($sql_total_creditNote_amount);

$params[':customer_code'] = $customer['custcode'];

$sth_total_creditNote_amount->execute($params);
$total_creditNote_amount_result = $sth_total_creditNote_amount->fetch();

// Assurez-vous que le résultat est non vide avant de l'afficher
$total_creditNote_amount = !empty($total_creditNote_amount_result) ? $total_creditNote_amount_result['total_amount'] : 0;




// Total Overdue Balance Amount
// Récupérez le total des montants des factures liées à ce client OVERDUE AMOUNT --------------------------------------------------------------------
$sql_total_remainingBalance_amount = "SELECT SUM(COALESCE(remaining_balance, ttl_price)) AS total_amount
                                                                                            FROM ivc_cus
                                                                                            WHERE ivc_customer = :customer_code
                                                                                              AND due_date < GETDATE()
                                                                                              AND status <> 'FINISHED' 
                                                                                              AND ivc_nbr IS NOT NULL";
$sth_total_remainingBalance_amount = $dbh->prepare($sql_total_remainingBalance_amount);

$params[':customer_code'] = $customer['custcode'];

$sth_total_remainingBalance_amount->execute($params);
$total_remainingBalance_amount_result = $sth_total_remainingBalance_amount->fetch();

// Assurez-vous que le résultat est non vide avant de l'afficher
$total_OverdueBalance_amount = !empty($total_remainingBalance_amount_result) ? $total_remainingBalance_amount_result['total_amount'] : 0;


// Total Outstanding Balance Amount
// Récupérez le total des montants des factures liées à ce client OUTSTANDING AMOUNT ----------------------------------------------------------------------
$sql_total_outstandingRemainingBalance_amount = "SELECT SUM(COALESCE(remaining_balance, ttl_price)) AS total_amount 
                                                                                                        FROM ivc_cus 
                                                                                                        WHERE ivc_customer = :customer_code
                                                                                                        AND status <> 'FINISHED' 
                                                                                                          AND ivc_nbr IS NOT NULL";

$sth_total_outstandingRemainingBalance_amount = $dbh->prepare($sql_total_outstandingRemainingBalance_amount);

$params[':customer_code'] = $customer['custcode'];

$sth_total_outstandingRemainingBalance_amount->execute($params);
$total_outstandingRemainingBalance_amount_result = $sth_total_outstandingRemainingBalance_amount->fetch();

$total_outstandingRemainingBalance_amount = !empty($total_outstandingRemainingBalance_amount_result) ? $total_outstandingRemainingBalance_amount_result['total_amount'] : 0;


// --------------------------------------------------------------


// Centrer les valeurs des six derniers appels à setCellValueByVariableName
$lastVariableNames = array('ttl_ivc_amount', 'ttl_payment_amount', 'ttl_outstanding_bal_amount', 'ttl_overdue_bal_amount', 'ttl_deposit_used_amount', 'ttl_cn_used_amount');

foreach ($lastVariableNames as $variableName) {
    $cellCoordinate = findCellCoordinateByVariableName($objWorksheet, $variableName);

    if ($cellCoordinate !== null) {
        // Appliquer le style pour centrer le contenu
        $objWorksheet->getStyle($cellCoordinate)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
        $objWorksheet->getStyle($cellCoordinate)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);


        // Appliquer le style pour écrire en rouge si c'est "ttl_overdue_bal_amount"
        if ($variableName === "ttl_overdue_bal_amount") {
            $redFill = array(
                'font' => array(
                    'color' => array('rgb' => 'FF0000'), // Rouge
                ),
            );
            $objWorksheet->getStyle($cellCoordinate)->applyFromArray($redFill);
        }



        // Appliquer le style pour écrire en vert foncé si c'est "ttl_deposit_used_amount"
        if ($variableName === "ttl_deposit_used_amount") {
            $greenFill = array(
                'font' => array(
                    'color' => array('rgb' => '76933c'), // vert
                ),
            );
            $objWorksheet->getStyle($cellCoordinate)->applyFromArray($greenFill);
        }
        // Appliquer le style pour écrire en bleu si c'est "ttl_cn_used_amount"
        if ($variableName === "ttl_cn_used_amount") {
            $blueFill = array(
                'font' => array(
                    'color' => array('rgb' => '00b0f0'), // bleu
                ),
            );
            $objWorksheet->getStyle($cellCoordinate)->applyFromArray($blueFill);
        }
    } else {
        // Gérer le cas où la variable n'est pas trouvée
        echo "Variable non trouvée : " . $variableName;
    }
}



// si ttl_payment_amount est vide, mettre 0
if ($total_payment_amount == NULL) {
    $total_payment_amount = 0;
}
if ($total_deposit_amount == NULL) {
    $total_deposit_amount = 0;
}
if ($total_creditNote_amount == NULL) {
    $total_creditNote_amount = 0;
}

// recap tableau set
setCellValueByVariableName($objWorksheet, 'ttl_ivc_amount', $total_invoices_amount);
setCellValueByVariableName($objWorksheet, 'ttl_payment_amount', $total_payment_amount);
setCellValueByVariableName($objWorksheet, 'ttl_outstanding_bal_amount', $total_outstandingRemainingBalance_amount);
setCellValueByVariableName($objWorksheet, 'ttl_overdue_bal_amount', $total_OverdueBalance_amount);
setCellValueByVariableName($objWorksheet, 'ttl_deposit_used_amount', $total_deposit_amount);
setCellValueByVariableName($objWorksheet, 'ttl_cn_used_amount', $total_creditNote_amount);





// list invoice

//$excel_row=14;
$invoice_row = 14; // Commencez à la ligne 14 pour les factures
$payment_row_offset = 1; // Offset d'une ligne pour commencer à la ligne suivante (colonne B)


$sql = "SELECT * FROM ivc_cus WHERE ivc_customer = :cust_code AND (finished <> :finished OR finished IS NULL) ORDER BY launch_date;";
$sth = $dbh->prepare($sql);

$sql_param[':cust_code'] = $customer['custcode'];
$sql_param[':finished'] = '1';


$q = $sth->execute($sql_param);
//echo $sth->getSQL( array(':ivccus_refid'=>$refid) ) . HTML_EOL;
pdo_showerror($sth, $q);

$ttl_var = array();
while ($customer_invoices = $sth->fetch()) {

    $rowheight = $default_rowh;
    $mergecell = false;

    $objWorksheet->insertNewRowBefore($invoice_row + 1, 1);
    // Appliquer le style pour les lignes d'invoice
    $styleArrayInvoice = array(
        'font' => array(
            'name' => 'Arial',
            'size' => 10,
        ),
        'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            'color' => array('rgb' => 'FFFFFF'),
        ),
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        ),
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array('argb' => '000000'), // Couleur des bordures, ici noir (000000)
            ),
        ),
    );

    // Appliquer le style pour les lignes Remittance Date, Remittance Type, Reference, Amount
    $styleArrayHeaders = array(
        'font' => array(
            'bold' => true,
            'name' => 'Arial',
            'size' => 10,
        ),
        'fill' => array(
            'type' => PHPExcel_Style_Fill::FILL_SOLID,
            'color' => array('rgb' => 'C0C0C0'),
        ),
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        ),
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array('argb' => '000000'), // Couleur des bordures, ici noir (000000)
            ),
        ),
    );




    $objWorksheet->getStyle("A$invoice_row:F$invoice_row")->applyFromArray($styleArrayInvoice);

    $objWorksheet->setCellValueByColumnAndRow(0, $invoice_row, $customer_invoices['launch_date']);
    $objWorksheet->setCellValueByColumnAndRow(1, $invoice_row, $customer_invoices['due_date']);
    $objWorksheet->setCellValueByColumnAndRow(2, $invoice_row, $customer_invoices['ivc_nbr']);
    $objWorksheet->getStyleByColumnAndRow(3, $invoice_row)->getNumberFormat()->setFormatCode('#,##0.00');
    $objWorksheet->setCellValueByColumnAndRow(3, $invoice_row, $customer_invoices['ttl_price']);

    if ($customer_invoices['remaining_balance'] == NULL) {
        $ivc_remainingBalance = $customer_invoices['ttl_price'];
    } else {
        $ivc_remainingBalance = $customer_invoices['remaining_balance'];
    }

    $objWorksheet->getStyleByColumnAndRow(4, $invoice_row)->getNumberFormat()->setFormatCode('#,##0.00');
    $objWorksheet->setCellValueByColumnAndRow(4, $invoice_row, $ivc_remainingBalance);




    if ($customer_invoices['status'] == 'INPROGRESS') {
        // Vérifier si la date d'échéance est dépassée
        if (strtotime($customer_invoices['due_date']) <= strtotime('now')) {
            $ivc_status = 'Remains Unpaid';
        } else {
            $ivc_status = "In Progress";
        }
    } elseif ($customer_invoices['status'] == 'PAID') {
        $ivc_status = "Paid";
    }




    $objWorksheet->setCellValueByColumnAndRow(5, $invoice_row, $ivc_status);

    // Augmenter la hauteur de la cellule pour mieux différencier
    $objWorksheet->getRowDimension($invoice_row)->setRowHeight(30); // Ajustez la hauteur selon vos préférences

    if (strtotime($customer_invoices['due_date']) <= strtotime('now')) {
        $lastColumn = 'F';  // Modifier cela en fonction de la dernière colonne que vous avez
        $range = "E$invoice_row:$lastColumn$invoice_row";
        $objWorksheet->getStyle($range)->applyFromArray(
            array(
                'fill' => array(
                    'type' => PHPExcel_Style_Fill::FILL_SOLID,
                    'color' => array('rgb' => 'ff0000'),
                ),
            )
        );
    }

    if ($ivc_status == 'In Progress') {
        $lastColumn = 'F';  // Modifier cela en fonction de la dernière colonne que vous avez
        $range = "E$invoice_row:$lastColumn$invoice_row";
        $objWorksheet->getStyle($range)->applyFromArray(
            array(
                'fill' => array(
                    'type' => PHPExcel_Style_Fill::FILL_SOLID,
                    'color' => array('rgb' => 'ffc000'),
                ),
            )
        );
    }



    // Si le statut est "PAID", appliquer la couleur de fond verte à toute la ligne
    if ($customer_invoices['status'] == 'PAID') {
        $lastColumn = 'F';  // Modifier cela en fonction de la dernière colonne que vous avez
        $range = "A$invoice_row:$lastColumn$invoice_row";
        $objWorksheet->getStyle($range)->applyFromArray(
            array(
                'fill' => array(
                    'type' => PHPExcel_Style_Fill::FILL_SOLID,
                    'color' => array('rgb' => '90d050'),
                ),
            )
        );
    }




    // $invoice_row++;

    // Exécuter la requête pour obtenir les paiements liés à la facture en cours
    $payment_sql = "SELECT * FROM ivc_cus_payment WHERE ivc_nbr = :ivc_nbr;";
    $payment_sth = $dbh->prepare($payment_sql);
    $payment_sth->bindParam(':ivc_nbr', $customer_invoices['ivc_nbr']);
    $payment_q = $payment_sth->execute();

    // Ajouter une sous-section pour les paiements
    $payment_row = $invoice_row + $payment_row_offset;
    $objWorksheet->getStyle("B$payment_row:E$payment_row")->applyFromArray($styleArrayHeaders);
    $objWorksheet->setCellValueByColumnAndRow(1, $payment_row, 'Remittance Date');
    $objWorksheet->setCellValueByColumnAndRow(2, $payment_row, 'Remittance Type');
    $objWorksheet->setCellValueByColumnAndRow(3, $payment_row, 'Reference');
    $objWorksheet->setCellValueByColumnAndRow(4, $payment_row, 'Amount');

    if ($payment_sth->rowCount() === 0) {
        // Aucun paiement trouvé, fusionner les colonnes B à E
        $no_remittance_row = $payment_row + 1;
        $objWorksheet->mergeCells("B$no_remittance_row:E$no_remittance_row");
        $objWorksheet->getStyle("B$no_remittance_row:E$no_remittance_row")->applyFromArray($styleArrayInvoice);
        $objWorksheet->setCellValueByColumnAndRow(1, $no_remittance_row, 'No Remittance for this invoice');
        $payment_row++;
    } else {
        // Paiements trouvés, ajouter les détails du paiement à la feuille Excel
        while ($payment = $payment_sth->fetch()) {
            // Ajouter les détails du paiement à la feuille Excel
            $payment_row++;
            $objWorksheet->getStyle("B$payment_row:E$payment_row")->applyFromArray($styleArrayInvoice);
            $objWorksheet->setCellValueByColumnAndRow(1, $payment_row, $payment['payment_date']);
            $objWorksheet->setCellValueByColumnAndRow(2, $payment_row, $payment['payment_type']);
            $objWorksheet->setCellValueByColumnAndRow(3, $payment_row, $payment['reference']);
            $objWorksheet->getStyleByColumnAndRow(4, $payment_row)->getNumberFormat()->setFormatCode('#,##0.00');
            $objWorksheet->setCellValueByColumnAndRow(4, $payment_row, $payment['payment_amount']);

            // Appliquer la couleur en fonction du type de paiement
            switch ($payment['payment_type']) {
                case 'Payment':
                    $paymentColor = '92d050';
                    break;
                case 'Deposit':
                    $paymentColor = '76933c';
                    break;
                case 'CreditNote':
                    $paymentColor = '00b0f0';
                    break;
                default:
                    $paymentColor = 'FFFFFF'; // Couleur par défaut (blanc)
            }

            // Appliquer la couleur de fond à la ligne de paiement
            $lastPaymentColumn = 'E';  // Modifier cela en fonction de la dernière colonne que vous avez
            $paymentRange = "B$payment_row:$lastPaymentColumn$payment_row";
            $objWorksheet->getStyle($paymentRange)->applyFromArray(
                array(
                    'fill' => array(
                        'type' => PHPExcel_Style_Fill::FILL_SOLID,
                        'color' => array('rgb' => $paymentColor),
                    ),
                )
            );
        }
    }

    // Mettre à jour la ligne de facture pour la prochaine itération de la boucle externe
    $invoice_row = $payment_row;
    $invoice_row++;
    $invoice_row++;
}

// ---------------------------------------------------------------------------------------------------------------------------------------------------


// ------------------ Sheet Deposits ---------------------------------------------------------------------------------------------------------------------------------
$objWorksheet = $objPHPexcel->getSheetByName('Deposits');
$deposits_row = 8; // Commencez à la ligne 8 pour les deposits

setCellValueByVariableName($objWorksheet, 'cust_code', html_entity_decode($customer['custcode'], ENT_QUOTES));
setCellValueByVariableName($objWorksheet, 'company_name', html_entity_decode($customer['companyname_en'], ENT_QUOTES));
setCellValueByVariableName($objWorksheet, 'launch_date', date_format(date_create($row['due_date']), 'M d, Y'));
setCellValueByVariableName($objWorksheet, 'payment_terms', html_entity_decode($customer['paymethod'] . " " . resolveCode_master_type_code('PAYMETHOD', $customer['paymethod']), ENT_QUOTES));

$sql_deposits = "SELECT *
FROM cus_deposit
WHERE cust_code = :customer_code
AND deposit_status <> 'FINISHED'";
$sth_deposits = $dbh->prepare($sql_deposits);

$params[':customer_code'] = $customer['custcode'];

$q = $sth_deposits->execute($params);

pdo_showerror($sth_deposits, $q);

while ($deposit = $sth_deposits->fetch()) {
    $styleDepositsList = array(
        'font' => array(
            'name' => 'Arial',
            'size' => 10,
        ),
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        ),
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array('argb' => '000000'), // Couleur des bordures, ici noir (000000)
            ),
        ),
    );


    $objWorksheet->getStyle("A$deposits_row:E$deposits_row")->applyFromArray($styleDepositsList);

    $objWorksheet->setCellValueByColumnAndRow(0, $deposits_row, $deposit['deposit_add_date']);
    $objWorksheet->setCellValueByColumnAndRow(1, $deposits_row, $deposit['deposit_reference']);
    $objWorksheet->getStyleByColumnAndRow(2, $deposits_row)->getNumberFormat()->setFormatCode('#,##0.00');
    $objWorksheet->setCellValueByColumnAndRow(2, $deposits_row, $deposit['deposit_total_amount']);
    $objWorksheet->getStyleByColumnAndRow(3, $deposits_row)->getNumberFormat()->setFormatCode('#,##0.00');
    $objWorksheet->setCellValueByColumnAndRow(3, $deposits_row, $deposit['deposit_remaining_amount']);
    $objWorksheet->setCellValueByColumnAndRow(4, $deposits_row, ucwords(strtolower($deposit['deposit_status'])));

    // Si le statut est "USED", appliquer la couleur de fond verte à toute la ligne
    if ($deposit['deposit_status'] == 'USED') {
        $lastColumn = 'E';  // Modifier cela en fonction de la dernière colonne que vous avez
        $range = "A$deposits_row:$lastColumn$deposits_row";
        $objWorksheet->getStyle($range)->applyFromArray(
            array(
                'fill' => array(
                    'type' => PHPExcel_Style_Fill::FILL_SOLID,
                    'color' => array('rgb' => '76933c'),
                ),
            )
        );
    }

    $deposits_row++;
}


// ---------------------------------------------------------------------------------------------------------------------------------------------------
// ------------------ Sheet Full Payment ---------------------------------------------------------------------------------------------------------------------------------
$objWorksheet = $objPHPexcel->getSheetByName('Payments');
$fullPayments_row = 8; // Commencez à la ligne 8 pour les payments

setCellValueByVariableName($objWorksheet, 'cust_code', html_entity_decode($customer['custcode'], ENT_QUOTES));
setCellValueByVariableName($objWorksheet, 'company_name', html_entity_decode($customer['companyname_en'], ENT_QUOTES));
setCellValueByVariableName($objWorksheet, 'launch_date', date_format(date_create($row['due_date']), 'M d, Y'));
setCellValueByVariableName($objWorksheet, 'payment_terms', html_entity_decode($customer['paymethod'] . " " . resolveCode_master_type_code('PAYMETHOD', $customer['paymethod']), ENT_QUOTES));

$sql_fullPayments = "SELECT *
FROM cus_payments
WHERE cust_code = :customer_code
AND payments_status <> 'FINISHED'";
$sth_fullPayments = $dbh->prepare($sql_fullPayments);

$params[':customer_code'] = $customer['custcode'];

$q = $sth_fullPayments->execute($params);

pdo_showerror($sth_fullPayments, $q);

while ($fullPayments = $sth_fullPayments->fetch()) {
    $stylefullPaymentsList = array(
        'font' => array(
            'name' => 'Arial',
            'size' => 10,
        ),
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        ),
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array('argb' => '000000'), // Couleur des bordures, ici noir (000000)
            ),
        ),
    );


    $objWorksheet->getStyle("A$fullPayments_row:E$fullPayments_row")->applyFromArray($stylefullPaymentsList);

    $objWorksheet->setCellValueByColumnAndRow(0, $fullPayments_row, $fullPayments['payments_add_date']);
    $objWorksheet->setCellValueByColumnAndRow(1, $fullPayments_row, $fullPayments['payments_reference']);
    $objWorksheet->getStyleByColumnAndRow(2, $fullPayments_row)->getNumberFormat()->setFormatCode('#,##0.00');
    $objWorksheet->setCellValueByColumnAndRow(2, $fullPayments_row, $fullPayments['payments_total_amount']);
    $objWorksheet->getStyleByColumnAndRow(3, $fullPayments_row)->getNumberFormat()->setFormatCode('#,##0.00');
    $objWorksheet->setCellValueByColumnAndRow(3, $fullPayments_row, $fullPayments['payments_remaining_amount']);
    $objWorksheet->setCellValueByColumnAndRow(4, $fullPayments_row, ucwords(strtolower($fullPayments['payments_status'])));

    // Si le statut est "USED", appliquer la couleur de fond verte à toute la ligne
    if ($fullPayments['payments_status'] == 'USED') {
        $lastColumn = 'E';  // Modifier cela en fonction de la dernière colonne que vous avez
        $range = "A$fullPayments_row:$lastColumn$fullPayments_row";
        $objWorksheet->getStyle($range)->applyFromArray(
            array(
                'fill' => array(
                    'type' => PHPExcel_Style_Fill::FILL_SOLID,
                    'color' => array('rgb' => '92d050'),
                ),
            )
        );
    }

    $fullPayments_row++;
}


// ---------------------------------------------------------------------------------------------------------------------------------------------------

// ------------------ Sheet Credit Note ---------------------------------------------------------------------------------------------------------------------------------
$objWorksheet = $objPHPexcel->getSheetByName('Credit Notes');
$creditNote_row = 8; // Commencez à la ligne 8 pour les credit notes

setCellValueByVariableName($objWorksheet, 'cust_code', html_entity_decode($customer['custcode'], ENT_QUOTES));
setCellValueByVariableName($objWorksheet, 'company_name', html_entity_decode($customer['companyname_en'], ENT_QUOTES));
setCellValueByVariableName($objWorksheet, 'launch_date', date_format(date_create($row['due_date']), 'M d, Y'));
setCellValueByVariableName($objWorksheet, 'payment_terms', html_entity_decode($customer['paymethod'] . " " . resolveCode_master_type_code('PAYMETHOD', $customer['paymethod']), ENT_QUOTES));

$sql_creditNote = "SELECT *
FROM cn_cus_main
WHERE cn_customer = :customer_code
AND status <> 'FINISHED'";
$sth_creditNote = $dbh->prepare($sql_creditNote);

$params[':customer_code'] = $customer['custcode'];

$q = $sth_creditNote->execute($params);

pdo_showerror($sth_creditNote, $q);

while ($creditNote = $sth_creditNote->fetch()) {
    $stylecreditNoteList = array(
        'font' => array(
            'name' => 'Arial',
            'size' => 10,
        ),
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        ),
        'borders' => array(
            'allborders' => array(
                'style' => PHPExcel_Style_Border::BORDER_THIN,
                'color' => array('argb' => '000000'), // Couleur des bordures, ici noir (000000)
            ),
        ),
    );


    $objWorksheet->getStyle("A$creditNote_row:E$creditNote_row")->applyFromArray($stylecreditNoteList);

    $objWorksheet->setCellValueByColumnAndRow(0, $creditNote_row, $creditNote['launch_date']);
    $objWorksheet->setCellValueByColumnAndRow(1, $creditNote_row, $creditNote['cn_nbr']);
    $objWorksheet->getStyleByColumnAndRow(2, $creditNote_row)->getNumberFormat()->setFormatCode('#,##0.00');
    $objWorksheet->setCellValueByColumnAndRow(2, $creditNote_row, $creditNote['ttl_amt']);

    if ($creditNote['remaining_amount'] == NULL) {
        $cn_remainingBalance = $creditNote['ttl_amt'];
    } else {
        $cn_remainingBalance = $creditNote['remaining_amount'];
    }
    $objWorksheet->getStyleByColumnAndRow(3, $creditNote_row)->getNumberFormat()->setFormatCode('#,##0.00');
    $objWorksheet->setCellValueByColumnAndRow(3, $creditNote_row, $cn_remainingBalance);
    $objWorksheet->setCellValueByColumnAndRow(4, $creditNote_row, ucwords(strtolower($creditNote['status'])));

    // Si le statut est "USED", appliquer la couleur de fond verte à toute la ligne
    if ($creditNote['status'] == 'USED') {
        $lastColumn = 'E';  // Modifier cela en fonction de la dernière colonne que vous avez
        $range = "A$creditNote_row:$lastColumn$creditNote_row";
        $objWorksheet->getStyle($range)->applyFromArray(
            array(
                'fill' => array(
                    'type' => PHPExcel_Style_Fill::FILL_SOLID,
                    'color' => array('rgb' => '00b0f0'),
                ),
            )
        );
    }

    $creditNote_row++;
}




// ---------------------------------------------------------------------------------------------------------------------------------------------------



//exit;
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="Balance_' . $customer['custcode'] . '.xls"');
header('Cache-Control: max-age=0');

$objPHPexcel->setActiveSheetIndex(0);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel, 'Excel5');
$objWriter->save('php://output');

//$objWriter = new PHPExcel_Writer_PDF($objPHPexcel);
//$objWriter->save("test.pdf");

flush();