#!/usr/bin/perl
# ==================================================================
# Gossamer Links - enhanced directory management system
#
#   Website  : http://rgbworld.com/
#   Support  : http://rgbworld.com/
#   CVS Info : 087,064,081,086,081      
#   Revision : $Id: contact.cgi,v 1.0 2005/24/01 12:00:00 jagerman Exp $
#
# Copyright (c) 2005 RGB World Inc.  All Rights Reserved.
# Redistribution in part or in whole strictly prohibited. Please
# see LICENSE file for full details.
# ==================================================================

    use strict;
    use lib '';
    use Links qw/$IN $DB $CFG/;
    use Links::Plugins;
    use Links::SiteHTML;
	use Links::Build;
    use CGI::Carp qw(fatalsToBrowser);

    local $SIG{__DIE__}  = \&Links::fatal;	

    Links::init();

my $pi = atan2(1,1) * 4;
my $time_hires;

# ------------------------------------------------------------------
# Determine what to do.
#
    &handle;

sub handle {
#--------------------------------------------------------------------------------
# Determine whether we are displaying the zipcode search form, or doing a search.
#
    my $args    = $IN->get_hash;

# Make sure we only search on validated links.
    $IN->param('isValidated', 'Yes');
    $IN->param('ExpiryDate', '>=' . time) if $CFG->{payment}->{enabled};

# If query is set we know we are searching.
    return search() if defined $args->{query} and $args->{query} =~ /\S/;

    print $IN->header();
    print Links::SiteHTML::display('zipcode_search', { main_title_loop => Links::Build::build('title', Links::language('ZIP_SEARCH'), "$CFG->{db_cgi_url}/zipcode_search.cgi") });
}

sub search {
# ------------------------------------------------------------------
# Do the search and print out the results.
#
    my $results = query();

    if (defined $results->{error}) {
        print $IN->header();
        $results->{main_title_loop} = Links::Build::build('title', Links::language('ZIP_SEARCH'), "$CFG->{db_cgi_url}/zipcode_search.cgi");
        print Links::SiteHTML::display('zipcode_search', $results);
    }
    else {
        print $IN->header();
        $results->{main_title_loop} = Links::Build::build('title', Links::language('ZIP_SEARCH_RESULTS'), "$CFG->{db_cgi_url}/zipcode_search.cgi");
        print Links::SiteHTML::display('zipcode_search_results', $results);
    }
    if ($CFG->{debug_level} > 1) {
        print "<blockquote><pre>", GT::SQL->query_stack_disp , "</pre></blockquote>";
    }
}

sub query {
# -----------------------------------------------------------------------------
# The main event
# This is *NOT* a hook
#
	# grab the plugin settings...
	my $opts = Links::Plugins->get_plugin_user_cfg('ZipCodeSearch');

	# First get our search options.
    my $args = $IN->get_hash;
    $args->{query} =~ s/^\s+//;
    $args->{query} =~ s/\s+$//;
    $args->{nh}   = (defined $args->{nh}   and $args->{nh}   =~ /^(\d+)$/)   ? $1    : 1;
    $args->{mh}   = (defined $args->{mh}   and $args->{mh}   =~ /^\d+$/) ? $args->{mh} : $opts->{'maxhits'};
    $args->{mh}   = 100 if $args->{mh} > 100; # Safety limit
    $args->{so}   = (defined $args->{so}   and $args->{so}   =~ /^(ASC|DESC)$/i) ? $args->{so} : $opts->{'sort_order'};
    $args->{sb}   = (defined $args->{sb}   and $args->{sb}   =~ /^[\w\s,]+$/) ? $args->{sb} : $opts->{'sort_by'};

	# Get our Links/Category db object.
    my $links_db       = $DB->table('Links');
    my $categories_db  = $DB->table('Category');

    my $query  = $IN->param('query');
	# Need to limit query length to be 10 digits (53142-9610) if country = US?
	substr($query, 10) = '' if length $query > 10;

    if($IN->param('dist') > $opts->{'maxdist'}) {
		$IN->param('dist', $opts->{'maxdist'});
    }
    my $dist  = $IN->param('dist');
    
	# units is the visible text, i.e. ['Mile','Kilometer','Nautical-Mile']. See Install.pm file.
	# unitcode is a one letter code representing units. M, K, N respectively.
    my $units  = $opts->{units};
    my $unitcode  = $units;
    substr($unitcode, 1) = '';

	# I use config variables for ALL COLUMN NAMES AND 'ZipCodes' TABLE NAME
	# Get the starting Zip Code we are searching for
	my $zipcode_db  = $DB->table($opts->{'src_table'}) || return $GT::SQL::error; 
    my $osth = $zipcode_db->select({ $opts->{'src_zipcode_col'} => $query })->fetchrow_hashref; 

	# Return if no match for starting zip code. It's not in the database
	if(! $osth) {
		return { error => Links::language('ZIP_MISSING', $query), term => $query };
	}

	# If we made it this far, grab Latitude and Longitude for starting zip
	my $olat = $osth->{$opts->{'src_latitude_col'}};
	my $olon = $osth->{$opts->{'src_longitude_col'}};
	
	# Get the category id if there is one
	my $catid = $IN->param('catid');
	my $catname = 'Links';

	my $link_count = 0;
    my @link_results_loop;
	my @ids;

    my $started;
    if (length $query and $opts->{logging} and $args->{nh} == 1) {
        if (!defined $time_hires) {
            $time_hires = eval { require Time::HiRes } || 0;
        }
        $started = $time_hires ? Time::HiRes::time() : time;
    }

	my $link_sth;
	my @zips;

	if ($catid)
	{
		# This is just the short category name for display on template results
		$catname  = $categories_db->select( ['Name'], { ID => $catid } )->fetchrow;  
	
		# We want all links in catid and below. Found at GT forums under <%load_lower_links($ID)%>
		my $catname_full  = $categories_db->select( ['Full_Name'], { ID => $catid } )->fetchrow;  
		my $cond = GT::SQL::Condition->new(  
			Full_Name => '=' => "$catname_full",  
			Full_Name => 'LIKE' => "$catname_full/%"  
		);  
		$cond->bool('OR');  
		my $sth = $categories_db->select( $cond ) || die $GT::SQL::error;  
	
		my $count = 0;
		my $temp_distance;
		
		while (my $hit = $sth->fetchrow_hashref) {  
			my $links_sth = $DB->table('CatLinks')->select( { CategoryID =>$hit->{ID} } ) || die $GT::SQL::error;  
	
			while (my $hit2 = $links_sth->fetchrow_hashref) {   
	
				my $link = $links_db->get($hit2->{LinkID});  
				
				# Get Current Links Zip Code from Main ZipCode DB
				my $osth = $links_db->select({ $opts->{'dest_zipcode_col'} => $link->{$opts->{'dest_zipcode_col'}} })->fetchrow_hashref; 
				my $currlat = $osth->{$opts->{'dest_latitude_col'}};
				my $currlon = $osth->{$opts->{'dest_longitude_col'}};
	
				# The actual distance search
				$temp_distance = distance($olat, $olon, $currlat, $currlon, $unitcode);
	
				if($temp_distance <= $dist)
				{
					$link_count++;
            		$link = Links::SiteHTML::tags('link', $link); 
            		push (@link_results_loop, $link); 
				}
			}  
		}
	}
	else
	{
		my $dest_zipcode_col = $opts->{'dest_zipcode_col'};
		my $dest_latitude_col = $opts->{'dest_latitude_col'};
		my $dest_longitude_col = $opts->{'dest_longitude_col'};
		my $sort_order = 'ORDER BY Distance ';
		$sort_order .= $args->{so};
		$sort_order .= ',';
		$sort_order .= $args->{sb};
		
		my $code = "SELECT DISTINCT o.ID, o.Title, o.Add_Date, (3956 * (2 * ASIN(SQRT( POWER(SIN(((z.DEST_LAT_COL-o.DEST_LAT_COL)*0.017453293)/2),2) + COS(z.DEST_LAT_COL*0.017453293) * COS(o.DEST_LAT_COL*0.017453293) * POWER(SIN(((z.DEST_LONG_COL-o.DEST_LONG_COL)*0.017453293)/2),2) )))) AS Distance FROM lsql_Links z, lsql_Links o, lsql_Links a WHERE z.DEST_ZIP_COL = ? AND z.DEST_ZIP_COL = a.DEST_ZIP_COL AND (3956 * (2 * ASIN(SQRT( POWER(SIN(((z.DEST_LAT_COL-o.DEST_LAT_COL)*0.017453293)/2),2) + COS(z.DEST_LAT_COL*0.017453293) * COS(o.DEST_LAT_COL*0.017453293) * POWER(SIN(((z.DEST_LONG_COL-o.DEST_LONG_COL)*0.017453293)/2),2) )))) <= ? SORT_ORDER";
		$code =~ s/DEST_ZIP_COL/$dest_zipcode_col/g; 
		$code =~ s/DEST_LAT_COL/$dest_latitude_col/g; 
		$code =~ s/DEST_LONG_COL/$dest_longitude_col/g; 
		$code =~ s/SORT_ORDER/$sort_order/; 

		my $offset   = ($args->{nh} - 1) * $args->{mh};
		my $sth = $links_db->prepare($code);
		$sth->execute ($query, $dist);

		while (my $link = $sth->fetchrow_hashref)
		{
			if($link_count >= $offset and ( $link_count < ($offset + $args->{mh})) ) {
            	$link = Links::SiteHTML::tags('link', $link); 
            	push (@link_results_loop, $link);
            }
			$link_count++;
		}
	}

#########################
# Log the search if it's a new query
    if (length $query and $opts->{logging} and $args->{nh} == 1) {
        my $elapsed = ($time_hires ? Time::HiRes::time() : time) - $started;
        my $results = $link_count || 0;
        my $sl = $DB->table('SearchLogs');

#        my $q = lc $query;
my $q = $catname;
$q .= ' in ';
if ($dist > 0) {
	$q .= $dist;
	$q .= $unitcode;
	$q .= ' of ';
}
$q .= $query;

        substr($q, 255) = '' if length $q > 255;
        if (my $row = $sl->select({ slog_query => $q })->fetchrow_hashref) {
            my $slog_time = defined $row->{slog_time}
                ? ($row->{slog_time} * $row->{slog_count} + $elapsed) / ($row->{slog_count} + 1)
                : $elapsed;
            $sl->update({
                slog_count => $row->{slog_count} + 1,
                slog_time  => sprintf('%.6f', $slog_time),
                slog_last  => time,
                slog_hits  => $results
            }, {
                slog_query => $q
            });
        }
        else {
            $sl->insert({
                slog_query => $q,
                slog_count => 1,
                slog_time  => sprintf('%.6f', $elapsed),
                slog_last  => time,
                slog_hits  => $results
            }) or die "$GT::SQL::error";
        }
    }

# Return if no results.
    unless ($link_count) {
		return { error => Links::language('ZIP_NO_RESULTS', $query), term => $query };
	}


# Generate a toolbar if requested.
    my ($toolbar, %paging);
    if ( $link_count > $args->{mh} ) {
        my $url = $CFG->{db_cgi_url} . "/" . $IN->url;
        $url =~ s/([;&?]?)nh=(\d+)/($1 and $1 eq '?') ? '?' : ''/eg;
        $toolbar = Links::Build::build(search_toolbar => {
            url => $url,
            numlinks => $link_count,
            nh => $args->{nh},
            mh => $args->{mh}
        });
        %paging = (
            url          => $url,
            num_hits     => $link_count,
            max_hits     => $args->{mh},
            current_page => $args->{nh}
        );
    }
    else {
        $toolbar = '';
    }


#########################

# Print the output.
    my $results = {
        link_results_loop     => \@link_results_loop,
        link_hits             => $link_count,
        next                  => $toolbar,
        paging                => \%paging,
        term                  => $query,
        highlight             => 0,
        units                 => $units,
        dist                  => $dist,
        catname				  => $catname
    };

#print $IN->header();
#require GT::Dumper;
#print GT::Dumper::Dumper(\@zips);

    return $results;
}


sub matching_zips {
# -----------------------------------------------------------------------------
#
#
	my ($query, $unit, $dist) = @_;
	my @output;

	my $zipcode_db  = $DB->table('ZipCodes') || return $GT::SQL::error; 
    my $osth = $zipcode_db->select({ ZipCode => $query })->fetchrow_hashref; 

	if($osth)
	{
		my $olat = $osth->{Latitude};
		my $olon = $osth->{Longitude};
		my $sth = $zipcode_db->select;
	
		while (my $link = $sth->fetchrow_hashref)
		{
			my $temp_distance = distance($olat, $olon, $link->{Latitude}, $link->{Longitude}, $unit);
			
			if( $temp_distance <= $dist)
			{
				$link->{Distance} = $temp_distance;
				push (@output, $link->{ZipCode});
			}
		}
	}
	
	return @output;

}

sub matching_zips2 {
# -----------------------------------------------------------------------------
#
#
	my ($query, $unit, $dist) = @_;
	my @output;

	my $zipcode_db  = $DB->table('ZipCodes') || return $GT::SQL::error; 
    my $osth = $zipcode_db->select({ ZipCode => $query })->fetchrow_hashref; 

	if($osth)
	{
		my $olat = $osth->{Latitude};
		my $olon = $osth->{Longitude};
		my $sth = $zipcode_db->select;
	
		while (my $link = $sth->fetchrow_hashref)
		{
			my $temp_distance = distance($olat, $olon, $link->{Latitude}, $link->{Longitude}, $unit);
			
			if( $temp_distance <= $dist)
			{
				$link->{Distance} = $temp_distance;
				push (@output, $link->{ZipCode});
			}
		}
	}
	
	return @output;

}


sub matching_zipsORIG {
# -----------------------------------------------------------------------------
#
#
	my ($query, $unit, $dist) = @_;

	my $where;
	my $zipcode_db  = $DB->table('ZipCodes') || return $GT::SQL::error; 
    my $osth = $zipcode_db->select({ ZipCode => $query })->fetchrow_hashref; 

if($osth)
{
	my $olat = $osth->{Latitude};
	my $olon = $osth->{Longitude};

	my $sth = $zipcode_db->select;
	my @output;

	while (my $link = $sth->fetchrow_hashref)
	{
		if(distance($olat, $olon, $link->{Latitude}, $link->{Longitude}, $unit) <= $dist)
		{
			push (@output, $link->{ZipCode});
		}
	}
	
	$where = "['";
	$where .= join("', '", grep(/\S/,@output)) if @output;
	$where .= "']";
}

return $where;

}

sub distance {
# -----------------------------------------------------------------------------
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
#:::                                                                         :::
#:::  This routine calculates the distance between two points (given the     :::
#:::  latitude/longitude of those points). It is being used to calculate     :::
#:::  the distance between two ZIP Codes or Postal Codes using our           :::
#:::  ZIPCodeWorld(TM) and PostalCodeWorld(TM) products.                     :::
#:::                                                                         :::
#:::  Definitions:                                                           :::
#:::    South latitudes are negative, east longitudes are positive           :::
#:::                                                                         :::
#:::  Passed to function:                                                    :::
#:::    lat1, lon1 = Latitude and Longitude of point 1 (in decimal degrees)  :::
#:::    lat2, lon2 = Latitude and Longitude of point 2 (in decimal degrees)  :::
#:::    unit = the unit you desire for results                               :::
#:::           where: 'M' is statute miles                                   :::
#:::                  'K' is kilometers (default)                            :::
#:::                  'N' is nautical miles                                  :::
#:::                                                                         :::
#:::  United States ZIP Code/ Canadian Postal Code databases with latitude   :::
#:::  & longitude are available at http://www.zipcodeworld.com               :::
#:::                                                                         :::
#:::  For enquiries, please contact sales@zipcodeworld.com                   :::
#:::                                                                         :::
#:::  Official Web site: http://www.zipcodeworld.com                         :::
#:::                                                                         :::
#:::  Hexa Software Development Center © All Rights Reserved 2004            :::
#:::                                                                         :::
#:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::

# I moved $pi to top and had to add 'my'
# $pi = atan2(1,1) * 4;

	my ($lat1, $lon1, $lat2, $lon2, $unit) = @_;
	my $theta = $lon1 - $lon2;
	my $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) + cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
  $dist  = acos($dist);
  $dist = rad2deg($dist);
  $dist = $dist * 60 * 1.1515;
  if ($unit eq "K") {
  	$dist = $dist * 1.609344;
  } elsif ($unit eq "N") {
  	$dist = $dist * 0.8684;
		}
	return ($dist);
}

#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
#:::  This function get the arccos function using arctan function   :::
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
sub acos {
	my ($rad) = @_;
	my $ret = atan2(sqrt(1 - $rad**2), $rad);
	return $ret;
}

#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
#:::  This function converts decimal degrees to radians             :::
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
sub deg2rad {
	my ($deg) = @_;
	return ($deg * $pi / 180);
}

#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
#:::  This function converts radians to decimal degrees             :::
#::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
sub rad2deg {
	my ($rad) = @_;
	return ($rad * 180 / $pi);
}