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

namespace AdminDAL.Query.LockSetting
{
    public static class LockSettingQB
    {
        // Indexes: MLOCKSETTING (OUID, BIZTRANSACTIONCLASSID)
        public const string GET_SELECTLIST_LOCKSETTING = @"
            SELECT
                ls.LOCKSETTINGID            AS LockSettingId,

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

                ls.BIZTRANSACTIONCLASSID    AS BIZTransactionClassId,
                btc.BIZTRANSACTIONCLASSCODE AS BIZTransactionClassCode,
                btc.BIZTRANSACTIONCLASSNAME AS BIZTransactionClassName,

                ls.LOCKID                   AS LockId,
                dl.LOCKCODE                 AS LockCode,
                dl.LOCKNAME                 AS LockName,

                ls.MONTHLOCKID              AS MonthLockId,
                ml.LOCKCODE                 AS MonthLockCode,
                ml.LOCKNAME                 AS MonthLockName,

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

            FROM
                MLOCKSETTING ls
                INNER JOIN MORGANIZATIONUNIT ou     ON ls.OUID = ou.OUID
                INNER JOIN MBIZTRANSACTION bt        ON ls.BIZTRANSACTIONID = bt.BIZTRANSACTIONID
                LEFT JOIN MBIZTRANSACTIONCLASS btc   ON ls.BIZTRANSACTIONCLASSID = btc.BIZTRANSACTIONCLASSID
                LEFT JOIN MLOCK dl                   ON ls.LOCKID = dl.LOCKID
                LEFT JOIN MLOCK ml                   ON ls.MONTHLOCKID = ml.LOCKID
            WHERE
                (@ouid = -1 OR ls.OUID = @ouid)
                AND (@biztransactionclassid = -1 OR ls.BIZTRANSACTIONCLASSID = @biztransactionclassid)" + "\r\n";

        public const string GET_SELECTLIST_LOCKSETTING_PG = @"
            SELECT
                ls.locksettingid            AS ""LockSettingId"",

                ls.ouid                     AS ""OUId"",
                ou.organizationunitcode      AS ""OUCode"",
                ou.organizationunitname      AS ""OUName"",

                ls.biztransactionclassid    AS ""BIZTransactionClassId"",
                btc.biztransactionclasscode AS ""BIZTransactionClassCode"",
                btc.biztransactionclassname AS ""BIZTransactionClassName"",

                ls.lockid                   AS ""LockId"",
                dl.lockcode                 AS ""LockCode"",
                dl.lockname                 AS ""LockName"",

                ls.monthlockid              AS ""MonthLockId"",
                ml.lockcode                 AS ""MonthLockCode"",
                ml.lockname                 AS ""MonthLockName"",

                ls.biztransactionid         AS ""BIZTransactionId"",
                bt.biztransactioncode       AS ""BIZTransactionCode"",
                bt.biztransactionname       AS ""BIZTransactionName""

            FROM
                mlocksetting ls
                INNER JOIN morganizationunit ou     ON ls.ouid = ou.ouid
                INNER JOIN mbiztransaction bt        ON ls.biztransactionid = bt.biztransactionid
                LEFT JOIN mbiztransactionclass btc  ON ls.biztransactionclassid = btc.biztransactionclassid
                LEFT JOIN mlock dl                  ON ls.lockid = dl.lockid
                LEFT JOIN mlock ml                  ON ls.monthlockid = ml.lockid
            WHERE
                (@ouid = -1 OR ls.ouid = @ouid)
                AND (@biztransactionclassid = -1 OR ls.biztransactionclassid = @biztransactionclassid)" + "\r\n";
    }
}