--- a/generateHourlys.php
+++ b/generateHourlys.php
@@ -1,41 +1,43 @@
&1';
+ if (!file_exists("hourly/" . $hfilename)) {
+
+ $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';
//print_r($hourly);
- exec ( $cmd,$output,$returncode );
- echo $cmd."
\n";
+ exec($cmd, $output, $returncode);
+ echo $cmd . "
\n";
if ($returncode != 10) {
- //print_r($output);
+ print_r($output);
//die();
} else {
- /* insert
- "filename" text NOT NULL,
- "files" text ARRAY NOT NULL,
- "datetime" timestamp NOT NULL
- */
- // delete wav files? can we link to times in a compilation?
+ $q = " insert into compilations (filename files datetime) ('" . $hfilename . "',{'" . implode("', '", $filenames) . "'},'" . strtottime($hourly['aday'] . ' +' . $hourly['ahour'] . " hours')") . "' ";
+
+ foreach ($filenames as $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;");
// TODO use tgid categories instead, tgid too specific
$sth->execute();
$hourlies = $sth->fetchAll(PDO::FETCH_ASSOC);
-foreach($hourlies as $hourly) {
+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 = $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) {
+foreach ($hourlies as $hourly) {
processHourly($hourly);
}