/** * Add custom search query (from the get param 'q') * * @param AIR2_Query $q * @param string $alias * @param string $search * @param boolean $useOr */ public static function add_search_str(&$q, $alias, $search, $useOr = null) { $a = $alias ? "{$alias}." : ""; $str = "(" . $a . "tm_name REGEXP ? OR iptc_name REGEXP ?)"; if ($useOr) { $q->orWhere($str, array("[[:<:]]{$search}", "[[:<:]]{$search}")); } else { $q->addWhere($str, array("[[:<:]]{$search}", "[[:<:]]{$search}")); } }
/** * Returns assoc array of all countries. * * @return array $all_countries_hash */ public static function get_all() { $q = AIR2_Query::create()->from('Country a'); $countries = array(); foreach ($q->execute() as $c) { $countries[$c->cntry_code] = $c->cntry_name; } return $countries; }
/** * Returns assoc array of all states. * * @return array $all_states_hash */ public static function get_all() { $q = AIR2_Query::create()->from('State a'); $states = array(); foreach ($q->execute() as $s) { $states[$s->state_code] = $s->state_name; } return $states; }
/** * Add custom search query (from the get param 'q') * * @param AIR2_Query $q * @param string $alias * @param string $search * @param boolean $useOr */ public static function add_search_str(&$q, $alias, $search, $useOr = null) { // make sure "SavedSearch" is part of the query $from_parts = $q->getDqlPart('from'); foreach ($from_parts as $string_part) { if ($match = strpos($string_part, "{$alias}.SavedSearch")) { $offset = strlen("{$alias}.SavedSearch") + 1; // remove space $pss_alias = substr($string_part, $match + $offset); $a = $pss_alias ? "{$pss_alias}." : ""; $str = "(" . $a . "ssearch_name LIKE ?)"; if ($useOr) { $q->orWhere($str, array("{$search}%")); } else { $q->addWhere($str, array("{$search}%")); } break; } } }
/** * Get all the possible values for the given type. * * -birth_year * -education_level * -ethnicity * -gender * -household_income * -lifecycle * -political_affiliation * -religion * -source_website * -timezone * false if none found. * * * @param string $type The type of fact to look up. Examples: * @return array Associative array from values to readable labels. Returns */ public static function get_hash_for($type) { $q = AIR2_Query::create()->from("FactValue fv")->innerJoin("fv.Fact f")->where("f.fact_identifier = ?", $type)->orderBy("fv.fv_seq asc")->execute(); if (count($q) === 0) { return false; } $vals = array(); foreach ($q as $record) { $vals[$record->fv_id] = $record->fv_value; } return $vals; }
/** * Query * * @param array $args * @return Doctrine_Query $q */ protected function air_query($args = array()) { $q = Doctrine_Query::create()->from('JobQueue j'); $q->leftJoin('j.CreUser cu'); $this->add_status($q); // since status isn't real, have to execute the actual queries if (isset($args['status'])) { $st = $args['status']; if (is_string($st) && strlen($st) && $st != '*') { $ins = str_split($st); $wherein = array(); foreach ($ins as $code) { if (isset($this->status_queries[$code])) { $wherein[] = '(' . $this->status_queries[$code] . ')'; } } $q->addWhere(implode(' or ', $wherein)); } } // owner == cre_user_id if (isset($args['owner'])) { User::add_search_str($q, 'cu', $args['owner']); } // job search if (isset($args['q'])) { // also look at the owner $tmp = AIR2_Query::create(); User::add_search_str($tmp, 'cu', $args['q']); $params = $tmp->getParams(); $params = isset($params['where']) ? $params['where'] : array(); $tmp = array_pop($tmp->getDqlPart('where')); // query job $params[] = '%' . $args['q'] . '%'; $q->addWhere("({$tmp} or j.jq_job like ?)", $params); } return $q; }
/** * Add custom search query (from the get param 'q') * * @param AIR2_Query $q * @param string $alias * @param string $search * @param boolean $useOr */ public static function add_search_str(&$q, $alias, $search, $useOr = null) { $mod = 'Organization'; if (InqOrg::$UUID_COL == 'Inquiry:inq_uuid') { $mod = 'Inquiry'; } // make sure "Organization" or "Inquiry" is part of the query $from_parts = $q->getDqlPart('from'); foreach ($from_parts as $string_part) { if ($match = strpos($string_part, "{$alias}.{$mod}")) { $offset = strlen("{$alias}.{$mod}") + 1; // remove space $org_alias = substr($string_part, $match + $offset); $a = $org_alias ? "{$org_alias}." : ""; if ($mod == 'Organization') { $str = "(" . $a . "org_name LIKE ? OR " . $a . "org_display_name LIKE ?)"; } else { $str = "(" . $a . "inq_title LIKE ? OR " . $a . "inq_ext_title LIKE ?)"; } if ($useOr) { $q->orWhere($str, array("%{$search}%", "%{$search}%")); } else { $q->addWhere($str, array("%{$search}%", "%{$search}%")); } break; } } }
/** * Returns authorized count of sources for $user + $org. * * @param User $user * @param Email $email * @return integer $count */ public function get_mailchimp_exportable_count($user, $email) { $q = AIR2_Query::create()->from('BinSource'); $q->where("bsrc_bin_id = ?", $this->bin_id); // src_org status $exportable_org_ids = $user->get_authz_str(ACTION_EXPORT_MAILCHIMP, 'soc_org_id'); $status = "soc_status = '" . SrcOrg::$STATUS_OPTED_IN . "'"; $cache = "select soc_src_id from src_org_cache where {$exportable_org_ids} and {$status}"; $q->addWhere("bsrc_src_id in ({$cache})"); // TODO should probably check emails status too return $q->count(); }
/** * Joins to xid relations * * @param AIR2_Query $q * @param string $alias */ public static function joinRelated($q, $alias) { $a = $alias ? "{$alias}." : ""; SrcExport::setupRelated(); $q->leftJoin("{$a}Bin WITH {$a}se_ref_type = ?", self::$REF_TYPE_BIN); $q->leftJoin("{$a}Source WITH {$a}se_ref_type = ?", self::$REF_TYPE_SOURCE); $q->leftJoin("{$a}SrcResponseSet xidsrs WITH {$a}se_ref_type = ?", self::$REF_TYPE_RESPONSE); $q->leftJoin("xidsrs.Source xidsrssrc"); }
/** * * * @return unknown */ public function get_number_involved_activities() { $q = AIR2_Query::create()->from('SrcActivity sact')->where('sact.sact_src_id=?', $this->src_id)->andWhere("sact.sact_actm_id in (select actm_id from activity_master where actm_type in ('I'))"); $res = $q->execute(); $c = $res->count(); $q->free(); $res->free(); return $c; }
/** * Returns AIR2_Query object for published Inquiries. * * @param string $class (optional) * @return AIR2_Query $q */ public static function get_base_query_for_published($class = 'Inquiry') { $now = air2_date(); $q = AIR2_Query::create()->from("{$class} i")->whereIn("i.inq_type", array(self::$TYPE_FORMBUILDER, self::$TYPE_QUERYBUILDER, self::$TYPE_TEST, self::$TYPE_NONJOURN))->andWhereIn('i.inq_status', Inquiry::published_status_flags())->andWhere("(i.inq_publish_dtim is null OR i.inq_publish_dtim <= '{$now}')")->andWhere("(i.inq_expire_dtim is null OR i.inq_expire_dtim > '{$now}')")->andWhere("(i.inq_deadline_dtim is null OR i.inq_deadline_dtim > '{$now}')"); return $q; }
/** * Helper function to fetch a User * * @param string $username * @return User */ private function _fetch_user($username) { // get user from DB $user = AIR2_Query::create()->from('User u')->where('u.user_username = ?', $username)->andWhere('u.user_status != ?', User::$STATUS_INACTIVE)->leftJoin('u.UserOrg o')->leftJoin('o.AdminRole a')->fetchOne(); return $user; }
/** * Add custom search query (from the get param 'q') * * @param AIR2_Query $q * @param string $alias * @param string $search * @param boolean $useOr */ public static function add_search_str(&$q, $alias, $search, $useOr = null) { $a = $alias ? "{$alias}." : ""; $str = "({$a}email_campaign_name like ? or email_subject_line like ?)"; $params = array("%{$search}%", "%{$search}%"); // try searching cre_user and organization if ($alias) { $parts = $q->getDqlPart('from'); foreach ($parts as $dql) { // CreUser if (preg_match("/{$alias}.CreUser.*\$/", $dql, $matches)) { $usr_alias = preg_replace("/{$alias}.CreUser\\s*/", '', $matches[0]); if ($usr_alias) { $tmp = Doctrine_Query::create(); User::add_search_str($tmp, $usr_alias, $search); $usrq = array_pop($tmp->getDqlPart('where')); $usrp = $tmp->getFlattenedParams(); $str .= " or {$usrq}"; $params = array_merge($params, $usrp); } } // Organization if (preg_match("/{$alias}.Organization.*\$/", $dql, $matches)) { $org_alias = preg_replace("/{$alias}.Organization\\s*/", '', $matches[0]); if ($org_alias) { $tmp = Doctrine_Query::create(); Organization::add_search_str($tmp, $org_alias, $search); $orgq = array_pop($tmp->getDqlPart('where')); $orgp = $tmp->getFlattenedParams(); $str .= " or {$orgq}"; $params = array_merge($params, $orgp); } } } } // add to query if ($useOr) { $q->orWhere($str, $params); } else { $q->addWhere($str, $params); } }
/** * Add user counts to an organization query. * * @param AIR2_Query $q * @param char $alias * @param array $fld_defs (optional, reference) */ public static function add_counts(AIR2_Query $q, $alias = null, &$fld_defs = null) { $a = $alias ? "{$alias}." : ''; $uost = UserOrg::$STATUS_ACTIVE; $ust1 = User::$STATUS_ACTIVE; $ust2 = User::$STATUS_PUBLISHABLE; $active_users = "select user_id from user where user_status " . "= '{$ust1}' or user_status = '{$ust2}'"; $c = "select count(*) from user_org where uo_org_id = {$a}org_id " . "and uo_status = '{$uost}' and uo_user_id in ({$active_users})"; $q->addSelect("({$c}) as active_users"); if (is_array($fld_defs)) { $fld_defs[] = array('name' => 'active_users', 'type' => 'int'); } }
/** * Apply authz rules for who may manage a User. * * @param AIR2_Query $q * @param User $u * @param string $alias (optional) */ public static function query_may_manage(AIR2_Query $q, User $u, $alias = null) { if ($u->is_system()) { return; } $a = $alias ? "{$alias}." : ""; // delete-usr authz in org $manage_org_ids = $u->get_authz_str(ACTION_ORG_USR_DELETE, 'uo_org_id', false); $stat = UserOrg::$STATUS_ACTIVE; $usr_ids = "select uo_user_id from user_org where {$manage_org_ids}"; $q->addWhere("{$a}user_id in ({$usr_ids})"); }
/** * Execute query and format results * * @param string $qry * @return array $radix */ protected function format_query_radix($qry) { $user_id = $this->parent_rec->user_id; $conn = AIR2_DBManager::get_connection(); $limit = $this->my_limit; $offset = $this->my_offset; $all = $conn->fetchAll("{$qry} order by dtim desc limit {$limit} offset {$offset}"); // get PK id's by type $ids_by_type = array(); foreach ($all as $row) { $type = $row['type']; if (!isset($ids_by_type[$type])) { $ids_by_type[$type] = array(); } $ids_by_type[$type][] = $row['id']; } // fetch all the types $fetched_in_order = array(); if (count($all)) { $fetched_in_order = array_fill(0, count($all), null); } foreach ($ids_by_type as $type => $ids) { $model = $this->actv_config[$type]['model']; $idcol = $this->actv_config[$type]['id']; $q = AIR2_Query::create()->from("{$model} a"); $q->whereIn("a.{$idcol}", $ids); if (isset($this->actv_config[$type]['join'])) { foreach ($this->actv_config[$type]['join'] as $join) { $q->leftJoin("a.{$join}"); } } $rs = $q->fetchArray(); // put them back in the right order foreach ($rs as $object) { $my_id = $object[$idcol]; foreach ($all as $idx => $row) { if ($row['type'] == $type && $row['id'] == $my_id) { $fetched_in_order[$idx] = array('type' => $type, 'dtim' => $row['dtim'], 'id' => $type . $my_id, $model => $object); break; } } } } // clean data foreach ($fetched_in_order as &$row) { $row = $this->_clean($row, $this->_fields); } return $fetched_in_order; }
/** * Apply authz rules for who may view the existence of a Saved Search. * * @param AIR2_Query $q * @param User $u * @param string $alias (optional) */ public static function query_may_write(AIR2_Query $q, User $u, $alias = null) { if ($u->is_system()) { return; } $a = $alias ? "{$alias}." : ""; $q->addWhere("{$a}ssearch_cre_user = ?", $u->user_id); }
/** * Only one of certain types (e.g. email) allowed. * * @param Doctrine_Event $event */ public function preValidate($event) { parent::preValidate($event); // check if this type is unique and we already have one if (!$this->osid_id && in_array($this->osid_type, self::$UNIQUE_TYPES)) { $q = AIR2_Query::create()->from('OrgSysId os'); $q->addWhere('osid_type = ?', $this->osid_type); $q->addWhere('osid_org_id = ?', $this->osid_org_id); if ($this->exists()) { $q->addWhere('osid_id != ?', $this->osid_id); } if ($q->count() > 0) { throw new Exception("May only have one OrgSysId of type " . $this->osid_type); } } }
/** * Add custom search query (from the get param 'q') * * @param AIR2_Query $q * @param string $alias * @param string $search * @param boolean $useOr */ public static function add_search_str(&$q, $alias, $search, $useOr = null) { // find the "Organization" alias in the query $from_parts = $q->getDqlPart('from'); foreach ($from_parts as $string_part) { if ($match = strpos($string_part, "{$alias}.Organization")) { $offset = strlen("{$alias}.Organization") + 1; // remove space $usr_alias = substr($string_part, $match + $offset); Organization::add_search_str($q, $usr_alias, $search, $useOr); break; } } }
/** * Apply authz rules for who may write. * * @param AIR2_Query $q * @param User $u * @param string $alias (optional) */ public static function query_may_write(AIR2_Query $q, User $u, $alias = null) { if ($u->is_system()) { return; } $a = $alias ? "{$alias}." : ""; // readable inquiries $tmp = AIR2_Query::create(); Inquiry::query_may_read($tmp, $u); $tmp = array_pop($tmp->getDqlPart('where')); $inq_ids = "select inq_id from inquiry where {$tmp}"; // add to query $user_id = $u->user_id; $own = "{$a}inqan_cre_user = {$user_id}"; $q->addWhere("({$a}inqan_inq_id in ({$inq_ids}) and {$own})"); }
/** * Inherit from Project * * @param AIR2_Query $q * @param User $u * @param string $alias (optional) */ public static function query_may_read(AIR2_Query $q, User $u, $alias = null) { if ($u->is_system()) { return; } $a = $alias ? "{$alias}." : ""; // readable projects $tmp = AIR2_Query::create(); Project::query_may_read($tmp, $u); $tmp = array_pop($tmp->getDqlPart('where')); $prj_ids = "select prj_id from project where {$tmp}"; // fetch actual id's, to prevent doctrine from adding its own alias to // our columns (porg fields will get re-aliased by doctrine). $conn = AIR2_DBManager::get_connection(); $rs = $conn->fetchColumn($prj_ids, array(), 0); $prj_ids = count($rs) ? implode(',', $rs) : 'NULL'; $q->addWhere("{$a}porg_prj_id in ({$prj_ids})"); }
/** * * * @return unknown */ private function get_all_orgs_by_uuid() { $q = AIR2_Query::create()->from("Organization"); $res = $q->execute(array(), Doctrine_Core::HYDRATE_ON_DEMAND); $orgs = array(); foreach ($res as $o) { $orgs[$o->org_uuid] = $o->toArray(); } return $orgs; }
/** * Apply authz rules for who may manage a SrcResponseSet. * * @param AIR2_Query $q * @param User $u * @param string $alias (optional) */ public static function query_may_manage(AIR2_Query $q, User $u, $alias = null) { if ($u->is_system()) { return; } $a = $alias ? "{$alias}." : ""; // manageable $mg_org_ids = $u->get_authz_str(ACTION_ORG_PRJ_INQ_SRS_DELETE, 'porg_org_id', true); $prj_ids = "select porg_prj_id from project_org where {$mg_org_ids}"; $inq_ids = "select pinq_inq_id from project_inquiry where pinq_prj_id in ({$prj_ids})"; // fetch actual id's, to prevent doctrine from adding its own alias to // our columns (pinq fields will get re-aliased by doctrine). $conn = AIR2_DBManager::get_connection(); $rs = $conn->fetchColumn($inq_ids, array(), 0); $inq_ids = count($rs) ? implode(',', $rs) : 'NULL'; // add to query $q->addWhere("{$a}srs_inq_id in ({$inq_ids})"); }
/** * Joins a SrcActivity to it's external reference in a Doctrine Query. * * @param AIR2_Query $q * @param string $alias */ public static function joinRelated($q, $alias) { $a = $alias ? "{$alias}." : ""; SrcActivity::setupRelated(); $q->leftJoin("{$a}SrcResponseSet WITH {$a}sact_ref_type = ?", self::$REF_TYPE_RESPONSE); $q->leftJoin("{$a}Tank WITH {$a}sact_ref_type = ?", self::$REF_TYPE_TANK); $q->leftJoin("{$a}Organization WITH {$a}sact_ref_type = ?", self::$REF_TYPE_ORG); $q->leftJoin("{$a}Inquiry WITH {$a}sact_ref_type = ?", self::$REF_TYPE_INQUIRY); $q->leftJoin("{$a}Email WITH {$a}sact_ref_type = ?", self::$REF_TYPE_EMAIL); }
/** * Joins a ProjectActivity to it's external reference in a Doctrine Query. * * @param AIR2_Query $q * @param string $alias */ public static function joinRelated($q, $alias) { $a = $alias ? "{$alias}." : ""; ProjectActivity::setupRelated(); $q->leftJoin("{$a}Organization WITH {$a}pa_ref_type = ?", self::$REF_TYPE_ORG); $q->leftJoin("{$a}Inquiry WITH {$a}pa_ref_type = ?", self::$REF_TYPE_INQ); }
/** * Add a query string to for "from" text * * @param AIR2_Query $q * @param string $alias * @param string $search * @param boolean $useOr (optional) */ public static function add_search_str(&$q, $alias, $search, $useOr = null) { $a = $alias ? "{$alias}." : ""; $str = "{$a}xm_xlate_from like ?"; if ($useOr) { $q->orWhere($str, array("%{$search}%")); } else { $q->addWhere($str, array("%{$search}%")); } }
/** * Add custom search query (from the get param 'q') * * @return unknown * @param AIR2_Query $q * @param string $alias * @param string $search * @param boolean $useOr */ public static function add_search_str(&$q, $alias, $search, $useOr = null) { $a = $alias ? "{$alias}." : ""; $str = "({$a}prj_name LIKE ? OR {$a}prj_display_name LIKE ?)"; if ($useOr) { $q->orWhere($str, array("%{$search}%", "{$search}%")); } else { $q->addWhere($str, array("%{$search}%", "{$search}%")); } }
/** * Apply authz rules for who may manage a SrcResponse. * * @param AIR2_Query $q * @param User $u * @param string $alias (optional) */ public static function query_may_manage(AIR2_Query $q, User $u, $alias = null) { if ($u->is_system()) { return; } $a = $alias ? "{$alias}." : ""; // manageable src_response_sets $tmp = AIR2_Query::create(); SrcResponseSet::query_may_manage($tmp, $u); $tmp = array_pop($tmp->getDqlPart('where')); $srs_ids = "select srs_id from src_response_set where {$tmp}"; // add to query $q->addWhere("{$a}sr_srs_id in ({$srs_ids})"); }
/** * Add conflict/error/done/etc counts to a tank. Optionally pass in which * types to select, rather than them all. * * @param AIR2_Query $q * @param char $alias * @param array $fld_defs (optional, reference) * @param array $types (optional) */ public static function add_counts($q, $alias, &$fld_defs = null, $types = null) { // code => subselect-alias $type_counts = array('*' => 'count_total', TankSource::$STATUS_NEW => 'count_new', TankSource::$STATUS_CONFLICT => 'count_conflict', TankSource::$STATUS_RESOLVED => 'count_resolved', TankSource::$STATUS_LOCKED => 'count_locked', TankSource::$STATUS_DONE => 'count_done', TankSource::$STATUS_ERROR => 'count_error'); if ($types) { $less_types = array(); foreach ($types as $t) { $less_types[$t] = $type_counts[$t]; } $type_counts = $less_types; } // add subselects $sub = "select count(*) from tank_source where tsrc_tank_id = {$alias}.tank_id"; foreach ($type_counts as $code => $name) { if ($code == '*') { $q->addSelect("({$sub}) as {$name}"); } else { $q->addSelect("({$sub} and tsrc_status = '{$code}') as {$name}"); } if (is_array($fld_defs)) { $fld_defs[] = array('name' => $name, 'type' => 'int'); } } }
/** * Apply authz rules for who may manage. * * @param AIR2_Query $q * @param User $u * @param string $alias (optional) */ public static function query_may_manage(AIR2_Query $q, User $u, $alias = null) { if ($u->is_system()) { return; } $a = $alias ? "{$alias}." : ""; $user_id = $u->user_id; $prjq = $q->createSubquery(); $prjq->select('prj.prj_id'); $prjq->from('Project prj'); Project::query_may_manage($prjq, $u); $q->addWhere("{$a}prjan_prj_id IN (" . $prjq->getDql() . ")"); $q->addWhere("{$a}prjan_cre_user = ?", $u->user_id); }