Files
2015-02-18 23:16:48 +01:00

168 lines
4.4 KiB
SQL

ALTER TABLE `PositionTypes`
CHANGE COLUMN `PositionTypeId` `PositionTypeId` INT(11) UNSIGNED NOT NULL,
CHANGE COLUMN `Name` `Name` VARCHAR(128) NOT NULL
#
CREATE TABLE `PositionTitles` (
`PositionTitleId` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
`Name` VARCHAR(128) NOT NULL,
PRIMARY KEY (`PositionTitleId`),
KEY `Index_Name` (`Name`)
)
#
ALTER TABLE `PositionsAdditional`
ADD COLUMN `PositionTitleId` INT NOT NULL AFTER `PositionTypeId`,
CHANGE COLUMN `Active` `Active` TINYINT(4) NOT NULL DEFAULT '1' AFTER `InheritsDownward`,
ADD COLUMN `Overridable` TINYINT NOT NULL AFTER `Volunteerable`
#
ALTER TABLE `PositionsStandard`
ADD COLUMN `PositionTitleId` INT NOT NULL AFTER `PositionTypeId`
#
ALTER TABLE `PositionAssignments`
CHANGE COLUMN `ExpiresDateTimeUtc` `ExpiresDateTimeUtc` DATETIME NOT NULL AFTER `Active`
#
DROP procedure IF EXISTS `CreateAdditionalPosition`
#
CREATE PROCEDURE `CreateAdditionalPosition`(
IN organizationId INT,
IN geographyId INT,
IN positionType VARCHAR(128),
IN positionTitle VARCHAR(128),
IN overridesHigherPositionId INT,
IN createdByPersonId INT,
IN createdDateTimeUtc DATETIME,
IN volunteerable TINYINT,
IN overridable TINYINT,
IN inheritsDownward TINYINT,
IN reportsToStandardPositionId INT,
IN reportsToAdditionalPositionId INT,
IN dotReportsToStandardPositionId 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 PositionsAdditional (OrganizationId,GeographyId,OverridesHigherPositionId,CreatedByPersonId,CreatedDateTimeUtc,PositionTypeId,PositionTitleId,InheritsDownward,Volunteerable,Overridable,ReportsToStandardPositionId,ReportsToAdditionalPositionId,DotReportsToStandardPositionId,MinCount,MaxCount)
VALUES (organizationId,geographyId,overridesHigherPositionId,createdByPersonId,createdDateTimeUtc,positionTypeId,positionTitleId,inheritsDownward,volunteerable,overridable,reportsToStandardPositionId,reportsToAdditionalPositionId,dotReportsToStandardPositionId,minCount,maxCount);
SELECT LAST_INSERT_ID() AS Identity;
END
#
DROP procedure IF EXISTS `CreateStandardPosition`
#
CREATE PROCEDURE `CreateStandardPosition`(
IN organizationId INT,
IN positionLevel INT,
IN positionType VARCHAR(128),
IN positionTitle VARCHAR(128),
IN volunteerable TINYINT,
IN overridable TINYINT,
IN reportsToStandardPositionId INT,
IN dotReportsToStandardPositionId 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 PositionsStandard (OrganizationId,PositionLevel,PositionTypeId,PositionTitleId,Volunteerable,Overridable,ReportsToStandardPositionId,DotReportsToStandardPositionId,MinCount,MaxCount)
VALUES (organizationId,positionLevel,positionTypeId,positionTitleId,volunteerable,overridable,reportsToStandardPositionId,dotReportsToStandardPositionId,minCount,maxCount);
SELECT LAST_INSERT_ID() AS Identity;
END