<?php

function Banned($Email='', $ListID=0) {

	if (empty($Email) || empty($ListID)) return false;

	global $TABLEPREFIX;

	$domain = "";

	if (strpos($Email, '@') === false) return true;

	list($j,$domain)=explode('@',$Email);

	$result = mysql_query("SELECT COUNT(*) FROM " . $TABLEPREFIX . "banned_emails WHERE (Email ='".addslashes($Email)."' OR Email LIKE '@".addslashes($domain)."') AND ListID='".addslashes($ListID)."' AND Status='1'");

	$results = mysql_result($result, 0, 0);
	if ($results > 0) {
		return true;
	}else{
		return false;
	}
} # end function function Banned($Email, $ListID)

function OnList($Email='', $ListID='') {

	if (empty($Email) || empty($ListID)) return false;

	global $TABLEPREFIX;

	$result = mysql_query("SELECT COUNT(*) FROM " . $TABLEPREFIX . "members WHERE Email ='".addslashes($Email)."' AND ListID='".addslashes($ListID)."'");
	$found = mysql_result($result, 0, 0);
	if ($found > 0) {
		return true;
	}else{
		return false;
	}
}

/*
	This function is used both for searching and creating the queues for exporting and sending newsletters.
	Up to insert_table everything is reasonably self explanatory.

	the insert_table parameter is an array with the following features:
		$table_details = array();
		$table_details['table']   =  ?? - this is the table we're going to insert into. eg. export_users or send_recipients;
		$table_details['idname']  =  ?? - this tells us what the extra field will be in the queue. eg. ExportID or SendID.
											This directly relates to the next one...
		$table_details['id']      =  ?? - what ID we're processing for.
		$table_details['orderby'] =  ?? - if we should order the results as we put the entries in...
											Lots quicker on the insert than on the select output!

	the limit_results parameter will fetch only specific records (after doing a sort by MemberID).
		$limit_results = array();
		$limit_results['Start'] = ?? - this is where we start in the results list.
		$limit_results['Number'] = ?? - number of results to fetch.

*/
function ReturnMembers($ListID, $Email, $Status, $Confirmed, $Fields, $ClickedLink=0, $Format='ALL', $sorted=true, $insert_table=array(), $count_only=false, $limit_results=array(), $order_results = 'Email', $sort_order='Up', $SearchMemberID=false, $subscribedateinfo=array(), $OpenedEmail=0) {

	global $TABLEPREFIX;

	if ($Email) {
		$Email = trim($Email);
	}

	$query_clause = '';

	if($Status!='ALL') {
		$query_clause.=" AND Status='".addslashes($Status)."'";
	}

	if($Confirmed!='ALL') {
		$query_clause.=" AND Confirmed='".addslashes($Confirmed)."'";
	}

	$SubscribeDate = (isset($_POST['SubscribeDate']['enable'])) ? stripslashes_array($_POST['SubscribeDate']) : array();

	if (isset($_GET['SubscribeDate'])) $SubscribeDate = unserialize(stripslashes(urldecode($_GET['SubscribeDate'])));

	if (empty($SubscribeDate) && !empty($subscribedateinfo)) {
		$SubscribeDate = $subscribedateinfo;
	}

	if (isset($SubscribeDate) && !empty($SubscribeDate)) {
		$type = strtolower($SubscribeDate['type']);

		$subdate = mktime(0, 0, 0, $SubscribeDate['mm_start'], $SubscribeDate['dd_start'], $SubscribeDate['yy_start']);
		switch($type) {
			case 'after':
				$query_clause .= " AND SubscribeDate >= " . $subdate;
			break;

			case 'before':
				$query_clause .= " AND SubscribeDate <= " . $subdate;
			break;

			case 'exact':
				$end_date = mktime(0,0,0, $SubscribeDate['mm_start'], ($SubscribeDate['dd_start'] + 1), $SubscribeDate['yy_start']);
				$query_clause .= " AND (SubscribeDate >= " . $subdate . " AND SubscribeDate < " . $end_date . ")";
			break;

			case 'between':
				$end_date = mktime(0, 0, 0, $SubscribeDate['mm_end'], $SubscribeDate['dd_end'], $SubscribeDate['yy_end']);
				$query_clause .= " AND (SubscribeDate >= " . $subdate . " AND SubscribeDate <= " . $end_date . ")";
			break;
		}

	}

	if ($Format!='ALL') {
			$query_clause.=" AND Format='" . addslashes($Format) . "'";
	}

	$include_members = array();
	$table_joins = array();
	$table_list  = array();

	if (!empty($insert_table)) {

		$search_args = array();
		$search_args['ListID'] = $ListID;
		$search_args['Email'] = $Email;
		$search_args['Status'] = $Status;
		$search_args['Confirmed'] = $Confirmed;
		$search_args['Format'] = $Format;
		$search_args['SubscribeDate'] = $SubscribeDate;

		if ($insert_table['table'] == 'send_recipients') {
			$query = "INSERT INTO " . $TABLEPREFIX . "send_recipients(MemberID, Format, SendID) SELECT m.MemberID, m.Format, " . $insert_table['id'];
		}
		if ($insert_table['table'] == 'export_users') {
			$query = "INSERT INTO " . $TABLEPREFIX . "export_users(MemberID, ExportID) SELECT m.MemberID, " . $insert_table['id'];
		}

		if ($insert_table['table'] == 'autoresponder_recipients') {
			$query = "INSERT INTO " . $TABLEPREFIX . "autoresponder_recipients(MemberID, AutoresponderID) SELECT m.MemberID, " . $insert_table['id'];
		}

	} else {
		if ($count_only) {
			$query = "SELECT COUNT(*)";
		} else {
			$query = "SELECT m.MemberID";
		}
	}

	$query .= " FROM " . $TABLEPREFIX . "members m";

	if (!empty($table_list)) {
		foreach($table_list as $pos => $details) {
			$join_query = '';
			$alias = end(array_keys($details));
			$query .= ", " . $TABLEPREFIX . $details[$alias]['table'] . " " . $alias . " ";
			$join_query .= "m.MemberID=" . $alias . "." . $details[$alias]['link_field'];

			if (isset($details[$alias]['limiter']) && isset($details[$alias]['limiter_field'])) {
				$join_query .= " AND " . $alias . "." . $details[$alias]['limiter_field'] . "='" . addslashes($details[$alias]['limiter']) . "'";
			}
			$table_joins[] = $join_query;
		}
	}

	$query .= " WHERE ";

	foreach($table_joins as $join) {
		$query .= $join . " AND ";
	}

	if($Email != '') {
		$query .= "Email LIKE '%".addslashes($Email)."%' AND ";
	}

	// can't do a straight join here in case a subscriber clicked the same link twice.
	if ((int)$ClickedLink > 0 || $ClickedLink == 'A') {
		$clicked_linkquery = "SELECT distinct memberid from " . $TABLEPREFIX . "link_clicks WHERE ListID='" . addslashes($ListID) . "'";
		if ((int)$ClickedLink > 0) $clicked_linkquery .= " AND LinkID='" . addslashes($ClickedLink) . "'";
		$clicked_members = array();
		$result = mysql_query($clicked_linkquery);
		while($row = mysql_fetch_assoc($result)) {
			$clicked_members[] = $row['memberid'];
		}
		mysql_free_result($result);
		if (empty($clicked_members)) $clicked_members = array('0');
		$query_clause .= " AND MemberID IN (" . implode(',', $clicked_members) . ")";
	}

	if ($OpenedEmail != '0') {
		$email_openquery = "SELECT distinct memberid from " . $TABLEPREFIX . "email_opens";
		if (is_numeric($OpenedEmail)) {
			$email_openquery .= " WHERE SendID='" . (int)$OpenedEmail . "'";
		}
		$email_opened_members = array();
		$result = mysql_query($email_openquery);
		while($row = mysql_fetch_assoc($result)) {
			$email_opened_members[] = $row['memberid'];
		}
		mysql_free_result($result);
		if (empty($email_opened_members)) $email_opened_members = array('0');

		if ($OpenedEmail == 'A') {
			$query_clause .= " AND MemberID IN (" . implode(',', $email_opened_members) . ")";
		}
		if ($OpenedEmail == 'N') {
			$query_clause .= " AND MemberID NOT IN (" . implode(',', $email_opened_members) . ")";
		}
		if (is_numeric($OpenedEmail)) {
			$query_clause .= " AND MemberID IN (" . implode(',', $email_opened_members) . ")";
		}
	}

	$query .= "m.ListID='" . addslashes($ListID) . "' " . $query_clause;

	if ($SearchMemberID) $query .= " AND m.MemberID='" . addslashes($SearchMemberID) . "'";

	if (!is_array($Fields)) {
		$Fields = stripslashes(unserialize($Fields));
	}

	if (!empty($Fields)) {
		$db_fields = array();
		$db_fields_result = mysql_query("SELECT * FROM " . $TABLEPREFIX . "list_fields");
		while($db_field_row=mysql_fetch_assoc($db_fields_result)) {
			$db_fields[] = $db_field_row;
		}

		$search_args['Fields'] = array();

		foreach($Fields as $fid => $fval) {
			if (empty($fval)) unset($Fields[$fid]);
		}

		$field_check_query = "SELECT UserID FROM " . $TABLEPREFIX . "list_field_values WHERE ListID='".addslashes($ListID)."'";

		$field_check_subqueries = array();

		$field_count = 0;

		$other_members = array();

		//check that this user fits the other search criteria!
		foreach($db_fields as $fid => $f) {
			$val=(isset($Fields[$f['FieldID']])) ? $Fields[$f['FieldID']] : '';
			if (empty($val)) continue;
			if ($f['FieldType'] == 'checkbox' && $val == 'all') continue;

			if ($f['FieldType'] == 'datebox' || $f['FieldType'] == 'multicheckbox') {
				if (!is_array($val)) {
					$val = stripslashes($val);
					$val = unserialize(stripslashes($val));
				}
				if (isset($val['type']) && strtolower($val['type']) == 'ignore') continue;
			}

			$field_count++;

			$val = stripslashes_array($val);

			$search_args['Fields'][] = array($f['FieldName'] => $val);

			switch($f['FieldType']) {
				case 'shorttext':
				case 'longtext':
					$field_check_subqueries[] = "(FieldID='" . $f['FieldID'] . "' AND Value LIKE '%" . addslashes($val)."%')";
				break;

				case 'dropdown':
					$field_check_subqueries[] = "(FieldID='" . $f['FieldID'] . "' AND Value = '" . addslashes($val)."')";
				break;

				case 'multicheckbox':
					if (is_array($val)) {
						$subquery = "(FieldID='" . $f['FieldID'] . "' AND (";
						foreach($val as $v) {
							$subquery .= "Value LIKE '%" . addslashes($v)."%' AND ";
						}
						$subquery = substr($subquery, 0, -4);
						$subquery .= "))";
						$field_check_subqueries[] = $subquery;
					}
				break;

				case 'checkbox':
					if($val=='n') {
						$other_members_query = "SELECT MemberID FROM " . $TABLEPREFIX . "members m LEFT OUTER JOIN " . $TABLEPREFIX . "list_field_values lfv ON lfv.UserID=m.MemberID WHERE m.ListID='" . addslashes($ListID) . "' AND lfv.UserID IS NULL";

						$other_members_result = mysql_query($other_members_query);
						while($other_members_row = mysql_fetch_assoc($other_members_result)) {
							$other_members[] = $other_members_row['MemberID'];
						}

						$field_check_subqueries[] = "(FieldID='" . $f['FieldID'] . "' AND Value != 'CHECKED')";

					} else {
						$field_check_subqueries[] = "(FieldID='" . $f['FieldID'] . "' AND Value  = 'CHECKED')";
					}
				break;

				case 'number':
					$subquery = "(FieldID='" . $f['FieldID'] . "' AND (";

					// get rid of anything that isn't a number, isn't a space, isn't a 
					// <, >, =, |, &, and, or.
					$val = preg_replace('%[^\d\s|&><=(and|or)]+%', '', $val);

					// get rid of multiple spaces between numbers.
					$val = preg_replace('%([\d])\s+([\d])%', '\\1\\2', $val);

					if (preg_match('%[^0-9]+%', $val)) {
						$val = strtolower($val);
						$val = str_replace(' && ', ' && Value ', $val);
						$val = str_replace(' || ', ' || Value ', $val);
						$val = str_replace(' and ', ' and Value ', $val);
						$val = str_replace(' or ', ' or Value ', $val);

						// get rid of anything not a number on the end of the query.
						$val = preg_replace('/[^\d]+$/', '', trim($val));

						$subquery .= "Value " . addslashes($val);
					} else {
						$subquery .= "Value='" . addslashes($val)."'";
					}
					$subquery .= "))";
					$field_check_subqueries[] = $subquery;
				break;

				case 'datebox':
					// if we're not passing in a proper value, it's probably a urlencoded string....
					if (!is_array($val)) {
						$val = stripslashes($val);
						$val = unserialize(stripslashes($val));
					}

					if (!isset($val['enable'])) {
						$field_count--;
						continue;
					}

					$subquery = "(FieldID='" . $f['FieldID'] . "' AND (";

					$type = strtolower($val['type']);
					unset($val['type']);

					switch($type) {
						case 'after':
							$subquery .= "(RIGHT(Value, 4) = '" . addslashes($val['yy_start']) . "') AND (LEFT(Value,2) >= '" . addslashes($val['dd_start']) . "' AND MID(Value, 4, 2) >= '" . addslashes($val['mm_start']) . "') OR (RIGHT(Value,4) > '" . addslashes($val['yy_start']) . "')";
						break;

						case 'before':
							$subquery .= "(";
							// check if the year is before yy_start
							$subquery .= "RIGHT(Value, 4) < '" . addslashes($val['yy_start']) . "'";

							// check if the year is the same or month of before mm_start
							$subquery .= " OR (";
							$subquery .= "RIGHT(Value, 4) = '" . addslashes($val['yy_start']) . "'";
							$subquery .= " AND MID(Value, 4, 2) < '" . addslashes($val['mm_start']) . "'";
							$subquery .= ")"; // end or

							// check if the year and month are the same but day is before dd_start
							$subquery .= " OR (";
							$subquery .= "RIGHT(Value, 4) = '" . addslashes($val['yy_start']) . "'";
							$subquery .= " AND MID(Value, 4, 2) = '" . addslashes($val['mm_start']) . "'";
							$subquery .= " AND LEFT(Value,2) < '" . addslashes($val['dd_start']) . "'";
							$subquery .= ")"; // end or

							$subquery .= ")";
						break;

						case 'exact':
							$val = $val['dd_start'] . ':' . $val['mm_start'] . ':' . $val['yy_start'];

							$subquery .= "Value='" . addslashes($val) . "'";
						break;

						case 'between':
							$subquery .= "((";
								$subquery .= "(RIGHT(Value, 4) = '" . addslashes($val['yy_start']) . "') AND (LEFT(Value,2) >= '" . addslashes($val['dd_start']) . "' AND MID(Value, 4, 2) >= '" . addslashes($val['mm_start']) . "') OR (RIGHT(Value,4) > '" . addslashes($val['yy_start']) . "')";
							$subquery .= ") AND (";
								$subquery .= "(RIGHT(Value, 4) = '" . addslashes($val['yy_end']) . "') AND (LEFT(Value,2) <= '" . addslashes($val['dd_end']) . "' AND MID(Value, 4, 2) <= '" . addslashes($val['mm_end']) . "') OR (RIGHT(Value,4) < '" . addslashes($val['yy_end']) . "')";
							$subquery .= "))";
						break;

						case 'ignore':
						default:
							continue;
						break;
					}

					$subquery .= '))';
					$field_check_subqueries[] = $subquery;
				break;
			}
		}

		if (!empty($field_check_subqueries)) {
			$field_check_query .= ' AND (' . implode(' OR ', $field_check_subqueries) . ')';
			if ($field_count > 1) {
				$field_check_query .= ' GROUP BY UserID Having COUNT(*)=' . $field_count;
			}

			$field_check = mysql_query($field_check_query);
			while($row = mysql_fetch_assoc($field_check)) {
				$include_members[] = $row['UserID'];
			}
			if (empty($include_members)) $include_members[] = '0';
		}
	}

	if (is_array($include_members) && !empty($include_members)) $query .= " AND m.MemberID IN (" . implode(',', $include_members) . ")";

	if (!empty($other_members)) {
		$query .= " OR m.MemberID IN (" . implode(',', $other_members) . ")";
	}

	if ($sorted) {
		$direction = ($sort_order == 'Up') ? 'ASC' : 'DESC';
		if ($order_results == '') $order_results = 'Email';
		$query .= " ORDER BY " . $order_results . " " . $direction;
		if ($order_results != 'Email') $query .= ", Email";
	}

	if (!empty($insert_table)) {

		// save the search criteria.. for statistical purposes.
		if ($insert_table['table'] == 'send_recipients') {
			$searchcriteria_query = "UPDATE " . $TABLEPREFIX . "sends SET SearchCriteria='" . addslashes(serialize($search_args)) . "' WHERE SendID='" . addslashes($insert_table['id']) . "'";
			mysql_query($searchcriteria_query);
		}

		if ($insert_table['orderby']) $query .= " ORDER BY " . $insert_table['orderby'];
		if (isset($insert_table['orderdirection'])) $query .= " " . $insert_table['orderdirection'];

		mysql_query($query);

		$count_query = "SELECT COUNT(*) FROM " . $TABLEPREFIX . $insert_table['table'] . " WHERE " . $insert_table['idname'] . "='".addslashes($insert_table['id'])."'";

		$count_result = mysql_query($count_query);
		return mysql_result($count_result, 0, 0);
	}

	if ($count_only) {
		$result = mysql_query($query);
		return mysql_result($result, 0, 0);
	}

	if (!empty($limit_results)) $query .= " LIMIT " . $limit_results['Start'] . ", " . $limit_results['Number'];

	$member_list = array();

	$result = mysql_query($query);
	while($row = mysql_fetch_assoc($result)) {
		$member_list[] = $row['MemberID'];
	}
	return $member_list;
} # end function returnmembers($ListID,$Email,$Status,$Confirmed,$Fields,$ClickedLink=0,$Format="ALL", $sorted=true, $SendID=0)

?>