<?php
class Mresourcemanager extends Model
{
    private $salt = "\c1afgWsv\$FdxMDd=ImjOl%vxp\$ecFw%";

    private $tables = array(
        'ref'   => 'resourcemanager.text_table_ref',
        '36'    => 'resourcemanager.text_table_hun',
        '39'    => 'resourcemanager.text_table_ita',
        '49'    => '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',
        '12'    => 'resourcemanager.text_table_chn',
        '47'    => 'resourcemanager.text_table_nor',
        '33'    => 'resourcemanager.text_table_fr',
        '51'    => 'resourcemanager.text_table_pt'
    );

    public $admin_pages = array(
        '/resourcemanager/newReference' => 'Add New Resource',
        '/resourcemanager/import'       => 'Resource Import',
        '/resourcemanager/export'       => 'Resource Export',
        '/resourcemanager/adminHelp'    => 'Admin Help'
    );

    public $gt_aliases = array(
        '36' => 'hu',
        '39' => 'it',
        '49' => 'de',
        '48' => 'pl',
        '82' => 'ko',
        '34' => 'es',
        '30' => 'el',
        '42' => 'cs',
        '38' => 'hr',
        '12' => 'zh-CN',
        '47' => 'no',
        '33' => 'fr',
        '51' => 'pt'
    );

    public $deepl_aliases = array(
        '36' => 'hu',
        '39' => 'it',
        '49' => 'de',
        '48' => 'pl',
        '34' => 'es',
        '30' => 'el',
        '42' => 'cs',
        '12' => 'zh',
        '33' => 'fr',
        '51' => 'pt'
    );

    public $protected_strings = array('%s', '{0}', '{1}', '{2}', '%d', '%c', '%lf');

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

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

    function login($username, $password)
    {
        $user = $this->db->query("SELECT username, password FROM resourcemanager.partners WHERE username = '{$username}'")->row();

        if (empty($user))
            return false;

        $phpass = new PasswordHash(10, true);
        $ok     = $phpass->CheckPassword($password, $user->password);

        if (!$ok)
            return false;

        return $user;
    }

    private function getSaltedPassword($password)
    {
        return hash_hmac("sha512", $password, $this->salt);
    }

    private function hashSha512($password)
    {
        return password_hash($this->getSaltedPassword($password), PASSWORD_DEFAULT);
    }

    function getTotalRecords($lang, $where, $needEmpty = false)
    {
        $table      = $this->tables[$lang];
        $ref_table  = $this->tables['ref'];

        if (!$needEmpty) {
            if ($where == '')
                $where = ' WHERE ref.text != ""';
            else
                $where .= ' AND ref.text != ""';
        }

        $query  = "SELECT COUNT(*) AS count FROM {$ref_table} ref
            LEFT JOIN {$table} res ON ref.id = res.ref_id
            {$where}";
        $total = $this->db->query($query)->row();

        return $total->count;
    }

    function getResourceDatas($lang, $offset, $records, $where, $bestMatch = '', $exact_match = false, $needEmpty = false, $sort = '', $sorted_table = '')
    {
        $table      = $this->tables[$lang];
        $ref_table  = $this->tables['ref'];
        $order      = 'ORDER BY ';
        $needorder  = true;
        $recentSrch = (isset($_SESSION['recent_search']) && $_SESSION['recent_search'] == 'on') ? true : false;

        if ($sort != '' && !$recentSrch) {
            switch ($sorted_table) {
                case 'ref_text':
                    $order .= 'ref.text '.$sort.', ';
                    break;

                case 'lang_text':
                    $order .= 'res.text '.$sort.', ';
                    break;

                case 'ref_key':
                    if ($sort == 'desc') {
                        $order .= 'ref.key '.$sort;
                        $needorder = false;
                    }
                    break;

                default:
                    break;
            }

            $_SESSION['sort']   = $sort;
            $_SESSION['column'] = $sorted_table;
        }

        if (!$recentSrch) {
            if ($bestMatch == '') {
                if ($needorder)
                    $order .= 'ref.type_id, cast(ref.key AS UNSIGNED) ASC';
            } else {
                if (!$needorder)
                    $order .= ', ';

                if ($exact_match) {
                    $order .= '
                        CASE
                            WHEN ref.text = "'.$bestMatch.'" OR res.text = "'.$bestMatch.'" THEN 1
                            WHEN ref.text LIKE "'.$bestMatch.' %" OR res.text LIKE "'.$bestMatch.' %" THEN 2
                            WHEN ref.text LIKE "% '.$bestMatch.'" OR res.text LIKE "% '.$bestMatch.'" THEN 3
                            ELSE 4
                        END';
                } else {
                    $case1 = '';
                    $case2 = '';
                    $case3 = '';
                    $case4 = '';
                    $case5 = '';
                    $case6 = '';
                    $case7 = '';
                    $case8 = '';

                    $srchText   = explode(' ', $bestMatch);
                    $count      = count($srchText) > 0 ? count($srchText) : 1;

                    $case1 = 'res.text = "'.$bestMatch.'" OR ref.text = "'.$bestMatch.'"';
                    $case2 = 'res.text LIKE "'.$bestMatch.' %" OR ref.text LIKE "'.$bestMatch.' %"';
                    $case3 = 'res.text LIKE "% '.$bestMatch.'" OR ref.text LIKE "% '.$bestMatch.'"';

                    for ($i = 0; $i < $count; $i++) {
                        $text = $srchText[$i];

                        if ($i == 0) {
                            $case4 .= '(res.text REGEXP "'.$text.'" OR ref.text REGEXP "'.$text.'")';
                            $case5 .= '(res.text LIKE "%'.$text.'%" OR ref.text LIKE "%'.$text.'%")';
                            $case6 .= 'res.text = "'.$text.'" OR ref.text = "'.$text.'"';
                            $case7 .= 'res.text = "'.$text.' %" OR ref.text = "'.$text.' %"';
                            $case8 .= 'res.text = "% '.$text.'" OR ref.text = "% '.$text.'"';
                        } else {
                            $case4 .= ' AND (res.text REGEXP "'.$text.'" OR ref.text REGEXP "'.$text.'")';
                            $case5 .= ' AND (res.text LIKE "%'.$text.'%" AND ref.text LIKE "%'.$text.'%")';
                            $case6 .= ' OR res.text = "'.$text.'" OR ref.text = "'.$text.'"';
                            $case7 .= ' OR res.text = "'.$text.' %" OR ref.text = "'.$text.' %"';
                            $case8 .= ' OR res.text = "% '.$text.'" OR ref.text = "% '.$text.'"';
                        }
                    }

                    $order .= '
                        CASE
                            WHEN '.$case1.' THEN 1
                            WHEN '.$case2.' THEN 2
                            WHEN '.$case3.' THEN 3
                            WHEN '.$case4.' THEN 4
                            WHEN '.$case5.' THEN 5
                            WHEN '.$case6.' THEN 6
                            WHEN '.$case7.' THEN 7
                            WHEN '.$case8.' THEN 8
                            ELSE 9
                        END,
                        ref.text ASC';
                }
            }
        } else {
            $order .= 'ref.id DESC';
        }

        if (!$needEmpty) {
            if ($where == '')
                $where = ' WHERE ref.text != ""';
            else
                $where .= ' AND ref.text != ""';
        }

        $query = 'SELECT res.id AS res_id, res_types.name AS type_name, ref.key AS key_id, ref.type_id AS type_id,
                ref.text AS ref_text, res.text AS lang_text, ref.id AS ref_id, res.approved AS approved,
                ref.invalid AS invalid, res.changed AS changed, res.to_translate AS to_translate, ref.context AS context,
                ref.comment AS comment, ref.define_txt AS defines
            FROM '.$ref_table.' ref
            LEFT JOIN '.$table.' res ON ref.id = res.ref_id
            LEFT JOIN resourcemanager.types res_types ON ref.type_id = res_types.id
            '.$where.'
            '.$order.'
            LIMIT '.$offset.', '.$records;
        $resDatas   = $this->db->query($query)->result();

        return $resDatas;
    }

    function getContextDatas($ref_id)
    {
        $ref_table = $this->tables['ref'];

        $query = 'SELECT ref.text AS text, ref.comment AS context
            FROM '.$ref_table.' ref
            WHERE ref.id = '.$ref_id;
        $contextData = $this->db->query($query)->row_array();

        return $contextData;
    }

    function getExistingHits($ref_text)
    {
        $ref_table = $this->tables['ref'];

        $query = 'SELECT count(ref.id) AS count_ref
            FROM '.$ref_table.' ref
            WHERE (ref.text LIKE "% '.$ref_text.' %" OR ref.text = "'.$ref_text.'")';
        $reference = $this->db->query($query)->row();

        return $reference->count_ref;
    }

    function setSearchSessions($ref_text)
    {
        $_SESSION['search_name']        = $ref_text;
        $_SESSION['extended_search']    = 'on';
        $_SESSION['key_search']         = 'off';
        $_SESSION['type_id']            = 0;
        $_SESSION['status']             = 0;

        return true;
    }

    function getGoogleTranslateUrl($text_id, $partner_id)
    {
        $table = $this->tables['ref'];

        $url = "https://translate.google.com/?sl=en&tl={$this->gt_aliases[$partner_id]}";

        $query      = 'SELECT text FROM '.$table.' WHERE id = '.$text_id;
        $resource   = $this->db->query($query)->row();

        $url .= "&text={$resource->text}&op=translate";

        return $url;
    }

    function getDeeplUrl($text_id, $partner_id)
    {
        $table = $this->tables['ref'];

        $url = "https://www.deepl.com/translator#en/{$this->deepl_aliases[$partner_id]}";

        $query      = 'SELECT text FROM '.$table.' WHERE id = '.$text_id;
        $resource   = $this->db->query($query)->row();

        $url .= "/{$resource->text}";

        return $url;
    }

    function getResourceTypes()
    {
        $query = "SELECT * FROM resourcemanager.types";

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

    function getYears()
    {
        $query = "SELECT * FROM resourcemanager.year";

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

    function addReference($datas)
    {
        $table      = $this->tables['ref'];
        $modified   = date('Y-m-d H:i:s');

        for ($i = 0; $i < $datas['count']; $i++) {
            $query = 'SELECT ref.key AS ref_key
                FROM '.$table.' ref
                WHERE ref.key = "'.$datas['key'][$i].'" AND ref.type_id = '.$datas['types'][$i];
            $ref = $this->db->query($query)->row();

            if (!empty($ref)) {
                $response['not_inserted_keys'][$i] = $datas['key'][$i].' (Duplicate)';
                continue;
            }

            $reference              = new stdClass();
            $reference->type_id     = $datas['types'][$i];
            $reference->key         = $datas['key'][$i];
            $reference->text        = $datas['text'][$i];
            $reference->from        = $datas['year'];
            $reference->modified    = $modified;
            $reference->invalid     = 0;
            $reference->comment     = $datas['contexts'][$i];

            $this->db->insert("{$table}", $reference);
            $refID = $this->db->insert_id();

            foreach ($this->tables as $key => $value) {
                if ($key == 'ref')
                    continue;

                $resource               = new stdClass();
                $resource->ref_id       = $refID;
                $resource->to_translate = 1;
                $resource->modified     = $modified;
                $resource->approved     = 0;

                if ($key == 36)
                    $resource->text = $datas['text_hun'][$i];
                else
                    $resource->text = $datas['text'][$i];

                $this->db->insert("{$value}", $resource);
            }
        }

        if (empty($response['not_inserted_keys']))
            $response['inserted'] = true;
        else
            $response['inserted'] = false;

        return $response;
    }

    function updateReference($ref_id, $text, $hun_text)
    {
        $ref_table  = $this->tables['ref'];
        $res_table  = $this->tables['36'];
        $date       = date('Y-m-d H:i');

        $warn_res = $this->get_errors_and_warnings($text, $hun_text);
        if ($warn_res['error'] != '') {
            $response['error'] = $warn_res['error'];
            return $response;
        }

        $response['warning'] = $warn_res['warning'];

        $updateQuery = 'UPDATE '.$ref_table.'
            SET text = "'.$text.'", invalid = 0, modified = "'.$date.'"
            WHERE id = '.$ref_id;
        $this->db->query($updateQuery);

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

        $updateQueryHun = 'UPDATE '.$res_table.'
            SET text = "'.$hun_text.'", to_translate = 0, modified = "'.$date.'", approved = 1, changed = 0
            WHERE ref_id = '.$ref_id;
        $this->db->query($updateQueryHun);

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

        return $response;
    }

    function deleteReference($ref_id)
    {
        $table = $this->tables['ref'];

        $deleteQuery = "DELETE FROM {$table} WHERE id = {$ref_id}";
        $this->db->query($deleteQuery);

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

        return $response;
    }

    function updateResource($partner_id, $ref_id, $text)
    {
        $res_table  = $this->tables[$partner_id];
        $ref_table  = $this->tables['ref'];
        $date       = date('Y-m-d H:i');

        $selectRef  = "SELECT text FROM {$ref_table} WHERE id = {$ref_id}";
        $ref        = $this->db->query($selectRef)->row();

        $warn_res = $this->get_errors_and_warnings($ref->text, $text);
        if ($warn_res['error'] != '') {
            $response['error'] = $warn_res['error'];
            return $response;
        }

        $response['warning'] = $warn_res['warning'];

        $selectRes  = "SELECT id FROM {$res_table} WHERE ref_id = {$ref_id}";
        $res        = $this->db->query($selectRes)->row();

        if (!empty($res)) {
            $updateQuery = 'UPDATE '.$res_table.'
                SET text = "'.$text.'", to_translate = 0, modified = "'.$date.'", approved = 1, changed = 0
                WHERE id = '.$res->id;
            $this->db->query($updateQuery);
        } else {
            $resource               = new stdClass();
            $resource->ref_id       = $ref_id;
            $resource->text         = $text;
            $resource->to_translate = 0;
            $resource->modified     = $date;
            $resource->approved     = 1;

            $this->db->insert("{$res_table}", $resource);
        }

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

        return $response;
    }

    function rejectResource($partner_id, $res_id)
    {
        $table = $this->tables[$partner_id];

        $updateQuery = "UPDATE {$table}
            SET approved = 0
            WHERE id = {$res_id}";
        $this->db->query($updateQuery);

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

        return $response;
    }

    function saveContext($ref_id, $comment)
    {
        $ref_table = $this->tables['ref'];

        $updateQuery = 'UPDATE '.$ref_table.'
            SET comment = "'.$comment.'", context = 0
            WHERE id = '.$ref_id;
        $this->db->query($updateQuery);

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

        return $updated;
    }

    function search($datas)
    {
        $where = ' WHERE 1';

        if (isset($datas['types'])) {
            if ($datas['types'] != 0)
                $where .= ' AND ref.type_id = '.$datas['types'];

            $_SESSION['type_id'] = $datas['types'];
        }

        if (isset($datas['status'])) {
            switch ($datas['status']) {
                case 1: // New resources
                    $where .= ' AND res.to_translate = 1';
                    break;

                case 2: // Changed resources
                    $where .= ' AND res.changed = 1';
                    break;

                case 3: // Not approved resources
                    $where .= ' AND res.approved = 0';
                    break;

                case 4: // Approved resources
                    $where .= ' AND res.approved = 1';
                    break;

                case 5: // Unchanged resources
                    $where .= ' AND res.changed = 0';
                    break;

                case 6: // Existing resources
                    $where .= ' AND res.to_translate = 0';
                    break;

                case 7: // With context
                    $where .= ' AND ref.comment != ""';
                    break;

                case 8: // Without context
                    $where .= ' AND (ref.comment = "" OR ref.comment is null)';
                    break;

                case 9: // Context requested
                    $where .= ' AND ref.context = 1 AND (ref.comment = "" OR ref.comment is null)';
                    break;

                default:
                    break;
            }

            $_SESSION['status'] = $datas['status'];
        }

        if (isset($datas['approved'])) {
            if ($datas['approved'] != -1)
                $where .= ' AND approved = '.$datas['approved'];

            $_SESSION['approved'] = $datas['approved'];
        }

        if (isset($datas['recent_search']))
            $_SESSION['recent_search'] = $datas['recent_search'];

        if (isset($datas['errors_search']) && $datas['errors_search'] == 'on') {
            for ($i = 0; $i < count($this->protected_strings); $i++) {
                $string_prot = $this->getProtectedStringCountFromSQL($this->protected_strings[$i]);

                if ($i == 0)
                    $where .= ' AND ('.$string_prot.')';
                else
                    $where .= ' OR ('.$string_prot.')';
            }

            $_SESSION['errors_search'] = $datas['errors_search'];
        } else {
            unset($_SESSION['errors_search']);
        }

        if (isset($datas['search_name'])) {
            if ($datas['search_name'] != '') {
                if (isset($datas['extended_search']) && $datas['extended_search'] == 'on') {
                    $text = $datas['search_name'];

                    $where .= ' AND ';
                    $where .= '(';

                    if ($datas['key_search'] != 'on') {
                        $where .= 'res.text LIKE "%|'.$text.'%" ESCAPE "|"';
                        $where .= ' OR ref.text LIKE "%|'.$text.'%" ESCAPE "|" OR ';

                        unset($_SESSION['key_search']);
                    } else {
                        $_SESSION['key_search'] = $datas['key_search'];
                    }

                    $where .= 'ref.key = "'.$text.'"';
                    $where .= ')';

                    $_SESSION['extended_search'] = $datas['extended_search'];
                } else {
                    $srchText   = explode(' ', $datas['search_name']);
                    $count      = count($srchText) > 0 ? count($srchText) : 1;
                    $where      .= ' AND ';
                    $where      .= '(';

                    if (!isset($datas['key_search']) || $datas['key_search'] != 'on') {
                        $where .= '(';

                        for ($i = 0; $i < $count; $i++) {
                            $text = $srchText[$i];

                            if ($i == 0)
                                $where .= 'res.text LIKE "%|'.$text.'%" ESCAPE "|"';
                            else
                                $where .= ' OR res.text LIKE "%|'.$text.'%" ESCAPE "|"';
                        }

                        $where .= ')';

                        $where .= ' OR ';
                        $where .= '(';

                        for ($i = 0; $i < $count; $i++) {
                            $text = $srchText[$i];

                            if ($i == 0)
                                $where .= 'ref.text LIKE "%|'.$text.'%" ESCAPE "|"';
                            else
                                $where .= ' OR ref.text LIKE "%|'.$text.'%" ESCAPE "|"';
                        }

                        $where .= ')';

                        $where .= ' OR ';

                        unset($_SESSION['key_search']);
                    } else {
                        $_SESSION['key_search'] = $datas['key_search'];
                    }

                    $where .= '(';

                    for ($i = 0; $i < $count; $i++) {
                        $text = $srchText[$i];

                        if ($i == 0)
                            $where .= 'ref.key LIKE "%'.$text.'%"';
                        else
                            $where .= ' OR ref.key LIKE "%'.$text.'%"';
                    }

                    $where .= ')';
                    $where .= ')';

                    unset($_SESSION['extended_search']);
                }
            } else {
                unset($_SESSION['extended_search']);
                unset($_SESSION['key_search']);
            }
        }

        $_SESSION['search_name'] = $datas['search_name'];

        return $where;
    }

    function getProtectedStringCountFromSQL($prot_string)
    {
        return 'ROUND((LENGTH(res.text) - LENGTH(REPLACE(res.text, "'.$prot_string.'", ""))) / LENGTH("'.$prot_string.'")) != ROUND((LENGTH(ref.text) - LENGTH(REPLACE(ref.text, "'.$prot_string.'", ""))) / LENGTH("'.$prot_string.'"))';
    }

    function insertResourceToDatabase($filename, $file, $lang)
    {
        /*$table      = $this->tables[$lang];
        $ref_table  = $this->tables['ref'];

        $xml = simplexml_load_file($file) or die("Error: Cannot create object");

        foreach ($xml->Res AS $data) {
            $typeID     = $this->getResourceType($data->attributes()->Type);
            $id         = trim($data->attributes()->ID, ' ');
            $text       = trim($data->attributes()->Text, ' ');
            $translate  = $data->attributes()->ToTranslate == 'true' ? 1 : 0;
            $breaks     = array("<br />","<br>","<br/>");
            $text       = str_ireplace($breaks, "\r\n", $text);

            $query = 'SELECT id FROM '.$ref_table.'
                WHERE text_table_ref.key = "'.$id.'" AND type_id = '.$typeID;
            $ref = $this->db->query($query)->row();

            if ($lang == 'ref') {
                if (empty($ref)) {
                    $reference = array(
                        'type_id'   => $typeID,
                        'key'       => "{$id}",
                        'text'      => "{$text}",
                        'from'      => 1,
                        'invalid'   => 0
                    );

                    $this->db->insert("{$ref_table}", $reference);
                }
            } else {
                if (!empty($ref)) {
                    $query2 = 'SELECT id FROM '.$table.'
                        WHERE ref_id = '.$ref->id;
                    $res = $this->db->query($query2)->row();

                    if (!empty($res))
                        continue;

                    $resource = array(
                        'ref_id'        => $ref->id,
                        'text'          => "{$text}",
                        'approved'      => 1,
                        'to_translate'  => $translate
                    );

                    $this->db->insert("{$table}", $resource);
                }
            }
        }*/

        return true;
    }

    function insertTranslatToDatabase($filename, $file, $lang)
    {
        $ref_table  = $this->tables['ref'];
        $typeID     = $this->getTranslatType($filename);

        $xml = simplexml_load_file($file) or die("Error: Cannot create object");

        $i = 0;

        foreach ($xml AS $data) {
            $data->Eng  = str_replace('\\', '&#92;', $data->Eng);
            $data->Nat  = str_replace('\\', '&#92;', $data->Nat);
            $eng        = trim($data->Eng, ' ');
            $nat        = trim($data->Nat, ' ');
            $translate  = $data->ToTranslate == 'TRUE' ? 1 : 0;
            $breaks     = array("<br />","<br>","<br/>");
            $nat        = str_ireplace($breaks, "\r\n", $nat);

            $query = 'SELECT id FROM '.$ref_table.'
                WHERE text_table_ref.key = "'.$eng.'" AND type_id = '.$typeID;
            $ref = $this->db->query($query)->row();

            if (empty($ref)) {
                if ($eng != $nat) {
                    if (strpos($eng, 'info@') !== false)
                        continue;

                    $res_key = explode("@", $eng);

                    $query = 'SELECT ref.id AS refID, ref.key AS refKey, ref.define_txt AS defines
                        FROM '.$ref_table.' ref
                        WHERE ref.key = "'.$res_key[0].'" AND ref.type_id = '.$typeID;
                    $res = $this->db->query($query)->row();

                    if (!empty($res)) {
                        if ($res->defines != '') {
                            $defines = json_decode($res->defines);
                            $json_str = '';

                            $json_str .= '{
                                "defines": [';

                            foreach ($defines->defines as $define) {
                                $json_str .= '{
                                    "lang": "ita",
                                    "ext_key": "'.$define->ext_key.'",
                                    "text": "'.$define->text.'"
                                },';
                            }

                            $json_str .= '{
                                    "lang": "ita",
                                    "ext_key": "@'.$res_key[1].'",
                                    "text": "'.$nat.'"
                                }';

                            $json_str .= ']
                                }';

                            $updateQuery = "UPDATE {$ref_table}
                                SET define_txt = '$json_str'
                                WHERE id = {$res->refID}";
                            
                            $this->db->query($updateQuery);
                        } else {
                            $json_str = '{
                                    "defines": [
                                        {
                                            "lang": "ita",
                                            "ext_key": "@'.$res_key[1].'",
                                            "text": "'.$nat.'"
                                        }
                                    ]
                                }';

                            $updateQuery = "UPDATE {$ref_table}
                                SET define_txt = '$json_str'
                                WHERE id = {$res->refID}";
                            
                            $this->db->query($updateQuery);
                        }
                    }

                    $i++;
                }
            }
        }

        /*$table      = $this->tables[$lang];
        $ref_table  = $this->tables['ref'];
        $typeID     = $this->getTranslatType($filename);

        $xml = simplexml_load_file($file) or die("Error: Cannot create object");

        foreach ($xml AS $data) {
            $data->Eng  = str_replace('\\', '&#92;', $data->Eng);
            $data->Nat  = str_replace('\\', '&#92;', $data->Nat);
            $eng        = trim($data->Eng, ' ');
            $nat        = trim($data->Nat, ' ');
            $translate  = $data->ToTranslate == 'TRUE' ? 1 : 0;
            $breaks     = array("<br />","<br>","<br/>");
            $nat        = str_ireplace($breaks, "\r\n", $nat);

            $query = 'SELECT id FROM '.$ref_table.'
                WHERE text_table_ref.key = "'.$eng.'" AND type_id = '.$typeID;
            $ref = $this->db->query($query)->row();

            if (empty($ref)) {
                if ($eng != $nat) {
                    $reference = array(
                        'type_id'   => $typeID,
                        'key'       => "{$eng}",
                        'text'      => "{$eng}",
                        'from'      => 1,
                        'invalid'   => 1
                    );

                    $this->db->insert("{$ref_table}", $reference);

                    $refID = $this->db->insert_id();

                    foreach ($this->tables as $key => $value) {
                        if ($key == 'ref')
                            continue;

                        if ($key == '39') {
                            $txt = $nat;
                            $to_translate = $translate;
                        } else {
                            $txt = $eng;
                            $to_translate = 1;
                        }

                        $query3 = 'SELECT id FROM '.$value.' res
                            WHERE res.ref_id = '.$refID;
                        $res2 = $this->db->query($query3)->row();

                        if (empty($res2)) {
                            $resource = array(
                                'ref_id'        => $refID,
                                'text'          => "{$txt}",
                                'to_translate'  => $to_translate,
                                'approved'      => 0
                            );

                            $this->db->insert("{$value}", $resource);
                        }
                    }
                }
            }
        }*/

        /*$table      = $this->tables[$lang];
        $ref_table  = $this->tables['ref'];
        $typeID     = $this->getTranslatType($filename);

        $xml = simplexml_load_file($file) or die("Error: Cannot create object");

        foreach ($xml AS $data) {
            $data->Eng  = str_replace('\\', '&#92;', $data->Eng);
            $data->Nat  = str_replace('\\', '&#92;', $data->Nat);
            $eng        = trim($data->Eng, ' ');
            $nat        = trim($data->Nat, ' ');
            $translate  = $data->ToTranslate == 'TRUE' ? 1 : 0;
            $breaks     = array("<br />","<br>","<br/>");
            $nat        = str_ireplace($breaks, "\r\n", $nat);

            $query = 'SELECT id FROM '.$ref_table.'
                WHERE text_table_ref.key = "'.$eng.'" AND type_id = '.$typeID;
            $ref = $this->db->query($query)->row();

            if ($lang == 'ref') {
                if (empty($ref)) {
                    $reference = array(
                        'type_id'   => $typeID,
                        'key'       => "{$eng}",
                        'text'      => "{$nat}",
                        'from'      => 1,
                        'invalid'   => 0
                    );

                    $this->db->insert("{$ref_table}", $reference);
                }
            } else {
                if (!empty($ref)) {
                    $query2 = 'SELECT id FROM '.$table.'
                        WHERE ref_id = '.$ref->id;
                    $res = $this->db->query($query2)->row();

                    if (empty($res)) {
                        $resource = array(
                            'ref_id'        => $ref->id,
                            'text'          => "{$nat}",
                            'approved'      => 1,
                            'to_translate'  => $translate
                        );

                        $this->db->insert("{$table}", $resource);
                    }
                }
            }
        }*/

        return true;
    }

    function correctResources()
    {
        foreach ($this->tables as $key => $value) {
            if ($key == 'ref')
                continue;

            $query1 = 'SELECT id FROM '.$value;
            $res1 = $this->db->query($query1)->row();

            if (!empty($res1)) {
                $query2 = 'SELECT * FROM '.$this->tables['ref'];
                $references = $this->db->query($query2)->result();

                if (!empty($references)) {
                    foreach ($references as $ref) {
                        $query3 = 'SELECT id FROM '.$value.' res
                            WHERE res.ref_id = '.$ref->id;
                        $res2 = $this->db->query($query3)->row();

                        if (empty($res2)) {
                            $resource = array(
                                'ref_id'        => $ref->id,
                                'text'          => "{$ref->text}",
                                'to_translate'  => 1,
                                'approved'      => 0
                            );

                            $this->db->insert("{$value}", $resource);
                        }
                    }
                }
            }
        }

        return true;
    }

    function getLastKey($exceptions, $typeID)
    {
        $table = $this->tables['ref'];

        $query  = "SELECT * FROM {$table} ref WHERE ref.type_id = {$typeID} ORDER BY cast(ref.key as unsigned) DESC";
        $res    = $this->db->query($query)->result();

        $min = 0;
        $max = $res[0]->key;

        $keszlet = range((int)$min, (int)$max);
        foreach ($res as $r) $keys[] = $r->key;

        $diff = array_diff($keszlet, $keys);
        $diff = array_values($diff);

        if (count($diff) < 20) {
            for ($i = 1; $i <= 20-count($diff); $i++)
                array_push($diff, $max+$i);
        }

        return $diff;
    }

    function askForContext($ref_id, $partner_id)
    {
        $ref_table  = $this->tables['ref'];

        $updateQuery = "UPDATE {$ref_table}
            SET context = 1
            WHERE id = {$ref_id}";
        $this->db->query($updateQuery);

        if ($this->db->affected_rows() > 0) {
            $query = "SELECT ref.key AS ref_key, ref.text AS txt, type.name AS type_name
                FROM {$ref_table} ref
                LEFT JOIN resourcemanager.types type ON type.id = ref.type_id
                WHERE ref.id = {$ref_id}";
            $ref = $this->db->query($query)->row();

            $query = "SELECT * FROM resourcemanager.partners
                WHERE username = {$partner_id}";
            $partner = $this->db->query($query)->row();

            $subject = 'Context Request';
            $message = '';

            $message .= "There's a new context request from a {$partner->name} partner for the following resource: <br/><br/>";
            $message .= "Text: <b>{$ref->txt}</b> <br/>";
            $message .= "Type: <b>{$ref->type_name}</b> <br/>";
            $message .= "Key: <b>{$ref->ref_key}</b> <br/><br/>";
            $message .= "Use the Resource Manager to provide a context for this resource: <br/>";
            $message .= "<a href='https://partners.archlinexp.com/resourcemanager/'>https://partners.archlinexp.com/resourcemanager/</a>";

            $to         = 'resourcemanager@cadline.hu';
            $headers    = "Content-Type: text/html; charset=utf-8\r\n";

            mail($to, $subject, $message, $headers);

            $asked = true;
        } else {
            $asked = false;
        }

        return $asked;
    }

    function getTranslatType($filename)
    {
        $filename   = explode(".", $filename);
        $name       = $filename[0];

        return $this->getResourceType($name);
    }

    function getResourceType($name)
    {
        $query  = "SELECT id FROM resourcemanager.types WHERE alias = '{$name}'";
        $type   = $this->db->query($query)->row();

        return $type->id;
    }

    function isTranslat($filename)
    {
        $filename = substr($filename, 0, 8);

        return strtolower($filename) == 'translat';
    }

    function isResource($filename)
    {
        $filename = substr($filename, 0, 8);

        return strtolower($filename) == 'resource';
    }

    function isAdmin()
    {
        return substr($this->phpsession->get('access_id'), 0, 2) == 36 || substr($this->phpsession->get('access_id'), 0, 2) == 37;
    }

    function getAliases()
    {
        return $this->tables;
    }

    function getPartners()
    {
        $query = "SELECT SUBSTR(username, 1, 2) AS username, name, prefix, flag
            FROM resourcemanager.partners GROUP BY SUBSTR(username, 1, 2) ORDER BY name";

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

    function getSelectedPartner($partner_id)
    {
        $query      = "SELECT * FROM resourcemanager.partners WHERE username = '{$partner_id}'";

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

    function getHelpText()
    {
        $query  = "SELECT * FROM www_archline_hu.eng_content WHERE id = 898";
        $help   = $this->db->query($query)->row();

        return $help->introtext;
    }

    function getAdminHelpText()
    {
        $query  = "SELECT * FROM www_archline_hu.eng_content WHERE id = 902";
        $help   = $this->db->query($query)->row();

        return $help->introtext;
    }

    function getTypeIdFromName($type_name)
    {
        $query  = "SELECT id FROM resourcemanager.types WHERE name = '{$type_name}'";
        $type   = $this->db->query($query)->row();

        return $type->id;
    }

    function getPartnerID()
    {

        if ($this->isAdmin()) {
            $aliases = $this->getAliases();

            if (!empty($aliases[$this->uri->segment(3)]))
                $partnerID = substr($this->uri->segment(3), 0, 2);
            else
                $partnerID = 36;
        } else {
            $partnerID = $_COOKIE["access_id"] != '' ? substr($_COOKIE["access_id"], 0, 2) : substr($this->phpsession->get('access_id'), 0, 2);
        }

        return $partnerID;
    }

    function updateProfile($profile_name, $profile_email, $notification)
    {
        $username = $this->phpsession->get('access_id');

        $updateQuery = 'UPDATE resourcemanager.partners
            SET profile_name = "'.$profile_name.'", profile_email = "'.$profile_email.'", profile_notification = '.$notification.'
            WHERE username = "'.$username.'"';
        $this->db->query($updateQuery);

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

        return $response;
    }

    function redirectInvalidUrl()
    {
        if ($this->isAdminPage() && !$this->isAdmin())
            show_404();
    }

    function isAdminPage()
    {
        $file = "/var/www/hosting/archlinexp.com/partners/application/views/resourcemanager/admin/{$this->uri->segment(2)}.php";

        if (file_exists($file))
            return true;

        return false;
    }

    function tableExist($partner_id)
    {
        return array_key_exists($partner_id, $this->mresourcemanager->tables);
    }

    function loadTemplate($temp, $resData, $title, $active = '')
    {
        $header                 = array();
        $header['title']        = $title;
        $header['partner_id']   = $this->getPartnerID();

        if ($active != '')
            $header['active'] = $active;

        $this->load->view('resourcemanager/includes/header.php', $header);
        $this->load->view($temp, $resData);
        $this->load->view('resourcemanager/includes/footer.php');
    }

    function starts_with_upper($str)
    {
        $chr = mb_substr ($str, 0, 1, "UTF-8");
        return mb_strtolower($chr, "UTF-8") != $chr;
    }

    function strpos_all($haystack, $needle)
    {
        $offset = 0;
        $allpos = array();
        while (($pos = strpos($haystack, $needle, $offset)) !== FALSE) {
            $offset   = $pos + 1;
            $allpos[] = $pos;
        }
        return $allpos;
    }

    function cut_string($str, $length)
    {
        $str_len        = strlen($str);
        $length         = $length+3;
        $cutted_string  = $str;

        if ($str_len > $length)
            $cutted_string = substr($str, 0, $length-6).'...';

        return $cutted_string;
    }

    function get_errors_and_warnings($ref_text, $lang_text)
    {
        $result = array();

        $ref_last       = substr($ref_text, -1);
        $ref_Upper      = $this->starts_with_upper($ref_text);
        $ref_first      = substr($ref_text, 0, 1);
        $ref_length     = strlen($ref_text);
        $res_last       = mb_substr($lang_text, -1);
        $res_Upper      = $this->starts_with_upper($lang_text);
        $res_first      = substr($lang_text, 0, 1);
        $res_length     = mb_strlen($lang_text);
        $roundMore      = round($res_length/$ref_length, 1);
        $sorted_strings = $this->order_protected_strings($ref_text, $lang_text);
        $pos_ref        = strpos(strtolower($ref_text), 'archline.xp');
        $pos_res        = strpos(strtolower($lang_text), 'archline.xp');

        if ((count($sorted_strings['lang']) < count($sorted_strings['ref'])) || (count($sorted_strings['lang']) > count($sorted_strings['ref'])))
            $result['error'] .= '<li>The translation contains less or more protected strings than the source text.</li>';

        if (!$sorted_strings['order'] && $result['error'] == '')
            $result['error'] .= '<li>The order of protected strings in the translated text does not match the source.</li>';

        if ($pos_ref !== false) {
            $ref_AL = substr($ref_text, $pos_ref, 11);
            $res_AL = substr($lang_text, $pos_res, 11);
            $s1     = mb_convert_encoding($ref_AL, "UTF-8", "UTF-16BE");
            $s2     = mb_convert_encoding($res_AL, "UTF-8", "UTF-16BE");

            if ($s1 != $s2)
                $result['warning'] .= '<li>ARCHLine.XP is not spelled correctly</li>';
        }

        if ((ctype_punct($ref_last) || ctype_punct($res_last)) && $ref_last != $res_last)
            $result['warning'] .= '<li>Punctuations do not match.</li>';

        if ((($ref_Upper && !$res_Upper) || (!$ref_Upper && $res_Upper)) && $_SESSION['partner_id'] != 12 && $_SESSION['partner_id'] != 82)
            $result['warning'] .= '<li>First letter casing do not match.</li>';

        if (ctype_space($ref_first) && !ctype_space($res_first))
            $result['warning'] .= '<li>Source text starts with space.</li>';

        if (ctype_space($ref_last) && !ctype_space($res_last))
            $result['warning'] .= '<li>Source text ends with space.</li>';

        if ($roundMore > 3)
            $result['warning'] .= '<li>The translation contains at least 3 times more characters than the source.</li>';

        return $result;
    }

    function order_protected_strings($ref_text, $lang_text)
    {
        $order      = true;
        $p_strings  = $this->protected_strings;

        $ref_array      = array();
        $lang_array     = array();
        $lang_p_strings = array();
        $ref_p_strings  = array();

        for ($i = 0; $i < count($p_strings); $i++) {
            if (strpos($ref_text, $p_strings[$i]) !== FALSE) {
                $pos = $this->strpos_all($ref_text, $p_strings[$i]);
                for ($j = 0; $j < count($pos); $j++)
                    $ref_p_strings[$pos[$j]] = $p_strings[$i];
            }

            if (strpos($lang_text, $p_strings[$i]) !== FALSE) {
                $pos = $this->strpos_all($lang_text, $p_strings[$i]);
                for ($j = 0; $j < count($pos); $j++)
                    $lang_p_strings[$pos[$j]] = $p_strings[$i];
            }
        }

        ksort($ref_p_strings);
        ksort($lang_p_strings);

        foreach ($ref_p_strings as $key => $value)
            array_push($ref_array, $value);

        foreach ($lang_p_strings as $key => $value)
            array_push($lang_array, $value);

        for ($i = 0; $i < count($ref_array); $i++) {
            if ($ref_array[$i] != $lang_array[$i])
                $order = false;
        }

        $sorted_array['ref']    = $ref_array;
        $sorted_array['lang']   = $lang_array;
        $sorted_array['order']  = $order;

        return $sorted_array;
    }
}
?>
