public function actionRecalculateReturns()
{
ini_set('max_execution_time', 50000);
$user = Users::model()->findByPk(Yii::app()->user->id);
$client_id = $user->client_id;
//$accessable_portfolios1 = $user->accessable_portfolios;
//$accessable_portfolios = implode("', '", explode(",", $accessable_portfolios1));
//$table_name = "client_".$client_id. "_inst_returns";
$default_portfolio_id = $user->default_portfolio_id;
$portfolios = Portfolios::model()->findByPk($default_portfolio_id);
$default_portfolio_currency = $portfolios->currency;
$inst_sql = "select distinct portfolio_id, instrument_id from ledger l\n inner join instruments i on l.instrument_id = i.id\n inner join portfolios port on port.id = l.portfolio_id\n where l.is_current = 1 and i.is_current = 1 and l.trade_status_id = 2 and i.price_uploaded = 1 and l.client_id = {$client_id} \n and (port.id = {$default_portfolio_id} or port.parrent_portfolio = {$default_portfolio_id} ) \n order by trade_date asc";
$trades = Yii::app()->db->createCommand($inst_sql)->queryAll(true);
if (count($trades) > 0) {
foreach ($trades as $trade) {
$portfolio_id = $trade['portfolio_id'];
$instrument_id = $trade['instrument_id'];
$portfolios = Portfolios::model()->findByPk($portfolio_id);
$portfolio_currency = $portfolios->currency;
Returns::model()->calculateIinstrumnetReturn($instrument_id, $portfolio_id, $client_id, $portfolio_currency);
//PortfolioReturns::model()->PortfolioReturnsUpdate(4, 2, 'SEK');
}
}
PortfolioReturns::model()->PortfolioReturnsUpdate($default_portfolio_id, $client_id, $default_portfolio_currency);
$client = Clients::model()->findByPk($client_id);
$client->last_recalculation = new CDbExpression('NOW()');
$client->save();
Yii::app()->user->setFlash('success', "Returns Recalculated!");
foreach (Yii::app()->user->getFlashes() as $key => $message) {
echo '<div class="alert alert-' . $key . '">' . $message . "</div>\n";
}
}
$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}' ";
}
$this->beginWidget('zii.widgets.CPortlet', array('title' => "Selection"));
echo CHtml::beginForm('portfolioReturns', 'post');
?>
<div class="span3">
<?php
echo CHtml::dropDownList('portfolio', $portfolio_id, CHtml::listData(Portfolios::model()->findAll(array('select' => 'id, portfolio', 'order' => 'portfolio')), 'id', 'portfolio'), array('empty' => '-- Select Instrument --'));
?>
</div>
<div class="span3">
<?php
$this->widget('zii.widgets.jui.CJuiDatePicker', array('name' => 'dt', 'options' => array('showAnim' => 'fold', 'dateFormat' => 'yy-mm-dd'), 'htmlOptions' => array('style' => 'height:20px;', 'placeholder' => 'YYYY-MM-DD')));
?>
</div>
<div class="span1">
<?php
echo CHtml::submitButton('Calculate Return', array('submit' => $baseurl . '/prices/PortfolioReturns', 'class' => "btn btn-primary"));
?>
</div>
<br />
<?php
echo CHtml::endForm();
<?php
$portfolio_id = $_REQUEST['portfolio'];
$client_id = $_REQUEST['client_id'];
$start_date = $_REQUEST['start_date'];
$end_date = $_REQUEST['end_date'];
$table_name = "client_" . $client_id . "_inst_returns";
$portfolios = Portfolios::model()->findByPk($portfolio_id);
$portfolio_currency = $portfolios->currency;
$month_ytd_start = date('Y-01-01');
$month3_start = date("Y-m-d", strtotime("-3 month", strtotime($end_date)));
$month6_start = date("Y-m-d", strtotime("-6 month", strtotime($end_date)));
$month9_start = date("Y-m-d", strtotime("-9 month", strtotime($end_date)));
$month1y_start = date("Y-m-d", strtotime("-1 years", strtotime($end_date)));
/////////////////////////////////////////////////////////////////
$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));
////////////////////////////////////////////////////////////////
$instruments_query = "select distinct i.id, i.instrument, l.portfolio_id from instruments i inner join ledger l on l.instrument_id = i.id \n where l.is_current=1 and l.trade_status_id = 2 \n and l.portfolio_id in ('{$all_p_ids}')\n and l.client_id = '{$client_id}' ";
$instruments = Yii::app()->db->createCommand($instruments_query)->queryAll(true);
$tbl_rows = '';
?>
</div>
<div class="row form-group">
<div class="col-sm-2 control-label">
<?php
echo $form->labelEx($model, 'default_portfolio_id');
?>
</div>
<?php
//echo $form->textField($model,'default_portfolio_id');
?>
<div class="col-sm-2">
<?php
$ports = Portfolios::model()->findAll(['condition' => 'client_id = :client_id', 'params' => array(':client_id' => $model->client_id)]);
$list = CHtml::listData($ports, 'id', 'portfolio');
echo $form->dropDownList($model, 'default_portfolio_id', $list, ['id' => 'portfolio', 'empty' => '-- Select --', 'class' => "form-control"]);
?>
</div>
<?php
echo $form->error($model, 'default_portfolio_id');
?>
</div>
<div class="row buttons">
<div class="col-sm-2 control-label"></div>
<div class="col-sm-2">
<?php
//echo CHtml::submitButton($model->isNewRecord ? 'Create' : 'Save');
?>
function portfolioUpdateoncreate($id, $values)
{
$user_data = Users::model()->findByPk(Yii::app()->user->id);
$client_id = $user_data->client_id;
$portfolio_id = 0;
//$ledger = Ledger::model()->findByPk($id);
//if(isset($instrument_id)){$portfolio_id = $values['ledger']['portfolio_id'];} //else{$portfolio_id = $existing_trades->portfolio_id;}
$portfolio_id = $values['ledger']['portfolio_id'];
$instrument_id = $values['ledger']['instrument_id'];
$trade_currency = $values['ledger']['currency'];
$portfolios = Portfolios::model()->findByPk($portfolio_id);
$portfolio_currency = $portfolios->currency;
//Returns::model()->calculateIinstrumnetReturn($instrument_id, $portfolio_id = 0, $trade_rate, $trade_currency, $client_id, $portfolio_currency);
//PortfolioReturns::model()->PortfolioReturnsUpdate($portfolio_id);
//$trade_rate, $trade_currency,
Returns::model()->calculateIinstrumnetReturn($instrument_id, $portfolio_id, $client_id, $portfolio_currency);
$step_completed = $user_data->step_completed;
if ($user_data->user_role == 2 && $step_completed < 5) {
$user_data->step_completed = 5;
$user_data->save();
}
}
/**
* Returns the data model based on the primary key given in the GET variable.
* If the data model is not found, an HTTP exception will be raised.
* @param integer $id the ID of the model to be loaded
* @return Portfolios the loaded model
* @throws CHttpException
*/
public function loadModel($id)
{
$model = Portfolios::model()->findByPk($id);
if ($model === null) {
throw new CHttpException(404, 'The requested page does not exist.');
}
return $model;
}
public function instrumnetReturnsUpdate($instrument_ids)
{
if (count($instrument_ids) > 0) {
ini_set('max_execution_time', 50000);
$user = Users::model()->findByPk(Yii::app()->user->id);
$client_id = $user->client_id;
foreach ($instrument_ids as $instrument_id) {
$portfolio_id = 0;
$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.trade_status_id = 2 and i.id = {$instrument_id} and l.client_id = {$client_id} order by trade_date asc";
$trades = Yii::app()->db->createCommand($inst_sql)->queryAll(true);
if (count($trades) > 0) {
foreach ($trades as $trade) {
$portfolio_id = $trade['portfolio_id'];
//$instrument_id = $trade['instrument_id'];
$portfolios = Portfolios::model()->findByPk($portfolio_id);
$portfolio_currency = $portfolios->currency;
Returns::model()->calculateIinstrumnetReturn($instrument_id, $portfolio_id = 0, $client_id, $portfolio_currency);
}
/*
$portfolio_id = $trades[0]['portfolio_id'];
//Prices and returns calculations
$prices_sql = "select distinct p.trade_date, p.price,
(select sum(if(trade_date<=p.trade_date, nominal, 0)) from ledger where instrument_id = p.instrument_id and ledger.trade_status_id = 2) nominal,
(select sum(if(trade_date=p.trade_date, nominal*price, 0)) from ledger where instrument_id = p.instrument_id and ledger.trade_status_id = 2) pnl
from prices p
where p.is_current = 1 and p.instrument_id = $instrument_id
order by p.trade_date asc";
//and p.trade_date >='$dt'
$prices = Yii::app()->db->createCommand($prices_sql)->queryAll(true);
if(count($prices)>0){
$i = 0;
foreach($prices as $price){
$rawData[$i]['id'] = $i;
$rawData[$i]['trade_date'] = $price['trade_date'];
$rawData[$i]['price'] = $price['price'];
$rawData[$i]['nominal'] = $price['nominal'];
$rawData[$i]['pnl'] = $price['pnl'];
$rawData[$i]['return'] = 1;
//$rawData[$i]['chart'] = 1;
if($i>0 && $rawData[0]['price'] !== 0){
// $rawData[$i]['chart'] = $rawData[$i]['price']/$rawData[0]['price'];
$div = $rawData[$i-1]['nominal'] * $rawData[$i-1]['price']+ $rawData[$i]['pnl'];
if($div>0){
$rawData[$i]['return'] = ($rawData[$i]['nominal'] * $rawData[$i]['price'])/$div;
}else{
$rawData[$i]['return'] = 1;
}
}
//checking if the return for current instrument is not exist and inserting the calculated return.//
$existing_return = Returns::model()->findByAttributes(['instrument_id'=>$instrument_id, 'trade_date' =>$rawData[$i]['trade_date']]);
if(count($existing_return)==0){
$return = new Returns;
$return->instrument_id = $instrument_id;
$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++;
}
}*/
}
//PortfolioReturns::model()->PortfolioReturnsUpdate($portfolio_id);
}
}
}