labtech Database

Parameters

Name	Type	Mode

Definition

BEGIN
 DECLARE GID INT DEFAULT 0;
 DECLARE GL VARCHAR(100000);
 Set @GID=0;
 SET group_concat_max_len=100000;
 Select GroupId from MasterGroups Where Name='All Clients' and Depth=0 order by GroupId limit 1 INTO @GID;
  IF @GID=0 THEN
   Insert Into MasterGroups (Name,FullName,depth,Notes,Children,Permissions,Template,AutoJoinScript,MaintenanceID,MaintWindowApplied,GroupType, Priority) Values('All Clients','All Clients',0,'This group is automatically created by the LabTech system to contain all your clients.','',0,0,0,0,Now(),1, 5);
   Select GroupId from MasterGroups Where Name='All Clients' and Depth=0 order by GroupId limit 1 INTO @GID;
 END IF;
 IF @GID>0 THEN
   INSERT IGNORE INTO MasterGroups (NAME, FullName,Notes, Control,ControlID,ParentID,Parents,Depth,`Master`,Children,Permissions,Template,AutoJoinScript,MaintenanceID,MaintWindowApplied,GroupType,Priority,GUID) (SELECT Clients.Name,CONCAT('All Clients.',Clients.Name),'Auto Created for easy Permission Setting.',1,Clients.ClientID,@GID,CONCAT(',',@GID,','),1,2,'',0,0,0,0,NOW(),1, 5, UUID() FROM Clients WHERE (flags & 4) = 0 AND ClientID NOT IN (SELECT ControlID FROM MasterGroups WHERE Control=1 AND ParentID=@GID));
   DELETE FROM MasterGroups WHERE ParentID=@GID AND controlID NOT IN (SELECT ClientID FROM Clients WHERE (flags & 4) = 0);
   UPDATE MasterGroups JOIN Clients ON MasterGroups.ControlID=Clients.ClientID SET MasterGroups.Name=Clients.Name, Fullname=CONCAT('All Clients.',Clients.Name) WHERE MasterGroups.Control=1 AND MasterGroups.ParentID=@GID AND MasterGroups.Name<>Clients.Name;
   SELECT GROUP_CONCAT(Groupid) FROM Mastergroups WHERE ParentID=@GID into @GL;
   UPDATE MasterGroups SET Children=CONCAT(',',@GL,',') WHERE GroupID=@GID;
 END IF;
END

sp_DoAllClients

Parameters

Definition