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

namespace MMDAL.Query.MProcessGroup
{
    public class MProcessGroupQB
    {
        public const string GET_MPROCESSGROUP = @"SELECT
                                                    MPG.PROCESSGROUPID        AS MProcessGroupId,
                                                    MPG.PROCESSGROUPCODE      AS MProcessGroupCode,
                                                    MPG.PROCESSGROUPNAME      AS MProcessGroupName,
                                                    MPG.REMARKS               AS MProcessGroupRemarks,
                                                    MPG.PRODUCTTYPE           AS MProcessGroupProductType,
                                                    MPG.PROCESSTYPE           AS MProcessGroupProcessType,
                                                    MPG.RESOURCETYPE          AS MProcessGroupResourceType,
                                                    MPG.PATTERNTYPE           AS MProcessGroupPatternType,
                                                    MPG.PARAMETERSETID        AS ParameterSetId,
                                                    PS.PARAMETERSETCODE       AS ParameterSetCode,
                                                    PS.PARAMETERSETNAME       AS ParameterSetName,
                                                    MPG.SORTORDER             AS MProcessGroupSortOrder,
                                                    MPG.STATUS                AS MProcessGroupStatus,
                                                    MPG.VERSION               AS MProcessGroupVersion,
                                                    MPG.CREATEDBYID           AS MProcessGroupCreatedById,
                                                    MPG.CREATEDON             AS MProcessGroupCreatedOn,
                                                    CU.USERNAME               AS MProcessGroupCreatedByName,
                                                    MPG.MODIFIEDBYID          AS MProcessGroupModifiedById,
                                                    MPG.MODIFIEDON            AS MProcessGroupModifiedOn,
                                                    MU.USERNAME               AS MProcessGroupModifiedByName,
                                                    MPG.SOURCETYPE            AS SourceType,
                                                    MPG.TENANTID              AS TenantId 
                                                FROM MPROCESSGROUP MPG
                                                LEFT JOIN MPARAMETERSET PS ON PS.PARAMETERSETID = MPG.PARAMETERSETID
                                                LEFT JOIN MUSER CU ON CU.USERID = MPG.CREATEDBYID
                                                LEFT JOIN MUSER MU ON MU.USERID = MPG.MODIFIEDBYID
                                                WHERE MPG.PROCESSGROUPID = @processgroupid;";

        public const string SAVE_MPROCESSGROUP = @"INSERT INTO MPROCESSGROUP
                                                    (
                                                        PROCESSGROUPID,
                                                        PROCESSGROUPCODE,
                                                        PROCESSGROUPNAME,
                                                        REMARKS,
                                                        PRODUCTTYPE,
                                                        PROCESSTYPE,
                                                        RESOURCETYPE,
                                                        PATTERNTYPE,
                                                        PARAMETERSETID,
                                                        SORTORDER,
                                                        STATUS,
                                                        VERSION,
                                                        CREATEDBYID,
                                                        CREATEDON,
                                                        MODIFIEDBYID,
                                                        MODIFIEDON,
                                                        SOURCETYPE,
                                                        TENANTID
                                                    )
                                                    VALUES
                                                    (
                                                        @MProcessGroupId,
                                                        @MProcessGroupCode,
                                                        @MProcessGroupName,
                                                        @MProcessGroupRemarks,
                                                        @MProcessGroupProductType,
                                                        @MProcessGroupProcessType,
                                                        @MProcessGroupResourceType,
                                                        @MProcessGroupPatternType,
                                                        @ParameterSetId,
                                                        @MProcessGroupSortOrder,
                                                        @MProcessGroupStatus,
                                                        @MProcessGroupVersion,
                                                        @MProcessGroupCreatedById,
                                                        @MProcessGroupCreatedOn,
                                                        @MProcessGroupModifiedById,
                                                        @MProcessGroupModifiedOn,
                                                        @SourceType,
                                                        @TenantId
                                                    );";

        public const string UPDATE_MPROCESSGROUP = @"UPDATE MPROCESSGROUP
                                                    SET
                                                        PROCESSGROUPCODE   = @MProcessGroupCode,
                                                        PROCESSGROUPNAME   = @MProcessGroupName,
                                                        REMARKS            = @MProcessGroupRemarks,
                                                        PRODUCTTYPE        = @MProcessGroupProductType,
                                                        PROCESSTYPE        = @MProcessGroupProcessType,
                                                        RESOURCETYPE       = @MProcessGroupResourceType,
                                                        PATTERNTYPE        = @MProcessGroupPatternType,
                                                        PARAMETERSETID     = @ParameterSetId,
                                                        SORTORDER          = @MProcessGroupSortOrder,
                                                        STATUS             = @MProcessGroupStatus,
                                                        VERSION            = @MProcessGroupVersion,
                                                        MODIFIEDBYID       = @MProcessGroupModifiedById,
                                                        MODIFIEDON         = @MProcessGroupModifiedOn,
                                                        SOURCETYPE         = @SourceType,
                                                        TENANTID           = @TenantId
                                                    WHERE
                                                        PROCESSGROUPID = @MProcessGroupId;";

        public const string DELETE_MPROCESSGROUP = @"DELETE FROM MPROCESSGROUP
                                                        WHERE PROCESSGROUPID = @processgroupid;";

        public const string GET_SELECTLIST_MPROCESSGROUP = @"WITH PagedMProcessGroup AS (
                                                        SELECT
                                                            PG.PROCESSGROUPID           AS Id,
                                                            PG.PROCESSGROUPCODE         AS Code,
                                                            PG.PROCESSGROUPNAME         AS Name,
                                                            ROW_NUMBER() OVER (ORDER BY PG.PROCESSGROUPID) AS RowNum
                                                        FROM 
                                                            MPROCESSGROUP PG
                                                        )
                                                    SELECT
                                                        Id,
                                                        Code,
                                                        Name
                                                    FROM PagedMProcessGroup
                                                   WHERE 
                                                    (@firstnumber = -1 AND @maxresult = -1)
                                                    OR (RowNum BETWEEN @firstnumber AND @maxresult)";
    }
}