<?php
namespace Modules\Admin\KamSalesTargets;

class Model extends \App\Mvc\Model_ListingAbstract
{
	/**
	 *
	 * {@inheritDoc}
	 * @see \App\Mvc\Model_ListingAbstract::list_execute()
	 */
	public function list_execute(\App\Mvc\View\Listing\Members\Params $listingParams)
	{
		$filters = $listingParams->getFilters();
		$listObj = new \App\EntityList\KamSalesTargets();
		$listObj->setColumns(['idKam', 'year', "GROUP_CONCAT(month, '_', salesTarget)" => 'salesTargets',
				"GROUP_CONCAT(month, '_', contactsTarget)" => 'contactsTarget'
		]);

		if ($filters['idKam']['id']) {
			$listObj->setFilter('idKam', '=', $filters['idKam']['id']);
		}

		if ($filters['year']) {
			$listObj->setFilter('year', '=', $filters['year']);
		}

		$listObj->setGroupBy('idKam');
		$listObj->setGroupBy('year');
		$listObj = $this->entityList_inheritListingParams($listingParams, $listObj);

		// add month columns
		$recordsList = [];
		foreach ($listObj->exportRecords() as $row) {
			$salesTargets   = explode(",", $row['salesTargets']);
			$contactsTarget = explode(",", $row['contactsTarget']);

			$record = [
				'idKam' => $row['idKam'],
				'year'  => $row['year'],
			];

			foreach ($salesTargets as $monthTarget) {
				list($month, $target) = explode("_", $monthTarget);
				$record['sales_'.$month] = $target;
			}

			foreach ($contactsTarget as $monthTarget) {
				list($month, $target) = explode("_", $monthTarget);
				$record['contacts_'.$month] = $target;
			}

			$recordsList[] = $record;
		}

		if ($recordsList) {
			$this->recordsList = $recordsList;
			$this->totalRecordsCount =  $listObj->getRecordsCount();
		}
		else {
			$this->recordsList = [];
			$this->totalRecordsCount = 0;
		}
	}

	/**
	 * Get targets for a single KAM and year - with the month as array key
	 * @param integer $idKam
	 * @param integer $year
	 * @return array
	 */
	public function getKamYearTargets($idKam, $year)
	{
		$listObj = new \App\EntityList\KamSalesTargets();
		$listObj->setColumns(['idKam', 'year', 'month', 'salesTarget', 'contactsTarget']);
		$listObj->setFilter('idKam', '=', $idKam);
		$listObj->setFilter('year', '=', $year);
		$listObj->setOrderBy('month', 'ASC');

		$resultsRaw = $listObj->exportRecords();
		$result = [];
		foreach ($resultsRaw as $resultRaw) {
			$result[$resultRaw['month']] = $resultRaw;
		}

		return $result;
	}

	/**
	 * Insert a month targets for KAM
	 * @param integer $idKam
	 * @param integer $year
	 * @param integer $month
	 * @param float $salesTarget
	 * @param integer $contactsTarget
	 * @throws \Exception
	 * @throws \App\MessagesException
	 */
	public function insertKamMonthTargets($idKam, $year, $month, $salesTarget, $contactsTarget)
	{
		$msgs = new \App\MessagesCollector();

		global $DBTKamSalesTargets;
		$db = \DB::getInstance();

		// VALIDATONS

		// check in transaction
		if (!\DB::isInsideTransaction()) {
			throw new \Exception("The operation can only be performed inside a transaction");
		}

		// check user
		if (!$idKam) {
			$msgs->addWarning($this->lang['messages']['mandatoryKam']);
		}
		else {
			$userEnt = \Entity\User::selectForUpdate($idKam);
			if (!$userEnt->recordExists() or $userEnt->deleted) {
				$msgs->addError($this->lang['messages']['invalidKam']);
			}
		}

		// check year
		if (!$year) {
			$msgs->addWarning($this->lang['messages']['mandatoryYear']);
		}
		else {
			$start = TODAY_YEAR-1;
			$end   = TODAY_YEAR+2;
			if ($year<$start or $year>$end) {
				$msgs->addWarning(sprintf($this->lang['messages']['invalidYear'], $start, $end));
			}
		}

		// check month
		if (!$month) {
			$msgs->addWarning($this->lang['messages']['mandatoryMonth']);
		}
		else {
			if ($month<1 or $month>12) {
				$msgs->addWarning(sprintf($this->lang['messages']['invalidMonth'], $month));
			}
		}

		// check kam-year-month duplicate
		$targets = $this->getKamYearTargets($idKam, $year);
		if ($targets[$month]) {
			$msgs->addWarning(sprintf($this->lang['messages']['duplicateTargets'], $year, $month));
		}

		if ($msgs->hasErrorOrWarning()) {
			throw new \App\MessagesException($msgs);
		}


		// INSERT
		$sql = "
			INSERT INTO `$DBTKamSalesTargets`
				(`idKam`, `year`, `month`, `salesTarget`, `contactsTarget`, `addTime`, `idAddUser`)
			VALUES (?, ?, ?, ?, ?, ?, ?)
		";

		$db->Execute($sql, [$idKam, $year, $month, $salesTarget, $contactsTarget, \Engine\DateTime::create()->formatDatetimeIso(), CURRENT_ID_USER]);

		if ($db->ErrorNo()) {
			throw new \DBException("There was a technical error when inserting KAM target");
		}
	}


	/**
	 * Update a month targets for KAM
	 * @param integer $idKam
	 * @param integer $year
	 * @param integer $month
	 * @param float $salesTarget
	 * @param integer $contactsTarget
	 * @throws \Exception
	 * @throws \App\MessagesException
	 */
	public function updateKamMonthTargets($idKam, $year, $month, $salesTarget, $contactsTarget)
	{
		$msgs = new \App\MessagesCollector();

		global $DBTKamSalesTargets;
		$db = \DB::getInstance();

		// VALIDATONS

		// check in transaction
		if (!\DB::isInsideTransaction()) {
			throw new \Exception("The operation can only be performed inside a transaction");
		}

		// check kam-year-month exists
		$targets = $this->getKamYearTargets($idKam, $year);
		if (!$targets[$month]) {
			$msgs->addError($this->lang['messages']['invalidRecord']);
		}

		if ($msgs->hasErrorOrWarning()) {
			throw new \App\MessagesException($msgs);
		}


		// UPDATE
 		$sql = "
			UPDATE `$DBTKamSalesTargets`
				SET `salesTarget`=?, `contactsTarget`=?, `lastUpdateTime`=?, `idUpdateUser`=?, `updatesCount`=`updatesCount`+1
			WHERE `idKam`=?
				AND `year`=? AND `month`=?
		";

 		$db->Execute($sql, [$salesTarget, $contactsTarget, DATETIME, CURRENT_ID_USER, $idKam, $year, $month]);

		if ($db->ErrorNo()) {
			throw new \DBException("There was a technical error when updating KAM target");
		}
	}


	/**
	 * Delete a month targets for KAM
	 * @param integer $idKam
	 * @param integer $year
	 * @param integer $month
	 */
	public function deleteKamMonthTargets($idKam, $year, $month)
	{
		$msgs = new \App\MessagesCollector();

		global $DBTKamSalesTargets;
		$db = \DB::getInstance();

		// VALIDATONS

		// check in transaction
		if (!\DB::isInsideTransaction()) {
			throw new \Exception("The operation can only be performed inside a transaction");
		}

		// check kam-year-month exists
		$targets = $this->getKamYearTargets($idKam, $year);
		if (!$targets[$month]) {
			$msgs->addError($this->lang['messages']['invalidRecord']);
		}

		if ($msgs->hasErrorOrWarning()) {
			throw new \App\MessagesException($msgs);
		}


		// DELETE
		$sql = "
			DELETE FROM `$DBTKamSalesTargets`
				WHERE `idKam`=?
					AND `year`=? AND `month`=?
		";

		$db->Execute($sql, [$idKam, $year, $month]);

		if ($db->ErrorNo()) {
			throw new \DBException("There was a technical error when deleting KAM target");
		}
	}

}
