+ /**
+ * SQL version of api function to assign filters to the DAO based on the syntax
+ * $field => array('IN' => array(4,6,9))
+ * OR
+ * $field => array('LIKE' => array('%me%))
+ * etc
+ *
+ * @param $fieldname string name of fields
+ * @param $filter array filter to be applied indexed by operator
+ * @param $type String type of field (not actually used - nor in api @todo )
+ * @param $alias String alternative field name ('as') @todo- not actually used
+ * @param bool $returnSanitisedArray return a sanitised array instead of a clause
+ * this is primarily so we can add filters @ the api level to the Query object based fields
+ * @todo a better solutution would be for the query object to apply these filters based on the
+ * api supported format (but we don't want to risk breakage in alpha stage & query class is scary
+ * @todo @time of writing only IN & NOT IN are supported for the array style syntax (as test is
+ * required to extend further & it may be the comments per above should be implemented. It may be
+ * preferable to not double-banger the return context next refactor of this - but keeping the attention
+ * in one place has some advantages as we try to extend this format
+ *
+ * @return NULL|string|array a string is returned if $returnSanitisedArray is not set, otherwise and Array or NULL
+ * depending on whether it is supported as yet
+ **/
+ public static function createSQLFilter($fieldName, $filter, $type, $alias = NULL, $returnSanitisedArray = FALSE) {
+ // http://issues.civicrm.org/jira/browse/CRM-9150 - stick with 'simple' operators for now
+ // support for other syntaxes is discussed in ticket but being put off for now
+ $acceptedSQLOperators = array('=', '<=', '>=', '>', '<', 'LIKE', "<>", "!=", "NOT LIKE", 'IN', 'NOT IN', 'BETWEEN', 'NOT BETWEEN');
+ foreach ($filter as $operator => $criteria) {
+ if (in_array($operator, $acceptedSQLOperators)) {
+ switch ($operator) {
+ // unary operators
+ case 'IS NULL':
+ case 'IS NOT NULL':
+ if(!$returnSanitisedArray) {
+ return (sprintf('%s %s', $fieldName, $operator));
+ }
+ else{
+ return NULL; // not yet implemented (tests required to implement)
+ }
+ break;
+
+ // ternary operators
+ case 'BETWEEN':
+ case 'NOT BETWEEN':
+ if (empty($criteria[0]) || empty($criteria[1])) {
+ throw new Exception("invalid criteria for $operator");
+ }
+ if(!$returnSanitisedArray) {
+ return (sprintf('%s ' . $operator . ' "%s" AND "%s"', $fieldName, CRM_Core_DAO::escapeString($criteria[0]), CRM_Core_DAO::escapeString($criteria[1])));
+ }
+ else{
+ return NULL; // not yet implemented (tests required to implement)
+ }
+ break;
+
+ // n-ary operators
+ case 'IN':
+ case 'NOT IN':
+ if (empty($criteria)) {
+ throw new Exception("invalid criteria for $operator");
+ }
+ $escapedCriteria = array_map(array(
+ 'CRM_Core_DAO',
+ 'escapeString'
+ ), $criteria);
+ if(!$returnSanitisedArray) {
+ return (sprintf('%s %s ("%s")', $fieldName, $operator, implode('", "', $escapedCriteria)));
+ }
+ return $escapedCriteria;
+ break;
+
+ // binary operators
+
+ default:
+ if(!$returnSanitisedArray) {
+ return(sprintf('%s %s "%s"', $fieldName, $operator, CRM_Core_DAO::escapeString($criteria)));
+ }
+ else{
+ return NULL; // not yet implemented (tests required to implement)
+ }
+ }
+ }
+ }
+ }
+
+ /**
+ * SQL has a limit of 64 characters on various names:
+ * table name, trigger name, column name ...
+ *
+ * For custom groups and fields we generated names from user entered input
+ * which can be longer than this length, this function helps with creating
+ * strings that meet various criteria.
+ *
+ * @param string $string - the string to be shortened
+ * @param int $length - the max length of the string
+ */
+ public static function shortenSQLName($string, $length = 60, $makeRandom = FALSE) {
+ // early return for strings that meet the requirements
+ if (strlen($string) <= $length) {
+ return $string;
+ }
+
+ // easy return for calls that dont need a randomized uniq string
+ if (! $makeRandom) {
+ return substr($string, 0, $length);
+ }
+
+ // the string is longer than the length and we need a uniq string
+ // for the same tablename we need the same uniq string everytime
+ // hence we use md5 on the string, which is not random
+ // we'll append 8 characters to the end of the tableName
+ $md5string = substr(md5($string), 0, 8);
+ return substr($string, 0, $length - 8) . "_{$md5string}";
+ }
+
+}