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