<?php
include('common.inc.php');
function processHourly($hourly) {
    $filename = str_replace(' ',"_",$hourly['tgid']).'-'.str_replace(' 00:00:00+1','',$hourly['aday']).'-'.$hourly['ahour'].'.3gp';
 
    if(!file_exists("hourly/".$filename)) {
 
        $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/".$filename . ' 2>&1';
        //print_r($hourly);
        exec ( $cmd,$output,$returncode );
        echo $cmd."<br>\n";
        if ($returncode != 10) {
            //print_r($output);
            //die();
        }
    }
}
/*$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->execute();
$hourlies = $sth->fetchAll(PDO::FETCH_ASSOC);
foreach($hourlies as $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->execute();
$hourlies = $sth->fetchAll(PDO::FETCH_ASSOC);
foreach($hourlies as $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);
}