<?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->db );
		$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($list_of_ids, $user_id) {
		$connection = new \yii\db\Connection ( Yii::$app->db );
		$connection->open ();
		
		foreach ( $list_of_ids as $l => $val ) {
			if ($val == 1) {
				$lparts = explode ( "---", $l );
				$unitID = $lparts [0];
				$collectionCode = $lparts [1];
				$institutionCode = $lparts [2];
				$query = (new Query ())->select ( 'unit_id' )->from ( 'user_shopping_cart' )->where ( [ 
						'user_id' => $user_id,
						'ordered' => '0',
						'unit_id' => $unitID,
						'collection_code' => $collectionCode,
						'institution_code' => $institutionCode 
				] )->all ();
				
				if (in_array ( $unitID, array_column ( $query, 'unit_id' ) )) {
					// if($val == 0){
					// DBInterface::deleteFromShoppingCart($l, $user_id);
					// }
					continue;
				} else {
					// if ($val == 1) {
					$connection->createCommand ()->insert ( 'user_shopping_cart', [ 
							'user_id' => $user_id,
							'unit_id' => $unitID,
							'collection_code' => $collectionCode,
							'institution_code' => $institutionCode 
					] )->execute ();
					// }
					// }
				}
			}
		}
		$connection->close ();
	}
	
	/**
	 * 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->db );
		$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->db );
		$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->db );
		$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 = (new Query ())->select ( 'logoURL' )->from ( 'shopping_view' )->where ( [ 
				'tripleidstoreid' => $tripleidstoreid 
		] )->one ();
		$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.*, par.*
				From contacts co
				Join shopping_view sh on sh.parentInstitutionID = co.fk_parentInstitutionID
				join parentInstitution par on par.parentInstitutionID = sh.parentInstitutionID
				Where sh.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 order by par.institutionFullName  asc' )->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  order by par.dateOfJoining  asc' )->queryAll ();
		else
		$query = $connection->createCommand ( 'SELECT par.*
				FROM parentInstitution par where parentInstitutionID!=38  AND memberStatus=\''.$t.'\' order by par.dateOfJoining  asc' )->queryAll ();
		$connection->close ();
		return $query;
	}
	
	public static function getNCD($name) {
		$connection = new \yii\db\Connection ( Yii::$app->ncd );
		$connection->open ();
		$query = $connection->createCommand ( 'SELECT i.*,id.* from Institutions i 
				JOIN InstitutionDataByLanguage id ON  i.ID=id.institutionID 
				WHERE id.Name=\'' . str_replace ( "'", "''", $name ) . '\'' )->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
				JOIN CollectionsToInstitutions ci ON c.ID=ci.CollectionID
				JOIN CollectionConservationStatuses ccs ON ccs.CollectionID=ci.CollectionID
				JOIN McGinleyConservationStatuses m ON ccs.ConservationStatusID=m.level
				JOIN CollectionDataByLanguage cl ON cl.CollectionID=ci.CollectionID
				where ci.InstitutionID=' . $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.*
				FROM parentInstitution par where parentInstitutionid=' . $id )->queryAll ();
		$connection->close ();
		return $query [0];
	}
	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, dateOfJoining, memberStatus, GRBioURI, onlineSince)
									VALUES ('" . $array ['institutionShort'] . "','" . $array ['institutionFullName'] . "','" . $array ['city'] . "',
				'" . $array ['latitude'] . "','" . $array ['longitude'] . "','" . $array ['country'] . "','" . $array ['logoUrl'] . "',CURDATE(),'" . $array ['memberStatus'] . "','" . $array ['GRBioURI'] . "', '" . $array ['onlineSince'] . "')" )->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, 
				GRBioURI=:GRBioURI,
				onlineSince=:onlineSince
				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 ( ":memberStatus", $array ['memberStatus'] )->bindValue ( ":GRBioURI", $array ['GRBioURI'] )->bindValue ( ":onlineSince", $array ['onlineSince'] )->bindValue ( ":id", $array ['parentInstitutionID'] )->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 {
			$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 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";
		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;
	}
	
}