using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; namespace CRMDAL.Query.Task { public static class TaskQB { public const string DELETE_TASK = @"DELETE FROM TTASK WHERE TASKID = @taskid"; // ───────────────────────────────────────────────────────────────── // UpdateTaskData support — migrated from GB4 TaskDAL.UpdateTaskData. // Full-field update path (UpdateOnlyStatus != 0, GB4's UPDATE_TASK_QUERY) // reuses the existing UPDATE_TASK constant, which dynamically writes only // the ASSIGNEDTO*/PARTYBRANCHID column owned by TaskAssignedToType (see // ASSIGNED_TO_COLUMN_BY_TYPE + TaskDAL.UpdateTask's runtime substitution) // instead of overwriting every typed column unconditionally. // This constant covers the "UpdateOnlyStatus == 0" fast path // (GB4's UPDATE_TASK_QUERY_NEW — status-only update). // ───────────────────────────────────────────────────────────────── public const string UPDATE_TASK_STATUS_ONLY = @" UPDATE TTASK SET TASKSTATUS = @TaskOriginalStatus, COMPLEXITYID = -1, MODIFIEDBYID = @UserId, MODIFIEDON = @ModifiedOn WHERE TASKID = @TaskId"; // Extends the parent task's PlanEndOn/DueOn/ExpireOn to the later of // its own value or the child's, mirroring GB4's ToUpdateParentDate region. public const string GET_TASK_DATES_FOR_PARENT_CASCADE = @" SELECT TASKID AS TaskId, PLANENDON AS TaskPlanEndOn, DUEON AS TaskDueOn, EXPIREON AS TaskExpireOn, TENANTID AS TenantId FROM TTASK WHERE TASKID = @TaskId AND TENANTID = @TenantId"; public const string UPDATE_PARENT_TASK_DATES = @" UPDATE TTASK SET PLANENDON = @ParentPlanEndOn, DUEON = @ParentDueOn, EXPIREON = @ParentExpireOn WHERE TASKID = @TaskId AND TENANTID = @TenantId"; // Child-status cascade — fired only when the saved task's status is // Closed (4) or ForceClosed (5). Branches by TaskDetailType exactly as // GB4 does (0=MileStone, 2=Heading, 3=Work); Task-level (4) has no // further children to cascade to in GB4 either. public const string UPDATE_TASK_FROM_MILESTONE = @" UPDATE TTASK SET TASKSTATUS = @TaskStatus WHERE TASKID IN ( SELECT TASKID FROM TTASK WHERE PARENTTASKID IN ( SELECT TASKID FROM TTASK WHERE PARENTTASKID IN ( SELECT TASKID FROM TTASK WHERE PARENTTASKID = @MilestoneTaskId ) ) ) AND TASKSTATUS <> 4"; public const string UPDATE_WORK_FROM_MILESTONE = @" UPDATE TTASK SET TASKSTATUS = @TaskStatus WHERE TASKID IN ( SELECT TASKID FROM TTASK WHERE PARENTTASKID IN ( SELECT TASKID FROM TTASK WHERE PARENTTASKID = @MilestoneTaskId ) )"; public const string UPDATE_HEADING_FROM_MILESTONE = @" UPDATE TTASK SET TASKSTATUS = @TaskStatus WHERE TASKID IN ( SELECT TASKID FROM TTASK WHERE PARENTTASKID = @MilestoneTaskId )"; public const string UPDATE_TASK_FROM_HEADING = @" UPDATE TTASK SET TASKSTATUS = @TaskStatus WHERE TASKID IN ( SELECT TASKID FROM TTASK WHERE PARENTTASKID IN ( SELECT TASKID FROM TTASK WHERE PARENTTASKID = @HeadingTaskId ) ) AND TASKSTATUS <> 4"; public const string UPDATE_WORK_FROM_HEADING = @" UPDATE TTASK SET TASKSTATUS = @TaskStatus WHERE TASKID IN ( SELECT TASKID FROM TTASK WHERE PARENTTASKID = @HeadingTaskId )"; public const string UPDATE_TASK_FROM_WORK = @" UPDATE TTASK SET TASKSTATUS = @TaskStatus WHERE TASKID IN ( SELECT TASKID FROM TTASK WHERE PARENTTASKID = @WorkTaskId ) AND TASKSTATUS <> 4"; // Comment posting (GB4 TCOMMENT Posting region) — GB5 has no generic // ICommentBLL equivalent (ECP's CommentThreadBLL is an unrelated // collaboration-thread subsystem), so this inserts directly. // CommentObjectTypeId = -1899999523 is GB4's fixed "Task" object type. public const string INSERT_TASK_COMMENT = @" INSERT INTO TCOMMENT ( COMMENTID, USERID, TIMESTAMP, COMMENTSTATUS, ACCESSTYPE, PRIVATEUSERGROUPID, OBJECTTYPEID, OBJECTID, COMMENTTEXT, SECURITYLEVEL, PARENTCOMMENTID, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON ) VALUES ( @CommentId, @UserId, @TimeStamp, 0, 0, -1, -1899999523, @ObjectId, @CommentText, 0, -1, 0, 1, @UserId, @TimeStamp, @UserId, @TimeStamp )"; // Used by TaskBLL to skip numbers that already exist in TTASK // (self-healing for orphaned rows from a previous code bug). public const string CHECK_TASK_NUMBER_EXISTS = "SELECT COUNT(1) FROM TTASK WHERE TASKNUMBER = @TaskNumber"; public const string GetSelectList_Task = @" WITH PagedTask AS ( SELECT TT.TASKID AS Id, TT.TASKNUMBER AS Number, TT.TASKNAME AS TaskName, TT.TASKDESCRIPTION AS Description, ROW_NUMBER() OVER (ORDER BY TT.TASKID) AS RowNum FROM TTASK TT WHERE 1=1 {CRITERIA} ) SELECT Id, Number, TaskName, Description FROM PagedTask WHERE (@firstnumber = -1 AND @maxresult = -1) OR (RowNum BETWEEN @firstnumber AND @maxresult);"; public const string GetSelectList_Task_Count = @" SELECT COUNT(1) FROM TTASK TT WHERE 1=1 {CRITERIA}"; // GB4 parity: TaskQueryBuilder.Get_MILESTONE_PICKLIST (DETAILTYPE = 0) public const string GET_MILESTONE_PICKLIST = @" SELECT TT.TASKID AS Id, TT.TASKNUMBER AS Number, TT.TASKNAME AS TaskName, TT.TASKDESCRIPTION AS Description FROM TTASK TT WHERE TT.DETAILTYPE = 0"; // GB4 parity: TaskQueryBuilder.Get_HEADING_PICKLIST (DETAILTYPE = 2) public const string GET_HEADING_PICKLIST = @" SELECT TT.TASKID AS Id, TT.TASKNUMBER AS Number, TT.TASKNAME AS TaskName, TT.TASKDESCRIPTION AS Description FROM TTASK TT WHERE TT.DETAILTYPE = 2"; // GB4 parity: TaskQueryBuilder.Get_ROllUP_PICKLIST (DETAILTYPE = 1) public const string GET_ROLLUP_PICKLIST = @" SELECT TT.TASKID AS Id, TT.TASKNUMBER AS Number, TT.TASKNAME AS TaskName, TT.TASKDESCRIPTION AS Description FROM TTASK TT WHERE TT.DETAILTYPE = 1"; // GB4 parity: TaskQueryBuilder.Get_WORK_PICKLIST (DETAILTYPE = 3) public const string GET_WORK_PICKLIST = @" SELECT TT.TASKID AS Id, TT.TASKNUMBER AS Number, TT.TASKNAME AS TaskName, TT.TASKDESCRIPTION AS Description FROM TTASK TT WHERE TT.DETAILTYPE = 3"; // GB4 parity: TaskQueryBuilder.Get_PARENT_PICKLIST (ISTODO = 1 — tasks flagged // eligible to serve as a parent, independent of the DETAILTYPE hierarchy above) public const string GET_PARENT_PICKLIST = @" SELECT TT.TASKID AS Id, TT.TASKNUMBER AS Number, TT.TASKNAME AS TaskName, TT.TASKDESCRIPTION AS Description FROM TTASK TT WHERE TT.ISTODO = 1"; // GB4 parity: TaskDAL.GetTaskQuery / TaskQueryBuilder.GET_TASK_ASSETACTIVITY — eligible // recurring asset-activities for CRM's "GenerateTask" batch job (Task.svc/GenerateTask). // @TaskType/@OuId mirror GB4's inline CASE that scopes eligibility to the OU that owns // the asset, machine, or resource depending on the requested generation nature. public const string GET_TASK_ASSETACTIVITY_FOR_GENERATION = @" SELECT assetActivity.ASSETACTIVITYID AS AssetActivityId, assetActivity.ASSETID AS AssetId, asset.ASSETTYPEID AS AssetTypeId, assetActivity.SLNO AS Slno, assetActivity.ACTIVITYID AS ActivityId, assetActivity.DURATION AS Duration, assetActivity.GRACETIME AS GraceTime, assetActivity.AUTOCLOSETIME AS AutoCloseTime, assetActivity.CRONEXPRESSION AS CronExpression, ISNULL(assetActivity.LASTGENERATEDTILL, '19000101') AS LastGeneratedTill, assetActivity.MACHINEID AS MachineId, assetActivity.RESOURCEID AS ResourceId, assetActivity.EMPLOYEEID AS EmployeeId, assetActivity.TASKUSERID AS TaskUserId, assetActivity.TASKUSERGROUPID AS TaskUserGroupId, assetActivity.TASKASSIGNEDTYPE AS TaskAssignedType, assetActivity.PARAMETERSETID AS ParameterSetId, asset.ASSETNAME AS AssetName, asset.ASSETCODE AS AssetCode, machine.MACHINENAME AS MachineName, resourcename.RESOURCENAME AS ResourceName, activity.ACTIVITYNAME AS ActivityName, asset.PARTYBRANCHID AS PartyBranchId, partybranchlocation.PARTYBRANCHLOCATIONNAME AS PartyBranchLocationName, ISNULL((SELECT TOP 1 mta.PERIOD FROM MASSETTYPEACTIVITY mta WHERE mta.ASSETTYPEID = asset.ASSETTYPEID AND mta.ACTIVITYID = activity.ACTIVITYID), 1) AS Period FROM MASSETACTIVITY assetActivity INNER JOIN MACTIVITY activity ON activity.ACTIVITYID = assetActivity.ACTIVITYID INNER JOIN MASSET asset ON asset.ASSETID = assetActivity.ASSETID INNER JOIN MEMPLOYEE emp ON assetActivity.EMPLOYEEID = emp.EMPLOYEEID INNER JOIN MMACHINE machine ON assetActivity.MACHINEID = machine.MACHINEID INNER JOIN MRESOURCE resourcename ON assetActivity.RESOURCEID = resourcename.RESOURCEID INNER JOIN MPARTYBRANCHLOCATION partybranchlocation ON asset.PARTYBRANCHLOCATIONID = partybranchlocation.PARTYBRANCHLOCATIONID WHERE assetActivity.ASSETACTIVITYID <> -1 AND assetActivity.CRONEXPRESSION IS NOT NULL AND assetActivity.CRONEXPRESSION NOT IN ('', 'NONE') AND CASE @TaskType WHEN 1 THEN asset.OUID WHEN 2 THEN machine.OUID WHEN 3 THEN resourcename.OUID WHEN 4 THEN @OuId END = @OuId {CRITERIA}"; // GB4 parity: TaskBLL.SaveTaskNew — resolves the BizTransactionType used to number // system-generated tasks, scoped to (class=Task, current OU, current Period, active). public const string GET_BIZTRANSACTIONTYPE_FOR_TASK_GENERATION = @" SELECT TOP 1 BIZTRANSACTIONTYPEID AS Id FROM MBIZTRANSACTIONTYPE WHERE BIZTRANSACTIONCLASSID = @BizTransactionClassId AND OUID = @OuId AND PERIODID = @PeriodId AND STATUS = 0 ORDER BY SORTORDER"; // GB4 parity: TaskBLL.SaveTaskNew's UPDATE_ASSET_ACTIVITY — records how far task // generation has progressed for this asset-activity so the next run resumes from here. public const string UPDATE_ASSETACTIVITY_LASTGENERATEDTILL = @" UPDATE MASSETACTIVITY SET LASTGENERATEDTILL = @LastGeneratedTill WHERE ASSETACTIVITYID = @AssetActivityId"; public const string GET_TASK = @"SELECT -- TTASK core T.TASKID AS TaskId, T.TASKTYPE AS TaskTaskType, -- BizTransactionType T.BIZTRANSACTIONTYPEID AS BizTransactionTypeId, BTT.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode, BTT.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName, -- OrganizationUnit T.OUID AS OuId, OU.ORGANIZATIONUNITCODE AS OuCode, OU.ORGANIZATIONUNITNAME AS OuName, -- Period T.PERIODID AS PeriodId, P.PERIODCODE AS PeriodCode, P.PERIODNAME AS PeriodName, ISNULL(P.FROMDATE, GETDATE()) AS PeriodFromDate, ISNULL(P.TODATE, GETDATE()) AS PeriodToDate, -- Task header fields T.TASKNUMBER AS TaskNumber, T.TASKDATE AS TaskDate, T.REFERENCENUMBER AS TaskReferenceNumber, T.REFERENCEDATE AS TaskReferenceDate, T.TASKNAME AS TaskTaskName, T.TASKDESCRIPTION AS TaskDescription, T.DETAILTYPE AS TaskDetailType, T.TASKNATURE AS TaskTaskNature, -- PartyBranch T.PARTYBRANCHID AS PartyBranchId, PB.PARTYBRANCHCODE AS PartyBranchCode, PB.PARTYBRANCHNAME AS PartyBranchName, -- Asset T.ASSETID AS AssetId, ASS.ASSETCODE AS AssetCode, ASS.ASSETNAME AS AssetName, -- Resource T.RESOURCEID AS ResourceId, RES.RESOURCECODE AS ResourceCode, RES.RESOURCENAME AS ResourceName, -- Machine T.MACHINEID AS MachineId, MC.MACHINECODE AS MachineCode, MC.MACHINENAME AS MachineName, -- Activity T.ACTIVITYID AS ActivityId, ACT.ACTIVITYCODE AS ActivityCode, ACT.ACTIVITYNAME AS ActivityName, -- RequestedBy employee T.REQUESTEDBYID AS RequestedById, RBE.EMPLOYEECODE AS RequestedByCode, RBE.EMPLOYEENAME AS RequestedByName, RBDPT.DEPARTMENTNAME AS RequestDepartmentName, RBDES.GCMNAME AS RequestDesignation, -- InCharge employee T.INCHARGEID AS InChargeId, ICE.EMPLOYEECODE AS InChargeCode, ICE.EMPLOYEENAME AS InChargeName, ICE.PRIMARYEMAILID AS InChargePrimaryEMailId, ICDPT.DEPARTMENTNAME AS InchargeDepartmentName, ICDES.GCMNAME AS InchargeDesignation, -- Allocation T.ALLOCATIONID AS AllocationId, AL.ALLOCATIONNAME AS AllocationName, AL.ALLOCATIONSHORTNAME AS AllocationShortName, -- ParentTask T.PARENTTASKID AS ParentTaskId, PT.TASKNUMBER AS ParentTaskNumber, PT.TASKNAME AS ParentTaskTaskName, -- WorkType / WBC T.WORKTYPEID AS TaskWorkType, T.SLNO AS TaskSlNo, T.WBCCODE AS TaskWbcCode, T.WBCLEVEL AS TaskWbcLevel, T.ASSIGNEDTOTYPE AS TaskAssignedToType, -- AssignedTo Role T.ASSIGNEDTOROLEID AS AssignedToRoleId, RL.ROLECODE AS AssignedToRoleCode, RL.ROLENAME AS AssignedToRoleName, -- AssignedTo User T.ASSIGNEDTOUSERID AS AssignedToUserId, USR.USERCODE AS AssignedToUserCode, USR.USERNAME AS AssignedToUserName, -- AssignedTo UserGroup T.ASSIGNEDTOUSERGROUPID AS AssignedToUserGroupId, UG.USERGROUPCODE AS AssignedToUserGroupCode, UG.USERGROUPNAME AS AssignedToUserGroupName, -- AssignedTo Employee T.ASSIGNEDTOEMPLOYEEID AS AssignedToEmployeeId, ATE.EMPLOYEECODE AS AssignedToEmployeeCode, ATE.EMPLOYEENAME AS AssignedToEmployeeName, ATE.PRIMARYEMAILID AS AssignedToEmployeePrimaryEMailId, ATDPT.DEPARTMENTNAME AS AssignToDepartmentName, ATDES.GCMNAME AS AssigntoDesignation, -- AssignedTo WorkType T.ASSIGNEDTOWORKTYPEID AS AssignedToWorkTypeId, ATWT.GCMCODE AS AssignedToWorkTypeCode, ATWT.GCMNAME AS AssignedToWorkTypeName, -- Status / Tags / Planning T.TASKSTATUS AS TaskOriginalStatus, T.TAGS AS TaskTags, T.PLANNEDDAYS AS TaskPlannedDays, T.PLANNEDHOURS AS TaskPlannedHours, -- Predecessor T.PREDECESSORID AS PredecessorId, PRED.TASKNUMBER AS PredecessorNumber, -- Dates T.PLANSTARTON AS TaskPlanStartOn, T.PLANENDON AS TaskPlanEndOn, T.DUEON AS TaskDueOn, T.EXPIRYTIME AS TaskExpiryTime, T.EXPIREON AS TaskExpireOn, T.GRACETIME AS TaskGraceTime, T.CRITICALITY AS TaskCriticality, T.PRIORITY AS TaskPriority, T.ACTUALSTARTON AS TaskActualStartOn, T.ACTUALENDON AS TaskActualEndOn, T.ACTUALDAYS AS TaskActualDays, T.ACTUALHOURS AS TaskActualHours, T.COMPLETEDPERCENTAGE AS TaskCompletedPercentage, T.REMARKS AS TaskRemarks, -- HeaderTask T.HEADERTASKID AS HeaderTaskId, HT.TASKNUMBER AS HeaderTaskNumber, -- Call T.CALLID AS CallId, CAL.CALLNUMBER AS CallNumber, -- Standard fields T.STATUS AS TaskStatus, T.VERSION AS TaskVersion, T.CREATEDBYID AS TaskCreatedById, T.CREATEDON AS TaskCreatedOn, T.MODIFIEDBYID AS TaskModifiedById, T.MODIFIEDON AS TaskModifiedOn, CRU.USERNAME AS TaskCreatedByName, MDU.USERNAME AS TaskModifiedByName, -- Misc T.ISTODO AS TaskIsToDo, T.ASSIGNEDTOCONTACTID AS AssignedToContactId, T.ORIGINALPLANNEDHOURS AS TaskOriginalPlannedHours, T.ORIGINALPLANNEDDATE AS TaskOriginalPlannedDate, T.ROLLUPTYPE AS TaskRollType, T.BUDGETCOST AS TaskBudgetCost, T.BUDGETREVENUE AS TaskBudgetRevenue, T.ACTUALCOST AS TaskActualCost, -- Lead T.LEADID AS LeadId, LD.LEADCODE AS LeadCode, LD.LEADNAME AS LeadName, -- Data counts T.PLANNUMBEROFDATA AS TaskPlanNumberOfData, T.ACTUALNUMBEROFDATA AS TaskActualNumberOfData, -- Complexity (MLOV) T.COMPLEXITYID AS ComplexityId, CMPLX.LOVNAME AS ComplexityName, -- ObjectType T.OBJECTTYPEID AS ObjectTypeId, ET.ENTITYCODE AS ObjectTypeCode, ET.ENTITYNAME AS ObjectTypeName, T.OBJECTID AS ObjectId, -- WorkTypeDetail / ParameterSet T.WORKTYPEDETAILID AS WorkTypeDetailId, T.PARAMETERSETID AS ParameterSetId, PS.PARAMETERSETCODE AS ParameterSetCode, PS.PARAMETERSETNAME AS ParameterSetName, -- Checklists T.PRECHECKLISTID AS PreCheckListId, T.POSTCHECKLISTID AS PostCheckListId, T.REVIEWCHECKLISTID AS ReviewCheckListId, -- Template T.FORMTEMPLATEID AS ForTemplateId, FT.TEMPLATECODE AS ForTemplateCode, FT.TEMPLATENAME AS ForTemplateName, -- BOM / BOR T.BOMID AS BOMId, BOM.BOMCODE AS BOMCode, BOM.BOMNAME AS BOMName, T.BORID AS BORId, -- Vehicle T.VEHICLEID AS VehicleId, VH.VEHICLENUMBER AS VehicleNumber, -- AllocationSplit T.ALLOCATIONSPLITID AS AllocationSplitId, ASP.SPLITCODE AS SplitCode, ASP.SPLITNAME AS SplitName, -- Sequences / flags T.PROJECTSEQUENCE AS TaskProjectSequence, T.RESOURCESEQUENCE AS TaskResourceSequence, T.ISBILLABLE AS TaskIsBillable, T.ISREWORK AS TaskIsReWork FROM TTASK T -- Lookups LEFT JOIN MBIZTRANSACTIONTYPE BTT ON T.BIZTRANSACTIONTYPEID = BTT.BIZTRANSACTIONTYPEID LEFT JOIN MORGANIZATIONUNIT OU ON T.OUID = OU.OUID LEFT JOIN MPERIOD P ON T.PERIODID = P.PERIODID LEFT JOIN MPARTYBRANCH PB ON T.PARTYBRANCHID = PB.PARTYBRANCHID LEFT JOIN MASSET ASS ON T.ASSETID = ASS.ASSETID LEFT JOIN MRESOURCE RES ON T.RESOURCEID = RES.RESOURCEID LEFT JOIN MMACHINE MC ON T.MACHINEID = MC.MACHINEID LEFT JOIN MACTIVITY ACT ON T.ACTIVITYID = ACT.ACTIVITYID -- RequestedBy employee + dept/designation LEFT JOIN MEMPLOYEE RBE ON T.REQUESTEDBYID = RBE.EMPLOYEEID LEFT JOIN MDEPARTMENT RBDPT ON RBE.DEPARTMENTID = RBDPT.DEPARTMENTID LEFT JOIN MGCM RBDES ON RBE.DESIGNATIONID = RBDES.GCMID -- InCharge employee + dept/designation LEFT JOIN MEMPLOYEE ICE ON T.INCHARGEID = ICE.EMPLOYEEID LEFT JOIN MDEPARTMENT ICDPT ON ICE.DEPARTMENTID = ICDPT.DEPARTMENTID LEFT JOIN MGCM ICDES ON ICE.DESIGNATIONID = ICDES.GCMID -- Allocation LEFT JOIN MALLOCATION AL ON T.ALLOCATIONID = AL.ALLOCATIONID -- Parent / Header / Predecessor tasks LEFT JOIN TTASK PT ON T.PARENTTASKID = PT.TASKID LEFT JOIN TTASK HT ON T.HEADERTASKID = HT.TASKID LEFT JOIN TTASK PRED ON T.PREDECESSORID = PRED.TASKID -- AssignedTo lookups LEFT JOIN MROLE RL ON T.ASSIGNEDTOROLEID = RL.ROLEID LEFT JOIN MUSER USR ON T.ASSIGNEDTOUSERID = USR.USERID LEFT JOIN MUSERGROUP UG ON T.ASSIGNEDTOUSERGROUPID = UG.USERGROUPID -- AssignedTo employee + dept/designation LEFT JOIN MEMPLOYEE ATE ON T.ASSIGNEDTOEMPLOYEEID = ATE.EMPLOYEEID LEFT JOIN MDEPARTMENT ATDPT ON ATE.DEPARTMENTID = ATDPT.DEPARTMENTID LEFT JOIN MGCM ATDES ON ATE.DESIGNATIONID = ATDES.GCMID -- AssignedTo WorkType LEFT JOIN MGCM ATWT ON T.ASSIGNEDTOWORKTYPEID = ATWT.GCMID -- Call LEFT JOIN TCALL CAL ON T.CALLID = CAL.CALLID -- Audit users LEFT JOIN MUSER CRU ON T.CREATEDBYID = CRU.USERID LEFT JOIN MUSER MDU ON T.MODIFIEDBYID = MDU.USERID -- Lead LEFT JOIN TLEAD LD ON T.LEADID = LD.LEADID -- Complexity LEFT JOIN MLOV CMPLX ON T.COMPLEXITYID = CMPLX.LOVID -- ObjectType LEFT JOIN MENTITY ET ON T.OBJECTTYPEID = ET.ENTITYID -- ParameterSet LEFT JOIN MPARAMETERSET PS ON T.PARAMETERSETID = PS.PARAMETERSETID -- Template LEFT JOIN MTEMPLATE FT ON T.FORMTEMPLATEID = FT.TEMPLATEID -- BOM LEFT JOIN MBOM BOM ON T.BOMID = BOM.BOMID -- Vehicle LEFT JOIN MVEHICLE VH ON T.VEHICLEID = VH.VEHICLEID -- AllocationSplit LEFT JOIN MALLOCATIONSPLIT ASP ON T.ALLOCATIONSPLITID = ASP.ALLOCATIONSPLITID WHERE T.TASKID = @TaskId;"; public const string SAVE_TASK = @"INSERT INTO TTASK ( TASKID, TASKTYPE, BIZTRANSACTIONTYPEID, OUID, PERIODID, TASKNUMBER, TASKDATE, REFERENCENUMBER, REFERENCEDATE, TASKNAME, TASKDESCRIPTION, DETAILTYPE, TASKNATURE, PARTYBRANCHID, ASSETID, RESOURCEID, MACHINEID, ACTIVITYID, REQUESTEDBYID, INCHARGEID, ALLOCATIONID, PARENTTASKID, WORKTYPEID, SLNO, WBCCODE, WBCLEVEL, ASSIGNEDTOTYPE, ASSIGNEDTOROLEID, ASSIGNEDTOUSERID, ASSIGNEDTOUSERGROUPID, TASKSTATUS, TAGS, PLANNEDDAYS, PLANNEDHOURS, PREDECESSORID, PLANSTARTON, PLANENDON, DUEON, EXPIRYTIME, EXPIREON, GRACETIME, CRITICALITY, PRIORITY, ACTUALSTARTON, ACTUALENDON, ACTUALDAYS, ACTUALHOURS, COMPLETEDPERCENTAGE, REMARKS, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON, HEADERTASKID, CALLID, ASSIGNEDTOEMPLOYEEID, ASSIGNEDTOWORKTYPEID, ISTODO, ASSIGNEDTOCONTACTID, ORIGINALPLANNEDHOURS, ORIGINALPLANNEDDATE, ROLLUPTYPE, BUDGETCOST, BUDGETREVENUE, ACTUALCOST, LEADID, PLANNUMBEROFDATA, ACTUALNUMBEROFDATA, COMPLEXITYID, OBJECTTYPEID, OBJECTID, WORKTYPEDETAILID, PARAMETERSETID, PRECHECKLISTID, POSTCHECKLISTID, REVIEWCHECKLISTID, FORMTEMPLATEID, BOMID, BORID, VEHICLEID, ALLOCATIONSPLITID, PROJECTSEQUENCE, RESOURCESEQUENCE, ISBILLABLE, ISREWORK ) VALUES ( @TaskId, @TaskTaskType, @BizTransactionTypeId, @OuId, @PeriodId, @TaskNumber, @TaskDate, @TaskReferenceNumber, @TaskReferenceDate, @TaskTaskName, @TaskDescription, @TaskDetailType, @TaskTaskNature, @PartyBranchId, @AssetId, @ResourceId, @MachineId, @ActivityId, @RequestedById, @InChargeId, @AllocationId, @ParentTaskId, @TaskWorkType, @TaskSlNo, @TaskWbcCode, @TaskWbcLevel, @TaskAssignedToType, @AssignedToRoleId, @AssignedToUserId, @AssignedToUserGroupId, @TaskOriginalStatus, @TaskTags, @TaskPlannedDays, @TaskPlannedHours, @PredecessorId, @TaskPlanStartOn, @TaskPlanEndOn, @TaskDueOn, @TaskExpiryTime, @TaskExpireOn, @TaskGraceTime, @TaskCriticality, @TaskPriority, @TaskActualStartOn, @TaskActualEndOn, @TaskActualDays, @TaskActualHours, @TaskCompletedPercentage, @TaskRemarks, @TaskStatus, @TaskVersion, @TaskCreatedById, @TaskCreatedOn, @TaskModifiedById, @TaskModifiedOn, @HeaderTaskId, @CallId, @AssignedToEmployeeId, @AssignedToWorkTypeId, @TaskIsToDo, @AssignedToContactId, @TaskOriginalPlannedHours, @TaskOriginalPlannedDate, @TaskRollType, @TaskBudgetCost, @TaskBudgetRevenue, @TaskActualCost, @LeadId, @TaskPlanNumberOfData, @TaskActualNumberOfData, @ComplexityId, @ObjectTypeId, @ObjectId, @WorkTypeDetailId, @ParameterSetId, @PreCheckListId, @PostCheckListId, @ReviewCheckListId, @ForTemplateId, @BOMId, @BORId, @VehicleId, @AllocationSplitId, @TaskProjectSequence, @TaskResourceSequence, @TaskIsBillable, @TaskIsReWork );"; // Maps TaskAssignedToType (0/4=Employee, 1=Role, 2=UserGroup, 3=User, // 5=WorkType, 6=Contact, 7=PartyBranch) to the single ASSIGNEDTO* column // that type owns. Mirrors GB4's single-column ReplaceFields dispatch in // TaskDAL.UpdateTaskData — only the owning column is written per update, // the other AssignedTo* columns are left untouched. public static readonly IReadOnlyDictionary ASSIGNED_TO_COLUMN_BY_TYPE = new Dictionary { [0] = "ASSIGNEDTOEMPLOYEEID", [1] = "ASSIGNEDTOROLEID", [2] = "ASSIGNEDTOUSERGROUPID", [3] = "ASSIGNEDTOUSERID", [4] = "ASSIGNEDTOEMPLOYEEID", [5] = "ASSIGNEDTOWORKTYPEID", [6] = "ASSIGNEDTOCONTACTID", [7] = "PARTYBRANCHID", }; public const string UPDATE_TASK = @"UPDATE TTASK SET TASKTYPE = @TaskTaskType, BIZTRANSACTIONTYPEID = @BizTransactionTypeId, OUID = @OuId, PERIODID = @PeriodId, TASKNUMBER = @TaskNumber, TASKDATE = @TaskDate, REFERENCENUMBER = @TaskReferenceNumber, REFERENCEDATE = @TaskReferenceDate, TASKNAME = @TaskTaskName, TASKDESCRIPTION = @TaskDescription, DETAILTYPE = @TaskDetailType, TASKNATURE = @TaskTaskNature, ASSETID = @AssetId, RESOURCEID = @ResourceId, MACHINEID = @MachineId, ACTIVITYID = @ActivityId, REQUESTEDBYID = @RequestedById, INCHARGEID = @InChargeId, ALLOCATIONID = @AllocationId, PARENTTASKID = @ParentTaskId, WORKTYPEID = @TaskWorkType, SLNO = @TaskSlNo, WBCCODE = @TaskWbcCode, WBCLEVEL = @TaskWbcLevel, ASSIGNEDTOTYPE = @TaskAssignedToType, {AssignedToColumnAssignment} TASKSTATUS = @TaskOriginalStatus, TAGS = @TaskTags, PLANNEDDAYS = @TaskPlannedDays, PLANNEDHOURS = @TaskPlannedHours, PREDECESSORID = @PredecessorId, PLANSTARTON = @TaskPlanStartOn, PLANENDON = @TaskPlanEndOn, DUEON = @TaskDueOn, EXPIRYTIME = @TaskExpiryTime, EXPIREON = @TaskExpireOn, GRACETIME = @TaskGraceTime, CRITICALITY = @TaskCriticality, PRIORITY = @TaskPriority, ACTUALSTARTON = @TaskActualStartOn, ACTUALENDON = @TaskActualEndOn, ACTUALDAYS = @TaskActualDays, ACTUALHOURS = @TaskActualHours, COMPLETEDPERCENTAGE = @TaskCompletedPercentage, REMARKS = @TaskRemarks, STATUS = @TaskStatus, VERSION = @TaskVersion, MODIFIEDBYID = @TaskModifiedById, MODIFIEDON = @TaskModifiedOn, HEADERTASKID = @HeaderTaskId, CALLID = @CallId, ISTODO = @TaskIsToDo, ORIGINALPLANNEDHOURS = @TaskOriginalPlannedHours, ORIGINALPLANNEDDATE = @TaskOriginalPlannedDate, ROLLUPTYPE = @TaskRollType, BUDGETCOST = @TaskBudgetCost, BUDGETREVENUE = @TaskBudgetRevenue, ACTUALCOST = @TaskActualCost, LEADID = @LeadId, PLANNUMBEROFDATA = @TaskPlanNumberOfData, ACTUALNUMBEROFDATA = @TaskActualNumberOfData, COMPLEXITYID = @ComplexityId, OBJECTTYPEID = @ObjectTypeId, OBJECTID = @ObjectId, WORKTYPEDETAILID = @WorkTypeDetailId, PARAMETERSETID = @ParameterSetId, PRECHECKLISTID = @PreCheckListId, POSTCHECKLISTID = @PostCheckListId, REVIEWCHECKLISTID = @ReviewCheckListId, FORMTEMPLATEID = @ForTemplateId, BOMID = @BOMId, BORID = @BORId, VEHICLEID = @VehicleId, ALLOCATIONSPLITID = @AllocationSplitId, PROJECTSEQUENCE = @TaskProjectSequence, RESOURCESEQUENCE = @TaskResourceSequence, ISBILLABLE = @TaskIsBillable, ISREWORK = @TaskIsReWork WHERE TASKID = @TaskId;"; public const string HOURS_VALIDATION_FOR_PARENTTASK = @" SELECT CAST(MAX(PARENT.PLANNEDHOURS) - ISNULL(SUM(CHILD.PLANNEDHOURS), 0) AS INT) AS TaskPlannedHours, MAX(PARENT.PLANNEDHOURS) AS ParentPlannedHrs FROM TTASK PARENT LEFT OUTER JOIN TTASK CHILD ON (PARENT.TASKID = CHILD.PARENTTASKID) WHERE PARENT.TASKID = @parenttaskid"; public const string GET_TASK_LIST_ON_ACTIVITY = @"Select task.TASKID as TaskId, task.TASKTYPE as TaskTaskType, task.BIZTRANSACTIONTYPEID as BizTransactionTypeId, task.OUID as OuId, task.PERIODID as PeriodId, task.TASKNUMBER as TaskNumber, task.TASKDATE as TaskDate, task.REFERENCENUMBER as TaskReferenceNumber, task.REFERENCEDATE as TaskReferenceDate, task.TASKNAME as TaskTaskName, task.TASKDESCRIPTION as TaskDescription, task.DETAILTYPE as TaskDetailType, task.TASKNATURE as TaskTaskNature, task.PARTYBRANCHID as PartyBranchId, task.ASSETID as AssetId, task.RESOURCEID as ResourceId, task.MACHINEID as MachineId, task.ACTIVITYID as ActivityId, task.REQUESTEDBYID as RequestedById, task.INCHARGEID as InChargeId, task.ALLOCATIONID as AllocationId, allocation.ALLOCATIONNAME as AllocationName, task.PARENTTASKID as ParentTaskId, task.WORKTYPEID as TaskWorkType, task.SLNO as TaskSlNo, task.WBCCODE as TaskWbcCode, task.WBCLEVEL as TaskWbcLevel, task.ASSIGNEDTOTYPE as TaskAssignedToType, task.ASSIGNEDTOROLEID as AssignedToRoleId, task.ASSIGNEDTOUSERID as AssignedToUserId, task.ASSIGNEDTOUSERGROUPID as AssignedToUserGroupId, task.TASKSTATUS as TaskOriginalStatus, task.TAGS as TaskTags, task.PLANNEDDAYS as TaskPlannedDays, task.PLANNEDHOURS as TaskPlannedHours, task.PREDECESSORID as PredecessorId, task.PLANSTARTON as TaskPlanStartOn, task.PLANENDON as TaskPlanEndOn, task.DUEON as TaskDueOn, task.EXPIRYTIME as TaskExpiryTime, task.EXPIREON as TaskExpireOn, task.GRACETIME as TaskGraceTime, task.CRITICALITY as TaskCriticality, task.PRIORITY as TaskPriority, task.ACTUALSTARTON as TaskActualStartOn, task.ACTUALENDON as TaskActualEndOn, task.ACTUALDAYS as TaskActualDays, task.ACTUALHOURS as TaskActualHours, task.COMPLETEDPERCENTAGE as TaskCompletedPercentage, task.REMARKS as TaskRemarks, task.HEADERTASKID as HeaderTaskId, task.CALLID as CallId, task.ASSIGNEDTOEMPLOYEEID as AssignedToEmployeeId, task.ASSIGNEDTOWORKTYPEID as AssignedToWorkTypeId, subquery.Balancehours as RemainingHours From TTASK task, MALLOCATION allocation, (select task.PERIODId, task.OUID, task.TASKID, task.PLANNEDHOURS - isnull(sum(actiondetail.DURATION), 0) as Balancehours from TTASK task left outer join TACTIONDETAIL actiondetail on (actiondetail.TASKID = task.TASKID) left outer join TACTION action1 on (action1.ACTIONID = actiondetail.ACTIONID and action1.PERIODID = task.PERIODID and action1.OUID = task.OUID) where 1=1 group by task.PERIODId, task.OUID, task.TASKID, task.PLANNEDHOURS ) subquery where subquery.TASKID = task.TASKID and subquery.PERIODId = task.PERIODId and subquery.OUID = task.OUID and subquery.TASKID <> -1 and task.STATUS = 1 and task.TASKSTATUS = 0 and task.ISTODO = 0 {ReplaceFields} and task.DETAILTYPE in (4) and allocation.ALLOCATIONID = task.ALLOCATIONID"; public const string GET_TASK_REPORT = @"Select task.TASKID as TaskId , task.TASKTYPE as TaskTaskType , task.BIZTRANSACTIONTYPEID as BizTransactionTypeId , task.OUID as OuId , task.PERIODID as PeriodId , task.TASKNUMBER as TaskNumber , task.TASKDATE as TaskDate , CONVERT(VARCHAR(8),task.createdon,108) as TaskTime, task.REFERENCENUMBER as TaskReferenceNumber , task.REFERENCEDATE as TaskReferenceDate , task.TASKNAME as TaskTaskName , task.TASKDESCRIPTION as TaskDescription , task.DETAILTYPE as ChildDetailType , CASE WHEN task.DETAILTYPE=0 THEN 'M' WHEN task.DETAILTYPE=1 THEN 'R' WHEN task.DETAILTYPE=2 THEN 'H' WHEN task.DETAILTYPE=3 THEN 'W' ELSE 'T' END AS TaskDetailTypeName, task.TASKNATURE as TaskTaskNature , CASE WHEN task.TASKNATURE=0 THEN 'GENERAL' WHEN task.TASKNATURE=1 THEN 'ASSET' WHEN task.TASKNATURE=2 THEN 'MACHINE' WHEN task.TASKNATURE=3 THEN 'RESOURCE' WHEN task.TASKNATURE=4 THEN 'CALL' ELSE 'WORK' END AS TaskNatureName, task.PARTYBRANCHID as PartyBranchId , mpb.PARTYBRANCHCODE as PartyBranchCode, mpb.PARTYBRANCHNAME as PartyBranchName, task.ACTIVITYID as ActivityId , mactivity.ACTIVITYCODE as ActivityCode, mactivity.ACTIVITYNAME as ActivityName, task.ASSIGNEDTOTYPE as AssignedToType, CASE WHEN task.ASSIGNEDTOTYPE=0 THEN '' WHEN task.ASSIGNEDTOTYPE=1 THEN '' WHEN task.ASSIGNEDTOTYPE=2 THEN '' WHEN task.ASSIGNEDTOTYPE=3 THEN u.DefaultCmsId WHEN task.ASSIGNEDTOTYPE=4 THEN emp.DefaultImageId ELSE '' END AS DefaultImageId, task.REQUESTEDBYID as RequestedById , req.EMPLOYEECODE as RequestedByCode, req.EMPLOYEENAME as RequestedByName, task.INCHARGEID as InChargeId , employee.EMPLOYEEID as EmployeeId , employee.EMPLOYEECODE as InChargeCode , employee.EMPLOYEENAME as InChargeName , employee.PRIMARYEMAILID as InchargePrimaryMailId, assignemp.PRIMARYEMAILID as AssignedToPrimaryMailId, CASE when task.assignedtotype = 1 then task.ASSIGNEDTOROLEID when task.assignedtotype = 2 then task.ASSIGNEDTOUSERGROUPID when task.assignedtotype = 3 then task.ASSIGNEDTOUSERID when task.assignedtotype = 4 then task.ASSIGNEDTOEMPLOYEEID when task.assignedtotype = 5 then task.ASSIGNEDTOWORKTYPEID when task.assignedtotype = 6 then task.ASSIGNEDTOCONTACTID when task.assignedtotype = 7 then task.PARTYBRANCHID end as AssignedToEmployeeId, CASE when task.assignedtotype = 1 then role.ROLECODE when task.assignedtotype = 2 then usergroup.USERGROUPCODE when task.assignedtotype = 3 then assignuser.usercode when task.assignedtotype = 4 then assignemp.EMPLOYEECODE when task.assignedtotype = 5 then gcm.GCMCODE when task.assignedtotype = 6 then contact.FIRSTNAME when task.assignedtotype = 7 then partybranch.PARTYBRANCHCODE end as AssignedToEmployeeCode, CASE when task.assignedtotype = 1 then role.ROLENAME when task.assignedtotype = 2 then usergroup.USERGROUPNAME when task.assignedtotype = 3 then assignuser.username when task.assignedtotype = 4 then assignemp.EMPLOYEEname when task.assignedtotype = 5 then gcm.GCMNAME when task.assignedtotype = 6 then contact.LASTNAME when task.assignedtotype = 7 then partybranch.PARTYBRANCHNAME end as AssignedToEmployeeName, task.ALLOCATIONID as AllocationId , malloc.ALLOCATIONNAME as AllocationName, malloc.ALLOCATIONSHORTNAME as AllocationShortName, task.PARENTTASKID as ParentTaskId , task.TASKSTATUS as TaskOriginalStatus , CASE WHEN task.TASKSTATUS=0 THEN 'Pending' WHEN task.TASKSTATUS=1 THEN 'Assigned' WHEN task.TASKSTATUS=3 THEN 'Completed' WHEN task.TASKSTATUS=2 THEN 'Inprogress' ELSE 'Closed' END AS TaskOriginalStatusName, task.ACTUALSTARTON as TaskActualStartOn , task.ACTUALENDON as TaskActualEndOn , task.ACTUALHOURS as TaskActualHours , task.PLANNEDHOURS as TaskPlannedHours , task.PLANSTARTON as TaskPlanStartOn , task.PLANENDON as TaskPlanEndOn , task.ORIGINALPLANNEDDATE as OriginalPlannedDate , task.ORIGINALPLANNEDHOURS as OriginalPlannedHours , task.REMARKS as TaskRemarks , task.DUEON as TaskDueOn , task.EXPIREON as ExpireOn, task.GRACETIME as GraceTime ,parent.TASKNAME as ParentName,task.WBCCODE as WBCCode ,parent.TASKNUMBER as ParentTaskNumber ,parent.DetailType as ParentDetailType , task.COMPLETEDPERCENTAGE as CompletedPercentage, CASE WHEN task.STATUS=0 THEN 'Pending' WHEN task.STATUS=1 THEN 'Active' WHEN task.STATUS=2 THEN 'Deleted' WHEN task.STATUS=3 THEN 'Amended' ELSE 'Inactive' END AS RecordStatus, CASE WHEN task.PRIORITY =0 THEN 'Low' WHEN task.PRIORITY=1 THEN 'Medium' ELSE 'High' END AS Priority, case when task.PLANSTARTON < cast(GETDATE() as date)then 'Overdue Yes' else 'Overdue No' End as Overdue, case when milestone.taskid is not null then milestone.taskid else 0 end MileStoneId, case when milestone.taskid is not null then milestone.tasknumber else '' end MileStoneNumber, case when milestone.taskid is not null then milestone.taskname else '' end MileStoneName, case when milestone.taskid is not null then milestone.taskdescription else '' end MileStoneDescription, case when milestone.taskid is not null then milestone.status else -1 end MileStoneStatus, case when head.taskid is not null then head.taskid else 0 end HeadingId, case when head.taskid is not null then head.tasknumber else '' end HeadingNumber, case when head.taskid is not null then head.taskdescription else '' end HeadingDescription , case when head.taskid is not null then head.taskname else '' end HeadingName, case when head.taskid is not null then head.status else -1 end HeadingStatus, case when work.taskid is not null then work.taskid else 0 end WorkId, case when work.taskid is not null then work.tasknumber else '' end WorkNumber, case when work.taskid is not null then work.taskdescription else '' end WorkDescription, case when work.taskid is not null then work.taskname else '' end WorkName, case when work.taskid is not null then work.status else -1 end WorkStatus, case when rollup.taskid is not null then rollup.taskid else 0 end RollUpId, case when rollup.taskid is not null then rollup.tasknumber else '' end RollUpNumber, case when rollup.taskid is not null then rollup.taskdescription else '' end RollUpDescription, case when rollup.taskid is not null then rollup.taskname else '' end RollUpName, case when rollup.taskid is not null then rollup.status else -1 end RollUpStatus, organizationunit.ORGANIZATIONUNITNAME as OuName, lead.LEADCODE as LeadCode, lead.LEADNAME as LeadName, task.CREATEDON as CreatedOn, task.CREATEDBYID as CreatedById, createduser.USERNAME as CreatedByName, task.MODIFIEDBYID as ModifiedById, modifieduser.USERNAME as ModifiedByName, task.MODIFIEDON as ModifiedOn, allocation.ALLOCATIONNAME as ProjectName, task.VERSION as TaskVersion, task.ASSETID as AssetId, mas.ASSETCODE as AssetCode, mas.ASSETNAME as AssetName ,task.version Version ,task.ALLOCATIONSPLITID as AllocationSplitId ,malls.SPLITCODE as SplitCode ,malls.SPLITNAME as SplitName ,task.PROJECTSEQUENCE as TaskProjectSequence ,task.RESOURCESEQUENCE as TaskResourceSequence @actionselectreplace from MEMPLOYEE req, MEMPLOYEE employee, masset mas left outer join massettype asstype on(mas.assettypeid=asstype.assettypeid) left outer join TTASK task on(1=1) left outer join massettypeactivity asstypeact on(task.activityid=asstypeact.activityid and asstypeact.AssetTypeId=asstype.AssetTypeId ) LEFT OUTER JOIN MUSER u on(task.INCHARGEID =U.USERID and task.ASSIGNEDTOTYPE=3) LEFT OUTER JOIN MEMPLOYEE emp on(task.ASSIGNEDTOEMPLOYEEID =emp.EMPLOYEEID and task.ASSIGNEDTOTYPE=4 and emp.EMPLOYEESTATUS in(0)) LEFT OUTER JOIN MROLE role on(task.ASSIGNEDTOROLEID =role.ROLEID and task.ASSIGNEDTOTYPE=1) LEFT OUTER JOIN MUSERGROUP usergroup on(task.assignedtousergroupid =usergroup.USERGROUPID and task.ASSIGNEDTOTYPE=2) LEFT OUTER JOIN MUSER assignuser on(task.ASSIGNEDTOUSERID =assignuser.USERID and task.ASSIGNEDTOTYPE=3) LEFT OUTER JOIN MEMPLOYEE assignemp on(task.ASSIGNEDTOEMPLOYEEID =assignemp.EMPLOYEEID and task.ASSIGNEDTOTYPE=4 and emp.EMPLOYEESTATUS in(0)) LEFT OUTER JOIN MGCM gcm on(task.ASSIGNEDTOWORKTYPEID =gcm.gcmid and gcm.GCMTYPEID=-1399999995 and task.ASSIGNEDTOTYPE=5) LEFT OUTER JOIN MCONTACT contact on(task.ASSIGNEDTOCONTACTID =contact.CONTACTID and task.ASSIGNEDTOTYPE=6) LEFT OUTER JOIN MALLOCATION malloc on(task.ALLOCATIONID=malloc.ALLOCATIONID) left outer join MALLOCATIONSPLIT malls on (task.ALLOCATIONSPLITID = malls.ALLOCATIONSPLITID) LEFT OUTER JOIN MACTIVITY mactivity on(task.ACTIVITYID=mactivity.ACTIVITYID) @actionfromreplace LEFT OUTER JOIN MACTIVITYTYPE mactivitytype on(mactivity.ACTIVITYTYPEID=mactivitytype.ACTIVITYTYPEID) LEFT OUTER JOIN MPARTYBRANCH mpb on(task.PARTYBRANCHID =mpb.PARTYBRANCHID) LEFT OUTER JOIN TTASK work on (task.parenttaskid=work.taskid AND work.DETAILTYPE=3) LEFT OUTER JOIN TTASK head on (work.parenttaskid=head.taskid AND head.DETAILTYPE=2 ) LEFT OUTER JOIN TTASK rollup on (head.parenttaskid=rollup.taskid AND head.DETAILTYPE=1) LEFT OUTER JOIN TTASK milestone on (rollup.parenttaskid=milestone.taskid AND head.DETAILTYPE=0) LEFT OUTER JOIN MORGANIZATIONUNIT organizationunit on (task.ouid=organizationunit.ouid) LEFT OUTER JOIN TLEAD lead on (task.LEADID=lead.LEADID) LEFT OUTER JOIN MUSER createduser on (task.CREATEDBYID=createduser.USERID) LEFT OUTER JOIN MUSER modifieduser on (task.MODIFIEDBYID=modifieduser.USERID) LEFT OUTER JOIN MALLOCATION allocation on (task.ALLOCATIONID=allocation.ALLOCATIONID) LEFT OUTER JOIN MPARTYBRANCH partybranch on (task.PARTYBRANCHID=partybranch.PARTYBRANCHID and task.ASSIGNEDTOTYPE=7), TTASK parent WHERE 1=1 AND task.INCHARGEID = employee.EMPLOYEEID AND task.REQUESTEDBYID = req.EMPLOYEEID AND task.assetid = mas.assetid AND task.PARENTTASKID = parent.taskid "; public const string GET_TASK_REPORT_COUNT = @" SELECT COUNT(1) FROM MEMPLOYEE req, MEMPLOYEE employee, masset mas LEFT OUTER JOIN massettype asstype ON (mas.assettypeid = asstype.assettypeid) LEFT OUTER JOIN TTASK task ON (1=1) LEFT OUTER JOIN massettypeactivity asstypeact ON ( task.activityid = asstypeact.activityid AND asstypeact.AssetTypeId = asstype.AssetTypeId ) LEFT OUTER JOIN MUSER u ON(task.INCHARGEID = U.USERID AND task.ASSIGNEDTOTYPE = 3) LEFT OUTER JOIN MEMPLOYEE emp ON(task.ASSIGNEDTOEMPLOYEEID = emp.EMPLOYEEID AND task.ASSIGNEDTOTYPE = 4 AND emp.EMPLOYEESTATUS IN (0)) LEFT OUTER JOIN MROLE role ON(task.ASSIGNEDTOROLEID = role.ROLEID AND task.ASSIGNEDTOTYPE = 1) LEFT OUTER JOIN MUSERGROUP usergroup ON(task.assignedtousergroupid = usergroup.USERGROUPID AND task.ASSIGNEDTOTYPE = 2) LEFT OUTER JOIN MUSER assignuser ON(task.ASSIGNEDTOUSERID = assignuser.USERID AND task.ASSIGNEDTOTYPE = 3) LEFT OUTER JOIN MEMPLOYEE assignemp ON(task.ASSIGNEDTOEMPLOYEEID = assignemp.EMPLOYEEID AND task.ASSIGNEDTOTYPE = 4 AND emp.EMPLOYEESTATUS IN (0)) LEFT OUTER JOIN MGCM gcm ON(task.ASSIGNEDTOWORKTYPEID = gcm.gcmid AND gcm.GCMTYPEID = -1399999995 AND task.ASSIGNEDTOTYPE = 5) LEFT OUTER JOIN MCONTACT contact ON(task.ASSIGNEDTOCONTACTID = contact.CONTACTID AND task.ASSIGNEDTOTYPE = 6) LEFT OUTER JOIN MALLOCATION malloc ON(task.ALLOCATIONID = malloc.ALLOCATIONID) LEFT OUTER JOIN MALLOCATIONSPLIT malls ON(task.ALLOCATIONSPLITID = malls.ALLOCATIONSPLITID) LEFT OUTER JOIN MACTIVITY mactivity ON(task.ACTIVITYID = mactivity.ACTIVITYID) LEFT OUTER JOIN MACTIVITYTYPE mactivitytype ON(mactivity.ACTIVITYTYPEID = mactivitytype.ACTIVITYTYPEID) LEFT OUTER JOIN MPARTYBRANCH mpb ON(task.PARTYBRANCHID = mpb.PARTYBRANCHID) LEFT OUTER JOIN TTASK work ON(task.parenttaskid = work.taskid AND work.DETAILTYPE = 3) LEFT OUTER JOIN TTASK head ON(work.parenttaskid = head.taskid AND head.DETAILTYPE = 2) LEFT OUTER JOIN TTASK rollup ON(head.parenttaskid = rollup.taskid AND head.DETAILTYPE = 1) LEFT OUTER JOIN TTASK milestone ON(rollup.parenttaskid = milestone.taskid AND head.DETAILTYPE = 0) LEFT OUTER JOIN MORGANIZATIONUNIT organizationunit ON(task.ouid = organizationunit.ouid) LEFT OUTER JOIN TLEAD lead ON(task.LEADID = lead.LEADID) LEFT OUTER JOIN MUSER createduser ON(task.CREATEDBYID = createduser.USERID) LEFT OUTER JOIN MUSER modifieduser ON(task.MODIFIEDBYID = modifieduser.USERID) LEFT OUTER JOIN MALLOCATION allocation ON(task.ALLOCATIONID = allocation.ALLOCATIONID) LEFT OUTER JOIN MPARTYBRANCH partybranch ON(task.PARTYBRANCHID = partybranch.PARTYBRANCHID AND task.ASSIGNEDTOTYPE = 7), TTASK parent WHERE 1 = 1 AND task.INCHARGEID = employee.EMPLOYEEID AND task.REQUESTEDBYID = req.EMPLOYEEID AND task.assetid = mas.assetid AND task.PARENTTASKID = parent.taskid"; public const string GET_WORKFLOW_ASSOSIATED_WITH_TASK = @" SELECT MAX(CASE WHEN A.NAME_ = 'DisplayValue' THEN A.TEXT_ END) AS DisplayValue, MAX(CASE WHEN A.NAME_ = 'fROMUSERCode' THEN A.TEXT_ END) AS FromUserCode, MAX(CASE WHEN A.NAME_ = 'InchargeMail' THEN A.TEXT_ END) AS InchargeMail, MAX(CASE WHEN A.NAME_ = 'tYPE' THEN A.TEXT_ END) AS EntityCode, MAX(CASE WHEN A.NAME_ = 'DocumentNumber' THEN A.TEXT_ END) AS DocumentNumber, MAX(CASE WHEN A.NAME_ = 'ObjectId' THEN A.TEXT_ END) AS ObjectId, MAX(A.PROC_INST_ID_) AS ProcessInstanceId FROM ACT_RU_VARIABLE A, ( SELECT A.TEXT_, A.NAME_, A.PROC_INST_ID_ FROM ACT_RU_VARIABLE A, ( SELECT COUNT(*) AS VAL, PROC_INST_ID_ FROM ACTIVITI..ACT_RU_VARIABLE WHERE NAME_ IN ( 'DisplayValue', 'fROMUSERID', 'InchargeMail', 'tYPE', 'rEMARKS', 'DocumentNumber', 'ObjectId' ) GROUP BY PROC_INST_ID_ ) SUBQUERY WHERE SUBQUERY.PROC_INST_ID_ = A.PROC_INST_ID_ AND A.NAME_ IN ( 'DisplayValue', 'fROMUSERID', 'InchargeMail', 'tYPE', 'DocumentNumber', 'ObjectId' ) AND SUBQUERY.VAL = 6 AND A.TEXT_ = 'task' ) SUB WHERE A.PROC_INST_ID_ = SUB.PROC_INST_ID_ GROUP BY SUB.PROC_INST_ID_;"; public const string GET_TASKLIST_FOR_MOBILE_APP = @" SELECT task.TASKID AS TaskId, task.TASKNUMBER AS TaskNumber, task.TASKNAME AS TaskTaskName, task.TASKDESCRIPTION AS TaskDescription, employee.EMPLOYEENAME AS InChargeName, employee.EMPLOYEECODE AS InChargeCode, task.TASKSTATUS AS TaskOriginalStatus, task.PLANSTARTON AS TaskPlanStartOn, task.DUEON AS TaskDueOn FROM TTASK task INNER JOIN MEMPLOYEE employee ON task.INCHARGEID = employee.EMPLOYEEID INNER JOIN MASSET mas ON task.ASSETID = mas.ASSETID LEFT JOIN MACTIVITY mactivity ON task.ACTIVITYID = mactivity.ACTIVITYID LEFT JOIN MACTIVITYTYPE mactivitytype ON mactivity.ACTIVITYTYPEID = mactivitytype.ACTIVITYTYPEID WHERE 1 = 1 AND mas.RUNNINGSTATUS IN (1,3,4)"; public const string GET_TASKLIST_FOR_MOBILE_APP_WITH_STATUS = @" SELECT task.TASKID AS TaskId, task.TASKNUMBER AS TaskNumber, task.TASKNAME AS TaskTaskName, task.TASKDESCRIPTION AS TaskDescription, employee.EMPLOYEENAME AS InChargeName, employee.EMPLOYEECODE AS InChargeCode, task.TASKSTATUS AS TaskOriginalStatus, task.PLANSTARTON AS TaskPlanStartOn, task.DUEON AS TaskDueOn FROM TTASK task INNER JOIN MEMPLOYEE employee ON task.INCHARGEID = employee.EMPLOYEEID INNER JOIN MASSET mas ON task.ASSETID = mas.ASSETID LEFT JOIN MACTIVITY mactivity ON task.ACTIVITYID = mactivity.ACTIVITYID LEFT JOIN MACTIVITYTYPE mactivitytype ON mactivity.ACTIVITYTYPEID = mactivitytype.ACTIVITYTYPEID WHERE 1 = 1 AND task.TASKNATURE IN (1,2,3) AND mas.RUNNINGSTATUS IN (1,3,4) AND task.OUID = @MobileOUId"; // GB4 parity: TaskQueryBuilder.GET_CALL_WORKBENCH — used by GetWorkBench when // WorkBenchType == 1 (call-based workbench, as opposed to task-based). public const string GET_CALL_WORKBENCH = @"Select call.CALLID as TaskId , call.CALLTYPEID as CallTypeId , 0 as TaskTaskType , calltypegcm.GCMNAME as CallTypeName, call.BIZTRANSACTIONTYPEID as BizTransactionTypeId , call.OUID as OuId , call.PERIODID as PeriodId , call.CALLNUMBER as TaskNumber , call.CALLDATE as TaskDate , call.REFERENCENUMBER as TaskReferenceNumber , call.REFERENCEDATE as TaskReferenceDate , call.REPORTEDPROBLEM as TaskTaskName , call.CUSTOMERREMARKS as TaskDescription , 0 as TaskDetailType , 'NONE' as TaskDetailTypeName , 0 as ActivityId , 'NONE' as ActivityCode, 'NONE' as ActivityName, -1 as RequestedById , 'NONE' as RequestedByCode, 'NONE' as RequestedByName, -1 as AllocationId , 'NONE' as AllocationName, -1 as ParentTaskId , call.CALLNATUREID as TaskTaskNature , gcm.GCMNAME As TaskNatureName, call.PARTYBRANCHID as PartyBranchId , mpb.PARTYBRANCHCODE as PartyBranchCode, mpb.PARTYBRANCHNAME as PartyBranchName, call.OBJECTTYPEID as SelectTypeId, CASE call.OBJECTTYPEID when -1899999858 then 'PARTY' WHEN -1899999839 THEN 'PARTYBRANCH' WHEN -2147481759 THEN 'CONTACT' ELSE 'EMPLOYEE' END AS SelectTypeName, call.ALLOTEDTOTYPE as AssignedTo, CASE call.ALLOTEDTOTYPE when 0 THEN 'EMPLOYEE' when 1 THEN 'TECHNICIAN' ELSE 'PARTY' END AS CallAllotedTypeName, CASE call.ALLOTEDTOTYPE when 0 THEN call.ALLOTEDTOEMPLOYEEID when 1 THEN call.ALLOTEDTOCONTACTID when 2 THEN call.ALLOTEDTOPARTYID END AS InChargeId, CASE call.ALLOTEDTOTYPE when 0 THEN E.EMPLOYEECODE WHEN 1 THEN allotedtocon.FIRSTNAME WHEN 2 THEN allotedtoparty.PARTYCODE END AS InChargeCode, case call.ALLOTEDTOTYPE when 0 THEN E.EMPLOYEENAME WHEN 1 THEN allotedtocon.FIRSTNAME WHEN 2 THEN allotedtoparty.PARTYNAME END AS InChargeName, call.CALLSTATUS as TaskOriginalStatus , CASE call.CALLSTATUS when 0 then 'Pending' when 1 THEN 'Allotted' when 2 THEN 'COMPLETED' when 3 THEN 'Postponed' ELSE 'Callback' END AS TaskOriginalStatusName, call.PROBLEMDATE as TaskActualStartOn, call.CLOSEDONTIME as TaskActualHours , call.COMPLETEDON as TaskDueOn , call.CLOSEDON as ExpireOn, call.CUSTOMERREMARKS as TaskRemarks , call.CALLNATUREID as CallNatureId, gcm.GCMNAME as CallNatureName, call.COMPLETEDBYID as CompletedById, req.USERNAME as CompletedByName, call.CLOSEDBYID as ClosedById, closedby.USERNAME as ClosedByName, call.CallPriorityId as CallPriorityId, prigcm.GCMNAME As CallPriorityName, call.CALLONOBJECTID as AssetId, mas.ASSETCODE as AssetCode, mas.ASSETNAME as AssetName from TCALL call LEFT OUTER JOIN MEMPLOYEE E on(call.ALLOTEDTOEMPLOYEEID =E.EMPLOYEEID) LEFT OUTER JOIN MUSER req on(call.COMPLETEDBYID =req.USERID) LEFT OUTER JOIN MUSER closedby on(call.CLOSEDBYID=closedby.USERID) LEFT OUTER JOIN MPARTYBRANCH mpb on(call.PARTYBRANCHID =mpb.PARTYBRANCHID) LEFT OUTER JOIN MGCM gcm on(call.CALLNATUREID =gcm.GCMID) LEFT OUTER JOIN MCONTACT allotedtocon on(call.ALLOTEDTOCONTACTID =allotedtocon.CONTACTID) LEFT OUTER JOIN MPARTY allotedtoparty on(call.ALLOTEDTOPARTYID =allotedtoparty.PARTYID) LEFT OUTER JOIN MGCM calltypegcm on(call.CALLTYPEID =calltypegcm.GCMID) LEFT OUTER JOIN MGCM prigcm on(call.CallPriorityId =prigcm.GCMID) LEFT OUTER JOIN MASSET mas on (call.CALLONOBJECTTYPEID=-1200000000 and call.CALLONOBJECTID = mas.assetid) where 1=1 "; public const string GET_CALL_WORKBENCH_COUNT = @" SELECT COUNT(1) FROM TCALL call LEFT OUTER JOIN MEMPLOYEE E on(call.ALLOTEDTOEMPLOYEEID =E.EMPLOYEEID) LEFT OUTER JOIN MCONTACT allotedtocon on(call.ALLOTEDTOCONTACTID =allotedtocon.CONTACTID) LEFT OUTER JOIN MPARTY allotedtoparty on(call.ALLOTEDTOPARTYID =allotedtoparty.PARTYID) where 1=1 "; // GB4 parity: TaskQueryBuilder.WORK_BENCH_TASK — dedicated task-workbench query // (WorkBenchType == 0). Distinct from GET_TASK_REPORT: denormalized code/name // columns instead of the report's aggregated hours/subqueries. public const string WORK_BENCH_TASK = @" SELECT task.TASKID as TaskId, task.TASKTYPE as TaskTaskType, task.BIZTRANSACTIONTYPEID as BizTransactionTypeId, task.OUID as OuId, task.PERIODID as PeriodId, task.TASKNUMBER as TaskNumber, task.TASKDATE as TaskDate, task.REFERENCENUMBER as TaskReferenceNumber, task.REFERENCEDATE as TaskReferenceDate, task.TASKNAME as TaskTaskName, task.TASKDESCRIPTION as TaskDescription, task.DETAILTYPE as TaskDetailType, CASE task.DETAILTYPE WHEN 0 THEN 'M' WHEN 1 THEN 'R' WHEN 2 THEN 'H' WHEN 3 THEN 'W' ELSE 'T' END AS TaskDetailTypeName, task.TASKNATURE as TaskTaskNature, CASE task.TASKNATURE WHEN 0 THEN 'GENERAL' WHEN 1 THEN 'ASSET' WHEN 2 THEN 'MACHINE' WHEN 3 THEN 'RESOURCE' ELSE 'CALL' END AS TaskNatureName, task.PARTYBRANCHID as PartyBranchId, mpb.PARTYBRANCHCODE as PartyBranchCode, mpb.PARTYBRANCHNAME as PartyBranchName, task.ACTIVITYID as ActivityId, mactivity.ACTIVITYCODE as ActivityCode, mactivity.ACTIVITYNAME as ActivityName, task.REQUESTEDBYID as RequestedById, req.USERCODE as RequestedByCode, req.USERNAME as RequestedByName, task.ASSIGNEDTOEMPLOYEEID as InChargeId, E.EMPLOYEECODE as InChargeCode, E.EMPLOYEENAME as InChargeName, task.ALLOCATIONID as AllocationId, malloc.ALLOCATIONNAME as AllocationName, task.PARENTTASKID as ParentTaskId, task.TASKSTATUS as TaskOriginalStatus, CASE task.TASKSTATUS WHEN 0 THEN 'Pending' WHEN 1 THEN 'Assigned' WHEN 3 THEN 'COMPLETED' WHEN 2 THEN 'INPROGRESS' ELSE 'CLOSED' END AS TaskOriginalStatusName, task.ACTUALSTARTON as TaskActualStartOn, task.ACTUALENDON as TaskActualEndOn, task.ACTUALHOURS as TaskActualHours, task.PLANNEDHOURS as TaskPlannedHours, task.PLANSTARTON as TaskPlanStartOn, task.PLANENDON as TaskPlanEndOn, task.REMARKS as TaskRemarks, task.DUEON as TaskDueOn, task.EXPIREON as ExpireOn, task.ISTODO as TaskIsToDo, task.GRACETIME as GraceTime, task.WBCCODE as WBCCode, task.ASSETID as AssetId, mas.ASSETCODE as AssetCode, mas.ASSETNAME as AssetName FROM TTASK task LEFT OUTER JOIN MUSER u on (task.ASSIGNEDTOEMPLOYEEID = u.USERID) LEFT OUTER JOIN MEMPLOYEE E on (task.ASSIGNEDTOEMPLOYEEID = E.EMPLOYEEID) LEFT OUTER JOIN MUSER req on (task.REQUESTEDBYID = req.USERID) LEFT OUTER JOIN MALLOCATION malloc on (task.ALLOCATIONID = malloc.ALLOCATIONID) LEFT OUTER JOIN MACTIVITY mactivity on (task.ACTIVITYID = mactivity.ACTIVITYID) LEFT OUTER JOIN MPARTYBRANCH mpb on (task.PARTYBRANCHID = mpb.PARTYBRANCHID) LEFT OUTER JOIN MASSET mas on (task.ASSETID = mas.ASSETID) WHERE 1=1"; public const string GET_WORK_BENCH_TASK_COUNT = @" SELECT COUNT(1) FROM TTASK task LEFT OUTER JOIN MUSER u on (task.ASSIGNEDTOEMPLOYEEID = u.USERID) LEFT OUTER JOIN MEMPLOYEE E on (task.ASSIGNEDTOEMPLOYEEID = E.EMPLOYEEID) LEFT OUTER JOIN MUSER req on (task.REQUESTEDBYID = req.USERID) LEFT OUTER JOIN MALLOCATION malloc on (task.ALLOCATIONID = malloc.ALLOCATIONID) LEFT OUTER JOIN MACTIVITY mactivity on (task.ACTIVITYID = mactivity.ACTIVITYID) LEFT OUTER JOIN MPARTYBRANCH mpb on (task.PARTYBRANCHID = mpb.PARTYBRANCHID) LEFT OUTER JOIN MASSET mas on (task.ASSETID = mas.ASSETID) WHERE 1=1"; } }