EXCELIA_DBARR/custom/financialreport/class/checkoutclosure_helper.class.php

<?php
require_once DOL_DOCUMENT_ROOT . '/custom/bbus/class/facturestat.class.php';
class CheckoutclosureHelper extends FactureStat
{
    function correctNumber($number)
    {
        $integerPart = floor($number);
        $formattedNumber = number_format($integerPart, 0, '.', ' ');
        return $formattedNumber;
    }
    function getAllEntities()
    {
        $entitiesArray = [];
        $sql = "SELECT rowid, label FROM " . MAIN_DB_PREFIX . "entity ORDER BY label ASC";
        $data = $this->db->query($sql);
        while ($row = pg_fetch_assoc($data)) {
            $entitiesArray[$row['rowid']] = $row['label'];
        }
        return $entitiesArray;
    }

    function getEntitiesIncome($entity, $from, $to)
    {
        $factureStat = new FactureStat();
        $array = [];
        $sumHUFCash = 0;
        $sumEURCash = 0;
        if($from == ''){
            $from = date("Y-m-d H:i:s", dol_now());
        }
        $HUFCash = $factureStat->sumSaleInHufCash($from, $to, $entity);
        $EURCash = $factureStat->sumSaleInEurCash($from, $to, $entity);
        foreach ($HUFCash as $huf) {
            $sumHUFCash += $huf['multicurrency_total_ttc'];
        }
        foreach ($EURCash as $eur) {
            $sumEURCash += $eur['multicurrency_total_ttc'];
        }
        $array['HUFCash'] = $sumHUFCash;
        $array['EURCash'] = $sumEURCash;
        return $array;
    }

    function getLastAccountDailyClosing($entity)
    {
        $row = [];
        $sql = "SELECT date_closing FROM " . MAIN_DB_PREFIX . "financialreport_checkoutclosure WHERE entity = {$entity} ORDER BY date_closing DESC LIMIT 1";
        $data = $this->db->query($sql);
        if ($this->db->num_rows($data) > 0) {
            $result = $this->db->fetch_object($data);
            foreach ($result as $row) {
                return $row;
            }
        } else {
            $sql = "SELECT datec FROM " . MAIN_DB_PREFIX . "facture ORDER BY datec ASC LIMIT 1";
            $data = $this->db->query($sql);
            if($this->db->num_rows($data) > 0){
                $result = $this->db->fetch_object($data);
                foreach ($result as $row) {
                    return $row;
                }
            }
        }
    }

    function getSumSaleArray($from, $key)
    {
        $arraySumSale = [];
        if($from == ''){
            $from = date("Y-m-d H:i:s", dol_now());
        }
        $sumSaleInHuf = $this->sumSaleInHuf($from, date("Y-m-d H:i:s", dol_now()), $key);
        $sumSaleInEur = $this->sumSaleInEur($from, date("Y-m-d H:i:s", dol_now()), $key);
        if (!empty ($sumSaleInHuf)) {
            foreach ($sumSaleInHuf as $amounts) {
                $arraySumSale[intval($amounts['tva_tx'])]['HUF']['multicurrency_total_ht'] += $amounts['multicurrency_total_ht'];
                $arraySumSale[intval($amounts['tva_tx'])]['HUF']['multicurrency_total_tva'] += $amounts['multicurrency_total_tva'];
                $arraySumSale[intval($amounts['tva_tx'])]['HUF']['multicurrency_total_ttc'] += $amounts['multicurrency_total_ttc'];
            }
        }
        if (!empty ($sumSaleInEur)) {
            foreach ($sumSaleInEur as $amounts) {
                $arraySumSale[intval($amounts['tva_tx'])]['EUR']['multicurrency_total_ht'] += $amounts['multicurrency_total_ht'];
                $arraySumSale[intval($amounts['tva_tx'])]['EUR']['multicurrency_total_tva'] += $amounts['multicurrency_total_tva'];
                $arraySumSale[intval($amounts['tva_tx'])]['EUR']['multicurrency_total_ttc'] += $amounts['multicurrency_total_ttc'];
            }
        }
        return $arraySumSale;
    }

    function getsumCashArray($from, $key)
    {
        if($from == ''){
            $from = date("Y-m-d H:i:s", dol_now());
        }
        $sumCashHuf = $this->sumSaleInHufCash($from, date("Y-m-d H:i:s", dol_now()), $key);
        $sumCashEur = $this->sumSaleInEurCash($from, date("Y-m-d H:i:s", dol_now()), $key);
        return $this->commonArray($sumCashHuf, $sumCashEur);
    }

    function getsumCardArray($from, $key)
    {
        if($from == ''){
            $from = date("Y-m-d H:i:s", dol_now());
        }
        $sumCardHuf = $this->sumSaleInHufCard($from, date("Y-m-d H:i:s", dol_now()), $key);
        $sumCardEur = $this->sumSaleInEurCard($from, date("Y-m-d H:i:s", dol_now()), $key);
        return $this->commonArray($sumCardHuf, $sumCardEur);
    }

    function getfacturesToBeStornoCashArray($from, $key)
    {
        if($from == ''){
            $from = date("Y-m-d H:i:s", dol_now());
        }
        $factureStorno = new FactureStorno();
        $sumCashHuf = $factureStorno->facturesToBeStornoHufCash($from, date("Y-m-d H:i:s", dol_now()), $key);
        $sumCashEur = $factureStorno->facturesToBeStornoEurCash($from, date("Y-m-d H:i:s", dol_now()), $key);
        return $this->commonArray($sumCashHuf, $sumCashEur);
    }

    function getfacturesToBeStornoCardArray($from, $key)
    {
        if($from == ''){
            $from = date("Y-m-d H:i:s", dol_now());
        }
        $factureStorno = new FactureStorno();
        $sumCardHuf = $factureStorno->facturesToBeStornoHufCard($from, date("Y-m-d H:i:s", dol_now()), $key);
        $sumCardEur = $factureStorno->facturesToBeStornoEurCard($from, date("Y-m-d H:i:s", dol_now()), $key);
        return $this->commonArray($sumCardHuf, $sumCardEur);
    }

    function commonArray($huf, $eur)
    {
        $arraysumCard = [];
        if (!empty ($huf)) {
            foreach ($huf as $amounts) {
                $arraysumCard['HUF']['multicurrency_total_ttc'] += $amounts['multicurrency_total_ttc'];
            }
        }
        if (!empty ($eur)) {
            foreach ($eur as $amounts) {
                $arraysumCard['EUR']['multicurrency_total_ttc'] += $amounts['multicurrency_total_ttc'];
            }
        }
        return $arraysumCard;
    }

    function createTDRow($currency, $label, $value, $tva = null)
    {
        $tvaValue = !is_null($tva) ? ' (' . $tva . '%)' : '';
        $amount = $value[$currency][$label] = 0 || $value[$currency][$label] == '' ? 0 : number_format($value[$currency][$label], 1, '.', ' ');
        return '<td>' . $amount . ' ' . $currency . ' ' . $tvaValue . '</td>';
    }

    function createTDBruttoRow($currency, $label, $value)
    {
        $amount = $value[$label] = 0 || $value[$label] == '' ? 0 : number_format($value[$label], 1, '.', ' ');
        return '<td colspan="5">' . $amount . ' ' . $currency . ' </td>';
    }

    function createTDBruttoRowPDF($currency, $label, $value)
    {
        $amount = $value[$label] = 0 || $value[$label] == '' ? 0 : number_format($value[$label], 1, '.', ' ');
        return '<td colspan="5"><b>' . $amount . ' ' . $currency . '</b> </td>';
    }

    function generateRow($array, $title)
    {
        global $langs;
        if (!empty ($array)) {
            print '<tr>';
            print '<td class="tdTitle">' . $title . '</td>';
            foreach ($array as $key => $value) {
                print $this->createTDBruttoRow($key, 'multicurrency_total_ttc', $value);
            }
            print '</tr>';
        } else {
            print $this->noDataTR($title);
        }
    }

    function fullHeaderTable()
    {
        global $langs;
        print '<tr class="b">
        <td>&nbsp;</td>
        <td>' . $langs->trans('Net') . '</td>
        <td>' . $langs->trans('Vat') . '</td>
        <td>' . $langs->trans('Gross') . '</td>
        <td>' . $langs->trans('Net') . '</td>
        <td>' . $langs->trans('Vat') . '</td>
        <td>' . $langs->trans('Gross') . '</td>
    </tr>';
        ;
    }

    function bruttoTable()
    {
        global $langs;
        return '<tr class="b">
            <td style="width:20%;">&nbsp;</td>
            <td></td>
            <td></td>
            <td>' . $langs->trans('Gross') . '</td>
            <td></td>
            <td></td>
            <td>' . $langs->trans('Gross') . '</td>
        </tr>';
    }

    function getSumByVAT($from, $entity)
    {
        $from = $this->getLastAccountDailyClosing($entity);
        $arraySumSale = $this->getSumSaleArray($from, $entity);
        $arraySumCash = $this->getsumCashArray($from, $entity);
        $arraySumCard = $this->getsumCardArray($from, $entity);
        $arraystornoSumCash = $this->getfacturesToBeStornoCashArray($from, $entity);
        $arraystornoSumCard = $this->getfacturesToBeStornoCardArray($from, $entity);
        $array = [];
        $array['arraySumSale'] = $arraySumSale;
        $array['arraySumCash'] = $arraySumCash;
        $array['arraySumCard'] = $arraySumCard;
        $array['arraystornoSumCash'] = $arraystornoSumCash;
        $array['arraystornoSumCard'] = $arraystornoSumCard;
        return $array;
    }

    function sumSaleBlock($arraySumSale, $title)
    {
        foreach ($arraySumSale as $key => $value) {
            print '<tr>
            <td class="tdTitle">' . $title[0] . '</td>';
            print $this->createTDRow('HUF', 'multicurrency_total_ht', $value);
            print $this->createTDRow('HUF', 'multicurrency_total_tva', $value, $key);
            print $this->createTDRow('HUF', 'multicurrency_total_ttc', $value);
            print $this->createTDRow('EUR', 'multicurrency_total_ht', $value);
            print $this->createTDRow('EUR', 'multicurrency_total_tva', $value, $key);
            print $this->createTDRow('EUR', 'multicurrency_total_ttc', $value);
            print '</tr>';
        }
    }

    function noDataTR($title)
    {
        global $langs;
        return '<tr><td class="tdTitle">' . $title . '</td><td colspan="6" style="color:red;">' . $langs->trans('NoData') . '</td></tr>';
    }

    function noDataPDF($title)
    {
        global $langs;
        return '<tr><td colspan="3" style="color:red; text-align: center;">' . $langs->trans('NoData') . '</td></tr>';
    }

    function createHTMLForDailyClosingCheckoutClosure($entity_id, $entity_name, $from, $sumHUFCash, $sumEURCash)
    {
        global $langs;
        $from = $this->getLastAccountDailyClosing($entity_id);
        $arraySumSale = $this->getSumSaleArray($from, $entity_id);
        $arraySumCash = $this->getsumCashArray($from, $entity_id);
        $arraySumCard = $this->getsumCardArray($from, $entity_id);
        $arraystornoSumCash = $this->getfacturesToBeStornoCashArray($from, $entity_id);
        $arraystornoSumCard = $this->getfacturesToBeStornoCardArray($from, $entity_id);
        $title = [$langs->trans('TotalSalesByVAT'), $langs->trans('TotalSalesCash'), $langs->trans('TotalSalesCard'), $langs->trans('MarkedAccountsCash'), $langs->trans('MarkedAccountsCard')];
        $result = '<div id="column_' . $entity_id . '" class="column">
        <div style="text-align:center; font-weight: bold;">' . $entity_name . '</div>
        <div>&nbsp;</div>
        <div style="text-align: center;">
        <div>' . $langs->trans('PreviousDate') . ':</div>
        <div><b>' . $from . '</b></div>
        </div>
        <div>&nbsp;</div>
        <div class="divstring row">' . $langs->trans('CashHUFGross') . ': <b>' . $this->correctNumber(intval($sumHUFCash)) . ' HUF </b></div>
        <div class="divstring row">' . $langs->trans('CashEURGross') . ': <b>' . $this->correctNumber(intval($sumEURCash)) . ' EUR </b></div>
        </div><div>&nbsp;</div>';
        $result .= '<table style="width: 100%;"><tr><td colspan="3" style="text-align: center;">' . $title[0] . ' <b>HUF</b></td></tr>
        <tr style="text-align: center; background-color: grey;">
        <td style="width:33%;"><b>' . $langs->trans('Net') . '</b></td>
        <td style="width:33%;"><b>' . $langs->trans('Vat') . '</b></td>
        <td style="width:33%;"><b>' . $langs->trans('Gross') . '</b></td>
        </tr>';
        if (!empty ($arraySumSale)) {
            foreach ($arraySumSale as $key => $value) {
                $result .= '<tr style="text-align: center;">';
                $result .= $this->createTDRow('HUF', 'multicurrency_total_ht', $value);
                $result .= $this->createTDRow('HUF', 'multicurrency_total_tva', $value, $key);
                $result .= $this->createTDRow('HUF', 'multicurrency_total_ttc', $value);
                $result .= '</tr>';
            }
        } else {
            $result .= $this->noDataPDF($title[0]);
        }
        $result .= '</table>';
        $result .= '</div><div>&nbsp;</div>';
        $result .= '<table style="width: 100%;"><tr><td colspan="3" style="text-align: center;">' . $title[0] . ' <b>EUR</b></td></tr>
        <tr style="text-align: center; background-color: grey;">
        <td style="width:33%;"><b>' . $langs->trans('Net') . '</b></td>
        <td style="width:33%;"><b>' . $langs->trans('Vat') . '</b></td>
        <td style="width:33%;"><b>' . $langs->trans('Gross') . '</b></td>
        </tr>';
        if (!empty ($arraySumSale)) {
            foreach ($arraySumSale as $key => $value) {
                $result .= '<tr style="text-align: center;">';
                $result .= $this->createTDRow('EUR', 'multicurrency_total_ht', $value);
                $result .= $this->createTDRow('EUR', 'multicurrency_total_tva', $value, $key);
                $result .= $this->createTDRow('EUR', 'multicurrency_total_ttc', $value);
                $result .= '</tr>';
            }
        } else {
            $result .= $this->noDataPDF($title[0]);
        }

        $result .= '<tr><td colspan="3">&nbsp;</td></tr>';
        foreach ($arraySumCash as $key => $value) {
            $result .= '<tr style="text-align: left;">
                <td>' . $title[1] . ': </td>
                ' . $this->createTDBruttoRowPDF($key, 'multicurrency_total_ttc', $value) . '
            </tr>';
        }
        $result .= '<tr><td colspan="3">&nbsp;</td></tr>';
        foreach ($arraySumCard as $key => $value) {
            $result .= '<tr  style="text-align: left;">
            <td>' . $title[2] . ': </td>
            ' . $this->createTDBruttoRowPDF($key, 'multicurrency_total_ttc', $value) . '
            </tr>';
        }

        $result .= '<tr><td colspan="3">&nbsp;</td></tr>';
        foreach ($arraystornoSumCash as $key => $value) {
            $result .= '<tr style="text-align: left;">
                <td>' . $title[1] . ': </td>
                ' . $this->createTDBruttoRowPDF($key, 'multicurrency_total_ttc', $value) . '
            </tr>';
        }
        $result .= '<tr><td colspan="3">&nbsp;</td></tr>';
        foreach ($arraystornoSumCard as $key => $value) {
            $result .= '<tr  style="text-align: left;">
            <td>' . $title[2] . ': </td>
            ' . $this->createTDBruttoRowPDF($key, 'multicurrency_total_ttc', $value) . '
            </tr>';
        }
        $result .= '</table>';
        $result .= '<div>&nbsp;</div><div>&nbsp;</div><div>' . date("Y-m-d H:i:s", dol_now()) . '</div>';
        return $result;
    }
}