static function getRelationships($ids, $options)
{
$db = Doctrine_Manager::connection();
$sql = 'SELECT ' . LsApi::generateSelectQuery(array('r' => 'Relationship')) . ' FROM relationship r WHERE r.id IN (' . implode(',', $ids) . ')';
$stmt = $db->execute($sql);
if (@$options['details']) {
$rels = array();
if ($catId = @$options['cat_id']) {
//make Relationships accessible by id
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $rel) {
$rels[$rel['id']] = $rel;
}
//get category data
$categoryName = RelationshipCategoryTable::$categoryNames[$catId];
$db = Doctrine_Manager::connection();
$sql = 'SELECT * FROM ' . Doctrine_Inflector::tableize($categoryName) . ' WHERE relationship_id IN (' . implode(',', $ids) . ')';
$stmt = $db->execute($sql, array($id));
//merge category data with relationship data
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
$relId = $row['relationship_id'];
unset($row['id'], $row['relationship_id']);
$rels[$relId] = array_merge($rels[$relId], $row);
}
return array_values($rels);
} else {
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $rel) {
$rels[] = array_merge($rel, RelationshipApi::getDetails($rel['id'], $rel['category_id']));
}
return $rels;
}
} else {
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
}
static function getReferences($id)
{
$db = Doctrine_Manager::connection();
$select = LsApi::generateSelectQuery(array('r' => 'Reference'));
$sql = 'SELECT ' . $select . ' FROM reference r WHERE r.object_model = ? AND r.object_id = ?';
$stmt = $db->execute($sql, array('Relationship', $id));
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
public function getConnections($db, $num = 5, $primary_ext = null)
{
$params = array($this->id);
$select = LsApi::generateSelectQuery(array('e' => 'Entity')) . ', COUNT(l.id) AS num';
$from = 'link l LEFT JOIN entity e ON (l.entity2_id = e.id)';
$where = 'l.entity1_id = ?';
if ($primary_ext) {
$where .= ' AND e.primary_ext = ?';
array_push($params, $primary_ext);
}
$sql = 'SELECT ' . $select . ' FROM ' . $from . ' WHERE ' . $where . ' GROUP BY l.entity2_id ORDER BY num DESC LIMIT ' . $num;
$stmt = $db->execute($sql, $params);
$this->connections = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
public function executeEntities($request)
{
$options = $this->getParams(array('q', 'type_ids', 'num', 'page', 'list_ids', 'search_all'));
$result = SearchApi::getEntities($options);
$this->entities = array();
if ($result['total_found'] > 0 && isset($result['matches'])) {
$db = Doctrine_Manager::connection();
$ids = array_keys($result['matches']);
$tmp = array();
$select = LsApi::generateSelectQuery(array('e' => 'Entity'));
$from = 'entity e';
$where = 'e.id IN (' . implode(',', $ids) . ') AND e.is_deleted = 0';
$sql = 'SELECT ' . $select . ' FROM ' . $from . ' WHERE ' . $where . ' ORDER BY FIELD(e.id, ' . implode(',', $ids) . ')';
$stmt = $db->execute($sql);
$this->entities = $stmt->fetchAll(PDO::FETCH_ASSOC);
}
$this->getResponse()->setSlot('total', $result['total_found']);
return 'Xml';
}
static function getRelationships($id1, $id2, $options = array())
{
$db = Doctrine_Manager::connection();
$select = LsApi::generateSelectQuery(array('r' => 'Relationship'));
$from = 'link l LEFT JOIN relationship r ON (r.id = l.relationship_id)';
$where = 'l.entity1_id = ? AND l.entity2_id = ? AND r.is_deleted = 0';
$params = array($id1, $id2);
if ($catIds = @$options['cat_ids']) {
if (count(explode(',', $catIds)) == 1) {
$where .= ' AND l.category_id = ?';
$params[] = $catIds;
} else {
$where .= ' AND l.category_id IN (' . $catIds . ')';
}
}
$paging = LsApi::getPagingFromOptions($options, $defaultNum = 20, $maxNum = 100);
$sql = 'SELECT ' . $select . ' FROM ' . $from . ' WHERE ' . $where . ' ' . $paging;
$stmt = $db->execute($sql, $params);
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
static function getSecondDegreeNetwork($id, $options = array(), $countOnly = false)
{
$db = Doctrine_Manager::connection();
$select = LsApi::generateSelectQuery(array('e2' => 'Entity')) . ', GROUP_CONCAT(DISTINCT l.entity1_id) degree1_ids, COUNT(DISTINCT l.entity1_id) degree1_num, SUM(r.amount) degree1_total';
$from = 'ls_list_entity le ' . 'LEFT JOIN link l ON (l.entity1_id = le.entity_id) ' . 'LEFT JOIN relationship r ON (r.id = l.relationship_id) ' . 'LEFT JOIN entity e1 ON (e1.id = l.entity1_id) ' . 'LEFT JOIN entity e2 ON (e2.id = l.entity2_id)';
if ($order = @$options['order']) {
$isReverse = (int) ($order == 2);
$where = 'le.list_id = ? AND le.is_deleted = 0 AND l.is_reverse = ? AND e1.is_deleted = 0 AND e2.is_deleted = 0';
$params = array($id, $isReverse);
} else {
$where = 'le.list_id = ? AND le.is_deleted = 0 AND e1.is_deleted = 0 AND e2.is_deleted = 0';
$params = array($id);
}
if ($catIds = @$options['cat_ids']) {
if (count(explode(',', $catIds)) == 1) {
$where .= ' AND l.category_id = ?';
$params[] = $catIds;
} else {
$where .= ' AND l.category_id IN (' . $catIds . ')';
}
}
if ($degree1Type = @$options['degree1_type']) {
$where .= ' AND e1.primary_ext = ?';
$params[] = $degree1Type;
}
if ($degree2Type = @$options['degree2_type']) {
$where .= ' AND e2.primary_ext = ?';
$params[] = $degree2Type;
}
if ($countOnly) {
$sql = 'SELECT COUNT(DISTINCT l.entity2_id) FROM ' . $from . ' WHERE ' . $where;
} else {
$paging = LsApi::getPagingFromOptions($options, $defaultNum = 10, $maxNum = 20);
$sql = 'SELECT ' . $select . ' FROM ' . $from . ' WHERE ' . $where . ' GROUP BY e2.id ORDER BY ' . (@$options['sort'] == 'amount' ? 'degree1_total DESC, degree1_num DESC' : 'degree1_num DESC, degree1_total DESC') . ' ' . $paging;
}
$stmt = $db->execute($sql, $params);
if ($countOnly) {
return $stmt->fetchColumn();
}
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
static function getChildOrgs($id)
{
$db = Doctrine_Manager::connection();
$select = LsApi::generateSelectQuery(array('e' => 'Entity'));
$sql = 'SELECT ' . $select . ' FROM entity e WHERE e.parent_id = ?';
$stmt = $db->execute($sql, array($id));
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}