--------------------------------------------------------------
-- SQL script to create the RB package specifications
-- Author  : MIM
-- Version : 3.0.0
-- Date    : 15/05/2007
--------------------------------------------------------------


PROCEDURE FindAllSessionPeriod
IS
   
	nspsessions        STABLE;
	ixpsessions        STABLEIXP;
	rowlength          LENTABLEIXP;
	db_link_name       VARCHAR2(80);
	cmd_period         CLOB;
	cmd_period_temp    CLOB;
	cmd_len            CLOB;
	cmd_len_temp       CLOB;
	snamecaps          VARCHAR2(30);
	irate              NUMBER;
	log_file           UTL_FILE.FILE_TYPE;
	db_link_count      NUMBER;
	o_code             NUMBER;
	o_text             VARCHAR2(1000);
	storageId          NUMBER;
	sessionCnt    	   NUMBER;
	ixpsessionscnt 	   NUMBER;
	rowlengthcnt 	   NUMBER;
	ixpsessionscnttemp NUMBER;
	rowlengthcnttemp   NUMBER;
	low                NUMBER;
	high               NUMBER;
	mid                NUMBER;
	found              NUMBER;
	updateQry          VARCHAR2(1000);
	
	-- get all unique storage ids from RB_XDR_SESSION
	CURSOR cur_storage
	IS
		SELECT STORAGE_ID FROM RB_XDR_SESSION GROUP BY STORAGE_ID;
	
	-- for each storage find the DWS
	CURSOR cur_dws
	IS
		SELECT H.HOST_ID, HOST_ADMIN_IP_ADDRESS, A.SUBSYSTEM_ID, D.STATE FROM RB_HOST H,RB_APPLICATION A, RB_APPLI_DWH D 
		WHERE A.HOST_ID = H.HOST_ID AND A.APPLICATION_ID=D.APPLICATION_ID
		AND A.SUBSYSTEM_ID=storageId AND D.STATE <> 0;
	
BEGIN
	-- open log file
	log_file := OpenLogFile('NSP_Find_PERIOD.log');
	WriteDbgLog (log_file, '----------------FindAllSessionPeriod Procedure launched at '||TO_CHAR(SYSDATE,'DD/MM/YY HH24:MI:SS') || '----------------');
	
	-- define update query for NSP
	updateQry := 'UPDATE RB_XDR_SESSION SET START_DATE = :sdate, END_DATE = :edate, XDRS = :xdrs_cnt, RATE = :rate_cnt WHERE NAME = :sname AND STORAGE_ID = :stid';
  
	FOR storage IN cur_storage
	LOOP
    BEGIN
		storageId := storage.STORAGE_ID;
		Writedbglog (Log_File, 'Updating Sessions for Storage Id: ' || storageId);
	   
	    FOR dws IN cur_dws
        LOOP
        BEGIN
			-- for each dws get all sessions information on IXP
            -- for each dws get all rate information from IXP
			Writedbglog (Log_File, 'Starting processing for DWS ' || dws.HOST_ID || ' IP ' || dws.HOST_ADMIN_IP_ADDRESS);
			  
			-- create db link
			BEGIN
				db_link_name := 'IXP'||dws.HOST_ID;
                WriteDbgLog (log_file, 'DB link name ' || db_link_name);
				
                SELECT COUNT(*)
                INTO db_link_count
                FROM user_db_links
                WHERE db_link    = db_link_name;
				
                IF(db_link_count = 0) THEN
					CreateSharedDblink(o_code,o_text,db_link_name,'ixp','ixp','IXP',dws.HOST_ADMIN_IP_ADDRESS);
                    WriteDbgLog (log_file, 'DB link '|| db_link_name || ' successfully created');
                ELSE
                    WriteDbgLog (log_file, 'DB link '|| db_link_name || ' already created');
                END IF;
            END;
			  
			-- get sessions on this storage from NSP
			SELECT SESSIONDATA(NAME, 2147483647, 0, 0, 0) BULK COLLECT INTO nspsessions FROM RB_XDR_SESSION WHERE STORAGE_ID = storageId AND FORMAT_ID = 1 ORDER BY NAME;
			  
			-- create queries for IXP
			cmd_period := 'SELECT SESSIONDATAIXP(NAME,BEGINTIME,ENDTIME,AVERAGECDR) FROM DATASERVERSESSION@'||db_link_name||' WHERE NAME IN (';
			cmd_len := 'SELECT LENDATAIXP(TABLE_NAME,AVG_ROW_LEN) FROM USER_TABLES@'||db_link_name||' WHERE TABLE_NAME IN (';
			
			-- Loop NSP Sessions and create query for IXP
			sessionCnt := nspsessions.COUNT - 1;
			FOR i IN 1 .. sessionCnt LOOP	
				cmd_period_temp := cmd_period || '''' || nspsessions(i).NAME || ''', ';
				cmd_period := cmd_period_temp;
				snamecaps := UPPER(nspsessions(i).NAME);
				cmd_len_temp := cmd_len || '''' || snamecaps ||''',';
				cmd_len := cmd_len_temp;
			END LOOP;