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;
}
protected function bindCollection(array $data)
{
$collection = new SecurityCollection();
foreach ($data as $values) {
$element = new Security();
$element->loadFromArray($values);
$collection->add($element);
}
return $collection;
}
/**
* @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 testSetSecurities()
{
$securityCollection = new SecurityCollection();
$security = new Security();
$security->setId(96);
$securityCollection->add($security);
$this->portfolio->setSecurities($securityCollection);
$securities = $this->portfolio->getSecurities();
$this->assertCount(1, $securities);
$this->assertEquals(96, $securities->get(96)->getId());
}
public function testIsCanBeSold()
{
$this->assertFalse($this->security->isCanBeSold(false, false));
$this->assertFalse($this->security->isCanBeSold(false, 1));
$this->assertFalse($this->security->isCanBeSold(1, false));
$this->assertFalse($this->security->isCanBeSold(-1, 1));
$this->assertFalse($this->security->isCanBeSold(1, -1));
$this->assertFalse($this->security->isCanBeSold(-1, -1));
$this->assertFalse($this->security->isCanBeSold(11, 206));
$this->assertFalse($this->security->isCanBeSold(11, 1));
$this->assertFalse($this->security->isCanBeSold(1, 206));
$this->assertTrue($this->security->isCanBeSold(1, 1));
}
/**
* Buy security
*
* @param Security $security
* @param Account $account
* @param $amount
* @param bool $dryRun
* @return float|int
* @throws \Exception
*/
public function buySecurity(Security $security, Account $account, $amount, $dryRun = false)
{
$price = $security->getPrice();
$buyQuantity = $price > 0 ? floor($amount / $price) : 0;
$buyAmount = $buyQuantity * $price;
if (!$security->isCanBePurchased($buyQuantity, $buyAmount)) {
$exception = new \Exception(sprintf('Buying security error: cannot buy security with id: %s, qty: %s, amount: %s.', $security->getId(), $buyQuantity, $buyAmount));
$this->logger->logError($exception);
throw $exception;
}
if (!$this->checkTransaction($account, $amount, $security, 'buy')) {
$exception = new \Exception("Cannot buy: {$security->getId()} . Transaction check fails. (Amount:{$amount})");
$this->logger->logError($exception);
throw $exception;
}
if (!$dryRun) {
$this->logger->logInfo("Buy security {$security->getId()} qty: {$buyQuantity}; amount: {$buyAmount}");
$security->buy($buyQuantity, $buyAmount);
$queueItem = new QueueItem();
$queueItem->setRebalancerActionId($this->getRebalancerAction()->getId());
$queueItem->setSecurity($security);
$queueItem->setAccount($account);
$queueItem->setQuantity($buyQuantity);
$queueItem->setAmount($buyAmount);
$queueItem->setStatus(QueueItem::STATUS_BUY);
$this->getRebalancerQueue()->addItem($queueItem);
}
return $buyAmount;
}
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);
}
protected function bindSubclassCollection(array $data, SecurityCollection $securityCollection)
{
$subclassCollection = new SubclassCollection();
foreach ($data as $values) {
$subclass = new Subclass();
$subclass->loadFromArray($values);
/** @var Security $security */
$security = $securityCollection->get($values['security_id']);
$security->setSubclass($subclass);
$subclass->setSecurity($security);
if (isset($values['muni_substitution_id']) && $values['muni_substitution_id']) {
$sql = "SELECT sp.price FROM " . self::TABLE_SECURITY_PRICE . " sp\n WHERE sp.is_current = true AND sp.security_id = :security_id\n ORDER BY sp.datetime DESC\n LIMIT 1\n ";
$parameters = array('security_id' => $values['muni_substitution_id']);
$securityPriceData = $this->db->query($sql, $parameters);
$muni = new Security();
$muni->setId($values['muni_substitution_id']);
$muni->setSubclass($subclass);
$muni->setIsPreferredBuy(true);
$muni->setName($values['muni_name']);
$muni->setSymbol($values['muni_symbol']);
$muni->setPrice($securityPriceData[0]['price']);
$subclass->setMuniSecurity($muni);
}
$subclassCollection->add($subclass);
}
return $subclassCollection;
}
public function testSellAllSecurities()
{
$security1 = new Security();
$security1->setId(15);
$security1->setQty(200);
$security2 = new Security();
$security2->setId(20);
$security2->setQty(400);
$securityCollection = new SecurityCollection();
$securityCollection->add($security1);
$securityCollection->add($security2);
$this->account->setSecurities($securityCollection);
$this->account->sellAllSecurities();
$qty = 0;
foreach ($this->account->getSecurities() as $security) {
$qty += $security->getQty();
}
$this->assertEquals(0, $qty);
}
public function testGetTotalAmount()
{
$this->assertEquals(1500, $this->subclass->getTotalAmount());
/** @var Subclass $emptySubclass */
$emptySubclass = $this->getMock('Model\\WealthbotRebalancer\\Subclass', null);
$this->assertEquals(0, $emptySubclass->getTotalAmount());
/** @var Subclass $subclassWithSecurity */
$subclassWithSecurity = $this->getMock('Model\\WealthbotRebalancer\\Subclass', null);
$security = new Security();
$security->setAmount(200);
$subclassWithSecurity->setSecurity($security);
$this->assertEquals(200, $subclassWithSecurity->getTotalAmount());
/** @var Subclass $subclassWithMuni */
$subclassWithMuni = $this->getMock('Model\\WealthbotRebalancer\\Subclass', null);
$muni = new Security();
$muni->setAmount(722);
$subclassWithMuni->setMuniSecurity($muni);
$this->assertEquals(722, $subclassWithMuni->getTotalAmount());
}
