<?php
namespace Modules\Admin\KamSalesTargets; class Model extends \App\Mvc\Model_ListingAbstract { 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); $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; } } 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; } public function insertKamMonthTargets($idKam, $year, $month, $salesTarget, $contactsTarget) { $msgs = new \App\MessagesCollector(); global $DBTKamSalesTargets; $db = \DB::getInstance(); if (!\DB::isInsideTransaction()) { throw new \Exception("The operation can only be performed inside a transaction"); } 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']); } } 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)); } } if (!$month) { $msgs->addWarning($this->lang['messages']['mandatoryMonth']); } else { if ($month<1 or $month>12) { $msgs->addWarning(sprintf($this->lang['messages']['invalidMonth'], $month)); } } $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); } $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"); } } public function updateKamMonthTargets($idKam, $year, $month, $salesTarget, $contactsTarget) { $msgs = new \App\MessagesCollector(); global $DBTKamSalesTargets; $db = \DB::getInstance(); if (!\DB::isInsideTransaction()) { throw new \Exception("The operation can only be performed inside a transaction"); } $targets = $this->getKamYearTargets($idKam, $year); if (!$targets[$month]) { $msgs->addError($this->lang['messages']['invalidRecord']); } if ($msgs->hasErrorOrWarning()) { throw new \App\MessagesException($msgs); } $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"); } } public function deleteKamMonthTargets($idKam, $year, $month) { $msgs = new \App\MessagesCollector(); global $DBTKamSalesTargets; $db = \DB::getInstance(); if (!\DB::isInsideTransaction()) { throw new \Exception("The operation can only be performed inside a transaction"); } $targets = $this->getKamYearTargets($idKam, $year); if (!$targets[$month]) { $msgs->addError($this->lang['messages']['invalidRecord']); } if ($msgs->hasErrorOrWarning()) { throw new \App\MessagesException($msgs); } $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"); } } } 