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

namespace MMDAL.Query.Pattern
{
    public static class PatternQB
    {
        public const string GET_PATTERN = @"SELECT
/* =====================
   PatternDTO (PARENT)
   ===================== */
P.PATTERNID               AS PatternId,
P.PATTERNCODE             AS PatternCode,
P.PATTERNNAME             AS PatternName,
P.PATTERNTYPE             AS PatternType,

P.PARTYID                 AS PartyId,
PT.PARTYCODE              AS PartyCode,
PT.PARTYNAME              AS PartyName,

P.PARAMETERSETID          AS ParameterSetId,
PS.PARAMETERSETCODE       AS ParameterSetCode,
PS.PARAMETERSETNAME       AS ParameterSetName,

P.POURINGWEIGHT           AS PatternPouringWeight,
P.TOTALWEIGHT             AS PatternTotalWeight,
P.RUNNERWEIGHT            AS PatternRunnerWeight,

P.UOMID                   AS UOMId,
U.UOMCODE                 AS UOMCode,
U.UOMNAME                 AS UOMName,

P.GRADEID                 AS GradeId,
G.GCMCODE                 AS GradeCode,
G.GCMNAME                 AS GradeName,

P.REMARKS                 AS PatternRemarks,
P.SOURCETYPE              AS PatternSourceType,
P.SORTORDER               AS PatternSortOrder,
P.STATUS                  AS PatternStatus,
P.VERSION                 AS PatternVersion,

P.CREATEDBYID             AS PatternCreatedById,
P.CREATEDON               AS PatternCreatedOn,
P.MODIFIEDBYID            AS PatternModifiedById,
P.MODIFIEDON              AS PatternModifiedOn,

P.NATURE                  AS PatternNature,
P.LIFECOUNT               AS PatternLifeCount,
P.START                   AS PatternStart,
P.OPENINGCOUNT            AS PatternOpeningCount,
P.TRANSACTIONCOUNT        AS PatternTransactionCount,
P.BALANCECOUNT            AS PatternBalanceCount,

COALESCE(P.PATTERNSTATUS,0)        AS PatternApproveStatus,
COALESCE(P.YIELDPERCENTAGE,0.0)   AS PatternYieldPercentage,
COALESCE(P.BOXSIZE,'')             AS PatternBoxSize,
COALESCE(P.SPRUCECUPSIZE,'')       AS PatternSpruceCupSize,
P.FIRSTSAMPLEMADEON               AS FirstSampleMadeOn,
COALESCE(P.CORELABOURCHARGE,0.0)  AS PatternCoreLabourCharge,
COALESCE(P.NUMBEROFCOREBOX,0)     AS PatternNumberofCoreBox,

P.GRADEPARAMETERSETID     AS GradeParameterSetId,
GPS.PARAMETERSETCODE      AS GradeParameterSetCode,
GPS.PARAMETERSETNAME      AS GradeParameterSetName,

P.POURINGPARAMETERSETID   AS PouringParameterSetId,
PPS.PARAMETERSETCODE      AS PouringParameterSetCode,
PPS.PARAMETERSETNAME      AS PouringParameterSetName,

P.KNOCKOUTPARAMETERSETID  AS KnockoutParameterSetId,
KPS.PARAMETERSETCODE      AS KnockoutParameterSetCode,
KPS.PARAMETERSETNAME      AS KnockoutParameterSetName,

P.PATTERNMATERIALID       AS PatternMaterialId,
PM.GCMCODE AS PatternMaterialCode,
PM.GCMNAME AS PatternMaterialName,

P.PATTERNMATERIALTOPID    AS PatternMaterialTopId,
PMT.GCMCODE AS PatternMaterialTopCode,
PMT.GCMNAME AS PatternMaterialTopName,

P.PATTERNMATERIALBOTTOMID AS PatternMaterialBottomId,
PMB.GCMCODE AS PatternMaterialBottomCode,
PMB.GCMNAME AS PatternMaterialBottomName,

P.PATTERNMETHODID         AS PatternMethodId,
P.NUMBEROFCAVITY          AS NumberOfCavity,
P.NUMBEROFWORKINGCAVITY   AS NumberOfWorkingCavity,
P.PLATEOWNERSHIPTYPE      AS PlateOwnershipType,
P.PLATEOWNERID            AS PlateOwnerId,
P.MATCHPLATEMATERIALID    AS MatchPlateMaterialId,
P.MATCHPLATESIZE          AS MatchPlateSize,
P.PATTERNLOCATION         AS PatternLocation,
P.PLATEREMARKS            AS PlateRemarks,

MN.GCMCODE				AS MatchPlateMaterialCode,
MN.GCMNAME				AS MatchPlateMaterialName,

MNA.GCMCODE				AS PatternMethodCode,
MNA.GCMNAME				AS PatternMethodName,

MNB.PARTYCODE			AS PlateOwnerCode,
MNB.PARTYNAME			AS PlateOwnerName,

P.ITEMID                  AS ItemId,
PI.ITEMCODE               AS ItemCode,
PI.ITEMNAME               AS ItemName,

P.TENANTID                AS TenantId,

/* ========= SPLIT POINT (MANDATORY) ========= */
PD.PATTERNDETAILID        AS PatternDetailId,

/* =====================
   PatternDetailDTO (CHILD)
   ===================== */
PD.PATTERNID              AS PatternId,
P.PATTERNCODE            AS PatternCode,  
P.PATTERNNAME            AS PatternName,  
PD.SLNO                   AS PatternDetailSlNo,
PD.ITEMID                 AS ItemId,
DI.ITEMCODE               AS ItemCode,
DI.ITEMNAME               AS ItemName,
DI.STOCKUOMID             AS ItemStockUOMId,
DU.UOMCODE                AS ItemStockUOMCode,
PD.SKUID                  AS SKUId,
S.SKUCODE                 AS SKUCode,
S.SKUNAME                 AS SKUName,
PD.QUANTITY               AS PatternDetailQuantity,
PD.WEIGHT                 AS PatternDetailWeight,
PD.TOTALWEIGHT            AS PatternDetailTotalWeight,
PD.IOTYPE                 AS PatternDetailIOType,
PD.PATTERNCOREID          AS PatternCoreId,
PC.PATTERNCORECODE        AS PatternCoreCode,
PC.PATTERNCORENAME        AS PatternCoreName,
PD.NOPERBOX               AS NoperBox,
PC.COREWEIGHT           AS CoreWeight,
PC.CORETYPEID           AS CoreTypeId,
CT.GCMCODE           AS CoreTypeCode,
CT.GCMNAME           AS CoreTypeName,
PC.TYPEOFCOATINGID    AS TypeOfCoatingId,
TC.GCMCODE         AS TypeOfCoatingCode,
TC.GCMNAME          AS TypeOfCoatingName

FROM MPATTERN P
LEFT JOIN MPATTERNDETAIL PD ON PD.PATTERNID = P.PATTERNID
LEFT JOIN MPARTY PT ON PT.PARTYID = P.PARTYID
LEFT JOIN MPARAMETERSET PS ON PS.PARAMETERSETID = P.PARAMETERSETID
LEFT JOIN MUOM U ON U.UOMID = P.UOMID
LEFT JOIN MGCM G ON G.GCMID = P.GRADEID
LEFT JOIN MITEM PI ON PI.ITEMID = P.ITEMID
LEFT JOIN MGCM MN ON MN.GCMID = P.MATCHPLATEMATERIALID
LEFT JOIN MGCM MNA ON MNA.GCMID = P.PATTERNMETHODID
LEFT JOIN MPARTY MNB ON MNB.PARTYID = P.PLATEOWNERID

-- Pattern Materials
LEFT JOIN MGCM PM ON PM.GCMID = P.PATTERNMATERIALID
LEFT JOIN MGCM PMT ON PMT.GCMID = P.PATTERNMATERIALTOPID
LEFT JOIN MGCM PMB ON PMB.GCMID = P.PATTERNMATERIALBOTTOMID

-- Parameter Sets
LEFT JOIN MPARAMETERSET GPS ON GPS.PARAMETERSETID = P.GRADEPARAMETERSETID
LEFT JOIN MPARAMETERSET PPS ON PPS.PARAMETERSETID = P.POURINGPARAMETERSETID
LEFT JOIN MPARAMETERSET KPS ON KPS.PARAMETERSETID = P.KNOCKOUTPARAMETERSETID

-- PatternDetail Joins
LEFT JOIN MITEM DI ON DI.ITEMID = PD.ITEMID
LEFT JOIN MUOM DU ON DU.UOMID = DI.STOCKUOMID
LEFT JOIN MSKU S ON S.SKUID = PD.SKUID
LEFT JOIN MPATTERNCORE PC ON PC.PATTERNCOREID = PD.PATTERNCOREID
LEFT JOIN MGCM CT ON CT.GCMID = PC.CORETYPEID
LEFT JOIN MGCM TC ON TC.GCMID = PC.TYPEOFCOATINGID

WHERE P.PATTERNID = @PatternId;

";

        public const string GET_ITEM_PATTERN = @"SELECT
/* =====================
   PatternDTO (PARENT)
   ===================== */
P.PATTERNID               AS PatternId,
P.PATTERNCODE             AS PatternCode,
P.PATTERNNAME             AS PatternName,
P.PATTERNTYPE             AS PatternType,

P.PARTYID                 AS PartyId,
PT.PARTYCODE              AS PartyCode,
PT.PARTYNAME              AS PartyName,

P.PARAMETERSETID          AS ParameterSetId,
PS.PARAMETERSETCODE       AS ParameterSetCode,
PS.PARAMETERSETNAME       AS ParameterSetName,

P.POURINGWEIGHT           AS PatternPouringWeight,
P.TOTALWEIGHT             AS PatternTotalWeight,
P.RUNNERWEIGHT            AS PatternRunnerWeight,

P.UOMID                   AS UOMId,
U.UOMCODE                 AS UOMCode,
U.UOMNAME                 AS UOMName,

P.GRADEID                 AS GradeId,
G.GCMCODE                 AS GradeCode,
G.GCMNAME                 AS GradeName,

P.REMARKS                 AS PatternRemarks,
P.SOURCETYPE              AS PatternSourceType,
P.SORTORDER               AS PatternSortOrder,
P.STATUS                  AS PatternStatus,
P.VERSION                 AS PatternVersion,

P.CREATEDBYID             AS PatternCreatedById,
P.CREATEDON               AS PatternCreatedOn,
P.MODIFIEDBYID            AS PatternModifiedById,
P.MODIFIEDON              AS PatternModifiedOn,

P.NATURE                  AS PatternNature,
P.LIFECOUNT               AS PatternLifeCount,
P.START                   AS PatternStart,
P.OPENINGCOUNT            AS PatternOpeningCount,
P.TRANSACTIONCOUNT        AS PatternTransactionCount,
P.BALANCECOUNT            AS PatternBalanceCount,

COALESCE(P.PATTERNSTATUS,0)        AS PatternApproveStatus,
COALESCE(P.YIELDPERCENTAGE,0.0)   AS PatternYieldPercentage,
COALESCE(P.BOXSIZE,'')             AS PatternBoxSize,
COALESCE(P.SPRUCECUPSIZE,'')       AS PatternSpruceCupSize,
P.FIRSTSAMPLEMADEON               AS FirstSampleMadeOn,
COALESCE(P.CORELABOURCHARGE,0.0)  AS PatternCoreLabourCharge,
COALESCE(P.NUMBEROFCOREBOX,0)     AS PatternNumberofCoreBox,

P.GRADEPARAMETERSETID     AS GradeParameterSetId,
GPS.PARAMETERSETCODE      AS GradeParameterSetCode,
GPS.PARAMETERSETNAME      AS GradeParameterSetName,

P.POURINGPARAMETERSETID   AS PouringParameterSetId,
PPS.PARAMETERSETCODE      AS PouringParameterSetCode,
PPS.PARAMETERSETNAME      AS PouringParameterSetName,

P.KNOCKOUTPARAMETERSETID  AS KnockoutParameterSetId,
KPS.PARAMETERSETCODE      AS KnockoutParameterSetCode,
KPS.PARAMETERSETNAME      AS KnockoutParameterSetName,

P.PATTERNMATERIALID       AS PatternMaterialId,
COALESCE(PM.MATERIALCODE,'NONE') AS PatternMaterialCode,
COALESCE(PM.MATERIALNAME,'NONE') AS PatternMaterialName,

P.PATTERNMATERIALTOPID    AS PatternMaterialTopId,
COALESCE(PMT.MATERIALCODE,'NONE') AS PatternMaterialTopCode,
COALESCE(PMT.MATERIALNAME,'NONE') AS PatternMaterialTopName,

P.PATTERNMATERIALBOTTOMID AS PatternMaterialBottomId,
COALESCE(PMB.MATERIALCODE,'NONE') AS PatternMaterialBottomCode,
COALESCE(PMB.MATERIALNAME,'NONE') AS PatternMaterialBottomName,

P.PATTERNMETHODID         AS PatternMethodId,
P.NUMBEROFCAVITY          AS NumberOfCavity,
P.NUMBEROFWORKINGCAVITY   AS NumberOfWorkingCavity,
P.PLATEOWNERSHIPTYPE      AS PlateOwnershipType,
P.PLATEOWNERID            AS PlateOwnerId,
P.MATCHPLATEMATERIALID    AS MatchPlateMaterialId,
P.MATCHPLATESIZE          AS MatchPlateSize,
P.PATTERNLOCATION         AS PatternLocation,
P.PLATEREMARKS            AS PlateRemarks,

P.ITEMID                  AS ItemId,
PI.ITEMCODE               AS ItemCode,
PI.ITEMNAME               AS ItemName,

P.TENANTID                AS TenantId,

/* ========= SPLIT POINT (MANDATORY) ========= */
PD.PATTERNDETAILID        AS PatternDetailId,

/* =====================
   PatternDetailDTO (CHILD)
   ===================== */
PD.PATTERNID              AS PatternId,
PD.SLNO                   AS PatternDetailSlNo,
PD.ITEMID                 AS ItemId,
DI.ITEMCODE               AS ItemCode,
DI.ITEMNAME               AS ItemName,
DI.STOCKUOMID             AS ItemStockUOMId,
DU.UOMCODE                AS ItemStockUOMCode,
PD.SKUID                  AS SKUId,
S.SKUCODE                 AS SKUCode,
S.SKUNAME                 AS SKUName,
PD.QUANTITY               AS PatternDetailQuantity,
PD.WEIGHT                 AS PatternDetailWeight,
PD.TOTALWEIGHT            AS PatternDetailTotalWeight,
PD.IOTYPE                 AS PatternDetailIOType,
PD.PATTERNCOREID          AS PatternCoreId,
PD.NOPERBOX               AS NoperBox,
COALESCE(PC.COREWEIGHT,0) AS CoreWeight,
COALESCE(PC.CORETYPEID,0) AS CoreTypeId,
COALESCE(CT.GCMCODE,'NONE') AS CoreTypeCode,
COALESCE(CT.GCMNAME,'NONE') AS CoreTypeName,
COALESCE(PC.TYPEOFCOATINGID,0) AS TypeOfCoatingId,
COALESCE(TC.GCMCODE,'NONE') AS TypeOfCoatingCode,
COALESCE(TC.GCMNAME,'NONE') AS TypeOfCoatingName


FROM MPATTERN P
LEFT JOIN MPATTERNDETAIL PD ON PD.PATTERNID = P.PATTERNID
LEFT JOIN MPARTY PT ON PT.PARTYID = P.PARTYID
LEFT JOIN MPARAMETERSET PS ON PS.PARAMETERSETID = P.PARAMETERSETID
LEFT JOIN MUOM U ON U.UOMID = P.UOMID
LEFT JOIN MGCM G ON G.GCMID = P.GRADEID
LEFT JOIN MITEM PI ON PI.ITEMID = P.ITEMID

-- Pattern Materials
LEFT JOIN MGCM PM ON PM.MATERIALID = P.PATTERNMATERIALID
LEFT JOIN MGCM PMT ON PMT.MATERIALID = P.PATTERNMATERIALTOPID
LEFT JOIN MGCM PMB ON PMB.MATERIALID = P.PATTERNMATERIALBOTTOMID

-- Parameter Sets
LEFT JOIN MPARAMETERSET GPS ON GPS.PARAMETERSETID = P.GRADEPARAMETERSETID
LEFT JOIN MPARAMETERSET PPS ON PPS.PARAMETERSETID = P.POURINGPARAMETERSETID
LEFT JOIN MPARAMETERSET KPS ON KPS.PARAMETERSETID = P.KNOCKOUTPARAMETERSETID

-- PatternDetail Joins
LEFT JOIN MITEM DI ON DI.ITEMID = PD.ITEMID
LEFT JOIN MUOM DU ON DU.UOMID = DI.STOCKUOMID
LEFT JOIN MSKU S ON S.SKUID = PD.SKUID
LEFT JOIN MPATTERNCORE PC ON PC.PATTERNCOREID = PD.PATTERNCOREID
LEFT JOIN MGCM CT ON CT.GCMID = PC.CORETYPEID
LEFT JOIN MGCM TC ON TC.GCMID = PC.TYPEOFCOATINGID
WHERE P.ITEMID = @itemid;

";

        public const string SAVE_PATTERN_PARENT = @"
INSERT INTO MPATTERN
(
    PATTERNID,
    PATTERNCODE,
    PATTERNNAME,
    PATTERNTYPE,
    PARTYID,
    PARAMETERSETID,
    POURINGWEIGHT,
    UOMID,
    GRADEID,
    TOTALWEIGHT,
    RUNNERWEIGHT,
    REMARKS,
    SORTORDER,
    STATUS,
    VERSION,
    SOURCETYPE,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    ITEMID,
    GRADEPARAMETERSETID,
    POURINGPARAMETERSETID,
    KNOCKOUTPARAMETERSETID,
    PATTERNMATERIALID,
    PATTERNMATERIALTOPID,
    PATTERNMATERIALBOTTOMID,
    YIELDPERCENTAGE,
    BOXSIZE,
    SPRUCECUPSIZE,
    FIRSTSAMPLEMADEON,
    CORELABOURCHARGE,
    NUMBEROFCOREBOX,
    TENANTID,
    NATURE,
    LIFECOUNT,
    START,
    OPENINGCOUNT,
    TRANSACTIONCOUNT,
    BALANCECOUNT,
    PATTERNMETHODID,
    NUMBEROFCAVITY,
    NUMBEROFWORKINGCAVITY,
    PLATEOWNERSHIPTYPE,
    PLATEOWNERID,
    MATCHPLATEMATERIALID,
    MATCHPLATESIZE,
    PATTERNLOCATION,
    PLATEREMARKS
)
VALUES
(
    @PatternId,
    @PatternCode,
    @PatternName,
    @PatternType,
    @PartyId,
    @ParameterSetId,
    @PatternPouringWeight,
    @UOMId,
    @GradeId,
    @PatternTotalWeight,
    @PatternRunnerWeight,
    @PatternRemarks,
    @PatternSortOrder,
    @PatternStatus,
    @PatternVersion,
    @PatternSourceType,
    @PatternCreatedById,
    @PatternCreatedOn,
    @PatternModifiedById,
    @PatternModifiedOn,
    @ItemId,
    @GradeParameterSetId,
    @PouringParameterSetId,
    @KnockoutParameterSetId,
    @PatternMaterialId,
    @PatternMaterialTopId,
    @PatternMaterialBottomId,
    @PatternYieldPercentage,
    @PatternBoxSize,
    @PatternSpruceCupSize,
    @FirstSampleMadeOn,
    @PatternCoreLabourCharge,
    @PatternNumberofCoreBox,
    @TenantId,
    @PatternNature,
    @PatternLifeCount,
    @PatternStart,
    @PatternOpeningCount,
    @PatternTransactionCount,
    @PatternBalanceCount,
    @PatternMethodId,
    @NumberOfCavity,
    @NumberOfWorkingCavity,
    @PlateOwnershipType,
    @PlateOwnerId,
    @MatchPlateMaterialId,
    @MatchPlateSize,
    @PatternLocation,
    @PlateRemarks
);
";



        public const string SAVE_PATTERN_CHILD = @"
INSERT INTO MPATTERNDETAIL
(
    PATTERNDETAILID,
    PATTERNID,
    SLNO,
    ITEMID,
    SKUID,
    QUANTITY,
    WEIGHT,
    TOTALWEIGHT,
    IOTYPE,
    PATTERNCOREID,
    NOPERBOX
)
VALUES
(
    @PatternDetailId,
    @PatternId,
    @PatternDetailSlNo,
    @ItemId,
    @SKUId,
    @PatternDetailQuantity,
    @PatternDetailWeight,
    @PatternDetailTotalWeight,
    @PatternDetailIOType,
    @PatternCoreId,
    @NoPerBox
);
";



        public const string UPDATE_PATTERN_PARENT = @"UPDATE MPATTERN
SET
    PATTERNCODE = @PatternCode,
    PATTERNNAME = @PatternName,
    PATTERNTYPE = @PatternType,
    PARTYID = @PartyId,
    PARAMETERSETID = @ParameterSetId,
    POURINGWEIGHT = @PatternPouringWeight,
    UOMID = @UOMId,
    GRADEID = @GradeId,
    TOTALWEIGHT = @PatternTotalWeight,
    RUNNERWEIGHT = @PatternRunnerWeight,
    REMARKS = @PatternRemarks,
    SORTORDER = @PatternSortOrder,
    STATUS = @PatternStatus,
    VERSION = @PatternVersion,
    SOURCETYPE = @PatternSourceType,
    MODIFIEDBYID = @PatternModifiedById,
    MODIFIEDON = @PatternModifiedOn,
    ITEMID = @ItemId,
    GRADEPARAMETERSETID = @GradeParameterSetId,
    POURINGPARAMETERSETID = @PouringParameterSetId,
    KNOCKOUTPARAMETERSETID = @KnockoutParameterSetId,
    PATTERNMATERIALID = @PatternMaterialId,
    PATTERNMATERIALTOPID = @PatternMaterialTopId,
    PATTERNMATERIALBOTTOMID = @PatternMaterialBottomId,
    YIELDPERCENTAGE = @PatternYieldPercentage,
    BOXSIZE = @PatternBoxSize,
    SPRUCECUPSIZE = @PatternSpruceCupSize,
    FIRSTSAMPLEMADEON = @FirstSampleMadeOn,
    CORELABOURCHARGE = @PatternCoreLabourCharge,
    NUMBEROFCOREBOX = @PatternNumberofCoreBox,
    TENANTID = @TenantId,
    NATURE = @PatternNature,
    LIFECOUNT = @PatternLifeCount,
    START = @PatternStart,
    OPENINGCOUNT = @PatternOpeningCount,
    TRANSACTIONCOUNT = @PatternTransactionCount,
    BALANCECOUNT = @PatternBalanceCount,
    PATTERNMETHODID = @PatternMethodId,
    NUMBEROFCAVITY = @NumberOfCavity,
    NUMBEROFWORKINGCAVITY = @NumberOfWorkingCavity,
    PLATEOWNERSHIPTYPE = @PlateOwnershipType,
    PLATEOWNERID = @PlateOwnerId,
    MATCHPLATEMATERIALID = @MatchPlateMaterialId,
    MATCHPLATESIZE = @MatchPlateSize,
    PATTERNLOCATION = @PatternLocation,
    PLATEREMARKS = @PlateRemarks
WHERE PATTERNID = @PatternId;
";

        public const string UPDATE_PATTERN_CHILD = @"UPDATE MPATTERNDETAIL
SET
    SLNO = @PatternDetailSlNo,
    ITEMID = @ItemId,
    SKUID = @SKUId,
    QUANTITY = @PatternDetailQuantity,
    WEIGHT = @PatternDetailWeight,
    TOTALWEIGHT = @PatternDetailTotalWeight,
    IOTYPE = @PatternDetailIOType,
    PATTERNCOREID = @PatternCoreId,
    NOPERBOX = @NoPerBox
WHERE PATTERNDETAILID = @PatternDetailId;
";

        public const string DELETE_PATTERN = @"
DELETE FROM MPATTERNDETAIL
WHERE PATTERNID = @PatternId;

DELETE FROM MPATTERN
WHERE PATTERNID = @PatternId;
";

        public const string GET_SELECT_LIST_PATTERN = @"
SELECT
    P.PATTERNID               AS Id,
    P.PATTERNCODE             AS Code,
    P.PATTERNNAME             AS Name,

    P.PARAMETERSETID          AS ParameterSetId,
    PS.PARAMETERSETNAME       AS ParameterSetName,

    P.GRADEPARAMETERSETID     AS GradeParameterSetId,
    GPS.PARAMETERSETNAME      AS GradeParameterSetName,

    P.POURINGPARAMETERSETID   AS PouringParameterSetId,
    PPS.PARAMETERSETNAME      AS PouringParameterSetName,

    P.KNOCKOUTPARAMETERSETID  AS KnockoutParameterSetId,
    KPS.PARAMETERSETNAME      AS KnockoutParameterSetName
FROM MPATTERN P
LEFT JOIN MPARAMETERSET PS   ON PS.PARAMETERSETID = P.PARAMETERSETID
LEFT JOIN MPARAMETERSET GPS  ON GPS.PARAMETERSETID = P.GRADEPARAMETERSETID
LEFT JOIN MPARAMETERSET PPS  ON PPS.PARAMETERSETID = P.POURINGPARAMETERSETID
LEFT JOIN MPARAMETERSET KPS  ON KPS.PARAMETERSETID = P.KNOCKOUTPARAMETERSETID
WHERE P.STATUS = 1
ORDER BY P.PATTERNNAME;
";

        public const string GET_PATTERN_BASED_ITEM = @"
SELECT DISTINCT
    mp.PATTERNID               AS Id,
    mp.PATTERNCODE             AS Code,
    mp.PATTERNNAME             AS Name,

    mp.PARAMETERSETID          AS ParameterSetId,
    PS.PARAMETERSETNAME        AS ParameterSetName,

    mp.GRADEPARAMETERSETID     AS GradeParameterSetId,
    GPS.PARAMETERSETNAME       AS GradeParameterSetName,

    mp.POURINGPARAMETERSETID   AS PouringParameterSetId,
    PPS.PARAMETERSETNAME       AS PouringParameterSetName,

    mp.KNOCKOUTPARAMETERSETID  AS KnockoutParameterSetId,
    KPS.PARAMETERSETNAME       AS KnockoutParameterSetName
FROM MPATTERN mp
INNER JOIN MPATTERNDETAIL mpd
    ON mpd.PATTERNID = mp.PATTERNID
LEFT JOIN MPARAMETERSET PS   ON PS.PARAMETERSETID = mp.PARAMETERSETID
LEFT JOIN MPARAMETERSET GPS  ON GPS.PARAMETERSETID = mp.GRADEPARAMETERSETID
LEFT JOIN MPARAMETERSET PPS  ON PPS.PARAMETERSETID = mp.POURINGPARAMETERSETID
LEFT JOIN MPARAMETERSET KPS  ON KPS.PARAMETERSETID = mp.KNOCKOUTPARAMETERSETID
WHERE mp.STATUS = 1
ORDER BY mp.PATTERNNAME;
";


        public const string GET_SELECT_LIST_PATTERN_REPORT = @"SELECT";


        public const string GET_PATTERN_LIST = @"SELECT
                                                /* =====================
                                                   PatternDTO (PARENT)
                                                   ===================== */
                                                P.PATTERNID               AS PatternId,
                                                P.PATTERNCODE             AS PatternCode,
                                                P.PATTERNNAME             AS PatternName,
                                                P.PATTERNTYPE             AS PatternType,

                                                P.PARTYID                 AS PartyId,
                                                PT.PARTYCODE              AS PartyCode,
                                                PT.PARTYNAME              AS PartyName,

                                                P.PARAMETERSETID          AS ParameterSetId,
                                                PS.PARAMETERSETCODE       AS ParameterSetCode,
                                                PS.PARAMETERSETNAME       AS ParameterSetName,

                                                P.POURINGWEIGHT           AS PatternPouringWeight,
                                                P.TOTALWEIGHT             AS PatternTotalWeight,
                                                P.RUNNERWEIGHT            AS PatternRunnerWeight,

                                                P.UOMID                   AS UOMId,
                                                U.UOMCODE                 AS UOMCode,
                                                U.UOMNAME                 AS UOMName,

                                                P.GRADEID                 AS GradeId,
                                                G.GCMCODE                 AS GradeCode,
                                                G.GCMNAME                 AS GradeName,

                                                P.REMARKS                 AS PatternRemarks,
                                                P.SOURCETYPE              AS PatternSourceType,
                                                P.SORTORDER               AS PatternSortOrder,
                                                P.STATUS                  AS PatternStatus,
                                                P.VERSION                 AS PatternVersion,

                                                P.CREATEDBYID             AS PatternCreatedById,
                                                P.CREATEDON               AS PatternCreatedOn,
                                                P.MODIFIEDBYID            AS PatternModifiedById,
                                                P.MODIFIEDON              AS PatternModifiedOn,

                                                P.NATURE                  AS PatternNature,
                                                P.LIFECOUNT               AS PatternLifeCount,
                                                P.START                   AS PatternStart,
                                                P.OPENINGCOUNT            AS PatternOpeningCount,
                                                P.TRANSACTIONCOUNT        AS PatternTransactionCount,
                                                P.BALANCECOUNT            AS PatternBalanceCount,

                                                COALESCE(P.PATTERNSTATUS,0)        AS PatternApproveStatus,
                                                COALESCE(P.YIELDPERCENTAGE,0.0)   AS PatternYieldPercentage,
                                                COALESCE(P.BOXSIZE,'')             AS PatternBoxSize,
                                                COALESCE(P.SPRUCECUPSIZE,'')       AS PatternSpruceCupSize,
                                                P.FIRSTSAMPLEMADEON               AS FirstSampleMadeOn,
                                                COALESCE(P.CORELABOURCHARGE,0.0)  AS PatternCoreLabourCharge,
                                                COALESCE(P.NUMBEROFCOREBOX,0)     AS PatternNumberofCoreBox,

                                                P.GRADEPARAMETERSETID     AS GradeParameterSetId,
                                                GPS.PARAMETERSETCODE      AS GradeParameterSetCode,
                                                GPS.PARAMETERSETNAME      AS GradeParameterSetName,

                                                P.POURINGPARAMETERSETID   AS PouringParameterSetId,
                                                PPS.PARAMETERSETCODE      AS PouringParameterSetCode,
                                                PPS.PARAMETERSETNAME      AS PouringParameterSetName,

                                                P.KNOCKOUTPARAMETERSETID  AS KnockoutParameterSetId,
                                                KPS.PARAMETERSETCODE      AS KnockoutParameterSetCode,
                                                KPS.PARAMETERSETNAME      AS KnockoutParameterSetName,

                                                P.PATTERNMATERIALID       AS PatternMaterialId,
                                                PM.GCMCODE AS PatternMaterialCode,
                                                PM.GCMNAME AS PatternMaterialName,

                                                P.PATTERNMATERIALTOPID    AS PatternMaterialTopId,
                                                PMT.GCMCODE AS PatternMaterialTopCode,
                                                PMT.GCMNAME AS PatternMaterialTopName,

                                                P.PATTERNMATERIALBOTTOMID AS PatternMaterialBottomId,
                                                PMB.GCMCODE AS PatternMaterialBottomCode,
                                                PMB.GCMNAME AS PatternMaterialBottomName,

                                                P.PATTERNMETHODID         AS PatternMethodId,
                                                P.NUMBEROFCAVITY          AS NumberOfCavity,
                                                P.NUMBEROFWORKINGCAVITY   AS NumberOfWorkingCavity,
                                                P.PLATEOWNERSHIPTYPE      AS PlateOwnershipType,
                                                P.PLATEOWNERID            AS PlateOwnerId,
                                                P.MATCHPLATEMATERIALID    AS MatchPlateMaterialId,
                                                P.MATCHPLATESIZE          AS MatchPlateSize,
                                                P.PATTERNLOCATION         AS PatternLocation,
                                                P.PLATEREMARKS            AS PlateRemarks,

                                                MN.GCMCODE				AS MatchPlateMaterialCode,
                                                MN.GCMNAME				AS MatchPlateMaterialName,

                                                MNA.GCMCODE				AS PatternMethodCode,
                                                MNA.GCMNAME				AS PatternMethodName,

                                                MNB.PARTYCODE			AS PlateOwnerCode,
                                                MNB.PARTYNAME			AS PlateOwnerName,

                                                P.ITEMID                  AS ItemId,
                                                PI.ITEMCODE               AS ItemCode,
                                                PI.ITEMNAME               AS ItemName,

                                                P.TENANTID                AS TenantId,

                                                /* ========= SPLIT POINT (MANDATORY) ========= */
                                                PD.PATTERNDETAILID        AS PatternDetailId,

                                                            /* =====================
                                                               PatternDetailDTO (CHILD)
                                                               ===================== */
                                                            PD.PATTERNID              AS PatternId,
                                                            P.PATTERNCODE            AS PatternCode,  
                                                            P.PATTERNNAME            AS PatternName,  
                                                            PD.SLNO                   AS PatternDetailSlNo,
                                                            PD.ITEMID                 AS ItemId,
                                                            DI.ITEMCODE               AS ItemCode,
                                                            DI.ITEMNAME               AS ItemName,
                                                            DI.STOCKUOMID             AS ItemStockUOMId,
                                                            DU.UOMCODE                AS ItemStockUOMCode,
                                                            PD.SKUID                  AS SKUId,
                                                            S.SKUCODE                 AS SKUCode,
                                                            S.SKUNAME                 AS SKUName,
                                                            PD.QUANTITY               AS PatternDetailQuantity,
                                                            PD.WEIGHT                 AS PatternDetailWeight,
                                                            PD.TOTALWEIGHT            AS PatternDetailTotalWeight,
                                                            PD.IOTYPE                 AS PatternDetailIOType,
                                                            PD.PATTERNCOREID          AS PatternCoreId,
                                                            PC.PATTERNCORECODE        AS PatternCoreCode,
                                                            PC.PATTERNCORENAME        AS PatternCoreName,
                                                            PD.NOPERBOX               AS NoperBox,
                                                            PC.COREWEIGHT           AS CoreWeight,
                                                            PC.CORETYPEID           AS CoreTypeId,
                                                            CT.GCMCODE           AS CoreTypeCode,
                                                            CT.GCMNAME           AS CoreTypeName,
                                                            PC.TYPEOFCOATINGID    AS TypeOfCoatingId,
                                                            TC.GCMCODE         AS TypeOfCoatingCode,
                                                            TC.GCMNAME          AS TypeOfCoatingName

                                                FROM MPATTERN P
                                                LEFT JOIN MPATTERNDETAIL PD ON PD.PATTERNID = P.PATTERNID
                                                LEFT JOIN MPARTY PT ON PT.PARTYID = P.PARTYID
                                                LEFT JOIN MPARAMETERSET PS ON PS.PARAMETERSETID = P.PARAMETERSETID
                                                LEFT JOIN MUOM U ON U.UOMID = P.UOMID
                                                LEFT JOIN MGCM G ON G.GCMID = P.GRADEID
                                                LEFT JOIN MITEM PI ON PI.ITEMID = P.ITEMID
                                                LEFT JOIN MGCM MN ON MN.GCMID = P.MATCHPLATEMATERIALID
                                                LEFT JOIN MGCM MNA ON MNA.GCMID = P.PATTERNMETHODID
                                                LEFT JOIN MPARTY MNB ON MNB.PARTYID = P.PLATEOWNERID

                                                -- Pattern Materials
                                                LEFT JOIN MGCM PM ON PM.GCMID = P.PATTERNMATERIALID
                                                LEFT JOIN MGCM PMT ON PMT.GCMID = P.PATTERNMATERIALTOPID
                                                LEFT JOIN MGCM PMB ON PMB.GCMID = P.PATTERNMATERIALBOTTOMID

                                                -- Parameter Sets
                                                LEFT JOIN MPARAMETERSET GPS ON GPS.PARAMETERSETID = P.GRADEPARAMETERSETID
                                                LEFT JOIN MPARAMETERSET PPS ON PPS.PARAMETERSETID = P.POURINGPARAMETERSETID
                                                LEFT JOIN MPARAMETERSET KPS ON KPS.PARAMETERSETID = P.KNOCKOUTPARAMETERSETID

                                                -- PatternDetail Joins
                                                LEFT JOIN MITEM DI ON DI.ITEMID = PD.ITEMID
                                                LEFT JOIN MUOM DU ON DU.UOMID = DI.STOCKUOMID
                                                LEFT JOIN MSKU S ON S.SKUID = PD.SKUID
                                                LEFT JOIN MPATTERNCORE PC ON PC.PATTERNCOREID = PD.PATTERNCOREID
                                                LEFT JOIN MGCM CT ON CT.GCMID = PC.CORETYPEID
                                                LEFT JOIN MGCM TC ON TC.GCMID = PC.TYPEOFCOATINGID;";

    }
}