public function testSetClient()
{
$newClient = new Client();
$newClient->setId(789);
$this->rebalancerAction->setClient($newClient);
$this->assertEquals(789, $this->rebalancerAction->getClient()->getId());
}
public function testLoadPortfolioValues()
{
$clientRepo = new ClientRepository();
$client = $clientRepo->findClientByEmail('*****@*****.**');
$portfolio = $this->repository->findPortfolioByClient($client);
$this->repository->loadPortfolioValues($client);
$this->assertEquals('2451038.0', $portfolio->getTotalValue());
$this->assertEquals('699098.0', $portfolio->getTotalInSecurities());
$this->assertEquals('992397.0', $portfolio->getTotalCashInAccounts());
$this->assertEquals('759543.0', $portfolio->getTotalCashInMoneyMarket());
$this->assertEquals('0.0', $portfolio->getSasCash());
$this->assertEquals('0.0', $portfolio->getCashBuffer());
$this->assertEquals('0.0', $portfolio->getBillingCash());
$notExistClient = new Client();
$notExistPortfolio = new Portfolio();
$notExistPortfolio->setId(0);
$notExistClient->setPortfolio($notExistPortfolio);
$this->repository->loadPortfolioValues($notExistClient);
$this->assertNull($notExistPortfolio->getTotalValue());
$this->assertNull($notExistPortfolio->getTotalInSecurities());
$this->assertNull($notExistPortfolio->getTotalCashInAccounts());
$this->assertNull($notExistPortfolio->getTotalCashInMoneyMarket());
$this->assertNull($notExistPortfolio->getSasCash());
$this->assertNull($notExistPortfolio->getCashBuffer());
$this->assertNull($notExistPortfolio->getBillingCash());
}
/**
* Find one ria by client
*
* @param Client $client
* @return Ria
*/
public function findOneByClient(Client $client)
{
$sql = "SELECT r.*, rci.is_tax_loss_harvesting as isTlhEnabled, rci.tax_loss_harvesting_minimum as minTlh,\n rci.tax_loss_harvesting_minimum_percent as minTlhPercent, rci.tax_loss_harvesting as minRelationshipValue,\n rci.tax_loss_harvesting_percent as clientTaxBracket, rci.use_municipal_bond as is_use_municipal_bond,\n rci.is_transaction_fees as useTransactionFees, rci.transaction_amount as transactionAmount,\n rci.transaction_amount_percent as transactionAmountPercent\n FROM {$this->table} r\n LEFT JOIN " . self::TABLE_USER_PROFILE . " cp ON (cp.ria_user_id = r.id)\n LEFT JOIN " . self::TABLE_RIA_COMPANY_INFORMATION . " rci ON (rci.ria_user_id = r.id)\n WHERE cp.user_id = :client_id";
$params = array('client_id' => $client->getId());
$result = $this->db->query($sql, $params);
$collection = $this->bindCollection($result);
return $collection->first();
}
/**
* Load stop THL value for client object
* If client specific value less then ria global then set ria global value
*
* @param Client $client
*/
public function loadStopTlhValue(Client $client)
{
$sql = "SELECT GREATEST(IFNULL(cs.stop_tlh_value, 0), IFNULL(rci.stop_tlh_value, 0)) as stop_tlh_value\n FROM {$this->table} c\n LEFT JOIN " . self::TABLE_USER_PROFILE . " cp ON (cp.user_id = c.id)\n LEFT JOIN " . self::TABLE_RIA_COMPANY_INFORMATION . " rci ON (cp.ria_user_id = rci.ria_user_id)\n LEFT JOIN " . self::TABLE_CLIENT_SETTINGS . " cs ON (cs.client_id = c.id)\n WHERE c.id = :client_id LIMIT 1";
$pdo = $this->db->getPdo();
$statement = $pdo->prepare($sql);
$statement->execute(array('client_id' => $client->getId()));
$data = $statement->fetch(\PDO::FETCH_ASSOC);
$client->setStopTlhValue($data['stop_tlh_value']);
}
/**
* Find system client accounts by client
* With approved bill amount
*
* @param Client $client
* @return AccountCollection
*/
public function findClientAccounts(Client $client)
{
$sql = "SELECT a.*, IFNULL(SUM(bi.feeBilled), 0) as billing_cash\n FROM " . self::TABLE_SYSTEM_ACCOUNT . " a\n LEFT JOIN " . self::TABLE_BILL_ITEM . " bi ON (bi.system_account_id = a.id AND bi.status = :status)\n WHERE a.client_id = :client_id\n GROUP BY a.id";
$parameters = array('client_id' => $client->getId(), 'status' => self::STATUS_BILL_APPROVED);
$result = $this->db->query($sql, $parameters);
$collection = $this->bindCollection($result);
foreach ($collection as $item) {
$item->setClient($client);
}
return $collection;
}
/**
* Get portfolio values from db and update portfolio object
*
* @param Client $client
*/
public function loadPortfolioValues(Client $client)
{
$sql = "SELECT * FROM " . self::TABLE_CLIENT_PORTFOLIO_VALUE . " as cpv\n LEFT JOIN {$this->table} cp ON (cpv.client_portfolio_id = cp.id)\n LEFT JOIN " . self::TABLE_USER . " u ON u.id = cp.client_id\n WHERE cp.portfolio_id = :portfolio_id AND u.id = :clientId\n ORDER BY cpv.date desc LIMIT 1";
$pdo = $this->db->getPdo();
$portfolio = $client->getPortfolio();
$statement = $pdo->prepare($sql);
$statement->execute(array('portfolio_id' => $portfolio->getId(), 'clientId' => $client->getId()));
$data = $statement->fetch(\PDO::FETCH_ASSOC);
if ($data) {
$portfolio->setTotalValue(isset($data['total_value']) ? round($data['total_value'], 2) : 0);
$portfolio->setTotalInSecurities(isset($data['total_in_securities']) ? round($data['total_in_securities'], 2) : 0);
$portfolio->setTotalCashInAccounts(isset($data['total_cash_in_accounts']) ? round($data['total_cash_in_accounts'], 2) : 0);
$portfolio->setTotalCashInMoneyMarket(isset($data['total_cash_in_money_market']) ? round($data['total_cash_in_money_market'], 2) : 0);
$portfolio->setSasCash(isset($data['sas_cash']) ? round($data['sas_cash'], 2) : 0);
$portfolio->setCashBuffer(isset($data['cash_buffer']) ? round($data['cash_buffer'], 2) : 0);
$portfolio->setBillingCash(isset($data['billing_cash']) ? round($data['billing_cash'], 2) : 0);
}
}
public function testGetAccountsByRebalancerActionData()
{
$rebalancerActionRepo = new RebalancerActionRepository();
$rebalancerActions = $rebalancerActionRepo->findAll();
/** @var RebalancerAction $rebalancerAction */
$rebalancerAction = $rebalancerActions->first();
$sql = "SELECT u.*, up.client_account_managed as account_managed from rebalancer_actions ra\n LEFT JOIN client_portfolio_values cpv ON cpv.id = ra.client_portfolio_value_id\n LEFT JOIN client_portfolio cp ON cp.id = cpv.client_portfolio_id\n LEFT JOIN users u ON u.id = cp.client_id\n LEFT JOIN user_profiles up ON up.user_id = u.id\n WHERE ra.id = :rebalancerActionId\n ";
$parameters = array('rebalancerActionId' => $rebalancerAction->getId());
$connection = WealthbotMySqlSqliteConnection::getInstance();
$pdo = $connection->getPdo();
$statement = $pdo->prepare($sql);
$statement->execute($parameters);
$result = $statement->fetch(\PDO::FETCH_ASSOC);
$client = new Client();
$client->setId($result['id']);
$accounts = $this->repository->findClientAccounts($client);
$rebalancerAction->setClient($client);
$client->setAccountManaged(Client::ACCOUNT_MANAGED_HOUSEHOLD);
$collection = $this->repository->getAccountsByRebalancerAction($rebalancerAction);
$this->assertCount(4, $collection);
//-------------------------------------------------------------------------
$rebalancerAction->setAccountId($accounts->first()->getId());
$client->setAccountManaged(Client::ACCOUNT_MANAGED_ACCOUNT);
$collection = $this->repository->getAccountsByRebalancerAction($rebalancerAction);
$account = $collection->first();
$this->assertCount(1, $collection);
$this->assertEquals($rebalancerAction->getAccountId(), $account->getId());
$this->assertEquals($client->getId(), $account->getClient()->getId());
}
public function startForClient(Client $client)
{
$this->logger->log("Start rebalance for client: {$client->getId()}.");
$this->client = $client;
/** @var Ria $ria */
$ria = $this->getRepository('Ria')->findOneByClient($client);
$client->setRia($ria);
$riaCompanyInformation = $this->getRepository('RiaCompanyInformation')->findOneByRia($ria);
$ria->setRiaCompanyInformation($riaCompanyInformation);
//steps 1-3
foreach ($client->getAccounts() as $account) {
$this->prepareAccount($account);
$this->rebalancingTrigger($account);
}
$this->rebalance();
/*//step 4
//$isAllowed = $this->isRebalAllowed($client);
$isAllowed = true;
//step 5 - 7
if ($isAllowed) {
$collection = $this->prepareCollection($client);
if (!$collection || ($collection && count($collection) < 2)) {
return false;
}
$this->startRebalanceCycle($client, $collection);
}*/
}
/**
* Get sum of client losses for year
*
* @param Client $client
* @param string $year
* @return float
*/
public function getClientLossesSumForYear(Client $client, $year)
{
$sql = "SELECT SUM(l.realized_gain_loss) as losses_sum FROM {$this->table} l\n LEFT JOIN " . self::TABLE_POSITION . " p ON (l.position_id = p.id)\n LEFT JOIN " . self::TABLE_SYSTEM_ACCOUNT . " a ON (p.client_system_account_id = a.id)\n WHERE a.client_id = :client_id\n AND l.date BETWEEN :date_from AND :date_to\n AND l.status = :status_closed AND l.realized_gain_loss < 0";
$params = array('client_id' => $client->getId(), 'status_closed' => Lot::LOT_CLOSED, 'date_from' => $year . "-01-01", 'date_to' => $year . "-12-31");
$lossesSum = 0;
$data = $this->db->queryOne($sql, $params);
if (isset($data['losses_sum'])) {
$lossesSum = $data['losses_sum'];
}
return $lossesSum;
}
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());
}
public function testSetClient()
{
$newClient = new Client();
$newClient->setId(29);
$this->account->setClient($newClient);
$this->assertEquals(29, $this->account->getClient()->getId());
}
