using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace AdminDAL.Query.CheckingSetting
{
    // Required indexes:
    //   TCHECKINGSETTINGDETAIL: (CHECKINGSETTINGID) INCLUDE (SLNO, CHECKINGID, CHECKINGTYPE)
    //   TCHECKINGSETTING: (CHECKINGSETTINGID, OUID, BIZTRANSACTIONCLASSID, BIZTRANSACTIONTYPEID)
    //   MORGANIZATIONUNIT: (OUID) INCLUDE (ORGANIZATIONUNITCODE, ORGANIZATIONUNITNAME)
    //   MBIZTRANSACTIONCLASS: (BIZTRANSACTIONCLASSID) INCLUDE (BIZTRANSACTIONCLASSCODE, BIZTRANSACTIONCLASSNAME)
    //   MBIZTRANSACTIONTYPE: (BIZTRANSACTIONTYPEID) INCLUDE (BIZTRANSACTIONTYPECODE, BIZTRANSACTIONTYPENAME)
    //   MCHECKING: (CHECKINGID) INCLUDE (CHECKINGCODE, CHECKINGNAME, DISPLAYTEXT)
    public static class CheckingSettingQB
    {
        public const string GET_CHECKING_SETTING = @"SELECT 
    -- Parent: CheckingSetting
    cs.CHECKINGSETTINGID          AS CheckingSettingId,
    cs.STATUS                     AS CheckingSettingStatus,
    cs.VERSION                    AS CheckingSettingVersion,
    cs.CREATEDBYID                AS CheckingSettingCreatedById,
    cs.CREATEDON                  AS CheckingSettingCreatedOn,
    cs.MODIFIEDBYID               AS CheckingSettingModifiedById,
    cs.MODIFIEDON                 AS CheckingSettingModifiedOn,
    uCreated.USERNAME AS CheckingSettingCreatedByName,
    uModified.USERNAME AS CheckingSettingModifiedByName,
    -- Parent: Entity
    e.ENTITYID                    AS EntityId,
    e.ENTITYCODE                  AS EntityCode,
    e.ENTITYNAME                  AS EntityName,

    -- Parent: BizTransactionClass
    btc.BIZTRANSACTIONCLASSID     AS BIZTransactionClassId,
    btc.BIZTRANSACTIONCLASSCODE   AS BIZTransactionClassCode,
    btc.BIZTRANSACTIONCLASSNAME   AS BIZTransactionClassName,

    -- Parent: OrganizationUnit
    ou.OUID                       AS OUId,
    ou.ORGANIZATIONUNITCODE       AS OUCode,
    ou.ORGANIZATIONUNITNAME       AS OUName,

    -- Parent: Period
    p.PERIODID                    AS PeriodId,
    p.PERIODCODE                  AS PeriodCode,
    p.PERIODNAME                  AS PeriodName,
    p.FROMDATE                    AS PeriodFromDate,
    p.TODATE                      AS PeriodToDate,

    -- Parent: BizTransactionType
    btt.BIZTRANSACTIONTYPEID      AS BIZTransactionTypeId,
    btt.BIZTRANSACTIONTYPECODE    AS BIZTransactionTypeCode,
    btt.BIZTRANSACTIONTYPENAME    AS BIZTransactionTypeName,

    -- Child: CheckingSettingDetail
    csd.CHECKINGSETTINGDETAILID   AS CheckingSettingDetailId,
    csd.CHECKINGSETTINGID         AS CheckingSettingId,
    csd.SLNO                      AS CheckingSettingDetailSlNo,
    csd.CHECKINGTYPE              AS CheckingSettingDetailType,
    csd.DEPLOYMENTID              AS CheckingSettingDetailDeploymentId,
    DE.WORKFLOWDEPLOYMENTNAME     AS CheckingSettingDetailDeploymentName,

    -- Child: Checking
    chk.CHECKINGID                AS CheckingId,
    chk.CHECKINGCODE              AS CheckingCode,
    chk.CHECKINGNAME              AS CheckingName,
    chk.DISPLAYTEXT               AS CheckingDisplayText,

    -- Child: Role
    r.ROLEID                      AS RoleId,
    r.ROLECODE                    AS RoleCode,
    r.ROLENAME                    AS RoleName,

    -- Child: User
    u.USERID                      AS UserId,
    u.USERCODE                    AS UserCode,
    u.USERNAME                    AS UserName

FROM TCHECKINGSETTING cs
LEFT JOIN MUSER uCreated ON cs.CREATEDBYID = uCreated.USERID
LEFT JOIN MUSER uModified ON cs.MODIFIEDBYID = uModified.USERID
LEFT JOIN MENTITY e ON cs.ENTITYID = e.ENTITYID
LEFT JOIN MBIZTRANSACTIONCLASS btc ON cs.BIZTRANSACTIONCLASSID = btc.BIZTRANSACTIONCLASSID
LEFT JOIN MORGANIZATIONUNIT ou ON cs.OUID = ou.OUID
LEFT JOIN MPERIOD p ON cs.PERIODID = p.PERIODID
LEFT JOIN MBIZTRANSACTIONTYPE btt ON cs.BIZTRANSACTIONTYPEID = btt.BIZTRANSACTIONTYPEID
LEFT JOIN TCHECKINGSETTINGDETAIL csd ON cs.CHECKINGSETTINGID = csd.CHECKINGSETTINGID
LEFT JOIN MCHECKING chk ON csd.CHECKINGID = chk.CHECKINGID
LEFT JOIN MROLE r ON csd.ROLEID = r.ROLEID
LEFT JOIN MUSER u ON csd.USERID = u.USERID
LEFT JOIN MWORKFLOWDEPLOYMENT DE ON csd.DEPLOYMENTID = DE.WORKFLOWDEPLOYMENTID
WHERE cs.CHECKINGSETTINGID = @checkingsettingid;
";

        public const string SAVE_CHECKING_SETTING = @"INSERT INTO TCHECKINGSETTING
(
    CHECKINGSETTINGID,
    ENTITYID,
    BIZTRANSACTIONCLASSID,
    OUID,
    PERIODID,
    BIZTRANSACTIONTYPEID,
    VERSION,
    STATUS,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @CheckingSettingId,
    @EntityId,
    @BIZTransactionClassId,
    @OUId,
    @PeriodId,
    @BIZTransactionTypeId,
    @CheckingSettingVersion,
    @CheckingSettingStatus,
    @CheckingSettingCreatedById,
    @CheckingSettingCreatedOn,
    @CheckingSettingModifiedById,
    @CheckingSettingModifiedOn
);
";

        public const string UPDATE_CHECKING_SETTING = @"UPDATE TCHECKINGSETTING
SET
    ENTITYID = @EntityId,
    BIZTRANSACTIONCLASSID = @BIZTransactionClassId,
    OUID = @OUId,
    PERIODID = @PeriodId,
    BIZTRANSACTIONTYPEID = @BIZTransactionTypeId,
    VERSION = @CheckingSettingVersion,
    STATUS = @CheckingSettingStatus,
    MODIFIEDBYID = @CheckingSettingModifiedById,
    MODIFIEDON = @CheckingSettingModifiedOn
WHERE
    CHECKINGSETTINGID = @CheckingSettingId;
";

        public const string DELETE_CHECKING_SETTING = @"DELETE FROM TCHECKINGSETTING
                                WHERE CHECKINGSETTINGID = @CheckingSettingId;";

        public const string GET_SELECTLIST_CHECKINGSETTING_REPORT_DETAIL = @"WITH CheckingSetting AS (
    SELECT
        OU.ORGANIZATIONUNITCODE AS OUCode,
        OU.ORGANIZATIONUNITNAME AS OUName,
        NULL AS ModuleName,
        NULL AS ModuleCode,
        BTC.BIZTRANSACTIONCLASSCODE AS BizTransactionClassCode,
        BTC.BIZTRANSACTIONCLASSNAME AS BizTransactionClassName,
        BTT.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode,
        BTT.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName,
        CSD.SLNO AS SlNo,
        C.CHECKINGCODE AS CheckingCode,
        C.CHECKINGNAME AS CheckingName,
        C.DISPLAYTEXT AS CheckingDisplayText,
        CSD.CHECKINGTYPE AS CheckingType,
        COUNT(*) OVER() AS NumberOfRecords,
        ROW_NUMBER() OVER (ORDER BY OU.ORGANIZATIONUNITCODE) AS RowNum
    FROM TCHECKINGSETTINGDETAIL CSD
    INNER JOIN TCHECKINGSETTING CS ON CS.CHECKINGSETTINGID = CSD.CHECKINGSETTINGID
    INNER JOIN MORGANIZATIONUNIT OU ON OU.OUID = CS.OUID
    INNER JOIN MBIZTRANSACTIONCLASS BTC ON BTC.BIZTRANSACTIONCLASSID = CS.BIZTRANSACTIONCLASSID
    INNER JOIN MBIZTRANSACTIONTYPE BTT ON BTT.BIZTRANSACTIONTYPEID = CS.BIZTRANSACTIONTYPEID
    INNER JOIN MCHECKING C ON C.CHECKINGID = CSD.CHECKINGID
)
SELECT
    OUCode,
    OUName,
    ModuleName,
    ModuleCode,
    BizTransactionClassCode,
    BizTransactionClassName,
    BizTransactionTypeCode,
    BizTransactionTypeName,
    SlNo,
    CheckingCode,
    CheckingName,
    CheckingDisplayText,
    CheckingType,
    NumberOfRecords,
    RowNum
FROM CheckingSetting
WHERE
    (@firstnumber = -1 AND @maxresult = -1)
    OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_CHECKING_SETTING_DETAIL = @"SELECT 
                                                                    d.CHECKINGSETTINGDETAILID AS CheckingSettingDetailId,
                                                                    d.CHECKINGSETTINGID AS CheckingSettingId,
                                                                    d.SLNO AS CheckingSettingDetailSlNo,
                                                                    d.CHECKINGID AS CheckingId,
                                                                    c.CHECKINGCODE AS CheckingCode,
                                                                    c.CHECKINGNAME AS CheckingName,
                                                                    c.DISPLAYTEXT AS CheckingDisplayText,
                                                                    d.CHECKINGTYPE AS CheckingSettingDetailType,
                                                                    d.ROLEID AS RoleId,
                                                                    r.ROLECODE AS RoleCode,
                                                                    r.ROLENAME AS RoleName,
                                                                    d.USERID AS UserId,
                                                                    u.USERCODE AS UserCode,
                                                                    u.USERNAME AS UserName,
                                                                    d.DEPLOYMENTID AS CheckingSettingDetailDeploymentId
                                                                FROM 
                                                                    TCHECKINGSETTINGDETAIL d
                                                                LEFT JOIN MCHECKING c ON d.CHECKINGID = c.CHECKINGID
                                                                LEFT JOIN MROLE r ON d.ROLEID = r.ROLEID
                                                                LEFT JOIN MUSER u ON d.USERID = u.USERID
                                                                ";
    }
}