<?php
/**
 * Top Performer Starter-Depot
 *
 * -
 *
 * 1st author:
 *   Angelos Paraskevaidis <angelos.paraskevaidis@factset.com>
 * Bugfix & Modifications:
 *   N/A
 *
 * @package   funds
 * @version   $Rev: 7523 $
 * @since     $Date: 2020-11-16 11:06:55 +0100 (Mon, 16 Nov 2020) $
 * @copyright Copyright (c) 2006 Factset Digital Solutions
 *            (http://www.factset.com/)
 * @author    $Author: Angelos.Paraskevaidis@factset.com $
 *
 */

## Custom XSS Dropper (special filter rules -> filter.class, all other input values are checked with the general XSS Dropper)
$aXSSDropper = array(
    'sTab' => 'TAB',
    'iAjax'=> 'AJAX',
);

##############################################
## Include API and standard includes
include( 'config/includes.inc' );

##############################################

OnvI18nBeginXSS( 'funds/CIWI1051' );

## Get Input
$sTab          = getMdgParameterXSS($oMdgSession, 'TAB', 1);
$iAjax          = getMdgParameterXSS($oMdgSession, 'AJAX', 0);
//$iNoMysql    = getMdgParameterXSS($oMdgSession, 'nomysql', NULL);

$iNoMysql = 0;
$sPreliveTable = '';
$sShowHtml = 'full';
$sJsFiles  = 'acehj';

if ($iAjax == 1) {
    $sShowHtml = 'AjaxData';
}
//$sPreliveTable   = '_prelive';
## Filter Input
foreach ($aXSSDropper as $sVar => $sRegex) {
    ${$sVar} = Filter::getRegex($sVar,${$sVar},$sRegex);
}

$iCat = (string)$sTab;
$aCategory = array();
$aCategoryOther = [];
$aFundsOther = [];
$aTopFunds = [];
$aCategories = [];
$aFunds = [];
$aEtf = [];
$aAmountTopFunds = [];
$aAmountTopEtfs = [];
$aAmountOtherFunds = [];
$aAmountOtherEtfs = [];
####TOP All Funds Categories####

$redis = loadRedis();

$sCacheKey = generate_cache_key('CIWI1051Cat'.$iCat.'Category','');
switch ($_SERVER['ENVIRONMENT']) {
    case 'dev':
        $sCacheKey .= 'Dev';
        break;
    case 'show':
        $sCacheKey .= 'Show';
        break;
}

$aCategory = [];
$entityManager = null;
try
{
    if ($iNoMysql==1)
    {
        throw new Exception('Test Mysql disconnected');
    }
    $entityManager = openDBConnection();
    if (!$entityManager) {
        throw new Exception('Could not open DB connection');
    }

    $queryBuilder = $entityManager->getConnection()->createQueryBuilder();
    $queryBuilder
        ->select(['id', 'tab', 'name', 'sort', 'other'])
        ->from('intranet__admin_fonds_categories'.$sPreliveTable)
        ->where('tab = ?')
        ->andWhere('sort < ?')
        ->setParameter(0, $iCat, \PDO::PARAM_INT)
        ->setParameter(1, 99, \PDO::PARAM_INT)
        ->orderBy('other', 'ASC')
        ->addOrderBy('sort', 'ASC');

    $sRawQuery = getRawQuery($queryBuilder);
    $aCategory = $queryBuilder->execute()->fetchAll();
    $aCategory = decryptDB($aCategory);
    if (!empty($trace)) {
        $trace[] = [
            'file' => basename(__FILE__) . ':' . __LINE__,
            'query' => $sRawQuery,
            'data' => $aCategory,
        ];
    }

    if ($statusRedis) {
        $redis->setex($sCacheKey, $aCacheLifetime['1h'], serialize($aCategory));
    }

    mdgTrace($trace);
    mdgTrace($aCategory);
}
catch (Exception $e)
{
    logError('Mysql Problem: '.$e->getMessage());
}
finally
{
    closeDBConnection($entityManager);
}

foreach ($aCategory as $sKey => $aData) {
    $sCategory = 'None';
    $sCategoryName = $aData['name'];
    $sCategoryId = $aData['id'];
    switch ($aData['tab']) {
        case 1:
            $sCategory = 'Aktienfonds';
            break;
        case 2:
            $sCategory = 'Rentenfonds';
            break;
        case 3:
            $sCategory = 'VMF';
            break;
    }
    $sCacheKey = generate_cache_key('CIWI1051Cat'.$iCat.'Funds','');
    switch ($_SERVER['ENVIRONMENT']) {
        case 'dev':
            $sCacheKey .= 'Dev';
            break;
        case 'show':
            $sCacheKey .= 'Show';
            break;
    }
    $entityManager = null;
    try {
        if ($iNoMysql == 1) {
            throw new Exception('Test Mysql disconnected');
        }

        $entityManager = openDBConnection();
        if (!$entityManager) {
            throw new Exception('Could not open DB connection');
        }

        $queryBuilder = $entityManager->getConnection()->createQueryBuilder();
        $queryBuilder
            ->select('*')
            ->from('intranet__admin_fonds_fonds' . $sPreliveTable)
            ->where('starter = ?')
            ->andWhere('category = ?')
            ->orWhere('category_alternative = ?')
            ->setParameter(0, 1, \PDO::PARAM_INT)
            ->setParameter(1, $sCategoryId, \PDO::PARAM_INT)
            ->setParameter(2, $sCategoryId, \PDO::PARAM_INT);

        $aTopFunds = $queryBuilder->execute()->fetchAll();
        $aTopFunds = decryptDB($aTopFunds);
        if (!empty($trace)) {
            $trace[] = [
                'file' => basename(__FILE__) . ':' . __LINE__,
                'query' => getRawQuery($queryBuilder),
                'data' => $aTopFunds,
            ];
        }

        $aData['Instruments'] = $aTopFunds;
        $aCategories[] = $aData;
    }
    catch (Exception $e)
    {
        if ($statusRedis) {
            $aTopFunds            = $redis->get($sCacheKey);
            $aData['Instruments'] = $aTopFunds;
            $aCategories[]        = $aData;
        }
        #$header    = 'MIME-Version: 1.0' . "\r\n";
        #$header   .= 'Content-type: text/html; charset=iso-8859-1' . "\r\n";
        #$nachricht = '<html><head><title></title></head><body>File: '.__FILE__.' ('.__LINE__.')<br /><br /><pre>'.var_export($e,true).'</pre><br /><br /><pre>'.var_export($_SERVER,true).'</pre></body></html>';
        #@mail('daniel.schmueck@factset.com','Targobank Intranet Mysql Problem INTRA1000 ('.$_SERVER['ENVIRONMENT'].')',$nachricht,$header);
        logException($e,__FILE__,__LINE__);
    }
    finally
    {
        closeDBConnection($entityManager);
    }

}
mdgTrace($aCategories);

foreach ($aCategories as $sItem => $aValues) {
    if (empty($aValues['Instruments'])) {
        unset($aCategories[$sItem]);
    }
}
$aCategories = array_values($aCategories);

#### Get Instrument Data ####
foreach ($aCategories as $sItem => $aCategoryDetails) {
    foreach ($aCategoryDetails['Instruments'] as $sKey => $aDetails) {
        $oGeneralDescriptor = MdgObject::getArray($oMdgSession, array('/general/descriptor.csv?ID_OSI='.$aDetails['instrument'].'&VERSION=1'));
        $oFundsDescriptor = MdgObject::getArray($oMdgSession, array('/funds/descriptor.csv?ID_INSTRUMENT='.$aDetails['instrument'].'&VERSION=2'));
        mdgTrace($oGeneralDescriptor);
        mdgTrace($oGeneralDescriptor);
        $aGetIdNotation =  array('ID_INSTRUMENT' => $aDetails['instrument'], 'CODE_MARKET' => $sFundsPricesCodeMarket);
        if ($oFundsDescriptor[0]->IS_EXCHANGE_TRADED == 1) {
            $aGetIdNotation =  array('ID_INSTRUMENT' => $aDetails['instrument'], 'CODE_MARKET' => $sEtfsPricesCodeMarket);
        }
        $iIdNotation = MDGTOOLS::getMdgId($oMdgSession, 'ID_NOTATION', $aGetIdNotation);
        $aFundsPerformanceRequest[$sKey] = '/funds/performance_list?VERSION=2&FORMAT=0&TIME_SPAN=1Y&USE_ALTERNATIVE_CURRENCY_FIGURES=1&ID_NOTATION=' . $iIdNotation;
        $aDetails['security_type'] = $oGeneralDescriptor[0]->ID_SECURITY_TYPE;
        $aDetails['is_exchange_trade'] = $oFundsDescriptor[0]->IS_EXCHANGE_TRADED;
        $aDetails['name'] = $oGeneralDescriptor[0]->NAME_SECURITY_OFFICIAL;
        $aCategoryDetails['Instruments'][$sKey] = $aDetails;
    }

    $aFundsPerformance = MdgObject::getArray($oMdgSession, $aFundsPerformanceRequest);
    mdgTrace($aFundsPerformance);

    #### Add Performance And Separate Funds - Etf's ####
    foreach ($aCategoryDetails['Instruments'] as $sKey => $aDetails) {
        $idFundNotation = '';
        $iPerformance = '';
        if (!empty($aFundsPerformance[$sKey]->ID_NOTATION)) {
            $idFundNotation = $aFundsPerformance[$sKey]->ID_NOTATION;
            $iPerformance = $aFundsPerformance[$sKey]->PERFORMANCE_TIME_SPAN_PCT[0];
        }
        $aDetails['notation'] = $idFundNotation;
        $aDetails['performance'] = $iPerformance;
        $aCategoryDetails['Instruments'][$sKey] = $aDetails;
        if ($aCategoryDetails['Instruments'][$sKey]['is_exchange_trade'] == 1) {
            $aCategoryDetails['Instruments']['Etf'][$sKey] = $aCategoryDetails['Instruments'][$sKey];
        } else {
            $aCategoryDetails['Instruments']['Funds'][$sKey] = $aCategoryDetails['Instruments'][$sKey];
        }

        unset($aCategoryDetails['Instruments'][$sKey]);

    }
    if (!empty($aCategoryDetails['Instruments']['Funds'])) {
        $aCategoryDetails['Instruments']['Funds'] = array_values($aCategoryDetails['Instruments']['Funds']);
        $fundKeys = array_column($aCategoryDetails['Instruments']['Funds'], 'performance');

        array_multisort($fundKeys, SORT_DESC, $aCategoryDetails['Instruments']['Funds']);
    }
    if (!empty($aCategoryDetails['Instruments']['Etf'])) {
        $aCategoryDetails['Instruments']['Etf'] = array_values($aCategoryDetails['Instruments']['Etf']);
        $etfKeys = array_column($aCategoryDetails['Instruments']['Etf'], 'performance');

        array_multisort($etfKeys, SORT_DESC, $aCategoryDetails['Instruments']['Etf']);
    }

    $aCategories[$sItem] = $aCategoryDetails;

}

#### Separate Top Categories with The rest ####
$aTopCategories = [];
$aOtherCategories = [];
foreach ($aCategories as $aCategory) {
    if ($aCategory['other'] === '1') {
        $aOtherCategories[] = $aCategory;
    } else {
        $aTopCategories[] = $aCategory;
    }
}

foreach (array_chunk($aTopCategories, 2) as $aRowCategories) {
    $iMaxFunds = 0;
    $iMaxEtfs = 0;
    foreach ($aRowCategories as $aTopCategory) {
        if (!empty($aTopCategory['Instruments']['Funds'])) {
            $iMaxFunds = max($iMaxFunds, count($aTopCategory['Instruments']['Funds']));
        }
        if (!empty($aTopCategory['Instruments']['Etf'])) {
            $iMaxEtfs = max($iMaxEtfs, count($aTopCategory['Instruments']['Etf']));
        }
    }
    foreach ($aRowCategories as $aTopCategory) {
        $aAmountTopFunds[] = $iMaxFunds;
        $aAmountTopEtfs[] = $iMaxEtfs;
    }
}

foreach (array_chunk($aOtherCategories, 2) as $aRowCategories) {
    $iMaxOtherFunds = 0;
    $iMaxOtherEtfs = 0;
    foreach ($aRowCategories as $aOtherCategorie) {
        if (!empty($aOtherCategorie['Instruments']['Funds'])) {
            $iMaxOtherFunds = max($iMaxOtherFunds, count($aOtherCategorie['Instruments']['Funds']));
        }
        if (!empty($aOtherCategorie['Instruments']['Etf'])) {
            $iMaxOtherEtfs = max($iMaxOtherEtfs, count($aOtherCategorie['Instruments']['Etf']));
        }
    }
    foreach ($aRowCategories as $aOtherCategorie) {
        $aAmountOtherFunds[] = $iMaxOtherFunds;
        $aAmountOtherEtfs[] = $iMaxOtherEtfs;
    }
}

mdgTrace($aAmountTopFunds);
mdgTrace($aAmountTopEtfs);
mdgTrace($aAmountOtherFunds);
mdgTrace($aAmountOtherEtfs);
mdgTrace($aTopCategories);
mdgTrace($aOtherCategories);

$iTopAmount = count($aTopCategories);
$iOtherAmount = count($aOtherCategories);

$sLinkAppendix = $DEFAULT_PARAMS_END;

$sLinkAppendixPrint  = $sLinkAppendix;
$sLinkAppendixPrint .= '&amp;PDF=1';

OnvI18nEndXSS();

// do a redirect to make a browser back possible
if (isset($_REQUEST['SUBMITTED']) && (int)$_REQUEST['SUBMITTED']===1) {
    doRedirect($TOOL_URL['FUN'].'INTRA1051.html?'.$sLinkAppendix);
}
## output template
mdgTraceFile( 'templates/ciminfo/funds/CIWI1051.tmpl' );
?>