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

namespace AnalyticsDAL.Query.DBJoin
{
    public static class DBJoinQB
    {
        public const string GET_DBJOIN = @"SELECT
                                            A.JOINID AS DBJoinId,
                                            A.FROMDBOBJECTID AS FromDBObjectId,
                                            A.TODBOBJECTID AS ToObjectId,
                                            A.DESCRIPTION AS DBJoinDescription,
                                            A.RELATIONTYPE AS DBJoinRelationType,
                                            A.JOINTYPE AS DBJoinJoinType,
                                            A.EXPRESSION AS DBJoinExpression,
                                            A.INDIRECTFROMDBOBJECTID AS IndirectFromDbobjectId,
                                            A.INDIRECTTODBOBJECTID AS IndirectToDbobjectId,
                                            A.SORTORDER AS DBJoinSortOrder,
                                            A.STATUS AS DBJoinStatus,
                                            A.VERSION AS DBJoinVersion,
                                            A.CREATEDBYID AS DBJoinCreatedById,
                                            A.CREATEDON AS DBJoinCreatedOn,
                                            A.MODIFIEDBYID AS DBJoinModifiedById,
                                            A.MODIFIEDON AS DBJoinModifiedOn,
                                            A.SOURCETYPE AS DBJoinSourceType
                                        FROM
                                            DBJOIN A
                                        WHERE
                                            A.JOINID = @joinid;";

        public const string GET_SELECTLIST_DBJOIN = @"WITH PagedDBJoin AS (
                                                SELECT
                                                    A.JOINID AS joinid,
                                                    A.DESCRIPTION AS description,
                                                    A.FROMDBOBJECTID AS fromdbobject,
                                                    A.TODBOBJECTID AS todbobject,
                                                    ROW_NUMBER() OVER (ORDER BY A.JOINID) AS RowNum
                                                FROM DBJOIN A
                                            )
                                            SELECT joinid, description, fromdbobject, todbobject
                                            FROM PagedDBJoin
                                            WHERE 
                                             (@firstnumber = -1 AND @maxresult = -1)
                                             OR (RowNum BETWEEN @firstnumber AND @maxresult);";

        public const string SAVE_DBJOIN = @"
INSERT INTO DBJOIN
(
    JOINID,
    DESCRIPTION,
    FROMDBOBJECTID,
    TODBOBJECTID,
    RELATIONTYPE,
    JOINTYPE,
    INDIRECTFROMDBOBJECTID,
    INDIRECTTODBOBJECTID,
    EXPRESSION,
    SORTORDER,
    STATUS,
    VERSION,
    CREATEDBYID,
    CREATEDON,
    MODIFIEDBYID,
    MODIFIEDON,
    SOURCETYPE
)
VALUES
(
    @DBJoinId,
    @DBJoinDescription,
    @FromDBObjectId,
    @ToObjectId,
    @DBJoinRelationType,
    @DBJoinJoinType,
    @IndirectFromDbobjectId,
    @IndirectToDbobjectId,
    @DBJoinExpression,
    @DBJoinSortOrder,
    @DBJoinStatus,
    @DBJoinVersion,
    @DBJoinCreatedById,
    @DBJoinCreatedOn,
    @DBJoinModifiedById,
    @DBJoinModifiedOn,
    @DBJoinSourceType
);";


        public const string UPDATE_DBJOIN = @"
UPDATE 
DBJOIN
SET
    FROMDBOBJECTID         = @FromDBObjectId,
    TODBOBJECTID           =@ToObjectId,
    DESCRIPTION            = @DBJoinDescription,
    RELATIONTYPE           = @DBJoinRelationType,
    JOINTYPE               = @DBJoinJoinType,
    EXPRESSION             = @DBJoinExpression,
    INDIRECTFROMDBOBJECTID = @IndirectFromDbobjectId,
    INDIRECTTODBOBJECTID   = @IndirectToDbobjectId,
    SOURCETYPE             = @DBJoinSourceType,
    SORTORDER              = @DBJoinSortOrder,
    STATUS                 = @DBJoinStatus,
    VERSION                = @DBJoinVersion,
    MODIFIEDBYID           = @DBJoinModifiedById,
    MODIFIEDON             = @DBJoinModifiedOn
WHERE
    JOINID = @DBJoinId;";

        public const string DELETE_DBJOIN = @"DELETE FROM DBJOIN
                                            WHERE  JOINID = @JoinId;";


    }
}