xxx
text/plain
Filename: xxx
Type: text/plain
Part: 0
-- 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' ;