sp_DoAllClients

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