<?php
class Mtrialusage extends Model
{
    private $intervals = array(
        'ALL',
        '7 DAY',
        '14 DAY',
        '1 MONTH',
        '2 MONTH',
        '3 MONTH',
        '6 MONTH',
        '1 YEAR',
        '2 YEAR',
        '3 YEAR'
    );

    function __construct()
    {
        parent::__construct();

        $this->load->library('session');
    }

    function _check_access()
    {
        if ($this->phpsession->get('bugtracker_access'))
            return true;

        return false;
    }

    private function getDistributorID($ctrID)
    {
        $query = "SELECT id
            FROM cl_hlusers.distributors
            WHERE ctrID = {$ctrID}";

        $dist = $this->db->query($query)->row();

        return $dist->id;
    }

    private function getAdditionalCtrIDs($ctrID)
    {
        $query = "SELECT additional_ctrID
            FROM cl_hlusers.distributors
            WHERE ctrID = {$ctrID}";

        $country = $this->db->query($query)->row();

        if ($country->additional_ctrID == '')
            return false;

        return explode(", ", $country->additional_ctrID);
    }

    function getSchools()
    {
        $query = "SELECT * FROM cl_hlusers.schools WHERE nSchoolID > 0";

        return $this->db->query($query)->result();
    }

    function getCourses($schools)
    {
        $schoolsString = implode(", ", $schools);

        $query = "SELECT * FROM cl_hlusers.courses WHERE school_id IN ({$schoolsString}) AND is_archive != 1";

        return $this->db->query($query)->result();
    }

    function getDistributor($ctr)
    {
        $query = "SELECT * FROM cl_hlusers.distributors WHERE ctrID = {$ctr}";

        return $this->db->query($query)->row();
    }

    function getTrialStarts($ctrID, $distributor, $permission_id, $partner_id, $startDate, $endDate, $typeID, $version)
    {
        $distID = $distributor;

        $query = "SELECT CONCAT(firstname, ' ', lastname) AS name, email, phone, update_date,
                JSON_EXTRACT(JSON_EXTRACT(JSON_EXTRACT(versionInfo, '$.{$typeID}'), '$.{$version}'), '$.count') AS prgCount
            FROM local_crm.trial_requests 
            WHERE distID = {$distID} AND JSON_UNQUOTE(JSON_EXTRACT(versionInfo, '$.{$typeID}')) != ''
                AND JSON_EXTRACT(JSON_EXTRACT(versionInfo, '$.{$typeID}'), '$.{$version}') != ''
                AND SUBSTRING(update_date, 1, 10) >= '{$startDate}' AND SUBSTRING(update_date, 1, 10) <= '{$endDate}'
            ORDER BY firstname ASC;";

        return $this->db->query($query)->result();
    }

    /**
     * Get all the program starts for the specific country.
     *
     * @return Object
     */
    function getProgramStarts($ctrID, $tmpID, $distributor, $permission_id, $partner_id, $startDate, $endDate, $courses)
    {
        $end        = "";
        $result     = array();
        $permission = $this->getPermission($permission_id);
        $ctr        = $distributor != '' && $permission->is_admin == 1 ? $distributor : $ctrID;

        $distID = $this->getDistributorID($ctr);

        if ($partner_id != '')
            $template = $this->getSelectedTemplate($partner_id, $tmpID);
        else
            $template = $this->getSelectedTemplate($ctrID, $tmpID);

        $versions   = implode(", ", $template->versions);
        $types      = implode(", ", $template->types);
        $ctrCodes   = $template->country_code;

        if (($key = array_search('trial', $ctrCodes)) !== false) {
            unset($ctrCodes[$key]);

            array_push($ctrCodes, '96');

            if ($ctr == '39' || $ctr == '0')
                array_push($ctrCodes, '94');

            if ($ctr == '82' || $ctr == '0')
                array_push($ctrCodes, '91');
        }

        if (($key = array_search('comm', $ctrCodes)) !== false) {
            unset($ctrCodes[$key]);

            $addCtrID = $this->getAdditionalCtrIDs($ctr);

            $addCountries = array();

            array_push($addCountries, $ctr);
            array_push($ctrCodes, $ctr);
            array_push($addCountries, '95');
            array_push($ctrCodes, '95');

            if ($addCtrID) {
                for ($i = 0; $i < count($addCtrID); $i++) {
                    if (strpos($addCtrID[$i], '+DID') !== false) {
                        $newCtr = substr($addCtrID[$i], 0, -4);
                        array_push($addCountries, $newCtr);
                    } else {
                        array_push($ctrCodes, $addCtrID[$i]);
                    }
                }
            } else {
                array_push($ctrCodes, '95');
            }

            $addCountryCode = implode(", ", $addCountries);
        }

        $countryCode = implode(", ", $ctrCodes);

        if ($template->product == 2 && array_search('comm', $ctrCodes) !== false)
            $countryCode = $ctr;

        if ($template->interval == 0)
            $template->interval = 10;

        $query = "SELECT us.usPwd AS usPwd, SUM(us.usInditasokSzama) AS startSum, from_unixtime(max(us.usDate), '%Y-%m-%d') AS lastUsage, SUM(us.workingMinutes) AS workingMinutes";

        for ($i = 1; $i < $template->interval; $i++) {
            $interval = $this->intervals[$i];

            $query .= ", IF(max(us.usDate) >= UNIX_TIMESTAMP(STR_TO_DATE(date_format(CURDATE() - INTERVAL {$interval}, '%Y-%m-%d'), '%Y-%m-%d')), '{$i}'";
            $end .= ")";
        }

        $query .= ", {$template->interval}{$end} AS trialUsage";

        $neddCtr = "";
        if ($ctr != '0' && (array_search('comm', $template->country_code)) !== false)
            $neddCtr = " h.hlCtrID = {$ctr} AND";

        $query .= " FROM cl_hlusers.userstats2013 us
            LEFT JOIN cl_hlusers.hardlock h ON h.hlNum = LEFT(us.usPwd, 6)
            LEFT JOIN cl_hlusers.users u ON u.nUserID = h.hlUser
            WHERE {$neddCtr} us.usVer IN ({$versions}) 
            AND h.hlUser != '' AND h.hlUser IS NOT NULL AND (h.product_id = {$template->product} AND LEFT(h.hlNum, 2) IN ({$countryCode})
            AND us.usPwd IN (SELECT prPass FROM cl_hlusers.h_programs";

        if (isset($template->status) && $template->status != '' && $template->status >= 0) {
            $status = $template->status;
            $query .= " WHERE prStat = {$status} ";
        }

        $query .= ")";

        if ($startDate != '') {
            $startDate = strtotime($startDate);
            $query .= " AND us.usDate >= {$startDate}";
        }

        if ($endDate != '') {
            $datetime = new DateTime($endDate);
            $datetime->modify('+1 day');
            $endDate = strtotime($datetime->format('Y-m-d'));
            $query .= " AND us.usDate <= {$endDate}";
        }

        if (!empty($courses)) {
            $courseString = implode(", ", $courses);
            $query .= " AND h.hlUser IN (SELECT nUserID FROM cl_hlusers.students WHERE course_id IN ({$courseString}))";
        }

        if ((array_search('99', $template->country_code)) !== false)
            $query .= " AND h.hlCtrID = {$ctr}";

        if ($template->interval != 10)
            $query .= " AND us.usDate >= UNIX_TIMESTAMP(STR_TO_DATE(date_format(CURDATE() - INTERVAL {$this->intervals[$template->interval]}, '%Y-%m-%d'), '%Y-%m-%d'))";

        if ($template->product == 1)
            $query .= " AND SUBSTR(us.usPwd, 7, 1) IN ({$types})";
        else
            $query .= " AND SUBSTR(us.usPwd, 7, 1) = 'L'";

        $query .= ")";

        if ($ctr != '0') {
            if ($template->product == 2)
                $types = "'L'";

            /*if ((array_search('comm', $template->country_code)) !== false && !empty($addCountries)) {
                $query .= " OR (LEFT(h.hlNum, 2) IN ($addCountryCode) AND us.usVer IN ({$versions}) AND h.product_id = {$template->product} 
                    AND SUBSTR(us.usPwd, 7, 1) IN ({$types}) AND h.hlCtrID = {$ctr} AND (u.distID_by_user = {$distID} OR u.nDistID = {$distID}))";
            }*/

            $query .= " AND (us.usVer IN ({$versions}) AND h.product_id = {$template->product} 
                    AND SUBSTR(us.usPwd, 7, 1) IN ({$types})";

            if ((array_search('trial', $template->country_code)) !== false)
                $query .= " AND (u.distID_by_user = {$distID} OR u.nDistID = {$distID})";

            if ((array_search('comm', $template->country_code)) !== false && !empty($addCountries))
                $query .= " AND h.hlCtrID = {$ctr} AND LEFT(h.hlNum, 2) IN ($addCountryCode)";

            $query .= ")";
        } else {
            $query .= " AND (us.usVer IN ({$versions}) AND h.product_id = {$template->product} 
                AND SUBSTR(us.usPwd, 7, 1) IN ({$types}) AND ((u.distID_by_user IS NULL OR u.distID_by_user < 1) AND u.nDistID < 1))";
        }

        $query .= " GROUP BY us.usPwd";

        //$result['query'] = $query;

        $result['programs'] = $this->db->query($query)->result();
        $result['interval'] = $template->interval;
        $result['chart']    = $template->chart_type != '' ? $template->chart_type : 'bar';

        return $result;
    }

    /**
     * Get the selected users from the passwords.
     *
     * @return Object
     */
    function getUsersFromPassword($passwords, $ctrID, $tmpID, $partner_id = '')
    {
        $result = array();

        $serials = "'" . implode("','", $passwords) . "'";

        if ($partner_id != '')
            $template = $this->getSelectedTemplate($partner_id, $tmpID);
        else
            $template = $this->getSelectedTemplate($ctrID, $tmpID);

        $versions = implode(", ", $template->versions);

        $customQuery = "SELECT lic.custom_id AS userID, lic.name AS strName, p.prSellDate AS prSellDate, p.prPass AS prPass, lic.email AS email
            FROM cl_hlusers.custom_login_licenses lic
            LEFT JOIN cl_hlusers.h_programs p ON p.prPass = lic.serial
            WHERE p.prPass IN ({$serials}) AND SUBSTR(p.prPass, 8, 2) IN ({$versions}) 
            GROUP BY p.prPass";
        $customUsers = $this->db->query($customQuery)->result();

        if (!empty($customUsers)) {
            foreach ($customUsers as $customUser) {
                if (($key = array_search($customUser->prPass, $passwords)) !== false)
                    unset($passwords[$key]);
            }

            $serials = "'" . implode("','", $passwords) . "'";
        }

        $query = "SELECT IF(u.strName IS NOT NULL, u.strName, h.hlUser) AS strName, p.prSellDate AS prSellDate, p.prPass AS prPass, u.strEMail AS email";

        if ($ctrID == '37' || $ctrID == '36')
            $query .= ", u.nUserID AS nUserID";

        $query .= " FROM cl_hlusers.h_programs p
            LEFT JOIN cl_hlusers.hardlock h ON h.hlNum = p.prHlNum
            LEFT JOIN cl_hlusers.users u ON u.nUserID = h.hlUser
            WHERE p.prPass IN ({$serials}) AND SUBSTR(p.prPass, 8, 2) IN ({$versions}) 
            GROUP BY p.prPass";

        $crmUsers = $this->db->query($query)->result();

        if (!empty($customUsers)) {
            $mergedUsers = array_merge(
                (array)$crmUsers,
                (array)$customUsers
            );

            usort($mergedUsers, function ($a, $b) {
                return strcmp($a->strName, $b->strName);
            });
        } else {
            $mergedUsers = $crmUsers;
        }

        $result['users'] = $mergedUsers;

        return $result;
    }

    function getPartnerTemplate($partner_id)
    {
        $query = "SELECT *
            FROM cl_hlusers.partner_templates
            WHERE partner_id = '{$partner_id}'";

        $template = $this->db->query($query)->row();

        return $template;
    }

    function getPartner($username)
    {
        $query = "SELECT id, permission_id, program_usage_name_mask
            FROM cl_hlusers.partner_authenticate
            WHERE username = '{$username}'";

        $partner = $this->db->query($query)->row();

        return $partner;
    }

    function getPermission($permission_id)
    {
        $query = "SELECT * FROM cl_hlusers.partner_permissions WHERE id = {$permission_id}";

        return $this->db->query($query)->row();
    }

    function getDistributors($isAdmin, $ctrID)
    {
        if ($isAdmin == 1)
            $query = "SELECT * FROM cl_hlusers.distributors WHERE id != 0";
        else
            $query = "SELECT * FROM cl_hlusers.distributors WHERE ctrID = {$ctrID}";

        return $this->db->query($query)->result();
    }

    function getProgramVersions()
    {
        $query = "SELECT * FROM cl_hlusers.p_versions WHERE verID > 15 ORDER BY verID DESC";

        return $this->db->query($query)->result();
    }

    function getProgramTypes()
    {
        $query = "SELECT typeCode, typeName FROM cl_hlusers.p_types WHERE typeOrder IS NOT NULL ORDER BY typeOrder";

        return $this->db->query($query)->result();
    }

    function getSpecEditonForCountry($ctrID)
    {
        $spec = array();

        switch ($ctrID) {
            case '82':
                $spec['id']     = 92;
                $spec['name']   = 'BIMLadder';
                break;
            case '39':
                $spec['id']     = 94;
                $spec['name']   = 'Namirial';
                break;

            default:
                break;
        }

        return $spec;
    }

    function getSelectedTemplate($partner_id, $temp_id)
    {
        $query = "SELECT template
            FROM cl_hlusers.partner_templates
            WHERE partner_id = '{$partner_id}'";
        $tmp = $this->db->query($query)->row();

        $db_templates = (array)json_decode($tmp->template)->templates;

        return $db_templates[$temp_id];
    }

    function saveNameMask($partner_id, $nameMask)
    {
        $updateQuery = "UPDATE cl_hlusers.partner_authenticate
            SET program_usage_name_mask = '{$nameMask}'
            WHERE id = {$partner_id}";
        $this->db->query($updateQuery);

        return $this->db->affected_rows() > 0;
    }

    function saveTemplate($template, $partner_id)
    {
        $query = "SELECT id, template FROM cl_hlusers.partner_templates WHERE partner_id = '{$partner_id}'";
        $tmp = $this->db->query($query)->row();

        if ($template->template_id == '' || empty($tmp)) {
            $template->template_id = $this->generateRandomString();
            $response['addOption'] = true;
        }

        if (empty($tmp)) {
            $db_template                = new stdClass();
            $db_template->name          = $template->template_name;
            $db_template->product       = $template->product;
            $db_template->types         = (array)$template->program_type;
            $db_template->versions      = (array)$template->program_version;
            $db_template->country_code  = (array)$template->country_code;
            $db_template->interval      = $template->interval;
            $db_template->chart_type    = $template->chart_type != '' ? $template->chart_type : 'bar';
            $db_template->status        = $template->status;

            $json_string = '{"templates":{"' . $template->template_id . '":' . json_encode($db_template, JSON_UNESCAPED_UNICODE) . '}}';

            $new_template = new stdClass();
            $new_template->partner_id   = $partner_id;
            $new_template->template     = $json_string;


            $this->db->insert("cl_hlusers.partner_templates", $new_template);

            $response['newName'] = $template->template_name;
        } else {
            $db_templates = (array)json_decode($tmp->template)->templates;

            if ($db_templates[$template->template_id]->name != $template->template_name)
                $response['newName'] = $template->template_name;

            if ($template->product != '1')
                $template->program_type = array();

            $db_templates[$template->template_id]->name         = $template->template_name;
            $db_templates[$template->template_id]->product      = $template->product;
            $db_templates[$template->template_id]->types        = (array)$template->program_type;
            $db_templates[$template->template_id]->versions     = (array)$template->program_version;
            $db_templates[$template->template_id]->country_code = (array)$template->country_code;
            $db_templates[$template->template_id]->interval     = $template->interval;
            $db_templates[$template->template_id]->chart_type   = $template->chart_type != '' ? $template->chart_type : 'bar';
            $db_templates[$template->template_id]->status       = $template->status;

            $updatedJson = '{"templates":' . json_encode($db_templates, JSON_UNESCAPED_UNICODE) . '}';

            $updateQuery = "UPDATE cl_hlusers.partner_templates
                SET template = '{$updatedJson}'
                WHERE id = {$tmp->id}";
            $this->db->query($updateQuery);
        }

        if ($this->db->affected_rows() > 0)
            $response['updated'] = true;
        else
            $response['updated'] = false;

        $response['temp_id'] = $template->template_id;

        return $response;
    }

    function deleteTemplate($tmpID, $ctrID)
    {
        $response = array();

        $query = "SELECT id, template FROM cl_hlusers.partner_templates WHERE partner_id = '{$ctrID}'";
        $tmp = $this->db->query($query)->row();

        $db_templates = (array)json_decode($tmp->template)->templates;
        unset($db_templates[$tmpID]);

        $updatedJson = '{"templates":' . json_encode($db_templates, JSON_UNESCAPED_UNICODE) . '}';

        $updateQuery = "UPDATE cl_hlusers.partner_templates
            SET template = '{$updatedJson}'
            WHERE id = {$tmp->id}";
        $this->db->query($updateQuery);

        if ($this->db->affected_rows() > 0)
            $response['deleted'] = true;
        else
            $response['deleted'] = false;


        $response['tmpID'] = $tmpID;

        return $response;
    }

    function generateRandomString($length = 22)
    {
        return substr(str_shuffle(str_repeat($x = '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ_$', ceil($length / strlen($x)))), 1, $length);
    }
}
