using System;

namespace DMSDAL.Query.Space
{
    public static class SpaceQB
    {
        public const string GET_SPACE = @"SELECT
                                            S.SPACEID              AS SpaceId,
                                            S.SPACECODE            AS SpaceCode,
                                            S.SPACENAME            AS SpaceName,
                                            S.SPACEDESCRIPTION     AS SpaceDescription,
                                            S.PARENTSAPCEID        AS ParentSapceId,
                                            S.PARENTSPACEIDS       AS ParentSpaceIds,
                                            S.SPACELEVEL           AS SpaceLevel,
                                            S.ISKB                 AS IsKb,
                                            S.ISVERSION            AS IsVersion,
                                            S.ISCHECKINOUT         AS IsCheckInOut,
                                            S.ISRSS                AS IsRss,
                                            S.ISFOLDERS            AS IsFolders,
                                            S.ISCOLLOBORATION      AS IsColloboration,
                                            S.ISBLOGS              AS IsBlogs,
                                            S.ISDISCUSSION         AS IsDiscussion,
                                            S.ISWIKI               AS IsWiki,
                                            S.SLUG                 AS Slug,
                                            S.ICON                 AS Icon,
                                            S.COLORID              AS ColorId,
                                            S.SETTINGS             AS Settings,
                                            S.SPACESTATUS          AS SpaceStatus,
                                            S.SORTORDER            AS SortOrder,
                                            S.STATUS               AS Status,
                                            S.VERSION              AS Version,
                                            S.SOURCETYPE           AS SourceType,
                                            S.CREATEDBYID          AS CreatedById,
                                            S.CREATEDON            AS CreatedOn,
                                            S.MODIFIEDBYID         AS ModifiedById,
                                            S.MODIFIEDON           AS ModifiedOn,
                                            S.TENANTID             AS TenantId
                                        FROM MSPACE S
                                        WHERE S.SPACEID = @SpaceId;";


        public const string SAVE_SPACE = @"INSERT INTO MSPACE
                                        (
                                            SPACECODE,
                                            SPACENAME,
                                            SPACEDESCRIPTION,
                                            PARENTSAPCEID,
                                            PARENTSPACEIDS,
                                            SPACELEVEL,
                                            ISKB,
                                            ISVERSION,
                                            ISCHECKINOUT,
                                            ISRSS,
                                            ISFOLDERS,
                                            ISCOLLOBORATION,
                                            ISBLOGS,
                                            ISDISCUSSION,
                                            ISWIKI,
                                            SLUG,
                                            ICON,
                                            COLORID,
                                            SETTINGS,
                                            SPACESTATUS,
                                            SORTORDER,
                                            STATUS,
                                            VERSION,
                                            SOURCETYPE,
                                            CREATEDBYID,
                                            CREATEDON,
                                            MODIFIEDBYID,
                                            MODIFIEDON,
                                            TENANTID
                                        )
                                        VALUES
                                        (
                                            @SpaceCode,
                                            @SpaceName,
                                            @SpaceDescription,
                                            @ParentSapceId,
                                            @ParentSpaceIds,
                                            @SpaceLevel,
                                            @IsKb,
                                            @IsVersion,
                                            @IsCheckInOut,
                                            @IsRss,
                                            @IsFolders,
                                            @IsColloboration,
                                            @IsBlogs,
                                            @IsDiscussion,
                                            @IsWiki,
                                            @Slug,
                                            @Icon,
                                            @ColorId,
                                            @Settings,
                                            @SpaceStatus,
                                            @SortOrder,
                                            @Status,
                                            @Version,
                                            @SourceType,
                                            @CreatedById,
                                            @CreatedOn,
                                            @ModifiedById,
                                            @ModifiedOn,
                                            @TenantId
                                        );";


        public const string UPDATE_SPACE = @"UPDATE MSPACE
                                            SET
                                                SPACECODE = @SpaceCode,
                                                SPACENAME = @SpaceName,
                                                SPACEDESCRIPTION = @SpaceDescription,
                                                PARENTSAPCEID = @ParentSapceId,
                                                PARENTSPACEIDS = @ParentSpaceIds,
                                                SPACELEVEL = @SpaceLevel,
                                                ISKB = @IsKb,
                                                ISVERSION = @IsVersion,
                                                ISCHECKINOUT = @IsCheckInOut,
                                                ISRSS = @IsRss,
                                                ISFOLDERS = @IsFolders,
                                                ISCOLLOBORATION = @IsColloboration,
                                                ISBLOGS = @IsBlogs,
                                                ISDISCUSSION = @IsDiscussion,
                                                ISWIKI = @IsWiki,
                                                SLUG = @Slug,
                                                ICON = @Icon,
                                                COLORID = @ColorId,
                                                SETTINGS = @Settings,
                                                SPACESTATUS = @SpaceStatus,
                                                SORTORDER = @SortOrder,
                                                STATUS = @Status,
                                                VERSION = @Version,
                                                SOURCETYPE = @SourceType,
                                                MODIFIEDBYID = @ModifiedById,
                                                MODIFIEDON = @ModifiedOn,
                                                TENANTID = @TenantId
                                            WHERE
                                                SPACEID = @SpaceId;";


        public const string GET_SELECTLIST_SPACE = @"WITH SpaceCTE AS (
                                                    SELECT
                                                        S.SPACEID              AS SpaceId,
                                                        S.SPACECODE            AS SpaceCode,
                                                        S.SPACENAME            AS SpaceName,
                                                        S.SPACEDESCRIPTION     AS SpaceDescription,
                                                        S.PARENTSAPCEID        AS ParentSapceId,
                                                        S.PARENTSPACEIDS       AS ParentSpaceIds,
                                                        S.SPACELEVEL           AS SpaceLevel,
                                                        S.ISKB                 AS IsKb,
                                                        S.ISVERSION            AS IsVersion,
                                                        S.ISCHECKINOUT         AS IsCheckInOut,
                                                        S.ISRSS                AS IsRss,
                                                        S.ISFOLDERS            AS IsFolders,
                                                        S.ISCOLLOBORATION      AS IsColloboration,
                                                        S.ISBLOGS              AS IsBlogs,
                                                        S.ISDISCUSSION         AS IsDiscussion,
                                                        S.ISWIKI               AS IsWiki,
                                                        S.SLUG                 AS Slug,
                                                        S.ICON                 AS Icon,
                                                        S.COLORID              AS ColorId,
                                                        S.SETTINGS             AS Settings,
                                                        S.SPACESTATUS          AS SpaceStatus,
                                                        S.SORTORDER            AS SortOrder,
                                                        S.STATUS               AS Status,
                                                        S.VERSION              AS Version,
                                                        S.SOURCETYPE           AS SourceType,
                                                        S.CREATEDBYID          AS CreatedById,
                                                        S.CREATEDON            AS CreatedOn,
                                                        S.MODIFIEDBYID         AS ModifiedById,
                                                        S.MODIFIEDON           AS ModifiedOn,
                                                        S.TENANTID             AS TenantId,
                                                        ROW_NUMBER() OVER (ORDER BY S.SPACEID) AS RowNum
                                                    FROM MSPACE S
                                                )
                                                SELECT *
                                                FROM SpaceCTE
                                                WHERE 
                                                    (@firstnumber = -1 AND @maxresult = -1)
                                                    OR (RowNum BETWEEN @firstnumber AND @maxresult);";


        public const string DELETE_SPACE = @"DELETE FROM MSPACE
                                            WHERE SPACEID = @SpaceId;";
        public const string GET_SPACE_LIST = @"SELECT
                                        S.SPACEID              AS SpaceId,
                                        S.SPACECODE            AS SpaceCode,
                                        S.SPACENAME            AS SpaceName,
                                        S.SPACEDESCRIPTION     AS SpaceDescription,
                                        S.PARENTSAPCEID        AS ParentSapceId,
                                        S.PARENTSPACEIDS       AS ParentSpaceIds,
                                        S.SPACELEVEL           AS SpaceLevel,
                                        S.ISKB                 AS IsKb,
                                        S.ISVERSION            AS IsVersion,
                                        S.ISCHECKINOUT         AS IsCheckInOut,
                                        S.ISRSS                AS IsRss,
                                        S.ISFOLDERS            AS IsFolders,
                                        S.ISCOLLOBORATION      AS IsColloboration,
                                        S.ISBLOGS              AS IsBlogs,
                                        S.ISDISCUSSION         AS IsDiscussion,
                                        S.ISWIKI               AS IsWiki,
                                        S.SLUG                 AS Slug,
                                        S.ICON                 AS Icon,
                                        S.COLORID              AS ColorId,
                                        S.SETTINGS             AS Settings,
                                        S.SPACESTATUS          AS SpaceStatus,
                                        S.SORTORDER            AS SortOrder,
                                        S.STATUS               AS Status,
                                        S.VERSION              AS Version,
                                        S.SOURCETYPE           AS SourceType,
                                        S.CREATEDBYID          AS CreatedById,
                                        S.CREATEDON            AS CreatedOn,
                                        S.MODIFIEDBYID         AS ModifiedById,
                                        S.MODIFIEDON           AS ModifiedOn,
                                        S.TENANTID             AS TenantId
                                    FROM MSPACE S;";
    }
}