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

namespace MMDAL.Query.Specification
{
    public class SpecificationQB
    {
        public const string GET_SPECIFICATION = @"SELECT
                                                -- Parent Table (MSPECIFICATION)
                                                S.SPECIFICATIONID            AS SpecificationId,
                                                S.SPECIFICATIONNAME          AS SpecificationName,
                                                S.SPECIFICATIONDESCRIPTION   AS SpecificationDescription,
                                                S.OBJECTTYPEID               AS ObjectTypeId,
                                                OT.ENTITYNAME                AS ObjectTypeName,
                                                OT.ENTITYCODE                AS ObjectTypeCode,
                                                S.OBJECTID                   AS ObjectId,
                                                OB.MATERIALGRADENAME         AS ObjectName,
                                                OB.MATERIALGRADECODE         AS ObjectCode,
                                                S.STANDARDID                 AS StandardId,
                                                ST.STANDARDNAME              AS StandardName,
                                                ST.STANDARDCODE              AS StandardCode,
                                                S.CREATEDBYID                AS SpecificationCreatedById,
                                                S.CREATEDON                  AS SpecificationCreatedOn,
                                                UC.USERNAME                  AS SpecificationCreatedByName,
                                                S.MODIFIEDBYID               AS SpecificationModifiedById,
                                                S.MODIFIEDON                 AS SpecificationModifiedOn,
                                                UM.USERNAME                  AS SpecificationModifiedByName,
                                                S.SORTORDER                  AS SpecificationSortOrder,
                                                S.STATUS                     AS SpecificationStatus,
                                                S.VERSION                    AS SpecificationVersion,
                                                S.SOURCETYPE                 AS SpecificationSourceType,

                                                            -- Child Table (MSPECIFICATIONDETAIL)
                                                            SD.SPECIFICATIONDETAILID     AS SpecificationDetailId,
                                                            SD.SPECIFICATIONID           AS SpecificationId,
                                                            SD.SETSLNO                   AS SetSlNo,
                                                            SD.PARAMETERSETID            AS SpecificationDetailParameterSetId,
                                                            PS.PARAMETERSETNAME          AS SpecificationDetailParameterSetName,
                                                            PS.PARAMETERSETCODE          AS SpecificationDetailParameterSetCode,
                                                            SD.SLNO                      AS SpecificationDetailSlNo,
                                                            SD.PARAMETERID               AS SpecificationDetailParameterId,
                                                            P.PARAMETERNAME              AS SpecificationDetailParameterName,
                                                            P.PARAMETERCODE              AS SpecificationDetailParameterCode,
                                                            SD.MINVALUE                  AS SpecificationDetailMinValue,
                                                            SD.MAXVALUE                  AS SpecificationDetailMaxValue,
                                                            SD.PERCENTAGE                AS SpecificationDetailPercentage,
                                                            SD.HELPTEXT                  AS SpecificationDetailHelpText,
                                                            SD.REMARKS                   AS SpecificationDetailRemarks
                                            FROM MSPECIFICATION S
                                            LEFT JOIN MSPECIFICATIONDETAIL SD ON S.SPECIFICATIONID = SD.SPECIFICATIONID
                                            LEFT JOIN MENTITY OT ON S.OBJECTTYPEID = OT.ENTITYID
                                            LEFT JOIN MMATERIALGRADE OB ON S.OBJECTID = OB.MATERIALGRADEID
                                            LEFT JOIN MSTANDARD ST ON S.STANDARDID = ST.STANDARDID
                                            LEFT JOIN MUSER UC ON S.CREATEDBYID = UC.USERID
                                            LEFT JOIN MUSER UM ON S.MODIFIEDBYID = UM.USERID
                                            LEFT JOIN MPARAMETERSET PS ON SD.PARAMETERSETID = PS.PARAMETERSETID
                                            LEFT JOIN MPARAMETER P ON SD.PARAMETERID = P.PARAMETERID
                                            WHERE S.SPECIFICATIONID = @specificationid;";

        public const string SAVE_SPECIFICATION = @"INSERT INTO MSPECIFICATION
                                           (
                                               SPECIFICATIONID,
                                               SPECIFICATIONNAME,
                                               SPECIFICATIONDESCRIPTION,
                                               OBJECTTYPEID,
                                               OBJECTID,
                                               STANDARDID,
                                               CREATEDBYID,
                                               CREATEDON,
                                               MODIFIEDBYID,
                                               MODIFIEDON,
                                               SORTORDER,
                                               STATUS,
                                               VERSION
                                           )
                                           VALUES
                                           (
                                               @SpecificationId,
                                               @SpecificationName,
                                               @SpecificationDescription,
                                               @ObjectTypeId,
                                               @ObjectId,
                                               @StandardId,
                                               @SpecificationCreatedById,
                                               @SpecificationCreatedOn,
                                               @SpecificationModifiedById,
                                               @SpecificationModifiedOn,
                                               @SpecificationSortOrder,
                                               @SpecificationStatus,
                                               @SpecificationVersion
                                        );";

        public const string SAVE_SPECIFICATION_DETAIL = @"INSERT INTO MSPECIFICATIONDETAIL
                                                (
                                                    SPECIFICATIONDETAILID,
                                                    SPECIFICATIONID,
                                                    SETSLNO,
                                                    PARAMETERSETID,
                                                    SLNO,
                                                    PARAMETERID,
                                                    MINVALUE,
                                                    MAXVALUE,
                                                    PERCENTAGE,
                                                    HELPTEXT,
                                                    REMARKS
                                                )
                                                VALUES
                                                (
                                                    @SpecificationDetailId,
                                                    @SpecificationId,
                                                    @SetSlNo,
                                                    @SpecificationDetailParameterSetId,
                                                    @SpecificationDetailSlNo,
                                                    @SpecificationDetailParameterId,
                                                    @SpecificationDetailMinValue,
                                                    @SpecificationDetailMaxValue,
                                                    @SpecificationDetailPercentage,
                                                    @SpecificationDetailHelpText,
                                                    @SpecificationDetailRemarks
                                                );";

        public const string UPDATE_SPECIFICATION = @"UPDATE MSPECIFICATION
                                            SET
                                                SPECIFICATIONNAME        = @SpecificationName,
                                                SPECIFICATIONDESCRIPTION = @SpecificationDescription,
                                                OBJECTTYPEID             = @ObjectTypeId,
                                                OBJECTID                 = @ObjectId,
                                                STANDARDID               = @StandardId,
                                                MODIFIEDBYID             = @SpecificationModifiedById,
                                                MODIFIEDON               = @SpecificationModifiedOn,
                                                SORTORDER                = @SpecificationSortOrder,
                                                STATUS                   = @SpecificationStatus,
                                                VERSION                  = @SpecificationVersion
                                            WHERE
                                                SPECIFICATIONID          = @SpecificationId";



        public const string UPDATE_SPECIFICATION_DETAIL = @"UPDATE MSPECIFICATIONDETAIL
                                                          SET
                                                              SPECIFICATIONID = @SpecificationId,
                                                              SETSLNO         = @SetSlNo,
                                                              PARAMETERSETID  = @SpecificationDetailParameterSetId,
                                                              SLNO            = @SpecificationDetailSlNo,
                                                              PARAMETERID     = @SpecificationDetailParameterId,
                                                              MINVALUE        = @SpecificationDetailMinValue,
                                                              MAXVALUE        = @SpecificationDetailMaxValue,
                                                              PERCENTAGE      = @SpecificationDetailPercentage,
                                                              HELPTEXT        = @SpecificationDetailHelpText,
                                                              REMARKS         = @SpecificationDetailRemarks
                                                          WHERE
                                                              SPECIFICATIONDETAILID = @SpecificationDetailId;";

        public const string DELETE_SPECIFICATION = @"DELETE FROM MSPECIFICATION
                                                        WHERE SPECIFICATIONID = @specificationid;";

        public const string DELETE_SPECIFICATION_DETAIL = @"DELETE FROM MSPECIFICATIONDETAIL
                                                            WHERE SPECIFICATIONID = @specificationid;";

        public const string GET_SELECTLIST_SPECIFICATION = @"SELECT
                                                                SPECIFICATIONID AS Id,
                                                                SPECIFICATIONNAME AS Name
                                                                FROM MSPECIFICATION";

        public const string GET_SPECIFICATION_COUNT = @"SELECT COUNT(1)
                                                    FROM dbo.MSPECIFICATION
                                                    WHERE
                                                        (
                                                            @SpecificationId <> -1 AND SPECIFICATIONID = @SpecificationId
                                                        )
                                                        AND SPECIFICATIONID <> @SpecificationId";
    }
}