FreenetIS

'failed to connect to database' => 'Nepodařilo se připojit k databázi.',

'installation' => 'Instalace',

<?php defined('SYSPATH') or die('No direct script access.');

class Config_Model extends Model {

public function __construct()

{

// load database library into $this->db (can be omitted if not required)

parent::__construct();

}

public function get_db_schema_version()

{

$this->db->where('name','db_schema_version');

$result = $this->db->query('select value from config where name = "db_schema_version"');

return (count($result)) ? $result[0]->value : 0;

}

}

<?php defined('SYSPATH') or die('No direct script access.'); +

$config['version'] = 176;

<?php

class Installation_Controller extends Controller

{

public function index()

{

$form = new Forge(url_lang::base()."installation", '', 'POST', array('id' => 'article_form'));

$form->set_attr('class', 'form_class')->set_attr('method', 'post');

$form->group('')->label(url_lang::lang('texts.login data'));

$form->input('login')->label(url_lang::lang('texts.username').':')->rules('required|length[3,50]')->value('admin');

$form->password('passwd')->label(url_lang::lang('texts.password').':')->rules('required|length[3,50]')->class('required');

$form->password('confirm_password')->label(url_lang::lang('texts.confirm password').':')->rules('required|length[3,50]')->matches($form->passwd);

$form->group('')->label(url_lang::lang('texts.contact data'));

$form->input('pre_title')->label(url_lang::lang('texts.pre title').':')->rules('length[3,40]');

$form->input('name')->label(url_lang::lang('texts.name').':')->rules('required|length[3,30]');

$form->input('middle_name')->label(url_lang::lang('texts.middle name').':')->rules('length[3,30]');

$form->input('surname')->label(url_lang::lang('texts.surname').':')->rules('required|length[3,60]');

$form->input('post_title')->label(url_lang::lang('texts.post title').':')->rules('length[3,30]');

$form->date('birthday')->label(url_lang::lang('texts.birthday').':')->years(date('Y')-100, date('Y'))->rules('required');

$form->input('street')->label(url_lang::lang('texts.street').':')->rules('required|length[1,200]');

$form->input('street_number')->label(url_lang::lang('texts.street number').':')->rules('required|length[1,50]');

$form->input('town')->label(url_lang::lang('texts.town').':')->rules('required|length[3,200]');

$form->input('zip_code')->label(url_lang::lang('texts.zip code').':')->rules('required|length[5,10]');

$form->input('phone')->label(url_lang::lang('texts.phone').':')->rules('required|length[9,40]')->callback(array($this, 'valid_phone'));

$form->input('email')->label(url_lang::lang('texts.email').':')->rules('length[3,50]');

$form->submit('submit')->value(url_lang::lang('texts.Install'));

special::required_forge_style($form, ' *', 'required');

if ($form->validate())

{

$form_data = $form->as_array();

foreach($form_data as $key => $value)

{

$form_data[$key] = htmlspecialchars($value);

}

$this->db = new Database();

require_once("upgrade_sql.php");

foreach ($upgrade_sql as $queries)

{

foreach ($queries as $query)

{

$this->db->query($query);

}

}

$member = new Member_Model();

$member->registration = NULL;

$member->name = $form_data["name"].' '.$form_data["surname"];

$member->street_number = $form_data["street_number"];

$member->street = $form_data["street"];

$member->town = $form_data["town"];

$member->ZIP_code = $form_data["zip_code"];

$member->type = 4;

$member->save();

$user = new User_Model();

$user->id = 1;

$user->member_id = $member->id;

$user->name = $form_data["name"];

$user->middle_name = $form_data["middle_name"];

$user->surname = $form_data["surname"];

$user->post_title = $form_data["post_title"];

$user->birthday = date("Y-m-d",$form_data["birthday"]);

$user->phone = $form_data["phone"];

$user->email = $form_data["email"];

$user->login = $form_data["login"];

$user->password = sha1($form_data["passwd"]);

$user->type = 'member';

$user->radius_password = $form_data["phone"];

$user->save();

url::redirect(url_lang::base().'login');

}

$view = new View('installation');

$view->title = url_lang::lang('texts.Installation');

$view->form = $form->html();

$view->render(TRUE);

}

function valid_phone($input)

{

$value = trim($input->value);

if(!ereg("^[0-9\.\+\/\-]{9,9}$",$value))

{

$input->add_error('required', url_lang::lang('texts.Bad phone format.'));

}

}

}

if(!$this->test_db())

{

$this->error(3);

die();

}

try

{

$config = new Config_Model();

if(!$config->get_db_schema_version()) url::redirect(url_lang::base().'installation');

}

catch (Kohana_Database_Exception $e)

{

if (url_lang::current()!='installation') url::redirect(url_lang::base().'installation');

}

if (!$this->session->get('user_id', 0) && url_lang::current()!='login' && url_lang::current()!='registration' && url_lang::current()!='scheduler/run' && substr(url_lang::current(),0,9)!='redirect/' && url_lang::current()!='installation')

if (url_lang::current()!='installation') $this->session->set('referer',url_lang::current());

$message = url_lang::lang('states.Failed to send e-mail').'<br />'.url_lang::lang('states.Please check settings.');

case 3:

$message = url_lang::lang('states.Failed to connect to database').'<br />'.url_lang::lang('states.Please check settings.');

$this->showbox($message, $this->ICON_ERROR, $content);

break;

private function test_db()

{

try

{

Database::instance()->connect();

}

catch (Kohana_Database_Exception $e)

{

return FALSE;

}

return TRUE;

}

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title><?php echo $title ?> | slfree.net</title>

<?php echo html::stylesheet('media/css/login.css') ?>

<?php echo html::stylesheet('media/css/forms.css') ?>

</head>

<body>

<div id="main-registration">

<h1><span>slfree.net</span></h1>

<div id="main-padd">

<div class="flags">

<?php echo special::create_language_flags(array('cs' => 'Česky', 'en' => 'English')) ?>

</div>

<h2><?php echo $title ?></h2><br />

<p>Vítejte u instalace Freenetisu! Vyplňte prosím následující údaje pro defaultního uživatele vašeho systému.</p>

<br />

<?php echo $form ?>

<div class="clear"></div>

</div>

</div><br />

</body>

</html>

Revize 172-176 - duben 2009

Přidán instalátor, zatím spíše zkušební verze.

<?php

$upgrade_sql = array(

176 => array("CREATE TABLE `acl` (

`id` int(11) NOT NULL default '0',

`section_value` varchar(230) collate utf8_czech_ci NOT NULL default 'system',

`allow` int(11) NOT NULL default '0',

`enabled` int(11) NOT NULL default '0',

`return_value` text collate utf8_czech_ci,

`note` text collate utf8_czech_ci,

`updated_date` int(11) NOT NULL default '0',

PRIMARY KEY (`id`),

KEY `enabled_acl` (`enabled`),

KEY `section_value_acl` (`section_value`),

KEY `updated_date_acl` (`updated_date`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `acl` (`id`, `section_value`, `allow`, `enabled`, `return_value`, `note`, `updated_date`) VALUES

(19, 'user', 1, 1, '', '', 1209664479),

(20, 'user', 1, 1, '', '', 1209664544),

(21, 'user', 1, 1, '', '', 1209655542),

(22, 'user', 1, 1, '', '', 1235666568),

(24, 'user', 1, 1, '', '', 1209657851),

(25, 'user', 1, 1, '', '', 1209652620),

(26, 'user', 1, 1, '', '', 1209678005),

(27, 'user', 1, 1, '', '', 1217570401),

(28, 'user', 1, 1, '', '', 1209678074),

(29, 'user', 1, 1, '', 'Technici maji prava na spravu clenu', 1217570478),

(30, 'user', 1, 1, '', 'Technici muzou vse', 1212251287),

(31, 'user', 1, 1, '', 'Správní rada a administra administrovat pihlask', 1237305553),

(32, 'user', 1, 1, '', '', 1235661551),

(33, 'user', 1, 1, '', 'Muzou odeslat e-mail z freenetisu.', 1236176614);",

"CREATE TABLE `acl_sections` (

`id` int(11) NOT NULL default '0',

`value` varchar(230) collate utf8_czech_ci NOT NULL,

`order_value` int(11) NOT NULL default '0',

`name` varchar(230) collate utf8_czech_ci NOT NULL,

`hidden` int(11) NOT NULL default '0',

PRIMARY KEY (`id`),

UNIQUE KEY `value_acl_sections` (`value`),

KEY `hidden_acl_sections` (`hidden`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `acl_sections` (`id`, `value`, `order_value`, `name`, `hidden`) VALUES

(1, 'system', 1, 'System', 0),

(2, 'user', 2, 'User', 0);",

"CREATE TABLE `acl_seq` (

`id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `acl_seq` (`id`) VALUES (33);",

"CREATE TABLE `aco` (

`id` int(11) NOT NULL default '0',

`section_value` varchar(240) collate utf8_czech_ci NOT NULL default '0',

`value` varchar(240) collate utf8_czech_ci NOT NULL,

`order_value` int(11) NOT NULL default '0',

`name` varchar(255) collate utf8_czech_ci NOT NULL,

`hidden` int(11) NOT NULL default '0',

PRIMARY KEY (`id`),

UNIQUE KEY `section_value_value_aco` (`section_value`,`value`),

KEY `hidden_aco` (`hidden`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `aco` (`id`, `section_value`, `value`, `order_value`, `name`, `hidden`) VALUES

(14, 'freenetis', 'view_all', 1, 'Prohlizeni vsech zaznamu', 0),

(15, 'freenetis', 'edit_all', 1, 'Editace vsech zaznamu', 0),

(16, 'freenetis', 'new_all', 1, 'Vytvareni novych zaznamu', 0),

(17, 'freenetis', 'view_own', 2, 'Prohlizeni vlastnich zaznamu', 0),

(18, 'freenetis', 'edit_own', 2, 'Editace vlastnich zaznamu', 0),

(19, 'freenetis', 'new_own', 2, 'Vytvareni novych vlastnich', 0),

(20, 'freenetis', 'delete_all', 1, 'Mazani vsech zaznamu', 0),

(21, 'freenetis', 'delete_own', 2, 'Mazani vlastnich zaznamu', 0),

(22, 'menu', 'my_users', 1, 'My users', 0),

(23, 'menu', 'my_transfers', 1, 'My transfers', 0),

(24, 'menu', 'my_devices', 1, 'My devices', 0),

(25, 'menu', 'unidentified_transfers', 2, 'Unidentified transfers', 0),

(26, 'menu', 'unconfirmed_works', 2, 'Unconfirmed works', 0),

(27, 'menu', 'accounts', 2, 'Accounts (master, operating...)', 0),

(28, 'menu', 'users_members', 3, 'Users and members', 0),

(29, 'menu', 'network', 4, 'Network menu', 0),

(30, 'menu', 'reports', 5, 'Reports', 0),

(31, 'menu', 'options', 6, 'Freenetis options', 0),

(32, 'menu', 'registration_admin', 2, 'Administration of registration forms', 0),

(33, 'freenetis', 'write_email', 3, 'Poslat e-mail', 0);",

"CREATE TABLE `aco_map` (

`acl_id` int(11) NOT NULL default '0',

`section_value` varchar(230) collate utf8_czech_ci NOT NULL default '0',

`value` varchar(230) collate utf8_czech_ci NOT NULL,

PRIMARY KEY (`acl_id`,`section_value`,`value`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `aco_map` (`acl_id`, `section_value`, `value`) VALUES

(19, 'freenetis', 'delete_all'),

(19, 'freenetis', 'delete_own'),

(19, 'freenetis', 'edit_all'),

(19, 'freenetis', 'edit_own'),

(19, 'freenetis', 'new_all'),

(19, 'freenetis', 'new_own'),

(19, 'freenetis', 'view_all'),

(19, 'freenetis', 'view_own'),

(20, 'freenetis', 'delete_all'),

(20, 'freenetis', 'delete_own'),

(20, 'freenetis', 'edit_all'),

(20, 'freenetis', 'edit_own'),

(20, 'freenetis', 'new_all'),

(20, 'freenetis', 'new_own'),

(20, 'freenetis', 'view_all'),

(20, 'freenetis', 'view_own'),

(21, 'freenetis', 'delete_own'),

(21, 'freenetis', 'edit_own'),

(21, 'freenetis', 'new_own'),

(21, 'freenetis', 'view_own'),

(22, 'freenetis', 'delete_own'),

(22, 'freenetis', 'edit_own'),

(22, 'freenetis', 'new_own'),

(22, 'freenetis', 'view_own'),

(24, 'freenetis', 'delete_own'),

(24, 'freenetis', 'edit_own'),

(24, 'freenetis', 'new_own'),

(24, 'freenetis', 'view_own'),

(25, 'freenetis', 'delete_own'),

(25, 'freenetis', 'edit_own'),

(25, 'freenetis', 'new_own'),

(25, 'freenetis', 'view_own'),

(26, 'menu', 'accounts'),

(26, 'menu', 'my_devices'),

(26, 'menu', 'my_transfers'),

(26, 'menu', 'my_users'),

(26, 'menu', 'network'),

(26, 'menu', 'registrations'),

(26, 'menu', 'unconfirmed_works'),

(26, 'menu', 'unidentified_transfers'),

(26, 'menu', 'users_members'),

(27, 'menu', 'my_devices'),

(27, 'menu', 'my_transfers'),

(27, 'menu', 'my_users'),

(28, 'menu', 'my_devices'),

(29, 'menu', 'network'),

(29, 'menu', 'users_members'),

(30, 'freenetis', 'delete_all'),

(30, 'freenetis', 'delete_own'),

(30, 'freenetis', 'edit_all'),

(30, 'freenetis', 'edit_own'),

(30, 'freenetis', 'new_all'),

(30, 'freenetis', 'new_own'),

(30, 'freenetis', 'view_all'),

(30, 'freenetis', 'view_own'),

(31, 'freenetis', 'delete_all'),

(31, 'freenetis', 'edit_all'),

(31, 'freenetis', 'view_all'),

(32, 'menu', 'registration_admin'),

(33, 'freenetis', 'write_email'); ",

"CREATE TABLE `aco_sections` (

`id` int(11) NOT NULL default '0',

`value` varchar(230) collate utf8_czech_ci NOT NULL,

`order_value` int(11) NOT NULL default '0',

`name` varchar(230) collate utf8_czech_ci NOT NULL,

`hidden` int(11) NOT NULL default '0',

PRIMARY KEY (`id`),

UNIQUE KEY `value_aco_sections` (`value`),

KEY `hidden_aco_sections` (`hidden`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `aco_sections` (`id`, `value`, `order_value`, `name`, `hidden`) VALUES

(14, 'freenetis', 1, 'system Freenetis', 0),

(15, 'menu', 1, 'Polozky menu', 0);",

"CREATE TABLE `aco_sections_seq` (

`id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `aco_sections_seq` (`id`) VALUES (15);",

"CREATE TABLE `aco_seq` (

`id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `aco_seq` (`id`) VALUES (33);",

"CREATE TABLE `aro_groups` (

`id` int(11) NOT NULL default '0',

`parent_id` int(11) NOT NULL default '0',

`lft` int(11) NOT NULL default '0',

`rgt` int(11) NOT NULL default '0',

`name` varchar(255) collate utf8_czech_ci NOT NULL,

`value` varchar(255) collate utf8_czech_ci NOT NULL,

PRIMARY KEY (`id`,`value`),

UNIQUE KEY `value_aro_groups` (`value`),

KEY `parent_id_aro_groups` (`parent_id`),

KEY `lft_rgt_aro_groups` (`lft`,`rgt`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `aro_groups` (`id`, `parent_id`, `lft`, `rgt`, `name`, `value`) VALUES

(21, 0, 1, 26, 'Vsichni lide na svete', 'all'),

(22, 21, 2, 21, 'Radni clenove sdruzeni', 'radni_clenove'),

(23, 21, 22, 23, 'Registrovani zajemci o clenstvi', 'registrovani_zajemci'),

(24, 22, 3, 4, 'Revizni komise', 'revizni_komise'),

(25, 22, 5, 10, 'Spravni rada', 'spravni_rada'),

(26, 22, 11, 18, 'Technici', 'technici'),

(27, 25, 6, 7, 'Hospodar', 'hospodar'),

(28, 25, 8, 9, 'Predseda jednatel', 'predseda_jednatel'),

(29, 26, 12, 13, 'Certifikovani sitovi experti', 'certifikovani_experti'),

(30, 26, 14, 15, 'Spravci AP', 'spravci_ap'),

(31, 26, 16, 17, 'Spravci freenetisu', 'spravci_freenetisu'),

(32, 22, 19, 20, 'Administratori systemu', 'admins'),

(33, 21, 24, 25, 'Uzivatele clenu', 'uzivatele');",

"CREATE TABLE `aro_groups_id_seq` (

`id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `aro_groups_id_seq` (`id`) VALUES (33);",

"CREATE TABLE `aro_groups_map` (

`acl_id` int(11) NOT NULL default '0',

`group_id` int(11) NOT NULL default '0',

PRIMARY KEY (`acl_id`,`group_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `aro_groups_map` (`acl_id`, `group_id`) VALUES

(19, 32),

(20, 32),

(21, 22),

(22, 22),

(24, 33),

(25, 33),

(26, 32),

(27, 22),

(27, 23),

(28, 33),

(29, 26),

(30, 26),

(31, 25),

(31, 32),

(32, 25),

(33, 25),

(33, 27),

(33, 28),

(33, 31),

(33, 32);",

"CREATE TABLE `aro_map` (

`acl_id` int(11) NOT NULL default '0',

`section_value` varchar(230) collate utf8_czech_ci NOT NULL default '0',

`value` varchar(230) collate utf8_czech_ci NOT NULL,

PRIMARY KEY (`acl_id`,`section_value`,`value`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"CREATE TABLE `aro_sections` (

`id` int(11) NOT NULL default '0',

`value` varchar(230) collate utf8_czech_ci NOT NULL,

`order_value` int(11) NOT NULL default '0',

`name` varchar(230) collate utf8_czech_ci NOT NULL,

`hidden` int(11) NOT NULL default '0',

PRIMARY KEY (`id`),

UNIQUE KEY `value_aro_sections` (`value`),

KEY `hidden_aro_sections` (`hidden`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `aro_sections` (`id`, `value`, `order_value`, `name`, `hidden`) VALUES

(12, 'all', 1, 'All users of slFree', 0);",

"CREATE TABLE `aro_sections_seq` (

`id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `aro_sections_seq` (`id`) VALUES (12);",

"CREATE TABLE `aro_seq` (

`id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `aro_seq` (`id`) VALUES (18);",

"CREATE TABLE `axo` (

`id` int(11) NOT NULL default '0',

`section_value` varchar(240) collate utf8_czech_ci NOT NULL default '0',

`value` varchar(240) collate utf8_czech_ci NOT NULL,

`order_value` int(11) NOT NULL default '0',

`name` varchar(255) collate utf8_czech_ci NOT NULL,

`hidden` int(11) NOT NULL default '0',

PRIMARY KEY (`id`),

UNIQUE KEY `section_value_value_axo` (`section_value`,`value`),

KEY `hidden_axo` (`hidden`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `axo` (`id`, `section_value`, `value`, `order_value`, `name`, `hidden`) VALUES

(15, 'Registration_Controller', 'name', 1, 'Jmeno', 0),

(16, 'Registration_Controller', 'surname', 1, 'Prijmeni', 0),

(17, 'Registration_Controller', 'street', 1, 'Ulice', 0),

(20, 'Members_Controller', 'view_comment', 0, 'Vidi komentar', 0),

(21, 'Members_Controller', 'view_entrance_date', 0, 'Vidi vstupni datum', 0),

(22, 'Members_Controller', 'view_qos_ceil', 0, 'Vidi qos ceil', 0),

(23, 'Members_Controller', 'view_qos_rate', 0, 'Vidi qos rate', 0),

(25, 'Members_Controller', 'edit_username', 1, 'Edituje login', 0),

(26, 'Members_Controller', 'edit_name', 1, 'Edituje jmeno', 0),

(27, 'Members_Controller', 'edit_surname', 1, 'Edituje prijmeni', 0),

(28, 'Members_Controller', 'edit_type', 1, 'Edituje typ', 0),

(29, 'Members_Controller', 'edit_membersname', 1, 'Edituje clenske jmeno', 0),

(30, 'Members_Controller', 'edit_phone', 1, 'Edituje telefon', 0),

(31, 'Members_Controller', 'edit_email', 1, 'Edituje email', 0),

(32, 'Members_Controller', 'edit_address', 1, 'Edituje adresu', 0),

(33, 'Members_Controller', 'edit_currentcredit', 1, 'Edituje vysi kreditu', 0),

(34, 'Members_Controller', 'edit_en_fee', 1, 'Edituje vysi vstupniho poplatku', 0),

(35, 'Members_Controller', 'edit_en_fee_left', 1, 'Edituje zbyvajici vysi vstup poplatku', 0),

(36, 'Members_Controller', 'edit_debit', 1, 'Edituje vysi splatky', 0),

(37, 'Members_Controller', 'edit_qos_ceil', 1, 'Edituje QOS ceil', 0),

(38, 'Members_Controller', 'edit_qos_rate', 1, 'Edituje QOS rate', 0),

(39, 'Members_Controller', 'edit_entrance_date', 1, 'Edituje vstupni datum', 0),

(40, 'Members_Controller', 'edit_comment', 1, 'Edituje komentar', 0),

(41, 'Members_Controller', 'edit_must_pay', 1, 'Edituje zda musi platit poplatky', 0),

(42, 'Users_Controller', 'edit_username', 1, 'Editovat login', 0),

(43, 'Users_Controller', 'edit_phone', 1, 'Editovat telefon', 0),

(44, 'Users_Controller', 'edit_email', 1, 'Editovat email', 0),

(45, 'Users_Controller', 'edit_comment', 1, 'Editovat komentar', 0),

(46, 'Users_Controller', 'add_work', 2, 'Muze pridavat prace', 0),

(47, 'Users_Controller', 'view_works', 0, 'Vidi prace', 0),

(48, 'Users_Controller', 'view_qos_ceil', 0, 'Vidi QOS ceil', 0),

(49, 'Users_Controller', 'view_qos_rate', 0, 'Vidi QOS rate', 0),

(50, 'Users_Controller', 'view_comment', 0, 'Vidi komentar', 0),

(51, 'Users_Controller', 'add_comment', 3, 'Pridava i komentar', 0),

(52, 'Users_Controller', 'edit_work', 2, 'Muze editovat praci', 0),

(53, 'Users_Controller', 'delete_work', 2, 'Smazat praci', 0),

(54, 'Works_Controller', 'work_confirm', 1, 'Potvrzovat prace a platit za ne', 0),

(55, 'Accounts_Controller', 'view_transfers', 1, 'Vidi sve transakce', 0),

(56, 'Members_Controller', 'add_must_pay', 2, 'Pri vytvareni nastavovat zda musi platit', 0),

(57, 'Members_Controller', 'add_current_credit', 2, 'Pri vytvareni nastavovat kredit uzivatele', 0),

(58, 'Accounts_Controller', 'view_all_transfers', 1, 'Vidi vsechny transakce', 0),

(59, 'Accounts_Controller', 'add_accounts', 2, 'Muze pridavat ucty', 0),

(60, 'Accounts_Controller', 'view_accounts', 2, 'Vidi vsechny sve ucty', 0),

(61, 'Accounts_Controller', 'edit_accounts', 3, 'Editovat ucty', 0),

(62, 'Accounts_Controller', 'delete_accounts', 4, 'Mazat ucty', 0),

(63, 'Accounts_Controller', 'assign_transfers', 5, 'Muze prirazovat neidentifikovane transakce', 0),

(64, 'Members_Controller', 'view_all_registrations', 1, 'Edituje prihlasky', 0),

(65, 'Members_Controller', 'edit_termination_date', 1, 'Edituje datum ukonceni clenstvi', 0),

(71, 'device', 'edit_user', 0, 'Editace uzivatele', 0),

(72, 'device', 'edit_title', 0, 'Editace nazvu', 0),

(73, 'device', 'edit_type', 0, 'Editace typu', 0),

(74, 'device', 'edit_pppoe', 0, 'Editace pppoe', 0),

(75, 'device', 'edit_login', 0, 'Editace loginu', 0),

(76, 'device', 'eedit_password', 0, 'Editace hesla', 0),

(77, 'device', 'edit_GPS', 0, 'Editace GPS', 0),

(78, 'device', 'edit_address_position', 0, 'Editace adresy umisteni', 0),

(79, 'device', 'edit_detail_position', 0, 'Editace detailu umisteni', 0),

(80, 'device', 'edit_comment', 0, 'Editace komentare', 0),

(81, 'device', 'show_user', 0, 'Zobrazeni uživatele', 0),

(82, 'device', 'show_title', 0, 'Zobrazeni nazvu', 0),

(83, 'device', 'show_type', 0, 'Zobrazeni typu', 0),

(84, 'device', 'show_pppoe', 0, 'Zobrazeni pppoe', 0),

(85, 'device', 'show_login', 0, 'Zobrazeni loginu', 0),

(86, 'device', 'show_password', 0, 'Zobrazeni hesla', 0),

(87, 'device', 'show_gps', 0, 'Zobrazeni GPS', 0),

(88, 'device', 'show_address_position', 0, 'Zobrazeni adresy umisteni', 0),

(89, 'device', 'show_detail_position', 0, 'Zobrazeni detailu umisteni', 0),

(90, 'device', 'show_comment', 0, 'Zobrazeni komentare', 0);",

"CREATE TABLE `axo_groups` (

`id` int(11) NOT NULL default '0',

`parent_id` int(11) NOT NULL default '0',

`lft` int(11) NOT NULL default '0',

`rgt` int(11) NOT NULL default '0',

`name` varchar(255) collate utf8_czech_ci NOT NULL,

`value` varchar(255) collate utf8_czech_ci NOT NULL,

PRIMARY KEY (`id`,`value`),

UNIQUE KEY `value_axo_groups` (`value`),

KEY `parent_id_axo_groups` (`parent_id`),

KEY `lft_rgt_axo_groups` (`lft`,`rgt`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `axo_groups` (`id`, `parent_id`, `lft`, `rgt`, `name`, `value`) VALUES

(10, 0, 1, 34, 'all', 'All'),

(13, 10, 2, 11, 'Sprava clenu', 'sprava_clenu'),

(14, 15, 4, 5, 'Registrace noveho clena', 'registration_new_self'),

(15, 13, 3, 6, 'Pridani noveho clena', 'add_new'),

(16, 10, 12, 19, 'sprava uzivatelu', 'sprava_uzivatelu'),

(17, 16, 13, 14, 'Pridani noveho uzivatele', 'pridani_uzivatele'),

(18, 13, 7, 8, 'Prohlizeni detailu', 'prohlizeni_detailu_clena'),

(19, 13, 9, 10, 'Editace clena', 'member_edit'),

(20, 16, 15, 16, 'Editace uzivatele', 'user_edit'),

(21, 16, 17, 18, 'Prohlizeni detailu', 'user_show'),

(22, 10, 20, 21, 'Sprava praci', 'works'),

(23, 10, 22, 27, 'Sprava uctu a transakci', 'accounts_transfers'),

(24, 23, 23, 24, 'Sprava uctu', 'accounts'),

(25, 23, 25, 26, 'Sprava transakci', 'transfers'),

(29, 10, 28, 29, 'Administrace prihlasek', 'member_registration'),

(30, 10, 30, 33, 'Spava zarizeni', 'sprava_zarizeni'),

(31, 30, 31, 32, 'Správa rozhraní', 'sprava_rozhrani');",

"CREATE TABLE `axo_groups_id_seq` (

`id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `axo_groups_id_seq` (`id`) VALUES (31);",

"CREATE TABLE `axo_groups_map` (

`acl_id` int(11) NOT NULL default '0',

`group_id` int(11) NOT NULL default '0',

PRIMARY KEY (`acl_id`,`group_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `axo_groups_map` (`acl_id`, `group_id`) VALUES

(19, 10),

(22, 17);",

"CREATE TABLE `axo_map` (

`acl_id` int(11) NOT NULL default '0',

`section_value` varchar(230) collate utf8_czech_ci NOT NULL default '0',

`value` varchar(230) collate utf8_czech_ci NOT NULL,

PRIMARY KEY (`acl_id`,`section_value`,`value`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `axo_map` (`acl_id`, `section_value`, `value`) VALUES

(24, 'Users_Controller', 'edit_email'),

(24, 'Users_Controller', 'edit_phone'),

(24, 'Users_Controller', 'edit_username'),

(24, 'Users_Controller', 'view_works');",

"CREATE TABLE `axo_sections` (

`id` int(11) NOT NULL default '0',

`value` varchar(230) collate utf8_czech_ci NOT NULL,

`order_value` int(11) NOT NULL default '0',

`name` varchar(230) collate utf8_czech_ci NOT NULL,

`hidden` int(11) NOT NULL default '0',

PRIMARY KEY (`id`),

UNIQUE KEY `value_axo_sections` (`value`),

KEY `hidden_axo_sections` (`hidden`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `axo_sections` (`id`, `value`, `order_value`, `name`, `hidden`) VALUES

(10, 'Registration_Controller', 1, 'registrace noveho clena', 0),

(13, 'Members_Controller', 1, 'Radni clenove', 0),

(14, 'Users_Controller', 1, 'Uzivatele radnych clenu', 0),

(15, 'Works_Controller', 1, 'Prace uzivatelu', 0),

(16, 'Accounts_Controller', 1, 'Ucetni a transakcni veci', 0),

(17, 'device', 1, 'Za&#345;ízení', 0);",

"CREATE TABLE `axo_sections_seq` (

`id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `axo_sections_seq` (`id`) VALUES (17);",

"CREATE TABLE `axo_seq` (

`id` int(11) NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `axo_seq` (`id`) VALUES (90);",

"CREATE TABLE `bank_fees` (

`id` int(11) NOT NULL auto_increment,

`fee` float NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `contacts` (

`user_id` int(11) default NULL,

`id` int(11) NOT NULL auto_increment,

`type` int(11) NOT NULL,

`value` varchar(255) collate utf8_czech_ci default NULL,

PRIMARY KEY (`id`),

KEY `user_contact` (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `device_admins` (

`id` int(11) NOT NULL auto_increment,

`device_id` int(11) default NULL,

`user_id` int(11) default NULL,

PRIMARY KEY (`id`),

KEY `device_has_admin` (`device_id`),

KEY `user_administers_device` (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `device_engineers` (

`id` int(10) NOT NULL auto_increment,

`device_id` int(11) NOT NULL,

`user_id` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `devices` (

`id` int(11) NOT NULL auto_increment,

`user_id` int(11) default NULL,

`name` varchar(254) collate utf8_czech_ci default NULL,

`type` int(11) NOT NULL,

`description` varchar(255) collate utf8_czech_ci default NULL,

`PPPoE_logging_in` tinyint(4) default NULL,

`login` varchar(254) collate utf8_czech_ci default NULL,

`password` varchar(254) collate utf8_czech_ci default NULL,

`GPS` varchar(100) collate utf8_czech_ci default NULL,

`location_address` varchar(254) collate utf8_czech_ci default NULL,

`location_details` varchar(254) collate utf8_czech_ci default NULL,

`comment` varchar(254) collate utf8_czech_ci default NULL,

PRIMARY KEY (`id`),

KEY `user_id` (`user_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `enum_types` (

`id` int(11) NOT NULL auto_increment,

`type_id` int(11) NOT NULL,

`value` varchar(254) collate utf8_czech_ci default NULL,

PRIMARY KEY (`id`),

KEY `value_of_type` (`type_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=27 ;",

"INSERT INTO `enum_types` (`id`, `type_id`, `value`) VALUES

(1, 1, 'Applicant'),

(2, 1, 'Regular member'),

(3, 1, 'Honorary member'),

(4, 1, 'Former member'),

(5, 1, 'Non-statutory member'),

(6, 1, 'Vacating member'),

(7, 2, 'PC'),

(8, 2, 'client'),

(9, 2, 'router'),

(10, 2, 'switch'),

(11, 3, 'member'),

(12, 3, 'member'),

(13, 3, 'user'),

(14, 3, 'other'),

(15, 1, 'Non-member'),

(16, 1, 'Bad payer'),

(17, 2, 'notebook'),

(18, 4, 'ICQ'),

(19, 4, 'Jabber'),

(20, 4, 'E-mail'),

(21, 4, 'Phone'),

(22, 4, 'Skype'),

(23, 4, 'MSN'),

(24, 2, 'home AP'),

(25, 4, 'Website'),

(26, 2, 'VoIP');",

"CREATE TABLE `members` (

`id` int(11) NOT NULL auto_increment,

`registration` tinyint(1) NOT NULL,

`name` varchar(100) collate utf8_czech_ci NOT NULL,

`street_number` varchar(50) collate utf8_czech_ci default NULL,

`street` varchar(250) collate utf8_czech_ci default NULL,

`town` varchar(250) collate utf8_czech_ci default NULL,

`ZIP_code` varchar(10) collate utf8_czech_ci default NULL,

`type` tinyint(4) default NULL,

`qos_ceil` varchar(20) collate utf8_czech_ci default NULL,

`qos_rate` varchar(20) collate utf8_czech_ci default NULL,

`entrance_fee` double default NULL,

`debt_payment_rate` double default NULL,

`entrance_fee_left` double default NULL,

`entrance_fee_date` date default NULL,

`must_pay_regular_fee` tinyint(4) default '1',

`current_credit` float default NULL,

`entrance_date` date default NULL,

`entrance_form_received` date default NULL,

`entrance_form_accepted` date default NULL,

`termination_date` date NOT NULL,

`comment` varchar(250) collate utf8_czech_ci default NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `users` (

`member_id` int(10) unsigned NOT NULL,

`id` int(11) NOT NULL auto_increment,

`login` varchar(50) collate utf8_czech_ci default NULL,

`password` varchar(50) collate utf8_czech_ci default NULL,

`name` varchar(30) collate utf8_czech_ci default NULL,

`middle_name` varchar(30) collate utf8_czech_ci default NULL,

`surname` varchar(60) collate utf8_czech_ci default NULL,

`pre_title` varchar(40) collate utf8_czech_ci default NULL,

`post_title` varchar(30) collate utf8_czech_ci default NULL,

`birthday` date default NULL,

`phone` varchar(40) collate utf8_czech_ci default NULL,

`email` varchar(60) collate utf8_czech_ci default NULL,

`type` enum('member','user','wannabe') collate utf8_czech_ci default NULL,

`web_messages_types` int(11) default NULL,

`email_messages_types` int(11) default NULL,

`comment` varchar(250) collate utf8_czech_ci default NULL,

`aro_section_value` varchar(240) collate utf8_czech_ci NOT NULL default 'all',

`aro_order_value` int(11) NOT NULL default '0',

`aro_hidden` int(11) NOT NULL default '0',

`radius_password` varchar(50) collate utf8_czech_ci NOT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `login` (`login`),

KEY `belogs_to_member` (`member_id`),

KEY `email` (`email`),

KEY `phone` (`phone`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `groups_aro_map` (

`group_id` int(11) NOT NULL default '0',

`aro_id` int(11) NOT NULL default '0',

PRIMARY KEY (`group_id`,`aro_id`),

KEY `aro_id` (`aro_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `groups_aro_map` (`group_id`, `aro_id`) VALUES (32, 1);",

"CREATE TABLE `groups_axo_map` (

`group_id` int(11) NOT NULL default '0',

`axo_id` int(11) NOT NULL default '0',

PRIMARY KEY (`group_id`,`axo_id`),

KEY `axo_id` (`axo_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `groups_axo_map` (`group_id`, `axo_id`) VALUES

(14, 15),

(14, 16),

(14, 17),

(18, 20),

(18, 21),

(18, 22),

(18, 23),

(19, 25),

(19, 26),

(19, 27),

(19, 28),

(19, 29),

(19, 30),

(19, 31),

(19, 32),

(19, 33),

(19, 34),

(19, 35),

(19, 36),

(19, 37),

(19, 38),

(19, 39),

(19, 40),

(19, 41),

(20, 42),

(20, 43),

(20, 44),

(20, 45),

(21, 46),

(21, 47),

(21, 48),

(21, 49),

(21, 50),

(17, 51),

(21, 52),

(21, 53),

(22, 54),

(25, 55),

(15, 56),

(15, 57),

(25, 58),

(24, 59),

(24, 60),

(24, 61),

(24, 62),

(25, 63),

(29, 64),

(19, 65);",

"CREATE TABLE `ifaces` (

`id` int(11) NOT NULL auto_increment,

`device_id` int(11) default NULL,

`segment_id` int(11) default NULL,

`MAC` varchar(20) collate utf8_czech_ci default NULL,

`name` varchar(254) collate utf8_czech_ci default NULL,

`comment` varchar(254) collate utf8_czech_ci default NULL,

PRIMARY KEY (`id`),

KEY `device_iface` (`device_id`),

KEY `segment_iface` (`segment_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `ip_addresses` (

`iface_id` int(11) default NULL,

`vlan_iface_id` int(11) default NULL,

`subnet_id` int(11) default NULL,

`IP_address` varchar(15) collate utf8_czech_ci default NULL,

`dhcp` tinyint(4) default NULL,

`id` int(11) NOT NULL auto_increment,

PRIMARY KEY (`id`),

KEY `iface_address` (`iface_id`),

KEY `subnet_address` (`subnet_id`),

KEY `VLAN_iface_address` (`vlan_iface_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `jobs` (

`id` int(11) NOT NULL auto_increment,

`user_id` int(11) NOT NULL,

`description` varchar(255) collate utf8_czech_ci default NULL,

`hours` float default NULL,

`confirmed_by_id` int(11) default NULL,

`transfer_id` int(11) default NULL,

PRIMARY KEY (`id`),

KEY `commited_by` (`user_id`),

KEY `transfer_salary` (`transfer_id`),

KEY `confirmed_by` (`confirmed_by_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `logging` (

`id` int(11) NOT NULL auto_increment,

`ip_address` varchar(15) collate utf8_czech_ci NOT NULL,

`prefix` varchar(30) collate utf8_czech_ci NOT NULL,

`unix` varchar(9) collate utf8_czech_ci default NULL,

`mikrotik` varchar(45) collate utf8_czech_ci default NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `prefix` (`prefix`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=12 ;",

"CREATE TABLE `menus` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(50) collate utf8_czech_ci default NULL,

`url` varchar(100) collate utf8_czech_ci default NULL,

`master_id` int(11) NOT NULL,

`sorting` int(11) default NULL,

`active` tinyint(1) NOT NULL default '1',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=34 ;",

"INSERT INTO `menus` (`id`, `name`, `url`, `master_id`, `sorting`, `active`) VALUES

(1, 'Members', 'members', 0, NULL, 1),

(2, 'Users', 'users', 0, NULL, 1),

(3, 'Přidat člena', 'pridat-clena', 1, NULL, 1),

(4, 'Seznam členů', 'seznam-clenu', 1, NULL, 1),

(5, 'pokus', 'pokus', 3, NULL, 1),

(6, 'Devices', 'devices', 0, NULL, 1),

(7, 'Devices list', 'devices/show_all', 6, NULL, 1),

(8, 'Add new device', 'devices/add', 6, NULL, 1),

(9, 'Interfaces', 'ifaces', 0, NULL, 1),

(10, 'Interfaces list', 'ifaces/show_all', 9, NULL, 1),

(11, 'Add new interface', 'ifaces/add', 9, NULL, 1),

(12, 'Ports', 'ports', 0, NULL, 1),

(13, 'Ports list', 'ports/show_all', 12, NULL, 1),

(14, 'Add new port', 'ports/add', 12, NULL, 1),

(15, 'VLAN interfaces', 'vlan_ifaces', 0, NULL, 1),

(16, 'VLAN interfaces list', 'vlan_ifaces/show_all', 15, NULL, 1),

(17, 'Add new VLAN interface', 'vlan_ifaces/add', 15, NULL, 1),

(18, 'VLANs', 'vlans', 0, NULL, 1),

(19, 'VLANs list', 'vlans/show_all', 18, NULL, 1),

(20, 'Add new VLAN', 'vlans/add', 18, NULL, 1),

(21, 'IP addresses', 'ip_addresses', 0, NULL, 1),

(22, 'IP addresses list', 'ip_addresses/show_all', 21, NULL, 1),

(23, 'Add new IP address', 'ip_addresses/add', 21, NULL, 1),

(24, 'Subnets', 'subnets', 0, NULL, 1),

(25, 'Subnets list', 'subnets/show_all', 24, NULL, 1),

(26, 'Add new subnet', 'subnets/add', 24, NULL, 1),

(27, 'Segments', 'segments', 0, NULL, 1),

(28, 'Segments list', 'segments/show_all', 27, NULL, 1),

(29, 'Add new segment', 'segments/add', 27, NULL, 1),

(30, 'Unidentified transfers', 'accounts/unidentified_transfers/', 0, NULL, 1),

(31, 'parse bank account', 'accounts/parse_ebank_account', 0, NULL, 1),

(32, 'works', 'works', 0, NULL, 1),

(33, 'logout', 'login/logout', 0, 5, 1);",

"CREATE TABLE `money_transfer_bank_infos` (

`id` int(11) NOT NULL auto_increment,

`variable_symbol` bigint(20) default NULL,

`constant_symbol` bigint(20) default NULL,

`specific_symbol` bigint(20) default NULL,

`name` varchar(250) collate utf8_czech_ci NOT NULL,

`date_time` datetime NOT NULL,

`identified` tinyint(1) default '0',

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `money_transfers` (

`id` int(11) NOT NULL auto_increment,

`bank_info_id` int(11) default NULL,

`origin_id` int(11) default NULL,

`destination_id` int(11) default NULL,

`previous_transfer_id` int(11) default NULL,

`timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

`text` varchar(254) collate utf8_czech_ci default NULL,

`amount` double default NULL,

PRIMARY KEY (`id`),

KEY `from` (`origin_id`),

KEY `has_bank_info` (`bank_info_id`),

KEY `to` (`destination_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `phpgacl` (

`name` varchar(230) collate utf8_czech_ci NOT NULL,

`value` varchar(230) collate utf8_czech_ci NOT NULL,

PRIMARY KEY (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `phpgacl` (`name`, `value`) VALUES

('schema_version', '2.1'),

('version', '3.3.7');",

"CREATE TABLE `ports` (

`id` int(11) NOT NULL auto_increment,

`device_id` int(11) default NULL,

`segment_id` int(11) default NULL,

`name` varchar(254) collate utf8_czech_ci default NULL,

PRIMARY KEY (`id`),

KEY `device_port` (`device_id`),

KEY `segment_port` (`segment_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `ports_vlans` (

`id` int(11) NOT NULL auto_increment,

`port_id` int(11) NOT NULL,

`vlan_id` int(11) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `segments` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(254) collate utf8_czech_ci default NULL,

`technology` varchar(254) collate utf8_czech_ci default NULL,

`bitrate` int(11) default NULL,

`duplex` tinyint(4) default NULL,

`comment` varchar(254) collate utf8_czech_ci default NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `subnets` (

`id` int(11) NOT NULL auto_increment,

`OSPF_area_id` int(11) default NULL,

`name` varchar(254) collate utf8_czech_ci default NULL,

`network_address` varchar(15) collate utf8_czech_ci default NULL,

`netmask` varchar(15) collate utf8_czech_ci default NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `translations` (

`id` int(11) NOT NULL auto_increment,

`original_term` varchar(254) collate utf8_czech_ci NOT NULL,

`translated_term` varchar(254) collate utf8_czech_ci NOT NULL,

`lang` varchar(50) collate utf8_czech_ci NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=14 ;",

"INSERT INTO `translations` (`id`, `original_term`, `translated_term`, `lang`) VALUES

(1, 'client', 'klient', 'cs'),

(2, 'home AP', 'domácí AP', 'cs'),

(3, 'Telephone', 'Telefon', 'cs'),

(4, 'Applicant', 'Čekatel na členství', 'cs'),

(5, 'Regular member', 'Řádný člen', 'cs'),

(6, 'Honorary member', 'Čestný člen', 'cs'),

(7, 'Former member', 'Bývalý člen', 'cs'),

(8, 'Vacating member', 'Prázdninový člen', 'cs'),

(9, 'Non-statutory member', 'Člen bez statusu', 'cs'),

(10, 'Bad payer', 'Neplatič', 'cs'),

(11, 'Non-member', 'Nečlen', 'cs'),

(12, 'Phone', 'Telefon', 'cs'),

(13, 'Website', 'Web', 'cs');",

"CREATE TABLE `vlan_ifaces` (

`id` int(11) NOT NULL auto_increment,

`vlan_id` int(11) default NULL,

`iface_id` int(11) default NULL,

`name` varchar(254) collate utf8_czech_ci default NULL,

PRIMARY KEY (`id`),

KEY `VLAN_on_iface` (`vlan_id`),

KEY `iface_belongs_to_VLAN` (`iface_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `vlans` (

`id` int(11) NOT NULL auto_increment,

`name` varchar(254) collate utf8_czech_ci default NULL,

`tag_802_1q` int(11) default NULL,

`comment` varchar(254) collate utf8_czech_ci default NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE TABLE `works` (

`id` int(11) NOT NULL auto_increment,

`user_id` int(11) NOT NULL,

`description` varchar(255) collate utf8_czech_ci default NULL,

`hours` float default NULL,

`confirmed_by_id` int(11) default NULL,

`transfer_id` int(11) default NULL,

PRIMARY KEY (`id`),

KEY `commited_by` (`user_id`),

KEY `transfer_salary` (`transfer_id`),

KEY `confirmed_by` (`confirmed_by_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"CREATE VIEW `aro` AS select `users`.`id` AS `id`,`users`.`aro_section_value` AS `section_value`,`users`.`login` AS `value`,`users`.`aro_order_value` AS `order_value`,concat(`users`.`login`,'-',`users`.`name`,' ' ,`users`.`surname`) AS `name`,`users`.`aro_hidden` AS `hidden` from `users`;",

"CREATE TABLE `accounts` (

`id` int(11) NOT NULL auto_increment,

`owner_id` int(11) default NULL,

`name` varchar(100) collate utf8_czech_ci default NULL,

`type` enum('bank','analytic','master','project','operating','infrastructure','suppliers','unidentified') collate utf8_czech_ci default NULL,

`start_amount` double default NULL,

`comment` varchar(254) collate utf8_czech_ci default NULL,

PRIMARY KEY (`id`),

KEY `is_owned_by` (`owner_id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;",

"INSERT INTO `accounts` (`id`, `owner_id`, `name`, `type`, `start_amount`, `comment`) VALUES

(1, 1, 'Master ucet', 'master', 0, 'Hlavni ucet sdruzeni, nic sem neposilat!'),

(2, 1, 'Infrastruktura', 'infrastructure', 0, NULL),

(3, 1, 'Operating account', 'operating', 0, NULL),

(4, 1, 'Dodavatelé', 'suppliers', 0, 'vsichni kterym neco platime'),

(5, 1, 'Neidentifikovane platby', 'unidentified', 0, 'Vsechny bankovni poplatky a vse co odchazi mimo tak poslat sem.'),

(6, 1, 'Default account', 'analytic', NULL, NULL);",

"CREATE TABLE IF NOT EXISTS `config` (

`name` varchar(100) COLLATE utf8_czech_ci NOT NULL,

`value` varchar(255) COLLATE utf8_czech_ci NOT NULL

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci;",

"INSERT INTO `config` (`name`, `value`) VALUES ('db_schema_version', '176');",

"CREATE FUNCTION `ip2str`(ip INT UNSIGNED) RETURNS varchar(255) CHARSET utf8

DETERMINISTIC

RETURN CONCAT(ip>>24 & 255, '.', ip>>16 & 255, '.', ip>>8 & 255, '.', ip & 255);",

"CREATE FUNCTION `mask2str`(ipmask INT) RETURNS varchar(255) CHARSET utf8

DETERMINISTIC

RETURN inet_ntoa(0xffffffff<<(32-ipmask) & 0xffffffff);",

"CREATE FUNCTION `str2ip`(ip VARCHAR(255)) RETURNS int(10) unsigned

DETERMINISTIC

RETURN (substring_index(ip,'.',1)<<24 |

substring_index(substring_index(ip,'.',2),'.',-1)<<16 |

substring_index(substring_index(ip,'.',-2), '.',1)<<8 |

substring_index(ip,'.',-1))")

);