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

namespace FrameworkDAL.Query.ProgramClass
{
    public static class ProgramClassQB
    {
        public const string GET_PROGRAMCLASS = @"
    SELECT
        PC.PROGRAMCLASSID AS ProgramClassId,
        PC.PROGRAMCLASSCODE AS ProgramClassCode,
        PC.PROGRAMCLASSNAME AS ProgramClassName,
        PC.REMARKS AS ProgramClassRemarks,

        PC.ASSEMBLYID AS ProgramAssemblyId,

        PC.SECTION AS ProgramClassSection,

        PC.CREATEDBYID AS ProgramClassCreatedById,
        PC.CREATEDON AS ProgramClassCreatedOn,
        UC.USERNAME AS ProgramClassCreatedByName,

        PC.MODIFIEDBYID AS ProgramClassModifiedById,
        PC.MODIFIEDON AS ProgramClassModifiedOn,
        UM.USERNAME AS ProgramClassModifiedByName,

        PC.SORTORDER AS ProgramClassSortOrder,
        PC.STATUS AS ProgramClassStatus,
        PC.VERSION AS ProgramClassVersion,
        PC.SOURCETYPE AS ProgramClassSourceType
    FROM MPROGRAMCLASS PC
    LEFT JOIN MPROGRAMASSEMBLY PA ON PC.ASSEMBLYID = PA.ASSEMBLYID
    LEFT JOIN MUSER UC ON PC.CREATEDBYID = UC.USERID
    LEFT JOIN MUSER UM ON PC.MODIFIEDBYID = UM.USERID
    WHERE PC.PROGRAMCLASSID = @programclassid;
";

        public const string SAVE_PROGRAMCLASS = @"
            INSERT INTO MPROGRAMCLASS
            (
                PROGRAMCLASSCODE,
                PROGRAMCLASSNAME,
                ASSEMBLYID,
                SECTION,
                REMARKS,
                SORTORDER,
                STATUS,
                VERSION,
                SOURCETYPE,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON
            )
            VALUES
            (
                @ProgramClassCode,
                @ProgramClassName,
                @ProgramAssemblyId,
                @ProgramClassSection,
                @ProgramClassRemarks,
                @ProgramClassSortOrder,
                @ProgramClassStatus,
                @ProgramClassVersion,
                @ProgramClassSourceType,
                @ProgramClassCreatedById,
                @ProgramClassCreatedOn,
                @ProgramClassModifiedById,
                @ProgramClassModifiedOn
            );
        ";

        public const string UPDATE_PROGRAMCLASS = @"
            UPDATE MPROGRAMCLASS
            SET
                PROGRAMCLASSCODE = @ProgramClassCode,
                PROGRAMCLASSNAME = @ProgramClassName,
                ASSEMBLYID = @ProgramAssemblyId,
                SECTION = @ProgramClassSection,
                REMARKS = @ProgramClassRemarks,
                SORTORDER = @ProgramClassSortOrder,
                STATUS = @ProgramClassStatus,
                VERSION = @ProgramClassVersion,
                SOURCETYPE = @ProgramClassSourceType,
                MODIFIEDBYID = @ProgramClassModifiedById,
                MODIFIEDON = @ProgramClassModifiedOn
            WHERE PROGRAMCLASSID = @ProgramClassId;
        ";

        public const string DELETE_PROGRAMCLASS = @"
            DELETE FROM MPROGRAMCLASS
            WHERE PROGRAMCLASSID = @ProgramClassId;
        ";

        public const string GET_SELECTLIST_PROGRAMCLASS = @"
            SELECT 
                PROGRAMCLASSID AS Id, 
                PROGRAMCLASSCODE AS Code, 
                PROGRAMCLASSNAME AS Name
            FROM MPROGRAMCLASS
            WHERE STATUS = 1
            ORDER BY PROGRAMCLASSNAME;
        ";
    }
}