protected function convertRelationship($id, $oldEntity2Id, $newEntity2Id) { $now = LsDate::getCurrentDateTime(); //update relationship $sql = 'UPDATE relationship r SET entity2_id = ?, last_user_id = ?, updated_at = ? WHERE id = ?'; if (!$this->testMode) { $stmt = $this->db->execute($sql, array($newEntity2Id, 1, $now, $id)); if (!$stmt->rowCount()) { throw new Exception("Couldn't update relationship " . $id); } } //create modification record $sql = 'INSERT INTO modification (object_model, object_id, object_name, created_at, updated_at) ' . 'VALUES (?, ?, ?, ?, ?)'; if (!$this->testMode) { $stmt = $this->db->execute($sql, array('Relationship', $id, 'Relationship ' . $id, $now, $now)); if (!$stmt->rowCount()) { throw new Exception("Couldn't insert modification record for relationship " . $id); } } //create modification_field record $modId = $this->db->lastInsertId('modification'); $sql = 'INSERT INTO modification_field (modification_id, field_name, old_value, new_value) VALUES (?, ?, ?, ?)'; if (!$this->testMode) { $stmt = $this->db->execute($sql, array($modId, 'entity2_id', $oldEntity2Id, $newEntity2Id)); if (!$stmt->rowCount()) { throw new Exception("Couldn't insert modification_field record for relationship " . $id); } } if ($this->debugMode) { print "Converted relationship " . $id . "\n"; } }
public function save(Doctrine_Connection $conn = null, $updateList = true, $updateEntity = true) { if ($conn == null) { $conn = $this->_table->getConnection(); } try { $conn->beginTransaction(); $ret = parent::save($conn); if ($updateList) { $this->LsList->last_user_id = LsVersionableListener::getUserId(); $this->LsList->updated_at = LsDate::getCurrentDateTime(); $this->LsList->save(); } if ($updateEntity) { $this->Entity->last_user_id = LsVersionableListener::getUserId(); $this->Entity->updated_at = LsDate::getCurrentDateTime(); $this->Entity->save(); } $conn->commit(); } catch (Exception $e) { $conn->rollback(); throw $e; } return $ret; }
static function addCategory($id, $categoryId, $source = null) { $db = LsDb::getDbConnection(); $sql = "INSERT INTO os_entity_category (entity_id, category_id, source, created_at, updated_at) " . "VALUES (?, ?, ?, ?, ?) " . "ON DUPLICATE KEY UPDATE updated_at = ?"; $now = LsDate::getCurrentDateTime(); return $stmt = $db->execute($sql, array($id, $categoryId, $source, $now, $now, $now)); }
static function convertForDisplay($date, $separator = '-', $abbreviate = false) { if ($date === null) { return null; } else { if (!LsDate::validate($date)) { throw new Exception("Can't convert: invalid date"); } } list($year, $month, $day) = explode('-', $date); if (intval($year) < 1930) { $abbreviate = false; } if (intval($year) > 0 && intval($month) > 0 && intval($day) > 0) { $ret = date('M j \'y', strtotime($date)); } elseif (intval($year) > 0 && intval($month) > 0) { //the month needs to be incremented because strtotime interprets '2008-12-00' as '2008-11-31' $year = $month > 10 ? $year + 1 : $year; $month = ($month + 1) % 12; $ret = date('M \'y', strtotime(implode('-', array($year, $month, $day)))); } elseif ($year) { if ($abbreviate) { $ret = '\'' . substr($year, 2, 2); } else { $ret = $year; } } else { $ret = null; } return $ret; }
public function executeMatch($request) { if ($request->isMethod('post')) { $this->checkArticle($request); //get verified entity ids $oldEntities = LsDoctrineQuery::create()->select('ae.entity_id')->from('ArticleEntity ae')->leftJoin('ae.Entity e')->where('ae.article_id = ?', $this->article->id)->andWhere('ae.is_verified = 1')->andWhere('e.is_deleted = 0')->fetchAll(PDO::FETCH_COLUMN); //get all submitted entity ids $newEntities = array_unique($request->getParameter('entity_ids', array())); //compute changes $verifiedEntities = array_diff($newEntities, $oldEntities); $unverifiedEntities = array_diff($oldEntities, $newEntities); //get user and time $userId = $this->getUser()->getGuardUser()->id; $time = LsDate::getCurrentDateTime(); //save changes LsDoctrineQuery::create()->update('ArticleEntity')->set('is_verified', '?', true)->set('reviewed_by_user_id', $userId)->set('reviewed_at', '?', array($time))->where('article_id = ?', $this->article->id)->andWhereIn('entity_id', $verifiedEntities)->execute(); LsDoctrineQuery::create()->update('ArticleEntity')->set('is_verified', '?', false)->set('reviewed_by_user_id', $userId)->set('reviewed_at', '?', array($time))->where('article_id = ?', $this->article->id)->andWhereIn('entity_id', $unverifiedEntities)->execute(); $this->article->description = $request->getParameter('description'); $this->article->reviewed_by_user_id = $userId; $this->article->reviewed_at = $time; $this->article->save(); $this->redirect('article/admin'); } }
static function compare(LsDate $date1, LsDate $date2) { //if exactly one of the dates are blank if ($date1->getYear() == '0000' xor $date2->getYear() == '0000') { return self::COMPARE_UNKNOWN; } if ($date1->_year > $date2->_year) { return self::COMPARE_AFTER; } elseif ($date1->_year < $date2->_year) { return self::COMPARE_BEFORE; } else { //years are equal, gotta look closer if ($date1->_month > $date2->_month && $date2->_month) { //first month is greater than nonzero second month return self::COMPARE_AFTER; } elseif ($date1->_month < $date2->_month && $date1->_month) { //second month is greater than nonzero first month return self::COMPARE_BEFORE; } elseif ($date1->_month == $date2->_month) { //months are equal, gotta look closer if ($date1->_day > $date2->_day && $date2->_day) { //first day is greater than nonzero second day return self::COMPARE_AFTER; } elseif ($date1->_day < $date2->_day && $date1->_day) { //second day is greater than nonzero first day return self::COMPARE_BEFORE; } elseif ($date1->_day == $date2->_day) { //same dates! return self::COMPARE_SAME; } else { //days are unequal but one of them is zero return self::COMPARE_UNKNOWN; } } else { //months are unequal but one of them is zero return self::COMPARE_UNKNOWN; } } }
function import(Entity $person, $possible_persons) { //loop through the people we found. usually just one. foreach ($possible_persons as $possible_person) { $this->printDebug('Query returned ' . count($possible_person) . ' person named ' . $possible_person->name); //this person does not provide education. we skip if (count($possible_person->education)) { $this->printDebug('Education found'); } else { $this->printDebug('No education history found'); continue; } //get employement info for this possible match $possible_person_bio = $possible_person->summary; if (count($possible_person->employment_history)) { foreach ($possible_person->employment_history as $employment) { $possible_person_bio .= ' ' . $employment->company . " "; } $this->printDebug('Employment found'); } else { $this->printDebug('No employment history found'); continue; } //get employment info for the person in our database $relationship_orgs = $person->getRelatedEntitiesQuery('Org', RelationshipTable::POSITION_CATEGORY, null, null, null, false, 1)->execute(); $person_bio = $person->summary; foreach ($relationship_orgs as $org) { $person_bio .= ' ' . $org->name; } //lets see how many matches we get $matches = LsLanguage::getCommonPronouns($person_bio, trim($possible_person_bio), LsLanguage::$business); if (count($matches)) { foreach ($possible_person->education as $school) { $school->institution = mb_convert_encoding($school->institution, 'UTF-8'); $school->institution = preg_replace('//isu', ' ', $school->institution); $this->printDebug('Looking for the school: ' . $school->institution); $current_school = EntityTable::findByAlias($school->institution, $context = 'bw_school'); //find school if ($current_school) { $this->printDebug('Found school'); } else { $current_school = EntityTable::getByExtensionQuery(array('Org', 'School'))->addWhere('LOWER(org.name) LIKE ?', '%' . strtolower($school->institution) . "%")->fetchOne(); if (!$current_school) { $new_school = new Entity(); $new_school->addExtension('Org'); $new_school->addExtension('School'); $new_school->name = $school->institution; $wikipedia = new LsWikipedia(); $wikipedia->request($school->institution); if ($wikipedia->execute() && !$wikipedia->isDisambiguation()) { $info_box = $wikipedia->getInfoBox(); if (isset($info_box['students']) && preg_match('/([\\d\\,]{2,})/isu', $info_box['students']['clean'], $match)) { $new_school->students = LsNumber::clean($match[1]); } else { $student_types = array('undergrad', 'postgrad', 'grad', 'doctoral'); $num_students = 0; foreach ($student_types as $st) { if (isset($info_box[$st]) && preg_match('/([\\d\\,]{2,})/isu', $info_box[$st]['clean'], $match)) { $num_students += LsNumber::clean($match[1]); } } if ($num_students > 0) { $new_school->students = $num_students; } } if (isset($info_box['faculty']) && preg_match('/([\\d\\,]{2,})/isu', $info_box['faculty']['clean'], $match)) { $new_school->faculty = LsNumber::clean($match[1]); } if (isset($info_box['type'])) { if (stristr($info_box['type']['clean'], 'public')) { $new_school->is_private = 0; } else { if (stristr($info_box['type']['clean'], 'private')) { $new_school->is_private = 1; } } } if (isset($info_box['endowment'])) { if (preg_match('/(\\$[\\d\\,\\.\\s]+)(million|billion)/isu', $info_box['endowment']['clean'], $match)) { if (strtolower($match[2]) == 'billion') { $factor = 1000000000; } else { $factor = 1000000; } $new_school->endowment = LsNumber::formatDollarAmountAsNumber($match[1], $factor); } } if (isset($info_box['established'])) { $year = null; if ($date = LsDate::convertDate($info_box['established']['clean'])) { $new_school->start_date = $date; } else { if (preg_match('/\\b(\\d\\d\\d\\d)\\b/isu', $info_box['established']['clean'], $match)) { $new_school->start_date = $match[1]; } } } $summary = trim($wikipedia->getIntroduction()); $summary = preg_replace('/\\n\\s*\\n/isu', '', $summary); if (strlen($summary) > 10) { $new_school->summary = $summary; } $new_school->save(); $new_school->addReference($source = $wikipedia->getUrl(), $excerpt = null, $fields = array('summary'), $name = 'Wikipedia'); } else { $new_school->save(); } $current_school = $new_school; $this->printDebug('Adding new school'); } $alias = new Alias(); $alias->name = $school->institution; $alias->context = 'bw_school'; $alias->Entity = $current_school; $alias->save(); } //find degree $degree = null; if (!($degree = DegreeTable::getByText($school->degree))) { $degree = DegreeTable::addDegree($school->degree); $this->printDebug('Adding new degree'); } //find relationship $relationship = null; $relationships = $person->getRelationshipsWithQuery($current_school, RelationshipTable::EDUCATION_CATEGORY)->execute(); foreach ($relationships as $existing_relationship) { if ($existing_relationship->degree_id == $degree->id) { $relationship = $existing_relationship; break; } } if ($relationship) { $this->printDebug('Relationship between person and school exists'); } else { $relationship = new Relationship(); $relationship->Entity1 = $person; $relationship->Entity2 = $current_school; $relationship->description1 = 'student'; $relationship->is_current = 0; if ($school->year) { $relationship->end_date = $school->year; } $relationship->setCategory('Education'); $this->printDebug('Creating new relationship between person and school'); } //save $relationship->save(); //add degree and reference if ($relationship->degree_id == null) { $reference_name = strstr($school->source, 'wikipedia') ? "Wikipedia" : "BusinessWeek"; $relationship->Degree = $degree; $relationship->save(); $relationship->addReference($source = $school->source, $excerpt = null, $fields = array('degree_id'), $name = $reference_name, $detail = null, $date = null); $this->printDebug('Adding degree and reference'); } } } else { $this->printDebug('No organization matches'); return false; } } return true; }
static function mergeField($fieldName, $existingValue, $newValue) { if ($newValue === null) { return $existingValue; } elseif ($existingValue === null) { return $newValue; } elseif ($existingValue === $newValue) { return $existingValue; } $appendWithNewlineFields = array('summary', 'notes'); $appendWithSpaceFields = array('name_suffix', 'name_prefix'); $dateFields = array('start_date', 'end_date'); $nameFields = array('name_first', 'name_middle'); $numberFields = array('employees', 'revenue', 'endowment'); if (in_array($fieldName, $appendWithNewlineFields)) { return $existingValue . "\n\n" . $newValue; } elseif (in_array($fieldName, $appendWithSpaceFields)) { return $existingValue . ' ' . $newValue; } elseif (in_array($fieldName, $dateFields)) { $existingDate = new LsDate($existingValue); $newDate = new LsDate($newValue); if ($existingDate->howSpecific() > $newDate->howSpecific()) { return $existingDate->format(); } elseif ($existingDate->howSpecific() < $newDate->howSpecific()) { return $newDate->format(); } else { if ($existingDate->howSpecific() == LsDate::YEAR_SPECIFIC) { return LsDate::lessOrEqual($existingDate, $newDate) ? $existingDate : $newDate; } else { return $existingDate; } } } elseif (in_array($fieldName, $nameFields)) { return strlen($existingValue) >= strlen($newValue) || strlen($existingValue) !== 1 ? $existingValue : $newValue; } elseif (in_array($fieldName, $numberFields)) { return $existingValue == 0 ? $newValue : $existingValue; } else { return $existingValue; } }
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)); }
public function updateDateRange($date, $use_year = true) { $date = new LsDate($date); if ($use_year) { $date->setDay('00'); $date->setMonth('00'); } if ($date->getYear() == '0000') { return false; } //start date and end date both null, make start date the date if (!$this->end_date && !$this->start_date) { $this->start_date = (string) $date; $this->end_date = (string) $date; return true; } $end_date = new LsDate($this->end_date); $start_date = new LsDate($this->start_date); $start_date_comp = LsDate::compare($date, $start_date); $end_date_comp = LsDate::compare($date, $end_date); //if date is the same as either start date or end date, make no changes if ($start_date_comp == LsDate::COMPARE_SAME || $end_date_comp === LsDate::COMPARE_SAME) { return false; } else { if ($start_date_comp == LsDate::COMPARE_BEFORE) { $this->start_date = (string) $date; return true; } else { if ($end_date_comp == LsDate::COMPARE_AFTER) { $this->end_date = (string) $date; return true; } } } //no changes have been made return false; }
static function logUpdate($id) { $db = LsDb::getDbConnection(); $now = LsDate::getCurrentDateTime(); $sql = "INSERT INTO task_meta (task, namespace, predicate, value, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?) " . "ON DUPLICATE KEY UPDATE value = ?"; $params = array(__CLASS__, $id, self::$logKey, $now, $now, $now, $now); return $stmt = $db->execute($sql, $params); }
public function processEntity($id, $newTrans, $oldTrans) { //get person names so we can make sure added donations are from the right person $sql = 'SELECT * FROM person WHERE entity_id = ?'; $stmt = $this->db->execute($sql, array($id)); if (!($donorPerson = $stmt->fetch(PDO::FETCH_ASSOC))) { if ($this->debugMode) { print "* Can't find Person record for donor with entity_id " . $id . "; skipping..."; } return; } if ($this->debugMode) { print "\n=== Processing entity " . $id . " (" . PersonTable::getLegalName($donorPerson) . ") ===\n"; } $recipients = array(); //get donations from all the newly matched transactions $newDonations = $this->getDonations($newTrans); foreach ($newDonations as $donation) { if (!$this->namesAreCompatible($donorPerson, $donation)) { if ($this->debugMode) { print "* Skipping donation with incompatible donor name: " . $donation['donor_name'] . "\n"; } continue; } $cycle = $donation['cycle']; $recipientId = $donation['recipient_id']; if (isset($recipients[$cycle][$recipientId]['new'])) { $recipients[$cycle][$recipientId]['new'][] = $donation; } else { if (!isset($recipients[$cycle])) { $recipients[$cycle] = array(); } $recipients[$cycle][$recipientId] = array(); $recipients[$cycle][$recipientId]['new'] = array($donation); $recipients[$cycle][$recipientId]['old'] = array(); } } //get donations from all the old transactions $oldDonations = $this->getDonations($oldTrans); foreach ($oldDonations as $donation) { $cycle = $donation['cycle']; $recipientId = $donation['recipient_id']; if (isset($recipients[$cycle][$recipientId]['old'])) { $recipients[$cycle][$recipientId]['old'][] = $donation; } else { if (!isset($recipients[$cycle])) { $recipients[$cycle] = array(); } $recipients[$cycle][$recipientId] = array(); $recipients[$cycle][$recipientId]['old'] = array($donation); $recipients[$cycle][$recipientId]['new'] = array(); } } //if there are NO already-processed matches, and no matches to remove, //ie, if we're going from no matches to any number of matches, //we can delete existing donation relationships for this entity $deleteRels = false; if (!count($oldDonations)) { $sql = 'SELECT COUNT(*) FROM os_entity_transaction WHERE entity_id = ? AND is_processed = 1'; $stmt = $this->db->execute($sql, array($id)); if (!$stmt->fetch(PDO::FETCH_COLUMN)) { $deleteRels = true; } } if ($deleteRels) { if ($this->debugMode) { print "- Removing old donation relationships...\n"; } //first get ids $sql = 'SELECT DISTINCT r.id FROM relationship r ' . 'LEFT JOIN fec_filing f ON (f.relationship_id = r.id) ' . 'WHERE r.entity1_id = ? AND r.category_id = ? AND r.is_deleted = 0 ' . 'AND f.id IS NOT NULL'; $stmt = $this->db->execute($sql, array($id, RelationshipTable::DONATION_CATEGORY)); $relIds = $stmt->fetchAll(PDO::FETCH_COLUMN); if (count($relIds)) { //soft delete them $sql = 'UPDATE relationship SET is_deleted = 1, updated_at = ? WHERE id IN (' . implode(',', $relIds) . ')'; $params = array(LsDate::getCurrentDateTime()); $this->db->execute($sql, $params); //create modification records of the deletions $sql = 'INSERT INTO modification (object_model, object_id, object_name, is_delete, created_at, updated_at) ' . 'VALUES '; $params = array(); foreach ($relIds as $relId) { $sql .= '(?, ?, ?, ?, ?, ?), '; $now = LsDate::getCurrentDateTime(); $params = array_merge($params, array('Relationship', $relId, 'Relationship ' . $relId, true, $now, $now)); } $sql = substr($sql, 0, strlen($sql) - 2); $stmt = $this->db->execute($sql, $params); } } //make sure the entity hasn't been deleted in the meantime! $sql = 'SELECT id FROM entity WHERE id = ? AND is_deleted = 0'; $stmt = $this->db->execute($sql, array($id)); if (!$stmt->fetch(PDO::FETCH_COLUMN)) { //skip to the end $recipients = array(); } //create filings/relationships for each cycle-recipient pair foreach ($recipients as $cycle => $recipients) { foreach ($recipients as $recipientId => $donations) { //if it's a committee recipient, try to determine //whether it belongs to a candidate if (strpos($recipientId, 'C') === 0) { $recipientId = $this->getFinalRecipientIdByCycleAndCommitteeId($cycle, $recipientId); } //find the entity with this recipient id, or generate a new one if (!($recipientEntity = $this->getEntityByRecipientId($recipientId))) { if ($this->debugMode) { print "* Couldn't find or create entity for recipient " . $recipientId . "; skipping...\n"; } continue; } //create committee entity and position relationship between it and the candidate, if necessary //DISABLED, FOR NOW //$this->createCampaignCommittee($recipientEntity['id'], $recipientId); if ($this->debugMode) { print "Updating donation relationship with " . $recipientEntity['name'] . "...\n"; } //see if there's already a relationship Doctrine_Manager::getInstance()->setCurrentConnection('main'); $q = LsDoctrineQuery::create()->from('Relationship r')->where('r.entity1_id = ? AND r.entity2_id = ? AND r.category_id = ?', array($id, $recipientEntity['id'], RelationshipTable::DONATION_CATEGORY)); $rel = $q->fetchOne(); //create relationship if there's not already one if (!$rel) { //but if there aren't any new donations, then we skip this recipient //THIS SHOULD NOT TYPICALLY HAPPEN, BECAUSE NO NEW DONATIONS MEANS //THERE ARE OLD DONATIONS TO REMOVE, WHICH MEANS THERE SHOULD BE //EXISTING RELATIONSHIPS... they may have been deleted if (!count($donations['new'])) { if ($this->debugMode) { print "* No relationships found, and no new donations to process, so skipping it...\n"; } continue; } if ($this->debugMode) { print "+ Creating new donation relationship\n"; } $rel = new Relationship(); $rel->entity1_id = $id; $rel->entity2_id = $recipientEntity['id']; $rel->setCategory('Donation'); $rel->description1 = 'Campaign Contribution'; $rel->description2 = 'Campaign Contribution'; $rel->save(); } //add new filings and references to the relationship foreach ($donations['new'] as $donation) { $filing = new FecFiling(); $filing->relationship_id = $rel->id; $filing->amount = $donation['amount']; $filing->fec_filing_id = $donation['fec_id']; $filing->crp_cycle = $donation['cycle']; $filing->crp_id = $donation['row_id']; $filing->start_date = $donation['date']; $filing->end_date = $donation['date']; $filing->is_current = false; $filing->save(); if ($this->debugMode) { print "+ Added new FEC filing: " . $donation['fec_id'] . " (" . $donation['amount'] . ")\n"; } //add reference if there's an fec_id if ($donation['fec_id']) { $ref = new Reference(); $ref->object_model = 'Relationship'; $ref->object_id = $rel->id; $ref->source = $this->fecImageBaseUrl . $donation['fec_id']; $ref->name = 'FEC Filing ' . $donation['fec_id']; $ref->save(); } } //remove old filings from the relationship foreach ($donations['old'] as $donation) { if ($this->debugMode) { print "- Deleting FEC filing: {$donation['fec_id']}, {$donation['cycle']}, {$donation['row_id']} ({$donation['amount']})\n"; } $sql = 'DELETE FROM fec_filing WHERE relationship_id = ? AND crp_cycle = ? AND crp_id = ?'; $stmt = $this->db->execute($sql, array($rel->id, $donation['cycle'], $donation['row_id'])); } //recompute fields based on filings if (!$rel->updateFromFecFilings()) { if ($this->debugMode) { print "- Deleting donation relationship with no filings: " . $rel->id . "\n"; } //no remaining filings for this relationship, so delete it! $rel->delete(); } else { if ($this->debugMode) { print "Relationship " . $rel->id . " updated with " . $rel->filings . " filings totaling " . $rel->amount . "\n"; } //add a reference to OS donation search for the relationship, if necessary $sql = 'SELECT COUNT(*) FROM reference ' . 'WHERE object_model = ? AND object_id = ? AND name = ?'; $stmt = $this->db->execute($sql, array('Relationship', $rel->id, 'FEC contribution search')); if (!$stmt->fetch(PDO::FETCH_COLUMN)) { $ref = new Reference(); $ref->object_model = 'Relationship'; $ref->object_id = $rel->id; $ref->source = sprintf($this->fecSearchUrlPattern, strtoupper($donorPerson['name_last']), strtoupper($donorPerson['name_first'])); $ref->name = 'FEC contribution search'; $ref->save(); if ($this->debugMode) { print "+ Added reference to FEC contribution search\n"; } } } //clear cache for recipient LsCache::clearEntityCacheById($recipientEntity['id']); } } //update os_entity_transaction $sql = 'UPDATE os_entity_transaction SET is_processed = is_verified, is_synced = 1 WHERE entity_id = ?'; $stmt = $this->db->execute($sql, array($id)); //make sure that all removed matches result in deleted fec filings and updated relationships for this entity $this->cleanupFecFilings($id, $oldDonations); //update opensecrets categories based on matched donations $this->printDebug("Updating industry categories based on matched donations..."); $newCategories = OsPerson::updateCategories($id); foreach ($newCategories as $categoryId) { $this->printDebug("+ Added industry category: " . $categoryId); } //clear cache for donor LsCache::clearEntityCacheById($id); }
private function importExecutiveRelationship($entityId, $person_arr, $title = null, $current = null) { if (!in_array($entityId, $this->old_exec_entity_ids)) { $rel = new Relationship(); $rel->entity1_id = $entityId; $rel->entity2_id = $this->entity->id; $rel->setCategory('Position'); $rel->is_current = $current; $rel->is_board = 0; $rel->is_executive = 1; if ($title != '') { $rel->description1 = $title; } //Form 3s let us set a start date if ($person_arr['formName'] == 'Form 3' && $person_arr['date']) { //filing date could be innacurate, so only use month $date = LsDate::formatFromText($person_arr['date']); $rel->start_date = preg_replace('/-\\d\\d$/', '-00', $date); } if (!$this->testMode) { $rel->save(); //save sources $rel->addReference($person_arr['readableXmlUrl'], null, null, $this->entity->name . ' ' . $person_arr['formName'], null, $person_arr['date']); if (isset($this->filing_url) && isset($this->filing_name)) { $rel->addReference($this->filing_url, null, null, $this->entity->name . " " . $this->filing_name, null, $this->filing_date); } } $this->printDebug("+ Relationship created: " . $rel->id . " (" . $rel->description1 . ")"); } }
static function getDisambiguated(LsDatespan $span) { $ret = new LsDatespan(); $ret->setStart(LsDate::getDisambiguatedAsStart($span->getStart())); $ret->setEnd(LsDate::getDisambiguatedAsEnd($span->getEnd())); return $ret; }
private function getTransactionDetails($org, $fedspending_id) { if (!$this->browser->get($this->fedSpendingSearchUrl, array('record_id' => $fedspending_id, 'datype' => 'X', 'detail' => '4'))->responseIsError()) { $response_url = $this->browser->getUrlInfo(); $response_url_str = "http://" . $response_url['host'] . $response_url['path'] . "?" . $response_url['query']; $this->printDebug("Transaction #{$fedspending_id} http://" . $response_url['host'] . $response_url['path'] . "?" . $response_url['query']); $text = $this->browser->getResponseText(); $this->browser->setResponseText(iconv('ISO-8859-1', 'UTF-8', $this->browser->getResponseText())); $xml = $this->browser->getResponseXml(); } else { $this->printDebug("Couldn't get " . $this->fedSpendingSearchUrl); return false; } $obligated_amount = $xml->xpath('/fedspendingSearchResults/data/record/amounts/obligatedAmount'); $maj_agency_cat = $xml->xpath('/fedspendingSearchResults/data/record/purchaser_information/maj_agency_cat'); $contracting_agency = $xml->xpath('/fedspendingSearchResults/data/record/purchaser_information/contractingOfficeAgencyID'); $psc_cat = $xml->xpath('/fedspendingSearchResults/data/record/product_or_service_information/psc_cat'); $signed_date = $xml->xpath('/fedspendingSearchResults/data/record/contract_information/signedDate'); $descriptionOfContractRequirement = $xml->xpath('/fedspendingSearchResults/data/record/contract_information/descriptionOfContractRequirement'); $current_completion_date = $xml->xpath('/fedspendingSearchResults/data/record/contract_information/currentCompletionDate'); $state_code = $xml->xpath('/fedspendingSearchResults/data/record/principal_place_of_performance/stateCode'); $place_of_performance_congressional_district = $xml->xpath('/fedspendingSearchResults/data/record/principal_place_of_performance/placeOfPerformanceCongressionalDistrict'); foreach ($obligated_amount as $key => $dont_use) { $gov_name = $this->cleanGovtBodyName($maj_agency_cat[$key]); $gov_agency = $this->getGovernmentBodyEntity($gov_name, $maj_agency_cat[$key]); if ($gov_agency) { //$this->printDebug("Found existing Government Agency" . $gov_agency->name); } else { $gov_name = $this->cleanGovtBodyName($maj_agency_cat[$key]); $this->printDebug($gov_name); $gov_agency = $this->addGovernmentBodyEntity($gov_name, $maj_agency_cat[$key]); $this->printDebug("Creating new Government Agency: " . $gov_agency->name); $gov_agency->addReference(self::$baseContractorUrl . $org->fedspending_id, null, array('name', 'parent_id'), 'FedSpending.org'); } if (!$gov_agency) { $this->printDebug("Error creating Government Agency"); return false; } $sub_agency_name = $this->cleanGovtBodyName($contracting_agency[$key]); $sub_agency = $this->getGovernmentBodyEntity($sub_agency_name, $contracting_agency[$key]); if ($sub_agency_name == $gov_agency->name) { $sub_agency = $gov_agency; } if (!$sub_agency) { $sub_agency = $this->addGovernmentBodyEntity($sub_agency_name, $contracting_agency[$key], $gov_agency->id); $this->printDebug("Creating new sub-agency: " . $sub_agency->name); $sub_agency->addReference(self::$baseContractorUrl . $org->fedspending_id, null, array('name', 'parent_id'), 'FedSpending.org'); } if (!$sub_agency) { $this->printDebug("Error creating Government Agency"); return false; } try { $district = null; $state = explode(': ', $state_code[$key]); $federal_district = explode(': ', $place_of_performance_congressional_district[$key]); $state = $state[0]; $federal_district = $federal_district[0]; $filing = new FedspendingFiling(); $filing->goods = $descriptionOfContractRequirement[$key]; $filing->amount = abs($obligated_amount[$key]); if ($filing->amount < 1000) { $this->printDebug('amount under $1000, rolling back'); return 'under_1000'; } //$this->printDebug("state: " . $state . " and district: " . $federal_district); if ($district = PoliticalDistrictTable::getFederalDistrict($state, $federal_district)) { //$this->printDebug("found " . $district->id); $filing->District = $district; } elseif (trim($state) && trim($federal_district)) { try { $district = PoliticalDistrictTable::addFederalDistrict($state, $federal_district); $this->printDebug("Adding District " . $state . " #" . $district->id); $filing->District = $district; } catch (Exception $e) { throw $e; } } $filing->fedspending_id = $fedspending_id; $filing->start_date = LsDate::formatFromText($signed_date[$key]); $filing->end_date = LsDate::formatFromText($current_completion_date[$key]); $relationship = null; if ($relationship = $org->getRelationshipsWithQuery($sub_agency, RelationshipTable::TRANSACTION_CATEGORY)->fetchOne()) { $relationship->addFedspendingFiling($filing); } else { $relationship = new Relationship(); $relationship->Entity1 = $org; $relationship->Entity2 = $sub_agency; $relationship->setCategory('Transaction'); $relationship->description1 = 'Contractor'; $relationship->description2 = 'Client'; $relationship->save(); $relationship->addReference(self::$baseContractorUrl . $org->fedspending_id, null, array('start_date', 'end_date', 'amount', 'goods'), 'FedSpending.org'); $relationship->addFedspendingFiling($filing); } $filing->save(); return true; } catch (Exception $e) { throw $e; } } }
static function logChanges(Doctrine_Record $oldObject, Doctrine_Record $newObject, $new = false) { $merged = false; $modId = null; //isMerge() is used to set the modification's is_merge field, which, for non-delete //modifications, is only used to identify the modification as part of a larger merge if (method_exists($newObject, 'isMerge')) { $merged = $newObject->isMerge(); } if (get_class($oldObject) != get_class($newObject)) { throw new exception("Can't log modifications between objects of different classes"); } $conn = Doctrine_Manager::connection(); try { $conn->beginTransaction(); //get clean sets of old and new data $oldData = method_exists($oldObject, 'getAllData') ? $oldObject->getAllData() : $oldObject->toArray(false); $newData = method_exists($newObject, 'getAllData') ? $newObject->getAllData() : $newObject->toArray(false); //if modification is create, set all old data to null if ($new) { $oldData = array_fill_keys(array_keys($oldData), null); } //remove null and blank keys $oldData = array_filter($oldData, array('self', 'notNullOrBlankCallback')); $newData = array_filter($newData, array('self', 'notNullOrBlankCallback')); //change false values to 0 $oldData = array_map(array('self', 'falseToZeroCallback'), $oldData); $newData = array_map(array('self', 'falseToZeroCallback'), $newData); //create set of possible modified fields $fields = array_unique(array_merge(array_keys($oldData), array_keys($newData))); $fields = array_diff($fields, array('id', 'created_at', 'updated_at', 'is_deleted', 'last_user_id')); $modifiedFields = array(); foreach ($fields as $field) { //field is modified if field was or is no longer null, or if old and new values are different if (!array_key_exists($field, $oldData) || !array_key_exists($field, $newData) || !self::areSameValues($oldData[$field], $newData[$field])) { $modifiedFields[] = $field; } } if ($merged || count($modifiedFields)) { $db = Doctrine_Manager::connection(); $sql = 'INSERT INTO modification (object_model, object_id, object_name, is_create, is_merge, user_id, created_at, updated_at) ' . 'VALUES (?, ?, ?, ?, ?, ?, ?, ?)'; if (!($name = $newObject->getName())) { $name = get_class($newObject); } $params = array(get_class($oldObject), $newObject->id ? $newObject->id : '', $name, $new, $merged, self::getUserId(), LsDate::getCurrentDateTime(), LsDate::getCurrentDateTime()); $stmt = $db->execute($sql, $params); $modId = $db->lastInsertId('modification'); if (count($modifiedFields)) { //insert all modification_field records at once $sql = 'INSERT INTO modification_field (modification_id, field_name, old_value, new_value) VALUES'; $params = array(); foreach ($modifiedFields as $field) { $sql .= ' (?, ?, ?, ?),'; $params = array_merge($params, array($modId, $field, isset($oldData[$field]) ? $oldData[$field] : null, isset($newData[$field]) ? $newData[$field] : null)); } $sql = substr($sql, 0, strlen($sql) - 1); $stmt = $db->execute($sql, $params); } } $conn->commit(); } catch (Exception $e) { $conn->rollback(); throw $e; } unset($oldObject); unset($newObject); return $modId; }
static function birthDatesAreCompatible(LsDate $d1, LsDate $d2, $maxYearDiff = 1) { if ($d1->isblank() || $d2->isBlank()) { return true; } elseif (abs($d1->getYear() - $d2->getYear()) <= $maxYearDiff && LsLogic::areCompatible($d1->getMonth(true), $d2->getMonth(true)) && LsLogic::areCompatible($d1->getDay(true), $d2->getDay(true))) { return true; } else { return false; } }
public function executeChat($request) { $this->checkUser(); $this->room = $request->getParameter('room', 1); if ($this->room < 1 || $this->room > 20) { $this->room = 1; } //log chat user $db = Doctrine_Manager::connection(); $sql = 'REPLACE INTO chat_user (user_id, room, updated_at) VALUES (?, ?, ?)'; //use REPLACE in order to update timestamp $stmt = $db->execute($sql, array($this->getUser()->getGuardUser()->id, $this->room, LsDate::getCurrentDateTime())); //get other chat users $q = LsDoctrineQuery::create()->from('sfGuardUser u')->leftJoin('u.Profile p')->leftJoin('u.ChatUser cu')->where('cu.room = ?', $this->room)->andWhere('cu.updated_at > ?', date('Y-m-d H:i:s', strtotime('5 minutes ago')))->andWhere('u.id <> ?', $this->getUser()->getGuardUser()->id)->setHydrationMode(Doctrine::HYDRATE_ARRAY); $this->users = $q->execute(); }
static function lockEntityById($id, $userId) { $db = Doctrine_Manager::connection(); $sql = 'UPDATE os_entity_transaction SET locked_at = ?, locked_by_user_id = ? WHERE entity_id = ?'; $stmt = $db->execute($sql, array(LsDate::getCurrentDateTime(), $userId, $id)); }
private function getRawFiles() { $url = 'http://www.senate.gov/legislative/Public_Disclosure/database_download.htm'; $this->browser->get($url); $text = $this->browser->getResponseText(); $re = '/(http\\:\\/\\/soprweb\\.senate\\.gov\\/downloads\\/(((19|20)\\d\\d)_(\\d)\\.zip))">.*?((\\d+)\\/(\\d+)\\/(\\d+))/is'; //</td><td align="center">6/17/2008</td> preg_match_all($re, $text, $matches, PREG_SET_ORDER); foreach ($matches as $match) { $date = new LsDate($match[6]); $file = sfConfig::get('sf_root_dir') . '/data/ldaFiles/' . $match[2]; if (file_exists($file)) { $date2 = new LsDate(date('Y-m-d', filemtime($file))); if (LsDate::compare($date, $date2) < 1) { continue; } } $this->browser->get($match[1]); $saved = file_put_contents($file, $this->browser->getResponseText()); if ($saved !== FALSE) { $this->printDebug('saved'); $zip = zip_open($file); if (is_resource($zip)) { $this->printDebug('unzipped'); while ($zip_entry = zip_read($zip)) { $n = basename(zip_entry_name($zip_entry)); $li = Doctrine::getTable('LdaImport')->findOneByFilename($n); if ($li && $li->filesize == zip_entry_filesize($zip_entry)) { continue; } $fp = fopen($this->_dir . $n, 'w'); if (zip_entry_open($zip, $zip_entry, "r")) { $buf = zip_entry_read($zip_entry, zip_entry_filesize($zip_entry)); zip_entry_close($zip_entry); fwrite($fp, $buf); fclose($fp); $li = new LdaImport(); $li->year = $match[2]; $li->quarter = $match[5]; $li->filename = $n; $li->filesize = filesize($this->_dir . $n); $li->offset = 0; $li->save(); } } zip_close($zip); } else { $this->printDebug('zip failure'); } } else { continue; } } }
public function logCustomFieldChange($key, $oldValue, $newValue, $modificationId = null) { $object = $this->getInvoker(); if (!($id = $object->id)) { throw Exception("Can't log custom field changes for object without id"); } $model = get_class($object); $db = Doctrine_Manager::connection(); try { $db->beginTransaction(); if (!$modificationId) { //insert modification $userId = LsVersionableListener::getUserId(); $time = LsDate::getCurrentDateTime(); $sql = 'INSERT INTO modification (object_model, object_id, user_id, created_at, updated_at) ' . 'VALUES (?, ?, ?, ?, ?)'; $stmt = $db->execute($sql, array($model, $id, $userId, $time, $time)); $modificationId = $db->lastInsertId('modification'); } //insert modification field $sql = 'INSERT INTO modification_field (modification_id, field_name, old_value, new_value) VALUES (?, ?, ?, ?)'; $stmt = $db->execute($sql, array($modificationId, $key, $oldValue, $newValue)); $db->commit(); } catch (Exception $e) { $db->rollback(); throw $e; } }
protected function scrapeArticles() { foreach ($this->articleList as $url => $ary) { $article = new Article(); $article->url = $this->cleanArticleUrl($url); $article->title = $this->cleanArticleTitle($ary['title']); $article->source_id = $this->getArticleSourceId(); //get authors if listed if (preg_match_all('#q=author%3A%22([^%]+)%22#i', $ary['subtitle'], $authorMatches, PREG_PATTERN_ORDER)) { $article->authors = urldecode(implode(',', $authorMatches[1])); } try { $article->body = $this->scrapeArticleBody($article->url); } catch (Exception $e) { $this->printDebug("*** Error scraping article: " . $e->getMessage() . " (" . $url . ")"); continue; } $article->published_at = $this->getArticleDateFromBody($article->body); $article->reviewed_at = NULL; $article->found_at = LsDate::getCurrentDateTime(); $this->articles[] = $article; $this->printDebug("+ Scraped article '" . $article->title . "'"); } }