view release on metacpan or  search on metacpan

t/04-write_sql_dd.t  view on Meta::CPAN

use strict;
use warnings;
use Test::More;

use Path::Tiny;
use Spreadsheet::XLSX;
use DBI;
use Archive::Zip;
use Tie::IxHash;

use AlignDB::ToXLSX;

# cd ~/Scripts/alignDB
# perl util/query_sql.pl -d ScervsRM11_1a_Spar -t csv -o isw.csv \
#     -q "SELECT isw_id, isw_length, isw_distance, isw_pi FROM isw LIMIT 1000"

my $temp = Path::Tiny->tempfile;

{
    #@type DBI
    my $dbh = DBI->connect("DBI:CSV:");
    $dbh->{csv_tables}->{isw} = {
        eol            => "\n",
        sep_char       => ",",
        file           => "t/isw.csv",
        skip_first_row => 1,
        quote_char     => '',
        col_names      => [qw{ isw_id isw_length isw_distance isw_pi }],
    };

    my $sql_query_1 = q{
        SELECT
            isw.isw_distance distance,
            AVG(isw.isw_pi) AVG_pi,
            COUNT(*) COUNT
        FROM
            isw
        WHERE
            isw.isw_distance <= 5
        AND isw.isw_distance >= 1
        AND isw.isw_length = 100
        GROUP BY
            distance
        ORDER BY
            distance
    };

    my $sql_query_2 = q{
        SELECT
            isw.isw_distance distance,
            AVG(isw.isw_pi) AVG_pi,
            COUNT(*) COUNT
        FROM
            isw
        WHERE
            isw.isw_distance <= 5
        AND isw.isw_distance >= 1
        AND isw.isw_length <> 100
        GROUP BY
            distance
        ORDER BY
            distance
    };

    my $toxlsx = AlignDB::ToXLSX->new(
        dbh => $dbh,

        outfile => $temp->stringify,

        #        outfile => "04.xlsx",
    );

    my $sheet_name = 'd1_pi';
    my $sheet;
    $toxlsx->row(0);
    $toxlsx->column(1);

    {    # header
        $sheet = $toxlsx->write_header( $sheet_name, { header => [qw{distance AVG_pi COUNT}], } );
    }

    tie my %data_of, 'Tie::IxHash';
    {    # content