<?php

namespace app\models;

use Yii;
use yii\base\Model;
use yii\widgets\Pjax;
use common\controllers\CommonDBInterface as DBInterface;

/***********************************
 * 
 * @author g.droege
 * performs database queries for the tree view, to enable users to browse the catalogue
 * tree shall be divided into cultivated and wild origin, sorted by families
 */

class TreeView extends Model
{
	
	
	public function getTree($origin, $IDsInCart) {
		
			$connection = new \yii\db\Connection ( Yii::$app->db );
			$connection->open ();

			if ($origin == "cultivated")  {
				$establishmentMeans = "LIKE '%".$origin."%'";
				$originText = "Seeds collected in the garden";
			}
			else { 
				$establishmentMeans = "NOT LIKE '%cultivated%'"; 
				$originText = "Seeds collected in the wild"; }
			
				$command = $connection->createCommand ( "SELECT DISTINCT rh.highertaxaid, highertaxon as classis from rawhigher rh
												JOIN identificationtohigher ih ON rh.highertaxaid = ih.fk_highertaxaid
												JOIN identification i ON ih.fk_rawidentificationid = i.identificationid
												JOIN occurrence o ON i.fk_occurrenceid = o.occurrenceid
												JOIN indexseminum_classes ic on i.identificationid=ic.fk_rawidentificationid
												JOIN bio_datasource b on o.fk_datasourceid=b.id
												WHERE o.establishmentMeans ".$establishmentMeans." AND higherrank = 'Classis'
												ORDER BY Sequence" );
				

				$origin = array('text' => $originText, 'load_on_demand' => true);
				$dataReader1 = $command->queryAll ();
				$Classis = array();
				
				
				foreach ($dataReader1 as $row1) {
				
					$command = $connection->createCommand( "SELECT DISTINCT Family FROM indexseminum_classes ic
															JOIN identification i ON ic.fk_rawidentificationid = i.identificationid
															JOIN occurrence o ON i.fk_occurrenceid = o.occurrenceid
															JOIN bio_datasource b on o.fk_datasourceid=b.id
															WHERE o.establishmentMeans ".$establishmentMeans." 
															AND Classis = '".$row1['classis']."' 
															ORDER BY Family ASC ");
					
					$Family = array();
					$dataReader2 = $command->queryAll ();
					
								foreach ( $dataReader2 as $row2 ) {
								
								
								$sql = " SELECT o.occurrenceid, o.fk_tripleidstoreid AS tripleidstoreid, i.fullScientificName, o.ipen, o.locality, o.minAltitude, o.maxAltitude, c.collectorname, o.collectornumber,
																	r.biotope, o.isocountrycode, o.hardiness, p.preparationdate, t.unitid, t.collectioncode, t.institutioncode, r.acquiredFrom
																	FROM identification i
										 							JOIN identificationtohigher ih on i.identificationid = ih.fk_rawidentificationid
										 							JOIN rawhigher rh on ih.fk_highertaxaid = rh.highertaxaid
										 							JOIN occurrence o on i.fk_occurrenceid = o.occurrenceid
																	JOIN rawoccurrence r on o.occurrenceid = r.occurrenceid
																	LEFT JOIN collectors c on o.occurrenceid = c.fk_occurrenceid
																	JOIN preparation p on o.fk_tripleidstoreid = p.fk_tripleidstoreid 
																	JOIN bio_datasource b on o.fk_datasourceid=b.id
																	JOIN tripleidstore t on o.fk_tripleidstoreid = t.tripleidstoreid
																	WHERE rh.highertaxon = '".$row2 ['Family']."'  
																	AND o.establishmentMeans ".$establishmentMeans."
																	ORDER BY fullScientificName";
								$command = $connection->createCommand ( $sql );
								$dataReader4 = $command->queryAll ();
								$ScientificName = array();
								
								foreach ( $dataReader4 as $row4 ) {
									$output = "<b>".$row4 ['fullScientificName']."</b> ".$row4 ['ipen'].": ".$row4['isocountrycode'].", ".$row4 ['locality'];
									if ($row4['locality']) $output .= ", ";
									if ($row4['biotope']) $output .= " ".$row4 ['biotope'].", ";
									$output .= $row4 ['minAltitude'];
									if ($row4['maxAltitude']) $output .= " - ".$row4 ['maxAltitude'];
									if ($row4['minAltitude']) $output .= " m, ";
									if ($row4['collectorname']) $output .= "leg. ";
									$output .= $row4 ['collectorname'];
									if ($row4['collectornumber']) $output .= " ".$row4 ['collectornumber'];
									if ($row4['acquiredFrom']) $output .= " Provided by: ".$row4 ['acquiredFrom'];
									if ($row4['preparationdate']) $output .= "<i>; seeds collected on ".$row4['preparationdate']."</i>";
									if ($row4['hardiness'] == 0) $output .= "<i>; Cultivaton in greenhouse</i>";
									
									
									//$cartContent = DBInterface::checkShoppingStatus($row4['unitid'], $row4['collectioncode'], $row4['institutioncode'], Yii::$app->user->identity->id );
									
									if(Yii::$app->user->isGuest) { $icontype = ''; }
									else {
									if(in_array($row4['tripleidstoreid'], $IDsInCart)) { $icontype = 'inCart';	}
									else { $icontype = 'children'; } }
									
									
									//$shop_id = preg_replace ( '*\/*', 'SLASH', $row4 ["unitid"] . "---" . $row4 ["collectioncode"] . "---" . $row4 ["institutioncode"]);
									$shop_id = $row4['tripleidstoreid'];
									$ScientificName[] = array('id' => $shop_id,
															  'text' => $output,
															  'type' => $icontype);
								}
									
								$Family[] = array_merge(array('text' => $row2 ['Family'], 'load_on_demand' => true), array('children' => $ScientificName));
								
								}
					
					
					$Classis[] = array_merge(array('text' => $row1 ['classis']), array('children' => $Family));
				}
			
				$treeOutput = array_merge($origin, array('children' => $Classis));
				
				Yii::$app->db->close ();
				
				return $treeOutput;
	
	}

}