function render($control_name)
{
$genid = gen_id();
$object_types = ObjectTypes::getAvailableObjectTypes();
$value = $this->getValue();
$out = '';
foreach ($object_types as $ot) {
$checked = array_search($ot->getId(), $value) !== false;
$out .= '<div class="checkbox-config-option">';
$out .= label_tag(lang($ot->getName()), $genid . '_' . $control_name . '_' . $ot->getId(), false, array('style' => 'cursor:pointer;'), '');
$out .= checkbox_field($control_name . '[' . $ot->getId() . ']', $checked, array('id' => $genid . '_' . $control_name . '_' . $ot->getId()));
$out .= '</div >';
}
$out .= '<input type="hidden" name="' . $control_name . '[0]" value=" "><div class="clear"></div>';
return $out;
}
function dimension_tree_for_permissions()
{
$dimension_id = array_var($_REQUEST, 'dimension_id');
$checkedField = array_var($_REQUEST, 'checkboxes') ? "checked" : "_checked";
$objectTypeId = array_var($_REQUEST, 'object_type_id', null);
$allowedMemberTypes = json_decode(array_var($_REQUEST, 'allowedMemberTypes', null));
if (!is_array($allowedMemberTypes)) {
$allowedMemberTypes = null;
}
$only_names = array_var($_REQUEST, 'onlyname', false);
$name = trim(array_var($_REQUEST, 'query', ''));
$extra_cond = $name == "" ? "" : " AND name LIKE '%" . $name . "%'";
if (array_var($_REQUEST, 'new_user')) {
if (isset($_REQUEST['forced_members'])) {
$forced_members = json_decode(array_var($_REQUEST, 'forced_members', ''));
$fms = array(0);
if (is_array($forced_members) && count($forced_members) > 0) {
foreach ($forced_members as $fm) {
if (is_numeric($fm)) {
$fms[] = $fm;
}
}
}
if (count($fms) > 0) {
$extra_cond .= " AND id IN (" . implode(',', $fms) . ")";
}
}
if (isset($_REQUEST['excluded_members'])) {
$excluded_members = json_decode(array_var($_REQUEST, 'excluded_members', ''));
$ems = array(0);
if (is_array($excluded_members) && count($excluded_members) > 0) {
foreach ($excluded_members as $em) {
if (is_numeric($em)) {
$ems[] = $em;
}
}
}
if (count($ems) > 0) {
$extra_cond .= " AND id NOT IN (" . implode(',', $ems) . ")";
}
}
} else {
// only use available object types
$ots = ObjectTypes::getAvailableObjectTypes();
$available_ots_csv = "";
foreach ($ots as $ot) {
$available_ots_csv .= ($available_ots_csv == "" ? "" : ",") . $ot->getId();
}
if (trim($available_ots_csv) != "") {
$ot_cond = " AND cmp.object_type_id IN ({$available_ots_csv})";
} else {
$ot_cond = "";
}
if (array_var($_REQUEST, 'only_with_perm')) {
$extra_cond .= " AND EXISTS (SELECT cmp.member_id FROM " . TABLE_PREFIX . "contact_member_permissions cmp WHERE cmp.member_id=id AND cmp.permission_group_id=" . array_var($_REQUEST, 'pg', '-1') . " {$ot_cond})";
} else {
if (array_var($_REQUEST, 'only_without_perm')) {
$extra_cond .= " AND NOT EXISTS (SELECT cmp.member_id FROM " . TABLE_PREFIX . "contact_member_permissions cmp WHERE cmp.member_id=id AND cmp.permission_group_id=" . array_var($_REQUEST, 'pg', '-1') . " {$ot_cond})";
}
}
}
$return_all_members = false;
$selected_member_ids = json_decode(array_var($_REQUEST, 'selected_ids', "[0]"));
$selected_members = Members::findAll(array('conditions' => 'id IN (' . implode(',', $selected_member_ids) . ')'));
$memberList = $this->initial_list_dimension_members($dimension_id, $objectTypeId, $allowedMemberTypes, $return_all_members, $extra_cond, null, false, null, $only_names, $selected_members);
// add missing parents
$missing_parent_ids = array();
$all_members = array();
foreach ($memberList as $m) {
$all_members[$m['id']] = $m['id'];
}
foreach ($memberList as $m) {
if ($m['parent'] > 0 && !isset($all_members[$m['parent']])) {
$missing_parent_ids[$m['parent']] = $m['parent'];
}
}
while (count($missing_parent_ids) > 0) {
$missing_members = DB::executeAll("SELECT m.*, ot.icon FROM " . TABLE_PREFIX . "members m INNER JOIN " . TABLE_PREFIX . "object_types ot ON ot.id=m.object_type_id WHERE m.id IN (" . implode(',', $missing_parent_ids) . ")");
$missing_parent_ids = array();
$new_missing = array();
foreach ($missing_members as $mem) {
$m = array("id" => $mem['id'], "name" => clean($mem['name']), "parent" => $mem['parent_member_id'], "realParent" => $mem['parent_member_id'], "object_id" => $mem['object_id'], "depth" => $mem['depth'], "iconCls" => 'ico-' . $mem['icon'], "dimension_id" => $mem['dimension_id'], "object_type_id" => $mem['object_type_id'], "expandable" => true);
$memberList[str_pad(array_var($m, 'parent'), 20, "0", STR_PAD_LEFT) . strtolower(array_var($m, 'name')) . array_var($m, 'id')] = $m;
$new_missing[] = $m;
$all_members[$m['id']] = $m;
}
foreach ($new_missing as $m) {
if ($m['parent'] > 0 && !isset($all_members[$m['parent']])) {
$missing_parent_ids[$m['parent']] = $m['parent'];
}
}
}
// --
$tree = buildTree($memberList, "parent", "children", "id", "name", $checkedField);
ajx_current("empty");
ajx_extra_data(array('dimension_members' => $tree, 'dimension_id' => $dimension_id));
}
<?php
$genid = gen_id();
$selectedPage = user_config_option('custom_report_tab');
$customReports = Reports::getAllReportsByObjectType();
$report = new Report();
$can_add_reports = $report->canAdd(logged_user(), active_context());
$reportPages = array();
$object_types = ObjectTypes::getAvailableObjectTypes();
foreach ($object_types as $ot) {
$reportPages[$ot->getId()] = array("name" => $ot->getName(), "display_name" => lang($ot->getName()));
}
$ignored = null;
Hook::fire('modify_report_pages', $ignored, $reportPages);
// To add, edit or remove report pages
$default_reports = array('task' => array('task time report' => array('url' => get_url('reporting', 'total_task_times_p'), 'name' => lang('task time report'), 'description' => lang('task time report description'))));
Hook::fire('modify_default_reports', $ignored, $default_reports);
// To add, edit or remove default reports
require_javascript("og/ReportingFunctions.js");
?>
<div style="padding:7px">
<table width=100% id="reportingMenu">
<tr>
<td style="height:2px;width:140px"></td><td width=12></td><td style="line-height:2px;"> </td><td width=12></td>
</tr>
<tr>
<td height=12></td>
<td rowspan=<?php
echo count($reportPages) + 2;
?>
function edit_custom_report()
{
if (logged_user()->isGuest()) {
flash_error(lang('no access permissions'));
ajx_current("empty");
return;
}
$report_id = array_var($_GET, 'id');
$report = Reports::getReport($report_id);
if (!$report->canEdit(logged_user())) {
flash_error(lang('no access permissions'));
ajx_current("empty");
return;
}
// if
if (is_array(array_var($_POST, 'report'))) {
try {
ajx_current("empty");
$report_data = array_var($_POST, 'report');
$member_ids = json_decode(array_var($_POST, 'members'));
if (!is_array($member_ids) || count($member_ids) == 0) {
flash_error(lang('must choose at least one member'));
ajx_current("empty");
return;
}
$members = Members::findAll(array("conditions" => array("`id` IN(?)", $member_ids)));
DB::beginWork();
$report->setObjectName($report_data['name']);
$report->setDescription($report_data['description']);
$report->setReportObjectTypeId($report_data['report_object_type_id']);
$report->setOrderBy($report_data['order_by']);
$report->setIsOrderByAsc($report_data['order_by_asc'] == 'asc');
$report->save();
$conditions = array_var($_POST, 'conditions');
if (!is_array($conditions)) {
$conditions = array();
}
foreach ($conditions as $condition) {
$newCondition = new ReportCondition();
if ($condition['id'] > 0) {
$newCondition = ReportConditions::getCondition($condition['id']);
}
if ($condition['deleted'] == "1") {
$newCondition->delete();
continue;
}
$newCondition->setReportId($report_id);
$custom_prop_id = isset($condition['custom_property_id']) ? $condition['custom_property_id'] : 0;
$newCondition->setCustomPropertyId($custom_prop_id);
$newCondition->setFieldName($condition['field_name']);
$newCondition->setCondition($condition['condition']);
if ($condition['field_type'] == 'boolean') {
$newCondition->setValue(isset($condition['value']) && $condition['value']);
} else {
if ($condition['field_type'] == 'date') {
if ($condition['value'] == '') {
$newCondition->setValue('');
} else {
$dtFromWidget = DateTimeValueLib::dateFromFormatAndString(user_config_option('date_format'), $condition['value']);
$newCondition->setValue(date("m/d/Y", $dtFromWidget->getTimestamp()));
}
} else {
$newCondition->setValue(isset($condition['value']) ? $condition['value'] : '');
}
}
$newCondition->setIsParametrizable(isset($condition['is_parametrizable']));
$newCondition->save();
}
ReportColumns::delete('report_id = ' . $report_id);
$columns = array_var($_POST, 'columns');
asort($columns);
//sort the array by column order
foreach ($columns as $column => $order) {
if ($order > 0) {
$newColumn = new ReportColumn();
$newColumn->setReportId($report_id);
if (is_numeric($column)) {
$newColumn->setCustomPropertyId($column);
} else {
$newColumn->setFieldName($column);
}
$newColumn->save();
}
}
$object_controller = new ObjectController();
$object_controller->add_to_members($report, $member_ids);
DB::commit();
flash_success(lang('custom report updated'));
ajx_current('back');
} catch (Exception $e) {
DB::rollback();
flash_error($e->getMessage());
ajx_current("empty");
}
// try
} else {
$this->setTemplate('add_custom_report');
tpl_assign('url', get_url('reporting', 'edit_custom_report', array('id' => $report_id)));
if ($report instanceof Report) {
tpl_assign('id', $report_id);
$report_data = array('name' => $report->getObjectName(), 'description' => $report->getDescription(), 'report_object_type_id' => $report->getReportObjectTypeId(), 'order_by' => $report->getOrderBy(), 'order_by_asc' => $report->getIsOrderByAsc());
tpl_assign('report_data', $report_data);
$conditions = ReportConditions::getAllReportConditions($report_id);
tpl_assign('conditions', $conditions);
$columns = ReportColumns::getAllReportColumns($report_id);
$colIds = array();
foreach ($columns as $col) {
if ($col->getCustomPropertyId() > 0) {
$colIds[] = $col->getCustomPropertyId();
} else {
$colIds[] = $col->getFieldName();
}
}
tpl_assign('columns', $colIds);
}
$selected_type = $report->getReportObjectTypeId();
$types = array(array("", lang("select one")));
$object_types = ObjectTypes::getAvailableObjectTypes();
foreach ($object_types as $ot) {
$types[] = array($ot->getId(), lang($ot->getName()));
}
tpl_assign('object_types', $types);
tpl_assign('selected_type', $selected_type);
tpl_assign('object', $report);
tpl_assign('allowed_columns', $this->get_allowed_columns($selected_type), true);
}
}
/**
* Execute search
* TODO: Performance gus:
* Fetch only ids and execute a select statement by pk (fer each result)
* @param void
* @return null
*/
function search() {
// Init vars
$search_for = array_var($_GET, 'search_for');
$search_dimension = array_var($_GET, 'search_dimension');
$advanced = array_var($_GET, 'advanced');
$minWordLength = $this->minWordLength($search_for);
$useLike = ( $minWordLength && ($this->ignoreMinWordLength) && ($minWordLength < self::$MYSQL_MIN_WORD_LENGHT) );
$search_pieces= explode(" ", $search_for);
$search_string = "";
if (!$useLike){
// Prepare MATCH AGAINST string
foreach ($search_pieces as $word ) {
if (( strpos($word, "@") || strpos($word, ".") || strpos($word, ",")) === false ) {
// STRING Dont containt special characheters that mysql use as separator. Noramal flow
if ($this->wildCardSearch) {
$word.="*";
}
}else{
$word = str_replace($this->mysqlWordSeparator, " +", $word) ;
}
if ( !str_starts_with($word, " ") ) {
$word = " +".$word;
}
$search_string .= mysql_real_escape_string( $word ). " ";
}
$search_string = substr($search_string, 0 , -1);
}else{
// USE Like Query
$search_string = mysql_real_escape_string($search_for, DB::connection()->getLink());
}
$this->search_for = $search_for;
$limit = $this->limit;
$start = array_var($_REQUEST, 'start' , $this->start);
$this->start = $start;
$limitTest = max( $this->limitTest , $this->limit);
$filteredResults = 0;
$uid = logged_user()->getId();
if(!isset($search_dimension)){
$members = active_context_members(false);
}else{
if($search_dimension == 0){
$members = array();
}else{
$members = array($search_dimension);
}
}
// click on search everywhere
if (array_var($_REQUEST, 'search_all_projects')) {
$members = array();
}
$revisionObjectTypeId = ObjectTypes::findByName("file revision")->getId();
$members_sql = "";
if(count($members) > 0){
$members_sql = "AND (rel_object_id IN (SELECT object_id FROM " . TABLE_PREFIX . "object_members om
WHERE member_id IN (" . implode ( ',', $members ) . ") GROUP BY object_id HAVING count(member_id) = ".count($members).")
OR o.object_type_id = $revisionObjectTypeId AND rel_object_id IN (SELECT fr.object_id FROM " . TABLE_PREFIX . "object_members om
INNER JOIN ".TABLE_PREFIX."project_file_revisions fr ON om.object_id=fr.file_id
WHERE member_id IN (" . implode ( ',', $members ) . ") GROUP BY object_id HAVING count(member_id) = ".count($members)."))";
$this->search_dimension = implode ( ',', $members );
}else{
$this->search_dimension = 0;
}
$listableObjectTypeIds = implode(",",ObjectTypes::getListableObjectTypeIds());
if($_POST) {
$conditions = array_var($_POST, 'conditions');
$search = array_var($_POST, 'search');
$type_object = array_var($search, 'search_object_type_id');
if(!is_array($conditions)) $conditions = array();
$where_condiition = '';
$conditions_view = array();
$cont = 0;
$joincp ="";
foreach($conditions as $condition){
$condValue = array_key_exists('value', $condition) ? $condition['value'] : '';
if($condition['field_type'] == 'boolean'){
$value = array_key_exists('value', $condition);
}else if($condition['field_type'] == 'date'){
if ($condValue != '') {
$dtFromWidget = DateTimeValueLib::dateFromFormatAndString(user_config_option('date_format'), $condValue);
$value = date("m/d/Y", $dtFromWidget->getTimestamp());
}
}else{
$value = mysql_real_escape_string($condValue, DB::connection()->getLink());
}
$condition_condition = mysql_real_escape_string(array_var($condition, 'condition'), DB::connection()->getLink());
$condition_field_name = mysql_real_escape_string(array_var($condition, 'field_name'), DB::connection()->getLink());
$conditionLocal = "like";
tpl_assign('type_object', $type_object);
if (isset($condition['custom_property_id']) and is_numeric($condition['custom_property_id'])){
$condition_field_name = 'value';
$joincp = 'JOIN fo_custom_property_values cp ON cp.object_id = so.rel_object_id';
};
if ($condition_condition == "=" or $condition_condition == ">" or $condition_condition == "<" or $condition_condition == "<>" or $condition_condition == ">=" or $condition_condition == "<="){
$conditionLocal = $condition_condition;
};
if($condition_field_name == "id"){
$condition_field_name = "o`.`id" ;
};
if($condition_condition == "like"){
$where_condiition .= " AND `" . $condition_field_name . "` " . "like" . " '%" . $value . "%' ";
}else if($condition_condition == "ends with"){
$where_condiition .= " AND `" . $condition_field_name . "` " . "like" . " '%" . $value . "' ";
}else if($condition_condition == "start with"){
$where_condiition .= " AND `" . $condition_field_name . "` " . "like" . " '" . $value . "%' ";
}else if($condition_condition == "not like"){
$where_condiition .= " AND `" . $condition_field_name . "` " . "not like" . " '%" . $value . "%' ";
}else{
$where_condiition .= " AND `" . $condition_field_name . "` " . $conditionLocal . " '" . $value . "' ";
}
$conditions_view[$cont]['id'] = $condition['id'];
$conditions_view[$cont]['custom_property_id'] = $custom_prop_id;
$conditions_view[$cont]['field_name'] = $condition['field_name'];
$conditions_view[$cont]['condition'] = $condition['condition'];
$conditions_view[$cont]['value'] = $value;
$cont++;
}
tpl_assign('conditions', $conditions_view);
if($type_object){
$object_table = ObjectTypes::findById($type_object);
$table = $object_table->getTableName();
}
$sql = "
SELECT distinct(so.rel_object_id) AS id
FROM ".TABLE_PREFIX."searchable_objects so
".$joincp."
INNER JOIN ".TABLE_PREFIX.$table." nto ON nto.object_id = so.rel_object_id
INNER JOIN ".TABLE_PREFIX."objects o ON o.id = so.rel_object_id
WHERE (
(
so.rel_object_id IN (
SELECT object_id FROM ".TABLE_PREFIX."sharing_table WHERE group_id IN (
SELECT permission_group_id FROM ".TABLE_PREFIX."contact_permission_groups WHERE contact_id = $uid
)
)
)
) " . $where_condiition . $members_sql . " ORDER by o.updated_on DESC
LIMIT $start, $limitTest ";
} else {
$type_object = '';
$sql = "
SELECT distinct(so.rel_object_id) AS id
FROM ".TABLE_PREFIX."searchable_objects so
INNER JOIN ".TABLE_PREFIX."objects o ON o.id = so.rel_object_id
WHERE (
(
o.object_type_id = $revisionObjectTypeId AND
EXISTS (
SELECT id FROM ".TABLE_PREFIX."sharing_table WHERE object_id = ( SELECT file_id FROM ".TABLE_PREFIX."project_file_revisions WHERE object_id = o.id )
AND group_id IN (SELECT permission_group_id FROM ".TABLE_PREFIX."contact_permission_groups WHERE contact_id = $uid )
)
)
OR (
so.rel_object_id IN (
SELECT object_id FROM ".TABLE_PREFIX."sharing_table WHERE group_id IN (
SELECT permission_group_id FROM ".TABLE_PREFIX."contact_permission_groups WHERE contact_id = $uid
)
)
)
)" . (($useLike) ? "AND so.content LIKE '%$search_string%' " : "AND MATCH (so.content) AGAINST ('$search_string' IN BOOLEAN MODE) ") . "
AND o.object_type_id IN ($listableObjectTypeIds) " . $members_sql . "
ORDER by o.updated_on DESC
LIMIT $start, $limitTest ";
}
tpl_assign('type_object', $type_object);
$db_search_results = array();
$search_results_ids = array();
$timeBegin = time();
$res = DB::execute($sql);
$timeEnd = time();
while ($row = $res->fetchRow() ) {
$search_results_ids[] = $row['id'] ;
}
// Prepare results for view to avoid processing at presentation layer
$search_results = $this->prepareResults($search_results_ids, $null, $limit);
// Calculate or approximate total for pagination
$total = count($search_results_ids) + $start ;
if ( count ( $search_results_ids ) < $limitTest ) {
$total = count($search_results_ids) + $start ;
}else{
$total = "Many" ;
}
//$total -= $filteredResults ;
$this->total = $total ;
// Pagination
$this->buildPagination($search_results, $search_for);
// Extra data
$extra = new stdClass() ;
if ($this->showQueryTime) {
$extra->time = $timeEnd-$timeBegin ;
}
//$extra->filteredResults = $filteredResults ;
// Template asigns
tpl_assign('pagination', $this->pagination);
tpl_assign('search_string', $search_for);
tpl_assign('search_dimension', $this->search_dimension);
tpl_assign('search_results', $search_results);
tpl_assign('advanced', $advanced);
tpl_assign('extra', $extra );
$types = array(array("", lang("select one")));
$object_types = ObjectTypes::getAvailableObjectTypes();
foreach ($object_types as $ot) {
$types[] = array($ot->getId(), lang($ot->getName()));
}
// if ($selected_type != '')
// tpl_assign('allowed_columns', $this->get_allowed_columns($selected_type));
tpl_assign('object_types', $types);
//Ajax
if (!$total && !$advanced){
if($_POST && count($search_results < 0)){
tpl_assign('msg_advanced', true);
}else{
$this->setTemplate('no_results');
}
}
ajx_set_no_toolbar(true);
}
/**
* Execute search
* TODO: Performance gus:
* Fetch only ids and execute a select statement by pk (fer each result)
* @param void
* @return null
*/
function search()
{
// Init vars
$search_for = array_var($_GET, 'search_for');
$search_dimension = array_var($_GET, 'search_dimension');
$advanced = array_var($_GET, 'advanced');
//$minWordLength = $this->minWordLength($search_for);
//$useLike = ( $minWordLength && ($this->ignoreMinWordLength) && ($minWordLength < self::$MYSQL_MIN_WORD_LENGHT) );
$useLike = false;
if (strlen($search_for) < 4) {
$useLike = true;
}
$search_pieces = explode(" ", $search_for);
$search_string = "";
$search_string = mysql_real_escape_string($search_for, DB::connection()->getLink());
$this->search_for = $search_for;
$limit = $this->limit;
$start = array_var($_REQUEST, 'start', $this->start);
$this->start = $start;
$limitTest = max($this->limitTest, $this->limit);
$filteredResults = 0;
$uid = logged_user()->getId();
if (!isset($search_dimension)) {
$members = active_context_members(false);
} else {
if ($search_dimension == 0) {
$members = array();
} else {
$members = array($search_dimension);
}
}
// click on search everywhere
if (array_var($_REQUEST, 'search_all_projects')) {
$members = array();
}
$revisionObjectTypeId = ObjectTypes::findByName("file revision")->getId();
$members_sql = "";
if (count($members) > 0) {
$context_condition = "(EXISTS\r\n\t\t\t\t\t\t\t\t\t\t(SELECT om.object_id\r\n\t\t\t\t\t\t\t\t\t\t\tFROM " . TABLE_PREFIX . "object_members om\r\n\t\t\t\t\t\t\t\t\t\t\tWHERE\tom.member_id IN (" . implode(',', $members) . ") AND so.rel_object_id = om.object_id\r\n\t\t\t\t\t\t\t\t\t\t\tGROUP BY object_id\r\n\t\t\t\t\t\t\t\t\t\t\tHAVING count(member_id) = " . count($members) . "\r\n\t\t\t\t\t\t\t\t\t\t)\r\n\t\t\t\t\t\t\t\t\t)";
$context_condition_rev = "(EXISTS\r\n\t\t\t\t\t\t\t\t\t\t(SELECT fr.object_id FROM " . TABLE_PREFIX . "object_members om\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN " . TABLE_PREFIX . "project_file_revisions fr ON om.object_id=fr.file_id\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tINNER JOIN " . TABLE_PREFIX . "objects ob ON fr.object_id=ob.id\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE fr.file_id = so.rel_object_id AND ob.object_type_id = {$revisionObjectTypeId} AND member_id IN (" . implode(',', $members) . ") \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tGROUP BY object_id \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tHAVING count(member_id) = " . count($members) . "\r\n\t\t\t\t\t\t\t\t\t\t)\r\n\t\t\t\t\t\t\t\t\t)";
$members_sql = "AND ( " . $context_condition . " OR " . $context_condition_rev . ")";
$this->search_dimension = implode(',', $members);
} else {
$this->search_dimension = 0;
}
$listableObjectTypeIds = implode(",", ObjectTypes::getListableObjectTypeIds());
$can_see_all_tasks_cond = "";
if (!SystemPermissions::userHasSystemPermission(logged_user(), 'can_see_assigned_to_other_tasks')) {
$can_see_all_tasks_cond = " AND IF((SELECT ot.name FROM " . TABLE_PREFIX . "object_types ot WHERE ot.id=o.object_type_id)='task',\r\n\t\t\t (SELECT t.assigned_to_contact_id FROM " . TABLE_PREFIX . "project_tasks t WHERE t.object_id=o.id) = " . logged_user()->getId() . ",\r\n\t\t\t true)";
}
if ($_POST) {
$conditions = array_var($_POST, 'conditions');
$search = array_var($_POST, 'search');
$type_object = array_var($search, 'search_object_type_id');
if (!is_array($conditions)) {
$conditions = array();
}
$where_condiition = '';
$conditions_view = array();
$cont = 0;
$joincp = "";
$value = "";
$custom_prop_id = "";
foreach ($conditions as $condition) {
$condValue = array_key_exists('value', $condition) ? $condition['value'] : '';
if ($condition['field_type'] == 'boolean') {
$value = array_key_exists('value', $condition);
} else {
if ($condition['field_type'] == 'date') {
if ($condValue != '') {
$dtFromWidget = DateTimeValueLib::dateFromFormatAndString(user_config_option('date_format'), $condValue);
$value = date("m/d/Y", $dtFromWidget->getTimestamp());
}
} else {
$value = mysql_real_escape_string($condValue, DB::connection()->getLink());
}
}
$condition_condition = mysql_real_escape_string(array_var($condition, 'condition'), DB::connection()->getLink());
$condition_field_name = mysql_real_escape_string(array_var($condition, 'field_name'), DB::connection()->getLink());
$conditionLocal = "like";
tpl_assign('type_object', $type_object);
//CREO QUE ESTO ESTA MAL
if (isset($condition['custom_property_id']) and is_numeric($condition['custom_property_id'])) {
$condition_field_name = 'value';
$joincp = 'JOIN ' . TABLE_PREFIX . 'custom_property_values cp ON cp.object_id = so.rel_object_id';
}
if ($condition['custom_property_id'] == 'phone_number') {
$condition_field_name = 'number';
$joincp = 'JOIN ' . TABLE_PREFIX . 'contact_telephones ct ON ct.contact_id = so.rel_object_id';
}
if ($condition['custom_property_id'] == 'email_address') {
$condition_field_name = 'email_address';
$joincp = 'JOIN ' . TABLE_PREFIX . 'contact_emails ce ON ce.contact_id = so.rel_object_id';
}
if ($condition['custom_property_id'] == 'web_url') {
$condition_field_name = 'url';
$joincp = 'JOIN ' . TABLE_PREFIX . 'contact_web_pages cw ON cw.contact_id = so.rel_object_id';
}
if ($condition['custom_property_id'] == 'im_value') {
$condition_field_name = 'value';
$joincp = 'JOIN ' . TABLE_PREFIX . 'contact_im_values cim ON cim.contact_id = so.rel_object_id';
}
if ($condition_condition == "=" or $condition_condition == ">" or $condition_condition == "<" or $condition_condition == "<>" or $condition_condition == ">=" or $condition_condition == "<=") {
$conditionLocal = $condition_condition;
}
if ($condition_field_name == "id") {
$condition_field_name = "o`.`id";
}
if ($condition_condition == "like") {
$where_condiition .= " AND `" . $condition_field_name . "` " . "like" . " '%" . $value . "%' ";
$con = "like '%" . $value . "%' ";
} else {
if ($condition_condition == "ends with") {
$where_condiition .= " AND `" . $condition_field_name . "` " . "like" . " '%" . $value . "' ";
$con = "like '%" . $value . "' ";
} else {
if ($condition_condition == "start with") {
$where_condiition .= " AND `" . $condition_field_name . "` " . "like" . " '" . $value . "%' ";
$con = "like '" . $value . "%' ";
} else {
if ($condition_condition == "not like") {
$where_condiition .= " AND `" . $condition_field_name . "` " . "not like" . " '%" . $value . "%' ";
$con = "not like '%" . $value . "%' ";
} else {
$where_condiition .= " AND `" . $condition_field_name . "` " . $conditionLocal . " '" . $value . "' ";
$con = $conditionLocal . " '" . $value . "' ";
}
}
}
}
if ($condition['custom_property_id'] == 'address') {
$addressCondiition .= " AND (street " . $con;
$addressCondiition .= " OR city " . $con;
$addressCondiition .= " OR state " . $con;
$addressCondiition .= " OR country " . $con;
$addressCondiition .= " OR zip_code " . $con . ")";
$where_condiition = $addressCondiition;
$joincp = 'JOIN ' . TABLE_PREFIX . 'contact_addresses ca ON ca.contact_id = so.rel_object_id';
}
$conditions_view[$cont]['id'] = $condition['id'];
$conditions_view[$cont]['custom_property_id'] = $custom_prop_id;
$conditions_view[$cont]['field_name'] = $condition['field_name'];
$conditions_view[$cont]['condition'] = $condition['condition'];
$conditions_view[$cont]['value'] = $value;
$cont++;
}
tpl_assign('conditions', $conditions_view);
if (empty($conditions)) {
$search_string = array_var($search, 'text');
$where_condiition .= " AND so.content LIKE '%{$search_string}%'";
}
if ($type_object) {
$object_table = ObjectTypes::findById($type_object);
$table = $object_table->getTableName();
}
$sql = "\r\n\t\t\tSELECT DISTINCT so.rel_object_id AS id\r\n\t\t\tFROM " . TABLE_PREFIX . "searchable_objects so\r\n\t\t\t" . $joincp . "\r\n\t\t\tINNER JOIN " . TABLE_PREFIX . $table . " nto ON nto.object_id = so.rel_object_id \r\n\t\t\tINNER JOIN " . TABLE_PREFIX . "objects o ON o.id = so.rel_object_id \r\n\t\t\tWHERE (\r\n\t\t\t\t(\r\n\t\t\t\t\tEXISTS ( \r\n\t\t\t\t\t\tSELECT object_id FROM " . TABLE_PREFIX . "sharing_table sh\r\n\t\t\t\t\t\tWHERE o.id = sh.object_id \r\n\t\t\t\t\t\tAND sh.group_id IN (\r\n\t\t \t\t\t\t\t\t\t\t\tSELECT permission_group_id FROM " . TABLE_PREFIX . "contact_permission_groups WHERE contact_id = {$uid}\r\n\t\t\t\t\t\t\t\t\t\t\t)\r\n\t\t\t\t\t\t\t)\t\t\t\t\r\n\t\t\t \t)\r\n\t\t\t) " . $where_condiition . $members_sql . $can_see_all_tasks_cond . " ORDER by o.updated_on DESC\r\n\t\t\tLIMIT {$start}, {$limitTest}";
} else {
$type_object = '';
$sql = "\t\r\n\t\t\tSELECT DISTINCT so.rel_object_id AS id \r\n\t\t\tFROM " . TABLE_PREFIX . "searchable_objects so\r\n\t\t\tWHERE " . ($useLike ? " so.content LIKE '%{$search_string}%' " : " MATCH (so.content) AGAINST ('\"{$search_string}\"' IN BOOLEAN MODE) ") . " \r\n\t\t\tAND (EXISTS\r\n\t\t\t\t(SELECT o.id\r\n\t\t\t\t FROM " . TABLE_PREFIX . "objects o\r\n\t\t\t\t WHERE\to.id = so.rel_object_id AND (\t\r\n\t\t\t\t\t\t\t(o.object_type_id = {$revisionObjectTypeId} AND \r\n\t\t\t\t\t\t\t\tEXISTS ( \r\n\t\t\t\t\t\t\t\t\tSELECT group_id FROM " . TABLE_PREFIX . "sharing_table WHERE object_id = ( SELECT file_id FROM " . TABLE_PREFIX . "project_file_revisions WHERE object_id = o.id ) \r\n\t\t\t\t\t\t\t\t\tAND group_id IN (SELECT permission_group_id FROM " . TABLE_PREFIX . "contact_permission_groups WHERE contact_id = {$uid} )\r\n\t\t\t\t\t\t\t\t)\r\n\t\t\t\t\t\t\t) \r\n\t\t\t\t\t\t\tOR (\r\n\t\t\t\t\t\t\t\t(EXISTS\r\n\t\t\t\t\t\t\t\t\t(SELECT object_id\r\n\t\t\t\t\t\t\t\t\t\tFROM " . TABLE_PREFIX . "sharing_table sh\r\n\t\t\t\t\t\t\t\t\t\tWHERE o.id = sh.object_id \r\n\t\t\t\t\t\t\t\t\t\tAND sh.group_id IN (\r\n\t\t\t\t\t\t\t\t\t\t\tSELECT permission_group_id FROM " . TABLE_PREFIX . "contact_permission_groups WHERE contact_id = {$uid}\r\n\t\t\t\t\t\t\t\t\t\t)\r\n\t\t\t\t\t\t\t\t\t)\r\n\t\t\t\t\t\t\t\t)\r\n\t\t\t \t\t\t\t)\r\n\t\t\t \t\t) AND o.object_type_id IN ({$listableObjectTypeIds}) " . $members_sql . $can_see_all_tasks_cond . "\r\n\t\t\t\t)\r\n\t\t\t)\t\t\t\r\n\t\t\t\r\n\t\t\tORDER BY id DESC \r\n\t\t\tLIMIT {$start}, {$limitTest}";
}
tpl_assign('type_object', $type_object);
$db_search_results = array();
$search_results_ids = array();
if (!$advanced) {
$timeBegin = time();
$res = DB::execute($sql);
$timeEnd = time();
while ($row = $res->fetchRow()) {
$search_results_ids[$row['id']] = $row['id'];
}
}
// Prepare results for view to avoid processing at presentation layer
$search_results = $this->prepareResults($search_results_ids, $null, $limit);
// Calculate or approximate total for pagination
$total = count($search_results_ids) + $start;
if (count($search_results_ids) < $limitTest) {
$total = count($search_results_ids) + $start;
} else {
$total = lang("many");
}
//$total -= $filteredResults ;
$this->total = $total;
// Pagination
$this->buildPagination($search_results, $search_for);
// Extra data
$extra = new stdClass();
if ($this->showQueryTime) {
$extra->time = $timeEnd - $timeBegin;
}
//$extra->filteredResults = $filteredResults ;
// Template asigns
tpl_assign('pagination', $this->pagination);
tpl_assign('search_string', $search_for);
tpl_assign('search_dimension', $this->search_dimension);
tpl_assign('search_results', $search_results);
tpl_assign('advanced', $advanced);
tpl_assign('extra', $extra);
$types = array(array("", lang("select one")));
$object_types = ObjectTypes::getAvailableObjectTypes();
foreach ($object_types as $ot) {
$types[] = array($ot->getId(), lang($ot->getName()));
}
// if ($selected_type != '')
// tpl_assign('allowed_columns', $this->get_allowed_columns($selected_type));
tpl_assign('object_types', $types);
//Ajax
if (!$total && !$advanced) {
if ($_POST && count($search_results < 0)) {
tpl_assign('msg_advanced', true);
} else {
$this->setTemplate('no_results');
}
}
ajx_set_no_toolbar(true);
}
/**
* Execute search
* TODO: Performance gus:
* Fetch only ids and execute a select statement by pk (fer each result)
* @param void
* @return null
*/
function search()
{
// Init vars
$search_for = array_var($_GET, 'search_for');
$search_dimension = array_var($_GET, 'search_dimension');
$advanced = array_var($_GET, 'advanced');
$minWordLength = $this->minWordLength($search_for);
$useLike = $minWordLength && $this->ignoreMinWordLength && $minWordLength < self::$MYSQL_MIN_WORD_LENGHT;
$search_pieces = explode(" ", $search_for);
$search_string = "";
if (!$useLike) {
// Prepare MATCH AGAINST string
foreach ($search_pieces as $word) {
if ((strpos($word, "@") || strpos($word, ".") || strpos($word, ",")) === false) {
// STRING Dont containt special characheters that mysql use as separator. Noramal flow
if ($this->wildCardSearch) {
$word .= "*";
}
} else {
$word = str_replace($this->mysqlWordSeparator, " +", $word);
}
if (!str_starts_with($word, " ")) {
$word = " +" . $word;
}
$search_string .= mysql_escape_string($word) . " ";
}
$search_string = substr($search_string, 0, -1);
} else {
// USE Like Query
$search_string = mysql_escape_string($search_for);
}
$this->search_for = $search_for;
$limit = $this->limit;
$start = array_var($_REQUEST, 'start', $this->start);
$this->start = $start;
$limitTest = max($this->limitTest, $this->limit);
$filteredResults = 0;
$uid = logged_user()->getId();
if (!isset($search_dimension)) {
$members = active_context_members(false);
} else {
if ($search_dimension == 0) {
$members = array();
} else {
$members = array($search_dimension);
}
}
$members_sql = "";
if (count($members) > 0) {
$members_sql = "AND rel_object_id IN (SELECT object_id FROM " . TABLE_PREFIX . "object_members om WHERE member_id IN (" . implode(',', $members) . ") \n GROUP BY object_id\n HAVING count(member_id) = " . count($members) . ")";
$this->search_dimension = implode(',', $members);
} else {
$this->search_dimension = 0;
}
$revisionObjectTypeId = ObjectTypes::findByName("file revision")->getId();
$listableObjectTypeIds = implode(",", ObjectTypes::getListableObjectTypeIds());
if ($_POST) {
$conditions = array_var($_POST, 'conditions');
$search = array_var($_POST, 'search');
$type_object = array_var($search, 'search_object_type_id');
if (!is_array($conditions)) {
$conditions = array();
}
$where_condiition = '';
$conditions_view = array();
$cont = 0;
foreach ($conditions as $condition) {
$condValue = array_key_exists('value', $condition) ? $condition['value'] : '';
if ($condition['field_type'] == 'boolean') {
$value = array_key_exists('value', $condition);
} else {
if ($condition['field_type'] == 'date') {
if ($condValue != '') {
$dtFromWidget = DateTimeValueLib::dateFromFormatAndString(user_config_option('date_format'), $condValue);
$value = date("m/d/Y", $dtFromWidget->getTimestamp());
}
} else {
$value = $condValue;
}
}
if ($condition['condition'] == "like") {
$where_condiition .= " AND " . $condition['field_name'] . " " . $condition['condition'] . " '" . $value . "%' ";
} else {
$where_condiition .= " AND " . $condition['field_name'] . " " . $condition['condition'] . " '" . $value . "' ";
}
$conditions_view[$cont]['id'] = $condition['id'];
$conditions_view[$cont]['custom_property_id'] = $condition['custom_property_id'];
$conditions_view[$cont]['field_name'] = $condition['field_name'];
$conditions_view[$cont]['condition'] = $condition['condition'];
$conditions_view[$cont]['value'] = $value;
$cont++;
}
tpl_assign('conditions', $conditions_view);
if ($type_object) {
$object_table = ObjectTypes::findById($type_object);
$table = $object_table->getTableName();
}
$sql = "\t\n\t\t\tSELECT distinct(so.rel_object_id) AS id\n\t\t\tFROM " . TABLE_PREFIX . "searchable_objects so\n INNER JOIN " . TABLE_PREFIX . $table . " nto ON nto.object_id = so.rel_object_id \n\t\t\tINNER JOIN " . TABLE_PREFIX . "objects o ON o.id = so.rel_object_id \n\t\t\tWHERE (\n\t\t\t\t(\n\t\t\t\t\tso.rel_object_id IN (\n\t\t\t \t\tSELECT object_id FROM " . TABLE_PREFIX . "sharing_table WHERE group_id IN (\n\t\t\t \t\t\tSELECT permission_group_id FROM " . TABLE_PREFIX . "contact_permission_groups WHERE contact_id = {$uid}\n\t\t\t \t\t)\n\t\t\t \t\t)\n\t\t\t \t)\n\t\t\t) " . $where_condiition . $members_sql . " ORDER by o.updated_on DESC\n\t\t\tLIMIT {$start}, {$limitTest} ";
} else {
$type_object = '';
$sql = "\t\n\t\t\tSELECT distinct(so.rel_object_id) AS id\n\t\t\tFROM " . TABLE_PREFIX . "searchable_objects so\n\t\t\tINNER JOIN " . TABLE_PREFIX . "objects o ON o.id = so.rel_object_id \n\t\t\tWHERE (\n\t\t\t\t(\t\n\t\t\t\t\to.object_type_id = {$revisionObjectTypeId} AND \n\t\t\t\t\tEXISTS ( \n\t\t\t\t\t\tSELECT id FROM " . TABLE_PREFIX . "sharing_table WHERE object_id = ( SELECT file_id FROM " . TABLE_PREFIX . "project_file_revisions WHERE object_id = o.id ) \n\t\t\t\t\t\tAND group_id IN (SELECT permission_group_id FROM " . TABLE_PREFIX . "contact_permission_groups WHERE contact_id = {$uid} )\n\t\t\t\t\t)\n\t\t\t\t\t\n\t\t\t\t) \n\t\t\t\tOR (\n\t\t\t\t\tso.rel_object_id IN (\n\t\t\t \t\tSELECT object_id FROM " . TABLE_PREFIX . "sharing_table WHERE group_id IN (\n\t\t\t \t\t\tSELECT permission_group_id FROM " . TABLE_PREFIX . "contact_permission_groups WHERE contact_id = {$uid}\n\t\t\t \t\t)\n\t\t\t \t\t)\n\t\t\t \t)\n\t\t\t)" . ($useLike ? "AND\tso.content LIKE '%{$search_string}%' " : "AND MATCH (so.content) AGAINST ('{$search_string}' IN BOOLEAN MODE) ") . " \n\t\t\tAND o.object_type_id IN ({$listableObjectTypeIds}) \n " . $members_sql . "\n\t\t\tORDER by o.updated_on DESC\n\t\t\tLIMIT {$start}, {$limitTest} ";
}
tpl_assign('type_object', $type_object);
$db_search_results = array();
$search_results_ids = array();
$timeBegin = time();
$res = DB::execute($sql);
$timeEnd = time();
if ($this->debug) {
alert_r("<br>SQL:<br>" . $sql . "<hr>TIME:" . ($timeEnd - $timeBegin));
}
while ($row = $res->fetchRow()) {
$search_results_ids[] = $row['id'];
}
// Prepare results for view to avoid processing at presentation layer
$search_results = $this->prepareResults($search_results_ids, $null, $limit);
// Calculate or approximate total for pagination
$total = count($search_results_ids) + $start;
if (count($search_results_ids) < $limitTest) {
$total = count($search_results_ids) + $start;
} else {
$total = "Many";
}
//$total -= $filteredResults ;
$this->total = $total;
// Pagination
$this->buildPagination($search_results);
// Extra data
$extra = new stdClass();
if ($this->showQueryTime) {
$extra->time = $timeEnd - $timeBegin;
}
//$extra->filteredResults = $filteredResults ;
// Template asigns
tpl_assign('pagination', $this->pagination);
tpl_assign('search_string', $search_for);
tpl_assign('search_dimension', $this->search_dimension);
tpl_assign('search_results', $search_results);
tpl_assign('advanced', $advanced);
tpl_assign('extra', $extra);
$types = array(array("", lang("select one")));
$object_types = ObjectTypes::getAvailableObjectTypes();
foreach ($object_types as $ot) {
$types[] = array($ot->getId(), lang($ot->getName()));
}
// if ($selected_type != '')
// tpl_assign('allowed_columns', $this->get_allowed_columns($selected_type));
tpl_assign('object_types', $types);
// //Ajax
if (!$total && !$advanced) {
if ($_POST && count($search_results < 0)) {
tpl_assign('msg_advanced', true);
} else {
$this->setTemplate('no_results');
}
}
ajx_set_no_toolbar(true);
}
