public function body($arg = null, $rb = null, $uid = null)
{
if (isset($arg) && isset($rb)) {
$this->group = $rb->tab . '/' . $arg['id'];
if (Utils_WatchdogCommon::get_category_id($rb->tab) !== null) {
$this->watchdog_category = $rb->tab;
$this->watchdog_id = $arg['id'];
}
$this->set_view_func(array('Utils_RecordBrowserCommon', 'create_default_linked_label'), array($rb->tab, $arg['id']));
}
if (!isset($this->group) && !$uid) {
trigger_error('Key not given to attachment module', E_USER_ERROR);
}
$_SESSION['client']['utils_attachment_group'] = $this->group;
load_js('modules/Utils/Attachment/attachments.js');
Base_ThemeCommon::load_css('Utils_Attachment', 'browse');
$this->rb = $this->init_module(Utils_RecordBrowser::module_name(), 'utils_attachment', 'utils_attachment');
$defaults = array('permission' => Base_User_SettingsCommon::get('CRM_Common', 'default_record_permission'), 'func' => serialize($this->func), 'args' => serialize($this->args));
$rb_cols = array();
$single_group = is_string($this->group) || count($this->group) == 1;
if ($this->force_multiple) {
$single_group = false;
}
if ($single_group) {
$group = is_string($this->group) ? $this->group : reset($this->group);
$defaults['local'] = $group;
} else {
// force attached to display
$rb_cols['attached_to'] = true;
$this->rb->set_button(false);
}
$this->rb->set_defaults($defaults);
$this->rb->set_additional_actions_method(array($this, 'add_actions'));
$this->rb->set_header_properties(array('sticky' => array('width' => 1, 'display' => false), 'attached_to' => array('width' => "16em"), 'edited_on' => array('width' => "12em"), 'title' => array('width' => "20em")));
if ($uid) {
$this->rb->set_button(false);
$this->rb->disable_actions(array('delete'));
$this->display_module($this->rb, array(array(':Created_by' => $uid), $rb_cols, array('sticky' => 'DESC', 'edited_on' => 'DESC')), 'show_data');
} else {
$crits = array();
if (!is_array($this->group)) {
$this->group = array($this->group);
}
if (isset($_SESSION['attachment_copy']) && count($this->group) == 1 && $_SESSION['attachment_copy']['group'] != $this->group) {
$this->rb->new_button(Base_ThemeCommon::get_template_file(Utils_Attachment::module_name(), 'link.png'), __('Paste'), Utils_TooltipCommon::open_tag_attrs($_SESSION['attachment_copy']['text']) . ' ' . $this->create_callback_href(array($this, 'paste')));
}
if ($this->group) {
$g = array_map(array('DB', 'qstr'), $this->group);
$crits['id'] = DB::GetCol('SELECT attachment FROM utils_attachment_local WHERE local IN (' . implode(',', $g) . ')');
} else {
$crits['id'] = 0;
}
$this->display_module($this->rb, array($crits, $rb_cols, array('sticky' => 'DESC', 'edited_on' => 'DESC')), 'show_data');
}
}
protected function handle_special_field_crit(Utils_RecordBrowser_CritsSingle $crit)
{
$field = $crit->get_field();
$operator = self::transform_meta_operators_to_sql($crit->get_operator());
$value = $crit->get_value();
$negation = $crit->get_negation();
$special = $field[0] == ':' || $field == 'id';
if ($special) {
$sql = '';
$vals = array();
switch ($field) {
case ':id':
case 'id':
if (!is_array($value)) {
$sql = $this->tab_alias . ".id {$operator} %d";
$vals[] = $value;
} else {
if ($operator != '=' && $operator != '==') {
throw new Exception("Cannot use array values for id field operator '{$operator}'");
}
$clean_vals = array();
foreach ($value as $v) {
if (is_numeric($v)) {
$clean_vals[] = $v;
}
}
if (empty($clean_vals)) {
$sql = 'false';
} else {
$sql = $this->tab_alias . ".id IN (" . implode(',', $clean_vals) . ")";
}
}
if ($negation) {
$sql = "NOT ({$sql})";
}
break;
case ':Fav':
$fav = $value == true;
if ($negation) {
$fav = !$fav;
}
if (!isset($this->applied_joins[$field])) {
$this->final_tab = '(' . $this->final_tab . ') LEFT JOIN ' . $this->tab . '_favorite AS ' . $this->tab_alias . '_fav ON ' . $this->tab_alias . '_fav.' . $this->tab . '_id=' . $this->tab_alias . '.id AND ' . $this->tab_alias . '_fav.user_id=' . Acl::get_user();
$this->applied_joins[$field] = true;
}
$rule = $fav ? 'IS NOT NULL' : 'IS NULL';
$sql = $this->tab_alias . "_fav.fav_id {$rule}";
break;
case ':Sub':
$sub = $value == true;
if ($negation) {
$sub = !$sub;
}
if (!isset($this->applied_joins[$field])) {
$this->final_tab = '(' . $this->final_tab . ') LEFT JOIN utils_watchdog_subscription AS ' . $this->tab_alias . '_sub ON ' . $this->tab_alias . '_sub.internal_id=' . $this->tab_alias . '.id AND ' . $this->tab_alias . '_sub.category_id=' . Utils_WatchdogCommon::get_category_id($this->tab) . ' AND ' . $this->tab_alias . '_sub.user_id=' . Acl::get_user();
$this->applied_joins[$field] = true;
}
$rule = $sub ? 'IS NOT NULL' : 'IS NULL';
$sql = $this->tab_alias . "_sub.internal_id {$rule}";
break;
case ':Recent':
$rec = $value == true;
if ($negation) {
$rec = !$rec;
}
if (!isset($this->applied_joins[$field])) {
$this->final_tab = '(' . $this->final_tab . ') LEFT JOIN ' . $this->tab . '_recent AS ' . $this->tab_alias . '_rec ON ' . $this->tab_alias . '_rec.' . $this->tab . '_id=' . $this->tab_alias . '.id AND ' . $this->tab_alias . '_rec.user_id=' . Acl::get_user();
$this->applied_joins[$field] = true;
}
$rule = $rec ? 'IS NOT NULL' : 'IS NULL';
$sql = $this->tab_alias . "_rec.user_id {$rule}";
break;
case ':Created_on':
$vals[] = Base_RegionalSettingsCommon::reg2time($value, false);
$sql = $this->tab_alias . '.created_on ' . $operator . '%T';
if ($negation) {
$sql = "NOT ({$sql})";
}
break;
case ':Created_by':
if (!is_array($value)) {
$value = array($value);
}
$sql = array();
foreach ($value as $v) {
$vals[] = $v;
$sql[] = $this->tab_alias . '.created_by = %d';
}
$sql = implode(' OR ', $sql);
if ($negation) {
$sql = "NOT ({$sql})";
}
break;
case ':Edited_on':
$inj = $operator . '%T';
$sql = '(((SELECT MAX(edited_on) FROM ' . $this->tab . '_edit_history WHERE ' . $this->tab . '_id=' . $this->tab_alias . '.id) ' . $inj . ') OR ' . '((SELECT MAX(edited_on) FROM ' . $this->tab . '_edit_history WHERE ' . $this->tab . '_id=' . $this->tab_alias . '.id) IS NULL AND created_on ' . $inj . '))';
$timestamp = Base_RegionalSettingsCommon::reg2time($value, false);
if ($negation) {
$sql = "NOT (COALESCE({$sql}, FALSE))";
}
$vals[] = $timestamp;
$vals[] = $timestamp;
break;
}
return array($sql, $vals);
}
return false;
}
public static function build_query( $tab, $crits = null, $admin = false, $order = array()) {
if (!is_array($order)) $order = array();
$cache_key=$tab.'__'.serialize($crits).'__'.$admin.'__'.serialize($order);
static $cache = array();
self::init($tab, $admin);
if (isset($cache[$cache_key])) return $cache[$cache_key];
if (!$tab) return false;
$postgre = DB::is_postgresql();
$having = '';
$fields = '';
$final_tab = $tab.'_data_1 AS r';
$vals = array();
if (!$crits) $crits = array();
$access = $admin ? true : self::get_access($tab, 'browse');
if ($access===false) return array();
elseif ($access!==true && is_array($access))
$crits = self::merge_crits($crits, $access);
$iter = 0;
self::init($tab, $admin);
foreach($order as $k=>$v) {
if (!is_string($k)) break;
if ($k[0]==':') $order[] = array('column'=>$k, 'order'=>$k, 'direction'=>$v);
else $order[] = array('column'=>self::$hash[$k], 'order'=>self::$hash[$k], 'direction'=>$v);
unset($order[$k]);
}
$or_started = false;
$sep = DB::qstr('::');
$group_or_start = $group_or = false;
$special_chars = str_split('!"(|<>=~]^');
foreach($crits as $k=>$v){
self::init($tab, $admin);
$f = explode('[',$k);
$f = str_replace($special_chars,'',$f[0]);
while ($f[0]=='_') $f = substr($f, 1);
if (!isset(self::$table_rows[$f]) && $f[0]!=':' && $f!=='id' && (!isset(self::$hash[$f]) || !isset(self::$table_rows[self::$hash[$f]]))) continue; //failsafe
$negative = $noquotes = $or_start = $or = false;
$operator = '=';
while (($k[0]<'a' || $k[0]>'z') && ($k[0]<'A' || $k[0]>'Z') && $k[0]!=':') {
if ($k[0]=='!') $negative = true;
if ($k[0]=='"') $noquotes = true;
if ($k[0]=='(') $or_start = true;
if ($k[0]=='|') $or = true;
if ($k[0]=='<') $operator = '<';
if ($k[0]=='>') $operator = '>';
if ($k[0]=='~') $operator = DB::like();
if ($k[0]=='^') $group_or_start = true;
if ($k[1]=='=' && $operator!=DB::like()) {
$operator .= '=';
$k = substr($k, 2);
} else $k = substr($k, 1);
if (!isset($k[0])) trigger_error('Invalid criteria in build query: missing word. Crits:'.print_r($crits,true), E_USER_ERROR);
}
$or |= $or_start;
if ($group_or && $group_or_start)
$having .= ')';
if ($or_start && $or_started || ($or_started && !$or)) {
$having .= ')';
$or_started = false;
}
if ($or) {
if ($having!='') {
if ($group_or && $group_or_start || $or_started) $having .= ' OR ';
else $having .= ' AND ';
}
if ($group_or_start) $having .= '(';
if (!$or_started) $having .= '(';
$or_started = true;
} else {
if ($having!='' && $group_or && $group_or_start) $having .= ' OR ';
if ($having!='' && (!$group_or || !$group_or_start)) $having .= ' AND ';
if ($group_or_start) $having .= '(';
}
if ($group_or_start) {
if (!$group_or) $having .= '(';
$group_or = true;
$group_or_start = false;
}
if ($k[strlen($k)-1]==']') {
list($ref, $sub_field) = explode('[', trim($k, ']'));
$args = self::$table_rows[self::$hash[$ref]];
$commondata = $args['commondata'];
if (is_array($args['param'])) {
if (isset($args['param']['array_id']))
$args['ref_table'] = $args['param']['array_id'];
else
$args['ref_table'] = $args['param'][1];
}
if (!isset($args['ref_table'])) trigger_error('Invalid crits, field '.$ref.' is not a reference; crits: '.print_r($crits,true),E_USER_ERROR);
$is_multiselect = ($args['type']=='multiselect');
$tab2 = $args['ref_table'];
$col2 = $sub_field;
if ($commondata) {
$ret = Utils_CommonDataCommon::get_translated_array($tab2);
$allowed_cd = array();
if (!is_array($v)) $v = array($v);
foreach ($ret as $kkk=>$vvv)
foreach ($v as $w) if ($w!='') {
if ($operator==DB::like())
$w = '/'.preg_quote($w, '/').'/i';
else
$w = '/^'.preg_quote($w, '/').'$/i';
if (preg_match($w,$vvv)!==0) {
$allowed_cd[] = $kkk;
break;
}
}
if (empty($allowed_cd)) {
$having .= $negative?'true':'false';
continue;
}
} else {
self::init($tab2);
$det = explode('/', $col2);
$col2 = explode('|', $det[0]);
//self::init($tab);
if (!is_array($v)) $v = array($v);
$poss_vals = '';
$col2s = array();
$col2m = array();
$conv = '';
if ($postgre) $conv = '::varchar';
foreach ($col2 as $c) {
if (self::$table_rows[self::$hash[$c]]['type']=='multiselect')
$col2m[] = $c.$conv;
else
$col2s[] = $c.$conv;
}
foreach ($v as $w) {
if ($w==='') {
$poss_vals .= 'OR f_'.implode(' IS NULL OR f_', $col2);
break;
} else {
if (!$noquotes) $w = DB::qstr($w);
if (!empty($col2s)) $poss_vals .= ' OR f_'.implode(' '.DB::like().' '.$w.' OR f_', $col2s).' '.DB::like().' '.$w;
if (!empty($col2m)) {
$w = DB::Concat(DB::qstr('%'),DB::qstr('\_\_'),$w,DB::qstr('\_\_'),DB::qstr('%'));
$poss_vals .= ' OR f_'.implode(' '.DB::like().' '.$w.' OR f_', $col2m).' '.DB::like().' '.$w;
}
}
}
$allowed_cd = DB::GetAssoc('SELECT id, id FROM '.$tab2.'_data_1 WHERE false '.$poss_vals);
if (empty($allowed_cd)) {
$having .= $negative?'true':'false';
continue;
}
}
if ($operator==DB::like())
$operator = '=';
$v = $allowed_cd;
$k = $ref;
}
self::init($tab);
if ($k[0]==':') {
switch ($k) {
case ':Fav' : $final_tab = '('.$final_tab.') LEFT JOIN '.$tab.'_favorite AS fav ON fav.'.$tab.'_id=r.id';
$having .= ' (fav.user_id='.Acl::get_user().' AND fav.user_id IS NOT NULL)';
break;
case ':Sub' : $final_tab = '('.$final_tab.') LEFT JOIN utils_watchdog_subscription AS sub ON sub.internal_id=r.id AND sub.category_id='.Utils_WatchdogCommon::get_category_id($tab);
$having .= ' (sub.user_id='.Acl::get_user().' AND sub.user_id IS NOT NULL)';
break;
case ':Recent' : $final_tab = '('.$final_tab.') LEFT JOIN '.$tab.'_recent AS rec ON rec.'.$tab.'_id=r.id';
$having .= ' (rec.user_id='.Acl::get_user().' AND rec.user_id IS NOT NULL)';
break;
case ':Created_on' :
$inj = $operator.'%T';
$timestamp = Base_RegionalSettingsCommon::reg2time($v, false);
$vals[] = $timestamp;
$having .= ' created_on '.$inj;
break;
case ':Created_by' :
$having .= ' created_by = '.$v;
break;
case ':Edited_on' :
$inj = $operator.'%T';
$having .= ' (((SELECT MAX(edited_on) FROM '.$tab.'_edit_history WHERE '.$tab.'_id=r.id) '.$inj.') OR'.
'((SELECT MAX(edited_on) FROM '.$tab.'_edit_history WHERE '.$tab.'_id=r.id) IS NULL AND created_on '.$inj.'))';
$timestamp = Base_RegionalSettingsCommon::reg2time($v, false);
$vals[] = $timestamp;
$vals[] = $timestamp;
break;
default:
trigger_error('Unknow paramter given to get_records criteria: '.$k, E_USER_ERROR);
}
} else {
if ($k == 'id') {
if (!is_array($v)) $v = array($v);
$having .= '('.($negative?'true':'false');
foreach($v as $w) {
if (!$noquotes) $w = DB::qstr($w);
$having .= ' '.($negative?'AND':'OR').($negative?' NOT':'').' id '.$operator.' '.$w;
}
$having .= ')';
} else {
// Postgres compatibility fix
if (!is_array($v)) $v = array($v);
if ($negative) $having .= 'NOT ';
$having .= '(false';
foreach($v as $w) {
if (isset(self::$hash[$k])) {
$f = self::$hash[$k];
$key = $k;
} elseif (isset(self::$table_rows[$k])) {
$f = $k;
$key = self::$table_rows[$k]['id'];
} else trigger_error('In table "'.$tab.'" - unknow column "'.$k.'" in criteria "'.print_r($crits,true).'". Available columns are: "'.print_r(self::$table_rows,true).'"', E_USER_ERROR);
if ($w && self::$table_rows[self::$hash[$key]]['type']=='timestamp' && $operator != DB::like()) {
$w = Base_RegionalSettingsCommon::reg2time($w, false);
$w = date('Y-m-d H:i:s', $w);
} elseif ($w && self::$table_rows[self::$hash[$key]]['type']=='date' && $operator != DB::like()) {
$w = Base_RegionalSettingsCommon::reg2time($w, false);
$w = date('Y-m-d', $w);
}
if ($postgre && $operator==DB::like()) $key .= '::varchar';
if (self::$table_rows[$f]['type']=='checkbox' && !$w) {
if($operator=='=')
$having .= ' OR r.f_'.$key.' IS NULL OR r.f_'.$key.'=0';
else
$having .= ' OR (r.f_'.$key.' IS NOT NULL AND r.f_'.$key.'!=0)';
} elseif (self::$table_rows[$f]['type']!='text' && self::$table_rows[$f]['type']!='long text' && ($w==='' || $w===null || $w===false)) {
if($operator=='=')
$having .= ' OR r.f_'.$key.' IS NULL';
else
$having .= ' OR r.f_'.$key.' IS NOT NULL';
} elseif ($w==='') {
$having .= ' OR r.f_'.$k.' IS NULL OR r.f_'.$k.'=\'\'';
} else {
if (self::$table_rows[$f]['type']=='multiselect') {
$operator = DB::like();
$param = explode('::',self::$table_rows[$f]['param']);
$w = DB::Concat(DB::qstr('%'),DB::qstr('\_\_'.$w.'\_\_'),DB::qstr('%'));
}
elseif (!$noquotes) $w = DB::qstr($w);
if (false || $postgre && ($operator=='<' || $operator=='<=' || $operator=='>' || $operator=='>=')) {
$field_full_name = 'r.f_' . $key;
switch (self::$table_rows[$f]['type']) {
case 'timestamp': $cast_type = 'timestamp'; break;
case 'date': $cast_type = 'date'; break;
case 'currency': $field_full_name = "split_part($field_full_name, '__', 1)";
$cast_type = 'integer';
break;
default: $cast_type = 'integer';
}
$c_field = 'CAST('.$field_full_name.' AS '.$cast_type.')';
} else $c_field = 'r.f_'.$key;
$having .= ' OR ('.$c_field.' '.$operator.' '.$w.' ';
if ($operator=='<' || $operator=='<=') {
$having .= 'OR r.f_'.$key.' IS NULL)';
} else {
$having .= 'AND r.f_'.$key.' IS NOT NULL)';
}
}
}
$having .= ')';
}
}
}
if ($or_started) $having .= ')';
if ($group_or) $having .= '))';
$orderby = array();
self::init($tab);
foreach($order as $v){
if ($v['order'][0]!=':' && !isset(self::$table_rows[$v['order']])) continue; //failsafe
if ($v['order'][0]==':') {
if (!is_numeric(Acl::get_user())) trigger_error('Invalid user id.');
switch ($v['order']) {
case ':id':
$orderby[] = ' id ' . $v['direction'];
case ':Fav' :
$orderby[] = ' (SELECT COUNT(*) FROM '.$tab.'_favorite WHERE '.$tab.'_id=r.id AND user_id='.Acl::get_user().') '.$v['direction'];
break;
case ':Visited_on' :
$orderby[] = ' (SELECT MAX(visited_on) FROM '.$tab.'_recent WHERE '.$tab.'_id=r.id AND user_id='.Acl::get_user().') '.$v['direction'];
break;
case ':Edited_on' :
$orderby[] = ' (CASE WHEN (SELECT MAX(edited_on) FROM '.$tab.'_edit_history WHERE '.$tab.'_id=r.id) IS NOT NULL THEN (SELECT MAX(edited_on) FROM '.$tab.'_edit_history WHERE '.$tab.'_id=r.id) ELSE created_on END) '.$v['direction'];
break;
default :
$orderby[] = ' '.substr($v['order'],1).' ' . $v['direction'];
//trigger_error('Unknow paramter given to get_records order: '.$v, E_USER_ERROR);
}
} else {
self::init($tab);
if (is_array(self::$table_rows[$v['order']]['param']))
$param = explode(';', self::$table_rows[$v['order']]['param']['array_id']);
else
$param = explode(';', self::$table_rows[$v['order']]['param']);
$param = explode('::',$param[0]);
if (isset($param[1]) && $param[1]!='') {
if (self::$table_rows[$v['order']]['type']!='commondata') {
if (!isset($param[1])) $cols = $param[0];
else if ($param[0]!='__COMMON__') {
$tab2 = $param[0];
$cols2 = $param[1];
$cols2 = explode('|', $cols2);
$cols2 = $cols2[0];
$cols2 = explode('/', $cols2);
if (isset($cols2[1])) $data_col = self::$table_rows[$cols2[1]]['id']; else $data_col = self::$table_rows[$v['order']]['id'];
$cols2 = $cols2[0];
$val = '(SELECT rdt.f_'.self::get_field_id($cols2).' FROM '.$tab.'_data_1 AS rd LEFT JOIN '.$tab2.'_data_1 AS rdt ON rdt.id=rd.f_'.$data_col.' WHERE r.id=rd.id)';
$orderby[] = ' '.$val.' '.$v['direction'];
$iter++;
continue;
}
}
}
$val = 'f_'.self::$table_rows[$v['order']]['id'];
if (self::$table_rows[$v['order']]['type'] == 'currency') {
if (DB::is_mysql()) {
$val = "CAST($val as INT)";
} elseif (DB::is_postgresql()) {
$val = "CAST(split_part($val, '__', 1) as integer)";
}
}
$orderby[] = ' '.$val.' '.$v['direction'];
$iter++;
}
}
if (!empty($orderby)) $orderby = ' ORDER BY'.implode(', ',$orderby);
else $orderby = '';
if (!$having) $having = 'true';
$final_tab = str_replace('('.$tab.'_data_1 AS r'.')',$tab.'_data_1 AS r',$final_tab);
$ret = array('sql'=>' '.$final_tab.' WHERE '.($admin?self::$admin_filter:'active=1 AND ').$having,'order'=>$orderby,'vals'=>$vals);
return $cache[$cache_key] = $ret;
}
