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