load_mectb10.sql 9.25 KB
Newer Older
1
/* Copyright (C) 2009-2012 Centro de Computacao Cientifica e Software Livre
2 3 4 5 6 7 8 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
 * Departamento de Informatica - Universidade Federal do Parana - C3SL/UFPR
 *
 * This file is part of database
 *
 * database is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301,
 * USA.
 */

/* Function to load the table mectb10_dm_inventario
 *
 * Receives as input the loading date.
 *
 * Returns an array with the schools id for each new machine.
 */
CREATE OR REPLACE FUNCTION load_mectb10(date DATE) RETURNS integer[] AS $$
DECLARE
    r_last_inventory mectb10_dm_inventario%ROWTYPE;
    r_first_inventory mectb10_dm_inventario%ROWTYPE;
    hash_last_inventory VARCHAR;
    hash_current_inventory VARCHAR;

    city VARCHAR(100);
    state VARCHAR(100);
    region VARCHAR(12);
    name_school VARCHAR(100);
    inep VARCHAR(18);
    mac VARCHAR(18);
    month_name VARCHAR(9);
42 43
    proc TEXT;
    so TEXT;
44 45 46
    total_disk_size INTEGER;
    size_original_disk INTEGER;
    size_original_mem INTEGER;
47
    disk_used INTEGER;
48 49 50 51 52 53 54 55 56 57 58 59 60
    total_disk_used INTEGER;
    memory_size INTEGER;
    year INTEGER;
    month INTEGER;
    day INTEGER;
    id_school INTEGER;
    id_date INTEGER;
    id_machine INTEGER;
    id_project INTEGER;
    id_catalog INTEGER;
    num_catalog INTEGER;
    alert_hd BIT;
    alert_mem BIT;
61 62 63 64 65 66 67 68 69 70 71 72
    alert BIT;

    cursor_inv CURSOR (date_id INTEGER, maq_id INTEGER) IS
    SELECT inv_disco_usado, inv_alarme, cat_componente, cat_modelo, cat_descricao
    FROM mectb07_inventario_fact, mectb02_catalogo_dim
    WHERE inv_cat_id=cat_id AND inv_dat_id=date_id AND inv_maq_id=maq_id AND
    inv_id_status='a';

    component VARCHAR(20);
    model VARCHAR(50);
    description VARCHAR;
    end_cursor_inv BOOLEAN;
73 74 75 76 77 78 79 80 81 82 83 84 85 86

    -- Vector that will contain the id of schools of the new machines
    array_id_esc INTEGER[];
    -- Indexing variable of the array_id_esc
    i INTEGER;

BEGIN
    year := date_part('year',date);
    month := date_part('month', date);
    day := date_part('day', date);
    -- Select the id of the current date
    SELECT dat_id INTO id_date FROM mectb03_data_dim WHERE dat_data=date;

    -- Function to get month name
87
    SELECT get_month_name(month) INTO month_name;
88 89 90 91 92 93

    -- Index arrow of array_id_esc
    i := 1;

    -- Runs through the inventory table by selecting the machines that sent
    -- the current date inventory
94 95
    FOR id_machine IN SELECT DISTINCT inv_maq_id FROM "mectb07_inventario_fact"
    WHERE inv_dat_id=id_date
96
    LOOP
97 98 99 100 101 102 103 104 105 106

        total_disk_size := 0;
        total_disk_used := 0;

        -- Select the id of the school, mac-adress and id project
        SELECT maq_esc_id, maq_mac, maq_projeto INTO id_school, mac, id_project
        FROM "mectb04_maquina_dim" WHERE maq_id=id_machine;

        OPEN cursor_inv(id_date, id_machine);

107 108
        FETCH cursor_inv INTO disk_used, alert, component, model, description;
        end_cursor_inv := FOUND;
109

110 111
        WHILE end_cursor_inv
        LOOP
112 113 114 115 116 117 118 119 120 121

            IF component = 'HD' THEN
                total_disk_size := total_disk_size + (cast(description AS INTEGER));
                total_disk_used := total_disk_used + disk_used;
            ELSIF component = 'PROC' THEN
                proc := model;
            ELSIF component = 'SO' THEN
                so := description;
            ELSIF component = 'MEM' THEN
                memory_size := (cast(model AS INTEGER));
122 123
            END IF;

124 125 126
            FETCH cursor_inv INTO disk_used, alert, component, model, description;
            end_cursor_inv := FOUND;

127 128 129 130 131 132 133 134 135 136 137
        END LOOP;

        CLOSE cursor_inv;

        OPEN cursor_inv(id_date, id_machine);
        FETCH cursor_inv INTO disk_used, alert, component, model, description;
        CLOSE cursor_inv;

        -- Selects information from the school in this machine
        SELECT esc_inep, esc_regiao, esc_uf, esc_municipio, esc_nome
        INTO inep, region, state, city, name_school FROM "mectb01_escola_dim"
138
        WHERE esc_id=id_school;
139 140 141 142 143 144 145 146 147 148 149

        -- Selects the first inventory registered machine
        SELECT * INTO r_first_inventory FROM mectb10_dm_inventario
        WHERE inv_maquina=mac AND inv_inep=inep ORDER BY inv_data ASC LIMIT 1;

        -- Selects the last inventory registered machine
        SELECT * INTO r_last_inventory FROM mectb10_dm_inventario
        WHERE inv_maquina=mac AND inv_inep=inep ORDER BY inv_data DESC LIMIT 1;

        -- Check whether the alarm is connected machine, if the machine
        -- was changed, otherwise a new machine
150
        IF (alert = 'B1') THEN
151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179

           -- Selects the size original disk
           SELECT inv_tam_disco INTO size_original_disk FROM mectb10_dm_inventario
           WHERE inv_maquina=mac AND inv_inep=inep ORDER BY inv_data ASC LIMIT 1;

           -- If moved the disk size between current and last inventory inserted
           IF (total_disk_size < (size_original_disk*0.9)) THEN
               alert_hd := 'B1';
           ELSE
               alert_hd := 'B0';
           END IF;

           -- Selects the size original memory
           SELECT inv_qtde_mem INTO size_original_mem FROM mectb10_dm_inventario
           WHERE inv_maquina=mac AND inv_inep=inep ORDER BY inv_data ASC LIMIT 1;

           -- If moved amount of memory between current and previous inventory
           -- registered
           IF (memory_size < (size_original_mem*0.9)) THEN
               alert_mem := 'B1';
           ELSE
               alert_mem := 'B0';
           END IF;

           -- If last inventory not existed
           IF r_last_inventory.inv_data IS NULL THEN
               INSERT INTO mectb10_dm_inventario VALUES (date, year, month,
               month_name, day, inep, mac, region, state, city, name_school,
               so, proc, memory_size, total_disk_size, total_disk_used,
180
               alert_hd, alert_mem, id_project);
181 182 183 184 185 186 187
           ELSE
               hash_last_inventory := md5(r_last_inventory.inv_so) ||
               md5(r_last_inventory.inv_modelo_proc) ||
               md5(cast(r_last_inventory.inv_qtde_mem AS VARCHAR)) ||
               md5(cast(r_last_inventory.inv_tam_disco AS VARCHAR));

               hash_current_inventory := md5(so) || md5(proc) ||
188
               md5(cast(memory_size AS VARCHAR)) || md5(cast(total_disk_size AS VARCHAR));
189 190 191 192 193 194 195 196

               -- If current and last invetory not are equal
               IF ( NULLIF(hash_current_inventory, hash_last_inventory)
                   IS NOT NULL ) THEN

                   INSERT INTO mectb10_dm_inventario VALUES (date, year, month,
                   month_name, day, inep, mac, region, state, city, name_school,
                   so, proc, memory_size, total_disk_size, total_disk_used,
197
                   alert_hd, alert_mem, id_project);
198 199 200 201 202
               ELSE
                   UPDATE mectb10_dm_inventario SET inv_data=date, inv_ano=year,
                   inv_mes=month, inv_mes_nm=month_name, inv_dia=day,
                   inv_disco_ocupado=total_disk_used
                   WHERE inv_maquina=mac AND inv_inep=inep AND
203 204
                   inv_data=r_last_inventory.inv_data AND
                   inv_projeto=id_project;
205 206 207 208 209
               END IF;
            END IF;
        ELSE
            alert_hd := 'B0';
            alert_mem := 'B0';
210

211 212
            -- First contacted the machine
            IF r_first_inventory.inv_data IS NULL THEN
213

214 215 216
                INSERT INTO mectb10_dm_inventario VALUES (date, year, month,
                month_name, day, inep, mac, region, state, city, name_school,
                so, proc, memory_size, total_disk_size, total_disk_used,
217
                alert_hd, alert_mem, id_project);
218

219 220
                array_id_esc[i] := id_school;
                i := i+1;
221
            ELSE
222 223 224 225
                IF (r_last_inventory.inv_alerta_hd = 'B1') OR
                   (r_last_inventory.inv_alerta_mem = 'B1') OR
                   (r_last_inventory.inv_tam_disco > total_disk_size) OR
                   (r_last_inventory.inv_qtde_mem > memory_size) THEN
226 227 228 229

                    INSERT INTO mectb10_dm_inventario VALUES (date, year, month,
                    month_name, day, inep, mac, region, state, city, name_school,
                    so, proc, memory_size, total_disk_size, total_disk_used,
230
                    alert_hd, alert_mem, id_project);
231
                ELSE
232 233 234 235
                    UPDATE mectb10_dm_inventario SET inv_data=date, inv_ano=year,
                    inv_mes=month, inv_mes_nm=month_name, inv_dia=day,
                    inv_disco_ocupado=total_disk_used
                    WHERE inv_maquina=mac AND inv_inep=inep AND
236 237
                    inv_data=r_last_inventory.inv_data AND
                    inv_projeto=id_project;
238
                END IF;
239 240 241 242 243 244 245 246
            END IF;
        END IF;

    END LOOP;
    -- Return vector with id of schools of each new machine
    RETURN array_id_esc;
END;
$$ LANGUAGE plpgsql;