#====================================
#Navigation navigate_Records Buttons 1-4
# 1 Beginning
# 2 Previous 
# 3 Next
# 4 End	

sub navigate_Records {

	my $kind = $query->param('kind');
	my $rowpointer = $query->param('searchterm'); 
    my ($sth, $stmt);
	my @row;
    warn("Entered update_tables.cgi Open Database return First Record with action: = '$action'");   
	warn("Search Parameter = '$kind'");
	warn("rowpointer = '$rowpointer'");	

	my $minrowpointer;
	my $maxrowpointer;
	my $searchfield;

	warn("sortindex =  '$sortindex'"); #Set by selection in search_Records
	if (!$sortindex) {
	  $sortindex = 0;
	}
	if ($sortindex == 0){
	    $searchfield = 'user_id';
		$minrowpointer = 1;
	    $maxrowpointer = 14999;
	   } 
	elsif ($sortindex == 1) {
        $searchfield = 'lastname';	
		$minrowpointer = 'a%';
	    $maxrowpointer = 'z%';
	   }
	elsif ($sortindex == 2) {
	    $searchfield = 'business';	
		$minrowpointer = 'a%';
	    $maxrowpointer = 'z%';
       } 	   
	
	

	#kind 0 & 3 ok with direct hard coded SQL 
	
	if ($kind == 0){  
		#beginning record Tested Good

        $stmt= "SELECT * FROM ( SELECT * FROM users WHERE $searchfield <= ? AND $searchfield >= ? ORDER BY $searchfield ASC LIMIT 1 ) sub ORDER BY $searchfield ASC"; 

        # example user_id 
#		$stmt = "SELECT * FROM ( SELECT * FROM users WHERE user_id <= 14999 AND user_id >= 1 ORDER BY user_id ASC LIMIT 1 ) sub ORDER BY user_id ASC"; #Works fine hard coded
		
        #example lastname SELECT * FROM ( SELECT * FROM users WHERE lastname <='z%' AND lastname >= 'a%'ORDER BY lastname ASC LIMIT 1 ) sub ORDER BY lastname ASC #Works fine hard coded

		#example business SELECT * FROM ( SELECT * FROM users WHERE business <='z%' AND business >= 'a%'ORDER BY business ASC LIMIT 1 ) sub ORDER BY business ASC 
	}
	
	if ($kind == 1){
		#previous record Tested Good
		$stmt = "SELECT * FROM users WHERE $searchfield <= (SELECT MAX($searchfield) FROM users WHERE $searchfield < ?) ORDER BY $searchfield DESC LIMIT 1";
	}                                            
  
	if ($kind == 2){
		#next record Tested Good
		$stmt = "SELECT * FROM users WHERE $searchfield >= (SELECT MIN($searchfield) FROM users WHERE $searchfield > ?) ORDER BY $searchfield ASC LIMIT 1";
   
	} 

	if ($kind == 3){
		#Last record Tested Good
		
        $stmt = "SELECT * FROM ( SELECT * FROM users WHERE $searchfield <= ? AND $searchfield >= ? ORDER BY $searchfield DESC LIMIT 1 ) sub ORDER BY $searchfield ASC"; 
		
        #example user_id 
#		$stmt = "SELECT * FROM ( SELECT * FROM users WHERE user_id <=14999 AND user_id >= 1 ORDER BY user_id DESC LIMIT 1 ) sub ORDER BY user_id ASC"; #Works fine hard coded
        		
		#example lastname SELECT * FROM ( SELECT * FROM users WHERE lastname >='z%' AND lastname >= 'a%'ORDER BY user_id DESC LIMIT 1 ) sub ORDER BY lastname ASC #Works fine hard coded

        #example business SELECT * FROM ( SELECT * FROM users WHERE business >='z%' AND business >= 'a%'ORDER BY business DESC LIMIT 1 ) sub ORDER BY business ASC 
	}

	warn("statement =  '$stmt'");
	
	$sth = $dbh->prepare ($stmt) or die "Error Preparing:\n" . $stmt . "\nDBI returned: \n", $dbh->errstr;

#	warn("'$maxrowpointer' '$minrowpointer' '$searchfield' '$rowpointer'");

   	if (($kind == 0) || ($kind == 3)){
	   warn("kind = '$kind' maxrowpointer = '$maxrowpointer'  minrowpointer = '$minrowpointer'"); 
	   $sth->execute ($maxrowpointer,$minrowpointer) or die "Unable to execute query: " . $sth->errstr;
     } 
	elsif (($kind == 1) || ($kind == 2)){
	   warn("kind = '$kind' rowpointer = '$rowpointer'"); 
       $sth->execute($rowpointer) or die "Unable to execute query: " . $sth->errstr;	
	}
     
    my $navResult = $sth->fetchrow_hashref();
	
	warn("navResult = '$navResult'");
	
    my $count = $sth->rows;

    warn("count = '$count'");
if ($count == 0) {
	  my %navFail = ( Count => $count);
	  my $json = JSON->new; 
	  $json->canonical(1);
	  $json = encode_json(\%navFail);
  	  print "Content-Type: application/json\n\n";
	  print $json;
	  warn("Finished print 0 count $json");	
      exit(1);     }
    else {
	  warn("count = '$count'");
      my $json = JSON->new; 
	  $json->canonical(1);
	  $json = encode_json($navResult);
	  print "Content-Type: application/json\n\n";	  
	  print $json;
	  warn("Finished print $json");	
      exit(0);
	}

}