EXCELIA_DBARR/custom/financialreport/monthlystatsindex.php

<?php
$res = 0;
// Try main.inc.php into web root known defined into CONTEXT_DOCUMENT_ROOT (not always defined)
if (!$res && !empty($_SERVER["CONTEXT_DOCUMENT_ROOT"])) {
    $res = @include $_SERVER["CONTEXT_DOCUMENT_ROOT"] . "/main.inc.php";
}
// Try main.inc.php into web root detected using web root calculated from SCRIPT_FILENAME
$tmp = empty($_SERVER['SCRIPT_FILENAME']) ? '' : $_SERVER['SCRIPT_FILENAME'];
$tmp2 = realpath(__FILE__);
$i = strlen($tmp) - 1;
$j = strlen($tmp2) - 1;
while ($i > 0 && $j > 0 && isset($tmp[$i]) && isset($tmp2[$j]) && $tmp[$i] == $tmp2[$j]) {
    $i--;
    $j--;
}
if (!$res && $i > 0 && file_exists(substr($tmp, 0, ($i + 1)) . "/main.inc.php")) {
    $res = @include substr($tmp, 0, ($i + 1)) . "/main.inc.php";
}
if (!$res && $i > 0 && file_exists(dirname(substr($tmp, 0, ($i + 1))) . "/main.inc.php")) {
    $res = @include dirname(substr($tmp, 0, ($i + 1))) . "/main.inc.php";
}
// Try main.inc.php using relative path
if (!$res && file_exists("../main.inc.php")) {
    $res = @include "../main.inc.php";
}
if (!$res && file_exists("../../main.inc.php")) {
    $res = @include "../../main.inc.php";
}
if (!$res && file_exists("../../../main.inc.php")) {
    $res = @include "../../../main.inc.php";
}
if (!$res) {
    die("Include of main fails");
}

require_once DOL_DOCUMENT_ROOT . '/core/class/html.formfile.class.php';
require_once DOL_DOCUMENT_ROOT . '/user/class/usergroup.class.php';
require_once DOL_DOCUMENT_ROOT . '/custom/settlements/class/groupusers.class.php';
require_once DOL_DOCUMENT_ROOT . '/custom/financialreport/class/helper.class.php';
require_once DOL_DOCUMENT_ROOT . '/custom/financialreport/class/userinvoice.class.php';
require_once DOL_DOCUMENT_ROOT . '/custom/bbus/class/commissionhandler.class.php';
require_once DOL_DOCUMENT_ROOT . '/user/class/user.class.php';



$helper = new HelperUserInvoice($db);
$commmissionhandler = new CommissionHandler();
$userObj = new User($db);

// Load translation files required by the page
$langs->loadLangs(array("financialreport@financialreport"));

$action = GETPOST('action', 'aZ09');
$id = GETPOST('id', 'int');
$idmenu = GETPOST('idmenu', 'int');

$limit = GETPOST('limit', 'int') ? GETPOST('limit', 'int') : $conf->liste_limit;
$sortfield = GETPOST('sortfield', 'aZ09comma');
$sortorder = GETPOST('sortorder', 'aZ09comma');
$search_nickname = GETPOST('search_nickname', 'alpha');
$search_name = GETPOST('search_name', 'alpha');
$search_login = GETPOST('search_login', 'alpha');
$searchArray = ['search_name' => $search_name, 'search_nickname' => $search_nickname];
$confirmmakeupto = GETPOST('confirmmakeupto', 'alpha');
$datastring = GETPOST('datastring', 'alpha');
$year = GETPOST('year', 'int');
$month = GETPOST('month', 'int') < 10 ? '0' . GETPOST('month', 'int') : GETPOST('month', 'int');
$excelexport = GETPOST('excelexport', 'int');

$search = array();
foreach ($searchArray as $key => $val) {
    if (GETPOST($key, 'alpha') !== '') {
        $search[$key] = GETPOST($key, 'alpha');
    }
}

if (GETPOST('button_removefilter_x', 'alpha')) {
    foreach ($searchArray as $key => $val) {
        $search[$key] = '';
        $searchArray[$key] = '';
        $$key = '';
    }
}

if (!$sortorder) {
    $sortorder = "ASC";
}

$form = new Form($db);
$formfile = new FormFile($db);

if($year == '' || $year == 0 || empty($year) || is_null($year)){
    $year = date("Y");
}
if($month == '' || $month == 0 || empty($month) || is_null($month)){
    $month = date("m");
}

function getLastDayOfMonth($year, $month) {
    $lastDay = date('t', mktime(0, 0, 0, $month, 1, $year));
    return $lastDay;
}

$lastDayOfMonth = getLastDayOfMonth($year, $month);

$from = $year . '-' . $month . '-01';
$to = $year . '-' . $month . '-' . $lastDayOfMonth;

$saledTickets = [];

$sql = "SELECT 
f.type,
f.ref, 
e.label, 
c.value, 
f.datef,
f.datef, 
f.date_lim_reglement, 
fdet.total_ht, 
fdet.total_tva,
fdet.total_ttc, 
fdet.multicurrency_total_ht, 
fdet.multicurrency_total_tva, 
fdet.multicurrency_total_ttc, 
fdet.multicurrency_code, 
pai.libelle,
fdet.tva_tx,
f.date_closing
FROM llx_facture as f
INNER JOIN llx_facturedet as fdet ON fdet.fk_facture = f.rowid
INNER JOIN llx_entity as e ON e.rowid = f.entity
INNER JOIN llx_const as c ON c.entity = f.entity
INNER JOIN llx_c_paiement as pai ON pai.id = f.fk_mode_reglement
WHERE f.datec BETWEEN '{$from} 00:00:00' AND '{$to} 23:59:59'
AND f.entity = {$entity}
AND c.name = 'MAIN_INFO_TVAINTRA'
AND f.date_closing IS NOT NULL
ORDER BY f.datec ASC";

//print $sql;

$data = $db->query($sql);
while($row=$db->fetch_object($data))
{
    $saledTickets[] = $row;
}

$daterange = $from  . ' - ' . $to;

if($excelexport){
	$helper->createExcelMonthlyExport($daterange, $entity, $saledTickets);
}

$title = $langs->trans('MonthlyStatistic');
$help_url = '';
$morejs = '';
$morecss = '';
llxHeader('', $title, $help_url, '', 0, 0, $morejs, $morecss, '', 'classforhorizontalscrolloftabs');

$url = $_SERVER["PHP_SELF"] . '?idmenu=' . $idmenu . '&mainmenu=financialreport&leftmenu=';

print '<form method="POST" id="witholdingForm" action="' . $url . '">' . "\n";
print '<input type="hidden" name="token" value="' . newToken() . '">';
print '<input type="hidden" name="formfilteraction" id="formfilteraction" value="list">';
print '<input type="hidden" name="action" value="list">';
print '<input type="hidden" name="year" value="' . $year . '">';
print load_fiche_titre($langs->trans("MonthlyStatistic"), '', 'financialreport.png@financialreport');

print '<div style="height: 50px; background-color: lightgray; text-align: center; padding-top: 20px; border-radius: 10px;">';
include DOL_DOCUMENT_ROOT . '/custom/financialreport/core/tpl/daterangedivbasecommission.tpl.php';
print '</div>';
print '<div class="center" style="margin-bottom: 30px;"><button class="ExcelExport" type="submit" name="excelexport", value="1">' . $langs->trans('Excel export') . '</button></div>';
print '
<table style="width:100%;">
    <tr>
        <th>' . $langs->trans("Facture") . '</th>
        <th>' . $langs->trans("Type") . '</th>
        <th>' . $langs->trans("Entity") . '</th>
        <th>' . $langs->trans("VATnumber") . '</th>
        <th>' . $langs->trans("DateCreation") . '</th>
        <th>' . $langs->trans("DateCompletion") . '</th>
        <th>' . $langs->trans("PaymentDeadline") . '</th>
        <th>' . $langs->trans("NetHUF") . '</th>
        <th>' . $langs->trans("VatHUF") . '</th>
        <th>' . $langs->trans("GrossHUF") . '</th>
        <th>' . $langs->trans("Net") . '</th>
        <th>' . $langs->trans("VAT") . '</th>
        <th>' . $langs->trans("Gross") . '</th>
        <th>' . $langs->trans("Currency") . '</th>
        <th>' . $langs->trans("PaymentMethod") . '</th>
        <th>' . $langs->trans("VAT") . ' %</th>
    </tr>';
//include DOL_DOCUMENT_ROOT . '/custom/financialreport/core/tpl/search_fields_for_userslist.tpl.php';
//$helper->tableDrawerForBasicCommission($allSalesUsers, $db, $idmenu, $year, $month);

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, ',', ' ');
    print '<tr class="center">
        <td>' . $item->ref . '</td>
        <td>' . $helper->getFectureType($item->type) . '</td>
        <td>' . $item->label . '</td>
        <td>' . $item->value . '</td>
        <td>' . $item->datef . '</td>
        <td>' . $item->date_lim_reglement . '</td>
        <td>' . $item->datef . '</td>
        <td>' . number_format($item->total_ht, 4, ',', ' ') . '</td>
        <td>' . number_format($item->total_tva, 4, ',', ' ') . '</td>
        <td>' . number_format($item->total_ttc, 4, ',', ' ') . '</td>
        <td>' . $mtht . '</td>
        <td>' . $mttva . '</td>
        <td>' . $mtttc . '</td>
        <td>' . $item->multicurrency_code . '</td>
        <td>' . $item->libelle . '</td>
        <td>' . number_format($item->tva_tx, 0) . ' %</td>
    </tr>';
}

print '</table>';

print '</form>' . "\n";

// End of page
llxFooter();
$db->close();

?>
<style>
	.ExcelExport{
        margin-top: 20px;
        width: 200px;
		height: 30px;
		background-color: rgb(163, 0, 51);
		color: white;
		border-radius: 20px;
		border: none;
		cursor: pointer;
		background-color: green;
	}
</style>