load_mectb12.sql 15.9 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
 * 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 mectb12_dm_instaladas_ag
 *
 * Receives as input the loading date.
 */
CREATE OR REPLACE FUNCTION load_mectb12(date DATE) RETURNS void AS $$
DECLARE
    state VARCHAR(100);
    region VARCHAR(12);
    month_name VARCHAR(9);
    year INTEGER;
    month INTEGER;
33

34 35 36 37 38 39
    current_number_installed INTEGER;
    current_number_modified_hd INTEGER;
    current_number_modified_mem INTEGER;
    last_number_installed INTEGER;
    last_number_modified_hd INTEGER;
    last_number_modified_mem INTEGER;
40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77

    current_number_installed_uca_class INTEGER;
    current_number_modified_hd_uca_class INTEGER;
    current_number_modified_mem_uca_class INTEGER;
    last_number_installed_uca_class INTEGER;
    last_number_modified_hd_uca_class INTEGER;
    last_number_modified_mem_uca_class INTEGER;

    current_number_installed_uca_server INTEGER;
    current_number_modified_hd_uca_server INTEGER;
    current_number_modified_mem_uca_server INTEGER;
    last_number_installed_uca_server INTEGER;
    last_number_modified_hd_uca_server INTEGER;
    last_number_modified_mem_uca_server INTEGER;

    current_number_installed_projector INTEGER;
    current_number_modified_hd_projector INTEGER;
    current_number_modified_mem_projector INTEGER;
    last_number_installed_projector INTEGER;
    last_number_modified_hd_projector INTEGER;
    last_number_modified_mem_projector INTEGER;

    num_provided_machines INTEGER;
    num_provided_machines_uca_class INTEGER;
    num_provided_machines_uca_server INTEGER;
    num_provided_machines_projector INTEGER;

    record_installed RECORD;
    record_modified RECORD;
    record_last_inst RECORD;

    cursor_installed REFCURSOR;
    cursor_modified REFCURSOR;
    cursor_last_inst REFCURSOR;

    end_cursor_installed BOOLEAN;
    end_cursor_modified BOOLEAN;
    end_cursor_last_inst BOOLEAN;
78 79 80 81 82 83 84 85

BEGIN
    year := date_part('year',date);
    month := date_part('month', date);

    -- Function to get month name
    SELECT get_month_name(month) INTO month_name;

86
    --
87 88 89 90 91 92
    FOR state, region IN SELECT DISTINCT(esc_uf), esc_regiao FROM mectb01_escola_dim
    LOOP
        current_number_installed := 0;
        current_number_modified_hd := 0;
        current_number_modified_mem := 0;

93 94 95
        current_number_installed_uca_class := 0;
        current_number_modified_hd_uca_class := 0;
        current_number_modified_mem_uca_class := 0;
96

97 98 99 100 101 102 103 104 105 106 107 108 109
        current_number_installed_uca_server := 0;
        current_number_modified_hd_uca_server := 0;
        current_number_modified_mem_uca_server := 0;

        current_number_installed_projector := 0;
        current_number_modified_hd_projector := 0;
        current_number_modified_mem_projector := 0;

        -- Select the installed machines
        OPEN cursor_installed FOR
        SELECT max(inv_data), inv_inep AS inep, inv_maquina AS maquina,
            inv_projeto AS projeto
        FROM
110
            mectb10_dm_inventario
111 112
        WHERE
            inv_estado=state GROUP BY inep, maquina, projeto;
113

114 115
        FETCH cursor_installed INTO record_installed;
        end_cursor_installed := FOUND;
116

117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143
        WHILE end_cursor_installed
        LOOP
            IF(record_installed.projeto = 0) THEN
                current_number_installed := current_number_installed + 1;
            ELSIF(record_installed.projeto = 1) THEN
                current_number_installed_uca_class := current_number_installed_uca_class + 1;
            ELSIF(record_installed.projeto = 2) THEN
                current_number_installed_uca_server := current_number_installed_uca_server + 1;
            ELSIF(record_installed.projeto = 3) THEN
                current_number_installed_projector := current_number_installed_projector + 1;
            END IF;

            FETCH cursor_installed INTO record_installed;
            end_cursor_installed := FOUND;

        END LOOP;

        CLOSE cursor_installed;
        -----------------------------------------------------------------------

        -- Select the machines with HD or memory modified
        OPEN cursor_modified FOR
        SELECT inv_projeto AS projeto, inv_alerta_hd AS hd, inv_alerta_mem AS mem
        FROM
            (SELECT MAX(inv_data) AS data, inv_maquina AS maquina, inv_inep
            AS inep FROM mectb10_dm_inventario WHERE inv_estado='SC' GROUP
            BY inep, maquina) AS temp,
144
            mectb10_dm_inventario
145 146 147 148 149
        WHERE
            temp.data=inv_data AND
            temp.maquina=inv_maquina AND
            temp.inep=inv_inep AND
            (inv_alerta_hd='B1' OR inv_alerta_mem='B1');
150

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 180 181 182 183 184 185 186 187 188 189
        FETCH cursor_modified INTO record_modified;
        end_cursor_modified := FOUND;

        WHILE end_cursor_modified
        LOOP
            IF(record_modified.hd = 'B1') THEN
                IF(record_modified.projeto = 0) THEN
                    current_number_modified_hd :=
                    current_number_modified_hd + 1;
                ELSIF(record_modified.projeto = 1) THEN
                    current_number_modified_hd_uca_class :=
                    current_number_modified_hd_uca_class + 1;
                ELSIF(record_modified.projeto = 2) THEN
                    current_number_modified_hd_uca_server :=
                    current_number_modified_hd_uca_server + 1;
                ELSIF(record_modified.projeto = 3) THEN
                    current_number_modified_hd_projector :=
                    current_number_modified_hd_projector + 1;
                END IF;
            END IF;

            IF(record_modified.mem = 'B1') THEN
                IF(record_modified.projeto = 0) THEN
                    current_number_modified_mem :=
                    current_number_modified_mem + 1;
                ELSIF(record_modified.projeto = 1) THEN
                    current_number_modified_mem_uca_class :=
                    current_number_modified_mem_uca_class + 1;
                ELSIF(record_modified.projeto = 2) THEN
                    current_number_modified_mem_uca_server :=
                    current_number_modified_mem_uca_server + 1;
                ELSIF(record_modified.projeto = 3) THEN
                    current_number_modified_mem_projector :=
                    current_number_modified_mem_projector + 1;
                END IF;
            END IF;

            FETCH cursor_modified INTO record_modified;
            end_cursor_modified := FOUND;
190 191 192

        END LOOP;

193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229
        CLOSE cursor_modified;
        -----------------------------------------------------------------------

        -- Selects the last entered information

        OPEN cursor_last_inst FOR
        SELECT inst_instaladas AS instaladas, inst_hd_modificado AS hd,
            inst_mem_modificada AS mem, inst_projeto AS projeto
        FROM
            mectb12_dm_instaladas_ag
        WHERE
            inst_mes=month AND
            inst_ano=year AND
            inst_estado=state;

        FETCH cursor_last_inst INTO record_last_inst;
        end_cursor_last_inst := FOUND;

        WHILE end_cursor_last_inst
        LOOP
            IF(record_last_inst.projeto = 0) THEN

                last_number_installed := record_last_inst.instaladas;
                last_number_modified_hd := record_last_inst.hd;
                last_number_modified_mem := record_last_inst.mem;

            ELSIF(record_last_inst.projeto = 1) THEN

                last_number_installed_uca_class := record_last_inst.instaladas;
                last_number_modified_hd_uca_class := record_last_inst.hd;
                last_number_modified_mem_uca_class := record_last_inst.mem;

            ELSIF(record_last_inst.projeto = 2) THEN

                last_number_installed_uca_server := record_last_inst.instaladas;
                last_number_modified_hd_uca_server := record_last_inst.hd;
                last_number_modified_mem_uca_server := record_last_inst.mem;
230 231


232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248
            ELSIF(record_last_inst.projeto = 3) THEN

                last_number_installed_projector := record_last_inst.instaladas;
                last_number_modified_hd_projector := record_last_inst.hd;
                last_number_modified_mem_projector := record_last_inst.mem;

            END IF;

            FETCH cursor_last_inst INTO record_last_inst;
            end_cursor_last_inst := FOUND;

        END LOOP;

        CLOSE cursor_last_inst;
        -----------------------------------------------------------------------

        -- Machines ProInfoData
249 250 251 252

        -- Checks if there is information already entered the current month
        IF (last_number_installed IS NULL) THEN

253 254
            SELECT sum(esc_maq_prev) INTO num_provided_machines FROM mectb01_escola_dim
            WHERE esc_uf=state;
255

256
            IF ( num_provided_machines IS NULL) THEN
257 258
                INSERT INTO mectb12_dm_instaladas_ag VALUES (date, year, month,
                month_name, region, state, current_number_installed, 0,
259 260
                current_number_modified_hd, current_number_modified_mem,
                0);
261 262 263
            ELSE
                INSERT INTO mectb12_dm_instaladas_ag VALUES (date, year, month,
                month_name, region, state, current_number_installed,
264 265
                num_provided_machines, current_number_modified_hd,
                current_number_modified_mem, 0);
266 267 268
            END IF;

        ELSIF ( (current_number_installed > last_number_installed) OR
269 270 271
                (current_number_modified_hd != last_number_modified_hd) OR
                (current_number_modified_mem != last_number_modified_mem) )
                THEN
272 273 274 275 276 277

            UPDATE mectb12_dm_instaladas_ag SET
            inst_instaladas=current_number_installed,
            inst_hd_modificado=current_number_modified_hd,
            inst_mem_modificada=current_number_modified_mem,
            inst_data=date
278 279
            WHERE inst_mes=month AND inst_ano=year AND inst_estado=state AND
            inst_projeto=0;
280 281

        END IF;
282
        -----------------------------------------------------------------------
283

284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392
        -- Machines Uca Clamates

        -- Checks if there is information already entered the current month
        IF (last_number_installed_uca_class IS NULL) THEN

            SELECT sum(esc_maq_prev) INTO num_provided_machines_uca_class FROM mectb01_escola_dim
            WHERE esc_uf=state;

            IF ( num_provided_machines_uca_class IS NULL) THEN
                INSERT INTO mectb12_dm_instaladas_ag VALUES (date, year, month,
                month_name, region, state, current_number_installed_uca_class, 0,
                current_number_modified_hd_uca_class, current_number_modified_mem_uca_class,
                1);
            ELSE
                INSERT INTO mectb12_dm_instaladas_ag VALUES (date, year, month,
                month_name, region, state, current_number_installed_uca_class,
                num_provided_machines_uca_class, current_number_modified_hd_uca_class,
                current_number_modified_mem_uca_class, 1);
            END IF;

        ELSIF ( (current_number_installed_uca_class > last_number_installed_uca_class) OR
                (current_number_modified_hd_uca_class != last_number_modified_hd_uca_class) OR
                (current_number_modified_mem_uca_class != last_number_modified_mem_uca_class) )
                THEN

            UPDATE mectb12_dm_instaladas_ag SET
            inst_instaladas=current_number_installed_uca_class,
            inst_hd_modificado=current_number_modified_hd_uca_class,
            inst_mem_modificada=current_number_modified_mem_uca_class,
            inst_data=date
            WHERE inst_mes=month AND inst_ano=year AND inst_estado=state AND
            inst_projeto=1;

        END IF;
        -----------------------------------------------------------------------

        -- Uca Server

        -- Checks if there is information already entered the current month
        IF (last_number_installed_uca_server IS NULL) THEN

            SELECT sum(esc_maq_prev) INTO num_provided_machines_uca_server FROM mectb01_escola_dim
            WHERE esc_uf=state;

            IF ( num_provided_machines_uca_server IS NULL) THEN
                INSERT INTO mectb12_dm_instaladas_ag VALUES (date, year, month,
                month_name, region, state, current_number_installed_uca_server, 0,
                current_number_modified_hd_uca_server, current_number_modified_mem_uca_server,
                2);
            ELSE
                INSERT INTO mectb12_dm_instaladas_ag VALUES (date, year, month,
                month_name, region, state, current_number_installed_uca_server,
                num_provided_machines_uca_server, current_number_modified_hd_uca_server,
                current_number_modified_mem_uca_server, 2);
            END IF;

        ELSIF ( (current_number_installed_uca_server > last_number_installed_uca_server) OR
                (current_number_modified_hd_uca_server != last_number_modified_hd_uca_server) OR
                (current_number_modified_mem_uca_server != last_number_modified_mem_uca_server) )
                THEN

            UPDATE mectb12_dm_instaladas_ag SET
            inst_instaladas=current_number_installed_uca_server,
            inst_hd_modificado=current_number_modified_hd_uca_server,
            inst_mem_modificada=current_number_modified_mem_uca_server,
            inst_data=date
            WHERE inst_mes=month AND inst_ano=year AND inst_estado=state AND
            inst_projeto=2;

        END IF;
        -----------------------------------------------------------------------

        -- Projector

        -- Checks if there is information already entered the current month
        IF (last_number_installed_projector IS NULL) THEN

            SELECT sum(esc_maq_prev) INTO num_provided_machines_projector FROM mectb01_escola_dim
            WHERE esc_uf=state;

            IF ( num_provided_machines_projector IS NULL) THEN
                INSERT INTO mectb12_dm_instaladas_ag VALUES (date, year, month,
                month_name, region, state, current_number_installed_projector, 0,
                current_number_modified_hd_projector, current_number_modified_mem_projector,
                3);
            ELSE
                INSERT INTO mectb12_dm_instaladas_ag VALUES (date, year, month,
                month_name, region, state, current_number_installed_projector,
                num_provided_machines_projector, current_number_modified_hd_projector,
                current_number_modified_mem_projector, 3);
            END IF;

        ELSIF ( (current_number_installed_projector > last_number_installed_projector) OR
                (current_number_modified_hd_projector != last_number_modified_hd_projector) OR
                (current_number_modified_mem_projector != last_number_modified_mem_projector) )
                THEN

            UPDATE mectb12_dm_instaladas_ag SET
            inst_instaladas=current_number_installed_projector,
            inst_hd_modificado=current_number_modified_hd_projector,
            inst_mem_modificada=current_number_modified_mem_projector,
            inst_data=date
            WHERE inst_mes=month AND inst_ano=year AND inst_estado=state AND
            inst_projeto=3;

        END IF;
        -----------------------------------------------------------------------

    END LOOP;
393 394 395
    RETURN;
END;
$$ LANGUAGE plpgsql;