se/SE/procesy/search.php

<?php

//@2014-01-18 some tunning of searching by A.Binder

function task_CRM_SEARCH() {
	$q = V::get('q', '', $_REQUEST);
	$f_limit = V::get('f-_limit', 10, $_REQUEST, 'int');
	$f_offset = V::get('f-_offset', 0, $_REQUEST, 'int');
	$active_tab = V::get('_active_tab', 0, $_REQUEST, 'int');

	$db = DB::getDB();
	$qProces = new CrmSearchProces($db);
	$qProces->set_search($q);
	$qProces->set_param('limit', $f_limit);
	if ($active_tab == 0) {
		$qProces->set_param('offset', $f_offset);
	}




	echo '<h1>' . "Wyszukiwanie: " . htmlspecialchars($q) . '</h1>';

	if (!$qProces->check_search()) {
		echo App::ui_widget('alert', "Wyszukiwane słowo musi mieć conajmniej 2 znaki");
		return;
	}

	$qZasob = new CrmSearchZasob($db);
	$qZasob->set_search($q);
	$qZasob->set_param('limit', $f_limit);
	if ($active_tab == 1) {
		$qZasob->set_param('offset', $f_offset);
	}

	$qWsk = new CrmSearchWskaznik($db);
	$qWsk->set_search($q);
	$qWsk->set_param('limit', $f_limit);
	if ($active_tab == 2) {
		$qWsk->set_param('offset', $f_offset);
	}

	$qProcesInit = new CrmSearchProcesInit($db);
	$qProcesInit->set_search($q);
	$qProcesInit->set_param('limit', $f_limit);
	if ($active_tab == 3) {
		$qProcesInit->set_param('offset', $f_offset);
	}


	Lib::loadClass('PageNav');

?>
<div>
  <ul id="search-result-tabs" class="nav nav-tabs">
		<li class="active"><a href="#tabs-1" data-toggle="tabs-1">Procesy (<?php echo $qProces->get_total();?>)</a></li>
		<li><a href="#tabs-2" data-toggle="tabs-2">Zasoby (<?php echo $qZasob->get_total();?>)</a></li>
		<li><a href="#tabs-3" data-toggle="tabs-3">Wskaźniki (<?php echo $qWsk->get_total();?>)</a></li>
		<li><a href="#tabs-4" data-toggle="tabs-4">Procesy Start (<?php echo $qProcesInit->get_total();?>)</a></li>
<!--
		<li><a href="#tabs-1" style="outline:none">Procesy (<?php echo $qProces->get_total();?>)</a></li>

    <li><a href="#tabs-2" style="outline:none">Zasoby (<?php echo $qZasob->get_total();?>)</a></li>
    <li><a href="#tabs-3" style="outline:none">Wskaźniki (<?php echo $qWsk->get_total();?>)</a></li>
        <li><a href="#tabs-4" style="outline:none">Procesy Start (<?php echo $qProcesInit->get_total();?>)</a></li>
-->
  </ul>
	<div id="myTabContent" class="tab-content">
  <?php $tab_nr = 1; ?>
  <div id="tabs-<?php echo $tab_nr; ?>" class="tab-pane fade in active" style="padding:10px 0;">
<?php
	$total = $qProces->get_total();
	if ($total <= 0) {
		echo App::ui_widget('info', "Nie znaleziono żadnych rekordów pasujacych do kryteriów wyszukiwania");
	}
	else {
		$list = $qProces->get_list();

		$nav = new PageNav($total, $qProces->get_param('offset'), $qProces->get_param('limit'));
		$nav->set_request_args(array('task'=>'CRM_SEARCH', 'q'=>$q, '_active_tab'=>($tab_nr - 1)));
		$nav->set_request_method_post();
		$nav->set_request_arg_page_nr_name('f-_offset');

		echo $nav->render('links_only');
?>
		<table cellspacing="0" cellpadding="0" border="1" class="tbl-view">
		<thead>
			<tr>
				<th>ID</th>
				<th>DESC</th>
				<th>OPIS</th>
				<th>hit_counter</th>
			</tr>
		</thead>
		<tbody>
<?php
		$t = 0;
		foreach ($list as $r) {
			$desc = strip_tags($r->DESC);
			$desc = str_replace($q, '<b>'.$q.'</b>', $desc);
			$desc = str_replace(strtoupper($q), '<b>'.strtoupper($q).'</b>', $desc);

			$opis = strip_tags($r->OPIS);
			if (strlen($opis) > 255) {
				$opis = substr($opis, 0, 255) . ' ...';
			}
			$opis = str_replace($q, '<b>'.$q.'</b>', $opis);
			$opis = str_replace(strtoupper($q), '<b>'.strtoupper($q).'</b>', $opis);

			echo '<tr class="row-'.($t = 1 - $t).'">';
				echo '<td>' . App::link($r->ID, array('task'=>'CRM_PROCES', 'filtr_id'=>$r->ID), array('class'=>'external', 'target'=>'_blank')) . '</td>';
				echo '<td>' . $desc . '</td>';
				echo '<td>' . $opis . '</td>';
				echo '<td>' . $r->hit_counter . '</td>';
			echo '</tr>';
		}
?>
		</tbody>
		</table>
<?php
		echo $nav->render();
	}
?>
  </div>
  <?php $tab_nr++; ?>
  <div id="tabs-<?php echo $tab_nr; ?>" class="tab-pane fade" style="padding:10px 0;">
<?php
	$total = $qZasob->get_total();
	if ($total <= 0) {
		echo App::ui_widget('info', "Nie znaleziono żadnych rekordów pasujacych do kryteriów wyszukiwania");
	}
	else {
		$list = $qZasob->get_list();

		$nav = new PageNav($total, $qZasob->get_param('offset'), $qZasob->get_param('limit'));
		$nav->set_request_args(array('task'=>'CRM_SEARCH', 'q'=>$q, '_active_tab'=>($tab_nr - 1)));
		$nav->set_request_method_post();
		$nav->set_request_arg_page_nr_name('f-_offset');

		echo $nav->render('links_only');
?>
		<table cellspacing="0" cellpadding="0" border="1" class="tbl-view">
		<thead>
			<tr>
				<th>ID</th>
				<th>DESC</th>
				<th>OPIS</th>
				<th>hit_counter</th>
			</tr>
		</thead>
		<tbody>
<?php
		$t = 0;
		foreach ($list as $r) {
			$desc = strip_tags($r->DESC);
			$desc = str_replace($q, '<b>'.$q.'</b>', $desc);
			$desc = str_replace(strtoupper($q), '<b>'.strtoupper($q).'</b>', $desc);

			$opis = strip_tags($r->OPIS);
			if (strlen($opis) > 255) {
				$opis = substr($opis, 0, 255) . ' ...';
			}
			$opis = str_replace($q, '<b>'.$q.'</b>', $opis);
			$opis = str_replace(strtoupper($q), '<b>'.strtoupper($q).'</b>', $opis);

			echo '<tr class="row-'.($t = 1 - $t).'">';
				echo '<td>' . App::link($r->ID, array('task'=>'CRM_LISTA_ZASOBOW', 'filtr_id'=>$r->ID), array('class'=>'external', 'target'=>'_blank')) . '</td>';
				echo '<td>' . $desc . '</td>';
				echo '<td>' . $opis . '</td>';
				echo '<td>' . $r->hit_counter . '</td>';
			echo '</tr>';
		}
?>
		</tbody>
		</table>
<?php
		echo $nav->render();
	}
?>
  </div>
  <?php $tab_nr++; ?>
  <div id="tabs-<?php echo $tab_nr; ?>" class="tab-pane fade" style="padding:10px 0;">
<?php
	$total = $qWsk->get_total();
	if ($total <= 0) {
		echo App::ui_widget('info', "Nie znaleziono żadnych rekordów pasujacych do kryteriów wyszukiwania");
	}
	else {
		$list = $qWsk->get_list();

		$nav = new PageNav($total, $qWsk->get_param('offset'), $qWsk->get_param('limit'));
		$nav->set_request_args(array('task'=>'CRM_SEARCH', 'q'=>$q, '_active_tab'=>($tab_nr - 1)));
		$nav->set_request_method_post();
		$nav->set_request_arg_page_nr_name('f-_offset');

		echo $nav->render('links_only');
?>
		<table cellspacing="0" cellpadding="0" border="1" class="tbl-view">
		<thead>
			<tr>
				<th>ID</th>
				<th>ID_PROCES</th>
				<th>ID_ZASOB</th>
				<th>OPIS_ZASOB</th>
				<th>hit_counter</th>
			</tr>
		</thead>
		<tbody>
<?php
		$t = 0;
		foreach ($list as $r) {
			$desc = strip_tags($r->DESC);
			$desc = str_replace($q, '<b>'.$q.'</b>', $desc);
			$desc = str_replace(strtoupper($q), '<b>'.strtoupper($q).'</b>', $desc);

			$opis = strip_tags($r->OPIS_ZASOB);
			if (strlen($opis) > 255) {
				$opis = substr($opis, 0, 255) . ' ...';
			}
			$opis = str_replace($q, '<b>'.$q.'</b>', $opis);
			$opis = str_replace(strtoupper($q), '<b>'.strtoupper($q).'</b>', $opis);

			echo '<tr class="row-'.($t = 1 - $t).'">';
				echo '<td>' . $r->ID . '</td>';
				echo '<td>' . App::link($r->ID_PROCES, array('task'=>'CRM_PROCES', 'filtr_id'=>$r->ID_PROCES), array('class'=>'external', 'target'=>'_blank')) . '</td>';
				echo '<td>' . App::link($r->ID_ZASOB, array('task'=>'CRM_LISTA_ZASOBOW', 'filtr_id'=>$r->ID_ZASOB), array('class'=>'external', 'target'=>'_blank')) . '</td>';
				echo '<td>' . $opis . '</td>';
				echo '<td>' . $r->hit_counter . '</td>';
			echo '</tr>';
		}
?>
		</tbody>
		</table>
<?php
		echo $nav->render();
	}
?>
  </div>
  
  
  
    <?php $tab_nr = 4; ?>
  <div id="tabs-<?php echo $tab_nr; ?>" class="tab-pane fade" style="padding:10px 0;">
<?php
	$total = $qProcesInit->get_total();
	if ($total <= 0) {
		echo App::ui_widget('info', "Nie znaleziono żadnych rekordów pasujacych do kryteriów wyszukiwania");
	}
	else {
		$list = $qProcesInit->get_list();

		$nav = new PageNav($total, $qProcesInit->get_param('offset'), $qProcesInit->get_param('limit'));
		$nav->set_request_args(array('task'=>'CRM_SEARCH', 'q'=>$q, '_active_tab'=>($tab_nr - 1)));
		$nav->set_request_method_post();
		$nav->set_request_arg_page_nr_name('f-_offset');

		echo $nav->render('links_only');
?>
		<table cellspacing="0" cellpadding="0" border="1" class="tbl-view">
		<thead>
			<tr>
				<th>ID</th>
				<th>DESC</th>
				<th>OPIS</th>
				<th>hit_counter</th>
			</tr>
		</thead>
		<tbody>
<?php
		$t = 0;
		foreach ($list as $r) {
			$desc = strip_tags($r->DESC);
			$desc = str_replace($q, '<b>'.$q.'</b>', $desc);
			$desc = str_replace(strtoupper($q), '<b>'.strtoupper($q).'</b>', $desc);

			$opis = strip_tags($r->OPIS);
			if (strlen($opis) > 255) {
				$opis = substr($opis, 0, 255) . ' ...';
			}
			$opis = str_replace($q, '<b>'.$q.'</b>', $opis);
			$opis = str_replace(strtoupper($q), '<b>'.strtoupper($q).'</b>', $opis);

			echo '<tr class="row-'.($t = 1 - $t).'">';
				echo '<td>' . App::link($r->ID, array('task'=>'CRM_PROCES', 'filtr_id'=>$r->ID), array('class'=>'external', 'target'=>'_blank')) . '</td>';
				echo '<td>' . $desc . '</td>';
				echo '<td>' . $opis . '</td>';
				echo '<td>' . $r->hit_counter . '</td>';
			echo '</tr>';
		}
?>
		</tbody>
		</table>
<?php
		echo $nav->render();
	}
?>
  </div>
</div>

</div>
<script type="text/javascript">
jQuery(document).ready(function(){
	$('#search-result-tabs a').click(function (e) {
		e.preventDefault();
		$(this).tab('show');
	})
});
</script>
<?php
}


class CrmSearchBase {

	var $db = null;
	var $q = '';
	var $params = array();
	var $fields = array();
	var $_limit = 0;
	var $_total = null;
	var $_list = null;

	public function __construct($db) {
		$this->db = $db;
	}

	public function set_fields($fields) {
		$this->fields = $fields;
	}

	public function add_field($field, $label) {
		$this->fields[$field] = $label;
	}

	public function get_field_names() {
		return array_keys($this->fields);
	}

	public function get_field_labels() {
		return array_values($this->fields);
	}

	public function get_fields() {
		return $this->fields;
	}

	public function set_search($q) {
		$q = trim($q);
		$this->q = $this->db->_($q);
	}

	public function check_search() {
		return (!empty($this->q) && strlen($this->q) > 2);
	}

	public function set_param($key, $value) {
		$this->params[$key] = $value;
	}

	public function get_param($key) {
		return (array_key_exists($key, $this->params))? $this->params[$key] : null;
	}

	public function get_total() {
		if ($this->_total === null) {
			$this->_total = $this->_fetch_total();
		}
		return $this->_total;
	}

	public function get_list() {
		if ($this->_list === null) {
			$this->_list = $this->_fetch_list();
		}
		return $this->_list;
	}

	protected function _fetch_total() {
		return 0;
	}

	protected function _fetch_list() {
		$ret = array();
		return $ret;
	}

}

class CrmSearchProces extends CrmSearchBase {

	function _get_sql_where() {
		$sql_where = "
			p.`A_STATUS` in('NORMAL','WAITING')
			and (p.`DESC` like '%{$this->q}%' or p.`OPIS` like '%{$this->q}%' or p.`ID` like '{$this->q}')
		";
		return $sql_where;
	}

	function _fetch_total() {
		$total = 0;
		$sql_where = $this->_get_sql_where();
		$sql = "select count(1) as cnt
			from `CRM_PROCES` as p
			where {$sql_where}
		";
		$res = $this->db->query($sql);
		if ($r = $this->db->fetch($res)) {
			$total = $r->cnt;
		}
		return $total;
	}

	function _fetch_list() {
		$list = array();
		$sql_where = $this->_get_sql_where();
		$sql_limit = V::get('limit', 10, $this->params, 'int');
		$sql_offset = V::get('offset', 0, $this->params, 'int');
		$sql = "select p.`ID`, p.`DESC`, p.`OPIS`
				, if(p.`ID`='{$this->q}', 100
				, if(p.`DESC`='{$this->q}', 99
					, if(p.`DESC` like '{$this->q}%', 90
						, if(p.`DESC` like '%{$this->q}%', 80
							, if(p.`OPIS`='{$this->q}', 50
								, if(p.`OPIS` like '{$this->q}%', 40
									, if(p.`OPIS` like '%{$this->q}%', 30
									, 0
									)
								)
							)
						)
					)
				) 
				)
				as hit_counter
			from `CRM_PROCES` as p
			where {$sql_where}
			order by hit_counter DESC, p.`ID` DESC
			limit {$sql_limit} offset {$sql_offset}
		";
		$res = $this->db->query($sql);
		while ($r = $this->db->fetch($res)) {
			$list[$r->ID] = $r;
		}
		return $list;
	}

}


class CrmSearchProcesInit extends CrmSearchBase {

	function _get_sql_where() {
		$sql_where = "
			p.`A_STATUS` in('NORMAL','WAITING')
			and (p.`DESC` like '%{$this->q}%' or p.`OPIS` like '%{$this->q}%' or p.`ID` like '%{$this->q}%')
			and p.`TYPE`='PROCES_INIT'

		";
		return $sql_where;
	}

	function _fetch_total() {
		$total = 0;
		$sql_where = $this->_get_sql_where();
		$sql = "select count(1) as cnt
			from `CRM_PROCES` as p
			where {$sql_where}
		";
		$res = $this->db->query($sql);
		if ($r = $this->db->fetch($res)) {
			$total = $r->cnt;
		}
		return $total;
	}

	function _fetch_list() {
		$list = array();
		$sql_where = $this->_get_sql_where();
		$sql_limit = V::get('limit', 10, $this->params, 'int');
		$sql_offset = V::get('offset', 0, $this->params, 'int');
		$sql = "select p.`ID`, p.`DESC`, p.`OPIS`
				, if(p.`ID`='{$this->q}', 100
				, if(p.`DESC`='{$this->q}', 99
					, if(p.`DESC` like '{$this->q}%', 90
						, if(p.`DESC` like '%{$this->q}%', 80
							, if(p.`OPIS`='{$this->q}', 50
								, if(p.`OPIS` like '{$this->q}%', 40
									, if(p.`OPIS` like '%{$this->q}%', 30
									, 0
									)
								)
							)
						)
					)
				)
				) as hit_counter
			from `CRM_PROCES` as p
			where {$sql_where}
			order by hit_counter DESC, p.`ID` DESC
			limit {$sql_limit} offset {$sql_offset}
		";
		$res = $this->db->query($sql);
		while ($r = $this->db->fetch($res)) {
			$list[$r->ID] = $r;
		}
		return $list;
	}

}


class CrmSearchZasob extends CrmSearchBase {

	function _get_sql_where() {
		$sql_where = "
			z.`A_STATUS` in('NORMAL','WAITING')
			and (z.`DESC` like '%{$this->q}%' or z.`OPIS` like '%{$this->q}%' or z.`ID` like '{$this->q}')
		";
		return $sql_where;
	}

	function _fetch_total() {
		$total = 0;
		$sql_where = $this->_get_sql_where();
		$sql = "select count(1) as cnt
			from `CRM_LISTA_ZASOBOW` as z
			where {$sql_where}
		";
		$res = $this->db->query($sql);
		if ($r = $this->db->fetch($res)) {
			$total = $r->cnt;
		}
		return $total;
	}

	function _fetch_list() {
		$list = array();
		$sql_where = $this->_get_sql_where();
		$sql_limit = V::get('limit', 10, $this->params, 'int');
		$sql_offset = V::get('offset', 0, $this->params, 'int');
		$sql = "select z.`ID`, concat(z.`DESC`,' (',z.`TYPE`,')') as `DESC`, z.`OPIS`
				, if(z.`ID`='{$this->q}', 100
    				, if(z.`DESC`='{$this->q}', 99
    					, if(z.`DESC` like '{$this->q}%', 90
    						, if(z.`DESC` like '%{$this->q}%', 80
    							, if(z.`OPIS`='{$this->q}', 50
    								, if(z.`OPIS` like '{$this->q}%', 40
    									, if(z.`OPIS` like '%{$this->q}%', 30
    									, 0
    									)
    								)
    							)
    						)
    					)
    				) 
				) as hit_counter
			from `CRM_LISTA_ZASOBOW` as z
			where {$sql_where}
			order by hit_counter DESC, z.`ID` DESC
			limit {$sql_limit} offset {$sql_offset}
		";
		$res = $this->db->query($sql);
		while ($r = $this->db->fetch($res)) {
			$list[$r->ID] = $r;
		}
		return $list;
	}

}

class CrmSearchWskaznik extends CrmSearchBase {

	function _get_sql_where() {
		$sql_where = "
			w.`A_STATUS` in('NORMAL','WAITING')
			and p.`A_STATUS` in('NORMAL','WAITING')
			and z.`A_STATUS` in('NORMAL','WAITING')
			and (w.`OPIS_ZASOB` like '%{$this->q}%' or w.`ID_ZASOB` like '{$this->q}' or w.`ID_PROCES` like '{$this->q}' )
		";
		return $sql_where;
	}

	function _fetch_total() {
		$total = 0;
		$sql_where = $this->_get_sql_where();
		$sql = "select count(1) as cnt
			from `CRM_WSKAZNIK` as w
				join `CRM_PROCES` as p on(p.`ID`=w.`ID_PROCES`)
				join `CRM_LISTA_ZASOBOW` as z on(z.`ID`=w.`ID_ZASOB`)
			where {$sql_where}
		";
		$res = $this->db->query($sql);
		if ($r = $this->db->fetch($res)) {
			$total = $r->cnt;
		}
		return $total;
	}

	function _fetch_list() {
		$list = array();
		$sql_where = $this->_get_sql_where();
		$sql_limit = V::get('limit', 10, $this->params, 'int');
		$sql_offset = V::get('offset', 0, $this->params, 'int');
		$sql = "select w.`ID`, w.`ID_PROCES`, w.`ID_ZASOB`, w.`OPIS_ZASOB`
				, if(w.`ID_ZASOB`='{$this->q}', 100
					, if(w.`ID_PROCES`='{$this->q}', 100
						, if(w.`OPIS_ZASOB`='{$this->q}', 99
							, if(w.`OPIS_ZASOB` like '{$this->q}%', 90
								, if(w.`OPIS_ZASOB` like '%{$this->q}%', 80
									, 0
								)
							)
						)
					)
				) as hit_counter
			from `CRM_WSKAZNIK` as w
				join `CRM_PROCES` as p on(p.`ID`=w.`ID_PROCES`)
				join `CRM_LISTA_ZASOBOW` as z on(z.`ID`=w.`ID_ZASOB`)
			where {$sql_where}
			order by hit_counter DESC, w.`ID` DESC
			limit {$sql_limit} offset {$sql_offset}
		";
		$res = $this->db->query($sql);
		while ($r = $this->db->fetch($res)) {
			$list[$r->ID] = $r;
		}
		return $list;
	}

}