<commit_before>package OpenXPKI::Server::Workflow::Activity::Reports::Timeline;

use strict;
use base qw( OpenXPKI::Server::Workflow::Activity );

use OpenXPKI::Server::Context qw( CTX );
use OpenXPKI::Exception;
use OpenXPKI::Debug;
use Data::Dumper;
use OpenXPKI::DateTime;
use DateTime;

sub execute {

    my $self = shift;
    my $workflow = shift;

    ##! 1: 'Start'
    my $context = $workflow->context();
    my $pki_realm = CTX('session')->data->pki_realm;

    my $target_key = $self->param('target_key');

    my $start_at = OpenXPKI::DateTime::get_validity({
        VALIDITY => $self->param('start_at') || '-01',
        VALIDITYFORMAT => 'detect',
    })->epoch();

    my $stop_at = time();
    $stop_at = OpenXPKI::DateTime::get_validity({
        VALIDITY => $self->param('stop_at'),
        VALIDITYFORMAT => 'detect',
    })->epoch()  if ($self->param('stop_at'));

    my $interval = { -between => [ $start_at, $stop_at ] };
    ##! 16: $interval

    # For special data types see:
    #  - https://metacpan.org/pod/SQL::Abstract::More#BIND-VALUES-WITH-TYPES
    #  - https://metacpan.org/pod/DBI#bind_param
    my %base_query = (
        from => 'certificate',
    );
    my %base_conditions = (
        req_key => { '!=' => undef },
        pki_realm => $pki_realm,
    );

    if (my $issuer = $self->param('issuer')) {
        $base_conditions{issuer_identifier} = $issuer;
    }

    my $select_column = 'EXTRACT(YEAR_MONTH FROM (FROM_UNIXTIME(%s)))|ivl';
    my $group_query = 'MONTH(FROM_UNIXTIME(%s)), YEAR(FROM_UNIXTIME(%1$s))';

    my $db = CTX('dbi');
    my $series;

    my $first_group = $db->select_one(%base_query, columns => [ sprintf($select_column, $start_at) ]);
    my $offset_year = int($first_group->{ivl} / 100);
    my $offset_month = $first_group->{ivl} % 100;

    ##! 16: "$offset_year / $offset_month"<commit_msg>Replace non-standard MySQL syntax YEAR_MONTH and YEAR() and MONTH()<commit_after>package OpenXPKI::Server::Workflow::Activity::Reports::Timeline;

use strict;
use base qw( OpenXPKI::Server::Workflow::Activity );

use OpenXPKI::Server::Context qw( CTX );
use OpenXPKI::Exception;
use OpenXPKI::Debug;
use Data::Dumper;
use OpenXPKI::DateTime;
use DateTime;

sub execute {

    my $self = shift;
    my $workflow = shift;

    ##! 1: 'Start'
    my $context = $workflow->context();
    my $pki_realm = CTX('session')->data->pki_realm;

    my $target_key = $self->param('target_key');

    my $start_at = OpenXPKI::DateTime::get_validity({
        VALIDITY => $self->param('start_at') || '-01',
        VALIDITYFORMAT => 'detect',
    })->epoch();

    my $stop_at = time();
    $stop_at = OpenXPKI::DateTime::get_validity({
        VALIDITY => $self->param('stop_at'),
        VALIDITYFORMAT => 'detect',
    })->epoch()  if ($self->param('stop_at'));

    my $interval = { -between => [ $start_at, $stop_at ] };
    ##! 16: $interval

    # For special data types see:
    #  - https://metacpan.org/pod/SQL::Abstract::More#BIND-VALUES-WITH-TYPES
    #  - https://metacpan.org/pod/DBI#bind_param
    my %base_query = (
        from => 'certificate',
    );
    my %base_conditions = (
        req_key => { '!=' => undef },
        pki_realm => $pki_realm,
    );

    if (my $issuer = $self->param('issuer')) {
        $base_conditions{issuer_identifier} = $issuer;
    }

    my $select_column = 'CONCAT(EXTRACT(YEAR FROM FROM_UNIXTIME(%s)),EXTRACT(MONTH FROM FROM_UNIXTIME(%1$s)))|ivl';
    my $group_query = 'EXTRACT(MONTH FROM FROM_UNIXTIME(%s)), EXTRACT(YEAR FROM FROM_UNIXTIME(%1$s))';

    my $db = CTX('dbi');
    my $series;

    my $first_group = $db->select_one(%base_query, columns => [ sprintf($select_column, $start_at) ]);
    my $offset_year = int($first_group->{ivl} / 100);
    my $offset_month = $first_group->{ivl} % 100;

    ##! 16: "$offset_year / $offset_month"
{'fraction_non_alphanumeric': 0.18295218295218296, 'fraction_numerical': 0.007536382536382537, 'mean_word_length': 3.549645390070922, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 49, 'https://': 4, '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': '6743460', 'n_tokens_mistral': 1453, 'n_tokens_neox': 1400, 'n_words': 259}