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());
}
