<?php

namespace ggbnportal\controllers;

use Yii;
use common\controllers\CommonSearchController;
use ggbnportal\models\SearchForm;
use ggbnportal\models\WebsitesearchForm;
use yii\data\Pagination;
use yii\helpers\Url;
use ggbn\SingleRecordManager;
use ggbn\FormSelectionQueryManager;
use ggbn\SOLRQueryManager;
use yii\helpers\Html;
use common\models\CartForm;
use ggbnportal\controllers\DBInterface;


class SearchController extends CommonSearchController {
	
	// --------------------------------------------------------
	
	// these are the attributes that will be used for calculation
	// and also these ones that are mandatory shown
	// do not change this unless you also adapt the whole programm
	// needed for single record output
	private $requestedAttributes = array (
			'unitID',
			'sampletype',
			'tripleidstoreid',
			'collectioncode',
			'collectioncode2',
			'institutioncode',
			'institutioncode2' 
	);
	
	// for pagination output
	private $key_link = 'fullScientificName';
	private $recordFilters = array (
			'unitID',
			'collectioncode',
			'institutioncode' 
	);
	private $submitURL;
	private $submitWebsearch;
	
	// we store all list for the drop down fields here
	private $_selectionLists;
	private $dropDownAttributes;
	private $knownLParams = [ 
			'country',
			'geneticlocus',
			'continent',
		
			'ocean',
			'sampletype' 
	];
	private $knownParams = [ 
			'fullScientificName',
			'familyName',
			'phylumName',
			'className',
			'orderName',
			'genusName',
			'kingdom',
			'sequenceaccessionIdentifier',
			'unitID',
			'locality',
			'collectors',
			'collectornumber',
			'institution',
			'voucherCol',
			'collectionYearFrom',
			'collectionYearTo',
			'latitudeTo',
			'latitudeFrom',
			'longitudeTo',
			'longitudeFrom',
			'cites',
			'sampleavailability',
			'institutioncode',
			'collectioncode'
	];
	
// 	// ------------ functions --------------------
// 	private function collectDropDownAttributesLists($model) {
// 		if (! isset ( $this->dropDownAttributes )) {
// 			$this->dropDownAttributes = array ();
			
// 			foreach ( $model->getParameters () as $name => $list ) {
// 				if (isset ( $list ['type'] ) && $list ['type'] == 'select') {
// 					array_push ( $this->dropDownAttributes, $name );
// 				}
// 			}
// 		}
// 		if (! isset ( $this->_selectionLists )) {
// 			$this->_selectionLists = FormSelectionQueryManager::getAllLists ( $this->dropDownAttributes, $model->getSelectionListNonChosen () );
// 		}
// 	}
	
	// ------------------- controller actions ----------------
	
	/**
	 * this does all the thing for searching the index:
	 */
	public function actionIndex() {
		// Yii::info ( "actionIndexSearch" );
		$model = new SearchForm ();
		$model->attributes = Yii::$app->request->post ();
		
		$modelwebsite = new WebsitesearchForm ();
		// we get all the lists for the drop-down lists in the form -- why??
		try {
			$this->_selectionLists=CommonSearchController::collectDropDownAttributesLists ( $model );
		} catch ( \Solarium\Exception\HttpException $e ) {
			Yii::$app->mailer->compose ()->setFrom (Yii::$app->params ['noreplyMail'] )->setTo ( Yii::$app->params ['feedbackMail'] )->setSubject ( 'Error occured' )->setTextBody ( $e )->send ();
			return $this->render ( '/site/error', [ 
					"name" => "Oooops",
					"message" => "The SOLR instance seems to be gone.",
					"exception" => $e 
			] );
		}
		
		// Rukeia 23-02-2013: we store all form parameter values in $filters
		$filters = array ();
		if (isset ( $_REQUEST ['SearchForm'] )) {
			$searchform = $_REQUEST ['SearchForm'];
		} else
			$searchform = [ ];
		
		foreach ( $this->knownLParams as $kp ) {
			if (isset ( $searchform [$kp] ) && strlen ( $searchform [$kp] ) > 0) {
				if (strcasecmp ( $searchform [$kp], "---" ) > 0)
					$filters [$kp] = $searchform [$kp];
			}
			if (isset ( $_REQUEST [$kp] ) && strlen ( $_REQUEST [$kp] ) > 0) {
				if (strcasecmp ( $_REQUEST [$kp], "---" ) > 0)
					$filters [$kp] = $_REQUEST [$kp];
			}
		}
		
		if (isset ( $searchform ['countrycode'] ) && strlen ( $searchform ['countrycode'] ) > 0) {
			$filters ['isocountrycode'] = $searchform ['countrycode'];
		}
		if (isset ( $_REQUEST ['countrycode'] ) && strlen ( $_REQUEST ['countrycode'] ) > 0) {
			$filters ['isocountrycode'] = $_REQUEST ['countrycode'];
		}
		
		foreach ( $this->knownParams as $kp ) {
			if (isset ( $searchform [$kp] ) && strlen ( $searchform [$kp] ) > 0) {
				$filters [$kp] = $searchform [$kp];
			}
			if (isset ( $_REQUEST [$kp] ) && strlen ( $_REQUEST [$kp] ) > 0) {
				$filters [$kp] = $_REQUEST [$kp];
			}
		}
		
		$submitWebsearch = Yii::$app->getUrlManager ()->createUrl ( [ 
				'websitesearch/search' 
		] );
		// Yii::info ( "Filters: " . implode ( $filters, "\n" ) );
		if ($filters) {
			if ($model->validate ()) {
				$this->redirect ( Url::to ( array_merge ( [ 
						'search/result' 
				], $filters ) ) );
			} else {
				$errors = $model->errors;
				$shopM = new CartForm();
				if (! Yii::$app->user->isGuest) {
					$shoppingCart = sizeof ( DBInterface::getShoppingCart ( Yii::$app->user->identity->id ) );
				} else {
					$shoppingCart = '';
				}
				return $this->render ( 'show/listView', [ 
						'model' => $model,
						'selectionLists' => $this->_selectionLists,
						"filters" => $filters,
						"errors" => $errors,
						'submitWebsearch' => $submitWebsearch,
						'shoppingCart' => $shoppingCart,
						'shopM' => $shopM 
				] );
			}
		}
// 		try {
			return $this->render ( 'search', array (
					'model' => $model,
					'selectionLists' => $this->_selectionLists,
					'modelwebsite' => $modelwebsite,
					'submitWebsearch' => $submitWebsearch 
			) );
// 		} catch ( \yii\base\ErrorException $e ) {
// 			// if(strpos($e, "SOLR")!=0)
// 			Yii::$app->mailer->compose ()->setFrom (Yii::$app->params ['noreplyMail'] )->setTo ( Yii::$app->params ['feedbackMail'] )->setSubject ( 'Error occured' )->setTextBody ( $e )->send ();
// 			return $this->render ( '/site/error', [ 
// 					"name" => "Oooops",
// 					"message" => "Something went wrong.",
// 					"exception" => $e 
// 			] );
// 		}
	}
	
	public function actionResult() {
		// Yii::info ( "actionResult" );
		$filters = [ ];
		$model = new SearchForm ();
		$model->attributes = Yii::$app->request->post ();
		// foreach ( $model->attributes as $att )
		// Yii::info ( "ATTR" + $att );
		
		if (isset ( $_REQUEST ['SearchForm'] )) {
			$searchform = $_REQUEST ['SearchForm'];
		} else
			$searchform = [ ];
		
		foreach ( $this->knownLParams as $kp ) {
			if (isset ( $searchform [$kp] ) && strlen ( $searchform [$kp] ) > 0) {
				if (strcasecmp ( $searchform [$kp], "---" ) > 0)
					$filters [$kp] = $searchform [$kp];
			}
			if (isset ( $_REQUEST [$kp] ) && strlen ( $_REQUEST [$kp] ) > 0) {
				if (strcasecmp ( $_REQUEST [$kp], "---" ) > 0)
					$filters [$kp] = $_REQUEST [$kp];
			}
		}
		
		if (isset ( $searchform ['countrycode'] ) && strlen ( $searchform ['countrycode'] ) > 0) {
			$filters ['isocountrycode'] = $searchform ['countrycode'];
		}
		if (isset ( $_REQUEST ['countrycode'] ) && strlen ( $_REQUEST ['countrycode'] ) > 0) {
			$filters ['isocountrycode'] = $_REQUEST ['countrycode'];
		}
		
		foreach ( $this->knownParams as $kp ) {
			if (isset ( $searchform [$kp] ) && strlen ( $searchform [$kp] ) > 0) {
				$filters [$kp] = $searchform [$kp];
			}
			if (isset ( $_REQUEST [$kp] ) && strlen ( $_REQUEST [$kp] ) > 0) {
				$filters [$kp] = $_REQUEST [$kp];
			}
		}
		
		// HERE THE TRICK FOR THE NICE URL
		if ($filters && isset ( $_REQUEST ['SearchForm'] )) {
			$this->redirect ( Url::to ( array_merge ( [ 
					'search/result' 
			], $filters ) ) );
		} else
			return $this->performSearch ( $filters, $model );
	}
	
	 function performSearch($filters, $model) {
		// Yii::info ( "actionperformSearch" );
		
		// we get all the lists for the drop-down lists in the form -- why??
		$this->_selectionLists=CommonSearchController::collectDropDownAttributesLists ( $model );
		
		$submitURL = $this->createUrl ( [ 
				'search/create' 
		], $filters );
		
		// validate user input
		if ($model->validate ()) {
			
			$config = SOLRQueryManager::getConfigSearch ();
			$query = SOLRQueryManager::createSearchQuery ( $config );
			
			$fq = "";
			$q = [ ];
			$filterInstitution = false;
			$filterVouchercol = false;
			foreach ( $filters as $key => $value ) {
				$value = str_replace ( " ", "\ ", $value );
				$value = str_replace ( "(", "\(", $value );
				$value = str_replace ( ")", "\)", $value );
				$value = str_replace ( ":", "\:", $value );
				if ($key == "institution") {
					$q [] = SOLRQueryManager::getPartialQuery ( $key, $value, $filters, False );
					$filterInstitution = true;
				}
				if ($key == "voucherCol") {
					$q [] = SOLRQueryManager::getPartialQuery ( $key, $value, $filters, False );
					$filterVouchercol = true;
				}
			}
			if (! $filterInstitution && ! $filterVouchercol)
				$q = [ 
						"*:*" 
				];
			$raw_response = SOLRQueryManager::fillAndExecuteQuery ( $query, implode ( " AND ", $q ), $fq, $filters, False );
			
			
			$q[]="isLowest:1";
			$query2 = SOLRQueryManager::createSearchQuery ( $config );
			$raw_responseCounts = SOLRQueryManager::fillAndExecuteQuery ( $query2, implode ( " AND ", $q ), $fq, $filters, False );
			//$numFound = $raw_responseCounts->getNumFound ();
			
			$tripleidstoreids=[];
			foreach($raw_response as $document){
				$tripleidstoreids = array_merge($tripleidstoreids, $document->lowestTripleIDs);
			}
			$tripleidstoreids=array_unique($tripleidstoreids); 
			
			
			$config = SOLRQueryManager::getConfigPreview();
			$query = SOLRQueryManager::createSearchQuery ( $config );
			if(sizeof($tripleidstoreids)>0){
			$q = "tripleidstoreid:(".implode(" OR ", $tripleidstoreids).")";
			}
			else $q ="";
			
			$raw_response = SOLRQueryManager::fillAndExecuteQuery ( $query, $q , $fq, [], False );
			$doc = $raw_response->getDocuments ();
			$numFound = $raw_response->getNumFound ();
			
			$ids = [ ];
			foreach ( $raw_response as $id ) {
				$ids [] = $id->tripleidstoreid;
			}
			
			// Repeat query because Solr won't permit queries with more OR clauses, didn't find a better solution :(
			// Executed in batches of 200
			$SOLR_QUERY_CONSTANT = 200;
			$shoppingArray = [ ];
			if(! Yii::$app->user->isGuest && Yii::$app->params ['shopping']){
			for($i = 0; $i < ceil ( count ( $ids ) / $SOLR_QUERY_CONSTANT ); $i ++) {
				$config = SOLRQueryManager::getConfigDetails ();
				$query1 = SOLRQueryManager::createSearchQuery ( $config );
				$loanStatus = SOLRQueryManager::loanQuery ( $query1, "*:*", array_slice ( $ids, $i * $SOLR_QUERY_CONSTANT, $SOLR_QUERY_CONSTANT ) );
				foreach ( $loanStatus as $l ) {
					$shoppingArray [$l->tripleidstoreid] = $l;
				}
			}
			}
			
			$shopM = new CartForm ();
			if (! Yii::$app->user->isGuest) {
				$shoppingCart = sizeof ( DBInterface::getShoppingCart ( Yii::$app->user->identity->id ) );
			} else {
				$shoppingCart = '';
			}
			
			return $this->render ( 'show/listView', array (
					"model" => $model,
					'response' => $doc,
					// 'shownAttributes' => $this->shownAttributes,
					'keyForLink' => $this->key_link,
					'recordFilters' => $this->recordFilters,
					'item_count' => $numFound,
					'selectionLists' => $this->_selectionLists,
					'submitURL' => $this->submitURL,
					'filters' => $filters,
					'shopping' => $shoppingArray,
					'shopM' => $shopM,
					'shoppingCart' => $shoppingCart 
			) ); // Rukeia 26-03-2015: hinzugefügt, damit listView auch auf selectionLists zugreifen kann
				     // 'submitURL'=>$this->$submitURL,
		} else {
			$errors = $model->errors;
			return $this->render ( 'search/result', [ 
					"model" => $model,
					"filters" => $filters,
					"errors" => $errors 
			] );
		}
	}

	
	public function actionDownloadabcd($unitID){
		Yii::$app->response->format = \yii\web\Response::FORMAT_RAW;
		$headers = Yii::$app->response->headers;
		$headers->add('Content-Type', 'text/xml');
		$filename=Yii::$app->params['tmpFolder'].$unitID.".xml";
		if(@file_exists($filename))
			return $this->renderFile($filename);
			else
				echo "The file does not exist anymore, refresh your results page.";
	}

	public function actionRecord() {
		$model = new SearchForm ();
		$model->attributes = Yii::$app->request->post ();
		
		// get the values that came from url back to an array
		$requestedAttributes = $this->requestedAttributes;
		
		if ($model->validate ()) {
			$output = SingleRecordManager::createOutput ( $requestedAttributes, $model );
			if (isset ( $output ) && sizeof ( $output ) > 0) {
				$output ['display'] = 'tab';
				$output['model']=$model;
				if ((strpos ( Yii::$app->request->referrer, "record" ) === 0)) 
					Yii::$app->session->set ('comeFrom', Yii::$app->request->referrer );
				$output['back']=Yii::$app->session->get('comeFrom',Yii::$app->request->referrer);
				return $this->render ( 'show/recordViewTabs', $output );
			} else
				return "Nothing found, check your tripleid!";
		} else {
			$errors = $model->errors;
			return $this->render ( 'search/result', [ 
					"filters" => $filters,
					"errors" => $errors,
					"model" => $model 
			] );
		}
	}

	public function actionBrowse() {
		// Yii::info ( "actionIndexbrowse" );
		$elts = Yii::$app->request->get ();
		if (isset ( $elts ["page"] ))
			$page = $elts ["page"];
		else
			$page = 0;
		$fqs = [ ];
		$q = [ ];
		$rawfqs = [ ];
		$escapedfqs=[];
		$filters = [ 
				"recordbasis",
				"fullScientificName",
				"familyName",
				"unitID",
				"institutioncode",
				"collectioncode",
				"country",
				"institution",
				"voucherCol",
				"sampletype" 
		];
		
		foreach ( $filters as $filter ) {
			if (isset ( $elts [$filter] )) {
				$rawfq = $elts [$filter];
				$rawfqs [] = $filter . "=" . $rawfq;
				$escapedfqs [] = $filter . "=" .urlencode($rawfq);
				$fq = str_replace ( " ", "\ ", $rawfq );
				$fq = str_replace ( "(", "\(", $fq );
				$fq = str_replace ( ")", "\)", $fq );
				$fq = str_replace ( ":", "\:", $fq );
				$fq = str_replace ( "'", "\'", $fq );
				$fq = str_replace ( "[", "\[", $fq );
				$fq = str_replace ( "]", "\]", $fq );
				$fq = str_replace ( "+", "\+", $fq );
				$fqs [] = $filter . ":" . $fq;
				$tmp = SOLRQueryManager::getPartialQuery ( $filter, $fq, $filters, False );
				// echo $tmp;
				$q [] = $tmp;
			}
		}
		if (empty ( $q ))
			$q [] = "*:*";
		$filters = [ ];
		
		// $config = SOLRQueryManager::getConfigDetails ();
		$config = SOLRQueryManager::getConfigSearch ();
		$query = SOLRQueryManager::createSearchQuery ( $config );
		
		$sortScientificName = "fullScientificName_asc";
		$sortUnitID = "unitID_asc";
		$sortRecordbasis = "recordbasis_asc";
		$sortInstitutioncode = "institutioncode_asc";
		$sortCollectioncode = "collectioncode_asc";
		
		if (isset ( $elts ["sort"] )) {
			$field = explode ( "_", $elts ["sort"] )[0];
			$direction = explode ( "_", $elts ["sort"] )[1];
			$query->addSort ( $field, $direction );
			switch ($field) {
				case "fullScientificName" :
					if ($direction === "asc")
						$sortScientificName = "fullScientificName_desc";
					else
						$sortScientificName = "fullScientificName_asc";
					break;
				case "recordbasis" :
					if ($direction === "asc")
						$sortRecordbasis = "recordbasis_desc";
					else
						$sortRecordbasis = "recordbasis_asc";
					break;
				case "UnitID" :
					if ($direction === "asc")
						$sortUnitID = "UnitID_desc";
					else
						$sortUnitID = "UnitID_asc";
					break;
				case "institutioncode" :
					if ($direction === "asc")
						$sortInstitutioncode = "institutioncode_desc";
					else
						$sortInstitutioncode = "institutioncode_asc";
					break;
				case "collectioncode" :
					if ($direction === "asc")
						$sortCollectioncode = "collectioncode_desc";
					else
						$sortCollectioncode = "collectioncode_asc";
					break;
				
				default :
					$sortScientificName = "fullScientificName_asc";
					$sortUnitID = "unitID_asc";
					$sortRecordbasis = "recordbasis_asc";
					$sortInstitutioncode = "institutioncode_asc";
					$sortCollectioncode = "collectioncode_asc";
			}
		}
		
		$facetnames = [ 
				'country',
				'institution',
				'recordbasis',
				'sampletype' 
		];
		try {
			$raw_response = SOLRQueryManager::browseQuery ( $query, implode ( " AND ", $q ), $fqs, $page, $facetnames );
		} catch ( \Solarium\Exception\HttpException $e ) {
			Yii::$app->mailer->compose ()->setFrom (Yii::$app->params ['noreplyMail'] )->setTo ( Yii::$app->params ['feedbackMail'] )->setSubject ( 'Error occured' )->setTextBody ( $e . "\n q:" . implode ( " AND ", $q ) . "\nfqs:" . implode ( " ", $fqs ) . "\n
                                                        facets" . implode ( " ", $facetnames ) . "\n
                                                        URL built: " . Yii::$app->request->url . "\n
                                                        and the user is " . Yii::$app->request->userIP )->send ();
			return $this->render ( '/site/error', [ 
					"name" => "Oooops",
					"message" => "The SOLR instance seems to be gone.",
					"error" => $e 
			] );
		}
		$rawfacets = $raw_response->getFacetSet ();
		$facets = [ ];
		foreach ( $facetnames as $fname ) {
			$tmp = [ 
					"<select class='sel'>" 
			];
			if (! empty ( ($rawfqs) ))
				$tmp [] = "<option value='/".Yii::$app->params['siteName']."/search/browse?" . implode ( "&amp;", $escapedfqs ) . "&amp;" . $fname . "=*'>---</option>";
			else
				$tmp [] = "<option value='/".Yii::$app->params['siteName']."/search/browse?" . $fname . "=*'>---</option>";
			foreach ( $rawfacets->getFacet ( $fname ) as $doc => $count )
				if (! empty ( ($rawfqs) ))
					$tmp [] = "<option value='/".Yii::$app->params['siteName']."/search/browse?" . implode ( "&amp;", $escapedfqs ) . "&amp;" . $fname . "=" . $doc . "'>" . $doc . " " . $count . "</option>";
				else
					$tmp [] = "<option value='/".Yii::$app->params['siteName']."/search/browse?" . $fname . "=" . $doc . "'>" . $doc . " " . $count . "</option>";
			$tmp [] = "</select>";
			$facets [$fname] = implode ( "", $tmp );
		}
		
		$doc = $raw_response->getDocuments ();
		$lowestDoc = [ ];
		foreach ( $doc as $unitdoc ) {
			$lowestUnits = $unitdoc->lowestTripleIDs;
			$currentID = $unitdoc->tripleidstoreid;
			if (in_array ( $currentID, $lowestUnits )) {
				$lowestDoc [$currentID] = [ 
						$unitdoc->unitID,
						$unitdoc->collectioncode,
						$unitdoc->institutioncode 
				];
			} else {
				$lowestDoc [$currentID] = $this->getTripleID ( $lowestUnits [0] );
			}
		}
		$numFound = $raw_response->getNumFound ();
		
		$nbPages = $numFound / Yii::$app->params ['listPerPage'];
		$pages = new Pagination ( [ 
				'totalCount' => $numFound,
				'pageSize' => Yii::$app->params ['listPerPage'] 
		] );
		$from = Yii::$app->params ['listPerPage'] * $page;
		$to = $from + Yii::$app->params ['listPerPage'];
		if (sizeof ( $rawfqs ) > 0)
			return $this->render ( 'browse', [ 
					"lowestDoc" => $lowestDoc,
					"doc" => $doc,
					"fq" => implode ( "&amp;", $rawfqs ),
					"ufq"=>implode ( "&amp;", $escapedfqs ),
					"pages" => $pages,
					"page" => $page,
					"sortScientificName" => $sortScientificName,
					"sortRecordbasis" => $sortRecordbasis,
					"sortInstitutioncode" => $sortInstitutioncode,
					"sortUnitID" => $sortUnitID,
					"sortCollectioncode" => $sortCollectioncode,
					"facets" => $facets,
					"recordsFrom" => $from,
					"recordsTo" => $to,
					"recordsTotal" => $numFound 
			] );
		else
			return $this->render ( 'browse', [ 
					"lowestDoc" => $lowestDoc,
					"doc" => $doc,
					"pages" => $pages,
					"page" => $page,
					"sortScientificName" => $sortScientificName,
					"sortRecordbasis" => $sortRecordbasis,
					"sortInstitutioncode" => $sortInstitutioncode,
					"sortUnitID" => $sortUnitID,
					"sortCollectioncode" => $sortCollectioncode,
					"facets" => $facets,
					"recordsFrom" => $from,
					"recordsTo" => $to,
					"recordsTotal" => $numFound 
			] );
	}
	public function actionTree() {
		$elts = Yii::$app->request->get ();
		if (isset ( $elts ["institution"] ))
			$institution = $elts ["institution"];
		else
			$institution = null;
		
		$model = new SearchForm ();
		$elements = $model->getParameters ();
		$this->_selectionLists=CommonSearchController::collectDropDownAttributesLists ( $model );
		$facetnames = [ 
				'institution' 
		];
		
		$facets = [ ];
		
		foreach ( $elements as $key => $param ) {
			if (in_array ( $key, $facetnames )) {
				$attributeList = isset ( $this->_selectionLists ) ? $this->_selectionLists [$key] : array ();
				$selectField = [ ];
				foreach ( $attributeList as $kk => $vv ) {
					if (preg_match ( "/ \(\d* \/ \d*\)/", $vv ))
						$selectField [preg_replace ( "/ \(\d* \/ \d*\)/", "", $vv )] = $vv;
					else
						$selectField [preg_replace ( "/ \(\d*\)/", "", $vv )] = $vv;
				}
				if (! empty ( $institution ))
					$facets [] = Html::dropDownList ( $key, $institution, $selectField, $htmlOptions = [ 
							'class' => "dropDownLabel",
							'id2' => $key 
					] );
				else
					$facets [] = Html::dropDownList ( $key, $key, $selectField, $htmlOptions = [ 
							'class' => "dropDownLabel",
							'id2' => $key 
					] );
			}
		}
		
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		
		$command = $connection->createCommand ( "SELECT distinct taxon_id, name, total_species_extant from _taxon_tree where rank='kingdom' order by name asc" );
		// $command = $connection->createCommand ( "SELECT distinct taxon_id, name, number_of_children from _taxon_tree where rank='kingdom' order by name asc" );
		
		$dataReader = $command->queryAll ();
		$tree = [ ];
		
		$config = SOLRQueryManager::getConfigBackbone ();
		
		foreach ( $dataReader as $row ) {
			$sql = "select count(distinct fullscientificname) as nbChildren,
                                                count(distinct occurrenceid) as nbOccurrences, kingdom
                                            from treeoflife
                                                where kingdom='" . $row ["name"] . "'";
			if (isset ( $institution ))
				$sql .= " AND institution='" . $institution . "'";
			
			$sql .= "group by kingdom";
			$command2 = $connection->createCommand ( $sql );
			$dataReader2 = $command2->queryAll ();
			// $value = $row['name'];
			// $value = str_replace (" ", "\ ", $value );
			// $q="rank:KINGDOM AND kingdom:".$value;
			
			// // $fq="{!join from=colKey to=Keys fromIndex=ggbn_details_local}*:*";
			// $fq=null;
			// $query = SOLRQueryManager::createSearchQuery ( $config );
			// $raw_response = SOLRQueryManager::fillAndExecuteQuery ( $query, $q , $fq, null, False);
			
			// $docs=$raw_response->getDocuments ();
			// $nbOccurrences=0;
			// $nbTaxChildren=0;
			
			// foreach ($docs as $doc){
			// $nbOccurrences =$doc->occurrenceCount;
			// $nbTaxChildren =$doc->childrenCount;
			
			// if(empty($nbOccurrences))
			// $nbOccurrences = 0;
			// $nbTaxChildren = $doc->childrenCount;
			// if(empty($nbTaxChildren))
			// $nbTaxChildren = 0;
			// }
			$ok = false;
			foreach ( $dataReader2 as $row2 ) {
				$ok = true;
				$nbOccurrences = $row2 ['nbOccurrences'];
				$nbTaxChildren = $row2 ['nbChildren'];
				$mykingdom = $row2 ['kingdom'];
				$tree [$row ["taxon_id"]] = [ 
						$mykingdom,
						$nbTaxChildren,
						$nbOccurrences 
				];
				// $tree[$row["taxon_id"]]=[$row["name"],$row["number_of_children"]];
			}
			if (! $ok)
				$tree [$row ["taxon_id"]] = [ 
						$row ["name"],
						0,
						0 
				];
		}
		Yii::$app->db->close ();
		return $this->render ( 'treeoflife', [ 
				"tree" => $tree,
				"facets" => $facets 
		] );
	}
}