photo-stats

commit 46289e9340be6ea45f5a61738a9a61f336623d27
parent 11fbae0e8848fb6f6b9e7b1dae4d1e07f87901a8
Author: René Wagner <rwagner@rw-net.de>
Date:   Wed, 26 Aug 2020 19:29:40 +0200

show applied timerange filter on output

Diffstat:
M
phosta.pl
 | 
70
++++++++++++++++++++++++++++++++++++++++++----------------------------

1 file changed, 42 insertions(+), 28 deletions(-)
diff --git a/phosta.pl b/phosta.pl
@@ -109,7 +109,7 @@ sub create_db
     my $dbh = DBI->connect($dsn, '', '', { RaiseError => 1 }) or die $DBI::errstr;
 
     my $stmt = 'CREATE TABLE photos (file TEXT PRIMARY KEY, maker TEXT, model TEXT, lensmake TEXT, lens TEXT, focallength INTEGER, focallength35mm INTEGER, aperture DECIMAL, exposuretime TEXT, iso INTEGER, flash TEXT, datetimeoriginal DATETIME);';
-    if ($opt_v) { say "### DB Statement: $stmt"; }
+    !$opt_v or say "### DB Statement: $stmt";
     my $rv = $dbh->do($stmt);
 }
 
@@ -120,7 +120,7 @@ sub populate
     my $extensions = ' -ext ' . join(' -ext ', split(/\,/, $opt_E)) .' ';
 
     my $cmd = "exiftool -fast2 -r -m -f -p '\$filepath##\$make##\$model##\$lensmake##\$lens##\$lensmodel##\$focallength##\$focallengthin35mmformat##\$aperture##\$exposuretime##\$shutterspeed##\$iso##\$flash##\$datetimeoriginal' -d \"%Y-%m-%d %H:%M:%S\" " . $extensions . $opt_p;
-    if ( $opt_v ) { say "### Exiftool command: $cmd"; }
+    !$opt_v or say "### Exiftool command: $cmd";
     my @lines = `$cmd`;
 
     my $dbh = DBI->connect($dsn, '', '', { RaiseError => 1 }) or die $DBI::errstr;
@@ -154,15 +154,23 @@ sub populate
     say "Updated database $opt_D.";
 }
 
-sub get_sql
+sub get_timerange
 {
-    my ($selected, $grouping, $order, $timerange, $filter) = @_;
+    my ($from, $to) = split(/\-/, $opt_t);
+     
+    $from = $from ne '' ? substr($from, 0, 4) .'-'. substr($from, 4, 2) .'-'. substr($from, 6, 2) : undef;
+    $to = $to ne '' ? substr($to, 0, 4) .'-'. substr($to, 4, 2) .'-'. substr($to, 6, 2) : undef;
+
+    return ($from, $to);
+}
 
+sub get_sql
+{
     my $fieldlist = '';
     my @grouparray;
     my $orderlist = '';
     my @wherearray; 
-    given ($grouping) 
+    given ($opt_g) 
     {
         when ('month')
         {
@@ -186,35 +194,29 @@ sub get_sql
         }
     }
 
-    if (defined($selected))
+    if (defined($opt_s))
     {
-        $fieldlist = $fieldlist . $selected . ', ';
-        push @grouparray, $selected;
+        $fieldlist = $fieldlist . $opt_s. ', ';
+        push @grouparray, $opt_s; 
     }
 
-    if (defined($order))
+    if (defined($opt_o))
     {
         my $sorter = $opt_r ? ' ASC' : ' DESC';
-        my @order = split(/\,/, $order);
+        my @order = split(/\,/, $opt_o);
         $orderlist = ' ORDER BY '. join("$sorter, ", @order) .$sorter;
     }
 
-    if (defined($timerange))
+    if (defined($opt_t))
     {
-        my ($from, $to) = split(/\-/, $timerange);
-        if ($from ne '')
-        {
-            push @wherearray, 'datetimeoriginal >= \''. substr($from, 0, 4) .'-'. substr($from, 4, 2) .'-'. substr($from, 6, 2) .'\'';
-        }
-        if ($to ne '')
-        {
-            push @wherearray, 'datetimeoriginal <= \''. substr($to, 0, 4) .'-'. substr($to, 4, 2) .'-'. substr($to, 6, 2) .'\'';
-        }
+        my ($from, $to) = get_timerange();
+        !defined($from) or push @wherearray, "datetimeoriginal >= '$from'";
+        !defined($to) or push @wherearray, "datetimeoriginal <= '$to'"; 
     }
 
-    if (defined($filter))
+    if (defined($opt_f))
     {
-        my (@filters) = split /\,/, $filter;
+        my (@filters) = split /\,/, $opt_f;
         foreach (@filters)
         {
             my ( $sub1, $sub2, $sub3 ) = split /(!){0,1}=/, $_;
@@ -239,8 +241,8 @@ sub query
     say "Querying database $opt_D with $total_count entries...";
     say '';
     $total_count > 0 or return;
-    my $sql = get_sql($opt_s, $opt_g, $opt_o, $opt_t, $opt_f);
-    if ($opt_v) { say '### SQL Statement: '. $sql; }
+    my $sql = get_sql();
+    !$opt_v or say '### SQL Statement: '. $sql;
     my $stmt = $dbh->prepare($sql);
     $stmt->execute();
     
@@ -291,6 +293,18 @@ sub result_to_stdout
         say 'A total of '. color('bold'). "$sum images". color('reset') .' matched your criteria.';
         say '';
     }
+
+    if (defined($opt_f) || defined($opt_t))
+    {
+        say 'results consider the following filters';
+        if ($opt_t)
+        {
+            my ($from, $to) = get_timerange();
+            !defined($from) or say '   - images taken since '. color('bold'). $from . color('reset');
+            !defined($to) or say   '   - images taken till '. color('bold'). $to . color('reset');          
+        }
+        say '';
+    }
     say $tb->draw; 
 }
 
@@ -321,16 +335,16 @@ sub main::HELP_MESSAGE
     say '                 Media files in the given folder and every subfolder are scanned, EXIF data extracted and pulled into the database';
     say '   -E <ext>    : list of comma separated extensions used for scanning image files, defaults to jpg,jpeg';
     say '                 only media files which match (case-insensitive!) the given extensions are added to the database';
-    say '                 This option is saved to the user conf';
+    say '                 This option is automatically saved to the user conf';
     say '';
     say 'stats querying:';
     say '   -g <period> : group by a time period, defaults to total (which means no grouping by period)';
     say '                 allowed values: year, month, week, hour';
     say '   -s <fields> : specify the information you want to select, defaults to none (just show number of images)';
     say '                 allowed values: maker, model, lensmake, lens, aperture, exposuretime, iso, focallength, focallength35mm';
-    say '                 multiple fields should be listed comma-separated';
+    say '                 multiple fields should be listed comma-separated without whitespaces';
     say '   -t <range>  : only take images into account which have been taken in the given timerange';
-    say '                 <range> must be specified as \'YYYYMMDD-YYYYMMDD\', you can omit one value';
+    say '                 <range> must be specified as \'YYYYMMDD-YYYYMMDD\', you can omit the first or the later value';
     say '   -f <expr>   : filter images based on the given expression, multiple expression can be given comma-separated';
     say '                 this works as a case-insensitive CONTAINS search, multiple expressions need to match all (AND logic)'; 
     say '                 use "field=value" for CONTAINS and "field!=value" for NOT CONTAINS, value must not include whitespaces';
@@ -345,7 +359,7 @@ sub main::HELP_MESSAGE
     say '   load EXIF data of files with the extensions jpg, jpeg and tiff in folder ~/Pictures to the database located in ~/Documents/stats.db';
     say '';
     say '   phosta -s model -g month -t 20190101-20121231 -o month,count';
-    say '   show number of pictures taken in with a specific camera body in 2019 grouped by month, sorted by newest month first';
+    say '   show number of pictures taken with a specific camera body in 2019 grouped by month, sorted by newest month first';
     say '';
     say '   phosta -n 10 -s lens -o count'; 
     say '   show top 10 lenses used the most';