EXCELIA_DBARR/custom/bbus/class/commissionhandler.class.php

<?php

require_once DOL_DOCUMENT_ROOT . '/compta/facture/class/facture.class.php';
require_once DOL_DOCUMENT_ROOT . '/custom/bbus/class/api_curl.class.php';
require_once DOL_DOCUMENT_ROOT . '/custom/bbus/class/api_bbus_log.class.php';

class CommissionHandler
{
    use CurlApi;

    public function __construct()
    {
        global $db, $user;

        $this->db = $db;
        $this->user = $user;
    }

    function getCommissionFromProduct($id)
    {
        $remiseSUM = 0;
        $commission = 0;
        $commission_type = '';
        $sqlRemise = "SELECT fdet.remise_percent FROM llx_facture as f
        INNER JOIN llx_facturedet as fdet ON fdet.fk_facture = f.rowid
        WHERE f.rowid = {$id}";
        $resultRemise = $this->db->query($sqlRemise);
        while ($remise = pg_fetch_assoc($resultRemise)) {
            $remiseSUM += $remise['remise_percent'];
        }
        $sql = "SELECT fk_product FROM public.llx_facturedet WHERE fk_facture = {$id}";
        $result = $this->db->query($sql);
        while ($row = pg_fetch_assoc($result)) {
            $product = new Product($this->db);
            $res = $product->fetch($row['fk_product']);
            if ($res > 0) {
                if($remiseSUM > 0 && $product->array_options['options_reduced_commission'] > 0){
                    $commission_type = $this->getCommissionType($product->array_options['options_reduced_commission_type']);
                    if($commission_type != '%'){
                        $commission += $product->array_options['options_reduced_commission'];
                    }else{
                        $commission = $product->array_options['options_reduced_commission'];
                    }
                }else{
                    $commission_type = $this->getCommissionType($product->array_options['options_commission_type']);
                    if($commission_type != '%'){
                        $commission += $product->array_options['options_commission_value'];
                    }else{
                        $commission = $product->array_options['options_commission_value'];
                    }
                    
                }
            }
        }
        return number_format($commission, 8, '.', '') . '_' . $commission_type;
    }

    private function getCommissionType($type)
    {
        switch ($type) {
            case '1':
                return '%';
                break;
            case '2':
                return 'Ft';
                break;
            case '3':
                return '€';
                break;
        }
    }

    function updateCommission($object)
    {
        global $user, $db;
        $commission = $this->getCommissionFromProduct($object->id);
        $facture = new Facture($db);
        $result = $facture->fetch($object->id);
        if ($result > 0) {
            $facture->array_options['options_commission'] = $commission;
            $facture->update($user);
        }

        //$objectFacture->commission = $commission;
        //$objectFacture->update($user);
    }

    function getAllCommissionByUserId($user, $date, $currency)
    {
        $result = 0;
        $sql = "SELECT f.rowid, f.total_ttc, fe.commission FROM llx_facture as f
        INNER JOIN llx_facture_extrafields as fe ON fe.fk_object = f.rowid
        WHERE 
        fk_user_closing = {$user['user_id']} AND
        multicurrency_code = '{$currency}' AND
        date_closing < '{$date} 00:00:00'";
        $result = $this->db->query($sql);
        while ($row = pg_fetch_assoc($result)) {
            $commissions += $this->getAmountOfCommission($row);
        }
        return round($commissions, 3);
    }

    function getAllCommission($allUsersString, $currency, $from, $to, $allComissionInvoicesOfUserInTheGroupFromUserInvoice)
    {
        $commissions = 0;
        $sql = "SELECT f.rowid, f.total_ttc, fe.commission FROM llx_facture as f
        INNER JOIN llx_facture_extrafields as fe ON fe.fk_object = f.rowid
        WHERE f.fk_user_closing IN ({$allUsersString})
        AND EXISTS (SELECT * FROM llx_bbus_bbticketinvoiceprinting as bbip WHERE bbip.fk_facture = f.rowid ORDER BY bbip.rowid DESC LIMIT 1) AND ";
        if (!empty($allComissionInvoicesOfUserInTheGroupFromUserInvoice)) {
            $sql .= " f.rowid NOT IN({$allComissionInvoicesOfUserInTheGroupFromUserInvoice}) AND";
        }
        $sql .= " multicurrency_code = '{$currency}' AND
        date_closing BETWEEN '{$from}' AND '{$to}' 
        --AND fe.marked_for_storno IS NULL 
        AND fe.commission_deduction is NULL
        AND (SELECT a.rowid FROM llx_facture as a WHERE a.fk_facture_source = f.rowid AND fk_user_closing IN ({$allUsersString})) Is NULL";
        //print $sql.'<br>';
        $result = $this->db->query($sql);
        while ($row = pg_fetch_assoc($result)) {
            $commissions += $this->getAmountOfCommission($row);
        }
        return $commissions;
    }

    function getCommisonByUserId($user_id, $currency, $from, $to, $allComissionInvoicesOfUserInTheGroupFromUserInvoice)
    {
        $to = date('Y-m-d H:i:s', dol_now());
        $crossShoppingFacturesString = "'MrNSTR2409-001003'";

        $params = ["from" => $from, "to" => $to, "factures" => $crossShoppingFacturesString];
        $postFields = json_encode($params);
        $crossShoppingFactures = $this->curlRunner('bookingapi/getPrintedFacturesRefs', $postFields, 'POST', true);
        global $db;
        $commissions = 0;
        $sql = "SELECT f.rowid, f.total_ttc, fe.commission, f.ref, bbt.rowid as bbt_rowid, bbtip.rowid as bbtip_rowid
        FROM llx_facture as f 
        INNER JOIN llx_facture_extrafields as fe ON fe.fk_object = f.rowid 
        LEFT JOIN llx_bbus_bbticket as bbt ON bbt.fk_facture = f.rowid
        LEFT JOIN llx_bbus_bbticketinvoiceprinting as bbtip ON bbtip.invoice_number = f.ref WHERE ";
        if ($crossShoppingFactures != '') {
            $sql .= " f.ref not in ({$crossShoppingFactures}) AND";
        }
        if (!empty($allComissionInvoicesOfUserInTheGroupFromUserInvoice)) {
            $sql .= " f.rowid NOT IN({$allComissionInvoicesOfUserInTheGroupFromUserInvoice}) AND";
        }
        $sql .= "
        fk_user_closing = {$user_id} 
        AND multicurrency_code = '{$currency}' 
        AND fe.commission_deduction is NULL
        AND date_closing BETWEEN '{$from}' AND '{$to}'";
        //print $sql;
        $result = $db->query($sql);
        if ($db->num_rows($result) != 0) {
            while ($row = pg_fetch_assoc($result)) {
                if (is_null($row['bbt_rowid']) || !is_null($row['bbtip_rowid'])) {
                $commissions += $this->getAmountOfCommission($row);
            }
        }
        }
        if ($crossShoppingFactures != '') {
            $sqlCS = "SELECT f.rowid, f.total_ttc, fe.commission, f.ref
        FROM llx_facture as f 
        INNER JOIN llx_facture_extrafields as fe ON fe.fk_object = f.rowid WHERE f.ref in ({$crossShoppingFactures}) AND";
            if (!empty($allComissionInvoicesOfUserInTheGroupFromUserInvoice)) {
                $sqlCS .= " f.rowid NOT IN({$allComissionInvoicesOfUserInTheGroupFromUserInvoice}) AND";
            }
            $sqlCS .= "
        fk_user_closing = {$user_id} 
        AND multicurrency_code = '{$currency}' 
        AND fe.commission_deduction is NULL
        AND date_closing BETWEEN '{$from}' AND '{$to}'";
            //print $sqlCS;
            $resultCS = $db->query($sqlCS);
            if ($db->num_rows($resultCS) != 0) {
                while ($rowCS = pg_fetch_assoc($resultCS)) {
                    $commissions += $this->getAmountOfCommission($rowCS);
                }
            }
        }
        return $commissions;
    }

    function getCashCommisonByUserId($user_id, $currency, $from, $to, $allComissionInvoicesOfUserInTheGroupFromUserInvoice, $entity, $code)
    {

        $to = date('Y-m-d H:i:s', dol_now());
        $crossShoppingFacturesString = "";
        $sqlForCrossShoppingFactures = "SELECT f.ref
        FROM llx_facture as f 
        INNER JOIN llx_facture_extrafields as fe ON fe.fk_object = f.rowid 
        LEFT JOIN llx_bbus_bbticket as bbt ON bbt.fk_facture = f.rowid
        LEFT JOIN llx_bbus_bbticketinvoiceprinting as bbtip ON bbtip.invoice_number = f.ref WHERE";
        if (!empty($allComissionInvoicesOfUserInTheGroupFromUserInvoice)) {
            $sqlForCrossShoppingFactures .= " f.rowid NOT IN({$allComissionInvoicesOfUserInTheGroupFromUserInvoice}) AND";
        }
        $sqlForCrossShoppingFactures .= " (SELECT a.rowid FROM llx_facture as a WHERE a.fk_facture_source = f.rowid AND fk_user_closing = {$user_id}) Is NULL AND
        fk_user_closing = {$user_id} 
        AND fk_mode_reglement = (SELECT id FROM public.llx_c_paiement WHERE entity = {$entity} AND code = '{$code}')
        AND multicurrency_code = '{$currency}' 
        AND date_closing BETWEEN '{$from}' AND '{$to}'";
        $resultForCrossShoppingFactures = $this->db->query($sqlForCrossShoppingFactures);
        if ($this->db->num_rows($resultForCrossShoppingFactures) > 0) {
            while ($factureCSRow = pg_fetch_assoc($resultForCrossShoppingFactures)) {
                if ($crossShoppingFacturesString == "") {
                    $crossShoppingFacturesString .= "'" . $factureCSRow['ref'] . "'";
                } else {
                    $crossShoppingFacturesString .= ",'" . $factureCSRow['ref'] . "'";
                }
            }
        }

        $params = ["from" => $from, "to" => $to, "factures" => $crossShoppingFacturesString];
        $postFields = json_encode($params);
        $crossShoppingFactures = $this->curlRunner('bookingapi/getPrintedFacturesRefs', $postFields, 'POST', true);
        $sql = $this->createSQL($from, $to, $currency, $code, $entity, $user_id, $allComissionInvoicesOfUserInTheGroupFromUserInvoice, $crossShoppingFactures);
        $commissions = $this->getMulticurrencyTotalTTC($sql);
        $sqlCroSho = $this->createSQL($from, $to, $currency, $code, $entity, $user_id, $allComissionInvoicesOfUserInTheGroupFromUserInvoice, $crossShoppingFactures, true);
        $commissionsCroSho = $this->getMulticurrencyTotalTTC($sqlCroSho);
        return $commissions + $commissionsCroSho;
    }

    function getMulticurrencyTotalTTC($sql)
    {
        $commissions = 0;
        $result = $this->db->query($sql);
        while ($factureRow = pg_fetch_assoc($result)) {
            //if (is_null($factureRow['bbt_rowid']) || !is_null($factureRow['bbtip_rowid'])) {
            $commissions += $factureRow['multicurrency_total_ttc'];
            //}
        }
        return $commissions;
    }

    function createSQL($from, $to, $currency, $code, $entity, $user_id, $allComissionInvoicesOfUserInTheGroupFromUserInvoice, $crossShoppingFactures, $crossShopping = false)
    {
        $sql = "SELECT f.rowid, f.multicurrency_total_ttc, fe.commission, f.ref, bbt.rowid as bbt_rowid, bbtip.rowid as bbtip_rowid
        FROM llx_facture as f 
        INNER JOIN llx_facture_extrafields as fe ON fe.fk_object = f.rowid
        LEFT JOIN llx_bbus_bbticket as bbt ON bbt.fk_facture = f.rowid
        LEFT JOIN llx_bbus_bbticketinvoiceprinting as bbtip ON bbtip.invoice_number = f.ref WHERE";
        if ($crossShopping && $crossShoppingFactures != "") {
            $sql .= " f.ref in ({$crossShoppingFactures}) AND";
        } else {
            if ($crossShoppingFactures != "") {
                $sql .= " f.ref not in ({$crossShoppingFactures}) AND";
            }
        }
        if (!empty($allComissionInvoicesOfUserInTheGroupFromUserInvoice)) {
            $sql .= " f.rowid NOT IN({$allComissionInvoicesOfUserInTheGroupFromUserInvoice}) AND";
        }
        $sql .= " (SELECT a.rowid FROM llx_facture as a WHERE a.fk_facture_source = f.rowid AND fk_user_closing = {$user_id}) Is NULL AND
        fk_user_closing = {$user_id} 
        AND fk_mode_reglement = (SELECT id FROM public.llx_c_paiement WHERE entity = {$entity} AND code = '{$code}')
        AND multicurrency_code = '{$currency}' 
        AND date_closing BETWEEN '{$from}' AND '{$to}'";

        return $sql;
    }

    function getCashCommisonByUserIdHistory($user_id, $currency, $allComissionInvoicesOfUserInTheGroupFromUserInvoice, $id, $entity, $code)
    {
        global $db;
        $from = $this->calculateFromDate($id);
        $to = $this->calculateToDate($user_id, $from);
        $commissions = 0;

        $sql = "SELECT f.rowid, f.multicurrency_total_ttc, fe.commission
        FROM llx_facture as f 
        INNER JOIN llx_facture_extrafields as fe ON fe.fk_object = f.rowid 
        WHERE EXISTS (SELECT * FROM llx_bbus_bbticketinvoiceprinting as bbip WHERE bbip.fk_facture = f.rowid ORDER BY bbip.rowid DESC LIMIT 1) AND";
        if (!empty($allComissionInvoicesOfUserInTheGroupFromUserInvoice)) {
            $sql .= " f.rowid NOT IN({$allComissionInvoicesOfUserInTheGroupFromUserInvoice}) AND";
        }
        $sql .= " (SELECT a.rowid FROM llx_facture as a WHERE a.fk_facture_source = f.rowid AND fk_user_closing = {$user_id}) Is NULL AND
        fk_user_closing = {$user_id} 
        AND fk_mode_reglement = (SELECT id FROM public.llx_c_paiement WHERE entity = {$entity} AND code = '{$code}')
        AND multicurrency_code = '{$currency}' 
        AND date_closing BETWEEN '{$from}' AND '{$to}'";
        //print $sql . '<br>';
        $result = $db->query($sql);
        while ($row = pg_fetch_assoc($result)) {
            $commissions += $row['multicurrency_total_ttc'];
        }
        return $commissions;
    }

    public function calculateFromDate($id)
    {
        global $db;
        $packageHistoryObj = new PackageHistory($db);
        $packageHistoryObj->fetch($id);
        return date('Y-m-d H:i:s', $packageHistoryObj->date_creation);
    }

    public function calculateToDate($user_id, $from)
    {
        global $db;
        $sql1 = "SELECT date_creation FROM public.llx_rollerstorage_packagehistory
        WHERE user_id = {$user_id}
        AND date_creation > '{$from}'
        ORDER BY rowid DESC";
        $result1 = $db->query($sql1);
        while ($row1 = pg_fetch_assoc($result1)) {
            $toDate = $row1['date_creation'];
        }
        $now = dol_now();
        return is_null($toDate) ? date('Y-m-d H:i:s', $now) : $toDate;
    }

    public function getAmountOfCommission($row)
    {
        $total_ttc = $row['total_ttc'];
        $commission = $row['commission'];
        if (isset($commission) && $commission != '_' && !is_null($commission)) {
            $array = explode('_', $commission);
            $number = $array[0];
            $type = $array[1];
            if ($type !== '%') {
                return $number;
            } else {
                //print number_format($number / 100, 4, '.', '');
                return $total_ttc * number_format($number / 100, 4, '.', '');
            }
        } else {
            return 0;
        }

    }

    function getOccassionOfThisPeriodByDateAndUserId_HUF($user_id, $from, $to)
    {
        $commissions = 0;
        $sql = "SELECT f.rowid, f.total_ttc, fe.commission FROM llx_facture as f
        INNER JOIN llx_facture_extrafields as fe ON fe.fk_object = f.rowid
        WHERE 
        fk_user_closing = {$user_id}  AND
        multicurrency_code = 'HUF' AND
        date_closing BETWEEN '{$from}' AND '{$to}' ";
        $result = $this->db->query($sql);
        while ($row = pg_fetch_assoc($result)) {
            $commissions += $this->getAmountOfCommission($row);
        }
        return $commissions;
    }

    function getCommisonByUserIdForAMonth($user_id, $year = null, $month = null)
    {
        global $db;
        $commissions = 0;
        $sql = "SELECT sum(amount) FROM llx_financialreport_userinvoice
        WHERE user_id = {$user_id}
        AND payment_type = 2";
        if (!is_null($year) && !is_null($month)) {
            $sql .= " AND DATE_PART('year', date_creation) = {$year} AND DATE_PART('month', date_creation) = {$month}";
        }
        $result = $db->query($sql);
        while ($row = pg_fetch_assoc($result)) {
            return $row['sum'];
        }
    }

    function getUserMinimumCommission($user_id)
    {
        global $db;
        $userObj = new User($db);
        $sql = "SELECT ue.minimum_monthly_commission FROM llx_user as u INNER JOIN llx_user_extrafields as ue ON ue.fk_object = u.rowid WHERE u.rowid = {$user_id}";
        $result = $db->query($sql);
        while($row = $db->fetch_object($result))
        {
            return $row->minimum_monthly_commission;
        }
    }

    function getTotalCommission($user_id, $from, $to)
    {
        global $db;
        $helper = new HelperUserInvoice($db);
        $allComissionInvoicesOfUserInTheGroupFromUserInvoice = $helper->getAllComissionInvoicesOfUserInTheGroupFromUserInvoice($user_id, $from, date("Y-m-d H:i:s", dol_now()));
        $commissionHUF = $this->getCommisonByUserId($user_id, 'HUF', $from, $to, $allComissionInvoicesOfUserInTheGroupFromUserInvoice);
        $commissionEUR = $this->getCommisonByUserId($user_id, 'EUR', $from, $to, $allComissionInvoicesOfUserInTheGroupFromUserInvoice);
        $fullCommission = $commissionHUF + $commissionEUR;
        return $fullCommission;
    }

    function getTotalCommissionFromThisPeriod($user_id, $from, $to)
    {
        global $db;
        $from = date("Y-m-d H:i:s", strtotime('-1 hour', strtotime($from)));
        $array = [];
        $sql = "SELECT label, amount, date_creation FROM " . MAIN_DB_PREFIX . "financialreport_userinvoice WHERE user_id = {$user_id} AND payment_type = 2 AND date_creation BETWEEN '{$from}' AND '{$to}'";
        $data = $db->query($sql);
        if(pg_num_rows($data) > 0){
            while ($row = pg_fetch_assoc($data)) {
                $array[] = $row;
            }
        }
        return $array;
    }

    function getDailyMinimumCommission()
    {
        global $db;
        $date = date('Y-m-d H:i:s', dol_now());
        $sql = "SELECT option.amount FROM public.llx_financialreport_commissioninterval AS interval
        INNER JOIN llx_financialreport_commissionoptions as option ON option.rowid = interval.option_id
        WHERE '{$date}' BETWEEN interval.interval_start AND interval.interval_end";
        //WHERE '2023-01-01 10:10:10' BETWEEN interval.interval_start AND interval.interval_end";
        $result = $db->query($sql);
        if(pg_num_rows($result) > 0){
            while ($row = pg_fetch_assoc($result)) {
                return $row['amount'];
            }
        }
        return 0;
    }

}