-- created by Oraschemadoc Wed Mar  4 07:58:20 2020
-- visit http://www.yarpen.cz/oraschemadoc/ for more info

  CREATE OR REPLACE PACKAGE "SPACEUSER"."RHN_ORG" 
IS
    procedure delete_org(org_id_in in number);
    procedure delete_user(user_id_in in number, deleting_org in number := 0);

END rhn_org;
CREATE OR REPLACE PACKAGE BODY "SPACEUSER"."RHN_ORG" 
IS
    procedure delete_org (
        org_id_in in number
    )
    is
        cursor users is
        select id
        from web_contact
        where org_id = org_id_in;

    cursor servers(org_id_in in number) is
        select    id
        from    rhnServer
        where    org_id = org_id_in;

        cursor config_channels is
        select id
        from rhnConfigChannel
        where org_id = org_id_in;

    cursor custom_channels is
        select    id
        from    rhnChannel
        where    org_id = org_id_in;

    cursor errata is
        select    id
        from    rhnErrata
        where    org_id = org_id_in;

    begin

        if org_id_in = 1 then
            rhn_exception.raise_exception('cannot_delete_base_org');
        end if;

        -- Delete all users.
        for u in users loop
            rhn_org.delete_user(u.id, 1);
        end loop;

        -- Delete all servers.
        for s in servers(org_id_in) loop
            delete_server(s.id);
        end loop;

        -- Delete all config channels.
        for c in config_channels loop
            rhn_config.delete_channel(c.id);
        end loop;

        -- Delete all custom channels.
        for cc in custom_channels loop
          delete from rhnServerChannel where channel_id = cc.id;
          delete from rhnServerProfilePackage where server_profile_id in (
            select id from rhnServerProfile where base_channel = cc.id
          );
          delete from rhnServerProfile where base_channel = cc.id;
        end loop;

        -- Delete all errata packages
        for e in errata loop
            delete from rhnErrataPackage where errata_id = e.id;
        end loop;

        -- Clean up tables where we don't have a cascading delete.
        delete from rhnChannel where org_id = org_id_in;
        delete from rhnDailySummaryQueue where org_id = org_id_in;
        delete from rhnFileList where org_id = org_id_in;
        delete from rhnServerGroup where org_id = org_id_in;
        delete from rhnContentSource where org_id = org_id_in;

        -- Delete the org.
        delete from web_customer where id = org_id_in;

    end delete_org;

    procedure delete_user(user_id_in in number, deleting_org in number := 0) is
        cursor servergroups_needing_admins is
            select    usgp.server_group_id    server_group_id
            from    rhnUserServerGroupPerms    usgp
            where    1=1
                and usgp.user_id = user_id_in
                and not exists (
                    select    1
                    from    rhnUserServerGroupPerms    sq_usgp
                    where    1=1
                        and sq_usgp.server_group_id = usgp.server_group_id
                        and    sq_usgp.user_id != user_id_in
                );
        users            number;
        our_org_id        number;
        other_users        number;
        other_org_admin    number;
        other_user_id  number;
        is_admin       number;
    begin
        select    wc.org_id
        into    our_org_id
        from    web_contact wc
        where    id = user_id_in;

        -- find any other users
        begin
            select    id, 1
            into    other_user_id, other_users
            from    web_contact
            where    1=1
                and org_id = our_org_id
                and id != user_id_in
                and rownum = 1;
        exception
            when no_data_found then
                other_users := 0;
        end;

        -- now do org admin stuff
        if other_users != 0 then
            -- is user admin?
            select  count(1)
             into   is_admin
            from    rhnUserGroupType    ugt,
                    rhnUserGroup        ug,
                    rhnUserGroupMembers    ugm
            where    ugm.user_id = user_id_in
                and ugm.user_group_id = ug.id
                and ug.group_type = ugt.id
                and ugt.label = 'org_admin';
            if is_admin > 0 then
                begin
                    select    new_ugm.user_id
                    into    other_org_admin
                    from    rhnUserGroupMembers    new_ugm,
                            rhnUserGroupType    ugt,
                            rhnUserGroup        ug,
                            rhnUserGroupMembers    ugm
                    where    ugm.user_id = user_id_in
                        and ugm.user_group_id = ug.id
                        and ug.group_type = ugt.id
                        and ugt.label = 'org_admin'
                        and ug.id = new_ugm.user_group_id
                        and new_ugm.user_id != user_id_in
                        and rownum = 1;
                exception
                    when no_data_found then
                        -- If we're deleting the org, we don't want to raise
                        -- the exception.
                        if deleting_org = 0 then
                            rhn_exception.raise_exception('cannot_delete_user');
                        end if;
                end;

                for sg in servergroups_needing_admins loop
                    rhn_user.add_servergroup_perm(other_org_admin,
                        sg.server_group_id);
                end loop;
            end if;
        end if;

        -- and now things for every user
        delete from rhnUserServerPerms where user_id = user_id_in;
        update rhnConfigRevision
           set changed_by_id = NULL
         where changed_by_id = user_id_in;
        if other_users != 0 then
            update        rhnRegToken
                set        user_id = nvl(other_org_admin, other_user_id)
                where    org_id = our_org_id
                    and user_id = user_id_in;
        end if;

        begin
            delete from web_contact where id = user_id_in;
        exception
            when others then
                rhn_exception.raise_exception('cannot_delete_user');
        end;
        return;
    end delete_user;

END rhn_org;
/
