public function findSecuritiesByPortfolio(Portfolio $portfolio)
{
$sql = "SELECT s.id as id, s.name as name, s.symbol as symbol, sp.price\n FROM " . self::TABLE_CE_MODEL_ENTITY . " ceme\n LEFT JOIN " . self::TABLE_SECURITY_ASSIGNMENT . " sa ON sa.id = ceme.security_assignment_id\n LEFT JOIN " . self::TABLE_SECURITY . " s ON s.id = sa.security_id\n LEFT JOIN " . self::TABLE_SECURITY_PRICE . " sp ON (sp.security_id = s.id AND sp.is_current = true)\n WHERE ceme.model_id = :portfolioId\n ORDER BY s.id ASC";
$parameters = array('portfolioId' => $portfolio->getId());
$result = $this->db->query($sql, $parameters);
$securityCollection = $this->bindCollection($result);
return $securityCollection;
}
/**
* @param Portfolio $portfolio
* @param Security $security
* @return SecurityTransaction
*/
public function findOneByPortfolioAndSecurity(Portfolio $portfolio, Security $security)
{
$sql = "\n SELECT st.* FROM {$this->table} st\n LEFT JOIN " . self::TABLE_SECURITY_ASSIGNMENT . " sa ON st.security_assignment_id = sa.id\n INNER JOIN ce_models cem ON (cem.parent_id = sa.model_id AND cem.id = :model_id)\n WHERE sa.security_id = :security_id\n ";
$params = array('security_id' => $security->getId(), 'model_id' => $portfolio->getId());
$result = $this->db->queryOne($sql, $params);
if (!$result) {
return null;
}
return $this->bindObject($result);
}
public function findByPortfolioAndJob(Portfolio $portfolio, Job $job)
{
$sql = "SELECT ra.*, j.rebalance_type as status, cp.portfolio_id FROM " . $this->table . " ra\n LEFT JOIN " . self::TABLE_CLIENT_PORTFOLIO_VALUE . " cpv ON cpv.id = ra.client_portfolio_value_id\n LEFT JOIN " . self::TABLE_CLIENT_PORTFOLIO . " cp ON cp.id = cpv.client_portfolio_id\n LEFT JOIN " . self::TABLE_JOB . " j ON j.id = ra.job_id\n WHERE cp.portfolio_id = :portfolioId AND ra.job_id = :jobId\n ";
$parameters = array('portfolioId' => $portfolio->getId(), 'jobId' => $job->getId());
$results = $this->db->query($sql, $parameters);
$collection = $this->bindCollection($results);
foreach ($collection as $item) {
$item->setJob($job);
}
return $collection;
}
public function getLastPositionLots(Portfolio $portfolio, $securityId, $clientSystemAccountId, $isMuni = false)
{
$securityField = $isMuni ? 'muni_substitution_id' : 'security_assignment_id';
$sql = "SELECT p.id, p.security_id as security_id, p.status as status, p.client_system_account_id\n FROM " . self::TABLE_CE_MODEL_ENTITY . " ceme\n INNER JOIN " . self::TABLE_SECURITY_ASSIGNMENT . " sa ON sa.id = ceme." . $securityField . "\n INNER JOIN " . self::TABLE_POSITION . " p ON p.security_id = sa.security_id\n INNER JOIN " . self::TABLE_SUBCLASS . " subc ON subc.id = sa.subclass_id\n WHERE p.security_id = :security_id AND p.client_system_account_id = :client_system_account_id AND ceme.model_id = :portfolio_id\n ORDER BY p.date DESC\n LIMIT 1";
$parameters = array('security_id' => $securityId, 'client_system_account_id' => $clientSystemAccountId, 'portfolio_id' => $portfolio->getId());
$lastPosition = $this->db->queryOne($sql, $parameters);
return $this->findLotsForPosition($lastPosition, $isMuni);
}
