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

/**
 * Country Yearly Income 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_By_Country 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_By_Country extends Report
{
    private $lang;

    protected $featureSet = 'billing';

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

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

        // Set the report information
        $this->SetDescription($this->user->lang('Displays the yearly revenue from clients of the selected country.'));

        // 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');

        $year = ((isset($_REQUEST['year']))? $_REQUEST['year'] : date('Y'));

        // 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;

        $filter = '<form id="report" method="GET">'
            .'    <div style="text-align:center">'
            .'        '.$this->user->lang('Country').': '
            .'        <select name="countrycode" id="countrycode" value="'.CE_Lib::viewEscape($countryCode).'" onChange="ChangeTable(this.value, \''.$year.'\', \''.$currencyCode.'\');"> ';

        //Get all countries with paid invoices
        $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();

        $isSelectedCountryInTheList = false;

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

        $filter .= '</select>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;';
        $filter .= '        '.$this->user->lang('Year').': '
            .'        <select name="year" id="year" value="'.CE_Lib::viewEscape($year).'" onChange="ChangeTable(\''.$countryCode.'\', this.value, \''.$currencyCode.'\');"> ';

        //Get all years when were paid invoices from clients of the selected country
        $result = Capsule::table('invoice as i')
            ->join('users as u', 'u.id', '=', 'i.customerid')
            ->join('user_customuserfields as ucuf', 'ucuf.userid', '=', 'u.id')
            ->selectRaw('DISTINCT YEAR(i.datepaid) as year')
            ->where('i.status', INVOICE_STATUS_PAID)
            ->where('ucuf.customid', $countryCustomFieldId)
            ->where('ucuf.value', $countryCode)
            ->orderBy('i.datepaid', 'desc')
            ->get();

        $isSelectedYearInTheList = false;

        foreach ($result as $row) {
            if (!$isSelectedYearInTheList && $year > $row->year) {
                $filter .= '<option value="'.$year.'" selected>'.$year.'</option>';
                $isSelectedYearInTheList = true;
            } elseif ($year == $row->year) {
                $isSelectedYearInTheList = true;
            }
            $filter .= '<option value="'.$row->year.'" '.(($year == $row->year)? 'selected' : '').'>'.$row->year.'</option>';
        }
        if (!$isSelectedYearInTheList) {
            $filter .= '<option value="'.$year.'" selected>'.$year.'</option>';
            $isSelectedYearInTheList = true;
        }

        $filter .= '</select>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;';
        $filter .= '        '.$this->user->lang('Currency').': '
            .'        <select name="currencycode" id="currencycode" value="'.CE_Lib::viewEscape($currencyCode).'" onChange="ChangeTable(\''.$countryCode.'\', \''.$year.'\', this.value);"> ';

        //Get all currencies of all years when were paid invoices from clients of the selected country
        $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();

        $isSelectedCurrencyInTheList = false;

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

        $filter .= '</select>';
        $filter .= '    </div>'
            .'</form>'
            .'</br>'
            .'<script type="text/javascript">'
            .'    function ChangeTable(countrycode, year, currencycode){'
            .'        location.href="index.php?fuse=reports&view=viewreport&controller=index&report=Revenue+By+Country&type=Revenue&countrycode="+countrycode+"&year="+year+"&currencycode="+currencycode;'
            .'    }'
            .'</script>';
        echo $filter;

        //Get all paid invoices amount from clients of the selected country paid the selected year with the selected currency
        $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('i.customerid, SUM(i.amount) AS totalAmount')
            ->where('i.status', INVOICE_STATUS_PAID)
            ->where('ucuf.customid', $countryCustomFieldId)
            ->where('ucuf.value', $countryCode)
            ->whereYear('i.datepaid', $year)
            ->where('i.currency', $currencyCode)
            ->groupBy('i.customerid')
            ->orderByDesc('totalAmount')
            ->get();

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

        foreach ($result as $row) {
            $totalAmount += $row->totalAmount;
            $user = new User($row->customerid);
            $clientFullName = $user->getFullName(true);
            $stateProvince = $user->getState(true);
            $formattedAmount = $currency->format($currencyCode, $row->totalAmount, true, false);

            $subGroup[] = array(
                "<a href=\"index.php?frmClientID=".$row->customerid."&fuse=clients&controller=userprofile&view=profilecontact\">".$clientFullName."</a>",
                (isset($stateProvince))? $stateProvince : '---',
                $formattedAmount
            );
        }

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

        $this->reportData[] = array(
            "group" => $subGroup,
            "groupname" => "",
            "label" => array(
                $this->user->lang('Client Name'),
                $this->user->lang('State/Province'),
                $this->user->lang('Amount')
            )
        );
    }
}
