Add Outlook export config for BT, per RT#48111

[sitka/sitka-tools.git] / maintenance / report-sql.pl

#!/usr/bin/perl
# vim:ts=4:noet:

# report-sql.pl - generate SQL for an existing report
#
# This script was created by taking the clark-kent.pl utility from
# Evergreen (http://evergreen-ils.org) and removing all the parts not
# directly required for generating report SQL.
#
# Copyright (C) 2008 Equinox Software, Inc.
# Copyright (C) 2016 BC Libraries Cooperative
# Author: Jeff Davis <jeff.davis@bc.libraries.coop>
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.

use strict;
use DBI;
use Getopt::Long;
use Data::Dumper;
use OpenSRF::EX qw/:try/;
use OpenSRF::Utils qw/:daemon/;
use OpenSRF::Utils::JSON;
use OpenSRF::Utils::Logger qw/$logger/;
use OpenSRF::System;
use OpenSRF::AppSession;
use OpenSRF::Utils::SettingsClient;
use OpenILS::Reporter::SQLBuilder;

use open ':utf8';


my $config = '/srv/openils/conf/opensrf_core.xml';
my $opt_report;
my $help;

GetOptions(
	"bootstrap|boostrap=s"	=> \$config,
	"report=i"              => \$opt_report,
	"help"                  => \$help
);

if ($help || !$report) {
	print <<"HELP";
Output the SQL that will be generated for the specified report.
NB: This script must be run on a server with Evergreen running!

USAGE:
	$0 --report <report-id> [ --bootstrap /openils/conf/opensrf_core.xml ] > query.sql

OPTIONS:
	--report
		The id of a row in reporter.report.
	--bootstrap
		Path to your opensrf_core.xml config file.

HELP
	exit;
}

OpenSRF::System->bootstrap_client( config_file => $config );

my (%data_db, %state_db);

my $sc = OpenSRF::Utils::SettingsClient->new;

$data_db{db_driver} = $sc->config_value( reporter => setup => database => 'driver' );
$data_db{db_host}   = $sc->config_value( reporter => setup => database => 'host' );
$data_db{db_port}   = $sc->config_value( reporter => setup => database => 'port' );
$data_db{db_name}   = $sc->config_value( reporter => setup => database => 'db' );
if (!$data_db{db_name}) {
    $data_db{db_name} = $sc->config_value( reporter => setup => database => 'name' );
    print STDERR "WARN: <database><name> is a deprecated setting for database name. For future compatibility, you should use <database><db> instead." if $data_db{db_name}; 
}
$data_db{db_user}   = $sc->config_value( reporter => setup => database => 'user' );
$data_db{db_pw}     = $sc->config_value( reporter => setup => database => 'pw' );



# Fetch the optional state database connection info
$state_db{db_driver} = $sc->config_value( reporter => setup => state_store => 'driver' ) || $data_db{db_driver};
$state_db{db_host}   = $sc->config_value( reporter => setup => state_store => 'host'   ) || $data_db{db_host};
$state_db{db_port}   = $sc->config_value( reporter => setup => state_store => 'port'   ) || $data_db{db_port};
$state_db{db_name}   = $sc->config_value( reporter => setup => state_store => 'db'     );
if (!$state_db{db_name}) {
    $state_db{db_name} = $sc->config_value( reporter => setup => state_store => 'name' ) || $data_db{db_name};
}
$state_db{db_user}   = $sc->config_value( reporter => setup => state_store => 'user'   ) || $data_db{db_user};
$state_db{db_pw}     = $sc->config_value( reporter => setup => state_store => 'pw'     ) || $data_db{db_pw};


die "Unable to retrieve database connection information from the settings server"
    unless ($state_db{db_driver} && $state_db{db_host} && $state_db{db_port} && $state_db{db_name} && $state_db{db_user} &&
        $data_db{db_driver} && $data_db{db_host} && $data_db{db_port} && $data_db{db_name} && $data_db{db_user});

my $state_dsn = "dbi:" . $state_db{db_driver} . ":dbname=" . $state_db{db_name} .';host=' . $state_db{db_host} . ';port=' . $state_db{db_port};
my $data_dsn  = "dbi:" .  $data_db{db_driver} . ":dbname=" .  $data_db{db_name} .';host=' .  $data_db{db_host} . ';port=' .  $data_db{db_port};

my $resultset_limit;

my $dbh = DBI->connect(
	$state_dsn,
	$state_db{db_user},
	$state_db{db_pw},
	{ AutoCommit => 1,
	  pg_expand_array => 0,
	  pg_enable_utf8 => 1,
	  RaiseError => 1
	}
);

if ($opt_report) {
	my $s3 = $dbh->selectrow_hashref(<<"	SQL", {}, $opt_report);
		SELECT * FROM reporter.report WHERE id = ?;
	SQL

	my $s2 = $dbh->selectrow_hashref(<<"	SQL", {}, $s3->{template});
		SELECT * FROM reporter.template WHERE id = ?;
	SQL

	$s3->{template} = $s2;

	my $b = OpenILS::Reporter::SQLBuilder->new;
	my $report_data = OpenSRF::Utils::JSON->JSON2perl( $s3->{data} );
	$b->register_params( $report_data );

	$s3->{resultset} = $b->parse_report( OpenSRF::Utils::JSON->JSON2perl( $s3->{template}->{data} ) );

	$s3->{resultset}->set_pivot_data($report_data->{__pivot_data}) if $report_data->{__pivot_data};
	$s3->{resultset}->set_pivot_label($report_data->{__pivot_label}) if $report_data->{__pivot_label};
	$s3->{resultset}->set_pivot_default($report_data->{__pivot_default}) if $report_data->{__pivot_default};
	$s3->{resultset}->resultset_limit($resultset_limit) if $resultset_limit;

	print $s3->{resultset}->toSQL;
}

$dbh->disconnect;