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