<?php

chdir('..');
include('global.php');

$Database2 = new Database($Config['Database']['Host'], $Config['Database']['User'], $Config['Database']['Password'], 'sugarcrm');
$Database2->charset($Config['Database']['Charset']);

echo("Import users to net_subject\n");
$Database2->query('TRUNCATE TABLE net_subject');
$DbResult = $Database->select('users_old', '*');
while($DbRow = $DbResult->fetch_array())
{
  //$Database->insert('User', array('Id' => $DbRow['id'], 'Name' => $DbRow['nick'], 'FirstName' => $DbRow['first_name'], 'SecondName' => $DbRow['second_name'], 'PhoneNumber' => $DbRow['phone'], 'Password' => substr(sha1(strtoupper($DbRow['nick'])), 0, 7), 'ICQ' => $DbRow['icq'], 'Email' => $DbRow['email'], 'Locked' => 0));
  if($DbRow['role'] == 2)
  {
    $Database2->insert('net_subject', array('old_id' => $DbRow['id'], 'id' => 'UUID()', 'name' => $DbRow['subject_name'], 'date_entered' => 'NOW()', 'date_modified' => 'NOW()', 'address_street' => $DbRow['street'], 'address_city' => $DbRow['town'], 'address_postalcode' => $DbRow['psc'], 'ico' => $DbRow['ico'], 'dic' => $DbRow['dic'], 'family_member_count' => $DbRow['family'], 'membership_date' => $DbRow['membership_date']));
    echo($Database2->LastQuery."\n");
    //$Database->insert('PermissionUserAssignment', array('User' => $UserId, 'GroupOrOperation' => $PermissionGroupSubjects, 'Type' => 'Group'));
  } //else
    //$Database->insert('PermissionUserAssignment', array('User' => $UserId, 'GroupOrOperation' => $PermissionGroupMembers, 'Type' => 'Group'));
}

echo("Import hosts to net_device\n");
$Database2->query('TRUNCATE TABLE net_device');
$DbResult = $Database->select('hosts', '*');
while($DbRow = $DbResult->fetch_array())
{
  $Database2->insert('net_device', array('old_id' => $DbRow['id'], 'id' => 'UUID()', 'name' => strtolower($DbRow['name']), 'date_entered' => 'NOW()', 'modified_user_id' => 1, 'date_modified' => 'NOW()', 'assigned_user_id' => 1, 'local_ip_address' => $DbRow['IP'], 'czfree_ip_address' => $DbRow['czfree_ip'], 'external_ip_address' => $DbRow['external_ip'], 'mac_address' => $DbRow['MAC'], 'last_online' => $DbRow['last_online'], 'used' => $DbRow['used'], 'show_online' => $DbRow['show_online']));
}

echo("Import host to user relations\n");
$Database2->query('TRUNCATE TABLE net_subject_net_device_c');
$DbResult = $Database->select('hosts', '*');
while($DbRow = $DbResult->fetch_array())
{
  $DbResult2 = $Database2->query('SELECT id FROM net_device WHERE old_id = '.$DbRow['id']);
  $DbRow2 = $DbResult2->fetch_array();
  $DbResult3 = $Database2->query('SELECT id FROM net_subject WHERE old_id = '.$DbRow['user']);
  $DbRow3 = $DbResult3->fetch_array();
  $Database2->insert('net_subject_net_device_c', array('id' => 'UUID()', 'date_modified' => 'NOW()', 'net_subjectet_subject_ida' => $DbRow3['id'], 'net_subjectnet_device_idb' => $DbRow2['id']));
  //echo($Database2->LastQuery."\n");
}
echo("Import parent host to host relations\n");
$Database2->query('TRUNCATE TABLE net_device_net_device_c');
$DbResult = $Database->select('hosts', '*');
while($DbRow = $DbResult->fetch_array())
{
  $DbResult2 = $Database2->query('SELECT id FROM net_device WHERE old_id = '.$DbRow['id']);
  $DbRow2 = $DbResult2->fetch_array();
  $DbResult3 = $Database2->query('SELECT id FROM net_device WHERE old_id = '.$DbRow['parent']);
  $DbRow3 = $DbResult3->fetch_array();
  $Database2->insert('net_device_net_device_c', array('id' => 'UUID()', 'date_modified' => 'NOW()', 'net_device_net_device_ida' => $DbRow3['id'], 'net_device_net_device_idb' => $DbRow2['id']));
  //echo($Database2->LastQuery."\n");
}


/*
$Subjects = array(
    'Jednota' => array(
      'Notice' => '',
      'LastUpdateTime' => '2008-07-04',
      'Intervals' => array(
        array('DayOfWeek' => 0, 'Index' => 0, 'OpenTime' => '7:00:00', 'CloseTime' => '12:00:00'),
        array('DayOfWeek' => 0, 'Index' => 1,  'OpenTime' => '14:00:00', 'CloseTime' => '16:30:00'),
        array('DayOfWeek' => 1, 'Index' => 0,  'OpenTime' => '7:00:00', 'CloseTime' => '12:00:00'),
        array('DayOfWeek' => 2, 'Index' => 0,  'OpenTime' => '7:00:00', 'CloseTime' => '12:00:00'),
        array('DayOfWeek' => 2, 'Index' => 1,  'OpenTime' => '14:00:00', 'CloseTime' => '16:30:00'),
        array('DayOfWeek' => 3, 'Index' => 0,  'OpenTime' => '7:00:00', 'CloseTime' => '12:00:00'),
        array('DayOfWeek' => 3, 'Index' => 1,  'OpenTime' => '14:00:00', 'CloseTime' => '16:30:00'),
        array('DayOfWeek' => 4, 'Index' => 0,  'OpenTime' => '7:00:00', 'CloseTime' => '12:00:00'),
        array('DayOfWeek' => 4, 'Index' => 1,  'OpenTime' => '13:30:00', 'CloseTime' => '16:30:00'),
        array('DayOfWeek' => 5, 'Index' => 0,  'OpenTime' => '6:00:00', 'CloseTime' => '10:30:00'),
      ),
    ),
    'Pošta' => array(
      'Notice' => 'Schránka se vybírá v pracovní dny v 7:45 a 9:30',
      'LastUpdateTime' => '2007-07-20',
      'Intervals' => array(
        array('DayOfWeek' => 0, 'Index' => 0,  'OpenTime' => '8:00:00', 'CloseTime' => '10:00:00'),
        array('DayOfWeek' => 0, 'Index' => 1,  'OpenTime' => '14:30:00', 'CloseTime' => '15:30:00'),
        array('DayOfWeek' => 1, 'Index' => 0,  'OpenTime' => '8:00:00', 'CloseTime' => '10:00:00'),
        array('DayOfWeek' => 1, 'Index' => 1,  'OpenTime' => '14:30:00', 'CloseTime' => '15:30:00'),
        array('DayOfWeek' => 2, 'Index' => 0,  'OpenTime' => '8:00:00', 'CloseTime' => '10:00:00'),
        array('DayOfWeek' => 2, 'Index' => 1,  'OpenTime' => '14:30:00', 'CloseTime' => '15:30:00'),
        array('DayOfWeek' => 3, 'Index' => 0,  'OpenTime' => '8:00:00', 'CloseTime' => '10:00:00'),
        array('DayOfWeek' => 3, 'Index' => 1,  'OpenTime' => '14:30:00', 'CloseTime' => '15:30:00'),
        array('DayOfWeek' => 4, 'Index' => 0,  'OpenTime' => '8:00:00', 'CloseTime' => '10:00:00'),
        array('DayOfWeek' => 4, 'Index' => 1,  'OpenTime' => '14:30:00', 'CloseTime' => '15:30:00'),
        array('DayOfWeek' => 5, 'Index' => 0,  'OpenTime' => '6:00:00', 'CloseTime' => '10:30:00'),
      ),
    ),
    'Obecní úřad' => array(
      'Notice' => 'Zdroj <a href="http://www.zdechov.cz/pages/main.php?pg=obecniurad&cat=1">www.zdechov.cz</a>',
      'LastUpdateTime' => '2008-07-07',
      'Intervals' => array(
        array('DayOfWeek' => 0, 'Index' => 0,  'OpenTime' => '7:00:00', 'CloseTime' => '11:00:00'),
        array('DayOfWeek' => 0, 'Index' => 1,  'OpenTime' => '11:30:00', 'CloseTime' => '14:00:00'),
        array('DayOfWeek' => 1, 'Index' => 0,  'OpenTime' => '7:00:00', 'CloseTime' => '11:00:00'),
        array('DayOfWeek' => 1, 'Index' => 1,  'OpenTime' => '11:30:00', 'CloseTime' => '14:00:00'),
        array('DayOfWeek' => 2, 'Index' => 0,  'OpenTime' => '7:00:00', 'CloseTime' => '11:00:00'),
        array('DayOfWeek' => 2, 'Index' => 1,  'OpenTime' => '11:30:00', 'CloseTime' => '14:00:00'),
        array('DayOfWeek' => 3, 'Index' => 0,  'OpenTime' => '7:00:00', 'CloseTime' => '11:00:00'),
        array('DayOfWeek' => 3, 'Index' => 1,  'OpenTime' => '11:30:00', 'CloseTime' => '14:00:00'),
        array('DayOfWeek' => 4, 'Index' => 0,  'OpenTime' => '7:00:00', 'CloseTime' => '11:00:00'),
        array('DayOfWeek' => 4, 'Index' => 1,  'OpenTime' => '11:30:00', 'CloseTime' => '14:00:00'),
      ),
    ),
    'Kavárna Centrum' => array(
      'Notice' => '',
      'LastUpdateTime' => '2006-09-13',
      'Intervals' => array(
        array('DayOfWeek' => 0, 'Index' => 0,  'OpenTime' => '15:00:00', 'CloseTime' => '21:00:00'),
        array('DayOfWeek' => 1, 'Index' => 0,  'OpenTime' => '15:00:00', 'CloseTime' => '21:00:00'),
        array('DayOfWeek' => 2, 'Index' => 0,  'OpenTime' => '15:00:00', 'CloseTime' => '21:00:00'),
        array('DayOfWeek' => 3, 'Index' => 0,  'OpenTime' => '15:00:00', 'CloseTime' => '21:00:00'),
        array('DayOfWeek' => 4, 'Index' => 0,  'OpenTime' => '15:00:00', 'CloseTime' => '21:00:00'),
        array('DayOfWeek' => 5, 'Index' => 0,  'OpenTime' => '15:00:00', 'CloseTime' => '22:00:00'),
      ),
    ),
    'Knihovna' => array(
      'Notice' => '',
      'LastUpdateTime' => '2004-08-20',
      'Intervals' => array(
        array('DayOfWeek' => 4, 'Index' => 0,  'OpenTime' => '14:30:00', 'CloseTime' => '17:00:00'),
      ),
    ),
    'Koliba' => array(
      'Notice' => '',
      'LastUpdateTime' => '2008-04-29',
      'Intervals' => array(
        array('DayOfWeek' => 2, 'Index' => 0,  'OpenTime' => '14:00:00', 'CloseTime' => '22:00:00'),
        array('DayOfWeek' => 3, 'Index' => 0,  'OpenTime' => '14:00:00', 'CloseTime' => '22:00:00'),
        array('DayOfWeek' => 4, 'Index' => 0,  'OpenTime' => '14:00:00', 'CloseTime' => '27:00:00'),
        array('DayOfWeek' => 5, 'Index' => 0,  'OpenTime' => '14:00:00', 'CloseTime' => '27:00:00'),
        array('DayOfWeek' => 6, 'Index' => 0,  'OpenTime' => '14:00:00', 'CloseTime' => '21:00:00'),
      ),
    ),
    'Hospoda Kasino' => array(
      'Notice' => '',
      'LastUpdateTime' => '2008-07-04',
      'Intervals' => array(
        array('DayOfWeek' => 0, 'Index' => 0,  'OpenTime' => '16:00:00', 'CloseTime' => '21:00:00'),
        array('DayOfWeek' => 1, 'Index' => 0,  'OpenTime' => '16:00:00', 'CloseTime' => '21:00:00'),
        array('DayOfWeek' => 2, 'Index' => 0,  'OpenTime' => '17:00:00', 'CloseTime' => '21:00:00'),
        array('DayOfWeek' => 3, 'Index' => 0,  'OpenTime' => '17:00:00', 'CloseTime' => '21:00:00'),
        array('DayOfWeek' => 4, 'Index' => 0,  'OpenTime' => '16:00:00', 'CloseTime' => '24:00:00'),
        array('DayOfWeek' => 5, 'Index' => 0,  'OpenTime' => '14:00:00', 'CloseTime' => '24:00:00'),
        array('DayOfWeek' => 6, 'Index' => 0,  'OpenTime' => '10:00:00', 'CloseTime' => '21:00:00'),
      ),
    ),
  );

  function SubjectOpenTime()
  {
    $this->Database->query('DELETE FROM Subject WHERE Subject.Id = SubjectOpenTime.Subject');
    $this->Database->query('TRUNCATE TABLE SubjectOpenTime');
    $this->Database->query('TRUNCATE TABLE SubjectOpenTimeInterval');
    foreach($this->Subjects as $SubjectName => $Subject)
    {
      $this->Database->insert('Subject', array('Name' => $SubjectName));
      $Subject['Id'] = $this->Database->insert_id;
      $this->Database->insert('SubjectOpenTime', array('Notice' => $Subject['Notice'], 'UpdateTime' => $Subject['LastUpdateTime'], 'Subject' => $Subject['Id']));
      foreach($Subject['Intervals'] as $TimeInterval)
      {
        $TimeInterval['Subject'] = $Subject['Id'];
        $TimeInterval['OpenTime'] = '0000-01-0'.($TimeInterval['DayOfWeek'] + 1).' '.$TimeInterval['OpenTime'];
        $TimeInterval['CloseTime'] = '0000-01-0'.($TimeInterval['DayOfWeek'] + 1).' '.$TimeInterval['CloseTime'];
        unset($TimeInterval['DayOfWeek']);
        unset($TimeInterval['Index']);
        $this->Database->insert('SubjectOpenTimeInterval', $TimeInterval);
      }
    }
  }

  function Show()
  {
    $Output = '';

    // Clear previous content
    $this->Database->query('TRUNCATE TABLE PermissionOperation');
    $this->Database->query('TRUNCATE TABLE PermissionGroup');
    $this->Database->query('TRUNCATE TABLE PermissionGroupAssignment');
    $this->Database->query('TRUNCATE TABLE PermissionUserAssignment');
    $this->Database->query('TRUNCATE TABLE User');
    $this->Database->query('TRUNCATE TABLE Subject');

    // Init permission groups
    $this->Database->insert('PermissionGroup', array('Description' => 'Ostatní'));
    $PermissionGroupOthers = $this->Database->insert_id;
    $this->Database->insert('PermissionGroup', array('Description' => 'Členové sítě'));
    $PermissionGroupMembers = $this->Database->insert_id;
    $this->Database->insert('PermissionGroupAssignment', array('Group' => $PermissionGroupMembers, 'GroupOrOperation' => $PermissionGroupOthers, 'Type' => 'Group'));
echo($this->Database->LastQuery);
    $this->Database->insert('PermissionGroup', array('Description' => 'Zástupci za domácnost'));
    $PermissionGroupSubjects = $this->Database->insert_id;
    $this->Database->insert('PermissionGroupAssignment', array('Group' => $PermissionGroupSubjects, 'GroupOrOperation' => $PermissionGroupMembers, 'Type' => 'Group'));
$this->Database->insert('PermissionGroup', array('Description' => 'Správci sítě'));
    $PermissionGroupNetworkAdmins = $this->Database->insert_id;
    $this->Database->insert('PermissionGroupAssignment', array('Group' => $PermissionGroupNetworkAdmins, 'GroupOrOperation' => $PermissionGroupSubjects, 'Type' => 'Group'));
    $this->Database->insert('PermissionGroup', array('Description' => 'Systémoví administrátoři'));
    $PermissionGroupAdmin = $this->Database->insert_id;
    $this->Database->insert('PermissionGroupAssignment', array('Group' => $PermissionGroupAdmin, 'GroupOrOperation' => $PermissionGroupNetworkAdmins, 'Type' => 'Group'));

    // Init permission operations
    $this->Database->insert('PermissionOperation', array('Module' => 'News', 'Operation' => 'Display', 'Item' => 'Item'));
    $OperationId = $this->Database->insert_id;
    $this->Database->insert('PermissionGroupAssignment', array('Group' => $PermissionGroupOthers, 'GroupOrOperation' => $OperationId, 'Type' => 'Operation'));

    $DbResult = $this->Database->select('news_category', 'id, permission');
    while($DbRow = $DbResult->fetch_array())
    {
      $this->Database->insert('PermissionOperation', array('Module' => 'News', 'Operation' => 'Insert', 'Item' => 'Group', 'ItemId' => $DbRow['id']));
      $OperationIdInsert = $this->Database->insert_id;
      $this->Database->insert('PermissionOperation', array('Module' => 'News', 'Operation' => 'Display', 'Item' => 'Group', 'ItemId' => $DbRow['id']));
      $OperationIdDisplay = $this->Database->insert_id;
      $this->Database->insert('PermissionGroupAssignment', array('Group' => $PermissionGroupOthers, 'GroupOrOperation' => $OperationIdDisplay, 'Type' => 'Operation'));
      if($DbRow['permission'] == 1)
      {
        $this->Database->insert('PermissionGroupAssignment', array('Group' => $PermissionGroupNetworkAdmins, 'GroupOrOoperation' => $OperationIdInsert, 'Type' => 'Operation'));
      } else
      {
        $this->Database->insert('PermissionGroupAssignment', array('Group' => $PermissionGroupOthers, 'GroupOrOperation' => $OperationIdInsert, 'Type' => 'Operation'));
      }
    }

    $this->Database->insert('PermissionOperation', array('Module' => 'Chat', 'Operation' => 'Display'));
    $OperationId = $this->Database->insert_id;
    $this->Database->insert('PermissionGroupAssignment', array('Group' => $PermissionGroupOthers, 'GroupOrOperation' => $OperationId, 'Type' => 'Operation'));

    $this->Database->insert('PermissionOperation', array('Module' => 'Share', 'Operation' => 'Display'));
    $OperationId = $this->Database->insert_id;
    $this->Database->insert('PermissionGroupAssignment', array('Group' => $PermissionGroupMembers, 'GroupOrOperation' => $OperationId, 'Type' => 'Operation'));

    $this->Database->insert('PermissionOperation', array('Module' => 'Finance', 'Operation' => 'DisplaySubjectState'));
    $OperationId = $this->Database->insert_id;
    $this->Database->insert('PermissionGroupAssignment', array('Group' => $PermissionGroupSubjects, 'GroupOrOperation' => $OperationId, 'Type' => 'Operation'));

    $this->Database->insert('PermissionOperation', array('Module' => 'EatingPlace', 'Operation' => 'Edit'));
    $OperationId = $this->Database->insert_id;
    $this->Database->insert('PermissionGroupAssignment', array('Group' => $PermissionGroupMembers, 'GroupOrOperation' => $OperationId, 'Type' => 'Operation'));

    $this->Database->insert('PermissionOperation', array('Module' => 'Finance', 'Operation' => 'Manage'));
    $OperationId = $this->Database->insert_id;
    $this->Database->insert('PermissionGroupAssignment', array('Group' => $PermissionGroupNetworkAdmins, 'GroupOrOperation' => $OperationId, 'Type' => 'Operation'));

    $this->Database->insert('PermissionOperation', array('Module' => 'Network', 'Operation' => 'Edit'));
    $OperationId = $this->Database->insert_id;
    $this->Database->insert('PermissionGroupAssignment', array('Group' => $PermissionGroupNetworkAdmins, 'GroupOrOperation' => $OperationId, 'Type' => 'Operation'));

    $this->Database->insert('PermissionOperation', array('Module' => 'Finance', 'Operation' => 'SubjectList'));
    $OperationId = $this->Database->insert_id;
    $this->Database->insert('PermissionGroupAssignment', array('Group' => $PermissionGroupNetworkAdmins, 'GroupOrOperation' => $OperationId, 'Type' => 'Operation'));

    $this->Database->insert('PermissionOperation', array('Module' => 'Log', 'Operation' => 'Show'));
    $OperationId = $this->Database->insert_id;
    $this->Database->insert('PermissionGroupAssignment', array('Group' => $PermissionGroupNetworkAdmins, 'GroupOrOperation' => $OperationId, 'Type' => 'Operation'));

    // Init User table from users
    $DbResult = $this->Database->select('users', '*');
    while($DbRow = $DbResult->fetch_array())
    {
      $this->Database->insert('User', array('Id' => $DbRow['id'], 'Name' => $DbRow['nick'], 'FirstName' => $DbRow['first_name'], 'SecondName' => $DbRow['second_name'], 'PhoneNumber' => $DbRow['phone'], 'Password' => substr(sha1(strtoupper($DbRow['nick'])), 0, 7), 'ICQ' => $DbRow['icq'], 'Email' => $DbRow['email'], 'Locked' => 0));
      $UserId = $this->Database->insert_id;
      if($DbRow['id'] == 1) 
      {
        $this->Database->insert('PermissionUserAssignment', array('User' => $UserId, 'GroupOrOperation' => $PermissionGroupAdmin, 'Type' => 'Group'));
      } else
      if($DbRow['role'] == 2)
      {
        $this->Database->insert('Subject', array('Id' => $DbRow['id'], 'Name' => $DbRow['subject_name'], 'AddressStreet' => $DbRow['street'], 'AddressTown' => $DbRow['town'], 'AddressPSC' => $DbRow['psc'], 'IC' => $DbRow['ico'], 'DIC' => $DbRow['dic'], 'FamilyMemberCount' => $DbRow['family'], 'MapPositionX' => $DbRow['PositionX'], 'MapPositionY' => $DbRow['PositionY'], 'NetworkSegment' => $DbRow['network_segment'], 'InternetTariffCurrentMonth' => $DbRow['inet_tarif_now'], 'InternetTariffNextMonth' => $DbRow['inet_tarif_next'], 'MembershipDate' => $DbRow['membership_date']));
        $this->Database->insert('PermissionUserAssignment', array('User' => $UserId, 'GroupOrOperation' => $PermissionGroupSubjects, 'Type' => 'Group'));
      } else
        $this->Database->insert('PermissionUserAssignment', array('User' => $UserId, 'GroupOrOperation' => $PermissionGroupMembers, 'Type' => 'Group'));
    }

    // Insert anonymous user
    $this->Database->insert('User', array('Name' => 'Anonym', 'Password' => 'dsafafdfadfs', 'Locked' => 0));
    $Output .=  'Anonymní uživatel: '.$this->Database->insert_id.'</ br>';
    $AnonymousUserId = $this->Database->insert_id;
    $this->Database->insert('PermissionUserAssignment', array('User' => $AnonymousUserId, 'GroupOrOperation' => $PermissionGroupOthers, 'Type' => 'Group'));

    // Init subjects open time
    $this->SubjectOpenTime();

    // Import chat messages history
    //$this->Database->query('TRUNCATE TABLE ChatHistory');
    //$this->Database->query('INSERT INTO ChatHistory (SELECT id as Id, nick as Nick, text as Text, time as Time, color as Color, roomname as RoomName, roomtype as RoomType, "" as Host FROM `sunrisechat_history`)');


    return($Output);
  }
*/

?>