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

namespace CostingDAL.Query.ResourceType
{
    public static class ResourceTypeQB
    {
        public const string GetSelectListResourceType = @"select
                                            RESOURCETYPEID AS Id,
                                            RESOURCETYPECODE AS Code,
                                            RESOURCETYPENAME AS Name 
                                            from MRESOURCETYPE";

        public const string GetResourceType = @"SELECT
                                        MR.RESOURCETYPEID        AS ResourceTypeId,
                                        MR.RESOURCETYPECODE      AS ResourceTypeCode,
                                        MR.RESOURCETYPENAME      AS ResourceTypeName,

                                        MR.PARENTTYPEID          AS ParentTypeId,
                                        PT.RESOURCETYPECODE      AS ParentTypeCode,
                                        PT.RESOURCETYPENAME      AS ParentTypeName,

                                        MR.REMARKS               AS ResourceTypeRemarks,

                                        MR.CAPACITYUOMID         AS CapacityUOMId,
                                        CU.UOMCODE               AS CapacityUOMCode,
                                        CU.UOMNAME               AS CapacityUOMName,

                                        MR.ISCAPACITYPLANNING    AS ResourceTypeIsCapacityPlanning,
                                        MR.ISRCCP                AS ResourceTypeIsRCCP,
                                        MR.COSTTYPE              AS ResourceTypeCostType,
                                        MR.RESOURCENATURE        AS ResourceTypeResourceNature,

                                        MR.DEPARTMENTID          AS DepartmentId,
                                        D.DEPARTMENTCODE         AS DepartmentCode,
                                        D.DEPARTMENTNAME         AS DepartmentName,

                                        MR.ISPRODUCTION          AS ResourceTypeIsProduction,

                                        MR.MACHINETYPEID         AS MachineTypeId,
                                        MT.MACHINETYPECODE       AS MachineTypeCode,
                                        MT.MACHINETYPENAME       AS MachineTypeName,

                                        MR.EMPLOYEETYPEID        AS EmployeeTypeId, 
                                        ET.GCMCODE               AS EmployeeTypeCode,
                                        ET.GCMNAME               AS EmployeeTypeName,

                                        MR.PROCESSID             AS ProcessId,
                                        P.PROCESSCODE            AS ProcessCode,
                                        P.PROCESSNAME            AS ProcessName,

                                        MR.WORKCENTERID          AS WorkCenterId,
                                        WC.WORKCENTERCODE        AS WorkCenterCode,
                                        WC.WORKCENTERNAME        AS WorkCenterName,

                                        MR.MATERIALFILTERID      AS MaterialFilterId,

                                        MR.SORTORDER             AS ResourceTypeSortOrder,
                                        MR.STATUS                AS ResourceTypeStatus,
                                        MR.VERSION               AS ResourceTypeVersion,

                                        MR.CREATEDBYID           AS ResourceTypeCreatedById,
                                        MR.CREATEDON             AS ResourceTypeCreatedOn,
                                        MUC.USERNAME             AS ResourceTypeCreatedByName,
                                        MR.MODIFIEDBYID          AS ResourceTypeModifiedById,
                                        MR.MODIFIEDON            AS ResourceTypeModifiedOn,
                                        MUM.USERNAME             AS ResourceTypeModifiedByName,

                                        MR.SOURCETYPE            AS SourceType,
                                        MR.ASSETTYPEID           AS AssetTypeId,
                                        AT.ASSETTYPECODE         AS AssetTypeCode,
                                        AT.ASSETTYPENAME         AS AssetTypeName,
                                        MR.TENANTID              AS TenantId

                                    FROM MRESOURCETYPE MR  
                                      LEFT JOIN MRESOURCETYPE PT   ON MR.PARENTTYPEID = PT.RESOURCETYPEID
                                      LEFT JOIN MUOM CU            ON MR.CAPACITYUOMID = CU.UOMID
                                      LEFT JOIN MDEPARTMENT D      ON MR.DEPARTMENTID = D.DEPARTMENTID
                                      LEFT JOIN MMACHINETYPE MT    ON MR.MACHINETYPEID = MT.MACHINETYPEID 
                                      LEFT JOIN MPROCESS P         ON MR.PROCESSID = P.PROCESSID
                                      LEFT JOIN MWORKCENTER WC     ON MR.WORKCENTERID = WC.WORKCENTERID
                                      LEFT JOIN MASSETTYPE AT      ON MR.ASSETTYPEID = AT.ASSETTYPEID 
                                      LEFT JOIN MGCM ET            ON MR.EMPLOYEETYPEID = ET.GCMID  
                                      LEFT JOIN MUSER MUC ON MR.CREATEDBYID = MUC.USERID
                                      LEFT JOIN MUSER MUM ON MR.MODIFIEDBYID = MUM.USERID
                                    WHERE MR.RESOURCETYPEID = @resourcetypeid;";

        public const string SAVE_RESOURCETYPE = @"INSERT INTO MRESOURCETYPE
                                                (
                                                    RESOURCETYPEID,
                                                    RESOURCETYPECODE,
                                                    RESOURCETYPENAME,
                                                    PARENTTYPEID,
                                                    REMARKS,
                                                    CAPACITYUOMID,
                                                    ISCAPACITYPLANNING,
                                                    ISRCCP,
                                                    COSTTYPE,
                                                    RESOURCENATURE,
                                                    DEPARTMENTID,
                                                    ISPRODUCTION,
                                                    MACHINETYPEID,
                                                    EMPLOYEETYPEID,
                                                    PROCESSID,
                                                    WORKCENTERID,
                                                    MATERIALFILTERID,
                                                    SORTORDER,
                                                    STATUS,
                                                    VERSION,
                                                    CREATEDBYID,
                                                    CREATEDON,
                                                    MODIFIEDBYID,
                                                    MODIFIEDON,
                                                    ASSETTYPEID
                                                )
                                                VALUES
                                                (
                                                    @ResourceTypeId,
                                                    @ResourceTypeCode,
                                                    @ResourceTypeName,
                                                    @ParentTypeId,
                                                    @ResourceTypeRemarks,
                                                    @CapacityUOMId,
                                                    @ResourceTypeIsCapacityPlanning,
                                                    @ResourceTypeIsRCCP,
                                                    @ResourceTypeCostType,
                                                    @ResourceTypeResourceNature,
                                                    @DepartmentId,
                                                    @ResourceTypeIsProduction,
                                                    @MachineTypeId,
                                                    @EmployeeTypeId,
                                                    @ProcessId,
                                                    @WorkCenterId,
                                                    @MaterialFilterId,
                                                    @ResourceTypeSortOrder,
                                                    @ResourceTypeStatus,
                                                    @ResourceTypeVersion,
                                                    @ResourceTypeCreatedById,
                                                    @ResourceTypeCreatedOn,
                                                    @ResourceTypeModifiedById,
                                                    @ResourceTypeModifiedOn,
                                                    @AssetTypeId
                                                );";

        public const string UPDATE_RESOURCETYPE = @"UPDATE MRESOURCETYPE
                                                    SET
                                                        RESOURCETYPECODE        = @ResourceTypeCode,
                                                        RESOURCETYPENAME        = @ResourceTypeName,
                                                        PARENTTYPEID            = @ParentTypeId,
                                                        REMARKS                 = @ResourceTypeRemarks,
                                                        CAPACITYUOMID           = @CapacityUOMId,
                                                        ISCAPACITYPLANNING      = @ResourceTypeIsCapacityPlanning,
                                                        ISRCCP                  = @ResourceTypeIsRCCP,
                                                        COSTTYPE                = @ResourceTypeCostType,
                                                        RESOURCENATURE          = @ResourceTypeResourceNature,
                                                        DEPARTMENTID            = @DepartmentId,
                                                        ISPRODUCTION            = @ResourceTypeIsProduction,
                                                        MACHINETYPEID           = @MachineTypeId,
                                                        EMPLOYEETYPEID          = @EmployeeTypeId,
                                                        PROCESSID               = @ProcessId,
                                                        WORKCENTERID            = @WorkCenterId,
                                                        MATERIALFILTERID        = @MaterialFilterId,
                                                        SORTORDER               = @ResourceTypeSortOrder,
                                                        STATUS                  = @ResourceTypeStatus,
                                                        VERSION                 = @ResourceTypeVersion,
                                                        MODIFIEDBYID            = @ResourceTypeModifiedById,
                                                        MODIFIEDON              = @ResourceTypeModifiedOn,
                                                        ASSETTYPEID             = @AssetTypeId
                                                    WHERE
                                                        RESOURCETYPEID          = @ResourceTypeId;";

        public const string DELETE_RESOURCETYPE= "DELETE FROM MRESOURCETYPE" +
            "                                       WHERE RESOURCETYPEID =@resourcetypeid;";
    }
}