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

namespace PayRollDAL.Query.TDSChallan
{
    // Indexes recommended:
    //   TTDSCHALLAN: IX_TTDSCHALLAN_ID (TDSCHALLANID)
    public static class TDSChallanQB
    {
        // ── Picklist (paged) — inner SELECT, criteria filter appended by DAL before closing ──

        public const string GET_SELECTLIST_TDSCHALLAN_INNER_SQL = @"
            SELECT
                a.TDSCHALLANID     AS Id,
                a.CHALLANNUMBER    AS ChallanNumber,
                CASE
                    WHEN a.FORMTYPE = 0 THEN '24Q'
                    WHEN a.FORMTYPE = 1 THEN '26Q'
                    WHEN a.FORMTYPE = 2 THEN '27Q'
                    ELSE '27EQ'
                END                AS FormType,
                a.CHALLANDATE      AS ChallanDate,
                a.REFERENCENUMBER  AS ReferenceNumber,
                a.REFERENCEDATE    AS ReferenceDate,
                ROW_NUMBER() OVER (ORDER BY a.TDSCHALLANID DESC) AS RowNum
            FROM TTDSCHALLAN a
            WHERE 1 = 1";

        public const string GET_SELECTLIST_TDSCHALLAN_OUTER_SQL = @"
            SELECT Id, ChallanNumber, FormType, ChallanDate, ReferenceNumber, ReferenceDate
            FROM PagedTDSChallan
            WHERE (@firstnumber = -1 AND @maxresult = -1)
               OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string GET_SELECTLIST_TDSCHALLAN_INNER_PG = @"
            SELECT
                a.TDSCHALLANID     AS ""Id"",
                a.CHALLANNUMBER    AS ""ChallanNumber"",
                CASE
                    WHEN a.FORMTYPE = 0 THEN '24Q'
                    WHEN a.FORMTYPE = 1 THEN '26Q'
                    WHEN a.FORMTYPE = 2 THEN '27Q'
                    ELSE '27EQ'
                END                AS ""FormType"",
                a.CHALLANDATE      AS ""ChallanDate"",
                a.REFERENCENUMBER  AS ""ReferenceNumber"",
                a.REFERENCEDATE    AS ""ReferenceDate"",
                ROW_NUMBER() OVER (ORDER BY a.TDSCHALLANID DESC) AS ""RowNum""
            FROM TTDSCHALLAN a
            WHERE 1 = 1";

        public const string GET_SELECTLIST_TDSCHALLAN_OUTER_PG = @"
            SELECT ""Id"", ""ChallanNumber"", ""FormType"", ""ChallanDate"", ""ReferenceNumber"", ""ReferenceDate""
            FROM PagedTDSChallan
            WHERE (:firstnumber = -1 AND :maxresult = -1)
               OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);";
    }
}