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