RHN_ENTITLEMENTS

Package source

Legend: comment string keyword reserved word operator

     1: package rhn_entitlements
     2: is
     3: 	body_version varchar2(100) := '';
     4: 
     5:    type ents_array is varray(10) of rhnServerGroupType.label%TYPE;
     6: 
     7:     procedure remove_org_entitlements (
     8:         org_id_in number
     9:     );
    10: 
    11:     function entitlement_grants_service (
    12: 	    entitlement_in in varchar2,
    13: 		service_level_in in varchar2
    14: 	) return number;
    15: 
    16: 	function lookup_entitlement_group (
    17: 		org_id_in in number,
    18: 		type_label_in in varchar2 := 'sw_mgr_entitled'
    19: 	) return number;
    20: 
    21: 	function create_entitlement_group (
    22: 		org_id_in in number,
    23: 		type_label_in in varchar2 := 'sw_mgr_entitled'
    24: 	) return number;
    25: 
    26:    function can_entitle_server (
    27:       server_id_in   in number,
    28:       type_label_in  in varchar2
    29:    )
    30:    return number;
    31: 
    32:    function can_switch_base (
    33:       server_id_in   in    integer,
    34:       type_label_in  in    varchar2
    35:    )
    36:    return number;
    37: 
    38:    function find_compatible_sg (
    39:       server_id_in in number,
    40:       type_label_in in varchar2,
    41:       sgid_out out number
    42:    )
    43:    return boolean;
    44: 
    45: 	procedure entitle_server (
    46: 		server_id_in in number,
    47: 		type_label_in in varchar2 := 'sw_mgr_entitled'
    48: 	);
    49: 
    50: 	procedure remove_server_entitlement (
    51: 		server_id_in in number,
    52: 		type_label_in in varchar2 := 'sw_mgr_entitled',
    53:         repoll_virt_guests in number := 1
    54: 	);
    55: 
    56: 	procedure unentitle_server (
    57: 		server_id_in in number
    58: 	);
    59: 
    60:     procedure repoll_virt_guest_entitlements(
    61:         server_id_in in number
    62:     );
    63: 
    64: 	function get_server_entitlement (
    65: 		server_id_in in number
    66: 	) return ents_array;
    67: 
    68: 	procedure modify_org_service (
    69: 		org_id_in in number,
    70: 		service_label_in in varchar2,
    71: 		enable_in in char
    72: 	);
    73: 
    74:     procedure set_customer_enterprise (
    75: 		customer_id_in in number
    76: 	);
    77: 
    78: 	procedure set_customer_provisioning (
    79: 		customer_id_in in number
    80: 	);
    81: 
    82: 	procedure set_customer_nonlinux (
    83: 		customer_id_in in number
    84: 	);
    85: 
    86:     procedure unset_customer_enterprise (
    87: 		customer_id_in in number
    88: 	);
    89: 
    90: 	procedure unset_customer_provisioning (
    91: 		customer_id_in in number
    92: 	);
    93: 
    94: 	procedure unset_customer_nonlinux (
    95: 		customer_id_in in number
    96: 	);
    97: 
    98:     procedure assign_system_entitlement(
    99:         group_label_in in varchar2,
   100:         from_org_id_in in number,
   101:         to_org_id_in in number,
   102:         quantity_in in number
   103:     );
   104: 
   105:     procedure assign_channel_entitlement(
   106:         channel_family_label_in in varchar2,
   107:         from_org_id_in in number,
   108:         to_org_id_in in number,
   109:         quantity_in in number
   110:     );
   111: 
   112:     procedure activate_system_entitlement(
   113:         org_id_in in number,
   114:         group_label_in in varchar2,
   115:         quantity_in in number
   116:     );
   117: 
   118:     procedure activate_channel_entitlement(
   119:         org_id_in in number,
   120:         channel_family_label_in in varchar2,
   121:         quantity_in in number
   122:     );
   123: 
   124:     procedure set_group_count (
   125: 		customer_id_in in number,	-- customer_id
   126: 		type_in in char,			-- 'U' or 'S'
   127: 		group_type_in in number,	-- rhn[User|Server]GroupType.id
   128: 		quantity_in in number		-- quantity
   129:     );
   130: 
   131:     procedure set_family_count (
   132: 		customer_id_in in number,		-- customer_id
   133: 		channel_family_id_in in number,	-- 246
   134: 		quantity_in in number			-- 3
   135:     );
   136: 
   137:     -- this makes NO checks that the quantity is within max,
   138:     -- so we should NEVER run this unless we KNOW that we won't be
   139:     -- violating the max
   140:     procedure entitle_last_modified_servers (
   141: 		customer_id_in in number,	-- customer_id
   142: 		type_label_in in varchar2,	-- 'enterprise_entitled'
   143: 		quantity_in in number		-- 3
   144:     );
   145: 
   146: 	procedure prune_everything (
   147: 		customer_id_in in number
   148: 	);
   149: 
   150: 	procedure subscribe_newest_servers (
   151: 		customer_id_in in number
   152: 	);
   153: end rhn_entitlements;

Package body source

Legend: comment string keyword reserved word operator

     1: package body rhn_entitlements
     2: is
     3: 	body_version varchar2(100) := '';
     4: 
     5: 
     6:     -- *******************************************************************
     7:     -- PROCEDURE: remove_org_entitlements
     8:     --
     9:     -- Removes both system entitlements and channel subscriptions
    10:     -- that are currently assigned to an org and re-assigns to the
    11:     -- master org (org_id = 1).
    12:     --
    13:     -- When we call this we expect everything to already be unentitled
    14:     -- which shoul be handled by delete_org.
    15:     --
    16:     -- Called by: delete_org
    17:     -- *******************************************************************
    18:     procedure remove_org_entitlements(
    19:         org_id_in in number
    20:     )
    21:     is
    22: 
    23:         cursor system_ents is
    24:         select sg.id, sg.max_members, sg.group_type
    25:         from rhnServerGroup sg
    26:         where group_type is not null
    27:           and org_id = org_id_in;
    28: 
    29:         cursor channel_subs is
    30:         select pcf.channel_family_id, pcf.max_members
    31:         from rhnChannelFamily cf,
    32:              rhnPrivateChannelFamily pcf
    33:         where pcf.org_id = org_id_in
    34:           and pcf.channel_family_id = cf.id
    35:           and cf.org_id is null;
    36: 
    37:     begin
    38: 
    39:         for system_ent in system_ents loop
    40:             update rhnServerGroup
    41:             set max_members = max_members + system_ent.max_members
    42:             where org_id = 1
    43:               and group_type = system_ent.group_type;
    44:         end loop;
    45: 
    46:         update rhnServerGroup
    47:         set max_members = 0
    48:         where org_id = org_id_in;
    49: 
    50:         for channel_sub in channel_subs loop
    51:             update rhnPrivateChannelFamily
    52:             set max_members = max_members + channel_sub.max_members
    53:             where org_id = 1
    54:               and channel_family_id = channel_sub.channel_family_id;
    55:         end loop;
    56: 
    57:         update rhnPrivateChannelFamily
    58:         set max_members = 0
    59:         where org_id = org_id_in;
    60: 
    61:     end remove_org_entitlements;
    62: 
    63: 	function entitlement_grants_service (
    64: 	    entitlement_in in varchar2,
    65: 	    service_level_in in varchar2
    66: 	) return number	is
    67: 	begin
    68: 		if service_level_in = 'provisioning' then
    69: 			if entitlement_in = 'provisioning_entitled' then
    70: 				return 1;
    71: 			else
    72: 				return 0;
    73: 			end if;
    74: 		elsif service_level_in = 'management' then
    75: 			if entitlement_in = 'enterprise_entitled' then
    76: 				return 1;
    77: 			else
    78: 				return 0;
    79: 			end if;
    80: 		elsif service_level_in = 'monitoring' then
    81: 			if entitlement_in = 'monitoring_entitled' then
    82: 				return 1;
    83: 			end if;
    84: 		elsif service_level_in = 'updates' then
    85: 			return 1;
    86: 		else
    87: 			return 0;
    88: 		end if;
    89: 	end entitlement_grants_service;
    90: 
    91: 	function lookup_entitlement_group (
    92: 		org_id_in in number,
    93: 		type_label_in in varchar2 := 'sw_mgr_entitled'
    94: 	) return number is
    95: 		cursor server_groups is
    96: 			select	sg.id				server_group_id
    97: 			from	rhnServerGroup		sg,
    98: 					rhnServerGroupType	sgt
    99: 			where	sgt.label = type_label_in
   100: 				and sgt.id = sg.group_type
   101: 				and sg.org_id = org_id_in;
   102: 	begin
   103: 		for sg in server_groups loop
   104: 			return sg.server_group_id;
   105: 		end loop;
   106: 		return rhn_entitlements.create_entitlement_group(
   107: 				org_id_in,
   108: 				type_label_in
   109: 			);
   110: 	end lookup_entitlement_group;
   111: 
   112: 	function create_entitlement_group (
   113: 		org_id_in in number,
   114: 		type_label_in in varchar2 := 'sw_mgr_entitled'
   115: 	) return number is
   116: 		sg_id_val number;
   117: 	begin
   118: 		select	rhn_server_group_id_seq.nextval
   119: 		into	sg_id_val
   120: 		from	dual;
   121: 
   122: 		insert into rhnServerGroup (
   123: 				id, name, description, max_members, current_members,
   124: 				group_type, org_id
   125: 			) (
   126: 				select	sg_id_val, sgt.label, sgt.label,
   127: 						0, 0, sgt.id, org_id_in
   128: 				from	rhnServerGroupType sgt
   129: 				where	sgt.label = type_label_in
   130: 			);
   131: 
   132: 		return sg_id_val;
   133: 	end create_entitlement_group;
   134: 
   135:    function can_entitle_server (
   136:         server_id_in in number,
   137:         type_label_in in varchar2 )
   138:    return number is
   139:       cursor addon_servergroups (base_label_in in varchar2,
   140:                                  addon_label_in in varchar2) is
   141:          select
   142:             addon_id
   143:          from
   144:             rhnSGTypeBaseAddonCompat
   145:          where base_id = lookup_sg_type (base_label_in)
   146:            and addon_id = lookup_sg_type (addon_label_in);
   147: 
   148:       previous_ent        rhn_entitlements.ents_array;
   149:       is_base_in          char   := 'N';
   150:       is_base_current     char   := 'N';
   151:       i                   number := 0;
   152:       sgid                number := 0;
   153: 
   154:    begin
   155: 
   156:       previous_ent := rhn_entitlements.ents_array();
   157:       previous_ent := rhn_entitlements.get_server_entitlement(server_id_in);
   158: 
   159:       select distinct is_base
   160:       into is_base_in
   161:       from rhnServerGroupType
   162:       where label = type_label_in;
   163: 
   164:       if previous_ent.count = 0 then
   165:          if (is_base_in = 'Y' and rhn_entitlements.find_compatible_sg (server_id_in, type_label_in, sgid)) then
   166:             -- rhn_server.insert_into_servergroup (server_id_in, sgid);
   167:             return 1;
   168:          else
   169:             -- rhn_exception.raise_exception ('invalid_base_entitlement');
   170:             return 0;
   171:          end if;
   172: 
   173:       -- there are previous ents, first make sure we're not trying to entitle a base ent
   174:       elsif is_base_in = 'Y' then
   175:          -- rhn_exception.raise_exception ('invalid_addon_entitlement');
   176:          return 0;
   177: 
   178:       -- it must be an addon, so proceed with the entitlement
   179:       else
   180: 
   181:          -- find the servers base ent
   182:          is_base_current := 'N';
   183:          i := 0;
   184:          while is_base_current = 'N' and i <= previous_ent.count
   185:          loop
   186:             i := i + 1;
   187:             select is_base
   188:             into is_base_current
   189:             from rhnServerGroupType
   190:             where label = previous_ent(i);
   191:          end loop;
   192: 
   193:          -- never found a base ent, that would be strange
   194:          if is_base_current  = 'N' then
   195:             -- rhn_exception.raise_exception ('invalid_base_entitlement');
   196:             return 0;
   197:          end if;
   198: 
   199:          -- this for loop verifies the validity of the addon path
   200:          for addon_servergroup in addon_servergroups  (previous_ent(i), type_label_in) loop
   201:             -- find an appropriate sgid for the addon and entitle the server
   202:             if rhn_entitlements.find_compatible_sg (server_id_in, type_label_in, sgid) then
   203:                -- rhn_server.insert_into_servergroup (server_id_in, sgid);
   204:                return 1;
   205:             else
   206:                -- rhn_exception.raise_exception ('invalid_addon_entitlement');
   207:                return 0;
   208:             end if;
   209:          end loop;
   210: 
   211:       end if;
   212: 
   213:       return 0;
   214: 
   215:    end can_entitle_server;
   216: 
   217:    function can_switch_base (
   218:       server_id_in   in    integer,
   219:       type_label_in  in    varchar2
   220:    ) return number is
   221: 
   222:       type_label_in_is_base   char(1);
   223:       sgid                    number;
   224: 
   225:    begin
   226: 
   227:       begin
   228:          select is_base into type_label_in_is_base
   229:          from rhnServerGroupType
   230:          where label = type_label_in;
   231:       exception
   232:          when no_data_found then
   233:             rhn_exception.raise_exception ( 'invalid_entitlement' );
   234:       end;
   235: 
   236:       if type_label_in_is_base = 'N' then
   237:          rhn_exception.raise_exception ( 'invalid_entitlement' );
   238:       elsif rhn_entitlements.find_compatible_sg ( server_id_in,
   239:                                                   type_label_in, sgid ) then
   240:          return 1;
   241:       else
   242:          return 0;
   243:       end if;
   244: 
   245:    end can_switch_base;
   246: 
   247: 
   248:    function find_compatible_sg (
   249:       server_id_in    in   number,
   250:       type_label_in   in   varchar2,
   251:       sgid_out        out  number
   252:    ) return boolean is
   253: 
   254:       cursor servergroups is
   255:          select sg.id            id
   256:            from rhnServerGroupType             sgt,
   257:                 rhnServerGroup                 sg,
   258:                 rhnServer                     s,
   259:                 rhnServerServerGroupArchCompat ssgac
   260:           where s.id = server_id_in
   261:             and s.org_id = sg.org_id
   262:             and sgt.label = type_label_in
   263:             and sg.group_type = sgt.id
   264:             and ssgac.server_group_type = sgt.id
   265:             and ssgac.server_arch_id = s.server_arch_id
   266:             and not exists (
   267:                      select 1
   268:                       from rhnServerGroupMembers sgm
   269:                      where sgm.server_group_id = sg.id
   270:                        and sgm.server_id = s.id);
   271: 
   272: 
   273:    begin
   274:       for servergroup in servergroups loop
   275:          sgid_out := servergroup.id;
   276:          return true;
   277:       end loop;
   278: 
   279:       --no servergroup found
   280:       sgid_out := 0;
   281:       return false;
   282:    end find_compatible_sg;
   283: 
   284: 	procedure entitle_server (
   285: 		server_id_in in number,
   286: 		type_label_in in varchar2 := 'sw_mgr_entitled'
   287: 	) is
   288:       sgid  number := 0;
   289:       is_virt number := 0;
   290: 
   291: 	begin
   292: 
   293:           begin
   294:           select 1 into is_virt
   295:             from rhnServerEntitlementView
   296:            where server_id = server_id_in
   297:              and label in ('virtualization_host', 'virtualization_host_platform');
   298: 	  exception
   299:             when no_data_found then
   300:               is_virt := 0;
   301:           end;
   302: 
   303:       if is_virt = 0 and (type_label_in = 'virtualization_host' or
   304:                           type_label_in = 'virtualization_host_platform') then
   305: 
   306:         is_virt := 1;
   307:       end if;
   308: 
   309: 
   310: 
   311:       if rhn_entitlements.can_entitle_server(server_id_in,
   312:                                              type_label_in) = 1 then
   313:          if rhn_entitlements.find_compatible_sg (server_id_in,
   314:                                                  type_label_in, sgid) then
   315:             insert into rhnServerHistory ( id, server_id, summary, details )
   316:             values ( rhn_event_id_seq.nextval, server_id_in,
   317:                      'added system entitlement ',
   318:                       case type_label_in
   319:                        when 'enterprise_entitled' then 'Management'
   320:                        when 'sw_mgr_entitled' then 'Update'
   321:                        when 'provisioning_entitled' then 'Provisioning'
   322:                        when 'monitoring_entitled' then 'Monitoring'
   323:                        when 'virtualization_host' then 'Virtualization'
   324:                        when 'virtualization_host_platform' then
   325:                             'Virtualization Platform' end  );
   326: 
   327:             rhn_server.insert_into_servergroup (server_id_in, sgid);
   328: 
   329:             if is_virt = 1 then
   330:               rhn_entitlements.repoll_virt_guest_entitlements(server_id_in);
   331:             end if;
   332: 
   333:          else
   334:             rhn_exception.raise_exception ('no_available_server_group');
   335:          end if;
   336:       else
   337:          rhn_exception.raise_exception ('invalid_entitlement');
   338:       end if;
   339:    end entitle_server;
   340: 
   341: 	procedure remove_server_entitlement (
   342: 		server_id_in in number,
   343: 		type_label_in in varchar2 := 'sw_mgr_entitled',
   344:         repoll_virt_guests in number := 1
   345: 	) is
   346: 		group_id number;
   347:       type_is_base char;
   348:       is_virt number := 0;
   349: 	begin
   350:       begin
   351: 
   352: 
   353:       -- would be nice if there were a virt attribute of entitlement types, not have to specify 2 different ones...
   354:         begin
   355:           select 1 into is_virt
   356:             from rhnServerEntitlementView
   357:            where server_id = server_id_in
   358:              and label in ('virtualization_host', 'virtualization_host_platform');
   359:         exception
   360:           when no_data_found then
   361:             is_virt := 0;
   362:         end;
   363: 
   364: 		select	sg.id, sgt.is_base
   365:   		into	group_id, type_is_base
   366:   		from	rhnServerGroupType sgt,
   367:    			rhnServerGroup sg,
   368:   				rhnServerGroupMembers sgm,
   369:   				rhnServer s
   370:   		where	s.id = server_id_in
   371:   			and s.id = sgm.server_id
   372:   			and sgm.server_group_id = sg.id
   373:   			and sg.org_id = s.org_id
   374:   			and sgt.label = type_label_in
   375:   			and sgt.id = sg.group_type;
   376: 
   377:       if ( type_is_base = 'Y' ) then
   378:          -- unentitle_server should handle everything, don't really need to do anything else special here
   379:          unentitle_server ( server_id_in );
   380:       else
   381: 
   382:          insert into rhnServerHistory ( id, server_id, summary, details )
   383:          values ( rhn_event_id_seq.nextval, server_id_in,
   384:                   'removed system entitlement ',
   385:                    case type_label_in
   386:                     when 'enterprise_entitled' then 'Management'
   387:                     when 'sw_mgr_entitled' then 'Update'
   388:                     when 'provisioning_entitled' then 'Provisioning'
   389:                     when 'monitoring_entitled' then 'Monitoring'
   390:                     when 'virtualization_host' then 'Virtualization'
   391:                     when 'virtualization_host_platform' then
   392:                          'Virtualization Platforrm' end  );
   393: 
   394:          rhn_server.delete_from_servergroup(server_id_in, group_id);
   395: 
   396:          -- special case: clean up related monitornig data
   397:          if type_label_in = 'monitoring_entitled' then
   398:            DELETE
   399:              FROM state_change
   400:             WHERE o_id IN (SELECT probe_id
   401:                              FROM rhn_check_probe
   402:                             WHERE host_id = server_id_in);
   403:            DELETE /*+index(time_series time_series_probe_id_idx)*/
   404:              FROM time_series
   405:             WHERE SUBSTR(o_id, INSTR(o_id, '-') + 1,
   406:                         (INSTR(o_id, '-', INSTR(o_id, '-') + 1) - INSTR(o_id, '-')) - 1)
   407:               IN (SELECT to_char(probe_id)
   408:                     FROM rhn_check_probe
   409:                    WHERE host_id = server_id_in);
   410:            DELETE
   411:              FROM rhn_probe
   412:             WHERE recid IN (SELECT probe_id
   413:                               FROM rhn_check_probe
   414:                              WHERE host_id = server_id_in);
   415:          end if;
   416: 
   417:          if is_virt = 1 and repoll_virt_guests = 1 then
   418:            rhn_entitlements.repoll_virt_guest_entitlements(server_id_in);
   419:          end if;
   420:       end if;
   421: 
   422:   		exception
   423:   		when no_data_found then
   424:   				rhn_exception.raise_exception('invalid_server_group_member');
   425:       end;
   426: 
   427:  	end remove_server_entitlement;
   428: 
   429: 
   430:    procedure unentitle_server (server_id_in in number) is
   431: 
   432:       cursor servergroups is
   433:          select distinct sgt.label, sg.id server_group_id
   434:          from  rhnServerGroupType sgt,
   435:                rhnServerGroup sg,
   436:                rhnServer s,
   437:                rhnServerGroupMembers sgm
   438:          where s.id = server_id_in
   439:             and s.org_id = sg.org_id
   440:             and sg.group_type = sgt.id
   441:             and sgm.server_group_id = sg.id
   442:             and sgm.server_id = s.id;
   443: 
   444:      is_virt number := 0;
   445: 
   446:    begin
   447: 
   448:       begin
   449:         select 1 into is_virt
   450:           from rhnServerEntitlementView
   451:          where server_id = server_id_in
   452:            and label in ('virtualization_host', 'virtualization_host_platform');
   453:       exception
   454:         when no_data_found then
   455:           is_virt := 0;
   456:       end;
   457: 
   458:       for servergroup in servergroups loop
   459: 
   460:          insert into rhnServerHistory ( id, server_id, summary, details )
   461:          values ( rhn_event_id_seq.nextval, server_id_in,
   462:                   'removed system entitlement ',
   463:                    case servergroup.label
   464:                     when 'enterprise_entitled' then 'Management'
   465:                     when 'sw_mgr_entitled' then 'Update'
   466:                     when 'provisioning_entitled' then 'Provisioning'
   467:                     when 'monitoring_entitled' then 'Monitoring'
   468:                     when 'virtualization_host' then 'Virtualization'
   469:                     when 'virtualization_host_platform' then
   470:                          'Virtualization Platform' end  );
   471: 
   472:          rhn_server.delete_from_servergroup(server_id_in,
   473:                                             servergroup.server_group_id );
   474:       end loop;
   475: 
   476:       if is_virt = 1 then
   477:         rhn_entitlements.repoll_virt_guest_entitlements(server_id_in);
   478:       end if;
   479: 
   480:    end unentitle_server;
   481: 
   482: 
   483:     -- *******************************************************************
   484:     -- PROCEDURE: repoll_virt_guest_entitlements
   485:     --
   486:     --   Whenever we add/remove a virtualization_host* entitlement from
   487:     --   a host, we can call this procedure to update what type of slots
   488:     --   the guests are consuming.
   489:     --
   490:     --   If you're removing the entitlement, it's
   491:     --   possible the guests will become unentitled if you don't have enough
   492:     --   physical slots to cover them.
   493:     --
   494:     --   If you're adding the entitlement, you end up freeing up physical
   495:     --   slots for other systems.
   496:     --
   497:     -- *******************************************************************
   498:     procedure repoll_virt_guest_entitlements(server_id_in in number)
   499:     is
   500: 
   501:         -- All channel families associated with the guests of server_id_in
   502:         cursor families is
   503:             select distinct cfs.channel_family_id
   504:             from
   505:                 rhnChannelFamilyServers cfs,
   506:                 rhnVirtualInstance vi
   507:             where
   508:                 vi.host_system_id = server_id_in
   509:                 and vi.virtual_system_id = cfs.server_id;
   510: 
   511:         -- All of server group types associated with the guests of
   512:         -- server_id_in
   513:         cursor group_types is
   514:             select distinct sg.group_type, sgt.label
   515:             from
   516:                 rhnServerGroupType sgt,
   517:                 rhnServerGroup sg,
   518:                 rhnServerGroupMembers sgm,
   519:                 rhnVirtualInstance vi
   520:             where
   521:                 vi.host_system_id = server_id_in
   522:                 and vi.virtual_system_id = sgm.server_id
   523:                 and sgm.server_group_id = sg.id
   524:                 and sg.group_type = sgt.id;
   525: 
   526:         -- Virtual servers from a certain family belonging to a speicifc
   527:         -- host that are consuming physical channel slots over the limit.
   528:         cursor virt_servers_cfam(family_id_in in number, quantity_in in number)  is
   529:             select virtual_system_id
   530:             from (
   531:                 select rownum, vi.virtual_system_id
   532:                 from
   533:                     rhnChannelFamilyMembers cfm,
   534:                     rhnServerChannel sc,
   535:                     rhnVirtualInstance vi
   536:                 where
   537:                     vi.host_system_id = server_id_in
   538:                     and vi.virtual_system_id = sc.server_id
   539:                     and sc.channel_id = cfm.channel_id
   540:                     and cfm.channel_family_id = family_id_in
   541:                 order by sc.modified desc
   542:                 )
   543:             where rownum <= quantity_in;
   544: 
   545:         -- Virtual servers from a certain family belonging to a speicifc
   546:         -- host that are consuming physical system slots over the limit.
   547:         cursor virt_servers_sgt(group_type_in in number, quantity_in in number)  is
   548:             select virtual_system_id
   549:             from (
   550:                 select rownum, vi.virtual_system_id
   551:                 from
   552:                     rhnServerGroup sg,
   553:                     rhnServerGroupMembers sgm,
   554:                     rhnVirtualInstance vi
   555:                 where
   556:                     vi.host_system_id = server_id_in
   557:                     and vi.virtual_system_id = sgm.server_id
   558:                     and sgm.server_group_id = sg.id
   559:                     and sg.group_type = group_type_in
   560:                 order by sgm.modified desc
   561:                 )
   562:             where rownum <= quantity_in;
   563: 
   564:         org_id_val number;
   565:         max_members_val number;
   566:         current_members_calc number;
   567:         sg_id number;
   568: 
   569:     begin
   570: 
   571:         select org_id
   572:         into org_id_val
   573:         from rhnServer
   574:         where id = server_id_in;
   575: 
   576:         -- deal w/ channel entitlements first ...
   577:         for family in families loop
   578:             -- get the current (physical) members of the family
   579:             current_members_calc :=
   580:                 rhn_channel.channel_family_current_members(family.channel_family_id,
   581:                                                            org_id_val); -- fixed transposed args
   582: 
   583:             -- get the max members of the family
   584:             select max_members
   585:             into max_members_val
   586:             from rhnPrivateChannelFamily
   587:             where channel_family_id = family.channel_family_id
   588:             and org_id = org_id_val;
   589: 
   590:             if current_members_calc > max_members_val then
   591:                 -- A virtualization_host* ent must have been removed, so we'll
   592:                 -- unsubscribe guests from the host first.
   593: 
   594:                 -- hm, i don't think max_members - current_members_calc yielding a negative number
   595:                 -- will work w/ rownum, swaping 'em in the body of this if...
   596:                 for virt_server in virt_servers_cfam(family.channel_family_id,
   597:                                 current_members_calc - max_members_val) loop
   598: 
   599:                     rhn_channel.unsubscribe_server_from_family(
   600:                                 virt_server.virtual_system_id,
   601:                                 family.channel_family_id);
   602:                 end loop;
   603: 
   604:                 -- if we're still over the limit, which would be odd,
   605:                 -- just prune the group to max_members
   606:                 --
   607:                 -- er... wouldn't we actually have to refresh the values of
   608:                 -- current_members_calc and max_members_val to actually ever
   609:                 -- *skip this??
   610:                 if current_members_calc > max_members_val then
   611:                     -- argh, transposed again?!
   612:                     set_family_count(org_id_val,
   613:                                      family.channel_family_id,
   614:                                      max_members_val);
   615:                 end if;
   616: 
   617:            end if;
   618: 
   619:             -- update current_members for the family.  This will set the value
   620:             -- to reflect adding/removing the entitlement.
   621:             --
   622:             -- what's the difference of doing this vs the unavoidable set_family_count above?
   623:             rhn_channel.update_family_counts(family.channel_family_id,
   624:                                              org_id_val);
   625:         end loop;
   626: 
   627:         for a_group_type in group_types loop
   628:           -- get the current *physical* members of the system entitlement type for the org...
   629:           --
   630:           -- unlike channel families, it appears the standard rhnServerGroup.max_members represents
   631:           -- *physical* slots, vs physical+virt ... boy that's confusing...
   632: 
   633:           select max_members, id
   634:             into max_members_val, sg_id
   635:             from rhnServerGroup
   636:             where group_type = a_group_type.group_type
   637:             and org_id = org_id_val;
   638: 
   639: 
   640: 	  select count(sep.server_id) into current_members_calc
   641:             from rhnServerEntitlementPhysical sep
   642:            where sep.server_group_id = sg_id
   643:              and sep.server_group_type_id = a_group_type.group_type;
   644: 
   645:           if current_members_calc > max_members_val then
   646:             -- A virtualization_host* ent must have been removed, and we're over the limit, so unsubscribe guests
   647:             for virt_server in virt_servers_sgt(a_group_type.group_type,
   648:                                                 current_members_calc - max_members_val) loop
   649:               rhn_entitlements.remove_server_entitlement(virt_server.virtual_system_id, a_group_type.label);
   650: 
   651:               -- decrement current_members_calc, we'll use it to reset current_members for the group at the end...
   652:               current_members_calc := current_members_calc - 1;
   653:             end loop;
   654: 
   655:           end if;
   656: 
   657:           update rhnServerGroup set current_members = current_members_calc
   658:            where org_id = org_id_val
   659:              and group_type = a_group_type.group_type;
   660: 
   661:           -- I think that's all the house-keeping we have to do...
   662:         end loop;
   663: 
   664:     end repoll_virt_guest_entitlements;
   665: 
   666: 
   667: 	function get_server_entitlement (
   668: 		server_id_in in number
   669: 	) return ents_array is
   670: 
   671: 		cursor server_groups is
   672: 			select	sgt.label
   673: 			from	rhnServerGroupType		sgt,
   674: 					rhnServerGroup			sg,
   675: 					rhnServerGroupMembers	sgm
   676: 			where	1=1
   677: 				and sgm.server_id = server_id_in
   678: 				and sg.id = sgm.server_group_id
   679: 				and sgt.id = sg.group_type
   680: 				and sgt.label in (
   681: 					'sw_mgr_entitled','enterprise_entitled',
   682: 					'provisioning_entitled', 'nonlinux_entitled',
   683: 					'monitoring_entitled', 'virtualization_host',
   684:                                         'virtualization_host_platform'
   685: 					);
   686: 
   687:          ent_array ents_array;
   688: 
   689: 	begin
   690: 
   691:       ent_array := ents_array();
   692: 
   693: 		for sg in server_groups loop
   694:          ent_array.extend;
   695:          ent_array(ent_array.count) := sg.label;
   696: 		end loop;
   697: 
   698: 		return ent_array;
   699: 
   700: 	end get_server_entitlement;
   701: 
   702: 
   703: 	-- this desperately needs to be table driven.
   704: 	procedure modify_org_service (
   705: 		org_id_in in number,
   706: 		service_label_in in varchar2,
   707: 		enable_in in char
   708: 	) is
   709: 		type roles_v is varray(10) of rhnUserGroupType.label%TYPE;
   710: 		roles_to_process roles_v;
   711: 		cursor roles(role_label_in in varchar2) is
   712: 			select	label, id
   713: 			from	rhnUserGroupType
   714: 			where	label = role_label_in;
   715: 		cursor org_roles(role_label_in in varchar2) is
   716: 			select	1
   717: 			from	rhnUserGroup ug,
   718: 					rhnUserGroupType ugt
   719: 			where	ugt.label = role_label_in
   720: 				and ug.org_id = org_id_in
   721: 				and ugt.id = ug.group_type;
   722: 
   723: 		type ents_v is varray(10) of rhnOrgEntitlementType.label%TYPE;
   724: 		ents_to_process ents_v;
   725: 		cursor ents(ent_label_in in varchar2) is
   726: 			select	label, id
   727: 			from	rhnOrgEntitlementType
   728: 			where	label = ent_label_in;
   729: 		cursor org_ents(ent_label_in in varchar2) is
   730: 			select	1
   731: 			from	rhnOrgEntitlements oe,
   732: 					rhnOrgEntitlementType oet
   733: 			where	oet.label = ent_label_in
   734: 				and oe.org_id = org_id_in
   735: 				and oet.id = oe.entitlement_id;
   736: 		create_row char(1);
   737: 	begin
   738: 		ents_to_process := ents_v();
   739: 		roles_to_process := roles_v();
   740: 		-- a bit kludgy, but only for 3.4 really.  Certainly no
   741: 		-- worse than the old code...
   742: 		if service_label_in = 'enterprise' or
   743:            service_label_in = 'management' then
   744: 			ents_to_process.extend;
   745: 			ents_to_process(ents_to_process.count) := 'sw_mgr_enterprise';
   746: 
   747: 			roles_to_process.extend;
   748: 			roles_to_process(roles_to_process.count) := 'org_admin';
   749: 
   750: 			roles_to_process.extend;
   751: 			roles_to_process(roles_to_process.count) := 'system_group_admin';
   752: 
   753: 			roles_to_process.extend;
   754: 			roles_to_process(roles_to_process.count) := 'activation_key_admin';
   755: 
   756: 			roles_to_process.extend;
   757: 			roles_to_process(roles_to_process.count) := 'org_applicant';
   758: 		elsif service_label_in = 'provisioning' then
   759: 			ents_to_process.extend;
   760: 			ents_to_process(ents_to_process.count) := 'rhn_provisioning';
   761: 
   762: 			roles_to_process.extend;
   763: 			roles_to_process(roles_to_process.count) := 'system_group_admin';
   764: 
   765: 			roles_to_process.extend;
   766: 			roles_to_process(roles_to_process.count) := 'activation_key_admin';
   767: 
   768: 			roles_to_process.extend;
   769: 			roles_to_process(roles_to_process.count) := 'config_admin';
   770: 			-- another nasty special case...
   771: 			if enable_in = 'Y' then
   772: 				ents_to_process.extend;
   773: 				ents_to_process(ents_to_process.count) := 'sw_mgr_enterprise';
   774: 			end if;
   775: 		elsif service_label_in = 'monitoring' then
   776: 			ents_to_process.extend;
   777: 			ents_to_process(ents_to_process.count) := 'rhn_monitor';
   778: 
   779: 			roles_to_process.extend;
   780: 			roles_to_process(roles_to_process.count) := 'monitoring_admin';
   781: 		elsif service_label_in = 'virtualization' then
   782: 			ents_to_process.extend;
   783: 			ents_to_process(ents_to_process.count) := 'rhn_virtualization';
   784: 
   785: 			roles_to_process.extend;
   786: 			roles_to_process(roles_to_process.count) := 'config_admin';
   787:         elsif service_label_in = 'virtualization_platform' then
   788: 			ents_to_process.extend;
   789: 			ents_to_process(ents_to_process.count) := 'rhn_virtualization_platform';
   790: 			roles_to_process.extend;
   791: 			roles_to_process(roles_to_process.count) := 'config_admin';
   792: 	elsif service_label_in = 'nonlinux' then
   793: 			ents_to_process.extend;
   794: 			ents_to_process(ents_to_process.count) := 'rhn_nonlinux';
   795: 			roles_to_process.extend;
   796: 			roles_to_process(roles_to_process.count) := 'config_admin';
   797: 		end if;
   798: 
   799: 		if enable_in = 'Y' then
   800: 			for i in 1..ents_to_process.count loop
   801: 				for ent in ents(ents_to_process(i)) loop
   802: 					create_row := 'Y';
   803: 					for oe in org_ents(ent.label) loop
   804: 						create_row := 'N';
   805: 					end loop;
   806: 					if create_row = 'Y' then
   807: 						insert into rhnOrgEntitlements(org_id, entitlement_id)
   808: 							values (org_id_in, ent.id);
   809: 					end if;
   810: 				end loop;
   811: 			end loop;
   812: 			for i in 1..roles_to_process.count loop
   813: 				for role in roles(roles_to_process(i)) loop
   814: 					create_row := 'Y';
   815: 					for o_r in org_roles(role.label) loop
   816: 						create_row := 'N';
   817: 					end loop;
   818: 					if create_row = 'Y' then
   819: 						insert into rhnUserGroup(
   820: 								id, name, description, current_members,
   821: 								group_type, org_id
   822: 							) (
   823: 								select	rhn_user_group_id_seq.nextval,
   824: 										ugt.name || 's',
   825: 										ugt.name || 's for Org ' ||
   826: 											o.name || ' ('|| o.id ||')',
   827: 										0, ugt.id, o.id
   828: 								from	rhnUserGroupType ugt,
   829: 										web_customer o
   830: 								where	o.id = org_id_in
   831: 									and ugt.id = role.id
   832: 							);
   833: 					end if;
   834: 				end loop;
   835: 			end loop;
   836: 		else
   837: 			for i in 1..ents_to_process.count loop
   838: 				for ent in ents(ents_to_process(i)) loop
   839: 					delete from rhnOrgEntitlements
   840: 					 where org_id = org_id_in
   841: 					   and entitlement_id = ent.id;
   842: 				end loop;
   843: 			end loop;
   844: 		end if;
   845: 	end modify_org_service;
   846: 
   847: 	procedure set_customer_enterprise (
   848: 		customer_id_in in number
   849: 	) is
   850: 	begin
   851: 		modify_org_service(customer_id_in, 'enterprise', 'Y');
   852: 	end set_customer_enterprise;
   853: 
   854: 	procedure set_customer_provisioning (
   855: 		customer_id_in in number
   856: 	) is
   857: 	begin
   858: 		modify_org_service(customer_id_in, 'provisioning', 'Y');
   859: 	end set_customer_provisioning;
   860: 
   861: 	procedure set_customer_monitoring (
   862: 		customer_id_in in number
   863: 	) is
   864: 	begin
   865: 		modify_org_service(customer_id_in, 'monitoring', 'Y');
   866: 	end set_customer_monitoring;
   867: 
   868: 	procedure set_customer_nonlinux (
   869: 		customer_id_in in number
   870: 	) is
   871: 	begin
   872: 		modify_org_service(customer_id_in, 'nonlinux', 'Y');
   873: 	end set_customer_nonlinux;
   874: 
   875: 	procedure unset_customer_enterprise (
   876: 		customer_id_in in number
   877: 	) is
   878: 	begin
   879: 		modify_org_service(customer_id_in, 'enterprise', 'N');
   880: 	end unset_customer_enterprise;
   881: 
   882: 	procedure unset_customer_provisioning (
   883: 		customer_id_in in number
   884: 	) is
   885: 	begin
   886: 		modify_org_service(customer_id_in, 'provisioning', 'N');
   887: 	end unset_customer_provisioning;
   888: 
   889: 	procedure unset_customer_monitoring (
   890: 		customer_id_in in number
   891: 	) is
   892: 	begin
   893: 		modify_org_service(customer_id_in, 'monitoring', 'N');
   894: 	end unset_customer_monitoring;
   895: 
   896: 	procedure unset_customer_nonlinux (
   897: 		customer_id_in in number
   898: 	) is
   899: 	begin
   900: 		modify_org_service(customer_id_in, 'nonlinux', 'N');
   901: 	end unset_customer_nonlinux;
   902: 
   903:     -- *******************************************************************
   904:     -- PROCEDURE: prune_group
   905:     -- Unsubscribes servers consuming physical slots that over the org's
   906:     --   limit.
   907:     -- Called by: set_group_count, prune_everything, repoll_virt_guest_entitlements
   908:     -- *******************************************************************
   909: 	procedure prune_group (
   910: 		group_id_in in number,
   911: 		type_in in char,
   912: 		quantity_in in number
   913: 	) is
   914: 		cursor usergroups is
   915: 			select	user_id, user_group_id, ugt.label
   916: 			from	rhnUserGroupType	ugt,
   917: 					rhnUserGroup		ug,
   918: 					rhnUserGroupMembers	ugm
   919: 			where	1=1
   920: 				and ugm.user_group_id = group_id_in
   921: 				and ugm.user_id in (
   922: 					select	user_id
   923: 					from	(
   924: 						select	rownum row_number,
   925: 								user_id,
   926: 								time
   927: 						from	(
   928: 							select	user_id,
   929: 									modified time
   930: 							from	rhnUserGroupMembers
   931: 							where	user_group_id = group_id_in
   932: 							order by time asc
   933: 						)
   934: 					)
   935: 					where	row_number > quantity_in
   936: 				)
   937: 				and ugm.user_group_id = ug.id
   938: 				and ug.group_type = ugt.id;
   939:         cursor servergroups is
   940:            select  server_id, server_group_id, sgt.id group_type_id, sgt.label
   941:             from    rhnServerGroupType              sgt,
   942:                             rhnServerGroup                  sg,
   943:                             rhnServerGroupMembers   sgm
   944:             where   1=1
   945:                     and sgm.server_group_id = group_id_in
   946:                     and sgm.server_id in (
   947:                             select  server_id
   948:                             from    (
   949:                                     select  rownum row_number,
   950:                                                     server_id,
   951:                                                     time
   952:                                     from    (
   953:                                             select  sep.server_id,
   954:                                                     sep.modified time
   955:                                             from
   956:                                                 rhnServerEntitlementPhysical sep
   957:                                             where
   958:                                                 sep.server_group_id = group_id_in
   959:                                             order by time asc
   960:                                     )
   961:                             )
   962:                             where   row_number > quantity_in
   963:                     )
   964:                     and sgm.server_group_id = sg.id
   965:                     and sg.group_type = sgt.id;
   966:       type_is_base char;
   967: 	begin
   968: 		if type_in = 'U' then
   969: 			update		rhnUserGroup
   970: 				set		max_members = quantity_in
   971: 				where	id = group_id_in;
   972: 
   973: 			for ug in usergroups loop
   974: 				rhn_user.remove_from_usergroup(ug.user_id, ug.user_group_id);
   975: 			end loop;
   976: 		elsif type_in = 'S' then
   977: 			update		rhnServerGroup
   978: 				set		max_members = quantity_in
   979: 				where	id = group_id_in;
   980: 
   981: 			for sg in servergroups loop
   982: 				remove_server_entitlement(sg.server_id, sg.label);
   983: 
   984:             select is_base
   985:             into type_is_base
   986:             from rhnServerGroupType sgt
   987:             where sgt.id = sg.group_type_id;
   988: 
   989:             -- if we're removing a base ent, then be sure to
   990:             -- remove the server's channel subscriptions.
   991:             if ( type_is_base = 'Y' ) then
   992: 				   rhn_channel.clear_subscriptions(sg.server_id);
   993:             end if;
   994: 
   995: 			end loop;
   996: 		end if;
   997: 	end prune_group;
   998: 
   999:     -- *******************************************************************
  1000:     -- PROCEDURE: assign_system_entitlement
  1001:     --
  1002:     -- Moves system entitlements from from_org_id_in to to_org_id_in.
  1003:     -- Can raise not_enough_entitlements_in_base_org if from_org_id_in
  1004:     -- does not have enough entitlements to cover the move.
  1005:     -- Takes care of unentitling systems if necessary by calling
  1006:     -- set_group_count
  1007:     -- *******************************************************************
  1008:     procedure assign_system_entitlement(
  1009:         group_label_in in varchar2,
  1010:         from_org_id_in in number,
  1011:         to_org_id_in in number,
  1012:         quantity_in in number
  1013:     )
  1014:     is
  1015:         prev_ent_count number;
  1016: 	to_org_prev_ent_count number;
  1017:         new_ent_count number;
  1018: 	new_quantity number;
  1019:         group_type number;
  1020:     begin
  1021: 
  1022:         begin
  1023:             select max_members
  1024:             into prev_ent_count
  1025:             from rhnServerGroupType sgt,
  1026:                  rhnServerGroup sg
  1027:             where sg.org_id = from_org_id_in
  1028:               and sg.group_type = sgt.id
  1029:               and sgt.label = group_label_in;
  1030:         exception
  1031:             when NO_DATA_FOUND then
  1032:                 rhn_exception.raise_exception(
  1033:                               'not_enough_entitlements_in_base_org');
  1034:         end;
  1035: 
  1036:         begin
  1037:             select max_members
  1038:             into to_org_prev_ent_count
  1039:             from rhnServerGroupType sgt,
  1040:                  rhnServerGroup sg
  1041:             where sg.org_id = to_org_id_in
  1042:               and sg.group_type = sgt.id
  1043:               and sgt.label = group_label_in;
  1044:         exception
  1045:             when NO_DATA_FOUND then
  1046:                 to_org_prev_ent_count := 0;
  1047:         end;
  1048: 
  1049:         begin
  1050:             select id
  1051:             into group_type
  1052:             from rhnServerGroupType
  1053:             where label = group_label_in;
  1054:         exception
  1055:             when NO_DATA_FOUND then
  1056:                 rhn_exception.raise_exception(
  1057:                               'invalid_server_group');
  1058:         end;
  1059: 
  1060:         new_ent_count := prev_ent_count - quantity_in;
  1061: 
  1062:         if prev_ent_count > new_ent_count then
  1063:             new_quantity := to_org_prev_ent_count + quantity_in;
  1064:         end if;
  1065: 
  1066:         if new_ent_count < 0 then
  1067:             rhn_exception.raise_exception(
  1068:                           'not_enough_entitlements_in_base_org');
  1069:         end if;
  1070: 
  1071: 
  1072:         rhn_entitlements.set_group_count(from_org_id_in,
  1073:                                          'S',
  1074:                                          group_type,
  1075:                                          new_ent_count);
  1076: 
  1077:         rhn_entitlements.set_group_count(to_org_id_in,
  1078:                                          'S',
  1079:                                          group_type,
  1080:                                          new_quantity);
  1081: 
  1082:         -- Create or delete the entries in rhnOrgEntitlementType
  1083:         if group_label_in = 'enterprise_entitled' then
  1084:             if new_quantity > 0 then
  1085:                 set_customer_enterprise(to_org_id_in);
  1086:             else
  1087:                 unset_customer_enterprise(to_org_id_in);
  1088:             end if;
  1089:         end if;
  1090: 
  1091:         if group_label_in = 'provisioning_entitled' then
  1092:             if new_quantity > 0 then
  1093:                 set_customer_provisioning(to_org_id_in);
  1094:             else
  1095:                 unset_customer_provisioning(to_org_id_in);
  1096:             end if;
  1097:         end if;
  1098: 
  1099:         if group_label_in = 'monitoring_entitled' then
  1100:             if new_quantity > 0 then
  1101:                 set_customer_monitoring(to_org_id_in);
  1102:             else
  1103:                 unset_customer_monitoring(to_org_id_in);
  1104:             end if;
  1105:         end if;
  1106: 
  1107:     end assign_system_entitlement;
  1108: 
  1109:     -- *******************************************************************
  1110:     -- PROCEDURE: assign_channel_entitlement
  1111:     --
  1112:     -- Moves channel entitlements from from_org_id_in to to_org_id_in.
  1113:     -- Can raise not_enough_entitlements_in_base_org if from_org_id_in
  1114:     -- does not have enough entitlements to cover the move.
  1115:     -- Takes care of unentitling systems if necessary by calling
  1116:     -- set_family_count
  1117:     -- *******************************************************************
  1118:     procedure assign_channel_entitlement(
  1119:         channel_family_label_in in varchar2,
  1120:         from_org_id_in in number,
  1121:         to_org_id_in in number,
  1122:         quantity_in in number
  1123:     )
  1124:     is
  1125:         prev_ent_count number;
  1126:         new_ent_count number;
  1127: 	to_org_prev_ent_count number;
  1128: 	new_quantity number;
  1129:         cfam_id       number;
  1130:     begin
  1131: 
  1132:         begin
  1133:             select max_members
  1134:             into prev_ent_count
  1135:             from rhnChannelFamily cf,
  1136:                  rhnPrivateChannelFamily pcf
  1137:             where pcf.org_id = from_org_id_in
  1138:               and pcf.channel_family_id = cf.id
  1139:               and cf.label = channel_family_label_in;
  1140:         exception
  1141:             when NO_DATA_FOUND then
  1142:                 rhn_exception.raise_exception(
  1143:                               'not_enough_entitlements_in_base_org');
  1144:         end;
  1145: 
  1146:         begin
  1147:             select max_members
  1148:             into to_org_prev_ent_count
  1149:             from rhnChannelFamily cf,
  1150:                  rhnPrivateChannelFamily pcf
  1151:             where pcf.org_id = to_org_id_in
  1152:               and pcf.channel_family_id = cf.id
  1153:               and cf.label = channel_family_label_in;
  1154:         exception
  1155:             when NO_DATA_FOUND then
  1156:                 to_org_prev_ent_count := 0;
  1157:         end;
  1158: 
  1159: 
  1160:         begin
  1161:             select id
  1162:             into cfam_id
  1163:             from rhnChannelFamily
  1164:             where label = channel_family_label_in;
  1165:         exception
  1166:             when NO_DATA_FOUND then
  1167:                 rhn_exception.raise_exception(
  1168:                               'invalid_channel_family');
  1169:         end;
  1170: 
  1171:         new_ent_count := prev_ent_count - quantity_in;
  1172: 
  1173: 	if prev_ent_count > new_ent_count then
  1174:             new_quantity := to_org_prev_ent_count + quantity_in;
  1175: 	end if;
  1176: 
  1177: 
  1178:         if new_ent_count < 0 then
  1179:             rhn_exception.raise_exception(
  1180:                           'not_enough_entitlements_in_base_org');
  1181:         end if;
  1182: 
  1183:         rhn_entitlements.set_family_count(from_org_id_in,
  1184:                                           cfam_id,
  1185:                                           new_ent_count);
  1186: 
  1187:         rhn_entitlements.set_family_count(to_org_id_in,
  1188:                                           cfam_id,
  1189:                                           new_quantity);
  1190: 
  1191:     end assign_channel_entitlement;
  1192: 
  1193:     -- *******************************************************************
  1194:     -- PROCEDURE: activate_system_entitlement
  1195:     --
  1196:     -- Sets the values in rhnServerGroup for a given rhnServerGroupType.
  1197:     --
  1198:     -- Calls: set_group_count to update, prune, or create the group.
  1199:     -- Called by: the code that activates a satellite cert.
  1200:     --
  1201:     -- Raises not_enough_entitlements_in_base_org if all entitlements
  1202:     -- in the org are used so the free entitlements would not cover
  1203:     -- the difference when descreasing the number of entitlements.
  1204:     -- *******************************************************************
  1205:     procedure activate_system_entitlement(
  1206:         org_id_in in number,
  1207:         group_label_in in varchar2,
  1208:         quantity_in in number
  1209:     )
  1210:     is
  1211:         prev_ent_count number;
  1212:         prev_ent_count_sum number;
  1213:         group_type number;
  1214:     begin
  1215: 
  1216:         -- Fetch the current entitlement count for the org
  1217:         -- into prev_ent_count
  1218:         begin
  1219:             select current_members
  1220:             into prev_ent_count
  1221:             from rhnServerGroupType sgt,
  1222:                  rhnServerGroup sg
  1223:             where sg.group_type = sgt.id
  1224:               and sgt.label = group_label_in
  1225:               and sg.org_id = org_id_in;
  1226:         exception
  1227:             when NO_DATA_FOUND then
  1228:                 prev_ent_count := 0;
  1229:         end;
  1230: 
  1231:         begin
  1232:             select id
  1233:             into group_type
  1234:             from rhnServerGroupType
  1235:             where label = group_label_in;
  1236:         exception
  1237:             when NO_DATA_FOUND then
  1238:                 rhn_exception.raise_exception(
  1239:                               'invalid_server_group');
  1240:         end;
  1241: 
  1242:         -- If we're setting the total entitlemnt count to a lower value,
  1243:         -- and that value is less than the allocated count in this org,
  1244:         -- we need to raise an exception.
  1245:         if quantity_in < prev_ent_count then
  1246:             rhn_exception.raise_exception(
  1247:                           'not_enough_entitlements_in_base_org');
  1248:         else
  1249:             rhn_entitlements.set_group_count(org_id_in,
  1250:                                              'S',
  1251:                                              group_type,
  1252:                                              quantity_in);
  1253:         end if;
  1254: 
  1255: 
  1256:     end activate_system_entitlement;
  1257: 
  1258:     -- *******************************************************************
  1259:     -- PROCEDURE: activate_channel_entitlement
  1260:     --
  1261:     -- Calls: set_family_count to update, prune, or create the family
  1262:     --        permission bucket.
  1263:     -- Called by: the code that activates a satellite cert.
  1264:     --
  1265:     -- Raises not_enough_entitlements_in_base_org if there are not enough
  1266:     -- entitlements in the org to cover the difference when you are
  1267:     -- descreasing the number of entitlements.
  1268:     --
  1269:     -- The backend code in Python is expected to do whatever arithmetics
  1270:     -- is needed.
  1271:     -- *******************************************************************
  1272:     procedure activate_channel_entitlement(
  1273:         org_id_in in number,
  1274:         channel_family_label_in in varchar2,
  1275:         quantity_in in number
  1276:     )
  1277:     is
  1278:         prev_ent_count number;
  1279:         prev_ent_count_sum number;
  1280:         cfam_id number;
  1281:     begin
  1282: 
  1283:         -- Fetch the current entitlement count for the org
  1284:         -- into prev_ent_count
  1285:         begin
  1286:             select current_members
  1287:             into prev_ent_count
  1288:             from rhnChannelFamily cf,
  1289:                  rhnPrivateChannelFamily pcf
  1290:             where pcf.org_id = org_id_in
  1291:               and pcf.channel_family_id = cf.id
  1292:               and cf.label = channel_family_label_in;
  1293:         exception
  1294:             when NO_DATA_FOUND then
  1295:                 prev_ent_count := 0;
  1296:         end;
  1297: 
  1298:         begin
  1299:             select id
  1300:             into cfam_id
  1301:             from rhnChannelFamily
  1302:             where label = channel_family_label_in;
  1303:         exception
  1304:             when NO_DATA_FOUND then
  1305:                 rhn_exception.raise_exception(
  1306:                               'invalid_channel_family');
  1307:         end;
  1308: 
  1309:         -- If we're setting the total entitlemnt count to a lower value,
  1310:         -- and that value is less than the count in that one org,
  1311:         -- we need to raise an exception.
  1312:         if quantity_in < prev_ent_count then
  1313:             rhn_exception.raise_exception(
  1314:                           'not_enough_entitlements_in_base_org');
  1315:         else
  1316:             rhn_entitlements.set_family_count(org_id_in,
  1317:                                               cfam_id,
  1318:                                               quantity_in);
  1319:         end if;
  1320: 
  1321:     end activate_channel_entitlement;
  1322: 
  1323: 
  1324: 	procedure set_group_count (
  1325: 		customer_id_in in number,
  1326: 		type_in in char,
  1327: 		group_type_in in number,
  1328: 		quantity_in in number
  1329: 	) is
  1330: 		group_id number;
  1331: 		quantity number;
  1332: 	begin
  1333: 		quantity := quantity_in;
  1334: 		if quantity is not null and quantity < 0 then
  1335: 			quantity := 0;
  1336: 		end if;
  1337: 
  1338: 		if type_in = 'U' then
  1339: 			select	rug.id
  1340: 			into	group_id
  1341: 			from	rhnUserGroup rug
  1342: 			where	1=1
  1343: 				and rug.org_id = customer_id_in
  1344: 				and rug.group_type = group_type_in;
  1345: 		elsif type_in = 'S' then
  1346: 			select	rsg.id
  1347: 			into	group_id
  1348: 			from	rhnServerGroup rsg
  1349: 			where	1=1
  1350: 				and rsg.org_id = customer_id_in
  1351: 				and rsg.group_type = group_type_in;
  1352: 		end if;
  1353: 
  1354: 		rhn_entitlements.prune_group(
  1355: 			group_id,
  1356: 			type_in,
  1357: 			quantity
  1358: 		);
  1359: 	exception
  1360: 		when no_data_found then
  1361: 			if type_in = 'U' then
  1362: 				insert into rhnUserGroup (
  1363: 						id, name, description, max_members, current_members,
  1364: 						group_type, org_id, created, modified
  1365: 					) (
  1366: 						select	rhn_user_group_id_seq.nextval, name, name,
  1367: 								quantity, 0, id, customer_id_in,
  1368: 								sysdate, sysdate
  1369: 						from	rhnUserGroupType
  1370: 						where	id = group_type_in
  1371: 				);
  1372: 			elsif type_in = 'S' then
  1373: 				insert into rhnServerGroup (
  1374: 						id, name, description, max_members, current_members,
  1375: 						group_type, org_id, created, modified
  1376: 					) (
  1377: 						select	rhn_server_group_id_seq.nextval, name, name,
  1378: 								quantity, 0, id, customer_id_in,
  1379: 								sysdate, sysdate
  1380: 						from	rhnServerGroupType
  1381: 						where	id = group_type_in
  1382: 				);
  1383: 			end if;
  1384: 	end set_group_count;
  1385: 
  1386:     -- *******************************************************************
  1387:     -- PROCEDURE: prune_family
  1388:     -- Unsubscribes servers consuming physical slots from the channel family
  1389:     --   that are over the org's limit.
  1390:     -- Called by: set_family_count, prune_everything
  1391:     -- *******************************************************************
  1392: 	procedure prune_family (
  1393: 		customer_id_in in number,
  1394: 		channel_family_id_in in number,
  1395: 		quantity_in in number
  1396: 	) is
  1397: 		cursor serverchannels is
  1398: 			select	sc.server_id,
  1399: 					sc.channel_id
  1400: 			from	rhnServerChannel sc,
  1401: 					rhnChannelFamilyMembers cfm
  1402: 			where	1=1
  1403: 				and cfm.channel_family_id = channel_family_id_in
  1404: 				and cfm.channel_id = sc.channel_id
  1405: 				and server_id in (
  1406: 					select	server_id
  1407: 					from	(
  1408: 						select	server_id,
  1409: 								time,
  1410: 								rownum row_number
  1411: 						from	(
  1412: 							select	rs.id					server_id,
  1413: 									rcfm.modified			time
  1414: 							from
  1415: 									rhnServerChannel		rsc,
  1416: 									rhnChannelFamilyMembers	rcfm,
  1417:                                     rhnServer				rs
  1418: 							where	1=1
  1419: 								and rs.org_id = customer_id_in
  1420: 								and rs.id = rsc.server_id
  1421: 								and rsc.channel_id = rcfm.channel_id
  1422: 								and rcfm.channel_family_id =
  1423: 									channel_family_id_in
  1424:                                 -- we only want to grab servers consuming
  1425:                                 -- physical slots.
  1426:                                 and exists (
  1427:                                     select 1
  1428:                                     from rhnChannelFamilyServerPhysical cfsp
  1429:                                     where cfsp.server_id = rs.id
  1430:                                     and cfsp.channel_family_id =
  1431:                                         channel_family_id_in
  1432:                                     )
  1433: 							order by time asc
  1434: 						)
  1435: 					)
  1436: 					where row_number > quantity_in
  1437: 				);
  1438: 	begin
  1439: 		-- if we get a null customer_id, this is completely bogus.
  1440: 		if customer_id_in is null then
  1441: 			return;
  1442: 		end if;
  1443: 
  1444: 		update		rhnPrivateChannelFamily
  1445: 			set		max_members = quantity_in
  1446: 			where	1=1
  1447: 				and org_id = customer_id_in
  1448: 				and channel_family_id = channel_family_id_in;
  1449: 
  1450: 		for sc in serverchannels loop
  1451: 			rhn_channel.unsubscribe_server(sc.server_id, sc.channel_id, 1, 1);
  1452: 		end loop;
  1453: 	end prune_family;
  1454: 
  1455: 	procedure set_family_count (
  1456: 		customer_id_in in number,
  1457: 		channel_family_id_in in number,
  1458: 		quantity_in in number
  1459: 	) is
  1460: 		cursor privperms is
  1461: 			select	1
  1462: 			from	rhnPrivateChannelFamily
  1463: 			where	org_id = customer_id_in
  1464: 				and channel_family_id = channel_family_id_in;
  1465: 		cursor pubperms is
  1466: 			select	o.id org_id
  1467: 			from	web_customer o,
  1468: 					rhnPublicChannelFamily pcf
  1469: 			where	pcf.channel_family_id = channel_family_id_in;
  1470: 		quantity number;
  1471: 		done number := 0;
  1472: 	begin
  1473: 		quantity := quantity_in;
  1474: 		if quantity is not null and quantity < 0 then
  1475: 			quantity := 0;
  1476: 		end if;
  1477: 
  1478: 		if customer_id_in is not null then
  1479: 			for perm in privperms loop
  1480: 				rhn_entitlements.prune_family(
  1481: 					customer_id_in,
  1482: 					channel_family_id_in,
  1483: 					quantity
  1484: 				);
  1485: 				update rhnPrivateChannelFamily
  1486: 					set max_members = quantity
  1487: 					where org_id = customer_id_in
  1488: 						and channel_family_id = channel_family_id_in;
  1489: 				return;
  1490: 			end loop;
  1491: 
  1492: 			insert into rhnPrivateChannelFamily (
  1493: 					channel_family_id, org_id, max_members, current_members
  1494: 				) values (
  1495: 					channel_family_id_in, customer_id_in, quantity, 0
  1496: 				);
  1497: 			return;
  1498: 		end if;
  1499: 
  1500: 		for perm in pubperms loop
  1501: 			if quantity = 0 then
  1502: 				rhn_entitlements.prune_family(
  1503: 					perm.org_id,
  1504: 					channel_family_id_in,
  1505: 					quantity
  1506: 				);
  1507: 				if done = 0 then
  1508: 					delete from rhnPublicChannelFamily
  1509: 						where channel_family_id = channel_family_id_in;
  1510: 				end if;
  1511: 			end if;
  1512: 			done := 1;
  1513: 		end loop;
  1514: 		-- if done's not 1, then we don't have any entitlements
  1515: 		if done != 1 then
  1516: 			insert into rhnPublicChannelFamily (
  1517: 					channel_family_id
  1518: 				) values (
  1519: 					channel_family_id_in
  1520: 				);
  1521: 		end if;
  1522: 	end set_family_count;
  1523: 
  1524: 	-- this expects quantity_in to be the number of available slots, not the
  1525: 	-- max_members of the server group.  If you give it too many, it'll fail
  1526: 	-- and raise servergroup_max_members.
  1527: 	-- We should NEVER run this unless we're SURE that we won't
  1528: 	-- be violating the max.
  1529: 	procedure entitle_last_modified_servers (
  1530: 		customer_id_in in number,
  1531: 		type_label_in in varchar2,
  1532: 		quantity_in in number
  1533: 	) is
  1534: 		-- find the servers that aren't currently in slots
  1535: 		cursor servers(cid_in in number, quant_in in number) is
  1536: 			select	server_id
  1537: 			from	(
  1538: 						select	rownum row_number,
  1539: 								server_id
  1540: 						from	(
  1541: 									select	rs.id server_iD
  1542: 									from	rhnServer rs
  1543: 									where	1=1
  1544: 										and rs.org_id = cid_in
  1545: 										and not exists (
  1546: 											select	1
  1547: 											from	rhnServerGroup sg,
  1548: 													rhnServerGroupMembers rsgm
  1549: 											where	rsgm.server_id = rs.id
  1550: 												and rsgm.server_group_id = sg.id
  1551: 												and sg.group_type is not null
  1552: 										)
  1553:                                         and not exists (
  1554:                                             select 1
  1555:                                             from rhnVirtualInstance vi
  1556:                                             where vi.virtual_system_id =
  1557:                                                   rs.id
  1558:                                         )                                                                           order by modified desc
  1559: 								)
  1560: 					)
  1561: 			where	row_number <= quant_in;
  1562: 	begin
  1563: 		for server in servers(customer_id_in, quantity_in) loop
  1564: 			rhn_entitlements.entitle_server(server.server_id, type_label_in);
  1565: 		end loop;
  1566: 	end entitle_last_modified_servers;
  1567: 
  1568: 	procedure prune_everything (
  1569: 		customer_id_in in number
  1570: 	) is
  1571: 		cursor everything is
  1572: 			-- all our server groups
  1573: 			select	sg.id					id,
  1574: 					'S'						type,
  1575: 					sg.max_members			quantity
  1576: 			from	rhnServerGroup			sg
  1577: 			where	sg.org_id = customer_id_in
  1578: 			union
  1579: 			-- all our user groups
  1580: 			select	ug.id					id,
  1581: 					'U'						type,
  1582: 					ug.max_members 			quantity
  1583: 			from	rhnUserGroup			ug
  1584: 			where	ug.org_id = customer_id_in
  1585: 			union (
  1586: 			-- all the channel families we have perms to
  1587: 			select	cfp.channel_family_id	id,
  1588: 					'C'						type,
  1589: 					cfp.max_members			quantity
  1590: 			from	rhnOrgChannelFamilyPermissions cfp
  1591: 			where	cfp.org_id = customer_id_in
  1592: 			union
  1593: 			-- plus all the ones we're using that we have no perms for
  1594: 			select	cfm.channel_family_id	id,
  1595: 					'C'						type,
  1596: 					0						quantity
  1597: 			from	rhnChannelFamily		cf,
  1598: 					rhnChannelFamilyMembers	cfm,
  1599: 					rhnServerChannel		sc,
  1600: 					rhnServer				s
  1601: 			where	s.org_id = customer_id_in
  1602: 				and s.id = sc.server_id
  1603: 				and sc.channel_id = cfm.channel_id
  1604: 				and cfm.channel_family_id = cf.id
  1605: 				and cf.org_id is not null
  1606: 				and cf.org_id != customer_id_in
  1607: 				and not exists (
  1608: 					select	1
  1609: 					from	rhnOrgChannelFamilyPermissions cfp
  1610: 					where	cfp.org_id = customer_id_in
  1611: 						and cfp.channel_family_id = cfm.channel_family_id
  1612: 					)
  1613: 			);
  1614: 	begin
  1615: 		for one in everything loop
  1616: 			if one.type in ('U','S') then
  1617: 				prune_group(one.id, one.type, one.quantity);
  1618: 			else
  1619: 				prune_family(customer_id_in, one.id, one.quantity);
  1620: 			end if;
  1621: 		end loop;
  1622: 	end prune_everything;
  1623: 
  1624: 	procedure subscribe_newest_servers (
  1625: 		customer_id_in in number
  1626: 	) is
  1627: 		-- find servers without base channels
  1628: 		cursor servers(cid_in in number) is
  1629: 			select	s.id
  1630: 			from	rhnServer			s
  1631: 			where	1=1
  1632: 				and s.org_id = cid_in
  1633: 				and not exists (
  1634: 						select 1
  1635: 						from	rhnChannel			c,
  1636: 								rhnServerChannel	sc
  1637: 						where	sc.server_id = s.id
  1638: 							and sc.channel_id = c.id
  1639: 							and c.parent_channel is null
  1640: 					)
  1641:                 and not exists (
  1642:                         select 1
  1643:                         from rhnVirtualInstance vi
  1644:                         where vi.virtual_system_id = s.id
  1645:                     )
  1646: 			order by s.modified desc;
  1647: 		channel_id number;
  1648: 	begin
  1649: 		for server in servers(customer_id_in) loop
  1650: 			channel_id := rhn_channel.guess_server_base(server.id);
  1651: 			if channel_id is not null then
  1652: 				begin
  1653: 					rhn_channel.subscribe_server(server.id, channel_id);
  1654: 					commit;
  1655: 				-- exception is really channel_family_no_subscriptions
  1656: 				exception
  1657: 					when others then
  1658: 						null;
  1659: 				end;
  1660: 			end if;
  1661: 		end loop;
  1662: 	end subscribe_newest_servers;
  1663: end rhn_entitlements;