148 lines
3.1 KiB
SQL
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
|
|
|