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

namespace AdminDAL.Query.BIZTransaction
{
    public static class BIZTransactionQB
    {
        public const string GET_MAX_DATE_BASED_ON_TABLE_NAME =

                                       @"Select 
                                           max(tmp.@columnname) as MaxDate 
                                        from 
                                           @poconame tmp
                                        where 
                                           1=1 
                                           and tmp.OUID = @ouid 
                                           and tmp.BIZTRANSACTIONTYPEID = @biztransactiontypeid" + "\r\n";

        public const string GET_BIZTRANSACTIONTYPE_BASED_ON_ID_SQL = @"
                SELECT
                    BIZTRANSACTIONTYPEID             AS BIZTransactionTypeId,
                    BIZTRANSACTIONCLASSID            AS BIZTransactionTypeClassId,
                    ISNULL(BACKDATECHECKTYPE,  0)    AS BIZTransactionTypeBackDateCheckType,
                    ISNULL(BACKDATEENTRYDAY,   0)    AS BIZTransactionTypeBackDateEntryDay,
                    ISNULL(BACKDATEUPDATEDAY,  0)    AS BIZTransactionTypeBackDateUpdateDay,
                    ISNULL(BACKDATEDELETEDAY,  0)    AS BIZTransactionTypeBackDateDeleteDay,
                    ISNULL(FORWARDENTRYDAY,    0)    AS BIZTransactionTypeForwardEntryDay
                FROM MBIZTRANSACTIONTYPE
                WHERE BIZTRANSACTIONTYPEID = @biztransactiontypeid" + "\r\n";

        public const string GET_BIZTRANSACTIONTYPE_BASED_ON_ID_PG = @"
                SELECT
                    biztransactiontypeid             AS ""BIZTransactionTypeId"",
                    biztransactionclassid            AS ""BIZTransactionTypeClassId"",
                    COALESCE(backdatechecktype,  0)  AS ""BIZTransactionTypeBackDateCheckType"",
                    COALESCE(backdateentryday,   0)  AS ""BIZTransactionTypeBackDateEntryDay"",
                    COALESCE(backdateupdateday,  0)  AS ""BIZTransactionTypeBackDateUpdateDay"",
                    COALESCE(backdatedeleteday,  0)  AS ""BIZTransactionTypeBackDateDeleteDay"",
                    COALESCE(forwardentryday,    0)  AS ""BIZTransactionTypeForwardEntryDay""
                FROM mbiztransactiontype
                WHERE biztransactiontypeid = @biztransactiontypeid" + "\r\n";

        public const string GET_BIZTRANSACTION_TYPE_CLASS_SUBCLASS_SQL = @"SELECT 
                                   t_biztransaction.BIZTRANSACTIONID AS BizTransactionId,
                                   t_biztransaction.BIZTRANSACTIONCODE AS BizTransactionCode,
                                   t_biztransaction.BIZTRANSACTIONNAME AS BizTransactionName,
   
                                   t_biztransactiontype.BIZTRANSACTIONTYPEID AS BizTransactionTypeId,
                                   t_biztransactiontype.BIZTRANSACTIONTYPECODE AS BizTransactionTypeCode,
                                   t_biztransactiontype.BIZTRANSACTIONTYPENAME AS BizTransactionTypeName,
   
                                   t_subclass.BIZTRANSACTIONSUBCLASSID AS BizTransactionSubClassId,
                                   t_subclass.BIZTRANSACTIONSUBCLASSCODE AS BizTransactionSubClassCode,
                                   t_subclass.BIZTRANSACTIONSUBCLASSNAME AS BizTransactionSubClassName,
   
                                   t_class.BIZTRANSACTIONCLASSID AS BizTransactionClassId,
                                   t_class.BIZTRANSACTIONCLASSCODE AS BizTransactionClassCode,
                                   t_class.BIZTRANSACTIONCLASSNAME AS BizTransactionClassName

                                FROM 
                                   MBIZTRANSACTIONTYPE t_biztransactiontype
                                   INNER JOIN MBIZTRANSACTION t_biztransaction
                                       ON t_biztransactiontype.BIZTRANSACTIONID = t_biztransaction.BIZTRANSACTIONID
                                   INNER JOIN MBIZTRANSACTIONCLASS t_class
                                       ON t_biztransactiontype.BIZTRANSACTIONCLASSID = t_class.BIZTRANSACTIONCLASSID
                                   INNER JOIN MBIZTRANSACTIONSUBCLASS t_subclass
                                       ON t_biztransactiontype.BIZTRANSACTIONSUBCLASSID = t_subclass.BIZTRANSACTIONSUBCLASSID

                                WHERE
                                   t_biztransactiontype.BIZTRANSACTIONTYPEID = @biztransactiontypeid";

        public const string GET_BIZTRANSACTION_TYPE_CLASS_SUBCLASS_PG = @"
                        SELECT 
                            t_biztransaction.BIZTRANSACTIONID        AS ""BizTransactionId"",
                            t_biztransaction.BIZTRANSACTIONCODE      AS ""BizTransactionCode"",
                            t_biztransaction.BIZTRANSACTIONNAME      AS ""BizTransactionName"",

                            t_biztransactiontype.BIZTRANSACTIONTYPEID   AS ""BizTransactionTypeId"",
                            t_biztransactiontype.BIZTRANSACTIONTYPECODE AS ""BizTransactionTypeCode"",
                            t_biztransactiontype.BIZTRANSACTIONTYPENAME AS ""BizTransactionTypeName"",

                            t_subclass.BIZTRANSACTIONSUBCLASSID     AS ""BizTransactionSubClassId"",
                            t_subclass.BIZTRANSACTIONSUBCLASSCODE   AS ""BizTransactionSubClassCode"",
                            t_subclass.BIZTRANSACTIONSUBCLASSNAME   AS ""BizTransactionSubClassName"",

                            t_class.BIZTRANSACTIONCLASSID           AS ""BizTransactionClassId"",
                            t_class.BIZTRANSACTIONCLASSCODE         AS ""BizTransactionClassCode"",
                            t_class.BIZTRANSACTIONCLASSNAME         AS ""BizTransactionClassName""

                        FROM MBIZTRANSACTIONTYPE t_biztransactiontype
                        INNER JOIN MBIZTRANSACTION t_biztransaction
                            ON t_biztransactiontype.BIZTRANSACTIONID = t_biztransaction.BIZTRANSACTIONID
                        INNER JOIN MBIZTRANSACTIONCLASS t_class
                            ON t_biztransactiontype.BIZTRANSACTIONCLASSID = t_class.BIZTRANSACTIONCLASSID
                        INNER JOIN MBIZTRANSACTIONSUBCLASS t_subclass
                            ON t_biztransactiontype.BIZTRANSACTIONSUBCLASSID = t_subclass.BIZTRANSACTIONSUBCLASSID

                        WHERE t_biztransactiontype.BIZTRANSACTIONTYPEID = @biztransactiontypeid
                        ";


        public const string GET_LOCKSETTING_BASED_ON_BIZTRANSACTIONCLASS_OU_ID =
                                  @"SELECT
                                        ls.LOCKSETTINGID            AS LockSettingId,

                                        ls.OUID                     AS OUId,
                                        ou.ORGANIZATIONUNITCODE     AS OUCode,
                                        ou.ORGANIZATIONUNITNAME     AS OUName,

                                        ls.BIZTRANSACTIONCLASSID    AS BIZTransactionClassId,

                                        ls.LOCKID                   AS LockId,
                                        ls.MONTHLOCKID              AS MonthLockId,

                                        ls.BIZTRANSACTIONID         AS BIZTransactionId,
                                        bt.BIZTRANSACTIONCODE       AS BIZTransactionCode,
                                        bt.BIZTRANSACTIONNAME       AS BIZTransactionName

                                    FROM
                                        MLOCKSETTING ls
                                        INNER JOIN MBIZTRANSACTION bt
                                            ON ls.BIZTRANSACTIONID = bt.BIZTRANSACTIONID
                                        INNER JOIN MORGANIZATIONUNIT ou
                                            ON ls.OUID = ou.OUID
                                    WHERE
                                        ls.BIZTRANSACTIONID = @biztransactionid
                                        AND ls.OUID = @ouid
                                    ";
        public const string GET_DAYLOCK_BASED_ON_LOCK_OU_PERIOD_ID =
                                   @"SELECT
                                        dl.DAYLOCKID                 AS DayLockId, 
                                        dl.OUID                      AS OUId,
                                        ou.ORGANIZATIONUNITCODE      AS OUCode,
                                        ou.ORGANIZATIONUNITNAME      AS OUName, 
                                        dl.PERIODID                  AS PeriodId,
                                        p.PERIODCODE                 AS PeriodCode,
                                        p.PERIODNAME                 AS PeriodName, 
                                        dl.LOCKID                    AS LockId,
                                        l.LOCKCODE                   AS LockCode,
                                        l.LOCKNAME                   AS LockName, 
                                        dl.OPENDATE                  AS DayLockOpenDate,
                                        dl.CLOSEDATE                 AS DayLockCloseDate,
                                        dl.OPENMONTHDATE             AS DayLockOpenMonthDate,
                                        dl.CLOSEMONTHDATE            AS DayLockCloseMonthDate 
                                    FROM
                                        TDAYLOCK dl
                                        INNER JOIN MORGANIZATIONUNIT ou
                                            ON dl.OUID = ou.OUID
                                        INNER JOIN MPERIOD p
                                            ON dl.PERIODID = p.PERIODID
                                        INNER JOIN MLOCK l
                                            ON dl.LOCKID = l.LOCKID
                                    WHERE
                                        dl.LOCKID = @lockid
                                        AND dl.OUID = @ouid
                                        AND dl.PERIODID = @periodid
                                   ";

        public const string GET_ENTITYID_BASED_ON_BIZTRANSACTIONCLASS =
                                    @"SELECT biz.ENTITYID AS EntityId,
                                    ME.ENTITYCODE AS EntityCode,
                                    ME.ENTITYNAME  AS EntityName
                                    FROM MBIZTRANSACTIONCLASS biz , MENTITY ME
                                    WHERE 
                                    1=1
                                    AND biz.ENTITYID = ME.ENTITYID
                                    AND BIZTRANSACTIONCLASSID  =@biztransactiontypeclassid";

        public const string GET_OVER_ALL_LOCK_DATE_PG = @"select OVERLOCKDATE as ""OverLockDate"" from MORGANIZATIONUNIT where OUID = @ouid";

        public const string GET_OVER_ALL_LOCK_DATE_SQL = @"select OVERLOCKDATE as OverLockDate from MORGANIZATIONUNIT where OUID = @ouid";

        public const string GET_Entity = @"select 
                                          E.entityid as EntityId,
                                          E.entitycode as EntityCode,
                                          E.entityname as EntityName 
                                        from MENTITY E where ENTITYID = @entityid";

        public const string GET_BIZTRANSACTION = @"SELECT
                                                BT.BIZTRANSACTIONID AS BIZTransactionId,
                                                BT.BIZTRANSACTIONCODE AS BIZTransactionCode,
                                                BT.BIZTRANSACTIONNAME AS BIZTransactionName,

                                                BT.BIZTRANSACTIONSUBCLASSID AS BIZTransactionSubClassId,
                                                BTSUBCLASS.BIZTRANSACTIONSUBCLASSCODE AS BIZTransactionSubClassCode,
                                                BTSUBCLASS.BIZTRANSACTIONSUBCLASSNAME AS BIZTransactionSubClassName,

                                                BT.PROCESSID AS ProcessId,
                                                PRC.PROCESSCODE AS ProcessCode,
                                                PRC.PROCESSNAME AS ProcessName,

                                                BT.CREATEDBYID AS BIZTransactionCreatedById,
                                                BT.CREATEDON AS BIZTransactionCreatedOn,
                                                BT.MODIFIEDBYID AS BIZTransactionModifiedById,
                                                BT.MODIFIEDON AS BIZTransactionModifiedOn,

                                                CBY.USERNAME AS BIZTransactionCreatedByName,
                                                MBY.USERNAME AS BIZTransactionModifiedByName,
                                                BT.SOURCETYPE AS SourceType,
                                                BT.TENANTID AS TenantId,

                                                BT.SORTORDER AS BIZTransactionSortOrder,
                                                BT.STATUS AS BIZTransactionStatus,
                                                BT.VERSION AS BIZTransactionVersion,

                                                BT.FINANCEBOOKID AS FinanceBookId,
                                                FB.FINANCEBOOKNAME AS FinanceBookName

                                            FROM MBIZTRANSACTION BT
                                            LEFT JOIN MBIZTRANSACTIONSUBCLASS BTSUBCLASS ON BTSUBCLASS.BIZTRANSACTIONSUBCLASSID = BT.BIZTRANSACTIONSUBCLASSID
                                            LEFT JOIN MPROCESS PRC ON PRC.PROCESSID = BT.PROCESSID
                                            LEFT JOIN MUSER CBY ON CBY.USERID = BT.CREATEDBYID
                                            LEFT JOIN MUSER MBY ON MBY.USERID = BT.MODIFIEDBYID
                                            LEFT JOIN MFINANCEBOOK FB ON FB.FINANCEBOOKID = BT.FINANCEBOOKID
                                            WHERE
                                            BT.BIZTRANSACTIONID = @biztransactionid;";

        public const string GET_SELECTLIST_BIZTRANSACTION = @"
            WITH BIZTransaction AS (
                SELECT A.BIZTRANSACTIONID          AS Id,
                       A.BIZTRANSACTIONCODE         AS Code,
                       A.BIZTRANSACTIONNAME         AS Name,
                       A.BIZTRANSACTIONSUBCLASSID   AS BIZTransactionSubClassId,
                       BTC.MODULEID                 AS ModuleId,
                       ROW_NUMBER() OVER (ORDER BY A.BIZTRANSACTIONID) AS RowNum
                FROM MBIZTRANSACTION A
                LEFT JOIN MBIZTRANSACTIONSUBCLASS BTSUBCLASS ON BTSUBCLASS.BIZTRANSACTIONSUBCLASSID = A.BIZTRANSACTIONSUBCLASSID
                LEFT JOIN MBIZTRANSACTIONCLASS BTC ON BTC.BIZTRANSACTIONCLASSID = BTSUBCLASS.BIZTRANSACTIONCLASSID
                WHERE A.STATUS = 1 /*CRITERIA*/
            )
            SELECT Id, Code, Name
            FROM   BIZTransaction
            WHERE  (@firstnumber = -1 AND @maxresult = -1)
               OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_BIZTRANSACTION_PG = @"
            WITH BIZTransaction AS (
                SELECT a.biztransactionid          AS ""Id"",
                       a.biztransactioncode         AS ""Code"",
                       a.biztransactionname         AS ""Name"",
                       a.biztransactionsubclassid   AS ""BIZTransactionSubClassId"",
                       btc.moduleid                 AS ""ModuleId"",
                       ROW_NUMBER() OVER (ORDER BY a.biztransactionid) AS ""RowNum""
                FROM mbiztransaction a
                LEFT JOIN mbiztransactionsubclass btsubclass ON btsubclass.biztransactionsubclassid = a.biztransactionsubclassid
                LEFT JOIN mbiztransactionclass btc ON btc.biztransactionclassid = btsubclass.biztransactionclassid
                WHERE a.status = 1 /*CRITERIA*/
            )
            SELECT ""Id"", ""Code"", ""Name""
            FROM   BIZTransaction
            WHERE  (:firstnumber = -1 AND :maxresult = -1)
               OR  (""RowNum"" BETWEEN :firstnumber AND :maxresult);";

        public const string GET_SELECTLIST_BIZTRANSACTION_COUNT = @"
            SELECT COUNT(*)
            FROM   MBIZTRANSACTION A
            WHERE  A.STATUS = 1;";

        public const string PG_GET_SELECTLIST_BIZTRANSACTION_COUNT = @"
            SELECT COUNT(*)
            FROM   mbiztransaction a
            WHERE  a.status = 1;";

        // ──────────────────────────────────────────────
        // BIZTransaction Detail List (with pagination)
        // Mirrors GB4 GetBIZTransactionDetail
        // ──────────────────────────────────────────────
        public const string GET_BIZTRANSACTION_DETAIL = @"
            WITH BIZTransactionDetail AS (
                SELECT
                    BT.BIZTRANSACTIONID              AS BIZTransactionId,
                    BT.BIZTRANSACTIONCODE            AS BIZTransactionCode,
                    BT.BIZTRANSACTIONNAME            AS BIZTransactionName,
                    BT.BIZTRANSACTIONSUBCLASSID      AS BIZTransactionSubClassId,
                    BTSC.BIZTRANSACTIONSUBCLASSCODE  AS BIZTransactionSubClassCode,
                    BTSC.BIZTRANSACTIONSUBCLASSNAME  AS BIZTransactionSubClassName,
                    BTC.BIZTRANSACTIONCLASSID        AS BIZTransactionClassId,
                    BTC.BIZTRANSACTIONCLASSCODE      AS BIZTransactionClassCode,
                    BTC.BIZTRANSACTIONCLASSNAME      AS BIZTransactionClassName,
                    MO.MODULEID                      AS ModuleId,
                    MO.MODULECODE                    AS ModuleCode,
                    MO.MODULENAME                    AS ModuleName,
                    ROW_NUMBER() OVER (ORDER BY BT.BIZTRANSACTIONID) AS RowNum
                FROM MBIZTRANSACTION BT
                LEFT JOIN MBIZTRANSACTIONSUBCLASS BTSC
                    ON BTSC.BIZTRANSACTIONSUBCLASSID = BT.BIZTRANSACTIONSUBCLASSID
                LEFT JOIN MBIZTRANSACTIONCLASS BTC
                    ON BTC.BIZTRANSACTIONCLASSID = BTSC.BIZTRANSACTIONCLASSID
                LEFT JOIN MMODULE MO
                    ON MO.MODULEID = BTC.MODULEID
                WHERE BT.STATUS = 1
            )
            SELECT BIZTransactionId, BIZTransactionCode, BIZTransactionName,
                   BIZTransactionSubClassId, BIZTransactionSubClassCode, BIZTransactionSubClassName,
                   BIZTransactionClassId, BIZTransactionClassCode, BIZTransactionClassName,
                   ModuleId, ModuleCode, ModuleName
            FROM   BIZTransactionDetail
            WHERE  (@firstnumber = -1 AND @maxresult = -1)
               OR  (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string PG_GET_BIZTRANSACTION_DETAIL = @"
            WITH BIZTransactionDetail AS (
                SELECT
                    bt.biztransactionid              AS ""BIZTransactionId"",
                    bt.biztransactioncode            AS ""BIZTransactionCode"",
                    bt.biztransactionname            AS ""BIZTransactionName"",
                    bt.biztransactionsubclassid      AS ""BIZTransactionSubClassId"",
                    btsc.biztransactionsubclasscode  AS ""BIZTransactionSubClassCode"",
                    btsc.biztransactionsubclassname  AS ""BIZTransactionSubClassName"",
                    btc.biztransactionclassid        AS ""BIZTransactionClassId"",
                    btc.biztransactionclasscode      AS ""BIZTransactionClassCode"",
                    btc.biztransactionclassname      AS ""BIZTransactionClassName"",
                    mo.moduleid                      AS ""ModuleId"",
                    mo.modulecode                    AS ""ModuleCode"",
                    mo.modulename                    AS ""ModuleName"",
                    ROW_NUMBER() OVER (ORDER BY bt.biztransactionid) AS ""RowNum""
                FROM mbiztransaction bt
                LEFT JOIN mbiztransactionsubclass btsc
                    ON btsc.biztransactionsubclassid = bt.biztransactionsubclassid
                LEFT JOIN mbiztransactionclass btc
                    ON btc.biztransactionclassid = btsc.biztransactionclassid
                LEFT JOIN mmodule mo
                    ON mo.moduleid = btc.moduleid
                WHERE bt.status = 1
            )
            SELECT ""BIZTransactionId"", ""BIZTransactionCode"", ""BIZTransactionName"",
                   ""BIZTransactionSubClassId"", ""BIZTransactionSubClassCode"", ""BIZTransactionSubClassName"",
                   ""BIZTransactionClassId"", ""BIZTransactionClassCode"", ""BIZTransactionClassName"",
                   ""ModuleId"", ""ModuleCode"", ""ModuleName""
            FROM   BIZTransactionDetail
            WHERE  (:firstnumber = -1 AND :maxresult = -1)
               OR  (""RowNum"" BETWEEN :firstnumber AND :maxresult);";

        public const string GET_BIZTRANSACTION_DETAIL_COUNT = @"
            SELECT COUNT(*)
            FROM   MBIZTRANSACTION BT
            WHERE  BT.STATUS = 1;";

        public const string PG_GET_BIZTRANSACTION_DETAIL_COUNT = @"
            SELECT COUNT(*)
            FROM   mbiztransaction bt
            WHERE  bt.status = 1;";


        public const string SAVE_BIZTRANSACTION = @"INSERT INTO MBIZTRANSACTION
                                                    (
                                                        BIZTRANSACTIONID,
                                                        BIZTRANSACTIONCODE,
                                                        BIZTRANSACTIONNAME,
                                                        BIZTRANSACTIONSUBCLASSID,
                                                        PROCESSID,
                                                        SORTORDER,
                                                        STATUS,
                                                        VERSION,
                                                        CREATEDBYID,
                                                        CREATEDON,
                                                        MODIFIEDBYID,
                                                        MODIFIEDON,
                                                        FINANCEBOOKID
                                                    )
                                                    VALUES
                                                    (
                                                        @BIZTransactionId,
                                                        @BIZTransactionCode,
                                                        @BIZTransactionName,
                                                        @BIZTransactionSubClassId,
                                                        @ProcessId,
                                                        @BIZTransactionSortOrder,
                                                        @BIZTransactionStatus,
                                                        @BIZTransactionVersion,
                                                        @BIZTransactionCreatedById,
                                                        @BIZTransactionCreatedOn,
                                                        @BIZTransactionModifiedById,
                                                        @BIZTransactionModifiedOn,
                                                        @FinanceBookId
                                                    );";

        public const string UPDATE_BIZTRANSACTION = @"UPDATE MBIZTRANSACTION
                                                    SET
                                                        BIZTRANSACTIONCODE       = @BIZTransactionCode,
                                                        BIZTRANSACTIONNAME       = @BIZTransactionName,
                                                        BIZTRANSACTIONSUBCLASSID = @BIZTransactionSubClassId,
                                                        PROCESSID                = @ProcessId,
                                                        SORTORDER                = @BIZTransactionSortOrder,
                                                        STATUS                   = @BIZTransactionStatus,
                                                        VERSION                  = @BIZTransactionVersion,
                                                        MODIFIEDBYID             = @BIZTransactionModifiedById,
                                                        MODIFIEDON               = @BIZTransactionModifiedOn,
                                                        FINANCEBOOKID            = @FinanceBookId
                                                    WHERE
                                                        BIZTRANSACTIONID = @BIZTransactionId;";


        public const string DELETE_BIZTRANSACTION = @"DELETE FROM MBIZTRANSACTION
                                                    WHERE BIZTRANSACTIONID = @biztransactionid;";
    }
}