EXCELIA_DBARR/custom/bbus/class/facturestat.class.php

<?php
require_once DOL_DOCUMENT_ROOT . '/custom/bbus/class/facturestatcommon.class.php';
class FactureStat extends FactureStatCommon
{

    /**
     * Get total sale in HUF in CARD
     * 
     * @param string    $fromDate       From date in YYYY-mm-dd H:i:s format
     * @param string    $toDate         To date in YYYY-mm-dd H:i:s format
     * @param int       $entity         Entity ID (optional, default=0)
     * 
     * @return array Arrays of records or empty
     */
    public function sumSaleInHufCard(string $fromDate, string $toDate, int $entity=0): array
    {
        return $this->factureSummary($fromDate, $toDate, FactureStatCommon::HUF, $entity, FactureStatCommon::CARD);
    }

    /**
     * Get total sale in EUR in CARD
     * 
     * @param string    $fromDate       From date in YYYY-mm-dd H:i:s format
     * @param string    $toDate         To date in YYYY-mm-dd H:i:s format
     * @param int       $entity         Entity ID (optional, default=0)
     * 
     * @return array Arrays of records or empty
     */
    public function sumSaleInEurCard(string $fromDate, string $toDate, int $entity=0): array
    {
        return $this->factureSummary($fromDate, $toDate, FactureStatCommon::EUR, $entity, FactureStatCommon::CARD);
    }

    /**
     * Get total sale in EUR in CASH
     * 
     * @param string    $fromDate       From date in YYYY-mm-dd H:i:s format
     * @param string    $toDate         To date in YYYY-mm-dd H:i:s format
     * @param int       $entity         Entity ID (optional, default=0)
     * 
     * @return array Arrays of records or empty
     */
    public function sumSaleInEurCash(string $fromDate, string $toDate, int $entity=0): array
    {
        return $this->factureSummary($fromDate, $toDate, FactureStatCommon::EUR, $entity, FactureStatCommon::CASH);
    }

    /**
     * Get total sale in HUF in CASH
     * 
     * @param string    $fromDate       From date in YYYY-mm-dd H:i:s format
     * @param string    $toDate         To date in YYYY-mm-dd H:i:s format
     * @param int       $entity         Entity ID (optional, default=0)
     * 
     * @return array Arrays of records or empty
     */
    public function sumSaleInHufCash(string $fromDate, string $toDate, int $entity=0): array
    {
        return $this->factureSummary($fromDate, $toDate, FactureStatCommon::HUF, $entity, FactureStatCommon::CASH);
    }

    /**
     * Get total sale in HUF
     * 
     * @param string    $fromDate       From date in YYYY-mm-dd H:i:s format
     * @param string    $toDate         To date in YYYY-mm-dd H:i:s format
     * @param int       $entity         Entity ID (optional, default=0)
     * @param string    $paymentCode    Payment code (optional, possible values LIQ or CB, default='')
     * 
     * @return array Arrays of records or empty
     */
    public function sumSaleInEur(string $fromDate, string $toDate, int $entity=0, string $paymentCode=''): array
    {
        return $this->factureSummary($fromDate, $toDate, FactureStatCommon::EUR, $entity, $paymentCode);
    }

    /**
     * Get total sale in HUF
     * 
     * @param string    $fromDate       From date in YYYY-mm-dd H:i:s format
     * @param string    $toDate         To date in YYYY-mm-dd H:i:s format
     * @param int       $entity         Entity ID (optional, default=0)
     * @param string    $paymentCode    Payment code (optional, possible values LIQ or CB, default='')
     * 
     * @return array Arrays of records or empty
     */
    public function sumSaleInHuf(string $fromDate, string $toDate, int $entity=0, string $paymentCode=''): array
    {
        return $this->factureSummary($fromDate, $toDate, FactureStatCommon::HUF, $entity, $paymentCode);
    }

    /**
     * Get rows from DB
     * 
     * @param string    $fromDate       From date in YYYY-mm-dd H:i:s format
     * @param string    $toDate         To date in YYYY-mm-dd H:i:s format
     * @param string    $currency       Currency (EUR or HUF)
     * @param int       $entity         Entity ID (optional, default=0)
     * @param string    $paymentCode    Payment code (optional, possible values LIQ or CB, default='')
     * 
     * @return array Arrays of records or empty
     */
    private function factureSummary(string $fromDate, string $toDate, string $currency, int $entity=0, string $paymentCode=''): array
    {
        $result = [];
        // entity condition
        $sqlEntity = $this->getEntitySql($entity);
        // payment code condition
        $sqlPaymentCode = (empty($paymentCode)) ? '' : $this->getPaymentCodeSql($paymentCode);

        $sql = "
            SELECT f.entity, d.tva_tx, SUM(d.multicurrency_total_ht) AS multicurrency_total_ht, 
            SUM(d.multicurrency_total_tva) AS multicurrency_total_tva, 
            SUM(d.multicurrency_total_ttc) AS multicurrency_total_ttc
            FROM ".MAIN_DB_PREFIX."facture AS f
            JOIN ".MAIN_DB_PREFIX."facturedet AS d ON d.fk_facture=f.rowid
            WHERE f.date_closing BETWEEN '{$fromDate}' AND '{$toDate}'
            AND f.multicurrency_code ILIKE '{$currency}'
            {$sqlEntity}
            {$sqlPaymentCode}
            GROUP BY f.entity, d.tva_tx
            ORDER BY d.tva_tx ASC
        ";

        $rows = $this->db->query($sql);
        if ($rows) {
            while ($row = $this->db->fetch_object($rows)) {
                $result[] = (array) $row;
            }
        }

        return $result;
    }
}