public function get__________________old()
{
$toReturn = collect();
$groupFilteredOperations = (new OperationFilter($this->request->all()))->fromVehicle($this->vehicle)->groupByToShareId()->filter()->get();
//->toVehicle($vehicle)
foreach ($groupFilteredOperations as $operationGrouped) {
$SubRow = collect();
foreach ($this->vehicle->shares as $share) {
//$this->generateDirectHoldingLine()
//$currencyRate = $this->CurrencyRate($latestShareValue->dateval);
$toShare = Share::find($operationGrouped->to_share_id);
$toVehicle = $toShare->vehicle;
$SubRow->put('toVehicle', $toVehicle->id . ' - ' . $toVehicle->name);
$SubRow->put('toshare', $toShare->id . ' ' . $toShare->name);
// $commitmentsSum
$operationList = (new OperationFilter($this->request->all()))->fromVehicle($this->vehicle)->fromShare($share)->toShare($toShare)->filter();
$SubRow->put('commitmentsSum_operations', $operationList->get());
// $lastDateRegisteredShareValueBorned = $this->lastDateRegisteredShareValueBorned();
$currencyRate = $this->currencyRate($share, $toShare, $this->request->toDate);
$commitmentsSum = $operationList->sum('commitment');
$SubRow->put('commitmentsSum', $commitmentsSum * $currencyRate);
$SubRow->put('commitmentsSum_calcul', $commitmentsSum . ' x ' . $currencyRate);
$SubRow->put('commitmentsSum_formul', 'commitmentsSum x currencyRate');
// $realizedValue
$operationList = (new OperationFilter($this->request->all()))->fromVehicle($this->vehicle)->fromShare($share)->toShare($toShare)->filter();
$realizedValueList = $operationList->where('cash_transfert', '>', 0);
//->sum('cash_transfert');
$SubRow->put('realizedValue_operations', $realizedValueList->get());
$realizedValue = $realizedValueList->sum('cash_transfert');
//->sum('cash_transfert');
$SubRow->put('realizedValue', $realizedValue);
// $amountCalled
$operationList = (new OperationFilter($this->request->all()))->fromVehicle($this->vehicle)->fromShare($share)->toShare($toShare)->filter();
$amountCalledList = $operationList->where('cash_transfert', '<', 0);
$amountCalled = null;
$amountCalled_formul = '';
foreach ($amountCalledList->get() as $operation) {
$amountCalled += $operation->cash_transfert * $operation->spot_currency;
$amountCalled_formul .= $operation->cash_transfert . ' x ' . $operation->spot_currency . ' +';
}
$amountCalled_formul = rtrim($amountCalled_formul, '+');
// prendre le spot_currendy ///////
$SubRow->put('amountCalled_operations', $amountCalledList->get());
// $amountCalled = $amountCalledList->sum('cash_transfert');
$SubRow->put('amountCalled', $amountCalled);
$SubRow->put('amountCalled_formul', $amountCalled_formul);
// $unrealisedValue : somme des number of shares ($shareSum) * derniere valorisation de l'action($latestShareValue)
$operationList = (new OperationFilter($this->request->all()))->fromVehicle($this->vehicle)->fromShare($share)->toShare($toShare)->filter();
$latestShareValue = ShareValuation::where('share_id', $toShare->id)->orderBy('dateval', 'desc')->first();
$shareSum = $operationList->sum('number_of_shares');
$currencyRate = $this->currencyRate($share, $toShare, $latestShareValue->dateval);
$unrealisedValue = isset($latestShareValue->value) ? $shareSum * $latestShareValue->value * $currencyRate : null;
$SubRow->put('unrealisedValue', $unrealisedValue);
$SubRow->put('unrealisedValue_details', $shareSum . ' x ' . $latestShareValue->value . ' x ' . $currencyRate);
$SubRow->put('unrealisedValue_tooltip', 'shareSum x latestShareValue x tx change');
// $totalValue
$totalValue = $realizedValue + $unrealisedValue;
$SubRow->put('totalValue', $totalValue);
// DPI :: Realised Value / ammount Called
$dpi = null;
if (abs($amountCalled) > 0) {
$dpi = $realizedValue / abs($amountCalled);
}
$SubRow->put('DPI', $dpi);
// RVPI :: UnRealised Value / ammount Called
$rvpi = null;
if (abs($amountCalled) > 0) {
$rvpi = $unrealisedValue / abs($amountCalled);
}
$SubRow->put('RVPI', $rvpi);
// TVPI
$tvpi = null;
if (abs($amountCalled) > 0) {
$tvpi = $totalValue / abs($amountCalled);
}
$SubRow->put('TVPI', $tvpi);
// echo "XIRR = ";
//cash_transfert avec date // unrealized avec date de unrealized (dateval) (appliquer finction excel)
//le premier cash transfert est negatif
// $serie = $operationList->get();
$operationList = (new OperationFilter($this->request->all()))->fromVehicle($this->vehicle)->fromShare($share)->toShare($toShare)->filter()->get();
$amountSerie = [];
$dateSerie = [];
foreach ($operationList as $operation) {
$amountSerie[] = $operation->cash_transfert * $operation->spot_currency;
$dateSerie[] = $operation->date->timestamp;
}
if (isset($latestShareValue->dateval)) {
////$currencyRate = $this->currencyRate($share, $toShare, $latestShareValue->dateval);
$amountSerie[] = $latestShareValue->value * $currencyRate;
$dateSerie[] = $latestShareValue->dateval->timestamp;
}
$financial = new Financial();
$XIRR = $financial->XIRR($amountSerie, $dateSerie, 0.1);
$SubRow->put('XIRR', $XIRR);
$toReturn->push($SubRow);
}
// $toReturn->push($total);
//$toReturn->push($SubRow);
}
return $toReturn;
}
public function generateDirectHoldingLine(Vehicle $vehicle = null, Share $fromShare = null, Share $toShare = null)
{
$SubRow = collect();
//$toShare = Share::find($operationGrouped->to_share_id);
$toVehicle = null;
if ($toShare) {
$toVehicle = $toShare->vehicle;
}
//$lineType =
$lineType = $this->lineType($vehicle, $fromShare, $toShare);
$SubRow->put('lineType', $lineType);
$SubRow->put('fromVehicle', $vehicle);
$SubRow->put('fromShare', $fromShare);
$SubRow->put('toVehicle', $toVehicle);
$SubRow->put('toShare', $toShare);
// $commitmentsSum
$operationList = $this->operationList($vehicle, $fromShare, $toShare);
$SubRow->put('commitmentsSum_operations', $operationList->get());
//to get the curtrency rate
//1// take each operation.
//2// => take from_share cuurency && to_share cuurency
//3// => Apply the function $this->currencyRate($fromShare, $toShare, $this->request->toDate);
//1//
$commitmentsSum = '';
$commitmentsSum_calcul = '';
foreach ($operationList->get() as $operation) {
$fromShare_item = Share::find($operation->from_share_id);
$toShare_item = Share::find($operation->to_share_id);
$currencyRate = $this->currencyRate($fromShare_item, $toShare_item, $this->request->toDate);
$commitmentsSum += $operation->commitment * $currencyRate;
$commitmentsSum_calcul .= '(' . $operation->commitment . ' * ' . $currencyRate . ') +';
}
$commitmentsSum_calcul = rtrim($commitmentsSum_calcul, '+');
// $lastDateRegisteredShareValueBorned = $this->lastDateRegisteredShareValueBorned();
// $currencyRate = $this->currencyRate($this->request->toDate);
//$commitmentsSum= $operationList->sum('commitment');
$SubRow->put('commitmentsSum', round($commitmentsSum, 1));
$SubRow->put('commitmentsSum_calcul', $commitmentsSum_calcul);
//$SubRow->put('commitmentsSum_formul','commitmentsSum x currencyRate');
// $realizedValue
$operationList = $this->operationList($vehicle, $fromShare, $toShare);
$realizedValueList = $operationList->where('cash_transfert', '>', 0);
//->sum('cash_transfert');
$SubRow->put('realizedValue_operations', $realizedValueList->get());
$realizedValue = $realizedValueList->sum('cash_transfert');
//->sum('cash_transfert');
$SubRow->put('realizedValue', round($realizedValue, 1));
// $amountCalled
$operationList = $this->operationList($vehicle, $fromShare, $toShare);
$amountCalledList = $operationList->where('cash_transfert', '<', 0);
$amountCalled = null;
$amountCalled_formul = '';
foreach ($amountCalledList->get() as $operation) {
$amountCalled += $operation->cash_transfert * $operation->spot_currency;
$amountCalled_formul .= $operation->cash_transfert . ' x ' . $operation->spot_currency . ' +';
}
$amountCalled_formul = rtrim($amountCalled_formul, '+');
// prendre le spot_currendy ///////
$SubRow->put('amountCalled_operations', $amountCalledList->get());
// $amountCalled = $amountCalledList->sum('cash_transfert');
$SubRow->put('amountCalled', round($amountCalled, 1));
$SubRow->put('amountCalled_formul', $amountCalled_formul);
/*
** $unrealisedValue
*/
// $unrealisedValue : somme des number of shares ($shareSum) * derniere valorisation de l'action($latestShareValue)
$operationList = $this->operationList($vehicle, $fromShare, $toShare);
//ancien senario $latestShareValue = ShareValuation::where('share_id', $toShare->id)->where('date', '<=', $this->request->toDate) ->orderBy('dateval', 'desc') ->first();
$latestResidualValueOperation = $this->getLatestResidalValue($operationList);
// $unrealisedValue = $shareSum * $latestResidualValueOperation->total_valuation;
// $unrealisedValue = isset($latestResidualValueOperation->value)? $shareSum * $latestResidualValueOperation->total_valuation : null;
// $unrealisedValue = isset($latestShareValue->value)? $shareSum * $latestShareValue->value*$currencyRate : null;
$unrealisedValue = $latestResidualValueOperation->total_valuation;
$SubRow->put('unrealisedValue', round($unrealisedValue, 1));
// $SubRow->put('unrealisedValue_operations',round($unrealisedValue,1));
// $SubRow->put('unrealisedValue_details',$latestResidualValueOperation->total_valuation);
$SubRow->put('unrealisedValue_tooltip', 'latestResidualValueOperation->total_valuation (la devise doit être deja appliquée lors de la soumission du formulaire operation typa residualVAlue)');
// $totalValue
$totalValue = $realizedValue + $unrealisedValue;
$SubRow->put('totalValue', round($totalValue, 1));
// DPI :: Realised Value / ammount Called
$dpi = null;
if (abs($amountCalled) > 0) {
$dpi = $realizedValue / abs($amountCalled);
}
$SubRow->put('DPI', round($dpi, 1) . 'x');
// RVPI :: UnRealised Value / ammount Called
$rvpi = null;
if (abs($amountCalled) > 0) {
$rvpi = $unrealisedValue / abs($amountCalled);
}
$SubRow->put('RVPI', round($rvpi, 1) . 'x');
// TVPI
$tvpi = null;
if (abs($amountCalled) > 0) {
$tvpi = $totalValue / abs($amountCalled);
}
$SubRow->put('TVPI', round($tvpi, 1) . 'x');
// echo "XIRR = ";
//cash_transfert avec date // unrealized avec date de unrealized (dateval) (appliquer finction excel)
//le premier cash transfert est negatif
// $serie = $operationList->get();
$operationList = $this->operationList($vehicle, $fromShare, $toShare);
$amountSerie = [];
$dateSerie = [];
foreach ($operationList->get() as $operation) {
$amountSerie[] = $operation->cash_transfert * $operation->spot_currency;
$dateSerie[] = $operation->date->timestamp;
}
if (isset($latestResidualValueOperation->date)) {
////$currencyRate = $this->currencyRate($share, $toShare, $latestShareValue->dateval);
$amountSerie[] = $latestResidualValueOperation->total_valuation;
$dateSerie[] = $latestResidualValueOperation->date->timestamp;
}
$financial = new Financial();
$XIRR = $financial->XIRR($amountSerie, $dateSerie, 0.1);
$SubRow->put('XIRR', round($XIRR * 100, 1) . '%');
//$SubRow->put('XIRR_operations',$XIRR);
return $SubRow;
}