public function findSecuritiesByAccount(Account $account)
{
$sql = "SELECT s.id, s.name AS name, s.symbol AS symbol, sp.price AS price, pos.quantity AS qty,\n IFNULL((SELECT 1 FROM " . self::TABLE_CE_MODEL . " cem\n LEFT JOIN " . self::TABLE_CLIENT_PORTFOLIO . " cp ON (cp.portfolio_id = cem.id)\n LEFT JOIN " . self::TABLE_SECURITY_ASSIGNMENT . " sa ON sa.model_id = cem.id\n WHERE sa.security_id = s.id AND cp.is_active = 1 AND cem.owner_id = :client_id\n ), 0) as isPreferredBuy\n FROM " . self::TABLE_POSITION . " pos\n LEFT JOIN " . self::TABLE_SYSTEM_ACCOUNT . " sca ON pos.client_system_account_id = sca.id\n LEFT JOIN " . self::TABLE_SECURITY . " s ON s.id = pos.security_id\n LEFT JOIN " . self::TABLE_SECURITY_PRICE . " sp ON (sp.security_id = s.id AND sp.is_current = true)\n WHERE sca.id = :account_id AND pos.status = :position_status;\n ";
$parameters = array('account_id' => $account->getId(), 'position_status' => Security::POSITION_STATUS_IS_OPEN, 'client_id' => $account->getClient()->getId());
$result = $this->db->query($sql, $parameters);
return $this->bindCollection($result);
}
public function getCurrentAllocations(Portfolio $portfolio, Account $account = null)
{
$sql = "SELECT pos.client_system_account_id, sa.security_id as security_id, sm.id as muni_id\n FROM " . self::TABLE_SUBCLASS . " subc\n INNER JOIN " . self::TABLE_CE_MODEL_ENTITY . " ceme ON ceme.subclass_id = subc.id\n INNER JOIN " . self::TABLE_SECURITY_ASSIGNMENT . " sa ON sa.id = ceme.security_assignment_id\n INNER JOIN " . self::TABLE_POSITION . " pos ON pos.security_id = sa.security_id\n LEFT JOIN " . self::TABLE_SECURITY_ASSIGNMENT . " sam ON sam.id = ceme.muni_substitution_id\n LEFT JOIN " . self::TABLE_SECURITY . " sm ON sam.security_id = sm.id\n WHERE ceme.model_id = :portfolioId";
$parameters = array('portfolioId' => $portfolio->getId());
if (null !== $account) {
$sql .= " AND pos.client_system_account_id = :accountId";
$parameters['accountId'] = $account->getId();
}
$sql .= " GROUP BY pos.client_system_account_id, pos.security_id";
$positions = $this->db->query($sql, $parameters);
$lastPositions = array();
foreach ($positions as $position) {
$lastPosition = $this->getLastPosition($portfolio, $position['security_id'], $position['client_system_account_id']);
if (!empty($lastPosition) && ($lastPosition['status'] == Security::POSITION_STATUS_INITIAL || $lastPosition['status'] == Security::POSITION_STATUS_IS_OPEN)) {
$lastPositions[]['security'] = $lastPosition;
}
if ($position['muni_id']) {
$lastMuniPosition = $this->getLastPosition($portfolio, $position['muni_id'], $position['client_system_account_id'], true);
if (!empty($lastMuniPosition) && ($lastMuniPosition['status'] == Security::POSITION_STATUS_INITIAL || $lastMuniPosition['status'] == Security::POSITION_STATUS_IS_OPEN)) {
$lastPositions[]['muni'] = $lastMuniPosition;
}
}
}
return $lastPositions;
}
/**
* @param Account $account
* @return int
*/
public function findOneTimeDistribution(Account $account)
{
$sql = "SELECT * FROM " . $this->table . "\n WHERE type = :type AND\n system_client_account_id = :account_id";
$parameters = array('type' => Distribution::TYPE_ONE_TIME, 'account_id' => $account->getId());
$distributions = $this->db->query($sql, $parameters);
$sum = 0;
foreach ($distributions as $distribution) {
$sum += $distribution['amount'];
}
return $sum;
}
public function testLoadFromArray()
{
$securities = $this->account->getSecurities();
$this->assertEquals(1, $this->account->getId());
$this->assertEquals(Account::TYPE_PERSONAL_INVESTMENT, $this->account->getType());
$this->assertTrue($this->account->isTaxable());
$this->assertEquals(Account::STATUS_INIT_REBALANCE, $this->account->getStatus());
$this->assertTrue($this->account->getIsFirstTime());
$this->assertTrue($this->account->getIsActiveEmployer());
$this->assertTrue($this->account->getIsReadyToRebalance());
$this->assertEquals(15000, $this->account->getScheduledDistribution());
$this->assertEquals(3450, $this->account->getOneTimeDistribution());
$this->assertEquals(2300, $this->account->getCashBuffer());
$this->assertEquals(3000, $this->account->getSasCash());
$this->assertEquals(3400, $this->account->getBillingCash());
$this->assertEquals(30000, $this->account->getTotalCash());
$this->assertEquals(28, $this->account->getClient()->getId());
$this->assertCount(2, $securities);
$this->assertEquals(1, $securities[1]->getId());
$this->assertEquals(2, $securities[2]->getId());
}
public function rebalancingTrigger(Account $account)
{
$accountStatus = $account->getStatus();
if ($accountStatus === Account::STATUS_INIT_REBALANCE) {
$this->logger->logInfo("Init rebalance for account: {$account->getId()}.");
$this->liquidateAccountIfNeed($account);
} elseif ($accountStatus === Account::STATUS_INIT_REBALANCE_COMPLETE || $accountStatus === Account::STATUS_REBALANCED || $accountStatus === Account::STATUS_ANALYZED) {
$this->checkCashNeeds($account);
}
}
public function getPositionsByPortfolio(Portfolio $portfolio, Account $account = null, Security $security = null)
{
$sql = "SELECT pos.client_system_account_id, sa.security_id as security_id, sm.id as muni_id\n FROM " . self::TABLE_SUBCLASS . " subc\n INNER JOIN " . self::TABLE_CE_MODEL_ENTITY . " ceme ON ceme.subclass_id = subc.id\n INNER JOIN " . self::TABLE_SECURITY_ASSIGNMENT . " sa ON sa.id = ceme.security_assignment_id\n INNER JOIN " . self::TABLE_POSITION . " pos ON pos.security_id = sa.security_id\n LEFT JOIN " . self::TABLE_SECURITY_ASSIGNMENT . " sam ON sam.id = ceme.muni_substitution_id\n LEFT JOIN " . self::TABLE_SECURITY . " sm ON sam.security_id = sm.id\n WHERE ceme.model_id = :portfolioId\n ";
$parameters = array('portfolioId' => $portfolio->getId());
if (null !== $account) {
$sql .= " AND pos.client_system_account_id = :accountId";
$parameters['accountId'] = $account->getId();
}
if (null !== $security) {
$sql .= " AND (pos.security_id = :securityId OR sm.id = :securityId)";
$parameters['securityId'] = $security->getId();
}
$sql .= " GROUP BY pos.client_system_account_id, pos.security_id";
return $this->db->query($sql, $parameters);
}
/**
* Get account data
*
* @param Account $account
* @return array
*/
public function getAccountValues(Account $account)
{
$sql = "SELECT * FROM " . self::TABLE_CLIENT_ACCOUNT_VALUES . " WHERE system_client_account_id = :account_id\n ORDER BY date DESC\n LIMIT 1";
return $this->db->queryOne($sql, array('account_id' => $account->getId()));
}
