<?php

namespace App\Config;

/**
 * Stores the matches for various entities asociatd with the imported orders.
 * @author Gabriel
 */
class ImportedOrders_Matches extends DbMultiAssocVarsAbstract
{

	/**
	 * The primary key (the list of columns) for this table - in the correct order.
	 * @var array
	 */
	protected $dbTablePkCols = ['entityType', 'idEntityExtSys', 'idSystem'];

	/**
	 * The column that contains the actual value for this association.
	 * @var String
	 */
	protected $dbTableValueCol='idEntityTina';

	/**
	 * Indicates the table column name which stores the last update time for the current record;
	 * if nothing is set, the info is not saved in the db.
	 * @var String
	 */
	protected $dbTableLastUpdateTimeCol = 'lastUpdateTime';

	/**
	 * Indicates the table column name which stores the last update user id for the current record;
	 * if nothing is set, the info is not saved in the db.
	 * @var String
	 */
	protected $dbTableLastUpdateUserCol = 'idUpdateUser';

	/**
	 * (non-PHPdoc)
	 * @see \App\Config\DbMultiAssocVarsAbstract::getDbTableName()
	 */
	protected function getDbTableName()
	{
		global $DBTImportedOrders_matches;
		return $DBTImportedOrders_matches;
	}


	/**
	 * Set a matching service type.
	 * @param Int $idSystem
	 * @param String $systemServiceCode
	 * @param String $tinaServiceCode
	 */
	public function setServiceType($idSystem, $systemServiceCode, $tinaServiceCode)
	{
		$pkFields = [
				'entityType' =>		'serviceType',
				'idEntityExtSys' =>	$systemServiceCode,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaServiceCode);
	}

	/**
	 * Sets a matching user.
	 * @param Int $idSystem
	 * @param String $systemIdUser
	 * @param Int $tinaIdUser
	 */
	public function setUser($idSystem, $systemIdUser, $tinaIdUser)
	{
		$pkFields = [
				'entityType' =>		'user',
				'idEntityExtSys' =>	$systemIdUser,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaIdUser);
	}

	/**
	 * Sets a matching office.
	 * @param Int $idSystem
	 * @param String $systemIdOffice
	 * @param Int $tinaIdOffice
	 */
	public function setOffice($idSystem, $systemIdOffice, $tinaIdOffice)
	{
		$pkFields = [
				'entityType' =>		'office',
				'idEntityExtSys' =>	$systemIdOffice,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaIdOffice);
	}

	/**
	 * Sets a matching customer.
	 * @param Int $idSystem
	 * @param String $systemIdCustomer
	 * @param Int $tinaIdCustomer
	 */
	public function setCustomer($idSystem, $systemIdCustomer, $tinaIdCustomer)
	{
		$pkFields = [
				'entityType' =>		'customer',
				'idEntityExtSys' =>	$systemIdCustomer,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaIdCustomer);
	}

	/**
	 * Sets a matching customer type.
	 * @param Int $idSystem
	 * @param String $systemCustomerType
	 * @param String $tinaCustomerType
	 */
	public function setCustomerType($idSystem, $systemCustomerType, $tinaCustomerType)
	{
		$pkFields = [
				'entityType' =>		'customer_type',
				'idEntityExtSys' =>	$systemCustomerType,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaCustomerType);
	}

	/**
	 * Sets a matching customer title.
	 * @param Int $idSystem
	 * @param String $systemCustomerTitle
	 * @param String $tinaCustomerTitle
	 */
	public function setCustomerTitle($idSystem, $systemCustomerTitle, $tinaCustomerTitle)
	{
		$pkFields = [
				'entityType' =>		'customer_title',
				'idEntityExtSys' =>	$systemCustomerTitle,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaCustomerTitle);
	}

	/**
	 * Sets a matching corporate.
	 * @param Int $idSystem
	 * @param String $systemIdCorporate
	 * @param Int $tinaIdCorporate
	 */
	public function setCorporate($idSystem, $systemIdCorporate, $tinaIdCorporate)
	{
		$pkFields = [
				'entityType' =>		'corporate',
				'idEntityExtSys' =>	$systemIdCorporate,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaIdCorporate);
	}

	/**
	 * Sets a matching supplier.
	 * @param Int $idSystem
	 * @param String $systemIdSupplier
	 * @param Int $tinaIdSupplier
	 */
	public function setSupplier($idSystem, $systemIdSupplier, $tinaIdSupplier)
	{
		$pkFields = [
				'entityType' =>		'supplier',
				'idEntityExtSys' =>	$systemIdSupplier,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaIdSupplier);
	}

	/**
	 * Sets a matching supplier product.
	 * @param Int $idSystem
	 * @param String $systemIdSupplierProduct
	 * @param Int $tinaIdSupplierProduct
	 */
	public function setSupplierProduct($idSystem, $systemIdSupplierProduct, $tinaIdSupplierProduct)
	{
		$pkFields = [
				'entityType' =>		'supplierProduct',
				'idEntityExtSys' =>	$systemIdSupplierProduct,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaIdSupplierProduct);
	}

	/**
	 * Sets a matching city.
	 * @param Int $idSystem
	 * @param String $systemCityId
	 * @param Int $tinaCityId
	 */
	public function setCity($idSystem, $systemCityId, $tinaCityId)
	{
		$pkFields = [
				'entityType' =>		'city',
				'idEntityExtSys' =>	$systemCityId,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaCityId);
	}

	/**
	 * Sets a matching station.
	 * @param Int $idSystem
	 * @param String $systemStationCode
	 * @param String $tinaStationCode
	 */
	public function setStation($idSystem, $systemStationCode, $tinaStationCode)
	{
		$pkFields = [
				'entityType' =>		'station',
				'idEntityExtSys' =>	$systemStationCode,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaStationCode);
	}

	/**
	 * Sets a matching service category.
	 * @param Int $idSystem
	 * @param String $systemServiceCode
	 * @param String $systemServiceCategoryCode
	 * @param String $tinaServiceCategoryCode
	 */
	public function setServiceCategory($idSystem, $systemServiceCode, $systemServiceCategoryCode, $tinaServiceCategoryCode)
	{
		$pkFields = [
				'entityType' =>		'service_category',
				'idEntityExtSys' =>	$systemServiceCode.'#'.$systemServiceCategoryCode,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaServiceCategoryCode);
	}

	/**
	 * Sets a matching service product type.
	 * @param Int $idSystem
	 * @param String $systemServiceCode
	 * @param String $systemServiceProductTypeCode
	 * @param String $tinaServiceProductTypeCode
	 */
	public function setServiceProductType($idSystem, $systemServiceCode, $systemServiceProductTypeCode, $tinaServiceProductTypeCode)
	{
		$pkFields = [
				'entityType' =>		'service_productType',
				'idEntityExtSys' =>	$systemServiceCode.'#'.$systemServiceProductTypeCode,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaServiceProductTypeCode);
	}

	/**
	 * Sets a matching service source.
	 * @param Int $idSystem
	 * @param String $systemServiceCode
	 * @param String $systemServiceSourceCode
	 * @param String $tinaServiceSourceCode
	 */
	public function setServiceSource($idSystem, $systemServiceCode, $systemServiceSourceCode, $tinaServiceSourceCode)
	{
		$pkFields = [
				'entityType' =>		'service_source',
				'idEntityExtSys' =>	$systemServiceCode.'#'.$systemServiceSourceCode,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaServiceSourceCode);
	}

	/**
	 * Sets a matching service multiplier.
	 * @param Int $idSystem
	 * @param String $systemServiceCode
	 * @param String $systemServiceMultiplierCode
	 * @param String $tinaServiceMultiplierCode
	 */
	public function setServiceMultiplier($idSystem, $systemServiceCode, $systemServiceMultiplierCode, $tinaServiceMultiplierCode)
	{
		$pkFields = [
				'entityType' =>		'service_multiplier',
				'idEntityExtSys' =>	$systemServiceCode.'#'.$systemServiceMultiplierCode,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaServiceMultiplierCode);
	}


	public function deleteServiceCategories($idSystem, $systemServiceCode)
	{
		$result = $this->searchByFields( [ 'entityType'=>'service_category', "`idEntityExtSys` LIKE '{$systemServiceCode}#%'", 'idSystem'=>$idSystem ] );

		foreach ($result as $row) {
			$pkFields = ['entityType' =>'service_category', 'idEntityExtSys' => $row['idEntityExtSys'], 'idSystem' => $idSystem];
			$this->set($pkFields, null);
		}
	}

	public function deleteServiceProductTypes($idSystem, $systemServiceCode)
	{
		$result = $this->searchByFields( [ 'entityType'=>'service_productType', "`idEntityExtSys` LIKE '{$systemServiceCode}#%'", 'idSystem'=>$idSystem ] );

		foreach ($result as $row) {
			$pkFields = ['entityType' =>'service_productType', 'idEntityExtSys' => $row['idEntityExtSys'], 'idSystem' => $idSystem];
			$this->set($pkFields, null);
		}
	}

	public function deleteServiceSources($idSystem, $systemServiceCode)
	{
		$result = $this->searchByFields( [ 'entityType'=>'service_source', "`idEntityExtSys` LIKE '{$systemServiceCode}#%'", 'idSystem'=>$idSystem ] );

		foreach ($result as $row) {
			$pkFields = ['entityType' =>'service_source', 'idEntityExtSys' => $row['idEntityExtSys'], 'idSystem' => $idSystem];
			$this->set($pkFields, null);
		}
	}

	public function deleteServiceMultipliers($idSystem, $systemServiceCode)
	{
		$result = $this->searchByFields( [ 'entityType'=>'service_multiplier', "`idEntityExtSys` LIKE '{$systemServiceCode}#%'", 'idSystem'=>$idSystem ] );

		foreach ($result as $row) {
			$pkFields = ['entityType' =>'service_multiplier', 'idEntityExtSys' => $row['idEntityExtSys'], 'idSystem' => $idSystem];
			$this->set($pkFields, null);
		}
	}

	/**
	 * Sets a matching offer form of payment
	 * @param Int $idSystem
	 * @param String $systemFop
	 * @param String $tinaFop
	 */
	public function setOfferFormOfPayment($idSystem, $systemFop, $tinaFop)
	{
		$pkFields = [
				'entityType' =>		'offerFormOfPayment',
				'idEntityExtSys' =>	$systemFop,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaFop);
	}

	/**
	 * Sets a matching offer form of cashing
	 * @param Int $idSystem
	 * @param String $systemFoc
	 * @param String $tinaFoc
	 */
	public function setOfferFormOfCashing($idSystem, $systemFoc, $tinaFoc)
	{
		$pkFields = [
				'entityType' =>		'offerFormOfCashing',
				'idEntityExtSys' =>	$systemFoc,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaFoc);
	}

	/**
	 * Sets a matching service cancel reason.
	 * @param Int $idSystem
	 * @param String $systemCancelReason
	 * @param String $tinaCancelReason
	 */
	public function setServiceCancelReason($idSystem, $systemCancelReason, $tinaCancelReason)
	{
		$pkFields = [
				'entityType' =>		'serviceCancelReason',
				'idEntityExtSys' =>	$systemCancelReason,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaCancelReason);
	}

	/**
	 * Sets a matching MICE project
	 * @param Int $idSystem
	 * @param String $systemCityId
	 * @param String $tinaCityId
	 */
	public function setMiceProject($idSystem, $systemMiceProject, $tinaMiceProject)
	{
		$pkFields = [
				'entityType' =>		'miceProject',
				'idEntityExtSys' =>	$systemMiceProject,
				'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaMiceProject);
	}

	/**
	 * Sets a matching service cost center
	 * @param Int $idSystem
	 * @param String $systemCityId
	 * @param String $tinaCityId
	 */
	public function setAgencyCostCenter($idSystem, $costCenterCode, $tinaCostCenter)
	{
		$pkFields = [
			'entityType' =>		'agencyCostCenter',
			'idEntityExtSys' =>	$costCenterCode,
			'idSystem' =>		$idSystem,
		];
		return $this->set($pkFields, $tinaCostCenter);
	}

	/**
	 * (non-PHPdoc)
	 * @see \App\Config\BaseAbstract::save_check()
	 */
	protected function save_check()
	{

		// global cfgs
		$CFG = \Engine\Loader_Cfg::getInstance()->getGlobal();

		// LOOP THROUGH EACH ITEM AND CHECK IT ////////////////////////////////////////////////////
		foreach($this->itemsList as $item) {

			// check the system id
			if (!\Entity\ExtResSystem::select($item['pkFields']['idSystem'])->recordExists()) {
				$this->addErrorMessage(sprintf('Invalid external system id `%s`.', $item['pkFields']['idSystem']));
			}

			if ($item['value']===null) {
				continue; // skip checking null values. These will get translated into a DELETE operation in MySQL
			}

			// check by entity type
			switch ($item['pkFields']['entityType']) {

				// service type
				case 'serviceType':
					if (!\App\Config\Services_Basic::getInstance()->checkService($item['value'])) {
						$this->addErrorMessage(sprintf('Invalid service code `%s`.', $item['value']));
					}
				break;

				// user check
				case 'user':
					if (!\Entity\User::select($item['value'])->recordExists()) {
						$this->addErrorMessage(sprintf('Invalid user id `%s`.', $item['value']));
					}
				break;

				// office check
				case 'office':
					if (!\Entity\Office::select($item['value'])->recordExists()) {
						$this->addErrorMessage(sprintf('Invalid office id `%s`.', $item['value']));
					}
				break;

				// customer check
				case 'customer':
					if (!\Entity\Customer::select($item['value'])->recordExists() or \Entity\Customer::select($item['value'])->deleted) {
						$this->addErrorMessage(sprintf('Invalid customer id `%s`.', $item['value']));
					}
				break;

				// corporate check
				case 'corporate':
					if (!\Entity\Corporate::select($item['value'])->recordExists() or \Entity\Corporate::select($item['value'])->deleted) {
						$this->addErrorMessage(sprintf('Invalid corporate id `%s`.', $item['value']));
					}
				break;

				// supplier check
				case 'supplier':
					if (!\Entity\Supplier::select($item['value'])->recordExists() or \Entity\Supplier::select($item['value'])->deleted) {
						$this->addErrorMessage(sprintf('Invalid supplier id `%s`.', $item['value']));
					}
				break;

				// supplier product check
				case 'supplierProduct':
					if (!\Entity\SupplierProduct::select($item['value'])->recordExists() or \Entity\SupplierProduct::select($item['value'])->deleted) {
						$this->addErrorMessage(sprintf('Invalid supplier product id `%s`.', $item['value']));
					}
				break;

				// city check
				case 'city':
					if (!\Entity\City::select($item['value'])->recordExists() or \Entity\City::select($item['value'])->deleted) {
						$this->addErrorMessage(sprintf('Invalid city id `%s`.', $item['value']));
					}
				break;

				// station check
				case 'station':
					if (!\Entity\Station::select($item['value'])->recordExists()) {
						$this->addErrorMessage(sprintf('Invalid station code `%s`.', $item['value']));
					}
				break;

				// customer type check
				case 'customer_type':
					if (!in_array($item['value'], $CFG['generalData']['personTypes'])) {
						$this->addErrorMessage(sprintf('Invalid customer type code `%s`.', $item['value']));
					}
				break;

				// customer title check
				case 'customer_title':
					if (!in_array($item['value'], $CFG['generalData']['personTitles'])) {
						$this->addErrorMessage(sprintf('Invalid customer title code `%s`.', $item['value']));
					}
				break;

				// service category check
				case 'service_category':
					list($systemServiceCode, $systemCategoryCode) = explode('#', $item['pkFields']['idEntityExtSys']);
					if (!$this->getServiceType($item['pkFields']['idSystem'], $systemServiceCode)) {
						$this->addErrorMessage(sprintf('Invalid service type `%s` when defining the category `%s`.', $systemServiceCode, $systemCategoryCode));
					}
				break;

				// service product type check
				case 'service_productType':
					list($systemServiceCode, $systemProductTypeCode) = explode('#', $item['pkFields']['idEntityExtSys']);
					if (!$this->getServiceType($item['pkFields']['idSystem'], $systemServiceCode)) {
						$this->addErrorMessage(sprintf('Invalid service type `%s` when defining the product type `%s`.', $systemServiceCode, $systemProductTypeCode));
					}
				break;

				// service source check
				case 'service_source':
					list($systemServiceCode, $systemServiceSourceCode) = explode('#', $item['pkFields']['idEntityExtSys']);
					if (!$this->getServiceType($item['pkFields']['idSystem'], $systemServiceCode)) {
						$this->addErrorMessage(sprintf('Invalid service type `%s` when defining the service source`%s`.', $systemServiceCode, $systemServiceSourceCode));
					}
				break;

				// service multiplier check
				case 'service_multiplier':
					list($systemServiceCode, $systemMultiplierCode) = explode('#', $item['pkFields']['idEntityExtSys']);
					if (!$this->getServiceType($item['pkFields']['idSystem'], $systemServiceCode)) {
						$this->addErrorMessage(sprintf('Invalid service type `%s` when defining the multiplier `%s`.', $systemServiceCode, $systemMultiplierCode));
					}
				break;

				// form of payment
				case 'offerFormOfPayment':
					if (!in_array($item['value'], $CFG['offers']['formOfPaymentList'])) {
						$this->addErrorMessage(sprintf('Invalid form of payment `%s`.', $item['value']));
					}
				break;

				// form of cashing
				case 'offerFormOfCashing':
					if (!in_array($item['value'], $CFG['offers']['formOfCashingList'])) {
						$this->addErrorMessage(sprintf('Invalid form of cashing `%s`.', $item['value']));
					}
				break;

				// service cancel reason
				case 'serviceCancelReason':
					$servCfg = \Entity\Service::loadCFG();
					if (in_array($item['value'], $servCfg['_special']['serviceCancelReasons'])) {
						$this->addErrorMessage(sprintf('Invalid service cancel reason `%s`.', $item['value']));
					}
				break;

				// mice project check
				case 'miceProject':
					if (!\Entity\MiceProject::select($item['value'])->recordExists()) {
						$this->addErrorMessage(sprintf('Invalid MICE project code `%s`.', $item['value']));
					}
				break;

				// agency cost center check
				case 'agencyCostCenter':
					if (!\App\Config\AgencyCostCenters::getInstance()->check($item['value'])) {
						$this->addErrorMessage(sprintf('Invalid agency cost center code `%s`.', $item['value']));
					}
				break;

				default: {
					$this->addErrorMessage(sprintf('Invalid entity type `%s`.', $item['pkFields']['entityType']));
				}

			}

		}

	}



	/**
	 * Gets the matched user id.
	 * @param Int $idSystem
	 * @param String $systemIdUser
	 * @return Int
	 */
	public function getUser($idSystem, $systemIdUser)
	{
		return $this->searchAssoc( [ 'entityType'=>'user', 'idEntityExtSys'=>$systemIdUser, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Gets the matched office id.
	 * @param Int $idSystem
	 * @param String $systemIdOffice
	 * @return Int
	 */
	public function getOffice($idSystem, $systemIdOffice)
	{
		return $this->searchAssoc( [ 'entityType'=>'office', 'idEntityExtSys'=>$systemIdOffice, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Gets the matched customer id.
	 * @param Int $idSystem
	 * @param String $systemIdCustomer
	 * @return Int
	 */
	public function getCustomer($idSystem, $systemIdCustomer)
	{
		return $this->searchAssoc( [ 'entityType'=>'customer', 'idEntityExtSys'=>$systemIdCustomer, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Gets the customer type.
	 * @param Int $idSystem
	 * @param String $systemCustomerType
	 * @return String
	 */
	public function getCustomerType($idSystem, $systemCustomerType)
	{
		return $this->searchAssoc( [ 'entityType'=>'customer_type', 'idEntityExtSys'=>$systemCustomerType, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Gets the matched customer title.
	 * @param Int $idSystem
	 * @param String $systemCustomerTitle
	 * @return String
	 */
	public function getCustomerTitle($idSystem, $systemCustomerTitle)
	{
		return $this->searchAssoc( [ 'entityType'=>'customer_title', 'idEntityExtSys'=>$systemCustomerTitle, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Gets the matched corporate id.
	 * @param Int $idSystem
	 * @param String $systemIdCorporate
	 * @return Int
	 */
	public function getCorporate($idSystem, $systemIdCorporate)
	{
		return $this->searchAssoc( [ 'entityType'=>'corporate', 'idEntityExtSys'=>$systemIdCorporate, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Gets the matched supplier id.
	 * @param Int $idSystem
	 * @param String $systemIdSupplier
	 * @return Int
	 */
	public function getSupplier($idSystem, $systemIdSupplier)
	{
		return $this->searchAssoc( [ 'entityType'=>'supplier', 'idEntityExtSys'=>$systemIdSupplier, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Gets the matched supplier product id.
	 * @param Int $idSystem
	 * @param String $systemIdSupplierProduct
	 * @return Int
	 */
	public function getSupplierProduct($idSystem, $systemIdSupplierProduct)
	{
		return $this->searchAssoc( [ 'entityType'=>'supplierProduct', 'idEntityExtSys'=>$systemIdSupplierProduct, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Gets the matched city id.
	 * @param Int $idSystem
	 * @param String $systemCityId
	 * @return Int
	 */
	public function getCity($idSystem, $systemCityId)
	{
		return $this->searchAssoc( [ 'entityType'=>'city', 'idEntityExtSys'=>$systemCityId, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Gets the matched station code.
	 * @param Int $idSystem
	 * @param String $systemStationCode
	 * @return String
	 */
	public function getStation($idSystem, $systemStationCode)
	{
		return $this->searchAssoc( [ 'entityType'=>'station', 'idEntityExtSys'=>$systemStationCode, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Gets the matched service type.
	 * @param Int $idSystem
	 * @param String $systemServiceCode
	 * @return String
	 */
	public function getServiceType($idSystem, $systemServiceCode)
	{
		return $this->searchAssoc( [ 'entityType'=>'serviceType', 'idEntityExtSys'=>$systemServiceCode, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Gets the matched service category.
	 * @param Int $idSystem
	 * @param String $systemServiceCode
	 * @param String $systemServiceCategoryCode
	 * @return String - the matched TINA service code
	 */
	public function getServiceCategory($idSystem, $systemServiceCode, $systemServiceCategoryCode)
	{
		return $this->searchAssoc( [ 'entityType'=>'service_category', 'idEntityExtSys'=>$systemServiceCode.'#'.$systemServiceCategoryCode, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Get a list of service categories
	 * @param int $idSystem
	 * @param String $systemServiceCode
	 * @return Array
	 */
	public function getServiceCategories($idSystem, $systemServiceCode)
	{
		$result = $this->searchByFields( [ 'entityType'=>'service_category', "`idEntityExtSys` LIKE '{$systemServiceCode}#%'", 'idSystem'=>$idSystem ] );
		foreach ($result as $k=>$row) {
			list($result[$k]['systemServiceCode'], $result[$k]['systemServiceCategoryCode']) = explode("#", $row['idEntityExtSys']);
		}

		return $result;
	}

	/**
	 * Gets the matched service product type.
	 * @param Int $idSystem
	 * @param String $systemServiceCode
	 * @param String $systemServiceProductTypeCode
	 * @return String
	 */
	public function getServiceProductType($idSystem, $systemServiceCode, $systemServiceProductTypeCode)
	{
		return $this->searchAssoc( [ 'entityType'=>'service_productType', 'idEntityExtSys'=>$systemServiceCode.'#'.$systemServiceProductTypeCode, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Get a list of service product types
	 * @param int $idSystem
	 * @param String $systemServiceCode
	 * @return Array
	 */
	public function getServiceProductTypes($idSystem, $systemServiceCode)
	{
		$result = $this->searchByFields( [ 'entityType'=>'service_productType', "`idEntityExtSys` LIKE '{$systemServiceCode}#%'", 'idSystem'=>$idSystem ] );
		foreach ($result as $k=>$row) {
			list($result[$k]['systemServiceCode'], $result[$k]['systemServiceProductTypeCode']) = explode("#", $row['idEntityExtSys']);
		}

		return $result;
	}


	/**
	 * Gets the matched service source.
	 * @param Int $idSystem
	 * @param String $systemServiceCode
	 * @param String $systemServiceSourceCode
	 * @return String
	 */
	public function getServiceSource($idSystem, $systemServiceCode, $systemServiceSourceCode)
	{
		return $this->searchAssoc( [ 'entityType'=>'service_source', 'idEntityExtSys'=>$systemServiceCode.'#'.$systemServiceSourceCode, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Get a list of service sources.
	 * @param int $idSystem
	 * @param String $systemServiceCode
	 * @return Array
	 */
	public function getServiceSources($idSystem, $systemServiceCode)
	{
		$result = $this->searchByFields( [ 'entityType'=>'service_source', "`idEntityExtSys` LIKE '{$systemServiceCode}#%'", 'idSystem'=>$idSystem ] );
		foreach ($result as $k=>$row) {
			list($result[$k]['systemServiceCode'], $result[$k]['systemServiceSourceCode']) = explode("#", $row['idEntityExtSys']);
		}

		return $result;
	}


	/**
	 * Gets the matched service multiplier.
	 * @param Int $idSystem
	 * @param String $systemServiceCode
	 * @param String $systemServiceMultiplierCode
	 * @return String
	 */
	public function getServiceMultiplier($idSystem, $systemServiceCode, $systemServiceMultiplierCode)
	{
		return $this->searchAssoc( [ 'entityType'=>'service_multiplier', 'idEntityExtSys'=>$systemServiceCode.'#'.$systemServiceMultiplierCode, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Get a list of service multipliers
	 * @param int $idSystem
	 * @param String $systemServiceCode
	 * @return Array
	 */
	public function getServiceMultipliers($idSystem, $systemServiceCode)
	{
		$result = $this->searchByFields( [ 'entityType'=>'service_multiplier', "`idEntityExtSys` LIKE '{$systemServiceCode}#%'", 'idSystem'=>$idSystem ] );
		foreach ($result as $k=>$row) {
			list($result[$k]['systemServiceCode'], $result[$k]['systemServiceMultiplierCode']) = explode("#", $row['idEntityExtSys']);
		}

		return $result;
	}

	/**
	 * Gets the matched offer form of payment.
	 * @param Int $idSystem
	 * @param String $systemServiceCode
	 * @param String $systemServiceMultiplierCode
	 * @return String
	 */
	public function getOfferFormOfPayment($idSystem, $systemFop)
	{
		return $this->searchAssoc( [ 'entityType'=>'offerFormOfPayment', 'idEntityExtSys'=>$systemFop, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Gets the matched offer form of cashing.
	 * @param Int $idSystem
	 * @param String $systemServiceCode
	 * @param String $systemServiceMultiplierCode
	 * @return String
	 */
	public function getOfferFormOfCashing($idSystem, $systemFop)
	{
		return $this->searchAssoc( [ 'entityType'=>'offerFormOfCashing', 'idEntityExtSys'=>$systemFop, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Gets the matched service cancel reason.
	 * @param Int $idSystem
	 * @param String $systemCancelReason
	 * @return String
	 */
	public function getServiceCancelReason($idSystem, $systemCancelReason)
	{
		return $this->searchAssoc( [ 'entityType'=>'serviceCancelReason', 'idEntityExtSys'=>$systemCancelReason, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Gets the matched MICE project id.
	 * @param Int $idSystem
	 * @param String $systemMiceProject
	 * @return Int
	 */
	public function getMiceProject($idSystem, $systemMiceProject)
	{
		return $this->searchAssoc( [ 'entityType'=>'miceProject', 'idEntityExtSys'=>$systemMiceProject, 'idSystem'=>$idSystem ] );
	}

	/**
	 * Gets the matched agency cost center id.
	 * @param Int $idSystem
	 * @param String $systemCostCenter
	 * @return Int
	 */
	public function getAgencyCostCenter($idSystem, $systemCostCenter)
	{
		return $this->searchAssoc( [ 'entityType'=>'agencyCostCenter', 'idEntityExtSys'=>$systemCostCenter, 'idSystem'=>$idSystem ] );
	}


	/**
	 * Generalised function that replaces direct SQL calls
	 * @param Array $fields
	 * @throws \Exception
	 */
	protected function searchByFields($fields)
	{
		$values = 		[];
		$conditions = 	[];
		foreach($fields as $fld=>$val) {
			if(is_numeric($fld)) {
				// numeric array, or custom condition
				$conditions[] = $val;
			}
			else {
				$conditions[] = "`{$fld}`=?";
				$values[] = 	$val;
			}
		}

		$db = \DB::getInstance();

		// compose the sql
		$query = "SELECT * FROM `{$this->dbTableName}` WHERE ".implode(' AND ', $conditions);
		$queryResult = $db->Execute($query, $values);
		if ($db->ErrorNo()) {
			\Log::getInstance()->dbError("searchAssoc failed during SQL execution.");
			throw new \Exception('The search assoc failed for class '.get_called_class().' due to a technical problem.');
		}

		return $queryResult->GetAll();
	}

}
