<?php
use Illuminate\Database\Capsule\Manager as Capsule;

/**
 * State Total Revenue Report
 *
 * @category Report
 * @package  ClientExec
 * @author   Juan Bolivar <juan@clientexec.com>
 * @license  ClientExec License
 * @version  1.0
 * @link     http://www.clientexec.com
 */

require_once 'modules/admin/models/Package.php';
require_once 'modules/clients/models/UserPackage.php';
require_once 'modules/billing/models/Currency.php';
require_once 'modules/clients/models/User.php';
require_once 'modules/billing/models/Invoice.php';

/**
 * Revenue_And_Taxes Report Class
 *
 * @category Report
 * @package  ClientExec
 * @author   Juan Bolivar <juan@clientexec.com>
 * @license  ClientExec License
 * @version  1.0
 * @link     http://www.clientexec.com
 */
class Revenue_And_Taxes extends Report
{
    private $lang;

    protected $featureSet = 'billing';

    function __construct($user = null, $customer = null)
    {
        $this->lang = lang('Revenue And Taxes');
        parent::__construct($user, $customer);
    }

    /**
     * Report Process Method
     *
     * @return null - direct output
     */
    function process()
    {
        @set_time_limit(0);

        $getInvoicesGroup = @$_GET['getinvoicesgroup'];

        if ($getInvoicesGroup) {
            $this->getInvoicesForGroup();
            exit;
        }

        // Set the report information
        $this->SetDescription($this->user->lang('Displays the total revenue between 2 dates broken by state of the clients of the selected country.'));

        $groupBy = ((isset($_REQUEST['groupby']))? $_REQUEST['groupby'] : 'state');

        // Load the country information
        $countries = new Countries($this->user);
        $countryCode = ((isset($_REQUEST['countrycode']))? $_REQUEST['countrycode'] : $this->settings->get('Default Country'));
        $countryName = $countries->validCountryCode($countryCode, false, 'name');

        if (isset($_REQUEST['startdate'])) {
            $startDateArray = explode('/', $_REQUEST['startdate']);

            if ($this->settings->get('Date Format') == 'm/d/Y') {
                $tempStartDate = date("Y-m-d", mktime(0, 0, 0, $startDateArray[0], $startDateArray[1], $startDateArray[2]));
            } else {
                $tempStartDate = date("Y-m-d", mktime(0, 0, 0, $startDateArray[1], $startDateArray[0], $startDateArray[2]));
            }
        } else {
            $tempStartDate = date("Y-m-d", mktime(0, 0, 0, date("m"), 1, date("Y")-1));
        }

        if (isset($_REQUEST['enddate'])) {
            $endDateArray = explode('/', $_REQUEST['enddate']);

            if ($this->settings->get('Date Format') == 'm/d/Y') {
                $tempEndDate = date("Y-m-d", mktime(0, 0, 0, $endDateArray[0], $endDateArray[1], $endDateArray[2]));
            } else {
                $tempEndDate = date("Y-m-d", mktime(0, 0, 0, $endDateArray[1], $endDateArray[0], $endDateArray[2]));
            }
        } else {
            $tempEndDate = date("Y-m-d");
        }

        // Load the currency information
        $currency = new Currency($this->user);
        $currencyCode = ((isset($_REQUEST['currencycode']))? $_REQUEST['currencycode'] : $this->settings->get('Default Currency'));
        $currencyName = $currency->getName($currencyCode);

        // Get id of the country custom field
        $result = Capsule::table('customuserfields')
            ->where('type', typeCOUNTRY)
            ->value('id');

        $countryCustomFieldId = $result;

        switch ($groupBy) {
            case 'state':
                // Get id of the state custom field
                $result = Capsule::table('customuserfields')
                    ->where('type', typeSTATE)
                    ->value('id');

                $stateCustomFieldId = $result;

                //Get all paid invoices amount from clients of the selected country paid the selected date range with the selected currency, grouped by state/province
                $result = Capsule::table('invoice as i')
                    ->join('users as u', 'u.id', '=', 'i.customerid')
                    ->join('user_customuserfields as ucuf', 'ucuf.userid', '=', 'u.id')
                    ->join('currency as c', 'c.abrv', '=', 'i.currency')
                    ->leftJoin('user_customuserfields as ucuf2', 'ucuf2.userid', '=', 'u.id')
                    ->selectRaw(
                        'ucuf2.value,
                         SUM(i.amount) AS totalAmount,
                         SUM(i.amount - i.subtotal) AS totalTax,
                         COUNT(*) AS totalNumber'
                    )
                    ->where('i.status', INVOICE_STATUS_PAID)
                    ->where('ucuf.customid', $countryCustomFieldId)
                    ->where('ucuf.value', $countryCode)
                    ->where('ucuf2.customid', $stateCustomFieldId)
                    ->where('i.datepaid', '>=', $tempStartDate)
                    ->where('i.datepaid', '<=', $tempEndDate . ' 23:59:59')
                    ->where('i.currency', $currencyCode)
                    ->groupBy('ucuf2.value')
                    ->orderBy('ucuf2.value')
                    ->get();

                break;
            case 'month':
                //Get all paid invoices amount from clients of the selected country paid the selected date range with the selected currency, grouped by year and month
                $result = Capsule::table('invoice as i')
                    ->join('users as u', 'u.id', '=', 'i.customerid')
                    ->join('user_customuserfields as ucuf', 'ucuf.userid', '=', 'u.id')
                    ->join('currency as c', 'c.abrv', '=', 'i.currency')
                    ->selectRaw(
                        'DATE_FORMAT(i.datepaid,"%m") AS month,
                         YEAR(i.datepaid) AS year, 
                         SUM(i.amount) AS totalAmount,
                         SUM(i.amount - i.subtotal) AS totalTax,
                         COUNT(*) AS totalNumber'
                    )
                    ->where('i.status', INVOICE_STATUS_PAID)
                    ->where('ucuf.customid', $countryCustomFieldId)
                    ->where('ucuf.value', $countryCode)
                    ->where('i.datepaid', '>=', $tempStartDate)
                    ->where('i.datepaid', '<=', $tempEndDate . ' 23:59:59')
                    ->where('i.currency', $currencyCode)
                    ->groupBy(
                        Capsule::raw('DATE_FORMAT(i.datepaid, "%m")'),
                        Capsule::raw('YEAR(i.datepaid)')
                    )
                    ->orderBy('year')
                    ->orderBy('month')
                    ->get();

                break;
        }

        $subGroup = array();
        $totalAmount = 0;
        $totalTax = 0;
        $totalInvoices = 0;

        foreach ($result as $row) {
            $totalAmount += $row->totalAmount;
            $totalTax += $row->totalTax;
            $totalInvoices += $row->totalNumber;

            switch ($groupBy) {
                case 'state':
                    $states = new States($this->user);
                    $stateProvince = $states->validStateCode($countryCode, $row->value, 'iso', false, 'name');
                    $groupName = (isset($stateProvince) && $stateProvince)? $stateProvince : '---';
                    $groupParam1 = $row->value;
                    $groupParam2 = '';

                    break;
                case 'month':
                    $tMonth = date("F", mktime(0, 0, 0, $row->month + 1, 0, 0));
                    $tYear = $row->year;
                    $groupName = $tYear.' - '.$tMonth;
                    $groupParam1 = $row->year;
                    $groupParam2 = $row->month;

                    break;
            }

            $formattedAmount = $currency->format($currencyCode, $row->totalAmount, true, false);
            $formattedTax = $currency->format($currencyCode, $row->totalTax, true, false);
            $subGroup[] = array(
                "<a href=\"javascript:LoadInvoicesForGroup('".$groupBy."','".$countryCode."','".$groupParam1."','".$groupParam2."','".$tempStartDate."','".$tempEndDate."','".$currencyCode."');\">".$groupName."</a>",
                $formattedAmount,
                $formattedTax,
                $row->totalNumber
            );
        }

        $subGroup[] = array('', '', '', '');
        $formattedTotalAmount = $currency->format($currencyCode, $totalAmount, true, false);
        $formattedTotalTax = $currency->format($currencyCode, $totalTax, true, false);
        $subGroup[] = array('<b>'.$this->user->lang('Totals').'</b>', '<b>'.$formattedTotalAmount.'</b>', '<b>'.$formattedTotalTax.'</b>', '<b>'.$totalInvoices.'</b>');

        $labels = array(
            (($groupBy == 'state')? $this->user->lang('State/Province') : $this->user->lang('Year').' - '.$this->user->lang('Month')),
            $this->user->lang('Total Paid Amount'),
            $this->user->lang('Total Paid Tax'),
            $this->user->lang('Number of Paid Invoices')
        );

        if (isset($_REQUEST['download']) && $_REQUEST['download'] == 1) {
            $this->download($labels, $subGroup, $countryName.' '.'total revenue from '.$tempStartDate.' to '.$tempEndDate.' for '.$currencyCode.'.csv');
        }

        // Build country options
        $result = Capsule::table('country as c')
            ->join('user_customuserfields as ucuf', function ($join) use ($countryCustomFieldId) {
                $join->on('ucuf.value', '=', 'c.iso')
                    ->where('ucuf.customid', '=', $countryCustomFieldId);
            })
            ->join('invoice as i', function ($join) {
                $join->on('i.customerid', '=', 'ucuf.userid')
                    ->where('i.status', '=', INVOICE_STATUS_PAID);
            })
            ->select('c.name', 'c.iso')
            ->distinct()
            ->orderBy('c.name', 'asc')
            ->get();

        $countryOptions = '';
        $isSelectedCountryInTheList = false;

        foreach ($result as $row) {
            if (!$isSelectedCountryInTheList && $countryName < $row->name) {
                $countryOptions .= '<option value="'.CE_Lib::viewEscape($countryCode).'" selected>'.CE_Lib::viewEscape($countryName).'</option>';
                $isSelectedCountryInTheList = true;
            } elseif ($countryCode == $row->iso) {
                $isSelectedCountryInTheList = true;
            }
            $countryOptions .= '<option value="'.CE_Lib::viewEscape($row->iso).'" '.(($countryCode == $row->iso)? 'selected' : '').'>'.CE_Lib::viewEscape($row->name).'</option>';
        }
        if (!$isSelectedCountryInTheList) {
            $countryOptions .= '<option value="'.CE_Lib::viewEscape($countryCode).'" selected>'.CE_Lib::viewEscape($countryName).'</option>';
        }

        // Build currency options
        $result = Capsule::table('invoice as i')
            ->join('users as u', 'u.id', '=', 'i.customerid')
            ->join('user_customuserfields as ucuf', 'ucuf.userid', '=', 'u.id')
            ->join('currency as c', 'c.abrv', '=', 'i.currency')
            ->where('i.status', INVOICE_STATUS_PAID)
            ->where('ucuf.customid', $countryCustomFieldId)
            ->where('ucuf.value', $countryCode)
            ->select('c.abrv', 'c.name')
            ->distinct()
            ->orderBy('c.name', 'asc')
            ->get();

        $currencyOptions = '';
        $isSelectedCurrencyInTheList = false;

        foreach ($result as $row) {
            if (!$isSelectedCurrencyInTheList && $currencyName < $row->name) {
                $currencyOptions .= '<option value="'.CE_Lib::viewEscape($currencyCode).'" selected>'.CE_Lib::viewEscape($currencyName).'</option>';
                $isSelectedCurrencyInTheList = true;
            } elseif ($currencyCode == $row->abrv) {
                $isSelectedCurrencyInTheList = true;
            }
            $currencyOptions .= '<option value="'.CE_Lib::viewEscape($row->abrv).'" '.(($currencyCode == $row->abrv)? 'selected' : '').'>'.CE_Lib::viewEscape($row->name).'</option>';
        }
        if (!$isSelectedCurrencyInTheList) {
            $currencyOptions .= '<option value="'.CE_Lib::viewEscape($currencyCode).'" selected>'.CE_Lib::viewEscape($currencyName).'</option>';
        }

        $startDateFormatted = CE_Lib::db_to_form($tempStartDate, $this->settings->get('Date Format'), "/");
        $endDateFormatted   = CE_Lib::db_to_form($tempEndDate, $this->settings->get('Date Format'), "/");

        $filter = '
<style>
.rat-row {
    display: flex;
    align-items: center;
    gap: 20px;
    flex-wrap: wrap;
    margin-bottom: 10px;
}
.rat-field {
    display: flex;
    align-items: center;
    gap: 8px;
}
.rat-field label {
    white-space: nowrap;
    margin: 0;
}
.rat-spacer { flex: 1; }
</style>

<form id="report" method="GET">

    <div class="rat-row">
        <div class="rat-field">
            <label>'.$this->user->lang('Group by').':</label>
            <select class="normalSelect2" name="groupby" id="groupby" style="min-width:140px;">
                <option value="state" '.(($groupBy == 'state') ? 'selected' : '').'>'.$this->user->lang('State/Province').'</option>
                <option value="month" '.(($groupBy == 'month') ? 'selected' : '').'>'.$this->user->lang('Month').'</option>
            </select>
        </div>
        <div class="rat-field">
            <label>'.$this->user->lang('Country').':</label>
            <select class="normalSelect2" name="countrycode" id="countrycode" style="min-width:160px;">
                '.$countryOptions.'
            </select>
        </div>
        <div class="rat-field">
            <label>'.$this->user->lang('Currency').':</label>
            <select class="normalSelect2" name="currencycode" id="currencycode" style="min-width:150px;">
                '.$currencyOptions.'
            </select>
        </div>
    </div>

    <div class="rat-row">
        <div class="rat-field">
            <label>'.$this->user->lang('Date Paid').':</label>
            <input class="datepicker" type="text" name="startdate" id="startdate" value=\''.CE_Lib::viewEscape($startDateFormatted).'\' />
            <span>&mdash;</span>
            <input class="datepicker" type="text" name="enddate" id="enddate" value=\''.CE_Lib::viewEscape($endDateFormatted).'\' />
        </div>
        <div class="rat-spacer"></div>
        <button class="cm-btns orange" type="button" data-loading-text="'.$this->user->lang('Loading...').'"
            onclick="ChangeTable(document.getElementById(\'groupby\').value, document.getElementById(\'countrycode\').value, document.getElementById(\'startdate\').value, document.getElementById(\'enddate\').value, document.getElementById(\'currencycode\').value, 1);">
            '.$this->user->lang('Download .csv').'
        </button>
        <input type="button" class="cm-btns orange" value=\''.$this->user->lang('Search').'\' onclick="ChangeTable(document.getElementById(\'groupby\').value, document.getElementById(\'countrycode\').value, document.getElementById(\'startdate\').value, document.getElementById(\'enddate\').value, document.getElementById(\'currencycode\').value, 0);">
    </div>

</form>

<script type="text/javascript">
function ChangeTable(groupby, countrycode, startdate, enddate, currencycode, download) {
    location.href = "index.php?fuse=reports&view=viewreport&controller=index&report=Revenue+And+Taxes&type=Revenue"
        + "&groupby=" + groupby
        + "&countrycode=" + countrycode
        + "&startdate=" + encodeURIComponent(startdate)
        + "&enddate=" + encodeURIComponent(enddate)
        + "&currencycode=" + currencycode
        + "&download=" + download;
}
function LoadInvoicesForGroup(groupby, countrycode, groupparam1, groupparam2, startdate, enddate, currencycode) {';

        $url = "admin/index.php?noheaderfooter=1&getinvoicesgroup=1&groupby='+groupby+'&countrycode='+countrycode+'&groupparam1='+groupparam1+'&groupparam2='+groupparam2+'&startdate='+startdate+'&enddate='+enddate+'&currencycode='+currencycode+'&view=".CE_Lib::viewEscape($_REQUEST['view'])."&fuse=".CE_Lib::viewEscape($_REQUEST['fuse'])."&report=".urlencode($_REQUEST['report'])."&type=".CE_Lib::viewEscape($_REQUEST['type']);
        $url = mb_substr(CE_Lib::getSoftwareURL(), -1, 1) == "//" ? CE_Lib::getSoftwareURL().$url : CE_Lib::getSoftwareURL()."/".$url;

        $filter .= "
    var url = '".$url."';
    window.open(url, '', 'top=100,left=100,width=800,height=600,scrollbars=yes');
}
</script>";

        echo $filter;

        $this->reportData[] = array(
            "group" => $subGroup,
            "groupname" => "",
            "label" => $labels
        );
    }



    function getInvoicesForGroup()
    {
        // date format of the paid date
        // uses the PHP date() format syntax
        $strDateFormat = "j M Y";

        include_once 'modules/billing/models/Currency.php';
        $currency = new Currency($this->user);

        $countryCode = $_GET["countrycode"];
        $countries = new Countries($this->user);
        $countryName = $countries->validCountryCode($countryCode, false, 'name');
        $nCurrentStartDate = $_GET["startdate"];
        $nCurrentEndDate = $_GET["enddate"];
        $currencyCode = $_GET["currencycode"];

        $groupBy = ((isset($_GET['groupby']))? $_GET['groupby'] : 'state');

        // Get id of the country custom field
        $result = Capsule::table('customuserfields')
            ->where('type', typeCOUNTRY)
            ->value('id');

        $countryCustomFieldId = $result;

        switch ($groupBy) {
            case 'state':
                $stateCode = $_GET["groupparam1"];
                $states = new States($this->user);
                $stateProvince = $states->validStateCode($countryCode, $stateCode, 'iso', false, 'name');
                $groupName = (isset($stateProvince) && $stateProvince)? $stateProvince : '---';

                // Get id of the state custom field
                $result = Capsule::table('customuserfields')
                    ->where('type', typeSTATE)
                    ->value('id');

                $stateCustomFieldId = $result;

                break;
            case 'month':
                $month = $_GET["groupparam2"];
                $monthName = date("F", mktime(0, 0, 0, $month + 1, 0, 0));
                $year = $_GET["groupparam1"];
                $groupName = $year.' - '.$monthName;

                break;
        }

        //Display Report
        echo "<H1>" . $countryName . " - " . $groupName . " (". $nCurrentStartDate . " - " . $nCurrentEndDate . ")</h1></br>";

        echo "<style>
            table {
                width:100%;
            }
            table, th, td {
                border: 1px solid black;
                border-collapse: collapse;
            }
            th, td {
                padding: 5px;
                text-align: left;
            }
            table#t01 tr:nth-child(even) {
                background-color: #eee;
            }
            table#t01 tr:nth-child(odd) {
               background-color:#fff;
            }
            table#t01 th	{
                background-color: white;
                color: black;
            }
            </style>";

        echo "<table id='t01'>";
        echo "<tr><th>". $this->user->lang('Invoice') . "</th><th>" . $this->user->lang('Customer') . "</th><th>" . $this->user->lang('Amount') . "</th><th>" . $this->user->lang('Tax') . "</th><th>" . $this->user->lang('Date Paid') . "</th></tr>";

        switch ($groupBy) {
            case 'state':
                $result = Capsule::table('invoice as i')
                    ->join('users as u', 'u.id', '=', 'i.customerid')
                    ->join('user_customuserfields as ucuf', 'ucuf.userid', '=', 'u.id')
                    ->leftJoin('user_customuserfields as ucuf2', 'ucuf2.userid', '=', 'u.id')
                    ->select(
                        'i.id',
                        'i.customerid',
                        'i.amount',
                        'i.subtotal',
                        'i.datepaid',
                    )
                    ->where('i.status', INVOICE_STATUS_PAID)
                    ->where('ucuf.customid', $countryCustomFieldId)
                    ->where('ucuf.value', $countryCode)
                    ->where('ucuf2.customid', $stateCustomFieldId)
                    ->where('ucuf2.value', $stateCode)
                    ->where('i.datepaid', '>=', $nCurrentStartDate)
                    ->where('i.datepaid', '<=', $nCurrentEndDate . ' 23:59:59')
                    ->where('i.currency', $currencyCode)
                    ->orderBy('i.datepaid')
                    ->get();

                break;
            case 'month':
                $result = Capsule::table('invoice as i')
                    ->join('users as u', 'u.id', '=', 'i.customerid')
                    ->join('user_customuserfields as ucuf', 'ucuf.userid', '=', 'u.id')
                    ->select(
                        'i.id',
                        'i.customerid',
                        'i.amount',
                        'i.subtotal',
                        'i.datepaid',
                    )
                    ->where('i.status', INVOICE_STATUS_PAID)
                    ->where('ucuf.customid', $countryCustomFieldId)
                    ->where('ucuf.value', $countryCode)
                    ->whereYear('i.datepaid', $year)
                    ->whereMonth('i.datepaid', $month)
                    ->where('i.datepaid', '>=', $nCurrentStartDate)
                    ->where('i.datepaid', '<=', $nCurrentEndDate . ' 23:59:59')
                    ->where('i.currency', $currencyCode)
                    ->orderBy('i.datepaid')
                    ->get();

                break;
        }

        //initialize
        $incomeTotal = 0;
        $monthSubTotal = 0;
        //$prevMonth = "-1";
        $recCount = 0;
        $TotalTax = 0;
        $totalInvoices = 0;
        $monthTax = 0;
        $monthInvoices = 0;

        foreach ($result as $row) {
            $thisMonth = date("n", strtotime($row->datepaid));

            // Get list of use name
            $title = '';

            if ($row->customerid != 0) {
                $tUser = new User($row->customerid);
                if ($tUser->IsOrganization()) {
                    $lastName = $tUser->getOrganization();
                    if (strlen($lastName) > 20) {
                        $dCustomerName = mb_substr($lastName, 0, 20) . "...";
                        $title = "title=\"$lastName\"";
                    } else {
                        $dCustomerName = $lastName;
                    }
                } else {
                    $dCustomerName = $tUser->getFirstName() . " " . $tUser->getLastName();
                    $title = "";
                }
                // build a link to the customer profile
                $CustomerLink = "<a href=\"index.php?fuse=clients&controller=userprofile&frmClientID=" . $row->customerid . "&view=profileinvoices\" $title target=blank>" . CE_Lib::viewEscape($dCustomerName) . "</a>";

                // build the Print to PDF Link
                $printInvoiceLink = "<a href=index.php?fuse=billing&frmClientID=" . $row->customerid . "&controller=invoice&view=invoice&profile=1&amp;invoiceid=" . $row->id . " target=blank>" . $row->id . "</a>";
            } else {
                $dCustomerName = $this->user->lang('DELETED');
                $CustomerLink = CE_Lib::viewEscape($dCustomerName);
                $printInvoiceLink = $row->id;
            }

            // Modified in reference to ticket #40938 : Tax is (amount-subtotal) in all cases.

            $tax = $row->amount - $row->subtotal;
            $strTaxFormatted = $currency->format($currencyCode, $tax, true, "NONE", true);
            $strAmountFormatted = $currency->format($currencyCode, $row->amount, true, "NONE", true);
            $monthTax += $tax;
            $TotalTax += $tax;
            $incomeTotal += $row->amount;
            $monthSubTotal += $row->amount;
            $aGroup = array();
            $aGroup[] = array($printInvoiceLink, $CustomerLink, $strAmountFormatted, $strTaxFormatted, date($strDateFormat, strtotime($row->datepaid)));
            $recCount++;
            $monthInvoices++;
            $totalInvoices++;

            echo "<tr><td>" . $printInvoiceLink . "</td><td>" . $CustomerLink . "</td><td>" . $strAmountFormatted . "</td><td>" . $strTaxFormatted. "</td><td>" . date($strDateFormat, strtotime($row->datepaid)) . "</td></tr>";
        }
        echo "</table>";

        if ($monthInvoices > 0) {
            echo "</br><table>";
            echo "<tr><th>" . $this->user->lang('Invoices') . "</th><th>" . $this->user->lang('Income (Tax In)') . "</th><th>" . $this->user->lang('Tax Charged') . "</th><th>" . $this->user->lang('Income (Tax Out)') . "</th></tr>";
            echo "<tr><td>" . $totalInvoices . "</td><td>" . $currency->format($currencyCode, $incomeTotal, true, "NONE", true) . "</td><td>" .  $currency->format($currencyCode, $TotalTax, true, "NONE", true) . "</td><td>" .  $currency->format($currencyCode, $incomeTotal - $TotalTax, true, "NONE", true) . "</td></tr>";
            echo "</table>";
        }
    }
}