Files

148 lines
3.1 KiB
SQL

DROP TABLE IF EXISTS `PositionsStandard`
#
DROP TABLE IF EXISTS `Positions`
#
ALTER TABLE `PositionsAdditional`
DROP COLUMN `ReportsToAdditionalPositionId`,
CHANGE COLUMN `AdditionalPositionId` `PositionId` INT(11) NOT NULL AUTO_INCREMENT,
CHANGE COLUMN `ReportsToStandardPositionId` `ReportsToPositionId` INT(11) NOT NULL,
ADD COLUMN `PositionLevel` INT NOT NULL AFTER `PositionId`,
ADD COLUMN `CreatedByPositionId` INT NOT NULL AFTER `CreatedByPersonId`,
DROP INDEX `Index_Report3`,
ADD INDEX `Index_Report2` (`DotReportsToPositionId` ASC),
ADD INDEX `Index_Level` (`PositionLevel` ASC),
DROP INDEX `Index_Report2`,
RENAME TO `Positions`
#
DROP procedure IF EXISTS `CreateAdditionalPosition`;
#
DROP procedure IF EXISTS `CreateStandardPosition`;
#
DROP procedure IF EXISTS `CreatePosition`;
#
CREATE PROCEDURE `CreatePosition`(
IN positionLevel INT,
IN organizationId INT,
IN geographyId INT,
IN overridesHigherPositionId INT,
IN createdByPersonId INT,
IN createdByPositionId INT,
IN createdDateTimeUtc DATETIME,
IN positionType VARCHAR(128),
IN positionTitle VARCHAR(128),
IN inheritsDownward TINYINT,
-- active defaults to true
IN volunteerable TINYINT,
IN overridable TINYINT,
-- covert defaults to false
IN reportsToPositionId INT,
IN dotReportsToPositionId INT,
IN minCount INT,
IN maxCount INT
)
BEGIN
DECLARE positionTypeId INTEGER;
DECLARE positionTitleId INTEGER;
IF ((SELECT COUNT(*) FROM PositionTitles WHERE PositionTitles.Name=positionTitle) = 0)
THEN
INSERT INTO PositionTitles (Name)
VALUES (positionTitle);
SELECT LAST_INSERT_ID() INTO positionTitleId;
ELSE
SELECT PositionTitles.PositionTitleId INTO positionTitleId FROM PositionTitles
WHERE PositionTitles.Name=positionTitle;
END IF;
IF ((SELECT COUNT(*) FROM PositionTypes WHERE PositionTypes.Name=positionType) = 0)
THEN
INSERT INTO PositionTypes (Name)
VALUES (positionType);
SELECT LAST_INSERT_ID() INTO positionTypeId;
ELSE
SELECT PositionTypes.PositionTypeId INTO positionTypeId FROM PositionTypes
WHERE PositionTypes.Name=positionType;
END IF;
INSERT INTO Positions (PositionLevel,OrganizationId,GeographyId,OverridesHigherPositionId,CreatedByPersonId,CreatedByPositionId,CreatedDateTimeUtc,PositionTypeId,PositionTitleId,InheritsDownward,Volunteerable,Overridable,ReportsToPositionId,DotReportsToPositionId,MinCount,MaxCount)
VALUES (positionLevel,organizationId,geographyId,overridesHigherPositionId,createdByPersonId,createdByPositionId,createdDateTimeUtc,positionTypeId,positionTitleId,inheritsDownward,volunteerable,overridable,reportsToPositionId,dotReportsToPositionId,minCount,maxCount);
SELECT LAST_INSERT_ID() AS Identity;
END
#
DROP procedure IF EXISTS `SetStandardPositionActive`
#
DROP procedure IF EXISTS `SetAdditionalPositionActive`
#
DROP procedure IF EXISTS `SetPositionActive`
#
CREATE PROCEDURE `SetPositionActive`(
IN positionId INT,
IN active TINYINT
)
BEGIN
UPDATE Positions
SET
Positions.Active=active
WHERE
Positions.PositionId=positionId;
END