#!/usr/bin/perl -w
package db_row2col;
use strict;
use DBI;
use Data::Dumper;

use constant DB => "DBNAME";
use constant USER => "USERNAME";
use constant PASSWORD => "PASSWORD";
use constant TAB  => "TABNAME";


#___DEF VARS_______________
my ( @data, @restr_data, $ser_out );

#____I UNTANGLE PERL AND NON-PERL-CODE IN THE MOST CASES, SO I TRY TO PUT SQL-QUERIES INTO VARIABLES__
my ( $sel_all, $tab_name, $query ) = ( "SELECT * FROM", TAB );
my $query = $sel_all." ". TAB. ";";

#___SUBROUTINES AND CONSTRUCTOR____

sub new {
        my $type = shift;
        my $self = {};
        bless $self, $type;
        $self;
}

sub pull_data {
        my ( $ref, $query, $data ) = ( shift, shift, shift );
        my $dbh = DBI->connect("DBI:mysql:database=" . DB, USER, PASSWORD);

        my $call_data = $dbh->prepare($query);
        $call_data->execute() or die $call_data->err_str;

        while( my $hshRef = $call_data -> fetchrow_hashref() ){
                my @tmp;
                map {
                        push @tmp, $hshRef->{ $_ }
                } @{$call_data->{ NAME }};

                push @{$data}, [ @tmp ];

        }
        $data;
}

#___INIT SCRIPT___
my $inst = db_row2col -> new();
#my $res_set = $inst -> pull_data( $query, [ @data ] );

# it is not really another order of elements, just a manipulated data-output
for my $y (0..$#{$inst -> pull_data( $query, [ @data ] )->[0]} ){
        printf '%-25s' x @{$inst -> pull_data( $query, [ @data ] )}."n", map $_->[$y], @{$inst -> pull_data( $query, [ @data ] )};
}

print "n______________________nn";

#real re-ordering of columns in row in a new array of reference to arrays
for my $i ( 0..$#{ $inst -> pull_data( $query, [ @data ] ) } ) {
        my $extern_loop = 0;
        for my $j ( 0..$#{ $inst -> pull_data( $query, [ @data ] )->[$i] } ) {


                                      #normal, not manipulated output
                printf "%-30s", $inst -> pull_data( $query, [ @data ] ) -> [$i]->[$j];
                # make restructured new array of reference on arrays, rows to columns, col. to rows
                $restr_data[$j][$i] = $inst -> pull_data( $query, [ @data ] ) -> [$i]->[$j];
        }
        print "n";

}

print "n";
#output data in simple json format


$ser_out .= " [ n";
for my $cnt (0.. $#restr_data){
        $ser_out .= " t{ "obj".$cnt."" : [ ";
        map {
                $ser_out .= sprintf "%-20s", $_.",";
        } @{ $restr_data[$cnt] };

        #remove comma after last value and close object
        $ser_out .= $cnt == $#restr_data ? " ] }":" ] },n";

        #remove comma after the last arrayref
        $ser_out =~ m/,.*?]/i ? $ser_out =~ s/,(s*?]+)/$1 /gi : $ser_out;
}
#close json
$ser_out .= "n ] n";

print "BEGIN_________nn".$ser_out;