/**
* 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;
}
/**
* 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;
}
/**
*
*
* @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;
}
/**
* 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})");
}
/**
* 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;
}
/**
* 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();
}
/**
* 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})");
}
/**
* 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})");
}
/**
* 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;
}
/**
* 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);
}
}
}
/**
*
*/
public function cancel_scheduled_send()
{
// find the job and delete it
$job_sql = 'jq_type=? and jq_xid=? and jq_start_dtim is null and jq_start_after_dtim=?';
$q = AIR2_Query::create()->from('JobQueue');
$q->where($job_sql, array(JobQueue::$TYPE_EMAIL, $this->email_id, $this->email_schedule_dtim));
$job = $q->fetchOne();
if (!$job) {
throw new Exception("No scheduled job found");
}
$job->delete();
$q->free();
// null schedule column and reset status
$this->email_schedule_dtim = null;
$this->email_status = self::$STATUS_DRAFT;
}
/**
*
*
* @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;
}
/**
* 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;
}
