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

namespace MMDAL.Query.Material
{
    public class MaterialQB
    {
        public const string GET_MATERIAL = @"SELECT
                                                M.MATERIALID     AS MaterialId,
                                                M.MATERIALCODE   AS MaterialCode,
                                                M.MATERIALNAME   AS MaterialName,
                                                M.NATURE         AS MaterialNature,
                                                M.DENSITY        AS MaterialDensity,
                                                M.SPECIFICGRAVITY  AS MaterialSG,
                                                M.SORTORDER      AS MaterialSortOrder,
                                                M.STATUS         AS MaterialStatus,
                                                M.VERSION        AS MaterialVersion,
                                                M.SOURCETYPE     AS MaterialSourceType,
                                                M.CREATEDBYID    AS MaterialCreatedById,
                                                M.CREATEDON      AS MaterialCreatedOn,
                                                M.MODIFIEDBYID   AS MaterialModifiedById,
                                                M.MODIFIEDON     AS MaterialModifiedOn
                                            FROM MMATERIAL M
                                            WHERE  M.MATERIALID = @materialid;";

        public const string SAVE_MATERIAL = @"INSERT INTO MMATERIAL
                                                (
                                                    MATERIALCODE,
                                                    MATERIALNAME,
                                                    NATURE,
                                                    DENSITY,
                                                    SPECIFICGRAVITY,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    SOURCETYPE,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON
                                                )
                                                VALUES
                                                (   
                                                    @MaterialCode,
                                                    @MaterialName,
                                                    @MaterialNature,
                                                    @MaterialDensity,
                                                    @MaterialSG,
                                                    @MaterialSortOrder,
                                                    @MaterialStatus,
                                                    @MaterialVersion,
                                                    @MaterialSourceType,
                                                    @MaterialCreatedById,
                                                    @MaterialCreatedOn,
                                                    @MaterialModifiedById,
                                                    @MaterialModifiedOn
                                                );";

        public const string UPDATE_MATERIAL = @"UPDATE MMATERIAL
                                            SET
                                                MATERIALCODE   = @MaterialCode,
                                                MATERIALNAME   = @MaterialName,
                                                NATURE          = @MaterialNature,
                                                DENSITY         = @MaterialDensity,
                                                SPECIFICGRAVITY = @MaterialSG,
                                                SORTORDER       = @MaterialSortOrder,
                                                STATUS          = @MaterialStatus,
                                                VERSION         = @MaterialVersion,
                                                SOURCETYPE      = @MaterialSourceType,
                                                MODIFIEDBYID    = @MaterialModifiedById,
                                                MODIFIEDON      = @MaterialModifiedOn
                                            WHERE MATERIALID = @MaterialId;";


        public const string DELETE_MATERIAL = @"DELETE FROM MMATERIAL
                                                WHERE MATERIALID =@materialid;";


        public const string GET_SELECTLIST_MATERIAL = @"WITH PagedMaterial AS (
                                                        SELECT
                                                            M.MATERIALID               AS Id,
                                                            M.MATERIALCODE             AS Code,
                                                            M.MATERIALNAME             AS Name,
                                                            ROW_NUMBER() OVER (ORDER BY M.MATERIALNAME) AS RowNum
                                                        FROM 
                                                            MMATERIAL M
                                                        WHERE 
                                                            M.STATUS = 1
                                                    )
                                                    SELECT
                                                        Id,
                                                        Code,
                                                        Name
                                                    FROM PagedMaterial
                                                   WHERE 
                                                    (@firstnumber = -1 AND @maxresult = -1)
                                                    OR (RowNum BETWEEN @firstnumber AND @maxresult);";
    }
}