Example #1
0
 public static function set()
 {
     if (self::$action) {
         return;
     }
     if (!isset($_SESSION['client']['__history_id__'])) {
         $_SESSION['client']['__history_id__'] = 0;
     }
     $data = serialize($_SESSION['client']['__module_vars__']);
     if (GZIP_HISTORY && function_exists('gzcompress')) {
         $data = gzcompress($data);
     }
     if (DB::is_postgresql()) {
         $data = '\'' . DB::BlobEncode($data) . '\'';
     } else {
         $data = DB::qstr($data);
     }
     DB::StartTrans();
     DB::Replace('history', array('data' => $data, 'page_id' => $_SESSION['client']['__history_id__'], 'session_name' => DB::qstr(self::session_id()), 'client_id' => CID), array('session_name', 'page_id'));
     $_SESSION['client']['__history_id__']++;
     $ret = DB::Execute('SELECT page_id FROM history WHERE session_name=%s AND (page_id>=%d OR page_id<%d) AND client_id=%d', array(self::session_id(), $_SESSION['client']['__history_id__'], $_SESSION['client']['__history_id__'] - 20, CID));
     while ($row = $ret->FetchRow()) {
         DB::Execute('DELETE FROM history WHERE session_name=%s AND page_id=%d AND client_id=%d', array(self::session_id(), $row['page_id'], CID));
     }
     DB::CompleteTrans();
 }
Example #2
0
 public static function unserialize_crits($str)
 {
     $ret = unserialize($str);
     if ($ret === false && DB::is_postgresql()) {
         $ret = unserialize(hex2bin($str));
     }
     return $ret;
 }
Example #3
0
 public static function like()
 {
     static $like = null;
     if ($like === null) {
         if (!DB::is_postgresql()) {
             $like = 'LIKE';
         } else {
             $like = 'ILIKE';
         }
     }
     return $like;
 }
Example #4
0
        if (DB::is_postgresql()) {
            DB::Execute('ALTER TABLE recordbrowser_table_properties DROP CONSTRAINT recordbrowser_table_properties_pkey');
            DB::Execute('ALTER TABLE recordbrowser_table_properties ADD COLUMN id SERIAL PRIMARY KEY');
        } else {
            DB::Execute('ALTER TABLE recordbrowser_table_properties DROP PRIMARY KEY');
            DB::Execute('ALTER TABLE recordbrowser_table_properties ADD id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY');
        }
        DB::CreateIndex('recordbrowser_table_properties_tab', 'recordbrowser_table_properties', 'tab', array('UNIQUE' => 1));
        $tab_ids_checkpoint->done();
    }
}
$field_ids_checkpoint = Patch::checkpoint('field_ids');
if (!$field_ids_checkpoint->is_done()) {
    Patch::require_time(20);
    $recordsets = Utils_RecordBrowserCommon::list_installed_recordsets();
    foreach ($recordsets as $tab => $caption) {
        $tab_f = $tab . "_field";
        $columns = DB::MetaColumnNames($tab_f);
        if (!isset($columns['ID'])) {
            if (DB::is_postgresql()) {
                @DB::Execute('ALTER TABLE ' . $tab_f . ' DROP CONSTRAINT ' . $tab_f . '_pkey');
                DB::Execute('ALTER TABLE ' . $tab_f . ' ADD COLUMN id SERIAL PRIMARY KEY');
            } else {
                @DB::Execute('ALTER TABLE ' . $tab_f . ' DROP PRIMARY KEY');
                DB::Execute('ALTER TABLE ' . $tab_f . ' ADD id SMALLINT NOT NULL AUTO_INCREMENT PRIMARY KEY');
            }
            DB::CreateIndex($tab_f . '_field', $tab_f, 'field', array('UNIQUE' => 1));
        }
    }
    $field_ids_checkpoint->done();
}
Example #5
0
    public function view_field($action = 'add', $field = null) {
        if (!$action) $action = 'add';
        if ($this->is_back()) return false;
        if ($this->check_for_jump()) return;
        $data_type = array(
            'autonumber'=>__('Autonumber'),
            'currency'=>__('Currency'),
            'checkbox'=>__('Checkbox'),
            'date'=>__('Date'),
            'time' => __('Time'),
            'timestamp' => __('Timestamp'),
            'integer'=>__('Integer'),
            'float'=>__('Float'),
            'text'=>__('Text'),
            'long text'=>__('Long text'),
			'select'=>__('Select field')
        );
        natcasesort($data_type);
		
		load_js('modules/Utils/RecordBrowser/js/field_admin.js');
        $form = $this->init_module('Libs/QuickForm');

        switch ($action) {
            case 'add': $form->addElement('header', null, __('Add new field'));
                        break;
            case 'edit': $form->addElement('header', null, __('Edit field properties'));
                        break;
        }
        $form->addElement('text', 'field', __('Field'), array('maxlength'=>32));
        $form->registerRule('check_if_column_exists', 'callback', 'check_if_column_exists', $this);
        $this->current_field = $field;
        $form->registerRule('check_if_no_id', 'callback', 'check_if_no_id', $this);
        $form->addRule('field', __('Field required'), 'required');
        $form->addRule('field', __('Field with this name already exists.'), 'check_if_column_exists');
        $form->addRule('field', __('Field length cannot be over 32 characters.'), 'maxlength', 32);
        $form->addRule('field', __('Invalid field name.'), 'regex', '/^[a-zA-Z][a-zA-Z \(\)\%0-9]*$/');
        $form->addRule('field', __('Invalid field name.'), 'check_if_no_id');

        $form->addElement('text', 'caption', __('Caption'), array('maxlength'=>255, 'placeholder' => __('Leave empty to use default label')));

        if ($action=='edit') {
            $row = DB::GetRow('SELECT field, caption, type, visible, required, param, filter, export, tooltip, extra, position FROM '.$this->tab.'_field WHERE field=%s',array($field));
			switch ($row['type']) {
				case 'select':
					$row['select_data_type'] = 'select';
					$row['select_type'] = 'select';
					$row['data_source'] = 'rset';
					$ref = explode(';', $row['param']);
					$refe = explode('::',$ref[0]);
					$row['rset'] = array_filter(explode(',',$refe[0]));
					$row['label_field'] = isset($refe[1]) ? str_replace('|', ',', $refe[1]) : '';
					break;
				case 'multiselect':
					$row['select_data_type'] = 'select';
					$row['select_type'] = 'multiselect';
					$ref = explode(';', $row['param']);
					$refe = explode('::',$ref[0]);
					$tab = $refe[0];
					if ($tab=='__COMMON__') {
						$row['data_source'] = 'commondata';
						$order = isset($refe[2])?$refe[2]:'value';
						$row['order_by'] = ($order=='key'?'key':'value');
						$row['commondata_table'] = $refe[1];
					} else {
						$row['label_field'] = '';
                        if (isset($refe[1]))
                            $row['label_field'] = str_replace('|', ',', $refe[1]);
						$row['data_source'] = 'rset';
						$row['rset'] = array_filter(explode(',',$tab));
					}
					break;
				case 'commondata':
					$row['select_data_type'] = 'select';
					$row['select_type'] = 'select';
					$row['data_source'] = 'commondata';
					$param = Utils_RecordBrowserCommon::decode_commondata_param($row['param']);
					$form->setDefaults(array('order_by'=>$param['order_by_key']?'key':'value', 'commondata_table'=>$param['array_id']));
					break;
                case 'autonumber':
                    $row['select_data_type'] = 'autonumber';
                    Utils_RecordBrowserCommon::decode_autonumber_param($row['param'], $autonumber_prefix, $autonumber_pad_length, $autonumber_pad_mask);
                    $row['autonumber_prefix'] = $autonumber_prefix;
                    $row['autonumber_pad_length'] = $autonumber_pad_length;
                    $row['autonumber_pad_mask'] = $autonumber_pad_mask;
                    break;
				case 'text':
                    $row['select_data_type'] = $row['type'];
					$row['text_length'] = $row['param'];
                    break;
                case 'time':
                case 'timestamp':
                    $row['select_data_type'] = $row['type'];
                    $row['minute_increment'] = $row['param'];
                    break;
				default:
					$row['select_data_type'] = $row['type'];
					if (!isset($data_type[$row['type']]))
						$data_type[$row['type']] = _V(ucfirst($row['type'])); // ****** - field type
			}
			if (!isset($row['rset'])) $row['rset'] = array('contact');
			if (!isset($row['data_source'])) $row['data_source'] = 'commondata';
            $form->setDefaults($row);
            $selected_data = $row['type'];
			$this->admin_field_type = $row['select_data_type'];
			$this->admin_field = $row;
        } else {
            $selected_data = $form->exportValue('select_data_type');
            $form->setDefaults(array('visible'=>1,
                'autonumber_prefix'=>'#',
                'autonumber_pad_length'=>'6',
                'autonumber_pad_mask'=>'0'));
        }
		$this->admin_field_mode = $action;
		$this->admin_field_name = $field;
		
		$form->addElement('select', 'select_data_type', __('Data Type'), $data_type, array('id'=>'select_data_type', 'onchange'=>'RB_hide_form_fields()'));

		$form->addElement('text', 'text_length', __('Maximum Length'), array('id'=>'length'));
        $minute_increment_values = array(1=>1,2=>2,5=>5,10=>10,15=>15,20=>20,30=>30,60=>__('Full hours'));
		$form->addElement('select', 'minute_increment', __('Minutes Interval'), $minute_increment_values, array('id'=>'minute_increment'));

		$form->addElement('select', 'data_source', __('Source of Data'), array('rset'=>__('Recordset'), 'commondata'=>__('CommonData')), array('id'=>'data_source', 'onchange'=>'RB_hide_form_fields()'));
		$form->addElement('select', 'select_type', __('Type'), array('select'=>__('Single value selection'), 'multiselect'=>__('Multiple values selection')), array('id'=>'select_type'));
		$form->addElement('select', 'order_by', __('Order by'), array('key'=>__('Key'), 'value'=>__('Value')), array('id'=>'order_by'));
		$form->addElement('text', 'commondata_table', __('CommonData table'), array('id'=>'commondata_table'));

		$tables = Utils_RecordBrowserCommon::list_installed_recordsets();
		asort($tables);
		$form->addElement('multiselect', 'rset', '<span id="rset_label">'.__('Recordset').'</span>', $tables, array('id'=>'rset'));
		$form->addElement('text', 'label_field', __('Related field(s)'), array('id'=>'label_field'));

		$form->addFormRule(array($this, 'check_field_definitions'));

		$form->addElement('checkbox', 'visible', __('Table view'));
		$form->addElement('checkbox', 'tooltip', __('Tooltip view'));
		$form->addElement('checkbox', 'required', __('Required'), null, array('id'=>'required'));
		$form->addElement('checkbox', 'filter', __('Filter enabled'), null, array('id' => 'filter'));
		$form->addElement('checkbox', 'export', __('Export'));
        
        $form->addElement('text', 'autonumber_prefix', __('Prefix string'), array('id' => 'autonumber_prefix'));
        $form->addRule('autonumber_prefix', __('Double underscore is not allowed'), 'callback', array('Utils_RecordBrowser', 'qf_rule_without_double_underscore'));
        $form->addElement('text', 'autonumber_pad_length', __('Pad length'), array('id' => 'autonumber_pad_length'));
        $form->addRule('autonumber_pad_length', __('Only integer numbers are allowed.'), 'regex', '/^[0-9]*$/');
        $form->addElement('text', 'autonumber_pad_mask', __('Pad character'), array('id' => 'autonumber_pad_mask'));
        $form->addRule('autonumber_pad_mask', __('Double underscore is not allowed'), 'callback', array('Utils_RecordBrowser', 'qf_rule_without_double_underscore'));

		$form->addElement('checkbox', 'advanced', __('Edit advanced properties'), null, array('onchange'=>'RB_advanced_settings()', 'id'=>'advanced'));
		$form->addElement('text', 'display_callback', __('Value display function'), array('maxlength'=>255, 'style'=>'width:300px', 'id'=>'display_callback'));
		$form->addElement('text', 'QFfield_callback', __('Field generator function'), array('maxlength'=>255, 'style'=>'width:300px', 'id'=>'QFfield_callback'));
		
        if ($action=='edit') {
			$form->freeze('field');
			$form->freeze('select_data_type');
			$form->freeze('data_source');
			$form->freeze('rset');
		}
		
		if ($action=='edit') {
			$display_callbacback = DB::GetOne('SELECT callback FROM '.$this->tab.'_callback WHERE freezed=1 AND field=%s', array($field));
			$QFfield_callbacback = DB::GetOne('SELECT callback FROM '.$this->tab.'_callback WHERE freezed=0 AND field=%s', array($field));
			$form->setDefaults(array('display_callback'=>$display_callbacback));
			$form->setDefaults(array('QFfield_callback'=>$QFfield_callbacback));
		}

        if ($form->validate()) {
            $data = $form->exportValues();
            $data['caption'] = trim($data['caption']);
            $data['field'] = trim($data['field']);
			$type = DB::GetOne('SELECT type FROM '.$this->tab.'_field WHERE field=%s', array($field));
			if (!isset($data['select_data_type'])) $data['select_data_type'] = $type;
            if ($action=='add')
                $field = $data['field'];
            $id = preg_replace('/[^a-z0-9]/','_',strtolower($field));
            $new_id = preg_replace('/[^a-z0-9]/','_',strtolower($data['field']));
            if (preg_match('/^[a-z0-9_]*$/',$id)==0) trigger_error('Invalid column name: '.$field);
            if (preg_match('/^[a-z0-9_]*$/',$new_id)==0) trigger_error('Invalid new column name: '.$data['field']);
			$param = '';
			switch ($data['select_data_type']) {
                case 'autonumber':
                    $data['required'] = false;
                    $data['filter'] = false;
                    $param = Utils_RecordBrowserCommon::encode_autonumber_param(
                            $data['autonumber_prefix'],
                            $data['autonumber_pad_length'],
                            $data['autonumber_pad_mask']);
                    // delete field and add again later to generate values
                    if ($action != 'add') {
                        Utils_RecordBrowserCommon::delete_record_field($this->tab, $field);
                        $action = 'add';
                        $field = $data['field'];
                    }
                    break;
				case 'checkbox': 
							$data['required'] = false;
							break;
				case 'text': if ($action=='add') $param = $data['text_length'];
							else {
								if ($data['text_length']<$row['param']) trigger_error('Invalid field length', E_USER_ERROR);
								$param = $data['text_length'];
								if ($data['text_length']!=$row['param']) {
									if(DB::is_postgresql())
										DB::Execute('ALTER TABLE '.$this->tab.'_data_1 ALTER COLUMN f_'.$id.' TYPE VARCHAR('.$param.')');
									else
										DB::Execute('ALTER TABLE '.$this->tab.'_data_1 MODIFY f_'.$id.' VARCHAR('.$param.')');
								}
							}
							break;
				case 'select':
							if ($data['data_source']=='commondata') {
								if ($data['select_type']=='select') {
									$param = Utils_RecordBrowserCommon::encode_commondata_param(array('order_by_key'=>$data['order_by']=='key', 'array_id'=>$data['commondata_table']));
									$data['select_data_type'] = 'commondata';
								} else {
									$param = '__COMMON__::'.$data['commondata_table'].'::'.$data['order_by'];
									$data['select_data_type'] = 'multiselect';
								}
							} else {
								$data['select_data_type'] = $data['select_type'];
								if (!isset($row) || !isset($row['param'])) $row['param'] = ';::';
								$props = explode(';', $row['param']);
                                $change_param = false;
								if($data['rset']) {
								    $fs = explode(',', $data['label_field']);
								    if($data['label_field']) foreach($data['rset'] as $rset) {
        								$ret = $this->detranslate_field_names($rset, $fs);
	        							if (!empty($ret)) trigger_error('Invalid fields: '.implode(',',$fs));
	        						    }
	        						    $data['rset'] = implode(',',$data['rset']);
	        						    $data['label_field'] = implode('|',$fs);
                                    $change_param = true;
								} else if ($action == 'add') {
								    $data['rset'] = '__RECORDSETS__';
								    $data['label_field'] = '';
                                    $change_param = true;
								}
                                if ($change_param) {
                                    $props[0] = $data['rset'].'::'.$data['label_field'];
                                    $param = implode(';', $props);
                                } else {
                                    $param = $row['param'];
                                }
							}
							if (isset($row) && isset($row['type']) && $row['type']=='multiselect' && $data['select_type']=='select') {
								$ret = DB::Execute('SELECT id, f_'.$id.' AS v FROM '.$this->tab.'_data_1 WHERE f_'.$id.' IS NOT NULL');
								while ($rr = $ret->FetchRow()) {
									$v = Utils_RecordBrowserCommon::decode_multi($rr['v']);
									$v = array_pop($v);
									DB::Execute('UPDATE '.$this->tab.'_data_1 SET f_'.$id.'=%s WHERE id=%d', array($v, $rr['id']));
								}
							}
							if (isset($row) && isset($row['type'])  && $row['type']!='multiselect' && $data['select_type']=='multiselect') {
								if(DB::is_postgresql())
									DB::Execute('ALTER TABLE '.$this->tab.'_data_1 ALTER COLUMN f_'.$id.' TYPE TEXT');
								else
									DB::Execute('ALTER TABLE '.$this->tab.'_data_1 MODIFY f_'.$id.' TEXT');
								$ret = DB::Execute('SELECT id, f_'.$id.' AS v FROM '.$this->tab.'_data_1 WHERE f_'.$id.' IS NOT NULL');
								while ($rr = $ret->FetchRow()) {
									$v = Utils_RecordBrowserCommon::encode_multi($rr['v']);
									DB::Execute('UPDATE '.$this->tab.'_data_1 SET f_'.$id.'=%s WHERE id=%d', array($v, $rr['id']));
								}
							}
							break;
                case 'time':
                case 'timestamp':
                    $param = $data['minute_increment'];
                    break;
				default:	if (isset($row) && isset($row['param']))
								$param = $row['param'];
							break;
			}
            if ($action=='add') {
                $id = $new_id;
                if (in_array($data['select_data_type'], array('time','timestamp','currency','integer')))
                    $style = $data['select_data_type'];
                else
                    $style = '';
                $new_field_data = array('name' => $data['field'], 'type' => $data['select_data_type'], 'param' => $param, 'style' => $style);
                if (isset($this->admin_field['position']) && $this->admin_field['position']) {
                    $new_field_data['position'] = (int) $this->admin_field['position'];
                }
                Utils_RecordBrowserCommon::new_record_field($this->tab, $new_field_data);
            }
            if(!isset($data['visible']) || $data['visible'] == '') $data['visible'] = 0;
            if(!isset($data['required']) || $data['required'] == '') $data['required'] = 0;
            if(!isset($data['filter']) || $data['filter'] == '') $data['filter'] = 0;
            if(!isset($data['export']) || $data['export'] == '') $data['export'] = 0;
            if(!isset($data['tooltip']) || $data['tooltip'] == '') $data['tooltip'] = 0;

            foreach($data as $key=>$val)
                if (is_string($val)) $data[$key] = htmlspecialchars($val);

/*            DB::StartTrans();
            if ($id!=$new_id) {
                Utils_RecordBrowserCommon::check_table_name($this->tab);
                if(DB::is_postgresql())
                    DB::Execute('ALTER TABLE '.$this->tab.'_data_1 RENAME COLUMN f_'.$id.' TO f_'.$new_id);
                else {
                    $old_param = DB::GetOne('SELECT param FROM '.$this->tab.'_field WHERE field=%s', array($field));
                    DB::RenameColumn($this->tab.'_data_1', 'f_'.$id, 'f_'.$new_id, Utils_RecordBrowserCommon::actual_db_type($type, $old_param));
                }
            }*/
            DB::Execute('UPDATE '.$this->tab.'_field SET caption=%s, param=%s, type=%s, field=%s, visible=%d, required=%d, filter=%d, export=%d, tooltip=%d WHERE field=%s',
                        array($data['caption'], $param, $data['select_data_type'], $data['field'], $data['visible'], $data['required'], $data['filter'], $data['export'], $data['tooltip'], $field));
/*            DB::Execute('UPDATE '.$this->tab.'_edit_history_data SET field=%s WHERE field=%s',
                        array($new_id, $id));
            DB::CompleteTrans();*/
			
			DB::Execute('DELETE FROM '.$this->tab.'_callback WHERE freezed=1 AND field=%s', array($field));
			if ($data['display_callback'])
				DB::Execute('INSERT INTO '.$this->tab.'_callback (callback,freezed,field) VALUES (%s,1,%s)', array($data['display_callback'], $data['field']));
				
			DB::Execute('DELETE FROM '.$this->tab.'_callback WHERE freezed=0 AND field=%s', array($field));
			if ($data['QFfield_callback'])
				DB::Execute('INSERT INTO '.$this->tab.'_callback (callback,freezed,field) VALUES (%s,0,%s)', array($data['QFfield_callback'], $data['field']));
			
            $this->init(true, true);
            return false;
        }
        $form->display_as_column();

		eval_js('RB_hide_form_fields();');
		eval_js('RB_advanced_confirmation = "'.Epesi::escapeJS(__('Changing these settings may often cause system unstability. Are you sure you want to see advanced settings?')).'";');
		eval_js('RB_advanced_settings();');

		Base_ActionBarCommon::add('save', __('Save'), $form->get_submit_form_href());
		Base_ActionBarCommon::add('back', __('Cancel'), $this->create_back_href());
		
        return true;
    }
Example #6
0
 protected function hf_select($field, $operator, $value, $raw_sql_val, $field_def)
 {
     $commondata = isset($field_def['commondata']) && $field_def['commondata'];
     if ($commondata) {
         return $this->hf_commondata($field, $operator, $value, $raw_sql_val, $field_def);
     }
     $sql = '';
     $vals = array();
     list($field, $sub_field) = Utils_RecordBrowser_CritsSingle::parse_subfield($field);
     $multiselect = $field_def['type'] == 'multiselect';
     $tab2 = isset($field_def['ref_table']) ? $field_def['ref_table'] : false;
     $single_tab = !($tab2 == '__RECORDSETS__' || count(explode(',', $tab2)) > 1);
     if ($sub_field && $single_tab && $tab2) {
         $col2 = explode('|', $sub_field);
         if (!isset($this->subqueries_tab_ids[$tab2])) {
             $this->subqueries_tab_ids[$tab2] = 0;
         }
         $tab_alias_id = $this->subqueries_tab_ids[$tab2]++;
         $nested_tab_alias = $this->tab_alias . '_' . $tab2 . '_' . $tab_alias_id;
         $crits = new Utils_RecordBrowser_Crits();
         foreach ($col2 as $col) {
             $col = $col[0] == ':' ? $col : Utils_RecordBrowserCommon::get_field_id(trim($col));
             if ($col) {
                 $crits->_or(new Utils_RecordBrowser_CritsSingle($col, $operator, $value, false, $raw_sql_val));
             }
         }
         if (!$crits->is_empty()) {
             $subquery = Utils_RecordBrowserCommon::build_query($tab2, $crits, $this->admin_mode, array(), $nested_tab_alias);
             $on_rule = $multiselect ? "{$field} LIKE CONCAT('%\\_\\_', {$nested_tab_alias}.id, '\\_\\_%')" : "{$field} = {$nested_tab_alias}.id";
             $sql = "EXISTS (SELECT 1 FROM {$subquery['sql']} AND {$on_rule})";
             $vals = $subquery['vals'];
         }
     } else {
         if ($raw_sql_val) {
             $sql = "{$field} {$operator} {$value}";
         } elseif (!$value) {
             $sql = "{$field} IS NULL";
             if (!$single_tab || $multiselect) {
                 $sql .= " OR {$field}=''";
             }
         } else {
             if ($single_tab && !$multiselect && $operator != DB::like()) {
                 $operand = '%d';
             } else {
                 if (DB::is_postgresql()) {
                     $field .= '::varchar';
                 }
                 $operand = '%s';
             }
             if ($multiselect) {
                 $value = "%\\_\\_{$value}\\_\\_%";
                 $operator = DB::like();
             }
             $sql = "({$field} {$operator} {$operand} AND {$field} IS NOT NULL)";
             $vals[] = $value;
         }
     }
     return array($sql, $vals);
 }
Example #7
0
function clean_database()
{
    require_once 'include/config.php';
    require_once 'include/database.php';
    $tables_db = DB::MetaTables();
    $tables = array();
    if (DB::is_mysql()) {
        DB::Execute('SET FOREIGN_KEY_CHECKS=0');
    }
    if (DB::is_postgresql() && strpos(DB::GetOne('SELECT version()'), 'PostgreSQL 8.2') !== false) {
        foreach ($tables_db as $t) {
            $idxs = DB::Execute('SELECT t.tgargs as args FROM pg_trigger t,pg_class c,pg_proc p WHERE t.tgenabled AND t.tgrelid = c.oid AND t.tgfoid = p.oid AND p.proname = \'RI_FKey_check_ins\' AND c.relname = \'' . strtolower($t) . '\' ORDER BY t.tgrelid');
            $matches = array(1 => array());
            while ($i = $idxs->FetchRow()) {
                $data = explode(chr(0), $i[0]);
                $matches[1][] = $data[0];
            }
            $num_keys = count($matches[1]);
            for ($i = 0; $i < $num_keys; $i++) {
                DB::Execute('ALTER TABLE ' . $t . ' DROP CONSTRAINT ' . $matches[1][$i]);
            }
        }
    }
    foreach ($tables_db as $t) {
        DB::DropTable($t);
    }
    if (DB::is_mysql()) {
        DB::Execute('SET FOREIGN_KEY_CHECKS=1');
    }
}
Example #8
0
	public function applet() {

		$gb = $this->init_module('Utils/GenericBrowser', null, 'agenda');
		$columns = array(
			array('name'=>__('Done'), 'order'=>'done', 'width'=>5),
			array('name'=>__('Start'), 'order'=>'alert_on', 'width'=>15),
			array('name'=>__('Info'), 'width'=>80)
		);
		$gb->set_table_columns($columns);

		$gb->set_default_order(array(__('Start')=>'ASC'));

		$this->lp = $this->init_module('Utils_LeightboxPrompt');
		$this->lp->add_option('holdon_'.(5*60), __('5 minutes'),null);
		$this->lp->add_option('holdon_'.(15*60), __('15 minutes'),null);
		$this->lp->add_option('holdon_'.(30*60), __('30 minutes'),null);
		$this->lp->add_option('holdon_'.(60*60), __('1 hour'),null);
		$this->lp->add_option('holdon_'.(240*60), __('4 hours'),null);
		$this->lp->add_option('holdon_'.(12*3600), __('12 hours'),null);
		$this->lp->add_option('holdon_'.(24*3600), __('24 hours'),null);
    	$this->display_module($this->lp, array('Hold on', array('alert_id'), '', false));
		$vals = $this->lp->export_values();
		if ($vals) {
		    if(preg_match('/^holdon_([0-9]+)$/',$vals['option'],$reqs)) {
		        $_SESSION['utils_messenger_holdon'][$vals['params']['alert_id']] = time()+$reqs[1];
    		}
		}

		$t = time();
        // postgres compatible interval with type cast
        $interval = DB::is_postgresql() ? '::timestamp - interval \'1 hour\'' : '-INTERVAL 1 hour';
		$ret = DB::Execute('(SELECT u.done,m.* FROM utils_messenger_message m INNER JOIN utils_messenger_users u ON u.message_id=m.id WHERE u.user_login_id=%d AND u.done=0 AND m.alert_on<%T)'.
					' UNION '.
				'(SELECT u.done,m.* FROM utils_messenger_message m INNER JOIN utils_messenger_users u ON u.message_id=m.id WHERE u.user_login_id=%d AND m.alert_on<%T AND u.done_on>=%T'.$interval.' AND u.done=1 ORDER BY m.alert_on DESC LIMIT 3)'.
					' UNION '.
				'(SELECT 0 as done,m.* FROM utils_messenger_message m INNER JOIN utils_messenger_users u ON u.message_id=m.id WHERE u.user_login_id=%d AND m.alert_on>=%T ORDER BY m.alert_on ASC LIMIT 5)'.$gb->get_query_order(),array(Acl::get_user(),$t,Acl::get_user(),$t,$t,Acl::get_user(),$t));

		while($row = $ret->FetchRow()) {
			$info = call_user_func_array(unserialize($row['callback_method']),unserialize($row['callback_args']));
			$info = str_replace("\n",'<br>',$info);
			$alert_on = Base_RegionalSettingsCommon::time2reg($row['alert_on']);
			$gb->add_row(($row['done']?'<span class="checkbox_on" />':'<a '.Utils_TooltipCommon::open_tag_attrs(__('Turn off alarm')).' '.$this->create_confirm_callback_href(__('Are you sure you want to turn off the alarm?'),array('Utils_MessengerCommon','turn_off'),array($row['id'])).'><span class="checkbox_off" /></a>'),
			        (($row['done'] || $row['alert_on']>$t)?$alert_on:'<a '.Utils_TooltipCommon::open_tag_attrs(__('Hold on')).' '.$this->lp->get_href(array($row['id'])).'>'.$alert_on.'</a>'),
			        $info.'<br>'.($row['message']?__('Alarm comment: %s',array($row['message'])):''));
		}

		$this->display_module($gb);
	}
Example #9
0
    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;
    }
Example #10
0
    public static function cron2() {
        $interval = DB::is_postgresql() ? DB::qstr('4 minute') : '4 minute';
		$arr = DB::GetAll('SELECT m.*,u.* FROM utils_messenger_message m INNER JOIN utils_messenger_users u ON u.message_id=m.id WHERE u.follow=0 AND m.alert_on+INTERVAL ' . $interval . ' <%T',array(time()));
		foreach($arr as $row) {
			Acl::set_user($row['user_login_id']);
			$always_follow = Base_User_SettingsCommon::get('Utils_Messenger','always_follow_me');
			if(!$always_follow && $row['done']) continue;
			ob_start();
			$fret = call_user_func_array(unserialize($row['callback_method']),unserialize($row['callback_args']));
			ob_end_clean();
			DB::Execute('UPDATE utils_messenger_users SET follow=1 WHERE message_id=%d AND user_login_id=%d',array($row['id'],$row['user_login_id']));

			$mail = Base_User_SettingsCommon::get('Utils_Messenger','mail');
			if($mail) {
				$msg = __('Alert on: %s',array(Base_RegionalSettingsCommon::time2reg($row['alert_on'],2)))."\n".$fret."\n".($row['message']?__('Alarm comment: %s',array($row['message'])):'');
				Base_MailCommon::send($mail,'Alert!',$msg);
			}
			Acl::set_user();
		}
		
		return '';
	}
Example #11
0
 public function body()
 {
     if (!Base_AclCommon::check_permission('View Activity Report')) {
         return;
     }
     $rb_tabs = DB::GetAssoc('SELECT tab, caption FROM recordbrowser_table_properties ORDER BY caption');
     foreach ($rb_tabs as $k => $v) {
         $rb_tabs[$k] = Utils_RecordBrowserCommon::get_caption($k);
     }
     $form = $this->init_module('Libs/QuickForm');
     $users_count = DB::GetOne('SELECT COUNT(id) FROM user_login') > Base_User_SettingsCommon::get('Utils_RecordBrowser', 'enable_autocomplete');
     if ($users_count) {
         $crits = array('!login' => '');
         $fcallback = array('CRM_ContactsCommon', 'contact_format_no_company');
         $form->addElement('autoselect', 'user', __('User'), array(), array(array('CRM_ContactsCommon', 'autoselect_contact_suggestbox'), array($crits, $fcallback)), $fcallback);
     } else {
         $users = DB::GetAssoc('SELECT id, id FROM user_login');
         foreach ($users as $k => $u) {
             $users[$k] = Base_UserCommon::get_user_label($u, true);
         }
         asort($users);
         $users = array('' => '[' . __('All') . ']') + $users;
         $form->addElement('select', 'user', __('User'), $users);
     }
     $form->addElement('multiselect', 'recordsets', __('Record Type'), $rb_tabs);
     $form->addElement('checkbox', 'new', __('New record'));
     $form->addElement('checkbox', 'edit', __('Record edit'));
     $form->addElement('checkbox', 'delete_restore', __('Record Delete/restore'));
     $form->addElement('checkbox', 'file', __('Files'));
     $form->addElement('datepicker', 'start_date', __('Start Date'));
     $form->addElement('datepicker', 'end_date', __('End Date'));
     //$form->addElement('submit', 'submit', __('Show'));
     Base_ActionBarCommon::add('search', __('Show'), $form->get_submit_form_href());
     $filters = $this->get_module_variable('filters', array('user' => '', 'new' => 1, 'edit' => 1, 'delete_restore' => 1, 'recordsets' => array_keys($rb_tabs), 'start_date' => date('Y-m-01'), 'end_date' => date('Y-m-d')));
     if ($form->validate()) {
         $filters = $form->exportValues();
         $this->set_module_variable('filters', $filters);
     }
     $form->setDefaults($filters);
     $theme = $this->init_module('Base/Theme');
     $form->assign_theme('form', $theme);
     $theme->display();
     $filters['recordsets'] = array_flip($filters['recordsets']);
     foreach ($rb_tabs as $k => $v) {
         if (!isset($filters['recordsets'][$k])) {
             unset($rb_tabs[$k]);
         }
     }
     $gb = $this->init_module('Utils/GenericBrowser', null, 'activity_report');
     $gb->set_table_columns(array(array('name' => __('Date'), 'width' => 40), array('name' => __('User'), 'width' => 40), array('name' => __('Type'), 'width' => 40), array('name' => __('Label')), array('name' => __('Actions taken'), 'width' => 40)));
     $tables = array();
     if ($users_count) {
         $filters['user'] = CRM_ContactsCommon::get_contact($filters['user']);
         $filters['user'] = $filters['user']['login'];
     }
     $af_where = array();
     foreach ($rb_tabs as $k => $t) {
         $af_where[] = 'ual.local ' . DB::like() . ' ' . DB::Concat(DB::qstr($k . '/'), DB::qstr('%'));
     }
     $af_where = count($af_where) ? ' (' . implode(' OR ', $af_where) . ')' : 'TRUE ';
     $e_where = array();
     $c_where = '';
     if ($filters['user']) {
         $e_where[] = ' edited_by = ' . $filters['user'];
         $c_where = ' created_by = ' . $filters['user'];
         $af_where .= ' AND uaf.created_by = ' . $filters['user'];
     }
     if (isset($filters['edit'])) {
         if (!isset($filters['delete_restore'])) {
             $e_where[] = ' ehd.field!=' . DB::qstr('id');
         }
     } else {
         if (isset($filters['delete_restore'])) {
             $e_where[] = ' ehd.field=' . DB::qstr('id');
         }
     }
     if ($filters['start_date']) {
         $date = DB::qstr(date('Y-m-d', strtotime($filters['start_date'])));
         $af_where .= ' AND uaf.created_on >= ' . $date;
         $c_where .= ($c_where ? ' AND' : '') . ' created_on >= ' . $date;
         $e_where[] = ' edited_on >= ' . $date;
     }
     if ($filters['end_date']) {
         $date = DB::qstr(date('Y-m-d 23:59:59', strtotime($filters['end_date'])));
         $af_where .= ' AND uaf.created_on <= ' . $date;
         $c_where .= ($c_where ? ' AND' : '') . ' created_on <= ' . $date;
         $e_where[] = ' edited_on <= ' . $date;
     }
     if (!empty($e_where)) {
         $e_where = ' WHERE' . implode(' AND', $e_where);
     } else {
         $e_where = '';
     }
     if ($c_where) {
         $c_where = ' WHERE' . $c_where;
     }
     $postgre_cast_type = DB::is_postgresql() ? '::varchar' : '';
     // **** files ****
     if (isset($filters['file'])) {
         $tables[] = 'SELECT uaf.id AS id,uaf.created_on AS edited_on,uaf.created_by AS edited_by, ual.local AS r_id, ' . DB::qstr('') . ' AS tab, ' . DB::qstr('file') . ' AS action FROM utils_attachment_file uaf INNER JOIN utils_attachment_data_1 ua ON uaf.attach_id=ua.id INNER JOIN utils_attachment_local ual ON ua.id=ual.attachment WHERE original!=' . DB::qstr('') . ' AND ' . $af_where;
     }
     // **** edit ****
     if (isset($filters['edit']) || isset($filters['delete_restore'])) {
         foreach ($rb_tabs as $k => $t) {
             $tables[] = 'SELECT id, edited_on, edited_by, ' . $k . '_id' . $postgre_cast_type . ' as r_id, ' . DB::qstr($k) . ' as tab, ' . DB::qstr('edit') . ' as action FROM ' . $k . '_edit_history eh LEFT JOIN ' . $k . '_edit_history_data ehd ON ehd.edit_id=eh.id' . $e_where;
         }
     }
     // **** create ****
     if (isset($filters['new'])) {
         foreach ($rb_tabs as $k => $t) {
             $tables[] = 'SELECT 0 AS id, created_on AS edited_on, created_by AS edited_by, id' . $postgre_cast_type . ' as r_id, ' . DB::qstr($k) . ' as tab, ' . DB::qstr('create') . ' as action FROM ' . $k . '_data_1' . $c_where;
         }
     }
     if (!empty($tables)) {
         $tables = implode(' UNION ', $tables);
         $limit = DB::GetOne('SELECT COUNT(*) FROM (' . $tables . ') AS tmp');
         $limit = $gb->get_limit($limit);
         $ret = DB::SelectLimit('SELECT * FROM (' . $tables . ') AS tmp ORDER BY edited_on DESC', $limit['numrows'], $limit['offset']);
         while ($row = $ret->FetchRow()) {
             $user = Base_UserCommon::get_user_label($row['edited_by']);
             $action = '';
             $link = '';
             switch ($row['action']) {
                 case 'edit':
                     $details = DB::GetAssoc('SELECT field, old_value FROM ' . $row['tab'] . '_edit_history_data WHERE edit_id=%d', array($row['id']));
                     if (isset($details['id'])) {
                         $action = $details['id'] == 'DELETED' ? __('Deleted') : __('Restored');
                     } else {
                         $action = __('Edited');
                         $action = '<a ' . Utils_TooltipCommon::tooltip_leightbox_mode() . ' ' . Utils_TooltipCommon::ajax_open_tag_attrs(array('Utils_RecordBrowserCommon', 'get_edit_details_label'), array($row['tab'], $row['r_id'], $row['id']), 500) . '>' . $action . '</a>';
                     }
                     $r_id = $row['r_id'];
                     break;
                 case 'create':
                     $action = __('Created');
                     $r_id = $row['r_id'];
                     break;
                 case 'file':
                     $action = __('Attachment') . ': ';
                     $action .= $row['id'] == 0 ? __('New') : __('Updated');
                     $id = explode('/', $row['r_id']);
                     $row['tab'] = $id[0];
                     $r_id = $id[1];
                     break;
                 case 'note':
                     $action = __('Note') . ': ';
                     $action .= $row['id'] == 0 ? __('New') : __('Updated');
                     $id = explode('/', $row['r_id']);
                     $row['tab'] = $id[0];
                     $r_id = $id[1];
                     break;
             }
             if (!Utils_RecordBrowserCommon::get_access($row['tab'], 'view', Utils_RecordBrowserCommon::get_record($row['tab'], $r_id))) {
                 $link = __('Access restricted');
                 $action = strip_tags($action);
             } else {
                 $link = Utils_TooltipCommon::create('<img src="' . Base_ThemeCommon::get_template_file('Utils_RecordBrowser', 'info.png') . '">', Utils_RecordBrowserCommon::get_html_record_info($row['tab'], $r_id), false);
                 $link .= '&nbsp;';
                 $link .= Utils_RecordBrowserCommon::create_default_linked_label($row['tab'], $r_id, false, false);
             }
             $gb->add_row(Base_RegionalSettingsCommon::time2reg($row['edited_on']), $user, $rb_tabs[$row['tab']], $link, $action);
         }
     }
     Base_ThemeCommon::load_css('Utils_RecordBrowser', 'changes_list');
     $this->display_module($gb);
 }