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

namespace FrameworkDAL.Query.GCM
{
    public static class GCMQB
    {
        public const string PICKLIST_GCMSELECTLIST_SORT_ORDER = @"SELECT
                                                    gcm.GCMID AS Id,
                                                    gcm.GCMCODE AS Code,
                                                    gcm.GCMNAME AS Name,
                                                    gcm.REMARKS AS Remarks
                                                FROM
                                                    MGCM gcm
                                                WHERE
                                                    1 = 1";

        public const string PICKLIST_GCMSELECTLIST_COUNT = @"SELECT COUNT(*)
                                                FROM MGCM gcm
                                                WHERE 1 = 1";
        public const string Get_GCM = @"
        
        SELECT
    MG.GCMID               AS GcmId,
    MG.VERSION             AS GcmVersion,
    MG.GCMCODE             AS GcmCode,
    MG.GCMNAME             AS GcmName,
    MG.COLORID             AS GcmColor,
    MG.REMARKS             AS GcmRemarks,
    MG.STATUS              AS GcmStatus,
    MG.SORTORDER           AS GcmSortOrder,
    MG.CREATEDBYID         AS GcmCreatedById,
    MG.CREATEDON           AS GcmCreatedOn,
    MG.MODIFIEDBYID        AS GcmModifiedById,
    MG.MODIFIEDON          AS GcmModifiedOn,
    MG.SOURCETYPE          AS GcmSourceType,
    MG.GCMTYPEID           AS GcmTypeId,

    GT.GCMTYPECODE         AS GcmTypeCode,
    GT.GCMTYPEDESCRIPTION  AS GcmTypeName,         -- Used as both Name & Description
    GT.GCMTYPEDESCRIPTION  AS GcmTypeDescription,

    CB.EMPLOYEENAME        AS GcmCreatedByName,
    MB.EMPLOYEENAME        AS GcmModifiedByName

FROM MGCM MG
LEFT JOIN MGCMTYPE GT     ON GT.GCMTYPEID = MG.GCMTYPEID
LEFT JOIN MEMPLOYEE CB    ON CB.EMPLOYEEID = MG.CREATEDBYID
LEFT JOIN MEMPLOYEE MB    ON MB.EMPLOYEEID = MG.MODIFIEDBYID
WHERE MG.GCMID = @gcmid;";

        public const string SAVE_GCM = @"
INSERT INTO MGCM (
    GCMID,
    VERSION,
    GCMCODE,
    GCMNAME,
    COLORID,
    REMARKS,
    STATUS,
    SORTORDER,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SOURCETYPE,
    GCMTYPEID
)
VALUES (
    @GcmId,
    @GcmVersion,
    @GcmCode,
    @GcmName,
    @GcmColor,
    @GcmRemarks,
    @GcmStatus,
    @GcmSortOrder,
    @GcmCreatedById,
    @GcmCreatedOn,
    @GcmModifiedById,
    @GcmModifiedOn,
    @GcmSourceType,
    @GcmTypeId
);";

        public const string UPDATE_GCM = @"
UPDATE MGCM SET
    GCMCODE = @GcmCode,
    GCMNAME = @GcmName,
    COLORID = @GcmColor,
    REMARKS = @GcmRemarks,
    STATUS = @GcmStatus,
    SORTORDER = @GcmSortOrder,
    VERSION = VERSION + 1,
    SOURCETYPE = @GcmSourceType,
    MODIFIEDBYID = @GcmModifiedById,
    MODIFIEDON = @GcmModifiedOn,
    GCMTYPEID = @GcmTypeId
WHERE GCMID = @GcmId;
";

        public const string DELETE_GCM = @"
DELETE FROM MGCM
WHERE GCMID = @GCMId;";


    }
}