/** * 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 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; }