#!/usr/bin/perl use warnings; use strict; use autodie; use Data::Dump qw(dump); my $filter = join(' ',@ARGV); my $offsets; open(my $o, '<', 'gnuplot/offset.txt'); while(<$o>) { chomp; my ( $pn, $o_x, $o_y ) = split(/ /,$_); $offsets->{$pn} = [ $o_x, $o_y ]; } close($o); my $time_limit; open(my $o, '<', 'gnuplot/time.txt'); while(<$o>) { chomp; my ( $pn, $time ) = split(/ /,$_, 2); $time_limit->{$pn} = $time; } close($o); my $sql; my @sql_location; my $view2number; open(my $n, '<', 'subvision-numbers.txt'); while(<$n>) { chomp; my ( $number, $pn, $imei, $name ) = split(/ /,$_); if ( $filter && $name !~ m/$filter/i ) { warn "SKIP $number $pn\n"; next; } my $view = 'sub_' . $number; $view =~ s/\W+/_/g; $view2number->{$view} = $number; my $offset_x = $offsets->{$pn}->[0] // 0; my $offset_y = $offsets->{$pn}->[1] // 0; my $time = $time_limit->{$pn} // ''; if ( ! defined( $sql->{$view} ) ) { $sql->{$view} = qq{ create or replace view $view as select time, pn, x_axis_angle + $offset_x as x_axis_angle, y_axis_angle + $offset_y as y_axis_angle, power_source_voltage, sensor_temperature, signal_strength from zc where pn = $pn $time }; } else { $sql->{$view} .= qq{ union select time, pn, x_axis_angle + $offset_x as x_axis_angle, y_axis_angle + $offset_y as y_axis_angle, power_source_voltage, sensor_temperature, signal_strength from zc where pn = $pn $time }; } push @sql_location, qq{ select timezone('UTC-2'::text, "time") AS "time", x_axis_angle + $offset_x as x_axis_angle, y_axis_angle + $offset_y as y_axis_angle, sensor_temperature, power_source_voltage, zc.pn from zc where pn = $pn $time }; } close($n); open(my $sql_fh, '>', "/tmp/$filter.sql"); open(my $shell_fh, '>', "/tmp/$filter.sh"); open(my $html_fh, '>', "/tmp/$filter.html"); print $html_fh qq{
}; foreach my $view ( sort keys %$sql ) { print $sql_fh "drop view $view ;\n"; print $sql_fh $sql->{$view} . ";\n"; print $sql_fh "grant select on $view to grafana ;\n\n"; print $shell_fh "ln -s sub.cgi $view.cgi\n"; print $html_fh qq{