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

namespace AdminDAL.Query.Department
{
    public static class DepartmentQB
    {
        public const string GET_DEPARTMENT = @"SELECT
                                D.DEPARTMENTID             AS DepartmentId,
                                D.DEPARTMENTCODE           AS DepartmentCode,
                                D.DEPARTMENTNAME           AS DepartmentName,
                                D.ISPRODUCTION             AS DepartmentIsProduction,

                                D.PROCESSID                AS ProcessId,
                                MP.PROCESSCODE             AS ProcessCode,
                                MP.PROCESSNAME             AS ProcessName,

                                D.CALENDARID               AS CalendarId,
                                MC.CALENDARCODE            AS CalendarCode,
                                MC.CALENDARNAME            AS CalendarName,

                                D.CREATEDBYID              AS DepartmentCreatedById,
                                CU.EMPLOYEENAME            AS DepartmentCreatedByName,
                                D.CREATEDON                AS DepartmentCreatedOn,

                                D.MODIFIEDBYID             AS DepartmentModifiedById,
                                MU.EMPLOYEENAME            AS DepartmentModifiedByName,
                                D.MODIFIEDON               AS DepartmentModifiedOn,

                                D.SORTORDER                AS DepartmentSortOrder,
                                D.STATUS                   AS DepartmentStatus,
                                D.VERSION                  AS DepartmentVersion,

                                D.DEPARTMENTHEADID         AS DepartmentHeadId,
                                DH.DEPARTMENTCODE          AS DepartmentHeadCode,
                                DH.DEPARTMENTNAME          AS DepartmentHeadName,

                                D.APPLICABLEOUIDS          AS DepartmentApplicableOUIds

                            FROM MDEPARTMENT D
                            LEFT JOIN MPROCESS MP ON D.PROCESSID = MP.PROCESSID
                            LEFT JOIN MCALENDAR MC ON D.CALENDARID = MC.CALENDARID
                            LEFT JOIN MDEPARTMENT DH ON D.DEPARTMENTHEADID = DH.DEPARTMENTID
                            LEFT JOIN MEMPLOYEE CU ON D.CREATEDBYID = CU.EMPLOYEEID
                            LEFT JOIN MEMPLOYEE MU ON D.MODIFIEDBYID = MU.EMPLOYEEID

                            WHERE
                            D.DEPARTMENTID = @departmentid;";

        public const string GetSelectList_Department = @"
SELECT
    md.DEPARTMENTID AS Id,
    md.DEPARTMENTCODE AS Code,
    md.DEPARTMENTNAME AS Name
FROM MDEPARTMENT md;";

        public const string SAVE_DEPARTMENT = @"INSERT INTO MDEPARTMENT
(
    DEPARTMENTID,
    DEPARTMENTCODE,
    DEPARTMENTNAME,
    ISPRODUCTION,
    PROCESSID,
    CALENDARID,
    DEPARTMENTHEADID,
    APPLICABLEOUIDS,
    SORTORDER,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON
)
VALUES
(
    @DepartmentId,
    @DepartmentCode,
    @DepartmentName,
    @DepartmentIsProduction,
    @ProcessId,
    @CalendarId,
    @DepartmentHeadId,
    @DepartmentApplicableOUIds,
    @DepartmentSortOrder,
    @DepartmentStatus,
    @DepartmentVersion,
    @DepartmentCreatedById,
    @DepartmentCreatedOn,
    @DepartmentModifiedById,
    @DepartmentModifiedOn
)";

        public const string UPDATE_DEPARTMENT = @"UPDATE MDEPARTMENT
SET
    DEPARTMENTCODE    = @DepartmentCode,
    DEPARTMENTNAME    = @DepartmentName,
    ISPRODUCTION      = @DepartmentIsProduction,
    PROCESSID         = @ProcessId,
    CALENDARID        = @CalendarId,
    DEPARTMENTHEADID  = @DepartmentHeadId,
    APPLICABLEOUIDS   = @DepartmentApplicableOUIds,
    SORTORDER         = @DepartmentSortOrder,
    STATUS            = @DepartmentStatus,
    VERSION           = @DepartmentVersion,
    MODIFIEDBYID      = @DepartmentModifiedById,
    MODIFIEDON        = @DepartmentModifiedOn
WHERE DEPARTMENTID = @DepartmentId";

        public const string DELETE_DEPARTMENT = @"DELETE FROM MDEPARTMENT
WHERE DEPARTMENTID = @DepartmentId";

    }
}