-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `pa_trenesanas_eksamenam_1`
--

-- --------------------------------------------------------

--
-- Table structure for table `Apraksti`
--

CREATE TABLE `Apraksti` (
  `id` int NOT NULL COMMENT 'unikāls ID',
  `kods` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'unikāls darba identifikators',
  `apraksts` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'darba apraksts',
  `mervieniba` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'darba apjoma mērvienība'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='satur darbu aprakstus un mērvienības';

--
-- Dumping data for table `Apraksti`
--

INSERT INTO `Apraksti` (`id`, `kods`, `apraksts`, `mervieniba`) VALUES
(1, '5.2', 'Maršruta apsekošana', 'km'),
(2, '5.1', 'Maršruta apsekošana', 'gab.'),
(3, '3.17', 'Ražošanas sapulce', 'm.'),
(4, '2.4', 'Signālgalvu uzstādīšana', 'kompl.'),
(5, '1.28', 'Signālgalvu uzstādīšana', ''),
(6, '4.4', 'Signālgalvu pievienošana', ''),
(7, '2.43', 'Signālgalvu pievienošana', ''),
(8, '1.11', 'Nenodoto signālgalvu sanešana Mazā noliktavā', ''),
(9, '1.25', 'Maršruta apsekošana', ''),
(10, '2.27', 'Ražošanas sapulce', ''),
(11, '1.26', 'Donatas interesējas par Stacijas MINI luksoforu', ''),
(12, '2.25', 'Skaitītāju rādījumi, P/S žurnāls', ''),
(13, '', 'Skaitītāju rādījumi, P/S žurnāls', ''),
(14, '', 'Rēķinu ievešana P/S', ''),
(15, '', 'Kļūda GEP', ''),
(16, '', 'Māris Mielavs par meteostaciju uzturēšanu', ''),
(17, '', 'Andrejs Bobikins liek pārbaudīt skaitītāja rādījumus', ''),
(18, '', 'Andrejs Bobikins liek pārbaudīt skaitītāja rādījumus', ''),
(19, '', 'GP1G1', ''),
(20, '', 'Vadības plates ZKS tipa drošinātāja nomaiņa', ''),
(21, '', 'Bojājuma noteikšana', ''),
(22, '', 'Trūkst balsta vāks', ''),
(23, '', 'Balsta vāks', ''),
(24, '', 'Kopētāja sagatavošana darbam, Veikala printera neveiksmīga instalēšana, Anrda datora papildus konfigurēšana', ''),
(25, '', 'Maršruta apsekošana', '');


-- --------------------------------------------------------

--
-- Table structure for table `Apsekojumi`
--

CREATE TABLE `Apsekojumi` (
  `id` int NOT NULL COMMENT 'unikāls ID',
  `apsekojuma_kods` varchar(20) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'apsekojuma unikāls kods',
  `datums` date DEFAULT NULL COMMENT 'apsekojuma datums'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='glabā informāciju par apsekojumiem (identifikatoru un datumu)';

--
-- Dumping data for table `Apsekojumi`
--

INSERT INTO `Apsekojumi` (`id`, `apsekojuma_kods`, `datums`) VALUES
(1, '2012.01.01-01', '2012-01-01'),
(2, '2012.01.02-01', '2012-01-02'),
(3, '2012.01.03-01', '2012-01-03'),
(4, '2012.01.03-02', '2012-01-03'),
(5, '2012.01.03-03', '2012-01-03'),
(6, '2012.01.03-04', '2012-01-03'),
(7, '2012.01.03-05', '2012-01-03'),
(8, '2012.01.03-06', '2012-01-03'),
(9, '2012.01.04-01', '2012-01-04'),
(10, '2012.01.04-02', '2012-01-04'),
(11, '2012.01.04-03', '2012-01-04'),
(12, '2012.01.05-01', '2012-01-05');

-- --------------------------------------------------------

--
-- Table structure for table `Darba_tipi`
--

CREATE TABLE `Darba_tipi` (
  `id` int NOT NULL COMMENT 'unikāls ID',
  `nosaukums` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'darba tipa nosaukums'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='satur darba tipu sarakstu';

--
-- Dumping data for table `Darba_tipi`
--

INSERT INTO `Darba_tipi` (`id`, `nosaukums`) VALUES
(1, 'Apgaita'),
(2, 'Apkope'),
(3, 'Būvniecība'),
(4, 'Datortehnikas apkope'),
(5, 'Dokumentācija'),
(6, 'Info'),
(7, 'Izsaukums'),
(8, 'Maiņa'),
(9, 'Projektēšana'),
(10, 'Sapulce');

-- --------------------------------------------------------

--
-- Table structure for table `Darba_veiceji`
--

CREATE TABLE `Darba_veiceji` (
  `id` int NOT NULL COMMENT 'unikāls ID',
  `vards` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'darbinieka vārds'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='satur darbinieku sarakstu';

--
-- Dumping data for table `Darba_veiceji`
--

INSERT INTO `Darba_veiceji` (`id`, `vards`) VALUES
(1, 'U.Grunde-Zeiferts'),
(2, 'Ē.Aukmanis');

-- --------------------------------------------------------

--
-- Table structure for table `Darbi`
--

CREATE TABLE `Darbi` (
  `id` int NOT NULL COMMENT 'unikāls ID',
  `datums` date DEFAULT NULL COMMENT 'darba datums',
  `sakuma_laiks` time DEFAULT NULL COMMENT 'sākuma laiks',
  `beigu_laiks` time DEFAULT NULL COMMENT 'beigu laiks',
  `stundas` time DEFAULT NULL COMMENT 'darba ilgums',
  `darba_tips_id` int DEFAULT NULL COMMENT 'atsauce uz Darba_tipi.id',
  `vieta_id` int DEFAULT NULL COMMENT 'atsauce uz Vietas.id',
  `darba_veicejs_id` int DEFAULT NULL COMMENT 'atsauce uz Darbinieki.id',
  `apraksts_id` int DEFAULT NULL COMMENT 'atsauce uz Apraksti.id',
  `apsekojums_id` int DEFAULT NULL COMMENT 'atsauce uz Apsekojumi.id',
  `daudzums` decimal(10,2) DEFAULT NULL COMMENT 'darba apjoms attiecīgajās mērvienībās',
  `cena` decimal(10,2) DEFAULT NULL COMMENT 'cena par vienu vienību',
  `kopa` decimal(10,2) DEFAULT NULL COMMENT 'kopējā summa (daudzums × cena)'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='glabā informāciju par visiem veiktajiem darbiem';

--
-- Dumping data for table `Darbi`
--

INSERT INTO `Darbi` (`id`, `datums`, `sakuma_laiks`, `beigu_laiks`, `stundas`, `darba_tips_id`, `vieta_id`, `darba_veicejs_id`, `apraksts_id`, `apsekojums_id`, `daudzums`, `cena`, `kopa`) VALUES
(1, '2012-01-01', '06:00:00', '07:30:00', '01:30:00', 1, 2, 1, 1, 1, '20.00', '1.00', '20.00'),
(2, '2012-01-02', '06:00:00', '07:30:00', '01:30:00', 1, 2, 1, 1, 2, '20.00', '1.00', '20.00'),
(3, '2012-01-02', '07:45:00', '08:30:00', '00:45:00', 2, 1, 1, 3, NULL, NULL, NULL, NULL),
(4, '2012-01-02', '09:00:00', '13:00:00', '04:00:00', 3, 3, 2, 4, NULL, NULL, NULL, NULL),
(5, '2012-01-02', '09:00:00', '13:00:00', '04:00:00', 3, 3, 1, 4, NULL, NULL, NULL, NULL),
(6, '2012-01-02', '13:30:00', '15:00:00', '01:30:00', 3, 3, 2, 6, NULL, NULL, NULL, NULL),
(7, '2012-01-02', '13:30:00', '16:00:00', '02:30:00', 3, 3, 1, 6, NULL, NULL, NULL, NULL),
(8, '2012-01-02', '16:00:00', '17:00:00', '01:00:00', 4, 1, 1, 8, NULL, NULL, NULL, NULL),
(9, '2012-01-03', '06:00:00', '07:30:00', '01:30:00', 1, 2, 1, 1, 3, '20.00', '1.00', '20.00'),
(10, '2012-01-03', '07:45:00', '08:30:00', '00:45:00', 2, 1, 1, 3, NULL, NULL, NULL, NULL),
(11, '2012-01-03', '08:40:00', '08:50:00', '00:10:00', 4, 1, 1, 11, NULL, NULL, NULL, NULL),
(12, '2012-01-03', '09:00:00', '10:30:00', '01:30:00', 5, 1, 1, 12, NULL, NULL, NULL, NULL),
(13, '2012-01-03', '09:00:00', '10:30:00', '01:30:00', 5, 1, 1, 12, NULL, NULL, NULL, NULL),
(14, '2012-01-03', '10:30:00', '10:45:00', '00:15:00', 5, 8, 1, 14, NULL, NULL, NULL, NULL),
(15, '2012-01-03', '11:50:00', '12:50:00', '01:00:00', 6, 7, 1, 15, 4, '1.00', '10.00', '10.00'),
(16, '2012-01-03', '13:00:00', '13:45:00', '00:45:00', 4, 8, 1, 16, NULL, NULL, NULL, NULL),
(17, '2012-01-03', '14:00:00', '14:15:00', '00:15:00', 6, 5, 1, 17, 5, '1.00', '10.00', '10.00'),
(18, '2012-01-03', '14:15:00', '14:30:00', '00:15:00', 6, 6, 1, 17, 6, '1.00', '10.00', '10.00'),
(19, '2012-01-03', '14:50:00', '15:30:00', '00:40:00', 6, 4, 1, 19, 7, '1.00', '10.00', '10.00'),
(20, '2012-01-03', '14:50:00', '15:30:00', '00:40:00', 7, 4, 1, 20, NULL, '1.00', '10.00', '10.00'),
(21, '2012-01-03', '14:50:00', '15:30:00', '00:40:00', 7, 4, 1, 21, NULL, '1.00', '5.00', '5.00'),
(22, '2012-01-03', '15:45:00', '16:00:00', '00:15:00', 6, 3, 1, 22, 8, '1.00', '10.00', '10.00'),
(23, '2012-01-03', '15:45:00', '16:00:00', '00:15:00', 7, 3, 1, 23, NULL, '1.00', '5.00', '5.00'),
(24, '2012-01-03', '16:00:00', '17:30:00', '01:30:00', 8, 1, 1, 24, NULL, NULL, NULL, NULL),
(25, '2012-01-04', '06:00:00', '07:30:00', '01:30:00', 1, 2, 1, 1, 9, '20.00', '1.00', '20.00'),
(26, '2012-01-04', '07:45:00', '08:30:00', '00:45:00', 2, 1, 1, 3, NULL, NULL, NULL, NULL),
(27, '2012-01-04', '08:30:00', '09:00:00', '00:30:00', 5, 1, 1, 16, NULL, NULL, NULL, NULL),
(28, '2012-01-04', '09:00:00', '16:00:00', '07:00:00', 6, 4, 1, 22, 10, '1.00', '10.00', '10.00'),
(29, '2012-01-04', '09:00:00', '09:30:00', '00:30:00', 7, 4, 1, 5, NULL, '1.00', '20.00', '20.00'),
(30, '2012-01-04', '09:30:00', '10:00:00', '00:30:00', 5, 1, 1, 12, NULL, NULL, NULL, NULL),
(31, '2012-01-04', '10:00:00', '11:30:00', '01:30:00', 8, 1, 1, 24, NULL, NULL, NULL, NULL),
(32, '2012-01-04', '11:30:00', '13:00:00', '01:30:00', 8, 1, 1, 24, NULL, NULL, NULL, NULL),
(33, '2012-01-04', '13:00:00', '13:30:00', '00:30:00', 5, 8, 1, 16, NULL, NULL, NULL, NULL),
(34, '2012-01-04', '13:30:00', '14:30:00', '01:00:00', 9, 8, 1, 16, NULL, NULL, NULL, NULL),
(35, '2012-01-04', '14:45:00', '15:00:00', '00:15:00', 8, 1, 1, 16, NULL, NULL, NULL, NULL),
(36, '2012-01-04', '14:22:00', '17:00:00', '02:38:00', 6, 10, 1, 25, 11, '1.00', '10.00', '10.00'),
(37, '2012-01-04', '15:00:00', '16:00:00', '01:00:00', 7, 10, 1, 4, NULL, '1.00', '10.00', '10.00'),
(38, '2012-01-04', '16:00:00', '17:30:00', '01:30:00', 7, 10, 1, 7, NULL, '1.00', '20.00', '20.00'),
(39, '2012-01-04', '16:00:00', '17:30:00', '01:30:00', 7, 10, 1, 8, NULL, '3.00', '1.50', '4.50'),
(40, '2012-01-04', '16:00:00', '17:30:00', '01:30:00', 7, 10, 1, 9, NULL, '1.00', '5.00', '5.00'),
(41, '2012-01-04', '21:00:00', '21:30:00', '00:30:00', 5, 9, 1, 16, NULL, NULL, NULL, NULL),
(42, '2012-01-04', '20:00:00', '21:00:00', '01:00:00', 10, 2, 1, 16, NULL, NULL, NULL, NULL),
(43, '2012-01-05', '06:00:00', '07:30:00', '01:30:00', 1, 2, 2, 1, 12, '20.00', '1.00', '20.00');

-- --------------------------------------------------------

--
-- Stand-in structure for view `darbi_excel_full`
-- (See below for the actual view)
--
CREATE TABLE `darbi_excel_full` (
`darbi_datums` varchar(5)
,`no` varchar(10)
,`līdz` varchar(10)
,`darba_tips` varchar(50)
,`vieta` varchar(50)
,`apraksts` varchar(255)
,`darbinieks` varchar(50)
,`darba_nr` varchar(20)
,`stundas` time
,`apsekojuma_datums` date
,`cena` decimal(10,2)
,`daudzums` decimal(10,2)
,`kopa` decimal(10,2)
);

-- --------------------------------------------------------

--
-- Table structure for table `Vietas`
--

CREATE TABLE `Vietas` (
  `id` int NOT NULL COMMENT 'unikāls ID',
  `nosaukums` varchar(50) COLLATE utf8mb4_unicode_ci DEFAULT NULL COMMENT 'darba vietas nosaukums'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='satur darbu izpildes vietas';

--
-- Dumping data for table `Vietas`
--

INSERT INTO `Vietas` (`id`, `nosaukums`) VALUES
(1, 'Dobeles-Brieža'),
(2, 'Jelgava'),
(3, 'ME'),
(4, 'P/S'),
(5, 'Raiņa-Akadēmijas'),
(6, 'Raiņa-Mātera'),
(7, 'Raiņa-Pasta'),
(8, 'Raiņa-Sarmas'),
(9, 'Rīgas-Izstādes'),
(10, 'mājās');

-- --------------------------------------------------------

--
-- Structure for view `darbi_excel_full`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`potapenko_a`@`localhost` SQL SECURITY DEFINER VIEW `darbi_excel_full`  AS SELECT date_format(`d`.`datums`,'%d.%m') AS `darbi_datums`, time_format(`d`.`sakuma_laiks`,'%H:%i') AS `no`, time_format(`d`.`beigu_laiks`,'%H:%i') AS `līdz`, `dt`.`nosaukums` AS `darba_tips`, `v`.`nosaukums` AS `vieta`, `apr`.`apraksts` AS `apraksts`, `darbin`.`vards` AS `darbinieks`, `aps`.`apsekojuma_kods` AS `darba_nr`, `d`.`stundas` AS `stundas`, `aps`.`datums` AS `apsekojuma_datums`, `d`.`cena` AS `cena`, `d`.`daudzums` AS `daudzums`, `d`.`kopa` AS `kopa` FROM (((((`Darbi` `d` left join `Darba_tipi` `dt` on((`d`.`darba_tips_id` = `dt`.`id`))) left join `Vietas` `v` on((`d`.`vieta_id` = `v`.`id`))) left join `Darba_veiceji` `darbin` on((`d`.`darba_veicejs_id` = `darbin`.`id`))) left join `Apraksti` `apr` on((`d`.`apraksts_id` = `apr`.`id`))) left join `Apsekojumi` `aps` on((`d`.`apsekojums_id` = `aps`.`id`))) ;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `Apraksti`
--
ALTER TABLE `Apraksti`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `Apsekojumi`
--
ALTER TABLE `Apsekojumi`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `Darba_tipi`
--
ALTER TABLE `Darba_tipi`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `Darba_veiceji`
--
ALTER TABLE `Darba_veiceji`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `Darbi`
--
ALTER TABLE `Darbi`
  ADD PRIMARY KEY (`id`),
  ADD KEY `darba_tips_id` (`darba_tips_id`),
  ADD KEY `vieta_id` (`vieta_id`),
  ADD KEY `darba_veicejs_id` (`darba_veicejs_id`),
  ADD KEY `apraksts_id` (`apraksts_id`),
  ADD KEY `apsekojums_id` (`apsekojums_id`);

--
-- Indexes for table `Vietas`
--
ALTER TABLE `Vietas`
  ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `Apraksti`
--
ALTER TABLE `Apraksti`
  MODIFY `id` int NOT NULL AUTO_INCREMENT COMMENT 'unikāls ID', AUTO_INCREMENT=130;

--
-- AUTO_INCREMENT for table `Apsekojumi`
--
ALTER TABLE `Apsekojumi`
  MODIFY `id` int NOT NULL AUTO_INCREMENT COMMENT 'unikāls ID', AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT for table `Darba_tipi`
--
ALTER TABLE `Darba_tipi`
  MODIFY `id` int NOT NULL AUTO_INCREMENT COMMENT 'unikāls ID', AUTO_INCREMENT=11;

--
-- AUTO_INCREMENT for table `Darba_veiceji`
--
ALTER TABLE `Darba_veiceji`
  MODIFY `id` int NOT NULL AUTO_INCREMENT COMMENT 'unikāls ID', AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `Darbi`
--
ALTER TABLE `Darbi`
  MODIFY `id` int NOT NULL AUTO_INCREMENT COMMENT 'unikāls ID', AUTO_INCREMENT=44;

--
-- AUTO_INCREMENT for table `Vietas`
--
ALTER TABLE `Vietas`
  MODIFY `id` int NOT NULL AUTO_INCREMENT COMMENT 'unikāls ID', AUTO_INCREMENT=11;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `Darbi`
--
ALTER TABLE `Darbi`
  ADD CONSTRAINT `Darbi_ibfk_1` FOREIGN KEY (`darba_tips_id`) REFERENCES `Darba_tipi` (`id`),
  ADD CONSTRAINT `Darbi_ibfk_2` FOREIGN KEY (`vieta_id`) REFERENCES `Vietas` (`id`),
  ADD CONSTRAINT `Darbi_ibfk_3` FOREIGN KEY (`darba_veicejs_id`) REFERENCES `Darba_veiceji` (`id`),
  ADD CONSTRAINT `Darbi_ibfk_4` FOREIGN KEY (`apraksts_id`) REFERENCES `Apraksti` (`id`),
  ADD CONSTRAINT `Darbi_ibfk_5` FOREIGN KEY (`apsekojums_id`) REFERENCES `Apsekojumi` (`id`);
COMMIT;

