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

namespace AdminDAL.Query.DayLock
{
    public static class DayLockQB
    {
        // Ported from BIZTransactionQB.GET_DAYLOCK_BASED_ON_LOCK_OU_PERIOD_ID (proven join set),
        // generalized so OU/Lock/Period are each optional filters, matching the GB4
        // Criteria.svc/List/?ObjectCode=DAYLOCK payload shape (not every filter is always supplied).
        // Indexes: TDAYLOCK (OUID, LOCKID, PERIODID)
        public const string GET_SELECTLIST_DAYLOCK = @"
            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
                (@ouid = -1 OR dl.OUID = @ouid)
                AND (@lockid = -1 OR dl.LOCKID = @lockid)
                AND (@periodid = -1 OR dl.PERIODID = @periodid)" + "\r\n";

        public const string GET_SELECTLIST_DAYLOCK_PG = @"
            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
                (@ouid = -1 OR dl.ouid = @ouid)
                AND (@lockid = -1 OR dl.lockid = @lockid)
                AND (@periodid = -1 OR dl.periodid = @periodid)" + "\r\n";
    }
}