photo-stats

commit d56591f42be3acdbba809a4af629655a35712e9f
parent 1fe3ae7e36fba77d471eb6e3b523ef1d9e7905c5
Author: René Wagner <rwagner@rw-net.de>
Date:   Fri, 14 Aug 2020 08:52:19 +0200

allow selection of a field

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

1 file changed, 20 insertions(+), 13 deletions(-)
diff --git a/phosta.pl b/phosta.pl
@@ -5,6 +5,7 @@
 
 use v5.32;
 use warnings;
+no warnings 'experimental';
 use strict;
 use feature qw(say);
 use POSIX qw(lround);
@@ -81,31 +82,36 @@ sub get_sql
 {
     my ($selected, $grouping) = @_;
 
-    my $grouping_field = '';
-    my $grouping_group = '';
+    my $fieldlist = '';
+    my $grouplist = '';
     given ($grouping) 
     {
         when ('week')
         {
-            $grouping_field = "strftime('%Y/%m', datetimeoriginal) as month,";
-            $grouping_group = "GROUP BY strftime('%Y/%m', datetimeoriginal)";
+            $fieldlist = "strftime('%Y/%m', datetimeoriginal) as month,";
+            $grouplist = "strftime('%Y/%m', datetimeoriginal)";
         }
         when ('month')
         {
-            $grouping_field = "strftime('%Y/%W', datetimeoriginal) as week_of_year,";
-            $grouping_group = "GROUP BY strftime('%Y/%W', datetimeoriginal)";
+            $fieldlist = "strftime('%Y/%W', datetimeoriginal) as week_of_year,";
+            $grouplist = "strftime('%Y/%W', datetimeoriginal)";
         }
         when ('year')
         {
-            $grouping_field = "strftime('%Y', datetimeoriginal) as year,";
-            $grouping_group = "GROUP BY strftime('%Y', datetimeoriginal)";
-        }
-        default
-        {
+            $fieldlist = "strftime('%Y', datetimeoriginal) as year,";
+            $grouplist = "strftime('%Y', datetimeoriginal)";
         }
     }
 
-    return qq/SELECT $grouping_field count(file) as photo_count FROM photos $grouping_group ORDER BY 1 DESC/;
+    if ($selected ne '') 
+    {
+        $fieldlist = $fieldlist . $selected . ', ';
+        $grouplist = $grouplist . ','. $selected;
+    }
+
+    if ( $grouplist ne '' ) { $grouplist = 'GROUP BY '. $grouplist; }
+
+    return qq/SELECT $fieldlist count(file) as photo_count FROM photos $grouplist ORDER BY 1 DESC/;
 }
 
 sub query_db
@@ -117,7 +123,8 @@ sub query_db
     my $total_count = $dbh->selectrow_array("SELECT count(file) from photos");
     say "Querying local database with $total_count entries...";
     say '';
-    
+   
+    #say get_sql($selected, $grouping);
     my $stmt = $dbh->prepare(get_sql($selected, $grouping));
     my @row;
     $stmt->execute();