#!/usr/bin/perl

#-----------------------------------------------------------------------
#	display_results.fcg
#
#		** MYSQL VERSION **
#		Fast CGI handler for displaying the search form submit and
#		handling the results of the search criteria.
#-----------------------------------------------------------------------
# Copyright (c) 2002 by Myxa Corporation. All rights reserved.
# RCSID: $Id: display_results.fcg,v 1.71 2006/04/29 00:50:14 syd Exp $
# $RCSfile: display_results.fcg,v $
# $Revision: 1.71 $
# $Author: syd $
# $Date: 2006/04/29 00:50:14 $
#-----------------------------------------------------------------------
use strict;

BEGIN {use CGI::Carp qw(fatalsToBrowser);}
use FindBin;
use lib "$FindBin::Bin/../lib";
use Localize;
use lib @Localize::LocalLibPaths;

use AppConfig qw/ :argcount /;
use AppConfig::File;
use Date::Manip qw/ UnixDate ParseDate/;
use PageNav qw/:custom :page_vars/;
use SearchLog qw(search_started search_ended);
use mysql;
use Display;
use FcgiLoop;
use Debug;
use Groups;
use Session;
use CGI_Startup;
use get_view_dates;

use vars qw/$style_img_path/;

Debug::trace('trace', 'display_results.fcg starting');

our %LINK_STYLE = (
	'text' => sub { 
			my ($cp, $lp) = @_; 
			PageNav::set_link_style(jump=>sub{qq(<font size="-2" color="666666">$cp of $lp</font>)});
			 },
	'text_var' => sub { 
			my ($cp, $lp, $b, $f, $l, $n) = @_; 
			PageNav::set_link_style(
				jump=>sub{qq(<font size="-2" color="666666">$cp of $lp</font>)},
				back => $b,
				next => $n,
				first=> $f,
				last => $l);
			 },
	'image' => sub { 
			my ($cp,$lp,$b,$f,$l,$n) = @_;
			PageNav::set_link_style(
				back =>qq(<img src="$style_img_path/$b" alt="Back" border="0">),
				next =>qq(<img src="$style_img_path/$n" alt="Next" border="0">),
				first=>qq(<img src="$style_img_path/$f" alt="First" border="0">),
				last =>qq(<img src="$style_img_path/$l" alt="Last" border="0">),
				jump =>qq(<font size="-2" color="666666">Page $cp of $lp</font>));
			},
	'default' => sub { 
			my ($cp, $lp) = @_;
			PageNav::set_link_style(
				back =>qq(<img src="$style_img_path/backlnk.gif" alt="Back" border="0">),
				next =>qq(<img src="$style_img_path/nextlnk.gif" alt="Next" border="0">),
				first=>qq(<img src="$style_img_path/firstlnk.gif" alt="First" border="0">),
				last =>qq(<img src="$style_img_path/lastlnk.gif" alt="Last" border="0">),
				jump =>qq(<font size="-2" color="666666">Page $cp of $lp</font>));
			},
	);

our @search_fields = qw /
	artist_or_title
	artist_title
	category_id
	conductor
	end_date
	featured
	org
	org_id
	perf
	region
	region_id
	series
	series_id
	season
	season_id
	start_date
	subcategory_id
	subscr
	ta
	title_or_composer
	type_id
	venue
	venue_id
/;

FcgiLoop
	{
	my $cgi = shift;
	Debug::fcg_dump($cgi, 'trace', "Entering main loop. Running as " . (getpwuid $>)[0] . ", group " . (getgrgid $))[0] . ".");

#
#	get existing session, if any
#
	my $input = get_session_id($cgi);

	my $context = $input->{context};
	my $ncontext = $input->{ncontext};
	my $id = $input->{ncontext_id};

	my $html_file = $input->{file};
	my $style = $input->{style};
	my $group = $input->{group};

	my @srch_vars = ();
	Debug::trace(trace => "Style = $style, group = $group");

	my @cfg_dirs;
	my $CONFIG = AppConfig->new({GLOBAL=>{ARGCOUNT => ARGCOUNT_ONE}}); 
	$CONFIG->define(qw(
		LINK_STYLE
		LINK_FIRST
		LINK_LAST
		LINK_BACK
		LINK_NEXT
		TEMPLATE_RESULT
		TEMPLATE_INFRAME
		TERM_START
		TERM_END
		STYLE_AVAILABLE_ICON
		STYLE_BUY_ICON
		STYLE_SOLDOUT_ICON
		STYLE_CANCELED_ICON
		STYLE_POSTPONED_ICON
		) );

	push @cfg_dirs, grep {-e $_} (
		"$Localize::cfg_dir/process_search.cfg",
		"$Localize::cfg_dir/$style/process_search.cfg",
		);
	$CONFIG->file(@cfg_dirs);
	#
	#	pre-load config style items
	#
	$CONFIG::Style_icons{available_icon} =  $CONFIG->STYLE_AVAILABLE_ICON();
	$CONFIG::Style_icons{buy_icon} =  $CONFIG->STYLE_BUY_ICON();
	$CONFIG::Style_icons{soldout_icon} =  $CONFIG->STYLE_SOLDOUT_ICON();
	$CONFIG::Style_icons{canceled_icon} =  $CONFIG->STYLE_CANCELED_ICON();
	$CONFIG::Style_icons{postponed_icon} =  $CONFIG->STYLE_POSTPONED_ICON();

	my $items;
	#
	# Sanity check
	#	items must be defined and an array, or its not proper
	#
	if ($context)
		{
		$items = $context->get('items');
		my $num = ref $items eq 'ARRAY' ? scalar(@$items) : 0;
		Debug::trace(trace => "\$context->get('items') has $num items in it.");
		}
	else
		{
		Debug::trace(trace => "no context, items count is 0");
		}

	#
	# If a pagenav variable exists, and $context->{items} exists,
	# then we've already done the search and are just paging around.
	#
	my $sql_query;
	unless ($input->{nav} &&  defined $items)
		{
		Debug::trace(trace => 'No pre-existing search!  Searching anew.');
	#
	#	stuff for logging the search
	#
		my $referer = $ENV{HTTP_REFERER};
		my $query   = $cgi->url(-query=>1);
		my $userip  = join ',', grep $_, @ENV{'REMOTE_HOST', 'REMOTE_ADDR'};
		my $sid;
		if ($input->{session})
			{
			$sid = $input->{session_id};
			}
		else
			{
			$sid = "none";
			}

		load_prior_search($input, $context);
		my $which = $input->{search} eq 'perfs'?  'perfs' : 'events';

		search_started("style=$style","EventDB: display_results($which)", "URL=$query;REFERER=$referer;IP=$userip;SID=$sid");

		$items = do_search($input, $ncontext, $which, \@srch_vars);
		Debug::trace(trace => "do_search[$which]:");
		Debug::trace(trace => "\$items: '$items'") if ref $items eq '';
		Debug::trace(trace => 'Num items: ' . scalar(@$items) ) if ref $items eq 'ARRAY';

		# END SEARCH LOGGING
		if ($items  &&  ref $items eq 'ARRAY'  &&  @$items > 0 )
			{
			my $sr;
			if ($which eq 'perfs')
				{
				$sr = 'p: ';
				foreach (@$items)
					{
					$sr .= $_->{org_id} . ':' . $_->{perf_id} . ',';
					}
				}
			else
				{
				$sr = 'e: ';
				foreach (@$items)
					{
					$sr .= $_->{org_id} . ':' . $_->{event_id} . ',';
					}
				}

			chop $sr;
			search_ended('ok', $sr);
			}
		else
			{
			search_ended('0', '');
			}
		}
	else
		{
		Debug::trace(trace => "Using existing search items.");
		if ($ncontext)
			{
			$ncontext->set('search_vars', $context->get('search_vars'));
			}
		}

	if ($ncontext)
		{
		$ncontext->set(items => $items);    # save!
		}

	# Set up page navigation
	PageNav::reset();
	PageNav::set_cgi_handler( $cgi->url(-absolute => 1) );

	my %pagenav_vars;
	PageNav::set_cgi_vars(id => $id);

	PageNav::set_nav_border(0);
	PageNav::set_nav_cellalign('center');
	PageNav::set_nav_cellvalign('middle');

	my $rows_per_page = $input->{itemsperpage} || 25;
	PageNav::set_page($items,$rows_per_page,$input->{nav});

	# Handle configuration items
	$style_img_path = Groups::img_path($style);
	if (exists $LINK_STYLE{$CONFIG->LINK_STYLE()})
		{
		if ($CONFIG->LINK_STYLE eq 'image' || $CONFIG->LINK_STYLE eq 'text_var')
			{
			$LINK_STYLE{$CONFIG->LINK_STYLE()} -> (
				$PageNav::cur_page,
				$PageNav::last_page,
				$CONFIG->LINK_BACK(),
				$CONFIG->LINK_FIRST(),
				$CONFIG->LINK_LAST(),
				$CONFIG->LINK_NEXT()
				);
			}
		else # text
			{
			$LINK_STYLE{$CONFIG->LINK_STYLE()}->($PageNav::cur_page, $PageNav::last_page);
			}
		}
	else
		{
		$LINK_STYLE{'default'}->();
		}

	# Get PageNav variables
	my $pn_vars = PageNav::get_pagenav();

	# store query string
	my $query = $cgi->self_url;
	$query =~ s/.*\?//;

#
#	pre template debug dump
#
	$context->dump(trace => "Context before display_template") if $context;
	$ncontext->dump(trace => "New Context before display_template") if $ncontext;

	my $search_vars;
	if ($ncontext)
		{
		$search_vars = $ncontext->get('search_vars');
		}

	#	Display the search result page with the results inserted.
	my %args = (
		%{$input},
		id              => $id,
		sid		=> $input->{session_id},
		myurl           => $cgi->url(),
		pn              => $pn_vars,
		items           => $items,
		curr_page_items => [ @$items[$pn_vars->{cur_top_index} .. $pn_vars->{cur_bot_index}] ],
		search_criteria => $query,
		search_vars 	=> $search_vars,
		document_name   => $ENV{DOCUMENT_NAME},
		host_uri	=> get_host_uri(),
		);

	# choose template to display
	my $template = $input->{file};
	$template ||= $CONFIG->TEMPLATE_INFRAME()  if $cgi->param('inframeset');
	$template ||= $CONFIG->TEMPLATE_RESULT ();

	if (exists $input->{cookies})
		{
		print $cgi->header(-cookie => $input->{cookies});
		}
	else
		{
		print $cgi->header;
		}
	display_template($template, \%args, $input);
	};


sub do_search
	{
	my $input = shift;	# hash ref of input values
	my $nctx = shift;	# new context to save search params
	my $search = shift;	# perfs or events
	my $srch_vars = shift;	# Search variable array to save what is being searched for

	Debug::trace(trace => "In do_search(input, nctx, $search, srch_vars)");

	my %search;    # User's search parameters
	$search{search} = $search;

	# --------------------
	# Gather Form Data:
	# --------------------
	# If there's a view associated with the query, adjust the start and end dates if necessary
	if ($input->{view})
		{
		# Compute new start/end dates for view period
		my $sd = $input->{start_date} || undef;
		my $ed = $input->{end_date}   || undef;
 		my ($nsd, $ned) = get_view_dates($input->{view}, $sd, $ed);

		$search{start_date} = $nsd;
		$search{end_date}   = $ned;

		$input->{start_date} = $nsd;
		$input->{end_date} =  $ned;

		# Remove prev_ or next_ from view variable
		my $new_view = $input->{view};
		$new_view =~ s/^(?: prev_ | next_ )//x;
		$input->{view} =  $new_view;
		}

	my $start_date = mysql::mysql_date( $input->{start_date} ||  'today' , 0);
	$input->{start_date} = $start_date;
	$search{start_date} = $start_date;

	my $end_date = $input->{end_date};
	if (defined $end_date)
		{
		$end_date = mysql::mysql_date($end_date), 0;
		$input->{end_date} = $end_date;
		$search{end_date} = $end_date;
		}

	foreach (qw(view start_date end_date))
		{
		if ($input->{$_})
			{
			push @$srch_vars, $_;
			}
		}


	# --------------------
	# Build SQL
	# --------------------

	# FROM and WHERE clauses
	my %from  = map {$_=>1} ('event ev', 'performance perf');
	my @where = ('ev.org_id=perf.org_id', 'ev.event_id=perf.event_id');

	# event type where clause
	if (my $parm = $input->{type_id})
		{
		my @type_ids = ref $parm eq 'ARRAY'? @$parm : ($parm);
		@type_ids = grep $_, @type_ids;
		if (@type_ids)
			{
			push @$srch_vars, 'type_id';
			$search{type_id} = \@type_ids;
			my $type_ids = join ',', @type_ids;
			push @where, "ev.type_id IN ($type_ids)";
			}
		}

	# category where clause
	if (my $parm = $input->{category_id})
		{
		my @cat_ids = ref $parm eq 'ARRAY'? @$parm : ($parm);
		@cat_ids = grep $_, @cat_ids;
		if (@cat_ids)
			{
			push @$srch_vars, 'category_id';
			$search{category_id} = \@cat_ids;
			my $cat_list = join ',', @cat_ids;
			$from{'organization org'} = 1;
			push @where, "( ev.category_id IN ($cat_list) OR (ev.category_id=0 AND ev.org_id=org.org_id AND org.category_id IN ($cat_list)) )";
			}
		}

	# subcategory where clause
	if (my $parm = $input->{subcategory_id})
		{
		my @subcats = ref $parm eq 'ARRAY'? @$parm : ($parm);
		@subcats = grep $_, @subcats;
		if (@subcats)
			{
			push @$srch_vars, 'subcategory_id';
			$search{subcategory_id} = \@subcats;
			my $subcats = join ',', @subcats;
			push @where, "ev.subcategory_id in ($subcats)";
			}
		}

	# Date range
	push @where, "perf.perf_end_date >= '$start_date'"  if $start_date;
	push @where, "perf.perf_date <= '$end_date'"    if $end_date;

	# Specific performance id?
	if (my $perf = $input->{perf})
		{
		push @$srch_vars, 'perf';
		$search{perf} = $perf;
		push @where, "perf.perf_id = $perf";
		}

	# region(s) where clause
	if (my $parm = $input->{region})
		{
		my @reg_ids = ref $parm eq 'ARRAY'? @$parm : ($parm);
		@reg_ids = grep $_, @reg_ids;
		if (@reg_ids)
			{
			push @$srch_vars, 'region';
			$search{region} = \@reg_ids;
			my $reg_list = join ',', @reg_ids;
			$from{'region_venue rv'} = 1;
			push @where, "perf.venue_id=rv.venue_id", "rv.region_id IN ($reg_list)";
			}
		}

	# region id where clause
	if (my $region_id = lc $input->{region_id})
		{
		push @$srch_vars, 'region_id';
		$search{region_id} = $region_id;
		$region_id =~ tr/\'%//d;
		$from{'region_venue rv'} = 1;
		push @where, "perf.venue_id=rv.venue_id", "rv.region_id = $region_id";
		}

	# organization where clause
	if (my $org = lc $input->{org})
		{
		push @$srch_vars, 'org';
		$search{org} = $org;
		$org =~ tr/\'%//d;
		$from{'organization org'} = 1;
		push @where, "org.org_id=ev.org_id", "LOWER(org.name) LIKE '%$org%'";
		}

	# organization id where clause
	if (my $org_id = lc $input->{org_id})
		{
		push @$srch_vars, 'org_id';
		$search{org_id} = $org_id;
		$org_id =~ tr/\'%//d;
		push @where, "ev.org_id=$org_id";
		}

	# group where clause - allow child groups
	if (my $grp = $input->{restrict_group})
		{
		push @$srch_vars, 'group';
		$search{group} = $grp;
		$grp =~ tr/\'//d;
		$from{'groups gr'} = 1;
		$from{'group_org go'} = 1;
		push @where, "ev.org_id=go.org_id", "go.group_id=gr.group_id", "gr.name='$grp'";
		}
	elsif (my $rgrp = $input->{group})
		{
		push @$srch_vars, 'group';
		$search{group} = $rgrp;
		$rgrp =~ tr/\'//d;
		$from{'groups gr'} = 1;
		$from{'group_org go'} = 1;
		push @where, "ev.org_id=go.org_id", "go.group_id=gr.group_id", "gr.name='$rgrp'";
		}

	# series where clause
	if (my $series = lc $input->{series})
		{
		push @$srch_vars, 'series';
		$search{series} = $series;
		$series =~ tr/\'%//d;
		$from{'series ser'} = 1;
		my ($id, $code) = split('\|', $series);
		push @where, "ser.series_id=ev.series_id", "ser.org_id=$id", "LOWER(ser.description) LIKE '%$code%'";
		}

	# series_id where clause
	if (my $sid = $input->{series_id})
		{
		push @$srch_vars, 'series_id';
		$search{series_id} = $sid;
		my ($id, $code) = split('\|', $sid);
		push @where, "ev.org_id=$id", "ev.series_id=$code";
		}

	# season where clause
	if (my $season = lc $input->{season})
		{
		push @$srch_vars, 'season';
		$search{season} = $season;
		$season =~ tr/\'%//d;
		$from{'season sea'} = 1;
		my ($id, $code) = split('\|', $season);
		push @where, "sea.season_id=ev.season_id", "sea.org_id=$id", "LOWER(sea.description) LIKE '%$code%'";
		}

	# season_id where clause
	if (my $sid = $input->{season_id})
		{
		push @$srch_vars, 'season_id';
		$search{season_id} = $sid;
		my ($id, $code) = split('\|', $sid);
		push @where, "ev.org_id=$id", "ev.season_id=$code";
		}

	# venue where clause
	if (my $venue = lc $input->{venue})
		{
		push @$srch_vars, 'venue';
		$search{venue} = $venue;
		push @where, "perf.venue_id=$venue"  unless $venue eq 'any';
		}

	# venue_id where clause
	if (my $venue_id = lc $input->{venue_id})
		{
		push @$srch_vars, 'venue_id';
		$search{venue_id} = $venue_id;
		push @where, "perf.venue_id=$venue_id"  unless $venue_id eq 'any';
		}

	# featured where clause
	if (my $featured = lc $input->{featured})
		{
		push @$srch_vars, 'featured';
		$search{featured} = $featured;
		$featured =~ tr/\'%//d;
		$from{'artist a1'} = 1;
		$from{'artist_event ae1'} = 1;
		push @where, "ae1.org_id=ev.org_id", "ae1.event_id=ev.event_id",
			"ae1.org_id = a1.org_id", "ae1.artist_id=a1.artist_id",
			"LOWER(a1.name) LIKE '%$featured%'";
		}

	# conductor where clause
	if (my $conductor = lc $input->{conductor})
		{
		push @$srch_vars, 'conductor';
		$search{conductor} = $conductor;
		$conductor =~ tr/\'%//d;
		$from{'artist a2'} = 1;
		$from{'artist_event ae2'} = 1;
		push @where, "ae2.org_id=ev.org_id", "ae2.event_id=ev.event_id",
			"ae2.org_id = a2.org_id", "ae2.artist_id=a2.artist_id",
			"lower(a2.name) LIKE '%$conductor%'", "lower(a2.title)='conductor'";
		}

	# "artist title" where clause
	if (my $artist_title = lc $input->{artist_title})
		{
		push @$srch_vars, 'artist_title';
		$search{artist_title} = $artist_title;
		$artist_title =~ tr/\'%//d;
		$from{'artist a3'} = 1;
		$from{'artist_event ae3'} = 1;
		push @where, "ae3.org_id=ev.org_id", "ae3.event_id=ev.event_id",
			"ae3.org_id = a3.org_id", "ae3.artist_id=a3.artist_id",
			"a3.title LIKE '%$artist_title%'";
		}

	# "artist name or title" where clause
	if (my $artist_or_title = lc $input->{artist_or_title})
		{
		push @$srch_vars, 'artist_or_title';
		$search{artist_or_title} = $artist_or_title;
		$from{'artist a4'} = 1;
		$from{'artist_event ae4'} = 1;
		push @where, "ae4.org_id=ev.org_id", "ae4.event_id=ev.event_id",
			"ae4.org_id = a4.org_id", "ae4.artist_id=a4.artist_id",
			"(a4.name LIKE '%$artist_or_title%' OR a4.title LIKE '%$artist_or_title%')";
		}

	# title or composer where clause
	if (my $torc = lc $input->{title_or_composer})
		{
		push @$srch_vars, 'title_or_composer';
		$search{title_or_composer} = $torc;
		$torc =~ tr/\'%//d;
		$from{'program pgm'} = 1;
		push @where, "pgm.org_id=ev.org_id", "pgm.event_id=ev.event_id", "(LOWER(pgm.title) LIKE '%$torc%' OR lower(pgm.composer) LIKE '%$torc%' OR ev.title LIKE '%$torc%' )";
		}

	# subscription where clause
	if (my $subscr = $input->{subscr})
		{
		push @$srch_vars, 'subscr';
		$search{subscr} = $subscr;
		$from{'subscription sub'} = 1;
		$from{'subscr_perf sp'}   = 1;
		my ($id, $code) = split('\|', $subscr);
		push @where, "sub.code='$code'",
			"sub.org_id=$id",
			"perf.org_id=sp.org_id",
			"perf.perf_id=sp.perf_id",
			"sp.org_id=sub.org_id",
			"sp.pkg_id=sub.pkg_id";
		}

	# ticket agent where clause
	if (my $tckt_agent = $input->{ta})
		{
		push @$srch_vars, 'ta';
		$search{ta} = $tckt_agent;
		push @where, "ev.agent_id = $tckt_agent";
		}

	# Restrict to published events
	push @where, 'ev.published >= 3';

	# Save input search parameters
	if ($nctx)
		{
		$nctx->set(search_vars => \%search);
		}

	# Put it all together
	my $from  = join ', ', sort keys %from;
	my $where = join ' AND ', @where;

	my ($sql, $field);
	if ($search eq 'events')
		{
		$sql = "SELECT DISTINCT ev.org_id, ev.event_id FROM $from WHERE $where ORDER BY perf.perf_date, perf.perf_time, perf.perf_end_date, perf.perf_end_time, ev.org_id, ev.event_id";
		$field = 'event_id';
		}
	else
		{
		$sql = "SELECT DISTINCT perf.org_id, perf.perf_id FROM $from WHERE $where ORDER BY perf.perf_date, perf.perf_time, perf.perf_end_date, perf.perf_end_time, perf.org_id, perf.perf_id";
		$field = 'perf_id';
		}

	# --------------------
	# Process SQL results
	# --------------------
	if (mysql::multiple_select($sql))
		{
		my @items;
		my %found;
		foreach (@$dbi::data_ref)
			{
#
#			there is a bug in mysql where the distinct select here doesn't work
#			we need to also filter it ourselves
#
			my $key = $_->[0] . '|' . $_->[1];
			next if $found{$key};
			$found{$key}++;
#
#			end of work around
#
			my %rec;
			$rec{org_id} = $_->[0];
			$rec{$field} = $_->[1];
			push @items, \%rec;
			}
		return \@items;
		}
	return undef;
	}

sub load_prior_search
	{
	my $input = shift;  # ref of input values
	my $ctx = shift;    # Context object

	Debug::trace(trace => "In load_prior_search($input, $ctx)");

	return unless $ctx;
	my $search = $ctx->get('search_vars');
	my $found = 0;
#
#	look for any prior input values in input that could be in search
#	if none found, set input to search from session
#
	foreach (@search_fields)
		{
		if ($input->{$_})
			{
			Debug::trace(trace => "input{$_} = '" . $input->{$_} . "'");
			$found = 1;
			last;
			}
		}

	Debug::trace(trace => "found = $found");
	return if $found;

#
#	load up the input with the found search parameters
#
	foreach (keys %{$search})
		{
		my $old = $input->{$_};
		$input->{$_} = $search->{$_};
		Debug::trace(trace => "Restoring $_ from '$old' to " . $input->{$_});
		}
	}