EXCELIA_DBARR/custom/financialreport/class/helper.class.php

<?php
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;

require_once DOL_DOCUMENT_ROOT . '/includes/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Spreadsheet.php';
require_once DOL_DOCUMENT_ROOT . '/includes/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Writer/Xlsx.php';
require_once DOL_DOCUMENT_ROOT . '/custom/settlements/class/usernaplo.class.php';
require_once DOL_DOCUMENT_ROOT . '/custom/settlements/class/hotel.class.php';
require_once DOL_DOCUMENT_ROOT . '/custom/settlements/class/group.class.php';
require_once DOL_DOCUMENT_ROOT . '/custom/bbus/class/commissionhandler.class.php';


class HelperUserInvoice
{
    public $db;

    public function __construct(DoliDB $db)
    {
        $this->db = $db;
        $this->userCategoryArray = [1 => "sales / kereskedő", 2 => "office / irodai dolgozó", 3 => "management / vezetőség", 4 => "maintenance / szerelő", 5 => "host(ess) / buszkísérő", 6 => "accounts / elszámolók", 7 => "hotelsales"];
    }
    /**
     * Output the buttons to submit a creation/edit form
     *
     * @param   string  $save_label     	Alternative label for save button
     * @param   string  $cancel_label   	Alternative label for cancel button
     * @param   array   $morebuttons    	Add additional buttons between save and cancel
     * @param   bool    $withoutdiv     	Option to remove enclosing centered div
     * @param	string	$morecss			More CSS
     * @param	string	$dol_openinpopup	If the button are shown in a context of a page shown inside a popup, we put here the string name of popup.
     * @return 	string						Html code with the buttons
     */
    public function buttonsSaveCancel($save_label = 'Save', $cancel_label = 'Cancel', $cancelURL = '', $morebuttons = array(), $withoutdiv = 0, $morecss = '', $dol_openinpopup = '')
    {
        global $langs;

        $buttons = array();

        $save = array(
            'name' => 'save',
            'label_key' => $save_label,
        );

        if ($save_label == 'Create' || $save_label == 'Add') {
            $save['name'] = 'add';
        } elseif ($save_label == 'Modify') {
            $save['name'] = 'edit';
        }

        $cancel = array(
            'name' => 'cancel',
            'label_key' => 'Cancel',
        );

        !empty($save_label) ? $buttons[] = $save : '';

        if (!empty($morebuttons)) {
            $buttons[] = $morebuttons;
        }

        //!empty($cancel_label) ? $buttons[] = $cancel : '';

        $retstring = $withoutdiv ? '' : '<div class="center">';

        foreach ($buttons as $button) {
            $addclass = empty($button['addclass']) ? '' : $button['addclass'];
            $retstring .= '<input type="submit" class="button button-' . $button['name'] . ($morecss ? ' ' . $morecss : '') . ' ' . $addclass . '" name="' . $button['name'] . '" value="' . dol_escape_htmltag($langs->trans($button['label_key'])) . '">';
        }

        if ($cancel_label == "Cancel") {
            $retstring .= '<a class="button button-' . $cancel['name'] . '" href="' . dol_buildpath($cancelURL, 1) . /*'?restore_lastsearch_values=1' .*/ (!empty($socid) ? '&socid=' . $socid : '') . '">' . $langs->trans($cancel['label_key']) . '</a>';
        }

        $retstring .= $withoutdiv ? '' : '</div>';

        if ($dol_openinpopup) {
            $retstring .= '<!-- buttons are shown into a $dol_openinpopup=' . $dol_openinpopup . ' context, so we enable the close of dialog on cancel -->' . "\n";
            $retstring .= '<script>';
            $retstring .= 'jQuery(".button-cancel").click(function(e) {
				e.preventDefault(); console.log(\'We click on cancel in iframe popup ' . $dol_openinpopup . '\');
				window.parent.jQuery(\'#idfordialog' . $dol_openinpopup . '\').dialog(\'close\');
				 });';
            $retstring .= '</script>';
        }

        return $retstring;
    }

    function getAllSales($userObj)
    {
        $salesUsers = [];
        $sql = "SELECT u.rowid, u.lastname, u.firstname, u.login, ue.nickname FROM " . MAIN_DB_PREFIX . "user AS u LEFT JOIN " . MAIN_DB_PREFIX . "user_extrafields AS ue ON u.rowid = ue.fk_object WHERE ue.user_category = '1' ORDER BY u.lastname";
        $result = $this->db->query($sql);
        while ($row = pg_fetch_assoc($result)) {
            $salesUsers[] = $row;
        }
        return $salesUsers;
    }

    function getAllSalesWithSQL($sql)
    {
        $salesUsers = [];
        $result = $this->db->query($sql);
        if($this->db->num_rows($result) > 0){
            while ($row = pg_fetch_assoc($result)) {
                $salesUsers[] = $row;
            }
        }
        return $salesUsers;
    }

    private function getAllPaymenttype($user_id, $db, $payment_type)
    {
        $sql = "SELECT 'HUF' AS type,
        SUM(amount) AS amount
        FROM " . MAIN_DB_PREFIX . "financialreport_userinvoice
        WHERE payment_type = {$payment_type} AND user_id = {$user_id} AND currency = 0";
        if ($payment_type != 1) {
            $sql .= " AND payed = 1";
        }
        /* $sql .= "UNION ALL
        SELECT 'EUR' AS type,
        SUM(amount) AS amount
        FROM " . MAIN_DB_PREFIX . "financialreport_userinvoice
        WHERE payment_type = {$payment_type} AND user_id = {$user_id} AND currency = 1"; */
        if ($payment_type != 1) {
            $sql .= " AND payed = 1";
        }
        $resultUserdata = $db->query($sql);
        return pg_fetch_all($resultUserdata);
    }

    function getAllCommissions($user_id, $db)
    {
        $sql = "SELECT 'HUF' AS type,
        SUM(amount) AS amount
        FROM " . MAIN_DB_PREFIX . "financialreport_userinvoice
        WHERE payment_type = 2 AND user_id = {$user_id} AND currency = 0
        UNION ALL
        SELECT 'EUR' AS type,
        SUM(amount) AS amount
        FROM " . MAIN_DB_PREFIX . "financialreport_userinvoice
        WHERE payment_type = 2 AND user_id = {$user_id} AND currency = 1";
        $resultUserdata = $db->query($sql);
        return pg_fetch_all($resultUserdata);
    }

    function getAllUnpaidCommissions($user_id, $db)
    {
        $sql = "SELECT 'HUF' AS type,
        SUM(amount) AS amount
        FROM " . MAIN_DB_PREFIX . "financialreport_userinvoice
        WHERE payment_type = 2 AND user_id = {$user_id} AND currency = 0 AND payed=0
        UNION ALL
        SELECT 'EUR' AS type,
        SUM(amount) AS amount
        FROM " . MAIN_DB_PREFIX . "financialreport_userinvoice
        WHERE payment_type = 2 AND user_id = {$user_id} AND currency = 1 AND payed=0";
        $resultUserdata = $db->query($sql);
        return pg_fetch_all($resultUserdata);
    }

    function getAllMinusInvoice($user_id, $db)
    {
        $sql = "SELECT 'HUF' AS type,
        SUM(amount) AS amount
        FROM " . MAIN_DB_PREFIX . "financialreport_userinvoice
        WHERE user_id = {$user_id} AND currency = 0 AND payed=0
        UNION ALL
        SELECT 'EUR' AS type,
        SUM(amount) AS amount
        FROM " . MAIN_DB_PREFIX . "financialreport_userinvoice
        WHERE user_id = {$user_id} AND currency = 1 AND payed=0";
        $resultUserdata = $db->query($sql);
        return pg_fetch_all($resultUserdata);
    }

    function createArray($daterange)
    {
        $dates = explode(" - ", $daterange);
        $startDate = new DateTime($dates[0]);
        $endDate = new DateTime($dates[1]);

        $dateArray = array();

        $currentDate = clone $startDate;
        while ($currentDate <= $endDate) {
            $dateArray[] = $currentDate->format('Y-m-d');
            $currentDate->modify('+1 day');
        }

        return $dateArray;
    }

    function getMonths()
    {
        global $langs;
        if ($langs->shortlang == 'hu') {
            return array(
                "01" => "Január",
                "02" => "Február",
                "03" => "Március",
                "04" => "Április",
                "05" => "Május",
                "06" => "Június",
                "07" => "Július",
                "08" => "Augusztus",
                "09" => "Szeptember",
                "10" => "Október",
                "11" => "November",
                "12" => "December"
            );
        } else {
            return array(
                "01" => "January",
                "02" => "February",
                "03" => "March",
                "04" => "April",
                "05" => "May",
                "06" => "June",
                "07" => "July",
                "08" => "August",
                "09" => "September",
                "10" => "Octóber",
                "11" => "November",
                "12" => "December"
            );
        }
    }

    function selectedMonths($year, $months, $honapok)
    {
        $selectedmonths = $year . ' - ';
        $count = count($months);
        $i = 1;
        foreach ($months as $month) {
            $selectedmonths .= $honapok[$month];
            if ($i < $count)
                $selectedmonths .= ', ';
            $i++;
        }
        return $selectedmonths;
    }

    private function getDateIntervalForExcel($year, $months)
    {
        $intervalArray = [];
        $from = $year . '-' . $months[0];
        $to = $year . '-' . end($months);
    }

    private function createStartDateForQuery($year, $month)
    {
        return $year . '-' . $month . '-' . '01 00:00:00';
    }

    private function createEndDateForQuery($year, $month)
    {
        $lastDayOfTheMonth = $this->getLastDayOfTheMonth($year, $month);
        return $year . '-' . $month . '-' . $lastDayOfTheMonth . ' 23:59:59';
    }

    private function getLastDayOfTheMonth($year, $month)
    {
        $lastDay = date("t", strtotime("$year-$month-01"));
        return $lastDay;
    }


    function createExcel($year, $months, $honapok)
    {
        $spreadsheet = new Spreadsheet();

        $felirat = $this->selectedMonths($year, $months, $honapok);
        $start_date = $this->createStartDateForQuery($year, min($months));
        $end_date = $this->createEndDateForQuery($year, max($months));
        //$dateInterval = $this->getDateIntervalForExcel($year, $months);
        $sql = "SELECT CONCAT(u.lastname, ' ', u.firstname) AS name, u.login, ue.tax_identification_number,
            COALESCE(login_count.record_count, 0) AS ledolgozott_napok_szama,
            COALESCE(payment_count_huf.total_amount_huf, 0) AS kifizetett_osszeg_huf
            /*COALESCE(payment_count_eur.total_amount_eur, 0) AS kifizetett_osszeg_eur*/
                FROM " . MAIN_DB_PREFIX . "user AS u
            INNER JOIN " . MAIN_DB_PREFIX . "user_extrafields as ue ON ue.fk_object = u.rowid
            LEFT JOIN (
                SELECT user_id, COUNT(DISTINCT DATE(date_creation)) AS record_count
                FROM " . MAIN_DB_PREFIX . "bbus_userloginnaplo
                WHERE date_creation BETWEEN '{$start_date}' AND '{$end_date}'
                GROUP By user_id
            ) AS login_count ON u.rowid = login_count.user_id
            LEFT JOIN (
                SELECT user_id, SUM(amount) AS total_amount_huf
                FROM " . MAIN_DB_PREFIX . "financialreport_userinvoice
                WHERE payment_type = 2 AND payed = 1 AND currency = 0 AND paymentdate BETWEEN '{$start_date}' AND '{$end_date}'
                GROUP BY user_id
            ) AS payment_count_huf ON u.rowid = payment_count_huf.user_id
            /*LEFT JOIN (
                SELECT user_id, SUM(amount) AS total_amount_eur
                FROM " . MAIN_DB_PREFIX . "financialreport_userinvoice
                WHERE payment_type = 2 AND payed = 1 AND currency = 1 AND paymentdate BETWEEN '{$start_date}' AND '{$end_date}'
                GROUP BY user_id
            ) AS payment_count_eur ON u.rowid = payment_count_eur.user_id*/
            WHERE ue.user_category = '1'
            ORDER BY name ASC;
            ";
        $resultUserdata = $this->db->query($sql);
        $allUsers = pg_fetch_all($resultUserdata);

        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle("{$felirat}");
        $sheet->setCellValue("A1", "Felhasználó");
        $sheet->setCellValue("B1", "Login");
        $sheet->setCellValue("C1", "Adóazonosító jel");
        $sheet->setCellValue("D1", "Ledolgozott napok száma");
        $sheet->setCellValue("E1", "Kifizetett jutalék HUF");
        /*$sheet->setCellValue("F1", "Kifizetett jutalék EUR");*/

        $i = 2;
        foreach ($allUsers as $user) {
            $sheet->setCellValue("A" . $i, $user['name']);
            $sheet->setCellValue("B" . $i, $user['login']);
            $sheet->setCellValue("C" . $i, $user['tax_identification_number']);
            $sheet->setCellValue("D" . $i, $user['ledolgozott_napok_szama']);
            $sheet->setCellValue("E" . $i, $user['kifizetett_osszeg_huf'] . ' HUF');
            /*$sheet->setCellValue("F" . $i, $user['kifizetett_osszeg_eur'] . ' EUR');*/
            $i++;
        }
        //$sheet->setCellValue("A1", "Hello World!");
        //$sheet->setCellValue("A1", "Hello World!");
        //$sheet->setCellValue("A2", "Goodbye World!");

        // (D) ADD NEW WORKSHEET + YOU CAN ALSO USE FORMULAS!
        /* $spreadsheet->createSheet();
        $sheet = $spreadsheet->getSheet(1);
        $sheet->setTitle("Formula");
        $sheet->setCellValue("A1", "5");
        $sheet->setCellValue("A2", "6");
        $sheet->setCellValue("A3", "=SUM(A1:A2)"); */

        $date = date('d-m-y-' . substr((string) microtime(), 1, 8));
        $date = str_replace(".", "", $date);
        $filename = "export_" . $date . ".xlsx";

        try {
            $writer = new Xlsx($spreadsheet);
            $writer->save($filename);
            $content = file_get_contents($filename);
        } catch (Exception $e) {
            exit($e->getMessage());
        }

        header("Content-Disposition: attachment; filename=" . $filename);
        unlink($filename);
        exit($content);
    }

    function createExcelInterval($daterange)
    {
        $spreadsheet = new Spreadsheet();

        $felirat = $daterange;
        $rangeArray = explode(' - ', $daterange);
        $start_date = $rangeArray[0] . ' 00:00:00';
        $end_date = $rangeArray[1] . ' 23:59:59';
        //$dateInterval = $this->getDateIntervalForExcel($year, $months);
        $sql = "SELECT CONCAT(u.lastname, ' ', u.firstname) AS name, u.login, ue.tax_identification_number,
            COALESCE(login_count.record_count, 0) AS ledolgozott_napok_szama,
            COALESCE(payment_count_huf.total_amount_huf, 0) AS kifizetett_osszeg_huf
            /*COALESCE(payment_count_eur.total_amount_eur, 0) AS kifizetett_osszeg_eur*/
                FROM " . MAIN_DB_PREFIX . "user AS u
            INNER JOIN " . MAIN_DB_PREFIX . "user_extrafields as ue ON ue.fk_object = u.rowid
            LEFT JOIN (
                SELECT user_id, COUNT(DISTINCT DATE(date_creation)) AS record_count
                FROM " . MAIN_DB_PREFIX . "bbus_userloginnaplo
                WHERE date_creation BETWEEN '{$start_date}' AND '{$end_date}'
                GROUP By user_id
            ) AS login_count ON u.rowid = login_count.user_id
            LEFT JOIN (
                SELECT user_id, SUM(amount) AS total_amount_huf
                FROM " . MAIN_DB_PREFIX . "financialreport_userinvoice
                WHERE payment_type = 2 AND payed = 1 AND currency = 0 AND paymentdate BETWEEN '{$start_date}' AND '{$end_date}'
                GROUP BY user_id
            ) AS payment_count_huf ON u.rowid = payment_count_huf.user_id
            /*LEFT JOIN (
                SELECT user_id, SUM(amount) AS total_amount_eur
                FROM " . MAIN_DB_PREFIX . "financialreport_userinvoice
                WHERE payment_type = 2 AND payed = 1 AND currency = 1 AND paymentdate BETWEEN '{$start_date}' AND '{$end_date}'
                GROUP BY user_id
            ) AS payment_count_eur ON u.rowid = payment_count_eur.user_id*/
            WHERE ue.user_category = '1'
            ORDER BY name ASC;
            ";
        $resultUserdata = $this->db->query($sql);
        $allUsers = pg_fetch_all($resultUserdata);

        $sheet = $spreadsheet->getActiveSheet();
        $sheet->setTitle("{$felirat}");
        $sheet->setCellValue("A1", "Felhasználó");
        $sheet->setCellValue("B1", "Login");
        $sheet->setCellValue("C1", "Adóazonosító jel");
        $sheet->setCellValue("D1", "Ledolgozott napok száma");
        $sheet->setCellValue("E1", "Kifizetett jutalék HUF");
        //$sheet->setCellValue("F1", "Kifizetett jutalék EUR");

        $i = 2;
        foreach ($allUsers as $user) {
            $sheet->setCellValue("A" . $i, $user['name']);
            $sheet->setCellValue("B" . $i, $user['login']);
            $sheet->setCellValue("C" . $i, $user['tax_identification_number']);
            $sheet->setCellValue("D" . $i, $user['ledolgozott_napok_szama']);
            $sheet->setCellValue("E" . $i, $user['kifizetett_osszeg_huf'] . ' HUF');
            //  $sheet->setCellValue("F" . $i, $user['kifizetett_osszeg_eur'] . ' EUR');
            $i++;
        }
        //$sheet->setCellValue("A1", "Hello World!");
        //$sheet->setCellValue("A1", "Hello World!");
        //$sheet->setCellValue("A2", "Goodbye World!");

        // (D) ADD NEW WORKSHEET + YOU CAN ALSO USE FORMULAS!
        /* $spreadsheet->createSheet();
        $sheet = $spreadsheet->getSheet(1);
        $sheet->setTitle("Formula");
        $sheet->setCellValue("A1", "5");
        $sheet->setCellValue("A2", "6");
        $sheet->setCellValue("A3", "=SUM(A1:A2)"); */

        $date = date('d-m-y-' . substr((string) microtime(), 1, 8));
        $date = str_replace(".", "", $date);
        $filename = "export_" . $date . ".xlsx";

        try {
            $writer = new Xlsx($spreadsheet);
            $writer->save($filename);
            $content = file_get_contents($filename);
        } catch (Exception $e) {
            exit($e->getMessage());
        }

        header("Content-Disposition: attachment; filename=" . $filename);
        unlink($filename);
        exit($content);
    }

    function createExcelHotelReport($daterange, $id, $entity, $groups)
    {
        $spreadsheet = new Spreadsheet();
        $usersList = $this->getUsersOfHotelGroup($id, $daterange, $entity);
        $felirat = $daterange;
        $usersIdList = $this->getUsersIdList($id, $daterange, $entity);
        $amountHUF = $this->getAllAmountInPeriod($id, $daterange, 'HUF', $usersIdList, $entity);
        $amountEUR = $this->getAllAmountInPeriod($id, $daterange, 'EUR', $usersIdList, $entity);
        $commissionHUF = $this->getAllCommissionInPeriod($id, $daterange, 'HUF', $usersIdList, $entity);
        $commissionEUR = $this->getAllCommissionInPeriod($id, $daterange, 'EUR', $usersIdList, $entity);
        $hotelsArray = $this->getAllHotels();
        $hotelName = $hotelsArray[$groups[$id]['fk_hotel_id']];

        $style = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, // Vízszintes középre igazítás
                'vertical' => Alignment::VERTICAL_CENTER,     // Függőleges középre igazítás
            ],
        ];

        $styleBold = [
            'font' => [
                'bold' => true,
            ],
        ];

        $sheet = $spreadsheet->getActiveSheet();
        $sheet->getStyle('A1')->applyFromArray($styleBold);
        $sheet->getStyle('A3:B3')->applyFromArray($styleBold);
        $sheet->getStyle('A7')->applyFromArray($styleBold);
        $sheet->getStyle('A9:D9')->applyFromArray($styleBold);
        $sheet->getStyle('A1:D9')->applyFromArray($style);
        $sheet->setTitle("{$felirat}");
        $sheet->getColumnDimension('A')->setWidth(25);
        $sheet->getColumnDimension('B')->setWidth(25);
        $sheet->getColumnDimension('C')->setWidth(25);
        $sheet->getColumnDimension('D')->setWidth(25);
        $sheet->setCellValue("A1", strtoupper($hotelName));
        $sheet->mergeCells("A1:B1");
        $sheet->setCellValue("A2", $daterange);
        $sheet->mergeCells("A2:B2");
        $sheet->setCellValue("A3", "INCOMES");
        $sheet->setCellValue("B3", "COMMISSIONS");
        $sheet->setCellValue("A4", $this->correctNumber($amountHUF) . ' HUF');
        $sheet->setCellValue("B4", $this->correctNumber($commissionHUF) . ' HUF');
        $sheet->setCellValue("A5", $this->correctNumber($amountEUR) . ' EUR');
        $sheet->setCellValue("B5", $this->correctNumber($commissionEUR) . ' EUR');
        $sheet->setCellValue("A6", "");
        $sheet->setCellValue("B6", "");

        $sheet->setCellValue("A7", strtoupper($hotelName));
        $sheet->mergeCells("A7:D7");
        $sheet->setCellValue("A8", $daterange);
        $sheet->mergeCells("A8:D8");
        $sheet->setCellValue("A9", "USER");
        $sheet->setCellValue("B9", "GROUP");
        $sheet->setCellValue("C9", "AMOUNT OF SALES");
        $sheet->setCellValue("D9", "AMOUNT OF SALES");

        $i = 10;
        foreach ($usersList as $userVal) {
            $sheet->setCellValue("A" . $i, $userVal['firstname'] . ' ' . $userVal['lastname']);
            $sheet->setCellValue("B" . $i, ' (' . $this->userCategoryArray[$userVal['user_category']] . ')');
            $sheet->setCellValue("C" . $i, $this->correctNumber($this->getAllAmountInPeriod($id, $daterange, 'HUF', $userVal['rowid'], $entity)) . ' HUF');
            $sheet->setCellValue("D" . $i, $this->correctNumber($this->getAllAmountInPeriod($id, $daterange, 'EUR', $userVal['rowid'], $entity)) . ' EUR');
            $i++;
        }
        $sheet->getStyle('C10:C' . $i)->applyFromArray($style);
        $sheet->getStyle('D10:D' . $i)->applyFromArray($style);
        //$sheet->setCellValue("A1", "Hello World!");
        //$sheet->setCellValue("A1", "Hello World!");
        //$sheet->setCellValue("A2", "Goodbye World!");

        // (D) ADD NEW WORKSHEET + YOU CAN ALSO USE FORMULAS!
        /* $spreadsheet->createSheet();
        $sheet = $spreadsheet->getSheet(1);
        $sheet->setTitle("Formula");
        $sheet->setCellValue("A1", "5");
        $sheet->setCellValue("A2", "6");
        $sheet->setCellValue("A3", "=SUM(A1:A2)"); */

        $date = date('d-m-y-' . substr((string) microtime(), 1, 8));
        $date = str_replace(".", "", $date);
        $filename = "hotelexport_" . $hotelName . '_' . $date . ".xlsx";

        try {
            $writer = new Xlsx($spreadsheet);
            $writer->save($filename);
            $content = file_get_contents($filename);
        } catch (Exception $e) {
            exit($e->getMessage());
        }

        header("Content-Disposition: attachment; filename=" . $filename);
        unlink($filename);
        exit($content);
    }

    function createExcelMonthlyExport($daterange, $entity, $saledTickets)
    {
        $spreadsheet = new Spreadsheet();
        $felirat = $daterange;
        $entities = $this->getAllEntities();

        $entityName = str_replace(" ", "_", $entities[$entity]);
        $entityName = str_replace(".", "_", $entityName);

        $style = [
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER, // Vízszintes középre igazítás
                'vertical' => Alignment::VERTICAL_CENTER,     // Függőleges középre igazítás
            ],
        ];

        $styleBold = [
            'font' => [
                'bold' => true,
            ],
        ];

        $sheet = $spreadsheet->getActiveSheet();
        $sheet->getStyle('A1')->applyFromArray($styleBold);
        $sheet->getStyle('A3:P3')->applyFromArray($styleBold);
        //$sheet->getStyle('A7')->applyFromArray($styleBold);
        //$sheet->getStyle('A9:D9')->applyFromArray($styleBold);
        //$sheet->getStyle('A1:D9')->applyFromArray($style);
        $sheet->setTitle("{$felirat}");
        $sheet->getColumnDimension('A')->setWidth(20);
        $sheet->getColumnDimension('B')->setWidth(20);
        $sheet->getColumnDimension('C')->setWidth(20);
        $sheet->getColumnDimension('D')->setWidth(20);
        $sheet->getColumnDimension('E')->setWidth(20);
        $sheet->getColumnDimension('F')->setWidth(20);
        $sheet->getColumnDimension('G')->setWidth(15);
        $sheet->getColumnDimension('H')->setWidth(15);
        $sheet->getColumnDimension('I')->setWidth(15);
        $sheet->getColumnDimension('J')->setWidth(15);
        $sheet->getColumnDimension('K')->setWidth(15);
        $sheet->getColumnDimension('L')->setWidth(15);
        $sheet->getColumnDimension('M')->setWidth(10);
        $sheet->getColumnDimension('N')->setWidth(15);
        $sheet->getColumnDimension('O')->setWidth(10);
        $sheet->getColumnDimension('P')->setWidth(10);
        $sheet->setCellValue("A1", strtoupper($entities[$entity]));
        $sheet->mergeCells("A1:B1");
        $sheet->setCellValue("A2", $daterange);
        $sheet->mergeCells("A2:B2");
        $sheet->setCellValue("A3", "Facture");
        $sheet->setCellValue("B3", "Type");
        $sheet->setCellValue("C3", "Environment");
        $sheet->setCellValue("D3", "VATnumber");
        $sheet->setCellValue("E3", "Creation date");
        $sheet->setCellValue("F3", "Completion date");
        $sheet->setCellValue("G3", "Payment deadline");
        $sheet->setCellValue("H3", "Net");
        $sheet->setCellValue("I3", "VAT");
        $sheet->setCellValue("J3", "Gross");
        $sheet->setCellValue("K3", "Net");
        $sheet->setCellValue("L3", "VAT");
        $sheet->setCellValue("M3", "Gross");
        $sheet->setCellValue("N3", "Currency");
        $sheet->setCellValue("O3", "PaymentMethod");
        $sheet->setCellValue("P3", "VAT  %");
        $i = 4;

        foreach ($saledTickets as $item) {
            $mtht = $item->multicurrency_total_ht == $item->total_ht ? '' : number_format($item->multicurrency_total_ht, 4, ',', ' ');
            $mttva = $item->multicurrency_total_tva == $item->total_tva ? '' : number_format($item->multicurrency_total_ht, 4, ',', ' ');
            $mtttc = $item->multicurrency_total_ttc == $item->total_ttc ? '' : number_format($item->multicurrency_total_ttc, 4, ',', ' ');
            $sheet->setCellValue("A" . $i, $item->ref);
            $sheet->setCellValue("B" . $i, $this->getFectureType($item->type));
            $sheet->setCellValue("C" . $i, $item->label);
            $sheet->setCellValue("D" . $i, $item->value);
            $sheet->setCellValue("E" . $i, $item->datef);
            $sheet->setCellValue("F" . $i, $item->date_lim_reglement);
            $sheet->setCellValue("G" . $i, $item->datef);
            $sheet->setCellValue("H" . $i, number_format($item->total_ht, 4, ',', ' '));
            $sheet->setCellValue("I" . $i, number_format($item->total_tva, 4, ',', ' '));
            $sheet->setCellValue("J" . $i, number_format($item->total_ttc, 4, ',', ' '));
            $sheet->setCellValue("K" . $i, $mtht);
            $sheet->setCellValue("L" . $i, $mttva);
            $sheet->setCellValue("M" . $i, $mtttc);
            $sheet->setCellValue("N" . $i, $item->multicurrency_code);
            $sheet->setCellValue("O" . $i, $item->libelle);
            $sheet->setCellValue("P" . $i, number_format($item->tva_tx, 0) . ' %');
            $i++;
        }

        ## file name
        $date = date('d-m-y-' . substr((string) microtime(), 1, 8));
        $date = str_replace(".", "", $date);
        $filename = "monthlyexport_" . $entityName . '_' . $date . ".xlsx";

        try {
            $writer = new Xlsx($spreadsheet);
            $writer->save($filename);
            $content = file_get_contents($filename);
        } catch (Exception $e) {
            exit($e->getMessage());
        }

        header("Content-Disposition: attachment; filename=" . $filename);
        unlink($filename);
        exit($content);
    }

    function getTypeOfPayment($payment_type)
    {
        switch ($payment_type) {
            case '0':
                return 'prepay';
            case '1':
                return 'withhold';
            case '2':
                return 'commission';
            default:
                return 'prepay';
        }
    }

    function getFectureType($type)
    {
        global $langs;
        switch ($type) {
            case '0':
                return $langs->trans("Invoice");
            case '2':
                return $langs->trans("CreditNote");
            case '7':
                return $langs->trans("Receipt");
            case '8':
                return $langs->trans("ReceiptCreditNote");
        }
    }

    function getTitle($payment_type)
    {
        global $langs;
        switch ($payment_type) {
            case '0':
                return $langs->trans("UserInvoices");
            case '1':
                return $langs->trans("Withhold");
            case '2':
                return $langs->trans("Commissionpaidamount");
            default:
                return $langs->trans("UserInvoices");
        }
    }

    function getViewTplTitle($payment_type)
    {
        global $langs;
        switch ($payment_type) {
            case '0':
                return $langs->trans("Deposit");
            case '1':
                return $langs->trans("WithheldAmount");
            case '2':
                return $langs->trans("Commissionpaidamount");
            default:
                return $langs->trans("Deposit");
        }
    }

    function backPageSelector($payment_type)
    {
        switch ($payment_type) {
            case '0':
                return 'prepaymentsindex';
            case '1':
                return 'withholdingindex';
            case '2':
                return 'commissionindex';
            default:
                return 'prepaymentsindex';
        }
    }

    function createHTML($id)
    {
        $currencies = ['0' => 'HUF', '1' => 'EUR'];

        $sql = "SELECT user1.lastname as creatorlastname, user1.firstname as creatorfirstname, user1.login as creatorlogin, user2.lastname as lastname, user2.firstname as firstname, user2.login as userlogin, ui.amount, ui.currency, ui.paymentdate FROM " . MAIN_DB_PREFIX . "financialreport_userinvoice as ui
        INNER JOIN " . MAIN_DB_PREFIX . "user AS user1 ON user1.rowid = ui.fk_user_creat
        INNER JOIN " . MAIN_DB_PREFIX . "user AS user2 ON user2.rowid = ui.user_id
        WHERE ui.rowid={$id}";
        //print $sql;

        $resultUserdata = $this->db->query($sql);
        $UserInvoice = pg_fetch_assoc($resultUserdata);

        $result = '<table style="width:100%"><tr><td><b>Átadó adatai <br>Donor / Giver user data</b></td><td style="text-align: right;"><b>Átvevő adatai <br>Receiving user data</b></td></tr>';
        $result .= '<div>&nbsp;</div>';
        $result .= '<tr><td>' . $UserInvoice['creatorlastname'] . ' ' . $UserInvoice['creatorfirstname'] . ' - ' . $UserInvoice['creatorlogin'] . '</td><td  style="text-align: right;">' . $UserInvoice['lastname'] . ' ' . $UserInvoice['firstname'] . ' - ' . $UserInvoice['userlogin'] . '</td></tr></table>';
        $result .= '<div style="padding-top: 100px;">&nbsp;</div>';
        $result .= '<table style="width:100%">';
        $result .= '<tr><td style="width:75%">Jutalék összege / Amount of commission: </td><td style="text-align: right; width:20%;">' . number_format($UserInvoice['amount'], 0, '.', ' ') . '</td></tr>';
        $result .= '<tr><td style="width:75%">Jutalék pénznem / Amount of currency: </td><td style="text-align: right; width:20%;">' . $currencies[$UserInvoice['currency']] . '</td></tr>';
        $result .= '</table>';
        $result .= '<div style="padding-top: 100px;">&nbsp;</div>';
        $result .= '<div style="width:100%; text-align: left;">Budapest, Bajcsy-Zsilinszky út 17, 1065</div>';
        $result .= '<div style="width:100%; text-align: left;">' . $UserInvoice['paymentdate'] . '</div>';
        $result .= '<div style="padding-top: 100px;">&nbsp;</div>';
        $result .= '<table style="width:100%; text-align:right;">';
        $result .= '</table>';
        $result .=
            '<table style="width:100%">
            <tr>
                <td>_________________________________</td>
                <td>_________________________________</td>
            </tr>
            <tr>
                <td style="text-align: center;">Átadó aláírása <br> (Donor / Giver)</td>
                <td style="text-align: center;">Átvevő aláírása <br>  (Recipient / Receiver)</td>
            </tr>
        </table>';
        return $result;
    }

    function createHTMLForSettlement($dataArray)
    {
        $result = '
        <table style="width:100%; text-align: center;">
            <tr style="font-weight: bold;">
                <td>Jutalék / Commisson</td>
            </tr>
            <tr><td>&nbsp;</td></tr>
            <tr style="background-color:lightgrey; font-weight: bold;">
                <td>Name of group</td>
            </tr>
            <tr>
                <td>' . $dataArray['GroupName'] . '</td>
            </tr>
            <tr><td>&nbsp;</td></tr>
            <tr style="background-color:lightgrey; font-weight: bold;">
                <td>Interval</td>
            </tr>
            <tr>
                <td>' . $dataArray['Interval'] . '</td>
            </tr>
            <tr><td>&nbsp;</td></tr>
            <tr style="background-color:lightgrey; font-weight: bold;">
                <td>Total amount</td>
            </tr>
            <tr>
                <td>' . $dataArray['FullAmount'] . ' HUF</td>
            </tr>
            <tr><td>&nbsp;</td></tr>

            <tr style="background-color:lightgrey; font-weight: bold;">
                <td>Data of Users</td>
            </tr>
        </table>';
        $result .= '
        <table>';
        foreach ($dataArray['UsersData'] as $userdata) {
            $result .= '<tr><td style="font-weight: bold;">User: </td><td style="font-style: italic;">' . $userdata['namedata'] . '</td></tr>';
            $result .= '<tr><td style="font-weight: bold;">Amount: </td><td style="font-style: italic;">' . $userdata['amount'] . ' HUF</td></tr>';
            //$result .= '<tr><td style="font-weight: bold;">Invoices: </td><td style="font-style: italic;">' . $userdata['invoices'] . '</td></tr>';
            $result .= '<tr><td>&nbsp;</td><td>&nbsp;</td></tr>';
        }
        $result .= '</table>';
        return $result;
    }

    function checkinterval($db)
    {

        $id = GETPOST('id', 'int');
        $interval_startyear = GETPOST('interval_startyear', 'int');
        $interval_startmonth = GETPOST('interval_startmonth', 'int');
        $interval_startday = GETPOST('interval_startday', 'int');
        $interval_starthour = GETPOST('interval_starthour', 'int');
        $interval_startmin = GETPOST('interval_startmin', 'int');
        $interval_startsec = GETPOST('interval_startsec', 'int');
        $interval_endyear = GETPOST('interval_endyear', 'int');
        $interval_endmonth = GETPOST('interval_endmonth', 'int');
        $interval_endday = GETPOST('interval_endday', 'int');
        $interval_endhour = GETPOST('interval_endhour', 'int');
        $interval_endmin = GETPOST('interval_endmin', 'int');

        $startdate = $interval_startyear . '-' . $interval_startmonth . '-' . $interval_startday . ' ' . $interval_starthour . ':' . $interval_startmin . ':' . $interval_startsec;
        $enddate = $interval_endyear . '-' . $interval_endmonth . '-' . $interval_endday . ' ' . $interval_endhour . ':' . $interval_endmin . ':59';

        if ($id) {
            $sql = "SELECT *
                FROM " . MAIN_DB_PREFIX . "financialreport_commissioninterval
                WHERE rowid <> {$id} 
                AND '{$startdate}' BETWEEN interval_start AND interval_end
                OR rowid <> {$id} AND  '{$enddate}' BETWEEN interval_start AND interval_end
            ";
            $result = $db->query($sql);
            return $db->num_rows($result) != 0;
        } else {
            $sql = "SELECT *
                FROM " . MAIN_DB_PREFIX . "financialreport_commissioninterval
                WHERE  
                '{$startdate}' BETWEEN interval_start AND interval_end
                OR  '{$enddate}' BETWEEN interval_start AND interval_end
            ";
            $result = $db->query($sql);
            if ($result) {
                return $db->num_rows($result) != 0;
            } else {
                return false;
            }
        }
    }

    public function getEntities($entitiesArrayIsEmpty = false, $entities, $entitiesArray)
    {
        if ($entitiesArrayIsEmpty) {
            $array = [];
            foreach ($entities as $key => $value) {
                $array[] = $key;
            }
            $selectedEntities = implode(', ', $array);
        } else {
            $array = [];
            foreach ($entitiesArray as $entity) {
                if ($entity)
                    $array[] = $entity;
            }
            $selectedEntities = implode(', ', $array);
        }
        return $selectedEntities;
    }

    function getAllGroupsFromGroupClass($selectedEntities)
    {
        global $db;
        $groups = [];
        $sql = "SELECT rowid, ref FROM " . MAIN_DB_PREFIX . "settlements_group WHERE hotelgroup IS NULL ";
        if ($selectedEntities != '') {
            $sql .= " AND fk_entity IN ({$selectedEntities})";
        }
        $sql .= "ORDER BY ref ASC";
        $data = $db->query($sql);
        if (!$data) {
            dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR);
            setEventMessage('A csoport tábla lekérdezése sikertelen volt.', 'errors');
            return $groups;
        }
        while ($row = pg_fetch_assoc($data)) {
            $groups[$row['rowid']] = $row['ref'];
        }
        return $groups;
    }

    function getAllHotelGroupsFromGroupClass($db)
    {
        $groups = [];
        $sql = "SELECT g.rowid, g.ref, g.fk_entity, gh.fk_hotel_id
        FROM " . MAIN_DB_PREFIX . "settlements_group AS g
        INNER JOIN " . MAIN_DB_PREFIX . "settlements_grouphotels as gh ON gh.fk_group_id = g.rowid 
        WHERE g.hotelgroup IS NOT NULL ORDER BY ref ASC";
        //$sql = "SELECT rowid, ref, fk_entity FROM " . MAIN_DB_PREFIX . "settlements_group WHERE hotelgroup IS NOT NULL ORDER BY ref ASC";
        //print $sql;exit;
        $data = $db->query($sql);
        if (!$data) {
            dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR);
            setEventMessage('A csoport tábla lekérdezése sikertelen volt.', 'errors');
            return $groups;
        }
        while ($row = pg_fetch_assoc($data)) {
            $groups[$row['rowid']] = ["ref" => $row['ref'], "entity" => $row['fk_entity'], "fk_hotel_id" => $row['fk_hotel_id']];
        }
        return $groups;
    }

    function getGroupHotels()
    {
        global $db;
        $groupHotels = [];
        $sql = "SELECT gh.fk_group_id, gh.fk_hotel_id, g.ref as groulabel, h.label as hotellabel FROM " . MAIN_DB_PREFIX . "settlements_grouphotels AS gh
        INNER JOIN " . MAIN_DB_PREFIX . "settlements_group AS g ON g.rowid = gh.fk_group_id
        INNER JOIN " . MAIN_DB_PREFIX . "settlements_hotel AS h ON h.rowid = gh.fk_hotel_id
        ORDER BY h.label ASC";
        $data = $db->query($sql);
        while ($row = pg_fetch_assoc($data)) {
            $groupHotels[] = $row;
        }
        return $groupHotels;
    }

    function getUsersByIdWithDateCol2($db, $id, $date)
    {
        $usersOfGroup = [];
        $date = date('Y-m-d', strtotime("-20 day", strtotime($date)));
        $dateNow = date('Y-m-d H:i:s', dol_now());
        if (isset($id)) {
            $sql = "SELECT un.user_id, u.firstname as firstname, u.lastname as lastname, u.login as login FROM " . MAIN_DB_PREFIX . "settlements_usernaplo AS un
            INNER JOIN " . MAIN_DB_PREFIX . "user as u ON u.rowid = un.user_id
            WHERE un.group_user_id = {$id} AND un.date_creation BETWEEN '{$date} 00:00:00' AND '{$dateNow}' AND un.status = 1 
            GROUP BY un.user_id, u.firstname, u.lastname, u.login ORDER BY u.lastname";
            $data = $db->query($sql);
            if (!$data) {
                dol_syslog("No data found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR);
                setEventMessage('A csoporthoz rendelt felhasználók naplózási lekérdezésébe valami hiba csúszott.', 'errors');
                return $usersOfGroup;
            }
            while ($row = pg_fetch_assoc($data)) {
                $usersOfGroup[] = $row;
            }

        }
        return $usersOfGroup;
    }

    /**
     * @param  int $id	Group ID
     */
    function getUsersByIdWithDate($db, $id, $date)
    {
        $usersOfGroup = [];
        $date = date('Y-m-d', strtotime("-20 day", strtotime($date)));
        $dateNow = date('Y-m-d H:i:s', dol_now());
        if (isset($id)) {
            $sql = "SELECT un.user_id, u.firstname as firstname, u.lastname as lastname, u.login as login, un.date_creation FROM " . MAIN_DB_PREFIX . "settlements_usernaplo AS un
            INNER JOIN " . MAIN_DB_PREFIX . "user as u ON u.rowid = un.user_id
            WHERE un.group_user_id = {$id} AND un.date_creation BETWEEN '{$date} 00:00:00' AND '{$dateNow}' AND un.status = 1 
            GROUP BY un.user_id, u.firstname, u.lastname, u.login, un.date_creation ORDER BY u.lastname";
            //print $sql.'<br>';
            $data = $db->query($sql);
            if (!$data) {
                dol_syslog("No data found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR);
                setEventMessage('A csoporthoz rendelt felhasználók naplózási lekérdezésébe valami hiba csúszott.', 'errors');
                return $usersOfGroup;
            }
            while ($row = pg_fetch_assoc($data)) {
                $usersOfGroup[] = $row;
            }
        }
        return $usersOfGroup;
    }

    function getDeletedUsers($db, $id, $date)
    {
        $deletedUsersArray = [];
        $date = date('Y-m-d', strtotime("-1 day", strtotime($date)));
        $dateNow = date('Y-m-d H:i:s', dol_now());
        //print $id;
        //$sql = "SELECT user_id, MAX(date_creation) as date FROM " . MAIN_DB_PREFIX . "settlements_usernaplo WHERE group_user_id = {$id} and date_creation BETWEEN '{$date} 00:00:00' AND '{$date} 23:59:59' AND status = 0 GROUP BY user_id ";
        $sql = "SELECT user_id, date_creation as date 
        FROM " . MAIN_DB_PREFIX . "settlements_usernaplo 
        WHERE group_user_id = {$id} 
        AND date_creation BETWEEN '{$date} 00:00:00' AND '{$dateNow}' 
        AND status = 0 ORDER BY date_creation ASC";
        //print $sql;
        //print '<br>';

        $data = $db->query($sql);
        $dataArray = pg_fetch_all($data);
        if (empty($dataArray)) {
            dol_syslog("No data found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR);
            return $deletedUsersArray;
        }
        foreach ($dataArray as $row) {
            $deletedUsersArray[$row['user_id']][] = $row['date'];
        }
        return $deletedUsersArray;
    }

    function getSum($db, $currency, $id, $users, $date = null)
    {
        $sum = [];
        foreach ($users as $user) {
            $usersArray[] = $user['user_id'];
        }
        $usersString = implode(',', $usersArray);
        $sql = "SELECT SUM(total_ht) as total_ht, SUM(total_tva) as total_tva, SUM(total_ttc) as total_ttc 
            FROM " . MAIN_DB_PREFIX . "facture 
            WHERE fk_user_closing IN({$usersString}) 
            AND fk_statut = 2 
            AND multicurrency_code = '{$currency}' 
            AND date_closing BETWEEN '{$date} 00:00:00' AND '{$date} 23:59:59'";
        //print $sql;
        $data = $db->query($sql);

        if (!$data) {
            dol_syslog("No data found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR);
            setEventMessage('A számlák összesítésének számításába valami hiba csúszott.', 'errors');
            return $sum;
        }
        $dataArray = pg_fetch_all($data);
        foreach ($dataArray as $row) {
            $sum[] = $row;
        }
        return $sum;
    }

    function correctNumber($number)
    {
        $integerPart = floor($number);
        $formattedNumber = number_format($integerPart, 0, '.', ' ');
        return $formattedNumber;
    }

    function getFullBalance($user_id, $db)
    {
        $sql = "SELECT
        currency,
        SUM(CASE WHEN payed = 0 AND payment_type = 2 THEN amount ELSE 0 END) -
        SUM(CASE WHEN payed = 1 AND payment_type = 0 THEN amount ELSE 0 END) -
        SUM(CASE WHEN payment_type = 1 THEN amount ELSE 0 END) AS amount
    FROM " . MAIN_DB_PREFIX . "financialreport_userinvoice
    WHERE user_id = {$user_id}
    GROUP BY currency;";
        //print $sql;exit;
        $resultUserdata = $db->query($sql);
        return pg_fetch_all($resultUserdata);
    }

    private function isWarning($amount)
    {
        return $amount < 0 ? '<font style="color: red;"> <span class="fa fa-exclamation pictofixedwidth"></span> </font>' : '';
    }

    private function getBgColor($amount)
    {
        return $amount < 0 ? 'red' : 'green';
    }

    function tableDrawer($allSalesUsers, $db, $idmenu, $payment_type)
    {
        $rownum = 0;
        foreach ($allSalesUsers as $user) {
            $url = '/custom/financialreport/userinvoice_list.php?user_id=' . $user['rowid'] . '&idmenu=' . $idmenu . '&payment_type=' . $payment_type . '&mainmenu=financialreport&leftmenu=';
            $bgcolor = $this->setRowColor($rownum);
            print '<tr style="height:30px; background-color: ' . $bgcolor . '">
            <td><a style="display: block; width: 100%; height: 100%;" href="' . $url . '">' . $user['lastname'] . ' ' . $user['firstname'] . '</a></td>';
            print '<td style="text-align: center;">' . $user['nickname'] . '</td>';
            print '<td style="text-align: center;">' . $user['login'] . '</td>';
            $this->drawCurrentBalance($user['rowid']);
            $this->drawPayedRows($user['rowid'], $payment_type);
            print '</tr>';
            $rownum++;
        }
    }

    function tableDrawerForBasicCommission($allSalesUsers, $db, $idmenu, $year, $month)
    {
        global $langs;
        $rownum = 0;
        $percentage = 0;
        $commmissionhandler = new CommissionHandler();
        foreach ($allSalesUsers as $user) {
            $amountHUF = $commmissionhandler->getCommisonByUserIdForAMonth($user['rowid'], $year, $month);
            $amount = $amountHUF;
            $userCommissionMinimumAmount = $commmissionhandler->getUserMinimumCommission($user['rowid']);
            $suplement = $userCommissionMinimumAmount - $amount < 0 ? 0 : $userCommissionMinimumAmount - $amount;
            $bgcolor = $this->setRowColor($rownum);
            $percentage = $this->getPercentage($userCommissionMinimumAmount, $amount);
            $amountcolor = $amount < $userCommissionMinimumAmount ? 'red' : 'lightgreen';
            $fontcolor = $percentage < 4 || $amount >= $userCommissionMinimumAmount ? 'black' : 'white';
            $makeUpToValue = $user['rowid'] . '|' . $suplement . '|' . $year . '|' . $month . '|' . $user['lastname'] . ' ' . $user['firstname'];
            $buttondisplay = $suplement > 0 ? 'style="display:block;"' : 'style="display:none;"';
            $makeUpToButton = strtotime($year . '-' . $month) < strtotime(date('Y-m')) ? '<td style="padding-left: 10px; padding-right: 10px;">
            <button ' . $buttondisplay . ' id="confirmmakeupto" name="confirmmakeupto" type="submit" value="' . $makeUpToValue . '">' . $langs->trans('MakeUpTo') . '</button>
            </td>' : '<td>&nbsp;</td>';
            //$url = '/custom/financialreport/userinvoicebasiccommission_list.php?user_id=' . $user['rowid'] . '&idmenu=' . $idmenu . '&mainmenu=financialreport&leftmenu=';
            print '<tr style="height:30px; background-color: ' . $bgcolor . '">';
            print '<td style="width: 15%;"><a href="/custom/financialreport/userinvoice_list.php?user_id=' . $user['rowid'] . '&idmenu=1618&payment_type=2&mainmenu=financialreport&leftmenu=">' . $user['lastname'] . ' ' . $user['firstname'] . '</td>';
            print '<td style="width: 10%; text-align: center;">' . $user['nickname'] . '</td>';
            print '<td style="width: 10%; text-align: center;">' . $this->correctNumber($userCommissionMinimumAmount) . ' HUF</td>';
            print '<td style="width: 10%; text-align: center; color:red;">' . $this->correctNumber($suplement) . ' HUF</td>';
            print '<td style="text-align: center; width:60%;"><div style="display:block; width:' . $percentage . '%; background-color: ' . $amountcolor . '; color:' . $fontcolor . '; white-space: nowrap;"> ' . $this->correctNumber($amount) . ' HUF </div></td>';
            print $makeUpToButton;
            print '</tr>';
            $rownum++;
        }
    }

    function getPercentage($minAmount, $amount)
    {
        if ($minAmount != 0) {
            $percentage = ($amount / $minAmount) * 100;
            if ($percentage > 100) {
                $percentage = 100;
            }
        } else {
            $percentage = $amount == 0 ? 0 : 100;
        }
        return $percentage;
    }

    private function drawCurrentBalanceForTpls($amount, $currency)
    {
        global $langs;
        $color = $this->getBgColor($amount);
        print '<div style="flex: 1; text-align: center;">
            <h3>' . $langs->trans("CurrentBalance") . ' ' . $currency . '</h3>
            <p style="background-color:' . $color . '; color:white;">' . number_format($amount, 0, '.', ' ') . ' ' . $currency . '</p>
            </div>';
    }

    private function drawMinimumCommissionForTpls($amount, $currency)
    {
        global $langs;
        $color = $this->getBgColor($amount);
        print '<div style="flex: 1; text-align: center;">
            <h3>' . $langs->trans("MinimumCommission") . ' ' . $currency . '</h3>
            <p style="background-color:' . $color . '; color:white;">' . number_format($amount, 0, '.', ' ') . ' ' . $currency . '</p>
            </div>';
    }

    function tableDrawerForViewTpls($user_id, $db, $userData, $payment_type)
    {
        global $langs;
        print '<div style="width: 100%; height: 40px;padding: 5px; background-color:lightgrey; text-align:center;"><b>' . $userData->firstname . ' ' . $userData->lastname . '</b> <br> ( ' . $userData->login . ' )</div>';
        print '<div></div>';

        $balance = $this->getFullBalance($user_id, $db);
        $earlierCommissionHUF = $balance[0]['amount'] == null ? 0 : $balance[0]['amount'];

        print '<div style="display: flex; width: 100%;">';
        $this->drawCurrentBalanceForTpls($earlierCommissionHUF, 'HUF');

        $amounts = $this->getAllPaymenttype($user_id, $db, $payment_type);
        foreach ($amounts as $amount) {
            print '<div style="flex: 1; text-align: center;">
                    <h3>' . $this->getViewTplTitle($payment_type) . ' ' . $amount['type'] . '</h3>
                    <p>' . number_format($amount['amount'], 0, '.', ' ') . ' ' . $amount['type'] . '</p>
                    </div>';
        }
        print '</div>';
        if ($payment_type == 2) {
            print '<div></div>';
            $minimumCommission = $this->getminimumCommissionGorUser($user_id);
            print '<div style="display: flex; width: 100%;">';
            $this->drawMinimumCommissionForTpls($minimumCommission, 'HUF');
            print '</div>';
        }
    }

    private function getminimumCommissionGorUser($user_id)
    {
        global $langs, $db;
        $userObj = new User($this->db);
        $sql = "SELECT * 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)){
            $minimum_monthly_commission = $row->minimum_monthly_commission;
        }
        if (!$result) {
            dol_syslog("No data found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR);
            setEventMessage($langs->trans('noUserForMinimumCommission'), 'errors');
        }
        return $minimum_monthly_commission;
    }

    private function setRowColor($rownum)
    {
        return $rownum % 2 == 0 ? '#b0c4de' : 'white';
    }

    private function drawCurrentBalance($user_id)
    {
        $balance = $this->getFullBalance($user_id, $this->db);
        if (isset($ammount[0])) {
            $earlierCommissionHUF = $balance[0]['amount'] == null ? 0 : $balance[0]['amount'];
        } else {
            $earlierCommissionHUF = 0;
        }
        $earlierCommissionHUF = $balance[0]['amount'] == null ? 0 : $balance[0]['amount'];
        //$earlierCommissionEUR = $balance[1]['amount'] == null ? 0 : $balance[1]['amount'];
        $warning = $this->isWarning($earlierCommissionHUF);
        print '<td style="text-align:center;">' . number_format($earlierCommissionHUF, 0, '.', ' ') . ' HUF ' . $warning . '</td>';
        /* $warning = $this->isWarning($earlierCommissionEUR);
        print '<td style="text-align:center;">' . number_format($earlierCommissionEUR, 0, '.', ' ') . ' EUR ' . $warning . '</td>'; */
    }

    private function drawPayedRows($user_id, $payment_type)
    {
        $amounts = $this->getAllPaymenttype($user_id, $this->db, $payment_type);
        foreach ($amounts as $amount) {
            print '<td style="text-align:center;">' . number_format($amount['amount'], 0, '.', ' ') . ' ' . $amount['type'] . ' </td>';
        }
    }

    function tableDrawerForHistory($allSalesUsers, $idmenu)
    {
        $rownum = 0;
        foreach ($allSalesUsers as $user) {
            $url = '/custom/financialreport/commissionhistory_list.php?user_id=' . $user['rowid'] . '&idmenu=' . $idmenu . '&payment_type=2&mainmenu=financialreport&leftmenu=';
            $bgcolor = $this->setRowColor($rownum);
            print '<tr style="height:30px; background-color: ' . $bgcolor . '">
            <td><a style="display: block; width: 100%; height: 100%;" href="' . $url . '">' . $user['lastname'] . ' ' . $user['firstname'] . '</a></td>';
            print '<td style="text-align: center;">' . $user['nickname'] . '</td>';
            print '<td style="text-align: center;">' . $user['login'] . '</td>';
            // Itt azt kell megejelníteni, ami a még ki nem fizetett jutalékokból és a kifizetett előlegek és visszatartsások összegének különbségéből áll össze.
            $this->drawCurrentBalance($user['rowid']);
            $this->drawPayedRows($user['rowid'], 2);
            $this->drawPayedRows($user['rowid'], 0);
            print '</tr>';
            $rownum++;
        }
    }

    function tableDrawerForDailyClosing($allSalesUsers, $idmenu)
    {
        $rownum = 0;
        foreach ($allSalesUsers as $user) {
            $url = '/custom/settlements/dailyclosing_card.php?user_id=' . $user['rowid'] . '&idmenu=' . $idmenu . '&mainmenu=settlements&leftmenu=';
            $bgcolor = $this->setRowColor($rownum);
            print '<tr style="height:30px; background-color: ' . $bgcolor . '">
            <td><a style="display: block; width: 100%; height: 100%;" href="' . $url . '">' . $user['lastname'] . ' ' . $user['firstname'] . '</a></td>';
            print '<td style="text-align: center;">' . $user['nickname'] . '</td>';
            print '<td style="text-align: center;">' . $user['login'] . '</td>';
            // Itt azt kell megejelníteni, ami a még ki nem fizetett jutalékokból és a kifizetett előlegek és visszatartsások összegének különbségéből áll össze.
            $this->drawCurrentBalance($user['rowid']);
            $this->drawPayedRows($user['rowid'], 2);
            $this->drawPayedRows($user['rowid'], 0);
            print '</tr>';
            $rownum++;
        }
    }

    function generateRef($payment_type)
    {
        return $this->getTypeOfPayment($payment_type) . '_' . dol_now() . '_' . rand(1000000, 9999999);
    }

    /* function checkBalance($user_id, $date)
    {
        $sql = "SELECT invoices FROM " . MAIN_DB_PREFIX . "financialreport_userinvoice 
        WHERE user_id = {$user_id} 
        AND date_creation BETWEEN '{$date} 00:00:00' AND '{$date} 23:59:59'
        AND payment_type = 2
        ";
        $resultBalanceData = $this->db->query($sql);
        $result = pg_fetch_all($resultBalanceData);
        return is_array($result);
    } */

    function getOccassionOfThisPeriodByDateAndUserId($user_id, $from, $to)
    {
        return rand(100, 1000);
    }

    function createDataArrayForLoginLogotOccassionTable($results)
    {
        $array = [];
        $i = 0;
        foreach ($results as $record) {
            if ($record->login_logout_status == 0) {
                $array[$i]['login'] = date('Y-m-d H:i:s', $record->date_creation);
            } else {
                if (isset($record->date_creation)) {
                    $array[$i]['logout'] = date('Y-m-d H:i:s', $record->date_creation);
                }
            }
            $i++;
        }
        return $array;
    }

    function createUserDataArray($array, $userOfGroups)
    {
        $userDataArray = [];
        $count = count($array);
        for ($k = 0; $k < $count; $k++) {
            if (isset($array[$k]['login'])) {
                if (!isset($userDataArray[$k - 1]['logout']) && $k != 0) {
                    $userDataArray[$k - 1]['logout'] = $array[$k]['login'] . '*';
                }
                $userDataArray[$k]['login'] = $array[$k]['login'];
            }
            if (isset($array[$k]['logout'])) {
                $userDataArray[$k - 1]['logout'] = $array[$k]['logout'];
            }
            if ($k == $count - 1 && isset($deletedUsersArray[$userOfGroups['user_id']])) {
                $userDataArray[$k]['logout'] = $array[$userOfGroups['user_id']];
            }
        }
        return $userDataArray;
    }

    function updateUserDataArray($userDataArray, $deletedUsersArray, $userOfGroups, $commmissionhandler)
    {
        foreach ($userDataArray as $key => $period) {
            $from = $period['login'];
            $to = isset($period['logout']) ? $period['logout'] : (isset($deletedUsersArray[$userOfGroups['user_id']]) ? $deletedUsersArray[$userOfGroups['user_id']] : date('Y-m-d', dol_now()) . ' 23:59:59');
            print $from . ' - ' . $to . '<br>';
            if (isset($from)) {
                $commission_HUF = $commmissionhandler->getOccassionOfThisPeriodByDateAndUserId_HUF($userOfGroups['user_id'], $from, $to);
                $commission_EURToHUF = $commmissionhandler->getOccassionOfThisPeriodByDateAndUserId_EURToHUF($userOfGroups['user_id'], $from, $to);
                $userDataArray[$key]['commission'] = $commission_HUF + $commission_EURToHUF;
            }
        }
        return $userDataArray;
    }

    function getAmountOfCommissions($userDataArray)
    {
        $amount = 0;
        foreach ($userDataArray as $record) {
            $amount += intval($record);
        }
        return $amount;
    }

    function getInvoicesRowidFromFacture($user_id, $from, $to)
    {
        $invocesRowidArray = [];
        $sql = "SELECT rowid FROM " . MAIN_DB_PREFIX . "facture AS f WHERE fk_user_closing = {$user_id} 
        AND EXISTS (SELECT * FROM " . MAIN_DB_PREFIX . "bbus_bbticketinvoiceprinting as bbip WHERE bbip.fk_facture = f.rowid ORDER BY bbip.rowid DESC LIMIT 1)
        AND date_closing BETWEEN '{$from}' AND '{$to}'";
        //print $sql;
        //exit;
        $resultInvoicesData = $this->db->query($sql);
        if ($this->db->num_rows($resultInvoicesData) != 0) {
            $result = pg_fetch_all($resultInvoicesData);
            foreach ($result as $value) {
                $invocesRowidArray[] = $value['rowid'];
            }
            $invocesRowidString = implode(',', $invocesRowidArray);
        }
        $readyInvoices = $this->getAllComissionInvoicesOfUserInTheGroupFromUserInvoice($user_id, $from, $to);
        //print $readyInvoices . '<br>';
        //print $invocesRowidString . '<br>';
        if (!empty($readyInvoices)) {
            $invocesRowidString = $this->giveMeTheDifferenceInString($readyInvoices, $invocesRowidString);
        }
        //print $invocesRowidString . '<br>';
        return $invocesRowidString;
    }

    function getInvoicesRowidFromFactureInAnIntervalByEveryUser($users, $from, $to, $exception)
    {
        $invocesRowidArray = [];
        $sql = "SELECT f.rowid FROM " . MAIN_DB_PREFIX . "facture as f WHERE fk_user_closing IN({$users})";
        if ($exception != '') {
            $sql .= " AND rowid NOT IN({$exception})";
        }
        $sql .= " AND EXISTS (SELECT * FROM " . MAIN_DB_PREFIX . "bbus_bbticketinvoiceprinting as bbip WHERE bbip.fk_facture = f.rowid ORDER BY bbip.rowid DESC LIMIT 1)
         AND date_closing BETWEEN '{$from}' AND '{$to}'";
        //print $sql . "\r\n";
        $resultInvoicesData = $this->db->query($sql);
        if ($this->db->num_rows($resultInvoicesData) != 0) {
            $result = pg_fetch_all($resultInvoicesData);
            foreach ($result as $value) {
                $invocesRowidArray[] = $value['rowid'];
            }
            $invocesRowidString = implode(',', $invocesRowidArray);
        }
        return $invocesRowidString;
    }

    function getAllComissionInvoicesOfUserInTheGroupFromUserInvoice($user_id, $from, $to)
    {
        $arraytmp = [];
        $invocesRowidString = '';
        $userInvocesObj = new UserInvoice($this->db);
        $result = $userInvocesObj->fetchAll('ASC', 'rowid', 0, 0, ['customsql' => "user_id = " . $user_id . " AND date_creation BETWEEN '" . $from . "' AND '" . $to . "'"]);
        foreach ($result as $data) {
            if (!empty($data->invoices) || $data->invoices != '') {
                $array1 = explode(',', $data->invoices);
                $arraytmp = array_merge($arraytmp, $array1);
            }
        }
        $invocesRowidString = implode(',', $arraytmp);
        return $invocesRowidString;
    }

    function giveMeTheDifferenceInString($readyInvoices, $invocesRowidString)
    {
        $string = '';
        if ($readyInvoices != '' && $invocesRowidString != '') {
            $array1 = explode(',', $readyInvoices);
            $array2 = explode(',', $invocesRowidString);
            /*             print 'array1: ';
                        print_r($array1);
                        print '<br>';
                        print 'array2: ';
                        print_r($array2);     
                        print '<br>'; */
            $arrayDiff = array_diff($array2, $array1);
            //$arrayDiff = array_diff($array1, $array2);  

            /* print 'Diff: ';
            print_r($arrayDiff);
            print '<br>';
            print '<br>';
            print '<br>';
     */
            //$arrayDiff = count($array2) > count($array1) ? array_diff($array2, $array1) : array_diff($array1, $array2);
            $string = implode(',', $arrayDiff);
            return $string;
        }
        return $string;

    }

    function getHtmlForConfirmWindow($commission)
    {
        $userData = [];
        $userObj = new User($this->db);
        foreach ($commission as $value) {
            $itemData = explode('_', $value);
            $userdata = $userObj->fetch($itemData[0]);
            //print_r($userObj);exit;
            $userData[$userObj->id]['name'] = $userObj->lastname . ' ' . $userObj->firstname . ' (' . $userObj->login . ') ' . $userObj->array_options['options_nickname'];
            $summaHUF += $itemData[2];
        }
        $uniformHUF = $summaHUF / count($commission);
        $array[] = $userData;
        $array[] = $uniformHUF;
        return $array;
    }

    function azosszesElemEgyezik($tomb)
    {
        $szamlalo = array_count_values($tomb);
        $kulonbozoErtekekSzama = count($szamlalo);
        return $kulonbozoErtekekSzama === 1;
    }

    function getUsersArray($data)
    {
        foreach ($data as $item) {
            $itemData = explode('_', $item);
            $usersArray[] = $itemData[0];
        }
        return $usersArray;
    }

    function checkUserInvoiceRecordAvailability($user_id)
    {
        $userInvoiceObj = new UserInvoice($this->db);
        $now = dol_now();
        $result = $userInvoiceObj->fetchAll('DESC', 'date_creation', 1, 0, ['customsql' => "user_id = " . $user_id . " AND date_creation BETWEEN '" . date('Y-m-d', $now) . " 00:00:00' AND '" . date('Y-m-d', $now) . " 23:59:59'"]);
        return $result;
    }

    function deleteUserFromUsersArrayTMPArray($key, $usersArrayTMP)
    {
        $index = array_search($key, $usersArrayTMP);
        if ($index !== false) {
            unset($usersArrayTMP[$index]);
        }
        return $usersArrayTMP;
    }

    function saveUserInvoiceRecord($usersArrayFilteredUsersFrom, $val, $uniformHUF, $settledInvoices)
    {
        global $user;
        $userinvoice = new UserInvoice($this->db);
        $currency = 0;
        $userinvoice->ref = $this->generateRef(2);
        $userinvoice->label = 'Jutalék/Commission ' . $usersArrayFilteredUsersFrom[$val];
        $userinvoice->description = 'Összeg / Amount: ' . $uniformHUF . 'HUF.';
        $userinvoice->user_id = $val;
        $userinvoice->payment_type = 2;
        $userinvoice->amount = $uniformHUF;
        $userinvoice->currency = $currency;
        $userinvoice->individual = 0;
        $userinvoice->invoices = $settledInvoices;
        $userinvoice->date_creation = strtotime($usersArrayFilteredUsersFrom[$val]);
        $userinvoice->create($user);
    }

    function getAllDifferentDatesASC($from, $to)
    {
        foreach ($from as $fromvalue) {
            foreach ($fromvalue as $value1) {
                $allDates[] = $value1;
            }
        }
        foreach ($to as $tovalue) {
            foreach ($tovalue as $value1) {
                $allDates[] = $value1;
            }
        }
        $allDates = array_unique($allDates);
        return $allDates;
    }

    function getQueryArray($from, $to)
    {
        $allDates = $this->getAllDifferentDatesASC($from, $to);
        uasort($allDates, 'datumOsszehasonlitas');
        $countAllDatesArray = count($allDates);
        $allDates = array_values($allDates);
        for ($i = 0; $i < $countAllDatesArray - 1; $i++) {
            $queryArray[$i]['from'] = $allDates[$i];
            $queryArray[$i]['to'] = $allDates[$i + 1];
        }
        return $queryArray;
    }


    function getAllComissionInvoicesOfUserInTheGroupFromUserInvoiceCreated($usersArray, $from, $to)
    {
        $userInvocesObj = new UserInvoice($this->db);
        $result = $userInvocesObj->fetchAll('ASC', 'rowid', 0, 0, ['customsql' => "user_id IN(" . $usersArray . ") AND date_creation BETWEEN '" . $from . "' AND '" . $to . "'"]);
        $all = [];
        foreach ($result as $key => $value) {
            //print $value->user_id . ' - ';
            //print $value->invoices . "\r\n";
            $arrayTmp = explode(',', $value->invoices);
            $all = array_merge($all, $arrayTmp);
        }
        $all = array_unique($all);
        $all = array_filter($all);
        sort($all);
        return implode(',', $all);
    }

    public function getGroupMemberHistory($user_id, $group_id, $date)
    {
        $groupMemberHistory = [];
        $from = $date . ' 00:00:00';
        $to = $date . ' 23:59:59';
        $UserNaplo = new UserNaplo($this->db);
        $result = $UserNaplo->fetchAll('ASC', 'rowid', 0, 0, ['customsql' => "user_id = {$user_id} AND group_user_id = {$group_id} AND date_creation BETWEEN '{$from}' AND '{$to}'"]);
        foreach ($result as $record) {
            if ($record->status == 1) {
                $groupMemberHistory[]['added'] = date("Y-m-d H:i:s", $record->date_creation);
            } else {
                $groupMemberHistory[]['deleted'] = date("Y-m-d H:i:s", $record->date_creation);
            }
        }
        return $groupMemberHistory;
    }

    function getUsersByIdFromGroupUsers($id)
    {
        global $db;
        $usersOfGroup = [];
        if (isset($id)) {
            $sql = "SELECT gu.fk_user AS user_id, gu.date_creation, u.firstname as firstname, u.lastname as lastname, u.login as login FROM " . MAIN_DB_PREFIX . "settlements_groupusers as gu  
            INNER JOIN " . MAIN_DB_PREFIX . "user AS u ON u.rowid = gu.fk_user
            WHERE gu.fk_Settlements_group = {$id}";
            $data = $db->query($sql);
            if (!$data) {
                dol_syslog("No data found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR);
                setEventMessage('A csoporthoz rendelt felhasználók naplózási lekérdezésébe valami hiba csúszott.', 'errors');
                return $usersOfGroup;
            }
            while ($row = pg_fetch_assoc($data)) {
                $usersOfGroup[] = $row;
            }
        }
        return $usersOfGroup;
    }

    function getDeletedHotelUsers($id)
    {
        global $db;
        $deletedUsersArray = [];
        if (isset($id)) {
            $sql = "SELECT user_id, date_creation as date 
        FROM " . MAIN_DB_PREFIX . "settlements_usernaplo 
        WHERE group_user_id = {$id} 
        AND status = 0 ORDER BY date_creation ASC";
            //print $sql;exit;
            $data = $db->query($sql);
            $dataArray = pg_fetch_all($data);
            if (empty($dataArray)) {
                dol_syslog("No data found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR);
                return $deletedUsersArray;
            }
            foreach ($dataArray as $row) {
                $deletedUsersArray[$row['user_id']][] = $row['date'];
            }
        }
        return $deletedUsersArray;
    }

    function getUserdataFromNaploByUaserId($user_id, $userGroupId)
    {
        global $db;
        $date = '';
        $sql = "SELECT date_creation as date FROM " . MAIN_DB_PREFIX . "settlements_usernaplo
        WHERE user_id = {$user_id} AND group_user_id = {$userGroupId} AND status = 1
        ORDER BY rowid ASC LIMIT 1";
        //print $sql;
        $data = $db->query($sql);
        $dataArray = pg_fetch_all($data);
        if (empty($dataArray)) {
            dol_syslog("No data found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR);
            return $date;
        }
        foreach ($dataArray as $row) {
            return $row['date'];
        }
    }

    function createDaterangeString($daterange)
    {
        $array = explode(' - ', $daterange);
        return str_replace('-', '.', $array[0]) . ' - ' . str_replace('-', '.', $array[1]);
    }

    function getUsersOfHotelGroup2($id, $daterange)
    {
        global $db;
        $dates = explode(' - ', $daterange);
        $now = dol_now();
        $to = $dates[1] > date('Y-m-d', $now) ? date('Y-m-d', $now) : $dates[1];

        $sql = "SELECT u.rowid, u.firstname, u. lastname, ue.user_category FROM " . MAIN_DB_PREFIX . "settlements_usernaplo AS un
        INNER JOIN " . MAIN_DB_PREFIX . "user as u ON u.rowid = un.user_id
        INNER JOIN " . MAIN_DB_PREFIX . "user_extrafields AS ue ON ue.fk_object = u.rowid
        WHERE un.date_creation BETWEEN '{$dates[0]} 00:00:00' AND '{$to} 23:59:59'
        AND un.group_user_id = {$id}
        GROUP BY u.firstname, u.lastname, ue.user_category, u.rowid";
        $data = $db->query($sql);
        $result = pg_fetch_all($data);
        if (empty($result)) {
            return [];
        }
        return $result;
    }


    function getUsersOfHotelGroup($id, $daterange, $entity)
    {
        global $db;
        $array = [];
        $dates = explode(' - ', $daterange);
        $now = dol_now();
        $to = $dates[1] > date('Y-m-d', $now) ? date('Y-m-d', $now) : $dates[1];
        if (isset($id)) {
            $sql = "SELECT u.rowid, u.firstname, u. lastname, ue.user_category FROM " . MAIN_DB_PREFIX . "facture AS f
            INNER JOIN " . MAIN_DB_PREFIX . "user as u ON u.rowid = f.fk_user_closing
            INNER JOIN " . MAIN_DB_PREFIX . "user_extrafields AS ue ON ue.fk_object = u.rowid
            INNER JOIN " . MAIN_DB_PREFIX . "facture_extrafields as fe ON fe.fk_object = f.rowid
            WHERE f.date_closing BETWEEN '{$dates[0]} 00:00:00' AND '{$to} 23:59:59'
            AND f.entity = {$entity}
            AND fe.react_facture = {$id}
            GROUP BY u.rowid, u.firstname, u. lastname, ue.user_category";
            //print $sql;
            $data = $db->query($sql);
            $array = pg_fetch_all($data);
            if (empty($result)) {
                return $array;
            }
        }
        return $array;
    }

    function getUsersList($id, $daterange, $entity)
    {
        global $db;
        $string = '';
        $result = $this->getUsersOfHotelGroup($id, $daterange, $entity);
        foreach ($result as $row) {
            $string .= '<tr class="smallredcolor"><td><b>' . $row['firstname'] . ' ' . $row['lastname'] . '</b></td><td>(' . $this->userCategoryArray[$row['user_category']] . ')</td><td>' . $this->correctNumber($this->getAllAmountInPeriod($id, $daterange, 'HUF', $row['rowid'], $entity)) . ' HUF</td><td>' . $this->correctNumber($this->getAllAmountInPeriod($id, $daterange, 'EUR', $row['rowid'], $entity)) . ' EUR</td></tr>';
        }
        return $string;
    }

    function getUsersIdList($id, $daterange, $entity)
    {
        global $db;
        $result = $this->getUsersOfHotelGroup($id, $daterange, $entity);
        if (!empty($result)) {
            foreach ($result as $row) {
                $array[] = $row['rowid'];
            }
            return implode(',', $array);
        }
        return '';
    }

    function getAllAmountInPeriod($id, $daterange, $currency, $usersIdList, $entity)
    {
        //$entity = 1;
        global $db;
        $result = 0;
        $dates = explode(' - ', $daterange);
        $now = dol_now();
        $to = $dates[1] > date('Y-m-d', $now) ? date('Y-m-d', $now) : $dates[1];
        if (isset($id)) {
            $sql = "SELECT sum(f.total_ttc) FROM " . MAIN_DB_PREFIX . "facture as f
            INNER JOIN " . MAIN_DB_PREFIX . "facture_extrafields as fe ON fe.fk_object = f.rowid
            WHERE f.paye = 1
            AND f.date_closing BETWEEN '{$dates[0]} 00:00:00' AND '{$to} 23:59:59'";
            if ($usersIdList !== '') {
                $sql .= " AND f.fk_user_closing in($usersIdList)";
            }
            $sql .= " AND f.multicurrency_code = '{$currency}'
        AND f.entity = {$entity}
        AND fe.react_facture = {$id}";
            //print $sql;
            $data = $db->query($sql);
            $result = pg_fetch_all($data);
            return is_null($result[0]['sum']) ? 0 : $result[0]['sum'];
        }
    }

    function getAllCommissionInPeriod($id, $daterange, $currency, $usersIdList, $entity)
    {
        global $db;
        $commissionsArray = [];
        $dates = explode(' - ', $daterange);
        $now = dol_now();
        $to = $dates[1] > date('Y-m-d', $now) ? date('Y-m-d', $now) : $dates[1];
        if (isset($id)) {
            $sql = "SELECT f.total_ttc, fe.commission
        FROM " . MAIN_DB_PREFIX . "facture as f 
        INNER JOIN " . MAIN_DB_PREFIX . "facture_extrafields as fe ON fe.fk_object = f.rowid 
        WHERE f.paye = 1 
        AND f.date_closing BETWEEN '{$dates[0]} 00:00:00' AND '{$to} 23:59:59'";
            if ($usersIdList !== '') {
                $sql .= " AND f.fk_user_closing in($usersIdList)";
            }
            $sql .= " AND f.multicurrency_code = '{$currency}' 
        AND f.entity = '{$entity}'
        AND fe.react_facture = {$id}";
            //print $sql;exit;
            $data = $db->query($sql);
            $result = pg_fetch_all($data);
            //print_r($result);
            if (!empty($result)) {
                foreach ($result as $row) {
                    $commissionValue = $this->getCommissionData($row['commission'], 0);
                    $commissionUnit = $this->getCommissionData($row['commission'], 1);
                    if ($commissionUnit == '%') {
                        $commissionsArray[] = ($row['total_ttc'] / 100) * $commissionValue;
                    } else {
                        $commissionsArray[] = $commissionValue;
                    }
                }
            }
            return $this->getAmountOfCommissions($commissionsArray);
        }
        return 0;
    }

    private function getCommissionData($commission, $number)
    {
        $array = explode('_', $commission);
        return $array[$number];
    }

    public function getAllHotels()
    {
        global $db;
        $resultArray = [];
        $hotelsObj = new Hotel($db);
        $hotelsArray = $hotelsObj->fetchAll('ASC', 'label', 0, 0);
        if (!empty($hotelsArray)) {
            foreach ($hotelsArray as $hotelRecord) {
                $resultArray[$hotelRecord->id] = $hotelRecord->label;
            }
        }
        return $resultArray;
    }

    public function getGroupRefByID($id)
    {
        if (isset($id)) {

            global $db;
            $sql = "SELECT ref FROM " . MAIN_DB_PREFIX . "settlements_group WHERE rowid = {$id}";
            $data = $db->query($sql);
            if (!$data) {
                dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR);
            }
            while ($row = pg_fetch_assoc($data)) {
                return $row['ref'];
            }
        }
        return '';
    }

    public function getGroupEntityId($id)
    {
        if (isset($id)) {
            global $db;
            $sql = "SELECT fk_entity FROM " . MAIN_DB_PREFIX . "settlements_group WHERE rowid = {$id}";
            $data = $db->query($sql);
            if (!$data) {
                dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR);
            }
            while ($row = pg_fetch_assoc($data)) {
                return $row['fk_entity'];
            }
        }
        return null;
    }

    public function getGroupLabelById($id)
    {
        global $db;
        $groupObj = new Group($db);
        $groupObj->fetch($id);
        return $groupObj->ref;
    }

    public function getFirstDateFromUserNaplo($id)
    {
        global $db;
        $from = date("Y-m-d") . ' 00:00:00';
        $to = date("Y-m-d H:i:s");
        $sql = "SELECT un.date_creation FROM " . MAIN_DB_PREFIX . "settlements_usernaplo AS un
            WHERE un.group_user_id = {$id} AND un.date_creation BETWEEN '{$from}' AND '{$to}' AND un.status = 1 
            ORDER BY un.date_creation ASC LIMIT 1";
        //print $sql;exit;
        $data = $db->query($sql);
        if (!$data) {
            dol_syslog("No group found.", LOG_DEBUG | LOG_INFO | LOG_WARNING | LOG_ERR);
        }
        while ($row = pg_fetch_all($data)) {
            return $row[0]['date_creation'];
        }
        return null;
    }

    public function getNamedataFoTheUser($user_id)
    {
        global $db;
        $sql = "SELECT u.firstname, u.lastname, u.login FROM " . MAIN_DB_PREFIX . "user AS u WHERE rowid = {$user_id}";
        //print $sql;exit;
        $data = $db->query($sql);
        foreach (pg_fetch_all($data) as $userData) {
            return $userData['firstname'] . ' ' . $userData['lastname'] . ' (' . $userData['login'] . ')';
        }
    }

    public function checklogoutRecordInUserNaplo($user_id, $from, $id)
    {
        global $db;
        $sql = "SELECT date_creation FROM " . MAIN_DB_PREFIX . "settlements_usernaplo
        WHERE group_user_id = {$id}
        AND user_id = {$user_id}
        AND date_creation > '{$from}'
        AND status = 0
        ORDER BY date_creation ASC LIMIT 1";
        $data = $db->query($sql);
        while ($row = pg_fetch_assoc($data)) {
            return $row['date_creation'];
        }
    }

    public function getGTAndGUArray()
    {
        global $db;
        $sql = "SELECT fk_groupid, fk_group_tools_id FROM " . MAIN_DB_PREFIX . "settlements_usergrouptoolgroup";
        $result = $db->query($sql);
        if (pg_num_rows($result) > 0) {
            while ($row = pg_fetch_assoc($result)) {
                $array[$row['fk_groupid']] = $row['fk_group_tools_id'];
            }
            return $array;
        }
        return [];

    }

    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 createPopoupBox($numberOfUsers, $usersArray, $id)
    {
        $popoupBox = '<div style="float:right; padding-bottom: 10px;" title="';
        foreach ($usersArray as $users) {
            $popoupBox .= '<span class=&quot;fas fa-user  em080&quot; style=&quot; color: #a69944;&quot; cursor: pointer;></span> 
            <u class=&quot;paddingrightonly&quot;>' . $users['lastname'] . ' ' . $users['firstname'] . ' (' . $users['login'] . ')</u><br>';
        }
        $popoupBox .= '" class="classfortooltip"><div onclick="popupboxClick(' . $id . ')" class="groupColumn2ndLine">' . $numberOfUsers . '</div>';
        return $popoupBox;
    }
}