<?php

namespace common\controllers;

use Yii;
use yii\db\ActiveRecord;
use yii\db\Query;

/**
 * This class serves as Interface for various DB Lists connected to the shoppings system.
 *
 * @author a.hartebrodt
 *        
 */
class CommonDBInterface extends ActiveRecord {
	
	/**
	 * deletes one sample from the database list user_shopping_cart
	 *
	 * @param unknown $unit_id
	 *        	the sample to be deleted
	 * @param unknown $user_id
	 *        	the concered user, usually the currently logged in user
	 */
	public static function deleteFromShoppingCart($unit_id, $collection_code, $institution_code, $user_id) {
		$connection = new \yii\db\Connection ( Yii::$app->userdb );
		$connection->open ();
		$connection->createCommand ()->delete ( 'user_shopping_cart', [ 
				'unit_id' => $unit_id,
				'user_id' => $user_id,
				'collection_code' => $collection_code,
				'institution_code' => $institution_code 
		] )->execute ();
		$connection->close ();
	}
	
	/**
	 * adds a list of samples to the shopping cart
	 *
	 * @param unknown $list_of_ids
	 *        	the samples to be added
	 * @param unknown $user_id
	 *        	the concerend user.
	 */
	public static function addToShoppingCart($tripleidstoreid, $user_id) {   // was  addToShoppingCart($list_of_ids, $user_id) {
		$connection = new \yii\db\Connection ( Yii::$app->userdb );
		$connection->open ();
		
		$unit = (new \yii\db\Query())
		->select(['unitid', 'collectioncode', 'institutioncode'])
		->from('tripleidstore')
		->where(['tripleidstoreid' => $tripleidstoreid])
		->all();
		
		if($unit) {
			
		$testRequestStatus = self::checkShoppingStatus($unit[0]['unitid'], $unit[0]['collectioncode'], $unit[0]['institutioncode'], $user_id);
				
		if($testRequestStatus != true) {	//if not yet in basket
		
		$connection->createCommand ()->insert ( 'user_shopping_cart', [
				'user_id' => $user_id,
				'unit_id' => $unit[0]['unitid'],
				'collection_code' => $unit[0]['collectioncode'],
				'institution_code' => $unit[0]['institutioncode'],
				'tripleidstoreid' => $tripleidstoreid
		] )->execute ();  
		
		return 'added to cart';
		
		}
		else {
			return 'already in cart';
		}
		}
		else {
			return 'id not found';
		}
		
	
		$connection->close ();
	}
	
	public static function checkShoppingStatus($unitid, $collectioncode, $institutioncode, $user_id) {
		
		$units = (new \yii\db\Query())
		->select(['unit_id', 'collection_code', 'institution_code'])
		->from('user_shopping_cart')
		->where(['unit_id' => $unitid,'collection_code' => $collectioncode, 'institution_code' => $institutioncode, 'user_id' => $user_id, 'ordered' => '0'])
		->one();
		
		if(!empty($units)) {
				
					return true; 

		 }
		else { return false; }
	}
	
	/**
	 * returns the items in the user's shopping cart, which have not been ordered yet.
	 *
	 * @param unknown $user_id        	
	 * @return unknown an array whith all the records.
	 */
	public static function getShoppingCart($user_id) {
		$connection = new \yii\db\Connection ( Yii::$app->userdb );
		$connection->open ();
		$query = (new Query ())->select ( '*' )->from ( 'user_shopping_cart' )->where ( [ 
				'user_id' => $user_id,
				'ordered' => '0' 
		] )->all ();
		$connection->close ();
		return $query;
	}
	
	/**
	 * Delete all items from shopping cart which habe not been ordered yet.
	 *
	 * @param unknown $user_id        	
	 */
	public static function emtpyShoppingCart($user_id) {
		$connection = new \yii\db\Connection ( Yii::$app->userdb );
		$connection->open ();
		$connection->createCommand ()->update ( 'user_shopping_cart', [ 
				'ordered' => '1' 
		], [ 
				'user_id' => $user_id 
		] )->execute ();
		$connection->close ();
	}
	
	/**
	 * Get all the orders the concerned user made in the last six month.
	 * Before this is done, all older oders are deleted from the shopping cart.
	 *
	 * @param unknown $user_id        	
	 * @return unknown An array of all orders
	 */
	public static function getPreviousOrders($user_id) {
		$connection = new \yii\db\Connection ( Yii::$app->userdb );
		$connection->open ();
		CommonDBInterface::deleteAllOldOrders ( $connection );
		$query = (new Query ())->select ( '*' )->from ( 'user_shopping_cart' )->where ( [ 
				'user_id' => $user_id,
				'ordered' => '1' 
		] )->all ();
		$connection->close ();
		return $query;
	}
	
	/**
	 * Delete all orders which are older than aprox.
	 * 6 month (60*60*24*30*6 = 15552000 seconds)
	 *
	 * @param unknown $connection        	
	 */
	private static function deleteAllOldOrders($connection) {
		$connection->createCommand ()->delete ( 'user_shopping_cart', 'ordered = 1 AND order_date <' . date ( 'YmdHis', time () - 15552000 ) )->execute ();
	}
	
	/**
	 * returns the logo of the institution given a tripleidstoreid.
	 *
	 * @param unknown $tripleidstoreid
	 *        	any tripleidstoreid.
	 * @return unknown the logoURL
	 */
	public static function getLogo($tripleidstoreid) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$query = $connection->createCommand ( "select logoURL from parentInstitution pi
			join bio_datasource b on pi.parentInstitutionID = b.fk_parentInstitutionid
			join occurrence o on b.id = o.fk_datasourceid
			where o.fk_tripleidstoreid = '".$tripleidstoreid."';" )->queryAll();		
		$connection->close ();
		return $query;
	}
	
	/**
	 * gets the email of the institution's contact person by giving a tripleidstoreid.
	 *
	 * @param unknown $tripleidstoreid        	
	 */
	public static function getMail($tripleidstoreid) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT co.*, pi.*
			From contacts co
			join parentInstitution pi on co.fk_parentInstitutionID = pi.parentInstitutionID
			join bio_datasource b on pi.parentInstitutionID = b.fk_parentInstitutionid
			join occurrence o on b.id = o.fk_datasourceid
			Where o.fk_tripleidstoreid =' . $tripleidstoreid . ' and co.shoppingSystem = 1;' )->queryAll ();
		$connection->close ();
		return $query;
	}
	
	/**
	 * inserts a new entry or updates the database entry for a date->instiution pair.
	 * For each date and instiution the database table contains the number of dna, tissue and
	 * request_dna samples.
	 *
	 * @param unknown $institution_id
	 *        	the institution
	 * @param unknown $statsArray
	 *        	an array containing (nr_dna_samples, nr_tissue_samples, nr_dna_req_samples)
	 */
	public static function stats($institution_id, $statsArray) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$test = $connection->createCommand ( 'SELECT * FROM user_shopping_statistics WHERE
				order_date = CURDATE() AND institution=' . $institution_id )->queryAll ();
		if (! $test) {
			$connection->createCommand ( 'INSERT INTO user_shopping_statistics ( order_date, institution, samples_dna, samples_tissue,
				samples_request_dna ) VALUES (CURDATE(),' . $institution_id . ',' . $statsArray [0] . ',' . $statsArray [1] . ',' . $statsArray [2] . ')' )->execute ();
		} else {
			$dna = $test [0] ['samples_dna'] + $statsArray [0];
			$tissue = $test [0] ['samples_tissue'] + $statsArray [1];
			$request = $test [0] ['samples_request_dna'] + $statsArray [2];
			
			$connection->createCommand ( 'UPDATE user_shopping_statistics SET samples_dna=' . $dna . ', samples_tissue=' . $tissue . ', samples_request_dna=' . $request . ' WHERE institution=' . $test [0] ['institution'] . ' AND order_date = CURDATE()' )->execute ();
		}
		$connection->close ();
	}
	public static function getStats() {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$stats = $connection->createCommand ( 'SELECT u.order_date,u.samples_dna,u.samples_tissue,u.samples_request_dna FROM user_shopping_statistics u' )->queryAll ();
		$connection->close ();
		return $stats;
	}
	
	public static function getInstitutions() {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT par.*
				FROM parentInstitution par where parentInstitutionID!=38 '.Yii::$app->params['thematicNCDfilter'].' order by par.institutionFullName  asc' )->queryAll ();
		$connection->close ();
		return $query;
	}
	
	public static function getLastInstitution() {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT par.parentInstitutionID
				FROM parentInstitution par order by par.parentInstitutionID  desc limit 1' )->queryAll ();
	
		$connection->close ();
		return $query;
	}
	
	
	public static function getJoining($t) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		if(empty($t))
			$query = $connection->createCommand ( 'SELECT par.*
				FROM parentInstitution par where parentInstitutionID!=38 '.Yii::$app->params['thematicNCDfilter'].' order by par.dateOfJoining  asc' )->queryAll ();
		if($t == "Core")
			$query = $connection->createCommand ( 'SELECT par.*
				FROM parentInstitution par where parentInstitutionID!=38 '.Yii::$app->params['thematicNCDfilter'].' AND memberStatus like "Core%" order by par.onlineSince  asc' )->queryAll ();
		else
		$query = $connection->createCommand ( 'SELECT par.*
				FROM parentInstitution par where parentInstitutionID!=38 '.Yii::$app->params['thematicNCDfilter'].' AND memberStatus LIKE "'.$t.'%" order by par.dateOfJoining  asc' )->queryAll ();
		$connection->close ();
		return $query;
	}
	
	public static function getNCDInstitution($ID) {
		$connection = new \yii\db\Connection ( Yii::$app->ncd );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT i.*,id.*,i.ID as ID_Institution from Institutions i 
				JOIN InstitutionDataByLanguage id ON  i.ID=id.institutionID 
				WHERE i.ID="' . $ID . '"' )->queryAll ();
		$connection->close ();
		return $query;
	}
	
	public static function getNCD($ID) {
		$connection = new \yii\db\Connection ( Yii::$app->ncd );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT c.*, c.ID as ID_Collection, i.ID as ID_Institution, i.*, id.* from Collections c
				LEFT JOIN CollectionsToInstitutions ci ON c.ID=ci.CollectionID
				LEFT JOIN Institutions i ON ci.InstitutionID=i.ID
				LEFT JOIN InstitutionDataByLanguage id ON  i.ID=id.institutionID 
				WHERE c.ID="' . $ID . '"')->queryAll ();
		$connection->close ();
		return $query;
	}
	
	
	
	public static function getNCDCollections($id) {
		$connection = new \yii\db\Connection ( Yii::$app->ncd );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT distinct c.*, cl.*, ccs.ConservationStatusDate, m.Explanation FROM ncd.Collections c
				LEFT JOIN CollectionsToInstitutions ci ON c.ID=ci.CollectionID
				LEFT JOIN CollectionConservationStatuses ccs ON ccs.CollectionID=ci.CollectionID
				LEFT JOIN McGinleyConservationStatuses m ON ccs.ConservationStatusID=m.level
				LEFT JOIN CollectionDataByLanguage cl ON cl.CollectionID=ci.CollectionID
				where c.ID=' . $id )->queryAll ();
		$connection->close ();
		return $query;
	}
	
	public static function getNCDChildren($id) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT par.*	FROM parentInstitution par where fk_ncd_collectionID IS NOT NULL and fk_ncd_institutionID=' . $id )->queryAll ();
		$connection->close ();
		return $query;
	}
	
	public static function getNCDcollectionstest($id) {
		$connection = new \yii\db\Connection ( Yii::$app->ncd );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT distinct c.*, cl.*, ccs.ConservationStatusDate, m.Explanation, i.*,id.* FROM ncd.Collections c
				LEFT JOIN CollectionsToInstitutions ci ON c.ID=ci.CollectionID
				LEFT JOIN CollectionConservationStatuses ccs ON ccs.CollectionID=ci.CollectionID
				LEFT JOIN McGinleyConservationStatuses m ON ccs.ConservationStatusID=m.level
				LEFT JOIN CollectionDataByLanguage cl ON cl.CollectionID=ci.CollectionID
				LEFT JOIN Institutions i  ON ci.institutionID=i.ID
				LEFT JOIN InstitutionDataByLanguage id ON i.ID=id.institutionID  
				where ci.CollectionID=' . $id )->queryAll ();
		$connection->close ();
		return $query;
	}
	
	
	public static function getInstitution($id) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT par.*, c.iso
				FROM parentInstitution par JOIN country c on par.country = c.iso where parentInstitutionID=' . $id )->queryAll ();
		$connection->close ();
		return $query [0];
	} 
	
	public static function getAllInstitutions() {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT *	FROM parentInstitution where parentInstitutionID!=38')->queryAll ();
		$connection->close ();
		return $query;
	}
	
	public static function getContact($id) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT contactID, name, email, role, notes, shoppingSystem, phone,
				 fk_parentInstitutionID as parentInstitution
				FROM contacts  where contactID=' . $id )->queryAll ();
		$connection->close ();
		return $query [0];
	}
	public static function getParentInstitution($registry) {
		Yii::info ( "getParentInstitution " . $registry );
		$abbrev = explode ( ", ", $registry ) [0];
		$city = explode ( ", ", $registry ) [1];
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$query = (new Query ())->select ( 'par.*' )->from ( 'parentInstitution par' )->join ( 'join', 'bio_datasource b', 'fk_parentInstitutionid=parentInstitutionid' )->where ( [ 
				'b.city' => $city,
				'b.institution' => $abbrev 
		] )->distinct ()->all ();
		$connection->close ();
		return $query;
	}
	public static function getNbUnits($id) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT sum(harvested_count) as nb,fk_parentInstitutionid from bio_datasource where fk_parentInstitutionid=' . $id . ' group by fk_parentInstitutionid ' )->queryAll ();
		$connection->close ();
		return $query;
	}
	public static function getContacts($id) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT *  from contacts where fk_parentInstitutionid=' . $id . ' and role=\'primary contact\'' )->queryAll ();
		$connection->close ();
		return $query;
	}
	public static function getAllcontacts() {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT  c.*,par.institutionFullName  from contacts as c 
				join parentInstitution par on par.parentInstitutionid=c.fk_parentInstitutionid
				 order by institutionFullName, name ' )->queryAll ();
		$connection->close ();
		return $query;
	}
	public static function addInstitution($array) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$test = $connection->createCommand ( 'SELECT par.parentInstitutionID, par.institutionFullName
				FROM parentInstitution par' )->queryAll ();
		$connection->createCommand ( "INSERT INTO parentInstitution (institutionShort, institutionFullName, city, Latitude, Longitude, 
				country, logoURL, urlLoanPolicies, urlMTA, dateOfJoining, memberStatus, votingMember, CITES_code, GRBioURI, onlineSince, IPEN_member, isMember, isPartner, isConsortium, isMemberOfConsortium)
									VALUES ('" . $array ['institutionShort'] . "','" . $array ['institutionFullName'] . "','" . $array ['city'] . "',
				'" . $array ['Latitude'] . "','" . $array ['Longitude'] . "','" . $array ['country'] . "','" . $array ['logoURL'] . "',
				'" . $array ['urlLoanPolicies'] . "','" . $array ['urlMTA'] . "',
				CURDATE(),'" . $array ['memberStatus'] . "','" . $array['memberStatus'] . "','" . $array['CITES_code'] . "', 
				'" . $array ['GRBioURI'] . "', '" . $array ['onlineSince'] . "', '" . $array ['IPEN_member'] . "', '" . $array ['isMember'] . "',
				'" . $array ['isPartner'] . "', '" . $array ['isConsortium'] . "', '" . $array ['isMemberOfConsortium'] . "')" )->execute ();
		$connection->close ();
	}
	public static function updateInstitution($array) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$connection->createCommand ( "UPDATE parentInstitution set 
				institutionShort=:institutionShort, 
				institutionFullName=:institutionFullName,
				city=:city, 
				Latitude=:Latitude, 
				Longitude=:Longitude, 
				country=:country, 
				logoURL=:logoURL, 
				memberStatus=:memberStatus,
				votingMember=:votingMember,
				dateOfJoining=:dateOfJoining,
				CITES_code=:CITES_code,
				GRBioURI=:GRBioURI,
				onlineSince=:onlineSince,
				IPEN_member=:IPEN_member,
				isMember=:isMember,
				isPartner=:isPartner,
				isConsortium=:isConsortium,
				isMemberOfConsortium=:isMemberOfConsortium,
				urlMTA=:urlMTA,
				urlLoanPolicies=:urlLoanPolicies
				where parentInstitutionid=:id " )->bindValue ( ":institutionShort", $array ['institutionShort'] )
		->bindValue ( ":institutionFullName", $array ['institutionFullName'] )->bindValue ( ":city", $array ['city'] )
		->bindValue ( ":Latitude", $array ['Latitude'] )->bindValue ( ":Longitude", $array ['Longitude'] )
		->bindValue ( ":country", $array ['country'] )->bindValue ( ":logoURL", $array ['logoURL'] )
		->bindValue ( ":votingMember", $array ['votingMember'] )->bindValue ( ":memberStatus", $array ['memberStatus'] )
		->bindValue ( ":dateOfJoining", $array ['dateOfJoining'] )->bindValue ( ":CITES_code", $array ['CITES_code'] )
		->bindValue ( ":GRBioURI", $array ['GRBioURI'] )->bindValue ( ":onlineSince", $array ['onlineSince'] )
		->bindValue ( ":id", $array ['parentInstitutionID'] )->bindValue ( ":IPEN_member", $array ['IPEN_member'] )
		->bindValue(":isMember", $array['isMember'])->bindValue(":isPartner", $array['isPartner'])
		->bindValue(":isConsortium", $array['isConsortium'])->bindValue(":isMemberOfConsortium", $array['isMemberOfConsortium'])
		->bindValue(":urlMTA", $array['urlMTA'])->bindValue(":urlLoanPolicies", $array['urlLoanPolicies'])
		->execute ();
		
		$connection->close ();
	}
	public static function updateContact($array) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$connection->createCommand ( "UPDATE contacts set
				name=:name,
				email=:email,
				role=:role,
				notes=:notes,
				shoppingSystem=:shoppingSystem,
				phone=:phone,
				fk_parentInstitutionID=:parentInstitution
				where contactID=:id " )
		->bindValue ( ":name", $array ['name'] )
		->bindValue ( ":email", $array ['email'] )
		->bindValue ( ":role", $array ['role'] )
		->bindValue ( ":notes", $array ['notes'] )
		->bindValue ( ":shoppingSystem", $array ['shoppingSystem'] )
		->bindValue ( ":phone", $array ['phone'] )
		->bindValue ( ":parentInstitution", $array ['parentInstitution'] )
		->bindValue ( ":id", $array ['contactID'] )->execute ();
		
		$connection->close ();
	}
	public static function addContact($array) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT con.email
				FROM contacts con' )->queryAll ();
		//if (! in_array ( $array ['email'], array_column ( $query, 'email' ) )) {
			$connection->createCommand ( "INSERT INTO contacts (name, email, role, notes, shoppingSystem, phone,  fk_parentInstitutionID) VALUES
					('" . $array ['name'] . "','" . $array ['email'] . "','" . $array ['role'] . "','" . $array ['notes'] . "'," . $array ['shoppingSystem'] . ",'" . $array ['phone'] . "'," . $array ['parentInstitution'] . ")" )->execute ();
			$connection->close ();
			return true;
		/*} else {					//workaround to enable more than one institution for one contact, TODO:should be turned into a real 1:n model
			$connection->close ();
			return false;
		} */
	}
	public static function getCountries() {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT  iso,country from country order by country asc ' )->queryAll ();
		$connection->close ();
		return $query;
	}
	
	public static function getIpenlist() {
		
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT  acronym,name from ipenlist order by acronym asc ' )->queryAll ();
		$connection->close ();
		return $query;
		
	}
	
	
	
	public static function getConsortia() {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT  parentInstitutionID, institutionFullName from parentInstitution where isConsortium = 1 order by institutionFullName asc ' )->queryAll ();
		$connection->close ();
		return $query;
	}
	
	public static function getSpeciesYear($registry) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		if (empty ( $registry ))
			$id = 38;
		else
			$id = $registry;
		$query = $connection->createCommand ( 'SELECT numberOfSpecies, date  FROM statsSpecies where fk_parentInstitutionid=' . $id. ' and date is not null' )->queryAll ();
		$connection->close ();
		return $query;
	}
	public static function getFamiliesYear($registry) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		if (empty ( $registry ))
			$id = 38;
			else
				$id = $registry;
				$query = $connection->createCommand ( 'SELECT numberOfFamily, date  FROM statsFamily where fk_parentInstitutionid=' . $id. ' and date is not null' )->queryAll ();
				$connection->close ();
				return $query;
	}
	public static function getGenusYear($registry) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		if (empty ( $registry ))
			$id = 38;
			else
				$id = $registry;
				$query = $connection->createCommand ( 'SELECT numberOfGenus, date  FROM statsGenus where fk_parentInstitutionid=' . $id. ' and date is not null' )->queryAll ();
				$connection->close ();
				return $query;
	}
	public static function getSamplesYear($registry) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		if (empty ( $registry ))
			$where = "where date is not null".Yii::$app->params['thematicB-HITfilter'];
		else
			$where = "where fk_parentInstitutionid=".$registry." and date is not null";
		$query = $connection->createCommand ( 'SELECT numberSamples, date, sampleType  FROM statsSample ' . $where )->queryAll ();
		$connection->close ();
			return $query;
	}
	
	public static function getMap() {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
	/*	$select = "select distinct co.longitude, co.latitude from
		coordinates co 
		left join occurrence o on co.fk_occurrenceid=o.occurrenceid
		left join identification i on o.occurrenceid=i.fk_occurrenceid
		left join hierarchy h on i.identificationid=h.fk_identificationid
		where h.kingdom='Plantae'"; */
		$select = "select distinct co.longitude, co.latitude from
		coordinates co 
		left join occurrence o on co.fk_occurrenceid=o.occurrenceid
		left join identification i on o.occurrenceid=i.fk_occurrenceid
		left join hierarchy h on i.identificationid=h.fk_identificationid
		where o.fk_datasourceid in (30,31,84,191,192,18,42) and o.isocountrycode='DE'";
		$query = $connection->createCommand ( $select )->queryAll ();
		$connection->close ();
		return $query;
	}
	
	public static function getMapAll() {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
	/*	$select = "select distinct co.longitude, co.latitude from
		coordinates co 
		left join occurrence o on co.fk_occurrenceid=o.occurrenceid
		left join identification i on o.occurrenceid=i.fk_occurrenceid
		left join hierarchy h on i.identificationid=h.fk_identificationid
		where h.kingdom='Plantae'"; */
		$select = "select distinct co.longitude, co.latitude from
		coordinates co 
		left join occurrence o on co.fk_occurrenceid=o.occurrenceid";
		$query = $connection->createCommand ( $select )->queryAll ();
		$connection->close ();
		return $query;
	}
	
}