function populate_cedole()
{
$bond_factory = new Bond();
$today = date('Y-m-d');
// $bonds = $bond_factory->find_all(array('where_clause' => "`stacco` != '0000-00-00' AND `cadenza` > 0 AND `scadenza` > '{$today}'"));
$bonds = $bond_factory->find_all(array('where_clause' => "`cadenza` > 0 AND `scadenza` > '{$today}'"));
// print_r($bonds);
foreach ($bonds as $bond) {
if ($bond->stacco != '0000-00-00') {
$s = strtotime($bond->stacco);
$k = 1;
} else {
$s = strtotime($bond->scadenza);
$k = -1;
}
$d = $today = mktime(0, 0, 0, date('m'), date('d'), date('Y'));
$c = 0;
while ($d >= $today && $d <= $s) {
$m = date('n', $s) + $c;
$d = mktime(0, 0, 0, $m, date('d', $s), date('Y'));
$cedola = new Cedola(array('isin' => $bond->isin, 'stacco' => date('Y-m-d', $d), 'tasso' => $bond->tasso));
$cedola->_force_create = TRUE;
$cedola->_ignore = TRUE;
$cedola->save();
$c += $k * $bond->cadenza;
}
}
}
public function run($context)
{
$bond_count = 0;
$yield_count = 0;
$bond_factory = new Bond();
$today = date('Y-m-d');
foreach ($context->stocks as $stock) {
if ($stock->tipo != 'obbligazione') {
// printf("%s is not a bond, skipping\n", $stock->isin);
continue;
}
// $bonds = $bond_factory->find_all(array('where_clause' => "`stacco` != '0000-00-00' AND `cadenza` > 0 AND `scadenza` > '{$today}'"));
$bond = new Bond();
if (!$bond->find_by_id($stock->isin)) {
// printf("%s does not have a bond, skipping\n", $stock->isin);
continue;
}
// Cleanup yields beyond expiration
$cedola_factory = new Cedola();
$cedole = $cedola_factory->find_all(array('where_clause' => "`isin` = '{$bond->isin}' " . "AND `stacco` > '{$bond->scadenza}'"));
if (count($cedole) > 0) {
foreach ($cedole as $cedola) {
// printf("Deleting yield of stock %s beyond expiration %s\n", $stock->isin, $cedola->stacco);
$cedola->delete();
}
}
// $bonds = $bond_factory->find_all(array('where_clause' => "`stacco` != '0000-00-00' AND `cadenza` > 0 AND `scadenza` > '{$today}'"));
$bonds = $bond_factory->find_all(array('where_clause' => '`cadenza` > 0 ' . "AND `isin` = '{$stock->isin}' " . "AND `zero_coupon` = 0 " . "AND `scadenza` > '{$today}'"));
if (count($bonds) < 1) {
// printf("No valid bond found with isin %s\n", $stock->isin);
continue;
}
$bond = $bonds[0];
if ($bond->stacco != '0000-00-00') {
$s = strtotime($bond->stacco);
$k = 1;
} else {
$s = strtotime($bond->scadenza);
$k = -1;
}
$d = $today = mktime(0, 0, 0, date('m'), date('d'), date('Y'));
$c = 0;
while ($d >= $today && $d <= $s) {
$m = date('n', $s) + $c;
$d = mktime(0, 0, 0, $m, date('d', $s), date('Y'));
$cedola = new Cedola(array('isin' => $bond->isin, 'stacco' => date('Y-m-d', $d), 'tasso' => $bond->tasso));
$cedola->_force_create = TRUE;
$cedola->_ignore = TRUE;
$cedola->save();
$c += $k * $bond->cadenza;
$yield_count++;
// printf("Creating new yield of stock %s on %s\n", $stock->isin, $cedola->stacco);
}
$bond_count++;
}
printf("%s updated %d yields for %d bonds\n", get_called_class(), $yield_count, $bond_count);
}
public function _index()
{
$bond_factory = new Bond();
$today = date('Y-m-d');
$bonds = $bond_factory->find_all(array('where_clause' => "`stacco` != '0000-00-00' AND `cadenza` > 0 AND `scadenza` > '{$today}'"));
// print_r($bonds);
$this->bonds = [];
foreach ($bonds as $bond) {
$bond->stock = new Stock();
$bond->stock->find_by_id($bond->isin);
$s = strtotime($bond->stacco);
if ($bond->cadenza < 12) {
for ($c = 0; $c < 12; $c += $bond->cadenza) {
$b = $c == 0 ? $bond : clone $bond;
$m = (date('m', $s) + $c) % 12;
$m = $m == 0 ? 12 : $m;
$b->stacco = date('Y-m-d', mktime(0, 0, 0, $m, date('d', $s), date('Y')));
$this->bonds[] = $b;
}
} else {
$bond->stacco = date('Y-m-d', mktime(0, 0, 0, date('m', $s), date('d', $s), date('Y')));
$this->bonds[] = $bond;
}
}
function cmp_bond($a, $b)
{
return strtotime($a->stacco) - strtotime($b->stacco);
}
usort($this->bonds, 'cmp_bond');
// print_r($this->bonds);
$this->bonds_by_month = array();
foreach ($this->bonds as $bond) {
$s = strtotime($bond->stacco);
$m = date('m', $s);
if (!isset($this->bonds_by_month[$m])) {
$this->bonds_by_month[$m] = array();
}
$this->bonds_by_month[$m][] = $bond;
}
// print_r($this->bonds_by_month);
}
/**
* Action methods
*/
public function index()
{
$conn = $this->get_connection();
if (empty($_REQUEST['month-from'])) {
$_REQUEST['month-from'] = date('Y-m-00');
}
if (empty($_REQUEST['month-to'])) {
// Default horizon is 6 months
$_REQUEST['month-to'] = date('Y-m-d', mktime(0, 0, 0, date('m') + 6, 0, date('Y')));
}
$portfolio_stock_factory = new PortfolioStock();
$stock_factory = new Stock();
$portfolio_stocks = $portfolio_stock_factory->find_by_query('SELECT `creso_portfolio`.`id`, `creso_portfolio`.`quantita`, `creso_portfolio`.`isin`, `creso_titoli`.`tipo`, `creso_titoli`.`title` ' . 'FROM `creso_portfolio` ' . 'LEFT JOIN `creso_titoli` ON `creso_portfolio`.`isin` = `creso_titoli`.`isin` ' . "WHERE `utente` = '{$conn->escape($_COOKIE['username'])}' " . (!empty($_REQUEST['mercato']) ? "AND `mercato` = '{$conn->escape($_REQUEST['mercato'])}' " : '') . (!empty($_REQUEST['tipo']) ? "AND `tipo` = '{$conn->escape($_REQUEST['tipo'])}' " : '') . (!empty($_REQUEST['divisa']) ? "AND `divisa` = '{$conn->escape($_REQUEST['divisa'])}' " : '') . 'ORDER BY ' . sprintf('`%s` %s ', $this->get_sort('isin'), $this->get_dir()) . "LIMIT {$conn->escape($this->get_start())}, {$conn->escape($this->get_limit())}");
$this->rimborsi = array();
if (count($portfolio_stocks) > 0) {
$bond_factory = new Bond();
foreach ($portfolio_stocks as $item) {
$item->stock = new Stock();
$item->stock->find_by_id($item->isin);
switch ($item->stock->tipo) {
case 'azione':
break;
case 'obbligazione':
$bonds = $bond_factory->find_all(array('where_clause' => "`isin` = '{$item->isin}' " . "AND `scadenza` >= '{$conn->escape($_REQUEST['month-from'])}' " . "AND `scadenza` <= '{$conn->escape($_REQUEST['month-to'])}' "));
if (count($bonds) > 0) {
$item->bond = $bonds[0];
$this->rimborsi[] = clone $item;
}
break;
}
}
if (!function_exists('cmp_stock')) {
function cmp_stock($a, $b)
{
return $a->bond->scadenza - $b->bond->scadenza;
}
}
uasort($this->rimborsi, 'cmp_stock');
}
}
function reinvest()
{
$conn = $this->get_connection();
if (!isset($_REQUEST['month-to'])) {
$_REQUEST['month-to'] = date('Y-m-d', mktime(0, 0, 0, date('m') + self::DEFAULT_ROI_HORIZON_MONTHS, 0, date('Y')));
}
$this->id = $id = $_REQUEST['isin'] = $_REQUEST['id'];
$stock = new Stock();
if ($stock->find_by_id($id)) {
$stock->has_one('bond');
}
$bond_factory = new Bond();
$this->bonds = $bond_factory->find_all(array('where_clause' => "`cadenza` = {$conn->escape($stock->bond->cadenza)}" . " AND `scadenza` LIKE '%-{$conn->escape(strftime('%m', strtotime($stock->bond->scadenza)))}-%'" . " AND `scadenza` >= NOW()", 'limit' => is_numeric($_REQUEST['limit']) ? $_REQUEST['limit'] : 100));
$this->stock = $stock;
$this->sorted_bonds = array();
foreach ($this->bonds as $bond) {
$this->sorted_bonds[$bond->isin] = $bond;
}
$this->isins = array_map(function ($bond) {
return $bond->isin;
}, $this->bonds);
$this->get_roi();
}
private function get_rimborsi()
{
$payments = array();
$conn = $this->get_connection();
$portfolio_stock_factory = new PortfolioStock();
$portfolio_stocks = $portfolio_stock_factory->find_by_query('SELECT `creso_portfolio`.`id`, `creso_portfolio`.`quantita`, `creso_portfolio`.`isin`, `creso_titoli`.`tipo`, `creso_titoli`.`title` ' . 'FROM `creso_portfolio` ' . 'LEFT JOIN `creso_titoli` ON `creso_portfolio`.`isin` = `creso_titoli`.`isin` ' . "WHERE `utente` = '{$conn->escape($_COOKIE['username'])}' " . (!empty($_REQUEST['mercato']) ? "AND `mercato` = '{$conn->escape($_REQUEST['mercato'])}' " : '') . (!empty($_REQUEST['tipo']) ? "AND `tipo` = '{$conn->escape($_REQUEST['tipo'])}' " : '') . (!empty($_REQUEST['divisa']) ? "AND `divisa` = '{$conn->escape($_REQUEST['divisa'])}' " : '') . (!empty($_REQUEST['isin']) ? "AND `creso_portfolio`.`isin` = '{$conn->escape($_REQUEST['isin'])}' " : '') . 'ORDER BY ' . sprintf('`%s` %s ', $this->get_sort('isin'), $this->get_dir()) . "LIMIT {$conn->escape($this->get_start())}, {$conn->escape($this->get_limit())}");
if (count($portfolio_stocks) == 0 && !empty($_REQUEST['isin'])) {
$portfolio_stocks = $this->synthesize_portfolio_stocks();
}
if (count($portfolio_stocks) > 0) {
foreach ($portfolio_stocks as $portfolio_stock) {
$stock = new Stock();
$stock->find_by_id($portfolio_stock->isin);
if ($this->historiful) {
$quantita_history = $this->backfill_with_historical_data($portfolio_stock);
}
switch ($stock->tipo) {
case 'azione':
break;
case 'obbligazione':
$bond_factory = new Bond();
$bonds = $bond_factory->find_all(array('where_clause' => "`isin` = '{$portfolio_stock->isin}' " . "AND `scadenza` >= '{$conn->escape($_REQUEST['month-from'])}' " . "AND `scadenza` <= '{$conn->escape($_REQUEST['month-to'])}' "));
if (count($bonds) > 0) {
$bond = $bonds[0];
$payment = new Payment();
$q = $portfolio_stock->quantita;
foreach ($quantita_history as $date => $quantita) {
if ($date < $bond->scadenza) {
break;
}
$q = $quantita;
}
$payment->stock = $stock;
$payment->portfolio_stock = $portfolio_stock;
$payment->bond = $bond;
$payment->timestamp = strtotime($bond->scadenza);
$payment->importo = $payment->bond->prezzo_rimborso * $q / 100;
$payment->quantita = $q;
$payment->tipo = 'rimborso';
$payments[] = $payment;
}
break;
}
}
}
return $payments;
}
public static function refunds($isin, $month_from, $month_to)
{
$payments = array();
$conn = Db::get_connection();
$stock = new Stock();
if (!$stock->find_by_id($isin)) {
return;
}
switch ($stock->tipo) {
case 'azione':
break;
case 'obbligazione':
$bond_factory = new Bond();
$bonds = $bond_factory->find_all(array('where_clause' => "`isin` = '{$conn->escape($isin)}' " . "AND `scadenza` >= '{$conn->escape($month_from)}' " . "AND `scadenza` <= '{$conn->escape($month_to)}' "));
if (count($bonds) > 0) {
$bond = $bonds[0];
$payment = new Payment();
$payment->stock = $stock;
$payment->bond = $bond;
$payment->timestamp = strtotime($bond->scadenza);
$payment->importo = $payment->bond->prezzo_rimborso;
$payment->tipo = 'rimborso';
$payments[] = $payment;
}
break;
}
return $payments;
}