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

namespace AdminDAL.Query.Period
{
    public static class PeriodQB
    {
        public const string GET_SELECTLIST_PERIOD = @"SELECT
                                            PERIODID AS Id,
                                            PERIODCODE AS Code,
                                            PERIODNAME AS Name,
                                            FROMDATE AS FromDate, 
                                            TODATE AS ToDate
                                            FROM MPERIOD;";

        public const string GET_PERIOD = @"SELECT
                                            P.PERIODID             AS PeriodId,
                                            P.PERIODCODE           AS PeriodCode,
                                            P.PERIODNAME           AS PeriodName,
                                            P.FROMDATE             AS PeriodFromDate,
                                            P.TODATE               AS PeriodToDate,
                                            P.CREATEDBYID          AS PeriodCreatedById,
                                            P.CREATEDON            AS PeriodCreatedOn,
                                            CB.USERNAME            AS PeriodCreatedByName,
                                            P.MODIFIEDBYID         AS PeriodModifiedById,
                                            P.MODIFIEDON           AS PeriodModifiedOn,
                                            MB.USERNAME            AS PeriodModifiedByName,
                                            P.SORTORDER            AS PeriodSortOrder,
                                            P.STATUS               AS PeriodStatus,
                                            P.VERSION              AS PeriodVersion,
                                            P.PERIODGROUP          AS PeriodGroup
                                        FROM MPERIOD P
                                        LEFT JOIN MUSER CB ON P.CREATEDBYID = CB.USERID
                                        LEFT JOIN MUSER MB ON P.MODIFIEDBYID = MB.USERID
                                        WHERE P.PERIODID = @periodid
                                        ";

        public const string SAVE_PERIOD = @"INSERT INTO MPERIOD
(
    PERIODID,
    PERIODCODE,
    PERIODNAME,
    FROMDATE,
    TODATE,
    SORTORDER,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    PERIODGROUP
)
VALUES
(
    @PeriodId,
    @PeriodCode,
    @PeriodName,
    @PeriodFromDate,
    @PeriodToDate,
    @PeriodSortOrder,
    @PeriodStatus,
    @PeriodVersion,
    @PeriodCreatedById,
    @PeriodCreatedOn,
    @PeriodModifiedById,
    @PeriodModifiedOn,
    @PeriodGroup
);";

        public const string UPDATE_PERIOD = @"UPDATE MPERIOD
SET
    PERIODCODE       = @PeriodCode,
    PERIODNAME       = @PeriodName,
    FROMDATE         = @PeriodFromDate,
    TODATE           = @PeriodToDate,
    SORTORDER        = @PeriodSortOrder,
    STATUS           = @PeriodStatus,
    VERSION          = @PeriodVersion,
    MODIFIEDBYID     = @PeriodModifiedById,
    MODIFIEDON       = @PeriodModifiedOn,
    PERIODGROUP      = @PeriodGroup
WHERE
    PERIODID = @PeriodId;";

        public const string DELETE_PERIOD = @"DELETE FROM MPERIOD
                                WHERE PERIODID = @PeriodId;";
    }
}