photo-stats

commit bab8a6a7c7a491395dca0089cab3a1df2d5cdc4c
parent 9f8bb9eb4bb362c53cb0da72bae87c98bd8dc39a
Author: René Wagner <rwagner@rw-net.de>
Date:   Sat, 15 Aug 2020 10:02:12 +0200

allow filtering of time range

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

1 file changed, 25 insertions(+), 5 deletions(-)
diff --git a/phosta.pl b/phosta.pl
@@ -27,13 +27,14 @@ my $configfile = catfile(File::HomeDir->my_home, '.phosta.conf');
 # read commandline switches
 our $opt_D=getcwd . "/photo_stats.db";
 our $opt_p='';
+our $opt_t='';
 our $opt_c=0;
 our $opt_s='';
 our $opt_g='';
 
 getconfig($configfile);
 
-getopts('cp:g:s:D:') or die "Invalid parameters provided! See 'phosta --help' for more details.";
+getopts('cp:g:s:t:D:') or die "Invalid parameters provided! See 'phosta --help' for more details.";
 validate() or die "Invalid parameters provided! See 'phosta --help' for more details.";
 my $dsn = "DBI:SQLite:dbname=$opt_D";
 
@@ -62,6 +63,8 @@ sub validate
         if ( ! ($_ ~~ @select_params) ) { return 0; }
     }
 
+    if ($opt_t ne '' && $opt_t !~ /^([0-9]{8}){0,1}\-([0-9]{8}){0,1}$/) { return 0; }
+
     return 1;
 }
 
@@ -128,6 +131,7 @@ sub get_sql
     my $fieldlist = '';
     my $grouplist = '';
     my $orderlist = '';
+    my $wherelist = '';
     given ($grouping) 
     {
         when ('month')
@@ -150,7 +154,7 @@ sub get_sql
     if ($selected ne '') 
     {
         $fieldlist = $fieldlist . $selected . ', ';
-        $grouplist = $grouplist . ($grouplist ne '' ? ',' : '') . $selected;
+        $grouplist .= ($grouplist ne '' ? ',' : '') . $selected;
     }
 
     if ($selected ne '')
@@ -163,16 +167,30 @@ sub get_sql
             $orderlist = $orderlist . ' 1 DESC,'; 
             $lastcolumn++;
         }
-        $orderlist = $orderlist . " $lastcolumn DESC";
+        $orderlist .= " $lastcolumn DESC";
     }
     elsif ($grouping ne '')
     {
         $orderlist = 'ORDER BY 1 DESC';
     }
 
-    if ( $grouplist ne '' ) { $grouplist = 'GROUP BY '. $grouplist; }
+    if  ($opt_t ne '')
+    {
+        my ($from, $to) = split (/\-/, $opt_t);
+        if ($from ne '')
+        {
+            $wherelist = 'datetimeoriginal >= \''. substr($from, 0, 4) .'-'. substr($from, 4, 2) .'-'. substr($from, 6, 2) .'\'';
+        }
+        if ($to ne '')
+        {
+            $wherelist .= ($wherelist ne '' ? ' AND ' : '') . 'datetimeoriginal <= \''. substr($to, 0, 4) .'-'. substr($to, 4, 2) .'-'. substr($to, 6, 2) .'\'';
+        }
+    }
+
+    if ($grouplist ne '') { $grouplist = 'GROUP BY '. $grouplist; }
+    if ($wherelist ne '') { $wherelist = 'WHERE ' . $wherelist; }
 
-    return "SELECT $fieldlist count(file) as count FROM photos $grouplist $orderlist";
+    return "SELECT $fieldlist count(file) as count FROM photos $wherelist $grouplist $orderlist";
 }
 
 sub query_db
@@ -232,4 +250,6 @@ sub main::HELP_MESSAGE
     say '   -s          : (query mode) specify the information you want to select, defaults to none (just show number of images)';
     say '                 allowed values: maker, model, lens, aperture, exposuretime, iso, focallength, focallength35mm';
     say '                 multiple fields should be listed comma-separated';
+    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';
 }