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'));
}
protected function getDonations(array $trans)
{
if (!count($trans)) {
return array();
}
$sql = 'SELECT * FROM os_donation FORCE INDEX(PRIMARY) WHERE (' . OsDonationTable::generateKeyClause('cycle', 'row_id', $trans) . ') AND recipient_id IS NOT NULL AND transaction_type <> ?';
if (!($stmt = $this->rawDb->execute($sql, array('22y')))) {
throw new Exception("Couldn't get donations for " . implode(', ', $trans));
}
return $stmt->fetchAll(PDO::FETCH_ASSOC);
}
?>
</a>
<?php
}
?>
</td>
<td style="font-size: 9px;"><?php
echo $donation['donor_name'];
?>
</td>
<td style="font-size: 9px;"><?php
echo $donation['employer_raw'];
?>
</td>
<td style="font-size: 9px;"><?php
echo OsDonationTable::buildAddress($donation);
?>
</td>
<td style="font-size: 9px;">
<?php
if ($donation['fec_id']) {
?>
<?php
echo link_to($donation['fec_id'], 'http://images.nictusa.com/cgi-bin/fecimg/?' . $donation['fec_id'], 'target=_new');
?>
('<?php
echo substr($donation['cycle'], -2);
?>
)
<?php
}
