labtech Database

Parameters

Name	Type	Mode
FID	int	IN
VName	varchar(30)	IN

Definition

proc_label: BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE extradataID INT;
  DECLARE extradataname VARCHAR(254);
  DECLARE basetablename VARCHAR(30);
  DECLARE pkey VARCHAR(100);
  DECLARE pkeyalias VARCHAR(30);
  DECLARE pname VARCHAR(100);
  DECLARE cur1 CURSOR FOR SELECT ID, NAME FROM extrafield WHERE Form = FID;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
  -- This procedure is special and needs a higher group_contact_max_len. Set it here for the session.
  SET SESSION group_concat_max_len = 10000000;
  -- Before declaring the cursor, let's set the variables that change based upon what FID/Form we're scanning against.
  -- pkey is the primary key of the newly created table, and pname is the name column we use to identify the 2nd column in the table.
	-- SPECIAL CASE! Tickets does not have a pname. This has an exception later.
 IF VName= 'Computers' THEN
	CALL v_ExtraDataRefreshComputers('v_extradatacomputers');
	LEAVE proc_label;
 END IF;
  CASE 
	WHEN VName = 'Probe' THEN
		SET pkey = 'computerid';
		SET pname = 'name';
		SET basetablename = 'Computers';
	WHEN VName = 'Locations' THEN
		SET pkey = 'locationid';
		SET pname = 'name';
		SET basetablename = 'Locations';
	WHEN VName = 'Clients' THEN
		SET pkey = 'clientid';
		SET pname = 'name';
		SET basetablename = 'Clients';
	WHEN VName = 'NetDevice' THEN
		SET pkey = 'id';
		SET pname = 'name';
		SET basetablename = 'NetworkDevices';
	WHEN VName = 'Tickets' THEN
		SET pkey = 'id';
		SET basetablename = 'Tickets';
	WHEN VName = 'Groups' THEN
		SET pkey = 'groupid';
		SET pname = 'name';
		SET basetablename = 'MasterGroups';
  END CASE;
  OPEN cur1;
  REPEAT
	FETCH cur1 INTO extradataID, extradataname;
	-- Insert the data.
		CASE 
			WHEN VName IN ('Locations', 'Clients', 'Probe', 'Groups') THEN
				SELECT CONCAT('INSERT IGNORE INTO `v_extradata', VName, '` (`', 
																			pkey, '`, `', 
																			pname, '`, `', 
																			extradataname, 
																		'`)', 
							' SELECT `', pkey, '`,
 `', 
										pname, '`,
', 
										'IFNULL(`efd`.`Value`, IFNULL(`efd2`.`Value`, '')) as `', extradataname, '`
', 
							'from `', basetablename, '` `src` 
', 
							'left join `ExtraFieldData` `efd` on `efd`.`ID`=`src`.`', pkey, '` and `efd`.`extrafieldid`=', extradataID, ' 
', 
							'left join `ExtraFieldData` `efd2` on `efd2`.`ID` = 0 and `efd2`.`extrafieldid`=', extradataID, '
',
							'Group by `src`.`', pkey, '`
', 
							'ON DUPLICATE KEY UPDATE `', extradataname, '`=VALUES(`', extradataname, '`);') INTO @datastatement;
			WHEN VName = 'Tickets' THEN
				SELECT CONCAT('INSERT IGNORE INTO `v_extradata', VName, '` (`', 
																pkey, '`, `',  
																extradataname, 
															'`)', 
				' SELECT `TicketID`,
', 
						'IFNULL(`efd`.`Value`, IFNULL(`efd2`.`Value`, '')) as `', extradataname, '`
', 
				'from `', basetablename, '` `src` 
', 
				'left join `ExtraFieldData` `efd` on `efd`.`ID`=`src`.`TicketID` and `efd`.`extrafieldid`=', extradataID, ' 
', 
				'left join `ExtraFieldData` `efd2` on `efd2`.`ID` = 0 and `efd2`.`extrafieldid`=', extradataID, '
',
				'Group by `src`.`TicketID`
', 
				'ON DUPLICATE KEY UPDATE `', extradataname, '`=VALUES(`', extradataname, '`);') INTO @datastatement;
			WHEN VName = 'NetDevice' THEN
				SELECT CONCAT('INSERT IGNORE INTO `v_extradata', VName, '` (`', 
																pkey, '`, `',
																pname, '`, `',
																extradataname, 
															'`)', 
				' SELECT `deviceid`,
 `', 
						pname, '`,
', 
						'IFNULL(`efd`.`Value`, IFNULL(`efd2`.`Value`, '')) as `', extradataname, '`
', 
				'from `', basetablename, '` `src` 
', 
				'left join `ExtraFieldData` `efd` on `efd`.`ID`=`src`.`deviceid` and `efd`.`extrafieldid`=', extradataID, ' 
', 
				'left join `ExtraFieldData` `efd2` on `efd2`.`ID` = 0 and `efd2`.`extrafieldid`=', extradataID, '
',
				'Group by `src`.`deviceid`
', 
				'ON DUPLICATE KEY UPDATE `', extradataname, '`=VALUES(`', extradataname, '`);') INTO @datastatement;
		END CASE;
		PREPARE stmtinsert FROM @datastatement;
		EXECUTE stmtinsert;
		SET @datastatement = '';
	UNTIL done END REPEAT;
  CLOSE cur1;
END