#!/usr/bin/perl

################################################################################
################################################################################
#####          This code tells the browser what MIME type the content is.  #####
#####          All content delivered by this script is HTML                #####
                            #####
################################################################################
################################################################################

# start SiteMyzer user configuration
################################################################################
################################################################################
# the mysql database table name that this script will use for the products table.
$Feature_Name = "Events Calendar";
$Feature_Short_Name = "Calendar";
$Feature_SearchLink_Name = "Events";

$Data_Table="events";
$item_number_field = "Event_ID";
$item_name_field = "Event_Name";
$item_active_field = "Active";
$item_active_true = "TRUE";
$item_category_field = "Category";
$item_subcategory_field = "Country";
$has_thirdCat = "FALSE";
$item_thirdcategory_field="State";
$item_details_field="Description"; ############### change here!
$item_orderby_field="Start_Date";
$item_orderby_order="DESC";

# back ground color for products table
$bgcolor="#ffffff";

$error_mail_link = $my_Business_email;

$selfURL = $ENV{SCRIPT_NAME};
#$selfURL = "/cgi-bin/store.pl";
my $CalCMD = "/usr/bin/cal";

use CGI::Carp qw(fatalsToBrowser);
#comment out the above line when you're done debugging this script

$|++;	#dont't buffer output

# find out the OS
if ($^O eq 'MSWin32' || $ENV{'OS'} eq 'Windows_NT') {
      # this one for windows
      $windows = 'Yes';
      $slash = '\\';
}
else
{
      $windows = 'No';
      $slash = '/';
}

#load required perl modules or libraries
#my $dbh;
my $db_params = "../cgi-bin/wsdbi_params.pm";
my $db_common = "../cgi-bin/admin/params.pl";

eval {
        #use strict;
        use DBI;
        require $db_params;
        require $db_common;
};
#check for load errors
if ($@) {
        &my_load_error_doc($@);
        exit(0);
}

print "Content-type: text/html \n\n\r\n\r\n\r" unless ($content_type_printed);
$content_type_printed++;
# connect to DB, read CGI data, verify Login,
SITe_ConnectToDB();
my_read_CGI_input();


##### define date/time variables
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$mon = $mon + 1;
$year = $year + 1900;
$accessTime = "$hour:$min:$sec";
$todaysDate = "$year-$mon-$mday";
$AN = int(rand(10000));

my $Days = "Sun Mon Tue Wed Thr Fri Sat";
my $DaysL = "S M T W T F S";
my @DayNames = split/ /, "Sunday Monday Tuesday Wednesday Thursday Friday Saturday";
my @MonthNames = split/ /, "NaM January February March April May June July August September October November December";
my @MonthNumbers = split/ /, "00 01 02 03 04 05 06 07 08 09 10 11 12";
my @DayNumbers = split/ /, "00 01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31";


if (defined $data{include}) {process_include(); exit(0);}

if    ($data{cmd} eq "Sign-Off")    { Log_Out();             }
else
{
    Main_HTML_Doc();
}

SITe_DisConnectFromDB();

#End of main perl code

sub Main_HTML_Doc(){
    $main_content_title = $Feature_Name;
    $search_link = qq~<A href="$selfURL?cmd=SearchForm"><FONT style="font-size:8pt;">Search</FONT></A>~;
    if ($data{cmd} eq "")
    {
        if($data{$item_category_field} eq "")
        {
        	$more_content = "";
        	$main_content = &Search_my_DB_Records($Data_Table,"");
            $left_content = Active_Left_Menu_Links();  $left_content_title = $Feature_Short_Name;
        	$page_title_text = "$my_Business_name $Feature_Name";
        }
        else
        {
            $more_content = "";
        	$main_content = &Search_my_DB_Records($Data_Table,"");
            $left_content = Active_Left_Menu_Links();   $left_content_title = $Feature_Short_Name;
        	$page_title_text = "$data{$item_category_field} - $my_Business_name $Feature_Short_Name";
        }
    }
    else
    {
        #$more_content = &Search_my_DB_Records($Data_Table) if($data{cmd} eq "Search");
        $main_content = &Search_my_DB_Records($Data_Table) if($data{cmd} eq "Search"); #Default Content
        $left_content = Active_Left_Menu_Links();  $left_content_title = $Feature_Short_Name;
	    if(($data{cmd} eq "SearchForm") || ($data{cmd} eq "Search"))
        {
            $main_content = &Search_my_DB_Records($Data_Table) if($data{cmd} eq "Search");
            $main_content .= Search_my_DB_Form();
            $left_content = Dir_Search(); $left_content_title = "Advanced Search";
            $page_title_text = "Advanced Search: $my_Business_name $Feature_Short_Name";
            $search_link = qq~~;
        }

        if ($data{cmd} eq "ShowEvent")
        {
            $main_content = Show_event_listing();
        }

        if ($data{cmd} eq "ListDate")
        {
            $main_content = query_events_for_date();
        }

	    $left_content = Dir_Help() if($data{cmd} eq "Help");
        $left_content_title = "Help" if($data{cmd} eq "Help");
    }

    print "Content-type: text/html \n\n\r\n\r\n\r" unless ($content_type_printed);
    $content_type_printed++;

	do "../cgi-bin/main_html_doc.pl";

}

sub my_load_error_doc(){
    my($item) = shift;
    my $server = ($ENV{HTTP_HOST}) ? $ENV{HTTP_HOST}:'localhost';
    my $REQUEST_URI = $ENV{'SCRIPT_NAME'} ? $ENV{'SCRIPT_NAME'}:$0;
    $item =~ s/\]/\]<br>/g;
    $item =~ s/\(/<br>\(/g;
    $item =~ s/\)/\)<br>/g;
    $item =~ s/\bat\b/<br>at/g;
    my $error =  qq~Error loading required perl modules or libraries.
    <br>Check that the required perl modules or libraries exist and are
    located in the correct directory.
    <br>Reason:<br>$item
    ~;

    print qq~
       <HTML>
    <HEAD>
    <TITLE>Script Error</TITLE>
    </HEAD>

    <BODY BGCOLOR='#FFFFFF'>
    <CENTER>
    <TABLE border='0' cellpadding='0' cellspacing='1' width='98%' BGCOLOR='#990000'><TR><TD>
        <TABLE BORDER=0 WIDTH="100%" CELLSPACING=0 BGCOLOR='#ffffff' cellpadding=3>
              <TR>
                  <TD align="center" BGCOLOR='#990000'>
                      <TABLE><TR><TD>
                              <FONT color='#FFFFFF' face="Verdana,Arial,Helvetica" size=3>
                              <B>CGI Module Error</B></FONT>
                          </TD></TR>
                      </TABLE>
                  </TD>
	          </TR>
	          <TR>
                  <TD bgcolor="#ffeeee">
                      <FONT color='#000000' face="Verdana,Arial,Helvetica" size=3>
                          <P><B>The requested URL</B><BR>http://$server$REQUEST_URI</p>
                          <P><B>returned this error:</B><BR>
                     </FONT>
                     <FONT color='#000000' face="Verdana,Arial,Helvetica" size=2>$error </FONT></P>
                     <P>
                  </TD>
	          </TR>
	          <TR>
                  <TD BGCOLOR='#990000'>
                     <DIV align="center">
                       <FONT color='#ffffff' size=1 face="Verdana,Arial,Helvetica">
                               <B>SITe Myzer for Unix/Linux</B>
                       </FONT>
                   </DIV>
                  </TD>
	          </TR>
        </TABLE>
    </TD></TR></TABLE><BR>
    <P>
    </CENTER>
    </BODY></HTML>
       ~;
    exit(0);
}


sub Active_Subcategory_Links(){
	my $html = "";  @my_unique_SubCats = ();
    return if ($data{$item_subcategory_field} eq "");

	my $dbquery = qq~ SELECT * FROM $Data_Table
    WHERE $item_category_field LIKE "$data{$item_subcategory_field}" AND
    $item_active_field LIKE "$item_active_true"
    ORDER BY $item_subcategory_field
    ~;
    my $sth = $dbh -> prepare($dbquery) or print "Query syntax error. $dbquery. Reason: $DBI::errstr.";
	if( not $sth -> execute())
	{
	    print "<p>Can't execute query:<br> $dbquery.<p> Reason: $DBI::errstr.";
	    $sth -> finish;
	    SITe_DisConnectFromDB();
	    exit;
	}
    while(my $sku_ref=$sth->fetchrow_hashref())
	{
        $entry_found = 0;
        foreach $this_Brand (@my_unique_SubCats)
        {
        	if ($this_Brand eq $sku_ref->{$item_subcategory_field})
            {
            	$entry_found = 1;
            }
        }
        if ($entry_found == 0)
        {
        	push(@my_unique_Brands,$sku_ref->{$item_subcategory_field});
			#push(@my_unique_Brand_link_alt,$sku_ref->{$item_subcategory_field});
        }
	}
    $sth->finish();

	$i=0;
    foreach $this_Brand (@my_unique_SubCats)
    {
        unless ($this_Brand eq "")
        {
	        #if (($data{Brand} eq $this_Brand))
	        #{
	        #    $this_link = qq~<DIV  align='left' class="LeftMenuLinkDiv" >&nbsp;&nbsp;&nbsp;&nbsp;<B>$this_Brand</B></DIV>\n~;
	        #}
	        #else
	        #{
	            $this_link = qq~<DIV  align='left' class="LeftMenuLinkDiv" >&nbsp;&nbsp;&nbsp;&nbsp;<A class="Left" href="$my_WS_HTTP_URL$selfURL?Brand=$this_Brand&AN=$AN" target='_self' title="@my_unique_Brand_link_alt[$i]">$this_Brand</A></DIV>\n~;
	        #}
	        push(@Brand_links,$this_link);
        }
        $i++;
    }


    $html = qq~
        @Brand_links
    ~;

    return $html;

}

sub Active_Category_Links(){
	my $html = ""; @my_unique_categories=();

	my $dbquery = qq~SELECT $item_category_field, $item_active_field
    	FROM $Data_Table
    	WHERE $item_active_field LIKE "$item_active_true"
        ORDER BY $item_category_field
    ~;

    my $sth = $dbh -> prepare($dbquery) or print "Query syntax error. $dbquery. Reason: $DBI::errstr.";
	if( not $sth -> execute())
	{
	    print "<p>Can't execute query:<br> $dbquery.<p> Reason: $DBI::errstr.";
	    $sth -> finish;
	    SITe_DisConnectFromDB();
	    exit;
	}
    while(my $sku_ref=$sth->fetchrow_hashref())
	{
        $entry_found = 0;
        foreach $this_category (@my_unique_categories)
        {
        	if ($this_category eq $sku_ref->{$item_category_field})
            {
            	$entry_found = 1;
            }
        }
        if ($entry_found == 0)
        {
        	push(@my_unique_categories,$sku_ref->{$item_category_field});
			push(@my_unique_cat_link_alt,$sku_ref->{$item_category_field});
        }
	}
    $count_of_active_records = $sth->rows();
    $sth->finish();

	$i=0;
    foreach $this_category (@my_unique_categories)
    {
        unless ($this_category eq "")
        {
	        $this_link = qq~<DIV  align='left' class="LeftMenuLinkDiv" >&nbsp;<A class="Left" href="$my_WS_HTTP_URL$selfURL?$item_category_field=$this_category&AN=$AN" target='_self' title="$this_category">$this_category</A></DIV>\n~;
	        push(@category_links,$this_link);
        }
        $i++;
    }

    $html = qq~
        @category_links
    ~;

    return $html;
}

sub Active_Left_Menu_Links(){
	my $html = ""; my @Left_Menu_Links = ();

    my $Category_Links = Active_Category_Links();
    #my $SubCat_Links   = Active_Subcategory_Links();
    #my $ThirdCat_Links = Active_Thirdcategory_Links();


    $i=0;
    my $categoriesFound = scalar(@my_unique_categories);
    foreach my $this_category (@my_unique_categories)
    {
        #unless ($data{$item_category_field} eq "")
        #{
            if (($data{$item_category_field} eq $this_category))
	        {
                $this_link = qq~<DIV  align='left' class="LeftMenuLinkDiv" >&nbsp;<A class="Left" href="$my_WS_HTTP_URL$selfURL?$item_category_field=$this_category&AN=$AN"><B>$this_category</B></A></DIV>\n~;
                push(@Left_Menu_Links,$this_link);
                my $bi=0;
                my $subCatsFound = scalar(@my_unique_SubCats);
	            foreach my $this_SubCat (@my_unique_SubCats)
	            {
	                if (($data{$item_subcategory_field} ne "")  && ($data{$item_subcategory_field} eq $this_SubCat))
	                {
	                    push(@Left_Menu_Links,qq~<DIV  align='left' class="LeftMenuLinkDiv" >&nbsp;&nbsp;-&nbsp;<B>$this_SubCat</B></DIV>\n~);
                        if ($has_thirdCat eq "TRUE")
                        {
	                        my $mi=0;
	                        foreach my $this_thirdCat (@my_unique_thirdCats)
	                        {
	                            push(@Left_Menu_Links,qq~<DIV  align='left' class="LeftMenuLinkDiv">&nbsp;&nbsp;&nbsp;=== [&nbsp;<A class="Left" href="$my_WS_HTTP_URL$selfURL?$item_category_field=$this_category&$item_subcategory_field=$this_SubCat&$item_thirdcategory_field=$this_SubCat&AN=$AN" target='_self' title="@my_unique_Model_link_alt[$i]">$this_Model</A> ]</DIV>\n~);
	                            $mi++;
	                        }
            			}
	                }
	                else
	                {
	                    push(@Left_Menu_Links,qq~<DIV  align='left' class="LeftMenuLinkDiv" >&nbsp;&nbsp;-&nbsp;<A class="Left" href="$my_WS_HTTP_URL$selfURL?$item_category_field=$this_category&$item_subcategory_field=$this_SubCat&AN=$AN" target='_self' title="$this_SubCat">$this_SubCat</A></DIV>\n~);
	                }
	                $bi++;
	            }
	        }
            else
            {
            	$this_link = qq~<DIV  align='left' class="LeftMenuLinkDiv" >&nbsp;<A class="Left" href="$my_WS_HTTP_URL$selfURL?$item_category_field=$this_category&AN=$AN" target='_self' title="$this_category">$this_category</A></DIV>\n~;
                push(@Left_Menu_Links,$this_link);
            }
        #}
        $i++;
    }
    my $html2 = &render_event_calendar(1);
    $html = qq~
        <DIV align="center" style="color:#000000; font-size:8pt; margin:4px;">
             Browse by clicking links located in the calendar or list below, or in the listings right.
        </DIV>
        $html2<BR>
    	@Left_Menu_Links

    ~;

    return $html;
}



sub get_field_names(){
	my $dbtable = shift;
    my @fields = ();
    if ($dbtable eq "")
    {
             &show_error("Can't execute query:<br> DESC.<p><B>Reason:</B><BR>Unknown DB table name: $dbtable");
             &DisConnectFromDB;
             exit(0);
    }
    my $sth = $dbh->prepare("DESC $dbtable");
    if( not $sth -> execute())
    {
            &show_error("Can't execute query:<br> $dbquery.<p><B>Reason:</B><BR>$DBI::errstr.");
            &DisConnectFromDB;
            exit(0);
    }

    my $i = 0;
    while( my $row = $sth->fetchrow_hashref )
    {
            push(@fields,$row->{Field});
            $i++;
    }
    $sth->finish;
    $DB_field_count = $i;
    return @fields;
}


sub Dir_Help(){
	my $help_content;
    $help_content = ""; #Default Content
    return $help_content;
}

sub Dir_Search(){
	my $help_content;
    $help_content = qq~

        <DIV align="left" style="color:#000000; font-size:10pt; margin:4px;">
             <BR><B><I>Follow these steps:</I></B><BR><BR>
             <B>1)</B> Type search text for Field A<BR>
             <B>2)</B> Select Field A from the drop-menu<BR>
             <B>3)</B> Repeat 1 &amp; 2 for Field B if desired<BR>
             <B>5)</B> Choose Field A Sort Order<BR>
             <B>6)</B> Click [Search]

        </DIV>
    ~;
    return $help_content;
}

sub Help_Html(){
	my $help_content;
    $help_content = Default_Content(); #Default Content

	$help_content = Dir_Help() if($data{option} eq "Help");

}



sub query_count_events_for_date(){
    my $searchDate = shift;
	my $dbquery = qq~SELECT $item_number_field FROM $Data_Table
    WHERE $item_active_field = "$item_active_true" AND
    Start_Date = "$searchDate"
    ~;
    my $sth = $dbh -> prepare($dbquery) or print "Query syntax error. $dbquery. Reason: $DBI::errstr.";
    if( not $sth -> execute())
    {
        print "<p>Can't execute query:<br> $dbquery.<p> Reason: $DBI::errstr.";
        $sth -> finish;
        SITe_DisConnectFromDB();
        exit;
    }
    my $rows=$sth->rows();
    $sth->finish();
    return $rows;
}

sub Search_my_DB_Form(){
    my @dbfields = &get_field_names($Data_Table);
    my @seacrhfield_option_list=();
    foreach my $field (@dbfields)
    {
        my $field_name = $field; $field_name =~ s/_/ /g;
        push(@seacrhfield_option_list,qq~<OPTION value="$field">$field_name</OPTION>~);
    }
   # my $sort1 = get field names as option list;
    my $html = qq~
        <FORM name="SearchForm" action="$selfURL" method="POST">
	        <TABLE align="center">
                <TR >
	                <TD colspan="2" align="center">
                    	<H4>Advanced Search Form</H4>
                        <DIV align="center" style="color:#ff0000; font-size:10pt; margin:4px;">
	                         Use the form below to perform an advanced search of our<BR>
	                         $Feature_Short_Name database.
	                    </DIV>
	                    <HR>
	                </TD>
	            </TR>
	            <TR>
	                <TD align="right" valign="top" >
	                    <DIV style="font-family:Verdana,Arial,Helvetica; font-size:10pt">

	                        <input type="hidden"  name="login"     value="$LOGIN">
	                        <INPUT type="hidden"  name="AN"        value="$AN">
	                        <input type="hidden"  name="dbtable"   value="$data{dbtable}">
	                        <INPUT type="hidden"  name="Table"     value="$data{Table}">
	                        <INPUT type="hidden"  name="cmd"       value="Search">
	                        <INPUT type="hidden"  name="action"    value="Search">
                            <DIV align="center"><B>Field A:</B></DIV>
	                        <input type="text"    name="keywords1" value="$data{keywords1}" size="20"> and
                            <DIV align="center">
	                            <select name="fieldsort1">
                                    <option value="" selected>Search field A</option>
                                    @seacrhfield_option_list
	                            </select>
                            </DIV>
	                    </DIV>
	                </td>
	                <td align="left" valign="top" >
	                    <DIV style="font-family:Verdana,Arial,Helvetica; font-size:10pt">
                             <DIV align="center"><B>Field B:</B></DIV>
                             --&gt; <input type="text"    name="keywords2" value="$data{keywords2}" size="20">
                             <DIV align="center">
	                             <select name="fieldsort2">
                                     <option value="" selected>Search field B</option>
                                     @seacrhfield_option_list
	                             </select>
                             </DIV>
	                    </DIV>
	                </TD>
                    <TR>
	                    <TD align="center"><BR>
                            <DIV style="font-family:Verdana,Arial,Helvetica; font-size:10pt">
	                            <B>Sort Order:</B><BR>
	                            &nbsp;Desc <input type="radio" name="sort_order" value="desc" checked>
	                            &nbsp;&nbsp;Asc<input type="radio" name="sort_order" value="asc"><BR><BR>

	                        </DIV>
	                    </TD>
                        <TD align="center">
                            <!--<BR>
                        	<B>SQL query: </B><BR>$data{user_query}<BR>
                            <INPUT type="text" name="user_query" value='' size="70">
                            -->
                            <input type="submit" value="Search" name="searchBTN" onClick="">
						</TD>
	                </TR>
	            </TR>
	        </TABLE>
        </FORM>
    ~;


    return $html;
}


sub Search_my_DB_Records(){
    my $dbtable = shift;
    my $user_query = "";
    $user_query = shift;
    $user_query = $data{user_query} if ((defined $data{user_query}) && ($data{user_query} ne ""));

    my $second_table_bgcolor = "";
    my $report_header_color = "gold";
    ##### The first row color (HTML) used for admin search results.
    my $row_color1 = "#ffffff";
    #The second row color (HTML) used for admin search results.
    my $row_color2 = "#F5F5F5";

    my ($offset, $LIMIT, $SELECT, $links, $limit_records, $order_by,
    $dbquery, $sth, $ref, $j, $key, $value, $show_full_details_link,
    $where, $search_title, $MaxRecordsToShow, $max_length_to_show,
    $Sort_Field);

    #### prepare query
    $limit_records = $MaxRecordsToShow ? $MaxRecordsToShow:5; ## set to 12 in params
    $limit_records = 7;
    $max_length_to_show = $max_length_to_show ? $max_length_to_show:120;

    $sort_field = $item_orderby_field;
    $sort_order = $item_orderby_order;
    $Sort_Field = "RecordID" unless($Sort_Field);

    my $count = $count_of_active_records;

    my @DB_field_names = &get_field_names($dbtable);

    if ($user_query eq "")
    {
	    my $fieldsort1 = &SIT_escape_string($data{fieldsort1});
	    my $fieldsort2 = &SIT_escape_string($data{fieldsort2});
	    my $keywords1 = &SIT_escape_string($data{keywords1});
	    my $keywords2 = &SIT_escape_string($data{keywords2});
	    my $sort_order = &SIT_escape_string($data{sort_order}) if($data{sort_order} ne "");

	    my $params = "&offset=$data->{offset}&max_rows=$data->{max_rows}&fieldsort1=$fieldsort1&&fieldsort2=$fieldsort2&keywords1=$keywords1&keywords2=$keywords2&sort_order=$sort_order";

        #move this IF under the IF below??
        if($data{sort_order} ne "")
        {
                $sort_order = $data{sort_order};
        }

        $order_by = " ORDER BY $Sort_Field $sort_order" if($Sort_Field ne "");
        $where = qq~ WHERE  Start_Date >= "$todaysDate" AND $item_active_field = "$item_active_true" ~;
        $where .= qq~ AND $item_category_field = "$data{$item_category_field}"~ if ($data{$item_category_field} ne "");
        #move this IF under the IF below??
        if($data{fieldsort1} ne "")
        {
                $order_by = " ORDER BY $data{fieldsort1} $sort_order";
        }
        ## WHERE
        if ((defined $data{fieldsort1}) || (defined $data{keywords1}))
        {
            if($data{keywords1} eq "")
            {
                push(@error_messages,"Enter a keyword or text to search for.");
                return;
            }

            if($data{fieldsort1} eq "")
            {
                push(@error_messages,"Select a field to search and sort the records by.");
                return;
            }

            my $keywords1 = "%" .$data{keywords1} ."%";
            my $keywords1_q = $dbh -> quote($keywords1);
            if ((defined $data{keywords2}) && ($data{keywords2}  ne ""))
            {
                my $keywords2 = "%" .$data{keywords2} ."%";
                my $keywords2_q = $dbh -> quote($keywords2);
                $where = qq~ WHERE $data{fieldsort1} LIKE $keywords1_q AND $data{fieldsort2} LIKE $keywords2_q AND $item_active_field = "$item_active_true"~;
            }
            else
            {
                $where = qq~ WHERE $data{fieldsort1} LIKE $keywords1_q AND $item_active_field = "$item_active_true"~;
            }
            $my_and = " AND $data{fieldsort2} having '$data{keywords2}'" if ($data{fieldsort2} ne "");
            $search_title = "Search Results --&gt; $data{fieldsort1} having '$data{keywords1}' $my_and";
        }
        else
        {
            $order_by = "ORDER BY $sort_field $sort_order";
            my $tempTXT;
            if ($data{$item_category_field} ne "")
            {
            	$tempTXT = $data{$item_category_field};
                $tempTXT =~ s/_/ /g;
            }
            else
            {
                $tempTXT = "Events";
            }
            $search_title = "Upcoming $tempTXT";
        }

        my $max_rows = $data{'max_rows'};
        if ($max_rows <= 0)
        { ### Get result count
            my $dbquery = "SELECT * FROM $dbtable $where";
            unless ($sth = $dbh -> prepare($dbquery))
            {
                push(@error_messages,"Query syntax error.<BR>$dbquery.<BR>Reason: $DBI::errstr.<BR>Contact: $error_mail_link.");
            }
            if( not $sth -> execute())
            {
                push(@error_messages,"Can't execute query:<BR>$dbquery.<BR>Reason: $DBI::errstr.<BR>Contact: $error_mail_link.");
                $sth -> finish;
                SITe_DisConnectFromDB();
                return;
            }

            $data{'max_rows'} = $max_rows = $sth -> rows;
            $sth -> finish;
        }

        $offset  = $data{offset};
        $offset  = 0 if(($offset <= 0) || ($offset > $max_rows) || ($offset eq ""));
        $LIMIT   = " LIMIT $offset,$limit_records";
        $SELECT  = "SELECT * FROM $dbtable $where $order_by";
        $dbquery = $SELECT.$LIMIT;
    }
    else
    {
    	$dbquery = $user_query;
        my @temp_moYr = split/-/, $data{Mo_Yr};
        my $Event_Date = "$MonthNames[$temp_moYr[0]] $data{Day}, $temp_moYr[1]";
        $search_title = "Events Scheduled for $Event_Date" if ($data{cmd} eq "ListDate");
        $search_title = "Search Results:<BR>$user_query" if ($data{user_query} ne "");
    }

    #### Query SOL for rows/records
    unless ($sth = $dbh -> prepare($dbquery))
    {
        push(@error_messages,"Query syntax error.<BR>$dbquery.<BR>Reason: $DBI::errstr.<BR>Contact: $error_mail_link.");
        return;
    }
    if( not $sth -> execute())
    {
    	push(@error_messages,"Can't execute query:<BR>$dbquery.<BR>Reason: $DBI::errstr.<BR>Contact: $error_mail_link.");
        $sth -> finish;
        SITe_DisConnectFromDB();
        return;
    }

    my $rows = $sth -> rows;
    if ($max_rows <=0) { $max_rows = $rows; }
    if ($rows <= 0)    { $sth -> finish; return qq~<FONT color="#990000"><B>No records found matching your search parameters.</B></FONT>~;}
    #### Build page links in memmory if not rendering page for printing

    if($max_rows > $limit_records){
        my $params = "&max_rows=$max_rows";

        $params  .= "&$data{offset}=" .&SIT_escape_string($data{offset}) if (defined $data{offset});
        $params  .= "&$data{max_rows}=" .&SIT_escape_string($data{max_rows}) if (defined $data{max_rows});


        $links = "Pages: ";
        my $num = int ($max_rows / $limit_records);
        my $rem = $max_rows % $limit_records;
        $num++ if($rem > 0);
        my ($lastpage,$lastnumber,$lastlink,$min,$max);

        if($num > 10){
            $min = (($data{offset} - (4 * $limit_records)) < 0) ? 0:($data{offset} - (4 * $limit_records));
            $max = (($data{offset} + (4 * $limit_records)) > $max_rows) ? $max_rows-$limit_records:($data{offset} + (4 * $limit_records));
            if ($data{offset} > 0){
                $links .= qq~<a href="$selfURL?offset=0$params"> 1</a>...&nbsp;~;
            }
            else{
                $links .= "1... &nbsp;";

            }
            $max = 9 * $limit_records if $max < 90;

            $lastpage = ($num-1) * $limit_records;
            $lastnumber = $num;
            $lastlink = qq~&nbsp;...<a href="$selfURL?offset=$lastpage$params"> $lastnumber</a>&nbsp;~;
            $lastlink = qq~&nbsp;...$lastnumber &nbsp;~ if($data{offset} == $lastpage);
            $min = ($lastpage - (9 * $limit_records)) if($min > $lastpage - (9 * $limit_records));
        }
        for(my $i=0; $i<$num; $i++){
            my $c = $i * $limit_records;
            my $ix = $i +1;
            if($num > 10 ){
                next if $i == 0;
                next if $c == $lastpage;
                if($c == $max){
                    $links .= qq~<a href="$selfURL?offset=$c$params"> $ix</a>&nbsp;~;
                }
                if($c == $min){
                    $links .= qq~<a href="$selfURL?offset=$c$params"> $ix</a>&nbsp;~;
                }
                if($c == $data{offset} && $data{offset} > 0){
                    $links .= " $ix&nbsp;";
                }
                else{
                    if(($c > $min) && ($c <  $max)){
                        $links .= qq~<a href="$selfURL?offset=$c$params"> $ix</a>&nbsp;~;
                    }
                }
            }
            else{
                if($c == $offset){
                    $links .= " $ix&nbsp;";
                }
                else{
                    $links .= qq~<a href="$selfURL?offset=$c$params"> $ix</a>&nbsp;~;
                }
            }
        }
        if($num > 10){
            $links .= $lastlink;
        }
    }
    my $my_page_links = qq~<TR><td colspan="3" align="right" style="background-color:$second_table_bgcolor;">$links</td></TR>\n~;

    ##### output the record/rows table starting with
    ##### the page links row & list index column hdr
    $main_content_title = $search_title;
    my $html =qq~
        <TABLE width="100%">
            $my_page_links
    ~;
    ##### output the table rows (data)
    my $listIndex = 1; my $news_paragraphs="";
    while(my $sku_ref = $sth->fetchrow_hashref())
    {
        my $color = $row_color1;
        if ($listIndex % 2) {$color = $row_color2; }
        ##### output in mem a line reference number for this record
        my @startDateData = split/-/, $sku_ref->{Start_Date};
	    my $startDate = "$MonthNames[$startDateData[1]] $startDateData[2], $startDateData[0]";
        my $tempMoYr = "$startDateData[1]-$startDateData[0]";
        my $truncated_details = substr($sku_ref->{$item_details_field},0,200);
        my $truncated_results = substr($sku_ref->{Final_Results},0,200);
        $show_full_details_link = qq~<A href="$selfURL?cmd=ShowEvent&$item_number_field=$sku_ref->{$item_number_field}&Mo_Yr=$tempMoYr">Learn More...</A>~;

        my ($colspan,$colspan_html);
        if ($sku_ref->{Final_Results} eq "")
        {
         	$colspan =3;
            $colspan_html = qq~~;
        }
        else
        {
             $colspan =2;
             $colspan_html = qq~<TD width="33%" valign="top" style="font-family:Ariel,Helvetica,sans-serif; font-size:8pt; background-color:$color">
                 <DIV style="margin:5px;"><B>Final Results:</B><BR>
                 $truncated_results $show_full_details_link</DIV>
	        </TD>~
        }

        $html .=qq~
        <TR class="hdr_results" >
            <TD  width="33%"  style="font-size:10pt;">
            	<B>&nbsp;$startDate</B><BR>
            </TD>
            <TD  colspan=2 style="font-size:10pt;">
            	&nbsp;$sku_ref->{$item_name_field}
            </TD>
        </TR>
        <TR>
	        <TD colspan=$colspan valign="top" style="font-family:Ariel,Helvetica,sans-serif; font-size:8pt; background-color:$color">
                 <DIV style="margin:5px;">
                 	<B>$sku_ref->{Host_Name} $sku_ref->{Start_Time} - $sku_ref->{Headline}</B> $sku_ref->{Subheading} $truncated_details
                 	$show_full_details_link
                 </DIV>
	        </TD>
            $colspan_html
	    </TR>
        ~;
        $listIndex++;
    }
    $sth -> finish;
    ##### output the table page links
    $html .= qq!$my_page_links</TABLE>\n!;

    return $html; #$rows;

}

sub Show_event_listing(){

    my $sku_ref = &query_INV_DB_for_record_data($data{$item_number_field});

    my $item_number =   $sku_ref->{$item_number_field};
    $item_name =   $sku_ref->{$item_name_field};
    $page_title_text = "$item_name - $sku_ref->{Host_Name}";
    $main_content_title = "$item_name";
    $this_row_bg_color = '#FFF7F0';
    if ($listIndex % 2) {$this_row_bg_color = '#ffffff'; }

    #my $image_file_name = "$sku_ref->{Long_Description}.jpg";
    my $med_image_file_name = "$sku_ref->{$item_number_field}.jpg";
    my $sml_image_file_name = "$sku_ref->{$item_number_field}_sm.jpg";
    $image_file_name =~ s/ /_/g;

    if (-e "../$Data_Table/med/$lrg_image_file_name")
    {
        $product_img_URL = qq~<img src="$my_WS_HTTP_URL/$Data_Table/med/$med_image_file_name" border="0" alt="$item_name ">~;
    }
    else
    {
        if (-e "../$Data_Table/small/$sml_image_file_name")
        {
            $product_img_URL = qq~<img src="$my_WS_HTTP_URL/$Data_Table/small/$sml_image_file_name" border="0" alt="$item_name ">~;
        }
        else
        {
            $product_img_URL = "<FONT color='#990000'><B>No Photo</B></FONT>";
        }
    }

    my @startDateData = split/-/, $sku_ref->{Start_Date};
    my $startDate = "$MonthNames[$startDateData[1]] $startDateData[2], $startDateData[0]";

    my $final_results_row = qq~
    		<TR>
	            <TD  class="cartTD" colspan='2' valign='top'>
                    <B>Final Results:</B><BR>$sku_ref->{Final_Results}<BR>
                    <A href="#Top">Top</A>
	            </TD>
	        </TR>
    ~ if ($sku_ref->{Final_Results} ne "");

    $html_row = qq~
	    <TABLE width=100%" cellpadding="5">
	        <tr>
	            <TD align="center" style="font-size:12pt;" colspan='2'>
	                <B>$sku_ref->{Headline}</B><BR>$sku_ref->{Subheading}
	            </TD>
	        </TR>
	        <tr >
	            <td class="cartTD" valign='top' align="center" width="50%">
	                $product_img_URL<BR>
	            </td>
	            <td  class="cartTD" valign='top' align="center" width="50%">
                    <B>Starts $startDate<BR>
                    $sku_ref->{Start_Time}</B><BR><BR>

                    $sku_ref->{Host_Name}<BR>
                    $sku_ref->{Host_Location}<BR>
                    $sku_ref->{Host_Address}<BR>
                    $sku_ref->{Host_City}, $sku_ref->{Host_State} $sku_ref->{Host_Zip}<BR>
                    <BR>

                    Call $sku_ref->{Contact_Name}<BR>
                    $sku_ref->{Contact_Phone}<BR>
                    for more details.
	            </td>
	        </tr>
	        <TR>
	            <TD  class="cartTD" colspan='2' valign='top'><BR>
                    $sku_ref->{$item_details_field}
	            </TD>
	        </TR>
            $final_results_row
	    </TABLE>
        <A href="#Top">Top</A>
    ~;

    return $html_row;
}

sub query_events_for_date(){
    my @temp_moYr = split/-/, $data{Mo_Yr};
    my $searchDate = "$temp_moYr[1]-$temp_moYr[0]-$data{Day}";
    my $Event_Date = "$MonthNames[$temp_moYr[0]] $data{Day}, $temp_moYr[1]";

    $page_title_text = "Events for $Event_Date";
    $main_content_title = "$Event_Date Events";
    &Search_my_DB_Records($Data_Table,qq~SELECT * FROM $Data_Table WHERE $item_active_field = "$item_active_true" AND Start_Date = "$searchDate"~)

}



sub SIT_escape_string {
    my($esc) = @_;
    $esc =~ s/([^a-zA-Z0-9_\-.])/uc sprintf("%%%02x",ord($1))/eg;
        $esc =~ s/ /+/g;
    return $esc;
}

sub my_read_CGI_input() {
        my($buffer) = undef;
        my($item);
        return(&SIT_read_CGI_input_multi_part()) if($ENV{'CONTENT_TYPE'} =~ /multi/i);

        if ($ENV{'REQUEST_METHOD'} =~ /POST/i) {
                read(STDIN,$buffer,$ENV{'CONTENT_LENGTH'});
        }
        else {
                $buffer=$ENV{'QUERY_STRING'};
        }
        $buffer = $ARGV[0]  if (not $buffer);

        my @pairs=split(/&/,$buffer);
        my $i = 0;
        foreach $item(@pairs) {
                my ($key,$content)=split (/=/,$item,2);    # Split into key and value.
                $content =~ tr/+/ /; # Convert plus's to spaces
                $content =~ s/%(..)/pack("c",hex($1))/ge;        # Convert %XX from hex numbers to alphanumeric
                # Convert %XX from hex numbers to alphanumeric when using hidden input tags
                $content =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
                $key =~ tr/+/ /; # Convert plus's to spaces
                $key =~ s/%(..)/pack("c",hex($1))/ge;        # Convert %XX from hex numbers to alphanumeric
                # Convert %XX from hex numbers to alphanumeric when using hidden input tags
                $key =~ s/%([a-fA-F0-9][a-fA-F0-9])/pack("C", hex($1))/eg;
                $key =~ s/ /_/g;

                # get rid of attempts to insert illegal characters
                $content =~ s/\\//g; # remove black slashes
                $content =~ s/\0//g; # remove nulls
                $content =~ s/[\\\&\;\`\'\"\|\*\~\^\[\]\{\}\$]//gs;
                # get rid of attempts to insert HTML tags
                $content =~ s/>/&gt;/gs;
                $content =~ s/</&lt;/gs;
                $content =~ s/\cM/\n/g;        #convert CR to LF
                $content =~ s/[\r\n]/ /g;
                $content =~ s/TRUNCATE/ /ig;
                $content = substr($content,0,4096);
                $content =~ s/^\s+|\s+$//gs;

                #make sure the email address is legal
                if(($content =~ m/\w\S+\@\w\S+\./) &&($key =~ /Email/i))
                {
                        my $mail_to = $content;
                        $mail_to =~ /([\w-.]+\@[\w-.]+)/;
                        $content = $1;
                }

                if (!defined($data{$key})) {
                        $data{$key}=$content;
                }
                else
                {
                    if($key !~ /username/ and   $key !~ /password/ and $key !~ /RecordID/)
                    {
                             $data{$key} .= ",$content" if($content ne "");
                    }
                }

                if ($data{$key} =~ /action/) {
                        $data{$key}=$content;
                }

                $CGI_input_names[$i]  = $key;
                $CGI_input_values[$i] = $content;

                $i++;
        }
        return 1;
}

sub show_error(){

    my($item) = shift;
    my $server = ($ENV{HTTP_HOST}) ? $ENV{HTTP_HOST}:'localhost';
    my $REQUEST_URI = $ENV{'SCRIPT_NAME'} ? $ENV{'SCRIPT_NAME'}:$0;
    $item =~ s/\]/\]<br>/g;
    $item =~ s/\(/<br>\(/g;
    $item =~ s/\)/\)<br>/g;
    $item =~ s/\bat\b/<br>at/g;
    my $error =  qq~

    <br>Reason:<br>$item
    ~;

    $main_content = qq~
    <HTML>
    <HEAD>
    <TITLE>Script Error</TITLE>
    </HEAD>

    <BODY BGCOLOR='#FFFFFF'>
    <CENTER>
    <TABLE border='0' cellpadding='0' cellspacing='1' width='98%' BGCOLOR='#990000'><TR><TD>
        <TABLE BORDER=0 WIDTH="100%" CELLSPACING=0 BGCOLOR='#ffffff' cellpadding=3>
              <TR>
                  <TD align="center" BGCOLOR='#990000'>
                      <TABLE><TR><TD>
                              <FONT color='#FFFFFF' face="Verdana,Arial,Helvetica" size=3>
                              <B>CGI Script Error</B></FONT>
                          </TD></TR>
                      </TABLE>
                  </TD>
	          </TR>
	          <TR>
                  <TD bgcolor="#ffeeee">
                      <FONT color='#000000' face="Verdana,Arial,Helvetica" size=3>
                          <P><B>The requested URL</B><BR>http://$server$REQUEST_URI</p>
                          <P><B>returned this error:</B><BR>
                     </FONT>
                     <FONT color='#000000' face="Verdana,Arial,Helvetica" size=2>$error </FONT></P>
                     <P>
                  </TD>
	          </TR>
	          <TR>
                  <TD BGCOLOR='#990000'>
                     <DIV align="center">
                       <FONT color='#ffffff' size=1 face="Verdana,Arial,Helvetica">
                               <B>Spark-IT SITe Myzer for Unix/Linux</B>
                       </FONT>
                   </DIV>
                  </TD>
	          </TR>
        </TABLE>
    </TD></TR></TABLE><BR>
    <P>
    </CENTER>
    </BODY></HTML>
    ~;
    do "../cgi-bin/main_html_doc.pl";
    SITe_DisConnectFromDB();
    exit(0);
}

sub urlencode(){
    my($esc) = @_;
    $esc =~ s/^\s+|\s+$//gs;
    $esc =~ s/([^a-zA-Z0-9_\-.])/uc sprintf("%%%02x",ord($1))/eg;
    $esc =~ s/ /\+/g;
    $esc =~ s/%20/\+/g;
    return $esc;
}


sub query_INV_DB_for_record_data(){
        my $item_number = shift;
        # Query DB for Inventory Item data
        #print "selecting $data{Item} from $dbtable<BR>\n";
        my $db_query = "SELECT * FROM $Data_Table WHERE $item_number_field LIKE '$item_number' LIMIT 1";
        my $recordRef;
        unless($sth = $dbh -> prepare($db_query))
        {
            push(@error_messages,"Query syntax error. $db_query. Reason: $DBI::errstr.");
            &DisConnectFromDB;
            return;
        }
        unless ($sth -> execute())
        {
            push(@error_messages,"Can't execute query:<br> $db_query.<p><B>Reason:</B><BR>$DBI::errstr.");
            &DisConnectFromDB;
            return;
        }
        unless ($recordRef = $sth->fetchrow_hashref())
        {
            push(@error_messages,"Can't read record:<br> $db_query.<p><B>Reason:</B><BR>Record not found.");
            &DisConnectFromDB;
            return;
        }
        $sth->finish();

        return $recordRef;
}

sub render_event_calendar(){
    my $NumberOfCals = shift;
    $NumberOfCals = 1 if (($NumberOfCals == 0) || ($NumberOfCals eq ""));

    my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
    my $theMonth = $mon+1;
    my $theYear = $year + 1900;

    if ((defined $data{Mo_Yr}) && ($data{Mo_Yr} ne ""))
    {
        @Mo_Yr_array = split /-/,$data{Mo_Yr};
        $theMonth = $Mo_Yr_array[0];
        $theYear = $Mo_Yr_array[1];

        if (defined $data{CalCmd})
	    {
            if ($data{CalCmd} eq "Next")
	        {
	            if ($theMonth == 12)
	            {
	                $theMonth = 1;
	                $theYear = @Mo_Yr_array[1]+1;
	            }
                else
                {
                	$theMonth++;
                }

	        }
	        elsif ($data{CalCmd} eq "Prev")
	        {
	            if ($theMonth == 1)
	            {
	                $theMonth = 12;
	                $theYear = @Mo_Yr_array[1]-1;
	            }
                else
                {
                	$theMonth--;
                }
	        }
	    }
    }


    my $cal = "";
    for ($i = 0; $i < $NumberOfCals; $i++)
	{
        my $targetMon = $theMonth + $i;
        if ($targetMon > 12)
        {
                $ARGmon = $targetMon - 12;
                $ARGyear = $theYear + 1;
                $cal .= &RetCal_small("$ARGmon-$ARGyear");
        }
        else
        {
                $ARGmon = $targetMon;
                $ARGyear = $theYear;
                $cal .= &RetCal_small("$ARGmon-$ARGyear");
        }
	}

    return $cal;

}

sub RetCal_small() {
	my $Mo_Yr = shift;
    # Initialize variables
    my $border="0";
    my $borderColor="";
    my $backColor="Gray";
    my $bgColorStyle="";
    my $column_spacer_width = "30px";
    my $row_spacer_height = "30px";

    my $titleBackColor = "White";
    my $hdrBackColor = "Cyan";
    my $hdrClass = "class='hdr_cyan'";
    my $tableAlign = "center";
    my $cellBackColor = "White";
    my $cellHighColor = "LightGreen";

	my $tempMonth ="";
	my $eventQUERY = "";

	my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
    my $theMonth = $mon+1;
    my $theYear = $year + 1900;
	my $todaysDate = "$theMonth/$mday/$theYear";

	my $clock = "$hour:$min:$sec";

	my ($ret, @Result, $Month, $dy, $line, $no);

	if ( -x "$CalCMD" )
    {
		if (($Mo_Yr !~ /^\d\d?-\d\d\d\d$/) || ($Mo_Yr eq ""))
        {
            $Mo_Yr = "";
			@date = localtime(time);
			$Today = $date[3];
		}
    	else
        {
            @Mo_Yr_ARRAY = split /-/, $Mo_Yr;
        	$theMonth = $MonthNumbers[$Mo_Yr_ARRAY[0]];
        	$theYear = $Mo_Yr_ARRAY[1];

            $Mo_Year_param = $Mo_Yr;
            $Mo_Year_param =~ s/-/ /g;
            @date = localtime(time);
			$Today = $date[3];
		}

		open(CAL, "$CalCMD $Mo_Year_param|");
		        @Result = <CAL>;
		close(CAL);

		$Month = $Result[0]; shift(@Result);

		$Month =~ s/\s\s\s*//g;

        my $tablePixelWidthVal = (7 x $column_spacer_width);
        my $tablePixelWidth = "$tablePixelWidthVal" & "px";
		$ret = qq~<TABLE align="$tableAlign" width="$tablePixelWidth" border="$border" bordercolor="$borderColor" bgcolor="$backColor"><TR bgcolor="$titleBackColor"><TD style="font-size:8pt;">&nbsp;<A href="$selfURL?CalCmd=Prev&Mo_Yr=$Mo_Yr"><B>&lt;</B></A></TD><TD colspan="5"><DIV align="center" style="font-size:10pt;">$Month</DIV></TD><TD align="right"  style="font-size:8pt;"><A href="$selfURL?CalCmd=Next&Mo_Yr=$Mo_Yr"><B>&gt;</B></A>&nbsp;</TD></TR>\n<TR bgcolor="$hdrBackColor" $hdrClass>~;

		shift(@Result);
		foreach $dy (split(/ /, $DaysL))
        {
			$ret .= qq~<TD style='font-size:10pt;' align='center'><img src='$my_Images_folder/spacer.gif' width='$column_spacer_width' height='1' border='0'><BR>$dy</TD>~;
		}
		$ret .= qq~\n</TR>\n~;

		foreach $line (@Result)
        {
			$line =~ s/\n//;
			$line =~ s/   /ccs/g;
			$line =~ s/  /sc/g;
			$line =~ s/ /s/g;
			$line =~ s/^s//g;
			$line =~ s/ss/s/g;
			$line =~ s/c//g;
			$ret .= qq~<TR >~;
			foreach $no (split(/s/, $line))
            {
	            if (($no > 0 ) && ($no < 32))
	            {
	                $bgColorStyle = "";
	                $EVENTstring = "&nbsp;";
	                my $theDay      =  $DayNumbers[$no];
	                $CheckDay    =  "$theYear-$theMonth-$theDay";

                    # Check for events for this day...
                    my $events_found  = 0;
	                $events_found = &query_count_events_for_date($CheckDay);

	                if ($events_found > 0)
	                {
	                    $EVENTstring = qq~<DIV align="center" bgcolor='#999900' style="font-size:10pt;"><A href="$selfURL?cmd=ListDate&Mo_Yr=$Mo_Yr&Day=$no"><B>$events_found</B></A></DIV>~;
	                    $bgColorStyle = "background-color:#EEEEEE;"
	                }

	                if (($no eq $Today)  && ($theMonth eq $mon+1) && ($theYear eq $year+1900))
	                {
	                    $ret .= qq~<TD align="left" height="$row_spacer_height" valign="top"  bgcolor="$cellHighColor" style="$bgColorStyle"><DIV style="font-size: 6pt; color: #000000;">$no</DIV>$EVENTstring</TD>\n~;
	                }
	                else
	                {
	                    $ret .= qq~<TD align="left" height="$row_spacer_height" valign="top"  bgcolor="$cellBackColor" style="$bgColorStyle"><DIV style="font-size: 6pt; color: #000000;" >$no</DIV>$EVENTstring</TD>\n~;
	                }
	            }
	            else
	            {
	                $ret .= qq~<TD>&nbsp;</TD>~; #??
	            }
			}
			$ret .= qq~</TR>\n~;
		}
		$ret .= qq~</TABLE>\n~;
	}
    else
    {
		$ret = "Could not find 'calendar' on this system.";
	}
	return $ret;
}


__END__



