· 6 years ago · Jun 13, 2019, 06:24 PM
1/*
2Ticket System Database
3v0.1
4*/
5DROP DATABASE IF EXISTS dbTisy;
6
7CREATE DATABASE dbTisy;
8
9use dbTisy;
10
11
12CREATE TABLE IF NOT EXISTS tblLanguage(
13
14 idLanguage VARCHAR(2) PRIMARY KEY,
15 dtLangName VARCHAR(64),
16
17 CONSTRAINT ui_LangName
18 UNIQUE INDEX idx_LangName(dtLangName)
19
20) ENGINE = INNODB;
21
22CREATE TABLE IF NOT EXISTS tblPerson(
23
24 idPerson MEDIUMINT UNSIGNED AUTO_INCREMENT,
25 dtName VARCHAR(64) NOT NULL,
26 dtLastName VARCHAR(64) NOT NULL,
27 dtAddress VARCHAR(128) NOT NULL,
28 dtPhoneNumber VARCHAR(64) NOT NULL UNIQUE,
29 dtEmail VARCHAR(254) NOT NULL UNIQUE,
30 fiPrefLanguage VARCHAR(2),
31 dtPassword VARCHAR(255) NOT NULL,
32
33 CONSTRAINT pk_Person
34 PRIMARY KEY (idPerson),
35
36 CONSTRAINT fk_PrefLanguage
37 FOREIGN KEY (fiPrefLanguage) REFERENCES tblLanguage(idLanguage)
38 ON UPDATE CASCADE
39 ON DELETE CASCADE
40
41) ENGINE = INNODB;
42
43
44CREATE TABLE IF NOT EXISTS tblCustomer(
45
46 idCustomer MEDIUMINT UNSIGNED AUTO_INCREMENT,
47 fiPerson MEDIUMINT UNSIGNED NOT NULL UNIQUE,
48
49 CONSTRAINT fk_Customer
50 FOREIGN KEY (fiPerson) REFERENCES tblPerson(idPerson)
51 ON UPDATE CASCADE
52 ON DELETE CASCADE,
53
54 CONSTRAINT pk_Customer
55 PRIMARY KEY(idCustomer)
56
57) ENGINE = INNODB;
58
59CREATE TABLE IF NOT EXISTS tblTechnician(
60
61 idTechnician MEDIUMINT UNSIGNED AUTO_INCREMENT,
62 fiPerson MEDIUMINT UNSIGNED NOT NULL,
63
64 CONSTRAINT pk_Technician
65 PRIMARY KEY(idTechnician, fiPerson),
66
67 CONSTRAINT fk_Technician
68 FOREIGN KEY (fiPerson) REFERENCES tblPerson(idPerson)
69 ON UPDATE CASCADE
70 ON DELETE CASCADE
71
72) ENGINE = INNODB;
73
74CREATE TABLE IF NOT EXISTS tblHelpdesk(
75
76 idHelpdesk MEDIUMINT UNSIGNED AUTO_INCREMENT,
77 fiPerson MEDIUMINT UNSIGNED NOT NULL,
78
79 CONSTRAINT pk_Helpdesk
80 PRIMARY KEY(idHelpdesk, fiPerson),
81
82 CONSTRAINT fk_Helpdesk
83 FOREIGN KEY (fiPerson) REFERENCES tblPerson(idPerson)
84 ON UPDATE CASCADE
85 ON DELETE CASCADE
86
87) ENGINE = INNODB;
88
89CREATE TABLE IF NOT EXISTS tblCategory(
90
91 idCategory TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
92 dtDescription VARCHAR(128) NOT NULL
93
94) ENGINE = INNODB;
95
96CREATE TABLE IF NOT EXISTS tblTicket(
97
98 idTicket MEDIUMINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
99 dtTitle VARCHAR(128) NOT NULL,
100 dtTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
101 dtState TINYINT UNSIGNED NOT NULL,
102 fiPerson MEDIUMINT UNSIGNED NOT NULL,
103 fiCategory TINYINT UNSIGNED NOT NULL,
104
105 CONSTRAINT fk_Ticket
106 FOREIGN KEY (fiPerson) REFERENCES tblPerson(idPerson)
107 ON UPDATE CASCADE
108 ON DELETE CASCADE,
109
110 CONSTRAINT fk_Category
111 FOREIGN KEY (fiCategory) REFERENCES tblCategory(idCategory)
112 ON UPDATE CASCADE
113 ON DELETE CASCADE
114
115) ENGINE = INNODB;
116
117
118CREATE TABLE IF NOT EXISTS tblTicketTranslation(
119
120 fiTicket MEDIUMINT UNSIGNED,
121 fiLanguage VARCHAR(8),
122 dtDescription VARCHAR(1024),
123
124 PRIMARY KEY(fiTicket, fiLanguage),
125
126 CONSTRAINT fk_TicketTranslation
127 FOREIGN KEY (fiTicket) REFERENCES tblTicket(idTicket)
128 ON UPDATE CASCADE
129 ON DELETE CASCADE,
130
131 CONSTRAINT fk_LanguageTicketTranslation
132 FOREIGN KEY (fiLanguage) REFERENCES tblLanguage(idLanguage)
133 ON UPDATE CASCADE
134 ON DELETE CASCADE
135
136) ENGINE = INNODB;
137
138CREATE TABLE IF NOT EXISTS tblPost(
139
140 idPost MEDIUMINT UNSIGNED AUTO_INCREMENT,
141 fiTicket MEDIUMINT UNSIGNED,
142 fiPerson MEDIUMINT UNSIGNED NOT NULL,
143 dtTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
144
145 PRIMARY KEY (idPost, fiTicket, fiPerson),
146
147 CONSTRAINT fk_TicketPost
148 FOREIGN KEY (fiTicket) REFERENCES tblTicket(idTicket)
149 ON UPDATE CASCADE
150 ON DELETE CASCADE,
151
152 CONSTRAINT fk_PersonPost
153 FOREIGN KEY (fiPerson) REFERENCES tblPerson(idPerson)
154 ON UPDATE CASCADE
155 ON DELETE CASCADE
156
157) ENGINE = INNODB;
158
159CREATE TABLE IF NOT EXISTS tblPostTranslation(
160
161 fiPost MEDIUMINT UNSIGNED,
162 fiLanguage VARCHAR(8),
163 dtDescription VARCHAR(1024),
164
165 PRIMARY KEY (fiPost, fiLanguage),
166
167 CONSTRAINT fk_PostTranslation
168 FOREIGN KEY (fiPost) REFERENCES tblPost(idPost)
169 ON UPDATE CASCADE
170 ON DELETE CASCADE,
171
172 CONSTRAINT fk_LanguagePostTranslation
173 FOREIGN KEY (fiLanguage) REFERENCES tblLanguage(idLanguage)
174 ON UPDATE CASCADE
175 ON DELETE CASCADE
176
177) ENGINE = INNODB;
178
179
180
181CREATE TABLE IF NOT EXISTS tblSubscription(
182
183 idSubscription MEDIUMINT UNSIGNED,
184 fiCustomer MEDIUMINT UNSIGNED NOT NULL,
185 fiTicket MEDIUMINT UNSIGNED NOT NULL,
186
187 PRIMARY KEY(idSubscription, fiTicket),
188
189 CONSTRAINT fk_Subscription
190 FOREIGN KEY (fiCustomer) REFERENCES tblCustomer(idCustomer)
191 ON UPDATE CASCADE
192 ON DELETE CASCADE,
193
194 CONSTRAINT fk_TicketSubscription
195 FOREIGN KEY (fiTicket) REFERENCES tblTicket(idTicket)
196 ON UPDATE CASCADE
197 ON DELETE CASCADE
198
199) ENGINE = INNODB;
200
201CREATE TABLE IF NOT EXISTS tblMail(
202
203 idMailAddress VARCHAR(254) PRIMARY KEY,
204 fiSubscription MEDIUMINT UNSIGNED,
205 fiTicket MEDIUMINT UNSIGNED,
206
207 CONSTRAINT fk_SubMail
208 FOREIGN KEY (fiSubscription) REFERENCES tblSubscription(idSubscription)
209 ON UPDATE CASCADE
210 ON DELETE CASCADE,
211
212 CONSTRAINT fk_TicketMail
213 FOREIGN KEY (fiTicket) REFERENCES tblTicket(idTicket)
214 ON UPDATE CASCADE
215 ON DELETE CASCADE
216
217) ENGINE = INNODB;
218
219CREATE TABLE IF NOT EXISTS tblTwitter(
220
221 idTwitterAccount VARCHAR(254) PRIMARY KEY,
222 fiSubscription MEDIUMINT UNSIGNED,
223
224 CONSTRAINT fk_Twitter
225 FOREIGN KEY (fiSubscription) REFERENCES tblSubscription(idSubscription)
226 ON UPDATE CASCADE
227 ON DELETE CASCADE
228
229) ENGINE = INNODB;
230
231CREATE TABLE IF NOT EXISTS tblToken(
232
233 idToken VARCHAR(255) PRIMARY KEY,
234 fiPerson MEDIUMINT UNSIGNED NOT NULL,
235 dtTimestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL,
236
237 CONSTRAINT fk_UserToken
238 FOREIGN KEY (fiPerson) REFERENCES tblPerson(idPerson)
239 ON UPDATE CASCADE
240 ON DELETE CASCADE
241
242) ENGINE = INNODB;
243
244/***
245* STORED PROCEDURES
246*/
247
248USE dbtisy
249
250DELIMITER $$
251
252CREATE PROCEDURE sp_add_post_translation(
253 IN i_post MEDIUMINT UNSIGNED,
254 IN i_language VARCHAR(2),
255 IN i_description VARCHAR(1024),
256 OUT o_status INT
257)
258BEGIN
259 DECLARE l_error_dupkey INT DEFAULT 0;
260 DECLARE l_error_null INT DEFAULT 0;
261
262 DECLARE dupkey_error CONDITION FOR 1062;
263 DECLARE null_error CONDITION FOR 1048;
264
265
266 BEGIN
267
268 DECLARE CONTINUE HANDLER FOR dupkey_error SET l_error_dupkey = 1;
269 DECLARE CONTINUE HANDLER FOR null_error SET l_error_null = 1;
270
271 INSERT INTO tblPostTranslation (
272 fiPost,
273 fiLanguage,
274 dtDescription
275 )
276 VALUES (
277 i_post,
278 i_language,
279 i_description
280 );
281
282 END;
283
284
285 IF l_error_dupkey THEN
286 SELECT 1012 INTO o_status;
287 ELSEIF l_error_null THEN
288 SELECT 1011 INTO o_status;
289 ELSE
290 SELECT 1001 INTO o_status;
291 END IF;
292
293END$$
294
295DELIMITER ;
296
297DELIMITER $$
298
299CREATE PROCEDURE sp_add_post(
300 IN i_ticketID MEDIUMINT UNSIGNED,
301 IN i_userID MEDIUMINT UNSIGNED,
302 IN i_description VARCHAR(1024),
303 IN i_language VARCHAR(2),
304 OUT o_status INT
305)
306BEGIN
307 DECLARE l_error_dupkey INT DEFAULT 0;
308 DECLARE l_error_null INT DEFAULT 0;
309 DECLARE l_return INT DEFAULT 1001;
310
311 DECLARE dupkey_error CONDITION FOR 1062;
312 DECLARE null_error CONDITION FOR 1048;
313
314
315 BEGIN
316
317 DECLARE CONTINUE HANDLER FOR dupkey_error SET l_error_dupkey = 1;
318 DECLARE CONTINUE HANDLER FOR null_error SET l_error_null = 1;
319
320 INSERT INTO tblPost (
321 fiTicket,
322 fiPerson
323 )
324 VALUES (
325 i_ticketID,
326 i_userID
327 );
328
329 SET @postID := LAST_INSERT_ID();
330
331 CALL sp_add_post_translation(@postID, i_language, i_description, l_return);
332
333 END;
334
335
336 IF l_error_dupkey THEN
337 SELECT 1012 INTO o_status;
338 ELSEIF l_error_null THEN
339 SELECT 1011 INTO o_status;
340 ELSEIF l_return != 1001 THEN
341 SELECT l_return INTO o_status;
342 ELSE
343 SELECT 1001 INTO o_status;
344 END IF;
345
346END$$
347
348DELIMITER ;
349
350DELIMITER $$
351
352CREATE PROCEDURE sp_add_ticket_translation(
353 IN i_ticketID MEDIUMINT UNSIGNED,
354 IN i_language VARCHAR(2),
355 IN i_description VARCHAR(1024),
356 OUT o_status INT
357)
358BEGIN
359
360 DECLARE l_error_dupkey INT DEFAULT 0;
361 DECLARE l_error_null INT DEFAULT 0;
362
363 DECLARE dupkey_error CONDITION FOR 1062;
364 DECLARE null_error CONDITION FOR 1048;
365
366 BEGIN
367
368 DECLARE CONTINUE HANDLER FOR dupkey_error SET l_error_dupkey = 1;
369 DECLARE CONTINUE HANDLER FOR null_error SET l_error_null = 1;
370
371 INSERT INTO tblTicketTranslation (
372 fiTicket,
373 fiLanguage,
374 dtDescription
375 )
376 VALUES (
377 i_ticketID,
378 i_language,
379 i_description
380 );
381
382 END;
383
384 IF l_error_dupkey THEN
385 SELECT 1012 INTO o_status;
386 ELSEIF l_error_null THEN
387 SELECT 1011 INTO o_status;
388 ELSE
389 SELECT 1001 INTO o_status;
390 END IF;
391
392END$$
393
394DELIMITER ;
395
396DELIMITER $$
397
398CREATE PROCEDURE sp_change_status(
399 IN i_ticketID MEDIUMINT UNSIGNED,
400 IN i_userID MEDIUMINT UNSIGNED,
401 IN i_status TINYINT UNSIGNED,
402 OUT o_status INT
403)
404BEGIN
405
406 DECLARE l_error_dupkey INT DEFAULT 0;
407 DECLARE l_error_null INT DEFAULT 0;
408 DECLARE l_error_not_owner INT DEFAULT 0;
409
410 DECLARE dupkey_error CONDITION FOR 1062;
411 DECLARE null_error CONDITION FOR 1048;
412
413 BEGIN
414
415 DECLARE l_isOwner BOOLEAN;
416 DECLARE l_ownerNumber MEDIUMINT UNSIGNED;
417
418 DECLARE CONTINUE HANDLER FOR dupkey_error SET l_error_dupkey = 1;
419 DECLARE CONTINUE HANDLER FOR null_error SET l_error_null = 1;
420
421
422
423 SELECT fiPerson
424 FROM tblTicket
425 WHERE idTicket = i_ticketID
426 INTO l_ownerNumber;
427
428 SELECT IF(i_userID = l_ownerNumber, TRUE, FALSE) INTO l_isOwner;
429
430
431
432 IF l_isOwner = TRUE THEN
433 UPDATE tblTicket
434 SET dtState = i_status
435 WHERE idTicket = i_ticketID;
436 ELSE SET l_error_not_owner = 1;
437 END IF;
438 END;
439
440 IF l_error_dupkey THEN
441 SELECT 1012 INTO o_status;
442 ELSEIF l_error_null THEN
443 SELECT 1011 INTO o_status;
444 ELSEIF l_error_not_owner THEN
445 SELECT 1013 INTO o_status;
446 ELSE
447 SELECT 1001 INTO o_status;
448 END IF;
449
450END$$
451
452DELIMITER ;
453
454DELIMITER $$
455
456CREATE PROCEDURE sp_create_ticket (
457 IN i_ticketTitle VARCHAR(128),
458 IN i_userid MEDIUMINT UNSIGNED,
459 IN i_category TINYINT UNSIGNED,
460 IN i_language VARCHAR(2),
461 IN i_description VARCHAR(1024),
462 OUT o_status INT
463)
464BEGIN
465
466 DECLARE l_error_dupkey INT DEFAULT 0;
467 DECLARE l_error_null INT DEFAULT 0;
468 DECLARE l_return INT DEFAULT 1001;
469
470 DECLARE l_state INT DEFAULT 1;
471
472 DECLARE dupkey_error CONDITION FOR 1062;
473 DECLARE null_error CONDITION FOR 1048;
474
475 BEGIN
476
477 DECLARE CONTINUE HANDLER FOR dupkey_error SET l_error_dupkey = 1;
478 DECLARE CONTINUE HANDLER FOR null_error SET l_error_null = 1;
479
480 INSERT INTO tblTicket (
481 dtTitle,
482 dtState,
483 fiPerson,
484 fiCategory
485 )
486 VALUES (
487 i_ticketTitle,
488 l_state,
489 i_userid,
490 i_category
491 );
492
493 SET @idTicket := LAST_INSERT_ID();
494
495 call sp_add_ticket_translation(@idTicket, i_language, i_description, l_return);
496
497 END;
498
499 IF l_error_dupkey THEN
500 SELECT 1012 INTO o_status;
501 ELSEIF l_error_null THEN
502 SELECT 1011 INTO o_status;
503 ELSEIF @result != 1001 THEN
504 SELECT l_return INTO o_status;
505 ELSE
506 SELECT 1001 INTO o_status;
507 END IF;
508
509END$$
510
511DELIMITER ;
512
513DELIMITER $$
514
515CREATE PROCEDURE sp_login (
516 IN i_userEmail VARCHAR(254),
517 OUT o_password VARCHAR(255)
518)
519BEGIN
520
521
522 BEGIN
523
524 SELECT dtPassword FROM tblPerson WHERE dtEmail = i_userEmail INTO o_password;
525
526 END;
527
528
529END$$
530
531DELIMITER ;
532
533DELIMITER $$
534
535CREATE PROCEDURE sp_register_user (
536
537 IN i_name VARCHAR(64),
538 IN i_lastname VARCHAR(64),
539 IN i_address VARCHAR(128),
540 IN i_phoneNumber VARCHAR(64),
541 IN i_email VARCHAR(254),
542 IN i_preferedLang VARCHAR(2),
543 IN i_password VARCHAR(255),
544 IN i_type INT,
545 OUT o_status INT
546)
547BEGIN
548
549 DECLARE l_error_dupkey INT DEFAULT 0;
550 DECLARE l_error_null INT DEFAULT 0;
551 DECLARE l_id INT DEFAULT 0;
552
553 DECLARE dupkey_error CONDITION FOR 1062;
554 DECLARE null_error CONDITION FOR 1048;
555
556 SET i_type = IFNULL(i_type, 1);
557
558 BEGIN
559
560 DECLARE CONTINUE HANDLER FOR dupkey_error SET l_error_dupkey = 1;
561 DECLARE CONTINUE HANDLER FOR null_error SET l_error_null = 1;
562
563 INSERT INTO tblPerson (
564 dtName,
565 dtLastName,
566 dtAddress,
567 dtPhoneNumber,
568 dtEmail,
569 fiPrefLanguage,
570 dtPassword
571 )
572 VALUES (
573 i_name,
574 i_lastname,
575 i_address,
576 i_phoneNumber,
577 i_email,
578 i_preferedLang,
579 i_password
580 );
581
582 SET @idPerson := LAST_INSERT_ID();
583
584 CASE i_type
585 WHEN 1 THEN INSERT INTO tblCustomer(fiPerson) VALUES(@idPerson);
586 WHEN 2 THEN INSERT INTO tblHelpdesk(fiPerson) VALUES(@idPerson);
587 WHEN 3 THEN INSERT INTO tblTechnician(fiPerson) VALUES(@idPerson);
588 ELSE INSERT INTO tblCustomer(fiPerson) VALUES(@idPerson);
589 END CASE;
590
591 END;
592
593 IF l_error_dupkey THEN
594 SELECT 1012 INTO o_status;
595 ELSEIF l_error_null THEN
596 SELECT 1011 INTO o_status;
597 ELSE
598 SELECT 1001 INTO o_status;
599 END IF;
600
601END$$
602
603DELIMITER ;
604
605DELIMITER $$
606
607CREATE PROCEDURE sp_reset_password (
608 IN i_email VARCHAR(254),
609 IN i_token VARCHAR(255),
610 OUT o_status INT
611)
612BEGIN
613
614 DECLARE l_isTokenValid BOOL DEFAULT FALSE;
615 DECLARE l_userID VARCHAR(254);
616
617 SELECT idUser FROM tblPerson WHERE dtEmail = i_email INTO l_userID;
618
619 SELECT EXISTS(SELECT * FROM tblToken WHERE idToken = i_token AND fiPerson = l_userID AND dtTimestamp < CURRENT_TIMESTAMP()) INTO l_isTokenValid;
620
621 IF l_isTokenValid THEN
622 UPDATE tblPerson SET dtPassword = i_newPassword WHERE idUser = l_userID;
623 SELECT 1001 INTO o_status;
624 ELSE
625 SELECT 1011 INTO o_status;
626 END IF;
627
628END$$
629
630DELIMITER ;
631
632
633INSERT INTO tblLanguage VALUES
634 ('aa','Afar'),
635 ('ab','Abkhazian'),
636 ('ae','Avestan'),
637 ('af','Afrikaans'),
638 ('ak','Akan'),
639 ('am','Amharic'),
640 ('an','Aragonese'),
641 ('ar','Arabic'),
642 ('as','Assamese'),
643 ('av','Avaric'),
644 ('ay','Aymara'),
645 ('az','Azerbaijani'),
646 ('ba','Bashkir'),
647 ('be','Belarusian'),
648 ('bg','Bulgarian'),
649 ('bh','Bihari languages'),
650 ('bi','Bislama'),
651 ('bm','Bambara'),
652 ('bn','Bengali'),
653 ('bo','Tibetan'),
654 ('br','Breton'),
655 ('bs','Bosnian'),
656 ('ca','Catalan'),
657 ('ce','Chechen'),
658 ('ch','Chamorro'),
659 ('co','Corsican'),
660 ('cr','Cree'),
661 ('cs','Czech'),
662 ('cu','Church Slavic'),
663 ('cv','Chuvash'),
664 ('cy','Welsh'),
665 ('da','Danish'),
666 ('de','German'),
667 ('dv','Divehi'),
668 ('dz','Dzongkha'),
669 ('ee','Ewe'),
670 ('el','Greek'),
671 ('en','English'),
672 ('eo','Esperanto'),
673 ('es','Spanish'),
674 ('et','Estonian'),
675 ('eu','Basque'),
676 ('fa','Persian'),
677 ('ff','Fulah'),
678 ('fi','Finnish'),
679 ('fj','Fijian'),
680 ('fo','Faroese'),
681 ('fr','French'),
682 ('fy','Western Frisian'),
683 ('ga','Irish'),
684 ('gd','Gaelic'),
685 ('gl','Galician'),
686 ('gn','Guarani'),
687 ('gu','Gujarati'),
688 ('gv','Manx'),
689 ('ha','Hausa'),
690 ('he','Hebrew'),
691 ('hi','Hindi'),
692 ('ho','Hiri Motu'),
693 ('hr','Croatian'),
694 ('ht','Haitian'),
695 ('hu','Hungarian'),
696 ('hy','Armenian'),
697 ('hz','Herero'),
698 ('ia','Interlingua'),
699 ('id','Indonesian'),
700 ('ie','Interlingue'),
701 ('ig','Igbo'),
702 ('ii','Sichuan Yi'),
703 ('ik','Inupiaq'),
704 ('io','Ido'),
705 ('is','Icelandic'),
706 ('it','Italian'),
707 ('iu','Inuktitut'),
708 ('ja','Japanese'),
709 ('jv','Javanese'),
710 ('ka','Georgian'),
711 ('kg','Kongo'),
712 ('ki','Kikuyu'),
713 ('kj','Kuanyama'),
714 ('kk','Kazakh'),
715 ('kl','Kalaallisut'),
716 ('km','Central Khmer'),
717 ('kn','Kannada'),
718 ('ko','Korean'),
719 ('kr','Kanuri'),
720 ('ks','Kashmiri'),
721 ('ku','Kurdish'),
722 ('kv','Komi'),
723 ('kw','Cornish'),
724 ('ky','Kirghiz'),
725 ('la','Latin'),
726 ('lb','Luxembourgish'),
727 ('lg','Ganda'),
728 ('li','Limburgan'),
729 ('ln','Lingala'),
730 ('lo','Lao'),
731 ('lt','Lithuanian'),
732 ('lu','Luba-Katanga'),
733 ('lv','Latvian'),
734 ('mg','Malagasy'),
735 ('mh','Marshallese'),
736 ('mi','Maori'),
737 ('mk','Macedonian'),
738 ('ml','Malayalam'),
739 ('mn','Mongolian'),
740 ('mr','Marathi'),
741 ('ms','Malay'),
742 ('mt','Maltese'),
743 ('my','Burmese'),
744 ('na','Nauru'),
745 ('nb','Bokmål'),
746 ('nd','Ndebele'),
747 ('ne','Nepali'),
748 ('ng','Ndonga'),
749 ('nl','Dutch'),
750 ('nn','Norwegian Nynorsk'),
751 ('no','Norwegian'),
752 ('nv','Navajo'),
753 ('ny','Chichewa'),
754 ('oc','Occitan'),
755 ('oj','Ojibwa'),
756 ('om','Oromo'),
757 ('or','Oriya'),
758 ('os','Ossetian'),
759 ('pa','Panjabi'),
760 ('pi','Pali'),
761 ('pl','Polish'),
762 ('ps','Pushto'),
763 ('pt','Portuguese'),
764 ('qu','Quechua'),
765 ('rm','Romansh'),
766 ('rn','Rundi'),
767 ('ro','Romanian'),
768 ('ru','Russian'),
769 ('rw','Kinyarwanda'),
770 ('sa','Sanskrit'),
771 ('sc','Sardinian'),
772 ('sd','Sindhi'),
773 ('se','Northern Sami'),
774 ('sg','Sango'),
775 ('si','Sinhala'),
776 ('sk','Slovak'),
777 ('sl','Slovenian'),
778 ('sm','Samoan'),
779 ('sn','Shona'),
780 ('so','Somali'),
781 ('sq','Albanian'),
782 ('sr','Serbian'),
783 ('ss','Swati'),
784 ('st','Sotho'),
785 ('su','Sundanese'),
786 ('sv','Swedish'),
787 ('sw','Swahili'),
788 ('ta','Tamil'),
789 ('te','Telugu'),
790 ('tg','Tajik'),
791 ('th','Thai'),
792 ('ti','Tigrinya'),
793 ('tk','Turkmen'),
794 ('tl','Tagalog'),
795 ('tn','Tswana'),
796 ('to','Tonga'),
797 ('tr','Turkish'),
798 ('ts','Tsonga'),
799 ('tt','Tatar'),
800 ('tw','Twi'),
801 ('ty','Tahitian'),
802 ('ug','Uighur'),
803 ('uk','Ukrainian'),
804 ('ur','Urdu'),
805 ('uz','Uzbek'),
806 ('ve','Venda'),
807 ('vi','Vietnamese'),
808 ('vo','Volapk'),
809 ('wa','Walloon'),
810 ('wo','Wolof'),
811 ('xh','Xhosa'),
812 ('yi','Yiddish'),
813 ('yo','Yoruba'),
814 ('za','Zhuang'),
815 ('zh','Chinese'),
816 ('zu','Zulu');
817
818
819/***
820* USER CREATION
821**/
822
823call sp_register_user("Steve","Schmit","9 rue de foret","4272349","steve.schmit@gmail.com","lu","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",1, @result);
824call sp_register_user("Michel","Schmit","13 rue de l'eglise","5476456","michel.schmit@gmail.com","de","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",1, @result);
825call sp_register_user("Tim","Schmit","13 rue de l'eglise","456453","tim.schmit@gmail.com","fr","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",1, @result);
826call sp_register_user("Pol","Schmit","13 rue de l'eglise","3254456","pol.schmit@gmail.com","zu","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",1, @result);
827call sp_register_user("Lisa","Schmit","13 rue de l'eglise","67956784","lisa.schmit@gmail.com","en","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",1, @result);
828call sp_register_user("Steve","Peters","9 rue de l'eglise","4273224349","steve.peters@gmail.com","lu","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",1, @result);
829call sp_register_user("Michel","Peters","13 rue de l'eglise","5685637","michel.peters@gmail.com","de","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",1, @result);
830call sp_register_user("Tim","Peters","13 rue de l'eglise","254611346","tim.peters@gmail.com","fr","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",1, @result);
831call sp_register_user("Pol","Peters","13 rue de l'eglise","452789536","pol.peters@gmail.com","zu","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",1, @result);
832call sp_register_user("Lisa","Peters","13 rue de l'eglise","13457543","lisa.peters@gmail.com","en","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",1, @result);
833
834call sp_register_user("Steve","Henkes","9 rue principale","427234549","steve.henkes@gmail.com","lu","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",2, @result);
835call sp_register_user("Michel","Henkes","13 rue de l'eglise","13467457","michel.henkes@gmail.com","de","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",2, @result);
836call sp_register_user("Tim","Henkes","13 rue de l'eglise","5685434","tim.henkes@gmail.com","fr","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",2, @result);
837call sp_register_user("Pol","Henkes","13 rue de l'eglise","653134","pol.henkes@gmail.com","zu","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",2, @result);
838call sp_register_user("Lisa","Henkes","13 rue de l'eglise","5742456","lisa.henkes@gmail.com","en","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",2, @result);
839
840call sp_register_user("Steve","Hetto","9 rue des bois","427223490","steve.hetto@gmail.com","lu","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",3, @result);
841call sp_register_user("Michel","Hetto","13 rue de l'eglise","4572456","michel.hetto@gmail.com","de","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",3, @result);
842call sp_register_user("Tim","Hetto","13 rue de l'eglise","245782","tim.hetto@gmail.com","fr","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",3, @result);
843call sp_register_user("Pol","Hetto","13 rue de l'eglise","7245723","pol.hetto@gmail.com","zu","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",3, @result);
844call sp_register_user("Lisa","Hetto","13 rue de l'eglise","24575426","lisa.hetto@gmail.com","en","$2y$10$o4KwO9TJzxnmgAyZ7wl2Xehzx4JJE50xgRelq9GVSG2M1P2i/pUHy",3, @result);
845
846
847/***
848* CATEGORY CREATION
849**/
850
851INSERT INTO tblCategory(dtDescription) VALUES
852 ("General"),
853 ("Billing"),
854 ("Tech Support"),
855 ("Product")
856;
857
858
859/***
860* TICKET CREATION
861**/
862
863
864# call sp_create_ticket("TITLE", USERID, CATEGORYID, "LANGID", "DESC"); #
865call sp_create_ticket("Help my dishwasher broke.", 1, 4, "EN", "Hello. My Dishwasher just broke and I can't wash my dishes anymore!!",@result);
866call sp_create_ticket("What are your opening hours?", 2, 1, "EN", "Question is above.",@result);
867call sp_create_ticket("Invoice #1337 not correct", 3, 2, "EN", "You accounted me a dishwasher but I ordered a toaster",@result);
868call sp_create_ticket("Has your Toaster WIFI?", 4, 4, "EN", "Is it possible to connect the toaster to a wlan network? Thank you.",@result);
869
870
871/***
872* TICKET TRANSLATION
873**/
874
875
876# call sp_add_post_translation(POSTID, "LANGID", "DESC"); #
877call sp_add_ticket_translation(1, "DE", "Hallo. Meine Spülmaschine ist gerade kaputt gegangen und ich kann meine Teller nicht mehr waschen!!",@result);
878call sp_add_ticket_translation(3, "DE", "Sie haben mir einen Geschirrspüler verrechnet obwohl ich einen Toaster bestellt habe",@result);
879
880
881/***
882* POST CREATION
883**/
884
885
886# call sp_add_post(TICKETID, USERID, "DESC", "LANGID"); #
887call sp_add_post(1, 11, "Hello. Would you please give additional information about your dishwasher?", "EN", @result);
888call sp_add_post(1, 1, "Model Number: QT5483. It caught fire and is totally destroyed.", "EN", @result);
889call sp_add_post(1, 11, "OK. Our assurance does not cover fire damage. Please contact your local dealer to get a replacement.", "EN", @result);
890call sp_add_post(2, 12, "Hello. We currently provide a 24/7 opening.", "EN", @result);
891call sp_add_post(2, 2, "Thanks.", "EN", @result);
892call sp_add_post(3, 13, "Hello. Our financial departement will investigate this.", "EN", @result);
893call sp_add_post(3, 3, "Thank you.", "EN", @result);
894call sp_add_post(4, 14, "Hello. No, our Toasters do not have a WI-FI interface. They do have a RJ-45 connector and Bluetooth. If you have any other questions regarding one of our products please let us know!","EN",@result);
895
896
897/***
898* POST TRANSLATION
899**/
900
901call sp_add_post_translation();