· 8 years ago · Aug 09, 2018, 09:36 AM
1DELIMITER $$
2CREATE DEFINER=`root`@`%` PROCEDURE `Intervention_Add_Installer`(_id_intervention integer, _d_installer integer)
3BEGIN
4 INSERT INTO mwsdb.intervention_installer (id_intervention, id_installer) VALUES(_id_intervention, _id_installer);
5 SELECT LAST_INSERT_ID();
6END$$
7DELIMITER ;
8
9DELIMITER $$
10CREATE DEFINER=`root`@`%` PROCEDURE `Intervention_Get_Last_Intervention_Id`()
11BEGIN
12 SELECT max(id_intervention) FROM mwsdb.intervention;
13END$$
14DELIMITER ;
15
16DELIMITER $$
17CREATE DEFINER=`root`@`%` PROCEDURE `Intervention_Add_Ticket`(_id_intervention VARCHAR(100), _id_vehicle VARCHAR(100), _brand VARCHAR(200), _model VARCHAR(200), _type VARCHAR(200), _note VARCHAR(200))
18BEGIN
19 INSERT INTO mwsdb.intervention_ticket (id_intervention, id_vehicle, intervention_type, model_vehicle, brand_vehicle, note) VALUES(_id_intervention, _id_vehicle, _type, _model, _brand, _note);
20 SELECT LAST_INSERT_ID();
21END$$
22DELIMITER ;
23
24DELIMITER $$
25CREATE DEFINER=`root`@`%` PROCEDURE `Intervention_Remove`(_id_intervention integer)
26BEGIN
27 DELETE FROM mwsdb.intervention
28 WHERE
29 id_intervention = _id_intervention;
30
31 DELETE FROM mwsdb.intervention_installer
32 WHERE
33 id_intervention = _id_intervention;
34 DELETE FROM mwsdb.intervention_ticket
35 WHERE
36 id_intervention = _id_intervention;
37END$$
38DELIMITER ;
39
40DELIMITER $$
41CREATE DEFINER=`root`@`%` PROCEDURE `Intervention_Remove_Installer_By_InterventionId`(_id_intervention integer)
42BEGIN
43 DELETE FROM mwsdb.intervention_installer
44 WHERE
45 id_intervention = _id_intervention;
46END$$
47DELIMITER ;
48
49DELIMITER $$
50CREATE DEFINER=`root`@`%` PROCEDURE `Intervention_Select_All`()
51BEGIN
52 SELECT intervention.id_intervention,
53 intervention.ref_intervention,
54 intervention.id_client,
55 intervention.adress AS intervention_adress,
56 intervention.city AS intervention_city,
57 intervention.cp AS intervention_cp,
58 intervention.contact_name AS intervention_contact_name,
59 intervention.contact_phone AS intervention_contact_phone,
60 intervention.note AS intervention_note,
61 intervention.date,
62 mwsdb.account.name AS account_name
63 FROM intervention
64 LEFT JOIN mwsdb.account ON account.id_account = intervention.id_client;
65END$$
66DELIMITER ;
67
68DELIMITER $$
69CREATE DEFINER=`root`@`%` PROCEDURE `Intervention_Select_By_Id`(_id_intervention integer)
70BEGIN
71 SELECT id_intervention, ref_intervention, adress, city, cp, contact_name, contact_phone, note, date
72 FROM mwsdb.intervention
73 WHERE id_intervention = _id_intervention;
74END$$
75DELIMITER ;
76
77DELIMITER $$
78CREATE DEFINER=`root`@`%` PROCEDURE `Intervention_Select_All_Account`()
79BEGIN
80 SELECT id_account, name FROM mwsdb.account;
81END$$
82DELIMITER ;
83
84DELIMITER $$
85CREATE DEFINER=`root`@`%` PROCEDURE `Intervention_Select_Installer`(_id_intervention integer)
86BEGIN
87 SELECT installer.id_installer AS installer_id,
88 installer.name AS installer_name,
89 installer.company AS installer_company,
90 installer.phone AS installer_phone
91 FROM installer
92 WHERE installer.id_installer IN (SELECT intervention_installer.id_installer
93 FROM intervention_installer
94 WHERE intervention_installer.id_intervention = _id_intervention);
95END$$
96DELIMITER ;
97
98DELIMITER $$
99CREATE DEFINER=`root`@`%` PROCEDURE `Intervention_Select_Tickets_By_Id`(_id_intervention integer)
100BEGIN
101 SELECT
102 intervention_ticket.id_ticket,
103 intervention_ticket.id_vehicle,
104 intervention_ticket.id_intervention,
105 intervention_ticket.intervention_type,
106 intervention_ticket.note,
107 intervention_ticket.model_vehicle,
108 intervention_ticket.brand_vehicle,
109 mwsdb.vehicle.id_equipment,
110 mwsdb.vehicle.license_plate,
111 mwsdb.vehicle.park_number,
112 mwsdb.equipment.imei
113 FROM intervention_ticket
114 LEFT JOIN mwsdb.vehicle ON mwsdb.vehicle.id_vehicle = intervention_ticket.id_vehicle
115 LEFT JOIN mwsdb.equipment ON mwsdb.equipment.id_equipment = mwsdb.vehicle.id_equipment
116 WHERE id_intervention = _id_intervention;
117END$$
118DELIMITER ;
119
120DELIMITER $$
121CREATE DEFINER=`root`@`%` PROCEDURE `Intervention_Select_Vehicles_All`()
122BEGIN
123 SELECT id_vehicle, license_plate, park_number, id_equipment FROM mwsdb.vehicle;
124END$$
125DELIMITER ;
126
127DELIMITER $$
128CREATE DEFINER=`root`@`%` PROCEDURE `Intervention_Update`(_id_intervention integer, _id_client integer, _adress VARCHAR(255), _city VARCHAR(255), _cp VARCHAR(255), _date VARCHAR(255), _note VARCHAR(3000), _contact_name VARCHAR(255), _contact_phone VARCHAR(255), _ref_intervention VARCHAR(45))
129BEGIN
130 IF EXISTS (SELECT * FROM intervention WHERE id_intervention = _id_intervention)
131 THEN
132 Update intervention
133 SET
134 id_client = _id_client,
135 adress = _adress,
136 city = _city,
137 cp = _cp,
138 date = _date,
139 note = _note,
140 contact_name = _contact_name,
141 contact_phone = _contact_phone,
142 ref_intervention = _ref_intervention
143 WHERE id_intervention = _id_intervention;
144 ELSE
145 INSERT INTO intervention (id_client, adress, city, cp, date, note, contact_name, contact_phone, ref_intervention)
146 VALUES (_id_client, _adress, _city, _cp, _date, _note, _contact_name, _contact_phone, _ref_intervention);
147 SELECT LAST_INSERT_ID();
148 END IF;
149END$$
150DELIMITER ;
151
152DELIMITER $$
153CREATE DEFINER=`root`@`%` PROCEDURE `Intervention_Update_Installer`(_id_intervention integer, _id_installer integer)
154BEGIN
155 INSERT INTO mwsdb.intervention_installer (id_intervention, id_installer) VALUES(_id_intervention, _id_installer);
156 SELECT id_intervention FROM mwsdb.intervention;
157END$$
158DELIMITER ;
159
160DELIMITER $$
161CREATE DEFINER=`root`@`%` PROCEDURE `Account_Intervention_Select_ById`(_id_account integer)
162BEGIN
163 SELECT name, id_account FROM mwsdb.account WHERE id_account = _id_account;
164END$$
165DELIMITER ;
166
167DELIMITER $$
168CREATE DEFINER=`root`@`%` PROCEDURE `Installer_Select_All`()
169BEGIN
170 SELECT * FROM installer;
171END$$
172DELIMITER ;
173
174DELIMITER $$
175CREATE DEFINER=`root`@`%` PROCEDURE `Installer_Add`(_name varchar(255), _company varchar(255), _phone varchar(255))
176BEGIN
177 INSERT INTO mwsdb.installer (name, company, phone) VALUES(_name, _company, _phone);
178 SELECT LAST_INSERT_ID();
179END$$
180DELIMITER ;
181
182DELIMITER $$
183CREATE DEFINER=`root`@`%` PROCEDURE `Installer_Select_By_Id`(_id_installer integer)
184BEGIN
185 SELECT id_installer, name, company, phone FROM mwsdb.installer WHERE id_installer = _id_installer;
186END$$
187DELIMITER ;
188
189CREATE TABLE `installer` (
190 `id_installer` int(11) NOT NULL AUTO_INCREMENT,
191 `name` varchar(255) DEFAULT NULL,
192 `company` varchar(255) DEFAULT NULL,
193 `phone` varchar(100) DEFAULT NULL,
194 PRIMARY KEY (`id_installer`)
195) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
196
197CREATE TABLE `intervention` (
198 `id_intervention` int(11) NOT NULL AUTO_INCREMENT,
199 `ref_intervention` varchar(45) DEFAULT NULL,
200 `id_client` int(11) DEFAULT NULL,
201 `adress` varchar(255) DEFAULT NULL,
202 `city` varchar(255) DEFAULT NULL,
203 `cp` varchar(255) DEFAULT NULL,
204 `contact_name` varchar(255) DEFAULT NULL,
205 `contact_phone` varchar(255) DEFAULT NULL,
206 `note` varchar(3000) DEFAULT NULL,
207 `date` date DEFAULT NULL,
208 `creation_date` datetime DEFAULT NULL,
209 PRIMARY KEY (`id_intervention`)
210) ENGINE=InnoDB AUTO_INCREMENT=59 DEFAULT CHARSET=utf8;
211
212CREATE TABLE `intervention_installer` (
213 `id` int(11) NOT NULL AUTO_INCREMENT,
214 `id_intervention` int(11) DEFAULT NULL,
215 `id_installer` int(11) DEFAULT NULL,
216 PRIMARY KEY (`id`)
217) ENGINE=InnoDB AUTO_INCREMENT=392 DEFAULT CHARSET=utf8;
218
219CREATE TABLE `intervention_ticket` (
220 `id_ticket` int(11) NOT NULL AUTO_INCREMENT,
221 `id_intervention` int(11) DEFAULT NULL,
222 `id_vehicle` int(11) DEFAULT NULL,
223 `intervention_type` varchar(100) DEFAULT NULL,
224 `brand_vehicle` varchar(100) DEFAULT NULL,
225 `model_vehicle` varchar(100) DEFAULT NULL,
226 `note` varchar(300) DEFAULT NULL,
227 PRIMARY KEY (`id_ticket`)
228) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8;