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); }