· 8 years ago · Feb 14, 2018, 08:38 PM
1CREATE VIEW somv_roster AS
2SELECT bd.businessday AS Datum,
3v.venue AS Dropzone,
4d.duty AS Service,
5c.nickname AS Staff
6FROM som_roster r
7INNER JOIN som_businessday bd ON bd.businessdayID = r.ID_businessday
8INNER JOIN som_venue v ON v.venueID = bd.ID_venue
9INNER JOIN som_duty d ON d.dutyID = r.ID_duty
10INNER JOIN som_contact c ON c.contactID = r.ID_contact
11ORDER BY bd.businessday, v.venue
12WHERE bd.businessday >= CURDATE();
13
14Datum Dropzone Service Staff
152018-04-28 Illertissen TM 2 Manu
162018-04-28 Illertissen Packer Martina B.
172018-04-28 Illertissen TM 1 Rued
182018-04-28 Illertissen TM 3 Hane
192018-04-29 Illertissen Manifest Sissi
202018-04-29 Illertissen TM 2 Ritchie
212018-04-29 Illertissen Packer Martina B.
222018-04-29 Illertissen TM 1 Rued
232018-04-29 Illertissen TM 3 Hane
242018-05-01 Illertissen TM 1 Ritchie
252018-05-01 Illertissen TM 3 Hane
262018-05-01 Illertissen TM 2 Purzl
272018-05-01 Illertissen Packer Martina B.
282018-05-26 Illertissen TM 1 Rued
292018-05-26 Kempten TM 1 Ritchie
302018-05-26 Kempten TM 2 Hane
312018-05-26 Kempten Manifest Sissi
32
33Datum Dropzone Manifest Packer TM 1 TM 2 TM 3
342018-04-28 Illertissen [NULL] Martina B. Rued Manu Hane
352018-04-29 Illertissen Sissi Martina B. Rued Ritchie Hane
362018-05-01 Illertissen [NULL] Martina B. Ritchie Purzl Hane
372018-05-26 Illertissen [NULL] [NULL] Rued [NULL] [NULL]
382018-05-26 Kempten Sissi [NULL] Ritchie Hane [NULL]
39
40Datum Dropzone TM 2 TM 3
412018-04-28 Illertissen [NULL] Hane
422018-04-29 Illertissen [NULL] Hane
432018-05-01 Illertissen [NULL] Hane
442018-05-26 Kempten Hane [NULL]
45
46/*
47 * Create routine somp_roster()
48 * Requires somp_roster_et() to expand the columns dynamically.
49 * Requires somv_roster view to read the rosters.
50 * 4Air 2018 (c)
51 */
52
53DROP PROCEDURE IF EXISTS somp_roster;
54
55DELIMITER $$
56CREATE PROCEDURE somp_roster(IN staff_name VARCHAR(25))
57BEGIN
58 /*
59 * variables have to be declared at the start of the procedure
60 * declare variables for the loops
61 * flag to be set at the end of the loop
62 */
63 DECLARE finishedloop INTEGER DEFAULT 0;
64
65 -- flag to loop through the duty table to
66 DECLARE temp_date DATE DEFAULT NULL;
67 DECLARE temp_venue VARCHAR(25) DEFAULT '';
68 DECLARE temp_duty VARCHAR(25) DEFAULT '';
69 DECLARE temp_staff VARCHAR(25) DEFAULT '';
70
71 -- this var holds the string for the alter table statement
72 DECLARE alttable VARCHAR(250) DEFAULT '';
73
74 -- the first cursor to prepare the temp table
75 DECLARE roster_cursor CURSOR FOR
76 SELECT * FROM somv_roster WHERE Datum >= CURDATE() AND Staff LIKE CONCAT('%',staff_name) ORDER BY Service;
77
78 -- to recognize the end of the loops. Set to 0 before starting the loop!
79 DECLARE CONTINUE HANDLER FOR NOT FOUND SET finishedloop = 1;
80
81 -- cleanup first from a interrupted previous run
82 DROP TEMPORARY TABLE IF EXISTS duties;
83
84 -- now we need a temp table for the duties
85 CREATE TEMPORARY TABLE duties (Datum DATE, Dropzone VARCHAR(25), PRIMARY KEY(Datum, Dropzone)) ENGINE=MEMORY DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
86
87 -- loop through the temp table
88 SET finishedloop = 0;
89 OPEN roster_cursor;
90 SET @alttable = '';
91
92 get_roster_loop: LOOP
93
94 -- now read the duties and create the new rows in the temp rosters
95 FETCH roster_cursor INTO temp_date, temp_venue, temp_duty, temp_staff;
96 IF finishedloop = 1 THEN
97 LEAVE get_roster_loop;
98 END IF;
99
100 /*
101 * fist step - try to expand the table with needed column
102 * the statement has to be this:
103 * call somp_rosters_et('tablename','columnname','columndefs')
104 * example:
105 * CALL somp_rosters_et('duties','TM 1','VARCHAR(25)');
106 */
107
108 CALL somp_roster_et('duties',temp_duty,'VARCHAR(25)');
109
110 /*
111 * next prepare the statement to fill the data...
112 * implies the test if the date and venue already exists
113 * if so, then use UPDATE TABLE rather then INSERT TABLE
114 */
115 SET @alttable = CONCAT('INSERT INTO duties (`Datum`,`Dropzone`,`', temp_duty , '`) VALUES (''' , temp_date , ''',''' , temp_venue , ''',''' , temp_staff , ''') ON DUPLICATE KEY UPDATE `', temp_duty , '` = ''', temp_staff , ''';') ;
116 PREPARE stmt FROM @alttable;
117 EXECUTE stmt;
118 DEALLOCATE PREPARE stmt;
119
120
121 END LOOP get_roster_loop;
122
123 CLOSE roster_cursor;
124
125 SELECT * FROM duties ORDER BY Datum, Dropzone;
126
127 DROP TEMPORARY TABLE IF EXISTS duties;
128
129END $$
130
131DELIMITER ;
132
133/*
134 * Stored Procedure to expand the table and do not care about an error if the column exists.
135 * Used by somp_roster();
136 * 4Air 2018 (c).
137 */
138
139DROP PROCEDURE IF EXISTS somp_roster_et;
140
141DELIMITER $$
142
143CREATE PROCEDURE somp_roster_et(IN t_name VARCHAR(25), IN c_name VARCHAR(25), IN c_attribute VARCHAR(25))
144BEGIN
145 DECLARE eflag INTEGER DEFAULT 0;
146 DECLARE modtable VARCHAR(150) DEFAULT "";
147 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET eflag = 1;
148 SET @modtable = CONCAT('ALTER TABLE ' , t_name , ' ADD COLUMN `' , c_name , '` ' , c_attribute , ';');
149 -- INSERT INTO som_log (Entry) VALUES(CONCAT('somp_roster_et: ',@modtable));
150 PREPARE stmt FROM @modtable;
151 EXECUTE stmt;
152 DEALLOCATE PREPARE stmt;
153END $$
154
155DELIMITER ;
156
157call somp_roster('Ritchie');
158SELECT * FROM duties;
159call somp_roster('Martina B.');
160SELECT * FROM duties;