public function executeDetails($request)
{
$this->relationship = array_merge($this->relationship, RelationshipApi::getDetails($this->relationship['id'], $this->relationship['category_id']));
$this->entity1 = EntityApi::get($this->relationship['entity1_id']);
$this->entity2 = EntityApi::get($this->relationship['entity2_id']);
return 'Xml';
}
public function preExecute()
{
//setup response format
$this->setResponseFormat();
$this->entity = EntityApi::get($this->getRequest()->getParameter('id'));
$this->setLastModified($this->entity);
$this->checkExistence($this->entity, 'Entity');
}
static function getEntityPatterns($id)
{
//clear this entity
$patterns = array(self::getEntityPattern($id));
//also clear related entities
$db = Doctrine_Manager::connection();
$sql = 'SELECT entity1_id, entity2_id FROM relationship r WHERE (entity1_id = ? OR entity2_id = ?) AND r.is_deleted = 0';
$stmt = $db->execute($sql, array($id, $id));
foreach ($stmt->fetchAll(PDO::FETCH_ASSOC) as $row) {
$relatedId = $row['entity1_id'] == $id ? $row['entity2_id'] : $row['entity1_id'];
foreach (array('relationships', 'board', 'boards', 'related') as $part) {
$patterns[] = '/entity/' . $relatedId . '/' . $part . '*';
}
}
//also clear lists it's on
$sql = 'SELECT le.list_id FROM ls_list_entity le WHERE le.entity_id = ?';
$stmt = $db->execute($sql, array($id));
foreach ($stmt->fetchAll(PDO::FETCH_COLUMN) as $listId) {
$patterns[] = '/list/' . $listId . '/entities*';
}
//also clear parent entity, if any
$entity = EntityApi::get($id);
if ($parentId = $entity['parent_id']) {
$patterns[] = '/entity/' . $parentId . '/child-orgs*';
}
return $patterns;
}
public function executeFindConnections()
{
$request = $this->getRequest();
if ($id2 = $request->getParameter('id2')) {
if (!($this->entity2 = EntityApi::get($id2))) {
$this->forward404();
}
$page = $this->page;
$num = $request->getParameter('num', 10);
$options = array('cat_ids' => $request->getParameter('cat_ids', '1'));
//get all chains
$chains = SearchApi::getEntitiesChains($this->entity['id'], $id2, $options);
$offset = ($page - 1) * $num;
$flat_chains = array();
foreach ($chains as $degree => $ary) {
foreach ($ary as $ids) {
$flat_chains[] = $ids;
}
}
$page_chains = array_slice($flat_chains, $offset, $num);
$full_chains = array();
foreach ($page_chains as $ids) {
$full = array();
$chain = SearchApi::buildRelationshipChain($ids, explode(',', $options['cat_ids']));
foreach ($chain as $id => $rels) {
$entity = EntityApi::get($id);
$entity['Relationships'] = count($rels) ? BatchApi::getRelationships($rels, array()) : array();
$full[] = $entity;
}
$full_chains[] = $full;
}
// foreach ($page_chains as $degree => $ary)
// {
// foreach ($ary as $ids)
// {
// if ($count == $page)
// {
// $chain = SearchApi::buildRelationshipChain($ids, explode(',', $options['cat_ids']));
// break 2;
// }
// $count++;
// }
// }
// count total number of chains
// $total = 0;
// foreach ($chains as $degree => $ary)
// {
// $total += count($ary);
// }
if ($total = count($flat_chains)) {
$chainAry = array_fill(0, $total, null);
array_splice($chainAry, $offset, $num, $full_chains);
$this->chain_pager = new LsDoctrinePager($chainAry, $page, $num);
}
// get entities for chain
// if ($chain)
// {
// $this->entities = array();
// foreach ($chain as $id => $rels)
// {
// $entity = EntityApi::get($id);
// $entity['Relationships'] = count($rels) ? BatchApi::getRelationships($rels, array()) : array();
// $this->entities[] = $entity;
// }
// $chainAry = array_fill(0, $total, null);
// $chainAry[$page-1] = $this->entities;
// $this->chain_pager = new LsDoctrinePager($chainAry, $page, $num);
// }
} else {
//form submission, display matching persons
if ($request->hasParameter('q')) {
$num = $request->getParameter('num', 10);
$page = $request->getParameter('page', 1);
if (!($terms = $request->getParameter('q'))) {
$this->entity_pager = new LsDoctrinePager(array(), $page, $num);
} else {
switch (sfConfig::get('app_search_engine')) {
case 'sphinx':
$this->entity_pager = EntityTable::getSphinxPager($terms, $page, $num);
break;
case 'lucene':
$ary = EntityTable::getLuceneArray($terms, null);
$this->entity_pager = new LsDoctrinePager($ary, $page, $num);
break;
case 'mysql':
default:
$terms = explode(' ', $terms);
$q = EntityTable::getSimpleSearchQuery($terms);
$this->entity_pager = new Doctrine_Pager($q, $page, $num);
break;
}
}
}
}
}
public function executeEntitiesChains($request)
{
$options = $this->getParams(array('cat_ids', 'page'));
$id1 = $request->getParameter('id1');
$id2 = $request->getParameter('id2');
if (!isset($options['cat_ids'])) {
$options['cat_ids'] = '1';
}
$this->chains = SearchApi::getEntitiesChains($id1, $id2, $options);
$this->chain = null;
$page = isset($options['page']) ? $options['page'] : 1;
$count = 1;
foreach ($this->chains as $degree => $ary) {
foreach ($ary as $ids) {
if ($count == $page) {
$this->chain = SearchApi::buildRelationshipChain($ids, $options['cat_ids']);
break 2;
}
$count++;
}
}
if ($this->chain) {
$this->entities = array();
foreach ($this->chain as $id => $rels) {
$this->entities[$id] = EntityApi::get($id);
$this->entities[$id]['relationship_ids'] = implode(',', $rels);
}
}
return 'Xml';
}
public function executeToolbar($request)
{
$this->checkToolbarCredentials(true);
if ($request->isMethod('post')) {
//if user wants to skip this relationship
if ($request->getParameter('commit') == 'Skip') {
$names = $this->getUser()->getAttribute('toolbar_names');
array_shift($names);
if (count($names)) {
$this->getUser()->setAttribute('toolbar_names', $names);
$this->redirect('relationship/toolbar');
} else {
$entityId = $this->getUser()->getAttribute('toolbar_entity');
$entity = Doctrine::getTable('Entity')->find($entityId);
$this->getUser()->setAttribute('toolbar_names', null);
$this->getUser()->setAttribute('toolbar_ref', null);
$this->getUser()->setAttribute('toolbar_entity', null);
$this->getUser()->setAttribute('toolbar_defaults', null);
$this->redirect($entity->getInternalUrl());
}
}
//if user wants to clear bulk queue
if ($request->getParameter('commit') == 'Clear') {
$entityId = $this->getUser()->getAttribute('toolbar_entity');
$entity = Doctrine::getTable('Entity')->find($entityId);
$this->getUser()->setAttribute('toolbar_names', null);
$this->getUser()->setAttribute('toolbar_ref', null);
$this->getUser()->setAttribute('toolbar_entity', null);
$this->getUser()->setAttribute('toolbar_defaults', null);
$this->redirect($entity->getInternalUrl());
}
$entity1Id = $request->getParameter('entity1_id');
$entity2Id = $request->getParameter('entity2_id');
$categoryName = $request->getParameter('category_name');
$refSource = $request->getParameter('reference_source');
$refName = $request->getParameter('reference_name');
$categoryParams = $request->getParameter('relationship');
$startDate = $categoryParams['start_date'];
$endDate = $categoryParams['end_date'];
unset($categoryParams['start_date'], $categoryParams['end_date']);
if (!$entity1Id || !$entity2Id || !$categoryName || !$refSource || !$refName) {
$this->forward('error', 'invalid');
}
if (!($entity1 = EntityApi::get($entity1Id))) {
$this->forward('error', 'invalid');
}
if (!($entity2 = EntityApi::get($entity2Id))) {
$this->forward('error', 'invalid');
}
$db = Doctrine_Manager::connection();
$sql = 'SELECT name FROM relationship_category ' . 'WHERE (entity1_requirements IS NULL OR entity1_requirements = ?) ' . 'AND (entity2_requirements IS NULL OR entity2_requirements = ?)';
$stmt = $db->execute($sql, array($entity1['primary_ext'], $entity2['primary_ext']));
$validCategoryNames = $stmt->fetchAll(PDO::FETCH_COLUMN);
if (!in_array($categoryName, $validCategoryNames)) {
$request->setError('category', 'Invalid relationship; try changing the category or switching the entity order');
//check session for bulk names
if ($bulkEntityId = $this->getUser()->getAttribute('toolbar_entity')) {
if ($this->entity1 = Doctrine::getTable('Entity')->find($bulkEntityId)) {
if ($names = $this->getUser()->getAttribute('toolbar_names')) {
$this->entity2_name = array_shift($names);
if ($refId = $this->getUser()->getAttribute('toolbar_ref')) {
$this->ref = Doctrine::getTable('Reference')->find($refId);
$request->getParameterHolder()->set('title', $this->ref->name);
$request->getParameterHolder()->set('url', $this->ref->source);
}
if ($defaults = $this->getUser()->getAttribute('toolbar_defaults')) {
if (isset($defaults['category'])) {
$this->category = $defaults['category'];
}
}
}
}
}
if ($createdId = $request->getParameter('created_id')) {
$this->created_rel = Doctrine::getTable('Relationship')->find($createdId);
}
return sfView::SUCCESS;
}
if (!preg_match('/^http(s?)\\:\\/\\/.{3,193}/i', $refSource)) {
$this->forward('error', 'invalid');
}
//all's well, create relationship!
$rel = new Relationship();
$rel->setCategory($categoryName);
$rel->entity1_id = $entity1['id'];
$rel->entity2_id = $entity2['id'];
//only set dates if valid
if ($startDate && preg_match('#^\\d{4}-\\d{2}-\\d{2}$#', Dateable::convertForDb($startDate))) {
$rel->start_date = Dateable::convertForDb($startDate);
}
if ($endDate && preg_match('#^\\d{4}-\\d{2}-\\d{2}$#', Dateable::convertForDb($endDate))) {
$rel->end_date = Dateable::convertForDb($endDate);
}
$rel->fromArray($categoryParams, null, $hydrateCategory = true);
$rel->save();
//create reference
$ref = new Reference();
$ref->name = $refName;
$ref->source = $refSource;
$ref->object_id = $rel->id;
$ref->object_model = 'Relationship';
$ref->save();
$redirect = 'relationship/toolbar?url=' . $refSource . '&title=' . $refName . '&created_id=' . $rel->id;
//if there's a bulk queue, remove one from the start
if ($isBulk = $request->getParameter('is_bulk')) {
$names = $this->getUser()->getAttribute('toolbar_names');
array_shift($names);
if (count($names)) {
$this->getUser()->setAttribute('toolbar_names', $names);
//keep track of entity order while in queue
$this->getUser()->setAttribute('toolbar_switched', $request->getParameter('is_switched', 0));
$redirect = 'relationship/toolbar?created_id=' . $rel->id;
} else {
//queue is finished; go to entity profile
$entityId = $this->getUser()->getAttribute('toolbar_entity');
$entity = Doctrine::getTable('Entity')->find($entityId);
$redirect = $entity->getInternalUrl();
$this->getUser()->setAttribute('toolbar_names', null);
$this->getUser()->setAttribute('toolbar_ref', null);
$this->getUser()->setAttribute('toolbar_entity', null);
$this->getUser()->setAttribute('toolbar_defaults', null);
$this->getUser()->setAttribute('toolbar_switched', null);
}
}
LsCache::clearEntityCacheById($entity1['id']);
LsCache::clearEntityCacheById($entity2['id']);
$this->redirect($redirect);
}
//check session for bulk names
if ($bulkEntityId = $this->getUser()->getAttribute('toolbar_entity')) {
if ($this->entity1 = Doctrine::getTable('Entity')->find($bulkEntityId)) {
if ($names = $this->getUser()->getAttribute('toolbar_names')) {
$this->entity2_name = array_shift($names);
if ($refId = $this->getUser()->getAttribute('toolbar_ref')) {
$this->ref = Doctrine::getTable('Reference')->find($refId);
$request->getParameterHolder()->set('title', $this->ref->name);
$request->getParameterHolder()->set('url', $this->ref->source);
}
if ($defaults = $this->getUser()->getAttribute('toolbar_defaults')) {
if (isset($defaults['category'])) {
$this->category = $defaults['category'];
}
}
}
}
$this->is_switched = $this->getUser()->getAttribute('toolbar_switched', 0);
}
if ($createdId = $request->getParameter('created_id')) {
$this->created_rel = Doctrine::getTable('Relationship')->find($createdId);
}
$this->setLayout($bulkEntityId ? 'layout' : 'toolbar');
}
public function executeMatchDonations($request)
{
$this->checkEntity($request);
//only allowed for persons
$this->forward404Unless($this->entity['primary_ext'] == 'Person');
$userId = $this->getUser()->getGuardUser()->id;
//preprocess if requested
if ($request->getParameter('preprocess')) {
OsPreprocessMatchesTask::preprocessEntity($this->entity);
$databaseManager = sfContext::getInstance()->getDatabaseManager();
$db = $databaseManager->getDatabase('main');
$db = Doctrine_Manager::connection($db->getParameter('dsn'));
}
//only allowed for preprocessed entities
$db = Doctrine_Manager::connection();
$sql = 'SELECT COUNT(*) FROM os_entity_preprocess WHERE entity_id = ?';
$stmt = $db->execute($sql, array($this->entity['id']));
//$this->forward404Unless($stmt->fetch(PDO::FETCH_COLUMN));
$count = $stmt->fetch(PDO::FETCH_COLUMN);
if (!$count) {
OsEntityDonorTable::unlockEntityById($this->entity['id']);
}
$related = $this->getUser()->getAttribute('os_related_ids');
if (count($related)) {
if ($this->entity['id'] == $related[0]) {
$this->next_entity = Doctrine::getTable('Entity')->find($related[1]);
$this->remaining = count($related) - 1;
if ($request->isMethod('post')) {
array_shift($related);
$this->getUser()->setAttribute('os_related_ids', $related);
}
} else {
$this->next_entity = Doctrine::getTable('Entity')->find($related[0]);
$this->remaining = count($related);
}
} else {
//get another entity that needs matching
$where = 'et.reviewed_at IS NULL AND et.locked_at IS NULL AND e.id <> ? AND e.is_deleted = 0';
$params = array($this->entity['id']);
if ($skippedIds = $this->getUser()->getAttribute('os_skipped_ids')) {
$where .= ' AND e.id NOT IN (' . implode(',', array_fill(0, count($skippedIds), '?')) . ')';
$params = array_merge($params, $skippedIds);
}
$sql = 'SELECT e.* FROM os_entity_transaction et LEFT JOIN entity e ON (et.entity_id = e.id) WHERE ' . $where . ' LIMIT 100';
$stmt = $db->execute($sql, $params);
$nextEntities = $stmt->fetchAll(PDO::FETCH_ASSOC);
$this->next_entity = $nextEntities[rand(0, count($nextEntities) - 1)];
}
//check for lock
$sql = 'SELECT COUNT(*) FROM os_entity_transaction WHERE entity_id = ? AND locked_by_user_id <> ? AND locked_by_user_id IS NOT NULL';
$stmt = $db->execute($sql, array($this->entity['id'], $userId));
$lockCount = $stmt->fetch(PDO::FETCH_COLUMN);
if ($lockCount > 0) {
return 'Locked';
}
//get preprocessed transactions
$sql = "SELECT CONCAT(cycle, ':', transaction_id) trans FROM os_entity_transaction WHERE entity_id = ?";
$stmt = $db->execute($sql, array($this->entity['id']));
$trans = $stmt->fetchAll(PDO::FETCH_COLUMN);
//get verified transactions
$sql = "SELECT CONCAT(cycle, ':', transaction_id) FROM os_entity_transaction WHERE entity_id = ? AND is_verified = 1";
$stmt = $db->execute($sql, array($this->entity['id']));
$this->verified_trans = $stmt->fetchAll(PDO::FETCH_COLUMN);
if (count($trans)) {
//check if matches have been updated
$sql = "SELECT MAX(updated_at) FROM os_entity_preprocess WHERE entity_id = ?";
$stmt = $db->execute($sql, array($this->entity['id']));
$this->updated_at = $stmt->fetch(PDO::FETCH_COLUMN);
}
//verify and unverify transactions
if ($request->isMethod('post')) {
$commit = $request->getParameter('commit');
//user cancels matching
if ($commit == 'Cancel') {
//unlock
OsEntityTransactionTable::unlockEntityById($this->entity['id']);
$this->redirect(EntityTable::getInternalUrl($this->entity));
}
if ($nextEntityId = $request->getParameter('next_id')) {
$nextEntity = EntityApi::get($nextEntityId);
}
//user skips this entity but wants to match another
if ($nextEntity && $commit == 'Skip and Match Another') {
$skippedIds = $this->getUser()->getAttribute('os_skipped_ids', array());
if (!array_search($this->entity['id'], $skippedIds)) {
$skippedIds[] = $this->entity['id'];
}
$this->getUser()->setAttribute('os_skipped_ids', $skippedIds);
//unlock
OsEntityTransactionTable::unlockEntityById($this->entity['id']);
$this->redirect(EntityTable::getInternalUrl($nextEntity, 'matchDonations'));
}
if (!($submittedTrans = $request->getParameter('trans'))) {
$submittedTrans = array();
}
$newTrans = array_diff($submittedTrans, $this->verified_trans);
$oldTrans = array_diff($this->verified_trans, $submittedTrans);
if ($newTrans) {
//mark new donor ids as verified
$sql = 'UPDATE os_entity_transaction SET is_verified = 1, is_synced = (is_verified = is_processed), reviewed_at = ?, reviewed_by_user_id = ? WHERE entity_id = ? AND (' . OsDonationTable::generateKeyClause('cycle', 'transaction_id', $newTrans) . ')';
$stmt = $db->execute($sql, array(date('Y-m-d H:i:s'), $this->getUser()->getGuardUser()->id, $this->entity['id']));
}
if ($oldTrans) {
//mark old donor ids as unverified
$sql = 'UPDATE os_entity_transaction SET is_verified = 0, is_synced = (is_verified = is_processed), reviewed_at = ?, reviewed_by_user_id = ? WHERE entity_id = ? AND (' . OsDonationTable::generateKeyClause('cycle', 'transaction_id', $oldTrans) . ')';
$stmt = $db->execute($sql, array(date('Y-m-d H:i:s'), $this->getUser()->getGuardUser()->id, $this->entity['id']));
}
//mark all donor ids as reviewed
$sql = 'UPDATE os_entity_transaction SET reviewed_at = ?, reviewed_by_user_id = ? WHERE entity_id = ?';
$stmt = $db->execute($sql, array(date('Y-m-d H:i:s'), $this->getUser()->getGuardUser()->id, $this->entity['id']));
//clear preprocess updated_at fields because the donations have now been verified by a user
if (isset($this->updated_at) && $this->updated_at) {
$sql = "UPDATE os_entity_preprocess SET updated_at = NULL WHERE entity_id = ?";
$stmt = $db->execute($sql, array($this->entity['id']));
}
//unlock
OsEntityTransactionTable::unlockEntityById($this->entity['id']);
//user submits matches and wants to match another entity
if ($nextEntity && $commit == 'Verify and Match Another') {
$this->redirect(EntityTable::getInternalUrl($nextEntity, 'matchDonations'));
}
$this->redirect(EntityTable::getInternalUrl($this->entity));
}
//lock page
OsEntityTransactionTable::lockEntityById($this->entity['id'], $userId);
//get user who last reviewed, if any
$this->reviewed_by_user = null;
$this->reviewed_at = null;
$sql = 'SELECT p.public_name, et.reviewed_at FROM os_entity_transaction et ' . 'LEFT JOIN sf_guard_user_profile p ON (et.reviewed_by_user_id = p.user_id) ' . 'WHERE et.entity_id = ? LIMIT 1';
$stmt = $db->execute($sql, array($this->entity['id']));
if ($row = $stmt->fetch(PDO::FETCH_NUM)) {
list($this->reviewed_by_user, $this->reviewed_at) = $row;
}
//get related entities
$options = array('cat_ids' => '1,3');
$this->related_entities = EntityApi::getRelated($this->entity['id'], $options);
//get addresses
$this->addresses = LsDoctrineQuery::create()->from('Address a')->leftJoin('a.State s')->where('a.entity_id = ?', $this->entity['id'])->andWhere('a.is_deleted = 0')->orderBy('s.abbreviation, a.city, a.postal')->execute();
$databaseManager = sfContext::getInstance()->getDatabaseManager();
if (count($trans)) {
//get all transaction records for the given donor ids
$rawDb = $databaseManager->getDatabase('raw');
$rawDb = Doctrine_Manager::connection($rawDb->getParameter('dsn'));
$sql = 'SELECT * FROM os_donation FORCE INDEX(PRIMARY) WHERE ' . OsDonationTable::generateKeyClause('cycle', 'row_id', $trans);
$stmt = $rawDb->execute($sql);
$donations = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
$donations = array();
}
$this->donors = array();
foreach ($donations as $row) {
//clean donor IDs so they don't break HTML ids and classes in the view
$donorId = str_replace(' ', '_', $row['donor_id']);
$donorId = str_replace('@', '-', $donorId);
//generate employer_raw from occupation and employer fields if null
if (!$row['employer_raw'] && $row['title_raw']) {
$row['employer_raw'] = trim($row['org_raw'] . "/" . $row['title_raw'], "/");
}
if (isset($this->donors[$donorId])) {
$this->donors[$donorId]['names'][$row['donor_name']] = true;
$this->donors[$donorId]['cycles'][$row['cycle']] = true;
$this->donors[$donorId]['orgs'][$row['employer_raw']] = true;
$this->donors[$donorId]['addresses'][OsDonationTable::buildAddress($row)] = true;
$this->donors[$donorId]['image_ids'][$row['fec_id']] = $row['cycle'];
if ($row['donor_name_middle']) {
$this->donors[$donorId]['middles'][$row['donor_name_middle']] = true;
}
$this->donors[$donorId]['donations'][] = $row;
} else {
$this->donors[$donorId] = array('names' => array($row['donor_name'] => true), 'middles' => $row['donor_name_middle'] ? array($row['donor_name_middle'] => true) : array(), 'cycles' => array($row['cycle'] => true), 'orgs' => array($row['employer_raw'] => true), 'addresses' => array(OsDonationTable::buildAddress($row) => true), 'image_ids' => array($row['fec_id'] => $row['cycle']), 'donations' => array($row));
}
}
$db = $databaseManager->getDatabase('main');
Doctrine_Manager::connection($db->getParameter('dsn'));
}
