function search($VAR)
{
### Read the contents of the /plugins/affiliate directory:
$count = 0;
chdir(PATH_PLUGINS . 'import');
$dir = opendir(PATH_PLUGINS . 'import');
while ($file_name = readdir($dir)) {
if ($file_name != '..' && $file_name != '.' && !preg_match("/^_/", $file_name) && preg_match("/.php\$/i", $file_name)) {
$count++;
}
}
# define the DB vars as a Smarty accessible block
global $smarty;
# create the search record:
if ($count > 0) {
# create the search record
include_once PATH_CORE . 'search.inc.php';
$search = new CORE_search();
$arr['module'] = $this->module;
$arr['sql'] = '';
$arr['limit'] = '999';
$arr['order_by'] = 'name';
$arr['results'] = $count;
$search->add($arr);
# define the search id and other parameters for Smarty
$smarty->assign('search_id', $search->id);
# page:
$smarty->assign('page', '1');
# limit:
$smarty->assign('limit', '999');
# order_by:
$smarty->assign('order_by', 'name');
# define the result count
$smarty->assign('results', $count);
}
}
function search($VAR)
{
$this->charge_construct();
$type = "search";
$this->method["{$type}"] = explode(",", $this->method["{$type}"]);
$db =& DB();
include_once PATH_CORE . 'validate.inc.php';
$validate = new CORE_validate();
# set the search criteria array
$arr = $VAR;
# loop through the submitted field_names to get the WHERE statement
$where_list = '';
$i = 0;
while (list($key, $value) = each($arr)) {
if ($i == 0) {
if ($value != '') {
$pat = "^" . $this->module . "_";
if (eregi($pat, $key)) {
$field = eregi_replace($pat, "", $key);
if (eregi('%', $value)) {
# do any data conversion for this field (date, encrypt, etc...)
if (isset($this->field["{$field}"]["convert"])) {
$value = $validate->convert($field, $value, $this->field["{$field}"]["convert"]);
}
$where_list .= " WHERE " . AGILE_DB_PREFIX . "charge." . $field . " LIKE " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
} else {
# check if array
if (is_array($value)) {
for ($i_arr = 0; $i_arr < count($value); $i_arr++) {
if ($value["{$i_arr}"] != '') {
# determine any field options (=, >, <, etc...)
$f_opt = '=';
$pat_field = $this->module . '_' . $field;
$VAR['field_option']["{$pat_field}"]["{$i_arr}"];
if (isset($VAR['field_option']["{$pat_field}"]["{$i_arr}"])) {
$f_opt = $VAR['field_option']["{$pat_field}"]["{$i_arr}"];
# error checking, safety precaution
if ($f_opt != '=' && $f_opt != '>' && $f_opt != '<' && $f_opt != '>=' && $f_opt != '<=' && $f_opt != '!=') {
$f_opt = '=';
}
}
# do any data conversion for this field (date, encrypt, etc...)
if (isset($this->field["{$field}"]["convert"])) {
$value["{$i_arr}"] = $validate->convert($field, $value["{$i_arr}"], $this->field["{$field}"]["convert"]);
}
if ($i_arr == 0) {
$where_list .= " WHERE " . AGILE_DB_PREFIX . "charge." . $field . " {$f_opt} " . $db->qstr($value["{$i_arr}"], get_magic_quotes_gpc());
$i++;
} else {
$where_list .= " AND " . AGILE_DB_PREFIX . "charge." . $field . " {$f_opt} " . $db->qstr($value["{$i_arr}"], get_magic_quotes_gpc());
$i++;
}
}
}
} else {
$where_list .= " WHERE " . AGILE_DB_PREFIX . "charge." . $field . " = " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
}
}
}
}
} else {
if ($value != '') {
$pat = "^" . $this->module . "_";
if (eregi($pat, $key)) {
$field = eregi_replace($pat, "", $key);
if (eregi('%', $value)) {
# do any data conversion for this field (date, encrypt, etc...)
if (isset($this->field["{$field}"]["convert"])) {
$value = $validate->convert($field, $value, $this->field["{$field}"]["convert"]);
}
$where_list .= " AND " . AGILE_DB_PREFIX . "charge." . $field . " LIKE " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
} else {
# check if array
if (is_array($value)) {
for ($i_arr = 0; $i_arr < count($value); $i_arr++) {
if ($value["{$i_arr}"] != '') {
# determine any field options (=, >, <, etc...)
$f_opt = '=';
$pat_field = $this->module . '_' . $field;
if (isset($VAR['field_option']["{$pat_field}"]["{$i_arr}"])) {
$f_opt = $VAR['field_option']["{$pat_field}"]["{$i_arr}"];
# error checking, safety precaution
if ($f_opt != '=' && $f_opt != '>' && $f_opt != '<' && $f_opt != '>=' && $f_opt != '<=' && $f_opt != '!=') {
$f_opt = '=';
}
}
# do any data conversion for this field (date, encrypt, etc...)
if (isset($this->field["{$field}"]["convert"])) {
$value["{$i_arr}"] = $validate->convert($field, $value["{$i_arr}"], $this->field["{$field}"]["convert"]);
}
$where_list .= " AND " . AGILE_DB_PREFIX . "charge." . $field . " {$f_opt} " . $db->qstr($value["{$i_arr}"], get_magic_quotes_gpc());
$i++;
}
}
} else {
$where_list .= " AND " . AGILE_DB_PREFIX . "charge." . $field . " = " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
}
}
}
}
}
}
# Code for attribute searches:
if (!empty($VAR['item_attributes'])) {
$attr_arr = $VAR['item_attributes'];
for ($ati = 0; $ati < count($attr_arr); $ati++) {
if (!empty($attr_arr[$ati]['0'])) {
if ($where_list == '') {
$where_list .= ' WHERE ';
} else {
$where_list .= ' AND ';
}
$where_list .= AGILE_DB_PREFIX . "charge.attributes LIKE " . $db->qstr("%{$attr_arr[$ati]['0']}=={$attr_arr[$ati]['1']}%");
}
}
}
#### finalize the WHERE statement
if ($where_list == '') {
$where_list .= ' WHERE ';
} else {
$where_list .= ' AND ';
}
# get limit type
if (isset($VAR['limit'])) {
$limit = $VAR['limit'];
} else {
$limit = $this->limit;
}
# get order by
if (isset($VAR['order_by'])) {
$order_by = $VAR['order_by'];
} else {
$order_by = $this->order_by;
}
$q = "SELECT " . AGILE_DB_PREFIX . "charge.id FROM " . AGILE_DB_PREFIX . "charge ";
$q .= $where_list . " " . AGILE_DB_PREFIX . "charge.site_id = " . $db->qstr(DEFAULT_SITE);
$q_save = "SELECT DISTINCT %%fieldList%%, " . AGILE_DB_PREFIX . "charge.id FROM " . AGILE_DB_PREFIX . "charge ";
$q_save .= $where_list . " %%whereList%% ";
#echo $q;
#exit;
# run the database query
$result = $db->Execute($q);
# error reporting
if ($result === false) {
global $C_debug;
$C_debug->error('charge.inc.php', 'search', $db->ErrorMsg());
return false;
}
# get the result count:
$results = $result->RecordCount();
# get the first record id:
if ($results == 1) {
$record_id = $result->fields['id'];
}
# define the DB vars as a Smarty accessible block
global $smarty;
# Create the definition for fast-forwarding to a single record:
if ($results == 1 && !isset($this->fast_forward)) {
$smarty->assign('record_id', $record_id);
}
# create the search record:
if ($results > 0) {
# create the search record
include_once PATH_CORE . 'search.inc.php';
$search = new CORE_search();
$arr['module'] = $this->module;
$arr['sql'] = $q_save;
$arr['limit'] = $limit;
$arr['order_by'] = $order_by;
$arr['results'] = $results;
$search->add($arr);
# define the search id and other parameters for Smarty
$smarty->assign('search_id', $search->id);
# page:
$smarty->assign('page', '1');
# limit:
$smarty->assign('limit', $limit);
# order_by:
$smarty->assign('order_by', $order_by);
}
# define the result count
$smarty->assign('results', $results);
}
/**
* AgileBill - Open Billing Software
*
* This body of work is free software; you can redistribute it and/or
* modify it under the terms of the Open AgileBill License
* License as published at http://www.agileco.com/agilebill/license1-4.txt
*
* For questions, help, comments, discussion, etc., please join the
* Agileco community forums at http://forum.agileco.com/
*
* @link http://www.agileco.com/
* @copyright 2004-2008 Agileco, LLC.
* @license http://www.agileco.com/agilebill/license1-4.txt
* @author Tony Landis <*****@*****.**>
* @package AgileBill
* @version 1.4.93
*/
function CORE_database_search($VAR, &$construct, $type)
{
$db =& DB();
include_once PATH_CORE . 'validate.inc.php';
$validate = new CORE_validate();
# set the search criteria array
$arr = $VAR;
# loop through the submitted field_names to get the WHERE statement
$where_list = '';
$i = 0;
while (list($key, $value) = each($arr)) {
if ($i == 0) {
if ($value != '') {
$pat = "^" . $construct->module . "_";
if (preg_match('/' . $pat . '/i', $key)) {
$field = preg_replace('/' . $pat . '/i', "", $key);
if (preg_match('/%/', $value)) {
# do any data conversion for this field (date, encrypt, etc...)
if (isset($construct->field["{$field}"]["convert"])) {
$value = $validate->convert($field, $value, $construct->field["{$field}"]["convert"]);
}
$where_list .= " WHERE " . $field . " LIKE " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
} else {
# check if array
if (is_array($value)) {
for ($i_arr = 0; $i_arr < count($value); $i_arr++) {
if ($value["{$i_arr}"] != '') {
# determine any field options (=, >, <, etc...)
$f_opt = '=';
$pat_field = $construct->module . '_' . $field;
$VAR['field_option']["{$pat_field}"]["{$i_arr}"];
if (isset($VAR['field_option']["{$pat_field}"]["{$i_arr}"])) {
$f_opt = $VAR['field_option']["{$pat_field}"]["{$i_arr}"];
# error checking, safety precaution
if ($f_opt != '=' && $f_opt != '>' && $f_opt != '<' && $f_opt != '>=' && $f_opt != '<=' && $f_opt != '!=') {
$f_opt = '=';
}
}
# do any data conversion for this field (date, encrypt, etc...)
if (isset($construct->field["{$field}"]["convert"])) {
$value["{$i_arr}"] = $validate->convert($field, $value["{$i_arr}"], $construct->field["{$field}"]["convert"]);
}
if ($i_arr == 0) {
$where_list .= " WHERE " . $field . " {$f_opt} " . $db->qstr($value["{$i_arr}"], get_magic_quotes_gpc());
$i++;
} else {
$where_list .= " AND " . $field . " {$f_opt} " . $db->qstr($value["{$i_arr}"], get_magic_quotes_gpc());
$i++;
}
}
}
} else {
$where_list .= " WHERE " . $field . " = " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
}
}
}
}
} else {
if ($value != '') {
$pat = "^" . $construct->module . "_";
if (preg_match('/' . $pat . '/', $key)) {
$field = preg_replace('/' . $pat . '/i', "", $key);
if (preg_match('/%/', $value)) {
# do any data conversion for this field (date, encrypt, etc...)
if (isset($construct->field["{$field}"]["convert"])) {
$value = $validate->convert($field, $value, $construct->field["{$field}"]["convert"]);
}
$where_list .= " AND " . $field . " LIKE " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
} else {
# check if array
if (is_array($value)) {
for ($i_arr = 0; $i_arr < count($value); $i_arr++) {
if ($value["{$i_arr}"] != '') {
# determine any field options (=, >, <, etc...)
$f_opt = '=';
$pat_field = $construct->module . '_' . $field;
if (isset($VAR['field_option']["{$pat_field}"]["{$i_arr}"])) {
$f_opt = $VAR['field_option']["{$pat_field}"]["{$i_arr}"];
# error checking, safety precaution
if ($f_opt != '=' && $f_opt != '>' && $f_opt != '<' && $f_opt != '>=' && $f_opt != '<=' && $f_opt != '!=') {
$f_opt = '=';
}
}
# do any data conversion for this field (date, encrypt, etc...)
if (isset($construct->field["{$field}"]["convert"])) {
$value["{$i_arr}"] = $validate->convert($field, $value["{$i_arr}"], $construct->field["{$field}"]["convert"]);
}
$where_list .= " AND " . $field . " {$f_opt} " . $db->qstr($value["{$i_arr}"], get_magic_quotes_gpc());
$i++;
}
}
} else {
$where_list .= " AND " . $field . " = " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
}
}
}
}
}
}
#### finalize the WHERE statement
if ($where_list == '') {
$where_list .= ' WHERE ';
} else {
$where_list .= ' AND ';
}
# get limit type
if (isset($VAR['limit'])) {
$limit = $VAR['limit'];
} else {
$limit = $construct->limit;
}
# get order by
if (isset($VAR['order_by'])) {
$order_by = $VAR['order_by'];
} else {
$order_by = $construct->order_by;
}
### Get any addition fields to select:
if (isset($construct->custom_EXP)) {
for ($ei = 0; $ei < count($construct->custom_EXP); $ei++) {
if ($ei == 0) {
$field_list = "," . $construct->custom_EXP[$ei]['field'];
}
}
}
# generate the full query
$q = "SELECT id" . $field_list . " FROM\n\t\t " . AGILE_DB_PREFIX . "{$construct->table}\n\t\t {$where_list}\n\t\t site_id = '" . DEFAULT_SITE . "'";
$q_save = "SELECT %%fieldList%% FROM %%tableList%% " . $where_list . " %%whereList%% ";
$result = $db->Execute($q);
//////////////// DEBUG ////
#echo "<PRE>$q</PRE>";
#exit;
# error reporting
if ($result === false) {
global $C_debug;
$C_debug->error('database.inc.php', 'search', $db->ErrorMsg());
if (isset($construct->trigger["{$type}"])) {
include_once PATH_CORE . 'trigger.inc.php';
$trigger = new CORE_trigger();
$trigger->trigger($construct->trigger["{$type}"], 0, $VAR);
}
return;
}
# get the result count:
$results = $result->RecordCount();
# get the first record id:
if ($results == 1) {
$record_id = $result->fields['id'];
}
### Run any custom validation on this result for
### this module
if (isset($construct->custom_EXP)) {
$results = 0;
while (!$result->EOF) {
for ($ei = 0; $ei < count($construct->custom_EXP); $ei++) {
$field = $construct->custom_EXP[$ei]["field"];
$value = $construct->custom_EXP[$ei]["value"];
if ($result->fields["{$field}"] == $value) {
//$result->MoveNext();
$ei = count($construct->custom_EXP);
$results++;
}
}
$result->MoveNext();
}
}
# define the DB vars as a Smarty accessible block
global $smarty;
# Create the definition for fast-forwarding to a single record:
if ($results == 1 && !isset($construct->fast_forward)) {
$smarty->assign('record_id', $record_id);
}
# create the search record:
if ($results > 0) {
# create the search record
include_once PATH_CORE . 'search.inc.php';
$search = new CORE_search();
$arr['module'] = $construct->module;
$arr['sql'] = $q_save;
$arr['limit'] = $limit;
$arr['order_by'] = $order_by;
$arr['results'] = $results;
$search->add($arr);
# define the search id and other parameters for Smarty
$smarty->assign('search_id', $search->id);
# page:
$smarty->assign('page', '1');
# limit:
$smarty->assign('limit', $limit);
# order_by:
$smarty->assign('order_by', $order_by);
}
# define the result count
$smarty->assign('results', $results);
if (isset($construct->trigger["{$type}"])) {
include_once PATH_CORE . 'trigger.inc.php';
$trigger = new CORE_trigger();
$trigger->trigger($construct->trigger["{$type}"], 1, $VAR);
}
}
function search($VAR)
{
$type = "search";
$this->method["{$type}"] = explode(",", $this->method["{$type}"]);
$db =& DB();
include_once PATH_CORE . 'validate.inc.php';
$validate = new CORE_validate();
# set the search criteria array
$arr = $VAR;
# loop through the submitted field_names to get the WHERE statement
$where_list = '';
$i = 0;
while (list($key, $value) = each($arr)) {
if ($i == 0) {
if ($value != '') {
$pat = "^" . $this->module . "_";
if (preg_match('/' . $pat . '/i', $key)) {
$field = preg_replace('/' . $pat . '/i', "", $key);
if (preg_match('/%/', $value)) {
# do any data conversion for this field (date, encrypt, etc...)
if (isset($this->field["{$field}"]["convert"])) {
$value = $validate->convert($field, $value, $this->field["{$field}"]["convert"]);
}
$where_list .= " WHERE " . AGILE_DB_PREFIX . "account." . $field . " LIKE " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
} else {
# check if array
if (is_array($value)) {
for ($i_arr = 0; $i_arr < count($value); $i_arr++) {
if ($value["{$i_arr}"] != '') {
# determine any field options (=, >, <, etc...)
$f_opt = '=';
$pat_field = $this->module . '_' . $field;
$VAR['field_option']["{$pat_field}"]["{$i_arr}"];
if (isset($VAR['field_option']["{$pat_field}"]["{$i_arr}"])) {
$f_opt = $VAR['field_option']["{$pat_field}"]["{$i_arr}"];
# error checking, safety precaution
if ($f_opt != '=' && $f_opt != '>' && $f_opt != '<' && $f_opt != '>=' && $f_opt != '<=' && $f_opt != '!=') {
$f_opt = '=';
}
}
# do any data conversion for this field (date, encrypt, etc...)
if (isset($this->field["{$field}"]["convert"])) {
$value["{$i_arr}"] = $validate->convert($field, $value["{$i_arr}"], $this->field["{$field}"]["convert"]);
}
if ($i_arr == 0) {
$where_list .= " WHERE " . AGILE_DB_PREFIX . "account." . $field . " {$f_opt} " . $db->qstr($value["{$i_arr}"], get_magic_quotes_gpc());
$i++;
} else {
$where_list .= " AND " . AGILE_DB_PREFIX . "account." . $field . " {$f_opt} " . $db->qstr($value["{$i_arr}"], get_magic_quotes_gpc());
$i++;
}
}
}
} else {
$where_list .= " WHERE " . AGILE_DB_PREFIX . "account." . $field . " = " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
}
}
}
}
} else {
if ($value != '') {
$pat = "^" . $this->module . "_";
if (preg_match('/' . $pat . '/i', $key)) {
$field = preg_replace('/' . $pat . '/i', "", $key);
if (preg_match('/%/', $value)) {
# do any data conversion for this field (date, encrypt, etc...)
if (isset($this->field["{$field}"]["convert"])) {
$value = $validate->convert($field, $value, $this->field["{$field}"]["convert"]);
}
$where_list .= " AND " . AGILE_DB_PREFIX . "account." . $field . " LIKE " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
} else {
# check if array
if (is_array($value)) {
for ($i_arr = 0; $i_arr < count($value); $i_arr++) {
if ($value["{$i_arr}"] != '') {
# determine any field options (=, >, <, etc...)
$f_opt = '=';
$pat_field = $this->module . '_' . $field;
if (isset($VAR['field_option']["{$pat_field}"]["{$i_arr}"])) {
$f_opt = $VAR['field_option']["{$pat_field}"]["{$i_arr}"];
# error checking, safety precaution
if ($f_opt != '=' && $f_opt != '>' && $f_opt != '<' && $f_opt != '>=' && $f_opt != '<=' && $f_opt != '!=') {
$f_opt = '=';
}
}
# do any data conversion for this field (date, encrypt, etc...)
if (isset($this->field["{$field}"]["convert"])) {
$value["{$i_arr}"] = $validate->convert($field, $value["{$i_arr}"], $this->field["{$field}"]["convert"]);
}
$where_list .= " AND " . AGILE_DB_PREFIX . "account." . $field . " {$f_opt} " . $db->qstr($value["{$i_arr}"], get_magic_quotes_gpc());
$i++;
}
}
} else {
$where_list .= " AND " . AGILE_DB_PREFIX . "account." . $field . " = " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
}
}
}
}
}
}
#### finalize the WHERE statement
if ($where_list == '') {
$where_list .= ' WHERE ';
} else {
$where_list .= ' AND ';
}
# get limit type
if (isset($VAR['limit'])) {
$limit = $VAR['limit'];
} else {
$limit = $this->limit;
}
# get order by
if (isset($VAR['order_by'])) {
$order_by = $VAR['order_by'];
} else {
$order_by = $this->order_by;
}
$pre = AGILE_DB_PREFIX;
$q = "SELECT DISTINCT " . AGILE_DB_PREFIX . "account.id," . AGILE_DB_PREFIX . "account.last_name," . AGILE_DB_PREFIX . "account.first_name," . AGILE_DB_PREFIX . "account.username FROM " . AGILE_DB_PREFIX . "account ";
$q_save = "SELECT DISTINCT %%fieldList%% FROM " . AGILE_DB_PREFIX . "account ";
# Code for group searches:
if (!empty($VAR['account_group'])) {
$q .= " LEFT JOIN " . AGILE_DB_PREFIX . "account_group ON " . AGILE_DB_PREFIX . "account_group.account_id = " . AGILE_DB_PREFIX . "account.id";
}
######## GET ANY STATIC VARS TO SEARCH ##########
$join_list = '';
if (!empty($VAR["static_relation"]) && count($VAR["static_relation"] > 0)) {
while (list($idx, $value) = each($VAR["static_relation"])) {
if ($value != "") {
$join_list .= " INNER JOIN {$pre}static_var_record AS s{$idx} ON \n\t\t\t\t\t\t( \n\t\t\t\t\t\t\ts{$idx}.record_id = {$pre}{$this->table}.id\n\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\ts{$idx}.static_var_relation_id = '{$idx}'\n\t\t\t\t\t\t\tAND\n\t\t\t\t\t\t\ts{$idx}.site_id = " . $db->qstr(DEFAULT_SITE) . "\t\t \t\t\t\t\n\t\t\t\t\t\t\tAND";
if (preg_match("/%/", $value)) {
$join_list .= " s{$idx}.value LIKE " . $db->qstr($VAR["static_relation"]["{$idx}"]);
} else {
$join_list .= " s{$idx}.value = " . $db->qstr($VAR["static_relation"]["{$idx}"]);
}
$join_list .= " ) ";
}
}
}
######## END STATIC VAR SEARCH ##################
# standard where list
$q .= $join_list . $where_list . " " . AGILE_DB_PREFIX . "account.site_id = " . $db->qstr(DEFAULT_SITE);
# Code for member group:
if (!empty($VAR['account_group'])) {
$q .= " AND " . AGILE_DB_PREFIX . "account_group.group_id = " . $db->qstr($VAR['account_group']) . "\n\t\t\t\t\tAND " . AGILE_DB_PREFIX . "account_group.site_id = " . $db->qstr(DEFAULT_SITE);
}
if (!empty($VAR['account_group'])) {
$q_save .= " LEFT JOIN " . AGILE_DB_PREFIX . "account_group ON " . AGILE_DB_PREFIX . "account_group.account_id = " . AGILE_DB_PREFIX . "account.id ";
if (!empty($join_list)) {
$q_save .= $join_list;
}
$q_save .= $where_list . " %%whereList%% ";
$q_save .= AGILE_DB_PREFIX . "account_group.group_id = " . $db->qstr($VAR['account_group']) . " AND ";
} else {
if (!empty($join_list)) {
$q_save .= $join_list;
}
$q_save .= $where_list . " %%whereList%% ";
}
################## DEBUG ##################
#echo "<pre>" . $q;
#echo "<BR><BR>" . $q_save;
#exit;
# run the database query
$result = $db->Execute($q);
# error reporting
if ($result === false) {
global $C_debug;
$C_debug->error('database.inc.php', 'search', $db->ErrorMsg());
return false;
}
# get the result count:
$results = $result->RecordCount();
# get the first record id:
if ($results == 1) {
$record_id = $result->fields['id'];
}
# define the DB vars as a Smarty accessible block
global $smarty;
# Create the definition for fast-forwarding to a single record:
if ($results == 1 && !isset($this->fast_forward)) {
$smarty->assign('record_id', $record_id);
}
# create the search record:
if ($results > 0) {
# create the search record
include_once PATH_CORE . 'search.inc.php';
$search = new CORE_search();
$arr['module'] = $this->module;
$arr['sql'] = $q_save;
$arr['limit'] = $limit;
$arr['order_by'] = $order_by;
$arr['results'] = $results;
$search->add($arr);
# define the search id and other parameters for Smarty
$smarty->assign('search_id', $search->id);
# page:
$smarty->assign('page', '1');
# limit:
$smarty->assign('limit', $limit);
# order_by:
$smarty->assign('order_by', $order_by);
}
# define the result count
$smarty->assign('results', $results);
}
function search($VAR)
{
$this->construct();
### Get the departments this staff member is authorized for:
$dbs =& DB();
$sql = 'SELECT id, department_avail FROM ' . AGILE_DB_PREFIX . 'staff WHERE
site_id = ' . $dbs->qstr(DEFAULT_SITE) . ' AND
account_id = ' . $dbs->qstr(SESS_ACCOUNT);
$result = $dbs->Execute($sql);
if ($result->RecordCount() == 0) {
### ERROR: this account does not have a staff id
global $C_debug, $C_translate;
$C_debug->alert($C_translate->translate('staff_no_account', 'ticket', ''));
return;
} else {
$staff_id = $result->fields['id'];
@($avail = unserialize($result->fields['department_avail']));
}
$type = "search";
$this->method["{$type}"] = split(",", $this->method["{$type}"]);
$db =& DB();
include_once PATH_CORE . 'validate.inc.php';
$validate = new CORE_validate();
# set the search criteria array
$arr = $VAR;
# loop through the submitted field_names to get the WHERE statement
$where_list = '';
$i = 0;
while (list($key, $value) = each($arr)) {
if ($i == 0) {
if ($value != '') {
$pat = "^" . $this->module . "_";
if (eregi($pat, $key)) {
$field = eregi_replace($pat, "", $key);
if (eregi('%', $value)) {
# do any data conversion for this field (date, encrypt, etc...)
if (isset($this->field["{$field}"]["convert"])) {
$value = $validate->convert($field, $value, $this->field["{$field}"]["convert"]);
}
$where_list .= " WHERE " . AGILE_DB_PREFIX . "ticket." . $field . " LIKE " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
} else {
# check if array
if (is_array($value)) {
for ($i_arr = 0; $i_arr < count($value); $i_arr++) {
if ($value["{$i_arr}"] != '') {
# determine any field options (=, >, <, etc...)
$f_opt = '=';
$pat_field = $this->module . '_' . $field;
$VAR['field_option']["{$pat_field}"]["{$i_arr}"];
if (isset($VAR['field_option']["{$pat_field}"]["{$i_arr}"])) {
$f_opt = $VAR['field_option']["{$pat_field}"]["{$i_arr}"];
# error checking, safety precaution
if ($f_opt != '=' && $f_opt != '>' && $f_opt != '<' && $f_opt != '>=' && $f_opt != '<=' && $f_opt != '!=') {
$f_opt = '=';
}
}
# do any data conversion for this field (date, encrypt, etc...)
if (isset($this->field["{$field}"]["convert"])) {
$value["{$i_arr}"] = $validate->convert($field, $value["{$i_arr}"], $this->field["{$field}"]["convert"]);
}
if ($i_arr == 0) {
$where_list .= " WHERE " . AGILE_DB_PREFIX . "ticket." . $field . " {$f_opt} " . $db->qstr($value["{$i_arr}"], get_magic_quotes_gpc());
$i++;
} else {
$where_list .= " AND " . AGILE_DB_PREFIX . "ticket." . $field . " {$f_opt} " . $db->qstr($value["{$i_arr}"], get_magic_quotes_gpc());
$i++;
}
}
}
} else {
$where_list .= " WHERE " . AGILE_DB_PREFIX . "ticket." . $field . " = " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
}
}
}
}
} else {
if ($value != '') {
$pat = "^" . $this->module . "_";
if (eregi($pat, $key)) {
$field = eregi_replace($pat, "", $key);
if (eregi('%', $value)) {
# do any data conversion for this field (date, encrypt, etc...)
if (isset($this->field["{$field}"]["convert"])) {
$value = $validate->convert($field, $value, $this->field["{$field}"]["convert"]);
}
$where_list .= " AND " . AGILE_DB_PREFIX . "ticket." . $field . " LIKE " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
} else {
# check if array
if (is_array($value)) {
for ($i_arr = 0; $i_arr < count($value); $i_arr++) {
if ($value["{$i_arr}"] != '') {
# determine any field options (=, >, <, etc...)
$f_opt = '=';
$pat_field = $this->module . '_' . $field;
if (isset($VAR['field_option']["{$pat_field}"]["{$i_arr}"])) {
$f_opt = $VAR['field_option']["{$pat_field}"]["{$i_arr}"];
# error checking, safety precaution
if ($f_opt != '=' && $f_opt != '>' && $f_opt != '<' && $f_opt != '>=' && $f_opt != '<=' && $f_opt != '!=') {
$f_opt = '=';
}
}
# do any data conversion for this field (date, encrypt, etc...)
if (isset($this->field["{$field}"]["convert"])) {
$value["{$i_arr}"] = $validate->convert($field, $value["{$i_arr}"], $this->field["{$field}"]["convert"]);
}
$where_list .= " AND " . AGILE_DB_PREFIX . "ticket." . $field . " {$f_opt} " . $db->qstr($value["{$i_arr}"], get_magic_quotes_gpc());
$i++;
}
}
} else {
$where_list .= " AND " . AGILE_DB_PREFIX . "ticket." . $field . " = " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
}
}
}
}
}
}
#### finalize the WHERE statement
if ($where_list == '') {
$where_list .= ' WHERE ';
} else {
$where_list .= ' AND ';
}
# get limit type
if (isset($VAR['limit'])) {
$limit = $VAR['limit'];
} else {
$limit = $this->limit;
}
# get order by
if (isset($VAR['order_by'])) {
$order_by = $VAR['order_by'];
} else {
$order_by = $this->order_by;
}
$pre = AGILE_DB_PREFIX;
# Dept ID;
if (!empty($staff_id) && count($avail) > 0) {
$where_list .= ' ( ';
### Loop through the records to define the custom SQL:
for ($i = 0; $i < count($avail); $i++) {
if ($i > 0) {
$where_list .= " OR ";
}
$where_list .= " {$pre}ticket.department_id = " . $db->qstr($avail[$i]);
}
$where_list .= ' ) AND ';
}
$q = "SELECT DISTINCT " . AGILE_DB_PREFIX . "ticket.id FROM " . AGILE_DB_PREFIX . "ticket ";
$q_save = "SELECT DISTINCT %%fieldList%% FROM " . AGILE_DB_PREFIX . "ticket ";
######## GET ANY STATIC VARS TO SEARCH ##########
$join_list = '';
if (!empty($VAR["static_relation"]) && count($VAR["static_relation"] > 0)) {
while (list($idx, $value) = each($VAR["static_relation"])) {
if ($value != "") {
$join_list .= " INNER JOIN {$pre}static_var_record AS s{$idx} ON\n\t\t \t\t\t( \n\t\t \t\t\t\ts{$idx}.record_id = {$pre}{$this->table}.id\n\t\t \t\t\t\tAND\n\t\t \t\t\t\ts{$idx}.static_var_relation_id = '{$idx}'\n\t\t \t\t\t\tAND\n\t\t \t\t\t\ts{$idx}.site_id = " . $db->qstr(DEFAULT_SITE) . "\t\t \t\t\t\t\n\t\t \t\t\t\tAND";
if (ereg("%", $value)) {
$join_list .= " s{$idx}.value LIKE " . $db->qstr($VAR["static_relation"]["{$idx}"]);
} else {
$join_list .= " s{$idx}.value = " . $db->qstr($VAR["static_relation"]["{$idx}"]);
}
$join_list .= " ) ";
}
}
}
######## END STATIC VAR SEARCH ##################
# standard where list
$q .= $join_list . $where_list . " " . AGILE_DB_PREFIX . "ticket.site_id = " . $db->qstr(DEFAULT_SITE);
$q_save .= $join_list . $where_list . " %%whereList%% ";
################## DEBUG ##################
#echo "<pre>" . $q;
#echo "<BR><BR>" . $q_save;
#exit;
# run the database query
$result = $db->Execute($q);
# error reporting
if ($result === false) {
global $C_debug;
$C_debug->error('ticket.inc.php', 'search', $db->ErrorMsg());
return false;
}
# get the result count:
$results = $result->RecordCount();
# get the first record id:
if ($results == 1) {
$record_id = $result->fields['id'];
}
# define the DB vars as a Smarty accessible block
global $smarty;
# Create the definition for fast-forwarding to a single record:
if ($results == 1 && !isset($this->fast_forward)) {
$smarty->assign('record_id', $record_id);
}
# create the search record:
if ($results > 0) {
# create the search record
include_once PATH_CORE . 'search.inc.php';
$search = new CORE_search();
$arr['module'] = $this->module;
$arr['sql'] = $q_save;
$arr['limit'] = $limit;
$arr['order_by'] = $order_by;
$arr['results'] = $results;
$search->add($arr);
# define the search id and other parameters for Smarty
$smarty->assign('search_id', $search->id);
# page:
$smarty->assign('page', '1');
# limit:
$smarty->assign('limit', $limit);
# order_by:
$smarty->assign('order_by', $order_by);
}
# define the result count
$smarty->assign('results', $results);
}
/** SEARCH
*/
function search($VAR)
{
$this->invoice_construct();
$type = "search";
$this->method["{$type}"] = explode(",", $this->method["{$type}"]);
$db =& DB();
include_once PATH_CORE . 'validate.inc.php';
$validate = new CORE_validate();
# set the search criteria array
$arr = $VAR;
# convert invoice_discount_arr
if (!empty($VAR['invoice_discount_arr'])) {
$arr['invoice_discount_arr'] = '%"' . $VAR['invoice_discount_arr'] . '"%';
}
# loop through the submitted field_names to get the WHERE statement
$where_list = '';
$i = 0;
while (list($key, $value) = each($arr)) {
if ($i == 0) {
if ($value != '') {
$pat = "^" . $this->module . "_";
if (preg_match('/' . $pat . '/', $key)) {
$field = preg_replace('/' . $pat . '/', "", $key);
if (preg_match('/%/', $value)) {
# do any data conversion for this field (date, encrypt, etc...)
if (isset($this->field["{$field}"]["convert"]) && $this->field["{$field}"]["convert"] != 'array') {
$value = $validate->convert($field, $value, $this->field["{$field}"]["convert"]);
}
$where_list .= " WHERE " . AGILE_DB_PREFIX . "invoice." . $field . " LIKE " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
} else {
# check if array
if (is_array($value)) {
for ($i_arr = 0; $i_arr < count($value); $i_arr++) {
if ($value["{$i_arr}"] != '') {
# determine any field options (=, >, <, etc...)
$f_opt = '=';
$pat_field = $this->module . '_' . $field;
$VAR['field_option']["{$pat_field}"]["{$i_arr}"];
if (isset($VAR['field_option']["{$pat_field}"]["{$i_arr}"])) {
$f_opt = $VAR['field_option']["{$pat_field}"]["{$i_arr}"];
# error checking, safety precaution
if ($f_opt != '=' && $f_opt != '>' && $f_opt != '<' && $f_opt != '>=' && $f_opt != '<=' && $f_opt != '!=') {
$f_opt = '=';
}
}
# do any data conversion for this field (date, encrypt, etc...)
if (isset($this->field["{$field}"]["convert"]) && $this->field["{$field}"]["convert"] != 'array') {
$value["{$i_arr}"] = $validate->convert($field, $value["{$i_arr}"], $this->field["{$field}"]["convert"]);
}
if ($i_arr == 0) {
$where_list .= " WHERE " . AGILE_DB_PREFIX . "invoice." . $field . " {$f_opt} " . $db->qstr($value["{$i_arr}"], get_magic_quotes_gpc());
$i++;
} else {
$where_list .= " AND " . AGILE_DB_PREFIX . "invoice." . $field . " {$f_opt} " . $db->qstr($value["{$i_arr}"], get_magic_quotes_gpc());
$i++;
}
}
}
} else {
$where_list .= " WHERE " . AGILE_DB_PREFIX . "invoice." . $field . " = " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
}
}
}
}
} else {
if ($value != '') {
$pat = "^" . $this->module . "_";
if (preg_match('/' . $pat . '/', $key)) {
$field = preg_replace('/' . $pat . '/', "", $key);
if (preg_match('/%/', $value)) {
# do any data conversion for this field (date, encrypt, etc...)
if (isset($this->field["{$field}"]["convert"]) && $this->field["{$field}"]["convert"] != 'array') {
$value = $validate->convert($field, $value, $this->field["{$field}"]["convert"]);
}
$where_list .= " AND " . AGILE_DB_PREFIX . "invoice." . $field . " LIKE " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
} else {
# check if array
if (is_array($value)) {
for ($i_arr = 0; $i_arr < count($value); $i_arr++) {
if ($value["{$i_arr}"] != '') {
# determine any field options (=, >, <, etc...)
$f_opt = '=';
$pat_field = $this->module . '_' . $field;
if (isset($VAR['field_option']["{$pat_field}"]["{$i_arr}"])) {
$f_opt = $VAR['field_option']["{$pat_field}"]["{$i_arr}"];
# error checking, safety precaution
if ($f_opt != '=' && $f_opt != '>' && $f_opt != '<' && $f_opt != '>=' && $f_opt != '<=' && $f_opt != '!=') {
$f_opt = '=';
}
}
# do any data conversion for this field (date, encrypt, etc...)
if (isset($this->field["{$field}"]["convert"]) && $this->field["{$field}"]["convert"] != 'array') {
$value["{$i_arr}"] = $validate->convert($field, $value["{$i_arr}"], $this->field["{$field}"]["convert"]);
}
$where_list .= " AND " . AGILE_DB_PREFIX . "invoice." . $field . " {$f_opt} " . $db->qstr($value["{$i_arr}"], get_magic_quotes_gpc());
$i++;
}
}
} else {
$where_list .= " AND " . AGILE_DB_PREFIX . "invoice." . $field . " = " . $db->qstr($value, get_magic_quotes_gpc());
$i++;
}
}
}
}
}
}
# Code for attribute searches:
if (!empty($VAR['join_product_id']) && !empty($VAR['item_attributes'])) {
$attr_arr = $VAR['item_attributes'];
for ($ati = 0; $ati < count($attr_arr); $ati++) {
if (!empty($attr_arr[$ati]['0'])) {
if ($where_list == '') {
$where_list .= ' WHERE ';
} else {
$where_list .= ' AND ';
}
$where_list .= AGILE_DB_PREFIX . "invoice_item.product_attr LIKE " . $db->qstr("%{$attr_arr[$ati]['0']}=={$attr_arr[$ati]['1']}%");
}
}
}
# get limit type
if (isset($VAR['limit'])) {
$limit = $VAR['limit'];
} else {
$limit = $this->limit;
}
# get order by
if (isset($VAR['order_by'])) {
$order_by = $VAR['order_by'];
} else {
$order_by = $this->order_by;
}
## SELECT FROM
$p = AGILE_DB_PREFIX;
$q = "SELECT DISTINCT {$p}invoice.id FROM " . AGILE_DB_PREFIX . "invoice ";
$q_save = "SELECT DISTINCT %%fieldList%%,{$p}invoice.id FROM {$p}invoice ";
## LEFT JOIN
if (!empty($VAR['join_product_id']) || !empty($VAR['join_service_id']) || !empty($VAR['join_domain_name']) || !empty($VAR['join_domain_tld']) || !empty($VAR['join_memo_text'])) {
# JOIN ON PRODUCT DETAILS:
if (!empty($VAR['join_product_id']) || !empty($VAR['join_service_id']) || !empty($VAR['join_domain_name']) || !empty($VAR['join_domain_tld'])) {
$q .= " LEFT JOIN {$p}invoice_item ON {$p}invoice_item.invoice_id = {$p}invoice.id";
$q_save .= " LEFT JOIN {$p}invoice_item ON {$p}invoice_item.invoice_id = {$p}invoice.id";
if ($where_list == '') {
$q .= " WHERE {$p}invoice_item.site_id = " . $db->qstr(DEFAULT_SITE);
$q_save .= " WHERE {$p}invoice_item.site_id = " . $db->qstr(DEFAULT_SITE);
} else {
$q .= $where_list . " AND {$p}invoice_item.site_id = " . $db->qstr(DEFAULT_SITE);
$q_save .= $where_list . " AND {$p}invoice_item.site_id = " . $db->qstr(DEFAULT_SITE);
}
# AND (invoice_item.product_id)
if (!empty($VAR['join_product_id'])) {
$q .= " AND {$p}invoice_item.product_id = " . $db->qstr($VAR['join_product_id']);
$q_save .= " AND {$p}invoice_item.product_id = " . $db->qstr($VAR['join_product_id']);
}
# AND (invoice_item.service_id)
if (!empty($VAR['join_service_id'])) {
$q .= " AND {$p}invoice_item.service_id = " . $db->qstr($VAR['join_service_id']);
$q_save .= " AND {$p}invoice_item.service_id = " . $db->qstr($VAR['join_service_id']);
}
# AND (invoice_item.domain_name)
if (!empty($VAR['join_domain_name'])) {
if (!preg_match('/%/', $VAR['join_domain_name'])) {
$qtype = ' = ';
} else {
$qtype = ' LIKE ';
}
$q .= " AND {$p}invoice_item.domain_name {$qtype} " . $db->qstr($VAR['join_domain_name']);
$q_save .= " AND {$p}invoice_item.domain_name {$qtype} " . $db->qstr($VAR['join_domain_name']);
}
# AND (invoice_item.domain_tld)
if (!empty($VAR['join_domain_tld'])) {
if (!preg_match('/%/', $VAR['join_domain_tld'])) {
$qtype = ' = ';
} else {
$qtype = ' LIKE ';
}
$q .= " AND {$p}invoice_item.domain_tld {$qtype} " . $db->qstr($VAR['join_domain_tld']);
$q_save .= " AND {$p}invoice_item.domain_tld {$qtype} " . $db->qstr($VAR['join_domain_tld']);
}
}
# JOIN ON MEMO TEXT:
if (!empty($VAR['join_memo_text'])) {
$q .= " LEFT JOIN {$p}invoice_memo ON {$p}invoice_memo.invoice_id = {$p}invoice.id";
$q_save .= " LEFT JOIN {$p}invoice_memo ON {$p}invoice_memo.invoice_id = {$p}invoice.id";
if ($where_list == '') {
$q .= " WHERE {$p}invoice_memo.site_id = " . $db->qstr(DEFAULT_SITE);
$q_save .= " WHERE {$p}invoice_memo.site_id = " . $db->qstr(DEFAULT_SITE);
} else {
$q .= $where_list . " AND {$p}invoice_memo.site_id = " . $db->qstr(DEFAULT_SITE);
$q_save .= $where_list . " AND {$p}invoice_memo.site_id = " . $db->qstr(DEFAULT_SITE);
}
$q .= " AND {$p}invoice_memo.memo LIKE " . $db->qstr('%' . $VAR['join_memo_text'] . '%');
$q_save .= " AND {$p}invoice_memo.memo LIKE " . $db->qstr('%' . $VAR['join_memo_text'] . '%');
}
$q .= " AND {$p}invoice.site_id = " . DEFAULT_SITE;
$q_save .= ' AND ';
} else {
if ($where_list == '') {
$q .= "WHERE {$p}invoice.site_id = " . DEFAULT_SITE;
$q_save .= ' WHERE ';
} else {
$q .= $where_list . " AND {$p}invoice.site_id = " . DEFAULT_SITE;
$q_save .= $where_list . ' AND ';
}
}
///////////////// debug
#echo $q;
#exit;
# run the database query
$result = $db->Execute($q);
# error reporting
if ($result === false) {
global $C_debug;
$C_debug->error('invoice.inc.php', 'search', $db->ErrorMsg());
return false;
}
# get the result count:
$results = $result->RecordCount();
# get the first record id:
if ($results == 1) {
$record_id = $result->fields['id'];
}
# define the DB vars as a Smarty accessible block
global $smarty;
# Create the definition for fast-forwarding to a single record:
if ($results == 1 && !isset($this->fast_forward)) {
$smarty->assign('record_id', $record_id);
}
# create the search record:
if ($results > 0) {
# create the search record
include_once PATH_CORE . 'search.inc.php';
$search = new CORE_search();
$arr['module'] = $this->module;
$arr['sql'] = $q_save;
$arr['limit'] = $limit;
$arr['order_by'] = $order_by;
$arr['results'] = $results;
$search->add($arr);
# define the search id and other parameters for Smarty
$smarty->assign('search_id', $search->id);
# page:
$smarty->assign('page', '1');
# limit:
$smarty->assign('limit', $limit);
# order_by:
$smarty->assign('order_by', $order_by);
}
# define the result count
$smarty->assign('results', $results);
}
