source: trunk/Common/Setup/Updates.php@ 743

Last change on this file since 743 was 743, checked in by chronos, 10 years ago
  • Fixed: Upgrade method.
  • Modified: AppModule Upgrade method improvement.
File size: 76.3 KB
Line 
1<?php
2
3function UpdateTo493($Manager)
4{
5 $Manager->Execute("ALTER TABLE `UserOnline` CHANGE `User` `User` INT( 11 ) NULL DEFAULT NULL COMMENT 'User.Id'");
6}
7
8function UpdateTo494($Manager)
9{
10 $Manager->Execute("ALTER TABLE `FinanceOperation` DROP FOREIGN KEY `FinanceOperation_ibfk_2` ;\n".
11 "ALTER TABLE `FinanceOperation` CHANGE `Bill` `File` INT( 11 ) NULL DEFAULT NULL;\n".
12 "ALTER TABLE `FinanceOperation` ADD FOREIGN KEY ( `File` ) REFERENCES `File` (`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;");
13}
14
15function UpdateTo495($Manager)
16{
17 $Manager->Execute("INSERT INTO `MapPosition` (SELECT NULL AS `Id`, `Name`, `MapPositionX` AS `Latitude`, `MapPositionY` AS `Longitude` FROM `Subject`)");
18 $Manager->Execute("ALTER TABLE `Subject` ADD `MapPosition` INT NULL AFTER `PayVAT`");
19 $Manager->Execute("UPDATE `Subject` SET `MapPosition` = (SELECT `MapPosition`.`Id` FROM `MapPosition` WHERE `MapPosition`.`Name` = `Subject`.`Name` LIMIT 1)");
20 $Manager->Execute("ALTER TABLE `Subject` DROP `MapPositionX`, DROP `MapPositionY`;");
21 $Manager->Execute("ALTER TABLE `Subject` ADD INDEX ( `MapPosition` ) ");
22 $Manager->Execute("ALTER TABLE `Subject` ADD FOREIGN KEY ( `MapPosition` ) REFERENCES `MapPosition` (".
23 "`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;");
24}
25
26function UpdateTo497($Manager)
27{
28 $Manager->Execute("ALTER TABLE `FinanceCharge` ADD `Id` INT NOT NULL AUTO_INCREMENT FIRST , ADD PRIMARY KEY ( `Id` ) ");
29 $Manager->Execute("ALTER TABLE `FinanceCharge` ADD `ReplaceId` INT NULL ,ADD INDEX ( `ReplaceId` ) ");
30 $Manager->Execute("DELETE FROM `FinanceCharge` WHERE `FinanceCharge`.`Period` = 1");
31 $Manager->Execute("ALTER TABLE `FinanceCharge` DROP `Period`");
32 $Manager->Execute("ALTER TABLE `FinanceCharge` ADD `Action` ENUM( 'add', 'modify', 'remove' ) NULL AFTER `TopTariffPrice` ");
33 $Manager->Execute("ALTER TABLE `FinanceCharge` ADD FOREIGN KEY ( `ReplaceId` ) REFERENCES `FinanceCharge` (".
34 "`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;");
35 $Manager->Execute("ALTER TABLE `FinanceCharge` ADD INDEX ( `Action` )");
36 $Manager->Execute("INSERT INTO `ISMenuItem` (`Id` ,`Name` ,`Parent` ,`Table` ,`IconName`) ".
37 "VALUES (NULL , 'Parametry účtování', '2', 'FinanceCharge', '');");
38 $Manager->Execute("INSERT INTO `ISMenuItem` (`Id` ,`Name` ,`Parent` ,`Table` ,`IconName`) ".
39 "VALUES (NULL , 'Úseky sítě', '2', 'NetworkSegment', '');");
40 $Manager->Execute("ALTER TABLE `ServiceCustomerRel` ADD `Action` ENUM( 'add', 'modify', 'remove' ) NULL , ".
41 "ADD `ReplaceId` INT NULL");
42 $Manager->Execute("DELETE FROM `ServiceCustomerRel` WHERE `Period`=1");
43 $Manager->Execute("ALTER TABLE `ServiceCustomerRel` DROP `Period`");
44 $Manager->Execute("ALTER TABLE `ServiceCustomerRel` ADD INDEX ( `Action` )");
45 $Manager->Execute("ALTER TABLE `ServiceCustomerRel` ADD INDEX ( `ReplaceId` ) ");
46 $Manager->Execute("ALTER TABLE `ServiceCustomerRel` ADD FOREIGN KEY ( `ReplaceId` ) REFERENCES `ServiceCustomerRel` (".
47 "`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;");
48 $Manager->Execute("ALTER TABLE `Service` ADD `Action` ENUM( 'add', 'modify', 'remove' ) NULL AFTER `CustomerCount`");
49 $Manager->Execute("ALTER TABLE `Service` ADD INDEX ( `Action` )");
50 $Manager->Execute("INSERT INTO `ISMenuItem` (`Id` ,`Name` ,`Parent` ,`Table` ,`IconName`) ".
51 "VALUES (NULL , 'Placení zákazníků', '2', 'MemberPayment', '');");
52}
53
54function UpdateTo498($Manager)
55{
56 $Manager->Execute("INSERT INTO `ISMenuItem` (`Id` ,`Name` ,`Parent` ,`Table` ,`IconName`) ".
57 "VALUES (NULL , 'Síťové služby', '1', 'NetworkConfiguration', '');");
58 $Manager->Execute("INSERT INTO `ISMenuItem` (`Id` ,`Name` ,`Parent` ,`Table` ,`IconName`) ".
59 "VALUES (NULL , 'Fronta e-mailů', '23', 'EmailQueue', '');");
60 $Manager->Execute("CREATE TABLE IF NOT EXISTS `FinanceVAT` (
61 `Id` int(11) NOT NULL AUTO_INCREMENT,
62 `Name` varchar(255) NOT NULL,
63 `ValidFrom` date NOT NULL,
64 `Value` int(11) NOT NULL,
65 PRIMARY KEY (`Id`)
66) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
67INSERT INTO `FinanceVAT` (`Id`, `Name`, `ValidFrom`, `Value`) VALUES
68(1, 'DPH snížená sazba', '2013-01-01', 15),
69(2, 'DPH běžná sazba', '2013-01-01', 21);");
70 $Manager->Execute("ALTER TABLE `Service` ADD `VAT` INT NOT NULL AFTER `Price`");
71 $Manager->Execute("ALTER TABLE `Service` ADD INDEX ( `VAT` ) ");
72 $Manager->Execute("UPDATE `Service` SET `VAT` = 2;");
73 $Manager->Execute("ALTER TABLE `Service` ADD FOREIGN KEY ( `VAT` ) REFERENCES `FinanceVAT` (".
74 "`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;");
75 $Manager->Execute("ALTER TABLE `FinanceInvoice` ADD `Cash` INT NOT NULL DEFAULT '0'");
76}
77
78function UpdateTo499($Manager)
79{
80 $Manager->Execute("CREATE TABLE IF NOT EXISTS `Currency` (
81 `Id` int(11) NOT NULL AUTO_INCREMENT,
82 `Code` varchar(3) NOT NULL,
83 `Name` varchar(255) NOT NULL,
84 `Symbol` varchar(255) NOT NULL,
85 PRIMARY KEY (`Id`)
86) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;
87
88INSERT INTO `Currency` (`Id`, `Code`, `Name`, `Symbol`) VALUES
89(1, 'CZK', 'Česká koruna', 'Kč'),
90(2, 'EUR', 'Euro', '€'),
91(3, 'USD', 'Americký dolar', '$'),
92(4, 'CNY', 'Čínský jüan', '¥');");
93 $Manager->Execute("CREATE TABLE IF NOT EXISTS `FinanceBankImport` (
94 `Id` int(11) NOT NULL AUTO_INCREMENT,
95 `BankAccount` int(11) NOT NULL,
96 `Time` date NOT NULL,
97 `Identification` varchar(255) NOT NULL,
98 `AccountNumber` varchar(255) NOT NULL,
99 `VariableSymbol` varchar(255) NOT NULL,
100 `ConstantSymbol` varchar(255) NOT NULL,
101 `SpecificSymbol` varchar(255) NOT NULL,
102 `Value` decimal(10,0) NOT NULL,
103 `Currency` int(11) NOT NULL,
104 `Description` varchar(255) NOT NULL,
105 `OffsetAccountName` varchar(255) NOT NULL,
106 `FinanceOperation` int(11) DEFAULT NULL,
107 PRIMARY KEY (`Id`),
108 KEY `FinanceOperation` (`FinanceOperation`),
109 KEY `Currency` (`Currency`),
110 KEY `BankAccount` (`BankAccount`)
111) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
112
113ALTER TABLE `FinanceBankImport`
114 ADD CONSTRAINT `FinanceBankImport_ibfk_3` FOREIGN KEY (`FinanceOperation`) REFERENCES `FinanceOperation` (`Id`),
115 ADD CONSTRAINT `FinanceBankImport_ibfk_1` FOREIGN KEY (`BankAccount`) REFERENCES `FinanceBankAccount` (`Id`),
116 ADD CONSTRAINT `FinanceBankImport_ibfk_2` FOREIGN KEY (`Currency`) REFERENCES `Currency` (`Id`);");
117 $Manager->Execute("INSERT INTO `ISMenuItem` (`Id` ,`Name` ,`Parent` ,`Table` ,`IconName`)".
118 "VALUES (NULL , 'Měny', '2', 'Currency', '');");
119 $Manager->Execute("INSERT INTO `ISMenuItem` (`Id` ,`Name` ,`Parent` ,`Table` ,`IconName`) ".
120 "VALUES (NULL , 'Import z účtu', '2', 'FinanceBankImport', '');");
121}
122
123function UpdateTo500($Manager)
124{
125 $Manager->Execute("CREATE TABLE IF NOT EXISTS `FinanceBank` (
126 `Id` int(11) NOT NULL AUTO_INCREMENT,
127 `Name` varchar(255) NOT NULL,
128 `Code` varchar(4) NOT NULL,
129 `BIC` varchar(255) NOT NULL,
130 `Country` int(11) NOT NULL,
131 PRIMARY KEY (`Id`),
132 KEY `Country` (`Country`)
133) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;");
134 $Manager->Execute("ALTER TABLE `FinanceBank`
135 ADD CONSTRAINT `FinanceBank_ibfk_1` FOREIGN KEY (`Country`) REFERENCES `Country` (`Id`);");
136 $Manager->Execute("ALTER TABLE `FinanceBankAccount` ADD `Bank` INT NOT NULL AFTER `Number` ,".
137 " ADD INDEX ( `Bank` )");
138 $Manager->Execute("INSERT INTO `FinanceBank` (`Id`, `Name`, `Code`, `BIC`, `Country`) VALUES
139 (1, 'Neznámá banka', '', '', 1);");
140 $Manager->Execute("UPDATE `FinanceBankAccount` SET `Bank` = 1 WHERE `Bank` = 0");
141 $Manager->Execute("ALTER TABLE `FinanceBankAccount` ADD FOREIGN KEY ( `Bank` ) REFERENCES `FinanceBank` (".
142 "`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;");
143 $Manager->Execute("INSERT INTO `ISMenuItem` (`Id` ,`Name` ,`Parent` ,`Table` ,`IconName`) ".
144 "VALUES (NULL , 'Banky', '2', 'FinanceBank', '');");
145}
146
147function UpdateTo502($Manager)
148{
149 $Manager->Execute("ALTER TABLE `FinanceBankAccount` ADD `LoginName` VARCHAR( 255 ) NOT NULL ");
150 $Manager->Execute("ALTER TABLE `FinanceBankAccount` ADD `LoginPassword` VARCHAR( 255 ) NOT NULL");
151 $Manager->Execute("ALTER TABLE `FinanceBankAccount` ADD `Currency` INT NOT NULL AFTER `Use` ,".
152 "ADD INDEX ( `Currency` ) ");
153 $Manager->Execute("INSERT INTO `ISMenuItem` (`Id` ,`Name` ,`Parent` ,`Table` ,`IconName`) ".
154 "VALUES (NULL , 'Banka', '2', '', '');");
155 $Id = $Manager->Database->insert_id;
156 $Manager->Execute("UPDATE `ISMenuItem` SET `Parent` = '".$Id."' WHERE `ISMenuItem`.`Id` ='Bankovní účty'; ".
157 "UPDATE `ISMenuItem` SET `Parent` = '".$Id."' WHERE `ISMenuItem`.`Id` ='Import z účtu';".
158 "UPDATE `ISMenuItem` SET `Parent` = '".$Id."' WHERE `ISMenuItem`.`Name` ='Banky';");
159}
160
161function UpdateTo505($Manager)
162{
163 $Manager->Execute("UPDATE `ISMenuItem` SET `Name` = 'Služby', `Table` = 'Service' WHERE `ISMenuItem`.`Name` ='Tarify';");
164 $Manager->Execute("INSERT INTO `ISMenuItem` (`Id` ,`Name` ,`Parent` ,`Table` ,`IconName`) ".
165 "VALUES (NULL , 'Kategorie služeb', '2', 'ServiceCategory', '');");
166 $Manager->Execute("CREATE TABLE IF NOT EXISTS `NetworkShareItem` (
167 `Id` int(11) NOT NULL AUTO_INCREMENT,
168 `Name` varchar(255) NOT NULL DEFAULT '',
169 `Parent` int(11) NOT NULL DEFAULT '0',
170 `Host` int(11) NOT NULL DEFAULT '0',
171 `Size` bigint(20) NOT NULL DEFAULT '0',
172 `Ext` varchar(64) NOT NULL DEFAULT '',
173 `Date` date NOT NULL DEFAULT '0000-00-00',
174 `Type` int(11) NOT NULL DEFAULT '0',
175 PRIMARY KEY (`Id`),
176 KEY `Parent` (`Parent`),
177 KEY `Search` (`Name`,`Size`),
178 KEY `Name` (`Name`)
179) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;");
180 $Manager->Execute("CREATE TABLE IF NOT EXISTS `NetworkShareError` (
181 `Id` int(11) NOT NULL AUTO_INCREMENT,
182 `Host` varchar(16) NOT NULL DEFAULT '',
183 `Message` varchar(255) NOT NULL DEFAULT '',
184 PRIMARY KEY (`Id`)
185) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;");
186}
187
188function UpdateTo507($Manager)
189{
190 $Manager->Execute("INSERT INTO `ISMenuItem` (`Id` ,`Name` ,`Parent` ,`Table` ,`IconName`) ".
191 "VALUES (NULL , 'Aktuality', NULL, '', '');");
192 $Id = $Manager->Database->insert_id;
193 $Manager->Execute("INSERT INTO `ISMenuItem` (`Id` ,`Name` ,`Parent` ,`Table` ,`IconName`) ".
194 "VALUES (NULL , 'Aktuality', '".$Id."', 'News', '');");
195 $Manager->Execute("INSERT INTO `ISMenuItem` (`Id` ,`Name` ,`Parent` ,`Table` ,`IconName`) ".
196 "VALUES (NULL , 'Kategorie aktualit', '".$Id."', 'NewsCategory', '');");
197 $Manager->Execute("INSERT INTO `FileDirectory` (`Id` ,`Name` ,`Parent`".
198 ") VALUES (NULL , 'news', NULL);");
199 $Manager->Execute("ALTER TABLE `File` ADD `Time` DATETIME NOT NULL ");
200 // Convert Hire column to Service item
201 $Manager->Execute("INSERT INTO `ServiceCategory` (
202`Id` ,`Name`) VALUES (NULL , 'Pevný nájem');");
203 $ServiceCategoryHire = $Manager->Database->insert_id;
204 $DbResult = $Manager->Execute("SELECT * FROM Member WHERE Hire>0");
205 while($Member = $DbResult->fetch_assoc())
206 {
207 $Manager->Execute("INSERT INTO `Service` (
208`Id` ,`Name` ,`Category` ,`Price` ,`VAT` ,`CustomerCount` ,`Action` ,`ReplaceId` ,
209`Public` ,`InternetSpeedMin` ,`InternetSpeedMax` ,`UploadAsymmetry` ,`Memory` ,`MemorySwap` ,`Storage` ,`CPUCount`)
210VALUES (
211NULL , 'Nájem', '".$ServiceCategoryHire."', '-".$Member['Hire']."', '2', '', NULL , NULL , '', '', '', '', '', '', '', ''
212);");
213 $ServiceHire = $Manager->Database->insert_id;
214 $Manager->Execute("INSERT INTO `ServiceCustomerRel` (`Service`, `Customer`) VALUES ".
215 "(".$ServiceHire.",".$Member['Id'].")");
216 }
217 $Manager->Execute("ALTER TABLE `Member` DROP `Hire`");
218 // Each device and subnet have to be assigned to payed service to determine service parameters
219 $Manager->Execute("ALTER TABLE `NetworkDevice` ADD `Service` INT NULL AFTER `Location` ,
220ADD INDEX ( `Service` )");
221 $Manager->Execute("ALTER TABLE `NetworkDevice` ADD FOREIGN KEY ( `Service` ) REFERENCES `ServiceCustomerRel` (
222`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;");
223 $Manager->Execute("UPDATE `NetworkDevice` SET `Service` = (SELECT `Id` FROM `ServiceCustomerRel` WHERE `ServiceCustomerRel`.`Customer` = `NetworkDevice`.`Member` LIMIT 1)");
224 $Manager->Execute("ALTER TABLE `NetworkSubnet` ADD `Service` INT NULL AFTER `Member` ,
225ADD INDEX ( `Service` ) ");
226 $Manager->Execute("ALTER TABLE `NetworkSubnet` ADD FOREIGN KEY ( `Service` ) REFERENCES `ServiceCustomerRel` (
227`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;");
228 $Manager->Execute("UPDATE `NetworkSubnet` SET `Service` = (SELECT `Id` FROM `ServiceCustomerRel` WHERE `ServiceCustomerRel`.`Customer` = `NetworkSubnet`.`Member` LIMIT 1)");
229}
230
231function UpdateTo515($Manager)
232{
233 $Manager->Execute("ALTER TABLE `PermissionUserAssignment` CHANGE `User` `User` INT( 11 ) NULL");
234}
235
236function UpdateTo517($Manager)
237{
238 $Manager->Execute("ALTER TABLE `Log` ADD `IPAddress` VARCHAR( 16 ) NOT NULL");
239}
240
241function UpdateTo526($Manager)
242{
243 $Manager->Execute("ALTER TABLE `Hyperlink` CHANGE `Name` `Title` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_czech_ci NOT NULL");
244 $Manager->Execute("ALTER TABLE `Hyperlink` ADD `Name` VARCHAR( 255 ) NOT NULL AFTER `Id`");
245 //$Manager->Execute("ALTER TABLE `Hyperlink` ADD UNIQUE ( `Name` )");
246 $Manager->Execute("ALTER TABLE `Hyperlink` ADD `Type` INT NOT NULL AFTER `Title` , ADD INDEX ( `Type` )");
247 $Manager->Execute("RENAME TABLE `Hyperlink` TO `Action` ;");
248 $Manager->Execute("RENAME TABLE `HyperlinkGroup` TO `ActionGroup` ;");
249 $Manager->Execute("CREATE TABLE IF NOT EXISTS `ActionType` (
250 `Id` int(11) NOT NULL AUTO_INCREMENT,
251 `Name` varchar(255) NOT NULL,
252 PRIMARY KEY (`Id`)
253 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;
254
255 INSERT INTO `ActionType` (`Id`, `Name`) VALUES
256 (1, 'Odkaz'),
257 (2, 'Obrázek');");
258 $Manager->Execute("UPDATE `Action` SET `Type` = 1;");
259 $Manager->Execute("ALTER TABLE `Action` ADD FOREIGN KEY ( `Type` ) REFERENCES `ActionType` (".
260 "`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;");
261 $Manager->Execute("UPDATE `Panel` SET `Module` = 'ActionGroup' WHERE `Module` = 'HyperlinkGroup'");
262 $Manager->Execute("CREATE TABLE IF NOT EXISTS `ActionIcon` (
263 `Id` int(11) NOT NULL AUTO_INCREMENT,
264 `Name` varchar(255) NOT NULL,
265 PRIMARY KEY (`Id`)
266) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;");
267 $Manager->Execute("INSERT INTO `ActionIcon` (SELECT NULL AS `Id`, `IconFile` AS `Name` FROM `Action` WHERE `IconFile` != '' GROUP BY `IconFile`)");
268 $Manager->Execute("ALTER TABLE `Action` CHANGE `IconFile` `IconFile` VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL");
269 $Manager->Execute("UPDATE `Action` SET `IconFile` = (SELECT `Id` FROM `ActionIcon` WHERE `ActionIcon`.`Name` = `Action`.`IconFile`) ");
270 $Manager->Execute("ALTER TABLE `Action` CHANGE `IconFile` `Icon` INT NULL ");
271 $Manager->Execute("UPDATE `Action` SET `Icon` = NULL WHERE `Icon` = 0");
272 $Manager->Execute("ALTER TABLE `Action` ADD INDEX ( `Icon` ) ");
273 $Manager->Execute("ALTER TABLE `Action` ADD FOREIGN KEY ( `Icon` ) REFERENCES `ActionIcon` (`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;");
274 $Manager->Execute("INSERT INTO `ISMenuItem` (`Id` ,`Name` ,`Parent` ,`Table` ,`IconName`) ".
275 "VALUES (NULL , 'Akce', '23', 'Action', '');");
276}
277
278function UpdateTo527($Manager)
279{
280 $Manager->Execute("RENAME TABLE `ISMenuItem` TO `MenuItem` ;");
281 $Manager->Execute("CREATE TABLE IF NOT EXISTS `Menu` (
282 `Id` int(11) NOT NULL AUTO_INCREMENT,
283 `Name` varchar(255) NOT NULL,
284 PRIMARY KEY (`Id`)
285) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;");
286 $Manager->Execute("INSERT INTO `Menu` (`Id` ,`Name`)VALUES (NULL , 'IS');");
287 $Manager->Execute("ALTER TABLE `MenuItem` ADD `Menu` INT NOT NULL , ADD INDEX ( `Menu` )");
288 $Manager->Execute("UPDATE `MenuItem` SET `Menu` = 1");
289 $Manager->Execute("ALTER TABLE `MenuItem` ADD FOREIGN KEY ( `Menu` ) REFERENCES `Menu` (".
290 "`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;");
291 $Manager->Execute("ALTER TABLE `Action` CHANGE `Group` `Group` INT( 11 ) NULL ");
292 $Manager->Execute("INSERT INTO `Action` (SELECT NULL AS `Id`, '' AS `Name`, `Name` AS `Title`, 1 AS `Type`, CONCAT('/is/?t=', `Table`, '&a=list') AS `URL`, NULL AS `Group`, NULL AS `Icon`, NULL AS `PermisionModule`, NULL AS `PermissionOperation`, 1 AS `Enable` FROM `MenuItem` WHERE `Parent` IS NOT NULL)");
293 $Manager->Execute("ALTER TABLE `MenuItem` DROP `IconName`");
294 $Manager->Execute("UPDATE `MenuItem` SET `Table` = (SELECT `Id` FROM `Action` WHERE `Action`.`URL` = CONCAT('/is/?t=', `MenuItem`.`Table`, '&a=list')) WHERE `MenuItem`.`Table` != ''");
295 $Manager->Execute("ALTER TABLE `MenuItem` CHANGE `Table` `Action` INT NULL");
296 $Manager->Execute("UPDATE `MenuItem` SET `Action` = NULL WHERE `Action` = 0");
297 $Manager->Execute("ALTER TABLE `MenuItem` ADD INDEX ( `Action` ) ");
298 $Manager->Execute("ALTER TABLE `MenuItem` ADD FOREIGN KEY ( `Action` ) REFERENCES `Action` (`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;");
299 $Manager->Execute("INSERT INTO `Action` (`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,`Group` ,`Icon` ,`PermissionModule` ,`PermissionOperation` ,`Enable`)".
300 "VALUES (NULL , '', 'Nabídky', '1', '/is/?t=Menu&a=list', NULL , NULL , '', '', '1');");
301 $ActionId = $Manager->Database->insert_id;
302 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
303 "VALUES (NULL , 'Nabídky', '23', '".$ActionId."', '1');");
304}
305
306function UpdateTo535($Manager)
307{
308 // Set all string collation to utf8 general
309 $DbResult = $Manager->Execute("SHOW TABLES");
310 while($DbRow = $DbResult->fetch_row())
311 {
312 $Manager->Execute("ALTER TABLE `".$DbRow[0]."` CONVERT TO CHARACTER SET utf8");
313 }
314}
315
316function UpdateTo549($Manager)
317{
318 $Manager->Execute("ALTER TABLE `FinanceOperation` ADD `Generate` INT NOT NULL DEFAULT '0',
319ADD INDEX ( `Generate` ) ;");
320 $Manager->Execute("ALTER TABLE `FinanceInvoice` ADD `Generate` INT NOT NULL DEFAULT '0',
321ADD INDEX ( `Generate` ) ;");
322}
323
324function UpdateTo550($Manager)
325{
326 $Manager->Execute('ALTER TABLE `FinanceBankAccount` ADD `LastImportId` VARCHAR( 255 ) NOT NULL ;');
327 $Manager->Execute('ALTER TABLE `FinanceBankAccount` ADD `LastImportDate` DATE NULL ;');
328 $Manager->Execute('ALTER TABLE `FinanceBankImport` ADD `BankCode` VARCHAR( 255 ) NOT NULL AFTER `AccountNumber` ;');
329}
330
331function UpdateTo551($Manager)
332{
333 $Manager->Execute('ALTER TABLE `FinanceOperation` ADD `DocumentLine` INT NULL AFTER `Value` ,
334ADD INDEX ( `DocumentLine` ) ;');
335 $Manager->Execute('ALTER TABLE `FinanceOperation` ADD FOREIGN KEY ( `DocumentLine` ) REFERENCES `DocumentLine` (
336`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;');
337 $Manager->Execute('ALTER TABLE `FinanceInvoice` ADD `DocumentLine` INT NULL AFTER `Id` ,
338ADD INDEX ( `DocumentLine` ) ;');
339 $Manager->Execute('ALTER TABLE `FinanceInvoice` ADD FOREIGN KEY ( `DocumentLine` ) REFERENCES `DocumentLine` (
340`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;');
341 $Manager->Execute('DROP TABLE `FinanceBillsItemsOld`');
342 $Manager->Execute('DROP TABLE `FinanceBillsOld`');
343}
344
345function UpdateTo565($Manager)
346{
347 $Manager->Execute('CREATE TABLE IF NOT EXISTS `WikiPage` (
348 `Id` int(11) NOT NULL AUTO_INCREMENT,
349 `Name` varchar(255) NOT NULL,
350 `NormalizedName` varchar(255) NOT NULL,
351 `VisibleInMenu` int(11) NOT NULL,
352 PRIMARY KEY (`Id`),
353 UNIQUE KEY `Name` (`Name`),
354 KEY `VisibleInMenu` (`VisibleInMenu`)
355 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
356 $Manager->Execute('CREATE TABLE IF NOT EXISTS `WikiPageContent` (
357 `Id` int(11) NOT NULL AUTO_INCREMENT,
358 `Page` int(11) NOT NULL,
359 `Time` datetime NOT NULL,
360 `Content` text NOT NULL,
361 `User` int(11) NOT NULL,
362 PRIMARY KEY (`Id`),
363 KEY `User` (`User`),
364 KEY `Page` (`Page`)
365 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
366 $Manager->Execute('ALTER TABLE `WikiPageContent`
367 ADD CONSTRAINT `WikiPageContent_ibfk_2` FOREIGN KEY (`Page`) REFERENCES `WikiPage` (`Id`),
368ADD CONSTRAINT `WikiPageContent_ibfk_1` FOREIGN KEY (`User`) REFERENCES `User` (`ID`);');
369}
370
371function UpdateTo571($Manager)
372{
373 $Manager->Execute('ALTER TABLE `NetworkDevice` ADD `LoginName` VARCHAR( 255 ) NOT NULL ,
374ADD `LoginPassword` VARCHAR( 255 ) NOT NULL ;');
375}
376
377function UpdateTo574($Manager)
378{
379 $Manager->Execute('ALTER TABLE `MapPosition` ADD `Pos` VARCHAR( 255 ) NOT NULL ;');
380 $Manager->Execute('UPDATE `MapPosition` SET `Pos`=CONCAT(`Latitude`, ";", `Longitude`)');
381 $Manager->Execute('ALTER TABLE `MapPosition` DROP `Latitude`');
382 $Manager->Execute('ALTER TABLE `MapPosition` DROP `Longitude`');
383}
384
385function UpdateTo584($Manager)
386{
387 $Manager->Execute("CREATE TABLE IF NOT EXISTS `Module` (
388 `Id` int(11) NOT NULL AUTO_INCREMENT,
389 `Name` varchar(255) NOT NULL,
390 `Title` varchar(255) NOT NULL,
391 PRIMARY KEY (`Id`)
392 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=33 ;");
393
394 $Manager->Execute("INSERT INTO `Module` (`Id`, `Name`, `Title`) VALUES
395 (1, 'Customer', 'Zákazník'),
396 (2, 'EmailQueue', 'Fronta emailů'),
397 (3, 'Error', 'Obsluha chyb'),
398 (4, 'File', 'Správa souborů'),
399 (5, 'Finance', 'Finance'),
400 (6, 'FinanceBankAPI', 'Rozhraní pro bankovní účty'),
401 (7, 'Chat', 'Pokec'),
402 (8, 'IS', 'Informační systém'),
403 (9, 'Log', 'Záznam událostí'),
404 (10, 'Map', 'Mapa'),
405 (11, 'Meals', 'Jídelníček'),
406 (12, 'Meteostation', 'Meteostanice'),
407 (13, 'Network', 'Síť'),
408 (14, 'NetworkConfig', 'Nastavení sítě'),
409 (15, 'NetworkConfigLinux', 'Nastavení linuxových zařízení'),
410 (16, 'NetworkConfigRouterOS', 'Nastavení RouterOS zařízení'),
411 (17, 'NetworkShare', 'Síťové sdílení'),
412 (18, 'NetworkTopology', 'Síťová topologie'),
413 (19, 'News', 'Aktuality'),
414 (20, 'OpeningHours', 'Otvírací doby'),
415 (21, 'Portal', 'Portál'),
416 (22, 'Search', 'Vyhledávání'),
417 (23, 'Setup', 'Instalace systému'),
418 (24, 'SpeedTest', 'Měření rychlosti'),
419 (25, 'Stock', 'Sklad'),
420 (26, 'System', 'Systém'),
421 (27, 'Task', 'Úlohy'),
422 (28, 'TimeMeasure', 'Časové průběhy'),
423 (29, 'TV', 'Televize'),
424 (30, 'User', 'Uživatelé'),
425 (31, 'WebCam', 'Web kamery'),
426 (32, 'Wiki', 'Wiki');");
427 $Manager->Execute("UPDATE `PermissionOperation` SET `Module` = 'Meals' WHERE `PermissionOperation`.`Module` ='EatingPlace';");
428 $Manager->Execute("UPDATE `PermissionOperation` SET `Module` = 'NetworkShare' WHERE `PermissionOperation`.`Module` ='Share';");
429 $Manager->Execute("UPDATE `PermissionOperation` SET `Module` = 'OpeningHours' WHERE `PermissionOperation`.`Module` ='SubjectOpenTime';");
430 $Manager->Execute("UPDATE `PermissionOperation` SET `Module`=(SELECT `Id` FROM `Module` WHERE `Module`.`Name` = `PermissionOperation`.`Module`)");
431 $Manager->Execute("ALTER TABLE `PermissionOperation` CHANGE `Module` `Module` INT( 11 ) NOT NULL ;");
432 $Manager->Execute("ALTER TABLE `PermissionOperation` ADD FOREIGN KEY ( `Module` ) REFERENCES `Module` (
433`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;");
434 $Manager->Execute("UPDATE `Action` SET `Action`.`PermissionOperation` =(SELECT Id FROM `PermissionOperation` WHERE `PermissionOperation`.`Operation`=`Action`.`PermissionOperation` AND `PermissionOperation`.`Module` = (SELECT Id FROM `Module` WHERE `Module`.`Name`=`Action`.`PermissionModule`))");
435 $Manager->Execute("ALTER TABLE `Action` CHANGE `PermissionOperation` `PermissionOperation` INT( 11 ) NULL ;");
436 $Manager->Execute("ALTER TABLE `Action` DROP `PermissionModule` ;");
437 $Manager->Execute("UPDATE `Action` SET `PermissionOperation`=NULL WHERE `PermissionOperation`=0");
438 $Manager->Execute("ALTER TABLE `Action` ADD INDEX (`PermissionOperation`);");
439 $Manager->Execute("ALTER TABLE `Action` ADD FOREIGN KEY ( `PermissionOperation` ) REFERENCES `PermissionOperation` (
440`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;");
441}
442
443function UpdateTo591($Manager)
444{
445 $Manager->Execute('ALTER TABLE `StockItem` ADD `Esemble` INT NULL ,
446ADD INDEX ( `Esemble` ) ;');
447 $Manager->Execute('ALTER TABLE `StockItem` ADD FOREIGN KEY ( `Esemble` ) REFERENCES `StockItem` (
448`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;');
449}
450
451function UpdateTo597($Manager)
452{
453 $Manager->Execute('CREATE TABLE IF NOT EXISTS `Model` (
454 `Id` int(11) NOT NULL AUTO_INCREMENT,
455 `Name` varchar(255) NOT NULL,
456 `Module` int(11) NOT NULL,
457 PRIMARY KEY (`Id`),
458 KEY `Module` (`Module`)
459) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
460
461 $Manager->Execute('ALTER TABLE `Model`
462 ADD CONSTRAINT `Model_ibfk_1` FOREIGN KEY (`Module`) REFERENCES `Module` (`Id`);');
463}
464
465function UpdateTo601($Manager)
466{
467 $Manager->Execute('CREATE TABLE IF NOT EXISTS `NetworkInterfaceWireless` (
468 `Id` int(11) NOT NULL AUTO_INCREMENT,
469 `NetworkInterface` int(11) NOT NULL,
470 `TxPower` int(11) NOT NULL,
471 `CableAttenuation` int(11) NOT NULL,
472 `AntenaGain` int(11) NOT NULL,
473 `Frequency` float NOT NULL,
474 `ChannelWidth` int(11) NOT NULL,
475 `SSID` varchar(255) NOT NULL,
476 PRIMARY KEY (`Id`),
477 KEY `NetworkInterface` (`NetworkInterface`)
478) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
479
480 $Manager->Execute('ALTER TABLE `NetworkInterfaceWireless`
481 ADD CONSTRAINT `NetworkInterfaceWireless_ibfk_1` FOREIGN KEY (`NetworkInterface`) REFERENCES `NetworkInterface` (`Id`);');
482 $Manager->Execute("INSERT INTO `Action` (
483`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,`Group` ,`Icon` ,`PermissionOperation` ,
484`Enable`) VALUES (NULL , '', 'Wifi rozhraní', '1', '/is/?t=NetworkInterfaceWireless&a=list', NULL , NULL , NULL , '1'
485);");
486 $ActionId = $Manager->Database->insert_id;
487 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
488 "VALUES (NULL , 'Bezdrátové rozhraní', '1', '".$ActionId."', '1');");
489}
490
491function UpdateTo615($Manager)
492{
493 $Manager->Execute('ALTER TABLE `NetworkInterfaceWireless` ADD `AntennaPolarity` INT NOT NULL ,
494ADD `Description` VARCHAR( 255 ) NOT NULL ;');
495 $Manager->Execute('CREATE TABLE IF NOT EXISTS `NewsImport` (
496 `Id` int(11) NOT NULL,
497 `Name` varchar(255) NOT NULL,
498 `Enabled` int(11) NOT NULL,
499 `Source` varchar(255) NOT NULL,
500 `Category` int(11) NOT NULL,
501 `BlockStart` varchar(255) NOT NULL,
502 `BlockEnd` varchar(255) NOT NULL,
503 `ItemStart` varchar(255) NOT NULL,
504 `ItemEnd` varchar(255) NOT NULL,
505 `TitleStart` varchar(255) NOT NULL,
506 `TitleEnd` varchar(255) NOT NULL,
507 `ContentStart` varchar(255) NOT NULL,
508 `ContentEnd` varchar(255) NOT NULL,
509 `LinkStart` varchar(255) NOT NULL,
510 `LinkEnd` varchar(255) NOT NULL,
511 `DateStart` varchar(255) NOT NULL,
512 `DateEnd` varchar(255) NOT NULL,
513 KEY `Category` (`Category`)
514) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
515 $Manager->Execute('ALTER TABLE `NewsImport` '.
516 'ADD CONSTRAINT `NewsImport_ibfk_1` FOREIGN KEY (`Category`) '.
517 ' REFERENCES `NewsCategory` (`Id`);');
518 $Manager->Execute('ALTER TABLE `NetworkInterfaceWireless` ADD `Antenna` INT NULL AFTER `AntennaPolarity` ,
519ADD INDEX ( `Product` ) ;');
520 $Manager->Execute('ALTER TABLE `NetworkInterfaceWireless` ADD FOREIGN KEY ( `Antenna` ) REFERENCES `Product` (
521`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;');
522 $Manager->Execute('ALTER TABLE `NetworkInterfaceWireless` ADD `MAC` VARCHAR( 17 ) NOT NULL ;');
523}
524
525function UpdateTo619($Manager)
526{
527 $Manager->Execute('ALTER TABLE `UserOnline` ADD `StayLogged` INT NOT NULL ;');
528}
529
530function UpdateTo620($Manager)
531{
532 $Manager->Execute('ALTER TABLE `NetworkInterfaceWireless` ADD `ChannelWidthLower` INT NOT NULL ,
533ADD `ChannelWidthUpper` INT NOT NULL ;');
534 $Manager->Execute('ALTER TABLE `NetworkInterfaceWireless` ADD `Mode` INT NOT NULL ;');
535 $Manager->Execute('DROP TABLE `NetworkAP`');
536}
537
538function UpdateTo627($Manager)
539{
540 $Manager->Execute('ALTER TABLE `FinanceInvoice` CHANGE `TimeCreation` `Time` DATETIME NOT NULL DEFAULT "0000-00-00 00:00:00";');
541 $Manager->Execute('ALTER TABLE `FinanceYear` ADD `Closed` INT NOT NULL ;');
542}
543
544function UpdateTo632($Manager)
545{
546 $Manager->Execute('CREATE TABLE IF NOT EXISTS `FinanceInvoiceOperationRel` (
547 `Id` int(11) NOT NULL AUTO_INCREMENT,
548 `Invoice` int(11) NOT NULL,
549 `Operation` int(11) NOT NULL,
550 PRIMARY KEY (`Id`),
551 KEY `Invoice` (`Invoice`),
552 KEY `Operation` (`Operation`)
553) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
554 $Manager->Execute('ALTER TABLE `FinanceInvoiceOperationRel`
555 ADD CONSTRAINT `FinanceInvoiceOperationRel_ibfk_2` FOREIGN KEY (`Operation`) REFERENCES `FinanceOperation` (`Id`),
556 ADD CONSTRAINT `FinanceInvoiceOperationRel_ibfk_1` FOREIGN KEY (`Invoice`) REFERENCES `FinanceInvoice` (`Id`);');
557}
558
559function UpdateTo633($Manager)
560{
561 $Manager->Execute('ALTER TABLE `UserOnline` ADD `StayLoggedHash` VARCHAR( 40 ) NOT NULL ;');
562}
563
564function UpdateTo645($Manager)
565{
566 $Manager->Execute('CREATE TABLE IF NOT EXISTS `FinanceVATType` (
567 `Id` int(11) NOT NULL,
568 `Name` varchar(255) NOT NULL,
569 PRIMARY KEY (`Id`)
570) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
571 $Manager->Execute('INSERT INTO `FinanceVATType` (SELECT Id, Name FROM `FinanceVAT`)');
572 $Manager->Execute('ALTER TABLE `FinanceVAT` ADD `ValidTo` DATE NULL ,
573ADD `Type` INT NOT NULL ;');
574 $Manager->Execute('UPDATE `FinanceVAT` SET `Type`=(SELECT `Id` FROM `FinanceVATType` WHERE `FinanceVATType`.`Name`=`FinanceVAT`.`Name`)');
575 $Manager->Execute('ALTER TABLE `FinanceVAT` DROP `Name`');
576}
577
578function UpdateTo646($Manager)
579{
580 $Manager->Execute('CREATE TABLE IF NOT EXISTS `Contract` (
581 `Id` int(11) NOT NULL AUTO_INCREMENT,
582 `BillCode` varchar(255) NOT NULL,
583 `Subject` int(11) NOT NULL,
584 `DocumentLine` int(11) NOT NULL,
585 `ValidFrom` date NOT NULL,
586 `ValidTo` date DEFAULT NULL,
587 `File` int(11) DEFAULT NULL,
588 PRIMARY KEY (`Id`),
589 KEY `Subject` (`Subject`),
590 KEY `DocumentLine` (`DocumentLine`),
591 KEY `File` (`File`)
592) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
593 $Manager->Execute('ALTER TABLE `Contract`
594 ADD CONSTRAINT `Contract_ibfk_3` FOREIGN KEY (`File`) REFERENCES `File` (`Id`),
595 ADD CONSTRAINT `Contract_ibfk_1` FOREIGN KEY (`Subject`) REFERENCES `Subject` (`Id`),
596 ADD CONSTRAINT `Contract_ibfk_2` FOREIGN KEY (`DocumentLine`) REFERENCES `DocumentLine` (`Id`);
597');
598 $Manager->Execute("INSERT INTO `Action` (
599`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,`Group` ,`Icon` ,`PermissionOperation` ,
600`Enable`) VALUES (NULL , '', 'Smlouvy', '1', '/is/?t=Contract&a=list', NULL , NULL , NULL , '1'
601);");
602 $ActionId = $Manager->Database->insert_id;
603 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
604 "VALUES (NULL , 'Smlouvy', '2', '".$ActionId."', '1');");
605 $Manager->Execute("INSERT INTO `Action` (
606`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,`Group` ,`Icon` ,`PermissionOperation` ,
607`Enable`) VALUES (NULL , '', 'Sazby DPH', '1', '/is/?t=FinanceVAT&a=list', NULL , NULL , NULL , '1'
608);");
609 $ActionId = $Manager->Database->insert_id;
610 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
611 "VALUES (NULL , 'Sazby DPH', '2', '".$ActionId."', '1');");
612}
613
614function UpdateTo647($Manager)
615{
616 $Manager->Execute('ALTER TABLE `EmployeeSalary` ADD FOREIGN KEY ( `Employee` ) REFERENCES `Employee` (
617`Id`
618) ON DELETE RESTRICT ON UPDATE RESTRICT ;');
619 $Manager->Execute('ALTER TABLE `EmployeeSalary` ADD `Contract` INT NULL ,
620ADD INDEX ( `Contract` ) ;');
621 $Manager->Execute('ALTER TABLE `EmployeeSalary` ADD FOREIGN KEY ( `Contract` ) REFERENCES `Contract` (
622`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;');
623 $Manager->Execute("INSERT INTO `Action` (
624`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,`Group` ,`Icon` ,`PermissionOperation` ,
625`Enable`) VALUES (NULL , '', 'Zaměstnanci', '1', '/is/?t=Employee&a=list', NULL , NULL , NULL , '1'
626);");
627 $ActionId = $Manager->Database->insert_id;
628 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
629 "VALUES (NULL , 'Zaměstnanci', '2', '".$ActionId."', '1');");
630 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
631 "VALUES (NULL , 'Roční výkazy', '2', NULL, '1');");
632 $MenuId = $Manager->Database->insert_id;
633 $Manager->Execute("INSERT INTO `Action` (
634`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,`Group` ,`Icon` ,`PermissionOperation` ,
635`Enable`) VALUES (NULL , '', 'Roční přehled', '1', '/is/?t=FinanceYearReport&a=list', NULL , NULL , NULL , '1'
636);");
637 $ActionId = $Manager->Database->insert_id;
638 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
639 "VALUES (NULL , 'Roční přehled', '".$MenuId."', '".$ActionId."', '1');");
640 $Manager->Execute("INSERT INTO `Action` (
641`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,`Group` ,`Icon` ,`PermissionOperation` ,
642`Enable`) VALUES (NULL , '', 'Výkaz subjektů', '1', '/is/?t=SubjectReport&a=list', NULL , NULL , NULL , '1'
643);");
644 $ActionId = $Manager->Database->insert_id;
645 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
646 "VALUES (NULL , 'Výkaz subjektů', '".$MenuId."', '".$ActionId."', '1');");
647}
648
649function UpdateTo656($Manager)
650{
651 $Manager->Execute('CREATE TABLE IF NOT EXISTS `Measure` (
652 `Id` int(11) NOT NULL AUTO_INCREMENT,
653 `Name` varchar(255) NOT NULL,
654 `Method` int(11) NOT NULL,
655 `Continuity` int(11) NOT NULL,
656 `Period` int(11) NOT NULL,
657 `Enabled` int(11) NOT NULL,
658 `Description` text NOT NULL,
659 `DataType` varchar(255) NOT NULL,
660 `DataTable` varchar(255) NOT NULL,
661 `Group` int(11) DEFAULT NULL,
662 `Cumulative` int(11) NOT NULL DEFAULT "0",
663 PRIMARY KEY (`Id`),
664 KEY `Group` (`Group`),
665 KEY `Method` (`Method`)
666) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
667
668 $Manager->Execute('CREATE TABLE IF NOT EXISTS `MeasureData` (
669 `Id` int(11) NOT NULL AUTO_INCREMENT,
670 `Time` datetime NOT NULL,
671 `Level` int(11) NOT NULL,
672 `Measure` int(11) NOT NULL,
673 `Continuity` int(11) NOT NULL,
674 `Min` int(11) NOT NULL,
675 `Avg` int(11) NOT NULL,
676 `Max` int(11) NOT NULL,
677 PRIMARY KEY (`Id`),
678 KEY `Time` (`Time`),
679 KEY `Measure` (`Measure`)
680) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
681
682 $Manager->Execute('CREATE TABLE IF NOT EXISTS `MeasureGroup` (
683 `Id` int(11) NOT NULL AUTO_INCREMENT,
684 `Name` varchar(255) NOT NULL,
685 `Parent` int(11) DEFAULT NULL,
686 PRIMARY KEY (`Id`),
687 KEY `Parent` (`Parent`)
688) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
689
690 $Manager->Execute('CREATE TABLE IF NOT EXISTS `MeasureMethod` (
691 `Id` int(11) NOT NULL AUTO_INCREMENT,
692 `Name` varchar(255) NOT NULL,
693 `Unit` varchar(255) NOT NULL,
694 PRIMARY KEY (`Id`)
695) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
696
697 $Manager->Execute('ALTER TABLE `Measure`
698 ADD CONSTRAINT `Measure_ibfk_1` FOREIGN KEY (`Group`) REFERENCES `MeasureGroup` (`Id`),
699 ADD CONSTRAINT `Measure_ibfk_2` FOREIGN KEY (`Method`) REFERENCES `MeasureMethod` (`Id`);');
700
701 $Manager->Execute('ALTER TABLE `MeasureData`
702 ADD CONSTRAINT `MeasureData_ibfk_1` FOREIGN KEY (`Measure`) REFERENCES `Measure` (`Id`);');
703
704 $Manager->Execute('ALTER TABLE `MeasureGroup`
705 ADD CONSTRAINT `MeasureGroup_ibfk_1` FOREIGN KEY (`Parent`) REFERENCES `MeasureGroup` (`Id`);');
706}
707
708function UpdateTo657($Manager)
709{
710 $Manager->Execute('CREATE TABLE IF NOT EXISTS `NetworkInterfaceUpDown` (
711 `Id` int(11) NOT NULL AUTO_INCREMENT,
712 `Time` datetime NOT NULL,
713 `Interface` int(11) NOT NULL,
714 `State` int(11) NOT NULL,
715 PRIMARY KEY (`Id`),
716 KEY `Interface` (`Interface`)
717) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci AUTO_INCREMENT=1 ;');
718 $Manager->Execute('ALTER TABLE `NetworkInterfaceUpDown`
719 ADD CONSTRAINT `NetworkInterfaceUpDown_ibfk_1` FOREIGN KEY (`Interface`) REFERENCES `NetworkInterface` (`Id`);');
720 $Manager->Execute("INSERT INTO `Action` (
721`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,`Group` ,`Icon` ,`PermissionOperation` ,
722`Enable`) VALUES (NULL , '', 'Změny stavu rozhraní', '1', '/is/?t=NetworkInterfaceUpDown&a=list', NULL , NULL , NULL , '1'
723);");
724 $ActionId = $Manager->Database->insert_id;
725 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
726 "VALUES (NULL , 'Změny stavu rozhraní', 4, '".$ActionId."', '1');");
727}
728
729function UpdateTo661($Manager)
730{
731 $Manager->Execute('CREATE TABLE IF NOT EXISTS `Contact` (
732 `Id` int(11) NOT NULL AUTO_INCREMENT,
733 `Category` int(11) NOT NULL,
734 `Value` varchar(255) NOT NULL,
735 `Subject` int(11) DEFAULT NULL,
736 `User` int(11) DEFAULT NULL,
737 PRIMARY KEY (`Id`),
738 KEY `Category` (`Category`),
739 KEY `User` (`User`),
740 KEY `Subject` (`Subject`)
741 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
742
743 $Manager->Execute('CREATE TABLE IF NOT EXISTS `ContactCategory` (
744 `Id` int(11) NOT NULL AUTO_INCREMENT,
745 `Name` varchar(255) NOT NULL,
746 PRIMARY KEY (`Id`)
747 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
748
749 $Manager->Execute('ALTER TABLE `Contact`
750 ADD CONSTRAINT `Contact_ibfk_3` FOREIGN KEY (`User`) REFERENCES `User` (`Id`),
751 ADD CONSTRAINT `Contact_ibfk_1` FOREIGN KEY (`Category`) REFERENCES `ContactCategory` (`Id`),
752 ADD CONSTRAINT `Contact_ibfk_2` FOREIGN KEY (`Subject`) REFERENCES `Subject` (`Id`);');
753
754 $Manager->Execute("INSERT INTO `Action` (
755`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,`Group` ,`Icon` ,`PermissionOperation` ,
756`Enable`) VALUES (NULL , '', 'Kontakty', '1', '/is/?t=Contact&a=list', NULL , NULL , NULL , '1'
757);");
758 $ActionId = $Manager->Database->insert_id;
759 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
760 "VALUES (NULL , 'Kontakty', '12', '".$ActionId."', '1');");
761 $ContactMenuId = $Manager->Database->insert_id;
762
763 $Manager->Execute("INSERT INTO `Action` (
764`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,`Group` ,`Icon` ,`PermissionOperation` ,
765`Enable`) VALUES (NULL , '', 'Druhy kontakty', '1', '/is/?t=ContactCategory&a=list', NULL , NULL , NULL , '1'
766);");
767 $ActionId = $Manager->Database->insert_id;
768 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
769 "VALUES (NULL , 'Druhy kontaktů', ".$ContactMenuId.", '".$ActionId."', '1');");
770
771 $Manager->Execute("INSERT INTO `ContactCategory` (`Id` ,`Name`)
772VALUES (NULL , 'Telefón'), (NULL , 'ICQ'), (NULL , 'Jabber'), (NULL , 'Email'), (NULL , 'Skype');");
773}
774
775function UpdateTo662($Manager)
776{
777 $Manager->Execute('INSERT INTO `Contact` (SELECT NULL AS `Id`, 2 AS `Category`, `ICQ` AS `Value`, NULL AS `Subject`, `Id` AS `User` FROM `User`
778WHERE (`ICQ` != "") AND (`ICQ` != 0))');
779 $Manager->Execute('ALTER TABLE `User` DROP `ICQ`;');
780 $Manager->Execute('INSERT INTO `ActionIcon` (`Id`, `Name`) VALUES (NULL, "Contact.png");');
781 $IconId = $Manager->Database->insert_id;
782 $Manager->Execute('UPDATE `Action` SET `Icon` = '.$IconId.' WHERE `Title`="Kontakty"');
783}
784
785function UpdateTo668($Manager)
786{
787 $Manager->Execute('CREATE TABLE IF NOT EXISTS `APIToken` (
788 `Id` int(11) NOT NULL AUTO_INCREMENT,
789 `User` int(11) NOT NULL,
790 `Token` varchar(255) NOT NULL,
791 PRIMARY KEY (`Id`),
792 KEY `User` (`User`)
793) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
794
795 $Manager->Execute('ALTER TABLE `APIToken`
796 ADD CONSTRAINT `APIToken_ibfk_1` FOREIGN KEY (`User`) REFERENCES `User` (`Id`);');
797 $Manager->Execute('INSERT INTO `Module` (`Id` ,`Name` ,`Title`)
798VALUES (NULL , "RSS", "RSS kanály");');
799}
800
801function UpdateTo671($Manager)
802{
803 $Manager->Execute('CREATE TABLE IF NOT EXISTS `NetworkSignal` (
804 `Id` int(11) NOT NULL AUTO_INCREMENT,
805 `Time` datetime NOT NULL,
806 `MAC` varchar(17) NOT NULL,
807 `Interface` int(11) DEFAULT NULL,
808 `Value` int(11) NOT NULL,
809 PRIMARY KEY (`Id`),
810 KEY `Interface` (`Interface`)
811) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
812
813 $Manager->Execute('ALTER TABLE `NetworkSignal`
814 ADD CONSTRAINT `NetworkSignal_ibfk_1` FOREIGN KEY (`Interface`) REFERENCES `NetworkInterface` (`Id`);');
815 $Manager->Execute('INSERT INTO `Action` (`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,
816`Group` ,`Icon` ,`PermissionOperation` ,`Enable`) VALUES (
817NULL , "", "Signál rozhraní", "1", "/is/?t=NetworkSignal&a=list", NULL , NULL , NULL , "1");');
818 $ActionId = $Manager->Database->insert_id;
819 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
820 "VALUES (NULL , 'Signál rozhraní', 4, '".$ActionId."', '1');");
821
822 $Manager->Execute('ALTER TABLE `NetworkDevice` ADD `API` INT NULL ,ADD INDEX ( `API` ) ;');
823 $Manager->Execute('CREATE TABLE IF NOT EXISTS `DeviceAPIType` (
824 `Id` int(11) NOT NULL AUTO_INCREMENT,
825 `Name` varchar(255) NOT NULL,
826 PRIMARY KEY (`Id`)
827) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;');
828
829 $Manager->Execute('INSERT INTO `DeviceAPIType` (`Id`, `Name`) VALUES
830(1, "RouterOS API"),
831(2, "AirOS");');
832 $Manager->Execute('ALTER TABLE `NetworkDevice` ADD FOREIGN KEY ( `API` ) REFERENCES `DeviceAPIType` (
833`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;');
834}
835
836function UpdateTo674($Manager)
837{
838 $Manager->Execute('ALTER TABLE `NetworkSignal` ADD `RateRx` INT NOT NULL ;');
839 $Manager->Execute('ALTER TABLE `NetworkSignal` ADD `RateTx` INT NOT NULL ;');
840}
841
842function UpdateTo676($Manager)
843{
844 $Manager->Execute('ALTER TABLE `NetworkSignal` ADD `Device` INT NULL ,
845ADD INDEX ( `Device` ) ;');
846 $Manager->Execute('ALTER TABLE `NetworkSignal` ADD FOREIGN KEY ( `Device` ) REFERENCES `NetworkDevice` (
847`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT ;');
848}
849
850function UpdateTo678($Manager)
851{
852 $Manager->Execute('ALTER TABLE `Contact` ADD `Description` VARCHAR( 255 ) NOT NULL ;');
853 $Manager->Execute('ALTER TABLE `Contact` ADD `Receive` BOOLEAN NOT NULL ;');
854
855 $Manager->Execute('CREATE TABLE IF NOT EXISTS `MenuItemFavorite` (
856 `Id` int(11) NOT NULL AUTO_INCREMENT,
857 `User` int(11) NOT NULL,
858 `MenuItem` int(11) NOT NULL,
859 PRIMARY KEY (`Id`),
860 KEY `User` (`User`),
861 KEY `MenuItem` (`MenuItem`)
862) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
863
864 $Manager->Execute('ALTER TABLE `MenuItemFavorite`
865 ADD CONSTRAINT `MenuItemFavorite_ibfk_2` FOREIGN KEY (`MenuItem`) REFERENCES `MenuItem` (`Id`),
866 ADD CONSTRAINT `MenuItemFavorite_ibfk_1` FOREIGN KEY (`User`) REFERENCES `User` (`Id`);');
867
868 $Manager->Execute('INSERT INTO `Action` (`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,
869`Group` ,`Icon` ,`PermissionOperation` ,`Enable`) VALUES (
870NULL , "", "Oblíbené položky nabídky", "1", "/is/?t=MenuItemFavorite&a=list", NULL , NULL , NULL , "1");');
871 $ActionId = $Manager->Database->insert_id;
872 $DbResult = $Manager->Execute('SELECT `Id` FROM `MenuItem` WHERE `Name`="Nabídky"');
873 if($DbResult->num_rows > 0)
874 {
875 $DbRow = $DbResult->fetch_assoc();
876 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
877 "VALUES (NULL , 'Oblíbené položky nabídky', ".$DbRow['Id'].", '".$ActionId."', '1');");
878 }
879}
880
881function UpdateTo679($Manager)
882{
883 $Manager->Execute('ALTER TABLE `NetworkDevice` ADD `Product` INT NOT NULL AFTER `Id` ,
884ADD INDEX ( `Product` ) ;');
885}
886
887function UpdateTo688($Manager)
888{
889 // Convert monthly plus payment for consumption to regular service
890 $DbResult = $Manager->Execute('SELECT `MonthlyPlus`, `Member` FROM `MemberPayment` WHERE `MonthlyPlus` > 0');
891 while($DbRow = $DbResult->fetch_assoc)
892 {
893 $Manager->Execute("INSERT INTO `Service` (`Id` ,`Name` ,`Category` ,`Price` ,`VAT`) ".
894 "VALUES (NULL , 'Spotřeba energie', 3, -".$DbRow['MonthlyPlus'].", 2);");
895 $ServiceId = $Manager->Database->insert_id;
896 $Manager->Execute("INSERT INTO `ServiceCustomerRel` (`Id` ,`Service` ,`Customer`) ".
897 "VALUES (NULL , ".$ServiceId.', '.$DbRow['Member'].");");
898 }
899}
900
901function UpdateTo692($Manager)
902{
903 // Convert user emails to contacts
904 $DbResult = $Manager->Execute('SELECT `Id` FROM `ContactCategory` WHERE `Name` = "Telefón"');
905 $DbRow = $DbResult->fetch_assoc();
906 $ContactCategoryId = $DbRow['Id'];
907
908 $Manager->Execute('INSERT INTO `Contact` (SELECT NULL AS `Id`, '.$ContactCategoryId.' AS `Category`, '.
909 '`PhoneNumber` AS `Value`, NULL AS `Subject`, `Id` AS `User`, "" AS `Descriptions`, 1 AS `Receive` FROM `User`
910WHERE (`PhoneNumber` != "") AND (`PhoneNumber` != 0))');
911 $Manager->Execute('ALTER TABLE `User` DROP `PhoneNumber`;');
912}
913
914function UpdateTo696($Manager)
915{
916 $Manager->Execute('ALTER TABLE `NetworkInterfaceUpDown` ADD `Duration` INT NOT NULL ;');
917 $Manager->Execute('UPDATE `NetworkInterfaceUpDown` AS `TM` SET `Duration` = IFNULL(TIMESTAMPDIFF(SECOND,
918 (SELECT `Time` FROM (SELECT * FROM `NetworkInterfaceUpDown`) AS `TA` WHERE (`TA`.`Time` < `TM`.`Time`)
919 AND (`TA`.`Interface`=`TM`.`Interface`) ORDER BY `TA`.`Time` DESC LIMIT 1), `TM`.`Time`), 0)');
920}
921
922function UpdateTo697($Manager)
923{
924 $Manager->Execute('ALTER TABLE `NetworkInterfaceUpDown` CHANGE `Duration` `Duration` INT( 11 ) NULL ;');
925 $Manager->Execute('UPDATE `NetworkInterfaceUpDown` SET `Duration` = NULL');
926 $Manager->Execute('UPDATE `NetworkInterfaceUpDown` AS `TM` SET `Duration` = TIMESTAMPDIFF(SECOND,
927 `TM`.`Time`, (SELECT `Time` FROM (SELECT * FROM `NetworkInterfaceUpDown`) AS `TA` WHERE (`TA`.`Time` > `TM`.`Time`)
928 AND (`TA`.`Interface`=`TM`.`Interface`) ORDER BY `TA`.`Time` ASC LIMIT 1))');
929}
930
931function UpdateTo707($Manager)
932{
933 $Manager->Execute('ALTER TABLE `NetworkDevice` CHANGE `Product` `Product` INT(11) NULL;');
934}
935
936function UpdateTo710($Manager)
937{
938 $Manager->Execute('RENAME TABLE `StockItem` TO `StockSerialNumber`;');
939 $Manager->Execute('UPDATE `Action` SET `URL`="/is/?t=StockSerialNumber&a=list",`Title`="Sériová čísla" WHERE `URL`="/is/?t=StockItem&a=list";');
940
941 // StockMove
942 $Manager->Execute('CREATE TABLE IF NOT EXISTS `StockMove` (
943 `Id` int(11) NOT NULL,
944 `Time` datetime NOT NULL,
945 `StockFrom` int(11) DEFAULT NULL,
946 `StockTo` int(11) DEFAULT NULL,
947 `BillCode` varchar(255) NOT NULL,
948 `DocumentLine` int(11) NOT NULL,
949 `File` int(11) DEFAULT NULL
950 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
951
952 $Manager->Execute('ALTER TABLE `StockMove`
953 ADD PRIMARY KEY (`Id`), ADD KEY `DocumentLine` (`DocumentLine`), ADD KEY `StockFrom` (`StockFrom`), ADD KEY `StockTo` (`StockTo`), ADD KEY `File` (`File`);');
954
955 $Manager->Execute('ALTER TABLE `StockMove`
956 MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;');
957
958 $Manager->Execute('ALTER TABLE `StockMove`
959 ADD CONSTRAINT `StockMove_ibfk_1` FOREIGN KEY (`StockFrom`) REFERENCES `Stock` (`Id`),
960 ADD CONSTRAINT `StockMove_ibfk_2` FOREIGN KEY (`StockTo`) REFERENCES `Stock` (`Id`),
961 ADD CONSTRAINT `StockMove_ibfk_3` FOREIGN KEY (`DocumentLine`) REFERENCES `DocumentLine` (`Id`);');
962
963 // StockMoveItem
964 $Manager->Execute('CREATE TABLE IF NOT EXISTS `StockMoveItem` (
965 `Id` int(11) NOT NULL,
966 `StockMove` int(11) NOT NULL,
967 `Product` int(11) NOT NULL,
968 `Amount` int(11) NOT NULL,
969 `Text` varchar(255) NOT NULL,
970 `UnitPrice` int(11) NOT NULL
971 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
972
973 $Manager->Execute('ALTER TABLE `StockMoveItem`
974 ADD PRIMARY KEY (`Id`), ADD KEY `Product` (`Product`), ADD KEY `StockMove` (`StockMove`);');
975
976 $Manager->Execute('ALTER TABLE `StockMoveItem`
977 MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;');
978
979 $Manager->Execute('ALTER TABLE `StockMoveItem`
980 ADD CONSTRAINT `StockMoveItem_ibfk_2` FOREIGN KEY (`Product`) REFERENCES `Product` (`Id`),
981 ADD CONSTRAINT `StockMoveItem_ibfk_1` FOREIGN KEY (`StockMove`) REFERENCES `StockMove` (`Id`);');
982
983 // IS menu item
984 $Manager->Execute('INSERT INTO `Action` (`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,
985`Group` ,`Icon` ,`PermissionOperation` ,`Enable`) VALUES (
986NULL , "", "Skladové pohyby", "1", "/is/?t=StockMove&a=list", NULL , NULL , NULL , "1");');
987 $ActionId = $Manager->Database->insert_id;
988 $DbResult = $Manager->Execute('SELECT `Id` FROM `MenuItem` WHERE `Name`="Sklad"');
989 if($DbResult->num_rows > 0)
990 {
991 $DbRow = $DbResult->fetch_assoc();
992 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
993 "VALUES (NULL , 'Skladové pohyby', ".$DbRow['Id'].", '".$ActionId."', '1');");
994 }
995}
996
997function UpdateTo715($Manager)
998{
999 $Manager->Execute('ALTER TABLE `StockSerialNumber` DROP FOREIGN KEY `StockSerialNumber_ibfk_6`;');
1000 $Manager->Execute('ALTER TABLE `StockSerialNumber` DROP `Segment`');
1001 $Manager->Execute('ALTER TABLE `Member` DROP `NetworkSegment`');
1002 $Manager->Execute('DROP TABLE `NetworkSegment`');
1003 $Manager->Execute('DELETE FROM `MenuItem` WHERE `Name`="Úseky sítě"');
1004 $Manager->Execute('DELETE FROM `Action` WHERE `Title`="Úseky sítě"');
1005}
1006
1007function UpdateTo718($Manager)
1008{
1009 $Manager->Execute('CREATE TABLE IF NOT EXISTS `Company` (
1010`Id` int(11) NOT NULL,
1011`Name` varchar(255) NOT NULL,
1012`Subject` int(11) NOT NULL
1013) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
1014 $Manager->Execute('ALTER TABLE `Company`
1015ADD PRIMARY KEY (`Id`), ADD KEY `Subject` (`Subject`);');
1016 $Manager->Execute('ALTER TABLE `Company`
1017MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;');
1018 $Manager->Execute('ALTER TABLE `Company`
1019ADD CONSTRAINT `Company_ibfk_1` FOREIGN KEY (`Subject`) REFERENCES `Subject` (`Id`);');
1020
1021 // IS menu item
1022 $Manager->Execute('INSERT INTO `Action` (`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,
1023`Group` ,`Icon` ,`PermissionOperation` ,`Enable`) VALUES (
1024NULL , "", "Firmy", "1", "/is/?t=Company&a=list", NULL , NULL , NULL , "1");');
1025 $ActionId = $Manager->Database->insert_id;
1026 $DbResult = $Manager->Execute('SELECT `Id` FROM `MenuItem` WHERE `Name`="Finance"');
1027 if($DbResult->num_rows > 0)
1028 {
1029 $DbRow = $DbResult->fetch_assoc();
1030 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
1031 "VALUES (NULL , 'Firmy', ".$DbRow['Id'].", '".$ActionId."', '1');");
1032 }
1033}
1034
1035function UpdateTo719($Manager)
1036{
1037 $Manager->Execute('ALTER TABLE `FinanceOperation` ADD `Direction` INT NOT NULL AFTER `Cash`;');
1038 $Manager->Execute('UPDATE `FinanceOperation` SET `Direction` = 1 WHERE `Value` >= 0 ;');
1039 $Manager->Execute('UPDATE `FinanceOperation` SET `Direction` = -1 WHERE `Value` < 0 ;');
1040 $Manager->Execute('UPDATE `FinanceOperation` SET `Value` = -`Value` WHERE `Value` < 0 ;');
1041 // Set missing FinanceOperation DocumentLine according BillCode
1042 $Manager->Execute('UPDATE `FinanceOperation` SET `DocumentLine` = 1 WHERE (`BillCode` LIKE "PP%") AND (`DocumentLine` IS NULL)');
1043 $Manager->Execute('UPDATE `FinanceOperation` SET `DocumentLine` = 2 WHERE (`BillCode` LIKE "VP%") AND (`DocumentLine` IS NULL)');
1044 $Manager->Execute('UPDATE `FinanceOperation` SET `DocumentLine` = 3 WHERE (`BillCode` LIKE "BV%") AND (`DocumentLine` IS NULL)');
1045 $Manager->Execute('UPDATE `FinanceOperation` SET `DocumentLine` = 4 WHERE (`BillCode` LIKE "PR%") AND (`DocumentLine` IS NULL)');
1046
1047 // IS menu item
1048 $DbResult = $Manager->Execute('SELECT `Id` FROM `MenuItem` WHERE `Name`="Příjmy a výdaje"');
1049 if($DbResult->num_rows > 0)
1050 {
1051 $DbRow = $DbResult->fetch_assoc();
1052 $Manager->Execute('INSERT INTO `Action` (`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,
1053 `Group` ,`Icon` ,`PermissionOperation` ,`Enable`) VALUES (
1054 NULL , "", "Příjem do pokladny", "1", "/is/?t=FinanceTreasuryIn&a=list", NULL , NULL , NULL , "1");');
1055 $ActionId = $Manager->Database->insert_id;
1056 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
1057 "VALUES (NULL , 'Příjem do pokladny', ".$DbRow['Id'].", '".$ActionId."', '1');");
1058 $Manager->Execute('INSERT INTO `Action` (`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,
1059 `Group` ,`Icon` ,`PermissionOperation` ,`Enable`) VALUES (
1060 NULL , "", "Výdej z pokladny", "1", "/is/?t=FinanceTreasuryOut&a=list", NULL , NULL , NULL , "1");');
1061 $ActionId = $Manager->Database->insert_id;
1062 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
1063 "VALUES (NULL , 'Výdej z pokladny', ".$DbRow['Id'].", '".$ActionId."', '1');");
1064 $Manager->Execute('INSERT INTO `Action` (`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,
1065 `Group` ,`Icon` ,`PermissionOperation` ,`Enable`) VALUES (
1066 NULL , "", "Příjem na účet", "1", "/is/?t=FinanceAccountIn&a=list", NULL , NULL , NULL , "1");');
1067 $ActionId = $Manager->Database->insert_id;
1068 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
1069 "VALUES (NULL , 'Příjem na účet', ".$DbRow['Id'].", '".$ActionId."', '1');");
1070 $Manager->Execute('INSERT INTO `Action` (`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,
1071 `Group` ,`Icon` ,`PermissionOperation` ,`Enable`) VALUES (
1072 NULL , "", "Výdej z účtu", "1", "/is/?t=FinanceAccountOut&a=list", NULL , NULL , NULL , "1");');
1073 $ActionId = $Manager->Database->insert_id;
1074 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
1075 "VALUES (NULL , 'Výdej z účtu', ".$DbRow['Id'].", '".$ActionId."', '1');");
1076 }
1077}
1078
1079function UpdateTo720($Manager)
1080{
1081 $Manager->Execute('ALTER TABLE `FinanceInvoice` ADD `Direction` INT NOT NULL AFTER `TimePayment`;');
1082 $Manager->Execute('UPDATE `FinanceInvoice` SET `Direction` = 1 WHERE `Value` >= 0 ;');
1083 $Manager->Execute('UPDATE `FinanceInvoice` SET `Direction` = -1 WHERE `Value` < 0 ;');
1084 $Manager->Execute('UPDATE `FinanceInvoice` SET `Value` = -`Value` WHERE `Value` < 0 ;');
1085 // Set missing FinanceInvoice DocumentLine according BillCode
1086 $Manager->Execute('UPDATE `FinanceInvoice` SET `DocumentLine` = 5 WHERE (`BillCode` LIKE "PF%") AND (`DocumentLine` IS NULL)');
1087 $Manager->Execute('UPDATE `FinanceInvoice` SET `DocumentLine` = 6 WHERE (`BillCode` LIKE "VF%") AND (`DocumentLine` IS NULL)');
1088
1089 // IS menu item
1090 $DbResult = $Manager->Execute('SELECT `Id` FROM `MenuItem` WHERE `Name`="Závazky a pohledávky"');
1091 if($DbResult->num_rows > 0)
1092 {
1093 $DbRow = $DbResult->fetch_assoc();
1094 $Manager->Execute('INSERT INTO `Action` (`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,
1095 `Group` ,`Icon` ,`PermissionOperation` ,`Enable`) VALUES (
1096 NULL , "", "Příjaté", "1", "/is/?t=FinanceInvoiceIn&a=list", NULL , NULL , NULL , "1");');
1097 $ActionId = $Manager->Database->insert_id;
1098 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
1099 "VALUES (NULL , 'Přijaté', ".$DbRow['Id'].", '".$ActionId."', '1');");
1100
1101 $Manager->Execute('INSERT INTO `Action` (`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,
1102 `Group` ,`Icon` ,`PermissionOperation` ,`Enable`) VALUES (
1103 NULL , "", "Vydané", "1", "/is/?t=FinanceInvoiceOut&a=list", NULL , NULL , NULL , "1");');
1104 $ActionId = $Manager->Database->insert_id;
1105 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
1106 "VALUES (NULL , 'Vydané', ".$DbRow['Id'].", '".$ActionId."', '1');");
1107 }
1108}
1109
1110function UpdateTo722($Manager)
1111{
1112 $Manager->Execute('ALTER TABLE `Service` DROP `CustomerCount`;');
1113}
1114
1115function UpdateTo725($Manager)
1116{
1117 // Text column of invoices is not used. Text from invoice items is taken instead.
1118 $DbResult = $Manager->Execute('ALTER TABLE `FinanceInvoice` DROP `Text`;');
1119
1120 $SearchText = 'Připojení k síti';
1121 $DbResult = $Manager->Execute('SELECT * FROM `FinanceInvoiceItem` WHERE `Description` LIKE "'.$SearchText.' za období%";');
1122 while($DbRow = $DbResult->fetch_assoc())
1123 {
1124 $Text = trim(substr($DbRow['Description'], strlen($SearchText.' za období') + 1));
1125 $Text = explode('-', $Text);
1126 $PeriodFrom = explode('.', trim($Text[0]));
1127 $PeriodFrom = $PeriodFrom[2].'-'.$PeriodFrom[1].'-'.$PeriodFrom[0];
1128 $PeriodTo = explode('.', trim($Text[1]));
1129 $PeriodTo = $PeriodTo[2].'-'.$PeriodTo[1].'-'.$PeriodTo[0];
1130 $Manager->Execute('UPDATE `FinanceInvoice` SET `PeriodFrom`="'.$PeriodFrom.'", `PeriodTo`="'.$PeriodTo.'" WHERE `Id`='.$DbRow['FinanceInvoice']);
1131 }
1132 $DbResult = $Manager->Execute('UPDATE `FinanceInvoiceItem` SET `Description` = "'.$SearchText.'" WHERE `Description` LIKE "'.$SearchText.' za období%";');
1133
1134 $SearchText = 'Připojení k Internetu';
1135 $DbResult = $Manager->Execute('SELECT * FROM `FinanceInvoiceItem` WHERE `Description` LIKE "'.$SearchText.' za období%";');
1136 while($DbRow = $DbResult->fetch_assoc())
1137 {
1138 $Text = trim(substr($DbRow['Description'], strlen($SearchText.' za období') + 1));
1139 $Text = explode('-', $Text);
1140 $PeriodFrom = explode('.', trim($Text[0]));
1141 $PeriodFrom = $PeriodFrom[2].'-'.$PeriodFrom[1].'-'.$PeriodFrom[0];
1142 $Text[1] = trim($Text[1]);
1143 if(strpos($Text[1], ' ') !== false) $Text[1] = substr($Text[1], 0, strpos($Text[1], ' '));
1144 $PeriodTo = explode('.', trim($Text[1]));
1145 $PeriodTo = $PeriodTo[2].'-'.$PeriodTo[1].'-'.$PeriodTo[0];
1146 $Manager->Execute('UPDATE `FinanceInvoice` SET `PeriodFrom`="'.$PeriodFrom.'", `PeriodTo`="'.$PeriodTo.'" WHERE `Id`='.$DbRow['FinanceInvoice']);
1147 }
1148 $DbResult = $Manager->Execute('UPDATE `FinanceInvoiceItem` SET `Description` = "'.$SearchText.'" WHERE `Description` LIKE "'.$SearchText.' za období%";');
1149}
1150
1151function UpdateTo726($Manager)
1152{
1153 $Manager->Execute('ALTER TABLE `ServiceCustomerRel` CHANGE `Action` `ChangeAction` ENUM("add","modify","remove") CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;');
1154 $Manager->Execute('ALTER TABLE `ServiceCustomerRel` ADD `ChangeTime` DATETIME NULL AFTER `ChangeAction`;');
1155 $Manager->Execute('ALTER TABLE `ServiceCustomerRel` DROP FOREIGN KEY `ServiceCustomerRel_ibfk_4`;');
1156 $Manager->Execute('ALTER TABLE `ServiceCustomerRel` CHANGE `ReplaceId` `ChangeReplaceId` INT(11) NULL DEFAULT NULL;');
1157 $Manager->Execute('ALTER TABLE `ServiceCustomerRel` ADD FOREIGN KEY (`ChangeReplaceId`) REFERENCES `ServiceCustomerRel`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
1158
1159 $Manager->Execute('ALTER TABLE `Service` CHANGE `Action` `ChangeAction` ENUM("add","modify","remove") CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;');
1160 $Manager->Execute('ALTER TABLE `Service` ADD `ChangeTime` DATETIME NULL AFTER `ChangeAction`;');
1161 $Manager->Execute('ALTER TABLE `Service` DROP FOREIGN KEY `Service_ibfk_2`;');
1162 $Manager->Execute('ALTER TABLE `Service` CHANGE `ReplaceId` `ChangeReplaceId` INT(11) NULL DEFAULT NULL;');
1163 $Manager->Execute('ALTER TABLE `Service` ADD FOREIGN KEY (`ChangeReplaceId`) REFERENCES `Service`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
1164
1165 $Manager->Execute('ALTER TABLE `FinanceCharge` CHANGE `Action` `ChangeAction` ENUM("add","modify","remove") CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL;');
1166 $Manager->Execute('ALTER TABLE `FinanceCharge` ADD `ChangeTime` DATETIME NULL AFTER `ChangeAction`;');
1167 $Manager->Execute('ALTER TABLE `FinanceCharge` DROP FOREIGN KEY `FinanceCharge_ibfk_1`;');
1168 $Manager->Execute('ALTER TABLE `FinanceCharge` CHANGE `ReplaceId` `ChangeReplaceId` INT(11) NULL DEFAULT NULL;');
1169 $Manager->Execute('ALTER TABLE `FinanceCharge` ADD FOREIGN KEY (`ChangeReplaceId`) REFERENCES `FinanceCharge`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
1170
1171 $Manager->Execute('ALTER TABLE `MemberPayment` DROP `NetworkDevice`;');
1172}
1173
1174function UpdateTo729($Manager)
1175{
1176 $Manager->Execute('ALTER TABLE `FinanceBankAccount` ADD `AutoImport` INT NOT NULL ;');
1177
1178 $Manager->Execute('CREATE TABLE IF NOT EXISTS `Scheduler` (
1179 `Id` int(11) NOT NULL,
1180 `Name` varchar(255) NOT NULL,
1181 `Enabled` int(11) NOT NULL,
1182 `Class` varchar(255) NOT NULL,
1183 `Log` mediumtext NOT NULL,
1184 `LastExecutedTime` datetime DEFAULT NULL,
1185 `ScheduledTime` datetime NOT NULL,
1186 `Period` int(11) NOT NULL
1187) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
1188
1189 $Manager->Execute('ALTER TABLE `Scheduler`
1190 ADD PRIMARY KEY (`Id`);');
1191
1192 $Manager->Execute('ALTER TABLE `Scheduler`
1193MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;');
1194
1195 // IS menu item
1196 $Manager->Execute('INSERT INTO `Action` (`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,
1197`Group` ,`Icon` ,`PermissionOperation` ,`Enable`) VALUES (
1198NULL , "", "Plánovač", "1", "/is/?t=Scheduler&a=list", NULL , NULL , NULL , "1");');
1199 $ActionId = $Manager->Database->insert_id;
1200 $DbResult = $Manager->Execute('SELECT `Id` FROM `MenuItem` WHERE `Name`="Systém"');
1201 if($DbResult->num_rows > 0)
1202 {
1203 $DbRow = $DbResult->fetch_assoc();
1204 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
1205 "VALUES (NULL , 'Plánovač', ".$DbRow['Id'].", '".$ActionId."', '1');");
1206 }
1207
1208 $Manager->Execute('INSERT INTO `Module` (`Id`, `Name`, `Title`) VALUES (NULL, "Plánovač", "Scheduler");');
1209}
1210
1211function UpdateTo730($Manager)
1212{
1213 $Manager->Execute('CREATE TABLE IF NOT EXISTS `SchedulerAction` (
1214 `Id` int(11) NOT NULL AUTO_INCREMENT,
1215 `Name` varchar(255) NOT NULL,
1216 `Class` varchar(255) NOT NULL,
1217 PRIMARY KEY (`Id`)
1218) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
1219 $Manager->Execute('ALTER TABLE `Scheduler` CHANGE `Class` `Action` INT(11) NOT NULL;');
1220 $Manager->Execute("ALTER TABLE `Scheduler` ADD INDEX ( `Action` ) ");
1221 $Manager->Execute('INSERT INTO `SchedulerAction` (`Id`, `Name`, `Class`) VALUES (NULL, "Import bankovních účtů", "ScheduleBankImport");');
1222 $Manager->Execute('UPDATE `Scheduler` SET `Action` = 1 WHERE `Scheduler`.`Id` = 1;');
1223 $Manager->Execute('ALTER TABLE `Scheduler` ADD CONSTRAINT `Schedule_ibfk_2` FOREIGN KEY (`Action`) REFERENCES `SchedulerAction` (`Id`);');
1224}
1225
1226function UpdateTo731($Manager)
1227{
1228 // NetworkDomain
1229 $Manager->Execute('CREATE TABLE IF NOT EXISTS `NetworkDomain` (
1230`Id` int(11) NOT NULL,
1231`Name` varchar(255) NOT NULL,
1232`Parent` int(11) DEFAULT NULL,
1233`Serial` int(11) NOT NULL,
1234`Refresh` int(11) NOT NULL,
1235`Expire` int(11) NOT NULL,
1236`Retry` int(11) NOT NULL,
1237`Minimum` int(11) NOT NULL,
1238`TTL` int(11) NOT NULL,
1239`Description` varchar(255) NOT NULL
1240) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
1241
1242 $Manager->Execute('ALTER TABLE `NetworkDomain` ADD PRIMARY KEY (`Id`);');
1243
1244 $Manager->Execute('ALTER TABLE `NetworkDomain`
1245 MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;');
1246
1247 $Manager->Execute('INSERT INTO `Action` (`Id` ,`Name` ,`Title` ,`Type` ,`URL` ,
1248`Group` ,`Icon` ,`PermissionOperation` ,`Enable`) VALUES (
1249NULL , "", "Síťová doména", "1", "/is/?t=NetworkDomain&a=list", NULL , NULL , NULL , "1");');
1250 $ActionId = $Manager->Database->insert_id;
1251 $DbResult = $Manager->Execute('SELECT `Id` FROM `MenuItem` WHERE `Name`="Síť"');
1252 if($DbResult->num_rows > 0)
1253 {
1254 $DbRow = $DbResult->fetch_assoc();
1255 $Manager->Execute("INSERT INTO `MenuItem` (`Id` ,`Name` ,`Parent` ,`Action` ,`Menu`) ".
1256 "VALUES (NULL , 'Síťová doména', ".$DbRow['Id'].", '".$ActionId."', '1');");
1257 }
1258
1259 // Model additions
1260 $Manager->Execute('ALTER TABLE `Model` ADD `Title` VARCHAR(255) NOT NULL , '.
1261 'ADD `Query` VARCHAR(255) NOT NULL , '.
1262 'ADD `DefaultSortColumn` VARCHAR(255) NOT NULL , '.
1263 'ADD `DefaultSortOrder` INT NOT NULL ;');
1264
1265 // ModelField
1266 $Manager->Execute('CREATE TABLE IF NOT EXISTS `ModelField` (
1267 `Id` int(11) NOT NULL,
1268 `Name` varchar(255) NOT NULL,
1269 `Model` int(11) NOT NULL,
1270 `Query` varchar(255) NOT NULL,
1271 `Type` varchar(255) NOT NULL,
1272 `Title` varchar(255) NOT NULL,
1273 `DefaultValue` varchar(255) NOT NULL,
1274 `IsNull` int(11) NOT NULL,
1275 `Suffix` varchar(255) NOT NULL
1276 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
1277
1278 $Manager->Execute('ALTER TABLE `ModelField`
1279 ADD PRIMARY KEY (`Id`), ADD KEY `Model` (`Model`);');
1280
1281 $Manager->Execute('ALTER TABLE `ModelField`
1282 MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;');
1283
1284 $Manager->Execute('ALTER TABLE `ModelField`
1285 ADD CONSTRAINT `ModelField_ibfk_1` FOREIGN KEY (`Model`) REFERENCES `Model` (`Id`);');
1286
1287 // Module additions
1288 $Manager->Execute('ALTER TABLE `Module` ADD `Version` VARCHAR(255) NOT NULL , '.
1289 'ADD `Creator` VARCHAR(255) NOT NULL , '.
1290 'ADD `License` VARCHAR(255) NOT NULL , '.
1291 'ADD `Installed` INT NOT NULL , '.
1292 'ADD `HomePage` VARCHAR(255) NOT NULL , '.
1293 'ADD `Description` TEXT NOT NULL ;');
1294
1295 // ModuleLink
1296 $Manager->Execute('CREATE TABLE IF NOT EXISTS `ModuleLink` (
1297`Id` int(11) NOT NULL,
1298 `Module` int(11) NOT NULL,
1299 `LinkedModule` int(11) NOT NULL,
1300 `Type` int(11) NOT NULL
1301) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
1302
1303 $Manager->Execute('ALTER TABLE `ModuleLink`
1304 ADD PRIMARY KEY (`Id`), ADD KEY `Module` (`Module`), ADD KEY `LinkedModule` (`LinkedModule`);');
1305
1306 $Manager->Execute('ALTER TABLE `ModuleLink`
1307 MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;');
1308}
1309
1310function UpdateTo735($Manager)
1311{
1312 $Manager->Execute('CREATE TABLE IF NOT EXISTS `NetworkFreeAccess` (
1313`Id` int(11) NOT NULL,
1314`IPAddress` varchar(255) NOT NULL,
1315`Time` datetime NOT NULL
1316) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
1317
1318 $Manager->Execute('ALTER TABLE `NetworkFreeAccess`
1319 ADD PRIMARY KEY (`Id`);');
1320
1321 $Manager->Execute('ALTER TABLE `NetworkFreeAccess`
1322 MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;');
1323
1324 $Manager->Execute('ALTER TABLE `NetworkFreeAccess` ADD `Configured` INT NOT NULL ;');
1325 $DbResult = $Manager->Execute('INSERT INTO `SchedulerAction` (`Id`, `Name`, `Class`) VALUES '.
1326 '(NULL, "Konfigurace internetu zdarma", "ScheduleConfigureFreeAccess");');
1327 $ActionId = $Manager->Database->insert_id;
1328 $Manager->Execute('INSERT INTO `Scheduler` (`Id`, `Name`, `Enabled`, `Action`,
1329 `Log`, `LastExecutedTime`, `ScheduledTime`, `Period`) VALUES (NULL, "Internet zdarma",
1330 1, '.$ActionId.', "", NULL, "", 5);');
1331 $Manager->Execute('ALTER TABLE `Scheduler` CHANGE `ScheduledTime` `ScheduledTime` DATETIME NULL;');
1332}
1333
1334function UpdateTo736($Manager)
1335{
1336 $Manager->Execute('CREATE TABLE IF NOT EXISTS `NetworkLinkType` (
1337`Id` int(11) NOT NULL,
1338`Name` varchar(255) NOT NULL
1339) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;');
1340
1341 $Manager->Execute('ALTER TABLE `NetworkLinkType` ADD PRIMARY KEY (`Id`);');
1342
1343 $Manager->Execute('ALTER TABLE `NetworkLinkType`
1344MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;');
1345}
1346
1347function UpdateTo739($Manager)
1348{
1349 $Manager->Execute('ALTER TABLE `NetworkDomain` ADD KEY (`Parent`);');
1350 $Manager->Execute('ALTER TABLE `NetworkDomain` ADD FOREIGN KEY (`Parent`) REFERENCES `NetworkDomain`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
1351
1352 $Manager->Execute('CREATE TABLE IF NOT EXISTS `NetworkDomainServer` (
1353 `Id` int(11) NOT NULL AUTO_INCREMENT,
1354 `Address` varchar(255) NOT NULL,
1355 `Domain` int(11) NOT NULL,
1356 `Sequence` int(11) NOT NULL,
1357 PRIMARY KEY (`Id`)
1358) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;');
1359
1360 $Manager->Execute('ALTER TABLE `NetworkDomainServer` ADD KEY `Domain` (`Domain`);');
1361
1362 $Manager->Execute('ALTER TABLE `NetworkDomainServer`
1363ADD CONSTRAINT `NetworkDomainServer_ibfk_1` FOREIGN KEY (`Domain`) REFERENCES `NetworkDomain` (`Id`);');
1364}
1365
1366function UpdateTo740($Manager)
1367{
1368 $Manager->Execute('CREATE TABLE IF NOT EXISTS `FinanceInvoiceGroup` (
1369 `Id` int(11) NOT NULL AUTO_INCREMENT,
1370 `Name` varchar(255) NOT NULL,
1371 `DocumentLine` int(11) NOT NULL,
1372 `ValueSign` int(11) NOT NULL,
1373 PRIMARY KEY (`Id`),
1374 KEY `DocumentLine` (`DocumentLine`)
1375) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;');
1376 $Manager->Execute('ALTER TABLE `FinanceInvoiceGroup`
1377ADD CONSTRAINT `FinanceInvoiceGroup_ibfk_1` FOREIGN KEY (`DocumentLine`) REFERENCES `DocumentLine` (`Id`);');
1378 $Manager->Execute('INSERT INTO `FinanceInvoiceGroup` (`Id`, `Name`, `DocumentLine`, `ValueSign`) VALUES
1379(1, "Přijaté faktury", 5, -1),
1380(2, "Vydané faktury", 6, 1);');
1381
1382 // Transform FinanceInvoice.DocumentLine reference to FinanceInvoiceGroup
1383 $Manager->Execute('ALTER TABLE `FinanceInvoice` ADD `Group` INT NULL AFTER `Id`;');
1384 $Manager->Execute('ALTER TABLE `FinanceInvoice` ADD INDEX(`Group`);');
1385 $Manager->Execute('ALTER TABLE `FinanceInvoice` ADD FOREIGN KEY (`Group`) REFERENCES `FinanceInvoiceGroup`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
1386 $Manager->Execute('UPDATE `FinanceInvoice` SET `Group`=1 WHERE `DocumentLine`=5');
1387 $Manager->Execute('UPDATE `FinanceInvoice` SET `Group`=2 WHERE `DocumentLine`=6');
1388 $Manager->Execute('ALTER TABLE `FinanceInvoice` DROP FOREIGN KEY `FinanceInvoice_ibfk_3`;');
1389 $Manager->Execute('ALTER TABLE `FinanceInvoice` DROP `DocumentLine`;');
1390
1391 $Manager->Execute('CREATE TABLE IF NOT EXISTS `FinanceOperationGroup` (
1392 `Id` int(11) NOT NULL AUTO_INCREMENT,
1393 `Name` varchar(255) NOT NULL,
1394 `DocumentLine` int(11) NOT NULL,
1395 `ValueSign` int(11) NOT NULL,
1396 PRIMARY KEY (`Id`),
1397 KEY `DocumentLine` (`DocumentLine`)
1398) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;');
1399 $Manager->Execute('ALTER TABLE `FinanceOperationGroup`
1400ADD CONSTRAINT `FinanceOperationGroup_ibfk_1` FOREIGN KEY (`DocumentLine`) REFERENCES `DocumentLine` (`Id`);');
1401 $Manager->Execute('INSERT INTO `FinanceOperationGroup` (`Id`, `Name`, `DocumentLine`, `ValueSign`) VALUES
1402(1, "Příjem do pokladny", 1, 1),
1403(2, "Výdej z pokladny", 2, -1),
1404(3, "Příjem na účet", 3, 1),
1405(4, "Výdej z účtu", 4, -1),
1406(5, "Nezařazené (ručně vyčistit)", 1, 1);');
1407
1408 // Transform FinanceOperation.DocumentLine reference to FinanceOperationGroup
1409 $Manager->Execute('ALTER TABLE `FinanceOperation` ADD `Group` INT NULL AFTER `Id`;');
1410 $Manager->Execute('ALTER TABLE `FinanceOperation` ADD INDEX (`Group`);');
1411 $Manager->Execute('ALTER TABLE `FinanceOperation` ADD FOREIGN KEY (`Group`) REFERENCES `FinanceOperationGroup`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
1412 $Manager->Execute('UPDATE `FinanceOperation` SET `Group`=1 WHERE `DocumentLine`=1');
1413 $Manager->Execute('UPDATE `FinanceOperation` SET `Group`=2 WHERE `DocumentLine`=2');
1414 $Manager->Execute('UPDATE `FinanceOperation` SET `Group`=3 WHERE `DocumentLine`=3');
1415 $Manager->Execute('UPDATE `FinanceOperation` SET `Group`=4 WHERE `DocumentLine`=4');
1416 $Manager->Execute('UPDATE `FinanceOperation` SET `Group`=5 WHERE `DocumentLine`=10');
1417 $Manager->Execute('UPDATE `FinanceOperation` SET `Group`=5 WHERE `DocumentLine`=5');
1418 $Manager->Execute('ALTER TABLE `FinanceOperation` DROP FOREIGN KEY `FinanceOperation_ibfk_6`;');
1419 $Manager->Execute('ALTER TABLE `FinanceOperation` DROP `DocumentLine`;');
1420
1421 $Manager->Execute('ALTER TABLE `FinanceInvoice` CHANGE `Direction` `ValueSign` INT(11) NOT NULL;');
1422 $Manager->Execute('ALTER TABLE `FinanceOperation` CHANGE `Direction` `ValueSign` INT(11) NOT NULL;');
1423}
1424
1425function UpdateTo741($Manager)
1426{
1427 // Add Direction column
1428 $Manager->Execute('ALTER TABLE `FinanceOperationGroup` ADD `Direction` INT NOT NULL ;');
1429 $Manager->Execute('UPDATE `FinanceOperationGroup` SET `Direction` = 1 WHERE ValueSign=-1');
1430 $Manager->Execute('ALTER TABLE `FinanceOperation` ADD `Direction` INT NOT NULL AFTER `Cash`;');
1431 $Manager->Execute('UPDATE `FinanceOperation` SET `Direction` = 1 WHERE ValueSign=-1');
1432 $Manager->Execute('ALTER TABLE `FinanceInvoiceGroup` ADD `Direction` INT NOT NULL ;');
1433 $Manager->Execute('UPDATE `FinanceInvoiceGroup` SET `Direction` = 1 WHERE ValueSign=1');
1434 $Manager->Execute('ALTER TABLE `FinanceInvoice` ADD `Direction` INT NOT NULL AFTER `TimePayment`;');
1435 $Manager->Execute('UPDATE `FinanceInvoice` SET `Direction` = 1 WHERE ValueSign=1');
1436}
1437
1438function UpdateTo742($Manager)
1439{
1440 $Manager->Execute('CREATE TABLE IF NOT EXISTS `DocumentLineCode` (
1441 `Id` int(11) NOT NULL AUTO_INCREMENT,
1442 `DocumentLine` int(11) NULL,
1443 `Name` varchar(255) NOT NULL,
1444 PRIMARY KEY (`Id`),
1445 UNIQUE KEY `Name` (`Name`),
1446 KEY `DocumentLine` (`DocumentLine`)
1447) ENGINE=InnoDB DEFAULT CHARSET=utf8;');
1448 $Manager->Execute('ALTER TABLE `DocumentLineCode`
1449ADD CONSTRAINT `DocumentLineCode_ibfk_1` FOREIGN KEY (`DocumentLine`) REFERENCES `DocumentLine` (`Id`);');
1450
1451 // Transform finance operations
1452 $Manager->Execute('ALTER TABLE `FinanceOperation` CHANGE `BillCode` `BillCodeText` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL');
1453 $Manager->Execute('ALTER TABLE `FinanceOperation` ADD `BillCode` INT NULL AFTER `BillCodeText`, ADD INDEX (`BillCode`)');
1454 $Manager->Execute('ALTER TABLE `FinanceOperation` ADD FOREIGN KEY (`BillCode`) REFERENCES `DocumentLineCode`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
1455 $DbResult = $Manager->Execute('SELECT `FinanceOperation`.`Id`, `FinanceOperation`.`BillCodeText`, `FinanceOperationGroup`.`DocumentLine` '.
1456 'FROM `FinanceOperation` '.
1457 'LEFT JOIN `FinanceOperationGroup` ON `FinanceOperationGroup`.`Id` = `FinanceOperation`.`Group` '.
1458 'WHERE `FinanceOperation`.`BillCodeText`!=""');
1459 while($DbRow = $DbResult->fetch_assoc())
1460 {
1461 if($DbRow['DocumentLine'] == '') $DbRow['DocumentLine'] = 'NULL';
1462 $Manager->Execute('INSERT INTO `DocumentLineCode` (`Id` ,`DocumentLine` ,`Name`) '.
1463 'VALUES (NULL , '.$DbRow['DocumentLine'].', "'.$DbRow['BillCodeText'].'");');
1464 $CodeId = $Manager->Database->insert_id;
1465 $Manager->Execute('UPDATE `FinanceOperation` SET `BillCode`='.$CodeId.' WHERE `Id`='.$DbRow['Id']);
1466 }
1467 $Manager->Execute('ALTER TABLE `FinanceOperation` DROP `BillCodeText`;');
1468
1469 // Transform invoices
1470 $Manager->Execute('ALTER TABLE `FinanceInvoice` CHANGE `BillCode` `BillCodeText` VARCHAR(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL');
1471 $Manager->Execute('ALTER TABLE `FinanceInvoice` ADD `BillCode` INT NULL AFTER `BillCodeText`, ADD INDEX (`BillCode`)');
1472 $Manager->Execute('ALTER TABLE `FinanceInvoice` ADD FOREIGN KEY (`BillCode`) REFERENCES `DocumentLineCode`(`Id`) ON DELETE RESTRICT ON UPDATE RESTRICT;');
1473 $DbResult = $Manager->Execute('SELECT `FinanceInvoice`.`Id`, `FinanceInvoice`.`BillCodeText`, `FinanceInvoiceGroup`.`DocumentLine` '.
1474 'FROM `FinanceInvoice` '.
1475 'LEFT JOIN `FinanceInvoiceGroup` ON `FinanceInvoiceGroup`.`Id` = `FinanceInvoice`.`Group` '.
1476 'WHERE `FinanceInvoice`.`BillCodeText`!=""');
1477 while($DbRow = $DbResult->fetch_assoc())
1478 {
1479 if($DbRow['DocumentLine'] == '') $DbRow['DocumentLine'] = 'NULL';
1480 $Manager->Execute('INSERT INTO `DocumentLineCode` (`Id` ,`DocumentLine` ,`Name`) '.
1481 'VALUES (NULL , '.$DbRow['DocumentLine'].', "'.$DbRow['BillCodeText'].'");');
1482 $CodeId = $Manager->Database->insert_id;
1483 $Manager->Execute('UPDATE `FinanceInvoice` SET `BillCode`='.$CodeId.' WHERE `Id`='.$DbRow['Id']);
1484 }
1485 $Manager->Execute('ALTER TABLE `FinanceInvoice` DROP `BillCodeText`;');
1486
1487 //$Manager->Execute('RENAME TABLE `FinanceOperationGroup` TO `FinanceOperationTemplate`');
1488 //$Manager->Execute('RENAME TABLE `FinanceInvoiceGroup` TO `FinanceInvoiceTemplate`');
1489}
1490
1491
1492class Updates
1493{
1494 function Get()
1495 {
1496 return(array(
1497 491 => array('Revision' => 493, 'Function' => 'UpdateTo493'),
1498 493 => array('Revision' => 494, 'Function' => 'UpdateTo494'),
1499 494 => array('Revision' => 495, 'Function' => 'UpdateTo495'),
1500 495 => array('Revision' => 497, 'Function' => 'UpdateTo497'),
1501 497 => array('Revision' => 498, 'Function' => 'UpdateTo498'),
1502 498 => array('Revision' => 499, 'Function' => 'UpdateTo499'),
1503 499 => array('Revision' => 500, 'Function' => 'UpdateTo500'),
1504 500 => array('Revision' => 502, 'Function' => 'UpdateTo502'),
1505 502 => array('Revision' => 505, 'Function' => 'UpdateTo505'),
1506 505 => array('Revision' => 507, 'Function' => 'UpdateTo507'),
1507 507 => array('Revision' => 515, 'Function' => 'UpdateTo515'),
1508 515 => array('Revision' => 517, 'Function' => 'UpdateTo517'),
1509 517 => array('Revision' => 526, 'Function' => 'UpdateTo526'),
1510 526 => array('Revision' => 527, 'Function' => 'UpdateTo527'),
1511 527 => array('Revision' => 535, 'Function' => 'UpdateTo535'),
1512 535 => array('Revision' => 549, 'Function' => 'UpdateTo549'),
1513 549 => array('Revision' => 550, 'Function' => 'UpdateTo550'),
1514 550 => array('Revision' => 551, 'Function' => 'UpdateTo551'),
1515 551 => array('Revision' => 565, 'Function' => 'UpdateTo565'),
1516 565 => array('Revision' => 571, 'Function' => 'UpdateTo571'),
1517 571 => array('Revision' => 574, 'Function' => 'UpdateTo574'),
1518 574 => array('Revision' => 584, 'Function' => 'UpdateTo584'),
1519 584 => array('Revision' => 591, 'Function' => 'UpdateTo591'),
1520 591 => array('Revision' => 597, 'Function' => 'UpdateTo597'),
1521 597 => array('Revision' => 601, 'Function' => 'UpdateTo601'),
1522 601 => array('Revision' => 615, 'Function' => 'UpdateTo615'),
1523 615 => array('Revision' => 619, 'Function' => 'UpdateTo619'),
1524 619 => array('Revision' => 620, 'Function' => 'UpdateTo620'),
1525 620 => array('Revision' => 627, 'Function' => 'UpdateTo627'),
1526 627 => array('Revision' => 632, 'Function' => 'UpdateTo632'),
1527 632 => array('Revision' => 633, 'Function' => 'UpdateTo633'),
1528 633 => array('Revision' => 645, 'Function' => 'UpdateTo645'),
1529 645 => array('Revision' => 646, 'Function' => 'UpdateTo646'),
1530 646 => array('Revision' => 647, 'Function' => 'UpdateTo647'),
1531 647 => array('Revision' => 657, 'Function' => 'UpdateTo657'),
1532 657 => array('Revision' => 661, 'Function' => 'UpdateTo661'),
1533 661 => array('Revision' => 662, 'Function' => 'UpdateTo662'),
1534 662 => array('Revision' => 668, 'Function' => 'UpdateTo668'),
1535 668 => array('Revision' => 671, 'Function' => 'UpdateTo671'),
1536 671 => array('Revision' => 674, 'Function' => 'UpdateTo674'),
1537 674 => array('Revision' => 676, 'Function' => 'UpdateTo676'),
1538 676 => array('Revision' => 678, 'Function' => 'UpdateTo678'),
1539 678 => array('Revision' => 679, 'Function' => 'UpdateTo679'),
1540 679 => array('Revision' => 688, 'Function' => 'UpdateTo688'),
1541 688 => array('Revision' => 692, 'Function' => 'UpdateTo692'),
1542 692 => array('Revision' => 696, 'Function' => 'UpdateTo696'),
1543 696 => array('Revision' => 697, 'Function' => 'UpdateTo697'),
1544 697 => array('Revision' => 707, 'Function' => 'UpdateTo707'),
1545 707 => array('Revision' => 710, 'Function' => 'UpdateTo710'),
1546 710 => array('Revision' => 715, 'Function' => 'UpdateTo715'),
1547 715 => array('Revision' => 718, 'Function' => 'UpdateTo718'),
1548 718 => array('Revision' => 719, 'Function' => 'UpdateTo719'),
1549 719 => array('Revision' => 720, 'Function' => 'UpdateTo720'),
1550 720 => array('Revision' => 722, 'Function' => 'UpdateTo722'),
1551 722 => array('Revision' => 725, 'Function' => 'UpdateTo725'),
1552 725 => array('Revision' => 726, 'Function' => 'UpdateTo726'),
1553 726 => array('Revision' => 729, 'Function' => 'UpdateTo729'),
1554 729 => array('Revision' => 730, 'Function' => 'UpdateTo730'),
1555 730 => array('Revision' => 731, 'Function' => 'UpdateTo731'),
1556 731 => array('Revision' => 735, 'Function' => 'UpdateTo735'),
1557 735 => array('Revision' => 736, 'Function' => 'UpdateTo736'),
1558 736 => array('Revision' => 739, 'Function' => 'UpdateTo739'),
1559 739 => array('Revision' => 740, 'Function' => 'UpdateTo740'),
1560 740 => array('Revision' => 741, 'Function' => 'UpdateTo741'),
1561 741 => array('Revision' => 742, 'Function' => 'UpdateTo742'),
1562 ));
1563 }
1564}
Note: See TracBrowser for help on using the repository browser.