-=item get_criteria($area);
-
-Returns an arraref to hashrefs suitable for using in a tmpl_loop. With the criteria and available values.
-
-=cut
-
-sub get_criteria {
- my ($area) = @_;
- my $dbh = C4::Context->dbh();
- my $crit = $criteria{$area};
- my @criteria_array;
- foreach my $localcrit (@$crit) {
- my ( $value, $type ) = split( /\|/, $localcrit );
- my ( $table, $column ) = split( /\./, $value );
- if ( $type eq 'date' ) {
- my %temp;
- $temp{'name'} = $value;
- $temp{'date'} = 1;
- $temp{'description'} = $columns{$value};
- push @criteria_array, \%temp;
- }
- else {
-
- my $query =
- "SELECT distinct($column) as availablevalues FROM $table";
- my $sth = $dbh->prepare($query);
- $sth->execute();
- my @values;
- while ( my $row = $sth->fetchrow_hashref() ) {
- push @values, $row;
- ### $row;
- }
- $sth->finish();
- my %temp;
- $temp{'name'} = $value;
- $temp{'description'} = $columns{$value};
- $temp{'values'} = \@values;
- push @criteria_array, \%temp;
- }
- }
- return ( \@criteria_array );
-}
-
-sub execute_query {
- my ( $sql, $type, $format ) = @_;
- my $dbh = C4::Context->dbh();
-
- # take this line out when in production
- $sql .= " LIMIT 10";
- my $sth = $dbh->prepare($sql);
- $sth->execute();
- my $colnames=$sth->{'NAME'};
- my @results;
- my $row = join ('</th><th>',@$colnames);
- $row = "<tr><th>$row</th></tr>";
- my %temphash;
- $temphash{'row'} = $row;
- push @results, \%temphash;
-
- my $string;
- while ( my @data = $sth->fetchrow_array() ) {
-
- # tabular
- my %temphash;
- my $row = join( '</td><td>', @data );
- $row = "<tr><td>$row</td></tr>";
- $temphash{'row'} = $row;
- if ( $format eq 'text' ) {
- $string .= "\n" . $row;
- }
- if ($format eq 'tab' ){
- $row = join("\t",@data);
- $string .="\n" . $row;
- }
- if ($format eq 'csv' ){
- $row = join(",",@data);
- $string .="\n" . $row;
- }
-
- push @results, \%temphash;
-# }
- }
- $sth->finish();
- if ( $format eq 'text' || $format eq 'tab' || $format eq 'csv') {
- return $string;
- }
- else {
- return ( \@results );
- }
-}
-
-=item save_report($sql,$name,$type,$notes)
-
-Given some sql and a name this will saved it so that it can resued
-
-=cut
-
-sub save_report {
- my ( $sql, $name, $type, $notes ) = @_;
- my $dbh = C4::Context->dbh();
- my $query =
-"INSERT INTO saved_sql (borrowernumber,date_created,last_modified,savedsql,report_name,type,notes) VALUES (?,now(),now(),?,?,?,?)";
- my $sth = $dbh->prepare($query);
- $sth->execute( 0, $sql, $name, $type, $notes );
- $sth->finish();
-
-}
-
-sub get_saved_reports {
- my $dbh = C4::Context->dbh();
- my $query = "SELECT * FROM saved_sql ORDER by date_created";
- my $sth = $dbh->prepare($query);
- $sth->execute();
- my @reports;
- while ( my $data = $sth->fetchrow_hashref() ) {
- push @reports, $data;
- }
- $sth->finish();
- return ( \@reports );
-}
-
-sub get_saved_report {
- my ($id) = @_;
- my $dbh = C4::Context->dbh();
- my $query = " SELECT * FROM saved_sql WHERE id = ?";
- my $sth = $dbh->prepare($query);
- $sth->execute($id);
- my $data = $sth->fetchrow_hashref();
- $sth->finish();
- return ( $data->{'savedsql'}, $data->{'type'} );
-}
-
-=item create_compound($masterID,$subreportID)
-
-This will take 2 reports and create a compound report using both of them
-
-=cut
-
-sub create_compound {
- my ($masterID,$subreportID) = @_;
- my $dbh = C4::Context->dbh();
- # get the reports
- my ($mastersql,$mastertype) = get_saved_report($masterID);
- my ($subsql,$subtype) = get_saved_report($subreportID);
-
- # now we have to do some checking to see how these two will fit together
- # or if they will
- my ($mastertables,$subtables);
- if ($mastersql =~ / from (.*) where /i){
- $mastertables = $1;
- }
- if ($subsql =~ / from (.*) where /i){
- $subtables = $1;
- }
- return ($mastertables,$subtables);
-}
-
-=item get_column_type($column)
-
-This takes a column name of the format table.column and will return what type it is
-(free text, set values, date)
-
-=cut
-
-sub get_column_type {
- my ($tablecolumn) = @_;
- my ($table,$column) = split(/\./,$tablecolumn);
- my $dbh = C4::Context->dbh();
- my $catalog;
- my $schema;
-
- # mysql doesnt support a column selection, set column to %
- my $tempcolumn='%';
- my $sth = $dbh->column_info( $catalog, $schema, $table, $tempcolumn ) || die $dbh->errstr;
- while (my $info = $sth->fetchrow_hashref()){
- if ($info->{'COLUMN_NAME'} eq $column){
- #column we want
- if ($info->{'TYPE_NAME'} eq 'CHAR' || $info->{'TYPE_NAME'} eq 'VARCHAR'){
- $info->{'TYPE_NAME'} = 'distinct';
- }
- return $info->{'TYPE_NAME'};
- }
- }
- $sth->finish();
-}
-
-=item get_distinct_values($column)
-
-Given a column name, return an arrary ref of hashrefs suitable for use as a tmpl_loop
-with the distinct values of the column
-
-=cut
-
-sub get_distinct_values {
- my ($tablecolumn) = @_;
- my ($table,$column) = split(/\./,$tablecolumn);
- my $dbh = C4::Context->dbh();
- my $query =
- "SELECT distinct($column) as availablevalues FROM $table";
- my $sth = $dbh->prepare($query);
- $sth->execute();
- my @values;
- while ( my $row = $sth->fetchrow_hashref() ) {
- push @values, $row;
- }
- $sth->finish();
- return \@values;
-}
-
-sub save_dictionary {
- my ($name,$description,$sql,$area) = @_;
- my $dbh = C4::Context->dbh();
- my $query = "INSERT INTO reports_dictionary (name,description,saved_sql,area,date_created,date_modified)
- VALUES (?,?,?,?,now(),now())";
- my $sth = $dbh->prepare($query);
- $sth->execute($name,$description,$sql,$area) || return 0;
- $sth->finish();
- return 1;
-}