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

namespace AdminDAL.Query.DataWareHouse
{
    public static class DataWareHouseQB
    {
        public const string GET_DATAWAREHOUSE = @"SELECT
                            DW.DWID AS DataWareHouseId,
                            DW.DWCODE AS DataWareHouseCode,
                            DW.DWNAME AS DataWareHouseName,
                            DW.SORTORDER AS DataWareHouseSortOrder,
                            DW.STATUS AS DataWareHouseStatus,
                            DW.VERSION AS DataWareHouseVersion,
                            DW.SOURCETYPE AS DataWareHouseSourceType,
                            DW.CREATEDBYID AS DataWareHouseCreatedById,
                            DW.CREATEDON AS DataWareHouseCreatedOn,
                            DW.MODIFIEDBYID AS DataWareHouseModifiedById,
                            DW.MODIFIEDON AS DataWareHouseModifiedOn,
                            DW.OBJECTNAMES AS DataWareHouseObjectNames,
                            DW.UPDATEFREQUENCY AS DataWareHouseUpdateFrequency,
                            DW.LASTUPDATED AS DataWareHouseLastUpdated,
                            DW.REMARKS AS DataWareHouseRemarks,

                            DWD.DWDETAILID AS DataWareHouseDetailId,
                            DW.DWID AS DataWareHouseId,
                            DW.DWCODE AS DataWareHouseCode,
                            DW.DWNAME AS DataWareHouseName,
                            DWD.SLNO AS DataWareHouseDetailSlNo,
                            DWD.DESCRIPTION AS DataWareHouseDetailDescription,
                            DWD.RUNORDER AS DataWareHouseDetailRunOrder,
                            DWD.QUERY AS DataWareHouseDetailQuery,
                            DWD.ISRUNTIMEREQUIRED AS DataWareHouseDetailIsRuntimeRequired
                            FROM MDATAWAREHOUSE DW
                            LEFT JOIN MDATAWAREHOUSEDETAIL DWD ON DW.DWID = DWD.DWID
                            WHERE DW.DWID = @datawarehouseid;
                            ";

        public const string GET_SELECTLIST_DATAWAREHOUSE = @"SELECT
                            DWID AS Id,
                            DWCODE AS Code,
                            DWNAME AS Name
                            FROM MDATAWAREHOUSE
                            ";

        public const string SAVE_DATAWAREHOUSE = @"
            IF @DataWareHouseId = 0
            BEGIN
                INSERT INTO MDATAWAREHOUSE (DWCODE, DWNAME, OBJECTNAMES, UPDATEFREQUENCY, SORTORDER, STATUS, SOURCETYPE, REMARKS, CREATEDBYID, CREATEDON, MODIFIEDBYID, MODIFIEDON)
                VALUES (@DataWareHouseCode, @DataWareHouseName, @DataWareHouseObjectNames, @DataWareHouseUpdateFrequency, @DataWareHouseSortOrder, @DataWareHouseStatus, @DataWareHouseSourceType, @DataWareHouseRemarks, @DataWareHouseCreatedById, GETUTCDATE(), @DataWareHouseModifiedById, GETUTCDATE())
                SELECT CAST(SCOPE_IDENTITY() AS INT)
            END
            ELSE
            BEGIN
                UPDATE MDATAWAREHOUSE
                SET DWCODE            = @DataWareHouseCode,
                    DWNAME            = @DataWareHouseName,
                    OBJECTNAMES       = @DataWareHouseObjectNames,
                    UPDATEFREQUENCY   = @DataWareHouseUpdateFrequency,
                    SORTORDER         = @DataWareHouseSortOrder,
                    STATUS            = @DataWareHouseStatus,
                    SOURCETYPE        = @DataWareHouseSourceType,
                    REMARKS           = @DataWareHouseRemarks,
                    MODIFIEDBYID      = @DataWareHouseModifiedById,
                    MODIFIEDON        = GETUTCDATE()
                WHERE DWID = @DataWareHouseId
                SELECT @DataWareHouseId
            END";

        public const string SAVE_DATAWAREHOUSE_DETAIL = @"
            INSERT INTO MDATAWAREHOUSEDETAIL (DWID, SLNO, DESCRIPTION, RUNORDER, QUERY, ISRUNTIMEREQUIRED)
            VALUES (@DataWareHouseId, @DataWareHouseDetailSlNo, @DataWareHouseDetailDescription, @DataWareHouseDetailRunOrder, @DataWareHouseDetailQuery, @DataWareHouseDetailIsRuntimeRequired)";

        public const string DELETE_DATAWAREHOUSE_DETAILS = @"
            DELETE FROM MDATAWAREHOUSEDETAIL WHERE DWID = @DataWareHouseId";

        public const string DELETE_DATAWAREHOUSE = @"
            DELETE FROM MDATAWAREHOUSE WHERE DWID = @DataWareHouseId";
    }
}