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