backup tgids master
[scannr.git] / generateHourlys.php
Alex Sadleir
Alex Sadleir
Alex Sadleir


Alex Sadleir
Alex Sadleir




Alex Sadleir
Alex Sadleir

Alex Sadleir



Alex Sadleir


Alex Sadleir
Alex Sadleir
Alex Sadleir

Alex Sadleir

Alex Sadleir
Alex Sadleir

Alex Sadleir
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
<?php
include('common.inc.php');
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) {
    processHourly($hourly);
}
 
 
// delete uninteresting compilations