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';
insert into menu( url,caption,parent_id) select '/modules/license/find.html','Find',menu_id from menu where caption = 'Licenses';


/* 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;