112 lines
3.0 KiB
SQL
112 lines
3.0 KiB
SQL
ALTER TABLE `ExpenseClaims`
|
|
ADD COLUMN `ExpenseClaimGroupId` INT(11) NOT NULL DEFAULT 0 AFTER `ClosedDateTime`,
|
|
ADD INDEX `Index_GroupId` (`ExpenseClaimGroupId` ASC)
|
|
|
|
#
|
|
|
|
CREATE TABLE `ExpenseClaimGroups` (
|
|
`ExpenseClaimGroupId` INT NOT NULL AUTO_INCREMENT,
|
|
`OrganizationId` INT NOT NULL,
|
|
`CreatedDateTime` DATETIME NOT NULL,
|
|
`CreatedByPersonId` INT NOT NULL,
|
|
`Open` TINYINT NOT NULL DEFAULT 1,
|
|
`ExpenseClaimGroupTypeId` INT NOT NULL,
|
|
`ExpenseClaimGroupData` TEXT NOT NULL,
|
|
PRIMARY KEY (`ExpenseClaimGroupId`),
|
|
INDEX `Idx_Person` (`CreatedByPersonId` ASC),
|
|
INDEX `Idx_Time` (`CreatedDateTime` ASC),
|
|
INDEX `Idx_Org` (`OrganizationId` ASC),
|
|
INDEX `Idx_Open` (`Open` ASC),
|
|
INDEX `Idx_Type` (`ExpenseClaimGroupTypeId` ASC))
|
|
|
|
#
|
|
|
|
CREATE TABLE `ExpenseClaimGroupTypes` (
|
|
`ExpenseClaimGroupTypeId` INT NOT NULL AUTO_INCREMENT,
|
|
`Name` VARCHAR(64) NOT NULL,
|
|
PRIMARY KEY (`ExpenseClaimGroupTypeId`))
|
|
|
|
#
|
|
|
|
DROP PROCEDURE IF EXISTS `CreateExpenseClaimGroup`
|
|
|
|
#
|
|
|
|
DROP PROCEDURE IF EXISTS `SetExpenseClaimGroup`
|
|
|
|
#
|
|
|
|
DROP PROCEDURE IF EXISTS `SetExpenseClaimGroupClosed`
|
|
|
|
#
|
|
|
|
CREATE PROCEDURE `CreateExpenseClaimGroup`(
|
|
IN organizationId INT,
|
|
IN dateTime DATETIME,
|
|
IN createdByPersonId INT,
|
|
IN expenseClaimGroupType VARCHAR(64),
|
|
IN expenseClaimGroupData TEXT
|
|
)
|
|
BEGIN
|
|
|
|
DECLARE expenseClaimGroupTypeId INT;
|
|
|
|
IF ((SELECT COUNT(*) FROM ExpenseClaimGroupTypes WHERE ExpenseClaimGroupTypes.Name=expenseClaimGroupType) = 0)
|
|
THEN
|
|
INSERT INTO ExpenseClaimGroupTypes (Name) VALUES (expenseClaimGroupType);
|
|
SELECT LAST_INSERT_ID() INTO expenseClaimGroupTypeId;
|
|
ELSE
|
|
SELECT ExpenseClaimGroupTypes.ExpenseClaimGroupTypeId
|
|
INTO expenseclaimGroupTypeId FROM ExpenseClaimGroupTypes
|
|
WHERE ExpenseClaimGroupTypes.Name = expenseClaimGroupType;
|
|
END IF;
|
|
|
|
INSERT INTO ExpenseClaimGroups (OrganizationId,CreatedDateTime,CreatedByPersonId,ExpenseClaimGroupTypeId,ExpenseClaimGroupData)
|
|
VALUES (organizationId,dateTime,createdByPersonId,expenseClaimGroupTypeId,expenseClaimGroupData);
|
|
|
|
SELECT LAST_INSERT_ID() AS Identity;
|
|
|
|
END
|
|
|
|
#
|
|
|
|
CREATE PROCEDURE `SetExpenseClaimGroupClosed` (
|
|
IN expenseClaimGroupId INT
|
|
)
|
|
BEGIN
|
|
DECLARE createdByPersonId INT;
|
|
DECLARE organizationId INT;
|
|
|
|
SELECT ExpenseClaimGroups.CreatedByPersonId
|
|
INTO createdByPersonId FROM ExpenseClaimGroups
|
|
WHERE ExpenseClaimGroups.ExpenseClaimGroupId = expenseClaimGroupId;
|
|
|
|
SELECT ExpenseClaimGroups.OrganizationId
|
|
INTO organizationId FROM ExpenseClaimGroups
|
|
WHERE ExpenseClaimGroups.ExpenseClaimGroupId = expenseClaimGroupId;
|
|
|
|
UPDATE ExpenseClaimGroups
|
|
SET ExpenseClaimGroups.Open = 0
|
|
WHERE ExpenseClaimGroups.ExpenseClaimGroupId = expenseClaimGroupId;
|
|
|
|
UPDATE ExpenseClaims
|
|
SET ClaimingPersonId = createdByPersonId,
|
|
OrganizationId = organizationId
|
|
WHERE ExpenseClaimGroupId = expenseClaimGroupId;
|
|
|
|
END
|
|
|
|
#
|
|
|
|
CREATE PROCEDURE `SetExpenseClaimGroup` (
|
|
IN expenseClaimId INT,
|
|
IN expenseClaimGroupId INT
|
|
)
|
|
BEGIN
|
|
UPDATE ExpenseClaims
|
|
SET ExpenseClaimGroupId=expenseClaimGroupId
|
|
WHERE ExpenseClaimId=expenseClaimId;
|
|
END
|
|
|
|
|