-- --------------------------------------------------------
-- Host:                         127.0.0.1
-- Server version:               10.4.32-MariaDB - mariadb.org binary distribution
-- Server OS:                    Win64
-- HeidiSQL Version:             12.14.0.7165
-- --------------------------------------------------------

/*!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 database structure for kr_trenesanas_eksamenam_1
CREATE DATABASE IF NOT EXISTS `kr_trenesanas_eksamenam_1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci */;
USE `kr_trenesanas_eksamenam_1`;

-- Dumping structure for table kr_trenesanas_eksamenam_1.darba_ieraksti
CREATE TABLE IF NOT EXISTS `darba_ieraksti` (
  `id` tinyint(4) NOT NULL,
  `darba_id` tinyint(4) NOT NULL,
  `vienibas_id` tinyint(4) NOT NULL,
  `daudzums` tinyint(4) NOT NULL,
  `cena_par_vienibu` decimal(20,6) NOT NULL,
  `summa` decimal(20,6) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table kr_trenesanas_eksamenam_1.darba_ieraksti: ~8 rows (approximately)
INSERT INTO `darba_ieraksti` (`id`, `darba_id`, `vienibas_id`, `daudzums`, `cena_par_vienibu`, `summa`) VALUES
	(1, 1, 1, 1, 5000.000000, 5000.000000),
	(2, 2, 1, 1, 250.000000, 250.000000),
	(3, 3, 1, 1, 360.000000, 360.000000),
	(4, 4, 1, 4, 430.000000, 1720.000000),
	(5, 5, 1, 4, 330.000000, 1320.000000),
	(6, 6, 1, 4, 250.000000, 1000.000000),
	(7, 7, 1, 4, 145.000000, 580.000000),
	(8, 8, 2, 80, 2.950000, 236.000000);

-- Dumping structure for table kr_trenesanas_eksamenam_1.darbi
CREATE TABLE IF NOT EXISTS `darbi` (
  `id` tinyint(4) NOT NULL,
  `nosaukums` varchar(70) NOT NULL,
  `apraksts` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table kr_trenesanas_eksamenam_1.darbi: ~8 rows (approximately)
INSERT INTO `darbi` (`id`, `nosaukums`, `apraksts`) VALUES
	(1, 'Kontrolieris "SWARCO Traffic Hungaria Kft" VTC 2000', 'Kontrolieris'),
	(2, 'Kontroliera skapis', 'Skapis'),
	(3, 'Elektroenerģijas uzskaites skapis LIS-A', 'Elektroenerģijas skapis'),
	(4, 'Luksoforu signalgalvas autotransportam ar gaismas diodēm LED 300x3', 'Signalgalva'),
	(5, 'Luksoforu signalgalvas gājējiem ar gaismas diodēm LED 200x2', 'Signalgalva'),
	(6, 'Luksoforu stabi ar pamati', 'Stabi'),
	(7, 'Izsaukuma pogas', 'Pogas'),
	(8, 'Kabelis MCMO 12x1,5 mm2', 'Kabelis');

-- Dumping structure for view kr_trenesanas_eksamenam_1.kr_result_1
-- Creating temporary table to overcome VIEW dependency errors
CREATE TABLE `kr_result_1` (
	`id` TINYINT(4) NOT NULL,
	`darbs` VARCHAR(1) NOT NULL COLLATE 'utf8mb4_general_ci',
	`vieniba` VARCHAR(1) NOT NULL COLLATE 'utf8mb4_general_ci',
	`daudzums` TINYINT(4) NOT NULL,
	`cena_par_vienibu` DECIMAL(20,6) NOT NULL,
	`summa` DECIMAL(20,6) NOT NULL
);

-- Dumping structure for table kr_trenesanas_eksamenam_1.vieniba
CREATE TABLE IF NOT EXISTS `vieniba` (
  `id` tinyint(4) NOT NULL,
  `nosaukums` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Dumping data for table kr_trenesanas_eksamenam_1.vieniba: ~3 rows (approximately)
INSERT INTO `vieniba` (`id`, `nosaukums`) VALUES
	(1, 'Gab.'),
	(2, 'm'),
	(3, 'm2');

-- Removing temporary table and create final VIEW structure
DROP TABLE IF EXISTS `kr_result_1`;
CREATE ALGORITHM=UNDEFINED SQL SECURITY DEFINER VIEW `kr_result_1` AS SELECT 
    di.id,
    d.nosaukums AS darbs,
    v.nosaukums AS vieniba,
    di.daudzums,
    di.cena_par_vienibu,
    di.summa
FROM Darba_ieraksti di
JOIN Darbi d ON di.darba_id = d.id
JOIN Vieniba v ON di.vienibas_id = v.id 
;

/*!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) */;
