<?php
/**
 * i-doit - Documentation and CMDB solution for IT environments
 *
 * This file is part of the i-doit framework. Modify at your own risk.
 *
 * Please visit http://www.i-doit.com/license for a full copyright and license information.
 *
 * @version     1.10
 * @package     i-doit
 * @author      synetics GmbH
 * @copyright   synetics GmbH
 * @url         http://www.i-doit.com
 * @license     http://www.i-doit.com/license
 */

/**
 * i-doit
 *
 * DAO: specific category for persons.
 *
 * @package     i-doit
 * @subpackage  CMDB_Categories
 * @author      Dennis Stuecken <dstuecken@i-doit.de>
 * @copyright   synetics GmbH
 * @license     http://www.i-doit.com/license
 */
abstract class isys_cmdb_dao_category_s_person extends isys_cmdb_dao_category_specific
{
    /**
     * Category's name. Will be used for the identifier, constant, main table, and many more.
     *
     * @var  string
     */
    protected $m_category = 'person';
    /**
     * @var string
     */
    protected $m_connected_object_id_field = 'isys_connection__isys_obj__id';
    /**
     * Field for the object id
     *
     * @var  string
     */
    protected $m_object_id_field = 'isys_cats_person_list__isys_obj__id';

    /**
     * @param int    $p_user_id
     * @param int    $p_group_id
     * @param string $p_changetyp
     */
    public static function slotBeforeUserGroupChanged($p_user_id, $p_group_id, $p_changetype)
    {
        // delete auth cache of the person
        isys_caching::factory('auth-' . $p_user_id)
            ->clear();

        /* Delete Object cache for this person */
        isys_auth_cmdb_objects::invalidate_cache($p_user_id);
    } // function

    /**
     * Retrieve custom properties
     *
     * @return array
     */
    public function custom_properties()
    {
        return [
            'custom_1' => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO     => [
                        C__PROPERTY__INFO__TITLE       => 'Custom 1',
                        C__PROPERTY__INFO__DESCRIPTION => 'Custom property 1'
                    ],
                    C__PROPERTY__DATA     => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__custom1'
                    ],
                    C__PROPERTY__UI       => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__CUSTOM1'
                    ],
                    C__PROPERTY__PROVIDES => [
                        C__PROPERTY__PROVIDES__LIST => false
                    ]
                ]
            ),
            'custom_2' => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO     => [
                        C__PROPERTY__INFO__TITLE       => 'Custom 2',
                        C__PROPERTY__INFO__DESCRIPTION => 'Custom property 2'
                    ],
                    C__PROPERTY__DATA     => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__custom2'
                    ],
                    C__PROPERTY__UI       => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__CUSTOM2'
                    ],
                    C__PROPERTY__PROVIDES => [
                        C__PROPERTY__PROVIDES__LIST => false
                    ]
                ]
            ),
            'custom_3' => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO     => [
                        C__PROPERTY__INFO__TITLE       => 'Custom 3',
                        C__PROPERTY__INFO__DESCRIPTION => 'Custom property 3'
                    ],
                    C__PROPERTY__DATA     => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__custom3'
                    ],
                    C__PROPERTY__UI       => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__CUSTOM3'
                    ],
                    C__PROPERTY__PROVIDES => [
                        C__PROPERTY__PROVIDES__LIST => false
                    ]
                ]
            ),
            'custom_4' => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO     => [
                        C__PROPERTY__INFO__TITLE       => 'Custom 4',
                        C__PROPERTY__INFO__DESCRIPTION => 'Custom property 4'
                    ],
                    C__PROPERTY__DATA     => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__custom4'
                    ],
                    C__PROPERTY__UI       => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__CUSTOM4'
                    ],
                    C__PROPERTY__PROVIDES => [
                        C__PROPERTY__PROVIDES__LIST => false
                    ]
                ]
            ),
            'custom_5' => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO     => [
                        C__PROPERTY__INFO__TITLE       => 'Custom 5',
                        C__PROPERTY__INFO__DESCRIPTION => 'Custom property 5'
                    ],
                    C__PROPERTY__DATA     => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__custom5'
                    ],
                    C__PROPERTY__UI       => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__CUSTOM5'
                    ],
                    C__PROPERTY__PROVIDES => [
                        C__PROPERTY__PROVIDES__LIST => false
                    ]
                ]
            ),
            'custom_6' => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO     => [
                        C__PROPERTY__INFO__TITLE       => 'Custom 6',
                        C__PROPERTY__INFO__DESCRIPTION => 'Custom property 6'
                    ],
                    C__PROPERTY__DATA     => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__custom6'
                    ],
                    C__PROPERTY__UI       => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__CUSTOM6'
                    ],
                    C__PROPERTY__PROVIDES => [
                        C__PROPERTY__PROVIDES__LIST => false
                    ]
                ]
            ),
            'custom_7' => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO     => [
                        C__PROPERTY__INFO__TITLE       => 'Custom 7',
                        C__PROPERTY__INFO__DESCRIPTION => 'Custom property 7'
                    ],
                    C__PROPERTY__DATA     => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__custom7'
                    ],
                    C__PROPERTY__UI       => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__CUSTOM7'
                    ],
                    C__PROPERTY__PROVIDES => [
                        C__PROPERTY__PROVIDES__LIST => false
                    ]
                ]
            ),
            'custom_8' => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO     => [
                        C__PROPERTY__INFO__TITLE       => 'Custom 8',
                        C__PROPERTY__INFO__DESCRIPTION => 'Custom property 8'
                    ],
                    C__PROPERTY__DATA     => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__custom8'
                    ],
                    C__PROPERTY__UI       => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__CUSTOM8'
                    ],
                    C__PROPERTY__PROVIDES => [
                        C__PROPERTY__PROVIDES__LIST => false
                    ]
                ]
            ),
        ];
    } // function

    /**
     * Callback method for property assigned_variant.
     *
     * @return  array
     * @author  Leonard Fischer <lfischer@i-doit.com>
     */
    public function callback_property_salutation()
    {
        return [
            'm' => _L('LC__UNIVERSAL__MISTER'),
            'f' => _L('LC__UNIVERSAL__MISSES')
        ];
    } // function

    /**
     * Method for creating a formatted user-name, for example "Herr Dr. Leonard Fischer".
     *
     * @param   integer  $p_object_id
     *
     * @return  string
     * @throws  isys_exception_database
     */
    public function get_formatted_name ($p_object_id)
    {
        $l_sql = 'SELECT isys_cats_person_list__salutation AS salutation,
            isys_cats_person_list__academic_degree AS degree,
            isys_cats_person_list__first_name AS firstname,
            isys_cats_person_list__last_name AS lastname
            FROM isys_cats_person_list
            WHERE isys_cats_person_list__isys_obj__id = ' . $this->convert_sql_id($p_object_id) . ';';

        $l_row = $this->retrieve($l_sql)->get_row();

        // We use this array stuff so that the name will not look like " dr.  fischer" (one space between every item).
        return implode(' ', array_filter([$this->callback_property_salutation()[$l_row['salutation']], $l_row['degree'], $l_row['firstname'], $l_row['lastname']]));
    } // function

    /**
     * Method for creating multiple formatted user-name, for example "Herr Dr. Leonard Fischer, Frau Mustermann".
     *
     * @param   array  $p_object_ids
     *
     * @return  string
     * @throws  isys_exception_database
     */
    public function get_formatted_names (array $p_object_ids)
    {
        $l_names = [];
        $l_sql = 'SELECT isys_cats_person_list__salutation AS salutation,
            isys_cats_person_list__academic_degree AS degree,
            isys_cats_person_list__first_name AS firstname,
            isys_cats_person_list__last_name AS lastname
            FROM isys_cats_person_list
            WHERE isys_cats_person_list__isys_obj__id ' . $this->prepare_in_condition($p_object_ids) . ';';

        $l_res = $this->retrieve($l_sql);

        while ($l_row = $l_res->get_row())
        {
            $l_names[] = implode(' ', array_filter([$this->callback_property_salutation()[$l_row['salutation']], $l_row['degree'], $l_row['firstname'], $l_row['lastname']]));
        } // while

        // We use this array stuff so that the name will not look like " dr.  fischer" (one space between every item).
        return implode(', ', $l_names);
    } // function

    /**
     * Return Category Data.
     *
     * @param   integer $p_cats_list_id
     * @param   mixed   $p_obj_id
     * @param   string  $p_condition
     * @param   mixed   $p_filter
     * @param   integer $p_status
     *
     * @return  isys_component_dao_result
     */
    public function get_data($p_cats_list_id = null, $p_obj_id = null, $p_condition = "", $p_filter = null, $p_status = null)
    {
        $l_sql = 'SELECT *, isys_catg_mail_addresses_list__title AS isys_cats_person_list__mail_address FROM isys_cats_person_list
			INNER JOIN isys_obj ON isys_cats_person_list__isys_obj__id = isys_obj__id
			LEFT JOIN isys_connection ON isys_connection__id = isys_cats_person_list__isys_connection__id
			LEFT JOIN isys_cats_organization_list ON isys_cats_organization_list__isys_obj__id = isys_connection__isys_obj__id
			LEFT JOIN isys_catg_mail_addresses_list ON isys_catg_mail_addresses_list__isys_obj__id = isys_cats_person_list__isys_obj__id AND isys_catg_mail_addresses_list__primary = 1
			WHERE TRUE ' . $p_condition . $this->prepare_filter($p_filter);

        if ($p_cats_list_id !== null)
        {
            $l_sql .= ' AND isys_cats_person_list__id = ' . $this->convert_sql_id($p_cats_list_id);
        } // if

        if ($p_obj_id !== null)
        {
            $l_sql .= $this->get_object_condition($p_obj_id);
        } // if

        if ($p_status !== null)
        {
            $l_sql .= ' AND isys_obj__status = ' . $this->convert_sql_int($p_status);
        } // if

        return $this->retrieve($l_sql);
    } // function

    /**
     * Method for returning the properties.
     * Property mail won´t be imported/exported because the category E-Mail handles the import/export.
     *
     * @return  array
     */
    protected function properties()
    {
        return [
            'title'               => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO     => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CMDB__LOGBOOK__TITLE',
                        C__PROPERTY__INFO__DESCRIPTION => 'Title'
                    ],
                    C__PROPERTY__DATA     => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__title'
                    ],
                    C__PROPERTY__UI       => [
                        C__PROPERTY__UI__ID => 'C__CMDB__CATS__PERSON_MASTER__TITLE'
                    ],
                    C__PROPERTY__PROVIDES => [
                        C__PROPERTY__PROVIDES__MULTIEDIT => false
                    ]
                ]
            ),
            'salutation'          => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_SALUTATION',
                        C__PROPERTY__INFO__DESCRIPTION => 'Salutation'
                    ],
                    C__PROPERTY__DATA => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__salutation',
                        C__PROPERTY__DATA__SELECT => idoit\Module\Report\SqlQuery\Structure\SelectSubSelect::factory(
                        	'(CASE WHEN isys_cats_person_list__salutation = \'m\' THEN ' . $this->convert_sql_text('LC__UNIVERSAL__MISTER') . '
                                    WHEN isys_cats_person_list__salutation = \'f\' THEN ' . $this->convert_sql_text('LC__UNIVERSAL__MISSES') . '
                                    ELSE ' . $this->convert_sql_text(isys_tenantsettings::get('gui.empty_value')) . ' END)',
                        	'isys_cats_person_list'
                        ),
                        C__PROPERTY__DATA__JOIN => [
                            idoit\Module\Report\SqlQuery\Structure\SelectJoin::factory(
                                'isys_cats_person_list',
                                'LEFT',
                                'isys_cats_person_list__isys_obj__id',
                                'isys_obj__id'
                            )
                        ]
                    ],
                    C__PROPERTY__UI   => [
                        C__PROPERTY__UI__ID     => 'C__CONTACT__PERSON_SALUTATION',
                        C__PROPERTY__UI__TYPE   => C__PROPERTY__UI__TYPE__DIALOG,
                        C__PROPERTY__UI__PARAMS => [
                            'p_strPopupType' => 'dialog',
                            'p_arData'       => new isys_callback(
                                [
                                    'isys_cmdb_dao_category_s_person_master',
                                    'callback_property_salutation'
                                ]
                            ),
                        ]
                    ]
                ]
            ),
            'first_name'          => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_FIRST_NAME',
                        C__PROPERTY__INFO__DESCRIPTION => 'First name'
                    ],
                    C__PROPERTY__DATA => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__first_name'
                    ],
                    C__PROPERTY__UI   => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__PERSON_FIRST_NAME'
                    ]
                ]
            ),
            'last_name'           => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_LAST_NAME',
                        C__PROPERTY__INFO__DESCRIPTION => 'Last name'
                    ],
                    C__PROPERTY__DATA => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__last_name'
                    ],
                    C__PROPERTY__UI   => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__PERSON_LAST_NAME'
                    ]
                ]
            ),
            'academic_degree'     => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_ACADEMIC_DEGREE',
                        C__PROPERTY__INFO__DESCRIPTION => 'Academic degree'
                    ],
                    C__PROPERTY__DATA => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__academic_degree'
                    ],
                    C__PROPERTY__UI   => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__PERSON_ACADEMIC_DEGREE'
                    ]
                ]
            ),
            'function'            => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_FUNKTION',
                        C__PROPERTY__INFO__DESCRIPTION => 'Function'
                    ],
                    C__PROPERTY__DATA => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__function'
                    ],
                    C__PROPERTY__UI   => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__PERSON_FUNKTION'
                    ]
                ]
            ),
            'service_designation' => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_SERVICE_DESIGNATION',
                        C__PROPERTY__INFO__DESCRIPTION => 'Service designation'
                    ],
                    C__PROPERTY__DATA => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__service_designation'
                    ],
                    C__PROPERTY__UI   => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__PERSON_SERVICE_DESIGNATION'
                    ]
                ]
            ),
            'street'              => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_STEET',
                        C__PROPERTY__INFO__DESCRIPTION => 'Street'
                    ],
                    C__PROPERTY__DATA => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__street'
                    ],
                    C__PROPERTY__UI   => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__PERSON_STREET'
                    ]
                ]
            ),
            'city'                => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_CITY',
                        C__PROPERTY__INFO__DESCRIPTION => 'City'
                    ],
                    C__PROPERTY__DATA => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__city'
                    ],
                    C__PROPERTY__UI   => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__PERSON_CITY'
                    ]
                ]
            ),
            'zip_code'            => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_ZIP_CODE',
                        C__PROPERTY__INFO__DESCRIPTION => 'Zip-Code'
                    ],
                    C__PROPERTY__DATA => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__zip_code'
                    ],
                    C__PROPERTY__UI   => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__PERSON_ZIP_CODE'
                    ]
                ]
            ),
            'mail'                => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO     => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_MAIL_ADDRESS',
                        C__PROPERTY__INFO__DESCRIPTION => 'E-mail address'
                    ],
                    C__PROPERTY__DATA     => [
                        C__PROPERTY__DATA__FIELD => 'isys_catg_mail_addresses_list__title',
                        C__PROPERTY__DATA__SELECT => idoit\Module\Report\SqlQuery\Structure\SelectSubSelect::factory(
                        	'SELECT isys_catg_mail_addresses_list__title  FROM isys_catg_mail_addresses_list',
                        	'isys_catg_mail_addresses_list',
                        	'isys_catg_mail_addresses_list__id',
                        	'isys_catg_mail_addresses_list__isys_obj__id',
                        	'',
                        	'',
                        	idoit\Module\Report\SqlQuery\Structure\SelectCondition::factory([' isys_catg_mail_addresses_list__primary = 1'])
                        ),
                        C__PROPERTY__DATA__JOIN => [
                            idoit\Module\Report\SqlQuery\Structure\SelectJoin::factory(
                                'isys_catg_mail_addresses_list',
                                'LEFT',
                                'isys_catg_mail_addresses_list__isys_obj__id',
                                'isys_obj__id'
                            )
                        ]
                    ],
                    C__PROPERTY__UI       => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__PERSON_MAIL_ADDRESS'
                    ],
                    C__PROPERTY__PROVIDES => [
                        C__PROPERTY__PROVIDES__SEARCH => false,
                        C__PROPERTY__PROVIDES__EXPORT => false,
                        // C__PROPERTY__PROVIDES__IMPORT => false
                    ]
                ]
            ),
            'phone_company'       => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_TELEPHONE_COMPANY',
                        C__PROPERTY__INFO__DESCRIPTION => 'Telephone company'
                    ],
                    C__PROPERTY__DATA => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__phone_company'
                    ],
                    C__PROPERTY__UI   => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__PERSON_PHONE_COMPANY'
                    ]
                ]
            ),
            'phone_home'          => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_TELEPHONE_HOME',
                        C__PROPERTY__INFO__DESCRIPTION => 'Telephone home'
                    ],
                    C__PROPERTY__DATA => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__phone_home'
                    ],
                    C__PROPERTY__UI   => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__PERSON_PHONE_HOME'
                    ]
                ]
            ),
            'phone_mobile'        => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_TELEPHONE_MOBILE',
                        C__PROPERTY__INFO__DESCRIPTION => 'Cellphone'
                    ],
                    C__PROPERTY__DATA => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__phone_mobile'
                    ],
                    C__PROPERTY__UI   => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__PERSON_PHONE_MOBILE'
                    ]
                ]
            ),
            'fax'                 => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_FAX',
                        C__PROPERTY__INFO__DESCRIPTION => 'Fax'
                    ],
                    C__PROPERTY__DATA => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__fax'
                    ],
                    C__PROPERTY__UI   => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__PERSON_FAX'
                    ]
                ]
            ),
            'pager'               => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_PAGER',
                        C__PROPERTY__INFO__DESCRIPTION => 'Pager'
                    ],
                    C__PROPERTY__DATA => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__pager'
                    ],
                    C__PROPERTY__UI   => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__PERSON_PAGER'
                    ]
                ]
            ),
            'personnel_number'    => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_PERSONNEL_NUMBER',
                        C__PROPERTY__INFO__DESCRIPTION => 'Personnel number'
                    ],
                    C__PROPERTY__DATA => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__personnel_number'
                    ],
                    C__PROPERTY__UI   => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__PERSON_PERSONNEL_NUMBER'
                    ]
                ]
            ),
            'department'          => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_DEPARTMENT',
                        C__PROPERTY__INFO__DESCRIPTION => 'Department'
                    ],
                    C__PROPERTY__DATA => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__department'
                    ],
                    C__PROPERTY__UI   => [
                        C__PROPERTY__UI__ID => 'C__CONTACT__PERSON_DEPARTMENT'
                    ]
                ]
            ),
            'organization'        => array_replace_recursive(
                isys_cmdb_dao_category_pattern::object_browser(),
                [
                    C__PROPERTY__INFO     => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CONTACT__PERSON_ASSIGNED_ORGANISATION',
                        C__PROPERTY__INFO__DESCRIPTION => 'Organisation'
                    ],
                    C__PROPERTY__DATA     => [
                        C__PROPERTY__DATA__FIELD            => 'isys_cats_person_list__isys_connection__id',
                        C__PROPERTY__DATA__RELATION_TYPE    => C__RELATION_TYPE__ORGANIZATION,
                        C__PROPERTY__DATA__RELATION_HANDLER => new isys_callback(
                            [
                                'isys_cmdb_dao_category_s_person',
                                'callback_property_relation_handler'
                            ], [
                                'isys_cmdb_dao_category_s_person',
                                true
                            ]
                        ),
                        C__PROPERTY__DATA__REFERENCES       => [
                            'isys_connection',
                            'isys_connection__id'
                        ],
                        C__PROPERTY__DATA__SELECT => idoit\Module\Report\SqlQuery\Structure\SelectSubSelect::factory(
                        	'SELECT CONCAT(isys_obj__title, \' {\', isys_obj__id, \'}\')
                                FROM isys_cats_person_list
                                INNER JOIN isys_connection ON isys_connection__id = isys_cats_person_list__isys_connection__id
                                INNER JOIN isys_obj ON isys_obj__id = isys_connection__isys_obj__id',
                        	'isys_cats_person_list',
                        	'isys_cats_person_list__id',
                        	'isys_cats_person_list__isys_obj__id',
                        	'',
                        	'',
                        	null,
                        	null,
                            '',
                            1
                        ),
                        C__PROPERTY__DATA__JOIN => [
                            idoit\Module\Report\SqlQuery\Structure\SelectJoin::factory(
                                'isys_cats_person_list',
                                'LEFT',
                                'isys_cats_person_list__isys_obj__id',
                                'isys_obj__id'
                            ),
                            idoit\Module\Report\SqlQuery\Structure\SelectJoin::factory(
                                'isys_connection',
                                'LEFT',
                                'isys_connection__id',
                                'isys_cats_person_list__isys_connection__id'
                            )
                        ]
                    ],
                    C__PROPERTY__UI       => [
                        C__PROPERTY__UI__ID     => 'C__CONTACT__PERSON_ASSIGNED_ORGANISATION',
                        C__PROPERTY__UI__PARAMS => [
                            'title'     => 'LC__POPUP__BROWSER__ORGANISATION',
                            'catFilter' => 'C__CATS__ORGANIZATION;C__CATS__ORGANIZATION_MASTER_DATA;C__CATS__ORGANIZATION_PERSONS'
                        ]
                    ],
                    C__PROPERTY__FORMAT   => [
                        C__PROPERTY__FORMAT__CALLBACK => [
                            'isys_export_helper',
                            'connection'
                        ]
                    ],
                    C__PROPERTY__PROVIDES => [
                        C__PROPERTY__PROVIDES__SEARCH => false
                    ]
                ]
            ),
            'ldap_id'             => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO     => [
                        C__PROPERTY__INFO__TITLE       => 'LC__UNIVERSAL__ID',
                        C__PROPERTY__INFO__DESCRIPTION => 'ID'
                    ],
                    C__PROPERTY__DATA     => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__isys_ldap__id'
                    ],
                    C__PROPERTY__UI       => [
                        C__PROPERTY__UI__ID => ''
                    ],
                    C__PROPERTY__PROVIDES => [
                        C__PROPERTY__PROVIDES__SEARCH    => false,
                        C__PROPERTY__PROVIDES__MULTIEDIT => false,
                        C__PROPERTY__PROVIDES__IMPORT    => false
                    ],
                    C__PROPERTY__FORMAT   => [
                        C__PROPERTY__FORMAT__CALLBACK => [
                            'isys_export_helper',
                            'person_property_ldap_id'
                        ]
                    ]
                ]
            ),
            'ldap_dn'             => array_replace_recursive(
                isys_cmdb_dao_category_pattern::text(),
                [
                    C__PROPERTY__INFO     => [
                        C__PROPERTY__INFO__TITLE       => 'DN',
                        C__PROPERTY__INFO__DESCRIPTION => 'DN'
                    ],
                    C__PROPERTY__DATA     => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__ldap_dn'
                    ],
                    C__PROPERTY__UI       => [
                        C__PROPERTY__UI__ID => ''
                    ],
                    C__PROPERTY__PROVIDES => [
                        C__PROPERTY__PROVIDES__SEARCH    => false,
                        C__PROPERTY__PROVIDES__MULTIEDIT => false,
                        C__PROPERTY__PROVIDES__IMPORT    => false
                    ]
                ]
            ),
            'description'         => array_replace_recursive(
                isys_cmdb_dao_category_pattern::commentary(),
                [
                    C__PROPERTY__INFO => [
                        C__PROPERTY__INFO__TITLE       => 'LC__CMDB__LOGBOOK__DESCRIPTION',
                        C__PROPERTY__INFO__DESCRIPTION => 'Description'
                    ],
                    C__PROPERTY__DATA => [
                        C__PROPERTY__DATA__FIELD => 'isys_cats_person_list__description'
                    ],
                    C__PROPERTY__UI   => [
                        C__PROPERTY__UI__ID => 'C__CMDB__CAT__COMMENTARY_' . C__CMDB__CATEGORY__TYPE_SPECIFIC . C__CATS__PERSON
                    ]
                ]
            ),
        ];
    } // function

    /**
     * Synchronizes properties from an import with the database.
     *
     * @param   array   $p_category_data Values of category data to be saved.
     * @param   integer $p_object_id     Current object identifier (from database)
     * @param   integer $p_status        Decision whether category data should be created or just updated.
     *
     * @return  mixed  Returns category data identifier (int) on success, true (bool) if nothing had to be done, otherwise false.
     */
    public function sync($p_category_data, $p_object_id, $p_status = 1 /* isys_import_handler_cmdb::C__CREATE */)
    {
        return true;
    } // function

    /**
     *
     * @param   integer $p_object_id
     *
     * @return  string
     */
    public function get_username_by_id_as_string($p_object_id)
    {
        $l_data = $this->get_data(null, $p_object_id);

        if (count($l_data))
        {
            return $l_data->get_row_value('isys_cats_person_list__title');
        } // if

        return '';
    } // funcion

    /**
     * @param      $p_id
     * @param      $p_username
     * @param      $p_pass
     * @param      $p_description
     * @param int  $p_status
     * @param bool $p_generate_md5
     *
     * @return bool
     * @throws isys_exception_dao
     */
    public function save_login($p_id, $p_username, $p_pass, $p_description, $p_status = C__RECORD_STATUS__NORMAL, $p_generate_md5 = true)
    {
        $l_sql = "UPDATE isys_cats_person_list SET
			isys_cats_person_list__title = " . $this->convert_sql_text($p_username) . ", ";

        if (!empty($p_pass))
        {
            $l_sql .= "isys_cats_person_list__user_pass = " . $this->convert_sql_text($p_generate_md5 ? md5($p_pass) : $p_pass) . ", ";
        } // if

        $l_sql .= 'isys_cats_person_list__description = ' . $this->convert_sql_text($p_description) . ',
			isys_cats_person_list__status = ' . $this->convert_sql_id($p_status) . '
			WHERE isys_cats_person_list__id = ' . $this->convert_sql_id($p_id) . ';';

        return ($this->update($l_sql) && $this->apply_update());
    } // function

    /**
     * Method for detaching a user from a group.
     *
     * @param   integer $p_user_id
     * @param   integer $p_group_id
     * @param   string  $p_condition
     *
     * @return  boolean
     */
    public function detach_groups($p_user_id, $p_group_id = null, $p_condition = null, $p_user_created = false)
    {
        // Need to remove the relation object first
        $l_sql = 'SELECT isys_person_2_group__id, isys_person_2_group__isys_catg_relation_list__id FROM isys_person_2_group WHERE
            isys_person_2_group__isys_obj__id__person = ' . $this->convert_sql_id($p_user_id);

        if ($p_group_id)
        {
            $l_sql .= " AND isys_person_2_group__isys_obj__id__group = " . $this->convert_sql_id($p_group_id);
        } // if

        if ($p_condition)
        {
            $l_sql .= $p_condition;
        } // if
        $l_res = $this->retrieve($l_sql);

        // Only call signal if user already existed it´s not necessary for users which were newly created
        if(!$p_user_created)
        {
            isys_component_signalcollection::get_instance()
                ->emit('mod.cmdb.beforeUserGroupChanged', $p_user_id, $p_group_id, 'detach-person');
        } // if

        if ($l_res->num_rows() > 0)
        {
            /**
             * @var $l_dao_rel isys_cmdb_dao_category_g_relation
             */
            $l_dao_rel    = isys_cmdb_dao_category_g_relation::instance($this->get_database_component());
            $l_delete_arr = [];
            while ($l_row = $l_res->get_row())
            {
                $l_delete_arr[] = $l_row['isys_person_2_group__id'];
                if ($l_row['isys_person_2_group__isys_catg_relation_list__id'])
                {
                    $l_dao_rel->delete_relation($l_row['isys_person_2_group__isys_catg_relation_list__id']);
                } // if
            } // while

            if (count($l_delete_arr))
            {
                $l_delete = 'DELETE FROM isys_person_2_group WHERE isys_person_2_group__id IN (' . implode(',', $l_delete_arr) . ')';
                $this->update($l_delete);
            } // if

            return $this->apply_update();
        } // if
        return false;
    } // function

    /**
     * Attaches user $p_user_id (objid) to group $p_group_id (objid).
     *
     * @param   integer $p_user_id
     * @param   integer $p_group_id
     * @param   string  $p_ldap
     *
     * @return  boolean
     */
    public function attach_group($p_user_id, $p_group_id, $p_ldap = '0')
    {
        if ($p_user_id > 0 && $p_group_id > 0)
        {
            $l_sql = 'INSERT INTO isys_person_2_group SET
                isys_person_2_group__isys_obj__id__person = ' . $this->convert_sql_id($p_user_id) . ',
                isys_person_2_group__isys_obj__id__group = ' . $this->convert_sql_id($p_group_id) . ',
                isys_person_2_group__ldap = ' . $this->convert_sql_int($p_ldap) . ';';

            if ($this->update($l_sql))
            {
                try
                {
                    // Add relation see ID-2284
                    isys_cmdb_dao_category_g_relation::instance($this->get_database_component())
                        ->handle_relation($this->get_last_insert_id(), "isys_person_2_group", C__RELATION_TYPE__PERSON_ASSIGNED_GROUPS, null, $p_group_id, $p_user_id);
                }
                catch (Exception $e)
                {
                    // Catching Error:
                    // CMDB Error: Error: Your relation type for table 'isys_person_2_group' is empty. The constant cache is maybe not available here. [] []
                }

                isys_component_signalcollection::get_instance()
                    ->emit('mod.cmdb.beforeUserGroupChanged', $p_user_id, $p_group_id, 'attach-person');

                return $this->apply_update();
            } // if
        } // if

        return false;
    } // function

    /**
     * Get person id by username.
     *
     * @param   string $p_username
     *
     * @return  integer
     */
    public function get_person_id_by_username($p_username, $p_status = null)
    {
        $l_sql = 'SELECT isys_cats_person_list__isys_obj__id FROM isys_cats_person_list
			INNER JOIN isys_obj ON isys_obj__id = isys_cats_person_list__isys_obj__id
			WHERE isys_cats_person_list__title = ' . $this->convert_sql_text($p_username);
        if ($p_status !== null)
        {
            $l_sql .= ' AND isys_obj__status = ' . $this->convert_sql_int($p_status);
        } // if
        return $this->retrieve($l_sql . ';')
            ->get_row_value('isys_cats_person_list__isys_obj__id');
    } // function

    /**
     * Retrieves a person by username.
     *
     * @param  integer $p_username
     * @param  integer $p_status
     *
     * @return isys_component_dao_result
     */
    public function get_person_by_username($p_username, $p_status = null)
    {
        $l_sql = 'SELECT *, isys_catg_mail_addresses_list__title AS isys_cats_person_list__mail_address
			FROM isys_cats_person_list
			INNER JOIN isys_obj ON isys_obj__id = isys_cats_person_list__isys_obj__id
			LEFT JOIN isys_connection ON isys_cats_person_list__isys_connection__id = isys_connection__id
			LEFT JOIN isys_catg_mail_addresses_list ON isys_catg_mail_addresses_list__isys_obj__id = isys_obj__id AND isys_catg_mail_addresses_list__primary = 1
			WHERE isys_cats_person_list__title = ' . $this->convert_sql_text($p_username);

        if ($p_status !== null)
        {
            $l_sql .= ' AND isys_obj__status = ' . $this->convert_sql_int($p_status);
        } // if

        return $this->retrieve($l_sql . ';');
    } // function

    /**
     * Retrieve persons by object ID.
     *
     * @param   integer $p_object_id
     *
     * @return  isys_component_dao_result
     */
    public function get_person_by_id($p_object_id)
    {
        return $this->get_data(null, $p_object_id);
    } // function

    /**
     * Retrieve persons by one or multiple email address(es).
     *
     * @param   mixed $p_email_address May be a string or an array.
     *
     * @return  isys_component_dao_result
     */
    public function get_persons_by_email($p_email_address)
    {
        if (!is_array($p_email_address))
        {
            $p_email_address[] = $p_email_address;
        } // if

        $l_parts = [];

        foreach ($p_email_address as $l_address)
        {
            $l_parts[] = 'isys_catg_mail_addresses_list__title = ' . $this->convert_sql_text($l_address);
        } // foreach

        $l_condition = '';

        if (count($l_parts) > 0)
        {
            $l_condition = 'AND (' . implode(' OR ', $l_parts) . ')';
        } // if

        return $this->get_data(null, null, $l_condition);
    }

    /**
     * @param isys_component_database $p_db
     */
    public function __construct(isys_component_database $p_db)
    {
        parent::__construct($p_db);

        isys_component_signalcollection::get_instance()
            ->connect(
                'mod.cmdb.beforeUserGroupChanged',
                [
                    'isys_cmdb_dao_category_s_person',
                    'slotBeforeUserGroupChanged'
                ]
            );
    }
} // class