<commit_before>from werkzeug.exceptions import BadRequest

from model.participant_summary import ParticipantSummary
from participant_enums import EnrollmentStatus, TEST_HPO_NAME, TEST_EMAIL_PATTERN
from participant_enums import WithdrawalStatus
from dao.hpo_dao import HPODao
from dao.base_dao import BaseDao

class ParticipantCountsOverTimeService(BaseDao):

  def __init__(self):
    super(ParticipantCountsOverTimeService, self).__init__(ParticipantSummary, backup=True)

  def get_filtered_results(self, start_date, end_date, filters, filter_by,
                           stratification='ENROLLMENT_STATUS'):
    """Queries DB, returns results in format consumed by front-end

    :param start_date: Start date object
    :param end_date: End date object
    :param filters: Objects representing filters specified in UI
    :param filter_by: indicate how to filter the core participant
    :param stratification: How to stratify (layer) results, as in a stacked bar chart
    :return: Filtered, stratified results by date
    """

    self.test_hpo_id = HPODao().get_by_name(TEST_HPO_NAME).hpoId
    self.test_email_pattern = TEST_EMAIL_PATTERN

    # save the enrollment statuses requirements for filtering the SQL result later
    if 'enrollment_statuses' in filters and filters['enrollment_statuses'] is not None:
      enrollment_statuses = [str(val) for val in filters['enrollment_statuses']]
    else:
      enrollment_statuses = []

    # Filters for participant_summary (ps) and participant (p) table
    # filters_sql_ps is used in the general case when we're querying participant_summary
    # filters_sql_p is used when also LEFT OUTER JOINing p and ps
    filters_sql_ps = self.get_facets_sql(filters, stratification)
    filters_sql_p = self.get_facets_sql(filters, stratification, table_prefix='p')

    if str(stratification) == 'TOTAL':
      strata = ['TOTAL']
      sql = self.get_total_sql(filters_sql_ps)
    elif str(stratification) == 'ENROLLMENT_STATUS':
      strata = [str(val) for val in EnrollmentStatus]
      sql = self.get_enrollment_status_sql(filters_sql_p, filter_by)
    elif str(stratification) == 'EHR_CONSENT':
      strata = ['EHR_CONSENT']
      sql = self.get_total_sql(filters_sql_ps, ehr_count=True)
    elif str(stratification) == 'EHR_RATIO':
      strata = ['EHR_RATIO']
      sql = self.get_ratio_sql(filters_sql_ps)
    else:
      raise BadRequest('Invalid stratification: %s' % stratification)

    params = {'start_date': start_date, 'end_date': end_date}

    results_by_date = []

    with self.session() as session:
      cursor = session.execute(sql, params)

    # Iterate through each result (by date), transforming tabular SQL results
    # into expected list-of-dictionaries response format
    try:
      results = cursor.fetchall()
      for result in results:
        date = result[-1]
        metrics = {}
        values = result[:-1]
        for i, value in enumerate(values):
          key = strata[i]
          if value is None or (str(stratification) == 'ENROLLMENT_STATUS'
                               and enrollment_statuses
                               and key not in enrollment_statuses):
            value = 0
          metrics[key] = int(value)
        results_by_date.append({
          'date': str(date),
          'metrics': metrics
        })
    finally:
      cursor.close()

    return results_by_date

  def get_facets_sql(self, facets, stratification, table_prefix='ps'):
    """Helper function to transform facets/filters selection into SQL

    :param facets: Object representing facets and filters to apply to query results
    :param stratification: How to stratify (layer) results, as in a stacked bar chart
    :param table_prefix: Either 'ps' (for participant_summary) or 'p' (for participant)
    :return: SQL for 'WHERE' clause, reflecting filters specified in UI
    """

    facets_sql = 'WHERE '
    facets_sql_list = []

    facet_map = {
      'awardee_ids': 'hpo_id',
      'enrollment_statuses': 'enrollment_status'
    }

    # the SQL for ENROLLMENT_STATUS stratify is using the enrollment status time
    # instead of enrollment status
    if 'enrollment_statuses' in facets and str(stratification) == 'ENROLLMENT_STATUS':
      del facets['enrollment_statuses']
      del facet_map['enrollment_statuses']

    for facet in facets:
      filter_prefix = table_prefix
      filters_sql = []
      db_field = facet_map[facet]
      filters = facets[facet]

      allow_null = False
      if db_field == 'enrollment_status':
        filter_prefix = 'ps'
        allow_null = True

      # TODO:
      # Consider using an IN clause with bound parameters, instead, which
      # would be simpler than this,
      #
      # TODO:
      # Consider using bound parameters here instead of inlining the values
      # in the SQL. We do that in other places using this function:
      #
      # dao/database_utils.py#L16
      #
      # This may help the SQL perform slightly better since the execution
      # plan for the query can be cached when the only thing changing are
      # the bound params.
      for q_filter in filters:
        if str(q_filter) != '':
          filter_sql = filter_prefix + '.' + db_field + ' = ' + str(int(q_filter))
          if allow_null and str(int(q_filter)) == '1':
            filters_sql.append('(' + filter_sql + ' or ' + filter_prefix
                               + '.' + db_field + ' IS NULL)')
          else:
            filters_sql.append(filter_sql)
      if len(filters_sql) > 0:
        filters_sql = '(' + ' OR '.join(filters_sql) + ')'
        facets_sql_list.append(filters_sql)

    if len(facets_sql_list) > 0:
      facets_sql += ' AND '.join(facets_sql_list) + ' AND'

    # TODO: use bound parameters
    # See https://github.com/all-of-us/raw-data-repository/pull/669/files/a08be0ffe445da60ebca13b41d694368e4d42617#diff-6c62346e0cbe4a7fd7a45af6d4559c3e  # pylint: disable=line-too-long
    facets_sql += ' %(table_prefix)s.hpo_id != %(test_hpo_id)s ' % {
      'table_prefix': table_prefix, 'test_hpo_id': self.test_hpo_id}
    facets_sql += ' AND (ps.email IS NULL OR NOT ps.email LIKE "%(test_email_pattern)s")' % {
      'test_email_pattern': self.test_email_pattern}
    facets_sql += ' AND %(table_prefix)s.withdrawal_status = %(not_withdrawn)i' % {
      'table_prefix': table_prefix, 'not_withdrawn': WithdrawalStatus.NOT_WITHDRAWN}

    return facets_sql

  def get_total_sql(self, filters_sql, ehr_count=False):
    if ehr_count:
      # Participants with EHR Consent
      required_count = 'ps.consent_for_electronic_health_records = 1'
    else:
      # All participants
      required_count = '*'

    sql = """
        SELECT
            SUM(ps_sum.cnt * (ps_sum.day <= calendar.day)) registered_count,
            calendar.day start_date
        FROM calendar,
        (SELECT COUNT(%(count)s) cnt, DATE(p.sign_up_time) day
        FROM participant p
        LEFT OUTER JOIN participant_summary ps ON p.participant_id = ps.participant_id
        %(filters)s
        GROUP BY day) ps_sum
        WHERE calendar.day >= :start_date
        AND calendar.day <= :end_date
        GROUP BY calendar.day
        ORDER BY calendar.day;
      """ % {'filters': filters_sql, 'count': required_count}

    return sql

  def get_ratio_sql(self, filters_sql):
    sql = """
        SELECT
            SUM(ps_sum.ratio * (ps_sum.day <= calendar.day)) ratio,
            calendar.day start_date
        FROM calendar,
        (SELECT avg(ps.consent_for_electronic_health_records = 2) ratio, DATE(p.sign_up_time) day
        FROM participant p
        LEFT OUTER JOIN participant_summary ps ON p.participant_id = ps.participant_id
        %(filters)s
        GROUP BY day) ps_sum
        WHERE calendar.day >= :start_date
        AND calendar.day <= :end_date
        GROUP BY calendar.day
        ORDER BY calendar.day;<commit_msg>change value in sql (#885)<commit_after>from werkzeug.exceptions import BadRequest

from model.participant_summary import ParticipantSummary
from participant_enums import EnrollmentStatus, TEST_HPO_NAME, TEST_EMAIL_PATTERN
from participant_enums import WithdrawalStatus
from dao.hpo_dao import HPODao
from dao.base_dao import BaseDao

class ParticipantCountsOverTimeService(BaseDao):

  def __init__(self):
    super(ParticipantCountsOverTimeService, self).__init__(ParticipantSummary, backup=True)

  def get_filtered_results(self, start_date, end_date, filters, filter_by,
                           stratification='ENROLLMENT_STATUS'):
    """Queries DB, returns results in format consumed by front-end

    :param start_date: Start date object
    :param end_date: End date object
    :param filters: Objects representing filters specified in UI
    :param filter_by: indicate how to filter the core participant
    :param stratification: How to stratify (layer) results, as in a stacked bar chart
    :return: Filtered, stratified results by date
    """

    self.test_hpo_id = HPODao().get_by_name(TEST_HPO_NAME).hpoId
    self.test_email_pattern = TEST_EMAIL_PATTERN

    # save the enrollment statuses requirements for filtering the SQL result later
    if 'enrollment_statuses' in filters and filters['enrollment_statuses'] is not None:
      enrollment_statuses = [str(val) for val in filters['enrollment_statuses']]
    else:
      enrollment_statuses = []

    # Filters for participant_summary (ps) and participant (p) table
    # filters_sql_ps is used in the general case when we're querying participant_summary
    # filters_sql_p is used when also LEFT OUTER JOINing p and ps
    filters_sql_ps = self.get_facets_sql(filters, stratification)
    filters_sql_p = self.get_facets_sql(filters, stratification, table_prefix='p')

    if str(stratification) == 'TOTAL':
      strata = ['TOTAL']
      sql = self.get_total_sql(filters_sql_ps)
    elif str(stratification) == 'ENROLLMENT_STATUS':
      strata = [str(val) for val in EnrollmentStatus]
      sql = self.get_enrollment_status_sql(filters_sql_p, filter_by)
    elif str(stratification) == 'EHR_CONSENT':
      strata = ['EHR_CONSENT']
      sql = self.get_total_sql(filters_sql_ps, ehr_count=True)
    elif str(stratification) == 'EHR_RATIO':
      strata = ['EHR_RATIO']
      sql = self.get_ratio_sql(filters_sql_ps)
    else:
      raise BadRequest('Invalid stratification: %s' % stratification)

    params = {'start_date': start_date, 'end_date': end_date}

    results_by_date = []

    with self.session() as session:
      cursor = session.execute(sql, params)

    # Iterate through each result (by date), transforming tabular SQL results
    # into expected list-of-dictionaries response format
    try:
      results = cursor.fetchall()
      for result in results:
        date = result[-1]
        metrics = {}
        values = result[:-1]
        for i, value in enumerate(values):
          key = strata[i]
          if value is None or (str(stratification) == 'ENROLLMENT_STATUS'
                               and enrollment_statuses
                               and key not in enrollment_statuses):
            value = 0
          metrics[key] = int(value)
        results_by_date.append({
          'date': str(date),
          'metrics': metrics
        })
    finally:
      cursor.close()

    return results_by_date

  def get_facets_sql(self, facets, stratification, table_prefix='ps'):
    """Helper function to transform facets/filters selection into SQL

    :param facets: Object representing facets and filters to apply to query results
    :param stratification: How to stratify (layer) results, as in a stacked bar chart
    :param table_prefix: Either 'ps' (for participant_summary) or 'p' (for participant)
    :return: SQL for 'WHERE' clause, reflecting filters specified in UI
    """

    facets_sql = 'WHERE '
    facets_sql_list = []

    facet_map = {
      'awardee_ids': 'hpo_id',
      'enrollment_statuses': 'enrollment_status'
    }

    # the SQL for ENROLLMENT_STATUS stratify is using the enrollment status time
    # instead of enrollment status
    if 'enrollment_statuses' in facets and str(stratification) == 'ENROLLMENT_STATUS':
      del facets['enrollment_statuses']
      del facet_map['enrollment_statuses']

    for facet in facets:
      filter_prefix = table_prefix
      filters_sql = []
      db_field = facet_map[facet]
      filters = facets[facet]

      allow_null = False
      if db_field == 'enrollment_status':
        filter_prefix = 'ps'
        allow_null = True

      # TODO:
      # Consider using an IN clause with bound parameters, instead, which
      # would be simpler than this,
      #
      # TODO:
      # Consider using bound parameters here instead of inlining the values
      # in the SQL. We do that in other places using this function:
      #
      # dao/database_utils.py#L16
      #
      # This may help the SQL perform slightly better since the execution
      # plan for the query can be cached when the only thing changing are
      # the bound params.
      for q_filter in filters:
        if str(q_filter) != '':
          filter_sql = filter_prefix + '.' + db_field + ' = ' + str(int(q_filter))
          if allow_null and str(int(q_filter)) == '1':
            filters_sql.append('(' + filter_sql + ' or ' + filter_prefix
                               + '.' + db_field + ' IS NULL)')
          else:
            filters_sql.append(filter_sql)
      if len(filters_sql) > 0:
        filters_sql = '(' + ' OR '.join(filters_sql) + ')'
        facets_sql_list.append(filters_sql)

    if len(facets_sql_list) > 0:
      facets_sql += ' AND '.join(facets_sql_list) + ' AND'

    # TODO: use bound parameters
    # See https://github.com/all-of-us/raw-data-repository/pull/669/files/a08be0ffe445da60ebca13b41d694368e4d42617#diff-6c62346e0cbe4a7fd7a45af6d4559c3e  # pylint: disable=line-too-long
    facets_sql += ' %(table_prefix)s.hpo_id != %(test_hpo_id)s ' % {
      'table_prefix': table_prefix, 'test_hpo_id': self.test_hpo_id}
    facets_sql += ' AND (ps.email IS NULL OR NOT ps.email LIKE "%(test_email_pattern)s")' % {
      'test_email_pattern': self.test_email_pattern}
    facets_sql += ' AND %(table_prefix)s.withdrawal_status = %(not_withdrawn)i' % {
      'table_prefix': table_prefix, 'not_withdrawn': WithdrawalStatus.NOT_WITHDRAWN}

    return facets_sql

  def get_total_sql(self, filters_sql, ehr_count=False):
    if ehr_count:
      # Participants with EHR Consent
      required_count = 'ps.consent_for_electronic_health_records = 1'
    else:
      # All participants
      required_count = '*'

    sql = """
        SELECT
            SUM(ps_sum.cnt * (ps_sum.day <= calendar.day)) registered_count,
            calendar.day start_date
        FROM calendar,
        (SELECT COUNT(%(count)s) cnt, DATE(p.sign_up_time) day
        FROM participant p
        LEFT OUTER JOIN participant_summary ps ON p.participant_id = ps.participant_id
        %(filters)s
        GROUP BY day) ps_sum
        WHERE calendar.day >= :start_date
        AND calendar.day <= :end_date
        GROUP BY calendar.day
        ORDER BY calendar.day;
      """ % {'filters': filters_sql, 'count': required_count}

    return sql

  def get_ratio_sql(self, filters_sql):
    sql = """
        SELECT
            SUM(ps_sum.ratio * (ps_sum.day <= calendar.day)) ratio,
            calendar.day start_date
        FROM calendar,
        (SELECT avg(ps.consent_for_electronic_health_records = 1) ratio,
        DATE(p.sign_up_time) day
        FROM participant p
        LEFT OUTER JOIN participant_summary ps ON p.participant_id = ps.participant_id
        %(filters)s
        GROUP BY day) ps_sum
        WHERE calendar.day >= :start_date
        AND calendar.day <= :end_date
        GROUP BY calendar.day
        ORDER BY calendar.day;
{'fraction_non_alphanumeric': 0.08906754447046908, 'fraction_numerical': 0.007153256947521681, 'mean_word_length': 2.9544430538172715, 'pattern_counts': {'":': 0, '<': 11, '<?xml version=': 0, '>': 11, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4436180', 'n_tokens_mistral': 5006, 'n_tokens_neox': 4778, 'n_words': 1293}