--- a/generateHourlys.php
+++ b/generateHourlys.php
@@ -1,24 +1,46 @@
 <?php
 include('common.inc.php');
-$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), ',') filenames from recordings group by tgid, ahour, aday order by  aday DESC, ahour, tgid;");
+function processHourly($hourly)
+{
+
+    $hfilename = $hourly['tgid'] . '-' . str_replace(' 00:00:00+10', '', $hourly['aday']) . '-' . $hourly['ahour'] . '.3gp';
+
+    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 . "<br>\n";
+        if ($returncode != 10) {
+            print_r($output);
+            //die();
+        } else {
+            $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;");
+// TODO use tgid categories instead, tgid too specific
+$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) {
-    $filename = $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 -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>';
-        if ($returncode != 10) {
-            //print_r($output);
-            //die();
-        }
-    }
+foreach ($hourlies as $hourly) {
+    processHourly($hourly);
 }
 
 
+// delete uninteresting compilations
+