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

namespace MMDAL.Query.Standard
{
    public class StandardQB
    {
        public const string GET_STANDARD = @"SELECT
                                                S.STANDARDID              AS StandardId,
                                                S.STANDARDCODE            AS StandardCode,
                                                S.STANDARDNAME            AS StandardName,
                                                S.STANDARDTYPEID          AS StandardTypeId,
                                                ST.STANDARDTYPECODE       AS StandardTypeCode,
                                                ST.STANDARDTYPENAME       AS StandardTypeName,
                                                S.STANDARDNATURE          AS StandardNature,
                                                S.REMARKS                 AS StandardRemarks,
                                                S.SORTORDER               AS StandardSortOrder,
                                                S.STATUS                  AS StandardStatus,
                                                S.VERSION                 AS StandardVersion,
                                                S.SOURCETYPE              AS StandardSourceType,
                                                S.CREATEDBYID             AS StandardCreatedById,
                                                S.CREATEDON               AS StandardCreatedOn,
                                                S.MODIFIEDBYID            AS StandardModifiedById,
                                                S.MODIFIEDON              AS StandardModifiedOn
                                            FROM MSTANDARD S
                                            INNER JOIN MSTANDARDTYPE ST ON S.STANDARDTYPEID = ST.STANDARDTYPEID
                                            WHERE S.STANDARDID = @standardid;";

        public const string SAVE_STANDARD = @"INSERT INTO MSTANDARD
                                                (
                                                    STANDARDID,
                                                    STANDARDCODE,
                                                    STANDARDNAME,
                                                    STANDARDTYPEID,
                                                    STANDARDNATURE,
                                                    REMARKS,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON,
                                                    SOURCETYPE
                                                )
                                                VALUES
                                                (
                                                    @StandardId,
                                                    @StandardCode,
                                                    @StandardName,
                                                    @StandardTypeId,
                                                    @StandardNature,
                                                    @StandardRemarks,
                                                    @StandardSortOrder,
                                                    @StandardStatus,
                                                    @StandardVersion,
                                                    @StandardCreatedById,
                                                    @StandardCreatedOn,
                                                    @StandardModifiedById,
                                                    @StandardModifiedOn,
                                                    @StandardSourceType
                                                );";

        public const string UPDATE_STANDARD = @"UPDATE MSTANDARD
                                                SET
                                                    STANDARDCODE        = @StandardCode,
                                                    STANDARDNAME        = @StandardName,
                                                    STANDARDTYPEID      = @StandardTypeId,
                                                    STANDARDNATURE      = @StandardNature,
                                                    REMARKS             = @StandardRemarks,
                                                    SORTORDER           = @StandardSortOrder,
                                                    STATUS              = @StandardStatus,
                                                    VERSION             = @StandardVersion,
                                                    MODIFIEDBYID        = @StandardModifiedById,
                                                    MODIFIEDON          = @StandardModifiedOn,
                                                    SOURCETYPE          = @StandardSourceType
                                                WHERE STANDARDID = @StandardId;";

        public const string DELETE_STANDARD = @"DELETE FROM MSTANDARD
                                                    WHERE STANDARDID = @standardid;";

        public const string GET_SELECTLIST_STANDARD = @"WITH PagedStandard AS (
                                                        SELECT
                                                            S.STANDARDID           AS Id,
                                                            S.STANDARDCODE         AS Code,
                                                            S.STANDARDNAME         AS Name,
                                                            ROW_NUMBER() OVER (ORDER BY S.STANDARDNAME) AS RowNum
                                                        FROM 
                                                            MSTANDARD S
                                                        WHERE 
                                                            S.STATUS = 1
                                                    )
                                                    SELECT
                                                        Id,
                                                        Code,
                                                        Name
                                                    FROM PagedStandard
                                                    WHERE(@firstnumber = -1 AND @maxresult = -1)
                                                       OR (RowNum BETWEEN @firstnumber AND @maxresult)";
    }
}