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

namespace FrameworkDAL.Query.GBPeriod
{
    public static class GBPeriodQB
    {
        public const string GET_GBPERIOD = @"  SELECT
                                            -- Parent: GBPeriodDTO
                                            MG.GBPERIODID               AS GBPeriodId,
                                            MG.GBPERIODCODE             AS GBPeriodCode,
                                            MG.GBPERIODNAME             AS GBPeriodName,
                                            MG.GBPERIODFROMDATE         AS GBPeriodGBPeriodFromDate,
                                            MG.GBPERIODTODATE           AS GBPeriodGBPeriodToDate,
                                            MG.PERIODTYPE               AS GBPeriodPeriodType,
                                            MG.SORTORDER                AS GBPeriodSortOrder,
                                            MG.STATUS                   AS GBPeriodStatus,
                                            MG.VERSION                  AS GBPeriodVersion,
                                            MG.SOURCETYPE               AS GBPeriodSourceType,
                                            MG.CREATEDBYID              AS GBPeriodCreatedById,
                                            MG.CREATEDON                AS GBPeriodCreatedOn,
                                            MG.MODIFIEDBYID             AS GBPeriodModifiedById,
                                            MG.MODIFIEDON               AS GBPeriodModifiedOn,

                                            -- Child: GBPeriodDetailDTO
                                            MD.GBPERIODDETAILID         AS GBPeriodDetailId,
                                            MD.GBPERIODID               AS GBPeriodId,
                                            MG.GBPERIODCODE             AS GBPeriodCode,
                                            MG.GBPERIODNAME             AS GBPeriodName,
                                            MD.SLNO                     AS GBPeriodDetailSlno,
                                            MD.SUBPERIODNAME            AS GBPeriodDetailSubPeriodName,
                                            MD.SUBPERIODFROMDATE        AS GBPeriodDetailSubPeriodFromDate,
                                            MD.SUBPERIODTODATE          AS GBPeriodDetailSubPeriodToDate

                                        FROM 
                                            MGBPERIOD MG
                                        LEFT JOIN 
                                            MGBPERIODDETAIL MD ON MD.GBPERIODID = MG.GBPERIODID
                                        WHERE 
                                            MG.GBPERIODID = @gbperiodid
                                        ORDER BY 
                                            MD.SLNO;";

        public const string SAVE_GBPERIOD = @"INSERT INTO MGBPERIOD (
                                                    GBPERIODID,
                                                    GBPERIODCODE,
                                                    GBPERIODNAME,
                                                    GBPERIODFROMDATE,
                                                    GBPERIODTODATE,
                                                    PERIODTYPE,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    SOURCETYPE,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON
                                                )
                                                VALUES (
                                                    @GBPeriodId,
                                                    @GBPeriodCode,
                                                    @GBPeriodName,
                                                    @GBPeriodGBPeriodFromDate,
                                                    @GBPeriodGBPeriodToDate,
                                                    @GBPeriodPeriodType,
                                                    @GBPeriodSortOrder,
                                                    @GBPeriodStatus,
                                                    @GBPeriodVersion,
                                                    @GBPeriodSourceType,
                                                    @GBPeriodCreatedById,
                                                    @GBPeriodCreatedOn,
                                                    @GBPeriodModifiedById,
                                                    @GBPeriodModifiedOn
                                                );";


        public const string UPDATE_GBPERIOD = @"UPDATE MGBPERIOD
                                                SET
                                                    GBPERIODCODE        = @GBPeriodCode,
                                                    GBPERIODNAME        = @GBPeriodName,
                                                    GBPERIODFROMDATE    = @GBPeriodGBPeriodFromDate,
                                                    GBPERIODTODATE      = @GBPeriodGBPeriodToDate,
                                                    PERIODTYPE          = @GBPeriodPeriodType,
                                                    SORTORDER           = @GBPeriodSortOrder,
                                                    STATUS              = @GBPeriodStatus,
                                                    VERSION             = @GBPeriodVersion,
                                                    SOURCETYPE          = @GBPeriodSourceType,
                                                    MODIFIEDBYID        = @GBPeriodModifiedById,
                                                    MODIFIEDON          = @GBPeriodModifiedOn
                                                WHERE GBPERIODID = @GBPeriodId;";

        public const string DELETE_GBPERIOD = @"DELETE FROM MGBPERIOD
                                                WHERE GBPERIODID = @GBPeriodId;";

        public const string GET_SELECTLIST_GBPERIOD = @"WITH PagedGBPeriod AS (
                                                            SELECT
                                                                GP.GBPERIODID       AS GBPeriodId,
                                                                GP.GBPERIODCODE     AS GBPeriodCode,
                                                                GP.GBPERIODNAME     AS GBPeriodName,
                                                                GP.PERIODTYPE       AS GBPeriodPeriodType,
                                                                ROW_NUMBER() OVER (ORDER BY GP.GBPERIODID) AS RowNum
                                                            FROM MGBPERIOD GP
                                                            WHERE GP.STATUS = 1
                                                        )
                                                        SELECT
                                                            GBPeriodId,
                                                            GBPeriodCode,
                                                            GBPeriodName,
                                                            GBPeriodPeriodType
                                                        FROM PagedGBPeriod
                                                        WHERE RowNum BETWEEN (@firstnumber + 1) AND (@firstnumber + @maxresult);
                                                        ";

        public const string GET_SELECTLIST_GBPERIODDETAIL = @"WITH PagedGBPeriodDetail AS (
                                                            SELECT
                                                                MD.GBPERIODDETAILID    AS Id,
                                                                MD.SUBPERIODNAME       AS SubPeriodName,
                                                                MD.SUBPERIODFROMDATE   AS SubPeriodFromDate,
                                                                MD.SUBPERIODTODATE     AS SubPeriodToDate,
                                                                ROW_NUMBER() OVER (ORDER BY MD.GBPERIODDETAILID) AS RowNum
                                                            FROM MGBPERIODDETAIL MD
                                                        )
                                                        SELECT
                                                            Id,
                                                            SubPeriodName,
                                                            SubPeriodFromDate,
                                                            SubPeriodToDate
                                                        FROM PagedGBPeriodDetail
                                                        WHERE RowNum BETWEEN (@firstnumber + 1) AND (@firstnumber + @maxresult);
                                                        ";

        public const string GET_GB4PERIOD_PICKLIST = @"SELECT
                                                            MD.GBPERIODDETAILID    AS Id,
                                                            MD.SUBPERIODNAME       AS SubPeriodName,
                                                            MD.SUBPERIODFROMDATE   AS SubPeriodFromDate,
                                                            MD.SUBPERIODTODATE     AS SubPeriodToDate
                                                        FROM MGBPERIOD MG
                                                        INNER JOIN MGBPERIODDETAIL MD ON MD.GBPERIODID = MG.GBPERIODID
                                                        WHERE 1 = 1";
    }
}