-- -------------------------------------------------------- -- Host: 127.0.0.1 -- Server version: 10.4.32-MariaDB - mariadb.org binary distribution -- Server OS: Win64 -- HeidiSQL Version: 12.6.0.6765 -- -------------------------------------------------------- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET NAMES utf8 */; /*!50503 SET NAMES utf8mb4 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- Dumping structure for table veide_object.address (table) CREATE TABLE IF NOT EXISTS `address (table)` ( `id_address` int(10) unsigned zerofill DEFAULT NULL, `id` int(10) unsigned NOT NULL, `name` varchar(50) DEFAULT NULL, `Iname` varchar(15) DEFAULT NULL, `id_address_level` int(1) unsigned NOT NULL, `id_address_index` int(1) DEFAULT NULL, PRIMARY KEY (`id`), KEY `FK_address (table)_address_level` (`id_address_level`), CONSTRAINT `FK_address (table)_address_level` FOREIGN KEY (`id_address_level`) REFERENCES `address_level` (`address_level_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Dumping data for table veide_object.address (table): ~59 rows (approximately) INSERT INTO `address (table)` (`id_address`, `id`, `name`, `Iname`, `id_address_level`, `id_address_index`) VALUES (0100000000, 100000000, 'Latvija', 'Latvija', 1, 0), (0100000000, 100003028, 'Jelgava', 'Jelgava', 3, 0), (0100003028, 100319088, '3. līnija, Jelgava', '3. līnija', 7, 0), (0100003028, 100319096, '4. līnija, Jelgava', '4. līnija', 7, 0), (0100003028, 100319102, '5. līnija, Jelgava', '5. līnija', 7, 0), (0100003028, 100319176, 'Akadēmijas iela, Jelgava', 'Akadēmijas', 7, 0), (0100003028, 100319264, 'Aspazijas iela, Jelgava', 'Aspazijas', 7, 0), (0100003028, 100319272, 'Asteru iela, Jelgava', 'Asteru', 7, 0), (0100003028, 100319280, 'Atmodas iela, Jelgava', 'Atmodas', 7, 0), (0100003028, 100319473, 'Blaumaņa iela, Jelgava', 'Blaumaņa', 7, 0), (0100003028, 100319498, 'Brīvības bulvāris, Jelgava', 'Brīvības', 7, 0), (0100003028, 100319617, 'Dambja iela, Jelgava', 'Dambja', 7, 0), (0100003028, 100319658, 'Dobeles iela, Jelgava', 'Dobeles', 7, 0), (0100003028, 100319666, 'Dobeles šoseja, Jelgava', 'Dobeles', 7, 0), (0100003028, 100319682, 'Driksas iela, Jelgava', 'Driksas', 7, 0), (0100003028, 100319738, 'Dzelzceļnieku iela, Jelgava', 'Dzelzceļnieku', 7, 0), (0100003028, 100319842, 'Filozofu iela, Jelgava', 'Filozofu', 7, 0), (0100003028, 100319891, 'Garozas iela, Jelgava', 'Garozas', 7, 0), (0100003028, 100320041, 'Institūta iela, Jelgava', 'Institūta', 7, 0), (0100003028, 100320066, 'Izstādes iela, Jelgava', 'Izstādes', 7, 0), (0100003028, 100320074, 'Jāņa iela, Jelgava', 'Jāņa', 7, 0), (0100003028, 100320090, 'Jāņa Čakstes bulvāris, Jelgava', 'Čakstes', 7, 0), (0100003028, 100320162, 'Kalnciema ceļš, Jelgava', 'Kalnciema', 7, 0), (0100003028, 100320234, 'Katoļu iela, Jelgava', 'Katoļu', 7, 0), (0100003028, 100320314, 'Krišjāņa Barona iela, Jelgava', 'Barona', 7, 0), (0100003028, 100320363, 'Lāčplēša iela, Jelgava', 'Lāčplēša', 7, 0), (0100003028, 100320507, 'Lielā iela, Jelgava', 'Lielā', 7, 0), (0100003028, 100320548, 'Lietuvas šoseja, Jelgava', 'Lietuvas', 7, 0), (0100003028, 100320597, 'Loka maģistrāle, Jelgava', 'Loka', 7, 0), (0100003028, 100320628, 'Malkas ceļš, Jelgava', 'Malkas', 7, 0), (0100003028, 100320636, 'Māras iela, Jelgava', 'Māras', 7, 0), (0100003028, 100320652, 'Mātera iela, Jelgava', 'Mātera', 7, 0), (0100003028, 100320708, 'Meiju ceļš, Jelgava', 'Meiju', 7, 0), (0100003028, 100320757, 'Miera iela, Jelgava', 'Miera', 7, 0), (0100003028, 100320886, 'Palīdzības iela, Jelgava', 'Palīdzības', 7, 0), (0100003028, 100320941, 'Pasta iela, Jelgava', 'Pasta', 7, 0), (0100003028, 100320982, 'Pērnavas iela, Jelgava', 'Pērnavas', 7, 0), (0100003028, 100320990, 'Pētera iela, Jelgava', 'Pētera', 7, 0), (0100003028, 100321051, 'Platones iela, Jelgava', 'Platones', 7, 0), (0100003028, 100321131, 'Pulkveža Oskara Kalpaka iela, Jelgava', 'Kalpaka', 7, 0), (0100003028, 100321148, 'Pumpura iela, Jelgava', 'Pumpura', 7, 0), (0100003028, 100321197, 'Raiņa iela, Jelgava', 'Raiņa', 7, 0), (0100003028, 100321228, 'IKI', 'IKI', 7, 0), (0100003028, 100321332, 'Rūpniecības iela, Jelgava', 'Rūpniecības', 7, 0), (0100003028, 100321398, 'Sarmas iela, Jelgava', 'Sarmas', 7, 0), (0100003028, 100321404, 'Satiksmes iela, Jelgava', 'Satiksmes', 7, 0), (0100003028, 100321453, 'Savienības iela, Jelgava', 'Savienības', 7, 0), (0100003028, 100321605, 'Sporta iela, Jelgava', 'Sporta', 7, 0), (0100003028, 100321638, 'Stacijas iela, Jelgava', 'Stacijas', 7, 0), (0100003028, 100321718, 'Strazdu iela, Jelgava', 'Strazdu', 7, 0), (0100003028, 100321726, 'Sudrabu Edžus iela, Jelgava', 'Edžus', 7, 0), (0100003028, 100321742, 'Svētes iela, Jelgava', 'Svētes', 7, 0), (0100003028, 100321750, 'Tērvetes iela, Jelgava', 'Tērvetes', 7, 0), (0100003028, 100321871, 'Uzvaras iela, Jelgava', 'Uzvaras', 7, 0), (0100003028, 100322166, 'Zemgales prospekts, Jelgava', 'Zemgales', 7, 0), (0100003028, 100322221, 'Zirgu iela, Jelgava', 'Zirgu', 7, 0), (0100003028, 100322246, 'Zvejnieku iela, Jelgava', 'Zvejnieku', 7, 0), (0100003028, 100412330, 'Briežu iela, Jelgava', 'Brieža', 7, 0), (0100003028, 100418212, 'Keramiķu iela, Jelgava', 'Keramikas', 7, 0); -- Dumping structure for table veide_object.address_level CREATE TABLE IF NOT EXISTS `address_level` ( `address_level_id` int(1) unsigned NOT NULL, `name` varchar(20) DEFAULT NULL, `fname` varchar(20) DEFAULT NULL, `sname` varchar(10) DEFAULT NULL, PRIMARY KEY (`address_level_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Dumping data for table veide_object.address_level: ~9 rows (approximately) INSERT INTO `address_level` (`address_level_id`, `name`, `fname`, `sname`) VALUES (1, 'Valsts', 'Valsts', NULL), (2, 'Novads', 'Novads', 'nov.'), (3, 'Valsts pilēta', 'Valsts pilsēta', NULL), (4, 'Pilsēta', 'Pilsēta', NULL), (5, 'Pagasts', 'Pagasts', 'pag.'), (6, 'Ciems', 'Ciems/mazciems', NULL), (7, 'Iela', 'Iela', NULL), (8, 'Ēka', 'Ēkas nr./Nosaukums, ', NULL), (9, 'Telpa', 'Telpu grupa (dzīvokl', NULL); -- Dumping structure for table veide_object.metter_acounting CREATE TABLE IF NOT EXISTS `metter_acounting` ( `id` int(11) NOT NULL AUTO_INCREMENT, `id_object` int(11) DEFAULT NULL, `number` varchar(50) DEFAULT NULL, `date_from` date DEFAULT NULL, `date_to` date DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=142 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Dumping data for table veide_object.metter_acounting: ~136 rows (approximately) INSERT INTO `metter_acounting` (`id`, `id_object`, `number`, `date_from`, `date_to`) VALUES (1, 1, '85682335', '2019-04-30', '2019-05-31'), (2, 1, '5301873827', '2019-05-31', '2021-08-31'), (3, 2, '568796', '2012-07-30', '2019-05-31'), (4, 2, '4107948968', '2019-05-31', '2021-08-31'), (5, 3, '568693', '2010-04-27', '2019-05-31'), (6, 3, '524351225', '2019-05-31', '2021-08-31'), (7, 5, '4109420729', '2017-02-28', '2021-08-31'), (8, 5, '50156373', '2008-02-01', '2017-02-28'), (9, 6, '1611742', '2014-11-30', '2021-08-31'), (10, 6, '56993', '2012-07-30', '2014-11-30'), (11, 7, '50135314', '2010-04-27', '2019-05-31'), (12, 7, '6102615629', '2019-05-31', '2021-08-31'), (13, 8, '29446699', '2005-12-01', '2019-05-31'), (14, 8, '10498', '2005-11-01', '2005-12-01'), (15, 9, '52685418', '2012-07-30', '2014-11-30'), (16, 9, '32558090', '2014-11-30', '2017-02-28'), (17, 9, '4109420527', '2017-02-28', '2021-08-31'), (18, 9, '29446616', '2005-12-01', '2008-03-01'), (19, 9, '96397', '2005-11-01', '2005-12-01'), (20, 10, '50028987', '2008-03-01', '2021-08-31'), (21, 11, '264', '2014-10-30', '2017-02-28'), (22, 11, '91002891', '2017-02-28', '2021-08-31'), (23, 12, '32687084', '2012-07-30', '2014-11-30'), (24, 12, '11669109', '2014-11-30', '2021-08-31'), (25, 12, '32687084', '2005-11-01', '2010-05-27'), (26, 13, '32199561', '2012-08-30', '2019-05-31'), (27, 13, '32469776', '2010-04-27', '2012-08-30'), (28, 13, '6102615900', '2019-05-31', '2021-08-31'), (29, 13, '32469776', '2005-11-01', '2008-03-01'), (30, 14, '50089149', '2012-07-30', '2017-02-28'), (31, 14, '4109420280', '2017-02-28', '2021-08-31'), (32, 14, '29376219', '2005-12-01', '2010-05-27'), (33, 14, '4224014', '2005-11-01', '2005-12-01'), (34, 18, '32414204', '2012-07-30', '2014-11-30'), (35, 18, '11688670', '2014-11-30', '2021-08-31'), (36, 16, '53119305', '2014-10-30', '2014-11-30'), (37, 16, '32245598', '2014-11-30', '2017-02-28'), (38, 16, '4109420145', '2017-02-28', '2021-08-31'), (39, 17, '50088638', '2012-07-30', '2019-05-31'), (40, 17, '5116905827', '2019-05-31', '2021-08-31'), (41, 17, '50088638', '2005-11-01', '2010-05-27'), (42, 18, '32414204', '2010-04-27', '2014-11-30'), (43, 18, '11688670', '2014-11-30', '2021-08-31'), (44, 19, '32778874', '2012-07-30', '2019-05-31'), (45, 19, '5110224320', '2019-05-31', '2021-08-31'), (46, 19, '32778874', '2005-11-01', '2010-05-27'), (47, 20, '50050895', '2012-07-30', '2017-02-28'), (48, 20, '4109420471', '2017-02-28', '2021-08-31'), (49, 20, '29376210', '2012-07-30', '2017-02-28'), (50, 20, '50050895', '2005-11-01', '2010-05-27'), (51, 20, '29376210', '2005-12-01', '2010-05-27'), (52, 21, '4109420325', '2017-02-28', '2021-08-31'), (53, 21, '110081', '2005-11-01', '2005-12-01'), (54, 22, '32647524', '2010-04-27', '2012-08-30'), (55, 22, '53117522', '2012-08-30', '2014-11-30'), (56, 22, '32295119', '2014-11-30', '2019-05-31'), (57, 22, '5117901127', '2019-05-31', '2021-08-31'), (58, 22, '32647524', '2005-11-01', '2008-03-01'), (59, 23, '53117426', '2008-03-01', '2021-08-31'), (60, 24, '5110914629', '2019-05-31', '2021-08-31'), (61, 25, '100145', '2017-01-31', '2019-05-31'), (62, 25, '5110833045', '2019-05-31', '2021-08-31'), (63, 27, '50028646', '2005-11-01', '2021-08-31'), (64, 28, '3639101', '2005-11-01', '2005-12-01'), (65, 28, '29376264', '2005-12-01', '2021-08-31'), (66, 29, '82776', '2017-01-31', '2017-02-28'), (67, 29, '4114492386', '2017-02-28', '2021-08-31'), (68, 30, '91005292', '2014-11-30', '2021-08-31'), (69, 30, '50086615', '2014-11-30', '2021-08-31'), (70, 31, '251280', '2005-11-01', '2021-08-31'), (71, 32, '557538', '2010-04-27', '2017-02-28'), (72, 32, '4114492229', '2017-02-28', '2021-08-31'), (73, 33, '50028590', '2005-11-01', '2021-08-31'), (78, 35, '29376213', '2005-12-01', '2021-08-31'), (79, 35, '97125', '2005-11-01', '2005-12-01'), (80, 36, '50158093', '2012-07-30', '2014-11-30'), (81, 36, '11669252', '2014-11-30', '2021-08-31'), (82, 36, '50158093', '2008-02-01', '2010-05-27'), (83, 36, '50158093', '2008-02-01', '2010-05-27'), (84, 37, '32996418', '2005-11-01', '2012-08-30'), (85, 37, '32996418', '2014-10-30', '2014-11-30'), (86, 38, '580689', '2010-04-27', '2019-05-31'), (87, 38, '4117013300', '2019-05-31', '2021-08-31'), (88, 39, '29292035', '2005-11-01', '2008-03-01'), (89, 39, '32735197', '2008-03-01', '2014-11-30'), (90, 39, '11667895', '2014-11-30', '2021-08-31'), (91, 40, '11667994', '2017-01-31', '2021-08-31'), (92, 40, '11667994', '2014-11-30', '2021-08-31'), (93, 41, '50161280', '2008-02-01', '2010-05-27'), (94, 41, '50161280', '2012-07-30', '2021-08-31'), (95, 42, '32276933', '2012-08-30', '2021-08-31'), (96, 42, '29376263', '2005-12-01', '2012-08-30'), (97, 42, '291044', '2005-11-01', '2005-12-01'), (98, 43, '29446613', '2005-12-01', '2014-11-30'), (99, 43, '7950242', '2005-11-01', '2005-12-01'), (100, 43, '11671877', '2014-11-30', '2021-08-31'), (101, 44, '32202631', '2012-08-30', '2021-08-31'), (102, 44, '29375625', '2005-12-01', '2012-08-30'), (103, 44, '1,84611E+11', '2005-11-01', '2005-12-01'), (104, 45, '341236', '2012-07-30', '2019-05-31'), (105, 45, '29292023', '2005-11-01', '2010-05-27'), (106, 45, '5116907043', '2019-05-31', '2021-08-31'), (107, 47, '83543477', '2012-07-30', '2014-11-30'), (108, 47, '11400194', '2014-11-30', '2021-08-31'), (109, 48, '34237', '2010-05-27', '2019-05-31'), (110, 48, '5116907100', '2019-05-31', '2021-08-31'), (111, 48, '29292026', '2005-12-01', '2010-05-27'), (112, 49, '184611011', '2005-11-01', '2005-12-01'), (113, 49, '34231', '2010-05-27', '2014-11-30'), (114, 49, '29292037', '2008-02-01', '2010-05-27'), (115, 49, '11675292', '2014-11-30', '2021-08-31'), (116, 50, '32735797', '2010-05-27', '2017-02-28'), (117, 50, '4114492173', '2017-02-28', '2021-08-31'), (118, 50, '556043', '2005-11-01', '2005-12-01'), (119, 50, '32735797', '2005-12-01', '2010-05-27'), (120, 50, '556043', '2005-11-01', '2005-12-01'), (121, 50, '32735797', '2008-02-01', '2010-05-27'), (122, 51, '50152351', '2008-02-01', '2010-05-27'), (123, 51, '50152351', '2012-07-30', '2017-02-28'), (124, 51, '41144920227', '2017-02-28', '2019-05-31'), (125, 51, '4114492027', '2019-05-31', '2021-08-31'), (126, 52, '29376231', '2005-12-01', '2017-02-28'), (127, 52, '4109420886', '2017-02-28', '2021-08-31'), (128, 52, '3493235', '2005-11-01', '2005-12-01'), (129, 53, '6100663686', '2019-05-31', '2021-08-31'), (130, 53, '50121054', '2008-02-01', '2019-05-31'), (131, 54, '6103887463', '2019-05-31', '2021-08-31'), (132, 55, '32778527', '2008-02-01', '2014-11-30'), (133, 55, '32778527', '2010-05-27', '2019-05-31'), (134, 55, '32778527', '2005-11-01', '2010-05-27'), (135, 54, '32149396', '2017-01-31', '2019-05-31'), (136, 23, '53117426', '2014-10-30', '2014-11-30'), (137, 23, '32248826', '2014-11-30', '2021-08-31'), (138, 34, '52685472', '2010-05-27', '2014-11-30'), (140, 34, '33165911', '2014-11-30', '2021-08-31'), (141, 34, '32719941', '2005-11-01', '2021-08-31'); -- Dumping structure for view veide_object.metter_consum -- Creating temporary table to overcome VIEW dependency errors CREATE TABLE `metter_consum` ( `id` INT(10) UNSIGNED NULL, `name` VARCHAR(69) NULL COLLATE 'utf8mb4_unicode_ci', `number` LONGTEXT NULL COLLATE 'utf8mb4_unicode_ci' ) ENGINE=MyISAM; -- Dumping structure for table veide_object.metter_metter CREATE TABLE IF NOT EXISTS `metter_metter` ( `id` int(11) NOT NULL AUTO_INCREMENT, `ch` varchar(2) DEFAULT NULL, `number` varchar(50) DEFAULT NULL, `len` int(11) DEFAULT NULL, `length` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=116 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Dumping data for table veide_object.metter_metter: ~115 rows (approximately) INSERT INTO `metter_metter` (`id`, `ch`, `number`, `len`, `length`) VALUES (1, '', '50156373', 8, 8), (2, '', '85682335', 8, 8), (3, '', '5301873827', 10, 10), (4, '', '568796', 6, 6), (5, '', '4107948968', 10, 10), (6, '', '568693', 6, 6), (7, '', '524351225', 9, 9), (8, '', '4109420729', 10, 10), (9, '', '1611742', 7, 7), (10, '', '56993', 5, 5), (11, '', '50135314', 8, 8), (12, '', '6102615629', 10, 10), (13, '', '29446699', 8, 8), (14, '', '10498', 5, 5), (15, '', '52685418', 8, 8), (16, '', '32558090', 8, 8), (17, '', '4109420527', 10, 10), (18, '', '50028987', 8, 8), (19, '', '29446616', 8, 8), (20, '', '96397', 5, 5), (21, 'W', '264', 6, 3), (22, '', '91002891', 8, 8), (23, '', '50161280', 8, 8), (24, '', '32687084', 8, 8), (25, '', '11669109', 8, 8), (26, '', '32469776', 8, 8), (27, '', '32199561', 8, 8), (28, '', '6102615900', 10, 10), (29, '', '50089149', 8, 8), (30, '', '4109420280', 10, 10), (31, '', '32414204', 8, 8), (32, '', '11688670', 8, 8), (33, '', '53119305', 8, 8), (34, '', '32245598', 8, 8), (35, '', '4109420145', 10, 10), (36, '', '50088638', 8, 8), (37, '', '5116905827', 10, 10), (38, '', '32778874', 8, 8), (39, '', '5110224320', 10, 10), (40, '', '50050895', 8, 8), (41, '', '4109420471', 10, 10), (42, '', '29376210', 8, 8), (43, '', '4109420325', 10, 10), (44, '', '29376219', 8, 8), (45, '', '4224014', 7, 7), (46, '', '110081', 6, 6), (47, '', '32647524', 8, 8), (48, '', '53117522', 8, 8), (49, '', '32295119', 8, 8), (50, '', '5117901127', 10, 10), (51, '', '53117426', 8, 8), (52, '', '32248826', 8, 8), (53, '', '32719941', 8, 8), (54, '', '5110914629', 10, 10), (55, '', '100145', 6, 6), (56, '', '5110833045', 10, 10), (57, '', '50152351', 8, 8), (58, '', '50028646', 8, 8), (59, '', '29376264', 8, 8), (60, '', '82776', 5, 5), (61, '', '4114492386', 10, 10), (62, '', '32778527', 8, 8), (63, '', '91005292', 8, 8), (64, '', '50086615', 8, 8), (65, '', '3639101', 7, 7), (66, '', '251280', 6, 6), (67, '', '557538', 6, 6), (68, '', '4114492229', 10, 10), (69, '', '50028590', 8, 8), (70, '', '52685472', 8, 8), (71, '', '33165911', 8, 8), (72, '', '29376213', 8, 8), (73, '', '97125', 5, 5), (74, '', '50158093', 8, 8), (75, '', '11669252', 8, 8), (76, '', '32996418', 8, 8), (77, '', '11667994', 8, 8), (78, '', '580689', 6, 6), (79, '', '4117013300', 10, 10), (80, '', '29292035', 8, 8), (81, '', '32735197', 8, 8), (82, '', '11667895', 8, 8), (83, '', '29376263', 8, 8), (84, '', '32276933', 8, 8), (85, '', '291044', 6, 6), (86, '', '29446613', 8, 8), (87, '', '7950242', 7, 7), (88, '', '11671877', 8, 8), (89, '', '29375625', 8, 8), (90, '', '32202631', 8, 8), (91, '', '1,84611E+11', 12, 12), (92, '', '341236', 6, 6), (93, '', '5116907043', 10, 10), (94, '', '83543477', 8, 8), (95, '', '11400194', 8, 8), (96, '', '29292023', 8, 8), (97, '', '34237', 5, 5), (98, '', '29292026', 8, 8), (99, '', '5116907100', 10, 10), (100, '', '184611011', 9, 9), (101, '', '34231', 5, 5), (102, '', '29292037', 8, 8), (103, '', '11675292', 8, 8), (104, '', '32735797', 8, 8), (105, '', '4114492173', 10, 10), (106, '', '41144920227', 11, 11), (107, '', '4114492027', 10, 10), (108, '', '556043', 6, 6), (109, '', '29376231', 8, 8), (110, '', '4109420886', 10, 10), (111, '', '3493235', 7, 7), (112, '', '50121054', 8, 8), (113, '', '6100663686', 10, 10), (114, '', '32149396', 8, 8), (115, '', '6103887463', 10, 10); -- Dumping structure for table veide_object.object CREATE TABLE IF NOT EXISTS `object` ( `id` int(10) unsigned NOT NULL, `date_from` date DEFAULT NULL, `date_to` date DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Dumping data for table veide_object.object: ~55 rows (approximately) INSERT INTO `object` (`id`, `date_from`, `date_to`) VALUES (1, '2019-04-30', '3000-01-01'), (2, '2012-07-30', '3000-01-01'), (3, '2010-04-27', '3000-01-01'), (4, '2024-10-01', '3000-01-01'), (5, '2008-02-01', '3000-01-01'), (6, '2012-07-30', '3000-01-01'), (7, '2010-04-27', '3000-01-01'), (8, '2000-01-01', '3000-01-01'), (9, '2012-07-30', '3000-01-01'), (10, '2000-01-01', '3000-01-01'), (11, '2014-10-30', '3000-01-01'), (12, '2000-01-01', '3000-01-01'), (13, '2000-01-01', '3000-01-01'), (14, '2000-01-01', '3000-01-01'), (15, '2012-07-30', '3000-01-01'), (16, '2014-10-30', '3000-01-01'), (17, '2005-11-01', '3000-01-01'), (18, '2010-04-27', '3000-01-01'), (19, '2000-01-01', '3000-01-01'), (20, '2000-01-01', '3000-01-01'), (21, '2000-01-01', '3000-01-01'), (22, '2000-01-01', '3000-01-01'), (23, '2008-03-01', '3000-01-01'), (24, '2000-01-01', '3000-01-01'), (25, '2017-01-31', '3000-01-01'), (26, '2018-09-01', '3000-01-01'), (27, '2000-01-01', '3000-01-01'), (28, '2000-01-01', '3000-01-01'), (29, '2017-01-31', '3000-01-01'), (30, '2014-11-30', '3000-01-01'), (31, '2000-01-01', '3000-01-01'), (32, '2010-04-27', '3000-01-01'), (33, '2000-01-01', '3000-01-01'), (34, '2008-02-01', '3000-01-01'), (35, '2000-01-01', '3000-01-01'), (36, '2008-02-01', '3000-01-01'), (37, '2005-11-01', '3000-01-01'), (38, '2010-04-27', '3000-01-01'), (39, '2005-11-01', '3000-01-01'), (40, '2000-01-01', '3000-01-01'), (41, '2008-02-01', '3000-01-01'), (42, '2000-01-01', '3000-01-01'), (43, '2000-01-01', '3000-01-01'), (44, '2000-01-01', '3000-01-01'), (45, '2005-11-01', '3000-01-01'), (46, '2012-07-30', '3000-01-01'), (47, '2012-07-30', '3000-01-01'), (48, '2005-12-01', '3000-01-01'), (49, '2008-02-01', '3000-01-01'), (50, '2000-01-01', '3000-01-01'), (51, '2008-02-01', '3000-01-01'), (52, '2000-01-01', '3000-01-01'), (53, '2008-02-01', '3000-01-01'), (54, '2021-07-30', '3000-01-01'), (55, '2000-01-01', '2019-05-31'); -- Dumping structure for view veide_object.objects -- Creating temporary table to overcome VIEW dependency errors CREATE TABLE `objects` ( `id` INT(10) UNSIGNED NOT NULL, `name` VARCHAR(69) NULL COLLATE 'utf8mb4_unicode_ci' ) ENGINE=MyISAM; -- Dumping structure for table veide_object.object_id CREATE TABLE IF NOT EXISTS `object_id` ( `id` int(10) unsigned NOT NULL, `id_object` int(10) unsigned NOT NULL, `id_address` int(10) unsigned NOT NULL DEFAULT 0, `direction` int(10) unsigned NOT NULL, PRIMARY KEY (`id`), KEY `FK_object_id_address (table)` (`id_address`), KEY `FK_object_id_object` (`id_object`), CONSTRAINT `FK_object_id_address (table)` FOREIGN KEY (`id_address`) REFERENCES `address (table)` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `FK_object_id_object` FOREIGN KEY (`id_object`) REFERENCES `object` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Dumping data for table veide_object.object_id: ~127 rows (approximately) INSERT INTO `object_id` (`id`, `id_object`, `id_address`, `direction`) VALUES (1, 1, 100319280, 1), (2, 1, 100319272, 2), (3, 2, 100319498, 1), (4, 2, 100319891, 2), (5, 3, 100319498, 1), (6, 3, 100320363, 2), (7, 4, 100319666, 1), (8, 4, 100319088, 2), (9, 4, 100320628, 3), (10, 5, 100319666, 1), (11, 5, 100319096, 2), (12, 6, 100319666, 1), (13, 6, 100319102, 2), (14, 7, 100319666, 1), (15, 7, 100319280, 2), (16, 8, 100319666, 1), (17, 8, 100412330, 2), (18, 9, 100319666, 1), (19, 9, 100320941, 2), (20, 9, 100319473, 3), (21, 10, 100319666, 1), (22, 10, 100321404, 2), (23, 10, 100319264, 3), (24, 11, 100319666, 1), (25, 11, 100321871, 2), (26, 12, 100320507, 1), (27, 12, 100319176, 2), (28, 12, 100321871, 3), (29, 13, 100320507, 1), (30, 13, 100320090, 2), (31, 14, 100320507, 1), (32, 14, 100319617, 2), (33, 14, 100320314, 3), (34, 15, 100320507, 1), (35, 15, 100321228, 2), (36, 16, 100320507, 1), (37, 16, 100321131, 2), (38, 17, 100320507, 1), (39, 17, 100320234, 2), (40, 18, 100320507, 1), (41, 18, 100320636, 2), (42, 18, 100319666, 3), (43, 19, 100320507, 1), (44, 19, 100320652, 2), (45, 20, 100320507, 1), (46, 20, 100320941, 2), (47, 21, 100320507, 1), (48, 21, 100320990, 2), (49, 21, 100320314, 3), (50, 22, 100320548, 1), (51, 22, 100320757, 2), (52, 22, 100321051, 3), (53, 23, 100320548, 1), (54, 23, 100321453, 2), (55, 24, 100320652, 1), (56, 24, 100321742, 2), (57, 25, 100320652, 1), (58, 25, 100322221, 2), (59, 26, 100320941, 1), (60, 26, 100320314, 2), (61, 27, 100320941, 1), (62, 27, 100319682, 2), (63, 28, 100320941, 1), (64, 28, 100321726, 2), (65, 29, 100320941, 1), (66, 29, 100321605, 2), (67, 29, 100322221, 3), (68, 30, 100320941, 1), (69, 30, 100321638, 2), (70, 31, 100321197, 1), (71, 31, 100319176, 2), (72, 32, 100321197, 1), (73, 32, 100321131, 2), (74, 33, 100321197, 1), (75, 33, 100320234, 2), (76, 34, 100321197, 1), (77, 34, 100320652, 2), (78, 35, 100321197, 1), (79, 35, 100320941, 2), (80, 36, 100321197, 1), (81, 36, 100321750, 2), (82, 36, 100321398, 3), (83, 36, 100319842, 4), (84, 37, 100321228, 1), (85, 37, 100319498, 2), (86, 38, 100321228, 1), (87, 38, 100320041, 2), (88, 38, 100320363, 3), (89, 39, 100321228, 1), (90, 39, 100320066, 2), (91, 40, 100321228, 1), (92, 40, 100320162, 2), (93, 40, 100319891, 3), (94, 41, 100321228, 1), (95, 41, 100418212, 2), (96, 42, 100321228, 1), (97, 42, 100320597, 2), (98, 43, 100321228, 1), (99, 43, 100320982, 2), (100, 43, 100321148, 3), (101, 44, 100321228, 1), (102, 44, 100321718, 2), (103, 45, 100321332, 1), (104, 46, 100321332, 1), (105, 46, 100319617, 2), (106, 46, 100319280, 3), (107, 47, 100321332, 1), (108, 47, 100319842, 2), (109, 48, 100321332, 1), (110, 48, 100321131, 2), (111, 48, 100319738, 3), (112, 49, 100321332, 1), (113, 49, 100320652, 2), (114, 50, 100321332, 1), (115, 50, 100321750, 2), (116, 51, 100321404, 1), (117, 51, 100320708, 2), (118, 51, 100322246, 3), (119, 52, 100321871, 1), (120, 52, 100320314, 2), (121, 53, 100322166, 1), (122, 53, 100320074, 2), (123, 53, 100320886, 3), (124, 54, 100322166, 1), (125, 54, 100321605, 2), (126, 55, 100322166, 1), (127, 55, 100321638, 2); -- Removing temporary table and create final VIEW structure DROP TABLE IF EXISTS `metter_consum`; CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `metter_consum` AS SELECT o.id, o.name, concat( trim(mm.ch), LPAD( mm.number, mm.len-LENGTH(trim(mm.ch)), 0 ) ) AS number FROM objects AS o LEFT JOIN metter_acounting AS ma ON ma.id_object = o.id RIGHT JOIN metter_metter AS mm ON ma.number = mm.number ORDER BY o.id ; -- Removing temporary table and create final VIEW structure DROP TABLE IF EXISTS `objects`; CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `objects` AS SELECT od_base.id_object AS id, CONCAT_WS( ' - ', if (ad1.Iname = 'IKI', "Rīgas", ad1.Iname), ad2.Iname, ad3.Iname, ad4.Iname ) AS name FROM object_id AS od_base LEFT JOIN object_id AS od1 ON od_base.id_object = od1.id_object AND od1.direction = 1 LEFT JOIN object_id AS od2 ON od_base.id_object = od2.id_object AND od2.direction = 2 LEFT JOIN object_id AS od3 ON od_base.id_object = od3.id_object AND od3.direction = 3 LEFT JOIN object_id AS od4 ON od_base.id_object = od4.id_object AND od4.direction = 4 LEFT JOIN `address (table)` AS ad1 ON od1.id_address = ad1.id LEFT JOIN `address (table)` AS ad2 ON od2.id_address = ad2.id LEFT JOIN `address (table)` AS ad3 ON od3.id_address = ad3.id LEFT JOIN `address (table)` AS ad4 ON od4.id_address = ad4.id GROUP BY od_base.id_object ORDER BY od_base.id_object ; /*!40103 SET TIME_ZONE=IFNULL(@OLD_TIME_ZONE, 'system') */; /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */; /*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;