Subversion Repositories eFlore/Projets.eflore-projets

Rev

Rev 880 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 880 Rev 890
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