· 8 years ago · Dec 04, 2017, 01:16 AM
1-- MySQL Script generated by MySQL Workbench
2-- Sun Dec 3 22:29:44 2017
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema project_build
12-- -----------------------------------------------------
13DROP SCHEMA IF EXISTS `project_build` ;
14
15-- -----------------------------------------------------
16-- Schema project_build
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `project_build` DEFAULT CHARACTER SET utf8 ;
19USE `project_build` ;
20
21-- -----------------------------------------------------
22-- Table `project_build`.`customer`
23-- -----------------------------------------------------
24CREATE TABLE IF NOT EXISTS `project_build`.`customer` (
25 `id` INT NOT NULL AUTO_INCREMENT,
26 `active` TINYINT NOT NULL DEFAULT 1,
27 `name` VARCHAR(255) NOT NULL,
28 `address` VARCHAR(500) NULL,
29 `email` VARCHAR(255) NULL,
30 `phone_number` VARCHAR(10) NULL,
31 PRIMARY KEY (`id`))
32ENGINE = InnoDB;
33
34
35-- -----------------------------------------------------
36-- Table `project_build`.`vendor`
37-- -----------------------------------------------------
38CREATE TABLE IF NOT EXISTS `project_build`.`vendor` (
39 `id` INT NOT NULL AUTO_INCREMENT,
40 `active` TINYINT NOT NULL DEFAULT 1,
41 `name` VARCHAR(255) NOT NULL,
42 `address` VARCHAR(500) NULL,
43 `email` VARCHAR(255) NULL,
44 `phone_number` VARCHAR(10) NULL,
45 PRIMARY KEY (`id`))
46ENGINE = InnoDB;
47
48
49-- -----------------------------------------------------
50-- Table `project_build`.`role`
51-- -----------------------------------------------------
52CREATE TABLE IF NOT EXISTS `project_build`.`role` (
53 `id` INT NOT NULL AUTO_INCREMENT,
54 `name` VARCHAR(255) NOT NULL,
55 `active` TINYINT NOT NULL DEFAULT 1,
56 PRIMARY KEY (`id`))
57ENGINE = InnoDB;
58
59
60-- -----------------------------------------------------
61-- Table `project_build`.`user`
62-- -----------------------------------------------------
63CREATE TABLE IF NOT EXISTS `project_build`.`user` (
64 `id` VARCHAR(255) NOT NULL,
65 `active` TINYINT NOT NULL DEFAULT 1,
66 `role_id` INT NOT NULL,
67 `password_hash` VARCHAR(64) NOT NULL,
68 `password_salt` VARCHAR(64) NOT NULL,
69 `name` VARCHAR(255) NOT NULL,
70 PRIMARY KEY (`id`),
71 INDEX `fk_user_role1_idx` (`role_id` ASC),
72 CONSTRAINT `fk_user_role1`
73 FOREIGN KEY (`role_id`)
74 REFERENCES `project_build`.`role` (`id`)
75 ON DELETE RESTRICT
76 ON UPDATE CASCADE)
77ENGINE = InnoDB;
78
79
80-- -----------------------------------------------------
81-- Table `project_build`.`project`
82-- -----------------------------------------------------
83CREATE TABLE IF NOT EXISTS `project_build`.`project` (
84 `id` INT NOT NULL AUTO_INCREMENT,
85 `name` VARCHAR(255) NOT NULL,
86 `address` VARCHAR(500) NULL,
87 `start_date` DATE NULL,
88 `end_date` DATE NULL,
89 `active` TINYINT NOT NULL DEFAULT 1,
90 PRIMARY KEY (`id`))
91ENGINE = InnoDB;
92
93
94-- -----------------------------------------------------
95-- Table `project_build`.`inventory_item`
96-- -----------------------------------------------------
97CREATE TABLE IF NOT EXISTS `project_build`.`inventory_item` (
98 `id` INT NOT NULL AUTO_INCREMENT,
99 `name` VARCHAR(500) NOT NULL,
100 `unit` VARCHAR(45) NOT NULL DEFAULT 'unit',
101 PRIMARY KEY (`id`))
102ENGINE = InnoDB;
103
104
105-- -----------------------------------------------------
106-- Table `project_build`.`inventory_item_price_list`
107-- -----------------------------------------------------
108CREATE TABLE IF NOT EXISTS `project_build`.`inventory_item_price_list` (
109 `id` INT NOT NULL AUTO_INCREMENT,
110 `name` VARCHAR(255) NOT NULL,
111 PRIMARY KEY (`id`))
112ENGINE = InnoDB;
113
114
115-- -----------------------------------------------------
116-- Table `project_build`.`inventory_item_price`
117-- -----------------------------------------------------
118CREATE TABLE IF NOT EXISTS `project_build`.`inventory_item_price` (
119 `inventory_item_price_list_id` INT NOT NULL,
120 `inventory_item_id` INT NOT NULL,
121 `price` DECIMAL(13,2) NOT NULL,
122 PRIMARY KEY (`inventory_item_price_list_id`, `inventory_item_id`),
123 INDEX `fk_inventory_item_price_inventory_item_price_list1_idx` (`inventory_item_price_list_id` ASC),
124 INDEX `fk_inventory_item_price_inventory_item1_idx` (`inventory_item_id` ASC),
125 CONSTRAINT `fk_inventory_item_price_inventory_item_price_list1`
126 FOREIGN KEY (`inventory_item_price_list_id`)
127 REFERENCES `project_build`.`inventory_item_price_list` (`id`)
128 ON DELETE RESTRICT
129 ON UPDATE CASCADE,
130 CONSTRAINT `fk_inventory_item_price_inventory_item1`
131 FOREIGN KEY (`inventory_item_id`)
132 REFERENCES `project_build`.`inventory_item` (`id`)
133 ON DELETE CASCADE
134 ON UPDATE CASCADE)
135ENGINE = InnoDB;
136
137
138-- -----------------------------------------------------
139-- Table `project_build`.`team_member`
140-- -----------------------------------------------------
141CREATE TABLE IF NOT EXISTS `project_build`.`team_member` (
142 `user_id` VARCHAR(255) NOT NULL,
143 `project_id` INT NOT NULL,
144 INDEX `fk_team_member_user_idx` (`user_id` ASC),
145 INDEX `fk_team_member_project1_idx` (`project_id` ASC),
146 PRIMARY KEY (`user_id`, `project_id`),
147 CONSTRAINT `fk_team_member_user`
148 FOREIGN KEY (`user_id`)
149 REFERENCES `project_build`.`user` (`id`)
150 ON DELETE NO ACTION
151 ON UPDATE NO ACTION,
152 CONSTRAINT `fk_team_member_project1`
153 FOREIGN KEY (`project_id`)
154 REFERENCES `project_build`.`project` (`id`)
155 ON DELETE NO ACTION
156 ON UPDATE NO ACTION)
157ENGINE = InnoDB;
158
159
160-- -----------------------------------------------------
161-- Table `project_build`.`budget_stage`
162-- -----------------------------------------------------
163CREATE TABLE IF NOT EXISTS `project_build`.`budget_stage` (
164 `id` INT NOT NULL AUTO_INCREMENT,
165 `project_id` INT NOT NULL,
166 `name` VARCHAR(255) NOT NULL,
167 PRIMARY KEY (`id`),
168 INDEX `fk_budget_stage_project1_idx` (`project_id` ASC),
169 CONSTRAINT `fk_budget_stage_project1`
170 FOREIGN KEY (`project_id`)
171 REFERENCES `project_build`.`project` (`id`)
172 ON DELETE CASCADE
173 ON UPDATE CASCADE)
174ENGINE = InnoDB;
175
176
177-- -----------------------------------------------------
178-- Table `project_build`.`budget_entry_material`
179-- -----------------------------------------------------
180CREATE TABLE IF NOT EXISTS `project_build`.`budget_entry_material` (
181 `id` INT NOT NULL AUTO_INCREMENT,
182 `budget_stage_id` INT NOT NULL,
183 `inventory_item_id` INT NOT NULL,
184 `no_of_units` INT NOT NULL,
185 `over_budget_limit` INT NOT NULL DEFAULT 0,
186 `inventory_item_price_list_id` INT NOT NULL,
187 INDEX `fk_budget_entry_budget_stage1_idx` (`budget_stage_id` ASC),
188 PRIMARY KEY (`id`),
189 INDEX `fk_budget_entry_inventory_item1_idx` (`inventory_item_id` ASC),
190 INDEX `fk_budget_entry_material_inventory_item_price1_idx` (`inventory_item_price_list_id` ASC, `inventory_item_id` ASC),
191 CONSTRAINT `fk_budget_entry_budget_stage1`
192 FOREIGN KEY (`budget_stage_id`)
193 REFERENCES `project_build`.`budget_stage` (`id`)
194 ON DELETE CASCADE
195 ON UPDATE CASCADE,
196 CONSTRAINT `fk_budget_entry_inventory_item1`
197 FOREIGN KEY (`inventory_item_id`)
198 REFERENCES `project_build`.`inventory_item` (`id`)
199 ON DELETE RESTRICT
200 ON UPDATE CASCADE,
201 CONSTRAINT `fk_budget_entry_material_inventory_item_price1`
202 FOREIGN KEY (`inventory_item_price_list_id` , `inventory_item_id`)
203 REFERENCES `project_build`.`inventory_item_price` (`inventory_item_price_list_id` , `inventory_item_id`)
204 ON DELETE RESTRICT
205 ON UPDATE CASCADE)
206ENGINE = InnoDB;
207
208
209-- -----------------------------------------------------
210-- Table `project_build`.`budget_entry_other`
211-- -----------------------------------------------------
212CREATE TABLE IF NOT EXISTS `project_build`.`budget_entry_other` (
213 `id` INT NOT NULL AUTO_INCREMENT,
214 `budget_stage_id` INT NOT NULL,
215 `ammount` DECIMAL(13,2) NOT NULL,
216 `over_budget_limit` DECIMAL(13,2) NOT NULL DEFAULT 0,
217 `name` VARCHAR(500) NOT NULL,
218 PRIMARY KEY (`id`),
219 INDEX `fk_budget_entry_other_budget_stage1_idx` (`budget_stage_id` ASC),
220 CONSTRAINT `fk_budget_entry_other_budget_stage1`
221 FOREIGN KEY (`budget_stage_id`)
222 REFERENCES `project_build`.`budget_stage` (`id`)
223 ON DELETE CASCADE
224 ON UPDATE CASCADE)
225ENGINE = InnoDB;
226
227
228-- -----------------------------------------------------
229-- Table `project_build`.`material_transaction`
230-- -----------------------------------------------------
231CREATE TABLE IF NOT EXISTS `project_build`.`material_transaction` (
232 `id` INT NOT NULL AUTO_INCREMENT,
233 `budget_entry_material_id` INT NULL,
234 `no_of_units` INT NOT NULL,
235 `ammount` DECIMAL(13,2) NULL,
236 `state` ENUM('to_be_approved', 'to_be_purchased', 'to_be_transfered', 'to_be_recived', 'to_be_paid', 'paid', 'transfered', 'denied', 'splitted') NOT NULL DEFAULT 'to_be_approved',
237 `parent_transaction_id` INT NULL DEFAULT NULL,
238 `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
239 PRIMARY KEY (`id`),
240 INDEX `fk_material_transaction_budget_entry_material1_idx` (`budget_entry_material_id` ASC),
241 INDEX `fk_material_transaction_material_transaction1_idx` (`parent_transaction_id` ASC),
242 CONSTRAINT `fk_material_transaction_budget_entry_material1`
243 FOREIGN KEY (`budget_entry_material_id`)
244 REFERENCES `project_build`.`budget_entry_material` (`id`)
245 ON DELETE SET NULL
246 ON UPDATE CASCADE,
247 CONSTRAINT `fk_material_transaction_material_transaction1`
248 FOREIGN KEY (`parent_transaction_id`)
249 REFERENCES `project_build`.`material_transaction` (`id`)
250 ON DELETE CASCADE
251 ON UPDATE CASCADE)
252ENGINE = InnoDB;
253
254
255-- -----------------------------------------------------
256-- Table `project_build`.`other_payment_transaction`
257-- -----------------------------------------------------
258CREATE TABLE IF NOT EXISTS `project_build`.`other_payment_transaction` (
259 `id` INT NOT NULL,
260 `budget_entry_other_id` INT NULL,
261 `ammount` DECIMAL(13,2) NOT NULL,
262 `state` ENUM('to_be_approved', 'to_be_paid', 'paid') NOT NULL DEFAULT 'to_be_approved',
263 `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
264 PRIMARY KEY (`id`),
265 INDEX `fk_other_payment_transaction_budget_entry_other1_idx` (`budget_entry_other_id` ASC),
266 CONSTRAINT `fk_other_payment_transaction_budget_entry_other1`
267 FOREIGN KEY (`budget_entry_other_id`)
268 REFERENCES `project_build`.`budget_entry_other` (`id`)
269 ON DELETE SET NULL
270 ON UPDATE CASCADE)
271ENGINE = InnoDB;
272
273
274-- -----------------------------------------------------
275-- Table `project_build`.`material_transaction_approving_user`
276-- -----------------------------------------------------
277CREATE TABLE IF NOT EXISTS `project_build`.`material_transaction_approving_user` (
278 `user_id` VARCHAR(255) NOT NULL,
279 `project_id` INT NOT NULL,
280 `request` TINYINT NOT NULL DEFAULT 0,
281 `approve` TINYINT NOT NULL DEFAULT 0,
282 `order` TINYINT NOT NULL DEFAULT 0,
283 `recive` TINYINT NOT NULL DEFAULT 0,
284 `pay` TINYINT NOT NULL DEFAULT 0,
285 INDEX `fk_material_transaction_approving_user_user1_idx` (`user_id` ASC),
286 INDEX `fk_material_transaction_approving_user_project1_idx` (`project_id` ASC),
287 PRIMARY KEY (`user_id`, `project_id`),
288 CONSTRAINT `fk_material_transaction_approving_user_user1`
289 FOREIGN KEY (`user_id`)
290 REFERENCES `project_build`.`user` (`id`)
291 ON DELETE RESTRICT
292 ON UPDATE CASCADE,
293 CONSTRAINT `fk_material_transaction_approving_user_project1`
294 FOREIGN KEY (`project_id`)
295 REFERENCES `project_build`.`project` (`id`)
296 ON DELETE NO ACTION
297 ON UPDATE NO ACTION)
298ENGINE = InnoDB;
299
300
301-- -----------------------------------------------------
302-- Table `project_build`.`other_payment_approving_user`
303-- -----------------------------------------------------
304CREATE TABLE IF NOT EXISTS `project_build`.`other_payment_approving_user` (
305 `user_id` VARCHAR(255) NOT NULL,
306 `project_id` INT NOT NULL,
307 `request` TINYINT NOT NULL DEFAULT 0,
308 `approve` TINYINT NOT NULL DEFAULT 0,
309 `pay` TINYINT NOT NULL DEFAULT 0,
310 INDEX `fk_other_payment_approving_user_user1_idx` (`user_id` ASC),
311 INDEX `fk_other_payment_approving_user_project1_idx` (`project_id` ASC),
312 PRIMARY KEY (`user_id`, `project_id`),
313 CONSTRAINT `fk_other_payment_approving_user_user1`
314 FOREIGN KEY (`user_id`)
315 REFERENCES `project_build`.`user` (`id`)
316 ON DELETE RESTRICT
317 ON UPDATE CASCADE,
318 CONSTRAINT `fk_other_payment_approving_user_project1`
319 FOREIGN KEY (`project_id`)
320 REFERENCES `project_build`.`project` (`id`)
321 ON DELETE NO ACTION
322 ON UPDATE NO ACTION)
323ENGINE = InnoDB;
324
325
326-- -----------------------------------------------------
327-- Table `project_build`.`approval`
328-- -----------------------------------------------------
329CREATE TABLE IF NOT EXISTS `project_build`.`approval` (
330 `id` INT NOT NULL AUTO_INCREMENT,
331 `user_id` VARCHAR(255) NOT NULL,
332 `other_payment_transaction_id` INT NULL,
333 `material_transaction_id` INT NULL,
334 `approved` TINYINT NOT NULL,
335 `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
336 INDEX `fk_approval_user1_idx` (`user_id` ASC),
337 INDEX `fk_approval_other_payment_transaction1_idx` (`other_payment_transaction_id` ASC),
338 INDEX `fk_approval_material_transaction1_idx` (`material_transaction_id` ASC),
339 PRIMARY KEY (`id`),
340 CONSTRAINT `fk_approval_user1`
341 FOREIGN KEY (`user_id`)
342 REFERENCES `project_build`.`user` (`id`)
343 ON DELETE RESTRICT
344 ON UPDATE CASCADE,
345 CONSTRAINT `fk_approval_other_payment_transaction1`
346 FOREIGN KEY (`other_payment_transaction_id`)
347 REFERENCES `project_build`.`other_payment_transaction` (`id`)
348 ON DELETE CASCADE
349 ON UPDATE CASCADE,
350 CONSTRAINT `fk_approval_material_transaction1`
351 FOREIGN KEY (`material_transaction_id`)
352 REFERENCES `project_build`.`material_transaction` (`id`)
353 ON DELETE CASCADE
354 ON UPDATE CASCADE)
355ENGINE = InnoDB;
356
357
358-- -----------------------------------------------------
359-- Table `project_build`.`customer_has_project`
360-- -----------------------------------------------------
361CREATE TABLE IF NOT EXISTS `project_build`.`customer_has_project` (
362 `customer_id` INT NOT NULL,
363 `project_id` INT NOT NULL,
364 PRIMARY KEY (`customer_id`, `project_id`),
365 INDEX `fk_customer_has_project_project1_idx` (`project_id` ASC),
366 INDEX `fk_customer_has_project_customer1_idx` (`customer_id` ASC),
367 CONSTRAINT `fk_customer_has_project_customer1`
368 FOREIGN KEY (`customer_id`)
369 REFERENCES `project_build`.`customer` (`id`)
370 ON DELETE CASCADE
371 ON UPDATE CASCADE,
372 CONSTRAINT `fk_customer_has_project_project1`
373 FOREIGN KEY (`project_id`)
374 REFERENCES `project_build`.`project` (`id`)
375 ON DELETE NO ACTION
376 ON UPDATE NO ACTION)
377ENGINE = InnoDB;
378
379
380-- -----------------------------------------------------
381-- Table `project_build`.`vendor_has_inventory_item`
382-- -----------------------------------------------------
383CREATE TABLE IF NOT EXISTS `project_build`.`vendor_has_inventory_item` (
384 `vendor_id` INT NOT NULL,
385 `inventory_item_id` INT NOT NULL,
386 PRIMARY KEY (`vendor_id`, `inventory_item_id`),
387 INDEX `fk_vendor_has_inventory_item_inventory_item1_idx` (`inventory_item_id` ASC),
388 INDEX `fk_vendor_has_inventory_item_vendor1_idx` (`vendor_id` ASC),
389 CONSTRAINT `fk_vendor_has_inventory_item_vendor1`
390 FOREIGN KEY (`vendor_id`)
391 REFERENCES `project_build`.`vendor` (`id`)
392 ON DELETE CASCADE
393 ON UPDATE CASCADE,
394 CONSTRAINT `fk_vendor_has_inventory_item_inventory_item1`
395 FOREIGN KEY (`inventory_item_id`)
396 REFERENCES `project_build`.`inventory_item` (`id`)
397 ON DELETE CASCADE
398 ON UPDATE CASCADE)
399ENGINE = InnoDB;
400
401
402-- -----------------------------------------------------
403-- Table `project_build`.`customer_payment`
404-- -----------------------------------------------------
405CREATE TABLE IF NOT EXISTS `project_build`.`customer_payment` (
406 `id` INT NOT NULL,
407 `customer_id` INT NOT NULL,
408 `project_id` INT NOT NULL,
409 `ammount` DECIMAL(13,2) NOT NULL,
410 INDEX `fk_customer_payment_customer1_idx` (`customer_id` ASC),
411 INDEX `fk_customer_payment_project1_idx` (`project_id` ASC),
412 PRIMARY KEY (`id`),
413 CONSTRAINT `fk_customer_payment_customer1`
414 FOREIGN KEY (`customer_id`)
415 REFERENCES `project_build`.`customer` (`id`)
416 ON DELETE RESTRICT
417 ON UPDATE CASCADE,
418 CONSTRAINT `fk_customer_payment_project1`
419 FOREIGN KEY (`project_id`)
420 REFERENCES `project_build`.`project` (`id`)
421 ON DELETE NO ACTION
422 ON UPDATE NO ACTION)
423ENGINE = InnoDB;
424
425
426-- -----------------------------------------------------
427-- Table `project_build`.`access`
428-- -----------------------------------------------------
429CREATE TABLE IF NOT EXISTS `project_build`.`access` (
430 `id` INT NOT NULL,
431 `object` ENUM('project', 'project_team', 'project_inventory', 'project_budget', 'project_operation_hierachy', 'project_customer', 'inventory', 'inventory_item', 'inventory_stock', 'inventory_price', 'customer', 'customer_project', 'customer_payment', 'vendor', 'user', 'user_role') NOT NULL,
432 `create` TINYINT NOT NULL DEFAULT 0,
433 `read` TINYINT NOT NULL DEFAULT 0,
434 `update` TINYINT NOT NULL DEFAULT 0,
435 `delete` TINYINT NOT NULL DEFAULT 0,
436 `role_id` INT NOT NULL,
437 PRIMARY KEY (`id`),
438 INDEX `fk_access_role1_idx` (`role_id` ASC),
439 CONSTRAINT `fk_access_role1`
440 FOREIGN KEY (`role_id`)
441 REFERENCES `project_build`.`role` (`id`)
442 ON DELETE CASCADE
443 ON UPDATE CASCADE)
444ENGINE = InnoDB;
445
446
447-- -----------------------------------------------------
448-- Table `project_build`.`inventory_item_stock`
449-- -----------------------------------------------------
450CREATE TABLE IF NOT EXISTS `project_build`.`inventory_item_stock` (
451 `id` INT NOT NULL,
452 `inventory_item_id` INT NOT NULL,
453 `project_id` INT NULL,
454 `no_of_units` INT NOT NULL,
455 PRIMARY KEY (`id`),
456 INDEX `fk_inventory_item_stock_inventory_item1_idx` (`inventory_item_id` ASC),
457 INDEX `fk_inventory_item_stock_project1_idx` (`project_id` ASC),
458 CONSTRAINT `fk_inventory_item_stock_inventory_item1`
459 FOREIGN KEY (`inventory_item_id`)
460 REFERENCES `project_build`.`inventory_item` (`id`)
461 ON DELETE RESTRICT
462 ON UPDATE CASCADE,
463 CONSTRAINT `fk_inventory_item_stock_project1`
464 FOREIGN KEY (`project_id`)
465 REFERENCES `project_build`.`project` (`id`)
466 ON DELETE RESTRICT
467 ON UPDATE CASCADE)
468ENGINE = InnoDB;
469
470
471-- -----------------------------------------------------
472-- Table `project_build`.`inventory_item_stock_log`
473-- -----------------------------------------------------
474CREATE TABLE IF NOT EXISTS `project_build`.`inventory_item_stock_log` (
475 `id` INT NOT NULL,
476 `inventory_item_id` INT NOT NULL,
477 `from_project_id` INT NULL,
478 `to_project_id` INT NULL,
479 `no_of_units` INT NOT NULL,
480 `user_id` VARCHAR(255) NULL,
481 `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
482 PRIMARY KEY (`id`),
483 INDEX `fk_inventory_item_stock_log_user1_idx` (`user_id` ASC),
484 INDEX `fk_inventory_item_stock_log_project1_idx` (`to_project_id` ASC),
485 INDEX `fk_inventory_item_stock_log_project2_idx` (`from_project_id` ASC),
486 INDEX `fk_inventory_item_stock_log_inventory_item1_idx` (`inventory_item_id` ASC),
487 CONSTRAINT `fk_inventory_item_stock_log_user1`
488 FOREIGN KEY (`user_id`)
489 REFERENCES `project_build`.`user` (`id`)
490 ON DELETE RESTRICT
491 ON UPDATE CASCADE,
492 CONSTRAINT `fk_inventory_item_stock_log_project1`
493 FOREIGN KEY (`to_project_id`)
494 REFERENCES `project_build`.`project` (`id`)
495 ON DELETE RESTRICT
496 ON UPDATE CASCADE,
497 CONSTRAINT `fk_inventory_item_stock_log_project2`
498 FOREIGN KEY (`from_project_id`)
499 REFERENCES `project_build`.`project` (`id`)
500 ON DELETE RESTRICT
501 ON UPDATE CASCADE,
502 CONSTRAINT `fk_inventory_item_stock_log_inventory_item1`
503 FOREIGN KEY (`inventory_item_id`)
504 REFERENCES `project_build`.`inventory_item` (`id`)
505 ON DELETE RESTRICT
506 ON UPDATE CASCADE)
507ENGINE = InnoDB;
508
509
510-- -----------------------------------------------------
511-- Table `project_build`.`purchase_order`
512-- -----------------------------------------------------
513CREATE TABLE IF NOT EXISTS `project_build`.`purchase_order` (
514 `id` INT NOT NULL,
515 `vendor_id` INT NOT NULL,
516 `user_id` VARCHAR(255) NOT NULL,
517 `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
518 PRIMARY KEY (`id`),
519 INDEX `fk_purchase_order_vendor1_idx` (`vendor_id` ASC),
520 INDEX `fk_purchase_order_user1_idx` (`user_id` ASC),
521 CONSTRAINT `fk_purchase_order_vendor1`
522 FOREIGN KEY (`vendor_id`)
523 REFERENCES `project_build`.`vendor` (`id`)
524 ON DELETE RESTRICT
525 ON UPDATE CASCADE,
526 CONSTRAINT `fk_purchase_order_user1`
527 FOREIGN KEY (`user_id`)
528 REFERENCES `project_build`.`user` (`id`)
529 ON DELETE NO ACTION
530 ON UPDATE NO ACTION)
531ENGINE = InnoDB;
532
533
534-- -----------------------------------------------------
535-- Table `project_build`.`material_transaction_has_purchase_order`
536-- -----------------------------------------------------
537CREATE TABLE IF NOT EXISTS `project_build`.`material_transaction_has_purchase_order` (
538 `material_transaction_id` INT NOT NULL,
539 `purchase_order_id` INT NOT NULL,
540 PRIMARY KEY (`material_transaction_id`, `purchase_order_id`),
541 INDEX `fk_material_transaction_has_purchase_order_purchase_order1_idx` (`purchase_order_id` ASC),
542 INDEX `fk_material_transaction_has_purchase_order_material_transac_idx` (`material_transaction_id` ASC),
543 CONSTRAINT `fk_material_transaction_has_purchase_order_material_transacti1`
544 FOREIGN KEY (`material_transaction_id`)
545 REFERENCES `project_build`.`material_transaction` (`id`)
546 ON DELETE NO ACTION
547 ON UPDATE NO ACTION,
548 CONSTRAINT `fk_material_transaction_has_purchase_order_purchase_order1`
549 FOREIGN KEY (`purchase_order_id`)
550 REFERENCES `project_build`.`purchase_order` (`id`)
551 ON DELETE NO ACTION
552 ON UPDATE NO ACTION)
553ENGINE = InnoDB;
554
555
556-- -----------------------------------------------------
557-- Table `project_build`.`payments`
558-- -----------------------------------------------------
559CREATE TABLE IF NOT EXISTS `project_build`.`payments` (
560 `id` INT NOT NULL,
561 `purchase_order_id` INT NOT NULL,
562 `user_id` VARCHAR(255) NOT NULL,
563 `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
564 INDEX `fk_payments_purchase_order1_idx` (`purchase_order_id` ASC),
565 PRIMARY KEY (`id`),
566 INDEX `fk_payments_user1_idx` (`user_id` ASC),
567 CONSTRAINT `fk_payments_purchase_order1`
568 FOREIGN KEY (`purchase_order_id`)
569 REFERENCES `project_build`.`purchase_order` (`id`)
570 ON DELETE NO ACTION
571 ON UPDATE NO ACTION,
572 CONSTRAINT `fk_payments_user1`
573 FOREIGN KEY (`user_id`)
574 REFERENCES `project_build`.`user` (`id`)
575 ON DELETE NO ACTION
576 ON UPDATE NO ACTION)
577ENGINE = InnoDB;
578
579
580-- -----------------------------------------------------
581-- Table `project_build`.`material_transaction_has_payments`
582-- -----------------------------------------------------
583CREATE TABLE IF NOT EXISTS `project_build`.`material_transaction_has_payments` (
584 `material_transaction_id` INT NOT NULL,
585 `payments_id` INT NOT NULL,
586 PRIMARY KEY (`material_transaction_id`, `payments_id`),
587 INDEX `fk_material_transaction_has_payments_payments1_idx` (`payments_id` ASC),
588 INDEX `fk_material_transaction_has_payments_material_transaction1_idx` (`material_transaction_id` ASC),
589 CONSTRAINT `fk_material_transaction_has_payments_material_transaction1`
590 FOREIGN KEY (`material_transaction_id`)
591 REFERENCES `project_build`.`material_transaction` (`id`)
592 ON DELETE NO ACTION
593 ON UPDATE NO ACTION,
594 CONSTRAINT `fk_material_transaction_has_payments_payments1`
595 FOREIGN KEY (`payments_id`)
596 REFERENCES `project_build`.`payments` (`id`)
597 ON DELETE NO ACTION
598 ON UPDATE NO ACTION)
599ENGINE = InnoDB;
600
601USE `project_build` ;
602
603-- -----------------------------------------------------
604-- procedure insert_project
605-- -----------------------------------------------------
606
607DELIMITER $$
608USE `project_build`$$
609CREATE PROCEDURE `insert_project` ()
610BEGIN
611END$$
612
613DELIMITER ;
614
615-- -----------------------------------------------------
616-- procedure delete_team_member
617-- -----------------------------------------------------
618
619DELIMITER $$
620USE `project_build`$$
621CREATE PROCEDURE `delete_team_member` (IN use_id VARCHAR(255), IN project_id INT)
622BEGIN
623
624IF( 1 < (SELECT COUNT(user_id) FROM `team_member` WHERE `project_id`=project_id AND `user_id`=user_id) ) THEN
625 DELETE FROM `team_member` WHERE `project_id`=project_id AND `user_id`=user_id;
626 SELECT 1;
627ELSE
628 SELECT 0;
629END IF;
630
631END$$
632
633DELIMITER ;
634
635-- -----------------------------------------------------
636-- procedure get_budget_entry_material_summary
637-- -----------------------------------------------------
638
639DELIMITER $$
640USE `project_build`$$
641CREATE PROCEDURE `get_budget_entry_material_summary` (IN budget_entry_material_id INT, IN item_id INT, IN project_id INT)
642BEGIN
643DECLARE pending INT;
644DECLARE used INT;
645DECLARE in_project_stock INT;
646DECLARE in_main_stock INT;
647DECLARE total_spend INT;
648
649SET pending = (SELECT COUNT(`id`) FROM `material_transaction` WHERE `budget_entry_material_id`=budget_entry_material_id AND NOT (`state` = 'paid' OR `state` = 'transfered' OR `state` = 'denied' OR `state` = 'splitted'));
650SET total_spend = (SELECT COUNT(`id`) FROM `material_transaction` WHERE `budget_entry_material_id`=budget_entry_material_id AND (`state` = 'paid' OR `state` = 'transfered'));
651SET in_project_stock = (SELECT `no_of_units` FROM `inventory_item_stock` WHERE `inventory_item_id` = item_id OR `project_id` = project_id );
652SET in_main_stock = (SELECT `no_of_units` FROM `inventory_item_stock` WHERE `inventory_item_id` = item_id OR `project_id` = 1 );
653SET used = total_spend - in_project_stock;
654
655SELECT pending AS 'pending', used AS 'used', in_project_stock AS 'in_project_stock', in_main_stock AS 'in_main_stock';
656END$$
657
658DELIMITER ;
659
660-- -----------------------------------------------------
661-- procedure inventory_TRANSFER
662-- -----------------------------------------------------
663
664DELIMITER $$
665USE `project_build`$$
666CREATE PROCEDURE `inventory_TRANSFER` (IN item_id INT, IN project_id_from INT, IN project_id_to INT, IN ammount INT, IN user_id VARCHAR(255))
667BEGIN
668
669DECLARE from_stock_ammount INT;
670DECLARE to_stock_ammount INT;
671
672SET from_stock_ammount = (SELECT `no_of_units` from `inventory_item_stock` where `item_id` = item_id AND `project_id` = projfrct_id_from);
673SET to_stock_ammount = (SELECT `no_of_units` from `inventory_item_stock` where `item_id` = item_id AND `project_id` = project_id_to);
674
675START TRANSACTION;
676SET autocommit = 0;
677
678IF ( from_stock_ammount IS NOT NULL AND from_stock_ammount >= ammount) THEN
679
680 SET from_stock_ammount = from_stock_ammount - ammount;
681 UPDATE `inventory_item_stock` SET `no_of_units` = from_stock_ammount WHERE `item_id` = item_id AND `project_id` = projfrct_id_from;
682 INSERT INTO `inventory_item_stock_log` (`inventory_item_id`,`from_project_id`,`to_project_id`,`no_of_units`,`user_id`) VALUES(item_id, project_id_from, project_id_to,ammount,user_id);
683
684 IF(to_stock_ammount IS NULL) THEN
685
686 INSERT INTO `inventory_item_stock` (`inventory_item_id`,`project_id`,`no_of_units`) VALUES(item_id, project_id_to, ammount);
687 SELECT 1;
688
689 ELSE
690
691 SET to_stock_ammount = to_stock_ammount + ammount;
692 UPDATE `inventory_item_stock` SET `no_of_units` = to_stock_ammount WHERE `item_id` = item_id AND `project_id` = project_id_to;
693 SELECT 1;
694
695 END IF;
696ELSE
697
698 SELECT 0;
699 ROLLBACK;
700
701END IF;
702
703SET autocommit = 1;
704COMMIT;
705
706END$$
707
708DELIMITER ;
709
710-- -----------------------------------------------------
711-- procedure inventory_IN
712-- -----------------------------------------------------
713
714DELIMITER $$
715USE `project_build`$$
716CREATE PROCEDURE `inventory_IN` (IN item_id INT, IN project_id INT, IN ammount INT, IN user_id VARCHAR(255))
717BEGIN
718
719DECLARE old_stock_ammount INT;
720
721START TRANSACTION;
722SET autocommit = 0;
723
724SET old_stock_ammount = (SELECT `no_of_units` from `inventory_item_stock` where `item_id` = item_id AND `project_id` = project_id);
725INSERT INTO `inventory_item_stock_log` (`inventory_item_id`, `to_project_id`, `no_of_units`, `user_id`) VALUES(item_id, project_id, ammount, user_id);
726
727IF(old_stock_ammount IS NULL) THEN
728
729 INSERT INTO `inventory_item_stock` (`inventory_item_id`, `project_id`, `no_of_units`) VALUES(item_id, project_id, ammount);
730
731ELSE
732
733 SET old_stock_ammount = old_stock_ammount + ammount;
734 UPDATE `inventory_item_stock` SET `no_of_units` = old_stock_ammount WHERE `item_id` = item_id AND `project_id` = project_id;
735
736END IF;
737
738SELECT 1;
739
740SET autocommit = 1;
741COMMIT;
742
743END$$
744
745DELIMITER ;
746
747-- -----------------------------------------------------
748-- procedure inventory_OUT
749-- -----------------------------------------------------
750
751DELIMITER $$
752USE `project_build`$$
753CREATE PROCEDURE `inventory_OUT` (IN item_id INT, IN project_id INT, IN ammount INT, IN user_id VARCHAR(255))
754BEGIN
755
756DECLARE old_stock_ammount INT;
757
758START TRANSACTION;
759SET autocommit = 0;
760
761SET old_stock_ammount = (SELECT `no_of_units` from `inventory_item_stock` where `item_id` = item_id AND `project_id` = project_id);
762INSERT INTO `inventory_item_stock_log` (`inventory_item_id`, `from_project_id`, `no_of_units`, `user_id`) VALUES(item_id, project_id, ammount, user_id);
763
764IF(old_stock_ammount IS NULL) THEN
765
766 INSERT INTO `inventory_item_stock` (`inventory_item_id`, `project_id`, `no_of_units`) VALUES(item_id, project_id, ammount);
767
768ELSE
769
770 SET old_stock_ammount = old_stock_ammount + ammount;
771 UPDATE `inventory_item_stock` SET `no_of_units` = old_stock_ammount WHERE `item_id` = item_id AND `project_id` = project_id;
772
773END IF;
774
775SELECT 1;
776
777SET autocommit = 1;
778COMMIT;
779
780END$$
781
782DELIMITER ;
783
784-- -----------------------------------------------------
785-- procedure get_budget_entry_other_payment_summary
786-- -----------------------------------------------------
787
788DELIMITER $$
789USE `project_build`$$
790CREATE PROCEDURE `get_budget_entry_other_payment_summary` (IN budget_entry_other_id INT)
791BEGIN
792DECLARE pending INT;
793DECLARE total_spend INT;
794
795SET pending = (SELECT COUNT(`id`) FROM `other_payment_transaction` WHERE `budget_entry_other_id`=budget_entry_other_id AND NOT (`state` = 'paid' OR `state` = 'denied' ));
796SET total_spend = (SELECT COUNT(`id`) FROM `other_payment_transaction` WHERE `budget_entry_other_id`=budget_entry_other_id AND (`state` = 'paid' ));
797
798SELECT pending AS 'pending', total_spend AS 'total_spend';
799END$$
800
801DELIMITER ;
802
803-- -----------------------------------------------------
804-- procedure split_approve_material_transaction
805-- -----------------------------------------------------
806
807DELIMITER $$
808USE `project_build`$$
809CREATE PROCEDURE `split_approve_material_transaction` (IN user_id VARCHAR(255), IN transaction_id INT, IN transfer_ammount INT)
810BEGIN
811
812DECLARE new_ammount INT;
813DECLARE budget_entry INT;
814
815START TRANSACTION;
816SET autocommit = 0;
817
818SET new_ammount = (SELECT `no_of_units` FROM `material_transaction` WHERE `id` = transaction_id);
819SET new_ammount = new_ammount - transfer_ammount;
820SET budget_entry = (SELECT `budget_entry_material_id` FROM `material_transaction` WHERE `id` = transaction_id);
821
822INSERT INTO `material_transaction` (`budget_entry_material_id`,`no_of_units`,`state`,`parent_transaction_id`) VALUES(budget_entry, transfer_ammount, 'to_be_transfered', transaction_id);
823INSERT INTO `material_transaction` (`budget_entry_material_id`,`no_of_units`,`state`,`parent_transaction_id`) VALUES(budget_entry, new_ammount, 'to_be_purchased', transaction_id);
824UPDATE `material_transaction` SET `state` = 'splitted' WHERE `id` = transaction_id;
825INSERT INTO `approval` (`user_id`,`material_transaction_id`,`approved`) VALUES(user_id, transaction_id, 1);
826
827SET autocommit = 1;
828COMMIT;
829
830END$$
831
832DELIMITER ;
833
834-- -----------------------------------------------------
835-- procedure approve_material_transaction
836-- -----------------------------------------------------
837
838DELIMITER $$
839USE `project_build`$$
840CREATE PROCEDURE `approve_material_transaction` (IN user_id VARCHAR(255), IN transaction_id INT)
841BEGIN
842
843START TRANSACTION;
844SET autocommit = 0;
845
846INSERT INTO `approval` (`user_id`,`material_transaction_id`,`approved`) VALUES(user_id, transaction_id, 1);
847UPDATE `material_transaction` SET `state` = 'to_be_purchased' WHERE `id` = transaction_id;
848
849SET autocommit = 1;
850COMMIT;
851
852END$$
853
854DELIMITER ;
855
856-- -----------------------------------------------------
857-- procedure approve_other_payment_transaction
858-- -----------------------------------------------------
859
860DELIMITER $$
861USE `project_build`$$
862CREATE PROCEDURE `approve_other_payment_transaction` (IN user_id VARCHAR(255), IN transaction_id INT)
863BEGIN
864
865START TRANSACTION;
866SET autocommit = 0;
867
868INSERT INTO `approval` (`user_id`,`other_payment_transaction_id`,`approved`) VALUES(user_id, transaction_id, 1);
869UPDATE `other_payment_transaction` SET `state` = 'to_be_paid' WHERE `id` = transaction_id;
870
871SET autocommit = 1;
872COMMIT;
873
874END$$
875
876DELIMITER ;
877
878SET SQL_MODE=@OLD_SQL_MODE;
879SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
880SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
881
882-- -----------------------------------------------------
883-- Data for table `project_build`.`role`
884-- -----------------------------------------------------
885START TRANSACTION;
886USE `project_build`;
887INSERT INTO `project_build`.`role` (`id`, `name`, `active`) VALUES (1, 'purchasing officer', DEFAULT);
888INSERT INTO `project_build`.`role` (`id`, `name`, `active`) VALUES (2, 'project manager', DEFAULT);
889INSERT INTO `project_build`.`role` (`id`, `name`, `active`) VALUES (3, 'technical officer', DEFAULT);
890INSERT INTO `project_build`.`role` (`id`, `name`, `active`) VALUES (4, 'quantity servayor', DEFAULT);
891INSERT INTO `project_build`.`role` (`id`, `name`, `active`) VALUES (5, 'ceo', DEFAULT);
892INSERT INTO `project_build`.`role` (`id`, `name`, `active`) VALUES (6, 'admin', DEFAULT);
893
894COMMIT;
895
896
897-- -----------------------------------------------------
898-- Data for table `project_build`.`user`
899-- -----------------------------------------------------
900START TRANSACTION;
901USE `project_build`;
902INSERT INTO `project_build`.`user` (`id`, `active`, `role_id`, `password_hash`, `password_salt`, `name`) VALUES ('user1@user', 1, 6, '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8', '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8', 'Rick Sanchez');
903INSERT INTO `project_build`.`user` (`id`, `active`, `role_id`, `password_hash`, `password_salt`, `name`) VALUES ('user2@user', 1, 2, '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8', '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8', 'Mortey Sanchez');
904INSERT INTO `project_build`.`user` (`id`, `active`, `role_id`, `password_hash`, `password_salt`, `name`) VALUES ('user3@user', 1, 3, '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8', '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8', 'Tim Coock');
905INSERT INTO `project_build`.`user` (`id`, `active`, `role_id`, `password_hash`, `password_salt`, `name`) VALUES ('user4@user', 1, 4, '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8', '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8', 'Jhon Brown');
906INSERT INTO `project_build`.`user` (`id`, `active`, `role_id`, `password_hash`, `password_salt`, `name`) VALUES ('user5@user', 1, 5, '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8', '5e884898da28047151d0e56f8dc6292773603d0d6aabbdd62a11ef721d1542d8', 'Tuan Amith');
907
908COMMIT;
909
910
911-- -----------------------------------------------------
912-- Data for table `project_build`.`project`
913-- -----------------------------------------------------
914START TRANSACTION;
915USE `project_build`;
916INSERT INTO `project_build`.`project` (`id`, `name`, `address`, `start_date`, `end_date`, `active`) VALUES (2, 'Kandy Project', '43D, Kandy road, Kandy', '2015-08-21', '2017-12-30', 1);
917INSERT INTO `project_build`.`project` (`id`, `name`, `address`, `start_date`, `end_date`, `active`) VALUES (3, 'Nelna Project', '1001, Pththalam road, Puththalama', '2016-09-12', '2018-08-17', 1);
918INSERT INTO `project_build`.`project` (`id`, `name`, `address`, `start_date`, `end_date`, `active`) VALUES (1, 'MAIN', 'MAIN', NULL, NULL, DEFAULT);
919
920COMMIT;
921
922
923-- -----------------------------------------------------
924-- Data for table `project_build`.`inventory_item`
925-- -----------------------------------------------------
926START TRANSACTION;
927USE `project_build`;
928INSERT INTO `project_build`.`inventory_item` (`id`, `name`, `unit`) VALUES (1, 'item 1', 'kg');
929INSERT INTO `project_build`.`inventory_item` (`id`, `name`, `unit`) VALUES (2, 'item 2', 'g');
930INSERT INTO `project_build`.`inventory_item` (`id`, `name`, `unit`) VALUES (3, 'item 3', 'units');
931INSERT INTO `project_build`.`inventory_item` (`id`, `name`, `unit`) VALUES (4, 'item 4', 'cubes');
932INSERT INTO `project_build`.`inventory_item` (`id`, `name`, `unit`) VALUES (5, 'item 5', 'units');
933
934COMMIT;
935
936
937-- -----------------------------------------------------
938-- Data for table `project_build`.`inventory_item_price_list`
939-- -----------------------------------------------------
940START TRANSACTION;
941USE `project_build`;
942INSERT INTO `project_build`.`inventory_item_price_list` (`id`, `name`) VALUES (1, 'default price');
943INSERT INTO `project_build`.`inventory_item_price_list` (`id`, `name`) VALUES (2, 'sales price');
944
945COMMIT;
946
947
948-- -----------------------------------------------------
949-- Data for table `project_build`.`inventory_item_price`
950-- -----------------------------------------------------
951START TRANSACTION;
952USE `project_build`;
953INSERT INTO `project_build`.`inventory_item_price` (`inventory_item_price_list_id`, `inventory_item_id`, `price`) VALUES (1, 1, 200.00);
954INSERT INTO `project_build`.`inventory_item_price` (`inventory_item_price_list_id`, `inventory_item_id`, `price`) VALUES (1, 2, 350.00);
955INSERT INTO `project_build`.`inventory_item_price` (`inventory_item_price_list_id`, `inventory_item_id`, `price`) VALUES (1, 3, 500.00);
956INSERT INTO `project_build`.`inventory_item_price` (`inventory_item_price_list_id`, `inventory_item_id`, `price`) VALUES (1, 4, 700.00);
957INSERT INTO `project_build`.`inventory_item_price` (`inventory_item_price_list_id`, `inventory_item_id`, `price`) VALUES (1, 5, 900.00);
958INSERT INTO `project_build`.`inventory_item_price` (`inventory_item_price_list_id`, `inventory_item_id`, `price`) VALUES (2, 1, 300.00);
959INSERT INTO `project_build`.`inventory_item_price` (`inventory_item_price_list_id`, `inventory_item_id`, `price`) VALUES (2, 2, 400.50);
960INSERT INTO `project_build`.`inventory_item_price` (`inventory_item_price_list_id`, `inventory_item_id`, `price`) VALUES (2, 3, 512.50);
961
962COMMIT;
963
964
965-- -----------------------------------------------------
966-- Data for table `project_build`.`team_member`
967-- -----------------------------------------------------
968START TRANSACTION;
969USE `project_build`;
970INSERT INTO `project_build`.`team_member` (`user_id`, `project_id`) VALUES ('user1@user', 1);
971INSERT INTO `project_build`.`team_member` (`user_id`, `project_id`) VALUES ('user2@user', 1);
972INSERT INTO `project_build`.`team_member` (`user_id`, `project_id`) VALUES ('user3@user', 1);
973
974COMMIT;
975
976
977-- -----------------------------------------------------
978-- Data for table `project_build`.`budget_stage`
979-- -----------------------------------------------------
980START TRANSACTION;
981USE `project_build`;
982INSERT INTO `project_build`.`budget_stage` (`id`, `project_id`, `name`) VALUES (1, 1, 'stage 1');
983INSERT INTO `project_build`.`budget_stage` (`id`, `project_id`, `name`) VALUES (2, 1, 'stage 2');
984INSERT INTO `project_build`.`budget_stage` (`id`, `project_id`, `name`) VALUES (3, 1, 'stage 3');
985INSERT INTO `project_build`.`budget_stage` (`id`, `project_id`, `name`) VALUES (4, 1, 'stage 4');
986INSERT INTO `project_build`.`budget_stage` (`id`, `project_id`, `name`) VALUES (5, 1, 'stage 5');
987
988COMMIT;
989
990
991-- -----------------------------------------------------
992-- Data for table `project_build`.`budget_entry_material`
993-- -----------------------------------------------------
994START TRANSACTION;
995USE `project_build`;
996INSERT INTO `project_build`.`budget_entry_material` (`id`, `budget_stage_id`, `inventory_item_id`, `no_of_units`, `over_budget_limit`, `inventory_item_price_list_id`) VALUES (1, 1, 1, 300, 50, 1);
997INSERT INTO `project_build`.`budget_entry_material` (`id`, `budget_stage_id`, `inventory_item_id`, `no_of_units`, `over_budget_limit`, `inventory_item_price_list_id`) VALUES (2, 1, 2, 200, 10, 1);
998INSERT INTO `project_build`.`budget_entry_material` (`id`, `budget_stage_id`, `inventory_item_id`, `no_of_units`, `over_budget_limit`, `inventory_item_price_list_id`) VALUES (3, 1, 3, 500, 5, 1);
999INSERT INTO `project_build`.`budget_entry_material` (`id`, `budget_stage_id`, `inventory_item_id`, `no_of_units`, `over_budget_limit`, `inventory_item_price_list_id`) VALUES (4, 1, 4, 1000, 200, 1);
1000INSERT INTO `project_build`.`budget_entry_material` (`id`, `budget_stage_id`, `inventory_item_id`, `no_of_units`, `over_budget_limit`, `inventory_item_price_list_id`) VALUES (5, 1, 5, 2000, 14, 1);
1001INSERT INTO `project_build`.`budget_entry_material` (`id`, `budget_stage_id`, `inventory_item_id`, `no_of_units`, `over_budget_limit`, `inventory_item_price_list_id`) VALUES (6, 2, 1, 70, 50, 1);
1002INSERT INTO `project_build`.`budget_entry_material` (`id`, `budget_stage_id`, `inventory_item_id`, `no_of_units`, `over_budget_limit`, `inventory_item_price_list_id`) VALUES (7, 2, 2, 5000, 300, 1);
1003
1004COMMIT;
1005
1006
1007-- -----------------------------------------------------
1008-- Data for table `project_build`.`budget_entry_other`
1009-- -----------------------------------------------------
1010START TRANSACTION;
1011USE `project_build`;
1012INSERT INTO `project_build`.`budget_entry_other` (`id`, `budget_stage_id`, `ammount`, `over_budget_limit`, `name`) VALUES (1, 1, 90000.00, 30000.00, 'labour payments');
1013INSERT INTO `project_build`.`budget_entry_other` (`id`, `budget_stage_id`, `ammount`, `over_budget_limit`, `name`) VALUES (2, 1, 100000.00, 5000.00, 'cleaning payments');
1014INSERT INTO `project_build`.`budget_entry_other` (`id`, `budget_stage_id`, `ammount`, `over_budget_limit`, `name`) VALUES (3, 1, 300000.00, 12000.00, 'advertising payments');
1015INSERT INTO `project_build`.`budget_entry_other` (`id`, `budget_stage_id`, `ammount`, `over_budget_limit`, `name`) VALUES (4, 2, 26000.00, 2000.00, 'transportation paymets');
1016INSERT INTO `project_build`.`budget_entry_other` (`id`, `budget_stage_id`, `ammount`, `over_budget_limit`, `name`) VALUES (5, 2, 56000.00, 0, 'utility payments');
1017
1018COMMIT;
1019
1020
1021-- -----------------------------------------------------
1022-- Data for table `project_build`.`material_transaction`
1023-- -----------------------------------------------------
1024START TRANSACTION;
1025USE `project_build`;
1026INSERT INTO `project_build`.`material_transaction` (`id`, `budget_entry_material_id`, `no_of_units`, `ammount`, `state`, `parent_transaction_id`, `time`) VALUES (1, 1, 50, NULL, 'to_be_approved', NULL, DEFAULT);
1027
1028COMMIT;
1029
1030
1031-- -----------------------------------------------------
1032-- Data for table `project_build`.`material_transaction_approving_user`
1033-- -----------------------------------------------------
1034START TRANSACTION;
1035USE `project_build`;
1036INSERT INTO `project_build`.`material_transaction_approving_user` (`user_id`, `project_id`, `request`, `approve`, `order`, `recive`, `pay`) VALUES ('user1@user', 1, 1, 1, 1, 1, 1);
1037
1038COMMIT;
1039
1040
1041-- -----------------------------------------------------
1042-- Data for table `project_build`.`other_payment_approving_user`
1043-- -----------------------------------------------------
1044START TRANSACTION;
1045USE `project_build`;
1046INSERT INTO `project_build`.`other_payment_approving_user` (`user_id`, `project_id`, `request`, `approve`, `pay`) VALUES ('user1@user', 1, 1, 1, 1);
1047
1048COMMIT;
1049
1050
1051-- -----------------------------------------------------
1052-- Data for table `project_build`.`access`
1053-- -----------------------------------------------------
1054START TRANSACTION;
1055USE `project_build`;
1056INSERT INTO `project_build`.`access` (`id`, `object`, `create`, `read`, `update`, `delete`, `role_id`) VALUES (1, 'project', 1, 1, 1, 1, 6);
1057INSERT INTO `project_build`.`access` (`id`, `object`, `create`, `read`, `update`, `delete`, `role_id`) VALUES (2, 'inventory', 1, 1, 1, 1, 6);
1058INSERT INTO `project_build`.`access` (`id`, `object`, `create`, `read`, `update`, `delete`, `role_id`) VALUES (3, 'customer', 1, 1, 1, 1, 6);
1059INSERT INTO `project_build`.`access` (`id`, `object`, `create`, `read`, `update`, `delete`, `role_id`) VALUES (4, 'vendor', 1, 1, 1, 1, 6);
1060INSERT INTO `project_build`.`access` (`id`, `object`, `create`, `read`, `update`, `delete`, `role_id`) VALUES (5, 'user', 1, 1, 1, 1, 6);
1061INSERT INTO `project_build`.`access` (`id`, `object`, `create`, `read`, `update`, `delete`, `role_id`) VALUES (6, 'project_team', 1, 1, 1, 1, 6);
1062INSERT INTO `project_build`.`access` (`id`, `object`, `create`, `read`, `update`, `delete`, `role_id`) VALUES (7, 'project_inventory', 1, 1, 1, 1, 6);
1063INSERT INTO `project_build`.`access` (`id`, `object`, `create`, `read`, `update`, `delete`, `role_id`) VALUES (8, 'project_budget', 1, 1, 1, 1, 6);
1064INSERT INTO `project_build`.`access` (`id`, `object`, `create`, `read`, `update`, `delete`, `role_id`) VALUES (9, 'project_operation_hierachy', 1, 1, 1, 1, 6);
1065INSERT INTO `project_build`.`access` (`id`, `object`, `create`, `read`, `update`, `delete`, `role_id`) VALUES (10, 'project_customer', 1, 1, 1, 1, 6);
1066INSERT INTO `project_build`.`access` (`id`, `object`, `create`, `read`, `update`, `delete`, `role_id`) VALUES (11, 'inventory_item', 1, 1, 1, 1, 6);
1067INSERT INTO `project_build`.`access` (`id`, `object`, `create`, `read`, `update`, `delete`, `role_id`) VALUES (12, 'inventory_stock', 1, 1, 1, 1, 6);
1068INSERT INTO `project_build`.`access` (`id`, `object`, `create`, `read`, `update`, `delete`, `role_id`) VALUES (13, 'inventory_price', 1, 1, 1, 1, 6);
1069INSERT INTO `project_build`.`access` (`id`, `object`, `create`, `read`, `update`, `delete`, `role_id`) VALUES (14, 'customer_project', 1, 1, 1, 1, 6);
1070INSERT INTO `project_build`.`access` (`id`, `object`, `create`, `read`, `update`, `delete`, `role_id`) VALUES (15, 'customer_payment', 1, 1, 1, 1, 6);
1071INSERT INTO `project_build`.`access` (`id`, `object`, `create`, `read`, `update`, `delete`, `role_id`) VALUES (16, 'user_role', 1, 1, 1, 1, 6);
1072
1073COMMIT;
1074
1075
1076-- -----------------------------------------------------
1077-- Data for table `project_build`.`inventory_item_stock`
1078-- -----------------------------------------------------
1079START TRANSACTION;
1080USE `project_build`;
1081INSERT INTO `project_build`.`inventory_item_stock` (`id`, `inventory_item_id`, `project_id`, `no_of_units`) VALUES (1, 1, 1, 30);
1082INSERT INTO `project_build`.`inventory_item_stock` (`id`, `inventory_item_id`, `project_id`, `no_of_units`) VALUES (2, 1, 2, 20);
1083
1084COMMIT;