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

namespace FrameworkDAL.Query.PatternCore
{
    public class PatternCoreQB
    {
        public const string GET_PATTERNCORE = @"SELECT
                                                PC.PATTERNCOREID        AS PatternCoreId,
                                                PC.PATTERNCORECODE      AS PatternCoreCode,
                                                PC.PATTERNCORENAME      AS PatternCoreName,
                                                PC.CORETYPEID           AS CoreTypeId,
                                                CT.GCMNAME              AS CoreTypeName,
                                                PC.CORECAVITY           AS CoreCavity,
                                                PC.COREOWNERSHIPTYPE    AS CoreOwnershipType,
                                                PC.PARTYID              AS PartyId,
                                                P.PARTYNAME             AS PartyName,
                                                P.PARTYCODE             AS PartyCode,
                                                PC.COREWEIGHT           AS CoreWeight,
                                                PC.CHAPLET              AS Chaplet,
                                                PC.COATINGMETHODID      AS CoatingMethodId,
                                                CM.GCMNAME              AS CoatingMethodName,
                                                PC.TYPEOFCOATINGID      AS TypeOfCoatingId,
                                                TC.GCMNAME              AS TypeOfCoatingName,
                                                PC.ITEMID               AS ItemId,
                                                I.ITEMNAME              AS ItemName,
                                                I.ITEMCODE              AS ItemCode,
                                                PC.SORTORDER            AS PatternCoreSortOrder,
                                                PC.STATUS               AS PatternCoreStatus,
                                                PC.VERSION              AS PatternCoreVersion,
                                                PC.SOURCETYPE           AS PatternCoreSourceType,
                                                PC.CREATEDBYID          AS PatternCoreCreatedById,
                                                CU.USERNAME             AS PatternCoreCreatedByName,
                                                PC.CREATEDON            AS PatternCoreCreatedOn,
                                                PC.MODIFIEDBYID         AS PatternCoreModifiedById,
                                                MU.USERNAME             AS PatternCoreModifiedByName,
                                                PC.MODIFIEDON           AS PatternCoreModifiedOn,
                                                PC.TENANTID             AS PatternCoreTenantId
                                            FROM MPATTERNCORE PC
                                            LEFT JOIN MGCM CT   ON CT.GCMID = PC.CORETYPEID
                                            LEFT JOIN MGCM CM   ON CM.GCMID = PC.COATINGMETHODID
                                            LEFT JOIN MGCM TC   ON TC.GCMID = PC.TYPEOFCOATINGID
                                            LEFT JOIN MPARTY P  ON P.PARTYID = PC.PARTYID
                                            LEFT JOIN MITEM I   ON I.ITEMID = PC.ITEMID
                                            LEFT JOIN MUSER CU  ON CU.USERID = PC.CREATEDBYID
                                            LEFT JOIN MUSER MU  ON MU.USERID = PC.MODIFIEDBYID
                                            WHERE PC.PATTERNCOREID = @patterncoreid;";

        public const string SAVE_PATTERNCORE = @"INSERT INTO MPATTERNCORE
                                                (
                                                    PATTERNCORECODE,
                                                    PATTERNCORENAME,
                                                    CORETYPEID,
                                                    CORECAVITY,
                                                    COREOWNERSHIPTYPE,
                                                    PARTYID,
                                                    COREWEIGHT,
                                                    CHAPLET,
                                                    COATINGMETHODID,
                                                    TYPEOFCOATINGID,
                                                    ITEMID,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    SOURCETYPE,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON,
                                                    TENANTID
                                                )
                                                VALUES
                                                (
                                                    @PatternCoreCode,
                                                    @PatternCoreName,
                                                    @CoreTypeId,
                                                    @CoreCavity,
                                                    @CoreOwnershipType,
                                                    @PartyId,
                                                    @CoreWeight,
                                                    @Chaplet,
                                                    @CoatingMethodId,
                                                    @TypeOfCoatingId,
                                                    @ItemId,
                                                    @PatternCoreSortOrder,
                                                    @PatternCoreStatus,
                                                    @PatternCoreVersion,
                                                    @PatternCoreSourceType,
                                                    @PatternCoreCreatedById,
                                                    @PatternCoreCreatedOn,
                                                    @PatternCoreModifiedById,
                                                    @PatternCoreModifiedOn,
                                                    @PatternCoreTenantId
                                                );";

        public const string UPDATE_PATTERNCORE = @"UPDATE MPATTERNCORE
                                                SET
                                                    PATTERNCORECODE      = @PatternCoreCode,
                                                    PATTERNCORENAME      = @PatternCoreName,
                                                    CORETYPEID           = @CoreTypeId,
                                                    CORECAVITY            = @CoreCavity,
                                                    COREOWNERSHIPTYPE     = @CoreOwnershipType,
                                                    PARTYID               = @PartyId,
                                                    COREWEIGHT            = @CoreWeight,
                                                    CHAPLET               = @Chaplet,
                                                    COATINGMETHODID       = @CoatingMethodId,
                                                    TYPEOFCOATINGID       = @TypeOfCoatingId,
                                                    ITEMID                = @ItemId,
                                                    SORTORDER             = @PatternCoreSortOrder,
                                                    STATUS                = @PatternCoreStatus,
                                                    VERSION               = @PatternCoreVersion,
                                                    SOURCETYPE            = @PatternCoreSourceType,
                                                    MODIFIEDBYID          = @PatternCoreModifiedById,
                                                    MODIFIEDON            = @PatternCoreModifiedOn,
                                                    TENANTID              = @PatternCoreTenantId
                                                WHERE
                                                    PATTERNCOREID = @PatternCoreId;";

        public const string DELETE_PATTERNCORE = @"DELETE FROM MPATTERNCORE
                                                    WHERE
                                                        PATTERNCOREID = @patterncoreid;";



        public const string GET_SELECTLIST_PATTERNCORE = @"SELECT
                                                                Id,
                                                                Code,
                                                                Name
                                                            FROM
                                                            (
                                                                SELECT
                                                                    PC.PATTERNCOREID      AS Id,
                                                                    PC.PATTERNCORECODE    AS Code,
                                                                    PC.PATTERNCORENAME    AS Name,
                                                                    ROW_NUMBER() OVER (ORDER BY PC.PATTERNCORENAME) AS RowNum
                                                                FROM dbo.MPATTERNCORE PC
                                                                WHERE
                                                                    (@Code IS NULL OR PC.PATTERNCORECODE LIKE '%' + @Code + '%')
                                                            ) AS T
                                                            WHERE
                                                                (@FirstNumber = -1 AND @MaxResult = -1)
                                                                OR (T.RowNum BETWEEN @FirstNumber AND @MaxResult)
                                                            ORDER BY
                                                                T.RowNum;";


        public const string GET_PATTERNCORE_LIST = @"SELECT
                                                PC.PATTERNCOREID        AS PatternCoreId,
                                                PC.PATTERNCORECODE      AS PatternCoreCode,
                                                PC.PATTERNCORENAME      AS PatternCoreName,
                                                PC.CORETYPEID           AS CoreTypeId,
                                                CT.GCMNAME              AS CoreTypeName,
                                                PC.CORECAVITY           AS CoreCavity,
                                                PC.COREOWNERSHIPTYPE    AS CoreOwnershipType,
                                                PC.PARTYID              AS PartyId,
                                                P.PARTYNAME             AS PartyName,
                                                P.PARTYCODE             AS PartyCode,
                                                PC.COREWEIGHT           AS CoreWeight,
                                                PC.CHAPLET              AS Chaplet,
                                                PC.COATINGMETHODID      AS CoatingMethodId,
                                                CM.GCMNAME              AS CoatingMethodName,
                                                PC.TYPEOFCOATINGID      AS TypeOfCoatingId,
                                                TC.GCMNAME              AS TypeOfCoatingName,
                                                PC.ITEMID               AS ItemId,
                                                I.ITEMNAME              AS ItemName,
                                                I.ITEMCODE              AS ItemCode,
                                                PC.SORTORDER            AS PatternCoreSortOrder,
                                                PC.STATUS               AS PatternCoreStatus,
                                                PC.VERSION              AS PatternCoreVersion,
                                                PC.SOURCETYPE           AS PatternCoreSourceType,
                                                PC.CREATEDBYID          AS PatternCoreCreatedById,
                                                CU.USERNAME             AS PatternCoreCreatedByName,
                                                PC.CREATEDON            AS PatternCoreCreatedOn,
                                                PC.MODIFIEDBYID         AS PatternCoreModifiedById,
                                                MU.USERNAME             AS PatternCoreModifiedByName,
                                                PC.MODIFIEDON           AS PatternCoreModifiedOn,
                                                PC.TENANTID             AS PatternCoreTenantId
                                            FROM MPATTERNCORE PC
                                            LEFT JOIN MGCM CT   ON CT.GCMID = PC.CORETYPEID
                                            LEFT JOIN MGCM CM   ON CM.GCMID = PC.COATINGMETHODID
                                            LEFT JOIN MGCM TC   ON TC.GCMID = PC.TYPEOFCOATINGID
                                            LEFT JOIN MPARTY P  ON P.PARTYID = PC.PARTYID
                                            LEFT JOIN MITEM I   ON I.ITEMID = PC.ITEMID
                                            LEFT JOIN MUSER CU  ON CU.USERID = PC.CREATEDBYID
                                            LEFT JOIN MUSER MU  ON MU.USERID = PC.MODIFIEDBYID";

    }
}