$div = $rawData[$i - 1]['top'] + $rawData[$i]['pnl'];
if ($div > 0) {
$rawData[$i]['return'] = $rawData[$i]['top'] / $div;
} else {
$rawData[$i]['return'] = 1;
}
}
//checking if the return for current instrument is not exist and inserting the calculated return.//
$existing_return = PortfolioReturns::model()->findByAttributes(['portfolio_id' => $portfolio_id, 'trade_date' => $rawData[$i]['trade_date'], 'is_prtfolio_or_group' => 1]);
if (count($existing_return) == 0) {
$return = new PortfolioReturns();
$return->portfolio_id = $portfolio_id;
$return->is_prtfolio_or_group = 1;
$return->trade_date = $rawData[$i]['trade_date'];
$return->return = $rawData[$i]['return'];
$return->save();
} else {
$existing_return->return = $rawData[$i]['return'];
$existing_return->save();
}
$i++;
}
?>
<div class="row-fluid"></div>
<?php
$dp = new CArrayDataProvider($rawData, ['pagination' => ['pageSize' => 70, 'params' => ['portfolio' => $portfolio_id, 'dt' => $dt]]]);
$dp->setTotalItemCount(count($rawData));
?>
<h3>Prices and Returns</h3>
<?php
/**
* Creates a new model.
* If creation is successful, the browser will be redirected to the 'view' page.
*/
public function actionCreate()
{
$model = new PortfolioReturns();
// Uncomment the following line if AJAX validation is needed
// $this->performAjaxValidation($model);
if (isset($_POST['PortfolioReturns'])) {
$model->attributes = $_POST['PortfolioReturns'];
if ($model->save()) {
$this->redirect(array('view', 'id' => $model->id));
}
}
$this->render('create', array('model' => $model));
}
public function PortfolioReturnsUpdate($portfolio_id, $client_id, $portfolio_currency)
{
if ($portfolio_id > 0) {
ini_set('max_execution_time', 50000);
//$table_name = "client_".$client_id. "_inst_returns";
$p_ids[] = $portfolio_id;
$all_portfolios = Yii::app()->db->createCommand("select * from portfolios where parrent_portfolio = {$portfolio_id}")->queryAll(true);
while (count($all_portfolios) > 0) {
$new_ids = [];
foreach ($all_portfolios as $ap) {
$p_ids[] = $ap['id'];
$new_ids[] = $ap['id'];
}
$new_p_ids = implode("','", array_unique($new_ids));
$all_portfolios = Yii::app()->db->createCommand("select * from portfolios where parrent_portfolio in ('{$new_p_ids}')")->queryAll(true);
}
$all_p_ids = implode("','", array_unique($p_ids));
Yii::app()->db->createCommand("delete from portfolio_returns where portfolio_id = '{$portfolio_id}'")->execute();
//Trades // and (p.id = $portfolio_id or p.parrent_portfolio = $portfolio_id )
$inst_sql = "select * from ledger l\n inner join instruments i on l.instrument_id = i.id\n inner join portfolios p on p.id = l.portfolio_id\n where l.is_current = 1 and i.is_current = 1 and l.trade_status_id = 2 and l.client_id = '{$client_id}' \n and p.id in ('{$all_p_ids}')\n order by trade_date asc";
$trades = Yii::app()->db->createCommand($inst_sql)->queryAll(true);
if (count($trades) > 0) {
foreach ($trades as $trd) {
$ins_ids[] = $trd['instrument_id'];
}
$insids = implode("','", array_unique($ins_ids));
$portfolio_return_sql = "select p.trade_date, \n if(c.trd is not NULL, c.trd, 0) pnl, \n if(sum(p.price * m.port_val) is not NULL, sum(p.price * m.port_val* cr.{$portfolio_currency}/curs.cur_rate), 0) top,\n if(bc.weight is not NULL, sum(bc.ww)/sum(bc.weight), 0) sums,\n if(c.coupon is not NULL, c.coupon, 0) coupon \n \n from prices p \n inner join currency_rates cr on cr.day = p.trade_date\n inner join instruments i on i.id = p.instrument_id\n inner join cur_rates curs on curs.day = p.trade_date and curs.cur = i.currency\n \n left join\n (select l.trade_date, sum(if(l.trade_type Not in ('2'), l.nominal*l.price * cr.{$portfolio_currency}/curs.cur_rate, 0)) trd,\n if(l.trade_type in ('2'), l.nominal*l.price * cr.{$portfolio_currency}/curs.cur_rate, 0) coupon\n \t\tfrom ledger l\n \n inner join currency_rates cr on cr.day = l.trade_date\n \tinner join instruments i on i.id = l.instrument_id\n \tinner join cur_rates curs on curs.day = l.trade_date and curs.cur = i.currency\n \n \t\twhere l.is_current = 1 and l.trade_status_id = 2 \n \t\tand l.instrument_id in ('{$insids}') and l.client_id = '{$client_id}' and l.portfolio_id in ('{$all_p_ids}') \n \t\tgroup by l.trade_date\n ) c on c.trade_date = p.trade_date \n \n left join\n (\n select trade_date, instrument_id, sum(nominal) port_val \n from ledger \n where is_current = 1 and trade_status_id = 2 \n and instrument_id in ('{$insids}') and client_id = '{$client_id}' and portfolio_id in ('{$all_p_ids}') \n group by trade_date, instrument_id\n ) m on m.trade_date <= p.trade_date and m.instrument_id = p.instrument_id\n \n left join\n (\n select bc.instrument_id, p.trade_date, p.price* bc.weight * cr.{$portfolio_currency}/curs.cur_rate ww, bc.weight\n from benchmark_components bc \n inner join benchmarks bench on bench.id = bc.benchmark_id \n inner join portfolios port on port.benchmark_id = bench.id\n inner join prices p on p.instrument_id = bc.instrument_id\n \n inner join currency_rates cr on cr.day = p.trade_date\n \tinner join instruments i on i.id = p.instrument_id\n \tinner join cur_rates curs on curs.day = p.trade_date and curs.cur = i.currency\n \n where port.id ='{$portfolio_id}'\n ) bc on bc.trade_date = p.trade_date\n \n where p.instrument_id in ('{$insids}') \n group by p.trade_date order by p.trade_date asc";
Yii::app()->db->createCommand("SET SQL_BIG_SELECTS = 1")->execute();
$portfolio_returns = Yii::app()->db->createCommand($portfolio_return_sql)->queryAll(true);
if (count($portfolio_returns) > 0) {
//Yii::app()->db->createCommand("delete from portfolio_returns where portfolio_id = '$portfolio_id'")->execute();
$i = 0;
//for benchmarks//
$return1[$i] = 1;
//$return_bench = 1;
//$return_bench_daily[] = 1;
////////////////////////
foreach ($portfolio_returns as $price) {
$rawData[$i]['id'] = $i;
$rawData[$i]['trade_date'] = $price['trade_date'];
$rawData[$i]['top'] = $price['top'];
$rawData[$i]['pnl'] = $price['pnl'];
$rawData[$i]['coupon'] = $price['coupon'];
$rawData[$i]['return'] = 1;
////For Benchmark///////
$sums[$i] = $price['sums'];
$rawData[$i]['benchmark_return'] = 1;
////////////////////////
$return1[$i] = 1;
if ($i > 0) {
////For Benchmark///////
if ($sums[$i - 1] > 0) {
$return1[$i] = $price['sums'] / $sums[$i - 1];
}
//$return_bench = $return_bench * $return1[$i];
$rawData[$i]['benchmark_return'] = $return1[$i];
////////////////////////
//Portfolio return//
$div = $rawData[$i - 1]['top'] + $rawData[$i]['pnl'];
if ($div > 0) {
$rawData[$i]['return'] = ($rawData[$i]['top'] + $rawData[$i]['coupon']) / $div;
}
}
$return = new PortfolioReturns();
$return->portfolio_id = $portfolio_id;
$return->is_prtfolio_or_group = 1;
$return->trade_date = $rawData[$i]['trade_date'];
$return->return = $rawData[$i]['return'];
$return->benchmark_return = $rawData[$i]['benchmark_return'];
$return->save();
$i++;
}
} else {
///portfolio return is empty////
//Yii::app()->user->setFlash('notice', "There are not confirmed trades available aor prices not found.");
//Yii::app()->user->setFlash('success', "Data1 saved!");
//Yii::app()->user->setFlash('error', "Data2 failed!");
// foreach(Yii::app()->user->getFlashes() as $key => $message) {
// echo '<div class="alert alert-' . $key . '">' . $message . "</div>\n";
//}
//exit;
}
} else {
///treades are not found//
Yii::app()->user->setFlash('notice', "Ledgar information not found.");
}
}
Yii::app()->user->setFlash('success', "Portfolio returns updated.");
//$this->redirect('admin');
}
public function actionPortfolioReturns()
{
$this->layout = 'column1';
$portfolio_id = '';
$dt = '';
$where = ' 1 = 1 ';
if (isset($_REQUEST['portfolio']) && !($_REQUEST['portfolio'] == '')) {
$portfolio_id = $_REQUEST['portfolio'];
}
if (isset($_REQUEST['dt']) && !($_REQUEST['dt'] == '')) {
$dt = $_REQUEST['dt'];
$where .= " and p.trade_date >='{$dt}' ";
}
//if(isset($_REQUEST['portfolio']) && !($_REQUEST['portfolio'] == '')){$portfolio_id = $_REQUEST['portfolio'];}
//if(isset($_REQUEST['dt']) && !($_REQUEST['dt'] == '')){$dt = $_REQUEST['dt'];}
///////////////////////////////////////////////////////////////////////////////////
if ($portfolio_id > 0) {
ini_set('max_execution_time', 50000);
//Trades
$inst_sql = "select * from ledger l\n inner join instruments i on l.instrument_id = i.id\n where l.is_current = 1 and i.is_current = 1 and l.portfolio_id = {$portfolio_id} order by trade_date asc";
$trades = Yii::app()->db->createCommand($inst_sql)->queryAll(true);
if (count($trades) > 0) {
foreach ($trades as $trd) {
$ins_ids[] = $trd['instrument_id'];
}
$insids = implode("','", $ins_ids);
$portfolio_return_sql = "select p.trade_date,\n sum((select sum(if(trade_date=p.trade_date, nominal*price, 0)) from ledger where instrument_id = p.instrument_id)) pnl,\n sum(p.price * (select sum(if(trade_date<=p.trade_date, nominal, 0)) from ledger where instrument_id = p.instrument_id)) top\n from prices p\n where p.is_current = 1 and instrument_id in ('{$insids}') and " . $where . " group by p.trade_date\n order by p.trade_date asc";
$portfolio_returns = Yii::app()->db->createCommand($portfolio_return_sql)->queryAll(true);
if (count($portfolio_returns) > 0) {
$i = 0;
foreach ($portfolio_returns as $price) {
$rawData[$i]['id'] = $i;
$rawData[$i]['trade_date'] = $price['trade_date'];
$rawData[$i]['top'] = $price['top'];
$rawData[$i]['pnl'] = $price['pnl'];
$rawData[$i]['return'] = 1;
if ($i > 0) {
$div = $rawData[$i - 1]['top'] + $rawData[$i]['pnl'];
if ($div > 0) {
$rawData[$i]['return'] = $rawData[$i]['top'] / $div;
} else {
$rawData[$i]['return'] = 1;
}
}
//checking if the return for current instrument is not exist and inserting the calculated return.//
$existing_return = PortfolioReturns::model()->findByAttributes(['portfolio_id' => $portfolio_id, 'trade_date' => $rawData[$i]['trade_date'], 'is_prtfolio_or_group' => 1]);
if (count($existing_return) == 0) {
$return = new PortfolioReturns();
$return->portfolio_id = $portfolio_id;
$return->is_prtfolio_or_group = 1;
$return->trade_date = $rawData[$i]['trade_date'];
$return->return = $rawData[$i]['return'];
$return->save();
} else {
$existing_return->return = $rawData[$i]['return'];
$existing_return->save();
}
$i++;
}
} else {
///portfolio return is empty////
}
} else {
///treades are not found//
}
}
$this->redirect('portfolioReturns/admin');
///////////////////////////////////////////////////////////////////////////////////
// $this->render('portfolio_returns', ['portfolio' => $portfolio, 'dt' => $dt]);
}