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

namespace FrameworkDAL.Query.CriteriaConfig
{
    public static class CriteriaConfigQB
    {
        public const string GET_CRITERIACONFIG = @"SELECT
                            CC.CRITERIACONFIGID AS CriteriaConfigId,
                            CC.CRITERIACONFIGNAME AS CriteriaConfigName,
                            CC.CRITERIASERVICEID AS CriteriaConfigCriteriaServiceId,
                            WS.WEBSERVICENAME AS CriteriaConfigServiceName,
                            CC.CRITERIACONFIGTYPE AS CriteriaConfigCriteriaConfigType,
                            CC.CRITERIAUSERID AS CriteriaConfigCriteriaUserId,
                            U.USERCODE AS CriteriaConfigUserCode,
                            U.USERNAME AS CriteriaConfigUserName,
                            CC.PERIODFILTER AS CriteriaConfigPeriodFilter,
                            CC.CRITERIAMENUID AS CriteriaConfigCriteriaMenuId,
                            MM.MENUCODE AS CriteriaConfigCriteriaMenuCode,
                            MM.MENUNAME AS CriteriaConfigCriteriaMenuName,
                            CC.STATUS AS CriteriaConfigStatus,
                            CC.VERSION AS CriteriaConfigVersion,
                            CC.CREATEDBYID AS CriteriaConfigCreatedById,
                            CC.CREATEDON AS CriteriaConfigCreatedOn,
                            CC.MODIFIEDBYID AS CriteriaConfigModifiedById,
                            CC.MODIFIEDON AS CriteriaConfigModifiedOn,
                            CC.CRITERIAOUID AS CriteriaConfigCriteriaOUId,
                            CC.RECORDSPERPAGE AS CriteriaConfigRecordsPerPage,
                            CC.REPORTFORMATID AS ReportFormatId,
                            RF.REPORTFORMATNAME AS ReportFormatName,
                            CC.DEFAULTREPORTVIEWID AS DefaultReportViewId,
                            CC.SOURCETYPE AS CriteriaConfigSourceType,
                            CC.CRITERIARUNTYPE AS UserCriteriaRunType,

                            CCS.CRITERIACONFIGSECTIONID AS CriteriaConfigSectionId,
                            CCS.CRITERIACONFIGID AS CriteriaConfigId,
                            CC.CRITERIACONFIGNAME AS CriteriaConfigName,
                            CCS.SLNO AS CriteriaConfigSectionSlNo,
                            CCS.SECTIONJOIN AS CriteriaConfigSectionSectionJoin,
                            CCS.STATUS AS CriteriaConfigSectionStatus,
                            CCS.VERSION AS CriteriaConfigSectionVersion,
                            CCS.CREATEDBYID AS CriteriaConfigSectionCreatedById,
                            CCS.CREATEDON AS CriteriaConfigSectionCreatedOn,
                            CCS.MODIFIEDBYID AS CriteriaConfigSectionModifiedById,
                            CCS.MODIFIEDON AS CriteriaConfigSectionModifiedOn,


                            CCA.CRITERIACONFIGATTRIBUTEID AS CriteriaConfigAttributeId,
                            CCA.CRITERIASECTIONID AS CriteriaSectionId,
                            CCA.SLNO AS CriteriaConfigAttributeSlNo,
                            CCA.CRITERIAATTRIBUTEID AS CriteriaAttributeId,
                            CA.CRITERIAATTRIBUTENAME AS CriteriaAttributeName,
                            CA.CRITERIAATTRIBUTETYPE AS CriteriaAttributeType,
                            CA.FILTERTYPE AS CriteriaAttributeFilterType,
                            CCA.OPERATIONTYPE AS CriteriaConfigAttributeOperationType,
                            CCA.CRITERIAFIELDVALUE AS CriteriaConfigAttributeCriteriaFieldValue,
                            CCA.CRITERIAFIELDVALUEIN AS CriteriaConfigAttributeCriteriaFieldValueIn,
                            CCA.CRITERIAJOIN AS CriteriaConfigAttributeCriteriaJoin,
                            CCA.CRITERIAFIELDDISPLAYVALUE AS CriteriaConfigAttributeCriteriaFieldDisplayValue,
                            CCA.CRITERIAVARIABLEFIELD AS CriteriaConfigAttributeCriteriaVariableField,
                            CCA.WEBSERVICECRITERIAID AS WebServiceCriteriaId

                            FROM TCRITERIACONFIG CC
                            LEFT JOIN MREPORTFORMAT RF ON CC.REPORTFORMATID = RF.REPORTFORMATID
                            LEFT JOIN MUSER U ON CC.CRITERIAUSERID = U.USERID
                            LEFT JOIN MWEBSERVICE WS ON CC.CRITERIASERVICEID = WS.WEBSERVICEID
                            LEFT JOIN MMENU MM ON CC.CRITERIAMENUID = MM.MENUID
                            LEFT JOIN TCRITERIACONFIGSECTION CCS ON CC.CRITERIACONFIGID = CCS.CRITERIACONFIGID
                            LEFT JOIN TCRITERIACONFIGATTRIBUTE CCA ON CCS.CRITERIACONFIGSECTIONID = CCA.CRITERIASECTIONID
                            LEFT JOIN MCRITERIAATTRIBUTE CA ON CCA.CRITERIAATTRIBUTEID = CA.CRITERIAATTRIBUTEID
                            WHERE CC.CRITERIACONFIGID = @criteriaconfigid;
                            ";

        public const string GET_SELECTLIST_CRITERIACONFIG = @"
                            WITH PagedCriteriaConfig AS (
                            SELECT 
                            CC.CRITERIACONFIGID AS Id,
                            CC.CRITERIACONFIGNAME AS Name,
                            CC.CRITERIACONFIGTYPE AS CriteriaConfigType,
                            CC.CRITERIAUSERID AS UserId,
                            ROW_NUMBER() OVER (ORDER BY CC.CRITERIACONFIGID) AS RowNum
                            FROM TCRITERIACONFIG CC
                            )
                            SELECT
                            Id,
                            Name,
                            CriteriaConfigType,
                            UserId
                            FROM PagedCriteriaConfig
                            WHERE  
                            (@firstnumber = -1 AND @maxresult = -1)
                            OR
                            (RowNum BETWEEN @firstnumber AND @maxresult);
                            ";

        public const string SAVE_CRITERIACONFIG = @"INSERT 
                            INTO TCRITERIACONFIG (
                                 CRITERIACONFIGID,
                                 CRITERIACONFIGNAME,
                                 CRITERIASERVICEID,
                                 CRITERIACONFIGTYPE,
                                 CRITERIAUSERID,
                                 PERIODFILTER,
                                 CRITERIAMENUID,
                                 STATUS,
                                 VERSION,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON,
                                 CRITERIAOUID,
                                 RECORDSPERPAGE,
                                 REPORTFORMATID,
                                 DEFAULTREPORTVIEWID,
                                 SOURCETYPE,
                                 CRITERIARUNTYPE
                            ) OUTPUT INSERTED.CriteriaConfigId VALUES (
                                 @CriteriaConfigId,
                                 @CriteriaConfigName,
                                 @CriteriaConfigCriteriaServiceId,
                                 @CriteriaConfigCriteriaConfigType,
                                 @CriteriaConfigCriteriaUserId,
                                 @CriteriaConfigPeriodFilter,
                                 @CriteriaConfigCriteriaMenuId,
                                 @CriteriaConfigStatus,
                                 @CriteriaConfigVersion,
                                 @CriteriaConfigCreatedById,
                                 @CriteriaConfigCreatedOn,
                                 @CriteriaConfigModifiedById,
                                 @CriteriaConfigModifiedOn,
                                 @CriteriaConfigCriteriaOUId,
                                 @CriteriaConfigRecordsPerPage,
                                 @ReportFormatId,
                                 @DefaultReportViewId,
                                 @CriteriaConfigSourceType,
                                 @UserCriteriaRunType
                            );
                            ";

        public const string SAVE_CRITERIACONFIGSECTION = @"INSERT 
                            INTO TCRITERIACONFIGSECTION (
                                 CRITERIACONFIGSECTIONID,
                                 CRITERIACONFIGID,
                                 SLNO,
                                 SECTIONJOIN,
                                 STATUS,
                                 VERSION,
                                 CREATEDBYID,
                                 CREATEDON,
                                 MODIFIEDBYID,
                                 MODIFIEDON
                            ) OUTPUT INSERTED.CriteriaConfigSectionId VALUES (
                                 @CriteriaConfigSectionId,
                                 @CriteriaConfigId,
                                 @CriteriaConfigSectionSlNo,
                                 @CriteriaConfigSectionSectionJoin,
                                 @CriteriaConfigSectionStatus,
                                 @CriteriaConfigSectionVersion,
                                 @CriteriaConfigSectionCreatedById,
                                 @CriteriaConfigSectionCreatedOn,
                                 @CriteriaConfigSectionModifiedById,
                                 @CriteriaConfigSectionModifiedOn
                            );
                            ";

        public const string SAVE_CRITERIACONFIGATTRIBUTE = @"INSERT 
                            INTO TCRITERIACONFIGATTRIBUTE (
                                 CRITERIACONFIGATTRIBUTEID,
                                 CRITERIASECTIONID,
                                 SLNO,
                                 CRITERIAATTRIBUTEID,
                                 OPERATIONTYPE,
                                 CRITERIAFIELDVALUE,
                                 CRITERIAFIELDVALUEIN,
                                 CRITERIAJOIN,
                                 CRITERIAFIELDDISPLAYVALUE,
                                 CRITERIAVARIABLEFIELD,
                                 WEBSERVICECRITERIAID
                            ) OUTPUT INSERTED.CriteriaConfigAttributeId VALUES (
                                 @CriteriaConfigAttributeId,
                                 @CriteriaSectionId,
                                 @CriteriaConfigAttributeSlNo,
                                 @CriteriaAttributeId,
                                 @CriteriaConfigAttributeOperationType,
                                 @CriteriaConfigAttributeCriteriaFieldValue,
                                 @CriteriaConfigAttributeCriteriaFieldValueIn,
                                 @CriteriaConfigAttributeCriteriaJoin,
                                 @CriteriaConfigAttributeCriteriaFieldDisplayValue,
                                 @CriteriaConfigAttributeCriteriaVariableField,
                                 @WebServiceCriteriaId
                            );
                            ";

        public const string UPDATE_CRITERIACONFIG = @"UPDATE 
                            TCRITERIACONFIG SET
                            CRITERIACONFIGNAME = @CriteriaConfigName,
                            CRITERIASERVICEID = @CriteriaConfigCriteriaServiceId,
                            CRITERIACONFIGTYPE = @CriteriaConfigCriteriaConfigType,
                            CRITERIAUSERID = @CriteriaConfigCriteriaUserId,
                            PERIODFILTER = @CriteriaConfigPeriodFilter,
                            CRITERIAMENUID = @CriteriaConfigCriteriaMenuId,
                            STATUS = @CriteriaConfigStatus,
                            VERSION = @CriteriaConfigVersion,
                            MODIFIEDBYID = @CriteriaConfigModifiedById,
                            MODIFIEDON = @CriteriaConfigModifiedOn,
                            CRITERIAOUID = @CriteriaConfigCriteriaOUId,
                            RECORDSPERPAGE = @CriteriaConfigRecordsPerPage,
                            REPORTFORMATID = @ReportFormatId,
                            DEFAULTREPORTVIEWID = @DefaultReportViewId,
                            SOURCETYPE = @CriteriaConfigSourceType,
                            CRITERIARUNTYPE = @UserCriteriaRunType
                            WHERE CriteriaConfigId = @CriteriaConfigId;
                            ";

        public const string UPDATE_CRITERIACONFIGSECTION = @"UPDATE 
                            TCRITERIACONFIGSECTION SET
                            CRITERIACONFIGID = @CriteriaConfigId,
                            SLNO = @CriteriaConfigSectionSlNo,
                            SECTIONJOIN = @CriteriaConfigSectionSectionJoin,
                            STATUS = @CriteriaConfigSectionStatus,
                            VERSION = @CriteriaConfigSectionVersion,
                            MODIFIEDBYID = @CriteriaConfigSectionModifiedById,
                            MODIFIEDON = @CriteriaConfigSectionModifiedOn
                            WHERE CriteriaConfigSectionId = @CriteriaConfigSectionId AND CriteriaConfigId = @CriteriaConfigId;
                            ";

        public const string UPDATE_CRITERIACONFIGATTRIBUTE = @"UPDATE 
                            TCRITERIACONFIGATTRIBUTE SET
                            CRITERIASECTIONID = @CriteriaSectionId,
                            SLNO = @CriteriaConfigAttributeSlNo,
                            CRITERIAATTRIBUTEID = @CriteriaAttributeId,
                            OPERATIONTYPE = @CriteriaConfigAttributeOperationType,
                            CRITERIAFIELDVALUE = @CriteriaConfigAttributeCriteriaFieldValue,
                            CRITERIAFIELDVALUEIN = @CriteriaConfigAttributeCriteriaFieldValueIn,
                            CRITERIAJOIN = @CriteriaConfigAttributeCriteriaJoin,
                            CRITERIAFIELDDISPLAYVALUE = @CriteriaConfigAttributeCriteriaFieldDisplayValue,
                            CRITERIAVARIABLEFIELD = @CriteriaConfigAttributeCriteriaVariableField,
                            WEBSERVICECRITERIAID = @WebServiceCriteriaId
                            WHERE CriteriaConfigAttributeId = @CriteriaConfigAttributeId AND CriteriaSectionId = @CriteriaSectionId;
                            ";

        public const string DELETE_CRITERIACONFIG = @"DELETE 
                            FROM TCRITERIACONFIG
                            WHERE CRITERIACONFIGID = @criteriaconfigid;
                            ";

        public const string DELETE_CRITERIACONFIGSECTION = @"DELETE 
                            FROM TCRITERIACONFIGSECTION
                            WHERE CRITERIACONFIGID = @criteriaconfigid;
                            ";

        public const string DELETE_CRITERIACONFIGATTRIBUTE = @"DELETE 
                            FROM TCRITERIACONFIGATTRIBUTE
                            WHERE CRITERIASECTIONID IN
                            (
                            SELECT CRITERIACONFIGSECTIONID
                            FROM TCRITERIACONFIGSECTION
                            WHERE CRITERIACONFIGID = @criteriaconfigid
                            );
                            ";

        public const string GET_MAX_CRITERIACONFIGSECTIONID = @"SELECT
                            ISNULL(MIN(CRITERIACONFIGSECTIONID), 0)
                            FROM TCRITERIACONFIGSECTION
                            WHERE CRITERIACONFIGID = @criteriaconfigid;
                            ";

        public const string GET_MAX_CRITERIACONFIGATTRIBUTEID = @"SELECT
                            ISNULL(MIN(CCA.CRITERIACONFIGATTRIBUTEID), 0)
                            FROM TCRITERIACONFIGATTRIBUTE CCA
                            INNER JOIN TCRITERIACONFIGSECTION CCS ON CCA.CRITERIASECTIONID = CCS.CRITERIACONFIGSECTIONID
                            WHERE CCS.CRITERIACONFIGID = @criteriaconfigid;
                            ";

        public const string UPDATE_USERVSPORTLET = @"UPDATE 
                            MUSERVSPORTLET SET 
                            CRITERIACONFIGID = @CriteriaConfigId
                            WHERE USERVSPORTLETID = @UserVsPortletId;
                            ";
    }
}