<commit_before>package mil.dds.anet.search.pg;

import mil.dds.anet.beans.UserActivity;
import mil.dds.anet.beans.search.UserActivitySearchQuery;
import mil.dds.anet.search.AbstractSearchQueryBuilder;
import mil.dds.anet.search.AbstractUserActivitySearcher;

public class PostgresqlUserActivitySearcher extends AbstractUserActivitySearcher {

  public PostgresqlUserActivitySearcher() {
    super(new PostgresqlSearchQueryBuilder<>("PostgresqlUserActivitySearch"));
  }

  @Override
  protected void buildQuery(final UserActivitySearchQuery query) {
    final PostgresqlSearchQueryBuilder<UserActivity, UserActivitySearchQuery> withQb =
        new PostgresqlSearchQueryBuilder<>("with-clause");
    switch (query.getSearchType()) {
      case PERSON:
        queryByPerson(query, withQb);
        break;
      case ORGANIZATION:
        queryByOrganization(query, withQb);
        break;
      case TOP_LEVEL_ORGANIZATION:
        queryByTopLevelOrganization(query, withQb);
        break;
    }
    qb.addWithClause(String.format("%1$s AS (%2$s)", WITH_CLAUSE_NAME, withQb.build()));
    qb.addSqlArgs(withQb.getSqlArgs());
    super.buildQuery(query);
  }

  private void queryByPerson(final UserActivitySearchQuery query,
      final PostgresqlSearchQueryBuilder<UserActivity, UserActivitySearchQuery> withQb) {
    withQb.addSelectClause("u.\"personUuid\", COUNT(*)");
    withQb.addFromClause("\"userActivities\" u");
    withQb.addDateRangeClause("startDate", "u.\"visitedAt\"",
        AbstractSearchQueryBuilder.Comparison.AFTER, query.getStartDate(), "endDate",
        "u.\"visitedAt\"", AbstractSearchQueryBuilder.Comparison.BEFORE, query.getEndDate());
    if (!query.getShowDeleted()) {
      withQb.addWhereClause("EXISTS (SELECT uuid FROM people WHERE uuid = u.\"personUuid\")");
    }
    withQb.addGroupByClause("u.\"personUuid\"");
  }

  private void queryByOrganization(final UserActivitySearchQuery query,
      final PostgresqlSearchQueryBuilder<UserActivity, UserActivitySearchQuery> withQb) {
    withQb.addSelectClause("p.\"organizationUuid\", COUNT(*)");
    withQb.addFromClause("\"userActivities\" u");
    withQb.addFromClause("LEFT JOIN positions p ON p.\"currentPersonUuid\" = u.\"personUuid\"");
    withQb.addDateRangeClause("startDate", "u.\"visitedAt\"",
        AbstractSearchQueryBuilder.Comparison.AFTER, query.getStartDate(), "endDate",
        "u.\"visitedAt\"", AbstractSearchQueryBuilder.Comparison.BEFORE, query.getEndDate());
    if (!query.getShowDeleted()) {
      withQb.addWhereClause("p.\"organizationUuid\" IS NOT NULL");
    }
    withQb.addGroupByClause("p.\"organizationUuid\"");
  }

  private void queryByTopLevelOrganization(final UserActivitySearchQuery query,
      final PostgresqlSearchQueryBuilder<UserActivity, UserActivitySearchQuery> withQb) {
    withQb.createWithClause(null, "parent_orgs", "organizations", "\"parentOrgUuid\"", false);
    withQb.addSelectClause("parent_orgs.parent_uuid AS \"organizationUuid\", COUNT(*)");
    withQb.addFromClause("\"userActivities\" u");
    withQb.addFromClause("LEFT JOIN positions p ON p.\"currentPersonUuid\" = u.\"personUuid\"");
    withQb.addFromClause("LEFT JOIN parent_orgs ON parent_orgs.uuid = p.\"organizationUuid\"");
    withQb.addDateRangeClause("startDate", "u.\"visitedAt\"",
        AbstractSearchQueryBuilder.Comparison.AFTER, query.getStartDate(), "endDate",
        "u.\"visitedAt\"", AbstractSearchQueryBuilder.Comparison.BEFORE, query.getEndDate());
    final String parentQueryFmt =
        "(%s parent_orgs.parent_uuid IN (SELECT uuid FROM organizations WHERE \"parentOrgUuid\" IS NULL))";
    withQb.addWhereClause(String.format(parentQueryFmt,
        !query.getShowDeleted() ? "" : "parent_orgs.uuid IS NULL OR"));
    withQb.addGroupByClause("parent_orgs.parent_uuid");
  }
}
<commit_msg>AB#218: Update query to use new organizationUuid column<commit_after>package mil.dds.anet.search.pg;

import mil.dds.anet.beans.UserActivity;
import mil.dds.anet.beans.search.UserActivitySearchQuery;
import mil.dds.anet.search.AbstractSearchQueryBuilder;
import mil.dds.anet.search.AbstractUserActivitySearcher;

public class PostgresqlUserActivitySearcher extends AbstractUserActivitySearcher {

  public PostgresqlUserActivitySearcher() {
    super(new PostgresqlSearchQueryBuilder<>("PostgresqlUserActivitySearch"));
  }

  @Override
  protected void buildQuery(final UserActivitySearchQuery query) {
    final PostgresqlSearchQueryBuilder<UserActivity, UserActivitySearchQuery> withQb =
        new PostgresqlSearchQueryBuilder<>("with-clause");
    switch (query.getSearchType()) {
      case PERSON:
        queryByPerson(query, withQb);
        break;
      case ORGANIZATION:
        queryByOrganization(query, withQb);
        break;
      case TOP_LEVEL_ORGANIZATION:
        queryByTopLevelOrganization(query, withQb);
        break;
    }
    qb.addWithClause(String.format("%1$s AS (%2$s)", WITH_CLAUSE_NAME, withQb.build()));
    qb.addSqlArgs(withQb.getSqlArgs());
    super.buildQuery(query);
  }

  private void queryByPerson(final UserActivitySearchQuery query,
      final PostgresqlSearchQueryBuilder<UserActivity, UserActivitySearchQuery> withQb) {
    withQb.addSelectClause("u.\"personUuid\", COUNT(*)");
    withQb.addFromClause("\"userActivities\" u");
    withQb.addDateRangeClause("startDate", "u.\"visitedAt\"",
        AbstractSearchQueryBuilder.Comparison.AFTER, query.getStartDate(), "endDate",
        "u.\"visitedAt\"", AbstractSearchQueryBuilder.Comparison.BEFORE, query.getEndDate());
    if (!query.getShowDeleted()) {
      withQb.addWhereClause("EXISTS (SELECT uuid FROM people WHERE uuid = u.\"personUuid\")");
    }
    withQb.addGroupByClause("u.\"personUuid\"");
  }

  private void queryByOrganization(final UserActivitySearchQuery query,
      final PostgresqlSearchQueryBuilder<UserActivity, UserActivitySearchQuery> withQb) {
    withQb.addSelectClause("u.\"organizationUuid\", COUNT(*)");
    withQb.addFromClause("\"userActivities\" u");
    withQb.addDateRangeClause("startDate", "u.\"visitedAt\"",
        AbstractSearchQueryBuilder.Comparison.AFTER, query.getStartDate(), "endDate",
        "u.\"visitedAt\"", AbstractSearchQueryBuilder.Comparison.BEFORE, query.getEndDate());
    if (!query.getShowDeleted()) {
      withQb.addWhereClause(
          "EXISTS (SELECT uuid FROM organizations WHERE uuid = u.\"organizationUuid\")");
    }
    withQb.addGroupByClause("u.\"organizationUuid\"");
  }

  private void queryByTopLevelOrganization(final UserActivitySearchQuery query,
      final PostgresqlSearchQueryBuilder<UserActivity, UserActivitySearchQuery> withQb) {
    withQb.createWithClause(null, "parent_orgs", "organizations", "\"parentOrgUuid\"", false);
    withQb.addSelectClause("parent_orgs.parent_uuid AS \"organizationUuid\", COUNT(*)");
    withQb.addFromClause("\"userActivities\" u");
    withQb.addFromClause("LEFT JOIN parent_orgs ON parent_orgs.uuid = u.\"organizationUuid\"");
    withQb.addDateRangeClause("startDate", "u.\"visitedAt\"",
        AbstractSearchQueryBuilder.Comparison.AFTER, query.getStartDate(), "endDate",
        "u.\"visitedAt\"", AbstractSearchQueryBuilder.Comparison.BEFORE, query.getEndDate());
    final String parentQueryFmt =
        "(%s parent_orgs.parent_uuid IN (SELECT uuid FROM organizations WHERE \"parentOrgUuid\" IS NULL))";
    withQb.addWhereClause(String.format(parentQueryFmt,
        !query.getShowDeleted() ? "" : "parent_orgs.uuid IS NULL OR"));
    withQb.addGroupByClause("parent_orgs.parent_uuid");
  }
}

{'fraction_non_alphanumeric': 0.12897667020148462, 'fraction_numerical': 0.0009278897136797455, 'mean_word_length': 4.759541984732825, 'pattern_counts': {'":': 0, '<': 15, '<?xml version=': 0, '>': 15, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '4046472', 'n_tokens_mistral': 2345, 'n_tokens_neox': 2329, 'n_words': 366}