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

require_once 'modules/admin/models/StatusAliasGateway.php';

/**
 * Client Countries Report
 *
 * @category Report
 * @package  ClientExec
 * @author   Juan D. Bolivar <juan@clientexec.com>
 * @license  ClientExec License
 * @version  1.0
 * @link     http://www.clientexec.com
 *
 *************************************************
 *   1.0 Initial Report Released.  - Juan D. Bolivar
 ************************************************
 */

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

    protected $featureSet = 'accounts';

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

    /**
     * Report Process Method
     *
     * @return null - direct output
     */
    function process()
    {
        $this->SetDescription($this->user->lang('Displays total users of each country.'));

        $graphdata = @$_GET['graphdata'];

        // Status Options
        $statusOptions = array();
        $it = StatusAliasGateway::getInstance($this->user)->getUserAliases();

        $line['Country'] = '-';

        while ($status = $it->fetch()) {
            $statusOptions[$status->statusid] = $status->name;
            $line[$status->name] = '-';
        }

        $line['Total'] = '-';

        $labels = array_keys($line);

        foreach ($labels as $labelKey => $labelValue) {
            if (in_array($labelValue, array('Country', 'Total'))) {
                $labels[$labelKey] = $this->user->lang($labelValue);
            } else {
                $labels[$labelKey] = $this->user->lang("%s Clients", $this->user->lang($labelValue));
            }
        }

        //get status count per country
        $countryStatusCount = array();

        $result = Capsule::table('users as u')
            ->selectRaw(
                'c.name, '
                .'u.status, '
                .'COUNT(ucuf.value) AS total'
            )
            ->leftJoin('user_customuserfields as ucuf', 'u.id', '=', 'ucuf.userid')
            ->leftJoin('country as c', 'ucuf.value', '=', 'c.iso')
            ->where('ucuf.customid', function ($subQuery) {
                $subQuery->select('cuf.id')
                    ->from('customuserfields as cuf')
                    ->where('cuf.name', 'Country');
            })
            ->groupBy('ucuf.value', 'u.status')
            ->orderBy('c.name', 'ASC')
            ->get();

        foreach ($result as $row) {
            $countryStatusCount[$row->name][$row->status] = $row->total;
        }

        $totalElements = 0;
        $aGroup = array();

        //SQL to generate the the result set of the report
        $result = Capsule::table('user_customuserfields as ucuf')
            ->selectRaw(
                'c.name, '
                .'COUNT(ucuf.value) AS total'
            )
            ->leftJoin('country as c', 'ucuf.value', '=', 'c.iso')
            ->where('ucuf.customid', function ($subQuery) {
                $subQuery->select('cuf.id')
                    ->from('customuserfields as cuf')
                    ->where('cuf.name', 'Country');
            })
            ->groupBy('ucuf.value')
            ->orderByRaw('COUNT(ucuf.value) DESC')
            ->get();

        foreach ($result as $row) {
            $groupLine = $line;
            $groupLine['Country'] = $this->user->lang($row->name);
            $groupLine['Total'] = $row->total;

            foreach ($countryStatusCount[$row->name] as $status => $statusCount) {
                $groupLine[$statusOptions[$status]] = $statusCount;
            }

            $groupLineFinal = $groupLine;
            $groupLineFinal['Total'] = '<b>'.$groupLineFinal['Total'].'</b>';
            $aGroup[] = array_values($groupLineFinal);
            $totalElements += $row->total;
        }

        if (isset($aGroup)) {
            $this->reportData[] = array(
                "group"     => $aGroup,
                "groupname" => $this->user->lang("Client Countries"),
                "label"     => $labels,
                "groupId"   => "",
                "isHidden"  => false
            );

            unset($aGroup);
        }
    }
}
