labtech Database

Parameters

Name	Type	Mode

Definition

BEGIN

INSERT IGNORE INTO SubGroups (GROUPID,COMPUTERID)
	(SELECT GroupID,ComputerID FROM MasterGroups
	 JOIN Computers ON Computers.ClientID=MasterGroups.ControlID
	 AND MasterGroups.Control=1);
DELETE SubGroups FROM SubGroups
	JOIN Mastergroups USING(groupid)
	JOIN Computers USING (ComputeriD)
	WHERE mastergroups.control=1 AND Computers.ClientID<>MasterGroups.ControlID;

INSERT IGNORE INTO SubGroupsNetworkDevices (GroupID, DeviceId)
	(SELECT mg.GroupID, n.DeviceID FROM MasterGroups mg
	 JOIN Locations l ON l.ClientID=mg.ControlID
	 JOIN NetworkDevices n ON n.LocationID=l.LocationID
	 WHERE mg.Control=1);
DELETE sgn FROM SubGroupsNetworkDevices sgn
	JOIN MasterGroups mg ON mg.GroupID=sgn.GroupID
	JOIN NetworkDevices n ON n.DeviceID=sgn.DeviceID
	JOIN Locations l ON l.LocationID=n.LocationID
	WHERE mg.Control=1 AND l.ClientID <> mg.ControlID;

INSERT IGNORE INTO SubGroupsContacts (GroupID, ContactID)
	(SELECT mg.GroupID, c.ContactID FROM MasterGroups mg
	 JOIN Contacts c ON mg.ControlID = c.ClientID WHERE mg.Control=1);
DELETE sgc FROM SubGroupsContacts sgc
	JOIN MasterGroups mg ON mg.GroupID=sgc.GroupID
	JOIN Contacts c ON c.ContactID=sgc.ContactID
	WHERE mg.Control=1 AND c.ClientID <> mg.ControlID;

INSERT IGNORE INTO SUBGROUPS(GROUPID,COMPUTERID)
	(SELECT GroupID,ComputerID FROM MasterGroups
	 JOIN Computers ON Computers.LocationID=MasterGroups.ControlID
	 AND MasterGroups.Control=2);
DELETE SubGroups FROM SubGroups
	JOIN Mastergroups USING(groupid)
	JOIN Computers USING (ComputeriD)
	WHERE mastergroups.control=2 AND Computers.LocationID<>MasterGroups.ControlID;

INSERT IGNORE INTO SubGroupsNetworkDevices (GroupID, DeviceID)
	(SELECT mg.GroupID, n.DeviceID FROM MasterGroups mg
	 JOIN NetworkDevices n ON n.LocationID=mg.ControlID
	 WHERE mg.Control=2);
DELETE sgn FROM SubGroupsNetworkDevices sgn
	JOIN Mastergroups mg USING (GroupID)
	JOIN NetworkDevices nd ON nd.DeviceID=sgn.DeviceID
	WHERE mg.Control=2 AND mg.ControlID <> nd.LocationID;

INSERT IGNORE INTO SubGroupsContacts (GroupID, ContactID)
	(SELECT mg.GroupID, c.ContactID FROM MasterGroups mg
	 JOIN Contacts c ON c.LocationID = mg.ControlID
	 WHERE mg.Control=2);
DELETE sgc FROM SubGroupsContacts sgc
	JOIN MasterGroups mg USING (GroupID)
	JOIN Contacts c ON c.ContactID = sgc.ContactID
	WHERE mg.Control=2 AND mg.ControlID <> c.LocationID;

UPDATE MasterGroups JOIN Clients ON MasterGroups.ControlID=Clients.ClientID
	SET MasterGroups.Name=Clients.Name
	WHERE MasterGroups.Control=1 AND MasterGroups.Name<>Clients.Name;
UPDATE MasterGroups JOIN Locations ON MasterGroups.ControlID=Locations.LocationID
	SET MasterGroups.Name=Locations.Name
	WHERE MasterGroups.Control=2 AND MasterGroups.Name<>Locations.Name;
UPDATE MasterGroups
	SET MasterGroups.FullName=f_GroupFullName(MasterGroups.GroupID)
	WHERE MasterGroups.Control>0;
END

sp_LinkedGroups

Parameters

Definition