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

namespace FrameworkDAL.Query.DataDictionary
{
    public static class DataDictionaryQB
    {
        public const string GET_DATADICTIONARY = @" 
        SELECT
    DD.DATADICTIONARYID           AS DataDictionaryId,
    DD.DATADICTIONARYNAME         AS DataDictionaryName,
    DD.SECTION                    AS DataDictionarySection,
    DD.MODULEID                   AS ModuleId,
    M.MODULECODE                  AS ModuleCode,
    M.MODULENAME                  AS ModuleName,
    DD.PARTICULARS                AS DataDictionaryParticulars,
    DD.SORTORDER                  AS DataDictionarySortOrder,
    DD.STATUS                     AS DataDictionaryStatus,
    DD.VERSION                    AS DataDictionaryVersion,
    DD.SOURCETYPE                 AS DataDictionarySourceType,
    DD.CREATEDBYID                AS DataDictionaryCreatedById,
    DD.CREATEDON                  AS DataDictionaryCreatedOn,
    DD.MODIFIEDBYID               AS DataDictionaryModifiedById,
    DD.MODIFIEDON                 AS DataDictionaryModifiedOn
FROM 
    MDATADICTIONARY DD
LEFT JOIN 
    MMODULE M ON DD.MODULEID = M.MODULEID
WHERE 
    DD.DATADICTIONARYID = @datadictionaryid;
";

        public const string SAVE_DATADICTIONARY = @"INSERT 
                            INTO MDATADICTIONARY (
                                 DATADICTIONARYID,
                                 DATADICTIONARYNAME,
                                 SECTION,
                                 MODULEID,
                                 PARTICULARS,
                                 SORTORDER,
                                 STATUS,
                                 VERSION,
                                 SOURCETYPE,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON
                            ) VALUES (
                                 @DataDictionaryId,
                                 @DataDictionaryName,
                                 @DataDictionarySection,
                                 @ModuleId,
                                 @DataDictionaryParticulars,
                                 @DataDictionarySortOrder,
                                 @DataDictionaryStatus,
                                 @DataDictionaryVersion,
                                 @DataDictionarySourceType,
                                 @DataDictionaryCreatedById,
                                 @DataDictionaryCreatedOn,
                                 @DataDictionaryModifiedById,
                                 @DataDictionaryModifiedOn
                            );
                            ";

        public const string UPDATE_DATADICTIONARY = @"UPDATE 
                            MDATADICTIONARY SET
                            DATADICTIONARYNAME = @DataDictionaryName,
                            SECTION = @DataDictionarySection,
                            MODULEID = @ModuleId,
                            PARTICULARS = @DataDictionaryParticulars,
                            SORTORDER = @DataDictionarySortOrder,
                            STATUS = @DataDictionaryStatus,
                            VERSION = @DataDictionaryVersion,
                            SOURCETYPE = @DataDictionarySourceType,
                            MODIFIEDBYID = @DataDictionaryModifiedById,
                            MODIFIEDON = @DataDictionaryModifiedOn
                            WHERE DATADICTIONARYID = @DataDictionaryId;
                            ";

        public const string DELETE_DATADICTIONARY = @"DELETE 
                            FROM MDATADICTIONARY
                            WHERE DATADICTIONARYID = @datadictionaryid;
                            ";

        public const string GET_SELECTLIST_DATADICTIONARY = @"
                            WITH PagedDataDictionary AS (
                            SELECT 
                            DD.DATADICTIONARYID AS Id,
                            DD.DATADICTIONARYNAME AS Name,
                            ROW_NUMBER() OVER (ORDER BY DD.DATADICTIONARYID) AS RowNum
                            FROM MDATADICTIONARY DD
                            )
                            SELECT
                            Id,
                            Name
                            FROM PagedDataDictionary
                            WHERE 
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";
    }
}