using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MMDAL.DTO.Casting;

namespace MMDAL.Query.MaterialGrade
{
    public class MaterialGradeQB
    {
        public const string GET_MATERIALGRADE = @"SELECT
                                                    MG.MATERIALGRADEID        AS MaterialGradeId,
                                                    MG.MATERIALGRADECODE      AS MaterialGradeCode,
                                                    MG.MATERIALGRADENAME      AS MaterialGradeName,
                                                    MG.REMARKS                AS MaterialGradeRemarks,
                                                    MG.MATERIALGROUPID        AS MaterialGroupId,
                                                    MGR.MATERIALGROUPCODE     AS MaterialGroupCode,
                                                    MGR.MATERIALGROUPNAME     AS MaterialGroupName,
                                                    MG.ITEMID                 AS ItemId,
                                                    IT.ITEMCODE               AS ItemCode,
                                                    IT.ITEMNAME               AS ItemName,
                                                    MG.SORTORDER              AS MaterialGradeSortOrder,
                                                    MG.STATUS                 AS MaterialGradeStatus,
                                                    MG.VERSION                AS MaterialGradeVersion,
                                                    MG.SOURCETYPE             AS MaterialGradeSourceType,
                                                    MG.CREATEDBYID            AS MaterialGradeCreatedById,
                                                    MG.CREATEDON              AS MaterialGradeCreatedOn,
                                                    MG.MODIFIEDBYID           AS MaterialGradeModifiedById,
                                                    MG.MODIFIEDON             AS MaterialGradeModifiedOn
                                                FROM MMATERIALGRADE MG
                                                INNER JOIN MMATERIALGROUP MGR ON MG.MATERIALGROUPID = MGR.MATERIALGROUPID
                                                INNER JOIN MITEM IT ON MG.ITEMID = IT.ITEMID
                                                WHERE MG.MATERIALGRADEID = @materialgradeid;";

        public const string SAVE_MATERIALGRADE = @"INSERT INTO MMATERIALGRADE
                                                    (
                                                        MATERIALGRADEID,
                                                        MATERIALGRADECODE,
                                                        MATERIALGRADENAME,
                                                        REMARKS,
                                                        MATERIALGROUPID,
                                                        ITEMID,
                                                        SORTORDER,
                                                        STATUS,
                                                        VERSION,
                                                        CREATEDBYID,
                                                        CREATEDON,
                                                        MODIFIEDBYID,
                                                        MODIFIEDON,
                                                        SOURCETYPE                                                
                                                    )
                                                    VALUES
                                                    (   
                                                        @MaterialGradeId,   
                                                        @MaterialGradeCode,
                                                        @MaterialGradeName,
                                                        @MaterialGradeRemarks,
                                                        @MaterialGroupId,
                                                        @ItemId,
                                                        @MaterialGradeSortOrder,
                                                        @MaterialGradeStatus,
                                                        @MaterialGradeVersion,
                                                        @MaterialGradeCreatedById,
                                                        @MaterialGradeCreatedOn,
                                                        @MaterialGradeModifiedById,
                                                        @MaterialGradeModifiedOn,
                                                        @MaterialGradeSourceType
                                                    );";

        public const string UPDATE_MATERIALGRADE = @"UPDATE MMATERIALGRADE
                                                    SET
                                                        MATERIALGRADECODE = @MaterialGradeCode,
                                                        MATERIALGRADENAME = @MaterialGradeName,
                                                        REMARKS           = @MaterialGradeRemarks,
                                                        MATERIALGROUPID   = @MaterialGroupId,
                                                        ITEMID            = @ItemId,
                                                        SORTORDER         = @MaterialGradeSortOrder,
                                                        STATUS            = @MaterialGradeStatus,
                                                        VERSION           = @MaterialGradeVersion,
                                                        MODIFIEDBYID      = @MaterialGradeModifiedById,
                                                        MODIFIEDON        = @MaterialGradeModifiedOn,
                                                        SOURCETYPE        = @MaterialGradeSourceType
                                                    WHERE
                                                        MATERIALGRADEID = @MaterialGradeId;";

        public const string DELETE_MATERIALGRADE = @"DELETE FROM MMATERIALGRADE
                                                        WHERE MATERIALGRADEID= @materialgradeid";

        public const string GET_SELECTLIST_MATERIALGRADE = @"WITH PagedMaterialGrade AS (
                                                        SELECT
                                                            MG.MATERIALGRADEID           AS Id,
                                                            MG.MATERIALGRADECODE         AS Code,
                                                            MG.MATERIALGRADENAME         AS Name,
                                                            ROW_NUMBER() OVER (ORDER BY MG.MATERIALGRADENAME) AS RowNum
                                                        FROM 
                                                            MMATERIALGRADE MG                                                    )
                                                    SELECT
                                                        Id,
                                                        Code,
                                                        Name
                                                    FROM PagedMaterialGrade
                                                   WHERE(@firstnumber = -1 AND @maxresult = -1)
                                                       OR (RowNum BETWEEN @firstnumber AND @maxresult);";
    }
}