<?php

use idoit\Component\Property\Type\DialogPlusProperty;
use idoit\Component\Property\Type\DialogProperty;
use idoit\Component\Property\Type\DialogYesNoProperty;
use idoit\Component\Property\Type\DynamicProperty;
use idoit\Component\Property\Type\ObjectBrowserProperty;
use idoit\Component\Property\Property;
use idoit\Module\Report\SqlQuery\Structure\SelectGroupBy;
use idoit\Module\Report\SqlQuery\Structure\SelectSubSelect;

/**
 * i-doit
 *
 * DAO: global category for SIM cards
 *
 * @package     i-doit
 * @subpackage  CMDB_Categories
 * @copyright   synetics GmbH
 * @license     http://www.i-doit.com/license
 */
class isys_cmdb_dao_category_g_cards extends isys_cmdb_dao_category_global
{
    /**
     * Category's name. Will be used for the identifier, constant, main table,
     * and many more.
     *
     * @var string
     */
    protected $m_category = 'cards';

    /**
     * Category entry is purgable
     *
     * @var bool
     */
    protected $m_is_purgable = true;

    /**
     * @var bool
     */
    protected $m_multivalued = true;

    /**
     * Dynamic property handling for retrieving the object ID.
     *
     * @param   array $row
     *
     * @return  string
     */
    public function dynamic_property_callback_assigned_mobile(array $row)
    {
        $return = '';

        $dao = self::instance(isys_application::instance()->container->get('database'));

        $entryRow = $dao->get_data(null, $row['isys_obj__id'])->get_row();

        if ($entryRow !== false && $entryRow['isys_catg_assigned_cards_list__isys_obj__id'] > 0) {
            $cellphoneData = $dao->get_object_by_id($entryRow['isys_catg_assigned_cards_list__isys_obj__id'])->get_row();

            $return = (new isys_ajax_handler_quick_info())->get_quick_info(
                $cellphoneData['isys_obj__id'],
                isys_application::instance()->container->get('language')->get($cellphoneData['isys_obj_type__title']) . ' &raquo; ' . $cellphoneData['isys_obj__title'],
                C__LINK__OBJECT
            );
        }

        return $return;
    }

    /**
     * @param  array $categoryData
     *
     * @return mixed
     * @throws isys_exception_dao
     */
    public function create_data($categoryData)
    {
        // Remember the assigned mobile and unset it, since the generic logic will produce an SQL error.
        $assignedMobile = $categoryData['assigned_mobile'];

        unset($categoryData['assigned_mobile']);

        $returnValue = parent::create_data($categoryData);

        if (is_numeric($returnValue)) {
            // After saving the data, proceed with the assigned mobile.
            $assignedCardsDao = isys_cmdb_dao_category_g_assigned_cards::instance($this->get_database_component());

            $objectId = $this->get_data($returnValue)->get_row_value('isys_catg_cards_list__isys_obj__id');

            if ($objectId > 0 && $assignedMobile > 0) {
                $this->removeComponent($returnValue);
                $this->addComponent($returnValue, $assignedMobile);
            }
        }

        return $returnValue;
    }

    /**
     * @param  int   $categoryEntryId
     * @param  array $categoryData
     *
     * @return bool
     * @throws isys_exception_dao
     * @throws isys_exception_dao_cmdb
     */
    public function save_data($categoryEntryId, $categoryData)
    {
        // Remember the assigned mobile and unset it, since the generic logic will produce an SQL error.
        $assignedMobile = $categoryData['assigned_mobile'];

        unset($categoryData['assigned_mobile']);

        $returnValue = parent::save_data($categoryEntryId, $categoryData);

        // After saving the data, proceed with the assigned mobile.
        $assignedCardsDao = isys_cmdb_dao_category_g_assigned_cards::instance($this->get_database_component());

        $objectId = $this->get_data($categoryEntryId)->get_row_value('isys_catg_cards_list__isys_obj__id');

        if ($objectId > 0) {
            $this->removeComponent($categoryEntryId);

            if ($assignedMobile > 0) {
                $this->addComponent($categoryEntryId, $assignedMobile);
            }
        }

        return $returnValue;
    }

    /**
     * @param int|null $cardId
     * @param int|null $objectId
     *
     * @return bool
     * @throws isys_exception_dao
     */
    public function removeComponent($cardId = null, $objectId = null)
    {
        if ($cardId === null && $objectId === null) {
            return false;
        }

        $delete = 'DELETE FROM isys_catg_cards_list_2_isys_obj WHERE %s;';

        if ($cardId !== null) {
            $condition[] = 'isys_catg_cards_list__id = ' . $this->convert_sql_int($cardId);
        }

        if ($objectId !== null) {
            $condition[] = ' isys_obj__id = ' . $this->convert_sql_int($objectId);
        }

        $delete = sprintf($delete, (implode(' AND ', $condition)));

        return $this->update($delete) && $this->apply_update();
    }

    /**
     * @param int $cardId
     * @param int $objectId
     *
     * @return bool
     * @throws isys_exception_dao
     */
    public function addComponent($cardId, $objectId)
    {
        $insert = 'INSERT INTO isys_catg_cards_list_2_isys_obj SET 
            isys_catg_cards_list_2_isys_obj__status = ' . $this->convert_sql_int(defined_or_default('C__RECORD_STATUS__NORMAL')) . ',
            isys_catg_cards_list__id = ' . $this->convert_sql_int($cardId) . ', 
            isys_obj__id = ' . $this->convert_sql_int($objectId);

        return $this->update($insert) && $this->apply_update();
    }


    /**
     * Return Category Data.
     *
     * @param  integer $p_catg_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_catg_list_id = null, $p_obj_id = null, $p_condition = '', $p_filter = null, $p_status = null)
    {
        $sql = 'SELECT * FROM isys_catg_cards_list 
            LEFT OUTER JOIN isys_catg_assigned_cards_list ON isys_catg_assigned_cards_list__isys_obj__id__card = isys_catg_cards_list__isys_obj__id 
            INNER JOIN isys_obj ON isys_obj__id = isys_catg_cards_list__isys_obj__id 
            WHERE TRUE ' . $p_condition . ' ' . $this->prepare_filter($p_filter);

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

        if ($p_catg_list_id !== null) {
            $sql .= ' AND isys_catg_cards_list__id = ' . $this->convert_sql_id($p_catg_list_id);
        }

        if ($p_status !== null) {
            $sql .= ' AND isys_catg_cards_list__status = ' . $this->convert_sql_int($p_status);
        }

        return $this->retrieve($sql . ';');
    }

    /**
     * Get Simcard by its title
     *
     * @param int       $objectId
     * @param string    $cardTitle
     *
     * @return int|null;
     */
    public function getSimcardByTitle($objectId, $cardTitle)
    {
        // Check for correct parameters
        if (empty($objectId) || empty($cardTitle)) {
            throw new \idoit\Exception\Exception('Please provide parameters');
        }

        return $this->get_data(
            null,
            $objectId,
            ' AND (isys_catg_cards_list__title = ' . $this->convert_sql_text($cardTitle) . ')'
        )->get_row_value('isys_catg_cards_list__id');
    }

    /**
     * Method for returning the properties.
     *
     * @return  array
     */
    protected function properties()
    {
        return [
            'assigned_mobile' => (new ObjectBrowserProperty(
                'C__CMDB__CATS__SIM_CARD__ASSIGNED_MOBILE_PHONE',
                'LC__CMDB__CATS__SIM_CARD__ASSIGNED_MOBILE_PHONE',
                'isys_catg_cards_list__id',
                'isys_catg_cards_list',
                []
            ))->mergePropertyProvides([
                C__PROPERTY__PROVIDES__REPORT     => true,
                C__PROPERTY__PROVIDES__MULTIEDIT  => true,
                C__PROPERTY__PROVIDES__LIST       => true,
                C__PROPERTY__PROVIDES__VIRTUAL    => false,
                C__PROPERTY__PROVIDES__IMPORT     => true
            ])->mergePropertyData([
                C__PROPERTY__DATA__FIELD  => 'isys_catg_cards_list__id',
                // Because its a multi object browser we don´t need to declare reference in the selection
                C__PROPERTY__DATA__SELECT => idoit\Module\Report\SqlQuery\Structure\SelectSubSelect::factory(
                    'SELECT CONCAT(obj.isys_obj__title, \' {\', obj.isys_obj__id, \'}\')
                                FROM isys_catg_cards_list AS main
                                INNER JOIN isys_catg_cards_list_2_isys_obj AS log2obj ON log2obj.isys_catg_cards_list__id = main.isys_catg_cards_list__id
                                INNER JOIN isys_obj AS obj ON obj.isys_obj__id = log2obj.isys_obj__id',
                    'isys_catg_cards_list',
                    'main.isys_catg_cards_list__id',
                    'main.isys_catg_cards_list__isys_obj__id',
                    '',
                    '',
                    idoit\Module\Report\SqlQuery\Structure\SelectCondition::factory([]),
                    idoit\Module\Report\SqlQuery\Structure\SelectGroupBy::factory(['main.isys_catg_cards_list__isys_obj__id'])
                ),
                C__PROPERTY__DATA__JOIN => [
                    idoit\Module\Report\SqlQuery\Structure\SelectJoin::factory(
                        'isys_catg_cards_list',
                        'INNER',
                        'isys_catg_cards_list__isys_obj__id',
                        'isys_obj__id'
                    ),
                    idoit\Module\Report\SqlQuery\Structure\SelectJoin::factory(
                        'isys_catg_cards_list_2_isys_obj',
                        'INNER',
                        'isys_catg_cards_list__id',
                        'isys_catg_cards_list__id'
                    ),
                    idoit\Module\Report\SqlQuery\Structure\SelectJoin::factory(
                        'isys_obj',
                        'INNER',
                        'isys_obj__id',
                        'isys_obj__id'
                    ),
                ],
            ])->mergePropertyUi([
                C__PROPERTY__UI__ID     => 'C__CATG__CARDS__ASSIGNED_MOBILE_PHONE',
                C__PROPERTY__UI__PARAMS => [
                    'p_strPopupType' => 'browser_object_ng',
                    'catFilter'     => 'C__CATG__ASSIGNED_SIM_CARDS',
                    'p_strValue'     => new isys_callback([
                        'isys_cmdb_dao_category_g_cards',
                        'callback_property_assigned_mobile'
                    ])
                ]
            ])->mergePropertyFormat([
                C__PROPERTY__FORMAT__CALLBACK => [
                    'isys_export_helper',
                    'object'
                ]
            ]),
            'card_no'          => array_replace_recursive(isys_cmdb_dao_category_pattern::text(), [
                C__PROPERTY__INFO => [
                    C__PROPERTY__INFO__TITLE       => 'LC__CMDB__CATS_CP_CONTRACT__CARD_NUMBER',
                    C__PROPERTY__INFO__DESCRIPTION => 'LC__CMDB__CATS_CP_CONTRACT__CARD_NUMBER'
                ],
                C__PROPERTY__DATA => [
                    C__PROPERTY__DATA__FIELD => 'isys_catg_cards_list__card_number'
                ],
                C__PROPERTY__UI   => [
                    C__PROPERTY__UI__ID => 'C__CATS__CP_CONTRACT__CARD_NUMBER'
                ]
            ]),
            'title'              => array_replace_recursive(isys_cmdb_dao_category_pattern::text(), [
                C__PROPERTY__INFO => [
                    C__PROPERTY__INFO__TITLE       => 'LC__CMDB__CATG__CARDS__TITLE',
                    C__PROPERTY__INFO__DESCRIPTION => 'LC__CMDB__CATG__CARDS__TITLE'
                ],
                C__PROPERTY__DATA => [
                    C__PROPERTY__DATA__FIELD => 'isys_catg_cards_list__title'
                ],
                C__PROPERTY__UI   => [
                    C__PROPERTY__UI__ID => 'C__CATG__CARDS__TITLE'
                ]
            ]),
            'pin'              => array_replace_recursive(isys_cmdb_dao_category_pattern::text(), [
                C__PROPERTY__INFO => [
                    C__PROPERTY__INFO__TITLE       => 'LC__CMDB__CATS_CP_CONTRACT__PIN',
                    C__PROPERTY__INFO__DESCRIPTION => 'LC__CMDB__CATS_CP_CONTRACT__PIN'
                ],
                C__PROPERTY__DATA => [
                    C__PROPERTY__DATA__FIELD => 'isys_catg_cards_list__pin'
                ],
                C__PROPERTY__UI   => [
                    C__PROPERTY__UI__ID => 'C__CATS__CP_CONTRACT__PIN'
                ]
            ]),
            'pin2'             => array_replace_recursive(isys_cmdb_dao_category_pattern::text(), [
                C__PROPERTY__INFO => [
                    C__PROPERTY__INFO__TITLE       => 'LC__CMDB__CATS_CP_CONTRACT__PIN2',
                    C__PROPERTY__INFO__DESCRIPTION => 'LC__CMDB__CATS_CP_CONTRACT__PIN2'
                ],
                C__PROPERTY__DATA => [
                    C__PROPERTY__DATA__FIELD => 'isys_catg_cards_list__pin2'
                ],
                C__PROPERTY__UI   => [
                    C__PROPERTY__UI__ID => 'C__CATS__CP_CONTRACT__PIN2'
                ]
            ]),
            'puk'              => array_replace_recursive(isys_cmdb_dao_category_pattern::text(), [
                C__PROPERTY__INFO => [
                    C__PROPERTY__INFO__TITLE       => 'LC__CMDB__CATS_CP_CONTRACT__PUK',
                    C__PROPERTY__INFO__DESCRIPTION => 'LC__CMDB__CATS_CP_CONTRACT__PUK'
                ],
                C__PROPERTY__DATA => [
                    C__PROPERTY__DATA__FIELD => 'isys_catg_cards_list__puk'
                ],
                C__PROPERTY__UI   => [
                    C__PROPERTY__UI__ID => 'C__CATS__CP_CONTRACT__PUK'
                ]
            ]),
            'puk2'             => array_replace_recursive(isys_cmdb_dao_category_pattern::text(), [
                C__PROPERTY__INFO => [
                    C__PROPERTY__INFO__TITLE       => 'LC__CMDB__CATS_CP_CONTRACT__PUK2',
                    C__PROPERTY__INFO__DESCRIPTION => 'LC__CMDB__CATS_CP_CONTRACT__PUK2'
                ],
                C__PROPERTY__DATA => [
                    C__PROPERTY__DATA__FIELD => 'isys_catg_cards_list__puk2'
                ],
                C__PROPERTY__UI   => [
                    C__PROPERTY__UI__ID => 'C__CATS__CP_CONTRACT__PUK2'
                ]
            ]),
            'serial'           => array_replace_recursive(isys_cmdb_dao_category_pattern::text(), [
                C__PROPERTY__INFO => [
                    C__PROPERTY__INFO__TITLE       => 'LC__CMDB__CATG__SERIAL',
                    C__PROPERTY__INFO__DESCRIPTION => 'LC__CMDB__CATG__SERIAL'
                ],
                C__PROPERTY__DATA => [
                    C__PROPERTY__DATA__FIELD => 'isys_catg_cards_list__serial_number'
                ],
                C__PROPERTY__UI   => [
                    C__PROPERTY__UI__ID => 'C__CATS__CP_CONTRACT__SERIAL_NUMBER'
                ]
            ]),
            '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 => 'LC__CMDB__LOGBOOK__DESCRIPTION'
                ],
                C__PROPERTY__DATA => [
                    C__PROPERTY__DATA__FIELD => 'isys_catg_cards_list__description'
                ],
                C__PROPERTY__UI   => [
                    C__PROPERTY__UI__ID => 'C__CMDB__CAT__COMMENTARY_' . C__CMDB__CATEGORY__TYPE_GLOBAL . defined_or_default('C__CATG__CARDS', 'C__CATG__CARDS')
                ]
            ])
        ];
    }

    /**
     * Callback method for the multiselection object-browser.
     *
     * @global  isys_component_database $g_comp_database
     *
     * @param   isys_request            $p_request
     *
     * @return  array
     */
    public function callback_property_assigned_mobile(isys_request $p_request)
    {
        global $g_comp_database;

        $l_return = [];

        $dao = isys_cmdb_dao_category_g_cards::instance($g_comp_database);

        $l_cat_id = $p_request->get_category_data_id();

        if ($l_cat_id > 0) {
            $l_res = $dao->get_assigned_mobile($l_cat_id, null, false, true);

            while ($l_row = $l_res->get_row()) {
                $l_return = $l_row['isys_obj__id'];
            }
        }

        return $l_return;
    }

    /**
     * Gets attached layer 2 nets.
     *
     * @param   integer $p_id        category entry id
     * @param   integer $p_obj_id    object id crom log. port
     * @param   boolean $p_as_string return as string
     * @param   boolean $p_as_result
     *
     * @return  mixed
     */
    public function get_assigned_mobile($p_id = null, $p_obj_id = null, $p_as_string = false, $p_as_result = false)
    {
        $l_sql = 'SELECT * FROM isys_catg_cards_list_2_isys_obj AS con
			INNER JOIN isys_catg_cards_list AS main ON main.isys_catg_cards_list__id = con.isys_catg_cards_list__id ';

        if ($p_id) {
            $l_sql .= 'WHERE con.isys_catg_cards_list__id = ' . $this->convert_sql_id($p_id);
        } elseif ($p_obj_id) { // @vqh: Why else if??
            $l_sql .= 'WHERE main.isys_catg_cards_list__isys_obj__id  = ' . $this->convert_sql_id($p_obj_id);
        }

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

        if (!$p_as_result) {
            $l_arr = [];

            while ($l_row = $l_res->get_row()) {
                $l_arr[] = $l_row['isys_obj__id'];
            }

            return $l_arr;
        } else {
            return $l_res;
        }
    }

    public function rank_record($p_object_id, $p_direction, $p_table, $p_checkMethod = null, $p_purge = false)
    {
        if ($p_purge) {
            $l_dao_relation = isys_cmdb_dao_category_g_relation::instance($this->get_database_component());

            $l_sql = 'SELECT  isys_catg_assigned_cards_list__isys_catg_relation_list__id FROM isys_catg_assigned_cards_list ' .
                'INNER JOIN  isys_catg_cards_list ON isys_catg_assigned_cards_list__isys_obj__id__card =  isys_catg_cards_list__isys_obj__id ' .
                'WHERE isys_catg_cards_list__id = ' . $this->convert_sql_id($p_object_id);

            $l_relation_id = $this->retrieve($l_sql)->get_row_value('isys_catg_assigned_cards_list__isys_catg_relation_list__id');

            // Delete relation.
            if ($l_relation_id > 0) {
                $l_dao_relation->delete_relation($l_relation_id);
            }
        }

        return parent::rank_record($p_object_id, $p_direction, $p_table, $p_checkMethod, $p_purge);
    }
}
