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

namespace FrameworkDAL.Query.DocumentMap
{
    public static class DocumentMapQB
    {
        public const string GET_DOCUMENTMAP = @"
    SELECT
-- Main Table: MDOCUMENTMAP
DM.DOCUMENTMAPID AS DocumentMapId,
DM.ENTITYID AS EntityId,
E.ENTITYCODE AS EntityCode,
E.ENTITYNAME AS EntityName,

DM.BIZTRANSACTIONCLASSID AS BIZTransactionClassId,
BTC.BIZTRANSACTIONCLASSCODE AS BIZTransactionClassCode,
BTC.BIZTRANSACTIONCLASSNAME AS BIZTransactionClassName,

DM.BIZTRANSACTIONID AS BiztransactionId,
BT.BIZTRANSACTIONCODE AS BiztransactionCode,
BT.BIZTRANSACTIONNAME AS BiztransactionName,

DM.DOCUMENTSETID AS DocumentSetId,
DS.DOCUMENTSETCODE AS DocumentSetCode,
DS.DOCUMENTSETNAME AS DocumentSetName,

DM.SORTORDER AS DocumentMapSortOrder,
DM.STATUS AS DocumentMapStatus,
DM.VERSION AS DocumentMapVersion,
DM.SOURCETYPE AS DocumentMapSourceType,

DM.CREATEDBYID AS DocumentMapCreatedById,
DM.CREATEDON AS DocumentMapCreatedOn,
DM.MODIFIEDBYID AS DocumentMapModifiedById,
DM.MODIFIEDON AS DocumentMapModifiedOn

FROM MDOCUMENTMAP DM

-- Entity join
LEFT JOIN MENTITY E ON DM.ENTITYID = E.ENTITYID

-- BIZTransactionClass join
LEFT JOIN MBIZTRANSACTIONCLASS BTC ON DM.BIZTRANSACTIONCLASSID = BTC.BIZTRANSACTIONCLASSID

-- BIZTransaction join
LEFT JOIN MBIZTRANSACTION BT ON DM.BIZTRANSACTIONID = BT.BIZTRANSACTIONID

-- DocumentSet join
LEFT JOIN MDOCUMENTSET DS ON DM.DOCUMENTSETID = DS.DOCUMENTSETID

WHERE 
DM.ENTITYID = @entityid
AND DM.BIZTRANSACTIONCLASSID = @biztransactionclassid
AND DM.BIZTRANSACTIONID = @biztransactionid;
    ";

        public const string SAVE_DOCUMENTMAP = @"INSERT 
                            INTO MDOCUMENTMAP (
                                 ENTITYID,
                                 BIZTRANSACTIONCLASSID,
                                 BIZTRANSACTIONID,
                                 DOCUMENTSETID,
                                 SORTORDER,
                                 STATUS,
                                 VERSION,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON,
                                 SOURCETYPE
                            ) VALUES (
                                 @EntityId,
                                 @BIZTransactionClassId,
                                 @BiztransactionId,
                                 @DocumentSetId,
                                 @DocumentMapSortOrder,
                                 @DocumentMapStatus,
                                 @DocumentMapVersion,
                                 @DocumentMapCreatedById,
                                 @DocumentMapCreatedOn,
                                 @DocumentMapModifiedById,
                                 @DocumentMapModifiedOn,
                                 @DocumentMapSourceType
                            );
                            SELECT CAST(SCOPE_IDENTITY() AS INT);
                            ";

        public const string UPDATE_DOCUMENTMAP = @"UPDATE 
                            MDOCUMENTMAP SET
                            ENTITYID = @EntityId,
                            BIZTRANSACTIONCLASSID = @BIZTransactionClassId,
                            BIZTRANSACTIONID = @BiztransactionId,
                            DOCUMENTSETID = @DocumentSetId,
                            SORTORDER = @DocumentMapSortOrder,
                            STATUS = @DocumentMapStatus,
                            VERSION = @DocumentMapVersion,
                            MODIFIEDBYID = @DocumentMapModifiedById,
                            MODIFIEDON = @DocumentMapModifiedOn,
                            SOURCETYPE = @DocumentMapSourceType
                            WHERE DOCUMENTMAPID = @DocumentMapId;
                            ";

        public const string DELETE_DOCUMENTMAP = @"DELETE
                            FROM MDOCUMENTMAP
                            WHERE DOCUMENTMAPID = @documentmapid;
                            ";

        public const string GET_BIZTRANSACTIONTYPE_CLASS_TRANSACTION = @"
                            SELECT
                                BIZTRANSACTIONCLASSID AS BizTransactionClassId,
                                BIZTRANSACTIONID AS BiztransactionId
                            FROM MBIZTRANSACTIONTYPE
                            WHERE BIZTRANSACTIONTYPEID = @biztransactiontypeid;
                            ";

        public const string GET_DOCUMENTSETID_FOR_ENTITY = @"
                            SELECT TOP 1
                                DOCUMENTSETID AS DocumentSetId
                            FROM MDOCUMENTMAP
                            WHERE ENTITYID = @entityid
                              AND BIZTRANSACTIONCLASSID = @biztransactionclassid
                              AND BIZTRANSACTIONID = @biztransactionid;
                            ";
    }
}