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

namespace PayRollDAL.Query.CasteCategory
{
    public static class CasteCategoryQB
    {
        public const string GET_CASTECATEGORY = @"SELECT
    A.CASTECATEGORYID AS CasteCategoryId,
    A.CASTECATEGORYCODE AS CasteCategoryCode,
    A.CASTECATEGORYNAME AS CasteCategoryName,
    A.RESERVATIONPERCENTAGE AS CasteCategoryReservationPercentage,
    A.SOURCETYPE AS CasteCategorySourceType,
    A.SORTORDER AS CasteCategorySortOrder,
    A.STATUS AS CasteCategoryStatus,
    A.VERSION AS CasteCategoryVersion,
    A.CREATEDBYID AS CasteCategoryCreatedById,
    A.CREATEDON AS CasteCategoryCreatedOn,
    A.MODIFIEDBYID AS CasteCategoryModifiedById,
    A.MODIFIEDON AS CasteCategoryModifiedOn
FROM MCASTECATEGORY A
WHERE A.CASTECATEGORYID = @castecategoryid";

        public const string GET_CASTECATEGORYDETAIL = @"SELECT
    D.CASTECATEGORYDETAILID AS CasteCategoryDetailId,
    D.CASTECATEGORYID AS CasteCategoryId,
    D.SLNO AS CasteCategoryDetailSlNo,

    D.CASTEID AS CasteId,
    C.CASTECODE AS CasteCode,
    C.CASTENAME AS CasteName

FROM MCASTECATEGORYDETAIL D
LEFT JOIN MCASTE C ON D.CASTEID = C.CASTEID
WHERE D.CASTECATEGORYID = @castecategoryid
ORDER BY D.SLNO";

        public const string SAVE_CASTECATEGORY = @"INSERT INTO MCASTECATEGORY
(
    CASTECATEGORYID, CASTECATEGORYCODE, CASTECATEGORYNAME, RESERVATIONPERCENTAGE, SOURCETYPE,
    SORTORDER, STATUS, VERSION, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON
)
VALUES
(
    @CasteCategoryId, @CasteCategoryCode, @CasteCategoryName, @CasteCategoryReservationPercentage, @CasteCategorySourceType,
    @CasteCategorySortOrder, @CasteCategoryStatus, 1, @CasteCategoryCreatedById, @CasteCategoryCreatedOn, @CasteCategoryModifiedById, @CasteCategoryModifiedOn
);";

        public const string UPDATE_CASTECATEGORY = @"UPDATE MCASTECATEGORY
SET
    CASTECATEGORYCODE      = @CasteCategoryCode,
    CASTECATEGORYNAME      = @CasteCategoryName,
    RESERVATIONPERCENTAGE  = @CasteCategoryReservationPercentage,
    SOURCETYPE             = @CasteCategorySourceType,
    SORTORDER              = @CasteCategorySortOrder,
    STATUS                 = @CasteCategoryStatus,
    VERSION                = VERSION + 1,
    MODIFIEDBYID           = @CasteCategoryModifiedById,
    MODIFIEDON             = @CasteCategoryModifiedOn
WHERE CASTECATEGORYID = @CasteCategoryId;";

        public const string DELETE_CASTECATEGORY = @"DELETE FROM MCASTECATEGORY
                                            WHERE CASTECATEGORYID = @castecategoryid;";

        public const string SAVE_CASTECATEGORYDETAIL = @"INSERT INTO MCASTECATEGORYDETAIL
(
    CASTECATEGORYDETAILID, CASTECATEGORYID, SLNO, CASTEID
)
VALUES
(
    @CasteCategoryDetailId, @CasteCategoryId, @CasteCategoryDetailSlNo, @CasteId
);";

        public const string DELETE_CASTECATEGORYDETAIL = @"DELETE FROM MCASTECATEGORYDETAIL
                                            WHERE CASTECATEGORYID = @castecategoryid;";

        public const string GET_SELECTLIST_CASTECATEGORY = @"SELECT
    A.CASTECATEGORYID   AS Id,
    A.CASTECATEGORYCODE AS Code,
    A.CASTECATEGORYNAME AS Name
FROM MCASTECATEGORY A
WHERE 1 = 1";
    }
}