labtech Database

Parameters

Name	Type	Mode
TableName	varchar(100)	IN

Definition

BEGIN
DECLARE done INT DEFAULT 0;
DECLARE extradataID INT;
DECLARE extradataname VARCHAR(254);
DECLARE cur1 CURSOR FOR SELECT ID, NAME FROM extrafield WHERE Form = 1;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
SET SESSION group_concat_max_len = 10000000;
SET @statement = '
(SELECT c.computerId, c.name';
SET @outsideQuery =CONCAT('INSERT INTO ',TableName,' (Select t.Computerid,t.Name');
SET @updateStatement= '';
DROP TEMPORARY TABLE IF EXISTS t2;
DROP TEMPORARY TABLE IF EXISTS tempComputers;
CREATE TEMPORARY TABLE tempComputers (PRIMARY KEY computerid(computerid)) SELECT computerid,`name` FROM computers;
SET @tempStatement = 'Create temporary table t2 (primary key pkey(computerid)) SELECT 0 as computerid ';
-- create out query
-- create inner query for all computers
-- create default temp table query
OPEN cur1;
mainLoop: LOOP
	FETCH cur1 INTO extradataID, extradataname;
	IF done = 1 THEN
		LEAVE mainLoop;
	END IF;
	SET @outsideQuery = CONCAT_WS(", ",@outsideQuery,CONCAT('
 IFNULL(t.`',extradataname,'`,t2.`',extradataname,'`) as `',extradataname,'`'));
	SET @statement = CONCAT_WS(", ",@statement,CONCAT('
(SELECT VALUE FROM extrafielddata WHERE extrafieldid=',extradataID,' AND id=c.computerid) AS `',extradataname,'`'));
	SET @tempStatement = CONCAT_WS(", ",@tempStatement,CONCAT('
(SELECT IFNULL(VALUE,'') AS `VALUE` FROM extrafielddata WHERE extrafieldid=',extradataID,' AND id=0) AS `',extradataname,'`'));
	SET @updateStatement = CONCAT_WS(',',@updateStatement,CONCAT('`',extradataname,'`=VALUES(`',extradataname,'`)'));
END LOOP mainLoop;
CLOSE cur1;
SET @tempStatement = CONCAT(@tempStatement,'
 From dual;');
PREPARE tempSQL FROM @tempStatement;
EXECUTE tempSQL;
SET @statement = CONCAT(@statement,'
 From tempComputers c) AS t');
SET @updateStatement = TRIM(LEADING ',' FROM @updateStatement);
SET @outsideQuery = CONCAT(@outsideQuery,' 
 From ',@statement,'
 Join t2) ON DUPLICATE KEY UPDATE ',@updateStatement,';');
PREPARE finalSQL FROM @outsideQuery;
EXECUTE finalSQL;
DROP TEMPORARY TABLE IF EXISTS t2;
DROP TEMPORARY TABLE IF EXISTS tempComputers;
END

v_ExtraDataRefreshComputers

Parameters

Definition