<?php
//Let's set up some local functions to make life easier on us.

//Some Global Variables

//QUERY_DEBUG used by function safe_query
$QUERY_DEBUG = "Off";

//Function: 	safe_query
//Source:	MySQL/PHP Database Applications by Greenspan and Bulger
//Use:		You can set debug to "on" if you want more descriptive 
//		error codes while programming. 

function safe_query ($query = " ")
{
	if (empty($query)) { return FALSE; }

//	 if($QUERY_DEBUG = "Off")
//	 {
//		$local_result = mysql_query($query) or
//			die ("Query failed: please contact admin@workshops.isoc.org");
//	}
//	else
//	{
	$local_result = mysql_query($query)
		or die("Query failed: contact admin@workshops.isoc.org. Errors are as follows:"
			."<li>errorno=".mysql_errno()
			."<li>error=".mysql_error()
			."<li>query=".$query
		);
//	}
	return $local_result;
}


//Function: 	date_to_date
//Source:	Hervey Allen, NSRC, September 2002
//Use:		This will format a date column in to the expected date
//		format for use with nsrc.org web page. The format is
//              "Updated: xn-MMM-YYYY" - or, 5-Aug-2001, and 10-Jan-1999,
//              Note that there is no leading "0".
//              Also Note, this is a different conversion from timestamp.

function date_to_date ($date)
{
  $formatted_year = substr($date,0,4);
  $formatted_month = substr($date,5,2);
  $formatted_day = substr($date,8,2);
     if (substr($formatted_day, 0, 1) == "0") {
       $formatted_day = substr($formatted_day, 1, 1);
     }

     switch ($formatted_month)
       {
       case "1": $formatted_month = "Jan";
	 break;
       case "2": $formatted_month = "Feb";
	 break;
       case "3": $formatted_month = "Mar";
	 break;
       case "4": $formatted_month = "Apr";
	 break;
       case "5": $formatted_month = "May";
	 break;
       case "6": $formatted_month = "Jun";
	 break;
       case "7": $formatted_month = "Jul";
	 break;
       case "8": $formatted_month = "Aug";
	 break;
       case "9": $formatted_month = "Sep";
	 break;
       case "10": $formatted_month = "Oct";
	 break;
       case "11": $formatted_month = "Nov";
	 break;
       case "12": $formatted_month = "Dec";
	 break;
       default:
	 $formatted_month = "??";
       }

     $formatted_date = $formatted_day ."-". $formatted_month ."-". $formatted_year;

     return $formatted_date;

}


//Function: 	date_to_ts
//Source:	Hervey Allen, ISOC, October 2003
//Use:		This will format a date column to a UNIX timestamp. Note that
//              we don't have an actual time, so we set this to Noon, or '120000'
//

function date_to_ts ($date)
{
    $year = substr($date,0,4);
    $month = substr($date,5,2);
    $day = substr($date,8,2);
    
    $timestamp = $year.$month.$day. "120000";

     return $timestamp;
}


//Function: 	ts_to_date
//Source:	Hervey Allen, NSRC, September 2002
//Use:		This will format a ts column in to the expected datea
//		format for use with nsrc.org web page. The format is
//              "Updated: xn-MMM-YYYY" - or, 5-Aug-2001, and 10-Jan-1999,
//              Note that there is no leading "0".

function ts_to_date ($ts)
{
     $formatted_date = substr($ts,0,8);
     $formatted_year = substr($formatted_date, 0, 4);

     $formatted_month = substr($formatted_date, 4, 2);
     if (substr($formatted_month, 0, 1) == "0") {
       $formatted_month = substr($formatted_month, 1, 1);
     }

     $formatted_day = substr($formatted_date, 6, 2);
     if (substr($formatted_day, 0, 1) == "0") {
       $formatted_day = substr($formatted_day, 1, 1);
     }

     switch ($formatted_month)
       {
       case "1": $formatted_month = "Jan";
	 break;
       case "2": $formatted_month = "Feb";
	 break;
       case "3": $formatted_month = "Mar";
	 break;
       case "4": $formatted_month = "Apr";
	 break;
       case "5": $formatted_month = "May";
	 break;
       case "6": $formatted_month = "Jun";
	 break;
       case "7": $formatted_month = "Jul";
	 break;
       case "8": $formatted_month = "Aug";
	 break;
       case "9": $formatted_month = "Sep";
	 break;
       case "10": $formatted_month = "Oct";
	 break;
       case "11": $formatted_month = "Nov";
	 break;
       case "12": $formatted_month = "Dec";
	 break;
       default:
	 $formatted_month = "??";
       }

     $formatted_date = $formatted_day ."-". $formatted_month ."-". $formatted_year;

     return $formatted_date;
}


//Function: 	select_countries
//Source:	Hervey Allen, NSRC, September 2002
//Use:		Used in Report View to determine what countries the
//		report belongs to. We need to parse all country codes,
//              and then we need to create a "Countries" variable with
//              each countries name.

function select_countries ($countries)
{

  // First, let's be careful. If for some reason we have a malformed
  // string with a trailing ':', get rid of it. Note, you can use
  // the more powerful preg POSIX regular expression functions like
  // preg_split to do this, but then have fun reading the code...

  $pieces = trim($countries);
  $pieces = explode(":", $pieces);

  return $pieces;

}


//Function: 	select_countries
//Source:	Hervey Allen, NSRC, September 2002
//Use:		Used in Report View to determine what countries the
//		report belongs to. We need to parse all country codes,
//              and then we need to create a "Countries" variable with
//              each countries name.

function display_countries ($formvalues_array)
{

  // First, let's be careful. If for some reason we have a malformed
  // string with a trailing ':', get rid of it. Note, you can use
  // the more powerful preg POSIX regular expression functions like
  // preg_split to do this, but then have fun reading the code...

  $local_countries = $formvalues_array["countries"];

  echo "<b>countries = : </b>" .$local_countries. "<br>\n";

     $pieces = $countries;
     $pieces = trim($pieces);
     $pieces = explode(":", $pieces);
  echo "<b>pieces = : </b>" .$pieces. "<br>\n";
     $num_cc = count($pieces);

     echo "<b>num_cc = : </b>" .$num_cc. "<br>\n";

     for($i = 1; $i <= $num_cc; $i++)
       {

       $query2 = "select * from country where country_code='$pieces[$i]'";
       $query2 = stripslashes($query2);
       $result2 = safe_query($query2);
       $row2 = mysql_fetch_array($result2);

       echo "<b>result2 = :" .$result2. "</b><br>\n";
       echo "<b>row2[country_name] = :" .$row2["country_name"]. "</b><br>\n";

       $exploded_countries = $row2["country_name"];
       
       }

  return $exploded_countries;

}


//
// function privilege_level:
//

function privilege_level($appUsername)
{

if((isset($PHPSESSID)) & (isset($HTTP_SESSION_VARS["authenticatedUser"])))
  {
  $authedUser = $HTTP_SESSION_VARS["authenticatedUser"];
  $query = "select * from user where userid='$authedUser'";
  $query = stripslashes($query);
  $result = safe_query($query);
  $row = mysql_fetch_array($result);

 if ((mysql_num_rows($result) == 1) & ($row["privilege"] == 1))
  {
    return 1;
  }
 elseif ((mysql_num_rows($result) == 1) & ($row["privilege"] == 0))
   {
     return 0;
   }
 elseif ((mysql_num_rows($result) == 1) & ($row["privilege"] != 0) & ($row["privilege"] != 1))
   {
     return -2;
   }
 else
   {
     // User is not logged in properly.
     return -1;

   } // end else
  } // end if

} // end priv_level


//
// function authenticate
//

function authenticate($authed_user, $authed_user_pw)
{

  if($authed_user == '')
    {
      $error_array["authed_user"] = "Username blank.";
    }

  if($authed_user_pw == '')
    {
      $error_array["authed_user_pw"] = "Password blank.";
    }

  if($authed_user != '')
    {
      $query = "select * from user where userid='$authed_user'";
      $query = stripslashes($query);
      $result = safe_query($query);
      $row = mysql_fetch_array($result);

      if (mysql_num_rows($result) === 1) 
	{
	  $error_array["authed_user"] = '';
	}
      else
	{
	  $error_array["authed_user"] = "Username \"" .$authed_user. "\" is invalid!";
	} 
    }

 if($error_array["authed_user"] == '')
   {

     $md5_password = "MD5:" . strtoupper(md5($authed_user_pw));

     $query = "select *  from user where password='$md5_password' and userid='$authed_user'";
     $query = stripslashes($query);
     $result = safe_query($query);
     $row = mysql_fetch_array($result);

// If true, then the password/userid combination is correct.

     if (mysql_num_rows($result) == 1)
       {
	 $error_array["authed_user_pw"] = '';
       }
     else
       {
	 $error_array["authed_user_pw"] = "Incorrect password.";
       }
   }
 else
   {
     $error_array["authed_user_pw"] = '';
   }

 return $error_array;

} // end function authenticate


//
// Function verify password. Just wanna know if the current user has
// given us a valid password.
//

function verify_password($authed_user, $user_pw)
    {
        
     if(empty($user_pw))
        {
            $result = 'EMPTY';
            
            return $result;
        }
        
     $md5_password = "MD5:" . strtoupper(md5($user_pw));

     $query = "select *  from user where password='$md5_password' and userid='$authed_user'";
     $query = stripslashes($query);
     $result = safe_query($query);
     $row = mysql_fetch_array($result);
     
    if (mysql_num_rows($result) == 1)
       {
	 $result = 'TRUE';
       }
     else
       {
	 $result = 'FALSE';
       }
   
        return $result;
    }


function country_lookup($ISO_code)
{
 $query_country = "select country_name from country where country_code='" .$ISO_code. "'";
 $query_country = stripslashes($query_country);
 $result_country = safe_query($query_country);
 $row_country = mysql_fetch_array($result_country);


 return $row_country['country_name'];
}


function region_lookup($region_code)
{
 $query_region = "select long_name from regions where region='" .$region_code. "'";
 $query_region = stripslashes($query_region);
 $result_region = safe_query($query_region);
 $row_region = mysql_fetch_array($result_region);


 return $row_region['long_name'];
}


function user_lookup($id)
{

  $query_user = "select name from user where id='" .$id. "'";
  $query_user = stripslashes($query_user);
  $result_user = safe_query($query_user);
  $row_user = mysql_fetch_array($result_user);

  return $row_user['name'];
}


//
// Function language_lookup
//
// Find the actual language name based on the drop-down list position
//

function language_lookup($lang_id)
{
       $querylang = "select * from languages where list_item_number='$lang_id'";
       $querylang = stripslashes($querylang);
       $resultlang = safe_query($querylang);
       $rowlang = mysql_fetch_array($resultlang);
       
       return $rowlang["language"];
}


//
// Function language_id_lookup
//
// Find the actual language id based on language name
//

function language_id_lookup($lang_name)
{
       $querylang = "select * from languages where language='$lang_name'";
       $querylang = stripslashes($querylang);
       $resultlang = safe_query($querylang);
       $rowlang = mysql_fetch_array($resultlang);
       
       return $rowlang["list_item_number"];
}


//
// Function topic_id_lookup
//
// Find the actual topic id based on ltopic name
//

function topic_id_lookup($topic)
{
       $querytopic = "select * from topics where topic='$topic'";
       $querytopic = stripslashes($querytopic);
       $resulttopic = safe_query($querytopic);
       $rowtopic = mysql_fetch_array($resulttopic);
       
       return $rowtopic["id"];
}


//
// Function workshop_lookup
//

function workshop_lookup($by_date, $by_region, $substring)
{

  //
  // We have three possible search methods with any combination of all three.
  // This function does the work of creating the MySQL query string first,
  // then doing the query, then returning the results in an array
  //

  if((!empty($by_date)) & (!empty($by_region)) & (!empty($substring)))
    {
      $query_string = "select * from workshop where year='" .$by_date. "' AND (region='" .$by_region. "' or region_secondary='" .$by_region. "' or region_terciary='" .$by_region. "') AND locate(lcase('" .$substring. "'),lcase(" .title. ")) ORDER by year DESC";
    }

  elseif((!empty($by_date)) & (!empty($by_region)) & (empty($substring)))
    {
      $query_string = "select * from workshop where year='" .$by_date. "' AND (region='" .$by_region. "' or region_secondary='" .$by_region. "' or region_terciary='" .$by_region. "') ORDER by year DESC";
    }

  elseif((!empty($by_date)) & (empty($by_region)) & (empty($substring)))
    {
      $query_string = "select * from workshop where year='" .$by_date. "' ORDER by year DESC";
    }

  elseif((empty($by_date)) & (!empty($by_region)) & (!empty($substring)))
    {
      $query_string = "select * from workshop where (region='" .$by_region. "' or region_secondary='" .$by_region. "' or region_terciary='" .$by_region. "') AND locate(lcase('" .$substring. "'),lcase(" .title. ")) ORDER by year DESC";
    }

  elseif((!empty($by_date)) & (empty($by_region)) & (!empty($substring)))
    {
      $query_string = "select * from workshop where year='" .$by_date. "' AND locate(lcase('" .$substring. "'),lcase(" .title. ")) ORDER by year DESC";
    }

  elseif((empty($by_date)) & (empty($by_region)) & (!empty($substring)))
    {
      $query_string = "select * from workshop where locate(lcase('" .$substring. "'),lcase(" .title. ")) ORDER by year DESC";
    }

  elseif((empty($by_date)) & (!empty($by_region)) & (empty($substring)))
    {
      $query_string = "select * from workshop where (region='" .$by_region. "' or region_secondary='" .$by_region. "' or region_terciary='" .$by_region. "') ORDER by year DESC";
    }

  elseif((empty($by_date)) & (empty($by_region)) & (empty($substring)))
    {
      $query_string = "No items selected!\n";
    }

  else
    {
      $query_string = "Error: unknown condition encountered. Contact admin@ws.edu.isoc.org for help.\n";
    }

  return $query_string;

}


?>