-- Generate_Score_URLs.sql

-- $Id$
--
-- $Log$

DELIMITER //

DROP PROCEDURE Generate_Score_URLs //

CREATE PROCEDURE Generate_Score_URLs()
  BEGIN
    DECLARE done INT DEFAULT 0;
    DECLARE id_char VARCHAR(100);
    DECLARE id_num VARCHAR(4);
    DECLARE id_num_num INT;
    DECLARE id_char_numpart VARCHAR(100);
    DECLARE id_char_suffix VARCHAR(100);
    DECLARE dir1 VARCHAR(10);
    DECLARE dir2 VARCHAR(10);
    DECLARE dir3 VARCHAR(10);
    DECLARE curr_score_file VARCHAR(20);
    DECLARE curr_score_url VARCHAR(200);
    DECLARE c1 CURSOR FOR
      SELECT 
        ID
      FROM Folop_Viol_Scores;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
    OPEN c1;
    REPEAT
      FETCH c1 INTO id_char;
      SET id_char_suffix = SUBSTR(id_char,5);
      SET id_num = LEFT(id_char,4);
      SET id_num_num = id_num;
      SET id_char_numpart = LPAD(id_num,4,'0');
      IF id_num_num < 1000 THEN
        SET dir1 = '0000';
      ELSE
        SET dir1 = ROUND(id_num_num,-3);
      END IF;
      IF id_num_num < 100 THEN
        SET dir2 = '0000';
      ELSE
        SET dir2 = ROUND(id_num_num,-2);
        SET dir2 = LPAD(dir2,4,'0');
      END IF;
      IF id_num_num < 10 THEN
        SET dir3 = '0000';
      ELSE
        SET dir3 = ROUND(id_num_num,-1);
        SET dir3 = LPAD(dir3,4,'0');
      END IF;
      SET curr_score_file = CONCAT(id_char_numpart,id_char_suffix,'.pdf');
      SET curr_score_url = CONCAT(dir1,'/',dir2,'/',dir3,'/',curr_score_file);
      UPDATE Folop_Viol_Scores
      SET
        Score_URL = curr_score_url
      WHERE ID = id_char;
    UNTIL done END REPEAT;
    CLOSE c1;
  END //

DELIMITER ;