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; }
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 findById($id) { $sql = "SELECT rq.*, ra.job_id FROM " . self::TABLE_REBALANCER_QUEUE . " rq\n LEFT JOIN " . self::TABLE_REBALANCER_ACTION . " ra On ra.id = rq.rebalancer_action_id\n WHERE rq.id = :id\n "; $result = $this->db->queryOne($sql, array('id' => $id)); if (empty($result)) { return null; } /** @var QueueItem $item */ $item = $this->bindObject($result); if (isset($result['security_id'])) { $security = new Security(); $security->setId($result['security_id']); $item->setSecurity($security); } if (isset($result['system_client_account_id'])) { $account = new Account(); $account->setId($result['system_client_account_id']); $item->setAccount($account); } if (isset($result['lot_id'])) { $lot = new Lot(); $lot->setId($result['lot_id']); $item->setLot($lot); } return $item; }
public function setUp() { $connection = WealthbotMySqlSqliteConnection::getInstance(); $sql = "SELECT * FROM " . BaseRepository::TABLE_SYSTEM_ACCOUNT . " ORDER BY id ASC LIMIT 1"; $result = $connection->query($sql); $this->account = new Account(); $this->account->loadFromArray($result[0]); $this->repository = new DistributionRepository(); $this->businessCalendar = new BusinessCalendar(); }
/** * @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 testSetAccount() { $account = new Account(); $account->setId(96); $this->queueItem->setAccount($account); $this->assertEquals(96, $this->queueItem->getAccount()->getId()); }
public function testIsAllSecuritiesEqualCash() { $this->assertFalse($this->account->isAllSecuritiesEqualCash()); $security = $this->account->getSecurities()->first(); $security->setSymbol(Security::SYMBOL_IDA12); $this->assertFalse($this->account->isAllSecuritiesEqualCash()); foreach ($this->account->getSecurities() as $security) { $security->setSymbol(Security::SYMBOL_IDA12); } $this->assertTrue($this->account->isAllSecuritiesEqualCash()); $this->account->getSecurities()->clear(); $this->assertFalse($this->account->isAllSecuritiesEqualCash()); }
/** * Checks if transaction is allowed * * @param Account $account [description] * @param amountOfTransaction [$ value of transaction buy/sell] * @param security [which security are we selling?] * @param txType [type of transaction buy/sell] * @return [bool] [true/false if transaction passes the filters or not] */ public function checkTransaction(Account $account, $amountOfTransaction, $security = null, $txType = null) { $ria = $account->getClient()->getRia(); $totalCash = $account->getTotalCash(); if (empty($totalCash) || $totalCash == 0) { return false; } $txAmountPercent = round($amountOfTransaction / $totalCash * 100, 2); if (!is_null($security) && !is_null($txType)) { $client = $account->getClient(); $repository = $this->getRepository('SecurityTransaction'); $securityTransaction = $repository->findOneByPortfolioAndSecurity($client->getPortfolio(), $security); if ($txType === 'sell' && $amountOfTransaction >= $securityTransaction->getMinimumSell()) { return true; } if ($txType === 'buy' && $amountOfTransaction >= $securityTransaction->getMinimumBuy()) { return true; } $this->logger->logInfo("Minimum buy/sell failed for {$security->getId()}, RIA: {$ria->getId()}"); return false; } //TODO: need more logging if (!$ria->getRiaCompanyInformation()->getUseTransactionFees()) { return true; } if ($amountOfTransaction >= $ria->getRiaCompanyInformation()->getTransactionMinAmount()) { return true; } if ($txAmountPercent >= $ria->getRiaCompanyInformation()->getTransactionMinAmountPercent()) { return true; } return false; }
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())); }
public function testLotOrderForMuni() { $positions = array(array('client_system_account_id' => 222, 'muni_id' => 78, 'security_id' => 22222), array('client_system_account_id' => 333, 'muni_id' => null, 'security_id' => 33333)); /** @var LotRepository $mockRepository */ $mockRepository = $this->getMockBuilder('Model\\WealthbotRebalancer\\Repository\\LotRepository')->disableOriginalConstructor()->setMethods(array('getPositionsByPortfolio', 'getLastPositionLots'))->getMock(); $mockRepository->expects($this->any())->method('getPositionsByPortfolio')->will($this->returnValue($positions)); $mockRepository->expects($this->any())->method('getLastPositionLots')->will($this->returnCallback(function (Portfolio $portfolio, $securityId, $clientSystemAccountId, $isMuni = false) { $lotCollection = new LotCollection(); $lot = new Lot(); $lot->setIsMuni($isMuni); $lotCollection->add($lot); return $lotCollection; })); $mockRepository->expects($this->any())->method('findLotsByAccountAndSecurity')->will($this->returnCallback(function (Portfolio $portfolio, $securityId, $clientSystemAccountId, $isMuni = false) { $lotCollection = new LotCollection(); $lot = new Lot(); $lot->setIsMuni($isMuni); $lotCollection->add($lot); return $lotCollection; })); $lotCollection = $mockRepository->findLotsBySubclass(new Portfolio(), new Subclass(), new Account()); $lot1 = $lotCollection->first(); $this->assertFalse($lot1->getIsMuni()); $lot2 = $lotCollection->next(); $this->assertTrue($lot2->getIsMuni()); $lot3 = $lotCollection->next(); $this->assertFalse($lot3->getIsMuni()); //-------------------------------------------------------------------------------------------------------------/ $account = new Account(); $client = new Client(); $client->setPortfolio(new Portfolio()); $account->setClient($client); $lotCollection = $mockRepository->findLotsByAccountAndSecurity($account, new Security()); $lot1 = $lotCollection->first(); $this->assertFalse($lot1->getIsMuni()); $lot2 = $lotCollection->next(); $this->assertTrue($lot2->getIsMuni()); $lot3 = $lotCollection->next(); $this->assertFalse($lot3->getIsMuni()); }