using System;

namespace CMSDAL.Query.ContentCategory
{
    public static class ContentCategoryQB
    {
        public const string GET_CONTENT_CATEGORY = @"SELECT
                                                        CC.CONTENTCATEGORYID              AS ContentCategoryId,
                                                        CC.CONTENTCATEGORYCODE            AS ContentCategoryCode,
                                                        CC.CONTENTCATEGORYNAME            AS ContentCategoryName,
                                                        CC.PARENTCONTENTCATEGORYID        AS ParentContentCategoryId,
                                                        CC.SOURCETYPE                     AS SourceType,
                                                        CC.SORTORDER                      AS SortOrder,
                                                        CC.STATUS                         AS Status,
                                                        CC.VERSION                        AS Version,
                                                        CC.CREATEDBYID                    AS CreatedById,
                                                        CC.CREATEDON                      AS CreatedOn,
                                                        CC.MODIFIEDBYID                   AS ModifiedById,
                                                        CC.MODIFIEDON                     AS ModifiedOn,
                                                        CC.TENANTID                       AS TenantId
                                                    FROM MCONTENTCATEGORY CC
                                                    WHERE CC.CONTENTCATEGORYID = @ContentCategoryId;";


        public const string SAVE_CONTENT_CATEGORY = @"INSERT INTO MCONTENTCATEGORY
                                                    (
                                                        CONTENTCATEGORYCODE,
                                                        CONTENTCATEGORYNAME,
                                                        PARENTCONTENTCATEGORYID,
                                                        SOURCETYPE,
                                                        SORTORDER,
                                                        STATUS,
                                                        VERSION,
                                                        CREATEDBYID,
                                                        CREATEDON,
                                                        MODIFIEDBYID,
                                                        MODIFIEDON,
                                                        TENANTID
                                                    )
                                                    VALUES
                                                    (
                                                        @ContentCategoryCode,
                                                        @ContentCategoryName,
                                                        @ParentContentCategoryId,
                                                        @SourceType,
                                                        @SortOrder,
                                                        @Status,
                                                        @Version,
                                                        @CreatedById,
                                                        @CreatedOn,
                                                        @ModifiedById,
                                                        @ModifiedOn,
                                                        @TenantId
                                                    );";


        public const string UPDATE_CONTENT_CATEGORY = @"UPDATE MCONTENTCATEGORY
                                                       SET
                                                           CONTENTCATEGORYCODE = @ContentCategoryCode,
                                                           CONTENTCATEGORYNAME = @ContentCategoryName,
                                                           PARENTCONTENTCATEGORYID = @ParentContentCategoryId,
                                                           SOURCETYPE = @SourceType,
                                                           SORTORDER = @SortOrder,
                                                           STATUS = @Status,
                                                           VERSION = @Version,
                                                           MODIFIEDBYID = @ModifiedById,
                                                           MODIFIEDON = @ModifiedOn,
                                                           TENANTID = @TenantId
                                                       WHERE
                                                           CONTENTCATEGORYID = @ContentCategoryId;";


        public const string GET_SELECTLIST_CONTENT_CATEGORY = @"WITH ContentCategory AS (
                                                        SELECT
                                                            CC.CONTENTCATEGORYID              AS ContentCategoryId,
                                                            CC.CONTENTCATEGORYCODE            AS ContentCategoryCode,
                                                            CC.CONTENTCATEGORYNAME            AS ContentCategoryName,
                                                            CC.PARENTCONTENTCATEGORYID        AS ParentContentCategoryId,
                                                            CC.SOURCETYPE                     AS SourceType,
                                                            CC.SORTORDER                      AS SortOrder,
                                                            CC.STATUS                         AS Status,
                                                            CC.VERSION                        AS Version,
                                                            CC.CREATEDBYID                    AS CreatedById,
                                                            CC.CREATEDON                      AS CreatedOn,
                                                            CC.MODIFIEDBYID                   AS ModifiedById,
                                                            CC.MODIFIEDON                     AS ModifiedOn,
                                                            CC.TENANTID                       AS TenantId,
                                                            ROW_NUMBER() OVER (ORDER BY CC.CONTENTCATEGORYID) AS RowNum
                                                        FROM MCONTENTCATEGORY CC
                                                    )
                                                    SELECT 
                                                        ContentCategoryId,
                                                        ContentCategoryCode,
                                                        ContentCategoryName,
                                                        ParentContentCategoryId,
                                                        SourceType,
                                                        SortOrder,
                                                        Status,
                                                        Version,
                                                        CreatedById,
                                                        CreatedOn,
                                                        ModifiedById,
                                                        ModifiedOn,
                                                        TenantId
                                                    FROM ContentCategory
                                                    WHERE 
                                                        (@firstnumber = -1 AND @maxresult = -1)
                                                        OR (RowNum BETWEEN @firstnumber AND @maxresult);";


        public const string DELETE_CONTENT_CATEGORY = @"DELETE FROM MCONTENTCATEGORY
                                                       WHERE CONTENTCATEGORYID = @ContentCategoryId;";
    }
}