namespace MMDAL.Query.PostingPosition
{
    public static class PostingPositionQB
    {
        public const string GET_SELECTLIST_POSTINGPOSITION_SQL = @"
            WITH PostingPositionSelectList AS (
                SELECT
                    post.POSTINGPOSITIONID  AS PostingId,
                    post.POSTINGNUMBER      AS PostingNumber,
                    post.POSTINGDATE        AS PostingDate,
                    post.EMPLOYEEID         AS EmployeeId,
                    emp.EMPLOYEECODE        AS EmployeeCode,
                    emp.EMPLOYEENAME        AS EmployeeName,
                    head.PARTYID            AS PartyId,
                    Party.PARTYNAME         AS PartyName,
                    head.PARTYBRANCHID      AS PartyBranchId,
                    partybranch.PARTYBRANCHNAME AS PartyBranchName,
                    head.REFERENCENUMBER    AS ReferenceNumber,
                    post.FROMDATE           AS FromDate,
                    post.TODATE             AS ToDate,
                    postingtype.GCMNAME     AS PostingTypeName,
                    ROW_NUMBER() OVER (ORDER BY post.POSTINGPOSITIONID DESC) AS RowNum
                FROM TPOSTINGPOSITION post, MEMPLOYEE emp, TMMDETAIL detail, TMMHEAD head, MPARTY Party, MPARTYBRANCH partybranch, MGCM postingtype
                WHERE post.EMPLOYEEID = emp.EMPLOYEEID
                  AND post.MMDETAILID = detail.DOCUMENTDETAILID
                  AND detail.DOCUMENTID = head.DOCUMENTID
                  AND head.PARTYID = Party.PARTYID
                  AND head.PARTYBRANCHID = partybranch.PARTYBRANCHID
                  AND post.POSTINGTYPEID = postingtype.GCMID
            )
            SELECT
                PostingId,
                PostingNumber,
                PostingDate,
                EmployeeId,
                EmployeeCode,
                EmployeeName,
                PartyId,
                PartyName,
                PartyBranchId,
                PartyBranchName,
                ReferenceNumber,
                FromDate,
                ToDate,
                PostingTypeName
            FROM PostingPositionSelectList
            WHERE
                  (@firstnumber = -1 AND @maxresult = -1)
               OR (RowNum BETWEEN @firstnumber AND @maxresult);
            ";

        public const string GET_SELECTLIST_POSTINGPOSITION_PG = @"
            WITH ""PostingPositionSelectList"" AS (
                SELECT
                    post.POSTINGPOSITIONID  AS ""PostingId"",
                    post.POSTINGNUMBER      AS ""PostingNumber"",
                    post.POSTINGDATE        AS ""PostingDate"",
                    post.EMPLOYEEID         AS ""EmployeeId"",
                    emp.EMPLOYEECODE        AS ""EmployeeCode"",
                    emp.EMPLOYEENAME        AS ""EmployeeName"",
                    head.PARTYID            AS ""PartyId"",
                    Party.PARTYNAME         AS ""PartyName"",
                    head.PARTYBRANCHID      AS ""PartyBranchId"",
                    partybranch.PARTYBRANCHNAME AS ""PartyBranchName"",
                    head.REFERENCENUMBER    AS ""ReferenceNumber"",
                    post.FROMDATE           AS ""FromDate"",
                    post.TODATE             AS ""ToDate"",
                    postingtype.GCMNAME     AS ""PostingTypeName"",
                    ROW_NUMBER() OVER (ORDER BY post.POSTINGPOSITIONID DESC) AS ""RowNum""
                FROM TPOSTINGPOSITION post, MEMPLOYEE emp, TMMDETAIL detail, TMMHEAD head, MPARTY Party, MPARTYBRANCH partybranch, MGCM postingtype
                WHERE post.EMPLOYEEID = emp.EMPLOYEEID
                  AND post.MMDETAILID = detail.DOCUMENTDETAILID
                  AND detail.DOCUMENTID = head.DOCUMENTID
                  AND head.PARTYID = Party.PARTYID
                  AND head.PARTYBRANCHID = partybranch.PARTYBRANCHID
                  AND post.POSTINGTYPEID = postingtype.GCMID
            )
            SELECT
                ""PostingId"",
                ""PostingNumber"",
                ""PostingDate"",
                ""EmployeeId"",
                ""EmployeeCode"",
                ""EmployeeName"",
                ""PartyId"",
                ""PartyName"",
                ""PartyBranchId"",
                ""PartyBranchName"",
                ""ReferenceNumber"",
                ""FromDate"",
                ""ToDate"",
                ""PostingTypeName""
            FROM ""PostingPositionSelectList""
            WHERE
                  (:firstnumber = -1 AND :maxresult = -1)
               OR (""RowNum"" BETWEEN :firstnumber AND :maxresult);
            ";

        public const string GET_SELECTLIST_POSTINGPOSITION_ORACLE = @";";
        public const string GET_SELECTLIST_POSTINGPOSITION_MYSQL = @";";
    }
}