photo-stats

commit 817ba7177d739b2d62ce7ca9872b4c1d6238b769
parent dfa50ac7e8a32a33f67e329c8fdb4651d001aa80
Author: René Wagner <rwagner@rw-net.de>
Date:   Sun, 16 Aug 2020 21:22:40 +0200

allow custom sorting including reverse order

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

1 file changed, 31 insertions(+), 32 deletions(-)
diff --git a/phosta.pl b/phosta.pl
@@ -20,7 +20,7 @@ use File::HomeDir;
 
 $Getopt::Std::STANDARD_HELP_VERSION = 'true';
 
-my $VERSION = '0.6';
+my $VERSION = '0.8';
 my $PROGRAM = 'Photo Stats';
 
 my $configfile = catfile(File::HomeDir->my_home, '.phosta.conf');
@@ -29,16 +29,18 @@ my $configfile = catfile(File::HomeDir->my_home, '.phosta.conf');
 our $opt_E='jpg,jpeg';
 our $opt_D=getcwd . "/photo_stats.db";
 our $opt_p='';
-our $opt_t='';
+our $opt_t=undef;
 our $opt_c=0;
-our $opt_s='';
-our $opt_g='';
+our $opt_s=undef;
+our $opt_g=undef;
 our $opt_v=0;
 our $opt_n=undef;
+our $opt_o='count';
+our $opt_r=0;
 
 getconfig($configfile);
 
-getopts('vcp:n:g:s:t:D:E:') or die "Invalid parameters provided! See 'phosta --help' for more details.";
+getopts('vcrp:n:g:s:t:D:E:o:') 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";
 
@@ -58,18 +60,23 @@ exit 0;
 
 sub validate
 {
-    my @group_params = ('year', 'month', 'week', 'hour', '');
-    if ( ! ($opt_g ~~  @group_params) ) { return 0; }
+    my @group_params = ('year', 'month', 'week', 'hour');
+    if (defined($opt_g) && ! ($opt_g ~~  @group_params) ) { return 0; }
 
     my @select_params = ('maker', 'model', 'lensmake', 'lens', 'aperture', 'exposuretime', 'iso', 'focallength', 'focallength35mm', '');
-    foreach (split (/,/, $opt_s))
+    if (defined($opt_s))
     {
-        if ( ! ($_ ~~ @select_params) ) { return 0; }
+        foreach (split (/,/, $opt_s))
+        {
+            if ( ! ($_ ~~ @select_params) ) { return 0; }
+        }
     }
 
+    #if (defined($opt_o) && !($opt_o ~~ @select_params) && !($opt_o ~~ @group_params)) { return 0; };
+
     if (defined($opt_n) && (!looks_like_number($opt_n) || $opt_n < 1)) { return 0; }
 
-    if ($opt_t ne '' && $opt_t !~ /^([0-9]{8}){0,1}\-([0-9]{8}){0,1}$/) { return 0; }
+    if (defined($opt_t) && $opt_t !~ /^([0-9]{8}){0,1}\-([0-9]{8}){0,1}$/) { return 0; }
 
     if ($opt_E !~ /^([a-z]{2,4}){1,}(\,[a-z]{2,4}){0,}$/) { return 0; }
 
@@ -144,7 +151,7 @@ sub populate
 
 sub get_sql
 {
-    my ($selected, $grouping) = @_;
+    my ($selected, $grouping, $order, $timerange) = @_;
 
     my $fieldlist = '';
     my $grouplist = '';
@@ -159,7 +166,7 @@ sub get_sql
         }
         when ('week')
         {
-            $fieldlist = "IFNULL(strftime('%Y/%W', datetimeoriginal), '-') as week_of_year,";
+            $fieldlist = "IFNULL(strftime('%Y/%W', datetimeoriginal), '-') as week,";
             $grouplist = "strftime('%Y/%W', datetimeoriginal)";
         }
         when ('year')
@@ -174,32 +181,22 @@ sub get_sql
         }
     }
 
-    if ($selected ne '') 
+    if (defined($selected))
     {
         $fieldlist = $fieldlist . $selected . ', ';
         $grouplist .= ($grouplist ne '' ? ',' : '') . $selected;
     }
 
-    if ($selected ne '')
-    {
-        my $lastcolumn = $selected =~ tr/,//;
-        $lastcolumn += 2;
-        $orderlist = 'ORDER BY ';
-        if ($grouping ne '')
-        { 
-            $orderlist = $orderlist . ' 1 DESC,'; 
-            $lastcolumn++;
-        }
-        $orderlist .= " $lastcolumn DESC";
-    }
-    elsif ($grouping ne '')
+    if (defined($order))
     {
-        $orderlist = 'ORDER BY 1 DESC';
+        my $sorter = $opt_r ? ' ASC' : ' DESC';
+        my @order = split(/\,/, $order);
+        $orderlist = ' ORDER BY '. join("$sorter, ", @order) .$sorter;
     }
 
-    if  ($opt_t ne '')
+    if (defined($timerange))
     {
-        my ($from, $to) = split (/\-/, $opt_t);
+        my ($from, $to) = split(/\-/, $timerange);
         if ($from ne '')
         {
             $wherelist = 'datetimeoriginal >= \''. substr($from, 0, 4) .'-'. substr($from, 4, 2) .'-'. substr($from, 6, 2) .'\'';
@@ -224,8 +221,8 @@ sub query
     say "Querying database $opt_D with $total_count entries...";
     say '';
    
-    if ($opt_v) { say '### SQL Statement: '. get_sql($opt_s, $opt_g); }
-    my $stmt = $dbh->prepare(get_sql($opt_s, $opt_g));
+    if ($opt_v) { say '### SQL Statement: '. get_sql($opt_s, $opt_g, $opt_o, $opt_t); }
+    my $stmt = $dbh->prepare(get_sql($opt_s, $opt_g, $opt_o, $opt_t));
     $stmt->execute();
     
     my $rows = $stmt->fetchall_arrayref;
@@ -311,10 +308,12 @@ sub main::HELP_MESSAGE
     say 'stats querying:';
     say '   -g          : group by time range, defaults to total (which means no grouping by time range)';
     say '                 allowed values: year, month, week, hour';
-    say '   -s          : specify the information you want to select, defaults to none (just show number of images)';
+    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 '   -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 '   -n <number> : limit the resultset to <number> of lines';
+    say '   -o <fields> : sort your output by the given fields (sequence matters!)';
+    say '   -r          : sort in reverse (ascending) order';
 }