/** * Query * * @param array $args * @return Doctrine_Query $q */ protected function air_query($args = array()) { $q = Doctrine_Query::create()->from('Tank t'); $q->leftJoin('t.User u'); $q->leftJoin('t.TankOrg to'); $q->leftJoin('to.Organization o'); $q->leftJoin('t.TankActivity ta'); $q->leftJoin('ta.Project p'); $q->leftJoin('ta.ActivityMaster m'); if (isset($args['type'])) { air2_query_in($q, $args['type'], 't.tank_type'); } if (isset($args['status'])) { air2_query_in($q, $args['status'], 't.tank_status'); } // add flattened and return Tank::add_counts($q, 't'); TankActivity::add_event_meta($q, 'ta'); $q->addSelect('u.user_uuid as user_uuid'); $q->addSelect('o.org_uuid as org_uuid'); //TODO: not really flat $q->addSelect('p.prj_uuid as prj_uuid'); //TODO: not really flat return $q; }
/** * Query * * @param array $args * @return Doctrine_Query $q */ protected function air_query($args = array()) { $q = Doctrine_Query::create()->from('PreferenceType pt'); if (isset($args['status'])) { air2_query_in($q, $args['status'], 'pt.pt_status'); } if (isset($args['pt_identifier'])) { $q->andWhere('pt.pt_identifier = ?', $args['pt_identifier']); } return $q; }
/** * Query * * @param array $args * @return Doctrine_Query $q */ protected function air_query($args = array()) { $q = Doctrine_Query::create()->from('Fact f'); if (isset($args['status'])) { air2_query_in($q, $args['status'], 'f.fact_status'); } if (isset($args['type'])) { air2_query_in($q, $args['type'], 'f.fact_fv_type'); } return $q; }
/** * Query * * @param array $args * @return Doctrine_Query $q */ protected function air_query($args = array()) { $q = Doctrine_Query::create()->from('TankSource t'); $q->where('t.tsrc_tank_id = ?', $this->parent_rec->tank_id); $q->leftJoin('t.Source s'); if (isset($args['status'])) { air2_query_in($q, $args['status'], 't.tsrc_status'); } $this->add_status_sort($q); return $q; }
/** * Query * * @param array $args * @return Doctrine_Query $q */ protected function air_query($args = array()) { $q = Doctrine_Query::create()->from('SrcEmail e'); // only WRITE-able sources (#4462) $q->innerJoin('e.Source s'); $q->leftJoin('s.SrcOrg so WITH so.so_home_flag = true'); $q->leftJoin('so.Organization o'); Source::query_may_write($q, $this->user, 's'); // status if (isset($args['status'])) { air2_query_in($q, $args['status'], 'e.sem_status'); } return $q; }
/** * Query * * @param array $args * @return Doctrine_Query $q */ protected function air_query($args = array()) { $q = Doctrine_Query::create()->from('SrcExport s'); $q->addWhere('s.se_xid = ?', $this->parent_rec->bin_id); $q->addWhere('s.se_ref_type = ?', SrcExport::$REF_TYPE_BIN); $q->leftJoin('s.CreUser c'); $q->leftJoin('s.UpdUser u'); $q->leftJoin('s.Inquiry i'); $q->leftJoin('s.Project p'); $q->leftJoin('s.Email e'); // type if (isset($args['type'])) { air2_query_in($q, $args['type'], 's.se_type'); } return $q; }
/** * Query * * @param array $args * @return Doctrine_Query $q */ protected function air_query($args = array()) { $q = Doctrine_Query::create()->from('Inquiry i'); $q->leftJoin('i.CreUser ic'); $q->leftJoin('i.UpdUser iu'); $q->leftJoin('i.CacheUser cu'); $q->leftJoin('i.Locale l'); $q->leftJoin('i.InqOrg io'); $q->leftJoin('io.Organization o'); $q->leftJoin('i.ProjectInquiry pi'); $q->leftJoin('pi.Project p'); $q->leftJoin("i.Logo ilg WITH ilg.img_ref_type = ?", 'Q'); $q->leftJoin("io.OrgLogo iol WITH iol.img_ref_type = ?", 'L'); Inquiry::add_counts($q, 'i'); $q->addSelect('(inq_publish_dtim is not null) as ispub'); // status and type if (isset($args['status'])) { air2_query_in($q, $args['status'], 'i.inq_status'); } if (isset($args['type'])) { air2_query_in($q, $args['type'], 'i.inq_type'); } // only within a project if (isset($args['prj_uuid'])) { $q->addWhere('p.prj_uuid = ?', $args['prj_uuid']); } // exclude project if (isset($args['excl_prj'])) { $prjq = "select prj_id from project where prj_uuid = ?"; $excl = "select pinq_inq_id from project_inquiry where pinq_prj_id = ({$prjq})"; $q->addWhere("i.inq_id NOT IN ({$excl})", $args['excl_prj']); } // exclude an outcome if (isset($args['excl_out'])) { $outq = "select out_id from outcome where out_uuid = ?"; $excl = "select iout_inq_id from inq_outcome where iout_out_id = ({$outq})"; $q->addWhere("i.inq_id not in ({$excl})", $args['excl_out']); } // exclude an email if (isset($args['excl_eml'])) { $emlq = "select email_id from email where email_uuid = ?"; $excl = "select einq_inq_id from email_inquiry where einq_email_id = ({$emlq})"; $q->addWhere("i.inq_id not in ({$excl})", $args['excl_eml']); } return $q; }
/** * Query * * @param array $args * @return Doctrine_Query $q */ protected function air_query($args = array()) { $q = Doctrine_Query::create()->from('Project p'); $q->leftJoin('p.ProjectOrg po'); $q->leftJoin('po.Organization o'); $q->leftJoin('p.CreUser c'); $q->leftJoin('p.UpdUser u'); // status if (isset($args['status'])) { air2_query_in($q, $args['status'], 'p.prj_status'); } // with a certain member org if (isset($args['org_uuid'])) { $q->addWhere('o.org_uuid = ?', $args['org_uuid']); } // exclude an inquiry if (isset($args['excl_inq'])) { $inqq = "select inq_id from inquiry where inq_uuid = ?"; $excl = "select pinq_prj_id from project_inquiry where pinq_inq_id = ({$inqq})"; $q->addWhere("p.prj_id not in ({$excl})", $args['excl_inq']); } // exclude an organization if (isset($args['excl_org'])) { $orgq = "select org_id from organization where org_uuid = ?"; $excl = "select porg_prj_id from project_org where porg_org_id = ({$orgq})"; $q->addWhere("p.prj_id not in ({$excl})", $args['excl_org']); } // exclude an outcome if (isset($args['excl_out'])) { $outq = "select out_id from outcome where out_uuid = ?"; $excl = "select pout_prj_id from prj_outcome where pout_out_id = ({$outq})"; $q->addWhere("p.prj_id not in ({$excl})", $args['excl_out']); } // add query count $count = 'select count(*) from project_inquiry'; $q->addSelect("({$count} where pinq_prj_id = p.prj_id) as inquiry_count"); return $q; }
/** * Query * * @param array $args * @return Doctrine_Query $q */ protected function air_query($args = array()) { $q = Doctrine_Query::create()->from('Email e'); $q->leftJoin('e.Logo elg WITH elg.img_ref_type = ?', 'E'); $q->leftJoin('e.Organization org'); $q->leftJoin('e.UserSignature usig'); $q->leftJoin('e.CreUser cu'); $q->leftJoin('e.UpdUser uu'); $q->leftJoin('e.SrcExport ese'); // flatten a bit $q->addSelect('org.org_uuid as org_uuid'); $q->addSelect('org.org_name as org_name'); $q->addSelect('org.org_display_name as org_display_name'); $q->addSelect('usig.usig_uuid as usig_uuid'); $q->addSelect('usig.usig_text as usig_text'); $q->addSelect('cu.user_first_name as owner_first'); $q->addSelect('(select count(*) from email_inquiry where einq_email_id = e.email_id) as inq_count'); $q->addSelect('ese.se_cre_dtim as first_exported_dtim'); // status and type if (isset($args['status'])) { air2_query_in($q, $args['status'], 'e.email_status'); } if (isset($args['type'])) { air2_query_in($q, $args['type'], 'e.email_type'); } // stuff I own if (isset($args['mine']) && $args['mine'] && $args['mine'] !== 'false') { $q->addWhere('e.email_cre_user = ?', $this->user->user_id); } return $q; }
/** * Query * * @param array $args * @return Doctrine_Query $q */ protected function air_query($args = array()) { $q = Doctrine_Query::create()->from('User u'); $q->leftJoin('u.UserOrg uo WITH uo.uo_home_flag = true'); $q->leftJoin('uo.Organization o'); $q->leftJoin('u.UserEmailAddress e with e.uem_primary_flag = true'); $q->leftJoin('u.UserPhoneNumber p with p.uph_primary_flag = true'); $q->leftJoin("u.Avatar av WITH av.img_ref_type = ?", 'A'); $q->leftJoin('u.CreUser cu'); $q->leftJoin('u.UpdUser uu'); // flatten $q->addSelect('e.uem_address as uem_address'); $q->addSelect('p.uph_number as uph_number'); $q->addSelect('p.uph_ext as uph_ext'); $q->addSelect('uo.uo_user_title as uo_user_title'); $q->addSelect('o.org_uuid as org_uuid'); $q->addSelect('o.org_name as org_name'); $q->addSelect('o.org_display_name as org_display_name'); $q->addSelect('o.org_html_color as org_html_color'); // sort by some home_org first if (isset($args['sort_home'])) { $q->addSelect("(o.org_name = '{$args['sort_home']}') as myhome"); $q->addOrderBy('myhome desc'); } // restrict to some home_org if (isset($args['home_org'])) { $q->addWhere("o.org_name = '{$args['home_org']}'"); } // status and type if (isset($args['status'])) { air2_query_in($q, $args['status'], 'u.user_status'); } if (isset($args['type'])) { air2_query_in($q, $args['type'], 'u.user_type'); } // text filter $str = isset($args['filter']) ? $args['filter'] : false; if ($str && strlen($str) > 0) { $usrs = "u.user_username LIKE '{$str}%' OR u.user_first_name " . "LIKE '{$str}%' OR u.user_last_name LIKE '{$str}%'"; $orgs = "o.org_display_name LIKE '{$str}%' OR o.org_name LIKE '{$str}%'"; $titles = "uo.uo_user_title LIKE '{$str}%'"; $emails = "e.uem_address LIKE '{$str}%'"; $q->addWhere("(({$usrs}) OR ({$orgs}) OR ({$titles}) OR ({$emails}))"); } // exclude users belonging to an organization if (isset($args['excl_org'])) { $conn = AIR2_DBManager::get_connection(); $orgq = "select z.org_id from organization z where z.org_uuid = ?"; $excl = "select uo_user_id from user_org where uo_org_id = ({$orgq})"; $exclude = $conn->fetchColumn($excl, array($args['excl_org']), 0); if (count($exclude) > 0) { $q->whereNotIn('u.user_id', $exclude); } } // users that are eligible contacts for an organization if (isset($args['incl_contact_org'])) { $org = AIR2_Record::find('Organization', $args['incl_contact_org']); if (!$org) { throw new Rframe_Exception(Rframe::BAD_DATA, 'invalid incl_contact_org'); } $orgids = Organization::get_org_parents($org->org_id); $orgids[] = $org->org_id; // assemble query $orgids = implode(',', $orgids); $orgids = "uo_org_id in ({$orgids})"; $arids = "select ar_id from admin_role where ar_code in ('M','W')"; $arids = "uo_ar_id in ({$arids})"; $uids = "select uo_user_id from user_org where {$orgids} and {$arids}"; $q->addWhere("u.user_id in ({$uids})"); } return $q; }
/** * Query * * @param array $args * @return Doctrine_Query $q */ protected function air_query($args = array()) { $q = Doctrine_Query::create()->from('Organization o'); $q->leftJoin('o.DefaultProject def'); $q->leftJoin('o.ProjectOrg po'); $q->leftJoin('po.Project p'); $q->leftJoin('o.parent r'); $q->leftJoin("o.Logo ilg WITH ilg.img_ref_type = ?", 'L'); $q->leftJoin("o.Banner ibn WITH ibn.img_ref_type = ?", 'B'); $q->leftJoin('o.CreUser cu'); $q->leftJoin('o.UpdUser uu'); // process query args if (isset($args['excl_user'])) { $usrq = "select user_id from user where user_uuid = ?"; $excl = "select uo_org_id from user_org where uo_user_id = ({$usrq})"; $q->addWhere("o.org_id not in ({$excl})", $args['excl_user']); } if (isset($args['excl_proj'])) { $prjq = "select prj_id from project where prj_uuid = ?"; $excl = "select porg_org_id from project_org where porg_prj_id = ({$prjq})"; $q->addWhere("o.org_id NOT IN ({$excl})", $args['excl_proj']); } if (isset($args['excl_src'])) { $srcq = "select src_id from source where src_uuid = ?"; $excl = "select so_org_id from src_org where so_src_id = ({$srcq})"; $q->addWhere("o.org_id NOT IN ({$excl})", $args['excl_src']); } if (isset($args['excl_inq'])) { $srcq = "select inq_id from inquiry where inq_uuid = ?"; $excl = "select iorg_org_id from inq_org where iorg_inq_id = ({$srcq})"; $q->addWhere("o.org_id NOT IN ({$excl})", $args['excl_inq']); } if (isset($args['role']) && !$this->user->is_system()) { // look for valid role string $role = strtoupper($args['role']); if ($role == 'READER') { $role = 'R'; } elseif ($role == 'READERPLUS' || $role == 'E' || $role == 'EDITOR') { $role = 'P'; } elseif ($role == 'WRITER') { $role = 'W'; } elseif ($role == 'MANAGER') { $role = 'M'; } if (!isset(AdminRole::$CODES[$role])) { throw new Rframe_Exception(Rframe::BAD_DATA, "Invalid role specified: '{$role}'"); } // check for the specified role OR BETTER $role_or_better = array(); $roles_in_order = array('N', 'X', 'R', 'F', 'P', 'W', 'M'); foreach ($roles_in_order as $code) { if ($role == $code || count($role_or_better)) { if (defined("AIR2_AUTHZ_ROLE_{$code}")) { $role_or_better[] = constant("AIR2_AUTHZ_ROLE_{$code}"); } } } // check each org in user authz for the role bitmasks $role_or_better_orgs = array(0); //default - none $user_authz = $this->user->get_authz(); foreach ($user_authz as $orgid => $role) { foreach ($role_or_better as $mask) { if ($mask == $role) { $role_or_better_orgs[] = $orgid; } } } // add to where $q->andWhereIn("o.org_id", $role_or_better_orgs); } if (isset($args['status'])) { air2_query_in($q, $args['status'], 'o.org_status'); } if (isset($args['type'])) { air2_query_in($q, $args['type'], 'o.org_type'); } // add user_org count Organization::add_counts($q, 'o'); return $q; }
/** * Query * * @param array $args * @return Doctrine_Query $q */ protected function air_query($args = array(), $include_temp = false) { $q = Doctrine_Query::create()->from('Bin b'); $q->leftJoin('b.User u'); // source count $src_count = 'select count(*) from bin_source where bsrc_bin_id=b.bin_id'; $q->addSelect("({$src_count}) as src_count"); // submission count $subm_count = 'select count(*) from bin_src_response_set where bsrs_bin_id=b.bin_id'; $q->addSelect("({$subm_count}) as subm_count"); // is user the bin-owner? $my_id = $this->user->user_id; $q->addSelect("(b.bin_user_id={$my_id}) as owner_flag"); // skip the temp Bins (print only) if (!$include_temp) { $q->addWhere("b.bin_type not in ('T')"); } // query args if (isset($args['owner']) || isset($args['owner_flag'])) { $q->addWhere('b.bin_user_id = ?', $this->user->user_id); } if (isset($args['status'])) { air2_query_in($q, $args['status'], 'b.bin_status'); } if (isset($args['type'])) { air2_query_in($q, $args['type'], 'b.bin_type'); } return $q; }