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

namespace MMDAL.Query.MRPPartyType
{
    public class MRPPartyTypeQB
    {
        public const string GET_MRPPARTYTYPE = @"SELECT
                                                       -----MRPPartyTypeDTO (Parent)-----
                                                    MPT.MRPPARTYTYPEID           AS MRPPartyTypeId,
                                                    MPT.MRPPARTYTYPECODE         AS MRPPartyTypeCode,
                                                    MPT.MRPPARTYTYPENAME         AS MRPPartyTypeName,
                                                    MPT.PARTICULARS              AS MRPPartyTypeParticulars,
                                                    MPT.STATUS                   AS MRPPartyTypeStatus,
                                                    MPT.VERSION                  AS MRPPartyTypeVersion,
                                                    MPT.CREATEDBYID              AS MRPPartyTypeCreatedById,
                                                    MPT.CREATEDON                AS MRPPartyTypeCreatedOn,
                                                    MUC.USERNAME                 AS MRPPartyTypeCreatedByName,
                                                    MPT.MODIFIEDBYID             AS MRPPartyTypeModifiedById,
                                                    MPT.MODIFIEDON               AS MRPPartyTypeModifiedOn,
                                                    MUM.USERNAME                 AS MRPPartyTypeModifiedByName,

                                                               -----MRPPartyTypeDetailDTO (Child)-----
                                                            MPTD.MRPPARTYTYPEDETAILID    AS MRPPartyTypeDetailId,
                                                            MPTD.MRPPARTYTYPEID          AS MRPPartyTypeId,
                                                            MPT.MRPPARTYTYPECODE         AS MRPPartyTypeCode,
                                                            MPT.MRPPARTYTYPENAME         AS MRPPartyTypeName,
                                                            MPTD.SLNO                    AS MRPPartyTypeDetailSlNo,
                                                            MPTD.MRPITEMTYPEID           AS MRPItemTypeId,
                                                            IT.GCMCODE                   AS MRPItemTypeCode,
                                                            IT.GCMNAME                   AS MRPItemTypeName,
                                                            MPTD.NATURE                  AS MRPPartyTypeDetailNature,
                                                            MPTD.BIZTRANSACTIONID        AS BIZTransactionId,
                                                            BT.BIZTRANSACTIONCODE        AS BIZTransactionCode, 
                                                            BT.BIZTRANSACTIONNAME        AS BIZTransactionName
                                                FROM MMRPPARTYTYPE MPT
                                                LEFT JOIN MMRPPARTYTYPEDETAIL MPTD ON MPT.MRPPARTYTYPEID = MPTD.MRPPARTYTYPEID
                                                LEFT JOIN MUSER MUC ON MPT.CREATEDBYID = MUC.USERID
                                                LEFT JOIN MUSER MUM ON MPT.MODIFIEDBYID = MUM.USERID
                                                LEFT JOIN MGCM IT ON MPTD.MRPITEMTYPEID = IT.GCMID
                                                LEFT JOIN MBIZTRANSACTION BT ON MPTD.BIZTRANSACTIONID = BT.BIZTRANSACTIONID
                                                WHERE MPT.MRPPARTYTYPEID =@mrppartytypeid;";

        public const string SAVE_MRPPARTYTYPE = @"INSERT INTO MMRPPARTYTYPE
                                                (
                                                    MRPPARTYTYPEID,
                                                    MRPPARTYTYPECODE,
                                                    MRPPARTYTYPENAME,
                                                    PARTICULARS,
                                                    STATUS,
                                                    VERSION,
                                                    CREATEDBYID,
                                                    CREATEDON
                                                )
                                                VALUES
                                                (
                                                    @MRPPartyTypeId,
                                                    @MRPPartyTypeCode,
                                                    @MRPPartyTypeName,
                                                    @MRPPartyTypeParticulars,
                                                    @MRPPartyTypeStatus,
                                                    @MRPPartyTypeVersion,
                                                    @MRPPartyTypeCreatedById,
                                                    @MRPPartyTypeCreatedOn
                                                );";

        public const string SAVE_MRPPARTYTYPE_DETAIL = @"INSERT INTO MMRPPARTYTYPEDETAIL
                                                        (
                                                            MRPPARTYTYPEDETAILID,
                                                            MRPPARTYTYPEID,
                                                            SLNO,
                                                            MRPITEMTYPEID,
                                                            NATURE,
                                                            BIZTRANSACTIONID
                                                        )
                                                        VALUES
                                                        (
                                                            @MRPPartyTypeDetailId,
                                                            @MRPPartyTypeId,
                                                            @MRPPartyTypeDetailSlNo,
                                                            @MRPItemTypeId,
                                                            @MRPPartyTypeDetailNature,
                                                            @BIZTransactionId
                                                        );";

        public const string UPDATE_MRPPARTYTYPE = @"UPDATE MMRPPARTYTYPE
                                                    SET
                                                        MRPPARTYTYPECODE = @MRPPartyTypeCode,
                                                        MRPPARTYTYPENAME = @MRPPartyTypeName,
                                                        PARTICULARS      = @MRPPartyTypeParticulars,
                                                        STATUS           = @MRPPartyTypeStatus,
                                                        VERSION          = @MRPPartyTypeVersion,
                                                        MODIFIEDBYID     = @MRPPartyTypeModifiedById,
                                                        MODIFIEDON       = @MRPPartyTypeModifiedOn
                                                    WHERE
                                                        MRPPARTYTYPEID = @MRPPartyTypeId;";

        public const string UPDATE_MRPPARTYTYPE_DETAIL = @"UPDATE MMRPPARTYTYPEDETAIL
                                                        SET
                                                            MRPPARTYTYPEID   = @MRPPartyTypeId,
                                                            SLNO             = @MRPPartyTypeDetailSlNo,
                                                            MRPITEMTYPEID    = @MRPItemTypeId,
                                                            NATURE           = @MRPPartyTypeDetailNature,
                                                            BIZTRANSACTIONID = @BIZTransactionId
                                                        WHERE
                                                            MRPPARTYTYPEDETAILID = @MRPPartyTypeDetailId;";

        public const string DELETE_MRPPARTYTYPE = @"DELETE FROM MMRPPARTYTYPE
                                                    WHERE MRPPARTYTYPEID = @mrppartytypeid;";

        public const string DELETE_MRPPARTYTYPE_DETAIL = @"DELETE FROM MMRPPARTYTYPEDETAIL
                                                            WHERE MRPPARTYTYPEID = @mrppartytypeid;";

        public const string GET_SELECTLIST_MRPPARTYTYPE = @"SELECT
                                                            MRPPARTYTYPEID           AS Id,
                                                            MRPPARTYTYPECODE         AS Code,
                                                            MRPPARTYTYPENAME         AS Name
                                                        FROM MMRPPARTYTYPE;";

        public const string GET_MRPPARTYTYPE_COUNT = @"SELECT COUNT(1)
                                                    FROM dbo.MMRPPARTYTYPE
                                                    WHERE
                                                        (
                                                            @MRPPartyTypeId <> -1 AND MRPPARTYTYPEID = @MRPPartyTypeId
                                                        )
                                                        AND MRPPARTYTYPEID <> @MRPPartyTypeId;";
    }
}