#!/usr/bin/perl -w

# search.page
# creates the search page

    # set libraries
    use strict;
    use DBI;
    use CGI qw(:standard *div *param *table *main *ul *li checkbox*);

    # intiail variables
    my $n = "\015\012";
    my $base_url = 'http://philately.dyerhouse.com';
    my $dbh = connect_to_database();
    
    # capture values from user
    my $search_term = param('search_term') || '';
    my @sc = param('collections_selected');
    my @sa = param('albums_selected');
    my $year_spec = param('year_spec') || '';
    my $cat_spec = param('cat_spec') || '0';
    my $type_spec = param('type_spec') || '';

    my %collection_hash = ( it=>' Italy', un=>' United Nations', us=>' United States' );
    my %album_hash = ( 'pre-states'=>' Pre-Italy', 'kingdom'=>' Kingdom of Italy', 
                       'colonies'=>' Italian Colonies', 'transition'=>' Italy in Transition', 
                       'republic'=>' Italian Republic', 'new-york'=>' U.N. New York', 
                       'early-years'=>' U.S. Early Years', 'bureau'=>' U.S. Bureau Stamps', 
                       'post-world-wars'=>' U.S. Post World Wars' );

    my %type_hash = ( aa=>'All Types' );
    my $types = get_types($dbh);

    foreach (@$types) {
       my ($key,$value) = @$_;
       $type_hash{$key} = $value;
    }
    my @type_keys = keys %type_hash;
    @type_keys = sort @type_keys;

    # dealing with no collections or albums selected
    my $collection_count = @sc;
    my $album_count = @sa;
    if($collection_count == 0) { 
       @sc = ( keys %collection_hash );
    }
    if($album_count == 0) { 
       @sa = ( keys %album_hash );
    }


    my $heading = 'Search Page';
    my $description = 'Experimental page for search stamps database.';
    my $main_text = 'This is an experimental page for searching my stamps database.';

    my @header_extras = (meta({-http_equiv=>'Content-Type', -content=>'text/html; charset=utf-8'}),
                         Link({-rel=>'icon', -type=>'text/css', -href=>'/favicon.ico', -media=>'image/x-icon'}),
                         Link({-rel=>'stylesheet', -type=>'text/css', -href=>'/_static/pygments.css', -media=>'screen'}),
                         Link({-rel=>'stylesheet', -type=>'text/css', -href=>'/_static/theme.css', -media=>'screen'}),
                         Link({-rel=>'stylesheet', -type=>'text/css', -href=>'/_static/main.css', -media=>'screen'}),
                         Link({-rel=>'stylesheet', -type=>'text/css', -href=>'/_static/dark.css', -media=>'screen'}),
                         Link({-rel=>'stylesheet', -type=>'text/css', -href=>'/_static/tables.css', -media=>'screen'}),
                         Link({-rel=>'stylesheet', -type=>'text/css', -href=>'/_static/mobile.css', -media=>'screen'}),
                         Link({-rel=>'stylesheet', -type=>'text/css', -href=>'/_static/printing.css', -media=>'print'}), 
                         Link({-rel=>'stylesheet', -type=>'text/css', -href=>'http://use.fontawesome.com/releases/v5.0.7/css/all.css', -media=>'screen'}),);

    # web page
    print header(-charset=>'UTF-8'), $n,
          start_html(-title=>"$heading", -lang=>'en-US',
                     -head=>\@header_extras,
                     -meta=>{'title'=>$heading,
                             'copyright'=>qq|Copyright 2022 - 2024 Russell J.T. Dyer. All rights reserved|,
                             'description'=>$description,
                             'viewport'=>qq|width=device-width,initial-scale=1.0|}), $n,$n,

          start_div({id=>'page', class=>'page'}), $n,$n,

          start_div({id=>'left_margin'}), $n,
          "<nav data-toggle='wy-nav-shift' class='wy-nav-side'>", $n,
          start_div({id=>'left_mgn_search'}), $n,
          
          start_div({class=>'wy-side-nav-search left_mgn_heading'}), a({href=>'../../index.page', class=>'fas fa-home'}, '&nbsp;philately.dyerhouse'), $n,

          start_div({role=>'search', class=>'left_mgn_search_box'}), $n,
          start_form({-id=>'left_form', -class=>'left_mgn_search_form', -method=>'POST', -enctype=>&CGI::URL_ENCODED, -action=>'search.page'}), $n,
          textfield(-name=>'search_term', -class=>'search_term', -value=>$search_term, -placeholder=>'Search Collections'), $n,
          end_form(), $n,
          end_div(), end_div(), $n, # end search & left_mgn_heading divs
          end_div(), $n,$n, # end left_mgn_search div

          # left margin
          h4({class=>'left_margin'}, 'Collections'), $n,
          
          start_div({class=>'wy-menu wy-menu-vertical', data_spy=>'affix', role=>'navigation', aria_label=>'Navigation menu'}), $n,
          start_ul({-class=>'level_1'}), $n,
          li({-class=>'level_1'},
          a({href=>'/italy/index.page', class=>'current reference internal'}, 'Italy')), $n,
            start_ul({-class=>'level_2'}), $n,
            li({-class=>'level_2'},
            a({href=>'/italy/pre-states/index.page', class=>'reference internal'}, 'Italian States (1850 - 1868)')), $n,
            li({-class=>'level_2'}, 
            a({href=>'/italy/kingdom/index.page', class=>'reference internal'}, 'Kingdom of Italy (1861 - 1943)')), $n,
            li({-class=>'level_2'}, 
            a({href=>'/italy/colonies/index.page', class=>'reference internal'}, 'Italian Colonies & Occupations')), $n,
            li({-class=>'level_2'}, 
            a({href=>'/italy/transition/index.page', class=>'reference internal'}, 'Italy in Transition (1943 - 45)')), $n,
            li({-class=>'level_2'}, 
            a({href=>'/italy/republic/index.page', class=>'reference internal'}, 'Republic of Italy (1945 - Present)')), $n,
            end_ul(), $n,$n,
          li({-class=>'level_1'},
          a({href=>'/united-nations/index.page', class=>'current reference internal'}, 'United Nations')), $n,
            start_ul({-class=>'level_2'}), $n,
            li({-class=>'level_2'},
            a({href=>'/united-nations/new-york/index.page', class=>'reference internal'}, 'U.N. New York (1951 - Present)')), $n,
            end_ul(), $n,$n,
          li({-class=>'level_1'},
          a({href=>'/us/index.page', class=>'current reference internal'}, 'United States')), $n,
            start_ul({-class=>'level_2'}), $n,
            li({-class=>'level_2'},
            a({href=>'/us/early-years/index.page', class=>'reference internal'}, 'Early Years')), $n,
            li({-class=>'level_2'},
            a({href=>'/us/bureau/index.page', class=>'reference internal'}, 'Bureau Stamps')), $n,
            li({-class=>'level_2'},
            a({href=>'/us/post-world-wars/index.page', class=>'reference internal'}, 'Post World Wars')), $n,
            end_ul(), $n,$n,
          end_ul(), $n,
          end_div(), $n, # end wy-menu-vertical div
          "</nav>", $n,
          end_div(), $n,$n, # end left_margin div

          # main panel
          start_div({class=>'main'}), $n,$n,
          
          "<nav role='navigation' aria-label='breadcrumbs' class='print:hidden mt-12 text-sm text-gray-light'>", $n,
          a({href=>'../../index.page', class=>'fas fa-home'}, "&nbsp;Russell's Philately"), $n,
          span({class=>'mr-1'}, '›'), span('Search'), $n,
          "</nav>", $n,$n,
          
          start_div({class=>'wy-nav-content'}), $n, 
          start_div({class=>'rst-content'}), $n,
          
          # main search form
          start_div({class=>'search_block'}), $n,
          
          start_form(-name =>'main_form', -method=>'POST', -enctype=>&CGI::URL_ENCODED, -action=>'search.page'), $n,
          b("Search Term"), br(), $n,
          textfield(-name=>'search_term', -class=>'search_term', -value=>$search_term, -placeholder=>'Enter Search Terms ', -size=>20, -maxlength=>20), $n,
          submit(-name=>'submit_form', -class=>'search_button', -value=>'Search'), br(), $n,

          b("Collections:"), br(), $n,
          checkbox_group({-name=>'collections_selected', -values=>['it','un','us'], -default=>['it','un','us'], -linebreak=>'false', -labels=>\%collection_hash}), br(),	 $n,

          b("Albums:"), br(), $n,
          checkbox_group({-name=>'albums_selected', -values=>['pre-states','kingdom','colonies','transition','republic','new-york','early-years','bureau','post-world-wars'], -default=>['pre-states','kingdom','colonies','transition','republic','new-york','early-years','bureau','post-world-wars'], -linebreak=>'false', -labels=>\%album_hash}), br(), $n,
          
          b("Specifics:"), br(), $n,
          span('Year: '), textfield({-name=>'year_spec', -class=>'search_specific', -value=>$year_spec, -default=>'0', -placeholder=>'Year Issued', -size=>4, -maxlength=>4}), $n,
          span('Catalog No: '), textfield({-name=>'cat_spec', -class=>'search_specific', -value=>$cat_spec, -default=>'0', -placeholder=>'Sassone', -size=>2, -maxlength=>4}), br(), $n,
          span('&nbsp; Type: '), popup_menu({-name=>'type_spec', -class=>'search_specific', -values=>\@type_keys, -labels=>\%type_hash, -default=>'aa'}), $n,

          end_form(), $n,
          end_div(), $n,$n,
          
          h1({class=>'search_heading'}, $heading), $n,
          p("This page and its form will allow you to search my stamp collections. You can enter multiple search terms, separated by spaces, but they will be treated as a phrase and only complete matches will be shown."), $n,
          p("To search on each term, separate the words by commas.  For example, to match all instances of <i>parcel</i> and <i>post</i>, you would enter <i>parcel, post</i>. To match only the pairing of those two words, enter it without commas (i.e., <i>parcel post</i>).  You can do a combination of single words and phrases (e.g., <i>parcel post, air mail</i>). Incidentally, you can search on any of the fields related an entry in the database for a stamp (e.g., year, color, etc.)."), $n,
          br({clear=>'all'}), $n,$n;
          
          my (@page_ids, @stamp_ids);

          if($cat_spec) { 
             my $stamp_found = catalog_search($dbh,\@sc,\@sa,$cat_spec);
             my $st_count = @$stamp_found || 0;
             if($st_count > 0) { 
                foreach (@$stamp_found) { 
                   my ($stamp_id) = @$_; 
                   push(@stamp_ids, $stamp_id);
                }
             }
          }
          elsif($year_spec) {
             my $stamps_found = stamps_search($dbh,\@sc,\@sa,$year_spec,$type_spec,$cat_spec,'');
             my $st_count = @$stamps_found || 0; 
             if($st_count > 0) { 
                foreach (@$stamps_found) { 
                   my ($stamp_id) = @$_; 
                   push(@stamp_ids, $stamp_id);
                }
             }
          }
          elsif($search_term) {
             my @terms = split(',', $search_term);
             my $term_count = @terms; 
             
             foreach (@terms) {
                my $pages_found = pages_search($dbh,\@sc,\@sa,$_);
                my $pg_count = @$pages_found || 0;
                if($pg_count > 0) { 
                   foreach (@$pages_found) {
                      my ($page_id) = @$_; 
                      if($year_spec || ($type_spec && $type_spec ne 'aa')) {
                         my $pages_found = page_check($dbh,$page_id,$year_spec,$type_spec);
                         if(!$pages_found) { next }
                      }
                      push(@page_ids, $page_id);
                   } 
                }
                
                my $stamps_found = stamps_search($dbh,\@sc,\@sa,$year_spec,$type_spec,$cat_spec,$_);
                my $st_count = @$stamps_found || 0;
                if($st_count > 0) { 
                   foreach (@$stamps_found) { 
                      my ($stamp_id) = @$_; 
                      push(@stamp_ids, $stamp_id);
                   }
                }
             } 
          }
          if($cat_spec or $year_spec or $search_term) { 
             my $pg_count = @page_ids || 0;
             my $st_count = @stamp_ids || 0;

             if($pg_count == 0 && $st_count == 0 ) { 
                print "Nothing found in the database matching the search term entered.", $n,
                       br({clear=>'all'}), br(), br(), hr(), br(), br(), $n,$n; 
             }
             elsif($pg_count > 0 || $st_count > 0 ) { 
                print h2({class=>'search_heading'}, "Search Results"), $n,
                      p("Below are the results of your search. They're in two groups: The first lists album pages that contain the search term in the title, description, or other text on the web page &mdash; not necessarily in the text related to the stamps on the album page. The second section lists stamps in my database for which the description or some other bit of data contains the search term."), $n,$n; 
             }

             # display page details
             if($pg_count > 0) { 
                @page_ids = do { my %seen; grep { !$seen{$_}++ } @page_ids };
             
                print h3({class=>'stamp-details rubric'}, 'Album Pages'), $n, hr(), $n,$n,         
                      start_table({-class=>'page-stamp-list', -width=>'90%', -border=>'0', -cellpadding=>'4', -cellspacing=>'4'}), $n,$n,
                      Tr(td({-width=>'10%'}, 'collection'), td({-width=>'30%'}, 'album & sub-album'), td({-width=>'25%'}, 'page title'), td({-width=>'35%'}, 'page description')), $n;

                foreach(@page_ids) {
                   my ($page_details,$page_stamp_cnt) = page_data($dbh,$_);
                   my ($page_id,$dir,$collect_name,$album_id,$file,$album_heading,$child_dir,$pg_title,$pg_label,$meta_desc) = @{$page_details->[0]};
                   
                   my $highlight = '';
                   if($search_term) { $highlight = $search_term }
                   elsif($cat_spec) { $highlight = $$cat_spec }
                   if($child_dir) { $child_dir = $child_dir . '/' }
                   my $path_file = "$base_url/$dir/$album_id/$child_dir$file.page?highlight=$highlight";

                   print Tr(td(a({href=>$path_file}, $collect_name)), 
                         td(a({href=>$path_file}, $album_heading . br() .' (' . $album_id . ', ' . $pg_label . ')')), 
                         td(a({href=>$path_file}, $pg_title . br() . $page_stamp_cnt . ' stamps')), 
                         td(a({href=>$path_file}, $meta_desc))), $n;          
                }
                print Tr(td({colspan=>'4'}, '&nbsp;')), $n,
                      end_table(), $n,$n;
             }

             if($pg_count > 0 || $st_count > 0 ) { 
                print p("To see a particular page or stamp, click on it. That will take you to the album web page you selected or, for a stamp, the album page containing the stamp. Your search terms will be highlighted."), br(), $n,$n;
             }

             # display stamp details
             if($st_count > 0) { 
                @stamp_ids = do { my %seen; grep { !$seen{$_}++ } @stamp_ids };
             
                print h3({class=>'stamp-details rubric'}, 'Stamps'), $n, hr(), $n,$n,
                      start_table({-class=>'page-stamp-list', -width=>'90%', -border=>'0', -cellpadding=>'0', -cellspacing=>'4'}), $n,$n,
                      Tr(td({-width=>'10%'}, 'collection'), td({-width=>'20%'}, 'album & sub-album'), td({-width=>'8%'}, 'catalog'), td({-width=>'20%'}, 'subject of stamp'), td({-width=>'8%'}, 'face amt.'), td({-width=>'10%'}, 'type'), td({-width=>'10%'}, 'issue date'), td({-width=>'14%'}, 'colors')), $n;
      
                foreach (@stamp_ids) {
                   my $stamp_details = stamp_data($dbh,$_);
                   my ($stamp_id,$page_id,$file,$dir,$collect_name,$album_heading,$child_dir,$album_id,$pg_title,$pg_label,$title,$sub_title,$issue_date,$amt,$color,$type,$cat) = @{$stamp_details->[0]};
                   if($sub_title) { $title = $title . br() . $sub_title }
                   if($child_dir) { $child_dir = $child_dir . '/' }
                   my $path_file = "$base_url/$dir/$album_id/$child_dir$file.page?highlight=$search_term";
      
                   print Tr(td(a({href=>$path_file}, $collect_name)), td(a({href=>$path_file}, $album_heading . br() . ' (' . $album_id . ', ' . $pg_label . ')')), td(a({href=>$path_file}, $cat)), td(a({href=>$path_file}, $title . br() . $pg_title)), td(a({href=>$path_file}, $amt)), td(a({href=>$path_file}, $type)), td(a({href=>$path_file}, $issue_date)), td(a({href=>$path_file}, $color))), $n;
                }
                print Tr(td({colspan=>'8'}, '&nbsp;')), $n,
                      end_table(), $n,$n;
             }
          }
          else { 
             print "No search term entered.", br({clear=>'all'}), $n;
             my @breaks = (1..40);
             foreach(@breaks){ print br() }   
          }
          
          print "<footer class='copyright'>", $n,
                div({id=>'footer_copyright'}, $n,
                span({class=>'mt-20 mb-4 text-sm text-gray-700 print:mt-4'}, '© Copyright 2022 & 2023, Russell J.T. Dyer. All Rights Reserved. '), $n), $n,
                '</footer>', $n,$n,
                end_div(), end_div(), $n, # end wy-nav-content & rst-content divs
                br({class=>'clear'}), br(), br(), '&nbsp;', $n,
                end_div(), $n, # end main div
                br({class=>'clear'}), '&nbsp;', $n,
                end_div(), $n,$n, # end page div
                end_html();

    exit;

sub connect_to_database {
    # connect to database
    my $db = 'philately';
    my $hst = 'localhost';
    my $usr = 'philatelist';
    my $pwd = 'Iceblink-Colonist-1102-Neuroma';

    my $dbh = DBI->connect(qq|DBI:mysql:$db;host=$hst;user=$usr;password=$pwd|,undef,undef, {RaiseError=>1})
              || die 'Could not connect to MariaDB: ' . DBI->errstr;

    return $dbh;
}
sub catalog_search { 
    # get stamp based on catalog number
    my ($dbh,$sc,$sa,$cat_spec) = @_;

    # determine which collections and albums to search
    my $collections = join ', ', map "'$_'", @$sc;
    my $albums = join ', ', map "'$_'", @$sa;

    my $sql_stmnt = qq|SELECT DISTINCT stamp_id
                       FROM stamps
                       JOIN pages USING(page_id)
                       JOIN albums USING(album_id)
                       LEFT JOIN series USING(series_id)
                       WHERE pg_status IN('pub','stk')
                       AND collection IN($collections)
                       AND album_id IN($albums) 
                       AND cat_nbr = ?|;
   my $sth = $dbh->prepare($sql_stmnt);
   $sth->execute($cat_spec); 
   my $stamp_found = $sth->fetchall_arrayref();
   $sth->finish();
   
   return $stamp_found;
}
sub pages_search { 
    # get lists of stamps
    my ($dbh,$sc,$sa,$search_term) = @_;
   
    # determine which collections and albums to search
    my $collections = join ', ', map "'$_'", @$sc;
    my $albums = join ', ', map "'$_'", @$sa;

    my $sql_stmnt = qq|SELECT page_id FROM
                       (SELECT page_id, SUM(rating) AS rate FROM 
                       (SELECT DISTINCT page_id, 1 AS rating
                       FROM pages
                       JOIN albums USING(album_id)
                       JOIN search_fields USING(page_id)
                       WHERE pg_status IN('pub','stk')
                       AND collection IN($collections)
                       AND album_id IN($albums)
                       AND (keywords REGEXP '\\\\b$search_term\\\\b')
                       UNION
                       SELECT DISTINCT page_id, 2 AS rating
                       FROM pages
                       JOIN albums USING(album_id)
                       JOIN search_fields USING(page_id)
                       WHERE pg_status IN('pub','stk')
                       AND collection IN($collections)
                       AND album_id IN($albums)
                       AND (text_copy REGEXP '\\\\b$search_term\\\\b')
                       UNION
                       SELECT DISTINCT page_id, 0.5 AS rating
                       FROM pages
                       JOIN albums USING(album_id)
                       JOIN search_fields USING(page_id)
                       WHERE pg_status IN('pub','stk')
                       AND collection IN($collections)
                       AND album_id IN($albums)
                       AND (page_title REGEXP '\\\\b$search_term\\\\b')
                       UNION
                       SELECT DISTINCT page_id, 0.5 AS rating
                       FROM pages
                       JOIN albums USING(album_id)
                       JOIN search_fields USING(page_id)
                       WHERE pg_status IN('pub','stk')
                       AND collection IN($collections)
                       AND album_id IN($albums)
                       AND (meta_desc REGEXP '\\\\b$search_term\\\\b')) AS derived1
                       GROUP BY page_id) AS derived2
                       WHERE rate > 0.75
                       ORDER BY rate DESC|;
   my $sth = $dbh->prepare($sql_stmnt);
   $sth->execute();
   my $pages_found = $sth->fetchall_arrayref();
   $sth->finish();

   return $pages_found;
}
sub page_check {
   # get lists of stamps
   my ($dbh,$page_id,$year_spec,$type_spec) = @_;

   my $sql_stmnt = qq|SELECT count(*)
                      FROM pages
                      JOIN stamps USING(page_id)
                      WHERE issue_year = ?
                      AND page_id = ?
                      GROUP BY page_id|; 
   my $sth = $dbh->prepare($sql_stmnt);
   $sth->execute($year_spec,$page_id);
   my ($pages_found_yr) = $sth->fetchrow() || 0;

   $sql_stmnt = qq|SELECT count(*)
                   FROM pages
                   JOIN stamps USING(page_id)
                   WHERE type_id = ?
                   AND page_id = ?
                   GROUP BY page_id|; 
   $sth = $dbh->prepare($sql_stmnt);
   $sth->execute($type_spec,$page_id);
   my ($pages_found_type) = $sth->fetchrow() || 0;

   my $pages_found = $pages_found_yr + $pages_found_type;
   $sth->finish();

   return $pages_found;
}
sub get_types {  
   # get lists of stamps
   my ($dbh,$page_id,$year_spec,$type_spec) = @_;

   my $sql_stmnt = qq|SELECT type_id, type_label 
                      FROM types
                      WHERE type_label != ''|; 
   my $sth = $dbh->prepare($sql_stmnt);
   $sth->execute();
   my $types = $sth->fetchall_arrayref();

   $sth->finish();

   return $types;
}
sub page_data { 
    # get lists of stamps
    my ($dbh,$page_id) = @_;

    my $sql_stmnt = qq|SELECT DISTINCT page_id, 
                       collection_dir, collection_name,
                       pages.album_id, file_prefix, album_heading, 
                       child_id, page_title, page_label, meta_desc
                       FROM pages
                       JOIN albums USING(album_id)
                       JOIN child_albums ON(parent_id = album_id)
                       JOIN search_fields USING(page_id)
                       JOIN collections USING(collection)
                       WHERE page_id = ?|; 
   my $sth = $dbh->prepare($sql_stmnt);
   $sth->execute($page_id);
   my $page_details = $sth->fetchall_arrayref();
   
   $sql_stmnt = qq|SELECT count(*)
                   FROM stamps
                   WHERE page_id = ?
                   GROUP BY page_id|; 
   $sth = $dbh->prepare($sql_stmnt);
   $sth->execute($page_id);
   my ($page_stamp_cnt) = $sth->fetchrow() || 0;

   $sth->finish();

   return ($page_details,$page_stamp_cnt);
}
sub stamps_search { 
    # get lists of stamps
    my ($dbh,$sc,$sa,$year_spec,$type_spec,$cat_spec,$search_term) = @_;

    # determine which collections and albums to search
    my $collections = join ', ', map "'$_'", @$sc;
    my $albums = join ', ', map "'$_'", @$sa;
    
    my $spec = '';
    if($year_spec) {
      $spec .= " AND issue_year = '$year_spec'";
    }
    if($type_spec ne 'aa') {
      $spec .= " AND type_id = '$type_spec'";
    }

    my $sql_stmnt = qq|SELECT stamp_id FROM
                         (SELECT stamp_id, SUM(rating) AS rate FROM 
                           (SELECT DISTINCT stamp_id, 2 AS rating
                             FROM stamps
                             JOIN pages USING(page_id)
                             JOIN albums USING(album_id)
                             LEFT JOIN series USING(series_id)
                             WHERE pg_status IN('pub','stk')
                             AND collection IN($collections)
                             AND album_id IN($albums) 
                             AND cat_nbr = '$cat_spec'
                           UNION
                             SELECT DISTINCT stamp_id, 1 AS rating
                             FROM stamps
                             JOIN pages USING(page_id)
                             JOIN albums USING(album_id)
                             LEFT JOIN series USING(series_id)
                             WHERE pg_status IN('pub','stk')
                             AND collection IN($collections)
                             AND album_id IN($albums) $spec
                             AND (title REGEXP '\\\\b$search_term\\\\b')
                           UNION
                             SELECT DISTINCT stamp_id, 0.5 AS rating
                             FROM stamps
                             JOIN pages USING(page_id)
                             JOIN albums USING(album_id)
                             LEFT JOIN series USING(series_id)
                             WHERE pg_status IN('pub','stk') 
                             AND collection IN($collections)
                             AND album_id IN($albums) $spec
                             AND (sub_title REGEXP '\\\\b$search_term\\\\b')
                           UNION
                             SELECT DISTINCT stamp_id, 0.5 AS rating
                             FROM stamps
                             JOIN pages USING(page_id)
                             JOIN albums USING(album_id)
                             LEFT JOIN series USING(series_id)
                             WHERE pg_status IN('pub','stk')
                             AND collection IN($collections)
                             AND album_id IN($albums)
                             AND (issue_year = '$year_spec')
                           UNION
                             SELECT DISTINCT stamp_id, 0.25 AS rating
                             FROM stamps
                             JOIN pages USING(page_id)
                             JOIN albums USING(album_id)
                             LEFT JOIN series USING(series_id)
                             WHERE pg_status IN('pub','stk')
                             AND collection IN($collections)
                             AND album_id IN($albums) $spec
                             AND (series.heading REGEXP '\\\\b$search_term\\\\b')) AS derived1
                         GROUP BY stamp_id) AS derived2
                       WHERE rate > 0
                       ORDER BY rate DESC|;
   my $sth = $dbh->prepare($sql_stmnt);
   $sth->execute(); 
   my $stamps_found = $sth->fetchall_arrayref();
   $sth->finish();
   
   return $stamps_found;
}
sub stamp_data { 
    # get lists of stamps
    my ($dbh,$stamp_id) = @_;

    my $sql_stmnt = qq|SELECT stamp_id, stamps.page_id, file_prefix, 
                       collection_dir,  collection_name,
                       album_heading, child_id, pages.album_id, 
                       page_title, page_label, title, sub_title, 
                       CONCAT(issue_month, SPACE(1), issue_year) AS issue_date, 
                       face_amt, colors, type, 
                       CONCAT(cat, '<br/>', cat_nbr)
                       FROM stamps
                       JOIN pages USING(page_id)
                       JOIN albums USING(album_id)
                       JOIN child_albums ON(parent_id = album_id)
                       JOIN collections USING(collection)
                       JOIN types USING(type_id)
                       WHERE stamp_id = ?|; 
   my $sth = $dbh->prepare($sql_stmnt);
   $sth->execute($stamp_id);
   my $stamp_details = $sth->fetchall_arrayref();
   $sth->finish();
   
   return $stamp_details;
}