#! /usr/bin/perl
#
# Archive extract utility for Vargus
##################
#    Copyright (C) 2010-2012 Michael A. Kangin <mak@complife.ru>
#
#    Copyright: Vargus is under GNU GPL, the GNU General Public License.
#
#    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; version 2 dated June, 1991.
#
#    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.
#
#    http://www.gnu.org/licenses/gpl-2.0.html
#



use strict;
use warnings;

use Getopt::Long;
use Time::Local qw(timelocal);
use File::Temp qw(tempfile);
use DBI;
use Sys::Syslog qw(LOG_DAEMON LOG_ERR LOG_WARNING LOG_NOTICE LOG_INFO LOG_DEBUG);
use IO::Handle;

use Vargus::Common;
#use Vargus::Objects;


my $tmp_dir = '/var/tmp';
our $conf_dir = "/etc/vargus";
my $config_file = $conf_dir . "/get-archive.cfg";
my $server = 'localhost';
our $informer_port = 9165;

my $iframe_compensation_time;
my $max_sub_time = 300;
my $sub_sync_factor = 1.0006;
my $cheque_time_before = 2;
my $cheque_time_after = 10;

my @command_line = @ARGV;
my %sql_access;
my (
	$start_time,
	$end_time,
	$object,
	$help_me,
	$query_range,
	$video_type,
	$query_alerts,
	$query_cheques,
	$cheque_no,
	$cheque_id,
	$cheque_text,
	$alert_id,
	$group,
	$query_gr,
	$gr_id
);
my ($dbh, $sth);
my $progress_handler;
my $progress_file;
my $sql_query;
my @events;
my $gr_real_start;
my $ifct = 1;


sub prepare_to_die {
	$sth->finish if $sth;
	$dbh->disconnect if $dbh;
	close($progress_handler) if $progress_handler && $progress_handler->opened();
	if ($progress_file && -e $progress_file) {
		unlink $progress_file;
	}
	die;
}

sub progress {
	my $procent = shift;
	truncate($progress_handler, 0);
	seek($progress_handler, 0, 0);
	print($progress_handler $procent);
}

sub get_events {
	my $object = shift;
	my $start_time = shift;
	my $end_time = shift;

	my $start_time_string = sprintf("%04d-%02d-%02d %02d:%02d:%02d", 
		sub {($_[5]+1900, $_[4]+1, $_[3], $_[2], $_[1], $_[0])}->(localtime($start_time)));
	my $end_time_string = sprintf("%04d-%02d-%02d %02d:%02d:%02d", 
		sub {($_[5]+1900, $_[4]+1, $_[3], $_[2], $_[1], $_[0])}->(localtime($end_time)));

	$sql_query = "select id, UNIX_TIMESTAMP(timestamp), time_ms, event from events where camera = '$object'";
	$sql_query .= " and timestamp <= '$end_time_string' and timestamp >= '$start_time_string' order by timestamp, time_ms";

	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or s_log(LOG_WARNING, "Can't execute SQL query $sql_query");


	if ($sth->rows) {
		while (my ($id, $time, $ms, $event) = $sth->fetchrow_array) {
			my %e_record = (
				id => $id,
				time => $time,
				ms => $ms,
				event => $event
			);
			push(@events, {%e_record});
		}
	}
}


sub get_query_range {
	my $object = shift;
	my @results;
	$sql_query = "select UNIX_TIMESTAMP(start_time) from archive where status >= 8 and camera = '$object' " . 
		"ORDER BY UNIX_TIMESTAMP(start_time) LIMIT 1";
	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");

	log_n_die("No results ($sql_query)") unless $sth->rows;
	my ($start) = $sth->fetchrow_array;

	$sql_query = "select UNIX_TIMESTAMP(end_time) from archive where status >=8 and camera = '$object' " . 
		"ORDER BY UNIX_TIMESTAMP(end_time) DESC LIMIT 1";
	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");

	log_n_die("No results ($sql_query)") unless $sth->rows;
	my ($end) = $sth->fetchrow_array;
	
	@results = ($start, $end);
	return @results;
}

sub get_query_alerts {
	my $object = shift;
	my @results;
	$sql_query = "select id, UNIX_TIMESTAMP(start_time), message from alerts where camera = '$object' " .
		"and UNIX_TIMESTAMP(start_time) < $end_time and UNIX_TIMESTAMP(end_time) > $start_time" . 
		" ORDER BY UNIX_TIMESTAMP(start_time);";
	
	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");
	log_n_die("No results ($sql_query)") unless $sth->rows;

	while (my ($id, $start, $message) = $sth->fetchrow_array) {
		push(@results, "$id;$start;$message");
	}
	
	return @results;
}

sub get_query_cheques {
	my $object = shift;
	my @results;
	my $like_search;

	if ($cheque_no and $like_search = grep(/[*?]/, $cheque_no)) {
		$cheque_no =~ tr/*?/%_/;
	}

	my $select_part = $cheque_no ? 
			($like_search ? 
				"and number like '$cheque_no'" :
				"and number='$cheque_no'") . " and end_time is not null" :
			"and UNIX_TIMESTAMP(start_time) < $end_time and UNIX_TIMESTAMP(end_time) > $start_time";

	$sql_query = "select id, number, UNIX_TIMESTAMP(start_time), sum from cheques where camera = '$object' " .
		$select_part . " ORDER BY UNIX_TIMESTAMP(start_time);";
	
	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");
	log_n_die("No results ($sql_query)") unless $sth->rows;

	while (my ($id, $cheque_no, $start, $sum) = $sth->fetchrow_array) {
		push(@results, "$id;$cheque_no;$start;$sum");
	}
	return @results;
}

sub get_query_gr {
	my $group = shift;
	my @results;
	my $group_condition = ' ';

	if ($group) {
		$group_condition = "set_name = '$group' and ";
	}

	$sql_query = "select id, set_name, UNIX_TIMESTAMP(start_time), UNIX_TIMESTAMP(end_time), ref_id, exported " . 
		"from group_write where $group_condition" .
		"UNIX_TIMESTAMP(start_time) < $end_time and UNIX_TIMESTAMP(end_time) > $start_time " .
		"ORDER BY UNIX_TIMESTAMP(start_time);";
	
	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");
	log_n_die("No results ($sql_query)") unless $sth->rows;

	my @exp_state = ('0', '1');
	while (my ($id, $set, $start, $end, $ref, $exported) = $sth->fetchrow_array) {
		if (! defined($exported)) {
			$exported = 0;
		}
		push(@results, "$id;$set;$start;$end;$ref;$exp_state[$exported]");
	}
	
	return @results;
}


sub get_archive {
	my $object = shift;
	open($progress_handler, "> $progress_file");
	$progress_handler->autoflush();
	progress(0);

	$dbh = DBI->connect($sql_access{dsn}, $sql_access{user}, $sql_access{password})
		or log_n_die("Can't connect to mysql base");

	$sql_query = "select filename, UNIX_TIMESTAMP(start_time), UNIX_TIMESTAMP(end_time) from archive where status >= 8";
	$sql_query .=  " and camera = '$object'";
	$sql_query .=  " and UNIX_TIMESTAMP(start_time) < $end_time and UNIX_TIMESTAMP(end_time) > $start_time" unless $query_range;
	$sql_query .=  " ORDER BY UNIX_TIMESTAMP(start_time);";

	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");

	log_n_die("No results ($sql_query)") unless $sth->rows;

	my ($results_start, $results_end, @results_files, %timings); 
	while (my ($filename, $start, $end) = $sth->fetchrow_array) {
		push(@results_files, $filename);
		$timings{$filename}{start} = $start;
		$timings{$filename}{end} = $end;
		$results_start = $start unless $results_start;
		$results_end = $end;
	}

	progress(5);

	#if ($query_range) {
	#	print($results_start . "\n");
	#	print($results_end . "\n");
	#	exit;
	#}

	get_events($object, $start_time - 20, $end_time);

	$sth->finish if $sth;
	$dbh->disconnect if $dbh;

	progress(10);

	my ($tmp1, $tmp2, $tmp3);

	$tmp1 = (tempfile("$tmp_dir/get_archive.XXXXXX", OPEN => 0))[1];
	$tmp2 = (tempfile("$tmp_dir/get_archive.XXXXXX", OPEN => 0))[1];
	$tmp3 = (tempfile("$tmp_dir/get_archive.XXXXXX", OPEN => 0))[1];
	#$tmp2 .= ".$video_type";

	my ($oldout, $olderr);
	open($oldout, ">&STDOUT");
	open($olderr, ">&STDERR");
	open(STDOUT, '>', "/dev/null");
	open(STDERR, '>', "/dev/null");
	select STDERR; 
	$| = 1;
	select STDOUT; 
	$| = 1;

	my @ffmpeg_parameters;

	# First step: join all needed video fragments into ts container
	my $progress_weight = @results_files ? 50 / ($#results_files + 1) : 0;
	my $vfile_counter;
	foreach (@results_files) {
		my $tmp0 = $_;
		if ($results_files[$vfile_counter + 1]) {
			my $next_file = $results_files[$vfile_counter + 1];
			if ($timings{$_}{end} > $timings{$next_file}{start}) {
				my $limit_length = $timings{$next_file}{start} - $timings{$_}{start};
				my $fact_length = $timings{$_}{end} - $timings{$_}{start};
				s_log(LOG_DEBUG, "Files overlap detected, fact length: $fact_length, will limit to $limit_length");
				$tmp0 = (tempfile("$tmp_dir/get_archive.XXXXXX", OPEN => 0))[1];
				@ffmpeg_parameters = split(' ', "-y -i $_ -vcodec copy -acodec copy -t $limit_length -f mp4 $tmp0");
				system('/usr/bin/avconv', @ffmpeg_parameters);
			}
		}

		@ffmpeg_parameters = split(' ', "-tmp $tmp_dir -force-cat -cat $tmp0 $tmp1");
		system('/usr/bin/MP4Box', @ffmpeg_parameters);
		unlink $tmp0 if $tmp0 =~ /$tmp_dir\/get_archive\./;
		print($olderr "Processing file: $_\n");
		$vfile_counter++;
		progress(10 + int($vfile_counter * $progress_weight));
		s_log(LOG_DEBUG, "Processing file: $_");
	}

	open(STDOUT, ">&", $oldout);

	progress(50);

	# Second step - calculate iframe_compensation_time for video always start from i_frame;
	my $skip = $start_time - $results_start;

	my $if_cnt = 0;
	my $ffmpeg_summary;
	open(STDERR, ">", $tmp2);
	if ($ifct) {
		do {
			truncate(STDERR, 0);
			seek(STDERR,0,0);
			@ffmpeg_parameters = split(' ', 
				"-y -i $tmp1 -vcodec copy -acodec copy -ss $skip -t 1.5 -f mp4 $tmp3");
			system('/usr/bin/avconv', @ffmpeg_parameters);
			open(FFMPEG, $tmp2);
			my @ffmpeg_output = <FFMPEG>;
			close(FFMPEG);
			$ffmpeg_summary = $ffmpeg_output[$#ffmpeg_output];
			if ($if_cnt++ > 20) {
				$skip = 1;
			}
		} while (--$skip > 0 and $ffmpeg_summary =~ /inf%$/);

		$if_cnt = 0;
		if ($skip > 0) {
			$skip++;
			do {
				truncate(STDERR, 0);
				seek(STDERR,0,0);
				$skip += 0.1;
				@ffmpeg_parameters = split(' ', 
					"-y -i $tmp1 -vcodec copy -acodec copy -ss $skip -t 1.5 -f mp4 $tmp3");
				system('/usr/bin/avconv', @ffmpeg_parameters);
				open(FFMPEG, $tmp2);
				my @ffmpeg_output = <FFMPEG>;
				close(FFMPEG);
				$ffmpeg_summary = $ffmpeg_output[$#ffmpeg_output];
				if ($if_cnt++ > 20) {
					$skip = 0.03;
					$ffmpeg_summary = "inf%";
				}
			} until ($ffmpeg_summary =~ /inf%$/);
			$skip -= 0.1;
		}
	}

	close(STDERR);

	$iframe_compensation_time = $start_time - $results_start - $skip;

	progress(60);

	# Third step - cut desired video fragment end put it into $video_type container

	my $duration = $end_time - $start_time + $iframe_compensation_time;
	@ffmpeg_parameters = split(' ', "-y -i $tmp1 -vcodec copy -acodec copy -ss $skip -t $duration -f $video_type $tmp3");
	system('/usr/bin/avconv', @ffmpeg_parameters);

	open(STDERR, ">&", $olderr);
	close($oldout);
	close($olderr);

	unlink($tmp1);
	unlink($tmp2);

	my $real_start = $results_start + $skip;
	my $export_file = "${object}_(date_of:$real_start)-(time_of:$real_start)";
	$export_file .= "_(date_of:$end_time)-(time_of:$end_time).$video_type";
	$export_file = expand_macroses($export_file);
	rename($tmp3, "$tmp_dir/$export_file");

	$gr_real_start = $real_start unless $gr_real_start;
	$gr_real_start = $real_start if $gr_real_start > $real_start;


	progress(80);
	if (@events) {
		my ($sub_cnt, $sub_time, $sub_next_time, $sub_duration, $sub_end_time, 
			$sub_start, $sub_end, $sub_offset, $sub_timestamp,
			@sub_text, $sub_file, $sub_repeat);

		my $tmp4 = (tempfile("$tmp_dir/get_archive.XXXXXX", OPEN => 0))[1];
		open(EVENTS, "> $tmp4");
		if ($video_type eq "flv") {
			print(EVENTS '<?xml version="1.0" encoding="UTF-8"?>' . "\n");
			print(EVENTS '<tt xml:lang="en" xmlns="http://www.w3.org/2006/10/ttaf1" xmlns:tts="http://www.w3.org/2006/10/ttaf1#styling">' . "\n");
			print(EVENTS "<body><div xml:lang='ru'>\n");
		}

		$progress_weight = @events ? 10 / ($#events + 1) : 0;
		for (my $e_cnt = 0; $e_cnt <= $#events; $e_cnt++) {
			progress(80 + int($e_cnt * $progress_weight));
			next until ${$events[$e_cnt]}{time};
			$sub_time = ${$events[$e_cnt]}{time} + ${$events[$e_cnt]}{ms} / 1000000 - $real_start;

			$sub_next_time = ${$events[$e_cnt + 1]}{time} ? ${$events[$e_cnt + 1]}{time} : 0;
			$sub_next_time += ${$events[$e_cnt + 1]}{ms} ? ${$events[$e_cnt + 1]}{ms} / 1000000 : 0;

			$sub_next_time = $end_time until $sub_next_time;
			$sub_next_time -= $real_start;

			next if $sub_time < 0;
			next if $sub_next_time < $sub_time or $sub_next_time < 0;

			$sub_timestamp = sprintf("%02d:%02d:%02d,%03d", 
				sub {($_[2], $_[1], $_[0])}->(localtime(int($sub_time + $real_start))),
				($sub_time + $real_start - int($sub_time + $real_start)) * 1000);


			${$events[$e_cnt]}{event} =~ s/(.*?) ([^\dx ]{6}.*)/$1\n$2\n&nbsp;/ if ${$events[$e_cnt]}{event} =~ /^[+-]/;
			push(@sub_text, "$sub_timestamp  " . ${$events[$e_cnt]}{event});
			shift @sub_text if $#sub_text > 5;

	#		print "<=== $sub_time : " . ($sub_time + $sub_duration) . " ($sub_duration) " . ${$events[$e_cnt]}{event} . "\n";

			$sub_time *= $sub_sync_factor;
			$sub_next_time *= $sub_sync_factor;
			$sub_time -= 0.5;
			$sub_next_time -= 0.5;

			$sub_duration = $sub_next_time - $sub_time;
			next if $sub_duration < 0.1;

			$sub_offset = $sub_time;
			$sub_offset = 0 if $sub_offset < 0;
			$sub_end_time = $sub_next_time;

			while ($sub_offset < $sub_end_time) {
				last if $sub_offset + $real_start > $end_time;

				$sub_cnt++;
				$sub_start = sprintf("%02d:%02d:%02d,%03d",
					sub {($_[2], $_[1], $_[0])}->(gmtime(int($sub_offset))), 
					($sub_offset - int($sub_offset)) * 1000);
				$sub_duration = $sub_end_time - $sub_offset > 1 ? 1 : $sub_end_time - $sub_offset;

	#			$sub_duration -= 0.001;

				if ($video_type eq "mp4") {
					$sub_end = sprintf("%02d:%02d:%02d,%03d",
						sub {($_[2], $_[1], $_[0])}->(gmtime($sub_offset + $sub_duration)), 
						($sub_offset + $sub_duration - int($sub_offset + $sub_duration)) * 1000);

					print(EVENTS "$sub_cnt\n");
					print(EVENTS "$sub_start --> $sub_end\n");
					foreach (@sub_text) {
						print(EVENTS "$_\n");
					}
					print(EVENTS "\n");
			
				} elsif ($video_type eq "flv") {
					$sub_duration = sprintf("%02d:%02d:%02d,%03d",
						sub {($_[2], $_[1], $_[0])}->(gmtime($sub_duration)),
						($sub_duration - int($sub_duration)) * 1000);
					$sub_duration =~ s/^[0:]*//g;
					print(EVENTS "<p begin='$sub_offset' dur='$sub_duration'>\n");
					foreach (@sub_text) {
						my $sub_element = $_;
						$sub_element =~ s/</{/g;
						$sub_element =~ s/>/}/g;
						$sub_element =~ s/&/*/g;
						$sub_element =~ s/\*nbsp;//g;
						$sub_element =~ s/\n/<br \/>/g;
						print(EVENTS "$sub_element<br />\n");
					}
					print(EVENTS "</p>\n");
				}
				
				$sub_offset++;
			}
		}

		print(EVENTS "</div></body></tt>\n") if $video_type eq "flv";
		close(EVENTS);

		$sub_file = $export_file;

		if ($video_type eq "flv") {
			$sub_file =~ s/$video_type$/xml/;
			rename($tmp4, "$tmp_dir/$sub_file");
		}

		progress(90);
		if ($video_type eq "mp4") {
			$sub_file =~ s/$video_type$/srt/;
			rename($tmp4, "$tmp_dir/$sub_file");
			system("cd $tmp_dir; MP4Box -noprog $export_file -add $sub_file >&2") == 0 
				or s_log(LOG_ERR, "Error embeding subtitles into videocontainer");
			unlink "$tmp_dir/$sub_file";
		}
	}

	progress(100);
	close($progress_handler);

	s_log(LOG_INFO, "Requested archive records: $export_file");

	return $export_file;
}


sub configure {
	my @cfg_body = ();
	my @main_section = ();

	open(CFG, $config_file) or log_n_die("Error reading config file $config_file");
	chomp(@cfg_body = <CFG>);
	close(CFG);

	@main_section = expand_macroses(get_cfg_section("main", @cfg_body));

	$sql_access{host} = get_option("sql-host", @main_section);
	$sql_access{db} = get_option("sql-db", @main_section) 
		or log_n_die("No SQL database specified");
	$sql_access{user} = get_option("sql-user", @main_section) 
		or log_n_die("No SQL user specified");
	$sql_access{password} = get_option("sql-password", @main_section) 
		or log_n_die("No SQL password specified");

	$sql_access{dsn} = "DBI:mysql:$sql_access{db}";
}


sub usage {
	my $message = shift;
	my $help = << "END";
Usage:
$0 
     --start sec,min,hour,day,month,year --end sec,min,hour,day,month,year 
	{[ --type { flv | mp4 }] | --query-alerts | --query-cheques | --query-gr } 
	{ --object camera-object | --group set-object }
     This give you archive file, or list alerts for specified time range.
 OR
     --query-range --object camera-object 
     This show possible archive range for the camera
 OR
     --query-cheques --object camera-object --cheque cheque-no
     This show all cheques with this number for the camera
 OR
     --query-gr [--group set-object] --start timestamp1 --end timestamp2
     This show all group records for this set from timestamp1 till timestamp2
 OR
     --cheque cheque-no --object camera-object [--text]
     This give you archive file for cheque with number cheque-no
     or text of this cheque.
 OR
     --cheque-id cheque-id [--text]
     This give you archive file for cheque with id cheque-id
     or text of this cheque.
 OR
     --alert alert-id
     This give you archive file for alert with ID alert-id
 OR
     --gr-id group-record-id
     This give you multichannel archive file for group record with ID group-record-id
 OR
     --help
     This show you this help.

Additional options:
     --no-ifct - Disable I-frame compensation time algorithm 
END
	print("\n") if $message;
	s_log(LOG_ERR, "$message") if $message;
	print("\n") if $message;
	print($help);
	die;
}
	

GetOptions(     'start=s' => \$start_time,
		'end=s' => \$end_time,
		'type=s' => \$video_type,
		'object=s' => \$object,
		'group=s' => \$group,
		'gr-id=i' => \$gr_id,
		'query-gr' => \$query_gr,
		'query-range' => \$query_range,
		'query-alerts' => \$query_alerts,
		'query-cheques' => \$query_cheques,
		'alert=i' => \$alert_id,
		'cheque=s' => \$cheque_no,
		'cheque-id=i' => \$cheque_id,
		'text' => \$cheque_text,
		'ifct!' => \$ifct,
		'help'	=> \$help_me);

usage if $help_me;

configure;	

unless ($query_range || $alert_id || $cheque_no || $cheque_id || $gr_id || ($query_cheques && $cheque_no)) {
	usage("No start time (" . join(" ", @command_line) . ")") unless $start_time;
	usage("No end time (" . join(" ", @command_line) . ")") unless $end_time;
	usage("No object or group (" . join(" ", @command_line) . ")") unless ($object || $group || $query_gr);
	usage("Wrong parameters (" . join(" ", @command_line) . ")") unless $start_time && $end_time && ($object || $group || $query_gr);

	my @date;

	for ($start_time, $end_time) {
		$_ = int($_ + 0.5) if /^\d+\.\d+$/;
		/^(\d+,){5}\d+$/ or /^\d+$/ or usage("Possibly bad time value: $_");
		unless (/^\d+$/) {
			@date = ();
			@date = split(',');
			$date[4]--;
			$_ = timelocal(@date);
		}
	}

	usage("Wrong parameters (recognize: start_time=$start_time; end_time=$end_time; object=$object; group=$group") unless $start_time && $end_time && ($object || $group || $query_gr);
	log_n_die("--start value is greater than --end value") if $start_time > $end_time;
}

if ($video_type) {
	$video_type eq "flv" or $video_type eq "mp4" or usage("Wrong video type $video_type");
} else {
	$video_type = "mp4";
}


$dbh = DBI->connect($sql_access{dsn}, $sql_access{user}, $sql_access{password}) 
	or log_n_die("Can't connect to mysql base");


if ($query_range) {
	my ($start, $end) = get_query_range($object);
	print "$start\n";
	print "$end\n";
	$sth->finish if $sth;
	$dbh->disconnect if $dbh;
	exit;
}

if ($query_alerts) {
	my @alerts = get_query_alerts($object);
	foreach my $alert (@alerts) {
		print "$alert\n";
	}
	$sth->finish if $sth;
	$dbh->disconnect if $dbh;
	exit;
}


if ($query_cheques) {
	usage("No object (" . join(" ", @command_line) . ")") if $cheque_no && ! $object;
	my @cheques = get_query_cheques($object);
	foreach my $cheque (@cheques) {
		print "$cheque\n";
	}
	$sth->finish if $sth;
	$dbh->disconnect if $dbh;
	exit;
}

if ($query_gr) {
	my @group_records = get_query_gr($group);
	foreach my $gr (@group_records) {
		print "$gr\n";
	}
	$sth->finish if $sth;
	$dbh->disconnect if $dbh;
	exit;
}

if ($alert_id) {
	$sql_query = "select camera, UNIX_TIMESTAMP(start_time), UNIX_TIMESTAMP(end_time) from alerts " .
		"where id=$alert_id;";
	
	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");
	log_n_die("Alert with ID $alert_id not found") unless $sth->rows;

	($object, $start_time, $end_time) = $sth->fetchrow_array;
	$progress_file = "/tmp/progress.alert-$alert_id.tmp";

} elsif ($gr_id) {
	$sql_query = "select set_name, UNIX_TIMESTAMP(start_time), UNIX_TIMESTAMP(end_time) from group_write " . 
		"where id=$gr_id";
	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");
	log_n_die("Group record with ID $gr_id not found") unless $sth->rows;

	($group, $start_time, $end_time) = $sth->fetchrow_array;

	$start_time -= 10;
	$end_time += 10;

	my $cam_selector = "(camera='" . do_remote_query($server, "query set;$group;cameras") . "') ";
	$cam_selector =~ s/,/' or camera='/g;

	$sql_query = "select UNIX_TIMESTAMP(start_time) from archive where " . $cam_selector .
		"and UNIX_TIMESTAMP(start_time) >$start_time " .
		"and UNIX_TIMESTAMP(end_time) < " . ($end_time + 3600) . " order by start_time limit 1";
	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");
	log_n_die("No archive files found in group-write range") unless $sth->rows;
	($start_time) = $sth->fetchrow_array;

	$progress_file = "/tmp/progress.$start_time-$end_time.tmp";
} elsif ($cheque_no || $cheque_id) {
	usage("No object (" . join(" ", @command_line) . ")") if $cheque_no && ! $object;

	if ($cheque_id) {
		$sql_query = "select id,camera,UNIX_TIMESTAMP(start_time),UNIX_TIMESTAMP(end_time) from cheques " .
			"where id='$cheque_id' and end_time is not null";
	} elsif ($cheque_no) {
		$sql_query = "select id,camera,UNIX_TIMESTAMP(start_time),UNIX_TIMESTAMP(end_time) from cheques " .
			"where number='$cheque_no' and camera='$object' and end_time is not null order by id desc limit 1";
	}


	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");

	unless ($sth->rows) {
		log_n_die("Cheque number $cheque_no not found for camera $object") if $cheque_no;
		log_n_die("Cheque id $cheque_id not found") if $cheque_id;
	}


	($cheque_id, $object, $start_time, $end_time) = $sth->fetchrow_array;
	$sth->finish if $sth;

	$sql_query = "select UNIX_TIMESTAMP(start_time) from cheques where camera='$object' and id > $cheque_id " .
		"and start_time < FROM_UNIXTIME(" . ($end_time + $cheque_time_after) . ") order by id limit 1";

	$sth = $dbh->prepare($sql_query) or log_n_die("Can't prepare SQL query $sql_query");
	$sth->execute or log_n_die("Can't execute SQL query $sql_query");

	my $next_start_time = 0;
	($next_start_time) = $sth->fetchrow_array;
	$sth->finish if $sth;

	$start_time -= $cheque_time_before;
	$end_time = $next_start_time ? $next_start_time - 2 : $end_time + $cheque_time_after;

	if ($cheque_text) {
		get_events($object, $start_time, $end_time);
		unless (@events) {
			log_n_die("No events found for cheque $cheque_no and camera $object") if $cheque_no;
			log_n_die("No events found for cheque id $cheque_id") unless @events;
		}
		print sprintf("%04d-%02d-%02d", sub {($_[5]+1900, $_[4]+1, $_[3])}->(localtime($start_time))) . "\n";
		foreach (@events) {
			print sprintf("%02d:%02d:%02d", sub {($_[2], $_[1], $_[0])}->(localtime(${$_}{time})));
			print "   ${$_}{event}\n";
		}
		$sth->finish if $sth;
		$dbh->disconnect if $dbh;
		exit;
	}



	$progress_file = "/tmp/progress.cheque-$cheque_id.tmp";
} else {
	$progress_file = "/tmp/progress.$start_time-$end_time.tmp";
}

$sth->finish if $sth;
$dbh->disconnect if $dbh;

unless ($group) {
	print get_archive($object) . "\n";
} else {
	my $gr_progress;
	my $gr_progress_file = "/tmp/progress.$group.$start_time-$end_time.tmp";
	my @archive_files;
	my @objects;

	if ($group =~ /,/) {
		@objects = split(',', $group);
	} else {
		@objects = split(',', do_remote_query($server, "query set;$group;cameras"));
	}

	open($gr_progress, "> $gr_progress_file") or warn $!;
	my $cnt = 1;
	foreach my $object (@objects) {
		push(@archive_files, get_archive($object));
		truncate($gr_progress, 0);
		seek($gr_progress, 0, 0);
		print($gr_progress 80 / $#objects * $cnt++);
	}

	my $result_archive = shift(@archive_files);
	chdir($tmp_dir);
	$cnt = 1;

	my ($oldout, $olderr);
	open($oldout, ">&STDOUT");
	open(STDOUT, '>&STDERR');
	select STDOUT; 
	$| = 1;

	foreach my $archive (@archive_files) {
		system('/usr/bin/MP4Box', split(' ', "-tmp $tmp_dir -noprog $result_archive -add $archive"));
		unlink $archive;
		truncate($gr_progress, 0);
		seek($gr_progress, 0, 0);
		print($gr_progress 80 + (20 / $#objects * $cnt++));
	}

	open(STDOUT, ">&", $oldout);
	close($oldout);


	my $gr_export_file = "${group}_(date_of:$gr_real_start)-(time_of:$gr_real_start)";
	$gr_export_file .= "_(date_of:$end_time)-(time_of:$end_time).$video_type";
	$gr_export_file = expand_macroses($gr_export_file);
	rename($result_archive, "$tmp_dir/$gr_export_file");

	print($gr_export_file . "\n");

}
