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

namespace MMDAL.Query.Shape
{
    public class ShapeQB
    {
        public const string GET_SHAPE = @"SELECT
                                            SHAPEID          AS ShapeId,
                                            SHAPECODE        AS ShapeCode,
                                            SHAPENAME        AS ShapeName,
                                            DESCRIPTION      AS ShapeDescription,
                                            DIM1NAME         AS DIM1Name,
                                            DIM2NAME         AS DIM2Name,
                                            DIM3NAME         AS DIM3Name,
                                            DIM4NAME         AS DIM4Name,
                                            DIM5NAME         AS DIM5Name,
                                            SORTORDER        AS ShapeSortOrder,
                                            STATUS           AS ShapeStatus,
                                            VERSION          AS ShapeVersion,
                                            SOURCETYPE       AS SourceType,
                                            CREATEDBYID      AS ShapeCreatedById,
                                            CREATEDON        AS ShapeCreatedOn,
                                            MODIFIEDBYID     AS ShapeModifiedById,
                                            MODIFIEDON       AS ShapeModifiedOn
                                        FROM MSHAPE
                                        WHERE SHAPEID = @shapeid;";

        public const string SAVE_SHAPE = @"INSERT INTO MSHAPE
                                            (
                                                SHAPECODE,
                                                SHAPENAME,
                                                DESCRIPTION,
                                                DIM1NAME,
                                                DIM2NAME,
                                                DIM3NAME,
                                                DIM4NAME,
                                                DIM5NAME,
                                                SORTORDER,
                                                STATUS,
                                                VERSION,
                                                SOURCETYPE,
                                                CREATEDBYID,
                                                CREATEDON,
                                                MODIFIEDBYID,
                                                MODIFIEDON
                                            )
                                            VALUES
                                            (
                                                @ShapeCode,
                                                @ShapeName,
                                                @ShapeDescription,
                                                @DIM1Name,
                                                @DIM2Name,
                                                @DIM3Name,
                                                @DIM4Name,
                                                @DIM5Name,
                                                @ShapeSortOrder,
                                                @ShapeStatus,
                                                @ShapeVersion,
                                                @SourceType,
                                                @ShapeCreatedById,
                                                @ShapeCreatedOn,
                                                @ShapeModifiedById,
                                                @ShapeModifiedOn
                                            );";

        public const string UPDATE_SHAPE = @"UPDATE MSHAPE
                                            SET
                                                SHAPECODE       = @ShapeCode,
                                                SHAPENAME       = @ShapeName,
                                                DESCRIPTION     = @ShapeDescription,
                                                DIM1NAME        = @DIM1Name,
                                                DIM2NAME        = @DIM2Name,
                                                DIM3NAME        = @DIM3Name,
                                                DIM4NAME        = @DIM4Name,
                                                DIM5NAME        = @DIM5Name,
                                                SORTORDER       = @ShapeSortOrder,
                                                STATUS          = @ShapeStatus,
                                                VERSION         = @ShapeVersion,
                                                SOURCETYPE      = @SourceType,
                                                MODIFIEDBYID    = @ShapeModifiedById,
                                                MODIFIEDON      = @ShapeModifiedOn
                                            WHERE
                                                SHAPEID = @ShapeId;";

        public const string GET_SELECTLIST_SHAPE = @"WITH PagedShape AS (
                                                        SELECT
                                                            S.SHAPEID               AS Id,
                                                            S.SHAPECODE             AS Code,
                                                            S.SHAPENAME             AS Name,
                                                            ROW_NUMBER() OVER (ORDER BY S.SHAPENAME) AS RowNum
                                                        FROM 
                                                            MSHAPE S
                                                        WHERE 
                                                            S.STATUS = 1
                                                    )
                                                    SELECT
                                                        Id,
                                                        Code,
                                                        Name
                                                    FROM PagedShape
                                                   WHERE 
                                                    (@firstnumber = -1 AND @maxresult = -1)
                                                    OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string DELETE_SHAPE = @"DELETE FROM MSHAPE
                                            WHERE SHAPEID = @shapeid;";
                                            
    }
}