124 lines
2.8 KiB
MySQL
124 lines
2.8 KiB
MySQL
|
DELIMITER $$
|
||
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`zone_getLeaves`(
|
||
|
|
vSelf INT,
|
||
|
|
vParentFk INT,
|
||
|
|
vSearch VARCHAR(255),
|
||
|
|
vHasInsert BOOL
|
||
|
|
)
|
||
|
|
BEGIN
|
||
|
|
/**
|
||
|
|
* Devuelve las ubicaciones incluidas en la ruta y que sean hijos de parentFk.
|
||
|
|
* @param vSelf Id de la zona
|
||
|
|
* @param vParentFk Id del geo a calcular
|
||
|
|
* @param vSearch Cadena a buscar
|
||
|
|
* @param vHasInsert Indica si inserta en tmp.zoneNodes
|
||
|
|
* Optional @table tmp.zoneNodes(geoFk, name, parentFk, sons, isChecked, zoneFk)
|
||
|
|
*/
|
||
|
|
DECLARE vIsNumber BOOL;
|
||
|
|
DECLARE vIsSearch BOOL DEFAULT vSearch IS NOT NULL AND vSearch <> '';
|
||
|
|
|
||
|
|
CREATE OR REPLACE TEMPORARY TABLE tNodes
|
||
|
|
(UNIQUE (id))
|
||
|
|
ENGINE = MEMORY
|
||
|
|
SELECT id
|
||
|
|
FROM zoneGeo
|
||
|
|
LIMIT 0;
|
||
|
|
|
||
|
|
IF vIsSearch THEN
|
||
|
|
SET vIsNumber = vSearch REGEXP '^[0-9]+$';
|
||
|
|
|
||
|
|
INSERT INTO tNodes
|
||
|
|
SELECT id
|
||
|
|
FROM zoneGeo
|
||
|
|
WHERE (vIsNumber AND `name` = vSearch)
|
||
|
|
OR (!vIsNumber AND `name` LIKE CONCAT('%', vSearch, '%'))
|
||
|
|
LIMIT 1000;
|
||
|
|
|
||
|
|
ELSEIF vParentFk IS NULL THEN
|
||
|
|
INSERT INTO tNodes
|
||
|
|
SELECT geoFk
|
||
|
|
FROM zoneIncluded
|
||
|
|
WHERE zoneFk = vSelf;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
IF vParentFk IS NULL THEN
|
||
|
|
CREATE OR REPLACE TEMPORARY TABLE tChilds
|
||
|
|
(INDEX(id))
|
||
|
|
ENGINE = MEMORY
|
||
|
|
SELECT id FROM tNodes;
|
||
|
|
|
||
|
|
CREATE OR REPLACE TEMPORARY TABLE tParents
|
||
|
|
(INDEX(id))
|
||
|
|
ENGINE = MEMORY
|
||
|
|
SELECT id FROM zoneGeo LIMIT 0;
|
||
|
|
|
||
|
|
myLoop: LOOP
|
||
|
|
DELETE FROM tParents;
|
||
|
|
INSERT INTO tParents
|
||
|
|
SELECT parentFk id
|
||
|
|
FROM zoneGeo g
|
||
|
|
JOIN tChilds c ON c.id = g.id
|
||
|
|
WHERE g.parentFk IS NOT NULL;
|
||
|
|
|
||
|
|
INSERT IGNORE INTO tNodes
|
||
|
|
SELECT id FROM tParents;
|
||
|
|
|
||
|
|
IF NOT ROW_COUNT() THEN
|
||
|
|
LEAVE myLoop;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
DELETE FROM tChilds;
|
||
|
|
INSERT INTO tChilds
|
||
|
|
SELECT id FROM tParents;
|
||
|
|
END LOOP;
|
||
|
|
|
||
|
|
DROP TEMPORARY TABLE tChilds, tParents;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
IF NOT vIsSearch THEN
|
||
|
|
INSERT IGNORE INTO tNodes
|
||
|
|
SELECT id
|
||
|
|
FROM zoneGeo
|
||
|
|
WHERE parentFk <=> vParentFk;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
CREATE OR REPLACE TEMPORARY TABLE tZones
|
||
|
|
SELECT g.id,
|
||
|
|
g.name,
|
||
|
|
g.parentFk,
|
||
|
|
g.sons,
|
||
|
|
NOT g.sons OR `type` = 'country' isChecked,
|
||
|
|
i.isIncluded selected,
|
||
|
|
g.`depth`,
|
||
|
|
vSelf
|
||
|
|
FROM zoneGeo g
|
||
|
|
JOIN tNodes n ON n.id = g.id
|
||
|
|
LEFT JOIN zoneIncluded i ON i.geoFk = g.id
|
||
|
|
AND i.zoneFk = vSelf
|
||
|
|
ORDER BY g.`depth`, selected DESC, g.name;
|
||
|
|
|
||
|
|
IF vHasInsert THEN
|
||
|
|
INSERT IGNORE INTO tmp.zoneNodes(geoFk, name, parentFk, sons, isChecked, zoneFk)
|
||
|
|
SELECT id,
|
||
|
|
name,
|
||
|
|
parentFk,
|
||
|
|
sons,
|
||
|
|
isChecked,
|
||
|
|
vSelf
|
||
|
|
FROM tZones
|
||
|
|
WHERE selected
|
||
|
|
OR (selected IS NULL AND vParentFk IS NOT NULL);
|
||
|
|
ELSE
|
||
|
|
SELECT id,
|
||
|
|
name,
|
||
|
|
parentFk,
|
||
|
|
sons,
|
||
|
|
selected
|
||
|
|
FROM tZones
|
||
|
|
ORDER BY `depth`, selected DESC, name;
|
||
|
|
END IF;
|
||
|
|
|
||
|
|
DROP TEMPORARY TABLE tNodes, tZones;
|
||
|
|
END$$
|
||
|
|
DELIMITER ;
|