<?php
require_once('config.inc');
require_once('database.inc');
class WebSearch {
	private $webDbObj;
	public static function getResults( $lookupType, $searchTokenType, $searchToken ) {
		switch ($lookupType) {
			case WebParameter::LookupRelease:
			case WebParameter::LookupTrackInfo:
				$dbName = WebDatabase::ArtRelations;
				break;
			case WebParameter::LookupDms:
			case WebParameter::LookupEQ:
			case WebParameter::LookupEQDetail:
			case WebParameter::LookupDDJob:
			case WebParameter::LookupDDPath:
			case WebParameter::LookupEncProfile:
			case WebParameter::LookupDmsEncProfile:
			case WebParameter::LookupDmsMetadataEncProfile:
			case WebParameter::LookupDeliveryPickup:
				$dbName = WebDatabase::DirectDelivery;
				break; 
		}
		$searchObj = new WebSearch($dbName);
		return $searchObj->doSearch($lookupType, $searchTokenType, $searchToken);
	}
	
	public function __construct($dbName) {
		$this->webDbObj = new WebDatabase($dbName);
	}
	
	private function doSearch( $lookupType, $searchTokenType, $searchToken ) {
		$queryString = '';
		switch ($lookupType) {
			case WebParameter::LookupRelease:
				$queryString = $this->getReleaseQuery( $searchTokenType, $searchToken );
				break;
			case WebParameter::LookupTrackInfo:
				$queryString = $this->getTrackInfoQuery( $searchTokenType, $searchToken );
				break;
			case WebParameter::LookupDms:
				$queryString = $this->getDmsQuery( $searchTokenType, $searchToken );
				break;
			case WebParameter::LookupEQDetail:
				$queryString = $this->getEQDetailQuery( $searchTokenType, $searchToken );
				break;
			case WebParameter::LookupDDPath:
				$queryString = $this->getDDPathQuery( $searchTokenType, $searchToken );
				break;
			case WebParameter::LookupDeliveryPickup:
				$queryString = $this->getDeliveryPickupQuery( $searchTokenType, $searchToken );
				break;
			case WebParameter::LookupDmsMetadataEncProfile:
				$queryString = $this->getDmsMetadataProfileQuery();
				break;
		}
		$resultSet = $this->webDbObj->runQuery($queryString);
		return $this->convertResultSetToHTML($resultSet);
	}
	
	/* Get Release Query */
	private function getReleaseQuery( $searchTokenType, $searchToken ) {
		$sqlString = 'SELECT r.release_id, r.last_updated, r.upc, r.artist_id, r.release_name,
			r.label, r.description, r.release_date, r.deletions, r.genre_id, r.c_line,
			r.product_type_id, r.preorder_date, r.season, r.episode, r.physical_release_date,
			r.theatrical_release_date, r.original_digital_release_date, r.est, r.vod, r.brand,
			r.release_status, r.itunes_previewable FROM releases r WHERE ';
		switch ($searchTokenType) {
			case WebParameter::SearchByUpc:
				$sqlString .= 'r.upc = ' . $searchToken;
				break;
		}
		return $this->formatString($sqlString);		
	}
	
	/* Get Track Info Query */
	private function getTrackInfoQuery( $searchTokenType, $searchToken ) {
		$sqlString = 'SELECT r.upc, r.release_name, r.release_date, r.product_type_id,
			r.europe_public_domain, r.c_line, r.season, r.episode, r.label,
			(SELECT COUNT(id) FROM track WHERE upc = r.upc) AS trackCount, a.name AS artist_name,
			t.id, t.cd, t.track_id, t.track_name, t.isrc, t.track_type, lang.iso_639_1_code,
			t.stereo_or_mono, r.production_number, t.length_minute, t.length_seconds, tv.aspect_ratio,
			tv.resolution, tv.fps, ast.asset_file_name
			FROM `art_relations`.track t
				INNER JOIN `art_relations`.releases r ON r.upc = t.upc
				INNER JOIN `art_relations`.artist_info a ON a.artist_id = r.artist_id
				LEFT JOIN `art_relations`.track_video tv ON tv.id = t.id
				LEFT JOIN `art_relations`.language lang ON lang.language_code = r.`language_id`
				LEFT JOIN `art_relations`.asset ast ON ast.product_id = r.upc AND ast.asset_type = "release" AND ast.bonus_material = "Y"
			WHERE t.upc = ' . $searchToken . ' ORDER BY t.cd, t.track_id';
		return $this->formatString($sqlString);
	}
	
	/* Get Dms Query */
	private function getDmsQuery( $searchTokenType, $searchToken ) {
		$sqlString = 'SELECT cmm.customer_master_master_id, cmm.customer_name, cmm.priority,
		cmm.encoding_REMOVE, cmm.delivery_REMOVE, cmm.mixed_album
		FROM customer_master_master cmm WHERE ';
		switch ($searchTokenType) {
			case WebParameter::SearchByDmsId:
				$sqlString .= 'cmm.customer_master_master_id = ' . $searchToken;
				break;
			case WebParameter::SearchByDmsName:
				$sqlString .= 'cmm.customer_name LIKE "%' . $searchToken . '%"';
				break;
		}
		return $this->formatString($sqlString);
	}

	/* Get EQ Detail */
	private function getEQDetailQuery( $searchTokenType, $searchToken ) {
		$sqlString = 'SELECT eqd.encoding_queue_detail_id, eqd.encoding_queue_id, eqd.upc, eqd.status,
		eqd.encoding_started, eqd.encoding_ended, eqd.delivery_started, eqd.delivery_ended,
		eqd.error_log, eqd.dms_master_master_id, eqd.encoding_server, eqd.delivery_server
		FROM encoding_queue_detail eqd WHERE ';
		switch ($searchTokenType) {
			case WebParameter::SearchByUpc:
				$sqlString .= 'eqd.upc = ' . $searchToken;
				break;
			case WebParameter::SearchByEQId:
				$sqlString .= 'eqd.encoding_queue_id = ' . $searchToken;
				break;	
			case WebParameter::SearchByEQDetailId:
				$sqlString .= 'eqd.encoding_queue_detail_id = ' . $searchToken;
				break;
		}
		return $this->formatString($sqlString);
	}

	/* Get DD Path */
	private function getDDPathQuery( $searchTokenType, $searchToken ) {
		$sqlString = 'SELECT 
		CONCAT("//", INET_NTOA(s.local_ip), "/", sdaf.initial_folder) as initial_dir,
		CONCAT("//", INET_NTOA(s.local_ip), "/", sdaf.initial_folder, ast.folder_structure) as source,
		CONCAT(s.unix_mount, "/", sdaf.initial_folder, ast.folder_structure) as unix_path,
		al.asset_location_id, al.storage_drive_id, al.last_modified,
		al.status, ast.asset
		FROM encoding_queue_detail eqd
		INNER JOIN asset a ON a.upc = eqd.upc
		INNER JOIN asset_type ast ON ast.asset_type_id=a.asset_type_id
		INNER JOIN asset_location al ON al.asset_id = a.asset_id
		INNER JOIN storage_drive sd ON sd.storage_drive_id = al.storage_drive_id
		INNER JOIN storage s ON s.storage_id = sd.storage_id
		INNER JOIN storage_drive_asset_folder sdaf ON sdaf.storage_drive_id=sd.storage_drive_id AND sdaf.asset_type_id=a.asset_type_id
		WHERE s.physical_location_id in (1,2) AND ';
		switch ($searchTokenType) {
			case WebParameter::SearchByUpc:
				$sqlString .= 'eqd.upc = ' . $searchToken;
				break;
			case WebParameter::SearchByEQDetailId:
				$sqlString .= 'eqd.encoding_queue_detail_id = ' . $searchToken;
				break;
		}
		$sqlString .= ' GROUP BY eqd.upc, a.asset_id';
		return $this->formatString($sqlString);
	}

	/* Get Delivery Pickup Queue */
	private function getDeliveryPickupQuery( $searchTokenType, $searchToken ) {
		if ( empty( $searchToken ) && $searchToken != 0 ) {
			$sqlString = 'SELECT "" as eq_detail_id, d.dms_master_master_id, "" as upc,
			d.picked_up, COUNT(*) as status_count, "" as enc_end, "" as del_start, "" as del_srv
			FROM delivery_pickup_queue d
			GROUP BY d.dms_master_master_id, d.picked_up';
		} else {
			$sqlString = 'SELECT 
			d.encoding_queue_detail_id, d.dms_master_master_id, eqd.upc, d.picked_up,
			eqd.status, eqd.encoding_ended, eqd.delivery_started, eqd.delivery_server
			FROM delivery_pickup_queue d
			INNER JOIN encoding_queue_detail eqd ON eqd.encoding_queue_detail_id = d.encoding_queue_detail_id
			WHERE ';
			switch ($searchTokenType) {
				case WebParameter::SearchByUpc:
					$sqlString .= 'eqd.upc = ' . $searchToken;
					break;
				case WebParameter::SearchByEQDetailId:
					$sqlString .= 'd.encoding_queue_detail_id = ' . $searchToken;
					break;
				case WebParameter::SearchByDmsId:
					if ( $searchToken == 0 )
						$sqlString .= 'd.dms_master_master_id <> ' . $searchToken;
					else
						$sqlString .= 'd.dms_master_master_id = ' . $searchToken;
					break;
			}
		}
		return $this->formatString($sqlString);
	}

	/* Get Dms Metadata Encoding Profile Query */
	private function getDmsMetadataProfileQuery() {
		$sqlString = 'SELECT cm.customer_master_master_id, cm.customer_name, dep.product_type_id, dep.order_type, ep.encoding_profile_id, ep.description, ep.url
			FROM customer_master_master cm
			INNER JOIN dms_encoding_profile dep ON dep.dms_master_master_id=cm.customer_master_master_id
			INNER JOIN encoding_profile ep ON ep.encoding_profile_id=dep.encoding_profile_id AND ep.profile_type="metadata"
			ORDER BY cm.customer_master_master_id, dep.order_type, dep.product_type_id';
		return $this->formatString($sqlString);
	}

	
	private function convertResultSetToHTML($resultSet) {
		$resultString = '';
		while($row = mysql_fetch_array($resultSet)) {
			$rowCount = count($row)/2;
			$resultString .= '<tr>';
			for ( $i=0; $i < $rowCount; $i++ )
				if ( strlen( $row[$i] ) > 1000 )
					$resultString .= '<td style="font-size:75%">' . $row[$i] . '</td>';
				else
					$resultString .= '<td>' . $row[$i] . '</td>';
			$resultString .= '</tr>';
		}
		return $resultString;
	}
	
	private function formatString( $text ) {
		$text = str_replace("\n", "", $text);
		$text = str_replace("\t", "", $text);
		$text = preg_replace('/\s+/', ' ', $text);
		return $text;
	}
	
}