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

namespace MMDAL.Query.ItemTemplatePart
{
    public class ItemTemplatePartQB
    {
        public const string GET_ITEMTEMPLATEPART = @"SELECT 
                                                        --Parent: MITEMTEMPLATEPART
                                                        ITP.ITEMTEMPLATEPARTID AS ItemTemplatePartId,
                                                        ITP.ITEMTEMPLATEPARTCODE AS ItemTemplatePartCode,
                                                        ITP.ITEMTEMPLATEPARTNAME AS ItemTemplatePartName,
                                                        ITP.ITEMTEMPLATEPARTTYPE AS ItemTemplatePartType,
                                                        ITP.PARENTPARTID AS ParentId,
                                                        PARENT.ITEMTEMPLATEPARTCODE AS ParentCode,
                                                        PARENT.ITEMTEMPLATEPARTNAME AS ParentName,
                                                        PS.PARAMETERSETID AS ParameterSetId,
                                                        PS.PARAMETERSETCODE AS ParameterSetCode,
                                                        PS.PARAMETERSETNAME AS ParameterSetName,
                                                        ITP.PARTDESCRIPTION AS ItemTemplatePartPartDescription,
                                                        U.UOMID AS UOMId,
                                                        U.UOMCODE AS UOMCode,
                                                        U.UOMNAME AS UOMName,
                                                        ITP.ITEMLINKTYPE AS ItemTemplatePartItemLinkType,
                                                        CASE ITP.ITEMLINKTYPE 
                                                            WHEN 0 THEN 'NoLink'
                                                            WHEN 1 THEN 'Item'
                                                            WHEN 2 THEN 'Variant'
                                                            ELSE 'Unknown'
                                                        END AS ItemTemplatePartItemLinkTypeName,
                                                        ITP.LINKITEMID AS ItemTemplatePartLinkItemId,
                                                        LI.ITEMCODE AS ItemTemplatePartLinkItemCode,
                                                        LI.ITEMNAME AS ItemTemplatePartLinkItemName,
                                                        ITP.CALCULATIONMETHOD AS ItemTemplatePartCalculationMethod,
                                                        ITP.CREATEDBYID AS ItemTemplatePartCreatedById,
                                                        CUSER.USERNAME AS ItemTemplatePartCreatedByName,
                                                        ITP.CREATEDON AS ItemTemplatePartCreatedOn,
                                                        ITP.MODIFIEDBYID AS ItemTemplatePartModifiedById,
                                                        MUSER.USERNAME AS ItemTemplatePartModifiedByName,
                                                        ITP.MODIFIEDON AS ItemTemplatePartModifiedOn,
                                                        ITP.SORTORDER AS ItemTemplatePartSortOrder,
                                                        ITP.STATUS AS ItemTemplatePartStatus,
                                                        ITP.VERSION AS ItemTemplatePartVersion,

                                                                            -- Child: MITEMTEMPLATEPARTVSPARAMETER 
                                                                            ITVSP.ITEMTEMPLATEPARTVSPARAMETERID AS ItemTemplatePartVsParameterId,
                                                                            ITVSP.ITEMTEMPLATEPARTID AS ItemTemplatePartId,
                                                                            ITVSP.SLNO AS ItemTemplatePartVsParameterSlNo,
                                                                            ITVSP.PARAMETERID AS ParameterId,
                                                                            PRM.PARAMETERCODE AS ParameterCode,
                                                                            PRM.PARAMETERNAME AS ParameterName,
                                                                            ITVSP.PARAMETERTYPE AS ItemTemplatePartVsParameterParameterType,
                                                                            ITVSP.PARAMETERINPUTTYPE AS ItemTemplatePartVsParameterParameterInputType,
                                                                            ITVSP.PARAMETERVALUE AS ItemTemplatePartVsParameterParameterValue,
                                                                            ITVSP.PARAMETERTEXTVALUE AS ItemTemplatePartVsParameterParameterTextValue,
                                                                            ITVSP.SPREADSHEETNAME AS ItemTemplatePartVsParameterSpreadSheetName,
                                                                            ITVSP.CELLNUMBER AS ItemTemplatePartVsParameterCellNumber

                                                                    FROM MITEMTEMPLATEPART ITP
                                                                    LEFT JOIN MITEMTEMPLATEPART PARENT ON ITP.PARENTPARTID = PARENT.ITEMTEMPLATEPARTID
                                                                    LEFT JOIN MPARAMETERSET PS ON ITP.PARAMETERSETID = PS.PARAMETERSETID
                                                                    LEFT JOIN MUOM U ON ITP.UOMID = U.UOMID
                                                                    LEFT JOIN MITEM LI ON ITP.LINKITEMID = LI.ITEMID
                                                                    LEFT JOIN MUSER CUSER ON ITP.CREATEDBYID = CUSER.USERID
                                                                    LEFT JOIN MUSER MUSER ON ITP.MODIFIEDBYID = MUSER.USERID
                                                                    LEFT JOIN MITEMTEMPLATEPARTVSPARAMETER ITVSP ON ITP.ITEMTEMPLATEPARTID = ITVSP.ITEMTEMPLATEPARTID
                                                                    LEFT JOIN MPARAMETER PRM ON ITVSP.PARAMETERID = PRM.PARAMETERID
                                                                    WHERE ITP.ITEMTEMPLATEPARTID = @itemtemplatepartid;";

        public const string GET_SELECTLIST_ITEMTEMPLATEPART = @"SELECT 
                                                                ITEMTEMPLATEPARTID AS Id,
                                                                ITEMTEMPLATEPARTCODE AS Code,
                                                                ITEMTEMPLATEPARTNAME AS Name
                                                            FROM MITEMTEMPLATEPART
                                                            WHERE STATUS = 1
                                                            ORDER BY ITEMTEMPLATEPARTNAME;";
        
        public const string SAVE_ITEMTEMPLATEPART = @"INSERT INTO MITEMTEMPLATEPART
                                                    (
                                                        ITEMTEMPLATEPARTID,
                                                        ITEMTEMPLATEPARTCODE,
                                                        ITEMTEMPLATEPARTNAME,
                                                        ITEMTEMPLATEPARTTYPE,
                                                        PARENTPARTID,
                                                        PARAMETERSETID,
                                                        PARTDESCRIPTION,
                                                        UOMID,
                                                        ITEMLINKTYPE,
                                                        LINKITEMID,
                                                        CALCULATIONMETHOD,
                                                        SORTORDER,
                                                        STATUS,
                                                        VERSION,
                                                        CREATEDBYID,
                                                        CREATEDON,
                                                        MODIFIEDBYID,
                                                        MODIFIEDON
                                                    )
                                                    VALUES
                                                    (
                                                        @ItemTemplatePartId,
                                                        @ItemTemplatePartCode,
                                                        @ItemTemplatePartName,
                                                        @ItemTemplatePartType,
                                                        @ParentId,
                                                        @ParameterSetId,
                                                        @ItemTemplatePartPartDescription,
                                                        @UOMId,
                                                        @ItemTemplatePartItemLinkType,
                                                        @ItemTemplatePartLinkItemId,
                                                        @ItemTemplatePartCalculationMethod,
                                                        @ItemTemplatePartSortOrder,
                                                        @ItemTemplatePartStatus,
                                                        @ItemTemplatePartVersion,
                                                        @ItemTemplatePartCreatedById,
                                                        @ItemTemplatePartCreatedOn,
                                                        @ItemTemplatePartModifiedById,
                                                        @ItemTemplatePartModifiedOn
                                                    );";

        public const string UPDATE_ITEMTEMPLATEPART = @"UPDATE MITEMTEMPLATEPART
                                                    SET
                                                        ITEMTEMPLATEPARTCODE       = @ItemTemplatePartCode,
                                                        ITEMTEMPLATEPARTNAME       = @ItemTemplatePartName,
                                                        ITEMTEMPLATEPARTTYPE       = @ItemTemplatePartType,
                                                        PARENTPARTID               = @ParentId,
                                                        PARAMETERSETID             = @ParameterSetId,
                                                        PARTDESCRIPTION            = @ItemTemplatePartPartDescription,
                                                        UOMID                      = @UOMId,
                                                        ITEMLINKTYPE               = @ItemTemplatePartItemLinkType,
                                                        LINKITEMID                 = @ItemTemplatePartLinkItemId,
                                                        CALCULATIONMETHOD          = @ItemTemplatePartCalculationMethod,
                                                        SORTORDER                  = @ItemTemplatePartSortOrder,
                                                        STATUS                     = @ItemTemplatePartStatus,
                                                        VERSION                    = @ItemTemplatePartVersion,
                                                        MODIFIEDBYID               = @ItemTemplatePartModifiedById,
                                                        MODIFIEDON                 = @ItemTemplatePartModifiedOn
                                                    WHERE ITEMTEMPLATEPARTID = @ItemTemplatePartId;";

        public const string DELETE_ITEMTEMPLATEPART = @"DELETE FROM MITEMTEMPLATEPART
                                                        WHERE ITEMTEMPLATEPARTID = @itemtemplatepartid;";
    }
}