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

namespace MMDAL.Query.Machine
{
    public class MachineQB
    {
        public const string GET_MACHINE = @"SELECT
                                                M.MACHINEID                AS MachineId,
                                                M.MACHINECODE              AS MachineCode,
                                                M.MACHINENAME              AS MachineName,
                                                M.MACHINETYPEID            AS MachineTypeId,
                                                MT.MACHINETYPECODE         AS MachineTypeCode,
                                                MT.MACHINETYPENAME         AS MachineTypeName,
                                                M.CATEGORYID               AS CategoryId,
                                                IC.ITEMCATEGORYCODE        AS CategoryCode,
                                                IC.ITEMCATEGORYNAME        AS CategoryName,
                                                M.SUBCATEGORYID            AS SubCategoryId,
                                                ISC.ITEMSUBCATEGORYCODE    AS SubCategoryCode,
                                                ISC.ITEMSUBCATEGORYNAME    AS SubCategoryName,
                                                M.MODELID                  AS ModelId,
                                                MD.MODELCODE               AS ModelCode,
                                                MD.MODELNAME               AS ModelName,
                                                M.BRANDID                  AS BrandId,
                                                IB.ITEMBRANDCODE           AS BrandCode,
                                                IB.ITEMBRANDNAME           AS BrandName,
                                                M.YOM                      AS MachineYOM,
                                                M.CAPACITY                 AS MachineCapacity,
                                                M.CAPACITYUOMID            AS UOMId,
                                                U.UOMCODE                  AS UOMCode,
                                                U.UOMNAME                  AS UOMName,
                                                M.MINCAPACITY              AS MachineMinCapacity,
                                                M.MAXCAPACITY              AS MachineMaxCapacity,
                                                M.SPEED                    AS MachineSpeed,
                                                M.LOTPERDAY                AS MachineLotPerDay,
                                                M.ITEMID                   AS ItemId,
                                                I.ITEMCODE                 AS ItemCode,
                                                I.ITEMNAME                 AS ItemName,
                                                M.ISCRITICAL               AS MachineIsCritical,
                                                M.SERVICESTATUS            AS MachineServiceStatus,
                                                M.SERVICEFROMDATE          AS MachineServiceFromDate,
                                                M.SERVICETODATE            AS MachineServiceToDate,
                                                M.SERVICEPROVIDERID        AS ServiceProviderId,
                                                SP.PARTYCODE               AS ServiceProviderCode,
                                                SP.PARTYNAME               AS ServiceProviderName,
                                                M.SUPPLIERID               AS SupplierId,
                                                S.PARTYCODE             AS SupplierCode,
                                                S.PARTYNAME             AS SupplierName,
                                                M.BILLNUMBER               AS MachineBillNumber,
                                                M.BILLDATE                 AS MachineBillDate,
                                                M.VALUEINBILL              AS MachineValueInBill,
                                                M.PURCHASEDATE             AS MachinePurchaseDate,
                                                M.INSTALLATIONDATE         AS MachineInstallationDate,
                                                M.MACHINESTATUS            AS MachineMachineStatusValue,
                                                M.MACHINEVALUE             AS MachineMachineValue,
                                                M.MFRSLNO                  AS MachineMfrSlNo,
                                                M.WORKCENTERID             AS WorkCenterId,
                                                WC.WORKCENTERCODE          AS WorkCenterCode,
                                                WC.WORKCENTERNAME          AS WorkCenterName,
                                                M.PROCESSID                AS ProcessId,
                                                P.PROCESSCODE              AS ProcessCode,
                                                P.PROCESSNAME              AS ProcessName,
                                                M.BATCHID                  AS BatchId,
                                                BT.BATCHNUMBER             AS BatchNumber,
                                                M.ITEMLINKTYPE             AS MachineItemLinkType,
                                                M.LASTNUMBER               AS MachineLastNumber,
                                                M.EFFICIENCY               AS MachineEfficiency,
                                                M.THUMBNAIL                AS MachineThumbNail,
                                                M.CREATEDBYID              AS MachineCreatedById,
                                                M.CREATEDON                AS MachineCreatedOn,
                                                M.MODIFIEDBYID             AS MachineModifiedById,
                                                M.MODIFIEDON               AS MachineModifiedOn,
                                                U1.USERNAME                AS MachineCreatedByName,
                                                U2.USERNAME                AS MachineModifiedByName,
                                                M.SORTORDER                AS MachineSortOrder,
                                                M.STATUS                   AS MachineStatus,
                                                M.VERSION                  AS MachineVersion,
                                                M.OUID                     AS OrganizationUnitId,
                                                OU.ORGANIZATIONUNITCODE    AS OrganizationUnitCode,
                                                OU.ORGANIZATIONUNITNAME    AS OrganizationUnitName

                                            FROM MMACHINE M
                                            LEFT JOIN MMACHINETYPE MT      ON MT.MACHINETYPEID = M.MACHINETYPEID
                                            LEFT JOIN MITEMCATEGORY IC     ON IC.ITEMCATEGORYID = M.CATEGORYID
                                            LEFT JOIN MITEMSUBCATEGORY ISC ON ISC.ITEMSUBCATEGORYID = M.SUBCATEGORYID
                                            LEFT JOIN MMODEL MD            ON MD.MODELID = M.MODELID
                                            LEFT JOIN MITEMBRAND IB        ON IB.ITEMBRANDID = M.BRANDID
                                            LEFT JOIN MUOM U               ON U.UOMID = M.CAPACITYUOMID
                                            LEFT JOIN MITEM I              ON I.ITEMID = M.ITEMID
                                            LEFT JOIN MPARTY SP            ON SP.PARTYID = M.SERVICEPROVIDERID
                                            LEFT JOIN MPARTY S             ON S.PARTYID = M.SUPPLIERID
                                            LEFT JOIN MWORKCENTER WC       ON WC.WORKCENTERID = M.WORKCENTERID
                                            LEFT JOIN MPROCESS P           ON P.PROCESSID = M.PROCESSID
                                            LEFT JOIN MBATCH BT            ON BT.BATCHID = M.BATCHID
                                            LEFT JOIN MUSER U1             ON U1.USERID = M.CREATEDBYID
                                            LEFT JOIN MUSER U2             ON U2.USERID = M.MODIFIEDBYID
                                            LEFT JOIN MORGANIZATIONUNIT OU ON OU.OUID = M.OUID
                                               WHERE M.MACHINEID = @machineid;";

        public const string SAVE_MACHINE = @"INSERT INTO MMACHINE
                                                    (
                                                        MACHINEID,
                                                        MACHINECODE,
                                                        MACHINENAME,
                                                        MACHINETYPEID,
                                                        CATEGORYID,
                                                        SUBCATEGORYID,
                                                        MODELID,
                                                        BRANDID,
                                                        YOM,
                                                        CAPACITY,
                                                        CAPACITYUOMID,
                                                        MINCAPACITY,
                                                        MAXCAPACITY,
                                                        SPEED,
                                                        LOTPERDAY,
                                                        ITEMLINKTYPE,
                                                        ITEMID,
                                                        BATCHID,
                                                        ISCRITICAL,
                                                        SERVICESTATUS,
                                                        SERVICEFROMDATE,
                                                        SERVICETODATE,
                                                        SERVICEPROVIDERID,
                                                        SUPPLIERID,
                                                        BILLNUMBER,
                                                        BILLDATE,
                                                        VALUEINBILL,
                                                        PURCHASEDATE,
                                                        INSTALLATIONDATE,
                                                        MACHINESTATUS,
                                                        MACHINEVALUE,
                                                        MFRSLNO,
                                                        WORKCENTERID,
                                                        PROCESSID,
                                                        LASTNUMBER,
                                                        EFFICIENCY,
                                                        SORTORDER,
                                                        STATUS,
                                                        VERSION,
                                                        CREATEDBYID,
                                                        CREATEDON,
                                                        MODIFIEDBYID,
                                                        MODIFIEDON,
                                                        OUID,
                                                        THUMBNAIL
                                                    )
                                                    VALUES
                                                    (
                                                        @MachineId,
                                                        @MachineCode,
                                                        @MachineName,
                                                        @MachineTypeId,
                                                        @CategoryId,
                                                        @SubCategoryId,
                                                        @ModelId,
                                                        @BrandId,
                                                        @MachineYOM,
                                                        @MachineCapacity,
                                                        @UOMId,
                                                        @MachineMinCapacity,
                                                        @MachineMaxCapacity,
                                                        @MachineSpeed,
                                                        @MachineLotPerDay,
                                                        @MachineItemLinkType,
                                                        @ItemId,
                                                        @BatchId,
                                                        @MachineIsCritical,
                                                        @MachineServiceStatus,
                                                        @MachineServiceFromDate,
                                                        @MachineServiceToDate,
                                                        @ServiceProviderId,
                                                        @SupplierId,
                                                        @MachineBillNumber,
                                                        @MachineBillDate,
                                                        @MachineValueInBill,
                                                        @MachinePurchaseDate,
                                                        @MachineInstallationDate,
                                                        @MachineMachineStatusValue,
                                                        @MachineMachineValue,
                                                        @MachineMfrSlNo,
                                                        @WorkCenterId,
                                                        @ProcessId,
                                                        @MachineLastNumber,
                                                        @MachineEfficiency,
                                                        @MachineSortOrder,
                                                        @MachineStatus,
                                                        @MachineVersion,
                                                        @MachineCreatedById,
                                                        @MachineCreatedOn,
                                                        @MachineModifiedById,
                                                        @MachineModifiedOn,
                                                        @OrganizationUnitId,
                                                        @MachineThumbNail
                                                    );";


        public const string UPDATE_MACHINE = @"UPDATE MMACHINE
                                            SET
                                                MACHINECODE        = @MachineCode,
                                                MACHINENAME        = @MachineName,
                                                MACHINETYPEID      = @MachineTypeId,
                                                CATEGORYID         = @CategoryId,
                                                SUBCATEGORYID      = @SubCategoryId,
                                                MODELID            = @ModelId,
                                                BRANDID            = @BrandId,
                                                YOM                = @MachineYOM,
                                                CAPACITY           = @MachineCapacity,
                                                CAPACITYUOMID      = @UOMId,
                                                MINCAPACITY        = @MachineMinCapacity,
                                                MAXCAPACITY        = @MachineMaxCapacity,
                                                SPEED              = @MachineSpeed,
                                                LOTPERDAY          = @MachineLotPerDay,
                                                ITEMLINKTYPE       = @MachineItemLinkType,
                                                ITEMID             = @ItemId,
                                                BATCHID            = @BatchId,
                                                ISCRITICAL         = @MachineIsCritical,
                                                SERVICESTATUS      = @MachineServiceStatus,
                                                SERVICEFROMDATE    = @MachineServiceFromDate,
                                                SERVICETODATE      = @MachineServiceToDate,
                                                SERVICEPROVIDERID  = @ServiceProviderId,
                                                SUPPLIERID         = @SupplierId,
                                                BILLNUMBER         = @MachineBillNumber,
                                                BILLDATE           = @MachineBillDate,
                                                VALUEINBILL        = @MachineValueInBill,
                                                PURCHASEDATE       = @MachinePurchaseDate,
                                                INSTALLATIONDATE   = @MachineInstallationDate,
                                                MACHINESTATUS      = @MachineMachineStatusValue,
                                                MACHINEVALUE       = @MachineMachineValue,
                                                MFRSLNO            = @MachineMfrSlNo,
                                                WORKCENTERID       = @WorkCenterId,
                                                PROCESSID          = @ProcessId,
                                                LASTNUMBER         = @MachineLastNumber,
                                                EFFICIENCY         = @MachineEfficiency,
                                                SORTORDER          = @MachineSortOrder,
                                                STATUS             = @MachineStatus,
                                                VERSION            = @MachineVersion,
                                                MODIFIEDBYID       = @MachineModifiedById,
                                                MODIFIEDON         = @MachineModifiedOn,
                                                OUID               = @OrganizationUnitId,
                                                THUMBNAIL          = @MachineThumbNail
                                            WHERE
                                                MACHINEID = @MachineId;";   

        public const string DELETE_MACHINE = @"DELETE FROM MMACHINE
                                                WHERE MACHINEID = @machineid;";

        public const string GET_SELECTLIST_MACHINE = @"SELECT
                                                        MACHINEID   AS Id,
                                                        MACHINECODE AS Code,
                                                        MACHINENAME AS Name
                                                    FROM MMACHINE ORDER BY MACHINENAME;";

    }
}