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

namespace FrameworkDAL.Query.Dataset
{
    public static class DatasetQB
    {
        public const string GET_DATASET = @"SELECT
                            DS.DATASETID AS DatasetId,
                            DS.DATASETCODE AS DatasetCode,
                            DS.DATASETNAME AS DatasetName,
                            DS.CRITERIACONFIGID AS CriteriaConfigId,
                            CC.CRITERIACONFIGNAME AS CriteriaConfigName,
                            DS.REMARKS AS DatasetRemarks,
                            DS.SORTORDER AS DatasetSortOrder,
                            DS.STATUS AS DatasetStatus,
                            DS.VERSION AS DatasetVersion,
                            DS.SOURCETYPE AS DatasetSourceType,
                            DS.CREATEDBYID AS DatasetCreatedById,
                            DS.CREATEDON AS DatasetCreatedOn,
                            DS.MODIFIEDBYID AS DatasetModifiedById,
                            DS.MODIFIEDON AS DatasetModifiedOn,

                            DSD.DATASETDETAILID AS DatasetDetailId,
                            DSD.DATASETID AS DatasetId,
                            DS.DATASETCODE AS DatasetCode,
                            DS.DATASETNAME AS DatasetName,
                            DSD.SLNO AS DatasetDetailSlNo,
                            DSD.ENTITYID AS EntityId,
                            E.ENTITYCODE AS EntityCode,
                            E.ENTITYNAME AS EntityName,
                            DSD.ENTITYCRITERIACONFIGID AS EntityCriteriaConfigId,
                            TCC.CRITERIACONFIGNAME AS EntityCriteriaConfigName,
                            DSD.DBOBJECTID AS DBObjectId,
                            DB.DBOBJECTNAME AS DBObjectName,
                            DSD.CHUNKSIZE AS ChunkSize,
                            DSD.PRIMARYKEYCOLUMN AS PrimaryKeyColumn,
                            DSD.CREATEDDATECOLUMN AS CreatedDateColumn,
                            DSD.MODIFIEDDATECOLUMN AS ModifiedDateColumn
                            FROM MDATASET DS
                            LEFT JOIN TCRITERIACONFIG CC ON DS.CRITERIACONFIGID = CC.CRITERIACONFIGID
                            LEFT JOIN MDATASETDETAIL DSD ON DS.DATASETID = DSD.DATASETID
                            LEFT JOIN MENTITY E ON DSD.ENTITYID = E.ENTITYID
                            LEFT JOIN TCRITERIACONFIG TCC ON DSD.ENTITYCRITERIACONFIGID = TCC.CRITERIACONFIGID
                            LEFT JOIN DBOBJECT DB ON DSD.DBOBJECTID = DB.DBOBJECTID
                            WHERE DS.DATASETID = @datasetid;
                            ";

        public const string SAVE_DATASET = @"INSERT 
                            INTO MDATASET (
                                 DATASETID,
                                 DATASETCODE,
                                 DATASETNAME,
                                 CRITERIACONFIGID,
                                 REMARKS,
                                 SORTORDER,
                                 STATUS,
                                 VERSION,
                                 SOURCETYPE,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON
                            ) OUTPUT INSERTED.DatasetId VALUES (
                                 @DatasetId,
                                 @DatasetCode,
                                 @DatasetName,
                                 @CriteriaConfigId,
                                 @DatasetRemarks,
                                 @DatasetSortOrder,
                                 @DatasetStatus,
                                 @DatasetVersion,
                                 @DatasetSourceType,
                                 @DatasetCreatedById,
                                 @DatasetCreatedOn,
                                 @DatasetModifiedById,
                                 @DatasetModifiedOn
                            );
                            ";

        public const string SAVE_DATASETDETAIL = @"INSERT
                            INTO MDATASETDETAIL (
                                 DATASETDETAILID,
                                 DATASETID,
                                 SLNO,
                                 ENTITYID,
                                 ENTITYCRITERIACONFIGID,
                                 DBOBJECTID,
                                 CHUNKSIZE,
                                 PRIMARYKEYCOLUMN,
                                 CREATEDDATECOLUMN,
                                 MODIFIEDDATECOLUMN
                            ) OUTPUT INSERTED.DatasetDetailId VALUES (
                                 @DatasetDetailId,
                                 @DatasetId,
                                 @DatasetDetailSlNo,
                                 @EntityId,
                                 @EntityCriteriaConfigId,
                                 @DBObjectId,
                                 @ChunkSize,
                                 @PrimaryKeyColumn,
                                 @CreatedDateColumn,
                                 @ModifiedDateColumn
                            );
                            ";

        public const string UPDATE_DATASET = @"UPDATE 
                            MDATASET SET
                            DATASETCODE = @DatasetCode,
                            DATASETNAME = @DatasetName,
                            CRITERIACONFIGID = @CriteriaConfigId,
                            REMARKS = @DatasetRemarks,
                            SORTORDER = @DatasetSortOrder,
                            STATUS = @DatasetStatus,
                            VERSION = @DatasetVersion,
                            SOURCETYPE = @DatasetSourceType,
                            MODIFIEDBYID = @DatasetModifiedById,
                            MODIFIEDON = @DatasetModifiedOn
                            WHERE DatasetId = @DatasetId;
                            ";

        public const string UPDATE_DATASETDETAIL = @"UPDATE
                            MDATASETDETAIL SET
                            DATASETID = @DatasetId,
                            SLNO = @DatasetDetailSlNo,
                            ENTITYID = @EntityId,
                            ENTITYCRITERIACONFIGID = @EntityCriteriaConfigId,
                            DBOBJECTID = @DBObjectId,
                            CHUNKSIZE = @ChunkSize,
                            PRIMARYKEYCOLUMN = @PrimaryKeyColumn,
                            CREATEDDATECOLUMN = @CreatedDateColumn,
                            MODIFIEDDATECOLUMN = @ModifiedDateColumn
                            WHERE DatasetDetailId = @DatasetDetailId AND DatasetId = @DatasetId;
                            ";

        public const string DELETE_DATASET = @"DELETE 
                            FROM MDATASET
                            WHERE DATASETID = @datasetid;
                            ";

        public const string DELETE_DATASETDETAIL = @"DELETE 
                            FROM MDATASETDETAIL
                            WHERE DATASETID = @datasetid;
                            ";

        public const string GET_MAX_DATASETDETAILID = @"SELECT
                            ISNULL(MIN(DATASETDETAILID), 0)
                            FROM MDATASETDETAIL
                            WHERE DATASETID = @datasetid;";

        public const string GET_SELECTLIST_DATASET = @"
                            WITH PagedDataset AS (
                            SELECT 
                            DS.DATASETID AS Id,
                            DS.DATASETCODE AS Code,
                            DS.DATASETNAME AS Name,
                            ROW_NUMBER() OVER (ORDER BY DS.DATASETID) AS RowNum
                            FROM MDATASET DS
                            )
                            SELECT
                            Id,
                            Code,
                            Name
                            FROM PagedDataset
                            WHERE 
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";
    }
}