<?php

/**
 * i-doit - Updates
 *
 * Migrating legacy licenses to be compatible with i-doit v1.12
 *
 * @package     i-doit
 * @subpackage  Update
 * @copyright   synetics GmbH
 * @license     http://www.i-doit.com/license
 * @author      Kevin Mauel <kmauel@i-doit.com>
 */

/**
 * @var $g_comp_database isys_component_database
 */
global $g_comp_database;

// Set migrationId
$g_migration_identifier = "sim_cards_migration";

// Check whether migration was executed before
if ($this->is_migration_done($g_migration_identifier)) {
    $g_migration_log[] = '<span class="bold">Migration of sim cards has already been done.</span>';
} else {
    $g_migration_log[] = '<span class="bold">Starting Migration of sim cards.</span>';

    $oldSimCards = $g_comp_database->retrieveArrayFromResource($g_comp_database->query('SELECT isys_catg_sim_card_list.*,isys_obj.isys_obj__title FROM isys_catg_sim_card_list INNER JOIN isys_obj ON (isys_catg_sim_card_list__isys_obj__id = isys_obj__id);'));

    $newSimCards = [];

    $cmdbDao = isys_cmdb_dao::instance($g_comp_database);

    foreach ($oldSimCards as $oldSimCard) {
        $sqlSim = 'INSERT INTO isys_catg_sim_list (
                                   isys_catg_sim_list__isys_obj__id,
                                   isys_catg_sim_list__status,
                                   isys_catg_sim_list__description,
                                   isys_catg_sim_list__isys_cp_contract_type__id,
                                   isys_catg_sim_list__isys_network_provider__id,
                                   isys_catg_sim_list__isys_telephone_rate__id,
                                   isys_catg_sim_list__start_date,
                                   isys_catg_sim_list__end_date,
                                   isys_catg_sim_list__threshold_date,
                                   isys_catg_sim_list__client_number,
                                   isys_catg_sim_list__phone_number
                               ) 
                               VALUES (
                                   '.$cmdbDao->convert_sql_id($oldSimCard['isys_catg_sim_card_list__isys_obj__id']).',
                                   '.$cmdbDao->convert_sql_int($oldSimCard['isys_catg_sim_card_list__status']).',
                                   '.$cmdbDao->convert_sql_text($oldSimCard['isys_catg_sim_card_list__description']).',
                                   '.$cmdbDao->convert_sql_id($oldSimCard['isys_catg_sim_card_list__isys_cp_contract_type__id']).',
                                   '.$cmdbDao->convert_sql_id($oldSimCard['isys_catg_sim_card_list__isys_network_provider__id']).',
                                   '.$cmdbDao->convert_sql_id($oldSimCard['isys_catg_sim_card_list__isys_telephone_rate__id']).',
                                   '.$cmdbDao->convert_sql_datetime($oldSimCard['isys_catg_sim_card_list__start_date']).',
                                   '.$cmdbDao->convert_sql_datetime($oldSimCard['isys_catg_sim_card_list__end_date']).',
                                   '.$cmdbDao->convert_sql_datetime($oldSimCard['isys_catg_sim_card_list__threshold_date']).',
                                   '.$cmdbDao->convert_sql_text($oldSimCard['isys_catg_sim_card_list__client_number']).',
                                   '.$cmdbDao->convert_sql_text($oldSimCard['isys_catg_sim_card_list__phone_number']).'
                               )';

        $g_comp_database->query($sqlSim) && $g_comp_database->commit();

        // First card
        $sqlCards = 'INSERT INTO isys_catg_cards_list (
                                   isys_catg_cards_list__isys_obj__id,
                                   isys_catg_cards_list__status,
                                   isys_catg_cards_list__title,
                                   isys_catg_cards_list__description,
                                   isys_catg_cards_list__serial_number,
                                   isys_catg_cards_list__pin,
                                   isys_catg_cards_list__pin2,
                                   isys_catg_cards_list__puk,
                                   isys_catg_cards_list__puk2,
                                   isys_catg_cards_list__card_number
                               ) 
                               VALUES (
                                   '.$cmdbDao->convert_sql_id($oldSimCard['isys_catg_sim_card_list__isys_obj__id']).',
                                   '.$cmdbDao->convert_sql_int($oldSimCard['isys_catg_sim_card_list__status']).',
                                   '.$cmdbDao->convert_sql_text($oldSimCard['isys_obj__title']).',
                                   '.$cmdbDao->convert_sql_text($oldSimCard['isys_catg_sim_card_list__description']).',
                                   '.$cmdbDao->convert_sql_text($oldSimCard['isys_catg_sim_card_list__serial_number']).',
                                   '.$cmdbDao->convert_sql_text($oldSimCard['isys_catg_sim_card_list__pin']).',
                                   '.$cmdbDao->convert_sql_text($oldSimCard['isys_catg_sim_card_list__pin2']).',
                                   '.$cmdbDao->convert_sql_text($oldSimCard['isys_catg_sim_card_list__puk']).',
                                   '.$cmdbDao->convert_sql_text($oldSimCard['isys_catg_sim_card_list__puk2']).',
                                   '.$cmdbDao->convert_sql_text($oldSimCard['isys_catg_sim_card_list__card_number']).'
                               )';

        $g_comp_database->query($sqlCards);

        $cardId = $g_comp_database->get_last_insert_id();

        $g_comp_database->commit();

        if ($oldSimCard['isys_catg_sim_card_list__twincard'] > 0) {
            $description = $oldSimCard['isys_catg_sim_card_list__optional_info'];

            if (!empty($oldSimCard['isys_catg_sim_card_list__tc_phone_number'])) {
                if (!empty($description)) {
                    $description .= ' <br /><br /> ';
                }

                $description .= 'Telephone number: ' . $oldSimCard['isys_catg_sim_card_list__tc_phone_number'];
            }

            // Twin card
            $sqlTwinCards = 'INSERT INTO isys_catg_cards_list (
                                   isys_catg_cards_list__isys_obj__id,
                                   isys_catg_cards_list__status,
                                   isys_catg_cards_list__title,
                                   isys_catg_cards_list__description,
                                   isys_catg_cards_list__serial_number,
                                   isys_catg_cards_list__pin,
                                   isys_catg_cards_list__pin2,
                                   isys_catg_cards_list__puk,
                                   isys_catg_cards_list__puk2,
                                   isys_catg_cards_list__card_number
                               ) 
                               VALUES (
                                   '.$cmdbDao->convert_sql_id($oldSimCard['isys_catg_sim_card_list__isys_obj__id']).',
                                   '.$cmdbDao->convert_sql_int($oldSimCard['isys_catg_sim_card_list__status']).',
                                   '.$cmdbDao->convert_sql_text('Twincard ' . $oldSimCard['isys_obj__title']).',
                                   '.$cmdbDao->convert_sql_text($description).',
                                   '.$cmdbDao->convert_sql_text($oldSimCard['isys_catg_sim_card_list__tc_serial_number']).',
                                   '.$cmdbDao->convert_sql_text($oldSimCard['isys_catg_sim_card_list__tc_pin']).',
                                   '.$cmdbDao->convert_sql_text($oldSimCard['isys_catg_sim_card_list__tc_pin2']).',
                                   '.$cmdbDao->convert_sql_text($oldSimCard['isys_catg_sim_card_list__tc_puk']).',
                                   '.$cmdbDao->convert_sql_text($oldSimCard['isys_catg_sim_card_list__tc_puk2']).',
                                   '.$cmdbDao->convert_sql_text($oldSimCard['isys_catg_sim_card_list__tc_card_number']).'
                               )';

            $g_comp_database->query($sqlTwinCards);

            $twinCardId = $g_comp_database->get_last_insert_id();

            $g_comp_database->commit();
        }

        $assignedMobilePhone = $g_comp_database->retrieveArrayFromResource(
            $g_comp_database->query('SELECT isys_catg_assigned_cards_list__isys_obj__id
                                     FROM isys_catg_assigned_cards_list
                                     WHERE isys_catg_assigned_cards_list__isys_obj__id__card = ' . $cmdbDao->convert_sql_id($oldSimCard['isys_catg_sim_card_list__isys_obj__id']) .
                ' LIMIT 1'
            )
        );

        if (!empty($assignedMobilePhone[0]['isys_catg_assigned_cards_list__isys_obj__id'])) {
            $sqlCardsToObject = 'INSERT INTO isys_catg_cards_list_2_isys_obj (isys_catg_cards_list__id, isys_obj__id, isys_catg_cards_list_2_isys_obj__status) VALUES ('.$cmdbDao->convert_sql_id($cardId).', '.$cmdbDao->convert_sql_id($assignedMobilePhone[0]['isys_catg_assigned_cards_list__isys_obj__id']).', 2)';

            $g_comp_database->query($sqlCardsToObject) && $g_comp_database->commit();

            if ($oldSimCard['isys_catg_sim_card_list__twincard'] > 0) {
                $sqlTwinCardsToObject = 'INSERT INTO isys_catg_cards_list_2_isys_obj (isys_catg_cards_list__id, isys_obj__id, isys_catg_cards_list_2_isys_obj__status) VALUES ('.$cmdbDao->convert_sql_id($twinCardId).', '.$cmdbDao->convert_sql_id($assignedMobilePhone[0]['isys_catg_assigned_cards_list__isys_obj__id']).', 2)';

                $g_comp_database->query($sqlTwinCardsToObject) && $g_comp_database->commit();
            }
        }
    }

    $g_migration_log[] = '<span class="bold">Migration of sim cards finished!</span>';

    // Mark migration as done
    $this->migration_done($g_migration_identifier);
}
