42 lines
1.3 KiB
MySQL
42 lines
1.3 KiB
MySQL
|
DELIMITER $$
|
||
|
|
CREATE OR REPLACE DEFINER=`root`@`localhost` PROCEDURE `vn`.`parking_add`(vFromColumn INT, vToColumn INT, vFromRow INT, vToRow INT, vSectorFk INT, vIsLetterMode BOOLEAN)
|
||
|
|
BEGIN
|
||
|
|
|
||
|
|
DECLARE vColumn INT;
|
||
|
|
DECLARE vRow INT;
|
||
|
|
DECLARE vLetters VARCHAR(26) DEFAULT 'ABCDEFGHIJKLMNOPQRSTUVWXYZ';
|
||
|
|
DECLARE vMaxId INT;
|
||
|
|
DECLARE vCode VARCHAR(8);
|
||
|
|
|
||
|
|
SET vColumn = vFromColumn;
|
||
|
|
SET vRow = vFromRow;
|
||
|
|
SET vCode = CONCAT(LPAD(vColumn,3,'0'),'-',IF(vIsLetterMode, MID(vLetters, vRow, 1), LPAD(vRow, 2,'0')));
|
||
|
|
|
||
|
|
SELECT MAX(id) INTO vMaxId
|
||
|
|
FROM vn.parking;
|
||
|
|
|
||
|
|
WHILE vColumn <= vToColumn DO
|
||
|
|
|
||
|
|
WHILE vRow <= vToRow DO
|
||
|
|
|
||
|
|
INSERT IGNORE INTO vn.parking(`column`, `row`, sectorFk, `code`, pickingOrder)
|
||
|
|
VALUES (vColumn, vRow, vSectorFk, vCode, vColumn * 100 + vRow )
|
||
|
|
ON DUPLICATE KEY UPDATE `code` = vCode;
|
||
|
|
|
||
|
|
SET vRow = vRow + 1;
|
||
|
|
SET vCode = CONCAT(LPAD(vColumn,3,'0'),'-',IF(vIsLetterMode, MID(vLetters, vRow, 1), LPAD(vRow, 2,'0')));
|
||
|
|
|
||
|
|
END WHILE;
|
||
|
|
|
||
|
|
SET vRow = vFromRow;
|
||
|
|
SET vColumn = vColumn + 1;
|
||
|
|
|
||
|
|
SET vCode = CONCAT(LPAD(vColumn,3,'0'),'-',IF(vIsLetterMode, MID(vLetters, vRow, 1), LPAD(vRow, 2,'0')));
|
||
|
|
|
||
|
|
END WHILE;
|
||
|
|
|
||
|
|
SELECT * FROM vn.parking WHERE `column` BETWEEN vFromColumn AND vToColumn;
|
||
|
|
|
||
|
|
END$$
|
||
|
|
DELIMITER ;
|