namespace AccountsDAL.Query.DeferalPlan
{
    public static class DeferalPlanQB
    {
        public const string GET_DEFERALPLAN = @"
            SELECT
                DP.DEFERALPLANID                  AS DeferalPlanId,
                DP.DEFERALPLANCODE                AS DeferalPlanCode,
                DP.DEFERALPLANNAME                AS DeferalPlanName,
                DP.REMARKS                        AS DeferalPlanRemarks,
                DP.DEFERALACCOUNTID               AS DeferalAccountId,
                A.ACCOUNTCODE                     AS DeferalAccountCode,
                A.ACCOUNTNAME                     AS DeferalAccountName,
                DP.DEFERALPERCENT                 AS DeferalPlanDeferalPercent,
                DP.PERIODTYPE                     AS DeferalPlanPeriodType,
                DP.PERIODS                        AS DeferalPlanPeriods,
                DP.CALCULATIONMETHOD              AS DeferalPlanCalculationMethod,
                DP.STARTDATETYPE                  AS DeferalPlanStartDateType,
                DP.PERIODREMARKSTEMPLATE          AS DeferalPlanPeriodRemarksTemplate,
                DP.DAYS                           AS DeferalPlanDays,
                DP.MONTHS                         AS DeferalPlanMonths,
                DP.SORTORDER                      AS DeferalPlanSortOrder,
                DP.STATUS                         AS DeferalPlanStatus,
                DP.VERSION                        AS DeferalPlanVersion,
                DP.SOURCETYPE                     AS DeferalPlanSourceType,
                DP.CREATEDBYID                    AS DeferalPlanCreatedById,
                DP.CREATEDON                      AS DeferalPlanCreatedOn,
                DP.MODIFIEDBYID                   AS DeferalPlanModifiedById,
                DP.MODIFIEDON                     AS DeferalPlanModifiedOn
            FROM   MDEFERALPLAN DP
            LEFT JOIN MACCOUNT A ON DP.DEFERALACCOUNTID = A.ACCOUNTID
            WHERE  DP.DEFERALPLANID = @DeferalPlanId;";

        public const string SAVE_DEFERALPLAN = @"
            INSERT INTO MDEFERALPLAN
            (
                DEFERALPLANID, DEFERALPLANCODE, DEFERALPLANNAME, REMARKS,
                DEFERALACCOUNTID, DEFERALPERCENT, PERIODTYPE, PERIODS,
                CALCULATIONMETHOD, STARTDATETYPE, PERIODREMARKSTEMPLATE,
                DAYS, MONTHS,
                SORTORDER, STATUS, VERSION, SOURCETYPE,
                CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
            )
            VALUES
            (
                @DeferalPlanId, @DeferalPlanCode, @DeferalPlanName, @DeferalPlanRemarks,
                @DeferalAccountId, @DeferalPlanDeferalPercent, @DeferalPlanPeriodType, @DeferalPlanPeriods,
                @DeferalPlanCalculationMethod, @DeferalPlanStartDateType, @DeferalPlanPeriodRemarksTemplate,
                @DeferalPlanDays, @DeferalPlanMonths,
                @DeferalPlanSortOrder, @DeferalPlanStatus, @DeferalPlanVersion, @DeferalPlanSourceType,
                @DeferalPlanCreatedById, @DeferalPlanCreatedOn, @DeferalPlanModifiedById, @DeferalPlanModifiedOn
            );";

        public const string UPDATE_DEFERALPLAN = @"
            UPDATE MDEFERALPLAN SET
                DEFERALPLANCODE           = @DeferalPlanCode,
                DEFERALPLANNAME           = @DeferalPlanName,
                REMARKS                   = @DeferalPlanRemarks,
                DEFERALACCOUNTID          = @DeferalAccountId,
                DEFERALPERCENT            = @DeferalPlanDeferalPercent,
                PERIODTYPE                = @DeferalPlanPeriodType,
                PERIODS                   = @DeferalPlanPeriods,
                CALCULATIONMETHOD         = @DeferalPlanCalculationMethod,
                STARTDATETYPE             = @DeferalPlanStartDateType,
                PERIODREMARKSTEMPLATE     = @DeferalPlanPeriodRemarksTemplate,
                DAYS                      = @DeferalPlanDays,
                MONTHS                    = @DeferalPlanMonths,
                SORTORDER                 = @DeferalPlanSortOrder,
                STATUS                    = @DeferalPlanStatus,
                VERSION                   = @DeferalPlanVersion,
                SOURCETYPE                = @DeferalPlanSourceType,
                MODIFIEDBYID              = @DeferalPlanModifiedById,
                MODIFIEDON                = @DeferalPlanModifiedOn
            WHERE DEFERALPLANID = @DeferalPlanId
           ;";

        public const string DELETE_DEFERALPLAN = @"
            DELETE FROM MDEFERALPLAN
            WHERE DEFERALPLANID = @DeferalPlanId;";

        public const string GET_SELECTLIST_DEFERALPLAN = @"
            SELECT
                DEFERALPLANID   AS Id,
                DEFERALPLANCODE AS Code,
                DEFERALPLANNAME AS Name
            FROM MDEFERALPLAN
            ORDER BY DEFERALPLANNAME;";
    }
}