| <?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'; | global $conn; |
| $hfilename = str_replace(' ','_',$hourly['tgid']) . '-' . str_replace(Array(' 00:00:00+10',' 00:00:00+11'), '', $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"; |
| if (count($filenames) > 1) { | |
| $cmd .=" -filter_complex concat=n=" . count($filenames) . ":v=0:a=1"; // only concat when more than 1 file | |
| } | |
| $cmd .=" -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 != 0) { |
| echo $returncode; | |
| print_r($output); | print_r($output); |
| //die(); | unlink("hourly/" . $hfilename); // delete incomplete file |
| // die(); | |
| } else { | } else { |
| $q = " insert into compilations (filename files datetime) ('" . $hfilename . "',{'" . implode("', '", $filenames) . "'},'" . strtottime($hourly['aday'] . ' +' . $hourly['ahour'] . " hours')") . "' "; | |
| $time = strtotime($hourly['aday'] . ' +' . $hourly['ahour'] . " hours"); | |
| $q = " insert into compilations (filename,files,datetime) VALUES ('" . $hfilename . "', ARRAY['" . implode("', '", $filenames) . "'], to_timestamp('" . $time . "') );"; | |
| $conn->query($q); | |
| //echo $q."\n"; | |
| 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' ;"; |
| $conn->query($q); | |
| //echo $q."\n"; | |
| } | } |
| //die(); | |
| } | } |
| } | } |
| } | } |
| /*$sth = $conn->prepare("select tgid, extract(hour from call_timestamp) ahour, date_trunc('day', call_timestamp) aday, | /*$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;"); | 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, | $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;"); | 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, | $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 | 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;"); | from recordings inner join tgids on recordings.tgid = tgids.tgid |
| group by category, ahour, aday | |
| having count(archived) != count(filename) | |
| order by aday DESC, ahour, category;"); | |
| $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); |
| } | } |