--Função que atualiza os dados das tabelas que serão importadas da biblioteca participante para a integradora através da biblioteca virtual
CREATE OR REPLACE FUNCTION atualizaDadosBibliotecaVirtual(p_zipname varchar)
RETURNS bool as $BODY$
    DECLARE
		v_sequenceLibrary int;
		v_sequence int;
		v_maxLibraryUnit int;
		v_maxcontrolNumber int;
		v_maxItemNumber varchar;
		v_library int;
		v_controlNumber int;
	BEGIN
                v_maxcontrolNumber = MAX(controlnumber) FROM gtcmaterial;
                v_maxcontrolNumber = COALESCE(v_maxcontrolNumber, 0);
                
		v_maxLibraryUnit = MAX(libraryunitid) FROM gtclibraryunit;
                v_maxLibraryUnit = COALESCE(v_maxLibraryUnit, 0);

		v_maxItemNumber = MAX(itemnumber) FROM gtcexemplarycontrol;
                v_maxItemNumber = COALESCE(v_maxItemNumber, '0');
		
                EXECUTE 'CREATE TEMP SEQUENCE seq_tmpgtclibraryunit START 1';
                EXECUTE 'CREATE TEMP SEQUENCE seq_tmpgtcmaterial START 1';
                
                --Atualiza a tabela de Unidade de biblioteca
                FOR v_library IN SELECT libraryunitid FROM tmpgtclibraryunit
		LOOP
			v_sequenceLibrary = nextval('seq_tmpgtclibraryunit');
                       
                        EXECUTE 'UPDATE tmpgtclibraryunit SET libraryname = ''' || p_zipname || ' - ' || ''' || trim(libraryname) WHERE libraryunitid =' || v_library;
			EXECUTE 'UPDATE tmpgtclibraryunit SET libraryunitid =' || v_maxLibraryUnit + v_sequenceLibrary || ' WHERE libraryunitid = ' || v_library;
                        END LOOP;
                --Atualiza as tabelas de Materiais e de Exemplares (gtcmaterial, gtcmaterialcontrol, gtcexemplarycontrol)
		FOR v_controlNumber IN SELECT controlnumber FROM tmpgtcmaterialcontrol
		LOOP
			v_sequence = nextval('seq_tmpgtcmaterial');

                        EXECUTE 'UPDATE tmpgtcmaterial SET content =''' || v_maxLibraryUnit + v_sequence || ''' WHERE fieldid = ' || '''949''' || ' AND subfieldid = ' || '''b''' || ' AND controlNumber =' || v_controlNumber;
			EXECUTE 'UPDATE tmpgtcmaterial SET content = lpad(''' || v_maxItemNumber || v_sequence || ''', 8 , \'0\') WHERE fieldid = ' || '''949''' || ' AND subfieldid = ' || '''a''' || ' AND controlnumber =' || v_controlNumber;
			EXECUTE 'UPDATE tmpgtcmaterial SET controlnumber =' || v_maxcontrolNumber + v_sequence || ' WHERE controlnumber =' || v_controlNumber;
                        EXECUTE 'UPDATE tmpgtcmaterialcontrol SET controlnumber =' || v_maxcontrolNumber + v_sequence || ' WHERE controlnumber =' || v_controlNumber;
			EXECUTE 'UPDATE tmpgtcexemplarycontrol SET itemnumber = lpad(''' || v_maxItemNumber || v_sequence || ''' , 8 , ' || '''0''' || ') WHERE controlnumber =' || v_controlNumber;
                        EXECUTE 'UPDATE tmpgtcexemplarycontrol SET controlnumber =' || v_maxcontrolNumber + v_sequence || ' WHERE controlnumber =' || v_controlNumber;
			END LOOP;
	    
		RETURN TRUE;

     END;

$BODY$ language plpgsql;
