labtech Database

Parameters

Name	Type	Mode

Definition

BEGIN
	/* add contacts into groups from last executed searches */
	INSERT IGNORE
	INTO SubGroupsContacts(GroupID, ContactID) (
		SELECT MasterGroups.GroupID,
			Searches.ContactID
		FROM MasterGroups
		JOIN Searches ON Searches.SearchID = MasterGroups.ContactJoin
		WHERE ContactJoin > 0);
	/* remove contacts from groups where the search options were excusionary */
	DELETE SubGroupsContacts
	FROM SubGroupsContacts
	JOIN MasterGroups USING (GroupID)
	WHERE (MasterGroups.ContactJoinOptions = 1
		AND ContactID NOT IN (
			SELECT ContactID
			FROM Searches
			WHERE SearchID = MasterGroups.ContactJoin))
		OR ContactID NOT IN (SELECT ContactID FROM `contacts`);
	/* create temp table of (groupid, ContactID) where group is not a master and contact belongs to another group that is a master */
	CREATE TEMPORARY TABLE IF NOT EXISTS GContacts
	SELECT *
	FROM SubGroupsContacts
	WHERE GroupID IN (
			SELECT GroupID
			FROM MasterGroups
			WHERE `master` = 0)
		AND ContactID IN (
			SELECT ContactID
			FROM SubGroupsContacts
			JOIN MasterGroups USING (groupID)
			WHERE MasterGroups.ContactJoin > 0
				AND MasterGroups.Master = 1);
	/* use above temp table to remove devices from non-master groups if they're already in other master groups */
	DELETE SubGroupsContacts
	FROM SubGroupsContacts
	JOIN GContacts USING (ContactID, GroupID);
	DROP TEMPORARY TABLE GContacts;	/* goodbye temp table */
END

sp_AutoJoinContacts

Parameters

Definition