namespace CMSDAL.Query.Task
{
    public static class TaskQB
    {
        public const string GET_TASK = @"
            SELECT
                T.TASKID                AS TaskId,
                T.TASKNAME              AS TaskName,
                T.TASKDESCRIPTION       AS TaskDescription,
                T.TASKPRIORITY          AS TaskPriority,
                T.TASKSTATUS            AS TaskStatus,
                T.TASKDUEDATE           AS TaskDueDate,
                T.OBJECTTYPEID          AS ObjectTypeId,
                OT.ENTITYCODE           AS ObjectTypeCode,
                OT.ENTITYNAME           AS ObjectTypeName,
                T.OBJECTID              AS ObjectId,
                T.ASSIGNEDTOUSERID      AS AssignedToUserId,
                U.USERCODE              AS AssignedToUserCode,
                U.USERNAME              AS AssignedToUserName,
                T.VERSION               AS Version,
                T.CREATEDBYID           AS CreatedById,
                T.CREATEDON             AS CreatedOn,
                CB.USERNAME             AS CreatedByName,
                T.MODIFIEDBYID          AS ModifiedById,
                T.MODIFIEDON            AS ModifiedOn,
                MB.USERNAME             AS ModifiedByName
            FROM TCMSTASK T
            LEFT JOIN MOBJECTTYPE   OT ON OT.OBJECTTYPEID   = T.OBJECTTYPEID
            LEFT JOIN MUSERS         U ON U.USERID           = T.ASSIGNEDTOUSERID
            LEFT JOIN MUSERS        CB ON CB.USERID          = T.CREATEDBYID
            LEFT JOIN MUSERS        MB ON MB.USERID          = T.MODIFIEDBYID
            WHERE T.TASKID = @Taskid";

        public const string GET_SELECTLIST_TASK = @"
            SELECT
                T.TASKID                AS TaskId,
                T.TASKNAME              AS TaskName,
                T.TASKDESCRIPTION       AS TaskDescription,
                T.TASKPRIORITY          AS TaskPriority,
                T.TASKSTATUS            AS TaskStatus,
                T.TASKDUEDATE           AS TaskDueDate,
                U.USERNAME              AS AssignedToUserName
            FROM TCMSTASK T
            LEFT JOIN MUSERS U ON U.USERID = T.ASSIGNEDTOUSERID
            WHERE T.TASKSTATUS != 99
            ORDER BY T.TASKID DESC";

        public const string SAVE_TASK = @"
            INSERT INTO TCMSTASK
            (
                TASKNAME, TASKDESCRIPTION, TASKPRIORITY, TASKSTATUS,
                TASKDUEDATE, OBJECTTYPEID, OBJECTID,
                ASSIGNEDTOUSERID, VERSION,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            )
            VALUES
            (
                @TaskName, @TaskDescription, @TaskPriority, @TaskStatus,
                @TaskDueDate, @ObjectTypeId, @ObjectId,
                @AssignedToUserId, 1,
                @CreatedById, @CreatedOn, @ModifiedById, @ModifiedOn
            )";

        public const string UPDATE_TASK = @"
            UPDATE TCMSTASK SET
                TASKNAME            = @TaskName,
                TASKDESCRIPTION     = @TaskDescription,
                TASKPRIORITY        = @TaskPriority,
                TASKSTATUS          = @TaskStatus,
                TASKDUEDATE         = @TaskDueDate,
                OBJECTTYPEID        = @ObjectTypeId,
                OBJECTID            = @ObjectId,
                ASSIGNEDTOUSERID    = @AssignedToUserId,
                VERSION             = VERSION + 1,
                MODIFIEDBYID        = @ModifiedById,
                MODIFIEDON          = @ModifiedOn
            WHERE TASKID = @TaskId";

        public const string DELETE_TASK = @"
            UPDATE TCMSTASK SET
                TASKSTATUS   = 99,
                MODIFIEDBYID = @ModifiedById,
                MODIFIEDON   = @ModifiedOn
            WHERE TASKID = @Taskid";
    }
}