/**
* Find filtered records
*
* @param array $filter
* @param \Tk\Db\Tool $tool
* @return \Tk\Db\ArrayObject
*/
protected function findCompanies($filter = array(), $tool = null)
{
/* @var \Ext\Db\CompanyMap $mapper */
$mapper = \Ext\Db\Company::getMapper();
$now = \Tk\Date::create();
$where = '';
if (!isset($filter['dateFrom'])) {
$filter['dateFrom'] = \Tk\Date::create($now->getYear() . '-01-01')->floor();
}
if (!isset($filter['dateTo'])) {
$filter['dateTo'] = \Tk\Date::create($now->getYear() . '-12-31')->ceil();
}
$orderBy = $tool->getOrderBy();
$dateFrom = $mapper->getDb()->quote($filter['dateFrom']->toString());
$dateTo = $mapper->getDb()->quote($filter['dateTo']->toString());
if (!empty($filter['courseId'])) {
$where .= ' AND b.courseId = ' . (int) $filter['courseId'];
}
if (!empty($filter['termId'])) {
$where .= ' AND d.termId = ' . (int) $filter['termId'];
}
if (!empty($filter['placementTypeId'])) {
if (!is_array($filter['placementTypeId'])) {
$filter['placementTypeId'] = array($filter['placementTypeId']);
}
$statusStr = '';
foreach ($filter['placementTypeId'] as $s) {
$statusStr .= sprintf('d.`placementTypeId` = %s OR ', (int) $s);
}
if ($statusStr) {
$where .= ' AND (' . substr($statusStr, 0, -3) . ') ';
}
}
if (!empty($filter['keywords'])) {
$kw = '%' . $mapper->getDb()->escapeString($filter['keywords']) . '%';
$w = '';
$w .= sprintf('a.`name` LIKE %s OR ', $mapper->getDb()->quote($kw));
if (is_numeric($filter['keywords'])) {
$id = (int) $filter['keywords'];
$w .= sprintf('a.`id` = %d OR ', $id);
}
if ($w) {
$where .= ' AND (' . substr($w, 0, -3) . ') ';
}
}
$sql = <<<SQL
SELECT a.id, a.name, a.email, a.country, a.status, a.created
,SUM(d.units) as 'totalUnits'
,COUNT(d.id) as 'totalPlaces'
FROM company a, company_course b
LEFT JOIN term c ON (b.courseId = c.courseId )
LEFT JOIN placement d ON (c.id = d.termId AND d.status = 'completed' AND d.dateTo > {$dateFrom} AND d.dateFrom < {$dateTo})
WHERE a.id = b.companyId
AND a.status = 'approved'
AND d.companyId = a.id
AND d.units > 0
{$where}
GROUP BY a.id
ORDER BY {$orderBy}
SQL;
$result = $mapper->getDb()->query($sql);
$list = $result->fetchAll();
return $list;
}
/**
* execute
*
* @param \Form\Form $form
*/
public function update($form)
{
$object = new \LTI_Tool_Consumer($form->getFieldValue('key'), $this->getConfig()->getLtiDataConnector());
// Load object
$object->name = $form->getFieldValue('name');
$object->secret = $form->getFieldValue('secret');
$object->enabled = $form->getFieldValue('enabled');
$object->protected = $form->getFieldValue('protected');
$object->css_path = $form->getFieldValue('css_path');
$object->updated = \Tk\Date::create()->getTimestamp();
// validate object
if (!$object->name) {
$form->addFieldError('name', 'Please supply a valid consumer name');
}
if (!preg_match('/^[a-z0-9_-]{1,64}$/i', $object->getKey())) {
$form->addFieldError('consumerKey', 'Invalid characters used in key field');
}
if (!preg_match('/^[a-z0-9]{1,64}$/i', $object->secret)) {
$form->addFieldError('secret', 'Invalid characters used in secret field');
}
if ($form->hasErrors()) {
if (!\Mod\Notice::hasMessages()) {
\Mod\Notice::addError('The form contains errors.');
}
return;
}
if (!$this->getConfig()->getLtiDataConnector()->Tool_Consumer_save($object)) {
if (!\Mod\Notice::hasMessages()) {
\Mod\Notice::addError('Error saving consumer to DB.');
}
return;
}
//$this->getRedirectUrl()->set('key', $object->getKey())->redirect();
$this->getRedirectUrl()->redirect();
}
public function __construct()
{
$this->modified = \Tk\Date::create();
$this->created = \Tk\Date::create();
}
/**
* Returns a property value ready to insert into a csv file
* Override this for types that you want to render differently in csv
*
*
* @param stdClass $obj
* @return string
*/
public function getCsv($obj)
{
$value = parent::getPropertyValue($obj);
return \Tk\Date::create($value)->toString();
}
