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

namespace MMDAL.Query.Grade
{
    public class GradeQB
    {

        public const string GET_GRADE = @"SELECT 
                                            /* ------------------ MATERIAL GRADE ------------------ */
                                            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,
                                            I.ITEMCODE                AS ItemCode,
                                            I.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,

                                                /* ------------------ SPECIFICATION ------------------ */
                                                S.SPECIFICATIONID           AS SpecificationId,
                                                S.SPECIFICATIONNAME         AS SpecificationName,
                                                S.SPECIFICATIONDESCRIPTION  AS SpecificationDescription,
                                                S.OBJECTTYPEID              AS ObjectTypeId,  
                                                E.ENTITYNAME                AS ObjectTypeName,
                                                E.ENTITYCODE                AS ObjectTypeCode,
                                                S.OBJECTID                  AS ObjectId,
                                                MG2.MATERIALGRADENAME       AS ObjectName,
                                                MG2.MATERIALGRADECODE       AS ObjectCode,
                                                S.STANDARDID                AS StandardId,
                                                ST.STANDARDNAME             AS StandardName, 
                                                ST.STANDARDCODE             AS StandardCode,

                                                S.CREATEDBYID             AS SpecificationCreatedById,
                                                S.CREATEDON               AS SpecificationCreatedOn,
                                                S.MODIFIEDBYID            AS SpecificationModifiedById,
                                                S.MODIFIEDON              AS SpecificationModifiedOn,
                                                S.SORTORDER               AS SpecificationSortOrder,
                                                S.STATUS                  AS SpecificationStatus,
                                                S.VERSION                 AS SpecificationVersion,
                                                S.SOURCETYPE              AS SpecificationSourceType,
                                                S.TENANTID                AS SpecificationTenantId,

                                                    /* ------------------ SPECIFICATION DETAIL ------------------ */
                                                    SD.SPECIFICATIONDETAILID  AS SpecificationDetailId,
                                                    SD.SPECIFICATIONID        AS SpecificationId,
                                                    SD.SETSLNO                AS SetSlNo,
                                                    SD.PARAMETERSETID         AS SpecificationDetailParameterSetId,
                                                    PS.PARAMETERSETCODE       AS SpecificationDetailParameterSetCode,
                                                    PS.PARAMETERSETNAME       AS SpecificationDetailParameterSetName,
                                                    SD.SLNO                   AS SpecificationDetailSlNo,
                                                    SD.PARAMETERID            AS SpecificationDetailParameterId,
                                                    P.PARAMETERCODE           AS SpecificationDetailParameterCode,
                                                    P.PARAMETERNAME           AS SpecificationDetailParameterName,
                                                    SD.MINVALUE               AS SpecificationDetailMinValue,
                                                    SD.MAXVALUE               AS SpecificationDetailMaxValue,
                                                    SD.PERCENTAGE             AS SpecificationDetailPercentage,
                                                    SD.HELPTEXT               AS SpecificationDetailHelpText,
                                                    SD.REMARKS                AS SpecificationDetailRemarks
                                                        FROM MMATERIALGRADE MG
                                                        LEFT JOIN MMATERIALGROUP MGR ON MGR.MATERIALGROUPID = MG.MATERIALGROUPID
                                                        LEFT JOIN MITEM I ON I.ITEMID = MG.ITEMID
                                                        LEFT JOIN MSPECIFICATION S ON S.OBJECTID = MG.MATERIALGRADEID
                                                        LEFT JOIN MENTITY E ON E.ENTITYID = S.OBJECTTYPEID
                                                        LEFT JOIN MMATERIALGRADE MG2  ON MG2.MATERIALGRADEID = S.OBJECTID
                                                        LEFT JOIN MSTANDARD ST ON ST.STANDARDID = S.STANDARDID
                                                        LEFT JOIN MSPECIFICATIONDETAIL SD ON SD.SPECIFICATIONID = S.SPECIFICATIONID
                                                        LEFT JOIN MPARAMETERSET PS ON PS.PARAMETERSETID = SD.PARAMETERSETID
                                                        LEFT JOIN MPARAMETER P ON P.PARAMETERID = SD.PARAMETERID
                                                        WHERE MG.MATERIALGRADEID = @GradeId
                                                        ORDER BY MG.MATERIALGRADEID, S.SPECIFICATIONID, SD.SPECIFICATIONDETAILID;";


        public const string DELETE_SPECIFICATION_DETAIL = @"DELETE SD
                                                            FROM MSPECIFICATIONDETAIL SD
                                                            INNER JOIN MSPECIFICATION S ON SD.SPECIFICATIONID = S.SPECIFICATIONID
                                                            WHERE S.OBJECTID = @GradeId;";

        public const string DELETE_SPECIFICATION = @"DELETE FROM MSPECIFICATION
                                                     WHERE OBJECTID = @GradeId;";

        public const string DELETE_MATERIAL_GRADE = @"DELETE FROM MMATERIALGRADE
                                                      WHERE MATERIALGRADEID = @GradeId;";

        public const string GET_PARAMETERSET_COUNT = @"SELECT COUNT(1)
                                                    FROM dbo.MMATERIALGRADE
                                                    WHERE
                                                        (
                                                            @MaterialGradeId <> -1 AND MATERIALGRADEID = @MaterialGradeId
                                                        )
                                                        AND MATERIALGRADEID <> @MaterialGradeId";

        public const string GET_SELECTLIST_GRADE = @"SELECT
                                                            MATERIALGRADEID AS Id,
                                                            MATERIALGRADECODE AS Code,
                                                            MATERIALGRADENAME AS Name
                                                            FROM MMATERIALGRADE";

        public const string GET_LIST_GRADE = @"SELECT

                                                                            /* ==========================================
                                                                               MATERIAL GRADE
                                                                               ========================================== */

                                                                            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,

                                                                            I.ITEMCODE
                                                                                AS ItemCode,

                                                                            I.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,


                                                                            /* ==========================================
                                                                               SPECIFICATION
                                                                               ========================================== */

                                                                            S.SPECIFICATIONID
                                                                                AS SpecificationId,

                                                                            S.SPECIFICATIONNAME
                                                                                AS SpecificationName,

                                                                            S.SPECIFICATIONDESCRIPTION
                                                                                AS SpecificationDescription,

                                                                            S.OBJECTTYPEID
                                                                                AS ObjectTypeId,

                                                                            E.ENTITYNAME
                                                                                AS ObjectTypeName,

                                                                            E.ENTITYCODE
                                                                                AS ObjectTypeCode,

                                                                            S.OBJECTID
                                                                                AS ObjectId,

                                                                            MG.MATERIALGRADENAME
                                                                                AS ObjectName,

                                                                            MG.MATERIALGRADECODE
                                                                                AS ObjectCode,

                                                                            S.STANDARDID
                                                                                AS StandardId,

                                                                            ST.STANDARDNAME
                                                                                AS StandardName,

                                                                            ST.STANDARDCODE
                                                                                AS StandardCode,

                                                                            S.CREATEDBYID
                                                                                AS SpecificationCreatedById,

                                                                            S.CREATEDON
                                                                                AS SpecificationCreatedOn,

                                                                            S.MODIFIEDBYID
                                                                                AS SpecificationModifiedById,

                                                                            S.MODIFIEDON
                                                                                AS SpecificationModifiedOn,

                                                                            S.SORTORDER
                                                                                AS SpecificationSortOrder,

                                                                            S.STATUS
                                                                                AS SpecificationStatus,

                                                                            S.VERSION
                                                                                AS SpecificationVersion,

                                                                            S.SOURCETYPE
                                                                                AS SpecificationSourceType,

                                                                            S.TENANTID
                                                                                AS SpecificationTenantId,


                                                                            /* ==========================================
                                                                               SPECIFICATION DETAIL
                                                                               ========================================== */

                                                                            SD.SPECIFICATIONDETAILID
                                                                                AS SpecificationDetailId,

                                                                            SD.SPECIFICATIONID
                                                                                AS SpecificationDetailSpecificationId,

                                                                            SD.SETSLNO
                                                                                AS SetSlNo,

                                                                            SD.PARAMETERSETID
                                                                                AS SpecificationDetailParameterSetId,

                                                                            PS.PARAMETERSETCODE
                                                                                AS SpecificationDetailParameterSetCode,

                                                                            PS.PARAMETERSETNAME
                                                                                AS SpecificationDetailParameterSetName,

                                                                            SD.SLNO
                                                                                AS SpecificationDetailSlNo,

                                                                            SD.PARAMETERID
                                                                                AS SpecificationDetailParameterId,

                                                                            P.PARAMETERCODE
                                                                                AS SpecificationDetailParameterCode,

                                                                            P.PARAMETERNAME
                                                                                AS SpecificationDetailParameterName,

                                                                            SD.MINVALUE
                                                                                AS SpecificationDetailMinValue,

                                                                            SD.MAXVALUE
                                                                                AS SpecificationDetailMaxValue,

                                                                            SD.PERCENTAGE
                                                                                AS SpecificationDetailPercentage,

                                                                            SD.HELPTEXT
                                                                                AS SpecificationDetailHelpText,

                                                                            SD.REMARKS
                                                                                AS SpecificationDetailRemarks


                                                                        FROM MMATERIALGRADE MG

                                                                        LEFT JOIN MMATERIALGROUP MGR
                                                                            ON MGR.MATERIALGROUPID =
                                                                               MG.MATERIALGROUPID

                                                                        LEFT JOIN MITEM I
                                                                            ON I.ITEMID =
                                                                               MG.ITEMID

                                                                        LEFT JOIN MSPECIFICATION S
                                                                            ON S.OBJECTID =
                                                                               MG.MATERIALGRADEID

                                                                        LEFT JOIN MENTITY E
                                                                            ON E.ENTITYID =
                                                                               S.OBJECTTYPEID

                                                                        LEFT JOIN MSTANDARD ST
                                                                            ON ST.STANDARDID =
                                                                               S.STANDARDID

                                                                        LEFT JOIN MSPECIFICATIONDETAIL SD
                                                                            ON SD.SPECIFICATIONID =
                                                                               S.SPECIFICATIONID

                                                                        LEFT JOIN MPARAMETERSET PS
                                                                            ON PS.PARAMETERSETID =
                                                                               SD.PARAMETERSETID

                                                                        LEFT JOIN MPARAMETER P
                                                                            ON P.PARAMETERID =
                                                                               SD.PARAMETERID

                                                                        ORDER BY

                                                                            MG.MATERIALGRADEID,

                                                                            S.SPECIFICATIONID,

                                                                            SD.SPECIFICATIONDETAILID;";


    }
}