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

namespace FrameworkDAL.Query.DocumentSet
{
    public static class DocumentSetQB
    {
        public const string GET_DOCUMENTSET = @"SELECT
                                                D.DOCUMENTSETID AS DocumentSetId,
                                                D.DOCUMENTSETCODE AS DocumentSetCode,
                                                D.DOCUMENTSETNAME AS DocumentSetName,
                                                D.MODULEID AS ModuleId,
                                                M.MODULECODE AS ModuleCode,
                                                M.MODULENAME AS ModuleName,
                                                D.REMARKS AS DocumentSetRemarks,
                                                D.TAGFIELDS AS DocumentSetTagFields,
                                                D.PROPERTYFIELDS AS DocumentSetPropertyFields,
                                                D.SORTORDER AS DocumentSetSortOrder,
                                                D.STATUS AS DocumentSetStatus,
                                                D.VERSION AS DocumentSetVersion,
                                                D.SOURCETYPE AS DocumentSetSourceType,
                                                D.CREATEDBYID AS DocumentSetCreatedById,
                                                D.CREATEDON AS DocumentSetCreatedOn,
                                                D.MODIFIEDBYID AS DocumentSetModifiedById,
                                                D.MODIFIEDON AS DocumentSetModifiedOn,
                                                DD.DOCUMENTSETDETAILID AS DocumentSetDetailId,
                                                DD.DOCUMENTSETID AS DocumentSetId,   
                                                D.DOCUMENTSETCODE AS DocumentSetCode,          
                                                D.DOCUMENTSETNAME AS DocumentSetName,          
                                                DD.SLNO AS DocumentSetDetailSlNo,
                                                DD.DOCUMENTTYPEID AS DocumentTypeId,
                                                DT.DOCUMENTTYPECODE AS DocumentTypeCode,
                                                DT.DOCUMENTTYPENAME AS DocumentTypeName,
                                                DD.ISMANDATORY AS DocumentSetDetailIsMandatory,
                                                DD.ISMULTIPLE AS DocumentSetDetailIsMultiple,
                                                DD.DISPLAYNAMETEMPLATE AS DocumentSetDetailDisplayNameTemplate,
                                                DD.DOCUMENTNAMETEMPLATE AS DocumentSetDetailDocumentNameTemplate,
                                                DD.LOCATIONTEMPLATE AS DocumentSetDetailLocationTemplate,
                                                DD.DEFAULTCLASSIFICATIONMARKID AS DefaultClassificationMarkId,
                                                SGD.MARKCODE AS DefaultClassificationMarkCode,
                                                SGD.MARKNAME AS DefaultClassificationMarkName
                                            FROM MDOCUMENTSET D
                                            LEFT JOIN MMODULE M ON D.MODULEID = M.MODULEID
                                            LEFT JOIN MDOCUMENTSETDETAIL DD ON D.DOCUMENTSETID = DD.DOCUMENTSETID
                                            LEFT JOIN MDOCUMENTTYPE DT ON DD.DOCUMENTTYPEID = DT.DOCUMENTTYPEID
                                            LEFT JOIN MSECURITYGROUPDETAIL SGD ON DD.DEFAULTCLASSIFICATIONMARKID = SGD.SECURITYGROUPDETAILID
                                            WHERE D.DOCUMENTSETID = @documentsetid; ";

        public const string GET_SELECTLIST_DOCUMENTSET = @"
WITH PagedDocumentSet AS (
    SELECT
        DS.DOCUMENTSETID AS Id,
        DS.DOCUMENTSETCODE AS Code,
        DS.DOCUMENTSETNAME AS Name,
        ROW_NUMBER() OVER (ORDER BY DS.DOCUMENTSETID) AS RowNum
    FROM MDOCUMENTSET DS
    WHERE DS.STATUS = 1
)
SELECT
    Id,
    Code,
    Name
FROM PagedDocumentSet
WHERE RowNum BETWEEN (@firstnumber + 1) AND (@firstnumber + @maxresult);";

        public const string SAVE_DOCUMENTSET = @"INSERT 
                            INTO MDOCUMENTSET (
                                 DOCUMENTSETID,
                                 DOCUMENTSETCODE,
                                 DOCUMENTSETNAME,
                                 REMARKS,
                                 TAGFIELDS,
                                 PROPERTYFIELDS,
                                 MODULEID,
                                 SORTORDER,
                                 STATUS,
                                 VERSION,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON,
                                 SOURCETYPE
                            ) OUTPUT INSERTED.DocumentSetId VALUES (
                                 @DocumentSetId,
                                 @DocumentSetCode,
                                 @DocumentSetName,
                                 @DocumentSetRemarks,
                                 @DocumentSetTagFields,
                                 @DocumentSetPropertyFields,
                                 @ModuleId,
                                 @DocumentSetSortOrder,
                                 @DocumentSetStatus,
                                 @DocumentSetVersion,
                                 @DocumentSetCreatedById,
                                 @DocumentSetCreatedOn,
                                 @DocumentSetModifiedById,
                                 @DocumentSetModifiedOn,
                                 @DocumentSetSourceType
                            );
                            ";

        public const string SAVE_DOCUMENTSETDETAIL = @"INSERT 
                            INTO MDOCUMENTSETDETAIL (
                                 DOCUMENTSETDETAILID,
                                 DOCUMENTSETID,
                                 SLNO,
                                 DOCUMENTTYPEID,
                                 ISMANDATORY,
                                 ISMULTIPLE,
                                 DISPLAYNAMETEMPLATE,
                                 DOCUMENTNAMETEMPLATE,
                                 LOCATIONTEMPLATE,
                                 DEFAULTCLASSIFICATIONMARKID
                            ) OUTPUT INSERTED.DocumentSetDetailId VALUES (
                                 @DocumentSetDetailId,
                                 @DocumentSetId,
                                 @DocumentSetDetailSlNo,
                                 @DocumentTypeId,
                                 @DocumentSetDetailIsMandatory,
                                 @DocumentSetDetailIsMultiple,
                                 @DocumentSetDetailDisplayNameTemplate,
                                 @DocumentSetDetailDocumentNameTemplate,
                                 @DocumentSetDetailLocationTemplate,
                                 @DefaultClassificationMarkId
                            );
                            ";

        public const string UPDATE_DOCUMENTSET = @"UPDATE 
                            MDOCUMENTSET SET
                            DOCUMENTSETCODE = @DocumentSetCode,
                            DOCUMENTSETNAME = @DocumentSetName,
                            REMARKS = @DocumentSetRemarks,
                            TAGFIELDS = @DocumentSetTagFields,
                            PROPERTYFIELDS = @DocumentSetPropertyFields,
                            MODULEID = @ModuleId,
                            SORTORDER = @DocumentSetSortOrder,
                            STATUS = @DocumentSetStatus,
                            VERSION = @DocumentSetVersion,
                            MODIFIEDBYID = @DocumentSetModifiedById,
                            MODIFIEDON = @DocumentSetModifiedOn,
                            SOURCETYPE = @DocumentSetSourceType
                            WHERE DocumentSetId = @DocumentSetId;
                            ";

        public const string UPDATE_DOCUMENTSETDETAIL = @"UPDATE 
                            MDOCUMENTSETDETAIL SET
                            DOCUMENTSETID = @DocumentSetId,
                            SLNO = @DocumentSetDetailSlNo,
                            DOCUMENTTYPEID = @DocumentTypeId,
                            ISMANDATORY = @DocumentSetDetailIsMandatory,
                            ISMULTIPLE = @DocumentSetDetailIsMultiple,
                            DISPLAYNAMETEMPLATE = @DocumentSetDetailDisplayNameTemplate,
                            DOCUMENTNAMETEMPLATE = @DocumentSetDetailDocumentNameTemplate,
                            LOCATIONTEMPLATE = @DocumentSetDetailLocationTemplate,
                            DEFAULTCLASSIFICATIONMARKID = @DefaultClassificationMarkId
                            WHERE DocumentSetDetailId = @DocumentSetDetailId AND DocumentSetId = @DocumentSetId;
                            ";

        public const string DELETE_DOCUMENTSET = @"DELETE 
                            FROM MDOCUMENTSET
                            WHERE DOCUMENTSETID = @documentsetid;
                            ";

        public const string DELETE_DOCUMENTSETDETAIL = @"DELETE 
                            FROM MDOCUMENTSETDETAIL
                            WHERE DOCUMENTSETID = @documentsetid;
                            ";

        public const string GET_MAX_DOCUMENTSETDETAILID = @"SELECT
                            ISNULL(MIN(DOCUMENTSETDETAILID), 0)
                            FROM MDOCUMENTSETDETAIL
                            WHERE DOCUMENTSETID = @documentsetid;
                            ";
    }
}