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

namespace MMDAL.Query.ShapeDimension
{
    public class ShapeDimensionQB
    {
        public const string GET_SHAPEDIMENSION = @"SELECT
                                                    SD.SHAPEDIMENSIONID        AS ShapeDimensionId,
                                                    SD.SHAPEID                AS ShapeId,
                                                    SD.DIMENSIONCODE          AS DimensionCode,
                                                    SD.SEMANTICNAME           AS SemanticName,
                                                    SD.DEFAULTMEASUREUNITID   AS DefaultMeasureUnitId,
                                                    MU.UOMCODE                AS DefaultMeasureUnitCode,
                                                    MU.UOMNAME                AS DefaultMeasureUnitName,
                                                    SD.REQUIREDTYPE           AS RequiredType,
                                                    SD.SORTORDER              AS ShapeDimensionSortOrder,
                                                    SD.STATUS                 AS ShapeDimensionStatus,
                                                    SD.VERSION                AS ShapeDimensionVersion,
                                                    SD.SOURCETYPE             AS ShapeDimensionSourceType,
                                                    SD.CREATEDBYID            AS ShapeDimensionCreatedById,
                                                    SD.CREATEDON              AS ShapeDimensionCreatedOn,
                                                    SD.MODIFIEDBYID           AS ShapeDimensionModifiedById,
                                                    SD.MODIFIEDON             AS ShapeDimensionModifiedOn
                                                FROM MSHAPEDIMENSION SD
                                                INNER JOIN MSHAPE S ON S.SHAPEID = SD.SHAPEID
                                                LEFT JOIN MUOM MU ON MU.UOMID = SD.DEFAULTMEASUREUNITID
                                                LEFT JOIN MUSER CU ON CU.USERID = SD.CREATEDBYID
                                                LEFT JOIN MUSER MUU ON MUU.USERID = SD.MODIFIEDBYID
                                                WHERE SD.SHAPEDIMENSIONID = @shapedimensionid;";


        public const string SAVE_SHAPEDIMENSION = @"INSERT INTO MSHAPEDIMENSION
                                                    (
                                                        SHAPEID,
                                                        DIMENSIONCODE,
                                                        SEMANTICNAME,
                                                        DEFAULTMEASUREUNITID,
                                                        REQUIREDTYPE,
                                                        SORTORDER,
                                                        STATUS,
                                                        VERSION,
                                                        SOURCETYPE,
                                                        CREATEDBYID,
                                                        CREATEDON,
                                                        MODIFIEDBYID,
                                                        MODIFIEDON
                                                    )
                                                    VALUES
                                                    (
                                                        @ShapeId,
                                                        @DimensionCode,
                                                        @SemanticName,
                                                        @DefaultMeasureUnitId,
                                                        @RequiredType,
                                                        @ShapeDimensionSortOrder,
                                                        @ShapeDimensionStatus,
                                                        @ShapeDimensionVersion,
                                                        @ShapeDimensionSourceType,
                                                        @ShapeDimensionCreatedById,
                                                        @ShapeDimensionCreatedOn,
                                                        @ShapeDimensionModifiedById,
                                                        @ShapeDimensionModifiedOn
                                                    );";

        public const string UPDATE_SHAPEDIMENSION = @"UPDATE MSHAPEDIMENSION
                                                    SET
                                                        SHAPEID              = @ShapeId,
                                                        DIMENSIONCODE        = @DimensionCode,
                                                        SEMANTICNAME         = @SemanticName,
                                                        DEFAULTMEASUREUNITID = @DefaultMeasureUnitId,
                                                        REQUIREDTYPE         = @RequiredType,
                                                        SORTORDER            = @ShapeDimensionSortOrder,
                                                        STATUS               = @ShapeDimensionStatus,
                                                        VERSION              = @ShapeDimensionVersion,
                                                        SOURCETYPE           = @ShapeDimensionSourceType,
                                                        MODIFIEDBYID         = @ShapeDimensionModifiedById,
                                                        MODIFIEDON           = @ShapeDimensionModifiedOn
                                                    WHERE SHAPEDIMENSIONID = @ShapeDimensionId;";

        public const string DELETE_SHAPEDIMENSION = @"DELETE FROM MSHAPEDIMENSION
                                                        WHERE SHAPEDIMENSIONID = @shapedimensionid;";

        public const string GET_SELECTLIST_SHAPEDIMENSION = @"WITH PagedShapeDimension AS (
                                                        SELECT
                                                            SD.SHAPEDIMENSIONID           AS Id,
                                                            SD.DIMENSIONCODE              AS Code,
                                                            SD.SEMANTICNAME               AS Name,
                                                            ROW_NUMBER() OVER (ORDER BY SD.SHAPEDIMENSIONID) AS RowNum
                                                        FROM 
                                                            MSHAPEDIMENSION SD
                                                        WHERE 
                                                            SD.STATUS = 1
                                                        )
                                                    SELECT
                                                        Id,
                                                        Code,
                                                        Name
                                                    FROM PagedShapeDimension
                                                   WHERE 
                                                    (@firstnumber = -1 AND @maxresult = -1)
                                                    OR (RowNum BETWEEN @firstnumber AND @maxresult);";
    }
}