<?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: abstraction layer for CMDB global categories.
 *
 * @package        i-doit
 * @subpackage     CMDB_Categories
 * @author         Leonard Fischer <lfischer@i-doit.org>
 * @version        Van Quyen Hoang <qhoang@i-doit.org> 19.05.2014
 * @copyright      synetics GmbH
 * @license        http://www.i-doit.com/license
 */
class isys_cmdb_dao_category_property_ng extends isys_cmdb_dao_category
{
    /**
     * Constants for grouping list values as comma or as list (<li></li>)
     */
    const C__GROUPING__COMMA = 0;
    const C__GROUPING__LIST  = 1;

    /**
     * @var
     */
    private $m_grouping = 0;

    /**
     * @return mixed
     * @author   Van Quyen Hoang <qhoang@i-doit.com>
     */
    public function getGrouping()
    {
        return $this->m_grouping;
    }

    /**
     * @param int $p_grouping
     *
     * @author   Van Quyen Hoang <qhoang@i-doit.com>
     */
    public function setGrouping($p_grouping)
    {
        $this->m_grouping = $p_grouping;
        return $this;
    } // function

    /**
     * Array which contains references
     *
     * @var array
     */
    private $m_reference = ['root' => 'obj_main.isys_obj__id'];

    /**
     * Helper array for reordering the properties for the joins
     *
     * @var array
     */
    private $m_property_order = [];

    /**
     * Ignore these format callbacks
     *
     * @var array
     */
    public static $m_ignored_format_callbacks = [
        'location_property_pos'
    ];
    /**
     * Alias counter for the report-builder.
     *
     * @var  integer
     */
    protected $m_alias_cnt = 1;
    /**
     * List of aliases for the report-builder.
     *
     * @var  array
     */
    protected $m_aliases = [
        'isys_obj'                        => 'obj_main'
    ];
    /**
     * @var array
     */
    protected $m_aliases_lvls = [];
    /**
     * Variable which determines if the report also displays empty values
     *
     * @var bool
     */
    protected $m_empty_values = true;
    /**
     * This variable is used to define, if all the necessary preparations have been made for creating the generic query.
     *
     * @var  boolean
     */
    protected $m_prepared_data_for_query_construction = false;
    /**
     * This array will hold all the necessary property-data!
     *
     * @var  array
     */
    protected $m_property_rows = [];
    /**
     * This array will hold all the necessary property-data!
     *
     * @var  array
     */
    protected $m_property_rows_lvls = [];
    /**
     * @var bool
     */
    protected $m_query_as_report = false;

    /**
     * Contains all used aliase
     *
     * @var array
     */
    private $m_already_used_aliase = [];
    /**
     * Variable which contains the data field columns which will be used for the custom categories joins only for the
     * main object.
     *
     * @var string
     */
    private $m_parent_custom_field = '';
    /**
     * Contains the referenced fields
     *
     * @var array
     */
    private $m_referenced_fields = [];
    /**
     * Variable which contains all referenced fields which will be deleted after building the query
     *
     * @var array
     */
    private $m_remove_from_selection = [];
    /**
     * Contains the sub joins from the conditions
     *
     * @var array
     */
    private $m_sub_joins = [];

    /**
     * @var array
     */
    private $m_property_key_selections = [];

    /**
     * @var array
     */
    private $m_selects = [];

    /**
     * @return array
     * @author   Van Quyen Hoang <qhoang@i-doit.com>
     */
    public function getSelects()
    {
        return $this->m_selects;
    } // function

    /**
     * @return array
     * @author   Van Quyen Hoang <qhoang@i-doit.com>
     */
    public function get_property_key_selections()
    {
        return $this->m_property_key_selections;
    } // function

    /**
     * @param array $p_value
     *
     * @author   Van Quyen Hoang <qhoang@i-doit.com>
     */
    public function set_property_key_selections($p_key, $p_value)
    {
        $this->m_property_key_selections[$p_key] = $p_value;
    } // function

    /**
     * Method for setting the "query_as_report" variable from extern.
     *
     * @param   boolean $p_query_as_report
     *
     * @return  isys_cmdb_dao_category_property_ng
     * @author  Leonard Fischer <lfischer@i-doit.com>
     */
    public function set_query_as_report($p_query_as_report)
    {
        $this->m_query_as_report = !!$p_query_as_report;

        return $this;
    } // function

    /**
     * Resets member variables
     *
     * @return  isys_cmdb_dao_category_property_ng
     */
    public function reset()
    {
        $this->m_already_used_aliase   = [];
        $this->m_aliases_lvls          = [];
        $this->m_sub_joins             = [];
        $this->m_referenced_fields     = [];
        $this->m_remove_from_selection = [];
        $this->m_parent_custom_field   = '';
        $this->m_aliases               = [
            'isys_obj'                        => 'obj_main'
        ];
        $this->m_alias_cnt = 1;
        $this->m_prepared_data_for_query_construction = false;

        return $this;
    } // function

    /**
     * Method for recieving the property-ID's by a given array.
     *
     * @param   array $p_properties
     *
     * @return  array
     * @author  Dennis Stücken <dstuecken@synetics.de>
     * @todo    Enable this method to handle categorie-arrays with more than one property!
     */
    public function format_property_array($p_properties)
    {
        $l_property_array = [];

        foreach ($p_properties as $l_props)
        {
            if ($l_props['g'])
            {
                foreach ($l_props['g'] as $l_cat_id => $l_property)
                {
                    $l_tmp = $this->retrieve_properties(null, $l_cat_id, null, null, ' AND isys_property_2_cat__prop_key = \'' . $l_property[0] . '\'')
                        ->get_row();

                    if ($l_tmp)
                    {
                        $l_property_array[] = $l_tmp['id'];
                    } // if
                } // foreach

                foreach ($l_props['s'] as $l_cat_id => $l_property)
                {
                    $l_tmp = $this->retrieve_properties(null, null, $l_cat_id, null, ' AND isys_property_2_cat__prop_key = \'' . $l_property[0] . '\'')
                        ->get_row();

                    if ($l_tmp)
                    {
                        $l_property_array[] = $l_tmp['id'];
                    } // if
                } // foreach
            } // if
        } // foreach

        return $l_property_array;
    } // function

    /**
     * Creates SQL query from selected properties.
     *
     * @param   mixed   $p_properties JSON array or array
     * @param   array   $p_objects    List of objects ('isys_obj__id' has be to included!).
     * @param   boolean $p_with_object_data
     *
     * @return  string  SQL query
     * @throws  isys_exception_general
     */
    public function create_property_query($p_properties, $p_objects, $p_with_object_data = false, $p_leave_field_identifiers = false)
    {
        try
        {
            if (is_string($p_properties))
            {
                $l_properties = isys_format_json::decode($p_properties);
            }
            else if (is_array($p_properties))
            {
                $l_properties = $p_properties;
            }
            else
            {
                throw new isys_exception_general('Invalid argument.');
            } // if

            $l_smarty_plugin = new isys_smarty_plugin_f_property_selector();
            $l_preselection  = $l_smarty_plugin->handle_preselection($l_properties);

            $l_keys = [];
            foreach ($l_preselection as $l_value)
            {
                $l_keys[] = $l_value['prop_id'];
            } // foreach

            if ($p_with_object_data)
            {
                $l_selects = $this->create_property_query_select($l_keys, ['obj_main.*'], $p_leave_field_identifiers);
            }
            else
            {
                $l_selects = $this->create_property_query_select($l_keys, [], $p_leave_field_identifiers);
            } // if

            $l_joins = $this->create_property_query_join($l_keys);

            $l_objects = [];

            if (is_array($p_objects) && count($p_objects))
            {
                foreach ($p_objects as $l_object)
                {
                    $l_objects[] = $this->convert_sql_id($l_object['isys_obj__id']);
                } // foreach

                return 'SELECT ' . implode(', ', $l_selects) . ' FROM isys_obj as obj_main ' . implode(' ', $l_joins) . ' WHERE obj_main.isys_obj__id IN (' . implode(
                    ',',
                    $l_objects
                ) . ');';
            } // if

            // This could happen, but shall not throw errors :)
            return 'SELECT ' . implode(', ', $l_selects) . ' FROM isys_obj as obj_main ' . implode(' ', $l_joins) . ' WHERE obj_main.isys_obj__id = NULL;';
        }
        catch (Exception $e)
        {
            throw new isys_exception_general('Failed to create property query: ' . $e->getMessage());
        } //try/catch
    } // function

    /**
     * Method for creating a generic list query based on configured category properties.
     *
     * @param   mixed   $p_properties
     * @param   mixed   $p_object_types Can be either a integer or a array of integers.
     * @param   mixed   $p_object_ids   Can be either a integer or a array of integers.
     * @param   array   $p_queries
     * @param   boolean $p_leave_field_identifiers
     *
     * @return  string
     * @author  Dennis Stücken <dstuecken@synetics.de>
     */
    public function create_property_query_for_lists($p_properties, $p_object_types = null, $p_object_ids = null, $p_queries = [], $p_leave_field_identifiers = true)
    {
        if (is_string($p_properties))
        {
            $p_properties = isys_format_json::decode($p_properties);
        } // if

        if (! is_array($p_properties))
        {
            $p_properties = [];
        } // if

        $this->prepare_necessary_tasks($p_properties);

        $l_selects = $this->create_property_query_select(
            $p_properties,
            $p_queries,
            $p_leave_field_identifiers,
            true
        );

        $l_joins = $this->create_property_query_join($p_properties, true);

        $l_sql = "SELECT \n" . implode(", \n", $l_selects) . " \n\n" . "FROM isys_obj AS obj_main \n" . implode(" \n", $l_joins) . " \n\n" . "WHERE TRUE \n";

        if ($p_object_types !== null)
        {
            if (is_array($p_object_types))
            {
                $l_sql .= 'AND obj_main.isys_obj__isys_obj_type__id ' . $this->prepare_in_condition($p_object_types) . " \n";
            }
            else
            {
                $l_sql .= 'AND obj_main.isys_obj__isys_obj_type__id = ' . $this->convert_sql_id($p_object_types) . " \n";
            } // if
        } // if

        if ($p_object_ids !== null)
        {
            if (is_array($p_object_ids))
            {
                $l_sql .= 'AND obj_main.isys_obj__id ' . $this->prepare_in_condition($p_object_ids) . " \n";
            }
            else
            {
                $l_sql .= 'AND obj_main.isys_obj__id = ' . $this->convert_sql_id($p_object_ids) . " \n";
            } // if
        } // if

        return $l_sql;
    } // function

    /**
     * Method for building the property-select query.
     *
     * @param   array   $p_property_ids              Array which holds the property-id's (from "isys_property_2_cat").
     * @param   boolean $p_select_status             Shall the status be selected aswell?
     * @param   array   $p_selects                   You may enter some SELECT-statements here (see $this->create_property_query_for_lists()).
     * @param   boolean $p_leave_field_identifiers   Set to true to keep the original field-names instead of "LANGUACE_CONSTANT###123".
     * @param   boolean $p_use_property_ids_as_title Set to true to retrieve the property-IDs as field-names.
     * @param   boolean $p_group_by_object
     *
     * @return  array
     * @author  Leonard Fischer <lfischer@i-doit.org>
     * @author  Van Quyen Hoang <qhoang@i-doit.org>
     */
    public function create_property_query_select(array $p_property_ids, $p_selects = [], $p_leave_field_identifiers = false, $p_for_list = false)
    {
        // We need to know if all the necessary stuff has been done!
        if (!$this->m_prepared_data_for_query_construction)
        {
            $this->prepare_necessary_tasks($p_property_ids);
        } // if

        // Now we prepare the SELECT's.
        $l_selects = array_merge(
            $p_selects,
            [
                "obj_main.isys_obj__id AS '__id__'"
            ]
        );

        // And add the selected ones from the report-builder.
        foreach ($p_property_ids as $l_select)
        {
            if (!isset($this->m_property_rows[$l_select])) continue;

            $l_cat   = 'cats';
            $l_table = current(explode('__', $this->m_property_rows[$l_select]['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD]));

            if ($this->m_property_rows[$l_select]['catg'] != null)
            {
                $l_cat = 'catg';
            }
            elseif ($this->m_property_rows[$l_select]['catg_custom'] != null)
            {
                $l_cat = 'catg_custom';
            } // if

            if(isset($this->m_property_rows[$l_select]['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__SELECT]))
            {
                if(!$p_leave_field_identifiers)
                {
                    $l_alias = $this->m_property_rows[$l_select]['title'] . "###" . $this->m_property_rows[$l_select][$l_cat];
                }
                else
                {
                    $l_alias = $this->m_property_rows[$l_select]['class'] . '__' . $this->m_property_rows[$l_select]['key'];
                    if($this->m_property_rows[$l_select]['key'] == 'description' && $l_cat == 'catg_custom')
                    {
                        $l_alias .= '_' . $this->m_property_rows[$l_select]['catg_custom'];
                    } // if
                } // if

                $l_alias = $this->convert_sql_text($l_alias);

                $l_joinedTableAlias = null;
                $l_alias_key = null;

                /**
                 * @var $l_selectSubSelect \idoit\Module\Report\SqlQuery\Structure\SelectSubSelect
                 */
                $l_selectSubSelect = clone $this->m_property_rows[$l_select]['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__SELECT];

                if($l_table == 'isys_obj' || $l_selectSubSelect->getSelectTable() == 'isys_obj')
                {
                    $l_joinedTableAlias = 'obj_main';
                }
                else
                {
                    if($l_selectSubSelect->getSelectTable() !== '' && $l_selectSubSelect->getSelectFieldObjectID() !== '')
                    {
                        $l_table = $l_selectSubSelect->getSelectTable();
                    }
                    elseif($l_selectSubSelect->getSelectTable() !== '' && $l_selectSubSelect->getSelectFieldObjectID() === '')
                    {
                        $l_alias_key = $l_selectSubSelect->getSelectTable() . '#' . $this->m_property_rows[$l_select]['table'];
                        $l_joinedTableAlias = (isset($this->m_aliases[$l_alias_key]))? 'j' . $this->m_aliases[$l_alias_key]: null;
                    } // if

                    if($l_joinedTableAlias === null && ($l_alias_num = $this->retrieve_alias($l_table)))
                    {
                        $l_joinedTableAlias = 'j' . $l_alias_num;
                    } // if
                } // if

                $l_selectCondition = clone $l_selectSubSelect->getSelectCondition();
                $l_condition = '';

                if($l_alias_key !== null)
                {
                    // No SubSelect just a field
                    $l_selectSubSelect->setSelectQuery(str_replace($l_selectSubSelect->getSelectTable(), $l_joinedTableAlias . '.' . $l_selectSubSelect->getSelectTable(), $l_selectSubSelect->getSelectQuery()));
                }
                else
                {
                    if ($l_selectSubSelect->getSelectTable())
                    {
                        if (/*($this->m_property_rows[$l_select]['provides'] & C__PROPERTY__PROVIDES__VIRTUAL || $l_joinedTableAlias === null || $l_joinedTableAlias === 'obj_main') &&*/
                        $l_selectSubSelect->getSelectFieldObjectID()
                        )
                        {
                            $l_condition = $l_selectSubSelect->getSelectFieldObjectID() . ' = obj_main.isys_obj__id';
                        }
                        elseif ($l_conditionField = $l_selectSubSelect->getSelectReferenceKey())
                        {
                            $l_condition = ($l_joinedTableAlias ? $l_joinedTableAlias . '.' : '') .
                                $this->m_property_rows[$l_select]['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD] . ' = ' . $l_conditionField;
                        }
                        elseif ($l_conditionField = $l_selectSubSelect->getSelectFieldObjectID() && $l_joinedTableAlias !== 'obj_main' && $l_joinedTableAlias !== null)
                        {
                            $l_condition = $l_joinedTableAlias . '.' . $l_selectSubSelect->getSelectTable() . '__isys_obj__id = ' . $l_conditionField;
                        }
                        // @todo modify this if Config parameter for Grouping of the selection has been added. If grouping is active then skip this if so that the reference is the object-ID
                        elseif (($l_conditionField = $l_selectSubSelect->getSelectPrimaryKey()) && (bool) $this->m_property_rows[$l_select]['multi'] === true)
                        {
                            $l_condition = ($l_joinedTableAlias ? $l_joinedTableAlias . '.' : '') . $l_selectSubSelect->getSelectTable() . '__id = ' . $l_conditionField;
                        }
                        elseif ($l_selectSubSelect->getSelectTable())
                        {
                            $l_condition = ($l_joinedTableAlias ? $l_joinedTableAlias . '.' : '') . $l_selectSubSelect->getSelectTable() . '__id = ' .
                                $l_selectSubSelect->getSelectTable() . '__id';
                        } // if

                        if ($l_condition)
                        {
                            $l_selectCondition->addCondition(' AND ' . $l_condition);
                        } // if

                        // Add status check only if table contains 1 '_list '
                        if(substr_count($l_selectSubSelect->getSelectTable(), '_list') === 1)
                        {
                            $l_alias_table = (strpos($l_selectSubSelect->getSelectFieldObjectID(), '.') ?
                                substr($l_selectSubSelect->getSelectFieldObjectID(), 0, strpos($l_selectSubSelect->getSelectFieldObjectID(), '.')) . '.' :
                                (strpos($l_selectSubSelect->getSelectPrimaryKey(), '.') ?
                                    substr($l_selectSubSelect->getSelectPrimaryKey(), 0, strpos($l_selectSubSelect->getSelectPrimaryKey(), '.')) . '.' : ''));

                            $l_selectCondition->addCondition(
                                ' AND ' . $l_alias_table . $l_selectSubSelect->getSelectTable() . '__status = ' . $this->convert_sql_int(C__RECORD_STATUS__NORMAL)
                            );
                        } // if
                    }
                    elseif ($l_joinedTableAlias != 'obj_main')
                    {
                        $l_selectSubSelect->setSelectQuery(
                            str_replace(
                                $this->m_property_rows[$l_select]['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD],
                                ($l_joinedTableAlias ? $l_joinedTableAlias . '.' : '') .
                                $this->m_property_rows[$l_select]['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD],
                                $l_selectSubSelect->getSelectQuery()
                            )
                        );
                    }
                    elseif ($this->m_property_rows[$l_select]['provides'] & C__PROPERTY__PROVIDES__VIRTUAL && $l_selectSubSelect->getSelectFieldObjectID())
                    {
                        $l_condition = ' AND ' . $l_selectSubSelect->getSelectFieldObjectID() . ' = obj_main.isys_obj__id';
                        $l_selectCondition->addCondition(' AND ' . $l_condition);
                    }
                    elseif ($l_joinedTableAlias == 'obj_main')
                    {
                        $l_selectSubSelect->setSelectQuery(str_replace('isys_obj__', 'obj_main.isys_obj__', $l_selectSubSelect->getSelectQuery()));
                    } // if
                } // if

                $l_selectSubSelect
                    ->setSelectAlias($l_alias)
                    ->setSelectCondition($l_selectCondition);

                // Handling Selection with GROUP BY and wrap the selection with the GROUP_CONCAT
                if($l_selectSubSelect->getSelectGroupBy()->getGroupBy() && $p_for_list)
                {
                    if(!($l_selection = $l_selectSubSelect->getSelectGroupBy()->getGroupConcatSelection()))
                    {
                        $l_selection = trim(
                            substr(
                                $l_selectSubSelect->getSelectQuery(),
                                stripos($l_selectSubSelect->getSelectQuery(), 'SELECT') + 6,
                                ((substr_count($l_selectSubSelect->getSelectQuery(), 'FROM') > 1)? strrpos($l_selectSubSelect->getSelectQuery(), 'FROM'): strpos($l_selectSubSelect->getSelectQuery(), 'FROM')) - 6
                            )
                        );
                    }
                    if(strpos($l_selection, 'GROUP_CONCAT') === false)
                    {
                        switch($this->m_grouping)
                        {
                            case self::C__GROUPING__LIST:
                                $l_new_selection = 'CONCAT(\'<ul><li>\', GROUP_CONCAT(' . $l_selection . ' SEPARATOR \'</li><li>\'), \'</li></ul>\')';
                                break;
                            case self::C__GROUPING__COMMA:
                            default:
                                $l_new_selection = 'GROUP_CONCAT(' . $l_selection . ' SEPARATOR \', \')';
                                break;
                        }

                        $l_selectSubSelect->setSelectQuery(str_replace($l_selection, $l_new_selection, $l_selectSubSelect->getSelectQuery()));
                    } // if
                } // if

                $l_selects[$l_select] = $l_selectSubSelect;

                $this->set_property_key_selections($this->m_property_rows[$l_select]['class'] . '__' . $this->m_property_rows[$l_select]['key'], $l_selectSubSelect->getSelectQuery());
                continue;
            } // if

            $l_alias = 'j' . $this->retrieve_alias($l_table);

            // Then we check for special table-names inside the select.
            if (isset($this->m_aliases[$l_table]))
            {
                $l_alias = $this->m_aliases[$l_table];
            }
            elseif (isset($this->m_aliases[$l_table . '#main_obj']))
            {
                if ($l_table == 'isys_catg_connector_list')
                {
                    $l_alias = 'j' . $this->retrieve_alias($l_table, $l_table);
                }
                else
                {
                    $l_alias = 'j' . $this->m_aliases[$l_table . '#main_obj'];
                }
            }
            elseif ($l_table == 'isys_logbook')
            {
                $l_alias = 'j' . $this->retrieve_alias('isys_catg_logb_list', 'isys_logbook');
            }

            // @todo Add Config parameter for Grouping the selection
            if($this->m_property_rows[$l_select]['multi'])
            {
                switch($this->m_grouping)
                {
                    case self::C__GROUPING__LIST:
                        $l_field = 'CONCAT(\'<ul><li>\', GROUP_CONCAT(' . $l_alias . "." . $this->m_property_rows[$l_select]['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD] . ' SEPARATOR \'</li><li>\'), \'</li></ul>\')';
                        break;
                    case self::C__GROUPING__COMMA:
                    default:
                        $l_field = 'GROUP_CONCAT(' . $l_alias . "." . $this->m_property_rows[$l_select]['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD] . ' SEPARATOR \', \')';
                        break;
                }
            }
            else
            {
                $l_field = $l_alias . "." . $this->m_property_rows[$l_select]['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD];
            } // if

            $l_selects[$l_select] = $l_field . ' ' .
                (!$p_leave_field_identifiers ? " AS '" . $this->m_property_rows[$l_select]['title'] . "###" . $this->m_property_rows[$l_select][$l_cat] . "'"
                    : $this->convert_sql_text($this->m_property_rows[$l_select]['class'] . '__' . $this->m_property_rows[$l_select]['key']));

            $this->set_property_key_selections(
                $this->m_property_rows[$l_select]['class'] . '__' . $this->m_property_rows[$l_select]['key'],
                $l_alias . "." . $this->m_property_rows[$l_select]['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD]
            );
        } // foreach

        // Clearing out all duplicate selects.
        return array_unique($l_selects);
    } // function

    /**
     * Method for creating the join-statements, based on the selected properties and conditions.
     *
     * @param   array   $p_property_ids The property-ID's of all the properties we need to join.
     * @param   boolean $l_main_obj
     *
     * @return  array
     * @author  Leonard Fischer <lfischer@i-doit.org>
     * @author  Van Quyen Hoang <qhoang@i-doit.org>
     */
    private function create_property_query_join(array $p_property_ids, $p_for_list = false)
    {
        // We need to know if all the necessary stuff has been done!
        if (!$this->m_prepared_data_for_query_construction)
        {
            $this->prepare_necessary_tasks($p_property_ids);
        } // if

        // We need this array to save "already joined" tables for saving a bit of performance.
        $l_already_joined_tables = $l_joins = [];

        if ($this->m_empty_values)
        {
            $l_join_type = "LEFT";
        }
        else
        {
            // Only root tables will be joined as INNER JOIN. Subquery joins will always be a LEFT JOIN
            $l_join_type = "INNER";
        } // if

        // Now we create the single JOIN's.
        foreach ($p_property_ids as $l_prop_id)
        {
            if (!isset($this->m_property_rows[$l_prop_id])) continue;

            $l_reference = $l_ref_field = $l_alias_sec = $l_alias_third = $l_alias_fourth = null;

            $l_prop_data = $this->m_property_rows[$l_prop_id];

            // We won't handle dynamic properties here.
            if ($l_prop_data['type'] == C__PROPERTY_TYPE__DYNAMIC && !isset($l_prop_data['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD]) || ($l_prop_data['provides'] & C__PROPERTY__PROVIDES__VIRTUAL))
            {
                continue;
            } // if

            {
                if ($l_prop_data['type'] == C__PROPERTY_TYPE__DYNAMIC)
                {
                    $l_table = $l_prop_data['table'];
                }
                else
                {
                    $l_table = current(explode('__', $l_prop_data['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD]));
                }

                // We have to check for an existing "predefined" alias.
                if (isset($this->m_aliases[$l_table]))
                {
                    $l_alias = $this->m_aliases[$l_table];
                }
                else
                {
                    $l_alias = 'j' . $this->retrieve_alias($l_table);
                } // if

                if(isset($this->m_reference['root-' . $l_table]) && !$l_prop_data['data'][C__PROPERTY__PROVIDES][C__PROPERTY__PROVIDES__VIRTUAL])
                {
                    $l_reference = $this->m_reference['root-' . $l_table];
                } // if

                if ($l_table == 'isys_logbook')
                {
                    if (!in_array('isys_catg_logb_list#isys_obj', $l_already_joined_tables))
                    {
                        $l_alias = 'j' . $this->retrieve_alias('isys_obj', 'isys_catg_logb_list');

                        if (in_array($l_alias, $this->m_already_used_aliase)) continue;

                        $l_already_joined_tables[] = 'isys_catg_logb_list#isys_obj';
                        $l_joins[$l_alias]         = $l_join_type . " JOIN isys_catg_logb_list AS " . $l_alias . " ON " . $l_alias . ".isys_catg_logb_list__isys_obj__id = obj_main.isys_obj__id";
                    }

                    if (!in_array('isys_logbook#isys_catg_logb_list', $l_already_joined_tables))
                    {
                        $l_alias     = 'j' . $this->retrieve_alias('isys_catg_logb_list', 'isys_logbook');
                        $l_alias_ref = 'j' . $this->retrieve_alias('isys_obj', 'isys_catg_logb_list');

                        if (in_array($l_alias, $this->m_already_used_aliase)) continue;

                        $l_already_joined_tables[] = 'isys_logbook#isys_catg_logb_list';
                        $l_joins[$l_alias]         = $l_join_type . " JOIN isys_logbook AS " . $l_alias . " ON " . $l_alias . ".isys_logbook__id = " . $l_alias_ref . ".isys_catg_logb_list__isys_logbook__id";
                    }
                    $this->m_already_used_aliase[] = $l_alias;
                    continue;
                }

                if(isset($l_prop_data['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__SELECT]))
                {
                    /**
                     * @var $l_selectSubSelect idoit\Module\Report\SqlQuery\Structure\SelectSubSelect
                     */
                    $l_selectSubSelect = clone $l_prop_data['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__SELECT];
                    if($l_selectSubSelect->getSelectTable() && $l_selectSubSelect->getSelectFieldObjectID() != '')
                    {
                        // We don´t need to join any table
                        // $l_table = $l_selectSubSelect->getSelectTable();
                        continue;
                    } // if
                } // if


                if(isset($l_prop_data['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__JOIN]))
                {
                    $l_last_table = null;
                    $l_last_alias = null;

                    /**
                     * @var $l_selectJoin idoit\Module\Report\SqlQuery\Structure\SelectJoin
                     *
                     * @todo needs to be modified see property pos from isys_cmdb_dao_category_g_location
                     */
                    foreach($l_prop_data['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__JOIN] AS $l_index => $l_selectJoin)
                    {
                        $l_selectJoin->setJoinType($l_join_type);
                        if($l_last_table === null)
                        {
                            $l_table = $l_selectJoin->getTable();
                            $l_last_alias = 'j' . $this->m_aliases[$l_table . '#isys_obj'];
                            $l_already_joined_tables[] = $l_table . '#isys_obj';

                            $l_selectJoin->setTableAlias($l_last_alias)->setOnLeftAlias($l_last_alias)->setOnRightALias('obj_main');
                        }
                        else
                        {
                            $l_table = $l_selectJoin->getTable();

                            if(in_array($l_table . '#' . $l_last_table, $l_already_joined_tables)) continue;

                            if($l_selectJoin->getRefTable()){
                                $l_last_table = $l_selectJoin->getRefTable();
                                $l_last_alias = 'j' . $this->m_aliases[$l_last_table . '#isys_obj'];
                            } // if
                            $l_alias = 'j' . $this->m_aliases[$l_table . '#' . $l_last_table];
                            $l_already_joined_tables[] = $l_table . '#' . $l_last_table;
                            $l_selectJoin->setTableAlias($l_alias)->setOnLeftAlias($l_last_alias)->setOnRightAlias($l_alias);
                            $l_last_alias = $l_alias;
                        }

                        if(!in_array($l_alias, $this->m_already_used_aliase))
                        {
                            $l_joins[$l_last_alias] = $l_selectJoin;
                            $this->m_already_used_aliase[] = $l_alias;
                        }

                        $l_last_table = $l_table;

                        if($l_table == 'isys_connection' || strpos($l_table, '_2_') ||
                            !(isset($l_prop_data['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__SELECT])))
                        {
                            continue;
                        }
                    }

                    continue;
                }

                if (!in_array($l_table . '#isys_obj', $l_already_joined_tables) && !in_array($l_alias, $this->m_already_used_aliase) && $l_alias != 'obj_main')
                {
                    if (isset($this->m_aliases[$l_table . '#main_obj']))
                    {
                        $l_alias = 'j' . $this->retrieve_alias('main_obj', $l_table);
                    } // if

                    if (!in_array($l_alias, $this->m_already_used_aliase))
                    {
                        $l_already_joined_tables[] = $l_table . '#isys_obj';

                        if (in_array($l_table, $l_already_joined_tables) && $l_reference !== null && $l_prop_data['multi'] > 0)
                        {
                            $l_join = $l_join_type . " JOIN " . $l_table . " AS " . $l_alias . " ON " . $l_alias . "." . $l_table . "__id = " . $l_reference .
                                ((substr_count($l_table, '_list') === 1) ?" AND " . $l_alias . "." . $l_table . "__status = " . $this->convert_sql_int(C__RECORD_STATUS__NORMAL): '');
                        }
                        else
                        {
                            $l_join                                = $l_join_type . " JOIN " . $l_table . " AS " . $l_alias . " ON " . $l_alias . "." . $l_table .
                                "__isys_obj__id = obj_main.isys_obj__id " .
                                ((substr_count($l_table, '_list') === 1) ?"AND " . $l_alias . '.' . $l_table . '__status = ' . $this->convert_sql_int(C__RECORD_STATUS__NORMAL): '');
                            $this->m_reference['root-' . $l_table] = $l_alias . "." . $l_table . '__id';
                        } // if
                        $l_joins[$l_alias]             = $l_join;
                        $this->m_already_used_aliase[] = $l_alias;
                    } // if
                } // if
            } // if
        } // foreach
        // Clearing out all duplicate joins.
        $l_joins = array_unique($l_joins);

        return $l_joins;
    } // function

    /**
     * Method for retrieving properties.
     *
     * @todo    Add custom categories
     *
     * @param   mixed   $p_property_id May be a array or an integer.
     * @param   mixed   $p_catg_id     May be a array or an integer.
     * @param   mixed   $p_cats_id     May be a array or an integer.
     * @param   integer $p_provides
     * @param   string  $p_condition
     * @param   boolean $p_dynamic_properties
     * @param   mixed   $p_catg_custom_id
     *
     * @return  isys_component_dao_result
     * @author  Leonard Fischer <lfischer@i-doit.org>
     */
    public function retrieve_properties($p_property_id = null, $p_catg_id = null, $p_cats_id = null, $p_provides = null, $p_condition = "", $p_dynamic_properties = false, $p_catg_custom_id = null)
    {
        $l_condition = " WHERE TRUE ";

        if ($p_property_id !== null)
        {
            if (is_array($p_property_id))
            {
                $l_condition .= "AND isys_property_2_cat__id " . $this->prepare_in_condition($p_property_id) . " ";
            }
            else if (is_numeric($p_property_id))
            {
                $l_condition .= "AND isys_property_2_cat__id = " . $this->convert_sql_id($p_property_id) . " ";
            } // if
        } // if

        if ($p_catg_id !== null)
        {
            if (is_array($p_catg_id))
            {
                $l_condition .= "AND isys_property_2_cat__isysgui_catg__id " . $this->prepare_in_condition($p_catg_id) . " ";
            }
            else if (is_numeric($p_catg_id))
            {
                $l_condition .= "AND isys_property_2_cat__isysgui_catg__id = " . $this->convert_sql_id($p_catg_id) . " ";
            } // if
        } // if

        if ($p_cats_id !== null)
        {
            if (is_array($p_cats_id))
            {
                $l_condition .= "AND isys_property_2_cat__isysgui_cats__id " . $this->prepare_in_condition($p_cats_id) . " ";
            }
            else if (is_numeric($p_cats_id))
            {
                $l_condition .= "AND isys_property_2_cat__isysgui_cats__id = " . $this->convert_sql_id($p_cats_id) . " ";
            } // if
        } // if

        if ($p_catg_custom_id !== null)
        {
            if (is_array($p_catg_custom_id))
            {
                $l_condition .= "AND isys_property_2_cat__isysgui_catg_custom__id " . $this->prepare_in_condition($p_catg_custom_id) . " ";
            }
            else if (is_numeric($p_catg_custom_id))
            {
                $l_condition .= "AND isys_property_2_cat__isysgui_catg_custom__id = " . $this->convert_sql_id($p_catg_custom_id) . " ";
            } // if
        }

        if ($p_provides !== null && $p_provides > 0)
        {
            $l_condition .= "AND isys_property_2_cat__prop_provides & " . $this->convert_sql_int($p_provides) . " ";
        } // if

        if (!$p_dynamic_properties)
        {
            $l_condition .= "AND isys_property_2_cat__prop_type = " . C__PROPERTY_TYPE__STATIC . " ";
        } // if

        $l_category_join = " LEFT JOIN isysgui_catg ON isysgui_catg__id = isys_property_2_cat__isysgui_catg__id " .
            " LEFT JOIN isysgui_cats ON isysgui_cats__id = isys_property_2_cat__isysgui_cats__id " .
            " LEFT JOIN isysgui_catg_custom ON isysgui_catg_custom__id = isys_property_2_cat__isysgui_catg_custom__id ";

        // We rename the fields for easier usage.
        $l_sql = "SELECT isys_property_2_cat__id AS 'id', " . "isys_property_2_cat__isysgui_catg__id AS 'catg', " . "isys_property_2_cat__isysgui_cats__id AS 'cats', " .
            "isys_property_2_cat__isysgui_catg_custom__id AS 'catg_custom', " . "isys_property_2_cat__cat_const AS 'const', " . "isys_property_2_cat__prop_type AS 'type', " .
            "isys_property_2_cat__prop_title AS 'title', " . "isys_property_2_cat__prop_key AS 'key', " .
            "(CASE WHEN isys_property_2_cat__isysgui_catg__id IS NOT NULL THEN isysgui_catg__list_multi_value " .
            "WHEN isys_property_2_cat__isysgui_cats__id IS NOT NULL THEN isysgui_cats__list_multi_value " .
            "WHEN isys_property_2_cat__isysgui_catg_custom__id IS NOT NULL THEN isysgui_catg_custom__list_multi_value END) AS 'multi', " .
            "(CASE WHEN isys_property_2_cat__isysgui_catg__id IS NOT NULL THEN isysgui_catg__class_name " .
            "WHEN isys_property_2_cat__isysgui_cats__id IS NOT NULL THEN isysgui_cats__class_name " .
            "WHEN isys_property_2_cat__isysgui_catg_custom__id IS NOT NULL THEN isysgui_catg_custom__class_name END) AS 'class', " .
            "(CASE WHEN isys_property_2_cat__isysgui_catg__id IS NOT NULL THEN isysgui_catg__source_table " .
            "WHEN isys_property_2_cat__isysgui_cats__id IS NOT NULL THEN isysgui_cats__source_table " .
            "WHEN isys_property_2_cat__isysgui_catg_custom__id IS NOT NULL THEN 'isys_catg_custom_fields_list' END) AS 'table', " .
            "isys_property_2_cat__prop_provides AS provides ".
            "FROM isys_property_2_cat " .
            $l_category_join . $l_condition;

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

    /**
     * This method retrieves all categories which have at least one properties that fit the given provide-parameter.
     *
     * @param   string  $p_category_type
     * @param   integer $p_provide
     * @param   boolean $p_dynamic_property
     *
     * @return  isys_component_dao_result
     */
    public function retrieve_categories_by_provide($p_provide, $p_category_type, $p_dynamic_property = false)
    {
        $l_sql = 'SELECT * FROM isys_property_2_cat
			LEFT JOIN isysgui_catg ON isysgui_catg__id = isys_property_2_cat__isysgui_catg__id
			LEFT JOIN isysgui_cats ON isysgui_cats__id = isys_property_2_cat__isysgui_cats__id
			LEFT JOIN isysgui_catg_custom ON isysgui_catg_custom__id = isys_property_2_cat__isysgui_catg_custom__id
			WHERE isys_property_2_cat__isysgui_cat' . $p_category_type . '__id > 0';

        if ($p_provide !== null)
        {
            $l_sql .= ' AND isys_property_2_cat__prop_provides & ' . $this->convert_sql_int($p_provide);
        } // if

        if (!$p_dynamic_property)
        {
            $l_sql .= ' AND isys_property_2_cat__prop_type = ' . $this->convert_sql_int(C__PROPERTY_TYPE__STATIC);
        } // if

        return $this->retrieve($l_sql . ' GROUP BY isys_property_2_cat__isysgui_cat' . $p_category_type . '__id;');
    } // function

    /**
     * Method which renews the property_2_cat table
     *
     * @return mixed
     * @author Van Quyen Hoang <qhoang@i-doit.org>
     */
    public function rebuild_properties()
    {
        $l_log = isys_log_migration::get_instance();

        if ($l_log->get_log_file() == '')
        {
            $l_log_file = isys_application::instance()->app_path . '/log/prop_' . date('Y-m-d_H_i_s', time()) . '.txt';
            $l_log->set_log_file($l_log_file);
        } // if

        $l_log->set_log_level(isys_log::C__ALL);

        $l_upd_prop = isys_factory::get_instance('isys_update_property_migration');

        $l_result = $l_upd_prop->set_database(isys_application::instance()->database)
            ->reset_property_table()
            ->collect_category_data()
            ->prepare_sql_queries('g')
            ->prepare_sql_queries('s')
            ->prepare_sql_queries('g_custom')
            ->execute_sql()
            ->get_results();

        $l_log->flush_log();

        return $l_result;
    } // function

    /**
     * Retrieves data by property chain.
     *
     * @param   integer $p_obj_id
     * @param   string  $p_chain
     *
     * @return  mixed
     * @author  Van Quyen Hoang <qhoang@i-doit.org>
     */
    public function retrieve_chained_obj_id($p_obj_id, $p_chain)
    {
        $l_return       = null;
        $l_assigned_key = null;
        $l_selects      = null;
        $l_joins        = [];
        $l_sub_joins    = [];
        $this->reset();
        if (strpos($p_chain, '--') !== false)
        {
            // more than one level chain
            $l_chain_arr      = explode('--', $p_chain);
            $l_obj_lvl_select = $l_obj_select = [];
            foreach ($l_chain_arr AS $l_key => $l_ref_key)
            {
                list($l_category_const, $l_prop_key) = explode('-', $l_ref_key);

                if (!isset($this->m_property_rows[$l_ref_key]))
                {
                    $l_condition = ' AND isys_property_2_cat__cat_const = ' . $this->convert_sql_text(
                            $l_category_const
                        ) . ' AND isys_property_2_cat__prop_key = ' . $this->convert_sql_text($l_prop_key);
                    $l_row       = $this->retrieve_properties(null, null, null, null, $l_condition)
                        ->get_row();

                    $l_cat_dao     = $this->get_dao_instance($l_row['class'], ($l_row['catg_custom'] ?: null));
                    $l_properties  = $l_cat_dao->get_properties();
                    $l_row['data'] = $l_properties[$l_row['key']];

                    $this->m_property_rows[$l_row['id']] = $l_row;
                    $this->m_property_rows[$l_ref_key]   = $l_row['id'];
                }
                else
                {
                    $l_row = $this->m_property_rows[$this->m_property_rows[$l_ref_key]];
                } // if

                if ($l_key == 0 && $l_assigned_key === null)
                {
                    $l_prop_data['data'] = $l_row['data'];

                    $this->create_alias($l_prop_data);
                    $l_selects      = $this->create_property_query_select([$l_row['id']]);
                    $l_obj_select   = str_replace('title', 'id', $l_selects[$l_row['id']]);
                    $l_joins        = $this->create_property_query_join([$l_row['id']]);
                    $l_assigned_key = $l_row['id'];
                }
                else
                {
                    $l_lvls_arr = [
                        $l_assigned_key => isys_format_json::encode([$l_row['id']])
                    ];

                    $l_condition_field = substr($l_obj_select, 0, strpos($l_obj_select, ' '));

                    $l_lvls_select = $this->create_property_query_lvls_select($l_lvls_arr, $l_selects);
                    $l_sub_joins[] = array_pop($this->create_property_query_join_lvls($this->m_property_order, $l_selects, $l_obj_lvl_select, false, $l_condition_field));
                    $l_assigned_key .= '--' . $l_row['id'];

                    $l_obj_select = array_pop($l_lvls_select);
                }
            }

            if (count($l_sub_joins) > 0)
            {
                foreach ($l_sub_joins AS $l_lvl_join)
                {
                    $l_joins = array_merge($l_joins, $l_lvl_join);
                }
            }
        }
        else
        {
            list($l_category_const, $l_prop_key) = explode('-', $p_chain);

            if (!isset($this->m_property_rows[$p_chain]))
            {
                $l_condition = ' AND isys_property_2_cat__cat_const = ' . $this->convert_sql_text(
                        $l_category_const
                    ) . ' AND isys_property_2_cat__prop_key = ' . $this->convert_sql_text($l_prop_key);
                $l_row       = $this->retrieve_properties(null, null, null, null, $l_condition)
                    ->get_row();

                $l_cat_dao                           = $this->get_dao_instance($l_row['class'], ($l_row['catg_custom'] ?: null));
                $l_properties                        = $l_cat_dao->get_properties();
                $l_row['data']                       = $l_properties[$l_row['key']];
                $this->m_property_rows[$l_row['id']] = $l_row;
                $this->m_property_rows[$p_chain]     = $l_row['id'];
            }
            else
            {
                $l_row = $this->m_property_rows[$this->m_property_rows[$p_chain]];
            } // if

            $l_prop_data['data'] = $l_row['data'];

            $this->create_alias($l_prop_data);
            $l_selects = $this->create_property_query_select([$l_row['id']]);

            $l_obj_select = str_replace('title', 'id', $l_selects[$l_row['id']]);
            $l_joins      = $this->create_property_query_join([$l_row['id']]);
        } // if

        $l_sql = "SELECT \n" . $l_obj_select . " \n\n" . "FROM isys_obj AS obj_main \n" . ((count($l_joins) > 0) ? implode(
                " \n",
                $l_joins
            ) : '') . " \n\n" . "WHERE TRUE \n" . "AND obj_main.isys_obj__id = " . $this->convert_sql_id($p_obj_id);

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

            if (count($l_res) > 0)
            {
                $l_return = [];

                while ($l_obj_row = $l_res->get_row())
                {
                    $l_return[] = array_pop($l_obj_row);
                } // while
            } // if
        }
        catch (Exception $e)
        {
            isys_notify::error('An Error occurred: ' . $e->getMessage() . ' File: ' . $e->getFile());
        } // try

        return $l_return;
    } // function

    /**
     * Method for creating an alias to the given properties.
     *
     * @param   array $p_props
     *
     * @author  Leonard Fischer <lfischer@i-doit.org>
     * @todo    Change the alias namespaces to "a_table.a_column-b_table.b_column => counter".
     * @todo    Also change the parameters to "$p_from" and "$p_to".
     */
    protected function create_alias(array $p_props)
    {
        if ($p_props['type'] == C__PROPERTY_TYPE__DYNAMIC)
        {
            $l_table = $p_props['table'];
        }
        else
        {
            $l_table = current(explode('__', $p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD]));
        }

        if(isset($p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__JOIN]))
        {
            if(count($p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__JOIN]))
            {
                $l_last_table = '';
                /**
                 * @var $l_selectJoin \idoit\Module\Report\SqlQuery\Structure\SelectJoin
                 */
                foreach($p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__JOIN] AS $l_selectJoin)
                {
                    $l_join_table = $l_selectJoin->getTable();
                    if($l_selectJoin->getRefTable())
                    {
                        $l_last_table = $l_selectJoin->getRefTable();
                    }

                    if (!isset($this->m_aliases[$l_join_table . '#isys_obj']))
                    {
                        $this->m_aliases[$l_join_table . '#isys_obj'] = $this->m_alias_cnt;
                        $this->m_alias_cnt++;
                    }

                    if ($l_last_table != '' && !isset($this->m_aliases[$l_join_table . '#' . $l_last_table]))
                    {
                        $this->m_aliases[$l_join_table . '#' . $l_last_table] = $this->m_alias_cnt;
                        //$this->m_aliases[$l_join_table . '#' . $l_last_table . '#' . $p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD]] = $this->m_alias_cnt;
                        $this->m_alias_cnt++;
                    } // if

                    $l_last_table = $l_join_table;
                }
                $this->m_property_order[] = $p_props['id'];
                return;
            }
        }

        if ($l_table == 'isys_catg_custom_fields_list')
        {
            $this->m_aliases[$p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD_ALIAS] . '#isys_catg_custom_fields_list#isys_obj'] = $this->m_alias_cnt;
            $this->m_alias_cnt++;

            if (isset($p_props['data'][C__PROPERTY__UI][C__PROPERTY__UI__TYPE]) &&
                ($p_props['data'][C__PROPERTY__UI][C__PROPERTY__UI__TYPE] == 'f_popup' || $p_props['data'][C__PROPERTY__UI][C__PROPERTY__UI__TYPE] == 'popup'))
            {
                if ($p_props['data'][C__PROPERTY__INFO][C__PROPERTY__INFO__TYPE] == C__PROPERTY__INFO__TYPE__OBJECT_BROWSER)
                {
                    if (!isset($this->m_aliases[$p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD_ALIAS] . '#isys_obj#isys_catg_custom_fields_list']))
                    {
                        $this->m_aliases[$p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD_ALIAS] . '#isys_obj#isys_catg_custom_fields_list'] = $this->m_alias_cnt;
                        $this->m_alias_cnt++;
                    } // if
                }
                else
                {
                    if (!isset($this->m_aliases[$p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD_ALIAS] . '#isys_dialog_plus_custom#isys_catg_custom_fields_list']))
                    {
                        $this->m_aliases[$p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD_ALIAS] . '#isys_dialog_plus_custom#isys_catg_custom_fields_list'] = $this->m_alias_cnt;
                        $this->m_alias_cnt++;
                    } // if
                } // if
            } // if
            $this->m_property_order[] = $p_props['id'];
            return;
        }
        elseif (isset($p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__SELECT]))
        {
            if($p_props['data'][C__PROPERTY__PROVIDES][C__PROPERTY__PROVIDES__VIRTUAL] === true) return;

            /**
             * @var $l_selectSubSelect idoit\Module\Report\SqlQuery\Structure\SelectSubSelect
             */
            $l_selectSubSelect = clone $p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__SELECT];
            $l_join_table = $l_selectSubSelect->getSelectTable();

            if($l_join_table)
            {
                if (!isset($this->m_aliases[$l_join_table . '#isys_obj']))
                {
                    $this->m_aliases[$l_join_table . '#isys_obj'] = $this->m_alias_cnt;
                    $this->m_alias_cnt++;
                }
            }
        }
        elseif ($p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__REFERENCES][0] !== null && strpos(
                ' ' . $p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__REFERENCES][0],
                'isys_'
            ) && !isset($this->m_aliases[$p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__REFERENCES][0] . '#' . $l_table])
        )
        {
            if ($l_table == 'isys_logbook')
            {
                if (!isset($this->m_aliases['isys_catg_logb_list#isys_obj']))
                {
                    $this->m_aliases['isys_catg_logb_list#isys_obj'] = $this->m_alias_cnt;
                    $this->m_alias_cnt++;
                    $this->m_aliases['isys_logbook#isys_catg_logb_list'] = $this->m_alias_cnt;
                    $this->m_alias_cnt++;
                } // if
            } // if

            if (!isset($this->m_aliases[$p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__REFERENCES][0] . '#' . $l_table]))
            {
                $this->m_aliases[$p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__REFERENCES][0] . '#' . $l_table] = $this->m_alias_cnt;
                $this->m_alias_cnt++;
            } // if

            if ($p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__REFERENCES][0] == 'isys_contact')
            {
                if (!isset($this->m_aliases['isys_contact_2_isys_obj#' . $p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__REFERENCES][0]]))
                {
                    $this->m_aliases['isys_contact_2_isys_obj#' . $p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__REFERENCES][0]] = $this->m_alias_cnt;
                    $this->m_alias_cnt++;
                } // if
                if (!isset($this->m_aliases['isys_contact_2_isys_obj#isys_obj']))
                {
                    $this->m_aliases['isys_contact_2_isys_obj#isys_obj'] = $this->m_alias_cnt;
                    $this->m_alias_cnt++;
                } // if
            } // if

            if (((strpos($p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__REFERENCES][0], 'catg') !== false || strpos(
                        $p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__REFERENCES][0],
                        'cats'
                    ) !== false) && strpos($p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__REFERENCES][0], '_list') !== false)
            )
            {
                if (!isset($this->m_aliases[$p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__REFERENCES][0] . '#isys_obj']))
                {
                    $this->m_aliases[$p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__REFERENCES][0] . '#isys_obj'] = $this->m_alias_cnt;
                    $this->m_alias_cnt++;
                }
                if (!isset($this->m_aliases['isys_obj#' . $p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__REFERENCES][0]]))
                {
                    $this->m_aliases['isys_obj#' . $p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__REFERENCES][0]] = $this->m_alias_cnt;
                    $this->m_alias_cnt++;
                }
            }
            $this->m_property_order[] = $p_props['id'];
            if ($l_table == 'isys_logbook') return;
        }
        elseif ($l_table == 'isys_catg_logb_list' || $l_table == 'isys_logbook')
        {
            if (!isset($this->m_aliases['isys_catg_logb_list#isys_obj']))
            {
                $this->m_aliases['isys_catg_logb_list#isys_obj'] = $this->m_alias_cnt;
                $this->m_alias_cnt++;
                $this->m_aliases['isys_logbook#isys_catg_logb_list'] = $this->m_alias_cnt;
                $this->m_alias_cnt++;
                $this->m_property_order[] = $p_props['id'];
            } // if

            if ($l_table == 'isys_logbook') return;
        }
        elseif ($p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD] == $l_table . '__isys_obj__id')
        {
            if ($l_table == 'isys_cats_net_ip_addresses_list')
            {
                if (!isset($this->m_aliases['isys_catg_ip_list#isys_obj']))
                {
                    $this->m_aliases['isys_catg_ip_list#isys_obj'] = $this->m_alias_cnt;
                    $this->m_alias_cnt++;
                    $this->m_aliases['isys_cats_net_ip_addresses_list#isys_catg_ip_list'] = $this->m_alias_cnt;
                    $this->m_alias_cnt++;
                    $this->m_aliases['isys_obj#isys_cats_net_ip_addresses_list'] = $this->m_alias_cnt;
                    $this->m_alias_cnt++;
                    $this->m_property_order[] = $p_props['id'];
                    return;
                } // if
            } // if
        }
        elseif (strpos($p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD], 'isys_cable_connection__id') !== false)
        {
            if (!isset($this->m_aliases[$l_table . '#isys_cable_connection']))
            {
                $this->m_aliases[$l_table . '#isys_cable_connection'] = $this->m_alias_cnt;
                $this->m_alias_cnt++;
            } // if
            if (!isset($this->m_aliases['isys_cable_connection#' . $l_table]))
            {
                $this->m_aliases['isys_cable_connection#' . $l_table] = $this->m_alias_cnt;
                $this->m_alias_cnt++;
            } // if
            if (!isset($this->m_aliases['isys_obj#isys_cable_connection']))
            {
                $this->m_aliases['isys_obj#isys_cable_connection'] = $this->m_alias_cnt;
                $this->m_alias_cnt++;
            } // if
            if (!isset($this->m_aliases['isys_cable_connection#isys_obj']))
            {
                $this->m_aliases['isys_cable_connection#isys_obj'] = $this->m_alias_cnt;
                $this->m_alias_cnt++;
            } // if
        }
        elseif (strpos($p_props['data'][C__PROPERTY__DATA][C__PROPERTY__DATA__FIELD], 'isys_catg_connector_list__id') !== false)
        {
            if ($l_table !== 'isys_catg_connector_list' && !isset($this->m_aliases[$l_table . '#isys_catg_connector_list#isys_obj']))
            {
                $this->m_aliases[$l_table . '#isys_catg_connector_list#isys_catg_connector_list'] = $this->m_alias_cnt;
                $this->m_alias_cnt++;
                $this->m_aliases[$l_table . '#isys_catg_connector_list#isys_obj'] = $this->m_alias_cnt;
                $this->m_alias_cnt++;
            } // if
            if (!isset($this->m_aliases[$l_table . '#isys_catg_connector_list']))
            {
                $this->m_aliases[$l_table . '#isys_catg_connector_list'] = $this->m_alias_cnt;
                $this->m_alias_cnt++;
            } // if
            if (!isset($this->m_aliases['isys_catg_connector_list#' . $l_table]))
            {
                $this->m_aliases['isys_catg_connector_list#' . $l_table] = $this->m_alias_cnt;
                $this->m_alias_cnt++;
            } // if
            if (!isset($this->m_aliases['isys_catg_connector_list#isys_catg_connector_list']))
            {
                $this->m_aliases['isys_catg_connector_list#isys_catg_connector_list'] = $this->m_alias_cnt;
                $this->m_alias_cnt++;
            } // if
            if (!isset($this->m_aliases['main_obj#' . $l_table]))
            {
                $this->m_aliases['main_obj#' . $l_table] = $this->m_alias_cnt;
                $this->m_alias_cnt++;
            } // if
            if (!isset($this->m_aliases[$l_table . '#main_obj']))
            {
                $this->m_aliases[$l_table . '#main_obj'] = $this->m_alias_cnt;
                $this->m_alias_cnt++;
            } // if
            if (!isset($this->m_aliases['isys_obj#isys_catg_connector_list']))
            {
                $this->m_aliases['isys_obj#isys_catg_connector_list'] = $this->m_alias_cnt;
                $this->m_alias_cnt++;
            } // if
            if (!isset($this->m_aliases['isys_catg_connector_list#isys_obj']))
            {
                $this->m_aliases['isys_catg_connector_list#isys_obj'] = $this->m_alias_cnt;
                $this->m_alias_cnt++;
            } // if
        } // if

        if (!isset($this->m_aliases[$l_table . '#isys_obj']))
        {
            $this->m_aliases[$l_table . '#isys_obj'] = $this->m_alias_cnt;
            $this->m_alias_cnt++;
        } // if

        if (!isset($this->m_aliases['isys_obj#' . $l_table]))
        {
            $this->m_aliases['isys_obj#' . $l_table] = $this->m_alias_cnt;
            $this->m_alias_cnt++;
        } // if

        if(!in_array($p_props['id'], $this->m_property_order)) $this->m_property_order[] = $p_props['id'];
    } // function

    /**
     * This method prepares all the data, which are needed for selects, joins and conditions.
     *
     * @param   array $p_property_ids
     *
     * @return  isys_cmdb_dao_category_property_ng
     */
    protected function prepare_necessary_tasks(array $p_property_ids)
    {
        if (count($p_property_ids) > 0)
        {
            // This query will be used to receive all the necessary entries from the isys_property_2_cat table.
            $l_res = $this->retrieve_properties($p_property_ids, null, null, null, "", true);

            // First we get all the needed data from the isys_property_2_cat table.
            while ($l_row = $l_res->get_row())
            {
                $l_row['prop_count'] = 0;
                $l_cat_dao        = $this->get_dao_instance($l_row['class'], ($l_row['catg_custom'] ?: null));
                $l_cat_properties = $l_cat_dao->get_properties();
                $l_properties     = array_merge($l_cat_properties, $l_cat_dao->get_dynamic_properties());
                $l_row['data']    = $l_properties[$l_row['key']];

                $l_table_arr = explode('_', $l_row['table']);

                if (array_pop($l_table_arr) !== 'list')
                {
                    $l_row['table'] = $l_row['table'] . '_list';
                } // if

                if($l_row['table'] == 'isys_catg_virtual_list')
                {
                    $l_row['table'] = ($l_cat_dao->get_table()) ?: $l_cat_dao->get_source_table();
                } // if

                foreach($l_cat_properties AS $l_prop)
                {
                    if($l_prop[C__PROPERTY__PROVIDES][C__PROPERTY__PROVIDES__REPORT])
                    {
                        $l_row['prop_count']++;
                    } // if
                } // foreach

                // We save every row, because we will need them quite often in the upcoming code.
                $this->m_property_rows[$l_row['id']] = $l_row;

                // Also we create table aliases for each possible join.
                $this->create_alias($l_row);
            } // while
            // Ordered properties
            /*if(isset($this->m_property_order['joins']))
            {
                $l_joins = $this->m_property_order['joins'];
                unset($this->m_property_order['joins']);
                $this->m_property_order = array_merge($l_joins, $this->m_property_order);
            }*/
        } // if

        // Also we save some information, so that the logic will not try to join the "isys_obj" or "isys_cmdb_status" tables.
        $this->m_aliases['isys_obj#isys_obj']         = $this->m_alias_cnt++;

        $this->m_prepared_data_for_query_construction = true;

        return $this;
    } // function

    /**
     * Method for retrieving the properties of every category dao.
     *
     * @return  array
     * @author  Dennis Stücken <dstuecken@i-doit.de>
     */
    protected function properties()
    {
        return [];
    } // function

    /**
     * Method for retrieving an previously created alias.
     *
     * @param   string $p_table
     * @param   string $p_ref
     *
     * @return  integer
     * @author  Leonard Fischer <lfischer@i-doit.org>
     * @todo    Change the alias namespaces to "a_table.a_column-b_table.b_column => counter"
     * @todo    Also change the parameters to "$p_from" and "$p_to".
     */
    protected function retrieve_alias($p_table, $p_ref = null)
    {
        if ($p_ref === null)
        {
            return $this->m_aliases[$p_table . '#isys_obj'];
        }
        else
        {
            return $this->m_aliases[$p_ref . '#' . $p_table];
        } // if
    } // function

    /**
     * Method for quick and easy ui-type decision.
     *
     * @param   array $p_property
     *
     * @return  string
     * @author  Leonard Fischer <lfischer@i-doit.org>
     */
    protected function retrieve_ui_type(array $p_property)
    {
        $l_prop_ui = $p_property[C__PROPERTY__UI];
        if ($l_prop_ui[C__PROPERTY__UI__TYPE] == C__PROPERTY__UI__TYPE__DIALOG)
        {
            return C__PROPERTY__UI__TYPE__DIALOG;
        }
        else if ($l_prop_ui[C__PROPERTY__UI__TYPE] == C__PROPERTY__UI__TYPE__POPUP && $l_prop_ui[C__PROPERTY__UI__PARAMS]['p_strPopupType'] == 'dialog_plus')
        {
            return C__PROPERTY__UI__TYPE__DIALOG;
        }
        else if ($l_prop_ui[C__PROPERTY__UI__TYPE] == C__PROPERTY__UI__TYPE__POPUP && $l_prop_ui[C__PROPERTY__UI__PARAMS]['p_strPopupType'] == 'object_browser_ng')
        {
            // We assume "popup" = "object browser".
            return C__PROPERTY__UI__TYPE__POPUP;
        } // if

        return null;
    } // function

    /**
     * Retrieves property id by condition
     *
     * @param string $p_condition
     *
     * @return mixed
     * @throws isys_exception_database
     * @author   Van Quyen Hoang <qhoang@i-doit.com>
     */
    public function get_property_id_by_condition($p_condition = 'TRUE')
    {
        $l_sql = 'SELECT isys_property_2_cat__id FROM isys_property_2_cat
          LEFT JOIN isysgui_catg ON isysgui_catg__id = isys_property_2_cat__isysgui_catg__id
          LEFT JOIN isysgui_cats ON isysgui_cats__id = isys_property_2_cat__isysgui_cats__id
          LEFT JOIN isysgui_catg_custom ON isysgui_catg_custom__id = isys_property_2_cat__isysgui_catg_custom__id
          WHERE ' . $p_condition . ' LIMIT 1;';
        return $this->retrieve($l_sql)->get_row_value('isys_property_2_cat__id');
    } // function
} // class