-- Table: usermenu CREATE TABLE "usermenu" ( "um_user" numeric(11, 0) NOT NULL, "um_app_id" numeric(11, 0) NOT NULL, "um_order" varchar(50), "um_gif" varchar(100), "um_alt" varchar(50), "um_link" varchar(100), "um_level" varchar(5), "um_initial" varchar(1), "um_gallery_id" numeric(11, 0), "um_en_length" numeric(4, 0), "um_tc_length" numeric(4, 0), "um_sc_length" numeric(4, 0), "um_terminator" varchar(1), "um_menu" varchar(1), "um_en_name" varchar(1000), "um_tc_name" varchar(1000), "um_sc_name" varchar(1000), "um_ext" varchar(1), CONSTRAINT "usermenu_pkey" PRIMARY KEY ("um_user", "um_app_id"), CONSTRAINT "usermenu_fk1992931308295" FOREIGN KEY (um_app_id) REFERENCES "application" (app_id) ); -- Table: application CREATE TABLE "application" ( "app_id" numeric(11, 0) NOT NULL, "app_en_name" varchar(100) NOT NULL, "app_tc_name" varchar(100), "app_sc_name" varchar(100), "app_menu" varchar(1), "app_installed" varchar(1), "app_order" varchar(50), "app_alt" varchar(50), "app_gif" varchar(100), "app_link" varchar(100), "app_initial" varchar(1), "app_gallery_id" numeric(11, 0), "app_terminator" varchar(1), "app_en_length" numeric(4, 0), "app_tc_length" numeric(4, 0), "app_sc_length" numeric(4, 0), "app_ext" varchar(1), "app_type" varchar(30), CONSTRAINT "application_pkey" PRIMARY KEY ("app_id") ); BEGIN TRANSACTION; drop function hku_usermenu( numeric, varchar, int, varchar ); CREATE FUNCTION hku_USERMENU ( numeric, varchar , int , varchar ) RETURNS varchar AS ' DECLARE vGroupId ALIAS FOR $1 ; vUserID ALIAS FOR $2 ; vGalleryId ALIAS FOR $3 ; rSuccess ALIAS FOR $4 ; vTmpUserID user_group.ug_user_id%TYPE; vTmpGroupID user_group.ug_id%TYPE; vTmpOrder application.app_order%TYPE; vAclLevel acl.acl_level%TYPE; vAppId application.app_id%TYPE; vAppMenu application.app_menu%TYPE; vAppInstalled application.app_installed%TYPE; vAppTerminator application.app_terminator%TYPE; vAppOrder application.app_order%TYPE; vAppOrderLen int:=0; vLength int:=0; vOrder application.app_order%TYPE; app_rec application%ROWTYPE; cnt1 int:=0; cnt2 int:=0; vcnt varchar(10):=''''; v_str varchar(1500):=''''; c1 record ; c2 record ; tmptime timestamp; t1 int := 0 ; t2 int := 0 ; BEGIN select timeofday() into tmptime ; RAISE NOTICE ''[MT][START]HKU_USERMENU % % % %'', vGroupId, vUserID, vGalleryId, tmptime ; if vUserID = '' '' or vUserID = '''' or vUserID is null then vTmpGroupID := vGroupId; else vTmpGroupID := 0; end if; for c1 in select user_id from ( (SELECT UG_USER_ID as user_id FROM USER_GROUP WHERE UG_ID = vTmpGroupID and GALLERY_ID = vGalleryId) union (SELECT USER_ID as user_id FROM HKU_USERS WHERE text( USER_ID ) = vUserId and GALLERY_ID = vGalleryId) ) tmp loop vTmpUserID := text( c1.user_id ); delete from usermenu where um_user = vTmpUserID ; for c2 in select ACL_APP, max(ACL_LEVEL) as acl_level, app.APP_ORDER, length(app.APP_ORDER) as app_order_length, app.APP_MENU, app.app_installed, app.app_terminator from ACL, APPLICATION app where ACL_APP = app.APP_ID and ACL_GALLERY_ID = vGalleryId and app.APP_GALLERY_ID = vGalleryId and ACL_GRP in (select u.ug_id from user_group u, user_group_master ug where u.ug_user_id = vTmpUserID and ug.ug_id = u.ug_id and (ug.deleted = ''N'' or ug.deleted IS NULL ) and u.gallery_id = vGalleryID and ug.gallery_id = vGalleryID ) group by acl_app, app.app_order,app.APP_MENU, app.app_installed, app.app_terminator order by app.app_order desc, ACL_LEVEL loop vAppId := c2.ACL_APP ; vAclLevel := c2.acl_level ; vAppOrder := c2.APP_ORDER ; vAppOrderLen := c2.app_order_length ; vAppMenu := c2.APP_MENU ; vAppInstalled := c2.app_installed ; vAppTerminator := c2.app_terminator ; vcnt := 0; vLength := vAppOrderLen; vTmpOrder := vAppOrder; select count(*) into vcnt from ACL, APPLICATION app where ACL_APP = app.app_id and ACL_LEVEL > 0 and APP_INSTALLED = ''Y'' and ACL_GALLERY_ID = vGalleryID and app.APP_GALLERY_ID = vGalleryID and substr(app.app_order,1,vAppOrderLen) = vAppOrder and app.app_order <> vAppOrder and ACL_GRP in (select u.ug_id from user_group u, user_group_master ug where u.ug_user_id = vTmpUserID and ug.ug_id = u.ug_id and (ug.deleted = ''N'' or ug.deleted IS NULL) and u.gallery_id = vGalleryID and ug.gallery_id = vGalleryID); IF vAclLevel IS NULL or vAclLevel <= 0 or upper(vAppInstalled) = ''N'' THEN delete from usermenu where um_user = vTmpUserID and substr( um_order, 1, vAppOrderLen ) = vAppOrder; t1 = t1 + 1 ; ELSE IF vcnt > 0 or vAppTerminator = ''Y'' THEN for app_rec in SELECT * FROM APPLICATION WHERE app_order = vTmpOrder and app_gallery_id = vGalleryId loop delete from usermenu where um_user = vTmpUserID and um_app_id = app_rec.app_id; insert into usermenu(um_user, um_app_id, um_order, um_level, um_menu, um_gallery_id, um_gif, um_alt, um_link, um_terminator, um_en_name, um_tc_name, um_sc_name, um_ext) values (vTmpUserID, app_rec.app_id, app_rec.app_order, text( vAclLevel ) , app_rec.app_menu, vGalleryId, app_rec.app_gif, app_rec.app_alt, app_rec.app_link, app_rec.app_terminator, app_rec.app_en_name, app_rec.app_tc_name, app_rec.app_sc_name, app_rec.app_ext); /*RAISE NOTICE ''insert into usermenu(um_user, um_app_id, um_order, um_level, um_menu, um_gallery_id, um_gif, um_alt, um_link, um_terminator, um_en_name, um_tc_name, um_sc_name, um_ext) values ( %, % , ''''%'''', % , ''''%'''', % , ''''%'''', ''''%'''' , ''''%'''', ''''%'''' , ''''%'''', ''''%'''' , ''''%'''', ''''%'''' ); '' , vTmpUserID, app_rec.app_id, app_rec.app_order, vAclLevel , app_rec.app_menu, vGalleryId, app_rec.app_gif, app_rec.app_alt, app_rec.app_link, app_rec.app_terminator, app_rec.app_en_name, app_rec.app_tc_name, app_rec.app_sc_name, app_rec.app_ext ; */ t2 = t2 + 1 ; /* SELECT count(*) into rSuccess from usermenu WHERE um_user = vTmpUserID and um_app_id = app_rec.app_id; */ EXIT; /* run once (if c3%FOUND then) */ end loop; END IF; END IF; vcnt := 0; end loop; end loop; select timeofday() into tmptime ; RAISE NOTICE ''[MT][STOP]HKU_USERMENU % % % % % %'', vGroupId, vUserID, vGalleryId, tmptime, t1, t2 ; return ''Y'' ; END; ' LANGUAGE 'plpgsql' ;