· 4 years ago · Jan 28, 2021, 11:38 PM
1-- phpMyAdmin SQL Dump
2-- version 5.0.2
3-- https://www.phpmyadmin.net/
4--
5-- Host: 127.0.0.1
6-- Generation Time: Jan 29, 2021 at 12:24 AM
7-- Server version: 10.4.14-MariaDB
8-- PHP Version: 7.4.9
9
10SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
11START TRANSACTION;
12SET time_zone = "+00:00";
13
14
15/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
16/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
17/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
18/*!40101 SET NAMES utf8mb4 */;
19
20--
21-- Database: `pos`
22--
23
24-- --------------------------------------------------------
25
26--
27-- Table structure for table `artikujt_e_faturave`
28--
29
30CREATE TABLE `artikujt_e_faturave` (
31 `id` int(11) NOT NULL,
32 `produkt_id` int(11) NOT NULL,
33 `sasia` int(11) NOT NULL,
34 `fatura_id` int(11) NOT NULL
35) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
36
37--
38-- Dumping data for table `artikujt_e_faturave`
39--
40
41INSERT INTO `artikujt_e_faturave` (`id`, `produkt_id`, `sasia`, `fatura_id`) VALUES
42(1, 5, 1, 1),
43(2, 4, 2, 1),
44(5, 7, 3, 3),
45(6, 5, 2, 3),
46(7, 7, 1, 3),
47(8, 5, 5, 4);
48
49-- --------------------------------------------------------
50
51--
52-- Stand-in structure for view `artikuj_fatura_detaje`
53-- (See below for the actual view)
54--
55CREATE TABLE `artikuj_fatura_detaje` (
56`id` int(11)
57,`fatura_id` int(11)
58,`sasia` int(11)
59,`emer` varchar(255)
60,`cmimi` int(11)
61,`totali` decimal(42,0)
62);
63
64-- --------------------------------------------------------
65
66--
67-- Table structure for table `dyqanet`
68--
69
70CREATE TABLE `dyqanet` (
71 `id` int(11) NOT NULL,
72 `emer` varchar(255) NOT NULL,
73 `adresa` varchar(255) NOT NULL
74) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
75
76--
77-- Dumping data for table `dyqanet`
78--
79
80INSERT INTO `dyqanet` (`id`, `emer`, `adresa`) VALUES
81(1, 'Kioska 1', 'Bllok'),
82(2, 'Kioska 2', 'Qender'),
83(3, 'Kioska 3', 'Kombinat');
84
85-- --------------------------------------------------------
86
87--
88-- Table structure for table `faturat`
89--
90
91CREATE TABLE `faturat` (
92 `id` int(11) NOT NULL,
93 `puntore_id` int(11) NOT NULL,
94 `data_e_leshimit` datetime NOT NULL DEFAULT current_timestamp(),
95 `dyqani_leshues_id` int(11) NOT NULL
96) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
97
98--
99-- Dumping data for table `faturat`
100--
101
102INSERT INTO `faturat` (`id`, `puntore_id`, `data_e_leshimit`, `dyqani_leshues_id`) VALUES
103(1, 1, '2021-01-27 23:26:52', 1),
104(3, 1, '2021-01-28 00:03:57', 3),
105(4, 1, '2021-01-28 00:12:04', 2);
106
107-- --------------------------------------------------------
108
109--
110-- Table structure for table `furnitoret`
111--
112
113CREATE TABLE `furnitoret` (
114 `id` int(11) NOT NULL,
115 `emer` varchar(255) NOT NULL,
116 `email` varchar(255) NOT NULL,
117 `telefon` int(255) NOT NULL
118) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
119
120-- --------------------------------------------------------
121
122--
123-- Table structure for table `magazinat`
124--
125
126CREATE TABLE `magazinat` (
127 `id` int(11) NOT NULL,
128 `emer` varchar(255) NOT NULL,
129 `adresa` varchar(255) NOT NULL
130) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
131
132-- --------------------------------------------------------
133
134--
135-- Table structure for table `produktet`
136--
137
138CREATE TABLE `produktet` (
139 `id` int(11) NOT NULL,
140 `emer` varchar(255) NOT NULL,
141 `cmimi` int(11) NOT NULL,
142 `data_skadence` date NOT NULL DEFAULT current_timestamp(),
143 `kategoria` varchar(100) NOT NULL,
144 `stok_minimal` int(11) NOT NULL DEFAULT 10,
145 `stok_optimal` int(11) NOT NULL DEFAULT 50
146) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
147
148--
149-- Dumping data for table `produktet`
150--
151
152INSERT INTO `produktet` (`id`, `emer`, `cmimi`, `data_skadence`, `kategoria`, `stok_minimal`, `stok_optimal`) VALUES
153(1, 'snickers', 50, '2021-06-30', 'cokollata', 10, 50),
154(4, 'coca cola', 100, '2021-01-27', 'pije energjike', 10, 50),
155(5, 'albeni', 20, '2021-01-31', 'cokollata', 10, 50),
156(7, 'tutku', 10, '2021-01-29', 'cokollata', 10, 50);
157
158-- --------------------------------------------------------
159
160--
161-- Table structure for table `puntoret`
162--
163
164CREATE TABLE `puntoret` (
165 `id` int(11) NOT NULL,
166 `emer` varchar(100) NOT NULL,
167 `mbiemer` varchar(100) NOT NULL,
168 `email` varchar(100) NOT NULL,
169 `password` varchar(100) NOT NULL
170) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
171
172--
173-- Dumping data for table `puntoret`
174--
175
176INSERT INTO `puntoret` (`id`, `emer`, `mbiemer`, `email`, `password`) VALUES
177(1, 'enea', 'jahollari', 'eneajaho@email.com', '12345678'),
178(3, 'fiorelo', 'zere', 'fiorelo@email.com', '12345678'),
179(5, 'etjen', 'disha', 'etjeni@email.com', '12345678');
180
181-- --------------------------------------------------------
182
183--
184-- Structure for view `artikuj_fatura_detaje`
185--
186DROP TABLE IF EXISTS `artikuj_fatura_detaje`;
187
188CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `artikuj_fatura_detaje` AS select `aef`.`id` AS `id`,`aef`.`fatura_id` AS `fatura_id`,`aef`.`sasia` AS `sasia`,`p`.`emer` AS `emer`,`p`.`cmimi` AS `cmimi`,sum(`p`.`cmimi` * `aef`.`sasia`) AS `totali` from (`artikujt_e_faturave` `aef` left join `produktet` `p` on(`p`.`id` = `aef`.`produkt_id`)) group by `aef`.`fatura_id` ;
189
190--
191-- Indexes for dumped tables
192--
193
194--
195-- Indexes for table `artikujt_e_faturave`
196--
197ALTER TABLE `artikujt_e_faturave`
198 ADD PRIMARY KEY (`id`),
199 ADD KEY `fatura_id` (`fatura_id`),
200 ADD KEY `id` (`id`),
201 ADD KEY `produkt_id_fk` (`produkt_id`);
202
203--
204-- Indexes for table `dyqanet`
205--
206ALTER TABLE `dyqanet`
207 ADD PRIMARY KEY (`id`);
208
209--
210-- Indexes for table `faturat`
211--
212ALTER TABLE `faturat`
213 ADD PRIMARY KEY (`id`),
214 ADD KEY `dyqani_id_fk` (`dyqani_leshues_id`),
215 ADD KEY `puntore_id_fk` (`puntore_id`);
216
217--
218-- Indexes for table `furnitoret`
219--
220ALTER TABLE `furnitoret`
221 ADD PRIMARY KEY (`id`);
222
223--
224-- Indexes for table `magazinat`
225--
226ALTER TABLE `magazinat`
227 ADD PRIMARY KEY (`id`);
228
229--
230-- Indexes for table `produktet`
231--
232ALTER TABLE `produktet`
233 ADD PRIMARY KEY (`id`);
234
235--
236-- Indexes for table `puntoret`
237--
238ALTER TABLE `puntoret`
239 ADD PRIMARY KEY (`id`);
240
241--
242-- AUTO_INCREMENT for dumped tables
243--
244
245--
246-- AUTO_INCREMENT for table `artikujt_e_faturave`
247--
248ALTER TABLE `artikujt_e_faturave`
249 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=9;
250
251--
252-- AUTO_INCREMENT for table `dyqanet`
253--
254ALTER TABLE `dyqanet`
255 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;
256
257--
258-- AUTO_INCREMENT for table `faturat`
259--
260ALTER TABLE `faturat`
261 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;
262
263--
264-- AUTO_INCREMENT for table `furnitoret`
265--
266ALTER TABLE `furnitoret`
267 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
268
269--
270-- AUTO_INCREMENT for table `magazinat`
271--
272ALTER TABLE `magazinat`
273 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
274
275--
276-- AUTO_INCREMENT for table `produktet`
277--
278ALTER TABLE `produktet`
279 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;
280
281--
282-- AUTO_INCREMENT for table `puntoret`
283--
284ALTER TABLE `puntoret`
285 MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
286
287--
288-- Constraints for dumped tables
289--
290
291--
292-- Constraints for table `artikujt_e_faturave`
293--
294ALTER TABLE `artikujt_e_faturave`
295 ADD CONSTRAINT `fatura_id_fk` FOREIGN KEY (`fatura_id`) REFERENCES `faturat` (`id`) ON DELETE CASCADE,
296 ADD CONSTRAINT `produkt_id_fk` FOREIGN KEY (`produkt_id`) REFERENCES `produktet` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION;
297
298--
299-- Constraints for table `faturat`
300--
301ALTER TABLE `faturat`
302 ADD CONSTRAINT `dyqani_id_fk` FOREIGN KEY (`dyqani_leshues_id`) REFERENCES `dyqanet` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
303 ADD CONSTRAINT `puntore_id_fk` FOREIGN KEY (`puntore_id`) REFERENCES `puntoret` (`id`);
304COMMIT;
305
306/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
307/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
308/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
309
310