<?php
error_reporting(E_ERROR | E_PARSE);

if (!class_exists('Database')) require_once("crm_database.class.php");
if (!isset($crm_config)) require_once('crm_config.php');

class crm_hardlock
{
    public static $aktivalasihibak = array(
        'error: nincs ilyen password',  // ok
        'error: nem aktiv a program', // ok
        'error: nincs vasarlasi idopont', // ok
        'error: hianyzo kod', // ok
        'error: masik gep van regisztralva', // ok
        'error: nem aktiv regisztracio',
        'error: nincs kifizetve',
        'error: tul sok telepites',
        'error: unregister failed',
        'error: clear failed',
        'error: secondary server: not available function',
        'error: unable to lock floating license'
    );

    const INVALID_PASSWORD              = 0;
    const INACTIVE_PROGRAM              = 1;
    const SELLDATE_IS_MISSING           = 2;
    const MISSING_CODE                  = 3;
    const ANOTHER_MACHINE_REGISTERED    = 4;
    const INACTIVE_REGISTRATION         = 5;
    const IS_NOT_PAID                   = 6;
    const TOO_MANY_INSTALLATION         = 7;
    const UNREGISTER_FAILED             = 8;
    const CLEAR_FAILED                  = 9;
    const SERVER_DOWN                   = 10;

    public static $fixExpirationDates = array(
        '99' => array("+1 year", "+11 month", "+30 day", "", ""),   // Nonprofit: A kiadasi evet koveto December 31
        '97' => array("", "", "", "", ""),                          // Student: A kiadasi ev Januar 1-e (ez minden esetben letiltott lesz)
        '96' => array("+1 year", "+11 month", "+30 day", "", ""),   // Trial
        '94' => array("+1 year", "+11 month", "+30 day", "", ""),   // Namirial Nonprofit
        '93' => array("+1 year", "+11 month", "+30 day", "", ""),   // Namirial Trial
        '92' => array("+1 year", "+11 month", "+30 day", "", ""),   // BimLadder Nonprofit
        '91' => array("+1 year", "+11 month", "+30 day", "", "")    // BimLadder Trial
    );

    public static $win10creator_old_isid;

    const CHECKED_DATE_FALSE    = 0;
    const CHECKED_DATE_OK       = 4;

    public function __construct()
    {
        self::$aktivalasihibak          = $GLOBALS['aktivalasihibak'];
        self::$win10creator_old_isid    = $GLOBALS['win10creator_old_isid'];
    }

    public static function isEmergencyServer($secondary)
    {
        return isset($secondary) && $secondary == 'true';
    }

    public static function isServerActive()
    {
        $file = $_SERVER['DOCUMENT_ROOT'] . "/activateServer.txt";
        $myfile = fopen($file, "r");

        $text = 'Inactive';
        $i = 0;

        while (!$myfile && $i < 10) {
            $myfile = fopen($file, "r");
            $i++;
        }

        if ($myfile) {
            $text = fread($myfile, filesize($file));
            fclose($myfile);
        }

        return $text == 'Active';
    }

    public static function isPrimaryServerAlive()
    {
        if ($_SERVER['SERVER_NAME'] == 'www.archline.hu' || $_SERVER['SERVER_NAME'] == 'www.archlinexp.com') {
            // TODO: logolni
            die();
        }


        if (crm_hardlock::isServerActive()) {
            return false;
        }

        $file = $_SERVER['DOCUMENT_ROOT'] . "/serverAlive.txt";
        $myfile = fopen($file, "r");

        $text = 'Failed';
        $i = 0;

        while (!$myfile && $i < 10) {
            $myfile = fopen($file, "r");
            $i++;
        }

        if ($myfile) {
            $text = fread($myfile, filesize($file));
            fclose($myfile);
        }

        return $text == 'Success';
    }

    public static function secondaryServerLogRefused($password, $function, $description)
    {
        if ($_SERVER['SERVER_NAME'] == 'secondary.cadline.hu') {
            $activity = array(
                'password'      => $password,
                'ip'            => $_SERVER['REMOTE_ADDR'],
                'time'          => time(),
                'function_id'   => $function,
                'description'   => $description
            );
            Database::getInstance()->insert("local_crm.activity_refused", $activity, 'ssiis');
        }
    }

    public static function secondaryServerLog($password, $function, $description)
    {
        if ($_SERVER['SERVER_NAME'] == 'secondary.cadline.hu') {
            $activity = array(
                'password'      => $password,
                'ip'            => $_SERVER['REMOTE_ADDR'],
                'time'          => time(),
                'function'      => $function,
                'description'   => $description
            );
            Database::getInstance()->insert("local_crm.activity", $activity, 'ssiss');
        }
    }

    public static function GetFileTransferCredentials($verID, $region = '')
    {
        /*if ($region == 'cn') {
            $credentials['protocol']    = 'webdav';
            $credentials['server']      = 'https://dav-gstarbim.gstarcad.cn';
            //$credentials['account']     = 'uploader';
            //$credentials['pwd']         = 'b5hWKhG5';

            $credentials['account']     = 'admin';
            $credentials['pwd']         = 'jow3YR98vBh6';

            return $credentials;
        }*/

        if ($verID >= 39) {
            $credentials['protocol']    = 'webdav';
            $credentials['server']      = 'https://webdav.cadline.hu';
            $credentials['account']     = 'webdavuser';
            $credentials['pwd']         = 'il2ieGhohz';
        } else {
            $credentials                = array();
            $credentials['protocol']    = 'ftp';
            $credentials['server']      = 'ftp://tanis2.cadline.hu';
            $credentials['account']     = 'vendeg';
            $credentials['pwd']         = 'vendeg';
        }

        return $credentials;
    }

    public static function getTestClass($result = '')
    {
        switch ($result) {
            case 'passed':
                $class = 'table-success';
                break;

            case 'no_results':
                $class = 'table-warning';
                break;

            case 'skipped':
                $class = 'table-warning';
                break;

            default:
                $class = 'table-danger';
                break;
        }

        return $class;
    }

    public static function getProductNameByID($id)
    {
        $product = '';

        switch ($id) {
            case 0:
                $product = 'LT';
                break;

            case 1:
                $product = 'Professional';
                break;

            default:
                $product = 'LIVE';
                break;
        }

        return $product;
    }

    public static function tryToAlloc($min = 990001, $max = 999999)
    {
        $ret = 0;
        if ((int)$min == 0 || (int)$max == 0) return $ret;

        $query = "SELECT LEFT(hlNum, 6) AS hlNum FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum >= " . (int)$min . " AND hlNum < " . (int)$max . " ORDER BY hlNum;";
        crm_database::getInstance()->query($query);
        $res = crm_database::getInstance()->fetchAssoc();

        if (count($res)) {
            $keszlet = range($min, $max);
            foreach ($res as $r) $kulcsok[] = $r['hlNum'];

            $diff = array_diff($keszlet, $kulcsok);
            reset($diff);
            $ret = current($diff);
        }
        return $ret;
    }

    public static function getVerIDFrom8and9($ver8and9)
    {
        $query = "SELECT * FROM `cl_hlusers`.`p_versions` WHERE verPassword8and9 = ?";
        Database::getInstance()->query($query, array('i', $ver8and9));
        $res = Database::getInstance()->fetchNext();

        return $res['verID'];
    }

    public static function GetActivationError($error_code)
    {
        return self::$aktivalasihibak[$error_code];
    }

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

    // Ez csak a class/selectprogram.class.php-ban hivodik, de az is hibas
    // A programbol a Namirial es BIMLadder trial visszavaltaskor volt hasznalva
    public static function GetAllPrograms($nUserID, $product_id, $verid)
    {
        $query = "SELECT h_programs.prPass AS prHlNum, h_programs.prActCode AS prActCode, h_programs.prTypeID AS prTypeID, h_programs.prVerID AS prVerID, h_programs.prLan AS prLan
            FROM `" . CRMADATBAZIS . "`.`hardlock`
            INNER JOIN `" . CRMADATBAZIS . "`.`h_programs` ON h_programs.prHlNum = hardlock.hlNum
            WHERE hardlock.hlUser = ? AND hardlock.product_id = ? AND hardlock.hlStat != ? AND h_programs.prVerID = ?";
        Database::getInstance()->query($query, array('sii', $nUserID, $product_id, _Hardlock::Blank, $verid));
        $res = Database::getInstance()->fetchAssoc();

        return $res;
    }

    public static function getLanName($lan, $ctrID)
    {
        $resource = new Resource($ctrID);

        return $lan == 0 ? $resource->getDlgString(1290) : $lan . ' ' . $resource->getDlgString(3347);
    }

    public static function getProgramType($serial, $prTypeID, $ctrID, $floating)
    {
        $resource = new Resource($ctrID);

        $type = crm_hardlock::GetTypeName($prTypeID);
        $type .= ' (';
        $isFloating = $floating == 1;

        if (!Codegen::IsSoftwareKey($serial))
            $type .= $resource->getDlgString(11807);

        if (Codegen::IsSoftwareKey($serial) && $isFloating)
            $type .= ucfirst($resource->getDlgString(11444));

        if (Codegen::IsSoftwareKey($serial) && !$isFloating)
            $type .= $resource->getDlgString(11806);

        $type .= ')';

        return $type;
    }

    public static function isProgramAvailable($currentSerial, $dbSerial, $prFizetve, $isid, $lan, $prActCode, &$availabelProgram, &$freeLic, $ctrID, $floating)
    {
        if ($prFizetve < date('Y-m-d'))
            return false;

        $isSoftwareKey  = Codegen::IsSoftwareKey($dbSerial);
        $isFloating     = $isSoftwareKey ? $floating == 1 : false;

        if ($isSoftwareKey) {
            $resource = new Resource($ctrID);
        }

        if ($isSoftwareKey && $lan > 0 && !$isFloating) {
            $query = "SELECT prActCode FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ?";
            Database::getInstance()->query($query, array('s', $dbSerial));
            $programs = Database::getInstance()->fetchAssoc();

            $i = 0;
            $j = 0;
            foreach ($programs as $program) {
                $prgIsid = crm_hardlock::GetIsidFromTimeCode($program['prActCode']);
                if ($program['prActCode'] != '' && $prgIsid != $isid) {
                    $i++;
                }

                if ($program['prActCode'] == '') {
                    $j++;
                }
            }

            if (!empty($programs) && count($programs) == $i) {
                $availabelProgram = $resource->getDlgString(11809);
                return false;
            }

            $freeLic = $resource->getDlgString(11805) . ' ' . $j;
        }

        if ($currentSerial == $dbSerial) {
            if ($isSoftwareKey && $lan == 0) {
                $computer = crm_hardlock::GetComputerInfo($dbSerial, $isid);
                $availabelProgram = $computer['custom_name'] != '' ? $computer['custom_name'] : $computer['computer_name'];

                if ($availabelProgram == '') {
                    $availabelProgram = $resource->getDlgString(11812);
                }
            }
            return false;
        }

        if ($isSoftwareKey && $lan == 0 && $prActCode != '' && !$isFloating) {
            $prgIsid = crm_hardlock::GetIsidFromTimeCode($prActCode);
            if ($prgIsid != $isid) {
                $computer = crm_hardlock::GetComputerInfo($dbSerial, $prgIsid);
                $compName = $computer['custom_name'] != '' ? $computer['custom_name'] : $computer['computer_name'];
                if ($compName == '') {
                    $compName = $resource->getDlgString(11812);
                }

                $availabelProgram = sprintf($resource->getDlgString(11811), $compName);
                return false;
            }
        }

        return true;
    }

    public static function getUserData($nUserID)
    {
        $query = "SELECT * FROM `" . JMLADATBAZIS . "`.`jml_users` WHERE nUserID = ?;";
        Database::getInstance()->query($query, array('i', $nUserID));
        $res = Database::getInstance()->fetchNext();

        return $res;
    }

    public static function getRemainingCredits($nUserID)
    {
        $query = "SELECT * FROM mobileview.ai_render_credits WHERE nUserID = ?;";
        Database::getInstance()->query($query, array('i', $nUserID));
        $res = Database::getInstance()->fetchNext();

        if (empty($res)) {
            return 3;
        }

        return (int)$res['credit'];
    }

    public static function GetComputerInfoByNpid($npid)
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`computers` WHERE computer_id = ?";
        Database::getInstance()->query($query, array('s', $npid));
        $res = Database::getInstance()->fetchNext();

        return $res;
    }

    public static function GetComputerInfo($serial, $isid)
    {
        $res = array();

        $query = "SELECT computer_id FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ?";
        Database::getInstance()->query($query, array('ss', $serial, $isid));
        $p_serial_isid = Database::getInstance()->fetchNext();

        if (!empty($p_serial_isid)) {
            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`computers` WHERE id = ?";
            Database::getInstance()->query($query, array('i', $p_serial_isid['computer_id']));
            $res = Database::getInstance()->fetchNext();
        }

        return $res;
    }

    public static function getInfoForProgramDialog($prFizetve, $availabelProgram, $freeLic, $ctrID)
    {
        $msg = '';

        if ($prFizetve < date('Y-m-d')) {
            $resource = new Resource($ctrID);
            $msg = $resource->getDlgString(11804);
        }

        if ($availabelProgram != '')
            $msg = $availabelProgram;

        if ($freeLic != '')
            $msg = $freeLic;

        return $msg;
    }

    public static function GetAllProgramsFromUser($nUserID, $product_id, $verid, $specEdition = '', $currentSerial = '')
    {
        if ($nUserID < 0) {
            $userID = abs($nUserID);
            $query = "SELECT custom_lic.serial AS prPass FROM cl_hlusers.custom_login_licenses custom_lic WHERE custom_lic.id = ? AND custom_lic.serial != ''";

            Database::getInstance()->query($query, array('i', $userID));
        } else {
            if ($specEdition == 'LT') {
                $query = "SELECT h_programs.prPass AS prPass, h_programs.prVerID AS prVerID, hardlock.hlLan AS hlLan, h_programs.prTypeID AS prTypeID, 
                        h_programs.prFizetve AS prFizetve, h_programs.prActCode AS prActCode, hardlock.isFloating AS isFloating
                    FROM " . CRMADATBAZIS . ".hardlock
                    LEFT JOIN " . CRMADATBAZIS . ".h_programs ON h_programs.prHlNum = hardlock.hlNum
                    LEFT JOIN cl_hlusers.hardlock_borrow borrow ON borrow.borrow_id = hardlock.borrow
                    WHERE h_programs.prTypeID = ? AND h_programs.prVerID = ? 
                        AND (hardlock.hlUser = ? AND (hardlock.borrow IS NULL OR hardlock.borrow = 0 OR hardlock.hlLan > 1) OR borrow.userID = ?)
                        AND hardlock.product_id = ? AND hardlock.hlStat = ?
                    GROUP BY h_programs.prPass";

                if ($currentSerial != '') {
                    $query .= " ORDER BY 
                        CASE
                            WHEN h_programs.prPass = '" . $currentSerial . "' THEN 1
                            ELSE 2
                        END, hardlock.hlLan ASC";
                }

                Database::getInstance()->query($query, array('iiiiii', 4, $verid, $nUserID, $nUserID, $product_id, _Hardlock::Active));
            } else {
                $query = "SELECT h_programs.prPass AS prPass, h_programs.prVerID AS prVerID, hardlock.hlLan AS hlLan, h_programs.prTypeID AS prTypeID,
                        h_programs.prFizetve AS prFizetve, h_programs.prActCode AS prActCode, hardlock.isFloating AS isFloating
                    FROM " . CRMADATBAZIS . ".hardlock
                    LEFT JOIN " . CRMADATBAZIS . ".h_programs ON h_programs.prHlNum = hardlock.hlNum
                    LEFT JOIN cl_hlusers.hardlock_borrow borrow ON borrow.borrow_id = hardlock.borrow
                    WHERE h_programs.prTypeID != 4 AND h_programs.prVerID = ?  
                        AND (hardlock.hlUser = ? AND (hardlock.borrow IS NULL OR hardlock.borrow = 0 OR hardlock.hlLan > 1) OR borrow.userID = ?)
                        AND hardlock.product_id = ? AND hardlock.hlStat = ?
                    GROUP BY h_programs.prPass";

                if ($currentSerial != '') {
                    $query .= " ORDER BY 
                        CASE
                            WHEN h_programs.prPass = '" . $currentSerial . "' THEN 1
                            ELSE 2
                        END, hardlock.hlLan ASC";
                }

                Database::getInstance()->query($query, array('iiiii', $verid, $nUserID, $nUserID, $product_id, _Hardlock::Active));
            }
        }

        $res = Database::getInstance()->fetchAssoc();

        return $res;
    }

    public static function getInstallerParam($string, $param)
    {
        $start = '<{' . $param . ':';
        $end = '}>';
        $string = ' ' . $string;
        $ini = strpos($string, $start);
        if ($ini == 0) return '';
        $ini += strlen($start);
        $len = strpos($string, $end, $ini) - $ini;
        return substr($string, $ini, $len);
    }

    public static function getMirrorFromUrl($url)
    {
        $mirror = 'hungary';

        if (strpos($url, 'download-mirror.s3.amazonaws.com') !== false) {
            $mirror = 'usa';
        }

        if (strpos($url, 'downloadmirror.blob.core.windows.net') !== false) {
            $mirror = 'korea';
        }

        return $mirror;
    }

    public static function updateMirror($compID, $mirror)
    {
        $query = "UPDATE cl_hlusers.computers SET mirror = ? WHERE id = ?";
        Database::getInstance()->query($query, array('si', $mirror, $compID));
    }

    public static function GetActCode($serial)
    {
        $query = "SELECT * FROM `cl_hlusers`.`h_programs` WHERE prPass = ?";
        Database::getInstance()->query($query, array('s', $serial));
        $res = Database::getInstance()->fetchNext();

        return $res['prActCode'];
    }

    public static function ResetKey($hlNum)
    {
        $query = "SELECT prPass FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prHlNum = ?";
        Database::getInstance()->query($query, array('s', $hlNum));
        $res = Database::getInstance()->fetchAssoc();

        foreach ($res as $r) self::DeleteProgramFromPassword($r["prPass"]);

        Database::getInstance()->delete(CRMADATBAZIS . ".nonprofit", array('hlID' => $hlNum), 's');
        Database::getInstance()->delete(CRMADATBAZIS . ".h_history", array('hiHlNum' => $hlNum), 's');

        $query = "UPDATE `" . CRMADATBAZIS . "`.`hardlock` SET hlStat = ?, hlUser = '', hlDateIssue = '', hlManID = ?, hlTime = ?, hlDatePro = ? WHERE hlNum = ?";
        Database::getInstance()->query($query, array('iis', _Hardlock::Blank, 0, $hlNum));
    }

    public static function DeleteProgram($hlNum)
    {
        Database::getInstance()->delete(CRMADATBAZIS . ".h_history", array('hiHlNum' => $hlNum), 's');
        Database::getInstance()->delete(CRMADATBAZIS . ".h_programs", array('prHlNum' => $hlNum), 's');
        Database::getInstance()->delete(CRMADATBAZIS . ".h_aktivalas_infok", array('kulcs' => $hlNum), 's');
        Database::getInstance()->query("DELETE FROM `" . CRMADATBAZIS . "`.`nonprofit` WHERE `expire_datetime`<DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -2 YEAR);");
    }

    public static function DeleteProgramFromPassword($password)
    {
        self::ClearProgram($password);
        Database::getInstance()->query("DELETE FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ?", array('s', $password));
    }

    public static function ClearProgram($password)
    {
        Database::getInstance()->delete(CRMADATBAZIS . ".p_serial_isid", array('serial' => $password), 's');
        Database::getInstance()->delete(CRMADATBAZIS . ".userstats2013", array('usPwd' => $password), 's');
        Database::getInstance()->query("UPDATE `" . CRMADATBAZIS . "`.`h_programs` SET prActCode = '' WHERE prPass = ?", array('s', $password));

        Database::getInstance()->query("SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ?", array('s', $password));
        $res = Database::getInstance()->fetchAssoc();

        return empty($res);
    }

    public static function ListExpiredPrograms($date, $db)
    {
        if ($db == 'hu') {
            $query = "SELECT p.prHlNum AS hlNum, p.prFizetve, p.prPass AS prPass FROM
                (SELECT * FROM cl_hlusers.h_programs WHERE h_programs.prPass LIKE ? AND h_programs.prHlNum IN
                    (SELECT hardlock.hlNum FROM cl_hlusers.hardlock WHERE hardlock.hlCtrID = ?) ORDER BY prFizetve desc) p
                GROUP BY p.prHlNum having p.prFizetve < ? LIMIT 200";
        } else {
            $query = "SELECT p.prHlNum AS hlNum, p.prFizetve, p.prPass AS prPass FROM
                (SELECT * FROM cl_hlusers.h_programs WHERE h_programs.prPass LIKE ? AND h_programs.prHlNum IN
                    (SELECT hardlock.hlNum FROM cl_hlusers.hardlock WHERE hardlock.hlCtrID != ?)
                ORDER BY prFizetve desc) p GROUP BY p.prHlNum having p.prFizetve < ? LIMIT 200";
        }

        Database::getInstance()->query($query, array('sis', "99%", 36, $date));
        $res = Database::getInstance()->fetchAssoc();

        return $res;
    }

    public static function GetLastRunningDateFromPassword($password)
    {
        $query = "SELECT usDate FROM `" . CRMADATBAZIS . "`.`userstats2013` WHERE usPwd = ? ORDER BY usDate DESC";
        Database::getInstance()->query($query, array('s', $password));
        $res = Database::getInstance()->fetchNext();

        return $res['usDate'];
    }

    public static function GetUserFromHlNum($hlNum)
    {
        $query = "SELECT hlUser FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum = ?";
        Database::getInstance()->query($query, array('s', $hlNum));
        $res = Database::getInstance()->fetchNext();

        if (!is_numeric($res['hlUser'])) return $res['hlUser'];

        $query = "SELECT strName FROM `" . CRMADATBAZIS . "`.`users` WHERE nUserID = ?";
        Database::getInstance()->query($query, array('i', $res['hlUser']));
        $res2 = Database::getInstance()->fetchNext();

        return $res2['strName'];
    }

    public static function GetHlUser($hlNum)
    {
        $query = "SELECT hlUser FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum = ?";
        Database::getInstance()->query($query, array('s', $hlNum));
        $res = Database::getInstance()->fetchNext();

        return $res['hlUser'];
    }

    public static function GetActiveRegistration($password, $isid)
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ? AND aktiv = ?";
        Database::getInstance()->query($query, array('ssi', $password, $isid, 1));
        $res = Database::getInstance()->fetchNext();

        return $res;
    }

    public static function GetHardlockData($hlNum)
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum = ?";
        Database::getInstance()->query($query, array('s', $hlNum));
        $res = Database::getInstance()->fetchNext();

        return $res;
    }

    public static function GetUserFromPassword($password)
    {
        $hlnum = self::GetHlnumFromPassword($password);

        $query = "SELECT hlUser FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum = ?";
        Database::getInstance()->query($query, array('s', $hlnum));
        $res = Database::getInstance()->fetchNext();

        return $res['hlUser'];
    }

    public static function getProgramsFromHardlock($hlNum, $verID = 0)
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prHlNum = ?";
        if ($verID != 0) {
            $query .= " AND prVerID = ?";
            Database::getInstance()->query($query, array('si', $hlNum, $verID));
        } else {
            Database::getInstance()->query($query, array('s', $hlNum));
        }
        $res = Database::getInstance()->fetchAssoc();

        return $res;
    }

    public static function getProgramStatus($status, $password)
    {
        if (($status & Status::sCracked) == Status::sCracked) return "Cracked ({$status})";
        if (($status & Status::sDisabled) == Status::sDisabled) return "Program is disabled ({$status})";

        $result = "";

        if (($status & Status::sHW_keyOK) == Status::sHW_keyOK) $result .= "HW OK, ";
        if (($status & Status::sNotCommercialProgram) == Status::sNotCommercialProgram) $result .= "Not com. program, ";

        if (crm_hardlock::ActiveRegistrationRequired($password)) {
            if (($status & Status::sActiveRegistration) != Status::sActiveRegistration) $result .= "Not act. registration,";
        }

        if (($status & Status::sUnlimited) == Status::sUnlimited) $result .= "Unlimited, ";
        if (($status & Status::sFixTimeLimitReached) == Status::sFixTimeLimitReached) $result .= "The program reached the fixed expiration date, ";

        $result  = rtrim($result, ', ');
        $result .= " ({$status})";
        return $result;
    }

    public static function GetHlnumFromPassword($password)
    {
        return substr($password, 0, 6);
    }

    public static function GetIsidFromTimeCode($idokod)
    {
        if (!Codegen::IsValidTimeCode($idokod)) return '';

        $arrIdokod = explode("-", $idokod);

        return $arrIdokod[1];
    }

    public static function GetIsidFromPasswordForHardwareKeys($password)
    {
        return substr($password, 0, 2) . substr($password, 7, 2);
    }

    public static function GetVerCodeFromverId($verid)
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_versions` WHERE verID = ?";
        Database::getInstance()->query($query, array('i', $verid));
        $versions = Database::getInstance()->fetchNext();

        return $versions['verCode'];
    }

    public static function GetVer8And9FromVerId($verid)
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_versions` WHERE verID = ?";
        Database::getInstance()->query($query, array('i', $verid));
        $versions = Database::getInstance()->fetchNext();

        return $versions['verPassword8and9'];
    }

    public static function GetVer8And9FromPassword($password)
    {
        return substr($password, 7, 2);
    }
    public static function GetTypeFromPassword($password)
    {
        return substr($password, 6, 1);
    }
    public static function GetLanSignFromPassword($password)
    {
        return substr($password, 8, 1);
    }
    public static function GetCountryFromPassword($password)
    {
        return substr($password, 0, 2);
    }

    public static function GetLaunchYearFromVer8And9($ver8and9)
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_versions` WHERE verPassword8and9 = ?";
        Database::getInstance()->query($query, array('i', $ver8and9));
        $versions = Database::getInstance()->fetchNext();

        return $versions["year"];
    }

    public static function GetVerIdFromPassword($password)
    {
        $ver = self::GetVer8And9FromPassword($password);

        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_versions` WHERE verPassword8and9 = ?";
        Database::getInstance()->query($query, array('i', $ver));
        $versions = Database::getInstance()->fetchNext();

        return $versions["verID"];
    }

    public static function GetVerName($verid)
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_versions` WHERE verID = ?";
        Database::getInstance()->query($query, array('i', $verid));
        $res = Database::getInstance()->fetchNext();

        return $res['verName'];
    }

    public static function GetTypeName($typeid)
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_types` WHERE typeCode = ?";
        Database::getInstance()->query($query, array('i', $typeid));
        $res = Database::getInstance()->fetchNext();

        return $res['typeName'];
    }

    public static function IsLiveKey($password = "")
    {
        return (substr($password, 6, 1) == 'L');
    }

    public static function IsLTkey($password)
    {
        (int)$ver = self::GetVer8And9FromPassword($password);
        return (substr($password, 6, 1) == '4' && $ver >= 36); // 2019-es evtol letezik az LT (ha netan megszunik, akkor ki kell egesziteni egy vegleges verzioszammal is)
    }

    public static function checkProgramType($type, $password)
    {
        $password_type = crm_hardlock::GetTypeFromPassword($password);

        if (strlen($type) > 1 && !self::IsLiveKey($password)) return true;

        if (self::IsLiveKey($password) && $type != 76) return false;

        if (self::IsLiveKey($password)) {
            if ($type != 76) return false;
            else return true;
        } else {
            if ($type == $password_type) return true;
            else return false;
        }
    }

    public static function checkProgramVersion($verid, $password)
    {
        $ver8and9 = self::GetVer8And9FromPassword($password);

        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_versions` WHERE verID = ?";
        Database::getInstance()->query($query, array('i', $verid));
        $p_version = Database::getInstance()->fetchNext();

        if ($p_version['verPassword8and9'] != $ver8and9) return false;

        return true;
    }

    public static function checkActDate($password, $isid)
    {
        $softwareKey = Codegen::IsSoftwareKey($password);

        $query = "SELECT twtw, prFizetve FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ? AND prActCode LIKE ?";
        Database::getInstance()->query($query, array('ss', $password, "%-{$isid}-%"));
        $program = Database::getInstance()->fetchNext();

        $query = "SELECT datum FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ?";
        Database::getInstance()->query($query, array('ss', $password, $isid));
        $p_serial_isid = Database::getInstance()->fetchNext();

        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`userstats2013` WHERE usPwd = ? AND usISID = ? ORDER BY usDate DESC";
        Database::getInstance()->query($query, array('ss', $password, $isid));
        $usstats = Database::getInstance()->fetchNext();

        if (
            $softwareKey && !empty($p_serial_isid) && $program['twtw'] == 0 && $program['prFizetve'] != '2050-01-01' &&
            Codegen::GetDayInterval(substr($p_serial_isid['datum'], 0, 10), $usstats['usTelepitesiDatum']) > 2
        ) {
            return 'error: nem egyezik meg a telepitesi datum: p_serial_isid: ' . $p_serial_isid['datum'] . ' | userstats2013: ' . $usstats['usTelepitesiDatum'];
        }
        return true;
    }

    public static function GetProgramData($password)
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ?";
        Database::getInstance()->query($query, array('s', $password));
        $program = Database::getInstance()->fetchNext();

        return $program;
    }

    public static function GetInstallCodeFromInstallDate($password, $isid, $fizetve, $isTwTw)
    {
        if ($fizetve == '2050-01-01') $dayInterval = 3583;
        else {
            if ($isTwTw) $installDate = crm_hardlock::getTwTwRootTimeString();
            else {
                if (Codegen::IsSoftwareKey($password)) {
                    $reg            = crm_hardlock::GetActiveRegistration($password, $isid);
                    $installDate    = $reg['datum'];
                } else {
                    $program        = crm_hardlock::GetProgramData($password);
                    $installDate    = $program['prSellDate'];
                }
            }

            $now            = date("Y-m-d");
            $dayInterval    = Codegen::GetDayInterval(substr($installDate, 0, 10), $now);
        }

        $installCode = Codegen::GenerateTimeCode($password, $isid, $dayInterval);

        return $installCode;
    }

    public static function getComputerIdFromNpid($npid)
    {
        $query = "SELECT id FROM `" . CRMADATBAZIS . "`.`computers` WHERE computer_id = ?";
        Database::getInstance()->query($query, array('s', $npid));
        $res = Database::getInstance()->fetchNext();

        return $res['id'];
    }

    public static function GetNpid($computerID)
    {
        $query = "SELECT computer_id FROM `" . CRMADATBAZIS . "`.`computers` WHERE id = ?";
        Database::getInstance()->query($query, array('i', $computerID));
        $res = Database::getInstance()->fetchNext();

        return $res['computer_id'];
    }

    public static function AddOrUpdateComputer($npID, $comp_name)
    {
        $query = "SELECT id FROM `" . CRMADATBAZIS . "`.`computers` WHERE computer_id = ?";
        Database::getInstance()->query($query, array('s', $npID));
        $res4 = Database::getInstance()->fetchNext();

        if (empty($res4)) {
            $comp = array(
                'computer_id'   => $npID,
                'computer_name' => $comp_name,
                'last_mod'      => date('Y-m-d', time())
            );
            return Database::getInstance()->insert(CRMADATBAZIS . ".computers", $comp, 'sss');
        } else {
            if (empty($comp_name)) { //- ha nincs szamitogep nev, akkor nem kell updatelni
                Database::getInstance()->update(
                    CRMADATBAZIS . ".computers",
                    array(
                        'last_mod'    => date('Y-m-d', time())
                    ),
                    array('id'    => $res4['id']),
                    'si'
                );
            } else {
                Database::getInstance()->update(
                    CRMADATBAZIS . ".computers",
                    array(
                        'last_mod'        => date('Y-m-d', time()),
                        'computer_name'   => $comp_name
                    ),
                    array('id'        => $res4['id']),
                    'ssi'
                );
            }
            return $res4['id'];
        }
    }

    public static function GetDealers()
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`distributors` d WHERE d.enabled = 1 AND d.ctrID IS NOT NULL AND d.ctrID != 37 ORDER BY d.order DESC, d.name ASC";
        Database::getInstance()->query($query);
        $res = Database::getInstance()->fetchAssoc();

        return $res;
    }

    public static function getPackage($year, $language)
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`packages` WHERE (year = ? OR year LIKE ?) AND (language = ? OR language LIKE ?) AND enabled = ?";
        Database::getInstance()->query($query, array('ssssi', '*', "%{$year};%", '*', "%{$language};%", 1));
        $res = Database::getInstance()->fetchAssoc();

        return $res;
    }

    public static function AddHardlockComputer($hlnum, $compid, $verid)
    {
        if ($verid >= 36) {
            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`hardlock_computers` WHERE hlNum = ? AND computer_id = ?";
            Database::getInstance()->query($query, array('si', $hlnum, $compid));
            $res = Database::getInstance()->fetchNext();

            if (empty($res)) {
                $comp = array(
                    'hlNum'         => $hlnum,
                    'computer_id'   => $compid
                );
                Database::getInstance()->insert(CRMADATBAZIS . ".hardlock_computers", $comp, 'si');
            }
        }
    }

    public static function CheckLanFromPassword($password)
    {
        $lan        = 0;
        $lanSign    = self::GetLanSignFromPassword($password);
        $hlNum      = self::GetHlnumFromPassword($password);

        $query = "SELECT hlLan FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum = ?";
        Database::getInstance()->query($query, array('s', $hlNum));
        $res    = Database::getInstance()->fetchNext();
        $users  = $res['hlLan'];

        $lan = self::GetLanSignFromLanUserNumber($users);

        if ($lan != $lanSign); //update
    }

    public static function GetLanSignFromLanUserNumber($users)
    {
        $lan = 0;

        if ($users == 0) $lan = 0;
        elseif ($users == 1) $lan = 1;
        elseif ($users == 2) $lan = 2;
        elseif ($users == 3) $lan = 3;
        elseif ($users == 4) $lan = 4;
        elseif ($users == 5) $lan = 5;
        elseif ($users == 10) $lan = 6;
        elseif ($users == 15) $lan = 7;
        elseif ($users == 20) $lan = 8;
        elseif ($users <= 49) $lan = 9;

        return $lan;
    }

    public static function AddToDebugTable($message, $session_id = '')
    {
        $debug = array(
            'data'          => $message,
            'insert_date'   => date('Y-m-d H:i:s'),
            'session_id'    => $session_id
        );
        Database::getInstance()->insert(CRMADATBAZIS . ".debug_table", $debug, 'sss');
    }

    public static function AddNonprofit($tomb = array(), $bind_type)
    {
        Database::getInstance()->insert(CRMADATBAZIS . ".nonprofit", $tomb, $bind_type);
    }

    public static function AddHardlock($tomb = array(), $bind_type)
    {
        Database::getInstance()->insert(CRMADATBAZIS . ".hardlock", $tomb, $bind_type);
    }

    public static function AddProgram($tomb = array(), $bind_type)
    {
        return Database::getInstance()->insert(CRMADATBAZIS . ".h_programs", $tomb, $bind_type);
    }

    public static function GetCustomUser($userID)
    {
        $query = "SELECT * FROM cl_hlusers.custom_login_licenses lic WHERE lic.id = ? LIMIT 1";
        Database::getInstance()->query($query, array('i', $userID));
        $user = Database::getInstance()->fetchNext();

        return $user;
    }



    public static function GetRegistration($user, $npid, $isTrial, $ver8and9, $custom)
    {
        if ($user->serial != '') {
            // Ha van a usernek serial-ja, akkor nem kell vele semmit csinalni
            $query = "SELECT * FROM cl_hlusers.custom_login_licenses lic WHERE lic.serial = ? AND lic.name = ? LIMIT 1";
            Database::getInstance()->query($query, array('ss', $user->serial, $user->name));
            $checked_user = Database::getInstance()->fetchNext();

            if ($checked_user)
                return $checked_user['id'];
        }

        if ($isTrial == 'true') { // Ha trial van, akkor meg kell nezni, hogy van-e trial az adott evhez es emailhez
            $query = "SELECT * FROM cl_hlusers.custom_login_licenses lic WHERE lic.email = ? AND ver8and9 = ?";
            Database::getInstance()->query($query, array('si', $user->email, $ver8and9));
            $checked_user = Database::getInstance()->fetchNext();

            if ($checked_user) // Ha van, visszaadjuk a user ID-t
                return $checked_user['id'];
        }

        // Uj user letrehozasa
        $new_user  = array(
            "serial"            => trim($user->serial),
            "name"              => $user->name,
            "email"             => $user->email,
            "date"              => date('Y-m-d'),
            "npid"              => $npid,
            "ver8and9"          => $ver8and9,
            "custom_login_id"   => $custom,
            "custom_id"         => $user->crmID
        );

        return Database::getInstance()->insert('cl_hlusers.custom_login_licenses', $new_user, 'sssssiss');
    }

    public static function GetOrGenerateToken($seatID, $nUserID)
    {
        $date = date('Y-m-d');

        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`archline_tokens` WHERE seatID = ? AND nUserID = ?";
        Database::getInstance()->query($query, array('si', $seatID, $nUserID));
        $token = Database::getInstance()->fetchNext();

        if (empty($token)) {
            $tokenID = crm_hardlock::generateRandomString();

            $query = Database::getInstance()->insert(
                CRMADATBAZIS . ".archline_tokens",
                array(
                    "tokenID"       => $tokenID,
                    "seatID"        => $seatID,
                    "nUserID"       => $nUserID,
                    "lastValidate"  => $date
                ),
                'ssis'
            );
        } else {
            Database::getInstance()->update(
                CRMADATBAZIS . ".archline_tokens",
                array(
                    'lastValidate' => $date
                ),
                array('tokenID' => $token['tokenID']),
                'ss'
            );

            $tokenID = $token['tokenID'];
        }

        return $tokenID;
    }

    public static function GetUserIdFromToken($token)
    {
        if ($token == '')
            return false;

        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`archline_tokens` WHERE tokenID = ? LIMIT 1;";
        Database::getInstance()->query($query, array('s', $token));
        $token = Database::getInstance()->fetchNext();

        return empty($token) ? false : $token['nUserID'];
    }

    public static function NotGeneratedNonprofitKeyIntervals($minHlNum, $maxHlNum)
    {
        $query = "SELECT LEFT(hlNum,6) AS hlNum FROM `cl_hlusers`.`hardlock` WHERE hlNum >= ? AND hlNum < ? ORDER BY hlNum";
        Database::getInstance()->query($query, array('ss', $minHlNum, $maxHlNum));
        $res = Database::getInstance()->fetchAssoc();

        $allHlNums = range($minHlNum, $maxHlNum);
        foreach ($res as $r) $usedHlNums[] = $r['hlNum'];

        $notUsedHlNums = array_diff($allHlNums, $usedHlNums);

        $ranges[] = array(); // initial value

        foreach ($notUsedHlNums as $number) {
            $range    = array_pop($ranges);
            $extend   = ($range[1] == $number - 1);
            $ranges[] = array($range[0], $extend ? $number : $range[1]);

            if (!$extend) $ranges[] = array($number, $number);
        }

        return $ranges;
    }

    public static function GenerateNonProfitKeys($mettol, $meddig)
    {
        if (!empty($meddig)) {
            for ($mettol; $mettol <= $meddig; $mettol++) {
                $rec                = new stdClass();
                $rec->hlNum         = $mettol;
                $rec->hlLan         = 0;
                $rec->hlStat        = 0;
                $rec->hlManID       = 36;
                $rec->hlTime        = date("Y-m-d H:i:s", time());
                $rec->bUjithato     = 1;
                $rec->product_id    = 1;
                Database::getInstance()->insert('hardlock', $rec, 'iiiisii');
            }
        } elseif (empty($meddig) && !empty($mettol)) {
            $rec                = new stdClass();
            $rec->hlNum         = $mettol;
            $rec->hlLan         = 0;
            $rec->hlStat        = 0;
            $rec->hlManID       = 36;
            $rec->hlTime        = date("Y-m-d H:i:s", time());
            $rec->bUjithato     = 1;
            $rec->product_id    = 1;
            Database::getInstance()->insert('hardlock', $rec, 'iiiisii');
        }
    }

    public static function GetTheFirstNotUsedHlNumFromInterval($minHlNum = 0, $maxHlNum = 0)
    {
        $ret = 0;
        if ((int)$minHlNum == 0 || (int)$maxHlNum == 0) return ($ret);

        $query = "SELECT LEFT(hlNum, 6) AS hlNum FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlStat != ? AND hlNum >= ? AND hlNum < ? ORDER BY hlNum";
        Database::getInstance()->query($query, array('iis', 0, $minHlNum, $maxHlNum));
        $res = Database::getInstance()->fetchAssoc();

        if (count($res)) {
            $allHlNums = range($minHlNum, $maxHlNum);
            foreach ($res as $r) $usedHlNums[] = $r['hlNum'];

            $notUsedHlNums = array_diff($allHlNums, $usedHlNums);
            reset($notUsedHlNums);
            $ret = current($notUsedHlNums);
        } else $ret = 0;

        return $ret;
    }

    public static function GetVersion($tomb = array())
    {
        $ret = array();
        if (is_array($tomb) && count($tomb)) {
            $key = key($tomb);
            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_versions` WHERE {$key} = ?";
            Database::getInstance()->query($query, array('s', current($tomb)));
            $ret = Database::getInstance()->fetchNext();

            return ($ret);
        }

        return ($ret);
    }

    public static function _Hibajelento($kuldo, $kulcs, $hiba = "", $arrGCData = array(), $usstatid = NULL, $pwd = "")
    {
        $tomb = array(
            "kulcs"     => $kulcs,
            "kuldo"     => $kuldo,
            "leiras"    => $hiba,
            "statusz"   => "uj",
            "datum"     => date('Y-m-d H:i:s', time()),
            "ip"        => $_SERVER['REMOTE_ADDR']
        );

        $bind_type = 'ssssss';

        if (isset($usstatid) && (int)$usstatid > 0) {
            $tomb['usID'] = $usstatid;
            $bind_type .= 'i';
        }

        if (isset($pwd) && $pwd != '') {
            $tomb['pwd'] = $pwd;
            $bind_type .= 's';
        }

        if (count($arrGCData)) {
            if (isset($arrGCData['usGCMan'])) { // graphic card manufacture
                $tomb['usGCMan'] = trim($arrGCData['usGCMan']);
                $bind_type .= 's';
            }

            if (isset($arrGCData['usGCType'])) { // graphic card type
                $tomb['usGCType'] = trim($arrGCData['usGCType']);
                $bind_type .= 's';
            }

            if (isset($arrGCData['usGCDriverVer'])) { // graphic card driver version
                $tomb['usGCDriverVer'] = trim($arrGCData['usGCDriverVer']);
                $bind_type .= 's';
            }
        }

        Database::getInstance()->insert(CRMADATBAZIS . ".h_aktivalas_infok", $tomb, $bind_type);
    }

    public static function startQuery($datas, $secondaryServer = 'false')
    {
        $time                   = time();
        $pwd                    = $datas['pwd'];
        $compID                 = $datas['compID'];
        $build                  = $datas['build'];
        $ctrID                  = $datas['ctrID'];
        $telepitesikod          = $datas['telepitesikod'];
        $osdata                 = $datas['osdata'];
        /*$usGCMan                = $datas['usGCMan'];
        $usGCType               = $datas['usGCType'];
        $usGCDriverVer          = $datas['usGCDriverVer'];*/
        $npID                   = $datas['npID'];
        $partnerID              = $datas['partnerID'];
        $name                   = $datas['name'];
        $email                  = $datas['email'];
        $company                = $datas['company'];
        $phone                  = $datas['phone'];
        $newsletter             = $datas['newsletter'];
        $reactivationCounter    = $datas['reactivationCounter'];
        $isTwTw                 = $datas['isTwTw'];
        $ctrname                = $datas['ctrname'];
        $user_IP                = $datas['user_IP'];
        $user_lang_db           = (isset($datas['lang']) && !empty($datas['lang'])) ? trim($datas['lang']) : 'eng';
        $wt                     = $datas['wt'];
        $seatID                 = $datas['seatID'];
        $floating               = $datas['floating'];
        $offline                = $datas['offline'];
        $se                     = $datas['se'];
        $isEmergencyServer      = crm_hardlock::isEmergencyServer($secondaryServer);
        $error                  = '';
        $errorDlgFreq           = 5; // masodperc
        $errorTolerancy         = 5;
        $floatingError          = 5;
        $floatingTolerancy      = 1440;
        $statisticInterval      = 10080;
        $floatingTryNumber      = 1; // probalkozask szama, ha nem tudja elerni a szervert
        $parallelDownload       = 'true';
        $floatingDecTime        = 200;
        $fddo                   = 5;

        $crackedProgram = strpos($osdata, ".gray.") !== false ? true : false;
        if ($crackedProgram) {    //- 2018-as proragramoktol(nehany 2018-as build nem fut ide be)
            /*$crackedProgramDeactivated = false;
            if(crm_hardlock::IsCalledFromMyIP()){
                header('Content-Type: text/xml; charset=utf-8');
                header('Content-Disposition: inline; filename=response.xml');
                print '<'.'?'.'xml version="1.0" encoding="UTF-8" standalone="yes"'.'?'.'>
                    <Tips>
                        <Tip lang="en" version="2">
                            <path>/public/support/tips/en/</path>
                        </Tip>
                        <actcode>'.$validTelepitesikod.'</actcode>
                        <Virtualization>true</Virtualization>
                        <logfilter>0</logfilter>
                        <loginEnabled>false</loginEnabled>
                        <PersonalMessage></PersonalMessage>
                        <graylink></graylink>
                    </Tips>';
                $crackedProgramDeactivated = true;
            }*/

            // A crackelt programok ne menjenek bele a debug_table-be
            /*$actual_link = (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? "https" : "http") . "://{$_SERVER['HTTP_HOST']}{$_SERVER['REQUEST_URI']}";
            $message = "Crack: ".$decoded." || url: ".$actual_link;
            crm_hardlock::AddToDebugTable($message);*/

            return; // A crackelt programokat nem taroljuk
        }

        $validPassword = Codegen::IsValidPassword($pwd);
        if ($validPassword) {
            $ver8and9 = crm_hardlock::GetVer8And9FromPassword($pwd);
            $softwareKey = Codegen::IsSoftwareKey($pwd); // Software kulcs 2019

            if (!$isEmergencyServer && crm_hardlock::IsProgramExpiredByFixDate($pwd)) {
                // A lejart programok ne menjenek be a debug_table-be
                /*$message = "Tip error: expired program by IsProgramExpiredByFixDate || password: ".$pwd;
                crm_hardlock::AddToDebugTable($message);*/

                return;
            }
        } else {
            $ver8and9       = 0;
            $softwareKey    = false;

            $actual_link = (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? "https" : "http") . "://{$_SERVER['HTTP_HOST']}{$_SERVER['REQUEST_URI']}";
            $message = "Tip error: invalid password || password: {$pwd} || url: {$actual_link}";
            crm_hardlock::AddToDebugTable($message);

            return;
        }
        $validTelepitesikod = Codegen::IsValidTimeCode($telepitesikod);
        if ($validTelepitesikod || ($validPassword && !$softwareKey))  //- hardware kulcseseten az isid a passwordbol generalhato
            $isid = ($softwareKey ? crm_hardlock::GetIsidFromTimeCode($telepitesikod) : substr($pwd, 0, 2) . $ver8and9);
        else {
            $telepitesikod  = "";
            $isid           = "";
        }

        $hlNum = crm_hardlock::GetHlnumFromPassword($pwd);

        if ($isEmergencyServer) {
            if (!$isTwTw || $isTwTw == 'false')
                return;

            $currentDate = new DateTime();
            $currentDate->modify('+10 day');
            $meddig = $currentDate->format('Y-m-d');

            $napok  = Codegen::GetDayInterval(self::getTwTwRootTimeString(), $meddig);
            $aktkod = Codegen::GenerateTimeCode($pwd, $isid, $napok);

            if ($softwareKey) {
                $utst = time();
                (int)$convertedDay = crm_hardlock::ConvertDay(date('w'));
                $tid = (int)($utst / $convertedDay);

                $regenr = crm_hardlock::Yes($utst, 39);
            }

            crm_hardlock::secondaryServerLog($pwd, 'startQuery', 'A startQuery lefutott');

            return array(
                'actCode'           => $aktkod,
                'virtualization'    => 'true',
                'usstatid'          => '',
                'logfilter'         => 0,
                'moduleList'        => '',
                'hlNum'             => $hlNum,
                'regenr'            => $regenr,
                'tid'               => $tid,
                'isFloating'        => $floating,
                'isOffline'         => $offline,
                'floatingFreq'      => '60',
                'flTolerancy'       => $floatingTolerancy,
                'askForPin'         => 'false',
                'error'             => $error,
                'errorDlgFreq'      => $errorDlgFreq, // FD - masodperc
                'errorTolerancy'    => $errorTolerancy,
                'floatingError'     => $floatingError,
                'floatingTryNumber' => $floatingTryNumber,
                'parallelDownload'  => $parallelDownload,
                'floatingDecTime'   => $floatingDecTime, // FloatingDecideTimeout
                'fddo'              => $fddo
            );
        }

        if (!$validPassword && substr($decoded, 0, 2) == "##") {    //- 2018-as proragramoktol(nehany 2018-as build nem fut ide be)
            $actual_link    = (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? "https" : "http") . "://{$_SERVER['HTTP_HOST']}{$_SERVER['REQUEST_URI']}";
            $message        = "Tip error: missing password || url: {$actual_link}";
            crm_hardlock::AddToDebugTable($message);
        }
        if ($validPassword && !$validTelepitesikod && $ver8and9 > 35) { //- csak a 2019-es programokat es ujabbakat vizsgalunk, a regbbiekben elofordulhatnak hibas idokodok
            $actual_link    = (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? "https" : "http") . "://{$_SERVER['HTTP_HOST']}{$_SERVER['REQUEST_URI']}";
            $message        = "Tip error: invalid timecode {$decoded} || url: {$actual_link}";
            crm_hardlock::AddToDebugTable($message);
        }

        if (isset($npID)) $compid = crm_hardlock::AddOrUpdateComputer($npID, '');

        $isNamirialAutoCreate   = isset($npID) && $npID > '' && isset($partnerID) && $partnerID == '2nt' /*-&& isset($telepitesikod) && $telepitesikod>''-*/ && $validPassword;
        $isBimLadderAutoCreate  = isset($npID) && $npID > '' && isset($partnerID) && $partnerID == 'kbl' /*-&& isset($telepitesikod) && $telepitesikod>''-*/ && $validPassword;

        /*******************************************************************************
         *NAMIRIAL vagy BIMLADDER automata kulcskiadás és program létrehozás
         ******************************************************************************/
        if ($isNamirialAutoCreate || $isBimLadderAutoCreate) {
            $queryStr = "SELECT hlID FROM `" . CRMADATBAZIS . "`.`nonprofit` WHERE computer_id = ? AND partnerID = ? AND verID = ?";
            Database::getInstance()->query($queryStr, array('isi', $compid, $partnerID, $ver8and9));
            $res = Database::getInstance()->fetchNext();

            $rangeMin   = $isNamirialAutoCreate ? 940001 : 920001;
            $rangeMax   = $isNamirialAutoCreate ? 949999 : 929999;
            $interval   = $isNamirialAutoCreate ? 90 : 30;
            $country_id = $isNamirialAutoCreate ? 39 : 82;
            $topic_id   = $isNamirialAutoCreate ? 366 : 368;
            $info       = $isNamirialAutoCreate ? 'Namirial nonprofit kulcs aktiválás' : 'BIMLadder nonprofit kulcs aktiválás';
            $contact    = $isNamirialAutoCreate ? 8 : 9;
            $fizetve    = $time + $interval * 86400;

            if ($isTwTw) { // Az engedelyezett napok szamat konvertalni kell TWTW eseten
                $interval = Codegen::GetDayInterval(crm_hardlock::getTwTwRootTimeString(), date('Y-m-d', $fizetve));
            }

            $type   = crm_hardlock::GetTypeFromPassword($pwd);
            $ver    = crm_hardlock::GetVersion(array('verPassword8and9' => $ver8and9));

            //ba nincs ehhez a gephez meg non-profit program regisztralva
            if (empty($res)) {
                $hlNum  = crm_hardlock::GetTheFirstNotUsedHlNumFromInterval($rangeMin, $rangeMax);
                $pass   = Codegen::GeneratePassword($hlNum, 0, $type, $ver['verCode']);
                $aktKod = Codegen::GenerateTimeCode($pass, $isid, $interval);

                // Ha nincs benne a user a CRM-be, akkor hozza adjuk
                $userID = crm_users::AddUser(
                    array(
                        'strName'       => $name,
                        'strEMail'      => $email,
                        'strTel'        => $phone,
                        'nCtrID'        => $country_id,
                        'old_db'        => 'clusers_eng',
                        'dateInsert'    => $time,
                        'strCompany'    => $company,
                        'nUserType'     => 2
                    ),
                    $newsletter,
                    'sssisssi'
                );

                //ba regisztraljuk hozza a non-profit programot
                crm_hardlock::AddNonprofit(
                    array(
                        'isid'                  => $isid,
                        'verid'                 => $ver8and9,
                        'hlID'                  => $hlNum,
                        'nUserID'               => $userID,
                        'add_datetime'          => date('Y-m-d H:i:s', $time),
                        'expire_datetime'       => date('Y-m-d H:i:s', strtotime('+90 day', $time)),
                        'activation_datetime'   => date('Y-m-d H:i:s', $time),
                        'old_db'                => 'clusers_eng',
                        'partnerID'             => $partnerID,
                        'enabled'               => 'Y',
                        'computer_id'           => $compid
                    ),
                    'sisissssssi'
                );

                //Namirial vagy BIMLADDER non-profit kod aktivalas beszurasa crm -> u_history tablaba
                crm_users::AddUserHistory(array(
                    'nUserID'       => $userID,
                    'nTopicID'      => $topic_id,
                    'dateEvent'     => date('Y-m-d H:i:s', $time),
                    'strPlace'      => '',
                    'strInfo'       => $info,
                    'nManagerID'    => 36,
                    'insertDate'    => date('Y-m-d H:i:s', $time)
                ));

                crm_hardlock::AddHardlock(
                    array(
                        'hlNum'         => $hlNum,
                        'hlCtrID'       => $country_id,
                        'hlLan'         => 0,
                        'hlStat'        => 1,
                        'hlDateIssue'   => date('Y-m-d', $time),
                        'hlManID'       => 36,
                        'hlTime'        => date('Y-m-d H:i:s', $time),
                        'hlUser'        => $userID,
                        'hlDealer'      => 62103,
                        'bUjithato'     => 1,
                        'old_db'        => 'clusers_eng'
                    ),
                    'siiisissiis'
                );

                crm_hardlock::AddProgram(
                    array(
                        'prTypeID'      => $type,
                        'prVerID'       => $ver['verID'],
                        'prLan'         => 0,
                        'prPass'        => trim($pass),
                        'prSellDate'    => date('Y-m-d', $time),
                        'prActDate'     => date('Y-m-d', $fizetve), // 90 vagy 30 nap
                        'prFizetve'     => date('Y-m-d', $fizetve),
                        'prActCode'     => trim($aktKod),
                        'prTime'        => date('Y-m-d H:i:s', $time),
                        'prLastMod'     => date('Y-m-d H:i:s', $time),
                        'prContact'     => $contact, // Non-profit
                        'prHlNum'       => $hlNum, // 99xxxx
                        'prManID'       => 36, // web
                        'prStat'        => 0,
                        'prHiType'      => 1, // 1 Rendelés | 2 Tipus Valtas | 3 Frissites | 4 Auto Friss.
                        'twtw'          => $isTwTw ? 1 : 0
                    ),
                    'iiisssssssisiiii'
                );

                $pwd = $pass; //ba 2018.07.02. mostantol az uj jelszava a usernek a most generalt, 94-es lesz, nem pedig a 93-as!
            } else {
                //ba ehhez a gephez mar volt non-profit regisztracio, keressuk ki a sorozatszamot neki
                $hlNum = $res['hlID'];
                $query = "SELECT prPass FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prHlNum = ?";
                Database::getInstance()->query($query, array('s', $hlNum));
                $programQuery = Database::getInstance()->fetchNext();
                $pwd = $programQuery['prPass'];
            }
        } //- if ($isNamirialAutoCreate || $isBimLadderAutoCreate) vege

        //insert stats to the userstats table
        $stats = array(
            'usLang'            => $user_lang_db,
            'usDate'            => time(),
            'usPwd'             => $pwd,
            'usVer'             => $ver8and9,
            'usCompID'          => $compID,
            'usCtrID'           => $ctrID,
            'usBuild'           => $build,
            'usCtrName'         => $ctrname,
            'usIP'              => $user_IP,
            'usOSData'          => $osdata,
            'usISID'            => $isid,
            'usInditasokSzama'  => 1
        );

        $query = "SELECT hlLan, hlCtrID, hlStat, hlUser, isFloating FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum = ?";
        Database::getInstance()->query($query, array('s', $hlNum));
        $hardlock = Database::getInstance()->fetchNext();

        $isLanProgram = !empty($hardlock) && $hardlock['hlLan'] > 0;

        // A szarmazasi orszag beallitasa
        if ($softwareKey) {
            if (!empty($hardlock)) { // Azok a programok, amik regisztrálódnak a CRM-ben 2019
                if (isset($hardlock['hlCtrID'])) $stats['usHlCtrID'] = $hardlock['hlCtrID'];
            }
        } else {
            $stats['usHlCtrID'] = substr($pwd, 0, 2);
        }

        $trialProgram               = crm_hardlock::IsTrialKey($pwd);
        $trialSpecialEditionProgram = $trialProgram ? crm_hardlock::IsSpecialEditionTrial($pwd) : false;

        // Kereskedelmi valtozatok lekezelese (a statisztika miatt bekerulnek a Namirial es a BIMLadder programok is)
        if ($ver8and9 > 29 && (!$trialProgram || $trialSpecialEditionProgram)) {
            if (!$trialSpecialEditionProgram) {
                $binding    = array('s', $pwd);
                $query      = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ?";
                if ($softwareKey && $isLanProgram) { //- szoftveres lan kulcsnal mindig telepitesenkent egyedi az aktivalo kod
                    $query  .= " AND prActCode LIKE ?";
                    $binding = array('ss', $pwd, "%-{$isid}-%");
                }
                Database::getInstance()->query($query, $binding);
                $prgrm = Database::getInstance()->fetchNext();

                if (empty($prgrm) && $softwareKey && $isLanProgram) {    //- a lan-os valtozatnal na nem volt telepitve lehet, hogy nem volt talalat
                    $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ? AND (prActCode = '' OR prActCode IS NULL)";
                    Database::getInstance()->query($query, array('s', $pwd));
                    $prgrm = Database::getInstance()->fetchNext();
                }
            }

            if ($softwareKey) {   //- a telepitesi datum frissitese
                if ($validTelepitesikod) //- telepitesi datum tarolasanak elokeszitese
                    $installDate = crm_hardlock::GetInstallationDateFromInstallationCode($telepitesikod, $isTwTw);
                else
                    $installDate = time();
            } else {
                if (empty($prgrm)) $installDate = time(); //- todo ez gigantikus hiba, jelezni kell, ha ilyen elofordul
                else $installDate = $prgrm['prSellDate'];
            }

            $stats['usTelepitesiDatum'] = $installDate;
            $stats['workingMinutes']    = $wt;

            if (!empty($prgrm)) $jelenlegi_aktivalokod = $prgrm['prActCode'];
            else $jelenlegi_aktivalokod = '';

            //- idokod generalasa
            if ($isTwTw) $aktivalokod = crm_hardlock::GetOrGenerateTimeCodeTWTW($pwd, $isid, $telepitesikod, $npID, '', $seatID, $build);
            else $aktivalokod = crm_hardlock::GetOrGenerateTimeCode($pwd, $isid, $telepitesikod, $npID);

            // A userstats2013 tablaba megkeresi az utolso bejegyzest és amennyiben valtozatlan, updateli az inditasok szamat, különben uj bejegyzést hoz letre
            if (!$trialSpecialEditionProgram) {
                $usstatid = crm_hardlock::UpdateInstallationDate($pwd, $compID, $build, $aktivalokod, $user_IP, $installDate, $stats);
            } else {
                // Nem irjuk vissza a generalt kodot
                $usstatid = crm_hardlock::UpdateInstallationDate($pwd, $compID, $build, $jelenlegi_aktivalokod, $user_IP, $installDate, $stats);
            }

            $virtualization = ($hardlock['hlStat'] == '2' ? "false" : "true"); // letiltott kulcs visszajelzése a programnak

            $valasz = ($aktivalokod != "") ? $aktivalokod : "(( nem kapott valaszt ))";

            if (($valasz == "(( nem kapott valaszt ))" && substr($pwd, 0, 2) != 39) || strpos($valasz, 'error') !== false) {
                $query = "SELECT id FROM `" . CRMADATBAZIS . "`.`hibajelento` WHERE pwd = ? ORDER BY datum DESC";
                Database::getInstance()->query($query, array('s', $pwd));
                $res = Database::getInstance()->fetchNext();

                if (!empty($res)) {
                    Database::getInstance()->update(
                        CRMADATBAZIS . ".hibajelento",
                        array(
                            'error'     => $valasz,
                            'datum'     => date('Y-m-d H:i:s', time())
                        ),
                        array('id'  => $res['id']),
                        'ssi'
                    );
                } else {
                    $query = Database::getInstance()->insert(
                        CRMADATBAZIS . ".hibajelento",
                        array(
                            "error" => $valasz,
                            "datum" => date('Y-m-d H:i:s', time()),
                            "pwd"   => $pwd
                        ),
                        'sss'
                    );
                }

                $floatingAktivalasihiba = crm_hardlock::GetActivationError(11);
                if ($floatingAktivalasihiba == $aktivalokod && $hardlock['hlLan'] <= 1) {
                    $query = "SELECT c.custom_name AS custom_name, c.computer_name AS computer_name 
                        FROM `" . SYNCADATBAZIS . "`.`floating_licenses` f
                        LEFT JOIN cl_hlusers.computers c ON c.id = f.computer_id
                        WHERE f.hlNum = ?
                        LIMIT 1";
                    Database::getInstance()->query($query, array('s', $hlNum));
                    $computer = Database::getInstance()->fetchNext();
                    if (!empty($computer) && count($computer) > 0) {
                        $resource = new Resource($ctrID);
                        $compName = $computer['custom_name'] != '' ? $computer['custom_name'] : $computer['computer_name'];

                        if ($compName == '') {
                            $compName = $resource->getDlgString(11812);
                        }

                        $error = $compName;
                    }
                }
            }
        }

        if ($aktivalokod != '') {
            $query = "SELECT prID, prLogFilter, prVerID FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ? AND prActCode = ?";
            Database::getInstance()->query($query, array('ss', $pwd, $aktivalokod));
            $programs = Database::getInstance()->fetchNext();

            if (empty($programs)) {
                $query = "SELECT prID, prLogFilter, prVerID FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ?";
                Database::getInstance()->query($query, array('s', $pwd));
                $programs = Database::getInstance()->fetchNext();
            }
        } else {
            $query = "SELECT prID, prLogFilter, prVerID FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ?";
            Database::getInstance()->query($query, array('s', $pwd));
            $programs = Database::getInstance()->fetchNext();
        }

        // Tavoli logolasi parameterek beallitasa
        $logfilter = 0;
        if ($validPassword && !$crackedProgram && !$trialProgram) $logfilter = $programs['prLogFilter'];

        // Ha a verid >= 36, akkor beinsertalja a hardlockot és a computer id-t a hardlock_computers tablaba, ha meg nincs benne
        if ($softwareKey && !$crackedProgram && !$trialProgram) crm_hardlock::AddHardlockComputer($hlNum, $compid, $ver8and9);

        // Kulcsok tesztelese
        if ($softwareKey && !$crackedProgram && !$trialProgram) {
            $valasz = crm_hardlock::sCheckPassword($pwd);

            if (!empty($valasz)) {
                $actual_link = (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? "https" : "http") . "://{$_SERVER['HTTP_HOST']}{$_SERVER['REQUEST_URI']}";
                $message = "Checked Password || Serial: {$pwd} || Valasz: {$valasz} || url: {$actual_link}";
                crm_hardlock::AddToDebugTable($message, $pwd);
            }
        }

        // Modul es reaktivalo kodok kiiratasa
        if (!$crackedProgram && !$trialProgram) {
            // Modul kodok osszegyujtese
            $moduleList = "";
            if (!empty($programs)) {
                $query = "SELECT mdCode FROM `" . CRMADATBAZIS . "`.`p_modules` WHERE mdPrID = ?";
                Database::getInstance()->query($query, array('i', $programs['prID']));
                $modules = Database::getInstance()->fetchAssoc();

                if (!empty($modules)) {
                    foreach ($modules as $module) {
                        if (!empty($moduleList)) $moduleList .= ";";

                        $moduleList .= $module['mdCode'];
                    }
                }
            }

            // Reaktivalokodok kuldese
            if ($virtualization == "true" && $reactivationCounter > 0) {
                $reactivationCounter--;
                $modulIsid = substr($pwd, 0, 2) . $ver8and9;

                if ($reactivationCounter > 4) $reactivationCounter = 4; // 5-nel tobb nem lehet

                for ($i = 0; $i <= $reactivationCounter; $i++) {
                    $modulID = 35 + $i;
                    $reactivationCode = Codegen::GenerateTimeCode($pwd, $modulIsid, $modulID);

                    if (!empty($moduleList)) $moduleList .= ";";

                    $moduleList .= $reactivationCode;
                }
            }
        }

        if ($softwareKey) {
            $utst = time();
            (int)$convertedDay = crm_hardlock::ConvertDay(date('w'));
            $tid = (int)($utst / $convertedDay);

            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ? AND aktiv = ?";
            Database::getInstance()->query($query, array('ssi', $pwd, $isid, 1));
            $p_serial_isid = Database::getInstance()->fetchAssoc();

            if (empty($p_serial_isid)) $regenr = crm_hardlock::No($utst, 39);
            else $regenr = crm_hardlock::Yes($utst, 39);
        }

        $isOffline = 'false';
        $isFloating = $hardlock['isFloating'] == 1 && $programs['prVerID'] >= 23;

        if ($isFloating && $programs['prVerID'] == 23 && $build < 255)
            $isFloating = false;

        $floatingFreq   = 'false';
        $askForPin      = 'false';

        if ($isFloating) {
            $query = "SELECT * FROM `" . SYNCADATBAZIS . "`.`floating_licenses` WHERE hlNum = ? AND seatID = ?";
            Database::getInstance()->query($query, array('ss', substr($pwd, 0, 6), $seatID));
            $floatingLic = Database::getInstance()->fetchNext();

            if (!empty($floatingLic) && $floatingLic['offline_expiration_date'] != '' && $floatingLic['offline_expiration_date'] >= date('Y-m-d')) {
                $isOffline = 'true';
            } else {
                if ($floatingLic['offline_expiration_date'] != '') {
                    Database::getInstance()->update(
                        SYNCADATBAZIS . ".floating_licenses",
                        array(
                            'offline_expiration_date' => ''
                        ),
                        array('seatID' => $seatID),
                        'ss'
                    );
                }
            }

            $floatingFreq = 60;

            $pinEnabled = crm_hardlock::needPin(substr($pwd, 0, 6));
            if ($pinEnabled) {
                $askForPin = crm_hardlock::askForPin(substr($pwd, 0, 6), $seatID) ? 'true' : 'false';
            }

            if ($askForPin == 'true') {
                $floatingTolerancy = 1;
                $errorTolerancy = 1;
            }

            if (substr($pwd, 0, 6) == '992313' || substr($pwd, 0, 6) == '991319') {
                $floatingFreq = 1;
                $floatingTolerancy = 30;
                $errorTolerancy = 1;
            }
        }

        if ($trialProgram && $ver8and9 >= 41) {
            $query = "SELECT id, versionInfo FROM local_crm.trial_requests WHERE seatID = ?;";
            Database::getInstance()->query($query, array('s', $seatID));
            $trialUser = Database::getInstance()->fetchNext();

            if (!empty($trialUser)) {
                $prod = 1;
                if ($se == 'LT') $prod = 0;
                if (crm_hardlock::IsLiveKey($pwd)) $prod = 2;

                $product            = crm_hardlock::getProduction($se, $prod);
                $versionInfoArray   = json_decode(stripslashes($trialUser['versionInfo']), true);
                $currentVersionInfo = $versionInfoArray[$product];
                $currentVersion     = $currentVersionInfo[$ver8and9];

                if ($currentVersion != null) {
                    $versionInfoArray[$product][$ver8and9]['count']++;

                    $versionInfo = json_encode($versionInfoArray);

                    Database::getInstance()->update(
                        "local_crm.trial_requests",
                        array(
                            'versionInfo' => $versionInfo,
                            'update_date' => date('Y-m-d H:i:s')
                        ),
                        array('seatID' => $seatID),
                        'sss',
                        false
                    );
                }
            }
        }

        return array(
            'actCode'           => $aktivalokod,
            'virtualization'    => $virtualization,
            'usstatid'          => $usstatid,
            'logfilter'         => $logfilter,
            'moduleList'        => $moduleList,
            'hlNum'             => $hlNum,
            'regenr'            => $regenr,
            'tid'               => $tid,
            'isFloating'        => $isFloating ? 'true' : 'false',
            'isOffline'         => $isOffline,
            'floatingFreq'      => $floatingFreq,
            'flTolerancy'       => $floatingTolerancy,
            'askForPin'         => $askForPin,
            'error'             => $error,
            'errorDlgFreq'      => $errorDlgFreq, // FD - masodperc
            'errorTolerancy'    => $errorTolerancy, // FDT
            'floatingError'     => $floatingError, // FE
            'statisticInterval' => $statisticInterval, // SI - masodperc
            'floatingTryNumber' => $floatingTryNumber,
            'parallelDownload'  => $parallelDownload,
            'floatingDecTime'   => $floatingDecTime, // FloatingDecideTimeout
            'fddo'              => $fddo
            // Ha felveszunk egy uj parametert, akkor az emergency servernel is fel kell venni!
        );
    }

    public static function needPin($hlNum)
    {
        $query = "SELECT * FROM sync_crm.hardlock_ext WHERE hlNum = ?";
        Database::getInstance()->query($query, array('s', $hlNum));
        $pinEnabled = Database::getInstance()->fetchNext();

        return !empty($pinEnabled);
    }

    public static function askForPin($hlNum, $seatID)
    {
        $query = "SELECT * FROM local_crm.hardlock_ext WHERE hlNum = ? AND seatIDs LIKE ?";
        Database::getInstance()->query($query, array('ss', $hlNum, '%' . $seatID . '%'));
        $localHardlockExt = Database::getInstance()->fetchNext();

        return empty($localHardlockExt);
    }

    public static function getProduction($se, $prod)
    {
        $prod = $se == 'LT' ? 0 : (int)$prod;
        switch ($prod) {
            case 0:
                $product = 'lt';
                break;

            case 1:
                $product = 'professional';
                break;

            default:
                $product = 'live';
                break;
        }

        return $product;
    }

    public static function getTrialEmailBody($ctrID, $name, $email, $phone, $distributor, $product, $year)
    {
        if ($ctrID == '36') {
            $body = "
                Kedves {$name}!
                <br><br>

                Azért kaptad ezt az e-mailt, mert a(z) " . $email . " fiókodról kérést kaptunk az ARCHLine.XP ingyenes próbaverziójának elindítására.
                <br>
                A 30 napos próbaidőszak most kezdődik.
                <br><br>

                <b>Hogyan válhatsz magabiztos felhasználóvá?</b>
                <br>
                Fedezd fel tanfolyamainkat, ahol alap-, közép- és felsőfokon sajátíthatod el az ONLINE anyagok segítségével az ARCHLine.XP program tudását.
                <br>
                <a href='https://www.archline.hu/oktatas/workshopok'>Tanfolyamok - Oktató videók</a>
                <br><br>

                Az ARCHLine.XP egy innovatív 3D CAD / BIM tervezőszoftver, mely alkalmas építészeti és belsőépítészeti projektek megtervezésére, illetve a teljes műszaki dokumentáció elkészítésére. A munkát a fotórealisztikus látványtervek teszik egésszé, igény esetén animációkkal kiegészítve.
                <br>
                Egy terv létrehozásakor alaprajzokkal, metszetekkel, homlokzatokkal, nézetekkel, tervlapokkal, kimutatásokkal dolgozunk egyszerre. Az ARCHLine.XP-vel készített BIM-projektek teljes mértékben koordináltak, nem igényelnek manuális frissítéseket a szinkronizáláshoz. Ez hatékonyabb munkafolyamatot tesz lehetővé.
                <br><br>

                Név: <b>{$name}</b> <br>
                Email: <b>{$email}</b> <br>
                Mobil: <b>{$phone}</b> <br>
                Chosen Distributor: <b>{$distributor}</b> <br>
                Termék: <b>{$product}</b> <br>
                Verzió: <b>{$year}</b>
                <br><br>

                Üdvözlettel, <br>
                ARCHLine.XP Team
            ";
        } else {
            $body = "
                Dear {$name}
                <br><br>

                ARCHLine.XP is a BIM (Building Information Modeling) architectural and interior design software that provides easy collaboration with IFC, DWG and RVT formats.
                <br><br>

                Your 30 days of trial period starts now.
                <br><br>

                Get started with ARCHLine.XP:
                <br><br>

                <a href='https://help.archlinexp.com/hc/en-us/articles/4414867837969-Quick-Start-Guide-Core-concepts-and-tools'>Quick Start Guide - Core concepts and tools (archlinexp.com)</a>
                <br><br>

                <a href='https://help.archlinexp.com/hc/en-us/articles/360020740237-Converting-the-2D-room-plan-into-3D'>Converting the 2D room plan into 3D (archlinexp.com)</a>
                <br><br>

                You receive this email because we received a request to start ARCHLine.XP free trial from your account {$email}
                <br><br>

                Name: <b>{$name}</b> <br>
                Email: <b>{$email}</b> <br>
                Phone: <b>{$phone}</b> <br>
                Chosen Distributor: <b>{$distributor}</b> <br>
                Product: <b>{$product}</b> <br>
                Version: <b>{$year}</b>
                <br><br>

                Thanks, <br>
                ARCHLine.XP Team
            ";
        }

        return $body;
    }

    public static function sentTrialEmail($email, $name, $phone, $distributor, $product, $year, $subject, $ctrID)
    {
        require_once($_SERVER['DOCUMENT_ROOT'] . '/maintenance/libraries/phpmailer/PHPMailerAutoload.php');
        require_once($_SERVER['DOCUMENT_ROOT'] . '/maintenance/config.mail.php');

        $mail           = new PHPMailer();
        $mail->CharSet  = 'UTF-8';
        $mail->Host     = $mailconfig['Host'];
        $mail->SMTPAuth = true;

        $mail->Username = $mailconfig['Username'];
        $mail->Password = $mailconfig['Password'];

        $mail->From     = 'info@archlinexp.com';
        $mail->FromName = 'www.archlinexp.com';
        $mail->AddAddress($email, $name);

        $mail->IsHTML(true);

        $mail->Subject = $subject;
        $mail->Body    = crm_hardlock::getTrialEmailBody($ctrID, $name, $email, $phone, $distributor, $product, $year);

        $mail->Send();
    }

    public static function InsertLog($password)
    {
        $query = "SELECT prLogFilter FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ?";
        Database::getInstance()->query($query, array('s', $password));
        $programQuery = Database::getInstance()->fetchNext();
        if (!empty($programQuery)) {
            $log = $programQuery['prLogFilter'];

            if ($log == 720895) {
                $actual_link = (isset($_SERVER['HTTPS']) && $_SERVER['HTTPS'] === 'on' ? "https" : "http") . "://{$_SERVER['HTTP_HOST']}{$_SERVER['REQUEST_URI']}";

                crm_hardlock::AddToDebugTable($actual_link, $password);
            }
        }
    }

    public static function getFloatingLic($serial, $seatID)
    {
        $query = "SELECT * FROM `" . SYNCADATBAZIS . "`.`floating_licenses` WHERE seatID = ? AND hlNum = ?";
        Database::getInstance()->query($query, array('ss', $seatID, substr($serial, 0, 6)));

        return Database::getInstance()->fetchNext();
    }

    public static function floatingUnlock($serial, $seatID, $offlineDelete = false)
    {
        $hlNum = substr($serial, 0, 6);
        $cadlineKeys = array('950521', '950298', '950124', '992313', '951399', '953091');

        $query = "SELECT * FROM `" . SYNCADATBAZIS . "`.`floating_licenses` WHERE seatID = ?";
        Database::getInstance()->query($query, array('s', $seatID));
        $key = Database::getInstance()->fetchNext();

        if (empty($key)) {
            return;
        }

        if (!$offlineDelete && $key['offline_expiration_date'] != '') {
            return;
        }

        if ($hlNum != $key['hlNum']) {
            crm_hardlock::AddToDebugTable("floatingUnlock(): Masik licenc van a seatID-hoz rogzitve. seatID: {$seatID}, serial: {$serial}");

            if (in_array($hlNum, $cadlineKeys)) {
                crm_hardlock::AddToDebugTable("floatingUnlock(): Masik licenc van a seatID-hoz rogzitve. seatID: {$seatID}, serial: {$serial}", "CADLine_TEST");
            }
        } else {
            if (in_array($hlNum, $cadlineKeys)) {
                crm_hardlock::AddToDebugTable("floatingUnlock(): DELETE begin. seatID: {$seatID}, serial: {$serial}", "CADLine_TEST");
            }

            $query = "DELETE FROM `" . SYNCADATBAZIS . "`.`floating_licenses` WHERE seatID = ?";
            Database::getInstance()->query($query, array('s', $seatID));

            if (in_array($hlNum, $cadlineKeys)) {
                crm_hardlock::AddToDebugTable("floatingUnlock(): DELETE end. seatID: {$seatID}, serial: {$serial}", "CADLine_TEST");
            }
        }

        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND computer_id = ?";
        Database::getInstance()->query($query, array('si', $serial, $key['computer_id']));
        $p_serial_isid = Database::getInstance()->fetchNext();

        Database::getInstance()->insert(
            'local_crm.floating_log',
            array(
                "seatID"        => $seatID,
                "hlNum"         => $hlNum,
                "inserted"      => date('Y-m-d H:i:s'),
                "isid"          => $p_serial_isid['isid'],
                "eventID"       => 2,
                "computerID"    => $key['computer_id']
            ),
            'ssssii'
        );
    }

    public static function generateDownloadUrl($url, $year, $isLT)
    {
        $newUrl = str_replace("{YEAR}", $year, $url);
        $newUrl = str_replace("{IS_LT}", $isLT, $newUrl);

        return $newUrl;
    }

    public static function getMirrorLinks()
    {
        $query = "SELECT * FROM local_crm.mirror_links WHERE id > 1 ORDER BY ordering";
        Database::getInstance()->query($query);
        return Database::getInstance()->fetchAssoc();
    }

    public static function floatingLock($serial, $seatID, $computerID, &$error = '')
    {
        $hlNum = substr($serial, 0, 6);
        $cadlineKeys = array('950521', '950298', '950124', '992313', '951399', '953091');

        $isPinEntered = crm_hardlock::needPin(substr($serial, 0, 6)) == false || crm_hardlock::askForPin(substr($serial, 0, 6), $seatID) == false;

        $query = "SELECT * FROM `" . SYNCADATBAZIS . "`.`floating_licenses` WHERE seatID = ?";
        Database::getInstance()->query($query, array('s', $seatID));
        $key = Database::getInstance()->fetchNext();

        if (!empty($key)) {
            if ($key['hlNum'] == $hlNum) {
                if ($isPinEntered) { // If the pin is not entered not allowed to register the floating license
                    Database::getInstance()->update(
                        SYNCADATBAZIS . ".floating_licenses",
                        array(
                            'modified' => date('Y-m-d H:i:s')
                        ),
                        array('seatID' => $seatID),
                        'ss'
                    );
                }

                return true;
            } else {
                $error = Floating_Error::DIFFERENT_LICENSE_ON_THE_SAME_COMPUTER;

                if (in_array($hlNum, $cadlineKeys)) {
                    crm_hardlock::AddToDebugTable("floatingLock(): Floating_Error::DIFFERENT_LICENSE_ON_THE_SAME_COMPUTER. seatID: {$seatID}, serial: {$serial}", "CADLine_TEST");
                }
                return false;
            }
        }

        $query = "SELECT * FROM `" . SYNCADATBAZIS . "`.`floating_licenses` WHERE hlNum = ?";
        Database::getInstance()->query($query, array('s', $hlNum));
        $key = Database::getInstance()->fetchAssoc();

        if (!empty($key)) {
            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum = '{$hlNum}'";
            crm_database::getInstance()->query($query);
            $hardlock = crm_database::getInstance()->fetchNext();

            if ($hardlock['hlLan'] <= 1 || count($key) >= $hardlock['hlLan']) {
                $error = Floating_error::LIMIT_REACHED;

                if (in_array($hlNum, $cadlineKeys)) {
                    $seatIdError = $hardlock['hlLan'] <= 1 ? 'A kulcs ehhez a seatID-hoz tartozik: ' . $key[0]['seatID'] : '';
                    crm_hardlock::AddToDebugTable("floatingLock(): Floating_error::LIMIT_REACHED. seatID: {$seatID}, serial: {$serial} || {$seatIdError}", "CADLine_TEST");
                }
                return false;
            }
        }

        try {
            if ($isPinEntered) { // If the pin is not entered not allowed to register the floating license
                Database::getInstance()->insert(
                    SYNCADATBAZIS . '.floating_licenses',
                    array(
                        "seatID"        => $seatID,
                        "hlNum"         => $hlNum,
                        "modified"      => date('Y-m-d H:i:s'),
                        "computer_id"   => $computerID
                    ),
                    'sssi'
                );
            }
        } catch (\Exception $e) {
            $query = "SELECT * FROM `" . SYNCADATBAZIS . "`.`floating_licenses` WHERE seatID = ?";
            Database::getInstance()->query($query, array('s', $seatID));
            $key = Database::getInstance()->fetchNext();

            if (empty($key)) {
                $insertDate = 'not inserted';
            } else {
                $insertDate = $key['modified'];
            }

            $modified = date('Y-m-d H:i:s');

            crm_hardlock::AddToDebugTable("floatingLock(): Floating_Error::INSERT_EXCEPTION. seatID: {$seatID}, serial: {$serial}, inserted: {$insertDate}, modified: {$modified}", "CADLine_TEST");
        }

        if ($isPinEntered) {
            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND computer_id = ?";
            Database::getInstance()->query($query, array('si', $serial, $computerID));
            $p_serial_isid = Database::getInstance()->fetchNext();

            Database::getInstance()->insert(
                'local_crm.floating_log',
                array(
                    "seatID"        => $seatID,
                    "hlNum"         => $hlNum,
                    "inserted"      => date('Y-m-d H:i:s'),
                    "isid"          => $p_serial_isid['isid'],
                    "eventID"       => 1,
                    "computerID"    => $computerID
                ),
                'ssssii'
            );
        }

        return true;
    }

    public static function activeFloating($password, $isid)
    {
        $hlNum = substr($password, 0, 6);

        $query = "SELECT computer_id FROM `" . CRMADATBAZIS . "`.`p_serial_isid`  WHERE serial = ? AND isid = ?";
        Database::getInstance()->query($query, array('ss', $password, $isid));
        $p_serial_isid = Database::getInstance()->fetchNext();

        if (empty($p_serial_isid))
            return false;

        $query = "SELECT seatID FROM `" . SYNCADATBAZIS . "`.`floating_licenses` WHERE hlNum = ? AND computer_id = ?";
        Database::getInstance()->query($query, array('si', $hlNum, $p_serial_isid['computer_id']));
        $floatingLic = Database::getInstance()->fetchNext();

        return !empty($floatingLic) ? $floatingLic['seatID'] : false;
    }

    public static function getTrialRequest($seatID)
    {
        $query = "SELECT * FROM `local_crm`.`trial_requests` WHERE seatID = ? ORDER BY id DESC LIMIT 1;";
        Database::getInstance()->query($query, array('s', $seatID));
        $trialRequest = Database::getInstance()->fetchNext();

        return $trialRequest;
    }

    public static function checkCaptcha($response)
    {
        $secret = '';

        if ($_SERVER['SERVER_NAME'] == 'www.archline.hu')
            $secret = '6Lc8nh8TAAAAALeRGLDbjsr7Rh1qpQJkYXYO-P50';
        else
            $secret = '6LeqnB8TAAAAAJTatP1dt8npqwDfJLz4_i9-rbNg';

        $post_data = http_build_query(
            array(
                'secret'    => $secret,
                'response'  => $response,
                'remoteip'  => $_SERVER['REMOTE_ADDR']
            )
        );

        $opts = array(
            'http' =>
            array(
                'method'    => 'POST',
                'header'    => 'Content-type: application/x-www-form-urlencoded',
                'content'   => $post_data
            )
        );

        $context  = stream_context_create($opts);

        $response = file_get_contents('https://www.google.com/recaptcha/api/siteverify', false, $context);
        $result = json_decode($response);

        return $result->success;
    }

    public static function isFloating($password)
    {
        $hlNum = substr($password, 0, 6);

        $query = "SELECT isFloating FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum = ?";
        Database::getInstance()->query($query, array('s', $hlNum));
        $hardlock = Database::getInstance()->fetchNext();

        $query = "SELECT prID, prLogFilter, prVerID FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ?";
        Database::getInstance()->query($query, array('s', $password));
        $programs = Database::getInstance()->fetchNext();

        return $hardlock['isFloating'] == 1 && $programs['prVerID'] >= 23;
    }

    public static function convertFloatingErrorCodeToString($floatingErrorCode, $verID, $build)
    {
        $floatingError = '';
        switch ($floatingErrorCode) {
            case Floating_Error::DIFFERENT_LICENSE_ON_THE_SAME_COMPUTER:
                if ($verID >= 24) {
                    $floatingError = 'error: unable to lock floating 2';
                } else {
                    $floatingError = 'error: unable to lock floating license';
                }
                break;

            case Floating_Error::LIMIT_REACHED:
            default:
                $floatingError = 'error: unable to lock floating license';
                break;
        }

        return $floatingError;
    }

    public static function FindTimeCode($serial, $isid, $telepitesikod, $isTimeCodeSendingEnabled, $isTwTw = false, $seatID = '', $build = '', $floatingLockAllowed = true)
    {
        $hasEmptyKey = false;

        // van-e olyan aktiv program aminek ez a sorozatszama // a/b kulcsok miatt
        $query = "SELECT prID, prStat, prActCode, prSellDate, twtw, prVerID FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ?";
        Database::getInstance()->query($query, array('s', $serial));
        $programs = Database::getInstance()->fetchAssoc();

        if (!$programs) return "error: nincs ilyen password";

        $query = "SELECT hlStat, hlID, hlDatePro, isFloating FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum = ?";
        Database::getInstance()->query($query, array('s', substr($serial, 0, 6)));
        $key = Database::getInstance()->fetchNext();

        if (
            $key['hlStat'] == 0 ||  //- ures kulcs
            ($key['hlStat'] == 2 && ($key['hlDatePro'] != '0000-00-00' && $key['hlDatePro'] != NULL && $key['hlDatePro'] != '')) //- letiltott kulcs
        ) { // a kulcs aktiv-e
            $valasz = "error: nem aktiv a program";
        } else { // egyeb hiba
            if ($key['hlID'] == 64300 || $key['hlID'] == 64897) //ba Namirial Trial program kulcsa es BIMLadder program kulcsa
                return "error: hianyzo kod";
            else {
                $szoftveres = Codegen::IsSoftwareKey($serial); // Szoftveres-e 2019
                $isFloatingKey = false;

                if ($key['isFloating'] == 1 && $programs[0]['prVerID'] >= 23) // Floating licencnel nincs limit
                    $isFloatingKey = true;

                foreach ($programs as $program) {
                    if ($program['prSellDate'] == "0000-00-00" || $program['prSellDate'] == NULL) { // van-e eladasi datum
                        $valasz = "error: nincs vasarlasi idopont";
                    } elseif ($program['prActCode'] != "") { // neki megfelelo aktivalasi kod
                        if ($szoftveres) {
                            $currentIsid = self::GetIsidFromTimeCode($program['prActCode']);

                            $isidOK = ($currentIsid == $isid);
                            // Regi programoknal elofordulhat a win 10 creator update miatti id valtozas, ezt szandekozott javitani
                            if (!$isTwTw && !$isidOK && substr($program['prActCode'], 7, 2) == substr($isid, 2, 2)) {
                                self::$win10creator_old_isid = $currentIsid;
                                $elteltnapok        = Codegen::GetDayFromTimeCode($telepitesikod);
                                $engedelyezettnapok = Codegen::GetDayFromTimeCode($program['prActCode']);
                                if ($engedelyezettnapok > $elteltnapok) {
                                    $valasz = "error: win10 creator";
                                    break;
                                }
                            }

                            if ($isidOK) {
                                if ($isTwTw == $program['twtw']) { // A kert es a tarolt kodrendszer egyeztetese
                                    // lockolva van-e a program ehhez a seatid-hoz
                                    if ($isFloatingKey) {
                                        if ($build <= '255' && $programs[0]['prVerID'] == 23) {
                                            // Ha floating licenc es a 255 buildnel regebbit hasznal, akkor ne futhasson, mert a program nem tudja kezelni
                                            $valasz = 'error: masik gep van regisztralva';
                                        } else {
                                            $floatingError = '';
                                            $reg = crm_hardlock::GetActiveRegistration($serial, $isid);
                                            if ($floatingLockAllowed && self::floatingLock($serial, $seatID, $reg['computer_id'], $floatingError)) {
                                                $floatingLic = self::getFloatingLic($serial, $seatID);
                                                $offlineActDate = $floatingLic['offline_expiration_date'];

                                                if ($offlineActDate < date('Y-m-d') || $offlineActDate == '') {
                                                    $actCode = $program['prActCode'];
                                                } elseif ($offlineActDate >= '2050-01-01') {
                                                    $days       = 3583;
                                                    $actCode    = Codegen::GenerateTimeCode($serial, $currentIsid, $days);
                                                } else {
                                                    $days       = Codegen::GetDayInterval(self::getTwTwRootTimeString(), $offlineActDate, true);
                                                    $actCode    = Codegen::GenerateTimeCode($serial, $currentIsid, $days);
                                                }

                                                $valasz = $actCode; // A kod rendben van, adja vissza
                                            } else {
                                                $valasz = crm_hardlock::convertFloatingErrorCodeToString($floatingError, $program['prVerID'], $build);
                                            }
                                        }
                                    } else {
                                        $valasz = $program['prActCode']; // A kod rendben van, adja vissza
                                    }
                                } else {
                                    $valasz         = 'error: hianyzo kod'; // A masik kodrendszerben van tarolva a kod, nem adja vissza
                                    $hasEmptyKey    = true;
                                }
                                break; // Ha mar megtalaltuk azzal az ISID-el, akkor nem kell a tobbi programon atmenni
                            } else {
                                if ($isFloatingKey)
                                    $valasz = 'error: hianyzo kod'; // Floating licencenel minden esetben engedi a regisztraciot
                                else
                                    $valasz = 'error: masik gep van regisztralva'; // Normal licencnel nem enged tovabbi regisztraciot
                            }
                        } else { // hardware kulcs esete
                            if ($isTwTw == $program['twtw']) { // A kert es a tarolt kodrendszer egyeztetese
                                $valasz = $program['prActCode'];    // hardveres kulcs eseten egybol visszaadja az aktualis aktivalasi kodot
                            } else {
                                $valasz         = 'error: hianyzo kod'; // A masik kodrendszerben van tarolva a kod, nem adja vissza
                                $hasEmptyKey    = true;
                            }
                            break;
                        }
                    } else { // nincs feltoltve a prActCode mezo
                        $valasz         = "error: hianyzo kod";        // nincs beirva a kod
                        $hasEmptyKey    = true;
                    }
                }
            }
        }

        if ($hasEmptyKey === true && $valasz == "error: masik gep van regisztralva") // LAN-os szoftver kulcsos liszensznel ha ezt a liszenszt nem talaltuk de volt ures liszensz, akkor csinalja ezt
            $valasz = "error: hianyzo kod";

        // Kivetel: 30, 34, 39, 41, 43 kulcsok, amik soha nem kapnak tolunk automata hosszabbitast.
        // csak a hibat adom vissza
        if ($isTimeCodeSendingEnabled || substr($valasz, 0, 5) == "error") return $valasz;
        else return ""; //- nincs hiba es van kod beirva, DE nem kaphatja meg
    }

    public static function IsTimeCodeSendingEnabled($password)
    {
        //  isCodeReplyEnabled(hlNum:str(6))
        //  2017.12.13. F.Zs.
        //  Ezekre az esetekre ad vissza TRUE-t a fuggveny (version tabla 1182-es rekord): 30;34;36;38;40;41;42;43;44;47;48;49;82;88;93;94;95;97;98;99;
        //  39 - Olaszorszag szandekosan hianyzik, amig a volume contract fennall!
        if (self::IsLiveKey($password) || self::IsLTkey($password)) // A live-ok mindig kommunikalnak a szerverrel 2019
            return true;

        $kulcs      = substr($password, 0, 6);
        $ver8and9   = self::GetVer8And9FromPassword($password);

        if ($ver8and9 >= 37) return true; // 2020-as valtozattol mindenki kap idokodot

        //  a lekerdezes vagy egy mezot ad vissza ver_id=1182 vagy semmit
        //  pl: SELECT ver_id  FROM `www_archline_hu`.`version`  WHERE ver_id=1182 AND (`usertype` LIKE '%;360012;%' OR `usertype` LIKE '%;36;%' OR LEFT(`usertype`,3)='36;');
        $ctrID = substr($kulcs, 0, 2);
        $query = "SELECT ver_id FROM `" . JMLADATBAZIS . "`.`version` WHERE ver_id = ? AND (usertype LIKE ? OR usertype LIKE ? OR LEFT(usertype, 3) = ?)";
        Database::getInstance()->query($query, array('isss', ACT_VERSION_ID, "%;{$kulcs};%", "%;{$ctrID};%", "{$ctrID};"));
        $res = Database::getInstance()->fetchNext();

        $ret = ((is_numeric($res['ver_id']) && $res['ver_id'] > 0) ? TRUE : FALSE);
        if (!$ret) {
            // Ezek a password-ok megis kaphatnak kodot
            $exceptions = array(
                "3948648362945744",
                "3960018340571178",
                "3981578343885829",
                "3984268320911925",
                "3944786350910035",
                "3964368342323114",
                "3968547340219718",
                "3922078343467818",
                "3986678330054419",
                "3954537340409996",
                "3987007330845972",
                "3953168353005544",
                "3925518350342279",
                "3948897340131564",
                "3972726350632589",
                "3937488350939975",
                "3986057360504402",
                "3941488360322917",
                "3987548340316176",
                "3949488330848001",
                "3902498366694835"
            );

            if (in_array($password, $exceptions)) $ret = true;
        }

        return $ret;
    }

    public static function IsRegistrationActive($password, $isid, $npid)
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ? AND aktiv = ?";
        Database::getInstance()->query($query, array('ssi', $password, $isid, 1));
        $res = Database::getInstance()->fetchNext();

        return (!empty($res));
    }

    public static function ActiveRegistrationRequired($password)
    {
        $ver8and9 = self::GetVer8And9FromPassword($password);

        // A 2019-es evtol vezettuk be az aktiv regisztracio szuksegesseget
        if ($ver8and9 < 36) return false;

        //ba namirial,bimladder trial igenis keri a rendszertol a lejaratot! Kell actcode az xml-be!
        if (self::IsSpecialEditionTrial($password)) return false;

        if (self::IsLiveKey($password)) return true; // A LIVE-oknak mar kell regisztralniuk

        $type = self::GetTypeFromPassword($password);

        // Egyenlore csak az LT valtozatban
        return ($type == '4' || (Codegen::IsSoftwareKey($password) && $ver8and9 > 36));
    }

    public static function correctIsid(&$isid)
    {
        while (strlen($isid) < 4) $isid = "0{$isid}";
    } // isid kiegeszitese

    public static function IsCalledFromMyIP()
    {
        // https://whatismyipaddress.com/ oldalrol kell ellenorizni az ip cimet
        return $_SERVER['REMOTE_ADDR'] == '79.122.124.35';
    }

    public static function Yes($utst, $x)
    {
        return (int)((((int)($utst / 8192) - 1519) * 76) / $x);
    }
    public static function No($utst, $x)
    {
        return (int)((((int)($utst / 4096) - 1873) * 96) / $x);
    }

    public static function ConvertDay($day)
    {
        switch ($day) {
            case 1: {
                    $ret = 2;
                    break;
                }
            case 2: {
                    $ret = 8;
                    break;
                }
            case 3: {
                    $ret = 5;
                    break;
                }
            case 4: {
                    $ret = 3;
                    break;
                }
            case 5: {
                    $ret = 7;
                    break;
                }
            case 6: {
                    $ret = 4;
                    break;
                }
            case 7: {
                    $ret = 6;
                    break;
                }    //- ilyen nincs, a vasarnap a 0 -> https://www.php.net/manual/en/datetime.format.php
            case 0: {
                    $ret = 6;
                    break;
                }
        }

        return $ret;
    }

    public static function RegisterProgram($password, $isid, $telepitesikod, $npid, $comp_name, $isTwTw = false, $seatID = '', $build = '', $floatingLockAllowed = false)
    {
        //- ha nincs computer nev, attol meg le kell a computer_id
        if (!empty($npid)) $computer_id = self::AddOrUpdateComputer($npid, $comp_name);

        $isTimeCodeSendingEnabled   = self::IsTimeCodeSendingEnabled($password);
        $softwareKey                = Codegen::IsSoftwareKey($password);

        $hlNum = self::GetHlnumFromPassword($password);
        $query = "SELECT hlLan, hlStat, hlDatePro, isFloating FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum = ?";
        Database::getInstance()->query($query, array('s', $hlNum));
        $key = Database::getInstance()->fetchNext();
        if (empty($key) || $key['hlStat'] == 0 || ($key['hlStat'] == 2 && ($key['hlDatePro'] != '0000-00-00' && $key['hlDatePro'] != NULL && $key['hlDatePro'] != ''))) { // a kulcs aktiv-e)
            return "error: nem aktiv a program";
        }

        $verid = self::GetVerIdFromPassword($password);
        if ($key['isFloating'] == 1 && $verid == 23 && ($build <= '255' || $build == '')) {
            crm_hardlock::AddToDebugTable("RegisterProgram(): 2023-as, 255 buildnel regebbi floating licenc! Password: {$password}");
            return "error: nincs kifizetve";
        }

        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ?";
        Database::getInstance()->query($query, array('s', $password));
        $program = Database::getInstance()->fetchNext();
        if (empty($program)) return "error: nem aktiv a program";

        if ($program['prFizetve'] < date('Y-m-d')) return "error: nincs kifizetve";

        // Hardver kulcsos valtozatok nem gephez kotott, egyedi kodot hasznalnak, ezert nem kell oket tarolni
        if ($softwareKey) {
            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ?";
            Database::getInstance()->query($query, array('ss', $password, $isid));
            $p_serial_isid = Database::getInstance()->fetchAssoc();
            if (!empty($p_serial_isid) && count($p_serial_isid) == 1) { //- normal eset, ez a gep mar volt ehez a programhoz regisztralva
                // Ez a gep valaha regisztralva volt
                if (isset($computer_id)) {
                    Database::getInstance()->update(
                        CRMADATBAZIS . ".p_serial_isid",
                        array(
                            'computer_id' => $computer_id
                        ),
                        array('id' => $p_serial_isid[0]['id']),
                        'ii'
                    );
                }

                if ($p_serial_isid[0]['aktiv'] == 1) { // Ez az aktiv, ezert mehet tovabb minden
                    $found = self::FindTimeCode($password, $isid, $telepitesikod, $isTimeCodeSendingEnabled, $isTwTw, $seatID, $build, $floatingLockAllowed);
                    if ($found == "error: hianyzo kod" || $found == "error: win10 creator") return '';  //- ha hiba van, akkor visszaadjuk
                    else return $found; // egyebnkent visszaadjuk a timecode-ot
                }
            } elseif (!empty($p_serial_isid) && count($p_serial_isid) > 1)
                return "error: masik gep van regisztralva"; //- tobb regisztraciot is talalt ugyanazon isiddel

            //- a regisztracio elvi idopontja
            $registration_date = date("Y-m-d h:i:s");
            if (!empty($telepitesikod)) { //- a megadott telepitesi kod alapjan ellenorizni lehet a telepites idopontjanak ervenyesseget
                $telepitesidatum    = self::GetInstallationDateFromInstallationCode($telepitesikod, $isTwTw);
                if (Codegen::GetDayFromTimeCode($telepitesikod) > 1) // Ha a telepites napjan regisztralja, akkor feltelelezzuk, hogy ebben a pillanatban inditotta el eloszor
                    $registration_date  = $telepitesidatum;
                $checked_reg_date   = self::CheckInstallDate($telepitesidatum, $telepitesikod, $isTwTw) ? self::CHECKED_DATE_OK : self::CHECKED_DATE_FALSE;
            } else
                $checked_reg_date = 0;

            $maxtelepites   = ($key['hlLan'] == 0) ? 1 : $key['hlLan'];
            if ($key['isFloating'] == 1 && $program['prVerID'] >= 23) { // A floating licenceknel nincs limit, tovabb kell engedni
            } elseif ($maxtelepites > 1) { //- lan-os valtozatok
                $query = "SELECT id FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid != '' AND isid IS NOT NULL AND aktiv = ?";
                Database::getInstance()->query($query, array('si', $password, 1));
                $telepitett = Database::getInstance()->fetchAssoc();
                if (!empty($telepitett) && count($telepitett) >= $maxtelepites) return "error: tul sok telepites";
            } else { //- Nem LAN-os kulcs eseten megnezi, hogy van-e aktivalt serial-isid paros
                $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid != ? AND aktiv = 1";
                Database::getInstance()->query($query, array('ss', $password, $isid));
                $aktiv = Database::getInstance()->fetchAssoc();
                if (!empty($aktiv) /*&& $verid < 20*/) return "error: masik gep van regisztralva";

                /*if(empty($aktiv) == false && $verid >= 20) { // 2020-as programnal engedelyezzuk a tobbszoros regisztraciot, Zoli keresere megszuntetve ezzel a szoftveres vedelmet
                    // A jelenlegi aktiv deaktivalasa és a kiadott idokot torlese
                    crm_database::getInstance()->query("UPDATE `".CRMADATBAZIS."`.`p_serial_isid` SET aktiv = 0 WHERE serial = '".$password."'");
                    crm_database::getInstance()->query("UPDATE `".CRMADATBAZIS."`.`h_programs` SET prActCode = NULL  WHERE prPass = '".$password."'");
                }*/
            }

            if (empty($p_serial_isid)) { //- registracio
                Database::getInstance()->insert(
                    CRMADATBAZIS . '.p_serial_isid',
                    array(
                        "serial"            => $password,
                        "isid"              => $isid,
                        "datum"             => $registration_date,
                        "ip"                => $_SERVER['REMOTE_ADDR'],
                        "aktiv"             => 1,
                        "datum_ellenorizve" => $checked_reg_date,
                        "computer_id"       => $computer_id
                    ),
                    'ssssiii'
                );

                if (!crm_hardlock::CheckRegistration($password, $isid)) {
                    crm_hardlock::AddToDebugTable("RegisterProgram(): Nem sikerult regisztralni: Password: {$password} || isid: {$isid}");
                }
            } else {
                if (isset($computer_id) && $computer_id != '') {
                    Database::getInstance()->update(
                        CRMADATBAZIS . '.p_serial_isid',
                        array(
                            "aktiv"         => 1,
                            "computer_id"   => $computer_id
                        ),
                        array('id'      => $p_serial_isid[0]['id']),
                        'iii'
                    );
                } else {
                    Database::getInstance()->update(
                        CRMADATBAZIS . '.p_serial_isid',
                        array(
                            "aktiv"         => 1
                        ),
                        array('id'      => $p_serial_isid[0]['id']),
                        'ii'
                    );
                }
                $found = self::FindTimeCode($password, $isid, $telepitesikod, $isTimeCodeSendingEnabled, $isTwTw, $seatID, $build, $floatingLockAllowed);
                if ($found == "error: hianyzo kod" || $found == "error: win10 creator") return '';  //- ha hiba van, akkor visszaadjuk
                else return $found; // egyebnkent visszaadjuk a timecode-ot
            }
        }
        return ""; //- regisztraltuk
    }

    public static function CheckRegistration($password, $isid)
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ?";
        Database::getInstance()->query($query, array('ss', $password, $isid));
        $res = Database::getInstance()->fetchNext();

        return (!empty($res));
    }

    public static function UnRegisterProgram($password, $isid, $prID = '', $compName = '', $message = '', $manID = 36, $forceUnlock = true)
    {
        $query = "UPDATE `" . CRMADATBAZIS . "`.`p_serial_isid` SET aktiv = ? WHERE serial = ? AND isid = ? AND aktiv = ? LIMIT 1";
        Database::getInstance()->query($query, array('issi', 0, $password, $isid, 1));

        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ?";
        Database::getInstance()->query($query, array('ss', $password, $isid));
        $p_serial_isid = Database::getInstance()->fetchNext();
        if (empty($p_serial_isid)) return false;

        $reg = $p_serial_isid['aktiv'];
        if ($reg != 0)  return false; // Ha aktiv maradt a program, akkor az hiba

        if ($prID != '') {
            $query = "UPDATE `" . CRMADATBAZIS . "`.`h_programs` SET prActCode = '' WHERE prID = ?";
            Database::getInstance()->query($query, array('i', $prID));
        } else {
            $query = "UPDATE `" . CRMADATBAZIS . "`.`h_programs` SET prActCode = '' WHERE prPass = ? AND prActCode LIKE ? LIMIT 1";
            Database::getInstance()->query($query, array('ss', $password, "%-{$isid}-%"));
        }

        if (crm_hardlock::GetVer8And9FromPassword($password) >= 40 && $forceUnlock) {
            $hlNum = substr($password, 0, 6);

            $query = "SELECT isFloating FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum = ?";
            Database::getInstance()->query($query, array('s', substr($password, 0, 6)));
            $key = Database::getInstance()->fetchNext();

            if (!empty($key) && $key['isFloating'] == 1) {
                $query = "SELECT seatID FROM `" . SYNCADATBAZIS . "`.`floating_licenses` WHERE hlNum = ? AND computer_id = ?";
                Database::getInstance()->query($query, array('si', $hlNum, $p_serial_isid['computer_id']));
                $floatingLic = Database::getInstance()->fetchNext();

                crm_hardlock::floatingUnlock($password, $floatingLic['seatID']);
            }
        }

        if ($compName != '')
            $info = "Felhasználói deaktiválás: {$compName}";
        else
            $info = $message;

        // internetes aktivalas esemeny
        $query = Database::getInstance()->insert(
            CRMADATBAZIS . ".h_history",
            array(
                "hiHlNum"   => substr($password, 0, 6),
                "hiDate"    => date('Y-m-d', time()),
                "hiManID"   => $manID,
                "hiTypeID"  => 27,
                "hiTime"    => date('Y-m-d H:i:s', time()),
                "hiInfo"    => $info
            ),
            'ssiiss'
        );

        return $reg == 0;
    }

    public static function CountRegistration($password, $action)
    {
        $query = "SELECT id FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ?";

        if ($action == 'active') $query .= " and aktiv = 1";
        elseif ($action == 'inactive') $query .= " and aktiv = 0";

        Database::getInstance()->query($query, array('s', $password));
        $res = Database::getInstance()->fetchAssoc();

        return count($res);
    }

    public static function ActivateHardlock($hlNum, $hlManID, $hlInfo)
    {
        Database::getInstance()->update(CRMADATBAZIS . ".hardlock", array('hlStat' => 1), array('hlNum' => $hlNum), 'is');
        Database::getInstance()->update(CRMADATBAZIS . ".h_programs", array('prStat' => 0), array('prHlNum' => $hlNum), 'is');
        Database::getInstance()->insert(
            CRMADATBAZIS . ".h_history",
            array(
                "hiHlNum"   => $hlNum,
                "hiDate"    => date('Y-m-d', time()),
                "hiManID"   => $hlManID,
                "hiTypeID"  => 16,
                "hiTime"    => date('Y-m-d H:i:s', time()),
                "hiInfo"    => $hlInfo
            ),
            'ssiiss'
        );

        $query = "SELECT hlStat FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum = ?";
        Database::getInstance()->query($query, array('s', $hlNum));
        $res = Database::getInstance()->fetchNext();

        return ($res['hlStat'] == 1 ? true : '');
    }

    public static function DeactivateHardlock($hlNum, $hlManID, $hlInfo, $hlDatePro)
    {
        Database::getInstance()->update(CRMADATBAZIS . ".hardlock", array('hlDatePro' => $hlDatePro, 'hlStat' => 2), array('hlNum' => $hlNum), 'sis');
        Database::getInstance()->update(CRMADATBAZIS . ".h_programs", array('prStat' => 1), array('prHlNum' => $hlNum), 'is');
        Database::getInstance()->insert(
            CRMADATBAZIS . ".h_history",
            array(
                "hiHlNum"   => $hlNum,
                "hiDate"    => date('Y-m-d', time()),
                "hiManID"   => $hlManID,
                "hiTypeID"  => 5,
                "hiTime"    => date('Y-m-d H:i:s', time()),
                "hiInfo"    => $hlInfo
            ),
            'ssiiss'
        );

        $query = "SELECT hlStat FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum = ?";
        Database::getInstance()->query($query, array('s', $hlNum));
        $res = Database::getInstance()->fetchNext();

        return ($res['hlStat'] == 2 ? false : '');
    }

    public static function CheckInstallDate($telepitesidatum, $telepitesikod, $isTwTw = false)
    {
        $installDateFromInstallCode = new DateTime(self::GetInstallationDateFromInstallationCode($telepitesikod, $isTwTw));
        $installDateMin             = new DateTime($telepitesidatum);
        $installDateMax             = new DateTime($telepitesidatum);
        $installDateMin->modify('-2 day');
        $installDateMax->modify('+2 day');

        if (($installDateFromInstallCode > $installDateMax) || ($installDateFromInstallCode < $installDateMin)) return false;
        else return true;
    }

    public static function GetInstallationDateFromInstallationCode($telepitesikod, $isTwTw = false)
    {
        $elteltnapok = Codegen::GetDayFromTimeCode($telepitesikod);

        if ($isTwTw) {
            $date = new DateTime(crm_hardlock::getTwTwRootTimeString());
            $date->modify('+' . $elteltnapok . ' day');
        } else {
            $date = new DateTime(date('Y-m-d'));
            $date->modify("-{$elteltnapok} day");
        }

        return $date->format('Y-m-d');
    }

    public static function GetInstallationDate_noRegisterAndValidate($password, $isid, $telepitesikod, $isTwTw = false)
    {
        $softwarekey = Codegen::IsSoftwareKey($password);
        if (!$softwarekey) {  //- a hardware kulcsok az eladasi datumtol szamolnak
            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ?";
            Database::getInstance()->query($query, array('s', $password));
            $program = Database::getInstance()->fetchNext();

            return $program['prSellDate'];
        }

        // a paros benne van-e a tablaban, regisztralva van-e
        $query = "SELECT datum, datum_ellenorizve FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ? ORDER BY datum DESC";
        Database::getInstance()->query($query, array('ss', $password, $isid));
        $registrations = Database::getInstance()->fetchAssoc();
        if (count($registrations) == 1) { //- normal eset softver kulcs eseten
            if (Codegen::IsValidTimeCode($telepitesikod) && ($registrations[0]['datum_ellenorizve'] == 0 || $registrations[0]['datum'] == '0000-00-00 00:00:00'))
                return self::GetInstallationDateFromInstallationCode($telepitesikod, $isTwTw);
            else
                return substr($registrations[0]['datum'], 0, 10);
        }

        //- egy isiddel tobb program is van regisztralva es az NEM hardware kulcsos
        if (count($registrations) > 1) return 'error: masik gep van regisztralva';

        return '';
    }

    public static function GetInstallationDate($password, $isid, $telepitesikod, $isTwTw = false)
    {
        $registrationDate = self::GetInstallationDate_noRegisterAndValidate($password, $isid, $telepitesikod, $isTwTw);

        if (strpos($registrationDate, 'error:') !== false) return $registrationDate;

        //- ezzel az isiddel meg nem volt regisztralva
        return self::RegisterProgram($password, $isid, $telepitesikod, '', '', $isTwTw, '');
    }

    public static function GetOrGenerateTimeCode($serial, $isid, $telepitesikod = '', $npID = '', $compName = '')
    {
        $isTimeCodeSendingEnabled   = self::IsTimeCodeSendingEnabled($serial);
        $valasz                     = self::FindTimeCode($serial, $isid, $telepitesikod, $isTimeCodeSendingEnabled, false, '', '', false);
        if ((!$isTimeCodeSendingEnabled) && (($valasz == 'error: hianyzo kod') || (!in_array($valasz, self::$aktivalasihibak))))
            return; // azoknal lep ki return-el, akiknek nem jar kod (pl. olaszok) es nincs veluk problema (nem kap error-t)

        if (self::ActiveRegistrationRequired($serial) && !self::IsRegistrationActive($serial, $isid, "")) {
            if (self::IsTrialKey($serial)) return;
            else return Codegen::GenerateTimeCode($serial, $isid, 1);
        }

        $szoftveres = Codegen::IsSoftwareKey($serial); // Szoftveres-e 2019

        $codeGenerationIsEnabled = (Codegen::IsValidTimeCode($telepitesikod));
        if ($codeGenerationIsEnabled) $elteltnap = Codegen::GetDayFromTimeCode($telepitesikod);
        elseif ($szoftveres) return; // Szoftverkulcsnal nem tudjuk kiszamolni a hasznalati ido hianyaban az idokodot

        $timeelteltnap = 60 * 60 * 24 * $elteltnap;

        if ($valasz == 'error: win10 creator') {
            // aktivalasi kod torlese
            $query = "UPDATE `" . CRMADATBAZIS . "`.`p_serial_isid` SET aktiv = ? WHERE serial = ? AND isid = ? LIMIT 1;";
            Database::getInstance()->query($query, array('iss', 0, $serial, self::$win10creator_old_isid));
            $query = "UPDATE `" . CRMADATBAZIS . "`.`h_programs` SET prActCode = NULL, prActDate = NULL WHERE prPass = ? AND SUBSTR(prActCode, 6, 4) = ? LIMIT 1;";
            Database::getInstance()->query($query, array('ss', $serial, self::$win10creator_old_isid));
            $valasz = 'error: hianyzo kod';
        }

        $specialEditionTrial = self::IsSpecialEditionTrial($serial);

        if (!$specialEditionTrial) {
            if ($szoftveres) {
                $refreshValidateDate = false;
                $query = "SELECT datum, datum_ellenorizve, aktiv FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ?";
                Database::getInstance()->query($query, array('ss', $serial, $isid));
                $validateDate   = Database::getInstance()->fetchNext();
                $verid          = self::GetVerIdFromPassword($serial);

                if ($verid < 20) {
                    if (empty($validateDate)) { //- nincs regisztracio
                        // A regi programok ha a CRM engedelyezte, automatikusan felfrissitettek magukat es kiadtak az aktualis programhoz a kodot ugy, hogy nem voltak beregisztralva (ezaltal nem is tortent meg a regisztracio)
                        // Tehat ezeket kiszurjuk es meghivjuk ezekere a programokra a regisztraciot
                        $hlnum = self::GetHlnumFromPassword($serial);
                        $query = "SELECT prID FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prHlNum = ? AND prVerID < ? GROUP BY prPass";
                        Database::getInstance()->query($query, array('si', $hlnum, $verid));
                        $prIdArray = Database::getInstance()->fetchAssoc();
                        if (empty($prIdArray)) { // Amennyiben az info mezoben oda van irva, hogy AutoReg, akkor engedelyezi az automatikus regisztraciot
                            $query = "SELECT prID FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ? AND prInfo LIKE ?";
                            Database::getInstance()->query($query, array('ss', $serial, "%AutoReg%"));
                            $prIdArray = Database::getInstance()->fetchAssoc();
                        }
                        $reg = empty($prIdArray) ? 'error:' : self::RegisterProgram($serial, $isid, $telepitesikod, $npID, '', false, '');
                        if (strpos($reg, 'error:') !== false)    //- ha sikerult a regisztracio, akkor frissiteni kell a $validateDate erteket
                            $refreshValidateDate = true;
                    } elseif ($validateDate['aktiv'] == 0) {    //- nem aktiv a regisztracio
                        $reg = self::RegisterProgram($serial, $isid, $telepitesikod, $npID, '', false, '');
                        if (strpos($reg, 'error:') !== false)    //- ha sikerult a regisztracio, akkor frissiteni kell a $validateDate erteket
                            $refreshValidateDate = true;
                    }
                    if ($refreshValidateDate) {    //- $validateDate frissitese
                        $query = "SELECT datum, datum_ellenorizve, aktiv FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ?";
                        Database::getInstance()->query($query, array('ss', $serial, $isid));
                        $validateDate = Database::getInstance()->fetchNext();
                    }

                    if ($validateDate['datum_ellenorizve'] < self::CHECKED_DATE_OK) // Voltak false bejegyzesek, ezert minden eddigi bejegyzest felul kell vizsgalni (az uj datum ellenorizve 2-es erteket kapott)
                        $validateDate['datum_ellenorizve'] = self::CHECKED_DATE_FALSE;
                }

                if (empty($validateDate)) return "error: nem aktiv regisztracio";

                if ($validateDate['datum_ellenorizve'] == self::CHECKED_DATE_FALSE) { //- tarolt regisztacios idopont ellenorzese
                    $regtelepites = $validateDate['datum'];

                    if (!isset($validateDate['computer_id']) && $npID != "") {
                        $compid = crm_hardlock::AddOrUpdateComputer($npID, '');
                        $query = "UPDATE `" . CRMADATBAZIS . "`.`p_serial_isid` SET computer_id = ? WHERE serial = ? AND isid = ? LIMIT 1";
                        Database::getInstance()->query($query, array('iss', $compid, $serial, $isid));
                    }

                    // Ellenorizzuk, hogy a regisztralt telepitesi ido egyezik-e a program altal regisztraltal
                    $checked_reg_date = self::CheckInstallDate($regtelepites, $telepitesikod);
                    if ($checked_reg_date) { // Egyezik, ezert az eredeti datum marad es jelezzuk, hogy ellenorizve van
                        $query = "UPDATE `" . CRMADATBAZIS . "`.`p_serial_isid` SET datum_ellenorizve = ? WHERE serial = ? AND isid = ? LIMIT 1";
                        Database::getInstance()->query($query, array('iss', self::CHECKED_DATE_OK, $serial, $isid));
                    } else { // A regisztralt telepitesi datum nem megfelelo, ezert felul irjuk a programban rogzitett datummal
                        $regtelepites = self::GetInstallationDateFromInstallationCode($telepitesikod, false);
                        $query = "UPDATE `" . CRMADATBAZIS . "`.`p_serial_isid` SET datum = ?, datum_ellenorizve = ? WHERE serial = ? AND isid = ? LIMIT 1";
                        Database::getInstance()->query($query, array('siss', $regtelepites, self::CHECKED_DATE_OK, $serial, $isid));
                        // Mivel megvaltozott a telepitesi datum, az eddigi aktivalasi kod ervenytelen
                        $valasz = "error: hianyzo kod";

                        $updateQuery = "UPDATE `" . CRMADATBAZIS . "`.`h_programs` SET prActCode = '' WHERE prPass = ? LIMIT 1";
                        Database::getInstance()->query($updateQuery, array('s', $serial));
                    }
                }
            }
        }

        // ha nincs aktivalasi kodja, akkor generalni neki
        if ($valasz == "error: hianyzo kod") {
            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ?";
            Database::getInstance()->query($query, array('s', $serial));
            $program = Database::getInstance()->fetchNext();

            if ($specialEditionTrial) {
                $aktkod = self::GenerateSpecialEditionTrialActCode($serial, $isid, $timeelteltnap, false);
                return $aktkod;
            } else {
                $timeszerzodes      = strtotime($program['prSellDate']);
                $timetelepites      = time() - $timeelteltnap;
                $maxtelepites       = max($timeszerzodes, $timetelepites);
                $maxdatetelepites   = date('Y-m-d', $maxtelepites);

                if ((!$szoftveres) && ($program['prSellDate'] != '') && ($program['prSellDate'] != '0000-00-00'))
                    $maxdatetelepites = substr($program['prSellDate'], 0, 10);

                if ($program['prFizetve']) {
                    $fizetvetime    = strtotime($program['prFizetve']);
                    $meddig         = date('Y-m-d', $fizetvetime);     // vege - telepites
                } elseif (substr($serial, 0, 2) == "99")
                    $meddig = date('Y-m-d', time() + 30 * 24 * 60 * 60);    // eloszor 30 napot kap
                else
                    return "error: nincs kifizetve";

                // mostani ido nagyobb mint ameddig jar
                if (date('Y-m-d', time()) > $meddig) return $valasz;

                $napok = Codegen::GetDayInterval($maxdatetelepites, $meddig);

                if ($meddig == '2050-01-01') $napok = 3583;

                $aktkod = Codegen::GenerateTimeCode($serial, $isid, $napok);
                $aktkod = (Codegen::IsValidTimeCode($aktkod) ? $aktkod : '');

                $serial = trim(addslashes($serial));

                $updateQuery = "UPDATE `" . CRMADATBAZIS . "`.`h_programs` SET prActCode = ?, twtw = ?, prActDate = ?, prFizetve = ? WHERE prPass = ? AND (prActCode = '' OR prActCode IS NULL) LIMIT 1";
                Database::getInstance()->query($updateQuery, array('sisss', $aktkod, 0, $maxdatetelepites, $meddig, $serial));

                if ($compName != '') $info = "Felhasználói aktiválás: {$compName}";

                // internetes aktivalas esemeny
                $query = Database::getInstance()->insert(
                    CRMADATBAZIS . ".h_history",
                    array(
                        "hiHlNum"   => $program['prHlNum'],
                        "hiDate"    => date('Y-m-d', time()),
                        "hiManID"   => 0,
                        "hiTypeID"  => 16,
                        "hiTime"    => date('Y-m-d H:i:s', time()),
                        "hiInfo"    => $info
                    ),
                    'ssiiss'
                );

                self::_Hibajelento("GetOrGenerateTimeCode", $program['prHlNum'], "felvettem: {$query}");
            }
            return $aktkod;
        }
        return $valasz;
    }

    public static function GetOrGenerateTimeCodeTWTW($serial, $isid, $telepitesikod = '', $npID = '', $compName = '', $seatID = '', $build = '', $floatingLockAllowed = true)
    {
        $valasz = self::FindTimeCode($serial, $isid, $telepitesikod, true, true, $seatID, $build, $floatingLockAllowed);

        if (self::ActiveRegistrationRequired($serial) && !self::IsRegistrationActive($serial, $isid, "")) {
            if (self::IsTrialKey($serial)) {
                return;
            } else {
                return Codegen::GenerateTimeCode($serial, $isid, 1);
            }
        }

        $szoftveres     = Codegen::IsSoftwareKey($serial); // Szoftveres-e 2019
        $elteltnap      = Codegen::GetDayFromTimeCode($telepitesikod);
        $timeelteltnap  = 60 * 60 * 24 * $elteltnap;

        // A 2020-as programok taroljak az isidet, ezert elvileg az op rendszer miatt nem valtozhatnak meg
        /*if($valasz == 'error: win10 creator') {
                // aktivalasi kod torlese
                crm_database::getInstance()->query("UPDATE `".CRMADATBAZIS."`.`p_serial_isid` SET `aktiv`=0 WHERE serial = '".$serial."' AND isid = '".self::$win10creator_old_isid."' LIMIT 1;");
                crm_database::getInstance()->query("UPDATE `".CRMADATBAZIS."`.`h_programs` SET `prActCode`=NULL,`prActDate`=NULL WHERE `prPass`='".$serial."' AND SUBSTR(prActCode,6,4)='".self::$win10creator_old_isid."' LIMIT 1;");
                $valasz = 'error: hianyzo kod';
      	}*/

        $specialEditionTrial = self::IsSpecialEditionTrial($serial);

        if (!$specialEditionTrial) {
            if ($szoftveres) {
                $refreshValidateDate = false;
                $query = "SELECT datum, datum_ellenorizve, aktiv FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ?";
                Database::getInstance()->query($query, array('ss', $serial, $isid));
                $registered = Database::getInstance()->fetchAssoc();
                $verid      = self::GetVerIdFromPassword($serial);

                if (empty($registered)) return "error: nem aktiv regisztracio";

                // Az uj modszerbe a telepitesi datum nem szerepel a kodbeallitasi folyamat parameterei kozott
                /*if($validateDate[0]['datum_ellenorizve'] == self::CHECKED_DATE_FALSE) { //- tarolt regisztacios idopont ellenorzese
                    $regtelepites = $validateDate[0]['datum'];

                    if(isset($validateDate[0]['computer_id']) == false && $npID != "") {
                        $compid = crm_hardlock::AddOrUpdateComputer($npID, '');
                        crm_database::getInstance()->query("UPDATE `".CRMADATBAZIS."`.`p_serial_isid` SET computer_id='".$compid."' WHERE `serial`='".$serial."' AND isid='".$isid."' LIMIT 1;");
                    }

                    // Ellenorizzuk, hogy a regisztralt telepitesi ido egyezik-e a program altal regisztraltal
                    $checked_reg_date = self::CheckInstallDate($regtelepites, $telepitesikod);
                    if($checked_reg_date) // Egyezik, ezert az eredeti datum marad es jelezzuk, hogy ellenorizve van
                        crm_database::getInstance()->query("UPDATE `".CRMADATBAZIS."`.`p_serial_isid` SET `datum_ellenorizve`='".self::CHECKED_DATE_OK."' WHERE `serial`='".$serial."' AND isid='".$isid."' LIMIT 1;");
                    else { // A regisztralt telepitesi datum nem megfelelo, ezert felul irjuk a programban rogzitett datummal
                        $regtelepites = self::GetInstallationDateFromInstallationCode($telepitesikod);
                        crm_database::getInstance()->query("UPDATE `".CRMADATBAZIS."`.`p_serial_isid` SET `datum`='".$regtelepites."', `datum_ellenorizve`='".self::CHECKED_DATE_OK."' WHERE `serial`='".$serial."' AND isid='".$isid."' LIMIT 1;");
                        // Mivel megvaltozott a telepitesi datum, az eddigi aktivalasi kod ervenytelen
                        $valasz = "error: hianyzo kod";

                        $updateQuery = "UPDATE `".CRMADATBAZIS."`.`h_programs` SET `prActCode`='' WHERE `prPass`='{$serial}' LIMIT 1";
                        crm_database::getInstance()->query($updateQuery);
                    }
                }*/
            } // szoftveres
        } // not spec. edition

        // ha nincs aktivalasi kodja, akkor generalni neki
        if ($valasz == "error: hianyzo kod") {
            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ?";
            Database::getInstance()->query($query, array('s', $serial));
            $program = Database::getInstance()->fetchNext();

            if ($specialEditionTrial) {
                $aktkod = self::GenerateSpecialEditionTrialActCode($serial, $isid, 0, true);
                return $aktkod;
            } else { // A normal programok
                $timeszerzodes      = strtotime($program['prSellDate']);
                $timetelepites      = self::getTwTwRootTime() + $timeelteltnap; //time()-$timeelteltnap;
                $maxtelepites       = max($timeszerzodes, $timetelepites);
                $maxdatetelepites   = date('Y-m-d', $maxtelepites);

                if ((!$szoftveres) && ($program['prSellDate'] != '') && ($program['prSellDate'] != '0000-00-00'))
                    $maxdatetelepites = substr($program['prSellDate'], 0, 10);

                if ($program['prFizetve']) {
                    $fizetvetime    = strtotime($program['prFizetve']);
                    $meddig         = date('Y-m-d', $fizetvetime);     // vege - telepites
                } elseif (substr($serial, 0, 2) == "99")
                    $meddig = date('Y-m-d', time() + 30 * 24 * 60 * 60);    // eloszor 30 napot kap
                else
                    return "error: nincs kifizetve";

                // mostani ido nagyobb mint ameddig jar
                if (date('Y-m-d', time()) > $meddig) return $valasz;

                $napok = Codegen::GetDayInterval(self::getTwTwRootTimeString(), $meddig);

                if ($meddig == '2050-01-01') $napok = 3583;

                $aktkod = Codegen::GenerateTimeCode($serial, $isid, $napok);
                $aktkod = (Codegen::IsValidTimeCode($aktkod) ? $aktkod : '');

                $serial = trim(addslashes($serial));

                // Megkeressuk, hogy pontosan ehhez a licenchez van-e mar felveve program
                $selectQuery = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ?
                    AND prActCode LIKE ?";
                Database::getInstance()->query($selectQuery, array('ss', $serial, "____-{$isid}-%"));
                $programWithIsid = Database::getInstance()->fetchNext();

                $cantLock = false;

                if (empty($programWithIsid)) { // Ha nincs, akkor felupdate-eljuk azt, ahol nincs aktivalokod
                    $query = "SELECT isFloating FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum = ?";
                    Database::getInstance()->query($query, array('s', substr($serial, 0, 6)));
                    $key = Database::getInstance()->fetchNext();

                    $isFloating = $key['isFloating'] == 1 && $program['prVerID'] >= 23;

                    if ($isFloating && $build != '' && $program['prVerID'] == 23 && $build < 255)
                        $isFloating = false;

                    if ($isFloating) {
                        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ? AND (prActCode = '' OR prActCode IS NULL) LIMIT 1";
                        Database::getInstance()->query($query, array('s', $serial));
                        $emptyProgram = Database::getInstance()->fetchNext();

                        if (empty($emptyProgram)) {
                            $prID = crm_hardlock::AddProgram(
                                array(
                                    'prTypeID'              => $program['prTypeID'],
                                    'prVerID'               => $program['prVerID'],
                                    'prLan'                 => $program['prLan'],
                                    'prPass'                => $program['prPass'],
                                    'prSellDate'            => $program['prSellDate'],
                                    'prActDate'             => $program['prActDate'],
                                    'prFizetve'             => $program['prFizetve'],
                                    'prActCode'             => '',
                                    'prTime'                => $program['prTime'],
                                    'prLastMod'             => $program['prLastMod'],
                                    'prContact'             => $program['prContact'],
                                    'prHlNum'               => $program['prHlNum'],
                                    'prManID'               => $program['prManID'],
                                    'prStat'                => $program['prStat'],
                                    'prHiType'              => $program['prHiType'],
                                    'twtw'                  => $program['twtw'],
                                    'prPrice'               => $program['prPrice'],
                                    'prInvoiceDateFrom'     => $program['prInvoiceDateFrom'],
                                    'prInvoiceDateTo'       => $program['prInvoiceDateTo'],
                                    'prInvoiceRecurrence'   => $program['prInvoiceRecurrence']
                                ),
                                'iiisssssssisiiiiissi'
                            );

                            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_modules` WHERE mdPrID = ? LIMIT 1";
                            Database::getInstance()->query($query, array('i', $program['prID']));
                            $modules = Database::getInstance()->fetchNext();

                            if (!empty($modules)) {
                                Database::getInstance()->insert(
                                    CRMADATBAZIS . ".p_modules",
                                    array(
                                        "mdPrice"       => $modules['mdPrice'],
                                        "mdInvNum"      => $modules['mdInvNum'],
                                        "mdOrdNum"      => $modules['mdOrdNum'],
                                        "mdDate"        => $modules['mdDate'],
                                        "mdIsPurchase"  => $modules['mdIsPurchase'],
                                        "mdID"          => $modules['mdID'],
                                        "mdPrID"        => $prID,
                                        "mdCode"        => $modules['mdCode'],
                                        "mdPrHlNum"     => substr($serial, 0, 6)
                                    ),
                                    'isssiiiss'
                                );
                            }
                        }

                        if ($floatingLockAllowed) {
                            $floatingError = '';
                            if (!crm_hardlock::floatingLock($program['prPass'], $seatID, crm_hardlock::getComputerIdFromNpid($npID), $floatingError)) {
                                $cantLock = true;
                            }
                        }
                    }

                    $updateQuery = "UPDATE `" . CRMADATBAZIS . "`.`h_programs` SET prActCode = ?, twtw = ?, prActDate = ?,
                        prFizetve = ? WHERE prPass = ? AND (prActCode = '' OR prActCode IS NULL) LIMIT 1";
                    $searched   = $serial;
                    $binded     = 'sisss';
                } else { // Ha megtalalta, akkor updatelje az aktivalo kodjat
                    $updateQuery = "UPDATE `" . CRMADATBAZIS . "`.`h_programs` SET prActCode = ?, twtw = ?, prActDate = ?,
                        prFizetve = ? WHERE prID = ? LIMIT 1";
                    $searched   = $programWithIsid['prID'];
                    $binded     = 'sissi';
                }
                Database::getInstance()->query($updateQuery, array($binded, $aktkod, 1, $maxdatetelepites, $meddig, $searched));

                if ($cantLock) {
                    $aktkod = crm_hardlock::convertFloatingErrorCodeToString($floatingError, $program['prVerID'], $build);
                }

                if ($compName != '') $info = "Felhasználói aktiválás: {$compName}";

                // internetes aktivalas esemeny
                $query = Database::getInstance()->insert(
                    CRMADATBAZIS . ".h_history",
                    array(
                        "hiHlNum"   => $program['prHlNum'],
                        "hiDate"    => date('Y-m-d', time()),
                        "hiManID"   => 0,
                        "hiTypeID"  => 16,
                        "hiTime"    => date('Y-m-d H:i:s', time()),
                        "hiInfo"    => $info
                    ),
                    'ssiiss'
                );

                self::_Hibajelento("GetOrGenerateTimeCode", substr($serial, 0, 6), "felvettem: {$query}");
            }
            return $aktkod;
        }
        return $valasz;
    }

    public static function IsTrialKey($password)
    {
        if (substr($password, 0, 1) != '9') return false;

        $secondChar = substr($password, 1, 1);

        if ($secondChar == '6') { // 2022-es programtol a 96-os passwordok szoftverkulcskent kezelodnek
            $ver8and9 = crm_hardlock::GetVer8And9FromPassword($password);

            if ($ver8and9 < 39) {
                return true;
            } else { // 2022-es programtol a 960001 és a 960002 az a nem regisztralt trial, a tobbi regisztralt, CRM-ben megtalalhato
                return substr($password, 2, 4) == '0001' || substr($password, 2, 4) == '0002';
            }
        }

        return ($secondChar == '3' || $secondChar == '1');
    }

    public static function IsSpecialEditionTrial($password)
    {
        $ver8and9 = self::GetVer8And9FromPassword($password);
        if ($ver8and9 >= 42) {
            return false;
        }

        return (substr($password, 0, 2) == '93' || substr($password, 0, 2) == '91');
    }

    public static function IsValidIsid($isid)
    {
        return (strlen($isid) == 4); // TODO Pontositani
    }

    public static function GetLicenseNumber($password)
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum = ?";
        Database::getInstance()->query($query, array('s', self::GetHlnumFromPassword($password)));
        $res = Database::getInstance()->fetchNext();

        if (empty($res)) return -1;

        return $res['hlLan'];
    }

    public static function GetDaysForUnlimitedTimeCode($password)
    {
        $ver8and9 = self::GetVer8And9FromPassword($password);

        return (($ver8and9 > 29) ? 3583 : 999);
    }

    public static function GetTrialDistributor($nUserID)
    {
        $login_id   = 67568;
        $userID     = abs($nUserID);

        $query = "SELECT custom_login_id FROM cl_hlusers.custom_login_licenses WHERE id = ?";
        Database::getInstance()->query($query, array('i', $userID));
        $res = Database::getInstance()->fetchNext();

        if (!empty($res)) {
            switch ($res['custom_login_id']) {
                case 'ita':
                    $login_id = 85644;
                    break;

                default:
                    break;
            }
        }

        return $login_id;
    }

    public static function GenerateTrial($userID, $product, $verID, $dealer, $installDate, $ctrID, $specEdition, $npid, &$errorMsg)
    {
        $hlNum      = '';
        $time       = time();
        $needKey    = false;
        $ver8and9   = crm_hardlock::GetVer8And9FromVerId($verID);
        $customName = '';

        if ($installDate != '') {
            $twtwDate = crm_hardlock::getTwTwRootTimeString();

            $reg_date = new DateTime($twtwDate);

            $reg_date->modify("+{$installDate} day");
            $reg_date->format('Y-m-d');

            $time = $reg_date->getTimestamp();
        }

        // Kikeresi, hogy a user-hez van-e trial
        if ($userID > 0) { // CADLine Login
            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlUser = ? AND product_id = ? AND hlNum LIKE '96%'";
            Database::getInstance()->query($query, array('ii', $userID, $product));
            $res = Database::getInstance()->fetchNext();

            $needKey = empty($res);

            if ($needKey) { // Ellenorizni kell, hogy erre a gepre kertek-e mar aktualis licencet
                // Ha nincs, megnezzuk, hogy az adott npid-hez van-e mar trial abbol az evbol

                $computerID = crm_hardlock::getComputerIdFromNpid($npid);

                if ($computerID) {
                    $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE computer_id = ? AND SUBSTR(serial, 8, 2) = ? 
                        AND serial LIKE '96%' AND SUBSTR(serial, 7, 1)";

                    if ($product == '2')
                        $query .= " = 'L'";
                    else
                        $query .= " != 'L'";

                    Database::getInstance()->query($query, array('ii', $computerID, $ver8and9));
                    $trial_user = Database::getInstance()->fetchNext();

                    $needKey = empty($trial_user);

                    if (!$needKey) {
                        $errorMsg = 'trial_exist_on_this_computer';
                        return '';
                    }
                }

                $time = time();
                $place = $product == 2 ? 'LIVE' : 'ARCHLine';

                Database::getInstance()->insert(
                    CRMADATBAZIS . ".u_history",
                    array(
                        'nUserID'       => $userID,
                        'nTopicID'      => 106,
                        'dateEvent'     => date('Y-m-d H:i:s', $time),
                        'strPlace'      => $place,
                        'strInfo'       => 'Bónusz Trialt igényelt',
                        'nManagerID'    => 36,
                        'insertDate'    => date('Y-m-d H:i:s', $time)
                    ),
                    'iisssis'
                );
            }
        } else { // Custom Login
            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`custom_login_licenses` WHERE id = ?";
            Database::getInstance()->query($query, array('i', abs($userID)));
            $trial_user = Database::getInstance()->fetchNext();
            if (empty($trial_user)) {
                $errorMsg = 'custom_user_not_found';
                return '';
            }

            // Ehhez az emailhez van-e mar trial
            $query = "SELECT LEFT(lic.serial, 6) AS hlNum FROM `" . CRMADATBAZIS . "`.`custom_login_licenses` lic WHERE lic.email = ? AND ver8and9 = ? 
                AND lic.serial LIKE '96%' AND SUBSTR(lic.serial, 7, 1)";

            if ($product == '2')
                $query .= " = 'L'";
            else
                $query .= " != 'L'";

            Database::getInstance()->query($query, array('si', $trial_user['email'], $ver8and9));
            $previousTrial = Database::getInstance()->fetchNext();

            $needKey = empty($previousTrial);

            if ($needKey) { // Ellenorizni kell, hogy erre a gepre kertek-e mar aktualis licencet
                // Ha nincs, megnezzuk, hogy az adott npid-hez van-e mar trial abbol az evbol
                $query = "SELECT LEFT(lic.serial, 6) AS hlNum FROM cl_hlusers.custom_login_licenses lic WHERE lic.npid = ? AND ver8and9 = ? 
                    AND lic.serial LIKE '96%' AND SUBSTR(lic.serial, 7, 1)";

                if ($product == '2')
                    $query .= " = 'L'";
                else
                    $query .= " != 'L'";

                Database::getInstance()->query($query, array('si', $npid, $ver8and9));
                $checked_user = Database::getInstance()->fetchNext();

                $needKey = empty($checked_user);

                if (!$needKey) {
                    $errorMsg = 'trial_exist_on_this_computer';
                    return '';
                }
            }

            $customName = $trial_user['name'];
        }

        if ($needKey) {
            $info = 'Trial igénylés';

            $query = "SELECT hlNum FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum >= 960010 AND hlNum <= 969999 AND hlStat = ? ORDER BY hlNum;";
            Database::getInstance()->query($query, array('i', _Hardlock::Blank));
            $hardlock = Database::getInstance()->fetchNext();

            $nUserID    = $userID;
            $userID     = $userID > 0 ? $userID : crm_hardlock::GetTrialDistributor($userID); // Custom trial request-nel ez mas lesz

            if (empty($hardlock)) {
                $hlNum  = crm_hardlock::tryToAlloc(960010, 969999);

                crm_hardlock::AddHardlock(
                    array(
                        'hlNum'         => $hlNum,
                        'hlCtrID'       => $ctrID,
                        'hlLan'         => 0,
                        'hlStat'        => _Hardlock::Active,
                        'hlDateIssue'   => date('Y-m-d', $time),
                        'hlManID'       => 36,
                        'hlTime'        => date('Y-m-d H:i:s', $time),
                        'hlUser'        => $userID,
                        'hlDealer'      => $dealer,
                        'bUjithato'     => 1,
                        'old_db'        => 'clusers_eng',
                        'product_id'    => $product
                    ),
                    'siiisisssisi'
                );
            } else {
                $hlNum = $hardlock['hlNum'];

                Database::getInstance()->update(
                    CRMADATBAZIS . '.hardlock',
                    array(
                        'hlStat'        => _Hardlock::Active,
                        'product_id'    => $product,
                        'hlDatePro'     => '',
                        'hlUser'        => $userID,
                        'hlCtrID'       => $ctrID,
                        'hlLan'         => 0
                    ),
                    array('hlNum' => $hlNum),
                    'iisiiii'
                );
            }

            Database::getInstance()->insert(
                CRMADATBAZIS . ".h_history",
                array(
                    "hiHlNum"   => $hlNum,
                    "hiDate"    => date('Y-m-d', time()),
                    "hiManID"   => _Manager::Web,
                    "hiTypeID"  => 2,
                    "hiTime"    => date('Y-m-d H:i:s', time()),
                    "hiInfo"    => $info
                ),
                'ssiiss'
            );
        } else {
            if ($res['hlStat'] == 2) {
                $errorMsg = 'trial_exist_on_this_computer';
                return '';
            }

            $hlNum = $res['hlNum'];
        }

        if ($specEdition == 'LT')
            $type = 4;
        else
            $type = $product == 1 ? 8 : 76;

        $verCode    = crm_hardlock::GetVerCodeFromverId($verID);
        $fizetve    = $time + 40 * 86400;

        $password = Codegen::GeneratePassword($hlNum, 0, $type, $verCode);

        crm_hardlock::AddProgram(
            array(
                'prTypeID'      => $type,
                'prVerID'       => $verID,
                'prLan'         => 0,
                'prPass'        => trim($password),
                'prSellDate'    => date('Y-m-d', $time),
                'prActDate'     => date('Y-m-d', $fizetve), // 40 nap
                'prFizetve'     => date('Y-m-d', $fizetve),
                'prTime'        => date('Y-m-d H:i:s', $time),
                'prLastMod'     => date('Y-m-d H:i:s', $time),
                'prContact'     => 10, // TRIAL
                'prHlNum'       => $hlNum,
                'prManID'       => 36,
                'prStat'        => 0,
                'prHiType'      => 1,
                'twtw'          => 1,
                'prInfo'        => $customName
            ),
            'iiissssssisiiiis'
        );

        if ($nUserID < 0) {
            Database::getInstance()->update(
                CRMADATBAZIS . '.custom_login_licenses',
                array(
                    'serial' => $password
                ),
                array('id' => abs($nUserID)),
                'si'
            );
        }

        return $password;
    }

    public static function UpdateInstallationDate($password, $compID, $build, $uj_aktivalokod, $user_IP, $installDate, &$stats = array())
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`userstats2013` WHERE usCompID = ? AND usPwd = ? AND usBuild = ? AND";
        $bind_array = array('sss', $compID, $password, $build);

        if (empty($uj_aktivalokod)) {
            $query .= " valasz IS NULL OR valasz = ''";
        } else {
            $query .= " valasz = ?";
            $bind_array = array('ssss', $compID, $password, $build, $uj_aktivalokod);
        }

        $query .= " ORDER BY usDate DESC";
        Database::getInstance()->query($query, $bind_array);
        $usStats = Database::getInstance()->fetchNext();

        if (empty($usStats)) { // Ha nincs benne a userstats2013 tablaba, akkor felvesszuk
            $stats["valasz"]    = $uj_aktivalokod;
            $bind               = '';

            foreach ($stats as $stat) {
                if (is_numeric($stat))
                    $bind .= 'i';
                else
                    $bind .= 's';
            }

            $usstatid = Database::getInstance()->insert(CRMADATBAZIS . ".userstats2013", $stats, $bind);
        } else {
            if ($stats['workingMinutes'] != null && $stats['workingMinutes'] != '')
                $wt = $stats['workingMinutes'] + $usStats['workingMinutes'];
            else
                $wt = $usStats['workingMinutes'];

            if ($wt == '' || $wt == null || $wt < 0)
                $wt = 0;

            $counter = $usStats['usInditasokSzama'];
            $counter++;
            $usstatid = (int)$usStats['usID'];
            Database::getInstance()->update(
                CRMADATBAZIS . ".userstats2013",
                array(
                    'usInditasokSzama'  => $counter,
                    'usIP'              => $user_IP,
                    'usTelepitesiDatum' => $installDate,
                    'usDate'            => time(),
                    'usOSData'          => $stats['usOSData'],
                    'workingMinutes'    => $wt
                ),
                array('usID' => $usstatid),
                'issisii'
            );
            $stats['usInditasokSzama'] = $counter;
        }
        return $usstatid;
    }

    public static function sCheckPassword($password)
    {
        $program = new program();
        $program->SetPassword($password);

        $engedelyek = array(/*'aktivalasi kod'*/);
        $hibak      = $program->CheckPassword($engedelyek);

        return $hibak;
    }

    public static function getLatestProgram($prHlNum)
    {
        $query = "SELECT prContact, prFizetve, prSellDate FROM h_programs WHERE prHlNum = '{$prHlNum}' ORDER BY prVerID DESC LIMIT 1;";
        crm_database::getInstance()->query($query);

        return crm_database::getInstance()->fetchNext();
    }

    public static function QuaterlyPaid($prFizetve)
    {
        $currentDate = date('Y-m-d');
        $currentTime = strtotime($currentDate);

        // Elso negyedev
        if ($prFizetve == date('Y') . '-01-31' && $currentTime < strtotime(date('Y') . '-01-01')) {
            return true;
        }

        // Masodik negyedev
        if ($prFizetve == date('Y') . '-04-30' && $currentTime < strtotime(date('Y') . '-04-01')) {
            return true;
        }

        // Harmadik negyedev
        if ($prFizetve == date('Y') . '-07-31' && $currentTime < strtotime(date('Y') . '-07-01')) {
            return true;
        }

        // Negyedik negyedev
        if ($prFizetve == date('Y') . '-10-31' && $currentTime < strtotime(date('Y') . '-10-01')) {
            return true;
        }

        return false;
    }

    public static function getQuarterlySupportPaid($prFizetve)
    {
        $currentDate  = date('Y-m-d');
        $currentTime  = strtotime($currentDate);
        $expire       = '';

        // Elso negyedev
        if ($prFizetve == date('Y') . '-01-31' && $currentTime < strtotime(date('Y') . '-01-01')) {
            $date = date('Y', strtotime('-1 year'));
            $expire = $date . '-12-31';
        }

        // Masodik negyedev
        if ($prFizetve == date('Y') . '-04-30' && $currentTime < strtotime(date('Y') . '-04-01')) {
            $expire = date('Y') . '-03-31';
        }

        // Harmadik negyedev
        if ($prFizetve == date('Y') . '-07-31' && $currentTime < strtotime(date('Y') . '-07-01')) {
            $expire = date('Y') . '-06-30';
        }

        // Negyedik negyedev
        if ($prFizetve == date('Y') . '-10-31' && $currentTime < strtotime(date('Y') . '-10-01')) {
            $expire = date('Y') . '-09-30';
        }

        return $expire;
    }

    public static function getQuarterlySupportNotPaid($prFizetve)
    {
        $currentDate  = date('Y-m-d');
        $currentTime  = strtotime($currentDate);
        $expire       = '';

        // Elso negyedev
        if ($prFizetve == date('Y') . '-01-31' && $currentTime >= strtotime(date('Y') . '-01-01')) {
            $expire = date('Y') . '-03-31';
        }

        // Masodik negyedev
        if ($prFizetve == date('Y') . '-04-30' && $currentTime >= strtotime(date('Y') . '-04-01')) {
            $expire = date('Y') . '-06-30';
        }

        // Harmadik negyedev
        if ($prFizetve == date('Y') . '-07-31' && $currentTime >= strtotime(date('Y') . '-07-01')) {
            $expire = date('Y') . '-09-30';
        }

        // Negyedik negyedev
        if ($prFizetve == date('Y') . '-10-31' && $currentTime >= strtotime(date('Y') . '-10-01')) {
            $expire = date('Y') . '-12-31';
        }

        return $expire;
    }

    public static function getSupport($prContact, $maintenance_valid, $maint_expiration, $prFizetve, $prSellDate)
    {
        // Karbantartasi szerzodesek
        $maintenanceArray = array(2, 6, 7);

        // Van ervenyes karbantartasi szerzodese es ki is van fizetve
        if (in_array($prContact, $maintenanceArray) && $prFizetve == '2050-01-01') {
            $expire = date('Y') . '-12-31';
            return "{$expire}-ig";
        }

        // Elofizetoi, ha a az aktivalas ervenyessege nagyobb, mint a jelenlegi datum
        if ($prContact == '1' && $prFizetve > date('Y-m-d')) {
            $exp = date('Y-m-d', strtotime($prSellDate . ' + 90 days'));
            return "{$exp}-ig";
        }

        // Licenc - egy osszeg vagy Licenc - berles vagy Licenc - reszlet
        if ($prContact == '0' || $prContact == '11' || $prContact == '3') {
            if ($maint_expiration != '' && $maint_expiration > date('Y-m-d')) {
                return "{$maint_expiration}-ig";
            }
            // Az eladasi datumhoz hozza kell adni 90 napot, annyi a termektamogatas
            $perpExpire = date('Y-m-d', strtotime($prSellDate . ' + 90 days'));

            // Ha a jelenlegi datum kisebb, mint az eladasi datum + 90 nap, akkor van termektamogatas
            if (strtotime(date("Y-m-d")) <= strtotime($perpExpire)) {
                return "{$perpExpire}-ig";
            }
        }

        // Karbantartasi - egyosszeg - nincs kifizetve
        if (($prContact == '2' && date('Y-m-d') > date('Y-01-20'))) {
            return "{$prFizetve}-ig";

            // return "Nem (Hátralék miatt felfüggesztve)";
        }

        // Karbantartasi - reszlet - nincs kifizetve
        if ($prContact == '7' && $prFizetve != '2050-01-01' && $prFizetve > date('Y-m-d')) {
            return "{$prFizetve}-ig";
        }

        // Karbantartasi - negyedeves - nincs kifizetve
        if ($prContact == '6' && !crm_hardlock::QuaterlyPaid($prFizetve)) {
            $expire = crm_hardlock::getQuarterlySupportNotPaid($prFizetve);
            return "{$prFizetve}-ig";
        }

        // Karbantartasi - negyedeves - kifizetve
        if ($prContact == '6' && crm_hardlock::QuaterlyPaid($prFizetve)) {
            return "{$prFizetve}-ig";
        }

        // Be van allitva a termektamogatas lejarata
        if ($maint_expiration != '' && strtotime($maint_expiration)) {
            return "{$maint_expiration}-ig";
        }

        return "";
    }

    public static function CheckUserProgram($nUserID, $engedelyek = array())
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlUser = ?";
        Database::getInstance()->query($query, array('s', $nUserID));
        $keys = Database::getInstance()->fetchAssoc();

        $hibak = array();
        foreach ($keys as $hl) {
            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prHlNum = ?";
            Database::getInstance()->query($query, array('s', $hl["hlNum"]));
            $programs = Database::getInstance()->fetchAssoc();
            if (empty($programs)) continue;

            $testedPasswords = array();
            foreach ($programs as $program) {
                $password = $program["prPass"];
                if (in_array($password, $testedPasswords)) continue;

                array_push($testedPasswords, $password);
                $program = new program();
                $program->SetPassword($password);
                $result = $program->CheckPassword($engedelyek);

                if (!empty($result)) array_push($hibak, $password . ": " . $result);
            }
        }
        return $hibak;
    }

    public static function CheckHardlockProgram($hlnum, $engedelyek = array())
    {
        $hibak = array();
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prHlNum = ?";
        Database::getInstance()->query($query, array('s', $hlnum));
        $programs = Database::getInstance()->fetchAssoc();
        if (empty($programs)) return;

        $testedPasswords = array();
        foreach ($programs as $program) {
            $password = $program["prPass"];
            if (in_array($password, $testedPasswords)) continue;

            array_push($testedPasswords, $password);
            $program = new program();
            $program->SetPassword($password);
            $result = $program->CheckPassword($engedelyek);

            if (!empty($result)) array_push($hibak, $password . ": " . $result);
        }
        return $hibak;
    }

    public static function CheckHardlockProgramFromArray($hlnum, $engedelyek = array())
    {
        $hibak      = array();
        $hlnumArray = explode(';', $hlnum);

        foreach ($hlnumArray as $hlnums) {
            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prHlNum = ?";
            Database::getInstance()->query($query, array('s', $hlnums));
            $programs = Database::getInstance()->fetchAssoc();
            if (empty($programs)) continue;

            $testedPasswords = array();
            foreach ($programs as $program) {
                $password = $program["prPass"];
                if (in_array($password, $testedPasswords)) continue;

                array_push($testedPasswords, $password);
                $program = new program();
                $program->SetPassword($password);
                $result = $program->CheckPassword($engedelyek);

                if (!empty($result)) array_push($hibak, $password . ": " . $result);
            }
        }
        return $hibak;
    }

    public static function CheckHardlockProgramWithLimit($hlnum, $limit, $engedelyek = array())
    {
        $hibak = array();
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prHlNum >= ? ORDER BY prHlNum ASC LIMIT {$limit}";
        Database::getInstance()->query($query, array('s', $hlnum));
        $programs = Database::getInstance()->fetchAssoc();
        if (empty($programs)) return;

        $testedPasswords = array();
        foreach ($programs as $program) {
            $password   = $program["prPass"];
            $prHlNum    = $program["prHlNum"];
            if (in_array($password, $testedPasswords)) continue;

            array_push($testedPasswords, $password);
            $program = new program();
            $program->SetPassword($password);
            $result = $program->CheckPassword($engedelyek);

            if (!empty($result))
                array_push($hibak, "<a href='/36/hardlock/show/{$prHlNum}' target='blank'>{$password}</a>: {$result}");
        }
        return $hibak;
    }

    public static function CorrectInstallDateInUserStats2013($password, $compid)
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`userstats2013` WHERE usPwd = ?";
        Database::getInstance()->query($query, array('s', $password));
        $usstats = Database::getInstance()->fetchAssoc();
    }

    public static function CorrectInstallDateInUserStats2013ForAllHardwareKeysAndDeleteExpiredSoftwareKeys()
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`userstats2013` WHERE (usTelepitesiDatum = ? OR usTelepitesiDatum IS NULL) AND usDate > ? ORDER BY usPwd DESC";
        Database::getInstance()->query($query, array('si', "0000-00-00", 1561939200));
        $usstats = Database::getInstance()->fetchAssoc();

        $utolsoSellDate = "";
        $utolsoPassword = "";

        foreach ($usstats as $stats) {
            $usid       = $stats["usID"];
            $password   = $stats["usPwd"];

            if (Codegen::IsSoftwareKey($password) && self::IsProgramExpiredByFixDate($password)) {
                $query = "DELETE FROM `" . CRMADATBAZIS . "`.`userstats2013` WHERE usID = ?";
                Database::getInstance()->query($query, array('i', $usid));
                print "<br/>";
                print "{$password} torolve lett (lejart szoftverkulcsos program)";

                continue;
            }

            if ($password != $utolsoPassword) {
                $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ?";
                Database::getInstance()->query($query, array('s', $password));
                $programs = Database::getInstance()->fetchNext();

                if (empty($programs)) continue;

                if (empty($programs["prSellDate"]) || $programs["prSellDate"] == "0000-00-00" || $programs["prSellDate"] == NULL)
                    continue;

                $sellDate = $programs["prSellDate"];
            } else
                $sellDate = $utolsoSellDate;

            Database::getInstance()->update(
                CRMADATBAZIS . ".userstats2013",
                array(
                    'usTelepitesiDatum' => $sellDate
                ),
                array('usID' => $usid),
                'si'
            );
            print "<br/>";
            print "{$password} datum beallitva (hardware kulcs) : {$sellDate}";

            $utolsoSellDate = $sellDate;
            $utolsoPassword = $password;
        }
    }

    public function GenerateSpecialEditionTrialActCode($password, $isid, $timeelteltnap, $isTwTw = false)
    {
        $ver8and9   = self::GetVer8And9FromPassword($password);
        $launchYear = self::GetLaunchYearFromVer8And9($ver8and9);
        $year       = new DateTime($launchYear . "-01-01");
        $ctr        = self::GetCountryFromPassword($password);

        $expirationDay = self::$fixExpirationDates[$ctr];

        if (empty($expirationDay)) return false;

        foreach ($expirationDay as $days) {
            if (empty($days)) continue;

            $year->modify((string)$days);
        }

        if ($isTwTw) { // 2020-tol nem a telepitesi idotol, hanem 2020-01-01-tol szamoljuk az eltelt napokat
            $mettol = self::getTwTwRootTimeString();
        } else {
            $timeMettol = time() - $timeelteltnap;
            $mettol     = date('Y-m-d', $timeMettol);
        }

        $napok  = Codegen::GetDayInterval($mettol, $year->format('Y-m-d'));
        $aktkod = Codegen::GenerateTimeCode($password, $isid, $napok);
        $aktkod = (Codegen::IsValidTimeCode($aktkod) ? $aktkod : '');
        return $aktkod;
    }

    public static function getTwTwRootTimeString()
    {
        return '2020-01-01';
    }
    public static function getTwTwRootTime()
    {
        return 1577836800;
    } // 2020-01-01 00:00:00 UTC

    public static function IsProgramExpiredByFixDate($password)
    {
        $ver8and9   = self::GetVer8And9FromPassword($password);
        $launchYear = self::GetLaunchYearFromVer8And9($ver8and9);
        $year       = new DateTime($launchYear . "-01-01");
        $ctr        = self::GetCountryFromPassword($password);

        $expirationDay = self::$fixExpirationDates[$ctr];
        if (empty($expirationDay)) return false;

        foreach ($expirationDay as $days) {
            if (empty($days)) continue;

            $year->modify((string)$days);
        }
        $date = new DateTime(date('Y-m-d'));

        return ($year < $date);
    }

    public static function CorrectActivationCode($password, $isid)
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ?";
        Database::getInstance()->query($query, array('s', $password));
        $programs = Database::getInstance()->fetchAssoc();
        if (empty($programs)) return;

        foreach ($programs as $program) {
            $aktivalokod = $program['prActCode'];
            if (empty($aktivalokod) || !Codegen::IsValidTimeCode($aktivalokod)) continue;

            $currentIsid = self::GetIsidFromTimeCode($aktivalokod);
            if ($isid != $currentIsid) continue;
        }
    }

    public static function GetWebformPrivateKey()
    {
        return "3BjGQ6fzMtHupM5V8U3l_u";
    }

    public static function CalcKey($privateKey, $publicKey)
    {
        $privateKeyLength   = strlen($privateKey);
        $publicKeyLength    = strlen($publicKey);

        if ($privateKeyLength > $publicKeyLength) {
            $result = $privateKey;
            for ($i = 1; $i < $publicKeyLength; $i += 2) $result[$i] = $publicKey[$i];
        } else {
            $result = $publicKey;
            for ($i = 0; $i < $privateKeyLength; $i += 2) $result[$i] = $privateKey[$i];
        }
        return sha1($result);
    }

    public static function IsActiveProgram($serial, $isid)
    {
        $query = "SELECT * FROM `cl_hlusers`.`p_serial_isid` WHERE serial = ? AND isid = ? AND aktiv = ?";
        Database::getInstance()->query($query, array('ssi', $serial, $isid, 1));
        $res = Database::getInstance()->fetchNext();

        return (!empty($res));
    }

    public static function GetComputerName($serial, $isid)
    {
        $query = "SELECT computer_id FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ?";
        Database::getInstance()->query($query, array('ss', $serial, $isid));
        $p_serial_isid = Database::getInstance()->fetchNext();

        $query = "SELECT computer_name FROM `" . CRMADATBAZIS . "`.`computers` WHERE id = ?";
        Database::getInstance()->query($query, array('i', $p_serial_isid['computer_id']));
        $res = Database::getInstance()->fetchNext();

        return $res['computer_name'];
    }

    public static function encode($privateKey, $publicKey, $data)
    {
        $key    = self::CalcKey($privateKey, $publicKey);
        $strLen = strlen($data);
        $keyLen = strlen($key);

        for ($i = 0; $i < $strLen; $i++) {
            $ordStr = ord(substr($data, $i, 1));

            if ($j == $keyLen) $j = 0;

            $ordKey = ord(substr($key, $j, 1));
            $j++;
            $hash .= strrev(base_convert(dechex($ordStr + $ordKey), 16, 36));
        }
        return $hash;
    }

    public static function decode($privateKey, $publicKey, $data)
    {
        $key    = self::CalcKey($privateKey, $publicKey);
        $strLen = strlen($data);
        $keyLen = strlen($key);

        for ($i = 0; $i < $strLen; $i += 2) {
            $ordStr = hexdec(base_convert(strrev(substr($data, $i, 2)), 36, 16));

            if ($j == $keyLen) $j = 0;

            $ordKey = ord(substr($key, $j, 1));
            $j++;
            $hash .= chr($ordStr - $ordKey);
        }
        return $hash;
    }

    // A selectprogramdialog.class.php hasznalja, ez mar regi!
    public static function decode2($string, $key)
    {
        $key = sha1($key);
        $strLen = strlen($string);
        $keyLen = strlen($key);
        for ($i = 0; $i < $strLen; $i += 2) {
            $ordStr = hexdec(base_convert(strrev(substr($string, $i, 2)), 36, 16));
            if ($j == $keyLen) $j = 0;
            $ordKey = ord(substr($key, $j, 1));
            $j++;
            $hash .= chr($ordStr - $ordKey);
        }
        return $hash;
    }

    public static function get_valueFromStringUrl($url, $parameter_name)
    {
        $parts = parse_url($url);
        if (isset($parts['query'])) {
            parse_str($parts['query'], $query);
            if (isset($query[$parameter_name])) return $query[$parameter_name];
            else return null;
        } else return null;
    }

    public static function SetWebformResult($session, $result, $encodedResult, $message, $button)
    {
        $query = "SELECT * FROM `cl_hlusers`.`webform_results` WHERE session_id = ?";
        Database::getInstance()->query($query, array('s', $session));
        $res = Database::getInstance()->fetchNext();

        if (empty($res)) {
            $sess = array(
                'session_id'    => $session,
                'result'        => $result . '=' . $encodedResult,
                'insertDate'    => date("Y-m-d"),
                'message'       => $message,
                'button'        => $button
            );
            Database::getInstance()->insert(CRMADATBAZIS . ".webform_results", $sess, 'sssss');
        } else {
            Database::getInstance()->update(
                CRMADATBAZIS . ".webform_results",
                array(
                    'result'        => $result . '=' . $encodedResult,
                    'insertDate'    => date("Y-m-d")
                ),
                array('session_id' => $session),
                'sss'
            );
        }
    }

    public static function Convert($betu)
    {
        $ret = 0;

        switch (strtoupper($betu)) {
            case 'A': {
                    $ret = 0;
                    break;
                }
            case 'B': {
                    $ret = 1;
                    break;
                }
            case 'C': {
                    $ret = 2;
                    break;
                }
            case 'D': {
                    $ret = 3;
                    break;
                }
            case 'E': {
                    $ret = 4;
                    break;
                }
            case 'F': {
                    $ret = 5;
                    break;
                }
            case 'G': {
                    $ret = 6;
                    break;
                }
            case 'H': {
                    $ret = 7;
                    break;
                }
            case 'I': {
                    $ret = 8;
                    break;
                }
            case 'J': {
                    $ret = 9;
                    break;
                }
            case 'K': {
                    $ret = 10;
                    break;
                }
            case 'L': {
                    $ret = 11;
                    break;
                }
            case 'M': {
                    $ret = 12;
                    break;
                }
            case 'N': {
                    $ret = 13;
                    break;
                }
        }
        return ($ret);
    }

    public static function getWebinarReminderEng()
    {
        $msg = "Hello! <br/>
            Did you catch the Webinar yesterday about <b>[TOPIC]</b>? <br/>
            If you did, we hope you enjoyed it! Need more time to practice? Watch it again here: <a href='[YT_LINK]'>[YT_LINK]</a> <br/><br/>

            If you missed the show, you can watch it here: <a href='[YT_LINK]'>[YT_LINK]</a> <br/><br/>


            Best wishes, <br/>
            The CadLine Team";

        return $msg;
    }

    public static function getWebinarReminderHun()
    {
        $msg = '<b>Kedves [NAME]</b>, <br/><br/>

            Köszönjük a jelentkezésedet a webinárra. A következő adásunk témája: <b>[TOPIC]</b> <br/><br/>

            <b>Mikor?</b> <br/>
            [DATE] <br/>
            <a href="[YT_LINK]">Itt nézheted</a> <br/><br/>

            <b>Mire lesz szükséged:</b> <br/>
            Győződj meg arról, hogy az eszközön, amin nézni fogod, van hang, továbbá egy YouTube vagy Gmail fiók is kelleni fog, ha szeretnél az adás alatt kommentálni, vagy kérdezni.<br/><br/>

            <br/>

            <b>Fontos:</b> <br/>
            Az adást fel fogjuk venni, és később is elérhető lesz a YouTube csatornánkon. Azzal, hogy részt veszel az Élő adáson, elfogadod, hogy a hozzászólásaid láthatóak lesznek a felvett anyagon. <br/><br/>

            Ha bármi kérdésed van, fordjulj hozzánk az <a href="mailto:info@cadline.hu">info@cadline.hu</a> email címen <br/><br/>

            Találkozunk az élő adásban! <br/><br/>


            Üdvözlettel,

            A CADLine csapata';

        return $msg;
    }
}

class Codegen
{
    public static function IsValidPassword($password)
    {
        if ($password == "") return false;

        $ph = popen(PATH_CODEGEN . " -isValidPassword " . $password, "r") or die("A 'codegen' paranccsal nem veheto fel kapcsolat");
        $result = fgets($ph, 1024);
        pclose($ph);

        return ($result == 'true');
    }

    public static function IsValidTimeCode($idokod)
    {
        if ($idokod == "") return false;

        $ph = popen(PATH_CODEGEN . " -isValidDealerCode " . $idokod, "r") or die("A 'codegen' paranccsal nem veheto fel kapcsolat");
        $result = fgets($ph, 1024);
        pclose($ph);

        return ($result == 'true');
    }

    public static function GetDayFromTimeCode($idokod)
    {
        $command    = PATH_CODEGEN . " -ParseTimeCode " . $idokod;
        $ph         = popen($command, "r");
        $result     = fgets($ph, 1024);
        $result     = (int)$result;
        pclose($ph);

        return $result;
    }

    public static function GeneratePassword($hlNum, $lan, $type, $version)
    {
        $country    = substr($hlNum, 0, 2);
        $number     = substr($hlNum, 2, 4);

        if ($type == 76) $type = 'L'; // Live
        else $type = (int)substr($type, 0, 1);

        $command    = PATH_CODEGEN . " -Password " . $country . $number . $version . $lan . $type;
        $ph         = popen($command, "r") or die("A 'codegen' paranccsal nem veheto fel kapcsolat");
        $password   = fgets($ph, 1024);
        pclose($ph);

        return $password;
    }

    public static function GenerateTimeCode($password, $isid, $numOfDays)
    {
        if (!self::IsSoftwareKey($password)) $isid = self::GetCountryFromPassword($password) . crm_hardlock::GetVer8And9FromPassword($password);

        $nVersion = intval(substr($password, 7, 2));
        if ($nVersion > 29) $command = PATH_CODEGEN . " -GenerateTimeCode " . $password . " " . $isid . " " . $numOfDays;
        else $command = PATH_CODEGEN . " -XDay " . $password . " " . $numOfDays;

        $command    = PATH_CODEGEN . " -GenerateTimeCode " . $password . " " . $isid . " " . $numOfDays;
        $ph         = popen($command, "r") or die("A 'codegen' paranccsal nem veheto fel kapcsolat");
        $aktkod     = fgets($ph, 1024);
        pclose($ph);

        return $aktkod;
    }

    public static function IsSoftwareKey($serial = "")
    {
        if ($serial == "") return false;

        if (self::GetCountryFromPassword($serial) == 37) { // Belso tesztekre hasznalt kulcsok a 37-esek
            if (crm_hardlock::IsLiveKey($serial)) return true;

            if (substr($serial, 0, 3) == 371) return true;
            else return false;
        }

        $ph         = popen(PATH_CODEGEN . " -IsSoftwareKey " . $serial, "r") or die("A 'codegen' paranccsal nem veheto fel kapcsolat");
        $szoftveres = fgets($ph, 1024);
        pclose($ph);

        return ($szoftveres == 'true');
    }

    public static function GetDayInterval($mettol, $meddig, $internal = false)
    {
        if ($internal) {
            $from   = strtotime($mettol . ' 00:00:00 GMT');
            $to     = strtotime($meddig . ' 00:00:00 GMT');
            $days   = $to - $from;
            $napok  = (int)floor($days / (60 * 60 * 24));

            $message = 'From: ' . $from . ' || To: ' . $to . ' || Days: ' . $napok;

            crm_hardlock::AddToDebugTable($message, 'offlineActDate');
        } else {
            $command3   = PATH_CODEGEN . " -DiffDay " . $mettol . " " . $meddig;
            $ph         = popen($command3, "r") or die("A 'codegen' paranccsal nem veheto fel kapcsolat");
            $napok      = fgets($ph, 1024);
            pclose($ph);
        }

        return $napok;
    }

    public static function GetCountryFromPassword($password)
    {
        return substr($password, 0, 2);
    }
    public static function GetCountryFromTimeCode($timeCode)
    {
        return substr($timeCode, 5, 2);
    }
}

class Webform
{
    public static function GetWebformPrivateKey()
    {
        return "3BjGQ6fzMtHupM5V8U3l_u";
    }

    public static function CalcKey($privateKey, $publicKey)
    {
        $privateKeyLength   = strlen($privateKey);
        $publicKeyLength    = strlen($publicKey);

        if ($privateKeyLength > $publicKeyLength) {
            $result = $privateKey;
            for ($i = 1; $i < $publicKeyLength; $i += 2) $result[$i] = $publicKey[$i];
        } else {
            $result = $publicKey;
            for ($i = 0; $i < $privateKeyLength; $i += 2) $result[$i] = $privateKey[$i];
        }
        return sha1($result);
    }

    public static function encode($privateKey, $publicKey, $data)
    {
        // Proba webform encode volt (C++)
        /*if ($_SERVER['REMOTE_ADDR'] == '176.63.24.91' || $_SERVER['REMOTE_ADDR'] == '94.247.91.41') {
            return twk_encode($privateKey, $publicKey, $data);
        }*/

        $hash   = '';
        $key    = self::CalcKey($privateKey, $publicKey);
        $strLen = strlen($data);
        $keyLen = strlen($key);

        for ($i = 0; $i < $strLen; $i++) {
            $ordStr = ord(substr($data, $i, 1));

            if ($j == $keyLen) $j = 0;

            $ordKey = ord(substr($key, $j, 1));
            $j++;
            $hash .= strrev(base_convert(dechex($ordStr + $ordKey), 16, 36));
        }
        return $hash;
    }

    public static function decode($privateKey, $publicKey, $data)
    {
        // Proba webform decode volt (C++)
        /*if ($_SERVER['REMOTE_ADDR'] == '176.63.24.91' || $_SERVER['REMOTE_ADDR'] == '94.247.91.41') {
            return twk_decode($privateKey, $publicKey, $data);
        }*/

        $hash   = '';
        $key    = self::CalcKey($privateKey, $publicKey);
        $strLen = strlen($data);
        $keyLen = strlen($key);

        for ($i = 0; $i < $strLen; $i += 2) {
            $ordStr = hexdec(base_convert(strrev(substr($data, $i, 2)), 36, 16));

            if ($j == $keyLen) $j = 0;

            $ordKey = ord(substr($key, $j, 1));
            $j++;
            $hash .= chr($ordStr - $ordKey);
        }
        return $hash;
    }

    public static function get_valueFromStringUrl($url, $parameter_name)
    {
        $parts = parse_url($url);
        if (isset($parts['query'])) {
            parse_str($parts['query'], $query);
            if (isset($query[$parameter_name])) return $query[$parameter_name];
            else return null;
        } else return null;
    }

    public static function SetWebformResult($session, $result, $encodedResult, $message, $button)
    {
        if (strpos($session, '\\') !== false || strpos($session, ']') !== false) {
            die();
        }

        if (strpos($session, 'xD4') !== false || strpos($session, 'x0F') !== false || strpos($session, '...') !== false) {
            die();
        }

        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`webform_results` WHERE session_id = ?";
        Database::getInstance()->query($query, array('s', $session));
        $res = Database::getInstance()->fetchAssoc();

        if (empty($res)) {
            $sess = array(
                'session_id'    => $session,
                'result'        => "{$result}={$encodedResult}",
                'insertDate'    => date("Y-m-d"),
                'message'       => $message,
                'button'        => $button
            );
            Database::getInstance()->insert(CRMADATBAZIS . ".webform_results", $sess, 'sssss');
        } else {
            Database::getInstance()->update(
                CRMADATBAZIS . ".webform_results",
                array(
                    'result'        => "{$result}={$encodedResult}",
                    'insertDate'    => date("Y-m-d")
                ),
                array('session_id' => $session),
                'sss'
            );
        }
    }

    public static function GenerateSerialXML($programCount, $password, $errorMsg)
    {
        $data = "<GetSerial>";

        if ($errorMsg != '') {
            $data .= "<Error>{$errorMsg}</Error>";
        } else {
            $data .= "<Serials>{$programCount}</Serials>";
            $data .= "<Serial>{$password}</Serial>";
        }

        $data .= "</GetSerial>";

        return $data;
    }

    public static function getLoginLanguageFile($ctrID)
    {
        switch ($ctrID) {
            case '36':
                $lang = 'hun';
                break;

            case '39':
                $lang = 'esp';
                break;

            default:
                $lang = 'eng';
                break;
        }

        return $_SERVER['DOCUMENT_ROOT'] . "/maintenance/webform/languages/{$lang}.php";
    }

    public static function getLanguageFile($lang)
    {
        $lang = $lang != '' && file_exists("languages/{$lang}.php") ? $lang : 'eng';

        return "languages/{$lang}.php";
    }
}

class Resource extends Database
{
    private $ctrID;
    private $table;
    private $regex;

    private $region     = 'INT';
    private $regionType = 17;

    private $tables = array(
        'ref'   => 'resourcemanager.text_table_ref',
        '36'    => 'resourcemanager.text_table_hun',
        '39'    => 'resourcemanager.text_table_ita',
        '49'    => 'resourcemanager.text_table_ger',
        '43'    => 'resourcemanager.text_table_ger',
        '48'    => 'resourcemanager.text_table_pol',
        '82'    => 'resourcemanager.text_table_kor',
        '34'    => 'resourcemanager.text_table_esp',
        '30'    => 'resourcemanager.text_table_gre',
        '42'    => 'resourcemanager.text_table_cz',
        '38'    => 'resourcemanager.text_table_cr',
        '88'    => 'resourcemanager.text_table_chn',
        '83'    => 'resourcemanager.text_table_zh_cn',
        '47'    => 'resourcemanager.text_table_nor',
        '33'    => 'resourcemanager.text_table_fr',
        '51'    => 'resourcemanager.text_table_pt',
        '55'    => 'resourcemanager.text_table_pt',
        '12'    => 'resourcemanager.text_table_bg'
    );

    public function __construct($ctrID, $rgn = 'INT')
    {
        parent::__construct();

        unset($this->ctrID);

        $this->ctrID = $ctrID;

        $this->setCountryTable();
        $this->setRegex();
        $this->setRegion($rgn);
    }

    private function setCountryTable()
    {
        $this->table = array_key_exists($this->ctrID, $this->tables) ? $this->tables[$this->ctrID] : $this->tables['ref'];
    }

    private function setRegex()
    {
        $delimiter = '#';
        $startTag = '$(';
        $endTag = ')';
        $this->regex = $delimiter . preg_quote($startTag, $delimiter)
            . '(.*?)'
            . preg_quote($endTag, $delimiter)
            . $delimiter
            . 's';
    }

    private function setRegion($rgn = 'INT')
    {
        $query = 'SELECT ref.text AS langText, ref.text AS refText, ref.key AS refKey
            FROM resourcemanager.text_table_ref ref
            WHERE ref.type_id = ? AND ref.key LIKE ? LIMIT 1;';
        Database::getInstance()->query($query, array('is', $this->regionType, '%_' . $rgn));
        $checkRegion = Database::getInstance()->fetchNext();
        if (!empty($checkRegion)) {
            $this->region = strtoupper($rgn);
        }
    }

    private function getRegionResult($text)
    {
        if ($this->table == 'resourcemanager.text_table_ref') {
            $query = 'SELECT ref.text AS langText, ref.text AS refText, ref.key AS refKey
                FROM resourcemanager.text_table_ref ref
                WHERE ref.type_id = ? AND ref.key = ?;';
        } else {
            $query = 'SELECT res.text AS langText, ref.text AS refText, ref.key AS refKey
                FROM resourcemanager.text_table_ref ref
                LEFT JOIN ' . $this->table . ' res ON res.ref_id = ref.id
                WHERE ref.type_id = ? AND ref.key = ?;';
        }
        Database::getInstance()->query($query, array('is', $this->regionType, $text . '_' . $this->region));

        return Database::getInstance()->fetchNext();
    }

    public function getDlgString($key, $year = '', $needNewLine = false)
    {
        $text = '';

        $query = "SELECT * FROM resourcemanager.text_table_ref ref WHERE ref.type_id = ? AND ref.key = ? LIMIT 1";
        $this->query($query, array('is', 1, $key));
        $ref = $this->fetchNext();

        if ($this->table == 'resourcemanager.text_table_ref') {
            $text = $ref['text'];
        } else {
            $query = "SELECT * FROM {$this->table} res WHERE res.ref_id = ? LIMIT 1";
            $this->query($query, array('i', $ref['id']));
            $res = $this->fetchNext();

            $text = $res['text'] == '' ? $ref['text'] : $res['text'];
        }

        $preg = preg_match($this->regex, $text, $matches);
        if (strpos($text, '$(PROGRAM_VERSION)') == false && $preg) {
            $regionResult = $this->getRegionResult($matches[1]);
            if (!empty($regionResult)) {
                $regionText = $regionResult['langText'] != '' ? $regionResult['langText'] : $regionResult['refText'];
                $text       = str_replace($matches[0], $regionText, $text);
            }
        }

        if ($year != '') {
            $progVersion = 'ARCHLine.XP ' . $year;
            $text = str_replace('$(PROGRAM_VERSION)', $progVersion, $text);
        }

        return $needNewLine ? nl2br($text) : $text;
    }
}

class Tester
{
    public static function getFalseTestResults()
    {
        $now        = date('Y-m-d H:i');
        $new_time   = substr(date("Y-m-d H:i", strtotime('-12 hours', strtotime($now))), 0, 13);

        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`telemetria` WHERE substring(mod_datetime, 1, 13) >= ?
            AND error_code != ? AND error_code != ?";
        Database::getInstance()->query($query, array('sii', $new_time, -1, 0));
        $res = Database::getInstance()->fetchAssoc();

        return $res;
    }

    public static function checkLicenceSettings()
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prContact = ? AND prFizetve != ? AND prVerID > ? AND prHlNum NOT LIKE ?
            AND prHlNum IN (SELECT prHlNum FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prVerID = ? AND prFizetve = ?)";
        Database::getInstance()->query($query, array('isisis', 0, "2050-01-01", 19, "39%", 19, "2050-01-01"));
        $programs = Database::getInstance()->fetchAssoc();

        return $programs;
    }

    public static function checkLicenceSettingsWithInOneAmount()
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prFizetve != ? AND prHlNum NOT LIKE ? AND prVerID >= ?
            AND (prContact = ? || prContact = ?)";
        Database::getInstance()->query($query, array('ssiii', "2050-01-01", "39%", 19, 0, 2));
        $programs = Database::getInstance()->fetchAssoc();

        return $programs;
    }

    public static function checkLicenceSettingsWithInstallment()
    {
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` 
            WHERE prHlNum NOT LIKE ? AND prActCode IS NOT NULL AND prActCode != ''
            AND prInvoiceDateTo != SUBSTR(prFizetve, 1, 7) AND prFizetve != '2050-01-01' AND prVerID >= ? AND (prContact = ? || prContact = ?)";
        Database::getInstance()->query($query, array('siii', "39%", 19, 3, 7));
        $programs = Database::getInstance()->fetchAssoc();

        return $programs;
    }

    public static function getCronTime()
    {
        $action = Time::sMin; // A default ertek az 1 perc
        $minute = date('i');
        $hour   = date('H');

        if (($minute % Time::sFiveMin) == 0)                    $action = Time::sFiveMin;       // 5 perc
        if (($minute % Time::sFifteenMin) == 0)                 $action = Time::sFifteenMin;    // 15 perc
        if (($hour % Hour::sHour) == 0 && $minute == 00)        $action = Time::sHour;          // 1 ora
        if (($hour % Hour::sThreeHour) == 0 && $minute == 00)   $action = Time::sThreeHour;     // 3 ora
        if (($hour % Hour::sSixHour) == 0 && $minute == 00)     $action = Time::sSixHour;       // 6 ora
        if (($hour % Hour::sTwelveHour) == 0 && $minute == 00)  $action = Time::sTwelveHour;    // 12 ora
        if ($hour == 00 && $minute == 00)                       $action = Time::sDay;           // 1 nap

        return $action;
    }

    public static function calculateElapsedTime($time_start)
    {
        $time_end       = microtime(true);
        $execution_time = $time_end - $time_start;

        return "{$execution_time} seconds";
    }

    public static function oneMinute()
    {
        $result = '';

        $query = "SELECT prPass, prActCode FROM `" . CRMADATBAZIS . "`.`h_programs` where prActCode IS NOT NULL AND prActCode != '' AND prActCode NOT LIKE ? AND prVerID > ?";
        Database::getInstance()->query($query, array('si', "____-____-___-____", 7));
        $invalid_actcodes = Database::getInstance()->fetchAssoc();

        // TODO: Hexadecimalis regex
        $query = "SELECT prPass, prHlNum FROM `" . CRMADATBAZIS . "`.`h_programs`  WHERE LENGTH(prPass) != ?;";
        //  $query = "SELECT prPass, prHlNum FROM `" . CRMADATBAZIS . "`.`h_programs`  WHERE (prPass REGEXP '.*[a-z]+' AND SUBSTRING(prPass, 7, 1) != ?) || LENGTH(prPass) != ?;";
        Database::getInstance()->query($query, array('si', "L", 16));
        $invalid_passwords = Database::getInstance()->fetchAssoc();

        if (empty($invalid_actcodes) && empty($invalid_passwords)) return '';

        foreach ($invalid_actcodes as $program)     $result .= $program['prPass'] . ': Invalid ActCode: ' . $program['prActCode'] . '\n';
        foreach ($invalid_passwords as $program)    $result .= $program['prHlNum'] . ': Invalid Password: ' . $program['prPass'] . '\n';

        if ($result != '') $result = "One minute: \n {$result}";
        return $result;
    }

    public static function fiveMinutes()
    {
        $result = '';

        if ($result != '') $result = "Five minutes: \n {$result}";
        return $result;
    }

    public static function fifteenMinutes()
    {
        $result = '';

        if ($result != '') $result = "Fifteen minutes: \n {$result}";
        return $result;
    }

    public static function oneHour()
    {
        $result = '';

        if ($result != '') $result = "One hour: \n {$result}";
        return $result;
    }

    public static function threeHour()
    {
        $result = '';

        $query = "SELECT p.serial AS prPass, COUNT(p.serial) AS cnt
            FROM cl_hlusers.p_serial_isid p
            WHERE  p.aktiv = ? AND (p.serial LIKE ? OR p.serial LIKE ?)
            AND p.serial IN (SELECT prPass FROM cl_hlusers.h_programs WHERE prLan = ? AND (prTypeID = ? OR prTypeID = ?))
            GROUP BY p.serial
            HAVING cnt > 1;";
        Database::getInstance()->query($query, array('issiii', 1, '95%', '99%', 0, 8, 6));
        $programs = Database::getInstance()->fetchAssoc();

        if (empty($programs)) return '';

        if (crm_hardlock::isFloating($programs[0]['prPass']))
            return '';

        foreach ($programs as $program) $result .= $program['prPass'] . ': Too much activation: ' . $program['cnt'] . '\n';

        if ($result != '') $result = "Three hour: \n {$result}";
        return $result;
    }

    public static function sixHour()
    {
        $result = '';

        /*$programs = self::checkLicenceSettingsWithInstallment();
        foreach ($programs as $program) $result .= $program['prPass'] . ': Wrong activation code (With Installment): ' . $program['prInvoiceDateTo'] . ' - ' . $program['prFizetve'] . '\n';

        if ($result != '') $result = "Six hour: \n {$result}";*/
        return $result;
    }

    public static function twelveHour()
    {
        $result = '';

        $telemetria = self::getFalseTestResults();
        foreach ($telemetria as $tm) $result .= $tm['Pwd'] . ': False test reult: ' . $tm['error_code'] . '\n';

        if ($result != '') $result = "Twelve hour: \n {$result}";
        return $result;
    }

    public static function oneDay()
    {
        $result = "One day: \n";
        $array = array();

        $query = "SELECT ctrID FROM `" . CRMADATBAZIS . "`.`countries` WHERE show_h = ? AND ctrID NOT IN (?, ?, ?, ?, ?, ?, ?, ?, ?)
            ORDER BY RAND()
            LIMIT 5";
        Database::getInstance()->query($query, array('iiiiiiiiii', 1, 41, 7, 421, 1, 2, 37, 99, 95, 94));
        $countries = Database::getInstance()->fetchAssoc();

        foreach ($countries as $country) array_push($array, $country['ctrID']);

        $imploded = implode(", ", $array);

        $query = "SELECT prPass FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prVerID >= ? AND prCtrID IN ({$imploded})";
        Database::getInstance()->query($query, array('i', 18));
        $programs = Database::getInstance()->fetchAssoc();

        foreach ($programs as $program) {
            $error = crm_hardlock::sCheckPassword($program['prPass']);
            if ($error != '') $result .= $program['prPass'] . ' - ' . $error . '\n';
        }
        return $result;
    }

    public static function syncSwitchTable()
    {
        $truncateQuery = "TRUNCATE TABLE " . CRMADATBAZIS . ".switch";
        Database::getInstance()->query($truncateQuery);

        $insertQuery = "INSERT INTO `" . CRMADATBAZIS . "`.switch
            SELECT h.hlUser,CONCAT(',',group_concat(p.prVerID),',') AS prVerID
            FROM (
                SELECT hlUser,hlNum 
                FROM `" . CRMADATBAZIS . "`.`hardlock` 
                WHERE hlStat=1 AND CONVERT(hlUser,UNSIGNED)>0 AND LENGTH(hlUser)<=6 AND hlUser>=1 AND hlUser<=999999
                ) h
            LEFT OUTER JOIN (
                SELECT prHlNum AS hlNum,MAX(prVerID) AS prVerID 
                FROM (SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prStat=0) AS `h_programs`
                GROUP BY prHlNum
                ) p ON h.hlNum=p.hlNum 
            WHERE prVerID IS NOT NULL
            GROUP BY h.hlUser 
            ORDER BY hlUser;";
        Database::getInstance()->query($insertQuery);

        $deleteQuery = "DELETE FROM `" . CRMADATBAZIS . "`.writelog WHERE `datum`<date_add(CURRENT_TIMESTAMP,INTERVAL -30 day) LIMIT 1000;";
        Database::getInstance()->query($deleteQuery);
    }
}

class Student extends Database
{
    private $nUserID;   // Diak CRM-ben levo ID-ja
    private $name;      // Diak neve
    private $email;     // Diak email cime
    private $phone;     // Diak telefonszama
    private $endDate;   // Vegzes ideje
    private $education; // Kepzes helye es ideje
    private $status;    // Diak statusza (aktiv - passziv)

    private $courseID;      // Kurzus ID-ja
    private $courseBegin;   // Kurzus kezdete
    private $courseEnd;     // Kurzus vege
    private $courseCode;    // Kurzus kodja
    private $version;       // AL verzioja (evjarat)

    private $schoolID;      // Iskola ID-ja
    private $nSchoolID;     // Iskola CRM-ben levo ID-ja
    private $username;      // Iskola felhasznaloneve
    private $schoolName;    // Iskola neve
    private $schoolLang;    // Magyar vagy angol
    public $schoolEmail;    // Az iskola email cime

    public function __construct()
    {
        parent::__construct();
        $this->clear();
    }

    private function clear()
    {
        foreach ($this as &$value) unset($value);
    }

    public function setUsername($username)
    {
        $this->username       = $username;
    }
    public function setName($name)
    {
        $this->name           = $name;
    }
    public function setPhone($phone)
    {
        $this->phone          = $phone;
    }
    public function setnSchoolID($nSchoolID)
    {
        $this->nSchoolID      = $nSchoolID;
    }
    public function setSchoolID($schoolID)
    {
        $this->schoolID       = $schoolID;
    }
    public function setSchoolName($schoolName)
    {
        $this->schoolName     = $schoolName;
    }
    public function setSchoolLang($schoolLang)
    {
        $this->schoolLang     = $schoolLang;
    }
    public function setSchoolEmail($email)
    {
        $this->schoolEmail    = $email;
    }
    public function setCourseID($courseID)
    {
        $this->courseID       = $courseID;
    }
    public function setCourseEnd($courseEnd)
    {
        $this->courseEnd      = $courseEnd;
    }
    public function setCourseBegin($courseBegin)
    {
        $this->courseBegin    = $courseBegin;
    }
    public function setCourseCode($courseCode)
    {
        $this->courseCode     = $courseCode;
    }
    public function setEducation($education)
    {
        $this->education      = $education;
    }
    public function setEndDate($endDate)
    {
        $this->endDate        = $endDate;
    }
    public function setVersion($version)
    {
        $this->version        = $version;
    }
    public function setStatus($status)
    {
        $this->status         = $status;
    }

    public function setEmail($email)
    {
        $email        = str_replace("\n", '', $email);
        $email        = str_replace('\n', '', $email);
        $email        = str_replace(';', '', $email);
        $this->email  = str_replace(' ', '', $email);
    }

    public function setCourse()
    {
        $course = $this->getCourseByID();
        $this->setCourseEnd($course['end']);
        $this->setVersion($course['version']);
        $this->setCourseBegin($course['begin']);
        $this->setCourseCode($course['course_code']);
        $this->setEndDate($course['vegzes_ideje']);
    }

    public function getSchoolByCourse()
    {
        $sql = "SELECT school.name AS school_name, course.name AS course_name
            FROM `" . CRMADATBAZIS . "`.`schools` school
            LEFT JOIN `" . CRMADATBAZIS . "`.`courses` course ON course.school_id = school.id
            WHERE course.id = ?";

        $this->query($sql, array('i', $this->courseID));

        return $this->fetchNext();
    }

    public function studentInOtherSchool($schoolId, $otherSchoolId)
    {
        return $schoolId != $otherSchoolId;
    }

    public function getCourseByID()
    {
        $sql = "SELECT course_code, vegzes_ideje, version, courses.to AS end, courses.from AS begin, school_id FROM `" . CRMADATBAZIS . "`.`courses` WHERE id = ?";
        $this->query($sql, array('i', $this->courseID));
        return $this->fetchNext();
    }

    public function setSchool()
    {
        $school = $this->getSchoolByID();
        $this->setUsername($school['username']);
        $this->setnSchoolID($school['nSchoolID']);
        $this->setSchoolName($school['name']);
        $this->setSchoolLang($school['lang']);
        $this->setSchoolEmail($school['email']);
    }

    public function getSchoolByID()
    {
        $sql = "SELECT username, nSchoolID, name, lang, email FROM `" . CRMADATBAZIS . "`.`schools` WHERE id = ?";
        $this->query($sql, array('i', $this->schoolID));
        return $this->fetchNext();
    }

    public function getnUserID()
    {
        return $this->nUserID;
    }

    public function phoneCorrect()
    {
        $phone = $this->phone;
        $phone = str_replace('-', '', $phone);
        $phone = str_replace(' ', '', $phone);
        $phone = str_replace('+', '', $phone);
        $phone = str_replace('/', '', $phone);
        $phone = str_replace('.', '', $phone);
        $phone = str_replace('  ', '', $phone);

        return $phone;
    }

    public function getStudent($userid, &$response = array())
    {
        $query  = "SELECT * FROM `" . CRMADATBAZIS . "`.`students` WHERE id = ?";
        $this->query($query, array('i', $userid));
        $students = $this->fetchAssoc();

        if (!empty($students)) foreach ($students as $student) $response = $student;
        else {
            $response["status"]     = 200;
            $response["message"]    = "Data not found!";
        }
    }

    public function getStudentStatus($status)
    {
        $statusStr = '';

        if ($_SERVER['HTTP_HOST'] == 'www.archline.hu') {
            if ($status == 0)
                $statusStr = 'Passzív';
            elseif ($status == 1)
                $statusStr = 'Aktív';
            else
                $statusStr = 'Levizsgázott';
        } else {
            if ($status == 0)
                $statusStr = 'Passive';
            elseif ($status == 1)
                $statusStr = 'Active';
            else
                $statusStr = 'Passed the exam';
        }

        return $statusStr;
    }

    public function getProgramStarts($pwd)
    {
        if (is_array($pwd))
            return 0;

        $sql = "SELECT SUM(usInditasokSzama) AS count FROM `" . CRMADATBAZIS . "`.`userstats2013` WHERE usPwd = '" . $pwd . "'";
        $this->query($sql);
        $userstats = $this->fetchNext();

        return !empty($userstats) ? $userstats['count'] : 0;
    }

    public function getAllStudents($search, $courseArray)
    {
        $isSearch = isset($search) && $search != null && $search != '' ? TRUE : FALSE;

        $course = $this->getCourseByID();

        $sql = "SELECT verID FROM `" . CRMADATBAZIS . "`.`p_versions` WHERE year = ?";
        $this->query($sql, array('i', $course['version']));
        $version = $this->fetchNext();

        if (empty($courseArray)) {
            $sql = "SELECT s.nUserID AS nUserID, s.name AS name, s.status AS status, s.email_sent AS email_sent, s.email AS email, s.phone AS phone, s.id AS student_id, c.name AS course_name
            FROM `" . CRMADATBAZIS . "`.`students` s
            LEFT JOIN `" . CRMADATBAZIS . "`.`courses` c ON c.id = s.course_id
            WHERE s.course_id = ?";

            if ($isSearch) $sql .= " AND s.name LIKE ?";
            $sql .= " ORDER BY s.name ASC;";

            if ($isSearch) $this->query($sql, array('is', $this->courseID, "%$search%"));
            else $this->query($sql, array('i', $this->courseID));
        } else {
            $coursesString = implode(", ", $courseArray);

            $sql = "SELECT s.nUserID AS nUserID, s.name AS name, s.status AS status, s.email_sent AS email_sent, s.email AS email, s.phone AS phone, s.id AS student_id, c.name AS course_name
                FROM `" . CRMADATBAZIS . "`.`students` s
                LEFT JOIN `" . CRMADATBAZIS . "`.`courses` c ON c.id = s.course_id
                WHERE s.course_id in ({$coursesString})";

            if ($isSearch) $sql .= " AND s.name LIKE ?";
            $sql .= " ORDER BY s.name ASC;";

            if ($isSearch) $this->query($sql, array('s', "%$search%"));
            else $this->query($sql);
        }

        $students       = $this->fetchAssoc();
        $studentsArray  = array();

        if (!empty($students)) {
            foreach ($students as $student) {
                $sql = "SELECT h.hlNum, p.prPass AS prPass, h.product_id AS product, h.hlUser
                    FROM `" . CRMADATBAZIS . "`.`hardlock` h
                    LEFT JOIN `" . CRMADATBAZIS . "`.`h_programs` p ON p.prHlNum = h.hlNum
                    WHERE h.hlUser = ? AND p.prPass LIKE ? AND p.prVerID = ? AND h.product_id = ? GROUP BY h.product_id LIMIT 1;";
                $this->query($sql, array('isii', $student['nUserID'], '99%', $version['verID'], 1));
                $hardlock = $this->fetchNext();

                $prPass     = "";

                if (!empty($hardlock)) {
                    $prPass = $hardlock['prPass'];
                    if ($hardlock['product'] == _Product::Professional) 
                        $prPass = "ARCHLine.XP: <b>{$prPass}</b>";
                }

                if ($prPass == '') {
                    if ($_SERVER['HTTP_HOST'] == 'www.archline.hu')
                        $prPass = 'Nincs ARCHLine.XP sorozatszám';
                    else
                        $prPass = 'No ARCHLine.XP serial number';
                }

                $studentsArray[] = array(
                    "name"        => $student["name"],
                    "email"       => $student["email"],
                    "phone"       => $student["phone"],
                    "student_id"  => $student["student_id"],
                    "course_name" => $student["course_name"],
                    "email_sent"  => $student["email_sent"],
                    "status"      => $student["status"],
                    "prPass"      => $prPass
                );
            }
        } else $studentsArray = "empty";

        return $studentsArray;
    }

    public function addStudent()
    {
        define('_JEXEC', 1);
        define('JPATH_BASE', '../');
        require_once JPATH_BASE . 'includes/defines.php';
        require_once JPATH_BASE . 'includes/framework.php';

        jimport('joomla.user.helper');

        $begin              = str_replace('-', '.', $this->courseBegin);
        $end                = str_replace('-', '.', $this->endDate);
        $this->education    = $this->username . " " . $this->courseCode . " " . $begin . " - " . $end;
        $hunInfo            = 'Weben regisztrált - ' . $this->username . " " . $this->courseCode . " " . $begin;

        $sql = "SELECT * FROM `" . JMLADATBAZIS . "`.`jml_users` WHERE username = ? OR email = ?";
        $this->query($sql, array('ss', $this->email, $this->email));
        $jml_students = $this->fetchNext();

        $phone = $this->phoneCorrect();

        if (empty($jml_students)) {
            $sql = "SELECT nUserID FROM `" . CRMADATBAZIS . "`.`u_emails` WHERE email = ?";
            $this->query($sql, array('s', $this->email));
            $stud = $this->fetchNext();

            if (empty($stud)) {
                $user = array(
                    'strName'       => $this->name,
                    'strEMail'      => $this->email,
                    'strTel'        => $phone,
                    'nCtrID'        => _Country::Hungary,
                    'nSchoolID'     => $this->nSchoolID,
                    'nUserType'     => _UserType::Nonprofit,
                    'nUserProf'     => _Profession::StudentIntDesinger,
                    'nUserStatus'   => _UserStatus::StudentProbBuyer,
                    'strEducation'  => $this->education,
                    'vegzes_ideje'  => $this->courseEnd,
                    'old_db'        => $this->schoolLang == 'hun' ? 'clusers' : 'clusers_eng'
                );
                $this->nUserID = $this->insert(CRMADATBAZIS . ".users", $user, 'sssiiiiisss');

                $u_email = array(
                    'email'       => $this->email,
                    'nUserID'     => $this->nUserID,
                    'default'     => 1,
                    'active'      => 1,
                    'deleted'     => 0,
                    'newsletter'  => 1
                );
                $this->insert(CRMADATBAZIS . ".u_emails", $u_email, 'siiiii');

                $userHistory = array(
                    'nUserID'       => $this->nUserID,
                    'nTopicID'      => $_SERVER['HTTP_HOST'] == 'www.archline.hu' ? 362 : 219,
                    'dateEvent'     => date('Y-m-d'),
                    'strPlace'      => 'Web',
                    'strInfo'       => $_SERVER['HTTP_HOST'] == 'www.archline.hu' ? $hunInfo : $this->education,
                    'nManagerID'    => _Manager::Web,
                    'insertDate'    => date('Y-m-d H:i:s')
                );
                $this->insert(CRMADATBAZIS . ".u_history", $userHistory, 'iisssis');
            } else {
                $this->nUserID = $stud['nUserID'];

                $sql = "SELECT strTel FROM `" . CRMADATBAZIS . "`.`users` WHERE nUserID = ?";
                $this->query($sql, array('i', $this->nUserID));
                $stud = $this->fetchNext();
                $tel = $stud['strTel'];

                if (strpos($tel, $phone) === false || strpos($tel, substr($phone, 2)) === false) $tel .= ";{$phone}";

                $this->update(
                    CRMADATBAZIS . ".users",
                    array(
                        'vegzes_ideje'  => $this->courseEnd,
                        'nSchoolID'     => $this->nSchoolID,
                        'strTel'        => $tel,
                        'strEducation'  => $this->education
                    ),
                    array('nUserID' => $this->nUserID),
                    'sissi'
                );
            }

            $reg_date       = date('Y-m-d h:i:s');
            $group          = 2;
            $exploded_name  = explode(" ", $this->name);
            $exploded_email = explode("@", $this->email);
            $salt           = JUserHelper::genRandomPassword(32);
            $crypt          = JUserHelper::getCryptedPassword($exploded_email[0], $salt);
            $password       = "{$crypt}:{$salt}";
            $privacy        = '{"privacy_username":"0","privacy_firstname":"0","privacy_lastname":"0","privacy_email":"0","privacy_password":"0","privacy_phone":"0","privacy_profession":"0","privacy_country":"0","privacy_city":"0","privacy_address":"0","privacy_zip":"0","privacy_gdpr":"0","privacy_old_db":"0"}';

            $jml_user = array(
                'name'          => $this->name,
                'username'      => $this->email,
                'email'         => $this->email,
                'password'      => $password,
                'registerDate'  => $reg_date,
                'nUserID'       => $this->nUserID
            );

            $jsn_user = array(
                'privacy'     => $privacy,
                'firstname'   => $exploded_name[0],
                'lastname'    => $exploded_name[1],
                'phone'       => $this->phone,
                'profession'  => _Profession::StudentIntDesinger,
                'country'     => _Country::Hungary,
                'gdpr'        => '["1"]'
            );
            $this->insert(JMLADATBAZIS . ".jml_jsn_users", $jsn_user, 'ssssiiss');
            $userID = $this->insert(JMLADATBAZIS . ".jml_users", $jml_user, 'sssssi');
            $this->insert(JMLADATBAZIS . ".jml_user_usergroup_map", array("user_id" => $userID, "group_id" => $group), 'ii');
        } else {
            $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`users` WHERE nUserID = ? LIMIT 1";
            $this->query($sql, array('i', $jml_students['nUserID']));
            $usr = $this->fetchNext();

            if (empty($usr)) {
                $user = array(
                    'strName'       => $this->name,
                    'strEMail'      => $this->email,
                    'strTel'        => $phone,
                    'nCtrID'        => _Country::Hungary,
                    'nSchoolID'     => $this->nSchoolID,
                    'nUserType'     => _UserType::Nonprofit,
                    'nUserProf'     => _Profession::StudentIntDesinger,
                    'nUserStatus'   => _UserStatus::StudentProbBuyer,
                    'strEducation'  => $this->education,
                    'vegzes_ideje'  => $this->courseEnd,
                    'old_db'        => $this->schoolLang == 'hun' ? 'clusers' : 'clusers_eng'
                );
                $this->nUserID = $this->insert(CRMADATBAZIS . ".users", $user, 'sssiiiiisss');

                $u_email = array(
                    'email'       => $this->email,
                    'nUserID'     => $this->nUserID,
                    'default'     => 1,
                    'active'      => 1,
                    'deleted'     => 0,
                    'newsletter'  => 1
                );
                $this->insert(CRMADATBAZIS . ".u_emails", $u_email, 'siiiii');
                $this->update(JMLADATBAZIS . ".jml_users", array('nUserID' => $this->nUserID), array('id' => $jml_students['id']), 'ii');

                $userHistory = array(
                    'nUserID'       => $this->nUserID,
                    'nTopicID'      => $_SERVER['HTTP_HOST'] == 'www.archline.hu' ? 362 : 219,
                    'dateEvent'     => date('Y-m-d'),
                    'strPlace'      => 'Web',
                    'strInfo'       => $_SERVER['HTTP_HOST'] == 'www.archline.hu' ? $hunInfo : $this->education,
                    'nManagerID'    => _Manager::Web,
                    'insertDate'    => date('Y-m-d H:i:s')
                );
                $this->insert(CRMADATBAZIS . ".u_history", $userHistory, 'iisssis');
            } else {
                $this->nUserID = $usr['nUserID'];

                $sql = "SELECT strTel FROM `" . CRMADATBAZIS . "`.`users` WHERE nUserID = ?";
                $this->query($sql, array('i', $this->nUserID));
                $stud = $this->fetchNext();
                $tel = $stud['strTel'];

                if (strpos($tel, $phone) === false || strpos($tel, substr($phone, 2)) === false) $tel .= ";{$phone}";

                $this->update(
                    CRMADATBAZIS . ".users",
                    array(
                        'vegzes_ideje'  => $this->courseEnd,
                        'nSchoolID'     => $this->nSchoolID,
                        'strTel'        => $tel,
                        'strEducation'  => $this->education
                    ),
                    array('nUserID' => $this->nUserID),
                    'sissi'
                );
            }
        }

        $student = array(
            'name'      => $this->name,
            'email'     => $this->email,
            'phone'     => $this->phone,
            'school_id' => $this->schoolID,
            'course_id' => $this->courseID,
            'nUserID'   => $this->nUserID
        );
        $this->insert(CRMADATBAZIS . ".students", $student, 'sssiii');
    }

    public function deleteStudent($userid)
    {
        $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`students` WHERE id = ?";
        $this->query($sql, array('i', $userid));
        $student = $this->fetchNext();
        $nUserID = $student['nUserID'];

        $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`courses` WHERE id = ?";
        $this->query($sql, array('i', $student['course_id']));
        $studentCourse = $this->fetchNext();

        $sql = "SELECT id FROM `" . CRMADATBAZIS . "`.`courses` WHERE school_id = ? AND is_archive = ?";
        $this->query($sql, array('ii', $student['school_id'], 1));
        $archiveCourse = $this->fetchNext();

        if ($studentCourse['is_archive'] == 0 && !empty($archiveCourse)) {
            $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlUser = ? AND hlNum LIKE ?";
            $this->query($sql, array('is', $nUserID, '99%'));
            $hardlock = $this->fetchAssoc();

            if (!empty($hardlock))
                foreach ($hardlock as $hl) crm_hardlock::DeactivateHardlock($hl['hlNum'], _Manager::Web, "Iskolai felület: kulcs letiltása", date('Y-m-d', time()));

            $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`users` WHERE nUserID = ?";
            $this->query($sql, array('i', $nUserID));
            $user = $this->fetchNext();
            $strEducation = $user['strEducation'] . " Törölve";

            $this->update(CRMADATBAZIS . ".users", array('strEducation' => $strEducation, 'nSchoolID' => 0), array('nUserID' => $nUserID), 'sii');
            $this->update(CRMADATBAZIS . ".students", array('course_id' => $archiveCourse['id']), array('id' => $userid), 'ii');
        } else {
            $this->delete(CRMADATBAZIS . '.students', array('id' => $userid), 'i');
        }
    }

    public function updateStudent($userid, $isDelete = false)
    {
        $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`students` WHERE id = ?";
        $this->query($sql, array('i', $userid));
        $stud           = $this->fetchNext();
        $nUserID        = $stud['nUserID'];
        $this->nUserID  = $nUserID;
        $changed        = $this->status != $stud['status'];

        if ($this->courseID != $stud['course_id']) $this->status = 1;

        if ($this->courseID != $stud['course_id'] || $changed || $this->status == 2) {
            if ($changed && $this->status == 0) $this->education .= ' Passzív';
            if ($changed && $this->status == 2) $this->education .= ' Levizsgázott';
            $this->update(CRMADATBAZIS . ".users", array('strEducation' => $this->education, 'nSchoolID' => $this->nSchoolID), array('nUserID'  => $nUserID), 'sii');

            if ($changed && $this->status == 0)
                $this->update(CRMADATBAZIS . ".users", array('nSchoolID' => 0), array('nUserID' => $nUserID), 'ii');

            if ($isDelete) {
                $this->delete(CRMADATBAZIS . '.students', array('id' => $userid), 'i');
                return;
            }

            if ($this->courseID != $stud['course_id']) {
                $this->generateStudentPassword();
            } else {
                $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlUser = ? AND hlNum LIKE ?";
                $this->query($sql, array('is', $nUserID, '99%'));
                $hardlock = $this->fetchAssoc();

                if (!empty($hardlock) && $this->status != 2) {
                    $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`courses` WHERE id = ?";
                    $this->query($sql, array('i', $this->courseID));
                    $next_course = $this->fetchNext();

                    foreach ($hardlock as $hl) {
                        if ($this->status == 0 && $hardlock['hlStat'] != 2) {
                            crm_hardlock::DeactivateHardlock($hl['hlNum'], _Manager::Web, "Iskolai felület: kulcs letiltása", date('Y-m-d', time()));
                            continue;
                        } elseif ($this->status == 1 && $hardlock['hlStat'] != 1) {
                            crm_hardlock::ActivateHardlock($hl['hlNum'], _Manager::Web, "Iskolai felület: kulcs aktiválása");

                            if ($this->courseID == $stud['course_id']) continue;
                        }

                        $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prHlNum = ? ORDER BY prVerID DESC";
                        $this->query($sql, array('s', $hl['hlNum']));
                        $program = $this->fetchNext();

                        if (!empty($program)) {
                            if ($program['prActCode'] != "" && $program['prActCode'] != NULL) {
                                $isid = crm_hardlock::GetIsidFromTimeCode($program['prActCode']);

                                $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ?";
                                $this->query($sql, array('ss', $program['prPass'], $isid));
                                $p_serial_isid = $this->fetchNext();

                                if ($program['twtw'] == 1)
                                    $installDate = crm_hardlock::getTwTwRootTimeString();
                                else
                                    $installDate = substr($p_serial_isid['datum'], 0, 10);

                                if ($next_course['to'] < $installDate)
                                    $dayInterval = Codegen::GetDayInterval($installDate, $installDate);
                                else
                                    $dayInterval = Codegen::GetDayInterval($installDate, $next_course['to']);

                                $timeCode = Codegen::GenerateTimeCode($program['prPass'], $isid, $dayInterval);

                                $this->update(CRMADATBAZIS . ".h_programs", array('prFizetve' => $next_course['to'], 'prActCode' => $timeCode), array('prID' => $program['prID']), 'ssi');
                            } else {
                                $this->update(CRMADATBAZIS . ".h_programs", array('prFizetve' => $next_course['to']), array('prID' => $program['prID']), 'si');
                            }
                        }
                    }
                }
            }
        }

        $this->update(
            CRMADATBAZIS . ".students",
            array(
                'name'      => $this->name,
                'email'     => $this->email,
                'phone'     => $this->phone,
                'status'    => $this->status,
                'course_id' => $this->courseID
            ),
            array('id'  => $userid),
            'sssiii'
        );

        $sql = "SELECT strTel FROM `" . CRMADATBAZIS . "`.`users` WHERE nUserID = ?";
        $this->query($sql, array('i', $nUserID));
        $stud   = $this->fetchNext();
        $tel    = $stud['strTel'];
        $phone  = $this->phoneCorrect();

        if (strpos($tel, $phone) === false || strpos($tel, substr($phone, 2)) === false) $tel .= ";{$phone}";

        $this->update(
            CRMADATBAZIS . ".users",
            array(
                'strName'       => $this->name,
                'strEmail'      => $this->email,
                'strEducation'  => $this->education,
                'vegzes_ideje'  => $this->courseEnd,
                'strTel'        => $tel
            ),
            array('nUserID' => $nUserID),
            'sssssi'
        );

        $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`u_emails` WHERE email = ?";
        $this->query($sql, array('s', $this->email));
        $emails = $this->fetchNext();

        if (empty($emails)) {
            $u_email = array(
                'email'       => $this->email,
                'nUserID'     => $nUserID,
                'default'     => 1,
                'active'      => 1,
                'deleted'     => 0,
                'newsletter'  => 1
            );
            $this->insert(CRMADATBAZIS . ".u_emails", $u_email, 'siiiii');
        }
    }

    public function generateStudentPassword()
    {
        $generate_live  = false;
        $generate_xp    = false;

        $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_versions` WHERE year = ?";
        $this->query($sql, array('i', $this->version));
        $ver = $this->fetchNext();

        $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlUser = ? AND hlNum LIKE ?";
        $this->query($sql, array('is', $this->nUserID, '99%'));
        $hl_user = $this->fetchAssoc();

        $lan            = 0;
        $type           = _Program::ProfType;
        $version        = $ver['verCode'];
        $live_type      = _Program::LiveType;
        $live_version   = 80 /*crm_hardlock::GetVerCodeFromverId(_Program::PrevVersion)*/;
        $limit          = $ver['verID'] < 25 ? 2 : 1;

        if (empty($hl_user)) {
            $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum LIKE ? AND hlStat = ? AND (hlUser = '' OR hlUser IS NULL) AND (hlDealer = '' OR hlDealer IS NULL) AND product_id = ? LIMIT {$limit}";
            $this->query($sql, array('sii', '99%', _Hardlock::Blank, _Product::Professional));
            $hardlock = $this->fetchAssoc();

            if (empty($hardlock) || count($hardlock) < 1) {
                $hlNum = crm_hardlock::tryToAlloc();

                $hl = array(
                    'hlNum'         => $hlNum,
                    'hlUser'        => $this->nUserID,
                    'hlStat'        => _Hardlock::Active,
                    'hlManID'       => 36,
                    'hlCtrID'       => _Country::Hungary,
                    'hlDateIssue'   => date('Y-m-d'),
                    'product_id'    => _Product::Professional
                );
                $this->insert(CRMADATBAZIS . ".hardlock", $hl, 'siiiisi');

                if ($ver['verID'] < 25) {
                    $live_hlNum = crm_hardlock::tryToAlloc();

                    $hl_live = array(
                        'hlNum'         => $live_hlNum,
                        'hlUser'        => $this->nUserID,
                        'hlStat'        => _Hardlock::Active,
                        'hlManID'       => 36,
                        'hlCtrID'       => _Country::Hungary,
                        'hlDateIssue'   => date('Y-m-d'),
                        'product_id'    => _Product::Live
                    );
                    $this->insert(CRMADATBAZIS . ".hardlock", $hl_live, 'siiiisi');
                }
            } else {
                $hlNum      = $hardlock[0]['hlNum'];
                $live_hlNum = $ver['verID'] < 25 ? $hardlock[1]['hlNum'] : '';

                $this->update(
                    CRMADATBAZIS . '.hardlock',
                    array(
                        'hlStat'        => _Hardlock::Active,
                        'product_id'    => _Product::Professional,
                        'hlDatePro'     => '',
                        'hlUser'        => $this->nUserID,
                        'hlCtrID'       => _Country::Hungary,
                        'hlLan'         => 0
                    ),
                    array('hlNum' => $hlNum),
                    'iisiiii'
                );

                if ($ver['verID'] < 25) {
                    $this->update(
                        CRMADATBAZIS . '.hardlock',
                        array(
                            'hlStat'      => _Hardlock::Active,
                            'product_id'  => _Product::Live,
                            'hlDatePro'   => '',
                            'hlUser'      => $this->nUserID,
                            'hlCtrID'     => _Country::Hungary,
                            'hlLan'       => 0
                        ),
                        array('hlNum' => $live_hlNum),
                        'iisiiii'
                    );
                }
            }

            $generate_live  = $ver['verID'] < 25 ? true : false;
            $generate_xp    = true;
        } else {
            $hlNum      = "";
            $live_hlNum = "";

            $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlUser = ? AND product_id = ? AND hlNum LIKE ?";
            $this->query($sql, array('iis', $this->nUserID, _Product::Professional, '99%'));
            $alXP = $this->fetchNext();

            if (!empty($alXP)) {
                $hlNum = $alXP['hlNum'];

                if ($alXP['hlStat'] == _Hardlock::Inactive)
                    $this->update(CRMADATBAZIS . '.hardlock', array('hlStat' => _Hardlock::Active, 'hlDatePro' => ''), array('hlNum' => $hlNum), 'iss');
            }

            if ($ver['verID'] < 25) {
                $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlUser = ? AND product_id = ? AND hlNum LIKE ?";
                $this->query($sql, array('iis', $this->nUserID, _Product::Live, '99%'));
                $alLIVE = $this->fetchNext();

                if (!empty($alLIVE)) {
                    $live_hlNum = $alLIVE['hlNum'];

                    if ($alLIVE['hlStat'] == _Hardlock::Inactive)
                        $this->update(CRMADATBAZIS . '.hardlock', array('hlStat' => _Hardlock::Active, 'hlDatePro' => ''), array('hlNum' => $live_hlNum), 'iss');
                }
            }

            if ($hlNum == '') {
                $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum LIKE ? AND hlStat = ? AND (hlUser = ? OR hlUser IS NULL) AND (hlDealer = ? OR hlDealer IS NULL) LIMIT 1";
                $this->query($sql, array('siss', '99%', _Hardlock::Blank, '', ''));
                $hardlock = $this->fetchNext();

                if (empty($hardlock)) {
                    $hlNum = crm_hardlock::tryToAlloc();

                    $hl = array(
                        'hlNum'         => $hlNum,
                        'hlUser'        => $this->nUserID,
                        'hlStat'        => _Hardlock::Active,
                        'hlManID'       => 36,
                        'hlCtrID'       => _Country::Hungary,
                        'hlDateIssue'   => date('Y-m-d'),
                        'product_id'    => _Product::Professional
                    );
                    $this->insert(CRMADATBAZIS . ".hardlock", $hl, 'siiiisi');
                } else {
                    $hlNum = $hardlock['hlNum'];

                    $this->update(
                        CRMADATBAZIS . '.hardlock',
                        array(
                            'hlStat'        => _Hardlock::Active,
                            'hlDatePro'     => '',
                            'product_id'    => _Product::Professional,
                            'hlUser'        => $this->nUserID,
                            'hlCtrID'       => _Country::Hungary,
                            'hlLan'         => 0
                        ),
                        array('hlNum' => $hlNum),
                        'isiiiis'
                    );
                }
            }

            if ($live_hlNum == '' && $ver['verID'] < 25) {
                $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum LIKE ? AND hlStat = ? AND (hlUser = ? OR hlUser IS NULL) AND (hlDealer = ? OR hlDealer IS NULL) LIMIT 1";
                $this->query($sql, array('siss', '99%', _Hardlock::Blank, '', ''));
                $hardlock = $this->fetchNext();

                if (empty($hardlock)) {
                    $live_hlNum = crm_hardlock::tryToAlloc();

                    $hl_live = array(
                        'hlNum'         => $live_hlNum,
                        'hlUser'        => $this->nUserID,
                        'hlStat'        => _Hardlock::Active,
                        'hlManID'       => 36,
                        'hlCtrID'       => _Country::Hungary,
                        'hlDateIssue'   => date('Y-m-d'),
                        'product_id'    => _Product::Live
                    );
                    $this->insert(CRMADATBAZIS . ".hardlock", $hl_live, 'siiiisi');
                } else {
                    $live_hlNum = $hardlock['hlNum'];

                    $this->update(
                        CRMADATBAZIS . '.hardlock',
                        array(
                            'hlStat'        => _Hardlock::Active,
                            'hlDatePro'     => '',
                            'product_id'    => _Product::Live,
                            'hlUser'        => $this->nUserID,
                            'hlCtrID'       => _Country::Hungary,
                            'hlLan'         => 0
                        ),
                        array('hlNum' => $live_hlNum),
                        'isiiiis'
                    );
                }
            }

            $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prHlNum = ? AND prVerID = ?";
            $this->query($sql, array('si', $hlNum, $ver['verID']));
            $user_program_xp = $this->fetchNext();

            if (empty($user_program_xp)) {
                $generate_xp = true;
            } else {
                if ($user_program_xp['prActCode'] != '' && $user_program_xp['prActCode'] != null) {
                    $isid = crm_hardlock::GetIsidFromTimeCode($user_program_xp['prActCode']);
                    $sql  = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ?";
                    $this->query($sql, array('ss', $user_program_xp['prPass'], $isid));
                    $p_serial_isid = $this->fetchNext();
                } else {
                    $sql  = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND aktiv = ?";
                    $this->query($sql, array('si', $user_program_xp['prPass'], 1));
                    $p_serial_isid = $this->fetchNext();
                    if (!empty($p_serial_isid))
                        $isid = $p_serial_isid['isid'];
                }

                if (!empty($p_serial_isid)) {
                    if ($user_program_xp['twtw'] == 1)
                        $installDate = crm_hardlock::getTwTwRootTimeString();
                    else
                        $installDate = substr($p_serial_isid['datum'], 0, 10);

                    if ($this->courseEnd < $installDate)
                        $dayInterval = Codegen::GetDayInterval($installDate, $installDate);
                    else
                        $dayInterval = Codegen::GetDayInterval($installDate, $this->courseEnd);

                    $generatedTimeCode = Codegen::GenerateTimeCode($user_program_xp['prPass'], $isid, $dayInterval);
                    $this->update(CRMADATBAZIS . '.h_programs', array('prActCode' => $generatedTimeCode), array('prID' => $user_program_xp['prID']), 'si');
                }
                $this->update(CRMADATBAZIS . '.h_programs', array('prSellDate' => $this->courseBegin, 'prFizetve' => $this->courseEnd, 'prStat' => _Program::Active), array('prID' => $user_program_xp['prID']), 'ssii');
            }

            if ($ver['verID'] < 25) {
                $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prHlNum = ? AND prVerID = ?";
                $this->query($sql, array('si', $live_hlNum, 23));
                $user_program_live = $this->fetchNext();

                if (empty($user_program_live)) {
                    $generate_live = true;
                } else {
                    if ($user_program_live['prActCode'] != '' && $user_program_live['prActCode'] != null) {
                        $isid = crm_hardlock::GetIsidFromTimeCode($user_program_live['prActCode']);
                        $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ?";
                        $this->query($sql, array('ss', $user_program_live['prPass'], $isid));
                        $p_serial_isid = $this->fetchNext();
                    } else {
                        $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND aktiv = ?";
                        $this->query($sql, array('si', $user_program_live['prPass'], 1));
                        $p_serial_isid = $this->fetchNext();
                        if (!empty($p_serial_isid))
                            $isid = $p_serial_isid['isid'];
                    }

                    if (!empty($p_serial_isid)) {
                        if ($user_program_live['twtw'] == 1)
                            $installDate = crm_hardlock::getTwTwRootTimeString();
                        else
                            $installDate = substr($p_serial_isid['datum'], 0, 10);

                        if ($this->courseEnd < $installDate)
                            $dayInterval = Codegen::GetDayInterval($installDate, $installDate);
                        else
                            $dayInterval = Codegen::GetDayInterval($installDate, $this->courseEnd);

                        $generatedTimeCode = Codegen::GenerateTimeCode($user_program_live['prPass'], $isid, $dayInterval);
                        $this->update(CRMADATBAZIS . '.h_programs', array('prActCode' => $generatedTimeCode), array('prID' => $user_program_live['prID']), 'si');
                    }
                    $this->update(CRMADATBAZIS . '.h_programs', array('prSellDate' => $this->courseBegin, 'prFizetve' => $this->courseEnd, 'prStat' => _Program::Active), array('prID' => $user_program_live['prID']), 'ssii');
                }
            }
        }

        if ($generate_xp) {
            $password   = Codegen::GeneratePassword($hlNum, $lan, $type, $version);
            $programs   = array(
                'prTypeID'    => $type,
                'prVerID'     => $ver['verID'],
                'prLan'       => $lan,
                'prPass'      => $password,
                'prSellDate'  => $this->courseBegin,
                'prActDate'   => date("Y-m-d H:i:s"),
                'prFizetve'   => $this->courseEnd,
                'prTime'      => date("Y-m-d H:i:s"),
                'prLastMod'   => date("Y-m-d H:i:s"),
                'prContact'   => _Program::Nonprofit,
                'prHlNum'     => $hlNum,
                'prManID'     => _Manager::Web,
                'prCtrID'     => _Country::Hungary,
                'prStat'      => _Program::Active,
                'prActCode'   => '',
                'old_db'      => 'clusers',
                'old_prID'    => 0
            );
            $query = "UPDATE `" . CRMADATBAZIS . "`.`h_programs` SET prStat = ? WHERE prHlNum = ? AND prVerID != ?";
            $this->query($query, array('isi', _Program::Inactive, $hlNum, $ver['verID']));
            $this->insert(CRMADATBAZIS . ".h_programs", $programs, 'iiissssssisiiissi');
        }

        if ($generate_live && $ver['verID'] < 25) {
            $live_password  = Codegen::GeneratePassword($live_hlNum, $lan, $live_type, $live_version);
            $programs       = array(
                'prTypeID'    => $live_type,
                'prVerID'     => 23,
                'prLan'       => $lan,
                'prPass'      => $live_password,
                'prSellDate'  => $this->courseBegin,
                'prActDate'   => date("Y-m-d H:i:s"),
                'prFizetve'   => $this->courseEnd,
                'prTime'      => date("Y-m-d H:i:s"),
                'prLastMod'   => date("Y-m-d H:i:s"),
                'prContact'   => _Program::Nonprofit,
                'prHlNum'     => $live_hlNum,
                'prManID'     => _Manager::Web,
                'prCtrID'     => _Country::Hungary,
                'prStat'      => _Program::Active,
                'prActCode'   => '',
                'old_db'      => 'clusers',
                'old_prID'    => 0
            );
            $query = "UPDATE `" . CRMADATBAZIS . "`.`h_programs` SET prStat = ? WHERE prHlNum = ? AND prVerID != ?";
            $this->query($query, array('isi', _Program::Inactive, $live_hlNum, $ver['verID']));
            $this->insert(CRMADATBAZIS . ".h_programs", $programs, 'iiissssssisiiissi');
        }
    }

    public function sendEngEmail($student_id, &$error = false, &$isSend = array(), $mail)
    {
        require_once($_SERVER['DOCUMENT_ROOT'] . '/maintenance/libraries/phpmailer/PHPMailerAutoload.php');
        require_once($_SERVER['DOCUMENT_ROOT'] . '/maintenance/config.mail.php');

        $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`students` WHERE id = ?";
        $this->query($sql, array('i', $student_id));
        $student = $this->fetchNext();

        $msg = "<b>Dear " . $student['name'] . "!</b><br/><br/>

            As a student of the " . $this->schoolName . ",  you can learn how to use the ARCHLine.XP design program and ARCHLine.XP LIVE visualization software. To facilitate your successful studies, CadLine Kft. provides free use of the programs for the duration of the referred semester.<br/><br/>

            In order for the education to start smoothly, follow the steps below:

            <ol>
                <li>Based on the data provided by the School, we have created a user account for you on the <a href='https://www.archlinexp.com/'>archlinexp.com</a> website, with the e-mail address to which you received this letter.<br/>
                    Username: <i>email address</i><br/>
                    Password: <i>the part of the e-mail address before @</i><br/>
                    On the <a href='https://www.archlinexp.com/'>www.archlinexp.com</a> website log in to the <a href='https://www.archlinexp.com/user'>User page</a> with the above data. After logging in, you can change your password on your User Profile.<br/>
                    If you have already created a user account with this e-mail address, log in to your account on the <a href='https://www.archlinexp.com/user'>User page</a>.
                </li>

                <li>Download from <a href='https://www.archlinexp.com/download/free-trial'>here</a> and install ARCHLine.XP Professional 2022 and ARCHLine.XP LIVE 2022 trial versions.<br/>
                    Before downloading the programs, please make sure that your computer meets the <a href='https://help.archlinexp.com/hc/en-us/articles/360012845578-System-requirements'>system requirements</a> published on our website. Only in this case will the programs work properly.
                </li>

                <li>
                    You can also find the individual serial numbers with which you can use the programs during your active semesters in the Licenses section of your <a href='https://www.archlinexp.com/user'>User Profile</a>.
                </li>

                <li>
                    <b>Registration of non-profit version of ARCHLine.XP:</b><br>
                    Start the program.<br>

                    <ul>
                        <li>
                            From the 3 options that appear, choose option 1: License activation with Login/User account, so it will not be necessary to enter the serial number. <br>
                            Follow the instructions. <br>
                            <img src='https://www.archlinexp.com/school/public/img/image001_eng.png'>
                            <br>
                            <img src='https://www.archlinexp.com/school/public/img/image002_eng.png'>
                        </li>

                        <li>
                            If several licenses are assigned to your user account, the program will warn you of this. In this case, choose option 2 - Serial number and enter the serial number in your User account:
                        </li>

                        <li>
                            Start the program again. <br>
                            See: <a href='https://help.archlinexp.com/hc/en-us/articles/360012716877-How-do-I-activate-my-license-'>Log in with an account</a>
                        </li>
                    </ul>
                </li>

                <li><b>Using the license on another computer, deactivating the license:</b><br/>
                    With the software key, the program can be used on one computer at a time, but the user can deactivate the license on the old computer and activate it on the new computer: <br/>
                    To do this, you must log out of the user account in the ARCHLine.XP program on the old computer and log in on the new computer. If you forgot to log out of your user account on the old machine, you must restart the program at least twice after logging in on the new computer. <br>
                    The method of logging out depends on whether the previous registration was done with a user account (Figure 1) or with a serial number (Figure 2) <br>

                    <img src='https://www.archlinexp.com/school/public/img/image003_eng.png'> <br>
                    or
                    <br>
                    <img src='https://www.archlinexp.com/school/public/img/image004_eng.png'> <br>
                </li>

                <li><b>Registration of ARCHLine.XP LIVE non-profit version:</b><br/>
                    After installation, you can start the program in two ways: <br>
                    <ul>
                        <li>
                            With the command View / Rendering / ARCHLine.XP LIVE in the ribbon menu of the ARCHLine.XP 2022 design program. It will then display the current model.
                        </li>

                        <li>
                            With the ARCHLine.XP LIVE icon that appears on the desktop, you can open models with the .allive extension previously saved in ARCHLine.XP LIVE's own format.
                        </li>
                    </ul>

                    Start the program, open an .allive project (you can also choose one of our example projects for the first time.) <br>

                    <img src='https://www.archlinexp.com/school/public/img/image005_eng.png'> <br><br>

                    Select Login from the list that appears. <br>
                    Log in with your user account. <br>

                    <img src='https://www.archlinexp.com/school/public/img/image006_eng.png' style='width: 80%;'>
                </li>

                <li><b>Free Online Tutorials:</b><br/>
                    <a href='https://www.archlinexp.com/education/workshops'>https://www.archlinexp.com/education/workshops</a><br/>
                    <a href='https://www.archlinexp.com/education/tutorial-videos'>https://www.archlinexp.com/education/tutorial-videos</a><br/>
                    <a href='https://www.archlinexp.com/products/archline-xp-live/learn-live'>https://www.archlinexp.com/products/archline-xp-live/learn-live</a>
                </li>
            </ol>

            If you have any questions, contact your instructor!
            <br/>
            <br/>

            CadLine Kft. <br/>
            Web: <a href='http://www.archlinexp.com/'>http://www.archlinexp.com/</a>";

        $subject    = 'ARCHLine.XP Course Installation Guide';

        $mail->FromName = 'www.archline.hu';
        $mail->From     = 'marketing@cadline.hu';
        $mail->ClearAllRecipients();
        $mail->AddAddress($student['email'], $student['name']);
        $mail->AddCC('marketing@cadline.hu');

        $mail->IsHTML(true);

        $mail->Subject = $subject;
        $mail->Body    = $msg;

        if (!$mail->Send()) {
            $error    = true;
            $isSend[] = array("email" => $student["email"]);
        } else {
            $begin    = str_replace('-', '.', $this->courseBegin);
            $end      = str_replace('-', '.', $this->endDate);
            $history  = array(
                "nUserID"     => $student['nUserID'],
                "nTopicID"    => "102",
                "strPlace"    => "web",
                "strInfo"     => "{$this->username} {$this->courseCode} {$begin} - {$end} Tanfolyami szerződés elküldve",
                "nManagerID"  => _Manager::Web,
                "dateEvent"   => date('Y-m-d')
            );
            $this->insert(CRMADATBAZIS . ".u_history", $history, 'iissis');
            $this->update(CRMADATBAZIS . ".students", array('email_sent' => 1), array('id' => $student_id), 'ii');
        }
    }

    public function sendDistributorEmail($student_id, &$error = false, &$isSend = array(), $email, $mail)
    {
        require_once($_SERVER['DOCUMENT_ROOT'] . '/maintenance/libraries/phpmailer/PHPMailerAutoload.php');
        require_once($_SERVER['DOCUMENT_ROOT'] . '/maintenance/config.mail.php');

        $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`students` WHERE id = ?";
        $this->query($sql, array('i', $student_id));
        $student = $this->fetchNext();

        $sql = "SELECT verID FROM `" . CRMADATBAZIS . "`.`p_versions` WHERE year = ?";
        $this->query($sql, array('i', $this->version));
        $version = $this->fetchNext();

        $sql = "SELECT h.hlNum, p.prPass AS prPass, h.product_id AS product, h.hlUser
            FROM `" . CRMADATBAZIS . "`.`hardlock` h
            LEFT JOIN `" . CRMADATBAZIS . "`.`h_programs` p ON p.prHlNum = h.hlNum
            WHERE h.hlUser = ? AND p.prPass LIKE ? AND p.prVerID = ? AND h.product_id = 1 LIMIT 1;";
        $this->query($sql, array('isi', $student['nUserID'], '99%', $version['verID']));
        $hardlock = $this->fetchNext();

        $msg = 'Thank You for Your Order. <br><br>
        
            We have accepted your online purchase order for the following items. <br><br>
            
            The following order details were accepted on ' . date('d-m-Y') . '<br><br>
            
            Licensee E-mail: <b>' . $student["email"] . '</b><br><br>
            
            Licensee name: <b>' . $student['name'] . '</b><br><br>
            
            Information: <br><br>
            
            Ordered product(s): <br><br>
            
            Item: <b>ARCHLine.XP Professional ' . $this->version . ' - Non-Profit</b> <br>
            Serial code ' . $this->version . ': <b>' . $hardlock['prPass'] . '</b> <br><br>
            
            Thank you for your order, <br><br>

            CadLine Ltd. <br><br>

            This is an automatically generated email – please do not reply to it.<br>
            If you have any queries regarding your order please email <a href="mailto: office@cadline.hu">office@cadline.hu</a>.';

        $subject    = 'ARCHLine.XP order: Non-Profit ' . $this->version . ' - ' . $student['name'];

        $mail->FromName = 'www.archline.hu';
        $mail->From     = 'marketing@cadline.hu';
        $mail->ClearAllRecipients();
        $mail->AddAddress($email);
        $mail->AddCC('marketing@cadline.hu');

        $mail->IsHTML(true);

        $mail->Subject = $subject;
        $mail->Body    = $msg;

        $mail->Send();

        $this->update(CRMADATBAZIS . ".students", array('email_sent' => 1), array('id' => $student_id), 'ii');
    }

    public function sendEmail($student_id, &$error = false, &$isSend = array(), $mail)
    {
        require_once($_SERVER['DOCUMENT_ROOT'] . '/maintenance/libraries/phpmailer/PHPMailerAutoload.php');
        require_once($_SERVER['DOCUMENT_ROOT'] . '/maintenance/config.mail.php');

        $sql = "SELECT * FROM `" . CRMADATBAZIS . "`.`students` WHERE id = ?";
        $this->query($sql, array('i', $student_id));
        $student = $this->fetchNext();

        $msg = '<b>Kedves ' . $student['name'] . ' Hallgató!</b><br/><br/>

            A(z) ' . $this->schoolName . ' Iskola hallgatójaként elsajátíthatod az <b>ARCHLine.XP tervezőprogram</b> használatát. Sikeres tanulmányaid elősegítéséhez a CadLine Kft. a képzés teljes időtartamára (az aktív féléveid alatt) ingyenesen biztosítja a program használatát.<br/><br/>

            Ahhoz, hogy az oktatás zökkenőmentesen elkezdődhessen, kövesd az alábbi lépéseket: 

            <ol>
                <li>Az Iskola által átadott adatok alapján létrehoztunk egy <b>felhasználói fiókot</b> a számodra az <a href="https://www.archline.hu/">archline.hu</a> oldalon, azzal az e-mail címmel, amire jelen levelet is kaptad.<br/>
                    Felhasználónév: <i>e-mail cím</i><br/>
                    Jelszó: az <i>e-mail cím @ előtti része</i><br/>
                    A fenti adatokkal jelentkezz be a <a href="https://www.archline.hu/felhasznalo">Felhasználó</a> oldalon. Bejelentkezés után a Felhasználói adatlapodon a jelszó módosítható.<br/>
                    Ha már létrehoztál ezzel az e-mail címmel felhasználói fiókot, akkor jelentkezz be a fiókodba a <a href="https://www.archline.hu/felhasznalo">Felhasználó</a> oldalon.
                </li>
                <br/>

                <li>A <a href="https://www.archline.hu/felhasznalo">Felhasználói Adatlapodon</a> a <b>Számlázás és szerződés</b> részben  kattints a <b>Tanfolyami szerződés hallgatóknak - Letöltés</b> gombra. Figyelmesen olvasd el a felugró ablakban megjelenő <b>tanfolyam szerződést</b>, és amennyiben elfogadod annak feltételeit, pipáld be az ablak alján található <b>"Elfogadom"</b> mezőt.</a></li>

                <li>Töltsd le és telepítsd az ARCHLine.XP próbaváltozatot.<br/>
                    A program letöltése előtt kérjük, győződj meg róla, hogy a honlapunkon közzétett <a href="https://www.archline.hu/oktatas/rendszerkovetelmenyek">rendszerkövetelményeknek</a> a számítógéped megfelel-e. Csak ebben az esetben fog a program megfelelően működni. 
                </li>
                <br/>

                <li>
                    Az egyéni <b>sorozatszámokat</b>, melyekkel a programokat az aktív féléveid során használhatod, szintén a <a href="https://www.archline.hu/felhasznalo">Felhasználói adatlapodon</a> a <b>Licencek</b> részben találod.
                </li>
                <br/>

                <li>
                    <b>ARCHLine.XP non-profit változat regisztrálása:</b><br>
                    Indítsd el a programot.<br>

                    <ul>
                        <li>
                            A megjelenő 3 lehetőség közül válaszd az 1. opciót: Licenc aktiválása Bejelentkezéssel/Felhasználói fiókkal, így nem lesz szükség a sorozatszám beírására. <br>
                            Kövesd az utasítást. <br>
                            <img src="https://www.archline.hu/iskolak/public/img/image001_hun.jpg">
                            <img src="https://www.archline.hu/iskolak/public/img/image002_hun.jpg">
                        </li>

                        <li>
                            Ha több licenc van a felhasználói fiókodhoz rendelve, akkor erre a program figyelmeztet. Ebben az esetben válaszd a 2. opciót – Sorozatszám, és írd be a Felhasználói fiókodban található sorozatszámot.
                        </li>

                        <li>
                            Indítsd újból a programot. <br>
                            Lásd: <a href="https://help.archlinexp.com/hc/hu-hu/articles/4432398810897-Bejelentkez%C3%A9s-fi%C3%B3kkal-pr%C3%B3bav%C3%A1ltozat">Bejelentkezés fiókkal</a>
                        </li>
                    </ul>
                </li>
                <br/>

                <li><b>A licenc használata másik gépen, licenc deaktiválása:</b><br/>
                    A szoftverkulccsal egyszerre egy számítógépen használható a program, de a felhasználó által deaktiválható a licenc a régi gépen és aktiválható az új gépen: <br/>
                    <b>Ehhez az ARCHLine.XP programban a régi gépen ki kell jelentkezni a felhasználói fiókból és az új gépen be kell jelentkezni.</b> Ha elfelejtettél kijelentkezni a felhasználói fiókodból a régi gépen, akkor az új gépen a bejelentkezés után a program legalább kétszeri újraindítása szükséges. <br>
                    A kijelentkezés módja azon múlik, hogy az előző regisztrálás felhasználó fiókkal történt (1. ábra) vagy sorozatszámmal (2. ábra)

                    <img src="https://www.archline.hu/iskolak/public/img/image003_hun.jpg"> <br>
                    vagy
                    <br>
                    <img src="https://www.archline.hu/iskolak/public/img/image004_hun.jpg"> <br>
                    <br>

                    Ha az előzőek alapján nem sikerül kijelentkeztetni a licencet a régi gépen, akkor csak a <a href="https://www.archline.hu/felhasznalo">https://www.archline.hu/felhasznalo</a> oldalon bejelentkezve lehet eltávolítani a régi gépről. <br>
                    <img src="https://www.archline.hu/iskolak/public/img/image010.png"> <br>
                    <br>

                    Ezután az új gépen be kell jelentkezni. <br>
                </li>
                <br/>

                <li><b>Ingyenes Online oktatóanyagok:</b><br/>
                    <a href="https://www.archline.hu/oktatas/workshopok">https://www.archline.hu/oktatas/workshopok</a><br/>
                    <a href="https://www.archline.hu/attekintes/ujdonsagok-2025">https://www.archline.hu/attekintes/ujdonsagok-2025</a><br/>
                </li>
                <br/>

                <li>
                    <b>Tippek</b>:<br/>
                    <a href="https://help.archlinexp.com/hc/hu-hu/articles/10178448221969-Tippek-az-informatikai-probl%C3%A9m%C3%A1k-elker%C3%BCl%C3%A9s%C3%A9re">Tippek az informatikai problémák elkerülésére</a>
                </li>
                <br/>

                <li><b>Facebook csoport</b><br/>
                    Szeretnénk figyelmedbe ajánlani, hogy a tanfolyami szerződés elfogadása után jelentkezhetsz az <a href="https://www.facebook.com/groups/1394408780876368/">ARCHLine felhasználók</a> zárt Facebook csoportba.
                    <br/>

                    A csoport célja, hogy a programot használó kollégák megosszák egymás között tapasztalataikat, segítsék egymás munkáját.
                    <br/>

                    Várjuk jelentkezésed a csoportba!
                </li>
                <br/>
            </ol><br/>
            Ha kérdésed merül fel, vedd fel a kapcsolatot az oktatóddal!
            <br/>
            <br/>

            Üdvözlettel:
            <br/>
            <br/>

            CadLine Kft. <br/>
            1037 Budapest, Montevideo u. 3/B. <br/>
            E-mail: <a href="mailto:marketing@cadline.hu">marketing@cadline.hu</a> | Web: <a href="https://www.archline.hu/">www.archline.hu</a>';

        $subject = 'ARCHLine.XP Tanfolyami szerződés és telepítesi útmutató - ' . $student['name'];

        if ($student['email_sent'] == 0) {
            $mail->FromName = 'www.archline.hu';
            $mail->From     = 'marketing@cadline.hu';
            $mail->ClearAllRecipients();
            $mail->AddAddress($student['email'], $student['name']);
            $mail->AddCC('marketing@cadline.hu');

            $mail->IsHTML(true);

            $mail->Subject = $subject;
            $mail->Body    = $msg;

            if (!$mail->Send()) {
                $error    = true;
                $isSend[] = array("email" => $student["email"]);
            } else {
                $begin    = str_replace('-', '.', $this->courseBegin);
                $end      = str_replace('-', '.', $this->endDate);
                $history  = array(
                    "nUserID"     => $student['nUserID'],
                    "nTopicID"    => "102",
                    "strPlace"    => "web",
                    "strInfo"     => "{$this->username} {$this->courseCode} {$begin} - {$end} Tanfolyami szerződés elküldve",
                    "nManagerID"  => _Manager::Web,
                    "dateEvent"   => date('Y-m-d')
                );
                $this->insert(CRMADATBAZIS . ".u_history", $history, 'iissis');
                $this->update(CRMADATBAZIS . ".students", array('email_sent' => 1), array('id' => $student_id), 'ii');
            }
        }
    }
}

class AML
{
    private static $Stream;

    public static function add_atribute($strTagName, $strData)
    {
        if ($strData != '') {
            self::$Stream .= self::OpenBracket();
            self::$Stream .= $strTagName;
            self::$Stream .= self::Separator();
            self::$Stream .= strlen($strData);
            self::$Stream .= self::CloseBracket();
            self::$Stream .= $strData;
        }
    }

    public static function GetStream()
    {
        return self::$Stream;
    }

    private static function OpenBracket()
    {
        return "{";
    }
    private static function Separator()
    {
        return ":";
    }
    private static function CloseBracket()
    {
        return "}";
    }

    public static function CreateAML($password, $installationCode, $npid, $computerName, $isFloating = '', $isOffline = '', $seatID = '')
    {
        self::$Stream = "";
        self::add_atribute("PWD", $password);
        self::add_atribute("IID", $installationCode);
        self::add_atribute("NP", $npid);
        self::add_atribute("Fl", $isFloating);
        self::add_atribute("Ol", $isOffline);
        self::add_atribute("SI", $seatID);

        if ($computerName != '') self::add_atribute("CN", $computerName);

        return self::$Stream;
    }

    public static function CreateEncodedAML($data)
    {
        self::$Stream = "";
        self::add_atribute("ED", $data);

        return self::$Stream;
    }

    public static function getAttribute($attribute, $aml)
    {
        $arr = array();
        $arr = explode("{{$attribute}:", $aml);
        $arr = explode('}', $arr[1]);

        return substr($arr[1], 0, $arr[0]);
    }

    public static function emptyAML()
    {
        self::$Stream = "";
    }
}

class program
{
    protected $db;

    protected $password;
    protected $isid;

    protected $ctrID;               // A password elso 2 karaktere
    protected $hlNum;               // A password elso 6 karaktere
    protected $verPassword8and9;    // A password 7-8 karaktere
    protected $verID;               // A verzio ID-je (pl 2019-ben az ertek 19)
    protected $prID;                // A program egyedi ID-je az adatbazisban
    protected $prActCode;           // A program aktivalo kodja
    protected $prSellDate;          // A program eladasi datuma
    protected $prFizetve;           // A program lejarati ideje
    protected $hlStat;              // A kulcs allapota (ures, aktiv, letiltott)

    protected $installDate;         // A program telepitesi datuma
    protected $installCode;         // Telepitesi kod
    protected $daysSinceInstall;    // Telepites ota eltelt napok szama

    protected $language;        // A kulcs nyelve (DB-ben hlCtrID)
    protected $lan;             // Lan szam (engedelyezett programok szama)
    protected $isSoftwareKey;   // Szoftveres-e
    protected $isValidPassword;

    protected $hlUser;
    protected $productID;

    protected $aktiv; // p_serial_isid tabla aktiv mezoje

    public function __construct()
    {
        $this->db = Database::getInstance();
    }

    public function SetPassword($password)
    {
        unset($this->isid);
        unset($this->prID);
        unset($this->prActCode);
        unset($this->language);
        if ($this->password != $password) {
            unset($this->ctrID);
            unset($this->hlNum);
            unset($this->verPassword8and9);
            unset($this->verID);
            unset($this->prSellDate);
            unset($this->prFizetve);
            unset($this->hlStat);
            unset($this->lan);
            unset($this->IsSoftwareKey);
            unset($this->isValidPassword);
        }
        if (!isset($this->isSoftwareKey) || $this->isSoftwareKey) {
            unset($this->installDate);
            unset($this->installCode);
            unset($this->daysSinceInstall);
        }
        $this->password = $password;
    }

    public function SetIsid($isid)
    {
        if ($this->isid != $isid) {
            unset($this->prID);
            unset($this->prActCode);
            unset($this->language);
            unset($this->installDate);
            unset($this->installCode);
            unset($this->daysSinceInstall);
        }
        $this->isid = $isid;
    }

    public function SetFromHardlockTable($hardlock)
    {
        if (!empty($hardlock)) {
            if (!isset($this->hlNum))       $this->hlNum        = $hardlock['hlNum'];
            if (!isset($this->language))    $this->language     = $hardlock['hlCtrID'];
            if (!isset($this->lan))         $this->lan          = $hardlock['hlLan'];
            if (!isset($this->hlStat))      $this->hlStat       = $hardlock['hlStat'];
            if (!isset($this->hlUser))      $this->hlUser       = $hardlock['hlUser'];
            if (!isset($this->productID))   $this->productID    = $hardlock['product_id'];
        }
    }

    public function SetFromHprogramsTable($program)
    {
        if (!empty($program)) {
            if (!isset($this->prID))        $this->prID         = $program['prID'];
            if (!isset($this->prSellDate))  $this->prSellDate   = $program['prSellDate'];
            if (!isset($this->prActCode))   $this->prActCode    = $program['prActCode'];
            if (!isset($this->prFizetve))   $this->prFizetve    = $program['prFizetve'];
            if (!isset($this->lan))         $this->lan          = $program['hlLan'];
            if (!isset($this->hlNum))       $this->hlNum        = $program['prHlNum'];
        }
    }

    public function SetFrompSerialIsidTable($serialisid)
    {
        if (!empty($serialisid)) {
            if (!isset($this->installDate)) $this->installDate  = $serialisid['datum'];
            if (!isset($this->isid))        $this->isid         = $serialisid['isid'];
            if (!isset($this->aktiv))       $this->aktiv        = $serialisid['aktiv'];
        }
    }

    public function SetInstallCode($installCode)
    {
        if (Codegen::IsValidTimeCode($installCode)) {
            $this->installCode      = $installCode;
            $this->daysSinceInstall = Codegen::GetDayFromTimeCode($installCode);
        }

        unset($this->installCode);
    }

    public function GetCtrID()
    {
        if (isset($this->ctrID)) return $this->ctrID;
        $this->ctrID = substr($this->password, 0, 2);

        return $this->ctrID;
    }

    public function GetHlNum()
    {
        if (isset($this->hlNum)) return $this->hlNum;
        $this->hlNum = crm_hardlock::GetHlnumFromPassword($this->password);

        return $this->hlNum;
    }

    public function GetVerPassword8and9()
    {
        if (isset($this->verPassword8and9)) return $this->verPassword8and9;
        $this->verPassword8and9 = substr($this->password, 7, 2);

        return $this->verPassword8and9;
    }

    public function GetInstallCode()
    {
        if (isset($this->installCode)) return $this->installCode;

        return $this->installCode;
    }

    public function IsValidPassword()
    {
        if (isset($this->isValidPassword)) return $this->isValidPassword;
        $this->isValidPassword = Codegen::IsValidPassword($this->password);

        return $this->isValidPassword;
    }

    public function IsSoftwareKey()
    {
        if (isset($this->isSoftwareKey)) return $this->isSoftwareKey;
        $this->isSoftwareKey = Codegen::IsSoftwareKey($this->password);

        return $this->isSoftwareKey;
    }

    public function GetVerID()
    {
        if (isset($this->verID)) return $this->verID;

        $verPassword8and9 = $this->GetVerPassword8and9();
        $query = "SELECT verID, verPassword8and9 FROM `" . CRMADATBAZIS . "`.`p_versions` WHERE verPassword8and9 = ?";
        $this->db->query($query, array('i', $verPassword8and9));
        $version = $this->db->fetchNext();
        if (!empty($version)) $this->verID = $version['verID'];

        return $this->verID;
    }

    public function GetIsid()
    {
        if (isset($this->isid)) return $this->isid;
        if (!$this->IsSoftwareKey()) $this->isid = $this->GetCtrID() . $this->GetVerPassword8and9();

        return $this->isid;
    }

    public function GetPrID()
    {
        if (isset($this->prID)) return $this->prID;

        $isid = $this->GetIsid();
        $query = "SELECT prID FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ? AND prActCode LIKE ?";
        $this->db->query($query, array('ss', $this->password, "%-{$isid}-%"));
        $programs = $this->db->fetchNext();
        if (!empty($programs)) $this->prID = $programs['prID'];

        return $this->prID;
    }

    private function executeQuery_programs()
    {
        if (isset($this->prID)) {
            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prID = ?";
            $this->db->query($query, array('i', $this->prID));
        } else {
            $isid = $this->GetIsid();
            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ? AND prActCode LIKE ?";
            $this->db->query($query, array('ss', $this->password, "%-{$isid}-%"));
        }
        $programs = $this->db->fetchNext();

        return $programs;
    }

    private function processQueryresult_programs($programs)
    {
        if (!empty($programs)) $this->SetFromHprogramsTable($programs);
    }

    public function GetPrActCode()
    {
        if (isset($this->prActCode)) return $this->prActCode;

        $programs = $this->executeQuery_programs();
        $this->processQueryresult_programs($programs);

        return $this->prActCode;
    }

    public function GetPrSellDate()
    {
        if (isset($this->prSellDate)) return $this->prSellDate;

        $programs = $this->executeQuery_programs();
        $this->processQueryresult_programs($programs);

        return $this->prSellDate;
    }

    public function GetPrFizetve()
    {
        if (isset($this->prFizetve)) return $this->prFizetve;

        $programs = $this->executeQuery_programs();
        $this->processQueryresult_programs($programs);

        return $this->prFizetve;
    }

    private function executeQuery_hardlock()
    {
        $hlNum = $this->GetHlNum();
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum = ?";
        $this->db->query($query, array('s', $hlNum));
        $keys = $this->db->fetchNext();

        return $keys;
    }

    private function executeQuery_p_serial_isid()
    {
        $isid = $this->GetIsid();
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ?";
        $this->db->query($query, array('ss', $this->password, $isid));
        $p_serial_isids = $this->db->fetchNext();

        return $p_serial_isids;
    }

    private function processQueryresult_hardlock($keys)
    {
        if (!empty($keys)) $this->SetFromHardlockTable($keys);
    }
    private function processQueryresult_p_serial_isid($serialisids)
    {
        if (!empty($serialisids)) $this->SetFrompSerialIsidTable($serialisids);
    }

    public function GetHlStat()
    {
        if (isset($this->hlStat)) return $this->hlStat;

        $keys = $this->executeQuery_hardlock();
        $this->processQueryresult_hardlock($keys);

        return $this->hlStat;
    }

    public function GetLan()
    {
        if (isset($this->lan)) return $this->lan;

        $keys = $this->executeQuery_hardlock();
        $this->processQueryresult_hardlock($keys);

        return $this->lan;
    }

    public function GetLanguage()
    {
        if (isset($this->language)) return $this->language;

        $keys = $this->executeQuery_hardlock();
        $this->processQueryresult_hardlock($keys);

        return $this->language;
    }

    public function GetInstallDate()
    {
        if (isset($this->installDate)) return $this->installDate;

        if ($this->IsSoftwareKey()) {
            $p_serial_isids = $this->executeQuery_p_serial_isid();
            $this->processQueryresult_p_serial_isid($p_serial_isids);
        } else $this->installDate = $this->GetPrSellDate();

        return $this->installDate;
    }

    public function GetDaysSinceInstall()
    {
        if (isset($this->daysSinceInstall)) return $this->daysSinceInstall;

        $installDate            = $this->GetInstallDate();
        $now                    = date("Y-m-d");
        $this->daysSinceInstall = Codegen::GetDayInterval($installDate, $now);

        return $this->daysSinceInstall;
    }

    public function CheckPassword($engedelyek = array())
    {
        $result = '';
        $hlNum  = $this->GetHlNum();
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`hardlock` WHERE hlNum = ?";
        $this->db->query($query, array('s', $hlNum));
        $keys = $this->db->fetchAssoc();

        if (empty($keys)) return "error: nincs ilyen kulcs a hardlock tablaban";
        elseif (count($keys) > 1) return "error: tobbszor van felveve a kulcs a hardlock tablaban";

        $hlLan_hardlock = $this->GetLan();
        if ($hlNum != $keys[0]['hlNum']) return "error: nem jo a hlNum erteke a hardlock tablaban";

        // A program megletenek ellenorzese
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ?";
        $this->db->query($query, array('s', $this->password));
        $programs = $this->db->fetchAssoc();
        if (empty($programs)) return "error: nincs ilyen password a h_programs tablaban";

        if (!$this->IsValidPassword() && $programs[0]['prVerID'] > 3) return "error: ervenytelen password";

        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prHlNum = ?";
        $this->db->query($query, array('s', $hlNum));
        $check_programs = $this->db->fetchNext();

        if (empty($check_programs) && $keys[0]['hlStat'] == 1) return "error: nem tartozik program ehhez az aktiv kulcshoz";

        $checkLan = $keys[0]['hlLan'] + 1;

        if ($keys[0]['isFloating'] == 0 && $programs[0]['prVerID'] < 24) {
            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prHlNum = ? AND prVerID >= 20 GROUP BY prVerID HAVING COUNT(prVerID) > " . $checkLan;
            $this->db->query($query, array('s', $hlNum));
            $checkVersions = $this->db->fetchAssoc();

            if (!empty($checkVersions)) {
                $versionError = '';

                foreach ($checkVersions as $checkVersion) {
                    if ($checkVersion['prPass'] == $this->password)
                        $versionError .= 'error: a h_programs tablaban tobb ugyan olyan verzioju program van felveve <br>';
                }

                return $versionError;
            }
        }

        $softwareKey = $this->IsSoftwareKey(); {   // A licenszek szamanak ellenorzese
            $numberOfLicenses = $hlLan_hardlock;
            if ($numberOfLicenses < 0) return "error: nincs ilyen kulcs a hardlock tablaban";
            elseif ($numberOfLicenses == 0) $numberOfLicenses = 1; // A nem lanos kulcsoknal 0 az ertek, ami 1 licenszt jelent

            if ($keys[0]['isFloating'] == 0 && $programs[0]['prVerID'] < 24) {
                if ($softwareKey) {
                    if ($numberOfLicenses != count($programs))
                        return "error: a h_programs tablaban levo programok szama nem egyezik meg a hardlock tablaban levo hlLan ertekkel";
                } else {
                    if (count($programs) != 1) {
                        $hlnums = array();
                        foreach ($programs as $program) {
                            $local_hlNum = $program["prHlNum"];
                            if (in_array($local_hlNum, $hlnums)) return "error: tobbszor van felveve a program a h_programs tablaba (HlNum: {$local_hlNum})";
                            array_push($hlnums, $local_hlNum);
                        }
                    }
                }
            }
        } {   // A kiadott idokodok konzisztenciainak az ellenorzese
            $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND aktiv = ? AND (isid != '' OR isid IS NOT NULL)";
            $this->db->query($query, array('si', $this->password, 1));
            $p_serial_localIsids    = $this->db->fetchAssoc();
            $p_serial_isids         = array();

            if ($softwareKey) {
                foreach ($p_serial_localIsids as $p_serial_localIsid) {
                    $local_isid = $p_serial_localIsid['isid'];
                    if (array_key_exists($local_isid, $p_serial_isids)) return "error: isid utkozest talaltunk a p_serial_isid tablaban: {$local_isid}";

                    $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`h_programs` WHERE prPass = ? AND prActCode LIKE ?";
                    $this->db->query($query, array('ss', $this->password, "%-{$local_isid}-%"));
                    $checkReg = $this->db->fetchAssoc();
                    if (empty($checkReg) && $checkReg['prFizetve'] > date('Y-m-d')) return "error: ehhez az isid-hez nem tartozik aktivalokod: {$local_isid}";
                    array_push($p_serial_isids, $p_serial_localIsid['isid']);
                }
            }

            $isids      = array();
            $fizetve    = $programs[0]['prFizetve'];
            foreach ($programs as $prgrm) {
                if (!crm_hardlock::checkProgramType($prgrm['prTypeID'], $this->password) && $prgrm['prVerID'] >= 20)
                    return "error: a program tipusa nem egyezik meg a passwordben levo tipus szammal";

                if (!crm_hardlock::checkProgramVersion($prgrm['prVerID'], $this->password) && $prgrm['prVerID'] >= 10)
                    return "error: a program verzioja nem egyezik meg a passwordben levo verzio szammal";

                if (empty($prgrm['prActCode']) || $prgrm['prActCode'] == '' || $prgrm['prActCode'] == NULL) continue;

                if (Codegen::IsSoftwareKey($this->password)) {
                    $prg_isid = crm_hardlock::GetIsidFromTimeCode($prgrm['prActCode']);
                    $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`p_serial_isid` WHERE serial = ? AND isid = ?";
                    $this->db->query($query, array('ss', $this->password, $prg_isid));
                    $prg_reg = $this->db->fetchNext();

                    if (empty($prg_reg)) return "error: nincs ilyen isiddel a password regisztralva: {$prg_isid}";

                    if ($prg_reg['aktiv'] != 1) return "error: a regisztracio nem aktiv: {$prg_isid}";
                }

                $lan_sign = crm_hardlock::GetLanSignFromLanUserNumber($prgrm['prLan']);
                if (substr($prgrm['prPass'], 9, 1) != $lan_sign && $prgrm['prVerID'] > 20)
                    return "error: a passwordben levo lan szam nem egyezik meg a tenyleges lan szammal";

                if ($prgrm['prLan'] != $hlLan_hardlock) return "error: prLan erteke nem egyezik meg a hardlock es a h_programs tablaban";

                if (substr($prgrm['prHlNum'], 0, 6) != $hlNum) // Regen duplikalodtak a kulcsok es ezt 1 plusz karakterrel kulonboztettek meg oket pl(360204a)
                    return "error: prHlNum erteke nem jo a h_programs tablaban";

                $localActCode = $prgrm['prActCode'];
                if (!Codegen::IsValidTimeCode($localActCode) && $prgrm['prVerID'] > 6) return "error: nem megfelelo formatumu idokod: {$localActCode}";
                $localIsid = crm_hardlock::GetIsidFromTimeCode($localActCode);

                if (!crm_hardlock::IsValidIsid($localIsid) && $prgrm['prVerID'] > 6) return "error: nem megfelelo formatumu isid: {$localActCode}";
                if ($softwareKey) {
                    if (array_key_exists($localIsid, $isids)) return "error: isid utkozest talaltunk a h_programs tablaban: {$localIsid}";
                } else {
                    $hardwareisid = crm_hardlock::GetIsidFromPasswordForHardwareKeys($prgrm['prPass']);

                    if ($hardwareisid != $localIsid && $prgrm['prVerID'] > 6)
                        return "error: a hardware kulcsos program aktivalasi kodja nem jo isidet tartalmaz: {$localIsid}";
                }
                array_push($isids, $localIsid);

                if ($softwareKey && array_search($localIsid, $p_serial_isids) === false && strpos($prgrm['prInfo'], 'AutoReg') === false && $prgrm['prVerID'] > 6)
                    return "error: nincs ilyen isid a p_serial isid tablaban: {$localIsid}";

                if ($prgrm['prFizetve'] != $fizetve)
                    return "error: nem egyezik meg az aktivalas ervenyessege az osszes programnal a h_programs tablaban";
            }
        }

        //- volt-e regisztralva
        $query = "SELECT * FROM `" . CRMADATBAZIS . "`.`userstats2013` WHERE usPwd = ? ORDER BY usDate DESC";
        $this->db->query($query, array('s', $this->password));
        $usstats = $this->db->fetchAssoc();
        if (empty($usstats)) return "";  //- nem haznalta, nem hiba, illetve a regi programok nincsenek benne a userstats2013 tablaban

        {
            //- telepitesi datum tarolasanak elokeszitese
            $telepitesi_datumok = array();
            $isidek             = array();
            foreach ($usstats as $stats) {
                $usISID = $stats['usISID'];
                if ($usISID == null || $usISID == '') continue;
                if ($softwareKey && ($stats['usTelepitesiDatum'] == null || $stats['usTelepitesiDatum'] == '' || $stats['usTelepitesiDatum'] == '0000-00-00')) {
                    if ($stats['usDate'] > 1561939200) return "error: nincsen telepitesi datum a userstats2013 tablaban: {$usISID}"; //2019-07-01 00:00:00
                    continue;
                }
                array_push($isidek, $usISID);
                array_push($telepitesi_datumok, $stats['usTelepitesiDatum']);
            }

            if ($fizetve != '' && $fizetve != null) {
                foreach ($programs as $prgrm) {
                    if (empty($prgrm['prActCode'])) continue;

                    $localActCode = $prgrm['prActCode'];
                    $localIsid = crm_hardlock::GetIsidFromTimeCode($localActCode);

                    $index = array_search($localIsid, $isidek);
                    if ($index === false && $softwareKey) {
                        if ($stats['usDate'] > 1561939200) return "error: nincs ilyen isid a userstats2013 tablaban: {$localIsid}"; //2019-07-01 00:00:00
                        continue;
                    }

                    if ($prgrm['twtw'] == 1) $installDate = crm_hardlock::getTwTwRootTimeString();
                    else $installDate = $telepitesi_datumok[$index];

                    $checkActDate = crm_hardlock::checkActDate($this->password, $localIsid);
                    if (strpos($checkActDate, 'error:') !== false) return $checkActDate;

                    if ($fizetve != "2050-01-01") { {   //- ervenyessegi datum ellenorzese
                            $engedelyezett_napok_szama  = Codegen::GetDayFromTimeCode($localActCode);
                            $dateExpire                 = new DateTime($fizetve);
                            $dateRealExpire             = new DateTime($installDate);
                            $dateRealExpire->modify("+{$engedelyezett_napok_szama} day");
                            $dateRealExpireString       = $dateRealExpire->format('Y-m-d');
                            $dateRealExpireMin          = new DateTime($dateRealExpireString);
                            $dateRealExpireMax          = new DateTime($dateRealExpireString);
                            $dateRealExpireMin->modify('-2 day');
                            $dateRealExpireMax->modify('+2 day');

                            if ($dateExpire > $dateRealExpireMax || $dateExpire < $dateRealExpireMin) {
                                $realstring     = "A valos lejarati datum: {$dateRealExpireString}";
                                $realstring2    = "Az aktivalas ervenyessege CRM szerint: {$dateExpire->format('Y-m-d')}";
                                $error          = "error: nem jol kiszamolt aktivalasi kod (ISID: {$localIsid}) {$realstring} {$realstring2} Aktivalo kod: {$localActCode}";

                                if (!in_array('aktivalasi kod', $engedelyek)) return $error;

                                if (!empty($result)) $result .= "<br/>";
                                $result .= $error;

                                $dayInterval        = Codegen::GetDayInterval(substr($installDate, 0, 10), $dateExpire->format('Y-m-d'));
                                $generatedTimeCode  = Codegen::GenerateTimeCode($this->password, $localIsid, $dayInterval);

                                if ($generatedTimeCode == $localActCode) return $error;

                                $this->db->update(
                                    CRMADATBAZIS . ".h_programs",
                                    array(
                                        'prActCode'     => $generatedTimeCode
                                    ),
                                    array('prID'    => $prgrm['prID']),
                                    'si'
                                );
                                $result .= " Javitva: {$generatedTimeCode}";
                            }
                        }
                    } else {
                        $numOfDays          = crm_hardlock::GetDaysForUnlimitedTimeCode($this->password);
                        $generatedTimeCode  = Codegen::GenerateTimeCode($this->password, $localIsid, $numOfDays);

                        if ($generatedTimeCode != $localActCode) return "error: nem vegleges kodot kapott: {$localIsid}";
                    }
                }
            }
        }
        return $result; // Sikeres teszt
    }
}
