using System;

namespace FrameworkDAL.Query.Program
{
    public static class ProgramQB
    {
        public const string GET_PROGRAM = @"
                                            SELECT
                                                P.PROGRAMID AS ProgramId,
                                                P.PROGRAMNAME AS ProgramName,
                                                P.SECTION AS ProgramSection,
                                                P.REMARKS AS ProgramRemarks,

                                                P.PROGRAMTYPEID AS ProgramTypeId,
                                                PT.PROGRAMTYPECODE AS ProgramTypeCode,
                                                PT.PROGRAMTYPENAME AS ProgramTypeName,

                                                P.MODULEID AS ModuleId,
                                                M.MODULECODE AS ModuleCode,
                                                M.MODULENAME AS ModuleName,

                                                P.PROGRAMCLASSID AS ProgramClassId,
                                                PC.PROGRAMCLASSCODE AS ProgramClassCode,
                                                PC.PROGRAMCLASSNAME AS ProgramClassName,

                                                P.CREATEDBYID AS ProgramCreatedById,
                                                P.CREATEDON AS ProgramCreatedOn,
                                                UC.USERNAME AS ProgramCreatedByName,

                                                P.MODIFIEDBYID AS ProgramModifiedById,
                                                P.MODIFIEDON AS ProgramModifiedOn,
                                                UM.USERNAME AS ProgramModifiedByName,

                                                P.SORTORDER AS ProgramSortOrder,
                                                P.STATUS AS ProgramStatus,
                                                P.VERSION AS ProgramVersion,
                                                P.SOURCETYPE AS ProgramSourceType
                                            FROM MPROGRAM P
                                            LEFT JOIN MPROGRAMTYPE PT ON P.PROGRAMTYPEID = PT.PROGRAMTYPEID
                                            LEFT JOIN MMODULE M ON P.MODULEID = M.MODULEID
                                            LEFT JOIN MPROGRAMCLASS PC ON P.PROGRAMCLASSID = PC.PROGRAMCLASSID
                                            LEFT JOIN MUSER UC ON P.CREATEDBYID = UC.USERID
                                            LEFT JOIN MUSER UM ON P.MODIFIEDBYID = UM.USERID
                                            WHERE P.PROGRAMID = @programid;";


        public const string SAVE_PROGRAM = @"
            INSERT INTO MPROGRAM
            (
                PROGRAMID,
                PROGRAMNAME,
                SECTION,
                REMARKS,
                PROGRAMTYPEID,
                MODULEID,
                PROGRAMCLASSID,
                SORTORDER,
                STATUS,
                VERSION,
                SOURCETYPE,
                CREATEDBYID,
                CREATEDON,
                MODIFIEDBYID,
                MODIFIEDON
            )
            VALUES
            (
                @ProgramId, 
                @ProgramName,
                @ProgramSection,
                @ProgramRemarks,
                @ProgramTypeId,
                @ModuleId,
                @ProgramClassId,
                @ProgramSortOrder,
                @ProgramStatus,
                @ProgramVersion,
                @ProgramSourceType,
                @ProgramCreatedById,
                @ProgramCreatedOn,
                @ProgramModifiedById,
                @ProgramModifiedOn
            );
        ";

        public const string UPDATE_PROGRAM = @"
            UPDATE MPROGRAM
            SET
                PROGRAMNAME = @ProgramName,
                SECTION = @ProgramSection,
                REMARKS = @ProgramRemarks,
                PROGRAMTYPEID = @ProgramTypeId,
                MODULEID = @ModuleId,
                PROGRAMCLASSID = @ProgramClassId,
                SORTORDER = @ProgramSortOrder,
                STATUS = @ProgramStatus,
                VERSION = @ProgramVersion,
                SOURCETYPE = @ProgramSourceType,
                MODIFIEDBYID = @ProgramModifiedById,
                MODIFIEDON = @ProgramModifiedOn
            WHERE PROGRAMID = @ProgramId;
        ";

        public const string DELETE_PROGRAM = @"
            DELETE FROM MPROGRAM
            WHERE PROGRAMID = @ProgramId;
        ";

        public const string GET_SELECTLIST_PROGRAM = @"
            SELECT PROGRAMID AS Id, PROGRAMNAME AS Name
            FROM MPROGRAM
            WHERE STATUS = 1
            ORDER BY PROGRAMNAME;
        ";
    }
}