L'application de gestion du staff du THSF: https://participer.thsf.net
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

participate.sql 19KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224
  1. \echo ******************************
  2. \echo * Dropping database participer_thsf
  3. \echo ******************************
  4. \c postgres;
  5. drop database participer_thsf;
  6. \echo **************************
  7. \echo * Dropping role participer_thsf
  8. \echo **************************
  9. drop role participer_thsf;
  10. \echo ***************************************************
  11. \echo * Creating role participer_thsf with password participer_thsf
  12. \echo ***************************************************
  13. create role participer_thsf with LOGIN ENCRYPTED PASSWORD 'participer_thsf';
  14. \echo ******************************
  15. \echo * Creating database participer_thsf
  16. \echo ******************************
  17. create database participer_thsf;
  18. \echo *******************************************
  19. \echo * Giving participer_thsf ownership to participer_thsf
  20. \echo *******************************************
  21. alter database participer_thsf owner to participer_thsf;
  22. \echo *********************************
  23. \echo * Creating participer_thsf_users table
  24. \echo *********************************
  25. \c participer_thsf;
  26. CREATE TABLE participer_thsf_users (
  27. id serial primary key,
  28. mail text not NULL,
  29. password text not NULL,
  30. name text,
  31. phone text,
  32. diet text,
  33. is_admin integer not NULL,
  34. link_id text
  35. );
  36. \echo *********************************
  37. \echo * Creating participer_thsf_roles table
  38. \echo *********************************
  39. CREATE TABLE participer_thsf_roles (
  40. id serial primary key,
  41. role text not NULL,
  42. description text not NULL,
  43. num_slot integer not null default 2
  44. );
  45. \echo *********************************
  46. \echo * Creating participer_thsf_turns table
  47. \echo *********************************
  48. CREATE TABLE participer_thsf_turns (
  49. id serial primary key,
  50. role_id integer not NULL,
  51. start_time timestamp not NULL,
  52. end_time timestamp not NULL,
  53. num_slot integer not NULL default 2,
  54. constraint fk_turns_role
  55. foreign key (role_id)
  56. REFERENCES participer_thsf_roles (id)
  57. );
  58. \echo *********************************
  59. \echo * Creating participer_thsf_staffs table
  60. \echo *********************************
  61. CREATE TABLE participer_thsf_staffs (
  62. id serial primary key,
  63. user_id integer not NULL,
  64. turn_id integer not NULL,
  65. slot_num integer not NULL,
  66. constraint fk_turns_turn_id
  67. foreign key (turn_id)
  68. REFERENCES participer_thsf_turns(id),
  69. constraint fk_turns_staff_user_id
  70. foreign key (user_id)
  71. REFERENCES participer_thsf_users (id)
  72. );
  73. \echo *************************************************
  74. \echo * Giving participer_thsf_users ownership to participer_thsf
  75. \echo *************************************************
  76. alter table participer_thsf_users owner to participer_thsf;
  77. \echo *************************************************
  78. \echo * Giving participer_thsf_roles ownership to participer_thsf
  79. \echo *************************************************
  80. alter table participer_thsf_roles owner to participer_thsf;
  81. \echo *************************************************
  82. \echo * Giving participer_thsf_turns ownership to participer_thsf
  83. \echo *************************************************
  84. alter table participer_thsf_turns owner to participer_thsf;
  85. \echo *************************************************
  86. \echo * Giving participer_thsf_staffs ownership to participer_thsf
  87. \echo *************************************************
  88. alter table participer_thsf_staffs owner to participer_thsf;
  89. \echo *********************************************************************
  90. \echo * Inserting user demo identified by password demo to participer_thsf_users
  91. \echo *********************************************************************
  92. insert into participer_thsf_users (mail, password, name, phone, diet, is_admin) values ('bofh@tetalab.org',
  93. '$2b$12$wm7PQ9IE7TYGYk1XB11mPusq7HsjYLYxt5G4v5Wz.jZbh5iWDHP5q',
  94. 'BOFH',
  95. '0000000000',
  96. 'Omnivore',
  97. 1);
  98. \echo *********************************************************************
  99. \echo * Inserting roles to participer_thsf_roles
  100. \echo *********************************************************************
  101. insert into participer_thsf_roles (role, description) values ('Référent staff', '');
  102. insert into participer_thsf_roles (role, description) values ('Référent run', '');
  103. insert into participer_thsf_roles (role, description) values ('Référent bar', '');
  104. insert into participer_thsf_roles (role, description) values ('Préparation/Installation/signalétique', 'Fermeture des zones inaccessibles au public|Prépa buvette : allumage tireuses et frigos 4h avant ouverture \+ approvisionnement des bars en produits destinés à la vente|Préparation des différents postes / stands selon besoins : PLN, billetterie, T-Shirts...|Prépa toilettes public : aspiration/pompage, nettoyage à grandes eaux, remplissage du produit, changement poubelles, approvisionnement PQ|Nettoyage lieu, réapprovisionnement poubelles aux endroits stratégiques');
  105. insert into participer_thsf_roles (role, description) values ('Volante', 'Veiller à la sécurité du lieu : rondes, surveillance accès, gestion de crises, messager, remplaçant, etc... (collaboration avec référent staff)|Ramassage verre, check PQ, ménage ponctuel|Sur le dernier créneau horaire, nettoyage de deux blocs WC|Au moins une personne connaissant bien Myrys sur chaque créneau horaire');
  106. insert into participer_thsf_roles (role, description) values ('P.L.N', 'Accueil public, explication du concept de PLN, tampons (si besoin)|Informer et guider le public (prog, espaces, toilettes,...)|Comptage du public');
  107. insert into participer_thsf_roles (role, description) values ('Buvette ''bulle''','Service au bar et encaissement (caisse ou tickets boisson selon besoins)|Nettoyage régulier : bar, tireuses, cendriers, verres abandonnés...');
  108. insert into participer_thsf_roles (role, description) values ('Bar ''concert''','Service au bar et encaissement (caisse ou tickets boisson selon besoins)|Nettoyage régulier : bar, tireuses, cendriers, verres abandonnés...');
  109. insert into participer_thsf_roles (role, description) values ('Billeterie','Vente de tickets boisson / 1 ticket = 2,50 €|Poinçonnage des cartes VIP. 1 poinçon = 1 ticket = 1 euros|Tickets valables sur les 4 jours mais NON REMBOURSABLES');
  110. insert into participer_thsf_roles (role, description) values ('Catering', 'Vérification de l''accès au catering (badge)|Service à l''assiette si besoin, gestion des quantités, réapprovisionnement du buffet|Maintenir la propreté des espaces catering (cuisine, buffet, tables, salle d''AG, tables extérieures, jardins)|Informer / rappeler le principe d''autogestion pour la vaisselle');
  111. insert into participer_thsf_roles (role, description) values ('Ménage', 'Nettoyage général à la fermeture du lieu : ramasser verres / déchets / cendriers, sortir poubelles, cleaner surfaces (bar, stand,...) \+ balai');
  112. \echo *********************************************************************
  113. \echo * Inserting turns to participer_thsf_turns
  114. \echo *********************************************************************
  115. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (1, '2018-05-10 20:00:00', '2018-05-11 02:00:00', 1);
  116. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (1, '2018-05-10 12:00:00', '2018-05-10 20:00:00', 1);
  117. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (1, '2018-05-11 12:00:00', '2018-05-11 21:00:00', 1);
  118. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (1, '2018-05-12 12:00:00', '2018-05-12 21:00:00', 1);
  119. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (1, '2018-05-11 21:00:00', '2018-05-12 04:00:00', 1);
  120. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (1, '2018-05-12 21:00:00', '2018-05-13 05:00:00', 1);
  121. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (1, '2018-05-13 12:00:00', '2018-05-13 18:00:00', 1);
  122. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (1, '2018-05-13 18:00:00', '2018-05-13 23:00:00', 1);
  123. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (2, '2018-05-10 12:00:00', '2018-05-10 21:00:00', 1);
  124. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (2, '2018-05-11 12:00:00', '2018-05-11 21:00:00', 1);
  125. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (2, '2018-05-12 12:00:00', '2018-05-12 21:00:00', 1);
  126. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (2, '2018-05-13 12:00:00', '2018-05-13 21:00:00', 1);
  127. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (3, '2018-05-10 12:00:00', '2018-05-10 21:00:00', 1);
  128. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (3, '2018-05-11 12:00:00', '2018-05-11 21:00:00', 1);
  129. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (3, '2018-05-12 12:00:00', '2018-05-12 21:00:00', 1);
  130. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (3, '2018-05-13 12:00:00', '2018-05-13 21:00:00', 1);
  131. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (4, '2018-05-08 14:00:00', '2018-05-08 18:00:00', 4);
  132. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (4, '2018-05-09 14:00:00', '2018-05-09 18:00:00', 4);
  133. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (4, '2018-05-10 13:00:00', '2018-05-10 15:00:00', 4);
  134. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (5, '2018-05-10 14:00:00', '2018-05-10 17:00:00', 2);
  135. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (5, '2018-05-10 17:00:00', '2018-05-10 20:00:00', 4);
  136. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (5, '2018-05-10 20:00:00', '2018-05-10 23:00:00', 4);
  137. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (5, '2018-05-10 23:00:00', '2018-05-11 02:00:00', 4);
  138. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (6, '2018-05-10 14:00:00', '2018-05-10 17:00:00', 2);
  139. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (6, '2018-05-10 17:00:00', '2018-05-10 20:00:00', 2);
  140. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (6, '2018-05-10 20:00:00', '2018-05-10 23:00:00', 2);
  141. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (6, '2018-05-10 23:00:00', '2018-05-11 02:00:00', 2);
  142. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (9, '2018-05-10 20:00:00', '2018-05-10 23:00:00', 4);
  143. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (9, '2018-05-10 23:00:00', '2018-05-11 02:00:00', 4);
  144. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (7, '2018-05-10 14:00:00', '2018-05-10 17:00:00', 2);
  145. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (7, '2018-05-10 17:00:00', '2018-05-10 21:00:00', 3);
  146. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (10, '2018-05-10 12:00:00', '2018-05-10 14:00:00', 2);
  147. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (10, '2018-05-10 19:30:00', '2018-05-10 22:30:00', 2);
  148. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (4, '2018-05-11 13:00:00', '2018-05-11 14:00:00', 4);
  149. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (5, '2018-05-11 14:00:00', '2018-05-11 17:00:00', 2);
  150. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (5, '2018-05-11 17:00:00', '2018-05-11 20:00:00', 2);
  151. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (5, '2018-05-11 20:00:00', '2018-05-11 23:00:00', 4);
  152. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (5, '2018-05-11 23:00:00', '2018-05-12 01:30:00', 4);
  153. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (5, '2018-05-12 01:30:00', '2018-05-12 03:30:00', 4);
  154. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (6, '2018-05-11 14:00:00', '2018-05-11 17:00:00', 2);
  155. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (6, '2018-05-11 17:00:00', '2018-05-11 20:00:00', 2);
  156. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (6, '2018-05-11 20:00:00', '2018-05-11 23:00:00', 2);
  157. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (6, '2018-05-11 23:00:00', '2018-05-12 01:30:00', 2);
  158. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (6, '2018-05-12 01:30:00', '2018-05-12 03:30:00', 2);
  159. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (9, '2018-05-11 20:00:00', '2018-05-11 23:00:00', 4);
  160. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (9, '2018-05-11 23:00:00', '2018-05-12 01:30:00', 4);
  161. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (9, '2018-05-12 01:30:00', '2018-05-12 03:15:00', 4);
  162. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (7, '2018-05-11 14:00:00', '2018-05-11 17:00:00', 2);
  163. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (7, '2018-05-11 17:00:00', '2018-05-11 20:00:00', 2);
  164. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (7, '2018-05-11 20:00:00', '2018-05-11 22:00:00', 3);
  165. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (8, '2018-05-11 20:00:00', '2018-05-11 23:00:00', 4);
  166. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (8, '2018-05-11 23:00:00', '2018-05-12 01:30:00', 4);
  167. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (8, '2018-05-12 01:30:00', '2018-05-12 03:30:00', 4);
  168. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (10, '2018-05-11 12:00:00', '2018-05-11 14:00:00', 2);
  169. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (10, '2018-05-11 19:30:00', '2018-05-11 22:30:00', 2);
  170. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (4, '2018-05-12 12:00:00', '2018-05-12 13:00:00', 4);
  171. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (5, '2018-05-12 13:30:00', '2018-05-12 16:30:00', 2);
  172. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (5, '2018-05-12 16:30:00', '2018-05-12 19:30:00', 2);
  173. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (5, '2018-05-12 19:30:00', '2018-05-12 22:30:00', 4);
  174. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (5, '2018-05-12 22:30:00', '2018-05-13 01:30:00', 4);
  175. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (5, '2018-05-13 01:30:00', '2018-05-13 04:30:00', 4);
  176. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (6, '2018-05-12 13:30:00', '2018-05-12 16:30:00', 2);
  177. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (6, '2018-05-12 16:30:00', '2018-05-12 19:30:00', 2);
  178. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (6, '2018-05-12 19:30:00', '2018-05-12 22:30:00', 2);
  179. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (6, '2018-05-12 22:30:00', '2018-05-13 01:30:00', 2);
  180. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (6, '2018-05-13 01:30:00', '2018-05-13 04:30:00', 2);
  181. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (9, '2018-05-12 19:30:00', '2018-05-12 22:30:00', 4);
  182. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (9, '2018-05-12 22:30:00', '2018-05-13 01:30:00', 4);
  183. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (9, '2018-05-13 01:30:00', '2018-05-13 04:30:00', 4);
  184. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (7, '2018-05-12 13:30:00', '2018-05-12 16:30:00', 2);
  185. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (7, '2018-05-12 16:30:00', '2018-05-12 19:30:00', 2);
  186. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (8, '2018-05-12 19:30:00', '2018-05-12 22:30:00', 4);
  187. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (8, '2018-05-12 22:30:00', '2018-05-13 01:30:00', 4);
  188. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (7, '2018-05-12 19:30:00', '2018-05-12 22:00:00', 3);
  189. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (8, '2018-05-13 01:30:00', '2018-05-13 04:30:00', 4);
  190. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (10, '2018-05-12 12:00:00', '2018-05-12 14:00:00', 2);
  191. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (10, '2018-05-12 19:30:00', '2018-05-12 22:30:00', 2);
  192. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (4, '2018-05-13 13:00:00', '2018-05-13 14:00:00', 4);
  193. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (5, '2018-05-13 14:00:00', '2018-05-13 17:00:00', 2);
  194. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (5, '2018-05-13 17:00:00', '2018-05-13 20:00:00', 2);
  195. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (5, '2018-05-13 20:00:00', '2018-05-13 22:00:00', 2);
  196. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (6, '2018-05-13 14:00:00', '2018-05-13 17:00:00', 2);
  197. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (6, '2018-05-13 17:00:00', '2018-05-13 20:00:00', 2);
  198. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (6, '2018-05-13 20:00:00', '2018-05-13 22:00:00', 2);
  199. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (7, '2018-05-13 14:00:00', '2018-05-13 17:00:00', 2);
  200. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (7, '2018-05-13 17:00:00', '2018-05-13 19:00:00', 3);
  201. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (8, '2018-05-13 19:00:00', '2018-05-13 22:00:00', 2);
  202. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (10, '2018-05-13 12:00:00', '2018-05-13 14:00:00', 2);
  203. INSERT INTO participer_thsf_turns (role_id, start_time, end_time, num_slot) VALUES (10, '2018-05-13 19:30:00', '2018-05-13 21:30:00', 2);