// $porfolio_amount[$i] = 0;
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;
}
// $porfolio_amount[$i] = $porfolio_amount[$i] + $rawData[$i]['nominal'] * $rawData[$i]['price'];
// $amount_traded[$i] = $amount_traded[$i] + $rawData[$i]['pnl'];
}
//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++;
}
//////////////////Portfolio calculation////////////////////
/*
if($i == 0){
$rawData[$i]['portfolio'] = 1;
}else{
public function actionFullupload()
{
/*
$price_update = Prices::model()->findByAttributes(['trade_date'=>'0000-00-00', 'instrument_id' =>43],
[
'condition'=>'price!=:price',
'params'=>array('price'=>17.420),
]
);
var_dump($price_update);
exit;
*/
$model = new Uploads();
//$path = Yii::app()->basePath.'../../uploads/';
$path = Yii::getPathOfAlias('webroot') . '/uploads/';
if (isset($_POST['Uploads'])) {
Yii::import('ext.phpexcel.XPHPExcel');
XPHPExcel::init();
ini_set('max_execution_time', 150000);
ini_set("memory_limit", "128M");
require_once Yii::app()->basePath . '/extensions/XLSXReader/XLSXReader.php';
//OKarray(2) { ["Uploads"]=> array(2) { ["instrument_id"]=> string(2) "12" ["upload_description"]=> string(5) "sfggs" } ["yt0"]=> string(6) "Upload" }
$model->attributes = $_POST['Uploads'];
if ($upload_file = self::uploadMultifile($model, 'upload_file', $path)) {
$model->upload_file = implode(",", $upload_file);
}
$model->user_id = Yii::app()->user->id;
//$instrument_id = $model->instrument_id;
//////////////////////////////////////////
if ($model->validate()) {
//Upload File //
if ($model->save()) {
$upload_file_id = Yii::app()->db->getLastInsertID();
$csvFile = CUploadedFile::getInstance($model, 'upload_file', '../../uploads/');
$tempLoc = Yii::getPathOfAlias('webroot') . '/uploads/' . $model->upload_file;
$xlsx = new XLSXReader($tempLoc);
$data = $xlsx->getSheetData('Sheet1');
$instruments = Instruments::model()->findAll(array('select' => 'id, instrument'));
$instruments_for_returns_update = [];
foreach ($data as $dat) {
$trade_date = gmdate('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($dat['0']));
$instrument_name = trim($dat['1']);
$price = $dat['2'];
$currency = $dat['3'];
$instrument = Instruments::model()->findByAttributes(['instrument' => $instrument_name, 'is_current' => 1]);
if ($instrument) {
$instrument_id = $instrument->id;
$instruments_for_returns_update[] = $instrument_id;
} else {
$new_instrument = new Instruments();
$new_instrument->instrument = $instrument_name;
$new_instrument->price_uploaded = 1;
$new_instrument->currency = $currency;
$new_instrument->save();
$instrument_id = $new_instrument->id;
$instruments_for_returns_update[] = $instrument_id;
}
$existing_record = Prices::model()->findByAttributes(['trade_date' => $trade_date, 'instrument_id' => $instrument_id]);
if ($existing_record) {
if ($existing_record->price !== $price) {
$existing_record->price = $price;
$existing_record->upload_file_id = $upload_file_id;
$existing_record->save();
}
} else {
$new_price = new Prices();
$new_price->instrument_id = $instrument_id;
$new_price->trade_date = $trade_date;
$new_price->price = $price;
$new_price->upload_file_id = $upload_file_id;
//$new_price->name = $instrument_name;
$new_price->save();
}
}
$unique_instruments_for_returns_update = array_unique($instruments_for_returns_update);
Returns::model()->instrumnetReturnsUpdate($unique_instruments_for_returns_update);
Yii::app()->user->setFlash('success', "Prices Uploaded!");
@chmod($tempLoc, 0777);
@unlink($tempLoc);
//unlink(Yii::getPathOfAlias('webroot').'/uploads/'.$model->upload_file);
//$this->redirect(array('view','id'=>$model->id));
$user_data = Users::model()->findByPk(Yii::app()->user->id);
$step_completed = $user_data->step_completed;
if ($user_data->user_role == 2 && $step_completed < 2) {
$user_data->step_completed = 1;
$user_data->save();
$this->redirect(Yii::app()->baseUrl . '/site/admin');
}
//else{
// $this->redirect(Yii::app()->baseUrl.'/site/admin');
// $this->render('overview', ['user_data' => $user_data]); }
}
}
///////////////////////////////////////////
}
$this->render('upload_form', array('model' => $model));
}
/**
* 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 Returns the loaded model
* @throws CHttpException
*/
public function loadModel($id)
{
$model = Returns::model()->findByPk($id);
if ($model === null) {
throw new CHttpException(404, 'The requested page does not exist.');
}
return $model;
}
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();
}
}
public function actionRecalculateReturns11()
{
ini_set('max_execution_time', 50000);
$user = Users::model()->findByPk(Yii::app()->user->id);
$client_id = $user->client_id;
$table_name = "client_" . $client_id . "_inst_returns";
$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.price_uploaded = 1 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'];
$prices_sql = "select distinct p.trade_date, p.price,\n (select sum(if(trade_date<=p.trade_date, nominal, 0)) from ledger where instrument_id = p.instrument_id and is_current = 1 and trade_status_id = 2 and client_id = {$client_id}) nominal,\n (select sum(if(trade_date=p.trade_date, nominal*price, 0)) from ledger where instrument_id = p.instrument_id and is_current = 1 and ledger.trade_status_id = 2 and client_id = {$client_id}) pnl\n from prices p\n where p.is_current = 1 and p.instrument_id = {$instrument_id} \n 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);
}
}
$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";
}
}
public function generateInventoryReport()
{
$res = '';
if (Input::get('status') == 'All') {
$res = Item::get();
$this->allInv($res);
} elseif (Input::get('status') == 'Broken') {
$res = Broken::get();
$this->broken($res);
} elseif (Input::get('status') == 'Returned') {
$res = Returns::get();
$this->returned($res);
}
/*elseif(Input::get('status') == 'Reserved')
{
$res = Broken::get();
}*/
return $res;
}
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);
}
}
}