361 lines
15 KiB
Transact-SQL
361 lines
15 KiB
Transact-SQL
/* =============================================================================
|
|
PMS (Management) alignment with Square_PMS + IDP (Independent Development Plan)
|
|
|
|
Covers the schema needed by the port of Square_PMS commits c7db84791..09b4d40c7
|
|
into this product, plus the IDP feature (my-idp / team IDP / IDP dashboard),
|
|
which had never been ported before.
|
|
|
|
Every statement is guarded, so the whole file is re-runnable and safe to run
|
|
against a database where part of it has already been applied. Nothing here is
|
|
conditional on a payroll type or any other variable - a previous PMS migration
|
|
gated its entire body behind a NULL variable and therefore silently did nothing,
|
|
so this file deliberately has no such gate.
|
|
|
|
Run order: sections are independent except that section 2 creates
|
|
dbo.ObjectiveHistory before section 2b adds columns to it, and section 7 creates
|
|
dbo.TrainingNeedAnalysisHistory. Run top to bottom.
|
|
|
|
NOTE: this schema has no IDENTITY columns anywhere - new primary keys come from
|
|
TransactionContext.GenerateID (SELECT MAX(col) + 1). New tables below follow
|
|
that convention and must NOT be given IDENTITY.
|
|
============================================================================= */
|
|
|
|
/* QUOTED_IDENTIFIER must be ON for the filtered index in section 6. SSMS sets it ON by
|
|
default but sqlcmd does NOT, so without this the CREATE INDEX fails with Msg 1934. */
|
|
SET QUOTED_IDENTIFIER ON;
|
|
SET NOCOUNT ON;
|
|
GO
|
|
|
|
/* -----------------------------------------------------------------------------
|
|
1. dbo.Objective - line manager / 2nd line manager KPI-setting remarks
|
|
ObjectiveDA.Insert / UpdateObjectiveSettingRemarksLM / ...LM2
|
|
----------------------------------------------------------------------------- */
|
|
IF COL_LENGTH('dbo.Objective', 'ObjectiveSettingRemarksLM') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.Objective ADD ObjectiveSettingRemarksLM NVARCHAR(MAX) NULL;
|
|
PRINT 'Added dbo.Objective.ObjectiveSettingRemarksLM';
|
|
END
|
|
GO
|
|
|
|
IF COL_LENGTH('dbo.Objective', 'ObjectiveSettingRemarksLM2') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.Objective ADD ObjectiveSettingRemarksLM2 NVARCHAR(MAX) NULL;
|
|
PRINT 'Added dbo.Objective.ObjectiveSettingRemarksLM2';
|
|
END
|
|
GO
|
|
|
|
/* -----------------------------------------------------------------------------
|
|
2. dbo.ObjectiveHistory - staging table for KPI changes
|
|
Created defensively: an earlier PMS migration was expected to create this
|
|
table but could not be relied on to have run.
|
|
----------------------------------------------------------------------------- */
|
|
IF OBJECT_ID('dbo.ObjectiveHistory', 'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.ObjectiveHistory
|
|
(
|
|
ObjectiveHistoryID INT NOT NULL,
|
|
ObjectiveSetID INT NULL,
|
|
EmployeeID INT NULL,
|
|
ObjectiveID INT NULL,
|
|
PMPYearID INT NULL,
|
|
ChangeType INT NULL,
|
|
ObjectiveTypeID INT NULL,
|
|
Title NVARCHAR(500) NULL,
|
|
Target NVARCHAR(MAX) NULL,
|
|
FromDate DATETIME NULL,
|
|
ToDate DATETIME NULL,
|
|
Weightage FLOAT NULL,
|
|
NewTitle NVARCHAR(500) NULL,
|
|
NewTarget NVARCHAR(MAX) NULL,
|
|
NewFromDate DATETIME NULL,
|
|
NewToDate DATETIME NULL,
|
|
NewWeightage FLOAT NULL,
|
|
Remarks NVARCHAR(MAX) NULL,
|
|
UserID INT NULL,
|
|
CreatedDate DATETIME NULL,
|
|
IsSubmitted BIT NULL,
|
|
/* Set when the change was raised outside the objective-setting window and
|
|
is therefore awaiting an explicit line-manager decision. */
|
|
OutsideOfSettings BIT NOT NULL CONSTRAINT DF_ObjectiveHistory_OutsideOfSettings DEFAULT (0),
|
|
/* Deliberately NULLable: the code tests "IsApproved IS NULL" to find rows
|
|
still awaiting a decision, so a NOT NULL default would break that. */
|
|
IsApproved BIT NULL,
|
|
LMActionRemarks NVARCHAR(MAX) NULL,
|
|
LMActionDate DATETIME NULL,
|
|
CONSTRAINT PK_ObjectiveHistory PRIMARY KEY CLUSTERED (ObjectiveHistoryID)
|
|
);
|
|
PRINT 'Created dbo.ObjectiveHistory';
|
|
END
|
|
GO
|
|
|
|
/* 2b. Columns added to dbo.ObjectiveHistory by this port (no-ops if section 2
|
|
just created the table). */
|
|
IF COL_LENGTH('dbo.ObjectiveHistory', 'PMPYearID') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.ObjectiveHistory ADD PMPYearID INT NULL;
|
|
PRINT 'Added dbo.ObjectiveHistory.PMPYearID';
|
|
END
|
|
GO
|
|
|
|
IF COL_LENGTH('dbo.ObjectiveHistory', 'OutsideOfSettings') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.ObjectiveHistory
|
|
ADD OutsideOfSettings BIT NOT NULL
|
|
CONSTRAINT DF_ObjectiveHistory_OutsideOfSettings DEFAULT (0);
|
|
PRINT 'Added dbo.ObjectiveHistory.OutsideOfSettings';
|
|
END
|
|
GO
|
|
|
|
IF COL_LENGTH('dbo.ObjectiveHistory', 'IsApproved') IS NULL
|
|
BEGIN
|
|
/* NULL = no line-manager decision yet. Must stay NULLable. */
|
|
ALTER TABLE dbo.ObjectiveHistory ADD IsApproved BIT NULL;
|
|
PRINT 'Added dbo.ObjectiveHistory.IsApproved';
|
|
END
|
|
GO
|
|
|
|
IF COL_LENGTH('dbo.ObjectiveHistory', 'LMActionRemarks') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.ObjectiveHistory ADD LMActionRemarks NVARCHAR(MAX) NULL;
|
|
PRINT 'Added dbo.ObjectiveHistory.LMActionRemarks';
|
|
END
|
|
GO
|
|
|
|
IF COL_LENGTH('dbo.ObjectiveHistory', 'LMActionDate') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.ObjectiveHistory ADD LMActionDate DATETIME NULL;
|
|
PRINT 'Added dbo.ObjectiveHistory.LMActionDate';
|
|
END
|
|
GO
|
|
|
|
IF COL_LENGTH('dbo.ObjectiveHistory', 'IsSubmitted') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.ObjectiveHistory ADD IsSubmitted BIT NULL;
|
|
PRINT 'Added dbo.ObjectiveHistory.IsSubmitted';
|
|
END
|
|
GO
|
|
|
|
/* -----------------------------------------------------------------------------
|
|
3. dbo.ObjectiveAttachment - link an attachment to a staged (not yet approved)
|
|
KPI change. NULL for every attachment that belongs to a live Objective row.
|
|
ObjectiveDA.InsertObjectiveAttachment / GetObjectiveAttachmentsByHistoryID
|
|
----------------------------------------------------------------------------- */
|
|
IF OBJECT_ID('dbo.ObjectiveAttachment', 'U') IS NOT NULL
|
|
AND COL_LENGTH('dbo.ObjectiveAttachment', 'ObjectiveHistoryID') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.ObjectiveAttachment ADD ObjectiveHistoryID INT NULL;
|
|
PRINT 'Added dbo.ObjectiveAttachment.ObjectiveHistoryID';
|
|
END
|
|
ELSE IF OBJECT_ID('dbo.ObjectiveAttachment', 'U') IS NULL
|
|
PRINT 'WARNING: dbo.ObjectiveAttachment does not exist - KPI attachments will fail until it is created.';
|
|
GO
|
|
|
|
/* -----------------------------------------------------------------------------
|
|
4. dbo.ObjectiveSet - IDP (Independent Development Plan) workflow
|
|
ObjectiveSetDA.SubmitDevelopmentPlan / approveDevelopmentPlan /
|
|
rejectDevelopmentPlan / UpdateIDPLMIDRemarksAndStatus / GetSearchIDPDashboard
|
|
Status values are enumIDPStatus: 1=Drafted 2=Submitted 3=Reverted 4=Approved
|
|
----------------------------------------------------------------------------- */
|
|
IF COL_LENGTH('dbo.ObjectiveSet', 'CareerExpectations') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.ObjectiveSet ADD CareerExpectations NVARCHAR(MAX) NULL;
|
|
PRINT 'Added dbo.ObjectiveSet.CareerExpectations';
|
|
END
|
|
GO
|
|
|
|
IF COL_LENGTH('dbo.ObjectiveSet', 'IDPEmployeeStatus') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.ObjectiveSet ADD IDPEmployeeStatus INT NULL;
|
|
PRINT 'Added dbo.ObjectiveSet.IDPEmployeeStatus';
|
|
END
|
|
GO
|
|
|
|
IF COL_LENGTH('dbo.ObjectiveSet', 'IDPEmployeeDate') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.ObjectiveSet ADD IDPEmployeeDate DATETIME NULL;
|
|
PRINT 'Added dbo.ObjectiveSet.IDPEmployeeDate';
|
|
END
|
|
GO
|
|
|
|
IF COL_LENGTH('dbo.ObjectiveSet', 'IDPLMID') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.ObjectiveSet ADD IDPLMID INT NULL;
|
|
PRINT 'Added dbo.ObjectiveSet.IDPLMID';
|
|
END
|
|
GO
|
|
|
|
IF COL_LENGTH('dbo.ObjectiveSet', 'IDPLMRDate') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.ObjectiveSet ADD IDPLMRDate DATETIME NULL;
|
|
PRINT 'Added dbo.ObjectiveSet.IDPLMRDate';
|
|
END
|
|
GO
|
|
|
|
IF COL_LENGTH('dbo.ObjectiveSet', 'IDPLMStatus') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.ObjectiveSet ADD IDPLMStatus INT NULL;
|
|
PRINT 'Added dbo.ObjectiveSet.IDPLMStatus';
|
|
END
|
|
GO
|
|
|
|
IF COL_LENGTH('dbo.ObjectiveSet', 'IDPSecondLMID') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.ObjectiveSet ADD IDPSecondLMID INT NULL;
|
|
PRINT 'Added dbo.ObjectiveSet.IDPSecondLMID';
|
|
END
|
|
GO
|
|
|
|
IF COL_LENGTH('dbo.ObjectiveSet', 'IDPSecondLMRDate') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.ObjectiveSet ADD IDPSecondLMRDate DATETIME NULL;
|
|
PRINT 'Added dbo.ObjectiveSet.IDPSecondLMRDate';
|
|
END
|
|
GO
|
|
|
|
IF COL_LENGTH('dbo.ObjectiveSet', 'IDPSecondLMStatus') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.ObjectiveSet ADD IDPSecondLMStatus INT NULL;
|
|
PRINT 'Added dbo.ObjectiveSet.IDPSecondLMStatus';
|
|
END
|
|
GO
|
|
|
|
/* IDPLMREMARKS lives on BOTH tables, by design of the ported code:
|
|
- dbo.ObjectiveSet written by ObjectiveSetDA.UpdateLineManagerRemarks
|
|
("Update ObjectiveSet set idplmremarks = ...") and read
|
|
UNGUARDED by ObjectiveSetService.MapObject off SELECT *,
|
|
so its absence breaks every ObjectiveSet read, not just IDP.
|
|
- dbo.ObjectiveSetRemarks read as OSR.IDPLMREMARKS by the IDP selects (section 5).
|
|
Both are required. The IDP selects are "SELECT OS.*, ..., OSR.IDPLMREMARKS", so the result
|
|
set carries the name twice and ADO.NET renames the second to IDPLMREMARKS1 - harmless, and
|
|
left as Square has it. */
|
|
IF COL_LENGTH('dbo.ObjectiveSet', 'IDPLMREMARKS') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.ObjectiveSet ADD IDPLMREMARKS NVARCHAR(MAX) NULL;
|
|
PRINT 'Added dbo.ObjectiveSet.IDPLMREMARKS';
|
|
END
|
|
GO
|
|
|
|
/* -----------------------------------------------------------------------------
|
|
5. dbo.ObjectiveSetRemarks - IDP line manager remarks.
|
|
Read as OSR.IDPLMREMARKS by the IDP selects in ObjectiveSetDA.
|
|
----------------------------------------------------------------------------- */
|
|
IF OBJECT_ID('dbo.ObjectiveSetRemarks', 'U') IS NOT NULL
|
|
AND COL_LENGTH('dbo.ObjectiveSetRemarks', 'IDPLMREMARKS') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.ObjectiveSetRemarks ADD IDPLMREMARKS NVARCHAR(MAX) NULL;
|
|
PRINT 'Added dbo.ObjectiveSetRemarks.IDPLMREMARKS';
|
|
END
|
|
ELSE IF OBJECT_ID('dbo.ObjectiveSetRemarks', 'U') IS NULL
|
|
PRINT 'WARNING: dbo.ObjectiveSetRemarks does not exist - the IDP screens will fail until it is created.';
|
|
GO
|
|
|
|
/* -----------------------------------------------------------------------------
|
|
6. dbo.TrainingNeedAnalysis - the IDP learning rows.
|
|
IDP reuses the existing Training Need Analysis table and adds four columns.
|
|
TrainingNeedAnalysisDA.Insert / Update, TrainingNeedAnalysisService.MapObject
|
|
ExpectedDate is held as text, not a date, because the BO property is a string
|
|
("by when" is free text on the form).
|
|
----------------------------------------------------------------------------- */
|
|
IF COL_LENGTH('dbo.TrainingNeedAnalysis', 'NeedType') IS NULL
|
|
BEGIN
|
|
/* EnumNeedType: 0=None 1=On_the_job 2=Coaching_or_Mentoring 3=Formal_Learning */
|
|
ALTER TABLE dbo.TrainingNeedAnalysis ADD NeedType INT NULL;
|
|
PRINT 'Added dbo.TrainingNeedAnalysis.NeedType';
|
|
END
|
|
GO
|
|
|
|
IF COL_LENGTH('dbo.TrainingNeedAnalysis', 'ExpectedDate') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.TrainingNeedAnalysis ADD ExpectedDate NVARCHAR(50) NULL;
|
|
PRINT 'Added dbo.TrainingNeedAnalysis.ExpectedDate';
|
|
END
|
|
GO
|
|
|
|
IF COL_LENGTH('dbo.TrainingNeedAnalysis', 'ExpectedResults') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.TrainingNeedAnalysis ADD ExpectedResults NVARCHAR(MAX) NULL;
|
|
PRINT 'Added dbo.TrainingNeedAnalysis.ExpectedResults';
|
|
END
|
|
GO
|
|
|
|
IF COL_LENGTH('dbo.TrainingNeedAnalysis', 'ObjectiveSetID') IS NULL
|
|
BEGIN
|
|
ALTER TABLE dbo.TrainingNeedAnalysis ADD ObjectiveSetID INT NULL;
|
|
PRINT 'Added dbo.TrainingNeedAnalysis.ObjectiveSetID';
|
|
END
|
|
GO
|
|
|
|
/* Existing non-IDP TNA rows have no objective set. The IDP screens filter by
|
|
ObjectiveSetID, so leaving these NULL keeps them out of IDP entirely. */
|
|
IF COL_LENGTH('dbo.TrainingNeedAnalysis', 'ObjectiveSetID') IS NOT NULL
|
|
AND NOT EXISTS (SELECT 1 FROM sys.indexes
|
|
WHERE name = 'IX_TrainingNeedAnalysis_ObjectiveSetID'
|
|
AND object_id = OBJECT_ID('dbo.TrainingNeedAnalysis'))
|
|
BEGIN
|
|
CREATE NONCLUSTERED INDEX IX_TrainingNeedAnalysis_ObjectiveSetID
|
|
ON dbo.TrainingNeedAnalysis (ObjectiveSetID)
|
|
WHERE ObjectiveSetID IS NOT NULL;
|
|
PRINT 'Created IX_TrainingNeedAnalysis_ObjectiveSetID';
|
|
END
|
|
GO
|
|
|
|
/* -----------------------------------------------------------------------------
|
|
7. dbo.TrainingNeedAnalysisHistory - per-column audit of IDP learning rows.
|
|
TrainingNeedAnalysisHistoryDA / TrainingNeedAnalysisHistoryService
|
|
ID is application-generated (GenerateID) - do not make it an IDENTITY.
|
|
----------------------------------------------------------------------------- */
|
|
IF OBJECT_ID('dbo.TrainingNeedAnalysisHistory', 'U') IS NULL
|
|
BEGIN
|
|
CREATE TABLE dbo.TrainingNeedAnalysisHistory
|
|
(
|
|
ID INT NOT NULL,
|
|
UserID INT NULL,
|
|
EmpID INT NULL,
|
|
/* EnumPMPActorType - who made the change (employee / LM / 2nd LM). */
|
|
EmpType INT NULL,
|
|
UpdateDate DATETIME NULL,
|
|
ColumnName NVARCHAR(200) NULL,
|
|
OldValue NVARCHAR(MAX) NULL,
|
|
NewValue NVARCHAR(MAX) NULL,
|
|
Remarks NVARCHAR(MAX) NULL,
|
|
Version INT NULL,
|
|
CreatedBy INT NULL,
|
|
CreatedDate DATETIME NULL,
|
|
ModifiedBy INT NULL,
|
|
ModifiedDate DATETIME NULL,
|
|
CONSTRAINT PK_TrainingNeedAnalysisHistory PRIMARY KEY CLUSTERED (ID)
|
|
);
|
|
PRINT 'Created dbo.TrainingNeedAnalysisHistory';
|
|
END
|
|
GO
|
|
|
|
IF OBJECT_ID('dbo.TrainingNeedAnalysisHistory', 'U') IS NOT NULL
|
|
AND NOT EXISTS (SELECT 1 FROM sys.indexes
|
|
WHERE name = 'IX_TrainingNeedAnalysisHistory_EmpID'
|
|
AND object_id = OBJECT_ID('dbo.TrainingNeedAnalysisHistory'))
|
|
BEGIN
|
|
CREATE NONCLUSTERED INDEX IX_TrainingNeedAnalysisHistory_EmpID
|
|
ON dbo.TrainingNeedAnalysisHistory (EmpID);
|
|
PRINT 'Created IX_TrainingNeedAnalysisHistory_EmpID';
|
|
END
|
|
GO
|
|
|
|
/* -----------------------------------------------------------------------------
|
|
8. Menu rows.
|
|
The new screens (IDP Dashboard, Mgt KPI Dashboard, Development Plan (IDP),
|
|
Team IDP, Team IDP Assessment) are added to
|
|
ClientApp/src/assets/resource/Menu.xml. The menu is data, not code: upload
|
|
that file through Authentication > Configuration > Menu so the rows land in
|
|
dbo.ConfigurationAttribute and sync onto dbo.RolePermission. No SQL here.
|
|
|
|
Routes added:
|
|
/talent-management/idp-dashboard
|
|
/talent-management/mgt-kpi-dashboard
|
|
/ess/my-idp
|
|
/ess/independent-development-plan/firstLM
|
|
/ess/independent-development-plan/LMAssesment
|
|
----------------------------------------------------------------------------- */
|
|
|
|
PRINT 'PMS_SquareAlign_IDP_DB_Changes.sql completed.';
|
|
GO
|