168 lines
4.4 KiB
SQL
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
|
|
|