namespace OKRDAL.QueryBuilders; // MOBJECTIVE carries no TENANTID column — scoping is by OBJECTIVEID/PARENTOBJECTIVEID only. // MOBJECTIVE also carries no OKRCYCLEID; FORPERIODID stands in for "which OKR cycle" per FORPERIODTYPE. public static class ObjectiveQB { private const string SELECT_COLS = @" o.OBJECTIVEID AS ObjectiveId, o.OBJECTIVENAME AS ObjectiveName, o.DESCRIPTION AS Description, o.MISSIONID AS MissionId, o.PERSPECTIVEID AS PerspectiveId, ISNULL(p.NAME, '') AS PerspectiveName, o.STRATEGYID AS StrategyId, o.REMARKS AS Remarks, o.SORTORDER AS SortOrder, o.STATUS AS Status, o.VERSION AS Version, o.SOURCETYPE AS SourceType, o.CREATEDBYID AS CreatedById, o.CREATEDON AS CreatedOn, o.MODIFIEDBYID AS ModifiedById, o.MODIFIEDON AS ModifiedOn, o.DOMAINID AS DomainId, o.SECTIONID AS SectionId, o.FORLEVEL AS ForLevel, o.FOROBJECTID AS ForObjectId, o.FORPERIODTYPE AS ForPeriodType, o.FORPERIODID AS ForPeriodId, o.FORPERIODFROM AS ForPeriodFrom, o.FORPERIODTO AS ForPeriodTo, o.OWNERID AS OwnerId, ISNULL(e.EMPLOYEENAME, '') AS OwnerName, o.LINKEDOBJECTIVEID AS LinkedObjectiveId, o.PARENTOBJECTIVEID AS ParentObjectiveId, ISNULL(par.OBJECTIVENAME, '') AS ParentName, o.PARENTOBJECTIVEIDS AS ParentObjectiveIds, o.OBJECTIVESTATUS AS ObjectiveStatus, o.SECURITYLEVEL AS SecurityLevel, o.PUBLICPRIVATE AS PublicPrivate, o.DUEDATE AS DueDate, o.WEIGHTAGE AS Weightage, o.PERCENTAGE AS Percentage, o.OBJECTIVEPRIORITY AS ObjectivePriority, o.TASKID AS TaskId, o.OBJECTIVETYPE AS ObjectiveType, o.TEMPLATEID AS TemplateId, o.OVERALLPROGRESS AS OverallProgress"; public const string GET_OBJECTIVE = @" SELECT " + SELECT_COLS + @", 0 AS TreeLevel FROM MOBJECTIVE o LEFT JOIN MOKRPERSPECTIVE p ON p.PERSPECTIVEID = o.PERSPECTIVEID LEFT JOIN MEMPLOYEE e ON e.EMPLOYEEID = o.OWNERID LEFT JOIN MOBJECTIVE par ON par.OBJECTIVEID = o.PARENTOBJECTIVEID WHERE o.OBJECTIVEID = @ObjectiveId"; public const string GET_OBJECTIVE_LIST = @" SELECT " + SELECT_COLS + @", 0 AS TreeLevel FROM MOBJECTIVE o LEFT JOIN MOKRPERSPECTIVE p ON p.PERSPECTIVEID = o.PERSPECTIVEID LEFT JOIN MEMPLOYEE e ON e.EMPLOYEEID = o.OWNERID LEFT JOIN MOBJECTIVE par ON par.OBJECTIVEID = o.PARENTOBJECTIVEID WHERE o.FORPERIODID = @OKRCycleId AND (@ObjectiveType = -1 OR o.OBJECTIVETYPE = @ObjectiveType) AND (@OwnerId = 0 OR o.OWNERID = @OwnerId) AND (@ForLevel = -1 OR o.FORLEVEL = @ForLevel) AND (@ForObjectId = 0 OR o.FOROBJECTID = @ForObjectId) ORDER BY o.OBJECTIVETYPE, o.OBJECTIVEID"; public const string GET_OBJECTIVE_LIST_COUNT = @" SELECT COUNT(1) FROM MOBJECTIVE o WHERE o.FORPERIODID = @OKRCycleId AND (@ObjectiveType = -1 OR o.OBJECTIVETYPE = @ObjectiveType) AND (@OwnerId = 0 OR o.OWNERID = @OwnerId) AND (@ForLevel = -1 OR o.FORLEVEL = @ForLevel) AND (@ForObjectId = 0 OR o.FOROBJECTID = @ForObjectId)"; // Recursive CTE — returns flat list with TreeLevel; frontend builds tree // OPTION (MAXRECURSION 10) guards against runaway deep hierarchy public const string GET_OBJECTIVE_TREE = @" WITH ObjectiveTree AS ( SELECT o.OBJECTIVEID, o.OBJECTIVENAME, o.DESCRIPTION, o.MISSIONID, o.PERSPECTIVEID, o.STRATEGYID, o.REMARKS, o.SORTORDER, o.STATUS, o.VERSION, o.SOURCETYPE, o.CREATEDBYID, o.CREATEDON, o.MODIFIEDBYID, o.MODIFIEDON, o.DOMAINID, o.SECTIONID, o.FORLEVEL, o.FOROBJECTID, o.FORPERIODTYPE, o.FORPERIODID, o.FORPERIODFROM, o.FORPERIODTO, o.OWNERID, o.LINKEDOBJECTIVEID, o.PARENTOBJECTIVEID, o.PARENTOBJECTIVEIDS, o.OBJECTIVESTATUS, o.SECURITYLEVEL, o.PUBLICPRIVATE, o.DUEDATE, o.WEIGHTAGE, o.PERCENTAGE, o.OBJECTIVEPRIORITY, o.TASKID, o.OBJECTIVETYPE, o.TEMPLATEID, o.OVERALLPROGRESS, 0 AS TreeLevel FROM MOBJECTIVE o WHERE o.PARENTOBJECTIVEID = -1 AND o.FORPERIODID = @OKRCycleId UNION ALL SELECT o.OBJECTIVEID, o.OBJECTIVENAME, o.DESCRIPTION, o.MISSIONID, o.PERSPECTIVEID, o.STRATEGYID, o.REMARKS, o.SORTORDER, o.STATUS, o.VERSION, o.SOURCETYPE, o.CREATEDBYID, o.CREATEDON, o.MODIFIEDBYID, o.MODIFIEDON, o.DOMAINID, o.SECTIONID, o.FORLEVEL, o.FOROBJECTID, o.FORPERIODTYPE, o.FORPERIODID, o.FORPERIODFROM, o.FORPERIODTO, o.OWNERID, o.LINKEDOBJECTIVEID, o.PARENTOBJECTIVEID, o.PARENTOBJECTIVEIDS, o.OBJECTIVESTATUS, o.SECURITYLEVEL, o.PUBLICPRIVATE, o.DUEDATE, o.WEIGHTAGE, o.PERCENTAGE, o.OBJECTIVEPRIORITY, o.TASKID, o.OBJECTIVETYPE, o.TEMPLATEID, o.OVERALLPROGRESS, ot.TreeLevel + 1 FROM MOBJECTIVE o INNER JOIN ObjectiveTree ot ON o.PARENTOBJECTIVEID = ot.OBJECTIVEID ) SELECT ot.OBJECTIVEID AS ObjectiveId, ot.OBJECTIVENAME AS ObjectiveName, ot.DESCRIPTION AS Description, ot.MISSIONID AS MissionId, ot.PERSPECTIVEID AS PerspectiveId, ISNULL(p.NAME, '') AS PerspectiveName, ot.STRATEGYID AS StrategyId, ot.REMARKS AS Remarks, ot.SORTORDER AS SortOrder, ot.STATUS AS Status, ot.VERSION AS Version, ot.SOURCETYPE AS SourceType, ot.CREATEDBYID AS CreatedById, ot.CREATEDON AS CreatedOn, ot.MODIFIEDBYID AS ModifiedById, ot.MODIFIEDON AS ModifiedOn, ot.DOMAINID AS DomainId, ot.SECTIONID AS SectionId, ot.FORLEVEL AS ForLevel, ot.FOROBJECTID AS ForObjectId, ot.FORPERIODTYPE AS ForPeriodType, ot.FORPERIODID AS ForPeriodId, ot.FORPERIODFROM AS ForPeriodFrom, ot.FORPERIODTO AS ForPeriodTo, ot.OWNERID AS OwnerId, ISNULL(e.EMPLOYEENAME, '') AS OwnerName, ot.LINKEDOBJECTIVEID AS LinkedObjectiveId, ot.PARENTOBJECTIVEID AS ParentObjectiveId, '' AS ParentName, ot.PARENTOBJECTIVEIDS AS ParentObjectiveIds, ot.OBJECTIVESTATUS AS ObjectiveStatus, ot.SECURITYLEVEL AS SecurityLevel, ot.PUBLICPRIVATE AS PublicPrivate, ot.DUEDATE AS DueDate, ot.WEIGHTAGE AS Weightage, ot.PERCENTAGE AS Percentage, ot.OBJECTIVEPRIORITY AS ObjectivePriority, ot.TASKID AS TaskId, ot.OBJECTIVETYPE AS ObjectiveType, ot.TEMPLATEID AS TemplateId, ot.OVERALLPROGRESS AS OverallProgress, ot.TreeLevel AS TreeLevel FROM ObjectiveTree ot LEFT JOIN MOKRPERSPECTIVE p ON p.PERSPECTIVEID = ot.PERSPECTIVEID LEFT JOIN MEMPLOYEE e ON e.EMPLOYEEID = ot.OWNERID ORDER BY ot.TreeLevel, ot.OBJECTIVEID OPTION (MAXRECURSION 10)"; public const string GET_ALIGNMENT_VIEW = @" SELECT a.ALIGNMENTID AS AlignmentId, a.SOURCEOBJECTIVEID AS SourceObjectiveId, src.OBJECTIVENAME AS SourceName, a.TARGETOBJECTIVEID AS TargetObjectiveId, tgt.OBJECTIVENAME AS TargetName, a.ALIGNMENTTYPE AS AlignmentType, a.TENANTID AS TenantId FROM MOKRALIGNMENT a JOIN MOBJECTIVE src ON src.OBJECTIVEID = a.SOURCEOBJECTIVEID JOIN MOBJECTIVE tgt ON tgt.OBJECTIVEID = a.TARGETOBJECTIVEID WHERE src.FORPERIODID = @OKRCycleId AND a.TENANTID = @TenantId"; public const string INSERT_OBJECTIVE = @" INSERT INTO MOBJECTIVE (OBJECTIVEID, OBJECTIVENAME, DESCRIPTION, MISSIONID, PERSPECTIVEID, STRATEGYID, REMARKS, SORTORDER, STATUS, VERSION, SOURCETYPE, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, DOMAINID, SECTIONID, FORLEVEL, FOROBJECTID, FORPERIODTYPE, FORPERIODID, FORPERIODFROM, FORPERIODTO, OWNERID, LINKEDOBJECTIVEID, PARENTOBJECTIVEID, PARENTOBJECTIVEIDS, OBJECTIVESTATUS, SECURITYLEVEL, PUBLICPRIVATE, DUEDATE, WEIGHTAGE, PERCENTAGE, OBJECTIVEPRIORITY, TASKID, OBJECTIVETYPE, TEMPLATEID, OVERALLPROGRESS) VALUES (@ObjectiveId, @ObjectiveName, @Description, @MissionId, @PerspectiveId, @StrategyId, @Remarks, @SortOrder, @Status, 1, @SourceType, @CreatedById, GETUTCDATE(), @CreatedById, GETUTCDATE(), @DomainId, @SectionId, @ForLevel, @ForObjectId, @ForPeriodType, @ForPeriodId, @ForPeriodFrom, @ForPeriodTo, @OwnerId, @LinkedObjectiveId, @ParentObjectiveId, @ParentObjectiveIds, @ObjectiveStatus, @SecurityLevel, @PublicPrivate, @DueDate, @Weightage, @Percentage, @ObjectivePriority, @TaskId, @ObjectiveType, @TemplateId, 0)"; public const string UPDATE_OBJECTIVE = @" UPDATE MOBJECTIVE SET OBJECTIVENAME = @ObjectiveName, DESCRIPTION = @Description, MISSIONID = @MissionId, PERSPECTIVEID = @PerspectiveId, STRATEGYID = @StrategyId, REMARKS = @Remarks, SORTORDER = @SortOrder, DOMAINID = @DomainId, SECTIONID = @SectionId, FORLEVEL = @ForLevel, FOROBJECTID = @ForObjectId, FORPERIODTYPE = @ForPeriodType, FORPERIODID = @ForPeriodId, FORPERIODFROM = @ForPeriodFrom, FORPERIODTO = @ForPeriodTo, OWNERID = @OwnerId, LINKEDOBJECTIVEID = @LinkedObjectiveId, PARENTOBJECTIVEID = @ParentObjectiveId, PARENTOBJECTIVEIDS = @ParentObjectiveIds, OBJECTIVESTATUS = @ObjectiveStatus, SECURITYLEVEL = @SecurityLevel, PUBLICPRIVATE = @PublicPrivate, DUEDATE = @DueDate, WEIGHTAGE = @Weightage, PERCENTAGE = @Percentage, OBJECTIVEPRIORITY = @ObjectivePriority, TASKID = @TaskId, OBJECTIVETYPE = @ObjectiveType, TEMPLATEID = @TemplateId, STATUS = @Status, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETUTCDATE(), VERSION = VERSION + 1 WHERE OBJECTIVEID = @ObjectiveId"; public const string DELETE_OBJECTIVE = @" UPDATE MOBJECTIVE SET OBJECTIVESTATUS = 5, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETUTCDATE(), VERSION = VERSION + 1 WHERE OBJECTIVEID = @ObjectiveId"; public const string INSERT_ALIGNMENT = @" INSERT INTO MOKRALIGNMENT (ALIGNMENTID, SOURCEOBJECTIVEID, TARGETOBJECTIVEID, ALIGNMENTTYPE, TENANTID) VALUES (@AlignmentId, @SourceObjectiveId, @TargetObjectiveId, @AlignmentType, @TenantId)"; public const string DELETE_ALIGNMENT = @" DELETE FROM MOKRALIGNMENT WHERE ALIGNMENTID = @AlignmentId AND TENANTID = @TenantId"; public const string UPDATE_OVERALL_PROGRESS = @" UPDATE MOBJECTIVE SET OVERALLPROGRESS = @OverallProgress, OBJECTIVESTATUS = @Status, MODIFIEDBYID = @ModifiedById, MODIFIEDON = GETUTCDATE(), VERSION = VERSION + 1 WHERE OBJECTIVEID = @ObjectiveId"; public const string GET_WEIGHTED_PROGRESS = @" SELECT CASE WHEN SUM(WEIGHTAGE) > 0 THEN SUM(CURRENTPROGRESS * WEIGHTAGE) / SUM(WEIGHTAGE) ELSE AVG(CURRENTPROGRESS) END AS WeightedProgress FROM TGOAL WHERE OBJECTIVEID = @ObjectiveId AND TENANTID = @TenantId AND STATUS != 5"; // exclude Cancelled }