Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 819 | Rev 892 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 819 Rev 882
Line 16... Line 16...
16 
        DECLARE c INT;
 16 
        DECLARE c INT;
17 
        SET c = (SELECT MAX(ordre) + 1 FROM `BASEEDIT`.`cel_obs` where ce_utilisateur = s1);
 17 
        SET c = (SELECT MAX(ordre) + 1 FROM `BASEEDIT`.`cel_obs` where ce_utilisateur = s1);
18 
        RETURN c;
 18 
        RETURN c;
19 
  END
 19 
  END
20 
|
 20 
|
21 
-- SELECT next_ordre("vincent.vuillermoz@apprenti.isa-lille.fr");
 21 
-- SELECT next_ordre("");
Line 22... Line 22...
22 
 
 22 
 
23 
CREATE PROCEDURE ordre_need_update(IN _s1 VARCHAR(255), OUT _ordre INT, OUT _c INT, OUT _min_obs INT)
 23 
CREATE PROCEDURE ordre_need_update(IN _s1 VARCHAR(255), OUT _ordre INT, OUT _c INT, OUT _min_obs INT)
24 
  BEGIN
 24 
  BEGIN
25 
        SELECT ordre, count(ordre), MIN(id_observation) INTO _ordre, _c, _min_obs FROM `BASEEDIT`.`cel_obs` WHERE ce_utilisateur = _s1 GROUP BY ordre HAVING COUNT(ordre) > 1 LIMIT 1;
 25 
        SELECT ordre, count(ordre), MIN(id_observation) INTO _ordre, _c, _min_obs FROM `BASEEDIT`.`cel_obs` WHERE ce_utilisateur = _s1 GROUP BY ordre HAVING COUNT(ordre) > 1 LIMIT 1;
26 
  END
 26 
  END
27 
|
 27 
|
Line 28... Line 28...
28 
-- SELECT ordre_need_update("vincent.vuillermoz@apprenti.isa-lille.fr");
 28 
-- SELECT ordre_need_update("");
29 
 
 29 
 
30 
CREATE PROCEDURE update_ordre_user(IN _s1 VARCHAR(255))
 30 
CREATE PROCEDURE update_ordre_user(IN _s1 VARCHAR(255))
31 
  BEGIN
 31 
  BEGIN
Line 52... Line 52...
52 
       CALL ordre_need_update(_s1, @o, @c, @minobs);
 52 
       CALL ordre_need_update(_s1, @o, @c, @minobs);
53 
       -- SELECT "====X", @o, @c;
 53 
       -- SELECT "====X", @o, @c;
54 
    END WHILE;
 54 
    END WHILE;
55 
  END
 55 
  END
56 
|
 56 
|
57 
-- CALL update_ordre_user("vincent.vuillermoz@apprenti.isa-lille.fr");
 57 
-- CALL update_ordre_user("");
Line 58... Line 58...
58 
 
 58 
 
59 
 
 59 
 
60 
CREATE PROCEDURE update_ordre_users()
 60 
CREATE PROCEDURE update_ordre_users()
61 
  BEGIN
 61 
  BEGIN
62 
    DECLARE _nom VARCHAR(255);
 62 
    DECLARE _nom VARCHAR(255);
Line 63... Line 63...
63 
    DECLARE subst INT DEFAULT 0;
 63 
    DECLARE subst INT DEFAULT 0;
64 
    DECLARE done INT DEFAULT 1;
 64 
    DECLARE done INT DEFAULT 1;
- 
 
 65 
 
- 
 
 66 
    -- temp table
65 
 
 67 
    CREATE TEMPORARY TABLE IF NOT EXISTS _temp_users (ce_utilisateur VARCHAR(255)) ENGINE=MEMORY AS \
66 
    -- temp table
 68 
    -- the following fails (http://dba.stackexchange.com/questions/48231 ?)
Line 67... Line 69...
67 
    CREATE TEMPORARY TABLE IF NOT EXISTS _temp_users (ce_utilisateur VARCHAR(255)) ENGINE=MEMORY AS \
 69 
    -- ( SELECT DISTINCT ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ce_utilisateur, ordre HAVING COUNT(*) > 1 );
68 
    ( SELECT DISTINCT ce_utilisateur FROM `BASEEDIT`.`cel_obs`
 70 
    ( SELECT DISTINCT ce_utilisateur FROM `BASEEDIT`.`cel_obs` WHERE ce_utilisateur IN \
69 
      GROUP BY ce_utilisateur, ordre HAVING COUNT(*) > 1 );
 71 
      (SELECT ce_utilisateur FROM `BASEEDIT`.`cel_obs` GROUP BY ce_utilisateur, ordre HAVING COUNT(1) > 1) );
70 
 
 72