<commit_before>package Tapper::RawSQL::TestrunDB::testruns;

use strict;
use warnings;

sub web_list {

    my ( $hr_vals ) = @_;

    my @a_where;
    if ( !$hr_vals || !$hr_vals->{testrun_date_from} || !$hr_vals->{testrun_date_to} ) {
        require Carp;
        Carp::croak('missing parameters for sql statement: reports::web_list');
    }
    else {
        $hr_vals->{testrun_date_min} = "$hr_vals->{testrun_date_from} 00:00:00";
        $hr_vals->{testrun_date_max} = "$hr_vals->{testrun_date_to} 23:59:59";
        push @a_where, 't.created_at BETWEEN $testrun_date_min$ AND $testrun_date_max$';
    }

    my %h_where_columns = (
        testrun_id   => 't.id',
        host         => 'ts.host_id',
        state        => 'ts.status',
        owner        => 't.owner_id',
        topic        => 't.topic_name',
    );

    for my $s_filter ( keys %h_where_columns ) {
        if ( $hr_vals->{$s_filter} ) {
            if ( ref $hr_vals->{$s_filter} eq 'ARRAY' ) {
                if ( @{$hr_vals->{$s_filter}} ) {
                    if ( @{$hr_vals->{$s_filter}} == 1 ) {
                        push @a_where, "$h_where_columns{$s_filter} = \$$s_filter\$";
                    }
                    else {
                        push @a_where, "$h_where_columns{$s_filter} IN (\$$s_filter\$)";
                    }
                }
            }
            else {
                push @a_where, "$h_where_columns{$s_filter} = \$$s_filter\$";
            }
        }
    }

    return {
        Pg => q#
            SELECT
                t.id                                                        AS testrun_id,
                FLOOR( CAST( NULLIF( rgts.success_ratio, 0 ) AS float ) )   AS success_ratio,
                r.id                                                        AS primary_report_id,
                t.topic_name,
                TO_CHAR( r.created_at, 'YYYY-MM-DD' )                       AS testrun_date,
                TO_CHAR( r.created_at, 'HH24:MM' )                          AS testrun_time,
                NULLIF( r.updated_at, t.updated_at )                        AS updated_at,
                NULLIF( o.login, 'unknown user' )                           AS testrun_owner,
                t.starttime_testrun,
                NULLIF( ts.status, 'unknown status' )                       AS testrun_state,
                IF(
                    ts.id IS NULL,
                    'unknownmachine',
                    IF(
                        h.id IS NULL,
                        IF(
                            ts.status = 'finished',
                            'Host deleted',
                            'No host assigned'
                        ),
                        h.name
                    )
                )                                                           AS machine_name
            FROM
                testrun t
                LEFT JOIN owner o
                    ON ( o.id = t.owner_id )
                LEFT JOIN (
                    reportgrouptestrunstats rgts
                    JOIN reportgrouptestrun rgt
                        ON ( rgt.testrun_id = rgts.testrun_id )
                    JOIN report r
                        ON ( r.id = rgt.report_id )
                    LEFT JOIN suite s
                        ON ( r.suite_id = s.id )
                )
                    ON (
                        rgts.testrun_id = t.id
                        AND rgt.report_id = (
                            SELECT
                                rgti.report_id
                            FROM
                                reportgrouptestrun rgti
                            WHERE
                                rgti.testrun_id = rgt.testrun_id
                            ORDER BY
                                rgti.primaryreport DESC,
                                rgti.report_id ASC
                            LIMIT
                                1
                        )
                    )
                LEFT JOIN (
                    testrun_scheduling ts
                    LEFT JOIN host h
                        ON ( h.id = ts.host_id )
                )
                    ON ( ts.testrun_id = t.id )
            WHERE
                # . ( join "\nAND ", @a_where ) . q#
            ORDER BY
                t.id DESC
        #,
        SQLite => q#
            SELECT
                t.id                                            AS testrun_id,
                CAST( IFNULL( rgts.success_ratio, 0 ) AS INT )  AS success_ratio,
                r.id                                            AS primary_report_id,
                t.topic_name,
                STRFTIME( '%Y-%m-%d', r.created_at )            AS testrun_date,
                STRFTIME( '%H:%M', r.created_at )               AS testrun_time,
                IFNULL( r.updated_at, t.updated_at )            AS updated_at,
                IFNULL( o.login, 'unknown user' )               AS testrun_owner,
                t.starttime_testrun,
                IFNULL( ts.status, 'unknown status' )           AS testrun_state,
                CASE WHEN
                    ts.id IS NULL
                THEN
                    'unknownmachine'
                ELSE
                    CASE WHEN
                        h.id IS NULL
                    THEN
                        CASE WHEN
                            ts.status = 'finished'
                        THEN
                            'Host deleted'
                        ELSE
                            'No host assigned'
                        END
                    ELSE
                        h.name
                    END
                END                                             AS machine_name
            FROM
                testrun t
                LEFT JOIN owner o
                    ON ( o.id = t.owner_id )
                LEFT JOIN (
                    reportgrouptestrunstats rgts
                    JOIN reportgrouptestrun rgt
                        ON ( rgt.testrun_id = rgts.testrun_id )
                    JOIN report r
                        ON ( r.id = rgt.report_id )
                    LEFT JOIN suite s
                        ON ( r.suite_id = s.id )
                )
                    ON (
                        rgts.testrun_id = t.id
                        AND rgt.report_id = (
                            SELECT
                                rgti.report_id
                            FROM
                                reportgrouptestrun rgti
                            WHERE
                                rgti.testrun_id = rgt.testrun_id
                            ORDER BY
                                rgti.primaryreport DESC,
                                rgti.report_id ASC
                            LIMIT
                                1
                        )
                    )
                LEFT JOIN (
                    testrun_scheduling ts
                    LEFT JOIN host h
                        ON ( h.id = ts.host_id )
                )
                    ON ( ts.testrun_id = t.id )
            WHERE
                # . ( join "\nAND ", @a_where ) . q#
            ORDER BY
                t.id DESC
        #,
        mysql => q#
            SELECT
                t.id                                        AS testrun_id,
                FLOOR( IFNULL( rgts.success_ratio, 0 ) )    AS success_ratio,
                r.id                                        AS primary_report_id,
                t.topic_name,
                DATE_FORMAT( t.created_at, '%Y-%m-%d' )     AS testrun_date,
                DATE_FORMAT( t.created_at, '%H:%m' )        AS testrun_time,
                IFNULL( r.updated_at, t.updated_at )        AS updated_at,
                IFNULL( o.login, 'unknown user' )           AS testrun_owner,
                t.starttime_testrun,
                IFNULL( ts.status, 'unknown status' )       AS testrun_state,
                IF(
                    ts.id IS NULL,
                    'unknownmachine',
                    IF(
                        h.id IS NULL,
                        IF(
                            ts.status = 'finished',
                            'Host deleted',<commit_msg>some little cosmetic changes  - add PASS/FAIL-Filter to Testrun-List<commit_after>package Tapper::RawSQL::TestrunDB::testruns;

use strict;
use warnings;

sub web_list {

    my ( $hr_vals ) = @_;

    my @a_where;
    if ( !$hr_vals || !$hr_vals->{testrun_date_from} || !$hr_vals->{testrun_date_to} ) {
        require Carp;
        Carp::croak('missing parameters for sql statement: reports::web_list');
    }
    else {
        $hr_vals->{testrun_date_min} = "$hr_vals->{testrun_date_from} 00:00:00";
        $hr_vals->{testrun_date_max} = "$hr_vals->{testrun_date_to} 23:59:59";
        push @a_where, 't.created_at BETWEEN $testrun_date_min$ AND $testrun_date_max$';
    }

    my %h_where_columns = (
        testrun_id   => 't.id',
        host         => 'ts.host_id',
        state        => 'ts.status',
        owner        => 't.owner_id',
        topic        => 't.topic_name',
        success      => 'r.successgrade',
    );

    for my $s_filter ( keys %h_where_columns ) {
        if ( $hr_vals->{$s_filter} ) {
            if ( ref $hr_vals->{$s_filter} eq 'ARRAY' ) {
                if ( @{$hr_vals->{$s_filter}} ) {
                    if ( @{$hr_vals->{$s_filter}} == 1 ) {
                        push @a_where, "$h_where_columns{$s_filter} = \$$s_filter\$";
                    }
                    else {
                        push @a_where, "$h_where_columns{$s_filter} IN (\$$s_filter\$)";
                    }
                }
            }
            else {
                push @a_where, "$h_where_columns{$s_filter} = \$$s_filter\$";
            }
        }
    }

    return {
        Pg => q#
            SELECT
                t.id                                                        AS testrun_id,
                FLOOR( CAST( NULLIF( rgts.success_ratio, 0 ) AS float ) )   AS success_ratio,
                r.successgrade,
                r.id                                                        AS primary_report_id,
                t.topic_name,
                TO_CHAR( r.created_at, 'YYYY-MM-DD' )                       AS testrun_date,
                TO_CHAR( r.created_at, 'HH24:MM' )                          AS testrun_time,
                NULLIF( r.updated_at, t.updated_at )                        AS updated_at,
                NULLIF( o.login, 'unknown user' )                           AS testrun_owner,
                t.starttime_testrun,
                NULLIF( ts.status, 'unknown status' )                       AS testrun_state,
                IF(
                    ts.id IS NULL,
                    'unknownmachine',
                    IF(
                        h.id IS NULL,
                        IF(
                            ts.status = 'finished',
                            'Host deleted',
                            'No host assigned'
                        ),
                        h.name
                    )
                )                                                           AS machine_name
            FROM
                testrun t
                LEFT JOIN owner o
                    ON ( o.id = t.owner_id )
                LEFT JOIN (
                    reportgrouptestrunstats rgts
                    JOIN reportgrouptestrun rgt
                        ON ( rgt.testrun_id = rgts.testrun_id )
                    JOIN report r
                        ON ( r.id = rgt.report_id )
                    LEFT JOIN suite s
                        ON ( r.suite_id = s.id )
                )
                    ON (
                        rgts.testrun_id = t.id
                        AND rgt.report_id = (
                            SELECT
                                rgti.report_id
                            FROM
                                reportgrouptestrun rgti
                            WHERE
                                rgti.testrun_id = rgt.testrun_id
                            ORDER BY
                                rgti.primaryreport DESC,
                                rgti.report_id ASC
                            LIMIT
                                1
                        )
                    )
                LEFT JOIN (
                    testrun_scheduling ts
                    LEFT JOIN host h
                        ON ( h.id = ts.host_id )
                )
                    ON ( ts.testrun_id = t.id )
            WHERE
                # . ( join "\nAND ", @a_where ) . q#
            ORDER BY
                t.id DESC
        #,
        SQLite => q#
            SELECT
                t.id                                            AS testrun_id,
                CAST( IFNULL( rgts.success_ratio, 0 ) AS INT )  AS success_ratio,
                r.successgrade,
                r.id                                            AS primary_report_id,
                t.topic_name,
                STRFTIME( '%Y-%m-%d', r.created_at )            AS testrun_date,
                STRFTIME( '%H:%M', r.created_at )               AS testrun_time,
                IFNULL( r.updated_at, t.updated_at )            AS updated_at,
                IFNULL( o.login, 'unknown user' )               AS testrun_owner,
                t.starttime_testrun,
                IFNULL( ts.status, 'unknown status' )           AS testrun_state,
                CASE WHEN
                    ts.id IS NULL
                THEN
                    'unknownmachine'
                ELSE
                    CASE WHEN
                        h.id IS NULL
                    THEN
                        CASE WHEN
                            ts.status = 'finished'
                        THEN
                            'Host deleted'
                        ELSE
                            'No host assigned'
                        END
                    ELSE
                        h.name
                    END
                END                                             AS machine_name
            FROM
                testrun t
                LEFT JOIN owner o
                    ON ( o.id = t.owner_id )
                LEFT JOIN (
                    reportgrouptestrunstats rgts
                    JOIN reportgrouptestrun rgt
                        ON ( rgt.testrun_id = rgts.testrun_id )
                    JOIN report r
                        ON ( r.id = rgt.report_id )
                    LEFT JOIN suite s
                        ON ( r.suite_id = s.id )
                )
                    ON (
                        rgts.testrun_id = t.id
                        AND rgt.report_id = (
                            SELECT
                                rgti.report_id
                            FROM
                                reportgrouptestrun rgti
                            WHERE
                                rgti.testrun_id = rgt.testrun_id
                            ORDER BY
                                rgti.primaryreport DESC,
                                rgti.report_id ASC
                            LIMIT
                                1
                        )
                    )
                LEFT JOIN (
                    testrun_scheduling ts
                    LEFT JOIN host h
                        ON ( h.id = ts.host_id )
                )
                    ON ( ts.testrun_id = t.id )
            WHERE
                # . ( join "\nAND ", @a_where ) . q#
            ORDER BY
                t.id DESC
        #,
        mysql => q#
            SELECT
                t.id                                        AS testrun_id,
                FLOOR( IFNULL( rgts.success_ratio, 0 ) )    AS success_ratio,
                r.successgrade,
                r.id                                        AS primary_report_id,
                t.topic_name,
                DATE_FORMAT( t.created_at, '%Y-%m-%d' )     AS testrun_date,
                DATE_FORMAT( t.created_at, '%H:%m' )        AS testrun_time,
                IFNULL( r.updated_at, t.updated_at )        AS updated_at,
                IFNULL( o.login, 'unknown user' )           AS testrun_owner,
                t.starttime_testrun,
                IFNULL( ts.status, 'unknown status' )       AS testrun_state,
                IF(
                    ts.id IS NULL,
                    'unknownmachine',
                    IF(
                        h.id IS NULL,
                        IF(
                            ts.status = 'finished',
                            'Host deleted',
{'fraction_non_alphanumeric': 0.07264269100017892, 'fraction_numerical': 0.0023856384564919187, 'mean_word_length': 0.7042382355930481, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 40, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 34, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3616258', 'n_tokens_mistral': 4454, 'n_tokens_neox': 4009, 'n_words': 891}