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

namespace AnalyticsDAL.Query.AnalysisFields
{
    public class AnalysisFieldsQB
    {
        public const string GET_ANALYSISFIELDS = @"SELECT
                                                    AF.ANALYSISFIELDID          AS AnalysisFieldsId,
                                                    AF.ANALYSISID               AS AnalysisId,
                                                    A.ANALYSISCODE              AS AnalysisCode,
                                                    A.ANALYSISNAME              AS AnalysisName,
                                                    AF.SLNO                     AS AnalysisFieldsSlNo,
                                                    AF.DBOBJECTFIELDID          AS DBObjectFieldsId,
                                                    DOF.DBFIELDNAME             AS DBObjectFieldsName,
                                                    DOF.DBOBJECTID              AS DBObjectId,
                                                    DB.DBOBJECTNAME             AS DBObjectName,
                                                    AF.FILTEREXPRESSION         AS AnalysisFieldsFilterExpression,
                                                    AF.VARIABLES                AS AnalysisFieldsVariables,
                                                    AF.FIELDTYPE                AS AnalysisFieldsFieldType,
                                                    AF.EXPRESSION               AS AnalysisFieldsExpression,
                                                    AF.DISPLAYNAME              AS AnalysisFieldsDisplayName,
                                                    AF.SORTORDER                AS AnalysisFieldsSortOrder,
                                                    AF.SOURCETYPE               AS AnalysisFieldsSourceType,
                                                    AF.STATUS                   AS AnalysisFieldsStatus,
                                                    AF.VERSION                  AS AnalysisFieldsVersion,
                                                    AF.CREATEDBYID              AS AnalysisFieldsCreatedById,
                                                    AF.CREATEDON                AS AnalysisFieldsCreatedOn,
                                                    AF.MODIFIEDBYID             AS AnalysisFieldsModifiedById,
                                                    AF.MODIFIEDON               AS AnalysisFieldsModifiedOn,
                                                    CU.USERNAME                 AS AnalysisFieldsCreatedByName,
                                                    MU.USERNAME                 AS AnalysisModifiedByName,
                                                    AF.ANALYSISOBJECTID         AS AnalysisObjectId,
                                                    AF.CRITERIAATTRIBUTEID      AS CriteriaAttributeId,
                                                    CA.CRITERIAATTRIBUTENAME    AS CriteriaAttributeName,
                                                    CASE 
                                                        WHEN AF.CRITERIAATTRIBUTEID > 0 
                                                        THEN CAST(1 AS TINYINT)
                                                        ELSE CAST(0 AS TINYINT)
                                                    END                         AS IsFilter,
                                                    AF.TENANTID                 AS AnalysisFieldsTenantId,
                                                    AF.WORKSPACEID              AS AnalyifledsWorkSpaceId
                                                    FROM MANALYSISFIELDS AF
                                                    LEFT JOIN MANALYSIS A ON AF.ANALYSISID = A.ANALYSISID
                                                    LEFT JOIN MANALYSISOBJECT AO ON AF.ANALYSISOBJECTID = AO.ANALYSISOBJECTID
                                                    LEFT JOIN DBOBJECTFIELDS DOF ON AF.DBOBJECTFIELDID = DOF.DBOBJECTFIELDID
                                                    LEFT JOIN MCRITERIAATTRIBUTE CA ON AF.CRITERIAATTRIBUTEID = CA.CRITERIAATTRIBUTEID
                                                    LEFT JOIN MUSER CU ON AF.CREATEDBYID = CU.USERID
                                                    LEFT JOIN MUSER MU ON AF.MODIFIEDBYID = MU.USERID
                                                   WHERE AF.ANALYSISFIELDID = @AnalysisFieldId;";

        public const string SAVE_ANALYSISFIELDS = @"INSERT INTO MANALYSISFIELDS
                                                    (
                                                        ANALYSISFIELDID,
                                                        ANALYSISID,
                                                        SLNO,
                                                        DBOBJECTFIELDID,
                                                        FILTEREXPRESSION,
                                                        VARIABLES,
                                                        FIELDTYPE,
                                                        EXPRESSION,
                                                        DISPLAYNAME,
                                                        SORTORDER,
                                                        SOURCETYPE,
                                                        STATUS,
                                                        VERSION,
                                                        CREATEDBYID,
                                                        CREATEDON,
                                                        MODIFIEDBYID,
                                                        MODIFIEDON,
                                                        ANALYSISOBJECTID,
                                                        CRITERIAATTRIBUTEID,
                                                        TENANTID,
                                                        WORKSPACEID
                                                    )
                                                    VALUES
                                                    (
                                                        @AnalysisFieldsId,
                                                        @AnalysisId,
                                                        @AnalysisFieldsSlNo,
                                                        @DBObjectFieldsId,
                                                        @AnalysisFieldsFilterExpression,
                                                        @AnalysisFieldsVariables,
                                                        @AnalysisFieldsFieldType,
                                                        @AnalysisFieldsExpression,
                                                        @AnalysisFieldsDisplayName,
                                                        @AnalysisFieldsSortOrder,
                                                        @AnalysisFieldsSourceType,
                                                        @AnalysisFieldsStatus,
                                                        @AnalysisFieldsVersion,
                                                        @AnalysisFieldsCreatedById,
                                                        @AnalysisFieldsCreatedOn,
                                                        @AnalysisFieldsModifiedById,
                                                        @AnalysisFieldsModifiedOn,
                                                        @AnalysisObjectId,
                                                        @CriteriaAttributeId,
                                                        @AnalysisFieldsTenantId,
                                                        @AnalyifledsWorkSpaceId
                                                    );";

        public const string UPDATE_ANALYSISFIELDS = @" UPDATE MANALYSISFIELDS
                                                SET
                                                    ANALYSISID = @AnalysisId,
                                                    SLNO = @AnalysisFieldsSlNo,
                                                    DBOBJECTFIELDID = @DBObjectFieldsId,
                                                    FILTEREXPRESSION = @AnalysisFieldsFilterExpression,
                                                    VARIABLES = @AnalysisFieldsVariables,
                                                    FIELDTYPE = @AnalysisFieldsFieldType,
                                                    EXPRESSION = @AnalysisFieldsExpression,
                                                    DISPLAYNAME = @AnalysisFieldsDisplayName,
                                                    SORTORDER = @AnalysisFieldsSortOrder,
                                                    SOURCETYPE = @AnalysisFieldsSourceType,
                                                    STATUS = @AnalysisFieldsStatus,
                                                    VERSION = @AnalysisFieldsVersion,
                                                    MODIFIEDBYID = @AnalysisFieldsModifiedById,
                                                    MODIFIEDON = @AnalysisFieldsModifiedOn,
                                                    ANALYSISOBJECTID = @AnalysisObjectId,
                                                    CRITERIAATTRIBUTEID = @CriteriaAttributeId,
                                                    TENANTID = @AnalysisFieldsTenantId,
                                                    WORKSPACEID = @AnalyifledsWorkSpaceId
                                                WHERE
                                                    ANALYSISFIELDID = @AnalysisFieldsId;";

        public const string GET_SELECTLIST_ANALYSISFIELDS = @"SELECT
                                                        AF.ANALYSISFIELDID AS Id,
                                                        AF.DISPLAYNAME AS Name
                                                    FROM MANALYSISFIELDS AF
                                                    WHERE AF.STATUS     = 1
                                                    AND   AF.ANALYSISID = @AnalysisId
                                                    ORDER BY AF.DISPLAYNAME";

        public const string DELETE_ANALYSISFIELDS = @"DELETE FROM MANALYSISFIELDS
                                                WHERE ANALYSISFIELDID = @AnalysisFieldId";
    }
}