use strict;
use warnings;
use DBI;
use Data::UUID;
use createSubReport;
use createMainXML;
 
# Database connection
my $dbhost = "localhost";
my $database = "netxms_db";
my $user = "netxms_user";
my $password = "password";
 
# Connect to PostgreSQL database
my $dbh = DBI->connect(
        "DBI:Pg:host=$dbhost;dbname=$database;",
    $user,
    $password
) or die "Can't Connect to database: $DBI::errstr\n";
 
# Preparing query. "object_properties.name ~" checking for node names
my $query = "SELECT
        object_properties.name AS nas,
        object_properties.object_id AS id
FROM
        object_properties
WHERE
        object_properties.name ~ 'secure.*' OR object_properties.name ~ 'webapp.*'
ORDER BY
        nas";
 
my $sth = $dbh->prepare($query);
 
 
# Intervals array uses SQL interval statements.
# You can add or reduce the intervals as needed.
my @intervals = ("1 day","7 days","1 month");
 
foreach my $interval (@intervals) {
       
        my $rv = $sth->execute() or die "Query error: $dbh->errstr";
        my $rows = $sth->rows();
		
		# Renaming intervals to more clear format for the filename.
        my $some_ly;
        if ($interval eq '1 day') {$some_ly = 'daily';}
        elsif ($interval eq '7 days') {$some_ly = 'weekly';}
        elsif ($interval eq '1 month') {$some_ly = 'monthly';}
        else {$some_ly = 'unknown';}
        
		# Creating main XML file. It is empty for the moment.
        my $mainReport = new createMainXML();
        $mainReport->setFileName("$some_ly.jrxml");
		# For Linux machines you should set the folder in format /folder/folder/
		# For Windows machines you should set the folder in format C:\\folder\\folder\\
		# Do not forget the closing slashes.
		$mainReport->setFolder("/opt/xml-reports/");
        $mainReport->createXML();
		
		#print $mainReport->getFileName("$some_ly.jrxml") . "\n";
       
		# Accessing the data from SQL query
        for (my $i = 0; $i < $rows; $i++) {
                (my $node_name, my $node_id) = $sth->fetchrow_array();
 
                if (!defined $node_name || !defined $node_id) {die "Query error or out of range\n"}
				
				# Define different subreports for different nodes. 
				# Comment this out, if you do not need diversification.
				if ($node_name =~ /secure/)  {
                        my $subreport = new createSubReport();
                        $subreport->setNodeId($node_id);
                        $subreport->setNodeName($node_name);
                        $subreport->setInterval($interval);
                        $subreport->setSubQuery(" AND (items.name = 'OpenVPN.Users' OR items.name = 'L2TP.Users')");
                        $subreport->setFirstColor("#9999FF");
                        $subreport->setSecondColor("#FFCC00");
                        $subreport->setDescription("users count");
                        $subreport->createXML();
                        $mainReport->setSubReportName($subreport->getFileName());
                        $mainReport->updateXML();
                }
                elsif ($node_name =~ /webapp/)  {
                        my $subreport = new createSubReport();
                        $subreport->setNodeId($node_id);
                        $subreport->setNodeName($node_name);
                        $subreport->setInterval($interval);
                        $subreport->setSubQuery(" AND (items.name = 'Apache.Connections')");
                        $subreport->setFirstColor("#FF3333");
                        $subreport->setDescription("apache connections");
                        $subreport->createXML();
                        $mainReport->setSubReportName($subreport->getFileName());
                        $mainReport->updateXML();
                }
				
				# Generic reports
				
                my $cpusubreport = new createSubReport();
                $cpusubreport->setNodeId($node_id);
                $cpusubreport->setNodeName($node_name);
                $cpusubreport->setInterval($interval);
                $cpusubreport->setSubQuery(" AND (items.name = 'System.CPU.Usage')");
                $cpusubreport->setFirstColor("#009933");
                $cpusubreport->setDescription("cpu load");
                $cpusubreport->createXML();
                $mainReport->setSubReportName($cpusubreport->getFileName());
                $mainReport->updateXML();
                       
                my $memsubreport = new createSubReport();
                $memsubreport->setNodeId($node_id);
                $memsubreport->setNodeName($node_name);
                $memsubreport->setInterval($interval);
                $memsubreport->setSubQuery(" AND (items.name = 'System.Memory.Physical.Free')");
                $memsubreport->setFirstColor("#FF9933");
                $memsubreport->setDescription("free memory");
                $memsubreport->createXML();
                $mainReport->setSubReportName($memsubreport->getFileName());
                $mainReport->updateXML();
        }
}