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

namespace MMDAL.Query.StandardType
{
    public class StandardTypeQB
    {
        public const string GET_STANDARDTYPE = @"SELECT
                                                    ST.STANDARDTYPEID        AS StandardTypeId,
                                                    ST.STANDARDTYPECODE      AS StandardTypeCode,
                                                    ST.STANDARDTYPENAME      AS StandardTypeName,
                                                    ST.REMARKS               AS StandardTypeRemarks,
                                                    ST.SORTORDER             AS StandardTypeSortOrder,
                                                    ST.STATUS                AS StandardTypeStatus,
                                                    ST.VERSION               AS StandardTypeVersion,
                                                    ST.SOURCETYPE            AS StandardTypeSourceType,
                                                    ST.CREATEDBYID           AS StandardTypeCreatedById,
                                                    ST.CREATEDON             AS StandardTypeCreatedOn,
                                                    ST.MODIFIEDBYID          AS StandardTypeModifiedById,
                                                    ST.MODIFIEDON            AS StandardTypeModifiedOn
                                                FROM MSTANDARDTYPE ST
                                                WHERE ST.STANDARDTYPEID = @standardtypeid;";

        public const string SAVE_STANDARDTYPE = @"INSERT INTO MSTANDARDTYPE
                                                (
                                                    STANDARDTYPEID,
                                                    STANDARDTYPECODE,
                                                    STANDARDTYPENAME,
                                                    REMARKS,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON,
                                                    SOURCETYPE
                                                )
                                                VALUES
                                                (
                                                    @StandardTypeId,
                                                    @StandardTypeCode,
                                                    @StandardTypeName,
                                                    @StandardTypeRemarks,
                                                    @StandardTypeSortOrder,
                                                    @StandardTypeStatus,
                                                    @StandardTypeVersion,
                                                    @StandardTypeCreatedById,
                                                    @StandardTypeCreatedOn,
                                                    @StandardTypeModifiedById,
                                                    @StandardTypeModifiedOn,
                                                    @StandardTypeSourceType
                                                );";

        public const string UPDATE_STANDARDTYPE = @"UPDATE MSTANDARDTYPE
                                                SET
                                                    STANDARDTYPECODE    = @StandardTypeCode,
                                                    STANDARDTYPENAME    = @StandardTypeName,
                                                    REMARKS             = @StandardTypeRemarks,
                                                    SORTORDER           = @StandardTypeSortOrder,
                                                    STATUS              = @StandardTypeStatus,
                                                    VERSION             = @StandardTypeVersion,
                                                    MODIFIEDBYID        = @StandardTypeModifiedById,
                                                    MODIFIEDON          = @StandardTypeModifiedOn,
                                                    SOURCETYPE          = @StandardTypeSourceType
                                                WHERE STANDARDTYPEID = @StandardTypeId;";

        public const string DELETE_STANDARDTYPE = @"DELETE FROM MSTANDARDTYPE
                                                        WHERE STANDARDTYPEID = @standardtypeid;";

        public const string GET_SELECTLIST_STANDARDTYPE = @"WITH PagedStandardType AS (
                                                        SELECT
                                                            ST.STANDARDTYPEID           AS Id,
                                                            ST.STANDARDTYPECODE         AS Code,
                                                            ST.STANDARDTYPENAME         AS Name,
                                                            ROW_NUMBER() OVER (ORDER BY ST.STANDARDTYPENAME) AS RowNum
                                                        FROM 
                                                            MSTANDARDTYPE ST
                                                        WHERE 
                                                            ST.STATUS = 1
                                                    )
                                                    SELECT
                                                        Id,
                                                        Code,
                                                        Name
                                                    FROM PagedStandardType
                                                    WHERE(@firstnumber = -1 AND @maxresult = -1)
                                                       OR (RowNum BETWEEN @firstnumber AND @maxresult);";
    }
}