static function getSectorNames()
{
$db = LsDb::getDbConnection();
$sql = "SELECT DISTINCT(sector_name) FROM os_category " . "WHERE sector_name NOT IN('" . join("', '", self::$ignoreSectors) . "') " . "ORDER BY sector_name ASC";
$stmt = $db->execute($sql);
return $stmt->fetchAll(PDO::FETCH_COLUMN);
}
public function executeCategory($request)
{
$this->checkCategory($request);
$this->sector = $this->category['sector_name'];
$this->industry = $this->category['industry_name'];
$this->type = $request->getParameter('type');
$page = $request->getParameter('page', 1);
$num = $request->getParameter('num', 50);
switch ($this->type) {
case 'people':
$q = LsDoctrineQuery::create()->from('Entity e')->leftJoin('e.OsEntityCategory ec')->leftJoin('ec.OsCategory c')->leftJoin('e.Person p')->where('e.primary_ext = ?', 'Person')->andWhere('c.category_id = ?', $this->category['category_id'])->orderBy('p.name_last ASC, p.name_first ASC')->setHydrationMode(Doctrine::HYDRATE_ARRAY);
$this->person_pager = new LsDoctrinePager($q, $page, $num);
break;
case 'orgs':
$q = LsDoctrineQuery::create()->from('Entity e')->leftJoin('e.OsEntityCategory ec')->leftJoin('ec.OsCategory c')->where('e.primary_ext = ?', 'Org')->andWhere('c.category_id = ?', $this->category['category_id'])->orderBy('e.name ASC')->setHydrationMode(Doctrine::HYDRATE_ARRAY);
$this->org_pager = new LsDoctrinePager($q, $page, $num);
break;
default:
$db = LsDb::getDbConnection();
$sql = "SELECT COUNT(DISTINCT(e.id)) FROM entity e " . "LEFT JOIN os_entity_category ec ON (ec.entity_id = e.id) " . "WHERE ec.category_id = ? AND e.primary_ext = ?";
$stmt = $db->execute($sql, array($this->category['category_id'], 'Person'));
$this->person_count = $stmt->fetch(PDO::FETCH_COLUMN);
$stmt = $db->execute($sql, array($this->category['category_id'], 'Org'));
$this->org_count = $stmt->fetch(PDO::FETCH_COLUMN);
break;
}
}
static function getMatchedDonations($id)
{
$db = LsDb::getDbConnection();
$sql = "SELECT d.* FROM littlesis_raw.os_donation d " . "LEFT JOIN littlesis.os_entity_transaction et ON (d.cycle = et.cycle AND d.row_id = et.transaction_id) " . "WHERE et.entity_id = ? AND et.is_verified = 1";
$params = array($id);
$stmt = $db->execute($sql, $params);
$donations = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $donations;
}
static function getCategories($id, $sort = false)
{
$db = LsDb::getDbConnection();
$sql = "SELECT c.* FROM os_entity_category ec " . "LEFT JOIN os_category c ON (ec.category_id = c.category_id) " . "WHERE ec.entity_id = ?";
if ($sort) {
$sql .= " ORDER BY c.category_name ASC";
}
$stmt = $db->execute($sql, array($id));
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
static function getByEntityId($id, $context = null)
{
$db = LsDb::getDbConnection();
$sql = "SELECT name FROM alias WHERE entity_id = ?";
$params = array($id);
if ($context) {
$sql .= " AND context = ?";
$params[] = $context;
} elseif ($context === false) {
$sql .= " AND context IS NULL";
}
$stmt = $db->execute($sql, $params);
return $stmt->fetchAll(PDO::FETCH_COLUMN);
}
static function addAlias($id, $alias)
{
$db = LsDb::getDbConnection();
$sql = "INSERT IGNORE INTO alias (entity_id, name, context, updated_at, created_at) VALUES (?, ?, ?, ?, ?)";
$now = LsDate::getCurrentDateTime();
return $stmt = $db->execute($sql, array($id, $alias, "opensecrets", $now, $now));
}
static function getName($id)
{
$db = LsDb::getDbConnection();
$sql = "SELECT name FROM entity WHERE id = ?";
$stmt = $db->execute($sql, array($id));
return $stmt->fetch(PDO::FETCH_COLUMN);
}
static function getPersonsWithPositions($id)
{
$db = LsDb::getDbConnection();
$sql = "SELECT e.* FROM entity e " . "LEFT JOIN link l ON (l.entity1_id = e.id) " . "WHERE l.entity2_id = ? AND l.is_reverse = 0 AND e.primary_ext = ? AND e.is_deleted = 0 " . "GROUP BY e.id";
$params = array($id, 'Person');
$stmt = $db->execute($sql, $params);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
protected function isSkippableEntity($id)
{
$db = LsDb::getDbConnection();
$sql = "SELECT definition_id FROM extension_record WHERE entity_id = ?";
$stmt = $db->execute($sql, array($id));
$extensionIds = $stmt->fetchAll(PDO::FETCH_COLUMN);
$typesToSkip = array(6, 11, 18, 19, 20, 26);
return count(array_intersect($extensionIds, $typesToSkip)) > 0;
}
