245 lines
5.8 KiB
SQL
245 lines
5.8 KiB
SQL
ALTER TABLE `RolesAdditional`
|
|
CHANGE COLUMN `AdditionalRoleId` `AdditionalPositionId` INT(11) NOT NULL AUTO_INCREMENT ,
|
|
CHANGE COLUMN `OverridesHigherRoleId` `OverridesHigherPositionId` INT(11) NOT NULL ,
|
|
CHANGE COLUMN `RoleTypeId` `PositionTypeId` INT(11) NOT NULL ,
|
|
CHANGE COLUMN `ReportsToDefaultRoleId` `ReportsToStandardPositionId` INT(11) NOT NULL ,
|
|
CHANGE COLUMN `ReportsToAdditionalRoleId` `ReportsToAdditionalPositionId` INT(11) NOT NULL ,
|
|
CHANGE COLUMN `DotReportsToRoleId` `DotReportsToPositionId` INT(11) NOT NULL ,
|
|
RENAME TO `PositionsAdditional`
|
|
|
|
|
|
#
|
|
|
|
|
|
ALTER TABLE `RoleTypes`
|
|
CHANGE COLUMN `RoleTypeId` `PositionTypeId` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
|
|
RENAME TO `PositionTypes`
|
|
|
|
|
|
#
|
|
|
|
|
|
|
|
ALTER TABLE `RolesDefault`
|
|
CHANGE COLUMN `DefaultRoleId` `StandardPositionId` INT(11) NOT NULL AUTO_INCREMENT ,
|
|
CHANGE COLUMN `RoleLevel` `PositionLevel` INT(11) NOT NULL ,
|
|
CHANGE COLUMN `RoleTypeId` `PositionTypeId` INT(11) NOT NULL ,
|
|
CHANGE COLUMN `ReportsToDefaultRoleId` `ReportsToStandardPositionId` INT(11) NOT NULL ,
|
|
CHANGE COLUMN `DotReportsToDefaultRoleId` `DotReportsToStandardPositionId` INT(11) NOT NULL ,
|
|
RENAME TO `PositionsStandard`
|
|
|
|
|
|
#
|
|
|
|
|
|
ALTER TABLE `RolesAssigned`
|
|
CHANGE COLUMN `AssignedRoleId` `PositionAssignmentId` INT(11) NOT NULL AUTO_INCREMENT ,
|
|
CHANGE COLUMN `RoleId` `PositionId` INT(11) NOT NULL ,
|
|
CHANGE COLUMN `CreatedByRoleId` `CreatedByPositionId` INT(11) NOT NULL ,
|
|
CHANGE COLUMN `TerminatedByRoleId` `TerminatedByPositionId` INT(11) NOT NULL DEFAULT '0' ,
|
|
DROP INDEX `Index_Role` ,
|
|
ADD INDEX `Index_Pos` (`PositionId` ASC),
|
|
RENAME TO `PositionAssignments`
|
|
|
|
|
|
#
|
|
|
|
|
|
DROP procedure IF EXISTS `TerminateAssignedRole`
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE PROCEDURE `TerminatePositionAssignment`(
|
|
IN positionAssignmentId INT,
|
|
IN terminatedDateTimeUtc DATETIME,
|
|
IN terminatedByPersonId INT,
|
|
IN terminatedByPositionId INT,
|
|
IN terminationNotes TEXT
|
|
)
|
|
BEGIN
|
|
|
|
UPDATE PositionAssignments
|
|
SET
|
|
PositionAssignments.Active=0,
|
|
PositionAssignments.TerminatedDateTimeUtc=terminatedDateTimeUtc,
|
|
PositionAssignments.TerminatedByPersonId=terminatedByPersonId,
|
|
PositionAssignments.TerminatedByPositionId=terminatedByPositionId,
|
|
PositionAssignments.TerminationNotes=terminationNotes
|
|
WHERE
|
|
PositionAssignments.PositionAssignmentId=positionAssignmentId;
|
|
|
|
END
|
|
|
|
|
|
#
|
|
|
|
|
|
DROP procedure IF EXISTS `SetDefaultRoleActive`
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE PROCEDURE `SetStandardPositionActive`(
|
|
IN standardPositionId INT,
|
|
IN active TINYINT
|
|
)
|
|
BEGIN
|
|
|
|
UPDATE PositionsStandard
|
|
SET
|
|
PositionsStandard.Active=active
|
|
WHERE
|
|
PositionsStandard.StandardPositionId=standardPositionId;
|
|
|
|
END
|
|
|
|
|
|
#
|
|
|
|
|
|
DROP procedure IF EXISTS `SetAdditionalRoleActive`
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE PROCEDURE `SetAdditionalPositionActive`(
|
|
IN additionalPositionId INT,
|
|
IN active TINYINT
|
|
)
|
|
BEGIN
|
|
|
|
UPDATE PositionsAdditional
|
|
SET
|
|
PositionsAdditional.Active=active
|
|
WHERE
|
|
PositionsAdditional.AdditionalPositionId=additionalPositionId;
|
|
|
|
END
|
|
|
|
|
|
#
|
|
|
|
|
|
DROP procedure IF EXISTS `SetAssignedRoleActing`
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE PROCEDURE `SetPositionAssignmentActing`(
|
|
IN positionAssignmentId INT,
|
|
IN acting TINYINT
|
|
)
|
|
BEGIN
|
|
|
|
UPDATE PositionAssignments
|
|
SET
|
|
PositionAssignments.Acting=acting
|
|
WHERE
|
|
PositionAssignments.PositionAssignmentId=positionAssignmentId;
|
|
|
|
END
|
|
|
|
|
|
#
|
|
|
|
|
|
|
|
DROP procedure IF EXISTS `CreateDefaultRole`
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE PROCEDURE `CreateStandardPosition`(
|
|
IN organizationId INT,
|
|
IN positionLevel INT,
|
|
IN positionType VARCHAR(64),
|
|
IN volunteerable TINYINT,
|
|
IN overridable TINYINT,
|
|
IN reportsToStandardPositionId INT,
|
|
IN dotReportsToStandardPositionId INT,
|
|
IN minCount INT,
|
|
IN maxCount INT
|
|
)
|
|
BEGIN
|
|
|
|
DECLARE positionTypeId INTEGER;
|
|
|
|
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,Volunteerable,Overridable,ReportsToStandardPositionId,DotReportsToStandardPositionId,MinCount,MaxCount)
|
|
VALUES (organizationId,positionLevel,positionTypeId,volunteerable,overridable,reportsToStandardPositionId,dotReportsToStandardPositionId,minCount,maxCount);
|
|
|
|
SELECT LAST_INSERT_ID() AS Identity;
|
|
|
|
END
|
|
|
|
|
|
#
|
|
|
|
|
|
DROP procedure IF EXISTS `CreateAdditionalRole`
|
|
|
|
|
|
#
|
|
|
|
|
|
CREATE PROCEDURE `CreateAdditionalPosition`(
|
|
IN organizationId INT,
|
|
IN geographyId INT,
|
|
IN positionType VARCHAR(64),
|
|
IN overridesStandardPositionId INT,
|
|
IN volunteerable TINYINT,
|
|
IN inheritsDownward TINYINT,
|
|
IN reportsToStandardPositionId INT,
|
|
IN reportsToAdditionalPositionId INT,
|
|
IN dotReportsToStandardPositionId INT,
|
|
IN createdByPersonId INT,
|
|
IN createdDateTimeUtc DATETIME,
|
|
IN minCount INT,
|
|
IN maxCount INT
|
|
)
|
|
BEGIN
|
|
|
|
DECLARE positionTypeId INTEGER;
|
|
|
|
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,PositionTypeId,Volunteerable,InheritsDownward,OverridesStandardPositionId,ReportsToStandardPositionId,ReportsToAdditionalPositionId,DotReportsToStandardPositionId,CreatedByPersonId,CreatedDateTimeUtc,MinCount,MaxCount)
|
|
VALUES (organizationId,geographyId,positionTypeId,volunteerable,inheritsDownward,overridesStandardPositionId,reportsToStandardPositionId,reportsToAdditionalPositionId,dotReportsToStandardPositionId,createdByPersonId,createdDateTimeUtc,minCount,maxCount);
|
|
|
|
SELECT LAST_INSERT_ID() AS Identity;
|
|
|
|
END
|
|
|
|
|