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

namespace GB5Shared.Query.FrameWork.CriteriaConfig
{
    public class CriteriaConfigQuery
    {
        public const string GET_CRITERIACONFIG_BASED_ON_CRITERIA_CONFIG_ID =
            @"select  
           criteriaconfig from TCriteriaConfig criteriaconfig where 
           criteriaconfig.Id =:criteriaconfigid" + "\r\n";

        public const string GET_CRITERIACONFIG = @"SELECT
                            a.CRITERIACONFIGID     AS CriteriaConfigId,  
                            a.CRITERIACONFIGNAME   AS CriteriaConfigName,  
                            a.CRITERIASERVICEID    AS CriteriaConfigCriteriaServiceId,
                            ser.webservicename     AS CriteriaConfigServiceName,
                            a.CRITERIACONFIGTYPE   AS CriteriaConfigCriteriaConfigType,  
                            a.CRITERIAUSERID       AS CriteriaConfigCriteriaUserId,  
                            ccuser.usercode        AS CriteriaConfigUserCode,
                            ccuser.username        AS CriteriaConfigUserName,
                            a.PERIODFILTER         AS CriteriaConfigPeriodFilter,  
                            a.CRITERIAMENUID       AS CriteriaConfigCriteriaMenuId,  
                            menu.menucode          AS CriteriaConfigCriteriaMenuCode,
                            menu.menuname          AS CriteriaConfigCriteriaMenuName,
                            0                      AS CriteriaConfigIsDefault,
                            a.sourcetype           AS CriteriaConfigSourceType,
                            a.STATUS               AS CriteriaConfigStatus,  
                            a.VERSION              AS CriteriaConfigVersion,  
                            a.CREATEDBYID          AS CriteriaConfigCreatedById,
                            cby.username           AS CriteriaConfigCreatedByName,
                            a.CREATEDON            AS CriteriaConfigCreatedOn,  
                            a.MODIFIEDBYID         AS CriteriaConfigModifiedById,
                            mby.username           AS CriteriaConfigModifiedByName,
                            a.MODIFIEDON           AS CriteriaConfigModifiedOn,  
                            a.CRITERIAOUID         AS CriteriaConfigCriteriaOUId,
                            ou.OrganizationUnitCode      AS CriteriaConfigOrganizationUnitCode,
                            ou.OrganizationUnitName      AS CriteriaConfigOrganizationUnitName,  
                            a.RECORDSPERPAGE        AS CriteriaConfigRecordsPerPage,  
                            a.REPORTFORMATID        AS ReportFormatId,  
                            a.DEFAULTREPORTVIEWID   AS DefaultReportViewId,  
                            a.SOURCETYPE            AS CriteriaConfigSourceType,    
                            rf.ReportFormatName     AS ReportFormatName,      
                            b.CRITERIACONFIGSECTIONID                         AS CriteriaConfigSectionId,
                            B.SLNO                                            AS CriteriaConfigSectionSlNo,
                            B.SECTIONJOIN                                     AS CriteriaConfigSectionSectionJoin,  
                            C.CRITERIACONFIGATTRIBUTEID                       AS CriteriaConfigAttributeId,
                            C.SLNO                                            AS CriteriaConfigAttributeSlNo,
                            C.CRITERIAATTRIBUTEID                             AS CriteriaAttributeId,
                            cratt.CRITERIAATTRIBUTEname                       AS CriteriaAttributeName,
                            cratt.filtertype                                  AS CriteriaAttributeFilterType,
                            C.OPERATIONTYPE                                   AS CriteriaConfigAttributeOperationType,
                            C.CRITERIAFIELDVALUE                              AS CriteriaConfigAttributeCriteriaFieldValue,
                            C.CRITERIAFIELDVALUEIN                            AS CriteriaConfigAttributeCriteriaFieldValueIn,
                            C.CRITERIAJOIN                                    AS CriteriaConfigAttributeCriteriaJoin,
                            C.CRITERIAFIELDDISPLAYVALUE                       AS CriteriaConfigAttributeCriteriaFieldDisplayValue,
                            C.CRITERIAVARIABLEFIELD                           AS CriteriaConfigAttributeCriteriaVariableField,
                            C.WEBSERVICECRITERIAID                            AS WebServiceCriteriaId,
                            ''                                                AS CriteriaConfigAttributeCriteriaFieldDisplay
                        FROM
                            TCriteriaConfig a,
                            tcriteriaConfigSECTION b,
                            TCRITERIACONFIGATTRIBUTE c,
                            muser cby,
                            muser mby,
                            muser ccuser,
                            mmenu menu,
                            mOrganizationUnit ou,
                            mreportformat rf,
                            mwebservice ser,
                            mcriteriaattribute cratt
                        WHERE
                            a.CriteriaConfigId = b.CriteriaConfigID
                            AND b.CRITERIACONFIGSECTIONID = C.CRITERIASECTIONID
                            AND a.CRITERIAUSERID = ccuser.userid
                            AND a.createdbyid = cby.userid
                            AND a.modifiedbyid = mby.userid
                            AND a.criteriamenuid = menu.menuid
                            AND a.criteriaouid = ou.ouid
                            AND a.reportformatid = rf.reportformatid
                            AND a.criteriaserviceid = ser.webserviceid
                            AND c.CRITERIAATTRIBUTEID = cratt.CRITERIAATTRIBUTEID
                            -- AND a.CRITERIACONFIID = @CRITERIACONFIID";
    }
}