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);
}
}
/**
* 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_add($VAR, $construct, $type)
{
global $C_translate;
# set the field list for this method:
$arr = $construct->method["{$type}"];
# define the validation class
include_once PATH_CORE . 'validate.inc.php';
$validate = new CORE_validate();
$construct->validated = true;
####################################################################
# loop through the field list to validate the required fields
####################################################################
while (list($key, $value) = each($arr)) {
# get the field value
$field_var = $construct->module . '_' . $value;
$field_name = $value;
$construct->validate = true;
####################################################################
# perform any field validation...
####################################################################
# check if this value is unique
if (isset($construct->field["{$value}"]["unique"]) && isset($VAR["{$field_var}"])) {
if (!$validate->validate_unique($construct->table, $field_name, "record_id", $VAR["{$field_var}"])) {
$construct->validated = false;
$construct->val_error[] = array('field' => $construct->table . '_' . $field_name, 'field_trans' => $C_translate->translate('field_' . $field_name, $construct->module, ""), 'error' => $C_translate->translate('validate_unique', "", ""));
}
}
# check if the submitted value meets the specifed requirements
if (isset($construct->field["{$value}"]["validate"])) {
if (isset($VAR["{$field_var}"])) {
if ($VAR["{$field_var}"] != '') {
if (!$validate->validate($field_name, $construct->field["{$value}"], $VAR["{$field_var}"], $construct->field["{$value}"]["validate"])) {
$construct->validated = false;
$construct->val_error[] = array('field' => $construct->module . '_' . $field_name, 'field_trans' => $C_translate->translate('field_' . $field_name, $construct->module, ""), 'error' => $validate->error["{$field_name}"]);
}
} else {
$construct->validated = false;
$construct->val_error[] = array('field' => $construct->module . '_' . $field_name, 'field_trans' => $C_translate->translate('field_' . $field_name, $construct->module, ""), 'error' => $C_translate->translate('validate_any', "", ""));
}
} else {
$construct->validated = false;
$construct->val_error[] = array('field' => $construct->module . '_' . $field_name, 'field_trans' => $C_translate->translate('field_' . $field_name, $construct->module, ""), 'error' => $C_translate->translate('validate_any', "", ""));
}
}
}
####################################################################
# If validation was failed, skip the db insert &
# set the errors & origonal fields as Smarty objects,
# and change the page to be loaded.
####################################################################
if (!$construct->validated) {
global $smarty;
# set the errors as a Smarty Object
$smarty->assign('form_validation', $construct->val_error);
# set the page to be loaded
if (!defined("FORCE_PAGE")) {
define('FORCE_PAGE', $VAR['_page_current']);
}
# define any triggers
if (isset($construct->trigger["{$type}"])) {
include_once PATH_CORE . 'trigger.inc.php';
$trigger = new CORE_trigger();
$trigger->trigger($construct->trigger["{$type}"], 0, $VAR);
}
# strip slashes
global $C_vars;
$C_vars->strip_slashes_all();
return false;
} else {
# begin the new database class:
$db =& DB();
# loop through the field list to create the sql queries
$field_list = '';
$i = 0;
reset($arr);
while (list($key, $value) = each($arr)) {
# get the field value
$field_var = $construct->module . '_' . $value;
$field_name = $value;
if (isset($VAR["{$field_var}"])) {
# check if html allowed:
if (@$construct->field["{$value}"]["html"] != 1 && !is_array($VAR["{$field_var}"])) {
$insert_value = htmlspecialchars($VAR["{$field_var}"]);
} else {
$insert_value = $VAR["{$field_var}"];
}
# perform data conversions
if (isset($construct->field["{$value}"]["convert"])) {
$insert_value = $validate->convert($field_name, $insert_value, $construct->field["{$value}"]["convert"]);
}
# create the sql statement
if (!empty($insert_value)) {
$field_list .= ", " . $value . "=" . $db->qstr($insert_value, get_magic_quotes_gpc());
}
}
}
# add a comma before the site_id if needed
if ($field_list != '') {
$field_list .= ',';
}
# determine the record id:
$construct->record_id = $db->GenID(AGILE_DB_PREFIX . "" . $construct->table . '_id');
# define the new ID as a constant
define(strtoupper('NEW_RECORD_' . $construct->table . '_ID'), $construct->record_id);
# generate the full query
$q = "INSERT INTO " . AGILE_DB_PREFIX . "{$construct->table}\n\t\t\t\tSET\n\t\t\t\tid = " . $db->qstr($construct->record_id) . "\n\t\t\t\t{$field_list}\n\t\t\t\tsite_id = " . $db->qstr(DEFAULT_SITE);
# execute the query
$result = $db->Execute($q);
## echo $q;
# error reporting:
if ($result === false) {
global $C_debug;
$C_debug->error('database.inc.php', 'add', $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 false;
}
}
# define any triggers:
if (isset($construct->trigger["{$type}"])) {
include_once PATH_CORE . 'trigger.inc.php';
$trigger = new CORE_trigger();
$trigger->trigger($construct->trigger["{$type}"], 1, $VAR);
}
global $VAR;
$VAR["id"] = $construct->record_id;
@($redirect_page = $VAR['_page']);
if (isset($VAR["_escape"]) || isset($VAR["_escape_next"])) {
$_escape = '&_escape=1&_escape_next=1';
}
define('REDIRECT_PAGE', '?_page=' . $redirect_page . '&id=' . $construct->record_id . '' . @$_escape);
return $construct->record_id;
}
}
function update($VAR, $module, $record_id)
{
include_once PATH_CORE . 'validate.inc.php';
####################################################################
### $Method is the method name called to add records, so we know if we
### should use the error class, i.e: 'user_add'
####################################################################
### Get the Id for this module
$db =& DB();
$sql = 'SELECT id FROM ' . AGILE_DB_PREFIX . 'module WHERE
site_id = ' . $db->qstr(DEFAULT_SITE) . ' AND
name = ' . $db->qstr($module);
$result = $db->Execute($sql);
if ($result->RecordCount() == 0) {
return false;
} else {
$module_id = $result->fields['id'];
}
####################################################################
### Get all the associated STATIC RELATION records
$sql = 'SELECT id, static_var_id FROM ' . AGILE_DB_PREFIX . 'static_relation WHERE
site_id = ' . $db->qstr(DEFAULT_SITE) . ' AND
module_id = ' . $db->qstr($module_id) . ' ORDER BY sort_order';
$relation = $db->Execute($sql);
if ($relation->RecordCount() == 0) {
return false;
} else {
$i = 0;
$validate = new CORE_validate();
while (!$relation->EOF) {
unset($value);
### Get the primary settings for this field
$id = $relation->fields['id'];
$static_var_relation_id = $id;
$static_var_id = $relation->fields['static_var_id'];
### Get the extended details for this field from the STATIC
### VAR records
$sql = 'SELECT id,name,convert_type FROM ' . AGILE_DB_PREFIX . 'static_var WHERE
site_id = ' . $db->qstr(DEFAULT_SITE) . ' AND
id = ' . $db->qstr($static_var_id);
$var = $db->Execute($sql);
$convert = $var->fields['convert_type'];
$name = $var->fields['name'];
############################################################
### Generate the field name, translate if it exists,
### otherwise, just return the actual field name
$static_relation = 'static_relation[' . $id . ']';
@($value = $VAR["static_relation"]["{$id}"]);
if (!empty($VAR["static_relation"]["{$id}"]) || $value == 0) {
if ($convert != 'none' && $convert != '') {
$value = $validate->convert($name, $value, $convert);
}
### Test record already exists:
$sql = 'SELECT id,value FROM ' . AGILE_DB_PREFIX . 'static_var_record
WHERE
site_id = ' . $db->qstr(DEFAULT_SITE) . ' AND
record_id = ' . $db->qstr($record_id) . ' AND
module_id = ' . $db->qstr($module_id) . ' AND
static_var_id=' . $db->qstr($static_var_id) . ' AND
static_var_relation_id= ' . $db->qstr($static_var_relation_id);
$return = $db->Execute($sql);
if ($return->RecordCount() == 0) {
### Create new record:
$idx = $db->GenID(AGILE_DB_PREFIX . "" . 'static_var_record_id');
$sql = 'INSERT INTO ' . AGILE_DB_PREFIX . 'static_var_record SET
site_id = ' . $db->qstr(DEFAULT_SITE) . ',
id = ' . $db->qstr($idx) . ',
record_id = ' . $db->qstr($record_id) . ',
module_id = ' . $db->qstr($module_id) . ',
static_var_id=' . $db->qstr($static_var_id) . ',
static_var_relation_id= ' . $db->qstr($static_var_relation_id) . ',
value = ' . $db->qstr($value);
$insert = $db->Execute($sql);
if ($insert === false) {
global $C_debug;
$C_debug->error('static_var.inc.php', 'update', $db->ErrorMsg());
return false;
}
} elseif ($value != $return->fields['value']) {
### UPDATE the DB Record:
$sql = 'UPDATE ' . AGILE_DB_PREFIX . 'static_var_record SET
value = ' . $db->qstr($value) . '
WHERE
site_id = ' . $db->qstr(DEFAULT_SITE) . ' AND
id = ' . $db->qstr($return->fields['id']);
$insert = $db->Execute($sql);
if ($insert === false) {
global $C_debug;
$C_debug->error('static_var.inc.php', 'update', $db->ErrorMsg());
return false;
}
}
} else {
### Test record already exists:
$sql = 'DELETE FROM ' . AGILE_DB_PREFIX . 'static_var_record
WHERE
site_id = ' . $db->qstr(DEFAULT_SITE) . ' AND
record_id = ' . $db->qstr($record_id) . ' AND
module_id = ' . $db->qstr($module_id) . ' AND
static_var_id=' . $db->qstr($static_var_id) . ' AND
static_var_relation_id= ' . $db->qstr($static_var_relation_id);
$return = $db->Execute($sql);
}
$relation->MoveNext();
}
}
}
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 add($VAR)
{
$search_limit = 50;
global $C_debug, $C_translate;
include_once PATH_CORE . 'validate.inc.php';
$validate = new CORE_validate();
$this->start_date = false;
if (!empty($VAR['affiliate_commission_start_date'])) {
$this->start_date = $validate->convert('', $VAR['affiliate_commission_start_date'], 'date');
}
$this->end_date = false;
if (!empty($VAR['affiliate_commission_end_date'])) {
$this->end_date = $validate->convert('', $VAR['affiliate_commission_end_date'], 'date');
}
# determine the offset & limit
if (!empty($VAR['page'])) {
$current_page = $VAR['page'];
} else {
$current_page = '1';
}
# determine the offset & limit
$offset = -1;
if ($current_page == 1) {
$offset = 0;
} else {
$offset = $current_page * $search_limit - $search_limit;
}
$db =& DB();
if ($current_page == 1) {
$this->GenID = $db->GenID(AGILE_DB_PREFIX . 'affiliate_commission_id');
} else {
$this->GenID = @$VAR['GenID'];
}
# Generate the SQL for this commission generation session:
$sql = "SELECT id,affiliate_id,total_amt,tax_amt,type FROM " . AGILE_DB_PREFIX . "invoice WHERE site_id = " . $db->qstr(DEFAULT_SITE) . " AND\n\t\t\t\tprocess_status = 1 AND billing_status = 1 AND\n\t\t\t\t( affiliate_id IS NOT NULL AND affiliate_id !='' ) AND \n\t\t\t\ttotal_amt > 0 ";
if ($this->start_date) {
$sql .= " AND date_orig\t>= " . $db->qstr($this->start_date);
}
if ($this->end_date) {
$sql .= " AND date_orig\t<= " . $db->qstr($this->end_date);
}
$result = $db->SelectLimit($sql, $search_limit, $offset);
#echo $sql;
#echo "<BR><BR>";
#print_r($result->fields);
#exit;
# No more results - print link to export data:
if ($result->RecordCount() == 0 && $current_page > 1) {
$msg = $C_translate->translate('generated', 'affiliate_commission', '');
$msg .= ' <a href="?_page=affiliate_commission:view&id=' . $this->GenID . '">' . $C_translate->translate('submit', '', '') . '</a>';
$msg .= '<SCRIPT LANGUAGE="JavaScript">
refresh("1", "?_page=affiliate_commission:view&id=' . $this->GenID . '");
</SCRIPT>';
$C_debug->alert($msg);
return;
}
# Loop through the results:
$count = 0;
while (!$result->EOF) {
$do = true;
$level = 1;
unset($affiliate_arr);
$affiliate_id = $result->fields["affiliate_id"];
# check if the commissions for this invoice have already been issued...
$sql = "SELECT id FROM " . AGILE_DB_PREFIX . "invoice_commission WHERE\n\t\t\t\t\tsite_id\t\t\t= " . $db->qstr(DEFAULT_SITE) . " AND\n\t\t\t\t\tinvoice_id\t\t= " . $db->qstr($result->fields["id"]);
$result2 = $db->Execute($sql);
if ($result2->RecordCount() == 0) {
# loop through all affiliate levels to generate the commissions...
while ($level < 100 && @$affiliate_id) {
for ($i = 0; $i < count(@$affiliate_arr); $i++) {
if ($affiliate_arr[$i] == $affiliate_id) {
$i = 100;
$do = false;
}
}
if ($do) {
// get the total amount of the items after discounts and before taxes
$sqli = "SELECT sum(total_amt) as invoice_amount\n\t\t\t\t\t\t\t\t FROM " . AGILE_DB_PREFIX . "invoice_item WHERE site_id = " . $db->qstr(DEFAULT_SITE) . " \n\t\t\t\t\t\t\t\t AND invoice_id = {$result->fields["id"]}\n\t\t\t\t\t\t\t\t GROUP BY invoice_id";
$rsi = $db->Execute($sqli);
if ($rsi && $rsi->RecordCount()) {
$invoice_amount = $rsi->fields["invoice_amount"];
$arr = $this->calc_commission($affiliate_id, $invoice_amount, $result->fields["type"], $level);
if (@$arr["amount"] > 0) {
$this->add_invoice_commission($arr['amount'], $affiliate_id, $result->fields["id"]);
}
unset($affiliate_id);
if (isset($arr["affiliate_id"])) {
$affiliate_id = $arr['affiliate_id'];
}
$count++;
}
}
$level++;
}
}
# add this invoice to the processed list...
$result->MoveNext();
}
# Create the affiliate_commission record:
if ($count == 0) {
$msg = $C_translate->translate('no_results', 'affiliate_commission', '');
$C_debug->alert($msg);
} else {
$start = $search_limit * $current_page - $search_limit;
$stop = $search_limit * $current_page;
$page = $current_page + 1;
$C_translate->value['affiliate_commission']['start'] = $start;
$C_translate->value['affiliate_commission']['stop'] = $stop;
$C_translate->value['affiliate_commission']['genid'] = $this->GenID;
$C_translate->value['affiliate_commission']['page'] = $page;
$C_translate->value['affiliate_commission']['unixtime_start_date'] = $this->start_date;
$C_translate->value['affiliate_commission']['unixtime_stop_date'] = $this->end_date;
$msg = $C_translate->translate('continue', 'affiliate_commission', '');
$url = '?_page=core:blank&do[]=affiliate_commission:add&GenID=' . $this->GenID . '&page=' . $page . '&affiliate_commission_start_date=' . @$VAR['affiliate_commission_start_date'] . '&affiliate_commission_start_date=' . @$VAR['affiliate_commission_start_date'];
$msg .= ' <a href="' . $url . '">' . $C_translate->translate('submit', '', '') . '</a>';
$msg .= '<script language="JavaScript">document.location = "' . $url . '";</script>';
$C_debug->alert($msg);
}
}
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);
}
function add($VAR)
{
$this->construct();
global $C_translate;
$type = "add";
$this->method["{$type}"] = split(",", $this->method["{$type}"]);
# set the field list for this method:
$arr = $this->method["{$type}"];
# define the validation class
include_once PATH_CORE . 'validate.inc.php';
$validate = new CORE_validate();
$this->validated = true;
####################################################################
# loop through the field list to validate the required fields
####################################################################
while (list($key, $value) = each($arr)) {
# get the field value
$field_var = $this->module . '_' . $value;
$field_name = $value;
$this->validate = true;
####################################################################
# perform any field validation...
####################################################################
# check if this value is unique
if (isset($this->field["{$value}"]["unique"]) && isset($VAR["{$field_var}"])) {
if (!$validate->validate_unique($this->table, $field_name, "record_id", $VAR["{$field_var}"])) {
$this->validated = false;
$this->val_error[] = array('field' => $this->table . '_' . $field_name, 'field_trans' => $C_translate->translate('field_' . $field_name, $this->module, ""), 'error' => $C_translate->translate('validate_unique', "", ""));
}
}
# check if the submitted value meets the specifed requirements
if (isset($this->field["{$value}"]["validate"])) {
if (isset($VAR["{$field_var}"])) {
if ($VAR["{$field_var}"] != '') {
if (!$validate->validate($field_name, $this->field["{$value}"], $VAR["{$field_var}"], $this->field["{$value}"]["validate"])) {
$this->validated = false;
$this->val_error[] = array('field' => $this->module . '_' . $field_name, 'field_trans' => $C_translate->translate('field_' . $field_name, $this->module, ""), 'error' => $validate->error["{$field_name}"]);
}
} else {
$this->validated = false;
$this->val_error[] = array('field' => $this->module . '_' . $field_name, 'field_trans' => $C_translate->translate('field_' . $field_name, $this->module, ""), 'error' => $C_translate->translate('validate_any', "", ""));
}
} else {
$this->validated = false;
$this->val_error[] = array('field' => $this->module . '_' . $field_name, 'field_trans' => $C_translate->translate('field_' . $field_name, $this->module, ""), 'error' => $C_translate->translate('validate_any', "", ""));
}
}
}
####################################################################
# If validation was failed, skip the db insert &
# set the errors & origonal fields as Smarty objects,
# and change the page to be loaded.
####################################################################
if (!$this->validated) {
global $smarty;
# set the errors as a Smarty Object
$smarty->assign('form_validation', $this->val_error);
# set the page to be loaded
if (!defined("FORCE_PAGE")) {
define('FORCE_PAGE', $VAR['_page_current']);
}
# define any triggers
if (isset($this->trigger["{$type}"])) {
include_once PATH_CORE . 'trigger.inc.php';
$trigger = new CORE_trigger();
$trigger->trigger($this->trigger["{$type}"], 0, $VAR);
}
return;
} else {
# begin the new database class:
$db =& DB();
# loop through the field list to create the sql queries
$field_list = '';
$i = 0;
reset($arr);
while (list($key, $value) = each($arr)) {
# get the field value
$field_var = $this->module . '_' . $value;
$field_name = $value;
####################################################################
# perform any special actions
####################################################################
# md5, rc5, pgp, gpg, time, date, date-time
if (isset($this->field["{$value}"]["convert"]) && isset($VAR["{$field_var}"])) {
# do the conversion...
$VAR["{$field_var}"] = $validate->convert($field_name, $VAR["{$field_var}"], $this->field["{$value}"]["convert"]);
}
if (isset($VAR["{$field_var}"])) {
$field_list .= ", " . $value . "=" . $db->qstr($VAR["{$field_var}"]);
}
}
# add a comma before the site_id if needed
if ($field_list != '') {
$field_list .= ',';
}
# determine the record id:
$this->record_id = $db->GenID(AGILE_DB_PREFIX . "" . $this->table . '_id');
# determine the record id, if it is an ACCOUNT record
if ($this->table == 'account') {
$this->record_id = md5($this->record_id . '' . microtime());
}
# define the new ID as a constant
define(strtoupper('NEW_RECORD_' . $this->table . '_ID'), $this->record_id);
# generate the full query
$q = "INSERT INTO " . AGILE_DB_PREFIX . "{$this->table}\n\t\t\t\t\tSET\n\t\t\t\t\tid = " . $db->qstr($this->record_id) . "\n\t\t\t\t\t{$field_list}\n\t\t\t\t\tsite_id = " . $db->qstr(DEFAULT_SITE);
# execute the query
$result = $db->Execute($q);
# error reporting:
if ($result === false) {
global $C_debug;
$C_debug->error('database.inc.php', 'add', $db->ErrorMsg());
if (isset($this->trigger["{$type}"])) {
include_once PATH_CORE . 'trigger.inc.php';
$trigger = new CORE_trigger();
$trigger->trigger($this->trigger["{$type}"], 0, $VAR);
}
}
$VAR["id"] = $this->record_id;
@($redirect_page = $VAR['_page']);
define('REDIRECT_PAGE', '?_page=' . $redirect_page . '&id=' . $this->record_id . '&s=' . SESS);
# RUN ANY INSTALL SCRIPT!
$file = $VAR['db_mapping_map_file'];
if ($file != '') {
include_once PATH_PLUGINS . 'db_mapping/' . $file . '.php';
eval('$_MAP = new map_' . strtoupper($file) . ';');
if (isset($_MAP->map['install']) && $_MAP->map['install'] == true) {
$_MAP->install();
}
}
}
}
/** 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);
}
/**
* 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_update($VAR, &$construct, $type)
{
global $C_translate;
# set the field list for this method:
$arr = $construct->method["{$type}"];
# define the validation class
include_once PATH_CORE . 'validate.inc.php';
$validate = new CORE_validate();
$construct->validated = true;
# define this record id
$id = $VAR[$construct->module . '_id'];
####################################################################
# loop through the field list to validate the required fields
####################################################################
while (list($key, $value) = each($arr)) {
# get the field value
$field_var = $construct->module . '_' . $value;
$field_name = $value;
$construct->validate = true;
####################################################################
# perform any field validation...
####################################################################
# check if the conversion type required is not one ignored on updates:
$ignore_con = false;
$ignore_convert = array('sha', 'md5', 'rc5', 'crypt');
for ($ic = 0; $ic < count($ignore_convert); $ic++) {
if (isset($construct->field["{$value}"]["convert"])) {
if ($construct->field["{$value}"]["convert"] == $ignore_convert[$ic]) {
$ignore_con = true;
}
}
}
if (!$ignore_con) {
# check if this value is unique
if (isset($construct->field["{$value}"]["unique"])) {
if (isset($VAR["{$field_var}"])) {
if (!$validate->validate_unique($construct->table, $field_name, $id, $VAR["{$field_var}"])) {
$construct->validated = false;
$construct->val_error[] = array('field' => $construct->module . '_' . $field_name, 'field_trans' => $C_translate->translate('field_' . $field_name, $construct->module, ""), 'error' => $C_translate->translate('validate_unique', "", ""));
}
}
}
# check if the submitted value meets the specifed requirements
if (isset($construct->field["{$value}"]["validate"])) {
if (isset($VAR["{$field_var}"])) {
if ($VAR["{$field_var}"] != '') {
if (!$validate->validate($field_name, $construct->field["{$value}"], $VAR["{$field_var}"], $construct->field["{$value}"]["validate"])) {
$construct->validated = false;
$construct->val_error[] = array('field' => $construct->module . '_' . $field_name, 'field_trans' => $C_translate->translate('field_' . $field_name, $construct->module, ""), 'error' => $validate->error["{$field_name}"]);
}
} else {
$construct->validated = false;
$construct->val_error[] = array('field' => $construct->module . '_' . $field_name, 'field_trans' => $C_translate->translate('field_' . $field_name, $construct->module, ""), 'error' => $C_translate->translate('validate_any', "", ""));
}
} else {
$construct->validated = false;
$construct->val_error[] = array('field' => $construct->module . '_' . $field_name, 'field_trans' => $C_translate->translate('field_' . $field_name, $construct->module, ""), 'error' => $C_translate->translate('validate_any', "", ""));
}
}
}
}
####################################################################
# If validation was failed, skip the db insert &
# set the errors & origonal fields as Smarty objects,
# and change the page to be loaded.
####################################################################
if (!$construct->validated) {
global $smarty;
# set the errors as a Smarty Object
$smarty->assign('form_validation', $construct->val_error);
# change the page to be loaded
global $VAR;
$VAR['_page'] = $construct->module . ':view';
if (isset($construct->trigger["{$type}"])) {
include_once PATH_CORE . 'trigger.inc.php';
$trigger = new CORE_trigger();
$trigger->trigger($construct->trigger["{$type}"], 0, $VAR);
}
# strip slashes
global $C_vars;
$C_vars->strip_slashes_all();
return false;
} else {
$db =& DB();
$field_list = '';
$i = 0;
reset($arr);
while (list($key, $value) = each($arr)) {
# get the field value
$field_var = $construct->module . '_' . $value;
$field_name = $value;
if (isset($VAR["{$field_var}"]) && $VAR["{$field_var}"] != 'IGNORE-ARRAY-VALUE') {
# check if html allowed:
if (@$construct->field["{$value}"]["html"] != 1 && !is_array($VAR["{$field_var}"])) {
$insert_value = htmlspecialchars($VAR["{$field_var}"]);
} else {
$insert_value = $VAR["{$field_var}"];
}
# perform data conversions
if (isset($construct->field["{$value}"]["convert"])) {
$insert_value = $validate->convert($field_name, $insert_value, $construct->field["{$value}"]["convert"]);
}
if ($i == 0) {
$field_list .= $value . "=" . $db->qstr($insert_value, get_magic_quotes_gpc());
} else {
$field_list .= ", " . $value . "=" . $db->qstr($insert_value, get_magic_quotes_gpc());
}
$i++;
} elseif (@$construct->field["{$value}"]["convert"] == "array" && @$VAR["{$field_var}"] != 'IGNORE-ARRAY-VALUE') {
# Handle blank array string...
$insert_value = serialize(array(""));
if ($i == 0) {
$field_list .= $value . "=" . $db->qstr($insert_value, get_magic_quotes_gpc());
} else {
$field_list .= ", " . $value . "=" . $db->qstr($insert_value, get_magic_quotes_gpc());
}
$i++;
}
}
# generate the full query
$q = "UPDATE " . AGILE_DB_PREFIX . "{$construct->table} SET\n\t\t\t\t{$field_list}\n\t\t\t\tWHERE\n\t\t\t\tid \t\t= " . $db->qstr($id) . "\n\t\t\t\tAND\n\t\t\t\tsite_id = " . $db->qstr(DEFAULT_SITE);
# execute the query
$db =& DB();
$result = $db->Execute($q);
# echo "<PRE>$q</PRE>";
# error reporting
if ($result === false) {
global $C_debug;
$C_debug->error('database.inc.php', 'update', $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 false;
} else {
if (isset($construct->trigger["{$type}"])) {
include_once PATH_CORE . 'trigger.inc.php';
$trigger = new CORE_trigger();
$trigger->trigger($construct->trigger["{$type}"], 1, $VAR);
}
return true;
}
}
}
