Subversion Repositories computer_asset_manager_v1

drop table if exists license;

create table license (

   license_id int unsigned not null auto_increment,

   client_id  int unsigned null comment 'link to client table',

   device_id   int unsigned null comment 'link to device table',

   license text not null comment 'the actual license text',

   license_product_id int unsigned not null references license_product(license_product_id),

   added_date date default null comment 'date the license used on this machine',

   removed_date date default null comment 'date license removed from this machine',

   primary key (license_id)

) comment 'Holds the license and links to machine installed on' ;

drop table if exists license_product;

create table license_product (

   license_product_id int unsigned not null auto_increment,

   name text comment 'name of the license',

   primary key ( license_product_id )

) comment 'a list of all products we track license for';

delete from _system where key_name = 'Licenses';

insert into _system ( group_name,key_name,theValue,added_date) values ( 'Modules', 'Licenses', 'modules/license/', now() );

delete from menu 

where 

   menu.parent_id in 

      ( select menu_id 

        from ( 

           select distinct a.menu_id as menu_id 

           from menu a join menu b on a.menu_id = b.menu_id 

           where a.caption = 'Licenses' 

           ) 

      as c );

delete from menu where caption = 'Licenses';

insert into menu( url, caption, parent_id) values ('/modules/license/', 'Licenses', null);

insert into menu( url,caption,parent_id) select '/modules/license/bulk_load.html','Bulk Load',menu_id from menu where caption = 'Licenses';

insert into menu( url,caption,parent_id) select '/modules/license/add_edit.html','Edit',menu_id from menu where caption = 'Licenses';

create or replace view view_device_systems as select * from device where device_type_id in (select device_type_id from device_type where show_as_system = 'Y');

/* DO NOT RUN BELOW THIS LINE */

select 

   license_product.name 'Product',

   license.license 'License',

   ifnull(device.name,'--- Available ---') 'Installed On',

   client.name 'Client'

from

   license join license_product using (license_product_id)

   left outer join device using (device_id)

   join client using ( client_id )

order by license_product.name, device.name;