using System;

namespace FrameworkDAL.Query.WorkflowAssignment
{
    public static class WorkflowAssignmentQB
    {
        public const string GET_WORKFLOW_ASSIGNMENT = @"
SELECT
    WA.WORKFLOWASSIGNMENTID   AS WorkflowAssignmentId,
    WA.WORKFLOWASSIGNMENTNAME AS WorkflowAssignmentName,
    WA.WORKFLOWID             AS WorkflowId,
    W.WORKFLOWNAME            AS WorkflowName,
    WA.STRATEGYTYPE           AS StrategyType,
    WA.RULEEXPRESSION         AS RuleExpression,
    WA.DESCRIPTION            AS Description,
    WA.TENANTID               AS TenantId,
    WA.SORTORDER              AS SortOrder,
    WA.STATUS                 AS Status,
    WA.VERSION                AS Version,
    WA.SOURCETYPE             AS SourceType,
    WA.CREATEDBYID            AS CreatedById,
    WA.CREATEDON              AS CreatedOn,
    WA.MODIFIEDBYID           AS ModifiedById,
    WA.MODIFIEDON             AS ModifiedOn
FROM MWORKFLOWASSIGNMENT WA
LEFT JOIN MWORKFLOW W ON W.WORKFLOWID = WA.WORKFLOWID
WHERE WA.WORKFLOWASSIGNMENTID = @WorkflowAssignmentId
  AND WA.STATUS = 1;";

        public const string SAVE_WORKFLOW_ASSIGNMENT = @"
INSERT INTO MWORKFLOWASSIGNMENT
(
    WORKFLOWASSIGNMENTNAME,
    WORKFLOWID,
    STRATEGYTYPE,
    RULEEXPRESSION,
    DESCRIPTION,
    TENANTID,
    SORTORDER,
    STATUS,
    VERSION,
    SOURCETYPE,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @WorkflowAssignmentName,
    @WorkflowId,
    @StrategyType,
    @RuleExpression,
    @Description,
    @TenantId,
    @SortOrder,
    @Status,
    @Version,
    @SourceType,
    @CreatedById,
    @CreatedOn,
    @ModifiedById,
    @ModifiedOn
);";

        public const string UPDATE_WORKFLOW_ASSIGNMENT = @"
UPDATE MWORKFLOWASSIGNMENT
SET
    WORKFLOWASSIGNMENTNAME = @WorkflowAssignmentName,
    WORKFLOWID             = @WorkflowId,
    STRATEGYTYPE           = @StrategyType,
    RULEEXPRESSION         = @RuleExpression,
    DESCRIPTION            = @Description,
    TENANTID               = @TenantId,
    SORTORDER              = @SortOrder,
    STATUS                 = @Status,
    VERSION                = @Version,
    SOURCETYPE             = @SourceType,
    MODIFIEDBYID           = @ModifiedById,
    MODIFIEDON             = @ModifiedOn
WHERE WORKFLOWASSIGNMENTID = @WorkflowAssignmentId;";

        public const string GET_SELECTLIST_WORKFLOW_ASSIGNMENT = @"
WITH WorkflowAssignment AS
(
    SELECT
        WA.WORKFLOWASSIGNMENTID   AS WorkflowAssignmentId,
        WA.WORKFLOWASSIGNMENTNAME AS WorkflowAssignmentName,
        WA.WORKFLOWID             AS WorkflowId,
        W.WORKFLOWNAME            AS WorkflowName,
        WA.STRATEGYTYPE           AS StrategyType,
        WA.RULEEXPRESSION         AS RuleExpression,
        WA.DESCRIPTION            AS Description,
        WA.TENANTID               AS TenantId,
        WA.SORTORDER              AS SortOrder,
        WA.STATUS                 AS Status,
        WA.VERSION                AS Version,
        WA.SOURCETYPE             AS SourceType,
        WA.CREATEDBYID            AS CreatedById,
        WA.CREATEDON              AS CreatedOn,
        WA.MODIFIEDBYID           AS ModifiedById,
        WA.MODIFIEDON             AS ModifiedOn,
        ROW_NUMBER() OVER
        (
            ORDER BY WA.SORTORDER, WA.WORKFLOWASSIGNMENTNAME
        ) AS RowNum
    FROM MWORKFLOWASSIGNMENT WA
    LEFT JOIN MWORKFLOW W ON W.WORKFLOWID = WA.WORKFLOWID
    WHERE WA.STATUS = 1
)
SELECT
    WorkflowAssignmentId,
    WorkflowAssignmentName,
    WorkflowId,
    WorkflowName,
    StrategyType,
    RuleExpression,
    Description,
    TenantId,
    SortOrder,
    Status,
    Version,
    SourceType,
    CreatedById,
    CreatedOn,
    ModifiedById,
    ModifiedOn
FROM WorkflowAssignment
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string DELETE_WORKFLOW_ASSIGNMENT = @"
DELETE FROM MWORKFLOWASSIGNMENT
WHERE WORKFLOWASSIGNMENTID = @WorkflowAssignmentId;";
    }
}