HRM-10/db/Script/PMS_SquareAlign_IDP_DB_Changes.sql

361 lines
15 KiB
MySQL
Raw Permalink Normal View History

2026-11-03 13:06:00 +06:00
/* =============================================================================
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