<?
	
	/*********************************************************************************/
	function dbLogin()
	{
		global $cfg;
		
		$link = mysql_connect($cfg["DB_HOST"], $cfg["DB_USER"], $cfg["DB_PASS"]);
		
		if (!$link) 
		{
   		die('connect: ' . mysql_error());
		}


		$db_selected = mysql_select_db($cfg["DB_NAME"], $link);
		if (!$db_selected) 
		{
   		die ('select '.$cfg["DB_NAME"].' : ' . mysql_error());
		}

		return true;
	}
	/************************************************************************************/
	
	function dbg($array)
	{
		echo "<pre>";
		print_r($array);
		echo "</pre>";
	}
	
	/************************************************************************************/
	
	function dbSelect($query)
	{
			$res_arr=array();
			
      $result=mysql_query($query) or die(mysql_error());
      
      while ($line = mysql_fetch_array($result, MYSQL_ASSOC))
      {
         $res_arr[]=$line;
      }         
      
      return $res_arr;
	}
	
	/************************************************************************************/
	
	function dbQuery($query)
	{
			$res_arr=array();
			
      $result=mysql_query($query) or die(mysql_error()."\n<br>".$query);
      
      
      return $res_arr;
	}
	
	/************************************************************************************/
	
	function getCategories()
	{
			$res_arr=array();
			
      $query = "select * from categories";
      
      $res_arr = dbSelect($query);
      
      return $res_arr;
	}
	
	/************************************************************************************/
	/************************************************************************************/
	
	function getvolCategories()
	{
			$res_arr=array();
			
      $query = "select * from volcategories";
      
      $res_arr = dbSelect($query);
      
      return $res_arr;
	}
	
	/************************************************************************************/
	
	function getCategory($id=-1)
	{
			$res_arr=array();
			
			if ($id > 0)
			{
      	$query = "select description from categories where id = ".$id;
      
      	$res_arr = dbSelect($query);
      
      	return $res_arr[0]["description"];
      }
      else
      {
      	$query = "select * from categories ";
      
      	$res_arr = dbSelect($query);
      
      	return $res_arr;
      }
	}
	
	
	/************************************************************************************/
	
	function insertWard($data, $categories)
	{
		
			$query = "insert into wards (timestamp, last_name, first_name, email, street_address, apartment_number, zip_code,
																	 telephone_number, request, contacted_before, contact_type)
								values(now(), 
											 '".mysql_escape_string(strip_tags(trim($data["last_name"])))."',
											 '".mysql_escape_string(strip_tags(trim($data["first_name"])))."',
											 '".mysql_escape_string(strip_tags(trim($data["email"])))."',
											 '".mysql_escape_string(strip_tags(trim($data["street_address"])))."',
											 '".mysql_escape_string(strip_tags(trim($data["apartment_number"])))."',
											 '".mysql_escape_string(strip_tags(trim($data["zip_code"])))."',
											 '".mysql_escape_string(strip_tags(trim($data["telephone_number"])))."',
											 '".mysql_escape_string(strip_tags(trim($data["request"])))."',
											 ".$data["contacted_before"].",
											 ".$data["contact_type"].")";
								
			$result=mysql_query($query);
			if (!$result) return false;
			
			$ward_id = mysql_insert_id();
			
			foreach($categories as $curr_cat)
			{
					$query = "insert into wards_categories (ward_id, category_id)
										values(".$ward_id.", ".$curr_cat.")";
								
					$result=mysql_query($query);
					
					if (!$result) return false;
			}
		
			return true;
	}
	
	/************************************************************************************/
	
	function sendEmail($data, $categories)
	{
		global $cfg;

    $ret_val = true;

		$to = $cfg["CUSTOMER_MAIL"].",".$data["email"];

		
    $headers  = "MIME-Version: 1.0\r\n";
    $headers .= "Content-type: text/html\r\n";

    $headers .= "To: ".$to."\r\n";
    $headers .= "From: ".$cfg["SENDER_MAIL"]."\r\n";

		$message = "";
		$message .= "Automatic Form information fields  \n<br>"; 
		$message .= "==============================================================\n<br>"; 
		$message .= "\n<br>"; 
		$message .= "Last  Name: ".$data["last_name"]."\n<br>";
		$message .= "First Name: ".$data["first_name"]."\n<br>";
		$message .= "Email: ".$data["email"]."\n<br>";
		$message .= "Street Address: ".$data["street_address"]."\n<br>";
		$message .= "Apartment Number: ".$data["apartment_number"]."\n<br>";
		$message .= "Zip Code: ".$data["zip_code"]."\n<br>";
		$message .= "Telephone Number: ".$data["telephone_number"]."\n<br>";
		$message .= "Service Request: ".$data["request"]."\n<br>";		
 		$message .= "Have you ever contacted the 50th Ward before?  "; 
		
		if ($data["contacted_before"] == 1) $message .= " YES\n<br>";
		else $message .= " NO\n<br>";

		$message .= "How the customer would like to be contacted?  "; 
		if ($data["contact_type"] == 0) $message .= " - Electronic mail (email)\n<br>"; 
		else $message .= " - Telephone\n<br>"; 
		
		$message .= "\n<br>"; 
		$message .= "Interested in the following:\n<br>"; 
		$message .= "\n<br>"; 


		foreach($categories as $curr_cat)
		{
				$desc = getCategory($curr_cat);
				$message .= "  - ".$desc."\n<br>";
		}
		
		$message .= "\n<br>"; 
		$message .= "\n<br>"; 
		
    $mail_sent = mail($to, $cfg["SUBJECT_MAIL"], $message, $headers);

		return $ret_val;
	}
	
	
	/************************************************************************************/
	
	function getActivePoll($id=-1)
	{
		 $ret_val = array();
		 
		 if ($id > 0)
		 {
		 	$query = "select * 
		 						 from polls 
		 						 where id = ".$id;
		 }
		 else
		 {
		 	$query = "select * 
		 						 from polls 
		 						 where active = 1 
		 						 limit 1";
     }
      
     $ret_val["poll"] = dbSelect($query);
     
     $ret_val["poll"] = $ret_val["poll"][0];
     
     if ($ret_val["poll"])
     {
     	$query = "select * from poll_answers where poll_id = ".$ret_val["poll"]["id"];
      
     	$ret_val["answers"] = dbSelect($query);
     	
     	
     	$query = "select sum(votes) total  from poll_answers where poll_id = ".$ret_val["poll"]["id"];
      
     	$ret_val["votes"] = dbSelect($query);
     	$ret_val["votes"] = $ret_val["votes"][0]["total"];
     	
     	
     }
     else $ret_val = array();
     
     return $ret_val;
      
	}
	
	/************************************************************************************/
	
	function updateAnswer($answer_id=0)
	{
		if (!$answer_id) $answer_id = 0;
		
		$query = "update poll_answers set votes=votes+1 where answer_id = ".$answer_id;
								
		$result=mysql_query($query);
					
		if (!$result)  return false;
		
		return true;
	}
	
	/************************************************************************************/
	
	function getRequests($id=-1)
	{
		$ret_val 	= array();
		$result 	= array();
		$tmp_arr 	= array();
		
		if ($id > 0) $where=" where id = ".$id;
		else $where = "";
		$query = "select * from wards ".$where." order by id";
								
		$result=dbSelect($query);
		
		if ($result)
		{
			foreach($result as $curr_res)
			{
					$query = "select * 
										from wards_categories,
												 categories
										where ward_id = ".$curr_res["id"]. "
										and category_id = id";
					$tmp_arr=dbSelect($query);
					
					$curr_res["cat_list"] = $tmp_arr;
					
					$ret_val[] = $curr_res;
			}
		}
						
		return $ret_val;
	}
	
	/************************************************************************************/
	
	function updateCase($data)
	{
	
		if ($data["case_status"] != "") $status = $data["case_status"];
		else $status = "status";
		
		$query = "update wards set status=".$status.", ward_notes='".mysql_escape_string(strip_tags(trim($data["ward_notes"])))."' 
							where id = ".$data["id"];
			
		$result=mysql_query($query);
					
		if (!$result)  return false;
		
		return true;
	}
	
	/************************************************************************************/
	
	function getPolls($id=-1)
	{
		$ret_val 	= array();
		$result 	= array();
		$tmp_arr 	= array();
		
		if ($id > 0) $where=" where id = ".$id;
		else $where = "";
		$query = "select * from polls ".$where." order by id";
								
		$result=dbSelect($query);
		
		if ($result)
		{
			foreach($result as $curr_res)
			{
					$answered = 0;
					
					$query = "select * 
										from poll_answers
										where poll_id = ".$curr_res["id"];
					$tmp_arr=dbSelect($query);
					
					$curr_res["answers"] = $tmp_arr;
					
					foreach($curr_res["answers"] as $curr_answer)
					{
						$answered += $curr_answer["votes"];
					}
					
					$curr_res["votes"] = $answered;
					
					$ret_val[] = $curr_res;
			}
		}
						
		return $ret_val;
	}
	/************************************************************************************/
	
	function addPoll($data)
	{
		
		$query = "insert into polls (question)
							values('".mysql_escape_string(strip_tags(trim($data["question"])))."')";
								
		$result=mysql_query($query);
		if (!$result) return false;
			
		$poll_id = mysql_insert_id();
		
		if ($data["ans_type"] == 0)
		{
			$query = "insert into poll_answers (poll_id, answer_text)
							values (".$poll_id.", 'YES'), (".$poll_id.", 'NO')";
								
			$result=mysql_query($query);
			if (!$result) return false;
		}
		else
		{
			foreach ($data["answers"] as $curr_ans)
			{
				if (mysql_escape_string(strip_tags(trim($curr_ans)))
				{
					$query = "insert into poll_answers (poll_id, answer_text)
								values (".$poll_id.", '".mysql_escape_string(strip_tags(trim($curr_ans)))."')";
									
					$result=mysql_query($query);
					if (!$result) return false;
				}
			}
		}
		
		
		if ($data["set_active"] == 1)
		{
			$query = "update polls set active=0";
									
			$result=mysql_query($query);
			if (!$result) return false;
			
			$query = "update polls set active=1 where id=".$poll_id;
									
			$result=mysql_query($query);
			if (!$result) return false;
		}
		
		
		return true;
	}
	
	/************************************************************************************/
	
	function removePoll($id)
	{
		$query = "delete from polls where id=".$id;
									
		$result=mysql_query($query);
		if (!$result) return false;
		
		$query = "delete from poll_answers where poll_id=".$id;
									
		$result=mysql_query($query);
		if (!$result) return false;
		
		return true;
	}
	
	/************************************************************************************/
	
	function setActivePoll($id)
	{
		$query = "update polls set active=0";
									
		$result=mysql_query($query);
		if (!$result) return false;
			
		$query = "update polls set active=1 where id=".$id;
									
		$result=mysql_query($query);
		if (!$result) return false;
			
		return true;	
	}
	
	/************************************************************************************/
	
	function updatePoll($data)
	{
		
		$query = "update polls set question='".mysql_escape_string(strip_tags(trim($data["question"])))."'
							where id = ".$data["id"];
									
		$result=mysql_query($query);
		if (!$result) return false;
		
		foreach($data["answers"] as $key=>$curr_ans)
		{	
			$query = "update poll_answers set answer_text='".mysql_escape_string(strip_tags(trim($curr_ans)))."' where answer_id=".$key;
									
			$result=mysql_query($query);
			if (!$result) return false;
		}
		
		return true;	
	}
	
	/************************************************************************************/
	
	function caseStatus($id=-1)
	{
		if ($id < 0)
		{
			$query = "select * 
								from status";
								
			$tmp_arr=dbSelect($query);
			
			return $tmp_arr;
		}
		else
		{
			$query = "select * 
								from status where id = ".$id;
								
			$tmp_arr=dbSelect($query);
			
			return $tmp_arr[0]["status"];
		}
	}
	
	/************************************************************************************/
	
	function getNews($id=-1)
	{
		if ($id > 0) $where=" where id = ".$id;
		else $where ="";
		
		$query = "select *,
										 date_format(timestamp, '%m-%d-%Y') date_fmt
							from ward_news ".$where." order by id desc";
								
		$tmp_arr=dbSelect($query);
			
		$news = array();
		
		foreach ($tmp_arr as $curr_new)
		{
			
			
			$curr_new["short_text"]    = str_replace ("\n", "<BR>", $curr_new["short_text"]);
			$curr_new["full_text"]    = str_replace ("\n", "<BR>", $curr_new["full_text"]);
			
			$news[] = $curr_new;
		}
		
		
		return $news;
	}
	
	/************************************************************************************/
	
	function getTypeNews($type="")
	{
		if ($type != "") $where=" where news_type = '".$type."'";
		else $where ="";
		
		$query = "select *,
										 date_format(timestamp, '%m-%d-%Y') date_fmt
							from ward_news ".$where." order by id desc";
								
		$tmp_arr=dbSelect($query);
		
		$news = array();
		
		foreach ($tmp_arr as $curr_new)
		{
			
			
			$curr_new["short_text"]    = str_replace ("\n", "<BR>", $curr_new["short_text"]);
			$curr_new["full_text"]    = str_replace ("\n", "<BR>", $curr_new["full_text"]);
			
			$news[] = $curr_new;
		}
		
		
		return $news;
	}
	
	/************************************************************************************/
	
	function removeNews($id)
	{
		$query = "delete from ward_news where id=".$id;
									
		$result=mysql_query($query);
		if (!$result) return false;
			
		return true;	
	}
	
	/************************************************************************************/
	
	function addNews($data)
	{
		$query = "insert into ward_news(headline1, headline2, short_text, full_text, news_type)
							values('".mysql_escape_string(strip_tags(trim($data["headline1"])))."', '".mysql_escape_string(strip_tags(trim($data["headline2"])))."', '".mysql_escape_string(strip_tags(trim($data["short_text"])))."', '".mysql_escape_string(strip_tags(trim($data["full_text"])))."', '".mysql_escape_string(strip_tags(trim($data["news_type"])))."')";
									
		$result=mysql_query($query);
		if (!$result) return false;
			
		return true;	
	}
	
	/************************************************************************************/
	
	function updateNews($data)
	{
		$query = "update ward_news set headline1 = '".mysql_escape_string(strip_tags(trim($data["headline1"])))."', 
														 headline2 = '".mysql_escape_string(strip_tags(trim($data["headline2"])))."', 
													 short_text = '".mysql_escape_string(strip_tags(trim($data["short_text"])))."', 
										 full_text = '".mysql_escape_string(strip_tags(trim($data["full_text"])))."',
										 news_type = '".mysql_escape_string(strip_tags(trim($data["news_type"])))."'
			where id = ".$data["id"];
							
									
		$result=mysql_query($query);
		if (!$result) return false;
			
		return true;	
	}
	
	/************************************************************************************/
	
	function updateStatus($data)
	{
	
		foreach ($data["status"] as $key=>$curr_status)
		{
			$query = "";
			if (mysql_escape_string(strip_tags(trim($curr_status)) != "")
			{
				
				if (caseStatus($key))
				{
					$query = "update status set status='".$curr_status."' where id=".$key;
				}
				else
				{
					$query = "insert into status(id, status) values (".$key.", '".$curr_status."')";
				}
			}
			else
			{
				if (caseStatus($key))
				{
					$query = "delete from status where id=".$key;
				}
			}
			
			if ($query)
			{
				$result=mysql_query($query);
				if (!$result) return false;
			}
		}
			
		return true;	
	}
	
	/************************************************************************************/
	
	function updateCategory($data)
	{
	
		foreach ($data["category"] as $key=>$curr_category)
		{
			$query = "";
			if (mysql_escape_string(strip_tags(trim($curr_category)) != "")
			{
				
				if (getCategory($key))
				{
					$query = "update categories set description='".$curr_category."' where id=".$key;
				}
				else
				{
					$query = "insert into categories(id, description) values (".$key.", '".$curr_category."')";
				}
			}
			else
			{
				if (getCategory($key))
				{
					$query = "delete from categories where id=".$key;
				}
			}
			
			if ($query)
			{
				$result=mysql_query($query);
				if (!$result) return false;
			}
		}
			
		return true;	
	}
?>