protected function getWhereClauseSQL($columns, $args, $type, &$ph, $query_stub = NULL, $and = TRUE)
{
//Debug::Text('Type: '. $type .' Query Stub: '. $query_stub .' AND: '. (int)$and, __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($columns, 'Columns: ', __FILE__, __LINE__, __METHOD__,10);
//Debug::Arr($args, 'Args: ', __FILE__, __LINE__, __METHOD__,10);
switch (strtolower($type)) {
case 'text':
if (isset($args) and !is_array($args) and trim($args) != '') {
if ($query_stub == '' and !is_array($columns)) {
$query_stub = 'lower(' . $columns . ') LIKE ?';
}
$ph[] = $this->handleSQLSyntax(strtolower($args));
$retval = $query_stub;
}
break;
case 'text_metaphone':
if (isset($args) and !is_array($args) and trim($args) != '') {
if ($query_stub == '' and !is_array($columns)) {
$query_stub = '( lower(' . $columns . ') LIKE ? OR ' . $columns . '_metaphone LIKE ? )';
}
$ph[] = $this->handleSQLSyntax(strtolower($args));
if (strpos($args, '"') !== FALSE) {
//ignores metaphone search.
$ph[] = '';
} else {
$ph[] = $this->handleSQLSyntax(metaphone($args));
}
$retval = $query_stub;
}
break;
case 'text_list':
case 'lower_text_list':
case 'upper_text_list':
if (!is_array($args)) {
$args = (array) $args;
}
if ($type == 'upper_text_list' or $type == 'lower_text_list') {
if ($type == 'upper_text_list') {
$text_case = CASE_UPPER;
} else {
$text_case = CASE_LOWER;
}
$args = array_flip(array_change_key_case(array_flip($args), $text_case));
}
if (isset($args) and isset($args[0]) and !in_array(-1, $args) and !in_array('00', $args)) {
if ($query_stub == '' and !is_array($columns)) {
$query_stub = $columns . ' in (?)';
}
$retval = str_replace('?', $this->getListSQL($args, $ph), $query_stub);
}
break;
case 'province':
if (!is_array($args)) {
$args = (array) $args;
}
if (isset($args) and isset($args[0]) and !in_array(-1, $args) and !in_array('00', $args)) {
if ($query_stub == '' and !is_array($columns)) {
$query_stub = $columns . ' in (?)';
}
$retval = str_replace('?', $this->getListSQL($args, $ph), $query_stub);
}
break;
case 'phone':
if (isset($args) and !is_array($args) and trim($args) != '') {
if ($query_stub == '' and !is_array($columns)) {
$query_stub = "( replace( replace( replace( replace( replace( replace( " . $columns . ", ' ', ''), '-', ''), '(', ''), ')', ''), '+', ''), '.', '') LIKE ? OR " . $columns . " LIKE ? )";
}
$ph[] = $ph[] = $this->handleSQLSyntax(preg_replace('/[^0-9\\%\\*\\"]/', '', strtolower($args)));
//Need the same value twice for the query stub.
$retval = $query_stub;
}
break;
case 'numeric':
if (!is_array($args)) {
//Can't check isset() on a NULL value.
if ($query_stub == '' and !is_array($columns)) {
if ($args === NULL) {
$query_stub = $columns . ' is NULL';
} else {
$args = $this->Validator->stripNonNumeric($args);
if (is_numeric($args)) {
$ph[] = $args;
$query_stub = $columns . ' = ?';
}
}
}
$retval = $query_stub;
}
break;
case 'numeric_list':
if (!is_array($args)) {
$args = (array) $args;
}
if (isset($args) and isset($args[0]) and !in_array(-1, $args)) {
if ($query_stub == '' and !is_array($columns)) {
$query_stub = $columns . ' in (?)';
}
$retval = str_replace('?', $this->getListSQL($args, $ph), $query_stub);
}
break;
case 'not_numeric_list':
if (!is_array($args)) {
$args = (array) $args;
}
if (isset($args) and isset($args[0]) and !in_array(-1, $args)) {
if ($query_stub == '' and !is_array($columns)) {
$query_stub = $columns . ' not in (?)';
}
$retval = str_replace('?', $this->getListSQL($args, $ph), $query_stub);
}
break;
case 'tag':
//We need company_id and object_type_id passed in.
if (isset($args['company_id']) and isset($args['object_type_id']) and isset($args['tag'])) {
//Parse the tags search syntax to determine ANY, AND, OR searches.
$parsed_tags = CompanyGenericTagFactory::parseTags($args['tag']);
//Debug::Arr($parsed_tags, 'Parsed Tags: ', __FILE__, __LINE__, __METHOD__,10);
if (is_array($parsed_tags)) {
$retval = '';
if (isset($parsed_tags['add']) and count($parsed_tags['add']) > 0) {
$query_stub = ' EXISTS (
select 1
from company_generic_tag_map as cgtm
INNER JOIN company_generic_tag as cgt ON (cgtm.tag_id = cgt.id)
WHERE cgt.company_id = ' . (int) $args['company_id'] . '
AND cgtm.object_type_id = ' . (int) $args['object_type_id'] . '
AND ' . $columns . ' = cgtm.object_id
AND ( lower(cgt.name) in (?) )
group by cgtm.object_id
HAVING COUNT(*) = ' . count($parsed_tags['add']) . '
)';
$retval .= str_replace('?', $this->getListSQL(Misc::arrayChangeValueCase($parsed_tags['add']), $ph), $query_stub);
if (isset($parsed_tags['delete']) and count($parsed_tags['delete']) > 0) {
$retval .= ' AND ';
}
}
if (isset($parsed_tags['delete']) and count($parsed_tags['delete']) > 0) {
$query_stub = ' NOT EXISTS (
select 1
from company_generic_tag_map as cgtm
INNER JOIN company_generic_tag as cgt ON (cgtm.tag_id = cgt.id)
WHERE cgt.company_id = ' . (int) $args['company_id'] . '
AND cgtm.object_type_id = ' . (int) $args['object_type_id'] . '
AND ' . $columns . ' = cgtm.object_id
AND ( lower(cgt.name) in (?) )
group by cgtm.object_id
HAVING COUNT(*) = ' . count($parsed_tags['delete']) . '
)';
$retval .= str_replace('?', $this->getListSQL(Misc::arrayChangeValueCase($parsed_tags['delete']), $ph), $query_stub);
}
}
}
if (!isset($retval)) {
$retval = '';
}
break;
case 'date_range':
//Uses EPOCH values only, used for integer datatype columns
//Uses EPOCH values only, used for integer datatype columns
case 'date_range_timestamp':
//Uses text timestamp values, used for timestamp datatype columns
if (isset($args) and !is_array($args) and trim($args) != '') {
if ($query_stub == '' and !is_array($columns)) {
if ($type == 'date_range_timestamp') {
$query_stub = $this->getDateRangeSQL($args, $columns, 'timestamp');
} elseif ($type == 'date_range_datestamp') {
$query_stub = $this->getDateRangeSQL($args, $columns, 'datestamp');
} else {
$query_stub = $this->getDateRangeSQL($args, $columns, 'epoch');
}
}
Debug::Text('Query Stub: ' . $query_stub, __FILE__, __LINE__, __METHOD__, 10);
$retval = $query_stub;
}
break;
case 'user_id_or_name':
if (isset($args) and is_array($args)) {
$retval = $this->getWhereClauseSQL($columns[0], $args, 'numeric_list', $ph, '', FALSE);
}
if (isset($args) and !is_array($args) and trim($args) != '') {
$ph[] = $ph[] = $this->handleSQLSyntax(strtolower(trim($args)));
$retval = '(lower(' . $columns[1] . ') LIKE ? OR lower(' . $columns[2] . ') LIKE ? ) ';
}
break;
case 'boolean':
if (isset($args) and !is_array($args) and trim($args) != '' and !is_numeric($args)) {
if ($query_stub == '' and !is_array($columns)) {
switch (strtolower(trim($args))) {
case 'yes':
case 'y':
case 'true':
case 't':
case 'on':
$ph[] = 1;
$query_stub = $columns . ' = ?';
break;
case 'no':
case 'n':
case 'false':
case 'f':
case 'off':
$ph[] = 0;
$query_stub = $columns . ' = ?';
break;
}
}
$retval = $query_stub;
}
break;
default:
Debug::Text('Invalid type: ' . $type, __FILE__, __LINE__, __METHOD__, 10);
break;
}
if (isset($retval)) {
$and_sql = NULL;
if ($and == TRUE and $retval != '') {
//Don't prepend ' AND' if there is nothing to come after it.
$and_sql = 'AND ';
}
//Debug::Arr($ph, 'Query Stub: '. $and_sql.$retval, __FILE__, __LINE__, __METHOD__,10);
return ' ' . $and_sql . $retval . ' ';
//Wrap each query stub in spaces.
}
return NULL;
}
function getByCompanyIDAndObjectTypeAndObjectID($company_id, $object_type_id, $id, $where = NULL, $order = NULL)
{
if ($company_id == '') {
return FALSE;
}
if ($object_type_id == '') {
return FALSE;
}
if ($id == '') {
return FALSE;
}
$additional_order_fields = array('cgtf.name');
if ($order == NULL) {
$order = array('cgtf.name' => 'asc');
$strict = FALSE;
} else {
$strict = TRUE;
}
$cgtf = new CompanyGenericTagFactory();
$ph = array('company_id' => $company_id);
//This should be a list of just distinct
$query = '
select
a.*,
cgtf.name as name
from ' . $this->getTable() . ' as a
LEFT JOIN ' . $cgtf->getTable() . ' as cgtf ON ( a.object_type_id = cgtf.object_type_id AND a.tag_id = cgtf.id AND cgtf.company_id = ?)
where
a.object_type_id in (' . $this->getListSQL($object_type_id, $ph) . ')
AND a.object_id in (' . $this->getListSQL($id, $ph) . ')
';
$query .= $this->getWhereSQL($where);
$query .= $this->getSortSQL($order, $strict, $additional_order_fields);
$this->ExecuteSQL($query, $ph);
return $this;
}
static function setTags($company_id, $object_type_id, $object_id, $tags)
{
Debug::text('Setting Tags: Company: ' . $company_id . ' Object Type: ' . $object_type_id . ' Object: ' . $object_type_id . ' Tags: ' . $tags, __FILE__, __LINE__, __METHOD__, 10);
if ($object_id > 0) {
//Parse tags
$parsed_tags = CompanyGenericTagFactory::parseTags($tags);
if (is_array($parsed_tags)) {
$existing_tags = CompanyGenericTagFactory::getOrCreateTags($company_id, $object_type_id, $parsed_tags);
$existing_tag_ids = array_values((array) $existing_tags);
//Debug::Arr($existing_tags, 'Existing Tags: ', __FILE__, __LINE__, __METHOD__, 10);
//Debug::Arr($existing_tag_ids, 'Existing Tag IDs: ', __FILE__, __LINE__, __METHOD__, 10);
//Get list of mapped Tag IDs that need to be deleted.
if (isset($parsed_tags['delete'])) {
foreach ($parsed_tags['delete'] as $del_tag) {
$del_tag = strtolower($del_tag);
if (isset($existing_tags[$del_tag]) and $existing_tags[$del_tag] > 0) {
$del_tag_ids[] = $existing_tags[$del_tag];
}
}
}
//If needed, delete mappings first.
$cgtmlf = TTnew('CompanyGenericTagMapListFactory');
$cgtmlf->getByCompanyIDAndObjectTypeAndObjectID($company_id, $object_type_id, $object_id);
$tmp_ids = array();
foreach ($cgtmlf as $obj) {
$id = $obj->getTagID();
Debug::text('Object Type ID: ' . $object_type_id . ' Object ID: ' . $obj->getObjectID() . ' Tag ID: ' . $id, __FILE__, __LINE__, __METHOD__, 10);
if (isset($del_tag_ids) and in_array($id, $del_tag_ids)) {
Debug::text('Deleting: ' . $id, __FILE__, __LINE__, __METHOD__, 10);
$obj->Delete();
} else {
//Save ID's that need to be updated.
Debug::text('NOT Deleting : ' . $id, __FILE__, __LINE__, __METHOD__, 10);
$tmp_ids[] = $id;
}
}
unset($id, $obj);
//Debug::Arr($tmp_ids, 'TMP Ids: ', __FILE__, __LINE__, __METHOD__, 10);
//Add new tags.
if (isset($parsed_tags['add'])) {
foreach ($parsed_tags['add'] as $add_tag) {
$add_tag = strtolower($add_tag);
if (isset($existing_tags[$add_tag]) and $existing_tags[$add_tag] > 0 and !in_array($existing_tags[$add_tag], $tmp_ids)) {
$cgtmf = TTnew('CompanyGenericTagMapFactory');
$cgtmf->setObjectType($object_type_id);
$cgtmf->setObjectID($object_id);
$cgtmf->setTagID($existing_tags[strtolower($add_tag)]);
if ($cgtmf->isValid()) {
$cgtmf->Save();
}
}
}
}
}
} else {
Debug::Text('Object ID not set, skipping tags!', __FILE__, __LINE__, __METHOD__, 10);
}
return TRUE;
}
