-- Views
--
-- Tracking and aggregating object views -- procedures.
--
-- Copyright (C) 2003 Jeff Davis
-- @author Jeff Davis <davis@xarg.net>
-- 
-- Adapted for Oracle by Mario Aguado <maguado@innova.uned.es>
-- @author Mario Aguado <maguado@innova.uned.es>
-- @creation-date 28/06/2006
--
-- @cvs-id $Id: views-procs.sql,v 1.1 2007/08/01 08:59:09 marioa Exp $
--
-- This is free software distributed under the terms of the GNU Public
-- License.  Full text of the license is available from the GNU Project:
-- http://www.fsf.org/copyleft/gpl.html
CREATE OR REPLACE PACKAGE VIEWS_VIEW AS
 
	FUNCTION RECORD_VIEW (
			P_OBJECT_ID IN ACS_OBJECTS.OBJECT_ID%TYPE,
			P_VIEWER_ID IN VIEWS_VIEWS.VIEWER_ID%TYPE) RETURN INTEGER;
	--update the view count of object_id for viewer viewer_id, returns view count
END VIEWS_VIEW;
/
CREATE OR REPLACE PACKAGE BODY VIEWS_VIEW AS

	FUNCTION RECORD_VIEW (
			P_OBJECT_ID IN ACS_OBJECTS.OBJECT_ID%TYPE, 
			P_VIEWER_ID IN VIEWS_VIEWS.VIEWER_ID%TYPE) RETURN INTEGER IS
	   V_COUNT INTEGER;
	   VIEWS_COUNT  VIEWS_VIEWS.VIEWS_COUNT%TYPE;
	BEGIN 
    	SELECT COUNT(*) INTO V_COUNT FROM VIEWS_VIEWS WHERE OBJECT_ID = P_OBJECT_ID AND VIEWER_ID = P_VIEWER_ID;

	    IF V_COUNT = 0 THEN 
    	    INSERT INTO VIEWS_VIEWS(OBJECT_ID,VIEWER_ID) 
        	VALUES (P_OBJECT_ID, P_VIEWER_ID);
	        VIEWS_COUNT := 0;
    	ELSE
    	    SELECT VIEWS_COUNT INTO VIEWS_COUNT FROM VIEWS_VIEWS WHERE OBJECT_ID = P_OBJECT_ID AND VIEWER_ID = P_VIEWER_ID;
	        UPDATE VIEWS_VIEWS
    	       SET VIEWS_COUNT = VIEWS_COUNT + 1, LAST_VIEWED = SYSDATE
        	 WHERE OBJECT_ID = P_OBJECT_ID
	           AND VIEWER_ID = P_VIEWER_ID;
    	END IF;
	    RETURN VIEWS_COUNT + 1;
	END RECORD_VIEW;

END VIEWS_VIEW;
/ 
SHOW ERRORS;

CREATE OR REPLACE PACKAGE VIEWS_VIEW_BY_TYPE AS 
	FUNCTION RECORD_VIEW (
			P_OBJECT_ID IN ACS_OBJECTS.OBJECT_ID%TYPE,
			P_VIEWER_ID IN VIEWS_BY_TYPE.VIEWER_ID%TYPE,
			P_VIEW_TYPE IN  VIEWS_BY_TYPE.VIEW_TYPE%TYPE) RETURN VIEWS_VIEWS.VIEWS_COUNT%TYPE; 
END VIEWS_VIEW_BY_TYPE;
/
SHOW ERRORS;

CREATE OR REPLACE PACKAGE BODY VIEWS_VIEW_BY_TYPE AS 
	FUNCTION RECORD_VIEW (
			P_OBJECT_ID IN ACS_OBJECTS.OBJECT_ID%TYPE,
			P_VIEWER_ID IN VIEWS_BY_TYPE.VIEWER_ID%TYPE,
			P_VIEW_TYPE IN  VIEWS_BY_TYPE.VIEW_TYPE%TYPE) RETURN VIEWS_VIEWS.VIEWS_COUNT%TYPE IS 
    	V_VIEWS     VIEWS_VIEWS.VIEWS_COUNT%TYPE;
	BEGIN 
    	SELECT VIEWS_COUNT INTO V_VIEWS FROM VIEWS_BY_TYPE WHERE OBJECT_ID = P_OBJECT_ID AND VIEWER_ID = P_VIEWER_ID AND VIEW_TYPE = P_VIEW_TYPE;

    	IF V_VIEWS IS NULL THEN 
        	INSERT INTO VIEWS_BY_TYPE(OBJECT_ID,VIEWER_ID,VIEW_TYPE) 
        	VALUES (P_OBJECT_ID, P_VIEWER_ID,P_VIEW_TYPE);
        	V_VIEWS := 0;
    	ELSE
	        UPDATE VIEWS_BY_TYPE
    	    SET VIEWS_COUNT = VIEWS_COUNT + 1, LAST_VIEWED = sysdate, VIEW_TYPE = P_VIEW_TYPE
        	WHERE OBJECT_ID = P_OBJECT_ID
           	AND VIEWER_ID = P_VIEWER_ID
           	AND VIEW_TYPE = P_VIEW_TYPE;
    	END IF;

    	RETURN V_VIEWS + 1;
	END RECORD_VIEW;
END VIEWS_VIEW_BY_TYPE;
/
SHOW ERRORS;