#!/usr/bin/perl 

## move result table header and footer out of this file
## add abillity to sort by title in ascending or desc  
	# just check for a last state and do the opposite
## add specialty rollover (bilingual)
## change everything to hash with ex: ->{TITLE_"some variable"} for language stuff
# remove titles from script and place in external file

use strict;
use CGI qw/:standard :all :cgi-lib/;
use CGI::Carp qw(fatalsToBrowser); 
use Data::Dumper;
#use constant PRIVATE_DIR => "/usr/www/users/fwdserv/CPSNB/t_sfm";
#use constant PRIVATE_DIR => "/usr/home/fwdserv/med_search";
use constant PRIVATE_DIR => "/var/www/vhosts/vagranthosting.com/httpdocs/dev/cpsnb_joomla/med_search";
use constant CONFIG_FILE => PRIVATE_DIR."/config/CONFIG.txt";

use lib PRIVATE_DIR."/Tie/Hash";
use Regex;
use lib PRIVATE_DIR."/lib";
use Html;
use DBFunctions;
use vars qw($CONFIG_FILE);
$CONFIG_FILE = CONFIG_FILE;
use Error qw($CONFIG_FILE);


use constant CHARSET => ("AÀÁÂÃÄÅÆaàáâãäåæ", "CÇcç", "EÈÉÊËeèéêë", "IÌÍÎÏiìíîï", 
			 "NÑnñ", "OÒÓÔÕÖØoòóôõöø",  "UÙÚÛÜuùúûü",  "YÝyý");

sub main {

  my $rparams = Vars;
  my $html_obj = new Html(config_file	=> CONFIG_FILE,
  		  	  params	=> $rparams);
  #print header();foreach (sort keys %{$rparams}) { print $_." == ".$rparams->{$_}."<BR>"; }
  #print "<Hr>".$html_obj->get_Params->{specialty}."<hr>";
  #$html_obj->get_ConfigData->{HITS_PER_PAGE} = 100;

  # fetch column titles #
  my %column_titles = fetchColumnTitles(); 

  if (keys %{$rparams}) { 
    my $query_params = buildQuery(html_obj => $html_obj);

    #print header(); print $query_params; # testing

    # get the count value #
    my ($num, $data) = DBFunctions::query(statement => "select count(*) from ".
						       $html_obj->get_ConfigData->{DB_TABLE_NAME}.
						       $query_params,
				             %{$html_obj->get_ConfigData});
    my $count = @{$data}[0]->{'count(*)'};

    my $footer = $html_obj->parseHtml(gui => ($html_obj->get_Params->{lang} eq "fr") ? 
					      $html_obj->get_ConfigData->{FOOTER_FR_GUI} :
					      $html_obj->get_ConfigData->{FOOTER_EN_GUI});
    # no results if count = 0 #
    if ($count < 1) { 
      my $gui = $html_obj->get_ConfigData->{NO_RESULT_EN_GUI};
      $gui = $html_obj->get_ConfigData->{NO_RESULT_FR_GUI}
    	if ($html_obj->get_Params->{lang} eq "fr");
      my $query_data = fetchQueryString(txt_file => $html_obj->get_ConfigData->{PARAM_LIST_TXT},
                                                  %{$html_obj->get_Params});
      delete($query_data->{lang});
      print header(),
	  $html_obj->parseHtml(gui => $html_obj->get_ConfigData->{HEADER_GUI},
			     TITLE => $html_obj->get_ConfigData->{"TITLE_".$html_obj->get_Params->{lang}},
		      HEADER_IMAGE => $html_obj->get_ConfigData->{"HEADER_IMAGE_".$html_obj->get_Params->{lang}}),
	  $html_obj->parseHtml(gui => $gui,
                      query_string => join("&", map { $_."=".$query_data->{$_} } keys %{$query_data})),
	  $footer,
          end_html;
      exit;
    }

    # round down any limit to the next multiple of HITS_PER_PAGE #
    # if a user tries to modify the limit value, this should fix the problem #
    $html_obj->get_Params->{limit} = 0 if ($html_obj->get_Params->{limit} < 1);
    $html_obj->get_Params->{limit} = $count if ($html_obj->get_Params->{limit} > $count);
    $html_obj->get_Params->{limit} -= ($html_obj->get_Params->{limit} % $html_obj->get_ConfigData->{HITS_PER_PAGE});
    
    # calc next limit #
    my $limit = 0;
    if ($html_obj->get_Params->{submit} eq $html_obj->get_ConfigData->{BACK_BUTTON_EN} ||
        $html_obj->get_Params->{submit} eq $html_obj->get_ConfigData->{BACK_BUTTON_FR}) {
	$limit = $html_obj->get_Params->{limit} - $html_obj->get_ConfigData->{HITS_PER_PAGE};
    } elsif ($html_obj->get_Params->{submit} eq $html_obj->get_ConfigData->{NEXT_BUTTON_EN} ||
             $html_obj->get_Params->{submit} eq $html_obj->get_ConfigData->{NEXT_BUTTON_FR}) {
	$limit = $html_obj->get_Params->{limit} + $html_obj->get_ConfigData->{HITS_PER_PAGE};
    } else {
	$limit = $html_obj->get_Params->{limit}; 
    }
    # don't allow drop below zero # 
    $limit = 0 if ($limit <= 0);

    # don't allow queries for more than the data which exists #
    # this removes the final page appearing with no data #
    $limit -= $html_obj->get_ConfigData->{HITS_PER_PAGE} if ($limit >= $count);

    # update the limit param value with the new limit value incase it is used later #
    $html_obj->get_Params->{limit} = $limit;

    my $last_displayed = $html_obj->get_Params->{limit} + $html_obj->get_ConfigData->{HITS_PER_PAGE}; 
       $last_displayed = $count if ($last_displayed > $count);

    my $for_download = 0;
    if ($html_obj->get_Params->{submit} eq $html_obj->get_ConfigData->{DOWNLOAD_BUTTON}) { $for_download = 1; }
    # fetch the data # 
    my $stmt = "select * from ".$html_obj->get_ConfigData->{DB_TABLE_NAME}.$query_params;
       $stmt .= " limit ".($limit||0).",".$html_obj->get_ConfigData->{HITS_PER_PAGE} unless $for_download;

    my ($num, $data) = DBFunctions::query(statement => $stmt,
	                    	          %{$html_obj->get_ConfigData}); 

    my $result_table = createResultTable(html_obj => $html_obj,
    				     for_download => $for_download,
	    				     from => ($limit + 1),
	      				       to => $last_displayed,
	      				       of => $count,
   				    column_titles => \%column_titles,
            			   	     data => $data);

    ## for excel download ##
    if ($for_download) {
      #print header(-type=>"text/excel", '-Content-disposition'=>"inline; filename=\"db.xls\"");
      print header(-type=>"application/vnd.ms-excel; charset=utf-8", '-Content-disposition'=>"attachment; filename=\"db.csv\"");
	# this didnt' work: $result_table = '<head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <meta charset="utf-8" /> <meta http-equiv="content-type" content="application/vnd.ms-excel; charset=UTF-8">
        $result_table =~ s/\<br\>/ /ig;

	# clean up download  and convert to csv
        $result_table =~ s{<\!--.*?-->|<a.*?>|</a>|<th>|<td.*?>|<table.*?>|<tr.*?>|</tr>|</table>}{}ig;
	# escape any existing commas
        $result_table =~ s{,}{}ig;
        $result_table =~ s{(</th>|</td>)\n}{,}ig;
        $result_table =~ s{(</th>|</td>)\n}{,}ig;
        $result_table =~ s/^\s+|\s+$//g;
	$result_table =~ s/^\s*\n//mg;
        $result_table =~ s/,+$//gm;
        $result_table =~ s/,\s\s/,/g;
        $result_table =~ s/\&nbsp;//g;

	#$result_table =~ s/\d\d//ig;
      print $result_table;
      #print @{$data};
      exit;
    }

    my $scroll = createScroll(html_obj => $html_obj, 
				 count => $count) 
		 unless ( $count <= $html_obj->get_ConfigData->{HITS_PER_PAGE});

    my $nav = $html_obj->parseHtml(gui => $html_obj->get_ConfigData->{NAV_GUI}, 
			 HIDDEN_PARAMS => join("",@{fetchHiddenParams(html_obj => $html_obj)}),  
			        SCROLL => ($scroll||""),
			   BACK_BUTTON => ($html_obj->get_Params->{lang} eq "fr") ? 
						$html_obj->get_ConfigData->{BACK_BUTTON_FR} :
						$html_obj->get_ConfigData->{BACK_BUTTON_EN},
			   NEXT_BUTTON => ($html_obj->get_Params->{lang} eq "fr") ? 
						$html_obj->get_ConfigData->{NEXT_BUTTON_FR} :
						$html_obj->get_ConfigData->{NEXT_BUTTON_EN},
				  ) 
		 unless ( $count <= $html_obj->get_ConfigData->{HITS_PER_PAGE});

    print header(),#$html_obj->get_Params->{submit},$html_obj->get_ConfigData->{DOWNLOAD_BUTTON},
	  $html_obj->parseHtml(gui => $html_obj->get_ConfigData->{HEADER_GUI},
			     TITLE => $html_obj->get_ConfigData->{"TITLE_".$html_obj->get_Params->{lang}},
		      HEADER_IMAGE => $html_obj->get_ConfigData->{"HEADER_IMAGE_".$html_obj->get_Params->{lang}}),
	  $html_obj->parseHtml(gui => $html_obj->get_ConfigData->{RESULT_GUI},
			   CONTENT => $result_table.$nav),
	  $footer,
          end_html;

#foreach (keys %{$html_obj->get_Params}) { print $_." == ".$html_obj->get_Params->{$_}."<BR>"; }

    exit;
  }
#$# NO SUBMISSION #$#
  print redirect($html_obj->get_ConfigData->{DEFAULT_MAIN});
} main();

sub fetchColumnTitles {
	return (
	     en => { 
                      t_region => "Region",
		        t_first=> "First Name",
		        t_last => "Last Name",
                     t_address1 => "Address 1",
                     t_address2 => "Address 2",
                        t_city => "City",
                        t_prov => "Province",
                 t_postal_code => "Postal Code",
 	           t_specialty => "Specialty",
	   	     t_license => "Lic#", 
                      t_school => "Medical School",
                   t_grad_year => "Grad Year",
                   t_telephone => "Tel#",
                         t_fax => "Fax#",
                       t_email => "Email Address",
                    t_atlantic => "Atlantic Registry",
               t_home_province => "Home Province",
			  t_id => "id",
		   },
	     fr => { 
                      t_region => "R&eacute;gion",
		        t_first => "Pr&eacute;nom",
		        t_last => "Nom de Famille",
                     t_address1 => "Adress 1",
                     t_address2 => "Adress 2",
                        t_city => "Ville",
                        t_prov => "Province",
                 t_postal_code => "Code Postal",
 	           t_specialty => "Sp&eacute;cialit&eacute;",
	   	     t_license => "Permis", 
                      t_school => "&Eacute;cole M&eacute;dicale",
                   t_grad_year => "Ann&eacute;e de rep&eacute;re",
                   t_telephone => "T&eacute;l",
                         t_fax => "Fax",
                       t_email => "Courriel",
                    t_atlantic => "la Registre de l'Atlantique",
               t_home_province => "Province d'origine",
			  t_id => "id",
		   },
	);
}

# display results #
sub createResultTable {
    my %args = @_;

    # language switch #
    my $lang_gui = $args{html_obj}->get_ConfigData->{LANG_EN_GUI};
    $lang_gui = $args{html_obj}->get_ConfigData->{LANG_FR_GUI}
    	if ($args{html_obj}->get_Params->{lang} eq "fr");

    my $query_data = fetchQueryString(txt_file => $args{html_obj}->get_ConfigData->{PARAM_LIST_TXT},
	       			 	        %{$args{html_obj}->get_Params}); 
    delete($query_data->{lang}); 
    my $table = $args{html_obj}->parseHtml(gui => $lang_gui,
				  query_string => join("&", map { $_."=".$query_data->{$_} } keys %{$query_data}),
	       				       %{$args{html_obj}->get_Params},
					       %args); 

    $table = "" if ($args{for_download});
 
    # move this to a gui #
    $table .= "<table border='0' align='center' class='table' cellpadding='1' cellspacing='0' width='95%'>";
    
    # column titles #
    my $lang = "en"; $lang = "fr" if ($args{html_obj}->get_Params->{lang} eq "fr");

    my $query_data = fetchQueryString(txt_file => $args{html_obj}->get_ConfigData->{PARAM_LIST_TXT},
  	         			       %{$args{html_obj}->get_Params}); 
    delete($query_data->{sort}); 
    $table .= $args{html_obj}->parseHtml(gui => $args{html_obj}->get_ConfigData->{TITLES_GUI}, 
			        query_string => join("&", map { $_."=".$query_data->{$_} } keys %{$query_data}),
	     				     %{$args{column_titles}->{$lang}});
    # fill in the blanks #
    foreach my $hash (@{$args{data}}) {
      map { s/^\s*$/&nbsp;/g } values %{$hash};
	$hash->{"license"} =~ s/^.*?-/&nbsp;/gi if ($args{for_download});
      $table .= $args{html_obj}->parseHtml(gui => $args{html_obj}->get_ConfigData->{RECORD_GUI},
					          %{$hash});
    }
    return $table."</table>";
}

sub createScroll {
    my %args = @_;

    my $scroll = "";
    my $start = ($args{html_obj}->get_Params->{limit} - ($args{html_obj}->get_ConfigData->{HITS_PER_PAGE}
              * (int(sprintf("%d",$args{html_obj}->get_ConfigData->{HITS_PER_PAGE}/2)))));

    # if result total less than hits**2, then don't dial
    $start = 0 if ($start < 1);
    $start = 0 if ($args{html_obj}->get_ConfigData->{HITS_PER_PAGE}**2 > $args{count});

    my $end = ($args{html_obj}->get_Params->{limit} + ($args{html_obj}->get_ConfigData->{HITS_PER_PAGE}
            * (int(sprintf("%d",$args{html_obj}->get_ConfigData->{HITS_PER_PAGE}/2)))));

    $end = ($args{html_obj}->get_ConfigData->{HITS_PER_PAGE}**2) 
	if ($end < $args{html_obj}->get_ConfigData->{HITS_PER_PAGE}**2);

    for (my $i = $start;$i < $args{count};$i += $args{html_obj}->get_ConfigData->{HITS_PER_PAGE}) {

      # don't display results futher than $end #
      # this value is used for the dial #
      last if ($i >= $end); 
      
      # calc current iter #
      my $iter = int(sprintf("%d", $i / $args{html_obj}->get_ConfigData->{HITS_PER_PAGE})) + 1;

      # create scroll button (numbered links) - unlink the current page #
      my $gui = $args{html_obj}->get_ConfigData->{SCROLL_GUI}; 
      $gui = $args{html_obj}->get_ConfigData->{CURRENT_GUI} 
	if ($iter eq (($args{html_obj}->get_Params->{limit} + $args{html_obj}->get_ConfigData->{HITS_PER_PAGE})
							    / $args{html_obj}->get_ConfigData->{HITS_PER_PAGE}));

      # create final link from gui #	
      $scroll .= "&nbsp;|&nbsp;" if $scroll;

      my $query_data = fetchQueryString(txt_file => $args{html_obj}->get_ConfigData->{PARAM_LIST_TXT},
	       			 	          %{$args{html_obj}->get_Params}); 
      delete($query_data->{limit}); 
      $scroll .= $args{html_obj}->parseHtml(gui => $gui,
				   query_string => join("&", map { $_."=".$query_data->{$_} } keys %{$query_data}),
                                                %{$args{html_obj}->get_Params},
				          limit => $i, 
				        DISPLAY => $iter);	
    }
    return $scroll;
}

sub fetchQueryString {
  my %args = @_; 
  my %params = ();
  open (FH, "<".$args{txt_file}) 
	or die "Could not open txt file ".$args{txt_file}.". $!.";
  while (<FH>) {
    chomp;
    $params{$_} = $args{$_}; 
  }
  return \%params;
}



sub fetchHiddenParams {
  my %args = @_; 
  my @params = ();
  open (FH, "<".$args{html_obj}->get_ConfigData->{PARAM_LIST_TXT}) 
	or die "Could not open txt file ".$args{html_obj}->get_ConfigData->{FIELD_LIST_TXT}.". $!.";
  while (<FH>) {
    chomp;
    push @params, $args{html_obj}->parseHtml(gui => $args{html_obj}->get_ConfigData->{HIDDEN_GUI},
					    name => $_,
					   value => $args{html_obj}->get_Params->{$_});
  }
  return \@params;
} 

# need alpha lookup table #
sub charLookup {
  my $char = shift;

  ## regex doesn't work well with perl meta chars, so we simply return ##
  my $charset = join("",CHARSET);
  return $char if (($char=~/\W/) && ($charset!~/\Q$char\E/)); 

  my %charset;
  tie %charset, 'Tie::Hash::Regex';
  %charset = map { $_ => $_ } CHARSET;
  return $charset{$char} || (uc($char).lc($char));
}

sub buildQuery {
  my %args = @_;

  my $query = "";
  # search by name #
  if (my $name = $args{html_obj}->get_Params->{'last name'}) {
    ## split name on blanks
    # remove slashes - also does unquotemeta #
    $name=~s{\/|\\}{}sg;
    $name=~s/[\,\.]//g; # remove commas and periods  
    $name = $args{html_obj}->stripLTspace($name); 
    $name =~ s/\s{2,}/ /g;

    #foreach my $val (split " ", $name) {
      if ($query) { $query.=" and " } else { $query.=" where " }

      # convert each character to its 'or' equivalent #
      $name=~s/(.)/"[".charLookup($1)."]"/eg;

      #$query .= "first_name regexp \".*".$val.".*\""; $query .= " or "; 
      $query .= "last_name regexp \".*".$name.".*\"";
    #}
  } 

  if (my $name = $args{html_obj}->get_Params->{'first name'}) {
    ## split name on blanks
    # remove slashes - also does unquotemeta #
    $name=~s{\/|\\}{}sg;
    $name=~s/[\,\.]//g; # remove commas and periods 
    $name = $args{html_obj}->stripLTspace($name);
    $name =~ s/\s{2,}/ /g;  

    #foreach my $val (split " ", $name) {         
      if ($query) { $query.=" and " } else { $query.=" where " }
    
      # convert each character to its 'or' equivalent #
      $name=~s/(.)/"[".charLookup($1)."]"/eg;
                                                                     
      #$query .= "first_name regexp \".*".$val.".*\""; $query .= " or "; 
      $query .= "first_name regexp \".*".$name.".*\""; 
    #}      
  }
  # search by region (multiple) #
  if (my $region = $args{html_obj}->get_Params->{region}) {
    if ($query) { $query.=" and " } else { $query.=" where " }
    $query .= "(".join(" or ", map{ "region = \"".$_."\"" } split(", ",$region)).")";
  } 
  # search by city #
  if (my $city = $args{html_obj}->get_Params->{city}) {
    if ($query) { $query.=" and " } else { $query.=" where " }
    $query .= "city like \"%".$city."%\"";
  } 
  # search by specialty (multiple) #
  if (my $specialty = $args{html_obj}->get_Params->{specialty}) {
    if ($query) { $query.=" and " } else { $query.=" where " }
    #$query .= "(".join(" or ", map{ "specialty like \"%".$_."%\"" } split(", ",$specialty)).")";
    #$query .= "(".join(" or ", map{ "specialty like \"".(($_!~/^\s*$/)?"$_%":())."\"" } split(", ",$specialty)).")";

    $query .= "(".join(" or ", map{ "specialty like \"".(($_!~/^\s*$/)?"%$_%":())."\"" } split(", ",$specialty));

    # this is specifically for Family Physicians where you can have a special license but no specialty designation with the record
    if ($specialty =~ /^,/) { $query .= " or specialty = \"(SP)\"  or specialty = \"(BA)\"  or specialty = \"(CL)\""; }

   $query .= ")";

  } 
  # search by metro (multiple) #
  if (my $metro = $args{html_obj}->get_Params->{metro}) {
    if ($query) { $query.=" and " } else { $query.=" where " }
    $query .= "(".join(" or ", map{ "city like \"%".$_."%\"" } split(", ",$metro)).")";
  } 
  # sort by #
  if (my $sort = $args{html_obj}->get_Params->{sort}) { 
    $query .= " order by ".$sort; 
  } 
  return $query; 
}