<?php

/**
 * i-doit
 *
 * Helper methods for installation process
 *
 * @package     i-doit
 * @subpackage  Helper
 * @author      Denis Koroliov <dkorolov@i-doit.com>
 * @copyright   synetics GmbH
 * @license     http://www.i-doit.com/license
 * @since       1.18
 */
class isys_helper_install
{
    /**
     * Preparing persons list data array
     *
     * @return array[]
     *
     * @author      Denis Koroliov <dkorolov@i-doit.com>
     */
    public static function getDataForPersonsList() {
        return [
                    [
                        'isys_cats_person_list__id' => 1,
                        'isys_cats_person_list__isys_connection__id' => 1,
                        'isys_cats_person_list__isys_ldap__id' => NULL,
                        'isys_cats_person_list__title' => 'guest',
                        'isys_cats_person_list__ldap_dn' => '',
                        'isys_cats_person_list__description' => '',
                        'isys_cats_person_list__sort' => 5,
                        'isys_cats_person_list__const' => NULL,
                        'isys_cats_person_list__personnel_number' => '',
                        'isys_cats_person_list__user_pass' => 'guest',
                        'isys_cats_person_list__last_name' => '',
                        'isys_cats_person_list__first_name' => 'guest',
                        'isys_cats_person_list__department' => '',
                        'isys_cats_person_list__position' => NULL,
                        'isys_cats_person_list__photo' => NULL,
                        'isys_cats_person_list__mail_address' => '',
                        'isys_cats_person_list__phone_company' => '',
                        'isys_cats_person_list__phone_mobile' => '',
                        'isys_cats_person_list__phone_home' => '',
                        'isys_cats_person_list__fax' => '',
                        'isys_cats_person_list__academic_degree' => NULL,
                        'isys_cats_person_list__function' => NULL,
                        'isys_cats_person_list__service_designation' => NULL,
                        'isys_cats_person_list__city' => NULL,
                        'isys_cats_person_list__zip_code' => NULL,
                        'isys_cats_person_list__street' => NULL,
                        'isys_cats_person_list__status' => 2,
                        'isys_cats_person_list__property' => 0,
                        'isys_cats_person_list__isys_obj__id' => 4,
                        'isys_cats_person_list__isys_catg_relation_list__id' => NULL,
                        'isys_cats_person_list__pager' => NULL,
                        'isys_cats_person_list__salutation' => NULL,
                        'isys_cats_person_list__last_login' => NULL,
                        'isys_cats_person_list__custom1' => NULL,
                        'isys_cats_person_list__custom2' => NULL,
                        'isys_cats_person_list__custom3' => NULL,
                        'isys_cats_person_list__custom3' => NULL,
                        'isys_cats_person_list__custom4' => NULL,
                        'isys_cats_person_list__custom6' => NULL,
                        'isys_cats_person_list__custom7' => NULL,
                        'isys_cats_person_list__custom8' => NULL,
                        'isys_cats_person_list__disabled_login' =>0,
                        'isys_cats_person_list__unmigrated_password' => 0,
                    ],
                    [
                        'isys_cats_person_list__id' => 2,
                        'isys_cats_person_list__isys_connection__id' => 2,
                        'isys_cats_person_list__isys_ldap__id' => NULL,
                        'isys_cats_person_list__title' => 'reader',
                        'isys_cats_person_list__ldap_dn' => '',
                        'isys_cats_person_list__description' => '',
                        'isys_cats_person_list__sort' => 5,
                        'isys_cats_person_list__const' => NULL,
                        'isys_cats_person_list__personnel_number' => '',
                        'isys_cats_person_list__user_pass' => 'reader',
                        'isys_cats_person_list__last_name' => '',
                        'isys_cats_person_list__first_name' => 'reader',
                        'isys_cats_person_list__department' => '',
                        'isys_cats_person_list__position' => NULL,
                        'isys_cats_person_list__photo' => NULL,
                        'isys_cats_person_list__mail_address' => '',
                        'isys_cats_person_list__phone_company' => '',
                        'isys_cats_person_list__phone_mobile' => '',
                        'isys_cats_person_list__phone_home' => '',
                        'isys_cats_person_list__fax' => '',
                        'isys_cats_person_list__academic_degree' => NULL,
                        'isys_cats_person_list__function' => NULL,
                        'isys_cats_person_list__service_designation' => NULL,
                        'isys_cats_person_list__city' => NULL,
                        'isys_cats_person_list__zip_code' => NULL,
                        'isys_cats_person_list__street' => NULL,
                        'isys_cats_person_list__status' => 2,
                        'isys_cats_person_list__property' => 0,
                        'isys_cats_person_list__isys_obj__id' => 5,
                        'isys_cats_person_list__isys_catg_relation_list__id' => NULL,
                        'isys_cats_person_list__pager' => NULL,
                        'isys_cats_person_list__salutation' => NULL,
                        'isys_cats_person_list__last_login' => NULL,
                        'isys_cats_person_list__custom1' => NULL,
                        'isys_cats_person_list__custom2' => NULL,
                        'isys_cats_person_list__custom3' => NULL,
                        'isys_cats_person_list__custom3' => NULL,
                        'isys_cats_person_list__custom4' => NULL,
                        'isys_cats_person_list__custom6' => NULL,
                        'isys_cats_person_list__custom7' => NULL,
                        'isys_cats_person_list__custom8' => NULL,
                        'isys_cats_person_list__disabled_login' =>0,
                        'isys_cats_person_list__unmigrated_password' => 0,
                    ],
                    [
                        'isys_cats_person_list__id' => 3,
                        'isys_cats_person_list__isys_connection__id' => 3,
                        'isys_cats_person_list__isys_ldap__id' => NULL,
                        'isys_cats_person_list__title' => 'editor',
                        'isys_cats_person_list__ldap_dn' => '',
                        'isys_cats_person_list__description' => '',
                        'isys_cats_person_list__sort' => 5,
                        'isys_cats_person_list__const' => NULL,
                        'isys_cats_person_list__personnel_number' => '',
                        'isys_cats_person_list__user_pass' => 'editor',
                        'isys_cats_person_list__last_name' => '',
                        'isys_cats_person_list__first_name' => 'editor',
                        'isys_cats_person_list__department' => '',
                        'isys_cats_person_list__position' => NULL,
                        'isys_cats_person_list__photo' => NULL,
                        'isys_cats_person_list__mail_address' => '',
                        'isys_cats_person_list__phone_company' => '',
                        'isys_cats_person_list__phone_mobile' => '',
                        'isys_cats_person_list__phone_home' => '',
                        'isys_cats_person_list__fax' => '',
                        'isys_cats_person_list__academic_degree' => NULL,
                        'isys_cats_person_list__function' => NULL,
                        'isys_cats_person_list__service_designation' => NULL,
                        'isys_cats_person_list__city' => NULL,
                        'isys_cats_person_list__zip_code' => NULL,
                        'isys_cats_person_list__street' => NULL,
                        'isys_cats_person_list__status' => 2,
                        'isys_cats_person_list__property' => 0,
                        'isys_cats_person_list__isys_obj__id' => 6,
                        'isys_cats_person_list__isys_catg_relation_list__id' => NULL,
                        'isys_cats_person_list__pager' => NULL,
                        'isys_cats_person_list__salutation' => NULL,
                        'isys_cats_person_list__last_login' => NULL,
                        'isys_cats_person_list__custom1' => NULL,
                        'isys_cats_person_list__custom2' => NULL,
                        'isys_cats_person_list__custom3' => NULL,
                        'isys_cats_person_list__custom3' => NULL,
                        'isys_cats_person_list__custom4' => NULL,
                        'isys_cats_person_list__custom6' => NULL,
                        'isys_cats_person_list__custom7' => NULL,
                        'isys_cats_person_list__custom8' => NULL,
                        'isys_cats_person_list__disabled_login' =>0,
                        'isys_cats_person_list__unmigrated_password' => 0,
                    ],
                    [
                        'isys_cats_person_list__id' => 4,
                        'isys_cats_person_list__isys_connection__id' => 4,
                        'isys_cats_person_list__isys_ldap__id' => NULL,
                        'isys_cats_person_list__title' => 'author',
                        'isys_cats_person_list__ldap_dn' => '',
                        'isys_cats_person_list__description' => '',
                        'isys_cats_person_list__sort' => 5,
                        'isys_cats_person_list__const' => NULL,
                        'isys_cats_person_list__personnel_number' => '',
                        'isys_cats_person_list__user_pass' => 'author',
                        'isys_cats_person_list__last_name' => '',
                        'isys_cats_person_list__first_name' => 'author',
                        'isys_cats_person_list__department' => '',
                        'isys_cats_person_list__position' => NULL,
                        'isys_cats_person_list__photo' => NULL,
                        'isys_cats_person_list__mail_address' => '',
                        'isys_cats_person_list__phone_company' => '',
                        'isys_cats_person_list__phone_mobile' => '',
                        'isys_cats_person_list__phone_home' => '',
                        'isys_cats_person_list__fax' => '',
                        'isys_cats_person_list__academic_degree' => NULL,
                        'isys_cats_person_list__function' => NULL,
                        'isys_cats_person_list__service_designation' => NULL,
                        'isys_cats_person_list__city' => NULL,
                        'isys_cats_person_list__zip_code' => NULL,
                        'isys_cats_person_list__street' => NULL,
                        'isys_cats_person_list__status' => 2,
                        'isys_cats_person_list__property' => 0,
                        'isys_cats_person_list__isys_obj__id' => 7,
                        'isys_cats_person_list__isys_catg_relation_list__id' => NULL,
                        'isys_cats_person_list__pager' => NULL,
                        'isys_cats_person_list__salutation' => NULL,
                        'isys_cats_person_list__last_login' => NULL,
                        'isys_cats_person_list__custom1' => NULL,
                        'isys_cats_person_list__custom2' => NULL,
                        'isys_cats_person_list__custom3' => NULL,
                        'isys_cats_person_list__custom3' => NULL,
                        'isys_cats_person_list__custom4' => NULL,
                        'isys_cats_person_list__custom6' => NULL,
                        'isys_cats_person_list__custom7' => NULL,
                        'isys_cats_person_list__custom8' => NULL,
                        'isys_cats_person_list__disabled_login' =>0,
                        'isys_cats_person_list__unmigrated_password' => 0,
                    ],
                    [
                        'isys_cats_person_list__id' => 5,
                        'isys_cats_person_list__isys_connection__id' => 5,
                        'isys_cats_person_list__isys_ldap__id' => NULL,
                        'isys_cats_person_list__title' => 'archivar',
                        'isys_cats_person_list__ldap_dn' => '',
                        'isys_cats_person_list__description' => '',
                        'isys_cats_person_list__sort' => 5,
                        'isys_cats_person_list__const' => NULL,
                        'isys_cats_person_list__personnel_number' => '',
                        'isys_cats_person_list__user_pass' => 'archivar',
                        'isys_cats_person_list__last_name' => '',
                        'isys_cats_person_list__first_name' => 'archivar',
                        'isys_cats_person_list__department' => '',
                        'isys_cats_person_list__position' => NULL,
                        'isys_cats_person_list__photo' => NULL,
                        'isys_cats_person_list__mail_address' => '',
                        'isys_cats_person_list__phone_company' => '',
                        'isys_cats_person_list__phone_mobile' => '',
                        'isys_cats_person_list__phone_home' => '',
                        'isys_cats_person_list__fax' => '',
                        'isys_cats_person_list__academic_degree' => NULL,
                        'isys_cats_person_list__function' => NULL,
                        'isys_cats_person_list__service_designation' => NULL,
                        'isys_cats_person_list__city' => NULL,
                        'isys_cats_person_list__zip_code' => NULL,
                        'isys_cats_person_list__street' => NULL,
                        'isys_cats_person_list__status' => 2,
                        'isys_cats_person_list__property' => 0,
                        'isys_cats_person_list__isys_obj__id' => 8,
                        'isys_cats_person_list__isys_catg_relation_list__id' => NULL,
                        'isys_cats_person_list__pager' => NULL,
                        'isys_cats_person_list__salutation' => NULL,
                        'isys_cats_person_list__last_login' => NULL,
                        'isys_cats_person_list__custom1' => NULL,
                        'isys_cats_person_list__custom2' => NULL,
                        'isys_cats_person_list__custom3' => NULL,
                        'isys_cats_person_list__custom3' => NULL,
                        'isys_cats_person_list__custom4' => NULL,
                        'isys_cats_person_list__custom6' => NULL,
                        'isys_cats_person_list__custom7' => NULL,
                        'isys_cats_person_list__custom8' => NULL,
                        'isys_cats_person_list__disabled_login' =>0,
                        'isys_cats_person_list__unmigrated_password' => 0,
                    ],
                    [
                        'isys_cats_person_list__id' => 6,
                        'isys_cats_person_list__isys_connection__id' => 6,
                        'isys_cats_person_list__isys_ldap__id' => NULL,
                        'isys_cats_person_list__title' => 'admin',
                        'isys_cats_person_list__ldap_dn' => '',
                        'isys_cats_person_list__description' => '',
                        'isys_cats_person_list__sort' => 5,
                        'isys_cats_person_list__const' => NULL,
                        'isys_cats_person_list__personnel_number' => '',
                        'isys_cats_person_list__user_pass' => 'admin',
                        'isys_cats_person_list__last_name' => '',
                        'isys_cats_person_list__first_name' => 'admin',
                        'isys_cats_person_list__department' => '',
                        'isys_cats_person_list__position' => NULL,
                        'isys_cats_person_list__photo' => NULL,
                        'isys_cats_person_list__mail_address' => '',
                        'isys_cats_person_list__phone_company' => '',
                        'isys_cats_person_list__phone_mobile' => '',
                        'isys_cats_person_list__phone_home' => '',
                        'isys_cats_person_list__fax' => '',
                        'isys_cats_person_list__academic_degree' => NULL,
                        'isys_cats_person_list__function' => NULL,
                        'isys_cats_person_list__service_designation' => NULL,
                        'isys_cats_person_list__city' => NULL,
                        'isys_cats_person_list__zip_code' => NULL,
                        'isys_cats_person_list__street' => NULL,
                        'isys_cats_person_list__status' => 2,
                        'isys_cats_person_list__property' => 0,
                        'isys_cats_person_list__isys_obj__id' => 9,
                        'isys_cats_person_list__isys_catg_relation_list__id' => NULL,
                        'isys_cats_person_list__pager' => 'NOW()',
                        'isys_cats_person_list__salutation' => NULL,
                        'isys_cats_person_list__last_login' => NULL,
                        'isys_cats_person_list__custom1' => NULL,
                        'isys_cats_person_list__custom2' => NULL,
                        'isys_cats_person_list__custom3' => NULL,
                        'isys_cats_person_list__custom3' => NULL,
                        'isys_cats_person_list__custom4' => NULL,
                        'isys_cats_person_list__custom6' => NULL,
                        'isys_cats_person_list__custom7' => NULL,
                        'isys_cats_person_list__custom8' => NULL,
                        'isys_cats_person_list__disabled_login' =>0,
                        'isys_cats_person_list__unmigrated_password' => 0,
                    ],
                    [
                        'isys_cats_person_list__id' => 7,
                        'isys_cats_person_list__isys_connection__id' => NULL,
                        'isys_cats_person_list__isys_ldap__id' => NULL,
                        'isys_cats_person_list__title' => 'systemapi',
                        'isys_cats_person_list__ldap_dn' => '',
                        'isys_cats_person_list__description' => '',
                        'isys_cats_person_list__sort' => 5,
                        'isys_cats_person_list__const' => NULL,
                        'isys_cats_person_list__personnel_number' => '',
                        'isys_cats_person_list__user_pass' => 'systemapi',
                        'isys_cats_person_list__last_name' => 'System',
                        'isys_cats_person_list__first_name' => 'Api',
                        'isys_cats_person_list__department' => '',
                        'isys_cats_person_list__position' => NULL,
                        'isys_cats_person_list__photo' => NULL,
                        'isys_cats_person_list__mail_address' => '',
                        'isys_cats_person_list__phone_company' => '',
                        'isys_cats_person_list__phone_mobile' => '',
                        'isys_cats_person_list__phone_home' => '',
                        'isys_cats_person_list__fax' => '',
                        'isys_cats_person_list__academic_degree' => NULL,
                        'isys_cats_person_list__function' => NULL,
                        'isys_cats_person_list__service_designation' => NULL,
                        'isys_cats_person_list__city' => NULL,
                        'isys_cats_person_list__zip_code' => NULL,
                        'isys_cats_person_list__street' => NULL,
                        'isys_cats_person_list__status' => 2,
                        'isys_cats_person_list__property' => 0,
                        'isys_cats_person_list__isys_obj__id' => 22,
                        'isys_cats_person_list__isys_catg_relation_list__id' => NULL,
                        'isys_cats_person_list__pager' => NULL,
                        'isys_cats_person_list__salutation' => NULL,
                        'isys_cats_person_list__last_login' => NULL,
                        'isys_cats_person_list__custom1' => NULL,
                        'isys_cats_person_list__custom2' => NULL,
                        'isys_cats_person_list__custom3' => NULL,
                        'isys_cats_person_list__custom3' => NULL,
                        'isys_cats_person_list__custom4' => NULL,
                        'isys_cats_person_list__custom6' => NULL,
                        'isys_cats_person_list__custom7' => NULL,
                        'isys_cats_person_list__custom8' => NULL,
                        'isys_cats_person_list__disabled_login' =>0,
                        'isys_cats_person_list__unmigrated_password' => 0,
                    ]
            ];
    }

    /**
     * Generate value part of the query for persons list
     *
     * @param $data
     *
     * @return string
     *
     * @author      Denis Koroliov <dkorolov@i-doit.com>
     */
    public static function movePersonsListDataToQueryValues ($data) {
        $valuesString = '';
        foreach ($data as $person) {
            $valuesString .= self::generateValuesStringForQuery($person).',';
        }
        return rtrim($valuesString,',');
    }

    /**
     * Fill the persons list table in DB
     *
     * @param $db_host
     * @param $db_username
     * @param $db_password
     * @param $db_database_name
     * @param $db_port
     *
     * @return array
     *
     * @author      Denis Koroliov <dkorolov@i-doit.com>
     */
    public static function insertPersons($db_host, $db_username, $db_password, $db_database_name, $db_port) {
        try {
            $dbLink = new mysqli(
                $db_host,
                $db_username,
                $db_password,
                $db_database_name,
                $db_port
            );
            $dbLink->query("SET sql_mode=''");
            $query = self::generatePersonsListDataQuery();
            $dbLink->query($query);
            $dbLink->close();
        } catch (Exception  $e) {
            return [
                'result' => false,
                'message' => $e->getMessage()
            ];
        }
        return [
            'result' => true,
            'message' => 'Success'
        ];


    }

    /**
     * Genereate SQL query for persons list
     *
     * @return string
     *
     * @author      Denis Koroliov <dkorolov@i-doit.com>
     */
    public static function generatePersonsListDataQuery() {
        $persons = self::getDataForPersonsList();
        $valuesToInsert = self::movePersonsListDataToQueryValues($persons);

        $query = "INSERT INTO isys_cats_person_list VALUES " . $valuesToInsert .";";

        return $query;
    }

    /**
     * Generate values string from data to insert persons
     *
     * @param $data
     *
     * @return string
     */
    public static function generateValuesStringForQuery($data) {
        $password = isys_helper_crypt::encryptPassword($data['isys_cats_person_list__user_pass']);

        return sprintf('(%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)',
            $data['isys_cats_person_list__id'],
            $data['isys_cats_person_list__isys_connection__id'] ? $data['isys_cats_person_list__isys_connection__id'] : "NULL",
            $data['isys_cats_person_list__isys_ldap__id']?$data['isys_cats_person_list__isys_ldap__id']:"NULL",
            $data['isys_cats_person_list__title'] ? "'" . $data['isys_cats_person_list__title'] . "'":"''",
            $data['isys_cats_person_list__ldap_dn'] ? "'" . $data['isys_cats_person_list__ldap_dn'] . "'":"''",
            $data['isys_cats_person_list__description'] ? "'" . $data['isys_cats_person_list__description'] . "'":"''",
            $data['isys_cats_person_list__sort']?$data['isys_cats_person_list__sort']:"NULL",
            $data['isys_cats_person_list__const']?$data['isys_cats_person_list__const']:"NULL",
            $data['isys_cats_person_list__personnel_number'] ? "'" . $data['isys_cats_person_list__personnel_number'] . "'":"''",
            "'" . $password . "'",
            $data['isys_cats_person_list__last_name'] ? "'" . $data['isys_cats_person_list__last_name'] . "'":"''",
            $data['isys_cats_person_list__first_name'] ? "'" . $data['isys_cats_person_list__first_name'] . "'":"''",
            $data['isys_cats_person_list__department'] ? "'" . $data['isys_cats_person_list__department'] . "'":"''",
            $data['isys_cats_person_list__position'] ? "'" . $data['isys_cats_person_list__position'] . "'":"NULL",
            $data['isys_cats_person_list__photo']?$data['isys_cats_person_list__photo']:"NULL",
            $data['isys_cats_person_list__mail_address'] ? "'" . $data['isys_cats_person_list__mail_address'] . "'":"''",
            $data['isys_cats_person_list__phone_company']?$data['isys_cats_person_list__phone_company']:"''",
            $data['isys_cats_person_list__phone_mobile']?$data['isys_cats_person_list__phone_mobile']:"''",
            $data['isys_cats_person_list__phone_home']?$data['isys_cats_person_list__phone_home']:"''",
            $data['isys_cats_person_list__fax']?$data['isys_cats_person_list__fax']:"''",
            $data['isys_cats_person_list__academic_degree']?$data['isys_cats_person_list__academic_degree']:"NULL",
            $data['isys_cats_person_list__function']?$data['isys_cats_person_list__function']:"NULL",
            $data['isys_cats_person_list__service_designation']?$data['isys_cats_person_list__service_designation']:"NULL",
            $data['isys_cats_person_list__city']?$data['isys_cats_person_list__city']:"NULL",
            $data['isys_cats_person_list__zip_code']?$data['isys_cats_person_list__zip_code']:"NULL",
            $data['isys_cats_person_list__street']?$data['isys_cats_person_list__zip_code']:"NULL",
            $data['isys_cats_person_list__status'],
            $data['isys_cats_person_list__property'],
            $data['isys_cats_person_list__isys_obj__id'],
            $data['isys_cats_person_list__isys_catg_relation_list__id']?$data['isys_cats_person_list__isys_catg_relation_list__id']:"NULL",
            $data['isys_cats_person_list__pager']?$data['isys_cats_person_list__pager']:"NULL",
            $data['isys_cats_person_list__salutation']?$data['isys_cats_person_list__salutation']:"NULL",
            $data['isys_cats_person_list__last_login']?$data['isys_cats_person_list__last_login']:"NULL",
            $data['isys_cats_person_list__custom1']?$data['isys_cats_person_list__custom1']:"NULL",
            $data['isys_cats_person_list__custom2']?$data['isys_cats_person_list__custom2']:"NULL",
            $data['isys_cats_person_list__custom3']?$data['isys_cats_person_list__custom3']:"NULL",
            $data['isys_cats_person_list__custom4']?$data['isys_cats_person_list__custom4']:"NULL",
            $data['isys_cats_person_list__custom5']?$data['isys_cats_person_list__custom5']:"NULL",
            $data['isys_cats_person_list__custom6']?$data['isys_cats_person_list__custom6']:"NULL",
            $data['isys_cats_person_list__custom7']?$data['isys_cats_person_list__custom7']:"NULL",
            $data['isys_cats_person_list__custom8']?$data['isys_cats_person_list__custom8']:"NULL",
            $data['isys_cats_person_list__disabled_login'],
            $data['isys_cats_person_list__unmigrated_password'],
        );
    }
}