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

namespace MMDAL.Query.NestingPlan
{
    public class NestingPlanQB
    {

        public const string GET_NESTINGPLAN_BYID = @"SELECT
                                                    NP.NESTINGPLANID                  AS NestingPlanId,
                                                    NP.NESTINGPLANCODE                AS NestingPlanCode,
                                                    NP.PLANDATE                       AS NestingPlanPlanDate,
                                                    NP.PLANTYPE                       AS NestingPlanPlanType,

                                                    -- Sheet Item
                                                    NP.SHEETITEMID                    AS SheetItemId,
                                                    I.ITEMCODE                        AS SheetItemCode,
                                                    I.ITEMNAME                        AS SheetItemName,
                                                    I.STOCKUOMID                      AS SheetItemStockUOMId,
                                                    U.UOMNAME                         AS SheetItemStockUOMName,
                                                    I.ROUTINGID                       AS SheetItemRoutingId,
                                                    I.MATERIALACCOUNTID               AS SheetItemMaterialAccountId,

                                                    -- Sheet SKU
                                                    NP.SHEETSKUID                     AS SheetSKUId,
                                                    S.SKUCODE                         AS SheetSKUCode,
                                                    S.SKUNAME                         AS SheetSKUName,

                                                    -- Lot
                                                    NP.LOTID                          AS LotId,
                                                    L.LOTNUMBER                       AS LotLotNumber,

                                                    -- Pack Number
                                                    NP.PACKNUMBERID                   AS PackNumberId,
                                                    P.PACKNUMBER                      AS PackNumberNumber,

                                                    NP.SHEETDIMENSION                 AS NestingPlanSheetDimension,
                                                    NP.REFERENCENUMBER                AS NestingPlanReferenceNumber,
                                                    NP.REFERENCEDATE                  AS NestingPlanReferenceDate,
                                                    NP.SHEETWEIGHT                    AS NestingPlanSheetWeight,
                                                    NP.TOTALCOMPONENTWEIGHT           AS NestingPlanTotalComponentWeight,
                                                    NP.WASTAGE                        AS NestingPlanWastage,
                                                    NP.WASTAGEPERCENTAGE              AS NestingPlanWastagePercentage,
                                                    NP.FILEREFERENCE                  AS NestingPlanFileReference,
                                                    NP.CMSID                          AS NestingPlanCMSID,

                                                    -- Prepared By Employee
                                                    NP.PLANPREPAREDBYID               AS PlanPreparedById,
                                                    EP.EMPLOYEECODE                   AS PlanPreparedByCode,
                                                    EP.EMPLOYEENAME                   AS PlanPreparedByName,

                                                    -- Approved By Employee
                                                    NP.PLANAPPROVEDBYID               AS PlanApprovedById,
                                                    EA.EMPLOYEECODE                   AS PlanApprovedByCode,
                                                    EA.EMPLOYEENAME                   AS PlanApprovedByName,

                                                    NP.NESTINGTIME                    AS NestingPlanNestingTime,
                                                    NP.REMARKS                        AS NestingPlanRemarks,
                                                    NP.RUNLENGTH                      AS NestingPlanRunLength,
                                                    NP.SETUPTIME                      AS NestingPlanSetUpTime,
                                                    NP.CYCLETIME                      AS NestingPlanCycleTime,

                                                    NP.VERSION                        AS NestingPlanVersion,
                                                    NP.STATUS                         AS NestingPlanStatus,
                                                    NP.CREATEDBYID                    AS NestingPlanCreatedById,
                                                    UC.USERNAME                       AS NestingPlanCreatedByName,
                                                    NP.CREATEDON                      AS NestingPlanCreatedOn,
                                                    NP.MODIFIEDBYID                   AS NestingPlanModifiedById,
                                                    UM.USERNAME                       AS NestingPlanModifiedByName,
                                                    NP.MODIFIEDON                     AS NestingPlanModifiedOn,
                                                    NP.TENANTID                       AS TenantId

                                                FROM TNESTINGPLAN NP

                                                LEFT JOIN MITEM I ON NP.SHEETITEMID = I.ITEMID
                                                LEFT JOIN MUOM U ON I.STOCKUOMID = U.UOMID
                                                LEFT JOIN MSKU S ON NP.SHEETSKUID = S.SKUID
                                                LEFT JOIN TLOT L ON NP.LOTID = L.LOTID
                                                LEFT JOIN TPACKNUMBER P ON NP.PACKNUMBERID = P.PACKNUMBERID
                                                LEFT JOIN MEMPLOYEE EP ON NP.PLANPREPAREDBYID = EP.EMPLOYEEID
                                                LEFT JOIN MEMPLOYEE EA ON NP.PLANAPPROVEDBYID = EA.EMPLOYEEID
                                                LEFT JOIN MUSER UC ON NP.CREATEDBYID = UC.USERID
                                                LEFT JOIN MUSER UM ON NP.MODIFIEDBYID = UM.USERID
                                                WHERE NP.NESTINGPLANID = @nestingPlanId;";

        public const string SAVE_NESTINGPLAN = @"INSERT INTO TNESTINGPLAN
                                                    (
                                                        NESTINGPLANID,
                                                        NESTINGPLANCODE,
                                                        PLANDATE,
                                                        PLANTYPE,
                                                        SHEETITEMID,
                                                        SHEETSKUID,
                                                        LOTID,
                                                        PACKNUMBERID,
                                                        SHEETDIMENSION,
                                                        REFERENCENUMBER,
                                                        REFERENCEDATE,
                                                        SHEETWEIGHT,
                                                        TOTALCOMPONENTWEIGHT,
                                                        WASTAGE,
                                                        WASTAGEPERCENTAGE,
                                                        FILEREFERENCE,
                                                        CMSID,
                                                        PLANPREPAREDBYID,
                                                        PLANAPPROVEDBYID,
                                                        VERSION,
                                                        STATUS,
                                                        CREATEDBYID,
                                                        CREATEDON,
                                                        MODIFIEDBYID,
                                                        MODIFIEDON,
                                                        NESTINGTIME,
                                                        REMARKS,
                                                        RUNLENGTH,
                                                        SETUPTIME,
                                                        CYCLETIME,
                                                        TENANTID
                                                    )
                                                    VALUES
                                                    (
                                                        @NestingPlanId,
                                                        @NestingPlanCode,
                                                        @NestingPlanPlanDate,
                                                        @NestingPlanPlanType,
                                                        @SheetItemId,
                                                        @SheetSKUId,
                                                        @LotId,
                                                        @PackNumberId,
                                                        @NestingPlanSheetDimension,
                                                        @NestingPlanReferenceNumber,
                                                        @NestingPlanReferenceDate,
                                                        @NestingPlanSheetWeight,
                                                        @NestingPlanTotalComponentWeight,
                                                        @NestingPlanWastage,
                                                        @NestingPlanWastagePercentage,
                                                        @NestingPlanFileReference,
                                                        @NestingPlanCMSID,
                                                        @PlanPreparedById,
                                                        @PlanApprovedById,
                                                        @NestingPlanVersion,
                                                        @NestingPlanStatus,
                                                        @NestingPlanCreatedById,
                                                        @NestingPlanCreatedOn,
                                                        @NestingPlanModifiedById,
                                                        @NestingPlanModifiedOn,
                                                        @NestingPlanNestingTime,
                                                        @NestingPlanRemarks,
                                                        @NestingPlanRunLength,
                                                        @NestingPlanSetUpTime,
                                                        @NestingPlanCycleTime,
                                                        @TenantId
                                                    );";

        public const string UPDATE_NESTINGPLAN = @"UPDATE TNESTINGPLAN
                                                SET
                                                    NESTINGPLANCODE          = @NestingPlanCode,
                                                    PLANDATE                 = @NestingPlanPlanDate,
                                                    PLANTYPE                 = @NestingPlanPlanType,
                                                    SHEETITEMID              = @SheetItemId,
                                                    SHEETSKUID               = @SheetSKUId,
                                                    LOTID                    = @LotId,
                                                    PACKNUMBERID             = @PackNumberId,
                                                    SHEETDIMENSION           = @NestingPlanSheetDimension,
                                                    REFERENCENUMBER          = @NestingPlanReferenceNumber,
                                                    REFERENCEDATE            = @NestingPlanReferenceDate,
                                                    SHEETWEIGHT              = @NestingPlanSheetWeight,
                                                    TOTALCOMPONENTWEIGHT     = @NestingPlanTotalComponentWeight,
                                                    WASTAGE                  = @NestingPlanWastage,
                                                    WASTAGEPERCENTAGE        = @NestingPlanWastagePercentage,
                                                    FILEREFERENCE            = @NestingPlanFileReference,
                                                    CMSID                    = @NestingPlanCMSID,
                                                    PLANPREPAREDBYID         = @PlanPreparedById,
                                                    PLANAPPROVEDBYID         = @PlanApprovedById,
                                                    VERSION                  = @NestingPlanVersion,
                                                    STATUS                   = @NestingPlanStatus,
                                                    MODIFIEDBYID             = @NestingPlanModifiedById,
                                                    MODIFIEDON               = @NestingPlanModifiedOn,
                                                    NESTINGTIME              = @NestingPlanNestingTime,
                                                    REMARKS                  = @NestingPlanRemarks,
                                                    RUNLENGTH                = @NestingPlanRunLength,
                                                    SETUPTIME                = @NestingPlanSetUpTime,
                                                    CYCLETIME                = @NestingPlanCycleTime,
                                                    TENANTID                 = @TenantId
                                                WHERE
                                                    NESTINGPLANID = @NestingPlanId;";

        public const string DELETE_NESTINGPLAN = @"DELETE FROM TNESTINGPLAN
                                                WHERE NESTINGPLANID = @nestingPlanId";

        public const string GET_SELECTLIST_NESTINGPLAN = @"SELECT 
                                                            NESTINGPLANID AS Id,
                                                            NESTINGPLANCODE AS Code
                                                            FROM TNESTINGPLAN";
    }
}