/** * Enter description here... * * @param DevblocksSearchCriteria[] $params * @param integer $limit * @param integer $page * @param string $sortBy * @param boolean $sortAsc * @param boolean $withCounts * @return array */ static function search($columns, $params, $limit = 10, $page = 0, $sortBy = null, $sortAsc = null, $withCounts = true) { $db = DevblocksPlatform::getDatabaseService(); $fields = SearchFields_FeedbackEntry::getFields(); // Sanitize if (!isset($fields[$sortBy])) { $sortBy = null; } list($tables, $wheres) = parent::_parseSearchParams($params, $columns, $fields, $sortBy); $start = $page * $limit; // [JAS]: 1-based [TODO] clean up + document $select_sql = sprintf("SELECT " . "f.id as %s, " . "f.log_date as %s, " . "f.worker_id as %s, " . "f.quote_text as %s, " . "f.quote_mood as %s, " . "f.quote_address_id as %s, " . "f.source_url as %s, " . "a.email as %s ", SearchFields_FeedbackEntry::ID, SearchFields_FeedbackEntry::LOG_DATE, SearchFields_FeedbackEntry::WORKER_ID, SearchFields_FeedbackEntry::QUOTE_TEXT, SearchFields_FeedbackEntry::QUOTE_MOOD, SearchFields_FeedbackEntry::QUOTE_ADDRESS_ID, SearchFields_FeedbackEntry::SOURCE_URL, SearchFields_FeedbackEntry::ADDRESS_EMAIL); $join_sql = "FROM feedback_entry f " . "LEFT JOIN address a ON (f.quote_address_id=a.id) "; // [JAS]: Dynamic table joins // (isset($tables['o']) ? "LEFT JOIN contact_org o ON (o.id=tt.debit_org_id)" : " "). // (isset($tables['mc']) ? "INNER JOIN message_content mc ON (mc.message_id=m.id)" : " "). // Custom field joins list($select_sql, $join_sql, $has_multiple_values) = self::_appendSelectJoinSqlForCustomFieldTables($tables, $params, 'f.id', $select_sql, $join_sql); $where_sql = "" . (!empty($wheres) ? sprintf("WHERE %s ", implode(' AND ', $wheres)) : ""); $sort_sql = !empty($sortBy) ? sprintf("ORDER BY %s %s ", $sortBy, $sortAsc || is_null($sortAsc) ? "ASC" : "DESC") : " "; $sql = $select_sql . $join_sql . $where_sql . ($has_multiple_values ? 'GROUP BY f.id ' : '') . $sort_sql; $rs = $db->SelectLimit($sql, $limit, $start) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg()); /* @var $rs ADORecordSet */ $results = array(); if (is_a($rs, 'ADORecordSet')) { while (!$rs->EOF) { $result = array(); foreach ($rs->fields as $f => $v) { $result[$f] = $v; } $id = intval($rs->fields[SearchFields_FeedbackEntry::ID]); $results[$id] = $result; $rs->MoveNext(); } } // [JAS]: Count all $total = -1; if ($withCounts) { $count_sql = ($has_multiple_values ? "SELECT COUNT(DISTINCT f.id) " : "SELECT COUNT(f.id) ") . $join_sql . $where_sql; $total = $db->GetOne($count_sql); } return array($results, $total); }
/** * Enter description here... * * @param DevblocksSearchCriteria[] $params * @param integer $limit * @param integer $page * @param string $sortBy * @param boolean $sortAsc * @param boolean $withCounts * @return array */ static function search($columns, $params, $limit = 10, $page = 0, $sortBy = null, $sortAsc = null, $withCounts = true) { $db = DevblocksPlatform::getDatabaseService(); $fields = SearchFields_CrmOpportunity::getFields(); // Sanitize if (!isset($fields[$sortBy])) { unset($sortBy); } list($tables, $wheres) = parent::_parseSearchParams($params, $columns, $fields); $start = $page * $limit; // [JAS]: 1-based [TODO] clean up + document $select_sql = sprintf("SELECT " . "o.id as %s, " . "o.name as %s, " . "o.amount as %s, " . "org.id as %s, " . "org.name as %s, " . "o.primary_email_id as %s, " . "a.email as %s, " . "o.created_date as %s, " . "o.updated_date as %s, " . "o.closed_date as %s, " . "o.is_closed as %s, " . "o.is_won as %s, " . "o.worker_id as %s ", SearchFields_CrmOpportunity::ID, SearchFields_CrmOpportunity::NAME, SearchFields_CrmOpportunity::AMOUNT, SearchFields_CrmOpportunity::ORG_ID, SearchFields_CrmOpportunity::ORG_NAME, SearchFields_CrmOpportunity::PRIMARY_EMAIL_ID, SearchFields_CrmOpportunity::EMAIL_ADDRESS, SearchFields_CrmOpportunity::CREATED_DATE, SearchFields_CrmOpportunity::UPDATED_DATE, SearchFields_CrmOpportunity::CLOSED_DATE, SearchFields_CrmOpportunity::IS_CLOSED, SearchFields_CrmOpportunity::IS_WON, SearchFields_CrmOpportunity::WORKER_ID); $join_sql = "FROM crm_opportunity o " . "LEFT JOIN address a ON (a.id = o.primary_email_id) " . "LEFT JOIN contact_org org ON (org.id = a.contact_org_id) "; // [JAS]: Dynamic table joins // (isset($tables['m']) ? "INNER JOIN requester r ON (r.ticket_id=t.id)" : " "). // Custom field joins list($select_sql, $join_sql) = self::_appendSelectJoinSqlForCustomFieldTables($tables, 'o.id', $select_sql, $join_sql); $where_sql = "" . (!empty($wheres) ? sprintf("WHERE %s ", implode(' AND ', $wheres)) : ""); $sort_sql = !empty($sortBy) ? sprintf("ORDER BY %s %s ", $sortBy, $sortAsc || is_null($sortAsc) ? "ASC" : "DESC") : " "; $group_sql = "GROUP BY o.id "; $sql = $select_sql . $join_sql . $where_sql . $group_sql . $sort_sql; $rs = $db->SelectLimit($sql, $limit, $start) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg()); /* @var $rs ADORecordSet */ $results = array(); if (is_a($rs, 'ADORecordSet')) { while (!$rs->EOF) { $result = array(); foreach ($rs->fields as $f => $v) { $result[$f] = $v; } $id = intval($rs->fields[SearchFields_CrmOpportunity::ID]); $results[$id] = $result; $rs->MoveNext(); } } // [JAS]: Count all $total = -1; if ($withCounts) { $count_sql = "SELECT COUNT(DISTINCT o.id) " . $join_sql . $where_sql; $total = $db->GetOne($count_sql); } return array($results, $total); }
/** * Enter description here... * * @param DevblocksSearchCriteria[] $params * @param integer $limit * @param integer $page * @param string $sortBy * @param boolean $sortAsc * @param boolean $withCounts * @return array */ static function search($columns, $params, $limit = 10, $page = 0, $sortBy = null, $sortAsc = null, $withCounts = true) { $db = DevblocksPlatform::getDatabaseService(); $fields = SearchFields_Task::getFields(); // Sanitize if (!isset($fields[$sortBy])) { $sortBy = null; } list($tables, $wheres) = parent::_parseSearchParams($params, $columns, $fields, $sortBy); $start = $page * $limit; // [JAS]: 1-based [TODO] clean up + document $select_sql = sprintf("SELECT " . "t.id as %s, " . "t.due_date as %s, " . "t.is_completed as %s, " . "t.completed_date as %s, " . "t.title as %s, " . "t.content as %s, " . "t.worker_id as %s, " . "t.source_extension as %s, " . "t.source_id as %s ", SearchFields_Task::ID, SearchFields_Task::DUE_DATE, SearchFields_Task::IS_COMPLETED, SearchFields_Task::COMPLETED_DATE, SearchFields_Task::TITLE, SearchFields_Task::CONTENT, SearchFields_Task::WORKER_ID, SearchFields_Task::SOURCE_EXTENSION, SearchFields_Task::SOURCE_ID); $join_sql = "FROM task t "; // "LEFT JOIN contact_org o ON (o.id=a.contact_org_id) " // [JAS]: Dynamic table joins // (isset($tables['o']) ? "LEFT JOIN contact_org o ON (o.id=a.contact_org_id)" : " "). // (isset($tables['mc']) ? "INNER JOIN message_content mc ON (mc.message_id=m.id)" : " "). // Custom field joins list($select_sql, $join_sql, $has_multiple_values) = self::_appendSelectJoinSqlForCustomFieldTables($tables, $params, 't.id', $select_sql, $join_sql); $where_sql = "" . (!empty($wheres) ? sprintf("WHERE %s ", implode(' AND ', $wheres)) : ""); $sort_sql = !empty($sortBy) ? sprintf("ORDER BY %s %s ", $sortBy, $sortAsc || is_null($sortAsc) ? "ASC" : "DESC") : " "; $sql = $select_sql . $join_sql . $where_sql . ($has_multiple_values ? 'GROUP BY t.id ' : '') . $sort_sql; $rs = $db->SelectLimit($sql, $limit, $start) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg()); /* @var $rs ADORecordSet */ $results = array(); if (is_a($rs, 'ADORecordSet')) { while (!$rs->EOF) { $result = array(); foreach ($rs->fields as $f => $v) { $result[$f] = $v; } $id = intval($rs->fields[SearchFields_Task::ID]); $results[$id] = $result; $rs->MoveNext(); } } // [JAS]: Count all $total = -1; if ($withCounts) { $count_sql = ($has_multiple_values ? "SELECT COUNT(DISTINCT t.id) " : "SELECT COUNT(t.id) ") . $join_sql . $where_sql; $total = $db->GetOne($count_sql); } return array($results, $total); }
public static function getSearchQueryComponents($columns, $params, $sortBy=null, $sortAsc=null) { $fields = SearchFields_IssueLink::getFields(); // Sanitize if('*'==substr($sortBy,0,1) || !isset($fields[$sortBy])) $sortBy=null; list($tables,$wheres) = parent::_parseSearchParams($params, $columns, $fields, $sortBy); $select_sql = sprintf("SELECT ". "issue_link.issue_id as %s, ". "issue_link.source_context as %s, ". "issue_link.source_id as %s, ". "issue_link.container_id as %s ", SearchFields_IssueLink::ISSUE_ID, SearchFields_IssueLink::CONTEXT, SearchFields_IssueLink::SOURCE_ID, SearchFields_IssueLink::CONTAINER_ID ); $join_sql = "FROM issue_link "; // Custom field joins //list($select_sql, $join_sql, $has_multiple_values) = self::_appendSelectJoinSqlForCustomFieldTables( // $tables, // $params, // 'issue_link.id', // $select_sql, // $join_sql //); $has_multiple_values = false; // [TODO] Temporary when custom fields disabled $where_sql = "". (!empty($wheres) ? sprintf("WHERE %s ",implode(' AND ',$wheres)) : "WHERE 1 "); $sort_sql = (!empty($sortBy)) ? sprintf("ORDER BY %s %s ",$sortBy,($sortAsc || is_null($sortAsc))?"ASC":"DESC") : " "; return array( 'primary_table' => 'issue_link', 'select' => $select_sql, 'join' => $join_sql, 'where' => $where_sql, 'has_multiple_values' => $has_multiple_values, 'sort' => $sort_sql, ); }
/** * Enter description here... * * @param DevblocksSearchCriteria[] $params * @param integer $limit * @param integer $page * @param string $sortBy * @param boolean $sortAsc * @param boolean $withCounts * @return array */ static function search($columns, $params, $limit = 10, $page = 0, $sortBy = null, $sortAsc = null, $withCounts = true) { $db = DevblocksPlatform::getDatabaseService(); $fields = SearchFields_CommunityTool::getFields(); // Sanitize if (!isset($fields[$sortBy])) { $sortBy = null; } list($tables, $wheres) = parent::_parseSearchParams($params, $columns, $fields, $sortBy); $start = $page * $limit; // [JAS]: 1-based [TODO] clean up + document $total = -1; $select_sql = sprintf("SELECT " . "ct.id as %s, " . "ct.name as %s, " . "ct.code as %s, " . "ct.extension_id as %s ", SearchFields_CommunityTool::ID, SearchFields_CommunityTool::NAME, SearchFields_CommunityTool::CODE, SearchFields_CommunityTool::EXTENSION_ID); $join_sql = "FROM community_tool ct "; // Custom field joins list($select_sql, $join_sql, $has_multiple_values) = self::_appendSelectJoinSqlForCustomFieldTables($tables, $params, 'ct.id', $select_sql, $join_sql); $where_sql = "" . (!empty($wheres) ? sprintf("WHERE %s ", implode(' AND ', $wheres)) : ""); $sort_sql = !empty($sortBy) ? sprintf("ORDER BY %s %s ", $sortBy, $sortAsc || is_null($sortAsc) ? "ASC" : "DESC") : " "; $sql = $select_sql . $join_sql . $where_sql . ($has_multiple_values ? 'GROUP BY ct.id ' : '') . $sort_sql; // [TODO] Could push the select logic down a level too if ($limit > 0) { $rs = $db->SelectLimit($sql, $limit, $start) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg()); /* @var $rs ADORecordSet */ } else { $rs = $db->Execute($sql) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg()); /* @var $rs ADORecordSet */ $total = $rs->RecordCount(); } $results = array(); if (is_a($rs, 'ADORecordSet')) { while (!$rs->EOF) { $result = array(); foreach ($rs->fields as $f => $v) { $result[$f] = $v; } $object_id = intval($rs->fields[SearchFields_CommunityTool::ID]); $results[$object_id] = $result; $rs->MoveNext(); } } // [JAS]: Count all if ($withCounts) { $count_sql = ($has_multiple_values ? "SELECT COUNT(DISTINCT ct.id) " : "SELECT COUNT(ct.id) ") . $join_sql . $where_sql; $total = $db->GetOne($count_sql); } return array($results, $total); }
public static function getSearchQueryComponents($columns, $params, $sortBy=null, $sortAsc=null) { $fields = SearchFields_Project::getFields(); // Sanitize if('*'==substr($sortBy,0,1) || !isset($fields[$sortBy])) $sortBy=null; list($tables,$wheres) = parent::_parseSearchParams($params, $columns, $fields, $sortBy); $select_sql = sprintf("SELECT ". "project.id as %s, ". "project.name as %s, ". "project.description as %s ", SearchFields_Project::ID, SearchFields_Project::NAME, SearchFields_Project::DESCRIPTION ); $join_sql = "FROM project "; // Custom field joins //list($select_sql, $join_sql, $has_multiple_values) = self::_appendSelectJoinSqlForCustomFieldTables( // $tables, // $params, // 'project.id', // $select_sql, // $join_sql //); $has_multiple_values = false; // [TODO] Temporary when custom fields disabled $where_sql = "". (!empty($wheres) ? sprintf("WHERE %s ",implode(' AND ',$wheres)) : "WHERE 1 "); $sort_sql = (!empty($sortBy)) ? sprintf("ORDER BY %s %s ",$sortBy,($sortAsc || is_null($sortAsc))?"ASC":"DESC") : " "; return array( 'primary_table' => 'project', 'select' => $select_sql, 'join' => $join_sql, 'where' => $where_sql, 'has_multiple_values' => $has_multiple_values, 'sort' => $sort_sql, ); }
/** * Enter description here... * * @param DevblocksSearchCriteria[] $params * @param integer $limit * @param integer $page * @param string $sortBy * @param boolean $sortAsc * @param boolean $withCounts * @return array */ static function search($columns, $params, $limit = 10, $page = 0, $sortBy = null, $sortAsc = null, $withCounts = true) { $db = DevblocksPlatform::getDatabaseService(); $fields = SearchFields_TimeTrackingEntry::getFields(); // Sanitize if (!isset($fields[$sortBy])) { unset($sortBy); } list($tables, $wheres) = parent::_parseSearchParams($params, $columns, $fields); $start = $page * $limit; // [JAS]: 1-based [TODO] clean up + document $select_sql = sprintf("SELECT " . "tt.id as %s, " . "tt.time_actual_mins as %s, " . "tt.log_date as %s, " . "tt.worker_id as %s, " . "tt.activity_id as %s, " . "tt.debit_org_id as %s, " . "tt.notes as %s, " . "tt.source_extension_id as %s, " . "tt.source_id as %s, " . "o.name as %s ", SearchFields_TimeTrackingEntry::ID, SearchFields_TimeTrackingEntry::TIME_ACTUAL_MINS, SearchFields_TimeTrackingEntry::LOG_DATE, SearchFields_TimeTrackingEntry::WORKER_ID, SearchFields_TimeTrackingEntry::ACTIVITY_ID, SearchFields_TimeTrackingEntry::DEBIT_ORG_ID, SearchFields_TimeTrackingEntry::NOTES, SearchFields_TimeTrackingEntry::SOURCE_EXTENSION_ID, SearchFields_TimeTrackingEntry::SOURCE_ID, SearchFields_TimeTrackingEntry::ORG_NAME); $join_sql = "FROM timetracking_entry tt " . "LEFT JOIN contact_org o ON (o.id=tt.debit_org_id) "; // [JAS]: Dynamic table joins // (isset($tables['o']) ? "LEFT JOIN contact_org o ON (o.id=tt.debit_org_id)" : " "). // (isset($tables['mc']) ? "INNER JOIN message_content mc ON (mc.message_id=m.id)" : " "). // Custom field joins list($select_sql, $join_sql) = self::_appendSelectJoinSqlForCustomFieldTables($tables, 'tt.id', $select_sql, $join_sql); $where_sql = "" . (!empty($wheres) ? sprintf("WHERE %s ", implode(' AND ', $wheres)) : ""); $sort_sql = !empty($sortBy) ? sprintf("ORDER BY %s %s ", $sortBy, $sortAsc || is_null($sortAsc) ? "ASC" : "DESC") : " "; $group_sql = "GROUP BY tt.id "; $sql = $select_sql . $join_sql . $where_sql . $group_sql . $sort_sql; $rs = $db->SelectLimit($sql, $limit, $start) or die(__CLASS__ . '(' . __LINE__ . ')' . ':' . $db->ErrorMsg()); /* @var $rs ADORecordSet */ $results = array(); if (is_a($rs, 'ADORecordSet')) { while (!$rs->EOF) { $result = array(); foreach ($rs->fields as $f => $v) { $result[$f] = $v; } $id = intval($rs->fields[SearchFields_TimeTrackingEntry::ID]); $results[$id] = $result; $rs->MoveNext(); } } // [JAS]: Count all $total = -1; if ($withCounts) { $count_sql = "SELECT COUNT(DISTINCT tt.id) " . $join_sql . $where_sql; $total = $db->GetOne($count_sql); } return array($results, $total); }