<?php |
<?php |
include('common.inc.php'); |
include('common.inc.php'); |
function processHourly($hourly) |
function processHourly($hourly) { |
{ |
$filename = str_replace(' ',"_",$hourly['tgid']).'-'.str_replace(' 00:00:00+1','',$hourly['aday']).'-'.$hourly['ahour'].'.3gp'; |
|
|
$hfilename = $hourly['tgid'] . '-' . str_replace(' 00:00:00+10', '', $hourly['aday']) . '-' . $hourly['ahour'] . '.3gp'; |
$hfilename = $hourly['tgid'] . '-' . str_replace(' 00:00:00+10', '', $hourly['aday']) . '-' . $hourly['ahour'] . '.3gp'; |
|
|
if (!file_exists("hourly/" . $hfilename)) { |
if (!file_exists("hourly/" . $hfilename)) { |
|
|
$filenames = explode(",", $hourly['filenames']); |
$filenames = explode(",", $hourly['filenames']); |
$cmd = "/usr/local/bin/ffmpeg -filter_complex concat=n=" . count($filenames) . ":v=0:a=1 -i data/" . implode(" -i data/", $filenames) . " -ar 8000 -ab 4.75k -ac 1 hourly/" . $hfilename . ' 2>&1'; |
$cmd = "/usr/local/bin/ffmpeg -filter_complex concat=n=" . count($filenames) . ":v=0:a=1 -i data/" . implode(" -i data/", $filenames) . " -ar 8000 -ab 4.75k -ac 1 hourly/" . $hfilename . ' 2>&1'; |
//print_r($hourly); |
//print_r($hourly); |
exec($cmd, $output, $returncode); |
exec($cmd, $output, $returncode); |
echo $cmd . "<br>\n"; |
echo $cmd . "<br>\n"; |
if ($returncode != 10) { |
if ($returncode != 10) { |
print_r($output); |
print_r($output); |
//die(); |
//die(); |
} else { |
} else { |
$q = " insert into compilations (filename files datetime) ('" . $hfilename . "',{'" . implode("', '", $filenames) . "'},'" . strtottime($hourly['aday'] . ' +' . $hourly['ahour'] . " hours')") . "' "; |
$q = " insert into compilations (filename files datetime) ('" . $hfilename . "',{'" . implode("', '", $filenames) . "'},'" . strtottime($hourly['aday'] . ' +' . $hourly['ahour'] . " hours')") . "' "; |
|
|
foreach ($filenames as $filename) { |
foreach ($filenames as $filename) { |
$q = "UPDATE recordings SET archived = '$hfilename' WHERE filename = '$filename' "; |
$q = "UPDATE recordings SET archived = '$hfilename' WHERE filename = '$filename' "; |
} |
} |
} |
} |
|
|
} |
} |
} |
} |
|
/*$sth = $conn->prepare("select tgid, extract(hour from call_timestamp) ahour, date_trunc('day', call_timestamp) aday, |
|
count(filename), array_to_string(array_agg(filename order by call_timestamp), ',') filenames from recordings group by tgid, ahour, aday order by aday DESC, ahour, tgid;"); |
|
|
$sth = $conn->prepare("SELECT tgid, EXTRACT(HOUR FROM call_timestamp) ahour, date_trunc('day', call_timestamp) aday, COUNT(filename), array_to_string(array_agg(filename ORDER BY call_timestamp), ',') filenames FROM recordings GROUP BY tgid, ahour, aday ORDER BY aday DESC, ahour, tgid;"); |
$sth = $conn->prepare("SELECT tgid, EXTRACT(HOUR FROM call_timestamp) ahour, date_trunc('day', call_timestamp) aday, COUNT(filename), array_to_string(array_agg(filename ORDER BY call_timestamp), ',') filenames FROM recordings GROUP BY tgid, ahour, aday ORDER BY aday DESC, ahour, tgid;"); |
// TODO use tgid categories instead, tgid too specific |
// TODO use tgid categories instead, tgid too specific |
$sth->execute(); |
$sth->execute(); |
$hourlies = $sth->fetchAll(PDO::FETCH_ASSOC); |
$hourlies = $sth->fetchAll(PDO::FETCH_ASSOC); |
foreach ($hourlies as $hourly) { |
foreach ($hourlies as $hourly) { |
processHourly($hourly); |
processHourly($hourly); |
} |
} |
$sth = $conn->prepare("SELECT 'hour' AS tgid, EXTRACT(HOUR FROM call_timestamp) ahour, date_trunc('day', call_timestamp) aday, COUNT(filename), array_to_string(array_agg(filename ORDER BY call_timestamp), ',') filenames FROM recordings GROUP BY ahour, aday ORDER BY aday DESC, ahour;"); |
$sth = $conn->prepare("select 'hour' as tgid, extract(hour from call_timestamp) ahour, date_trunc('day', call_timestamp) aday, |
|
count(filename), array_to_string(array_agg(filename order by call_timestamp), ',') filenames from recordings group by ahour, aday order by aday DESC, ahour;"); |
|
|
$sth->execute(); |
$sth->execute(); |
$hourlies = $sth->fetchAll(PDO::FETCH_ASSOC); |
$hourlies = $sth->fetchAll(PDO::FETCH_ASSOC); |
foreach ($hourlies as $hourly) { |
foreach ($hourlies as $hourly) { |
processHourly($hourly); |
processHourly($hourly); |
} |
} |
|
$sth = $conn->prepare("select coalesce(category,'unknown') as tgid, extract(hour from call_timestamp) ahour, date_trunc('day', call_timestamp) aday, |
|
count(filename), array_to_string(array_agg(filename order by call_timestamp), ',') filenames |
|
from recordings inner join tgids on recordings.tgid = tgids.tgid group by category, ahour, aday order by aday DESC, ahour, category;"); |
|
|
|
$sth->execute(); |
|
$hourlies = $sth->fetchAll(PDO::FETCH_ASSOC); |
|
foreach($hourlies as $hourly) { |
|
processHourly($hourly); |
|
} |
|
|
|
|
// delete uninteresting compilations |
|
|
|