using System;

namespace FrameworkDAL.Query.WorkflowConfig
{
    public static class WorkflowConfigQB
    {
        public const string GET_WORKFLOW_CONFIG = @"
SELECT
    WC.WORKFLOWCONFIGID              AS WorkflowConfigId,
    WC.WORKFLOWCONFIGNAME            AS WorkflowConfigName,
    WC.CLIENTID                      AS WorkflowConfigClientId,

    WC.ENTITYID                      AS WorkflowConfigEntityId,
    ENT.ENTITYNAME                   AS WorkflowConfigEntityName,

    WC.OUID                          AS WorkflowConfigOuId,
    OU.ORGANIZATIONUNITNAME          AS WorkflowConfigOuName,

    WC.BIZTRANSACTIONCLASSID         AS WorkflowConfigBizTransactionClassId,
    BTC.BIZTRANSACTIONCLASSNAME      AS WorkflowConfigBizTransactionClassName,

    1 - WC.ISBIZTRANSACTIONWISE      AS WorkflowConfigIsBizTransactionWise,

    WC.BIZTRANSACTIONID              AS WorkflowConfigBizTransactionId,
    BT.BIZTRANSACTIONNAME            AS WorkflowConfigBizTransactionName,

    1 - WC.ISFORMBASEDAPPROVAL       AS WorkflowConfigIsFormBasedApproval,

    WC.WORKFLOWID                    AS WorkflowConfigWorkflowId,
    WF.WORKFLOWNAME                  AS WorkflowConfigWorkflowName,

    WC.EVALCONDITION                 AS WorkflowConfigEvalCondition,
    WC.REMARKS                       AS WorkflowConfigRemarks,
    WC.PARTICULARSTEMPLATE           AS WorkflowConfigParticularsTemplate,
    WC.CALLBACKENDPOINT              AS WorkflowConfigCallbackEndPoint,
    WC.CREATEDON                     AS WorkflowConfigCreatedDate

FROM MWORKFLOWCONFIG WC

LEFT JOIN MORGANIZATIONUNIT OU
    ON OU.OUID = WC.OUID

LEFT JOIN MENTITY ENT
    ON ENT.ENTITYID = WC.ENTITYID

LEFT JOIN MBIZTRANSACTIONCLASS BTC
    ON BTC.BIZTRANSACTIONCLASSID = WC.BIZTRANSACTIONCLASSID

LEFT JOIN MBIZTRANSACTION BT
    ON BT.BIZTRANSACTIONID = WC.BIZTRANSACTIONID

LEFT JOIN MWORKFLOW WF
    ON WF.WORKFLOWID = WC.WORKFLOWID

WHERE WC.WORKFLOWCONFIGID = @WorkflowConfigId;";


        public const string SAVE_WORKFLOW_CONFIG = @"INSERT INTO MWORKFLOWCONFIG
                                                    (
                                                        CLIENTID,
                                                        WORKFLOWCONFIGNAME,
                                                        ENTITYID,
                                                        OUID,
                                                        BIZTRANSACTIONCLASSID,
                                                        ISBIZTRANSACTIONWISE,
                                                        BIZTRANSACTIONID,
                                                        ISFORMBASEDAPPROVAL,
                                                        WORKFLOWID,
                                                        EVALCONDITION,
                                                        REMARKS,
                                                        CALLBACKENDPOINT,
                                                        PARTICULARSTEMPLATE,
                                                        CREATEDON
                                                    )
                                                    VALUES
                                                    (
                                                        @WorkflowConfigClientId,
                                                        @WorkflowConfigName,
                                                        @WorkflowConfigEntityId,
                                                        @WorkflowConfigOuId,
                                                        @WorkflowConfigBizTransactionClassId,
                                                        1 - CONVERT(INT, @WorkflowConfigIsBizTransactionWise),
                                                        @WorkflowConfigBizTransactionId,
                                                        1 - CONVERT(INT, @WorkflowConfigIsFormBasedApproval),
                                                        @WorkflowConfigWorkflowId,
                                                        @WorkflowConfigEvalCondition,
                                                        @WorkflowConfigRemarks,
                                                        @WorkflowConfigParticularsTemplate,
                                                        @WorkflowConfigCallbackEndPoint,
                                                        @WorkflowConfigCreatedDate
                                                    );";


        public const string UPDATE_WORKFLOW_CONFIG = @"UPDATE MWORKFLOWCONFIG
                                                      SET
                                                          WORKFLOWCONFIGNAME = @WorkflowConfigName,
                                                           ENTITYID = @WorkflowConfigEntityId,
                                                        CLIENTID = @WorkflowConfigClientId,
                                                        OUID = @WorkflowConfigOuId,
                                                        BIZTRANSACTIONCLASSID = @WorkflowConfigBizTransactionClassId,
                                                        ISBIZTRANSACTIONWISE = 1 - CONVERT(INT, @WorkflowConfigIsBizTransactionWise),
                                                        BIZTRANSACTIONID = @WorkflowConfigBizTransactionId,
                                                        ISFORMBASEDAPPROVAL = 1 - CONVERT(INT, @WorkflowConfigIsFormBasedApproval),
                                                        WORKFLOWID = @WorkflowConfigWorkflowId,
                                                        EVALCONDITION = @WorkflowConfigEvalCondition,
                                                        REMARKS = @WorkflowConfigRemarks,
                                                        CALLBACKENDPOINT = @WorkflowConfigCallbackEndPoint,
                                                        PARTICULARSTEMPLATE = @WorkflowConfigParticularsTemplate
                                                    WHERE WORKFLOWCONFIGID = @WorkflowConfigId;";


        public const string GET_SELECTLIST_WORKFLOW_CONFIG = @"
WITH WorkflowConfig AS
(
    SELECT
        WC.WORKFLOWCONFIGID          AS WorkflowConfigId,
        WC.WORKFLOWCONFIGNAME        AS WorkflowConfigName,
        WC.CLIENTID                  AS WorkflowConfigClientId,
        WC.ENTITYID                  AS WorkflowConfigEntityId,
        WC.OUID                      AS WorkflowConfigOuId,
        WC.BIZTRANSACTIONCLASSID     AS WorkflowConfigBizTransactionClassId,
        1 - WC.ISBIZTRANSACTIONWISE  AS WorkflowConfigIsBizTransactionWise,
        WC.BIZTRANSACTIONID          AS WorkflowConfigBizTransactionId,
        1 - WC.ISFORMBASEDAPPROVAL   AS WorkflowConfigIsFormBasedApproval,
        WC.WORKFLOWID                AS WorkflowConfigWorkflowId,
        WC.EVALCONDITION             AS WorkflowConfigEvalCondition,
        WC.REMARKS                   AS WorkflowConfigRemarks,
        WC.CREATEDON                 AS WorkflowConfigCreatedDate,
        WC.CALLBACKENDPOINT          AS WorkflowConfigCallbackEndPoint,
        WC.PARTICULARSTEMPLATE       AS WorkflowConfigParticularsTemplate,

        ROW_NUMBER() OVER
        (
            ORDER BY WC.WORKFLOWCONFIGID DESC
        ) AS RowNum
    FROM MWORKFLOWCONFIG WC
)
SELECT
    WorkflowConfigId,
    WorkflowConfigName,
    WorkflowConfigClientId,
    WorkflowConfigEntityId,
    WorkflowConfigOuId,
    WorkflowConfigBizTransactionClassId,
    WorkflowConfigIsBizTransactionWise,
    WorkflowConfigBizTransactionId,
    WorkflowConfigIsFormBasedApproval,
    WorkflowConfigWorkflowId,
    WorkflowConfigEvalCondition,
    WorkflowConfigRemarks,
    WorkflowConfigCreatedDate,
    WorkflowConfigCallbackEndPoint,
    WorkflowConfigParticularsTemplate
FROM WorkflowConfig
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult);
";


        public const string DELETE_WORKFLOW_CONFIG = @"DELETE FROM MWORKFLOWCONFIG
                                                       WHERE WORKFLOWCONFIGID = @WorkflowConfigId;";
    }
}