apisub_functions.sql 1.92 KB
Newer Older
1
CREATE OR REPLACE FUNCTION unaccent_string(TEXT) RETURNS TEXT AS $$
2 3 4 5 6 7 8
  SELECT translate(
      $1,
      'âãäåÁÂÃÄÅèééêëÈÉÉÊËìíîïìÌÍÎÏÌóôõöÒÓÔÕÖùúûüÙÚÛÜ',
      'aaaaAAAAAeeeeeEEEEEiiiiiIIIIIooooOOOOOuuuuUUUU'
  );
$$ LANGUAGE SQL;

Stephane Crozat's avatar
Stephane Crozat committed
9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
CREATE OR REPLACE FUNCTION nb_api(TEXT, TEXT) RETURNS INTEGER AS $$
/* Returns number of api for one week and year */
DECLARE
  w ALIAS FOR $1;
  y ALIAS FOR $2;
  c INTEGER;
BEGIN
  SELECT COUNT(*) INTO c
  FROM vapi
  WHERE week=w AND year=y;
  RETURN c;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION nb_sub(TEXT, TEXT, TEXT) RETURNS INTEGER AS $$
/* Returns number of subsciption for one student for one week and year */
DECLARE
  student ALIAS FOR $1;
  w ALIAS FOR $2;
  y ALIAS FOR $3;
  c INTEGER;
BEGIN
  SELECT COUNT(*) INTO c
  FROM vsubscription
  WHERE utclogin=student AND week=w AND year=y;
  RETURN c;
END;
$$ LANGUAGE PLPGSQL;

CREATE OR REPLACE FUNCTION indicator(TEXT, TEXT, TEXT) RETURNS INTEGER AS $$
/* Returns indicator one student for one week and year */
DECLARE
  student ALIAS FOR $1;
  w ALIAS FOR $2;
  y ALIAS FOR $3;
  nb_sub FLOAT;
  nb_api FLOAT;
BEGIN
  nb_sub = nb_sub(student,w,y);
  nb_api = nb_api(w,y);
  IF nb_sub = 0 THEN
    RETURN -1;
  ELSIF nb_sub = 1 THEN
    RETURN 100;
  ELSE
    RETURN 100 * (1 - (nb_sub / nb_api));
  END IF;
END;
$$ LANGUAGE PLPGSQL;

59
CREATE OR REPLACE FUNCTION is_available(TEXT, TEXT, TEXT) RETURNS BOOLEAN AS $$
60
/* Returns True if student has no api validated for same week and year */
61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77
DECLARE
  student ALIAS FOR $1;
  w ALIAS FOR $2;
  y ALIAS FOR $3;
  c INTEGER;
BEGIN
  SELECT COUNT(*) INTO c
  FROM vsubscription
  WHERE utclogin=student AND week=w AND year=y AND validation;
  IF c = 0 THEN
    RETURN 'TRUE';
  ELSE
    RETURN 'FALSE';
  END IF;
END;
$$ LANGUAGE PLPGSQL;

78
/** TODO **/
79
CREATE OR REPLACE FUNCTION semester(DATE) RETURNS CHAR(1) AS $$
80 81 82 83
BEGIN
  RETURN 'E';
END;
$$ LANGUAGE PLPGSQL;