/**
* Creates a model for getModel(). Called only for each new $action.
*
* The parameters allow you to easily adapt the model to the current action. The $detailed
* parameter was added, because the most common use of action is a split between detailed
* and summarized actions.
*
* @param boolean $detailed True when the current action is not in $summarizedActions.
* @param string $action The current action.
* @return \MUtil_Model_ModelAbstract
*/
protected function createModel($detailed, $action)
{
$fields = array();
// Export all
if ('export' === $action) {
$detailed = true;
}
$organizations = $this->util->getDbLookup()->getOrganizations();
$fields[] = 'gtr_track_name';
$sql = "CASE WHEN gtr_organizations LIKE '%%|%s|%%' THEN 1 ELSE 0 END";
foreach ($organizations as $orgId => $orgName) {
$fields['O' . $orgId] = new \Zend_Db_Expr(sprintf($sql, $orgId));
}
$fields['total'] = new \Zend_Db_Expr("(LENGTH(gtr_organizations) - LENGTH(REPLACE(gtr_organizations, '|', ''))-1)");
$fields[] = 'gtr_id_track';
$select = $this->db->select();
$select->from('gems__tracks', $fields);
$model = new \MUtil_Model_SelectModel($select, 'track-verview');
$model->setKeys(array('gtr_id_track'));
$model->resetOrder();
$model->set('gtr_track_name', 'label', $this->_('Track name'));
$model->set('total', 'label', $this->_('Total'));
$model->setOnTextFilter('total', array($this, 'noTextFilter'));
foreach ($organizations as $orgId => $orgName) {
$model->set('O' . $orgId, 'label', $orgName, 'tdClass', 'rightAlign', 'thClass', 'rightAlign');
$model->setOnTextFilter('O' . $orgId, array($this, 'noTextFilter'));
if ($action !== 'export') {
$model->set('O' . $orgId, 'formatFunction', array($this, 'formatCheckmark'));
}
}
// \MUtil_Model::$verbose = true;
return $model;
}
/**
* Creates a model for getModel(). Called only for each new $action.
*
* The parameters allow you to easily adapt the model to the current action. The $detailed
* parameter was added, because the most common use of action is a split between detailed
* and summarized actions.
*
* @param boolean $detailed True when the current action is not in $summarizedActions.
* @param string $action The current action.
* @return \MUtil_Model_ModelAbstract
*/
public function createModel($detailed, $action)
{
$model = new \Gems_Model_JoinModel('resptrack', 'gems__respondent2track');
$model->addTable('gems__respondent2org', array('gr2t_id_user' => 'gr2o_id_user', 'gr2t_id_organization' => 'gr2o_id_organization'));
$model->addTable('gems__tracks', array('gr2t_id_track' => 'gtr_id_track'));
$model->addTable('gems__reception_codes', array('gr2t_reception_code' => 'grc_id_reception_code'));
$model->addFilter(array('grc_success' => 1));
$model->resetOrder();
$model->set('gr2o_patient_nr', 'label', $this->_('Respondent nr'));
$model->set('gr2t_start_date', 'label', $this->_('Start date'), 'dateFormat', 'dd-MM-yyyy');
$model->set('gr2t_end_date', 'label', $this->_('End date'), 'dateFormat', 'dd-MM-yyyy');
$filter = $this->getSearchFilter($action !== 'export');
if (!(isset($filter['gr2t_id_organization']) && $filter['gr2t_id_organization'])) {
$model->addFilter(array('gr2t_id_organization' => $this->currentUser->getRespondentOrgFilter()));
}
if (!(isset($filter['gr2t_id_track']) && $filter['gr2t_id_track'])) {
$model->setFilter(array('1=0'));
$this->autofilterParameters['onEmpty'] = $this->_('No track selected...');
return $model;
}
// Add the period filter - if any
if ($where = \Gems_Snippets_AutosearchFormSnippet::getPeriodFilter($filter, $this->db)) {
$model->addFilter(array($where));
}
$select = $this->db->select();
$select->from('gems__rounds', array('gro_id_round', 'gro_id_order', 'gro_round_description', 'gro_icon_file'))->joinInner('gems__surveys', 'gro_id_survey = gsu_id_survey', array('gsu_survey_name'))->where('gro_id_track = ?', $filter['gr2t_id_track'])->order('gro_id_order');
if (isset($filter['gsu_id_primary_group']) && $filter['gsu_id_primary_group']) {
$select->where('gsu_id_primary_group = ?', $filter['gsu_id_primary_group']);
}
$data = $this->db->fetchAll($select);
if (!$data) {
return $model;
}
$status = $this->util->getTokenData()->getStatusExpression();
$select = $this->db->select();
$select->from('gems__tokens', array('gto_id_respondent_track', 'gto_id_round', 'gto_id_token', 'status' => $status))->joinInner('gems__reception_codes', 'gto_reception_code = grc_id_reception_code', array())->where('gto_id_track = ?', $filter['gr2t_id_track'])->order('grc_success')->order('gto_id_respondent_track')->order('gto_round_order');
// \MUtil_Echo::track($this->db->fetchAll($select));
$newModel = new \MUtil_Model_SelectModel($select, 'tok');
$newModel->setKeys(array('gto_id_respondent_track'));
$transformer = new \MUtil_Model_Transform_CrossTabTransformer();
$transformer->addCrosstabField('gto_id_round', 'status', 'stat_')->addCrosstabField('gto_id_round', 'gto_id_token', 'tok_');
foreach ($data as $row) {
$name = 'stat_' . $row['gro_id_round'];
$transformer->set($name, 'label', \MUtil_Lazy::call('substr', $row['gsu_survey_name'], 0, 2), 'description', sprintf("%s\n[%s]", $row['gsu_survey_name'], $row['gro_round_description']), 'noSort', true, 'round', $row['gro_round_description'], 'roundIcon', $row['gro_icon_file']);
$transformer->set('tok_' . $row['gro_id_round']);
}
$newModel->addTransformer($transformer);
// \MUtil_Echo::track($data);
$joinTrans = new \MUtil_Model_Transform_JoinTransformer();
$joinTrans->addModel($newModel, array('gr2t_id_respondent_track' => 'gto_id_respondent_track'));
$model->resetOrder();
$model->set('gr2o_patient_nr');
$model->set('gr2t_start_date');
$model->addTransformer($joinTrans);
return $model;
}
/**
* Creates a model for getModel(). Called only for each new $action.
*
* The parameters allow you to easily adapt the model to the current action. The $detailed
* parameter was added, because the most common use of action is a split between detailed
* and summarized actions.
*
* @param boolean $detailed True when the current action is not in $summarizedActions.
* @param string $action The current action.
* @return \MUtil_Model_ModelAbstract
*/
public function createModel($detailed, $action)
{
$select = $this->getSelect();
// \MUtil_Model::$verbose = true;
$model = new \MUtil_Model_SelectModel($select, 'summary');
// Make sure of filter and sort for these fields
$model->set('gro_id_order');
$model->set('gto_id_track');
$model->set('gto_id_organization');
$model->resetOrder();
$model->set('gro_round_description', 'label', $this->_('Round'));
$model->set('gsu_survey_name', 'label', $this->_('Survey'));
$model->set('answered', 'label', $this->_('Answered'), 'tdClass', 'centerAlign', 'thClass', 'centerAlign');
$model->set('missed', 'label', $this->_('Missed'), 'tdClass', 'centerAlign', 'thClass', 'centerAlign');
$model->set('open', 'label', $this->_('Open'), 'tdClass', 'centerAlign', 'thClass', 'centerAlign');
$model->set('total', 'label', $this->_('Total'), 'tdClass', 'centerAlign', 'thClass', 'centerAlign');
// $model->set('future', 'label', $this->_('Future'), 'tdClass', 'centerAlign', 'thClass', 'centerAlign');
// $model->set('unknown', 'label', $this->_('Unknown'), 'tdClass', 'centerAlign', 'thClass', 'centerAlign');
// $model->set('is', 'label', ' ', 'tdClass', 'centerAlign', 'thClass', 'centerAlign');
// $model->set('success', 'label', $this->_('Success'), 'tdClass', 'centerAlign', 'thClass', 'centerAlign');
// $model->set('removed', 'label', $this->_('Removed'), 'tdClass', 'deleted centerAlign',
// 'thClass', 'centerAlign');
$model->set('ggp_name', 'label', $this->_('Filler'), 'column_expression', new \Zend_Db_Expr('COALESCE(gems__track_fields.gtf_field_name, gems__groups.ggp_name)'));
$filter = $this->getSearchFilter($action !== 'export');
if (!(isset($filter['gto_id_organization']) && $filter['gto_id_organization'])) {
$model->addFilter(array('gto_id_organization' => $this->currentUser->getRespondentOrgFilter()));
}
if (isset($filter['gto_id_track']) && $filter['gto_id_track']) {
// Add the period filter
if ($where = \Gems_Snippets_AutosearchFormSnippet::getPeriodFilter($filter, $this->db)) {
$select->joinInner('gems__respondent2track', 'gto_id_respondent_track = gr2t_id_respondent_track', array());
$model->addFilter(array($where));
}
} else {
$model->setFilter(array('1=0'));
$this->autofilterParameters['onEmpty'] = $this->_('No track selected...');
}
return $model;
}
/**
* Creates the base model.
*
* @return \MUtil_Model_SelectModel
*/
protected function createModel()
{
$groupby['period_1'] = new \Zend_Db_Expr("YEAR({$this->dateFrom})");
$date = new \MUtil_Date();
switch ($this->dateType) {
case 'D':
$keyCount = 1;
$groupby['period_1'] = new \Zend_Db_Expr("CONVERT({$this->dateFrom}, DATE)");
$date->setTime(0);
$date->addDay($this->dateFactor - $this->dateRange);
$start = $date->getIso();
for ($i = -$this->dateRange; $i <= $this->dateRange; $i++) {
if (0 == $i) {
$this->dateCurrentStart = clone $date;
$this->dateCurrentEnd = clone $date;
$this->dateCurrentEnd->setTimeToDayEnd();
}
$values = array();
$values['period_1'] = $date->get('yyyy-MM-dd');
$values['range'] = $i;
$requiredRows[$i] = $values;
$date->addDay(1);
}
$date->subSecond(1);
$end = $date->getIso();
break;
case 'W':
$keyCount = 2;
// Use MONDAY as start of week
$groupby['period_1'] = new \Zend_Db_Expr("substr(YEARWEEK(gto_valid_from, 3),1,4)");
//$groupby['period_1'] = new \Zend_Db_Expr("YEAR($this->dateFrom) - CASE WHEN WEEK($this->dateFrom, 1) = 0 THEN 1 ELSE 0 END");
$groupby['period_2'] = new \Zend_Db_Expr("WEEK({$this->dateFrom}, 3)");
$date->setWeekday(1);
$date->setTime(0);
$date->addWeek($this->dateFactor - $this->dateRange);
$start = $date->getIso();
for ($i = -$this->dateRange; $i <= $this->dateRange; $i++) {
if (0 == $i) {
$this->dateCurrentStart = clone $date;
$this->dateCurrentEnd = clone $date;
$this->dateCurrentEnd->addWeek(1)->subSecond(1);
}
$values = array();
$values['period_1'] = $date->get(\Zend_Date::YEAR);
$values['period_2'] = $date->get(\Zend_Date::WEEK);
// When monday is in the previous year, add one to the year
if ($date->get(\Zend_Date::DAY_OF_YEAR) > 14 && $date->get(\Zend_Date::WEEK) == 1) {
$values['period_1'] = $values['period_1'] + 1;
}
$values['range'] = $i;
$requiredRows[$i] = $values;
$date->addWeek(1);
}
$date->subSecond(1);
$end = $date->getIso();
break;
case 'M':
$keyCount = 2;
$groupby['period_2'] = new \Zend_Db_Expr("MONTH({$this->dateFrom})");
$date->setDay(1);
$date->setTime(0);
$date->addMonth($this->dateFactor - $this->dateRange);
$start = $date->getIso();
for ($i = -$this->dateRange; $i <= $this->dateRange; $i++) {
if (0 == $i) {
$this->dateCurrentStart = clone $date;
$this->dateCurrentEnd = clone $date;
$this->dateCurrentEnd->addMonth(1)->subSecond(1);
}
$values = array();
$values['period_1'] = $date->get(\Zend_Date::YEAR);
$values['period_2'] = $date->get(\Zend_Date::MONTH);
$values['range'] = $i;
$requiredRows[$i] = $values;
$date->addMonth(1);
}
$date->subSecond(1);
$end = $date->getIso();
break;
case 'Y':
$keyCount = 1;
$date->setDay(1);
$date->setMonth(1);
$date->setTime(0);
$date->addYear($this->dateFactor - $this->dateRange);
$start = $date->getIso();
for ($i = -$this->dateRange; $i <= $this->dateRange; $i++) {
if (0 == $i) {
$this->dateCurrentStart = clone $date;
$this->dateCurrentEnd = clone $date;
$this->dateCurrentEnd->addYear(1)->subSecond(1);
}
$values = array();
$values['period_1'] = $date->get(\Zend_Date::YEAR);
$values['range'] = $i;
$requiredRows[$i] = $values;
$date->addYear(1);
}
$date->subSecond(1);
$end = $date->getIso();
break;
default:
throw new \Gems_Exception_Coding('Incorrect date_type value: ' . $this->dateType);
}
$where = "{$this->dateFrom} BETWEEN '{$start}' AND '{$end}'";
for ($i = -$this->dateRange; $i <= $this->dateRange; $i++) {
$requiredRows[$i]['date_factor'] = $this->dateFactor + $i;
$requiredRows[$i]['df_link'] = null;
$requiredRows[$i]['df_label'] = null;
}
if ($this->dateRange > 0) {
$requiredRows[-$this->dateRange]['df_link'] = $this->dateFactor - $this->dateRange * 2;
$requiredRows[-$this->dateRange]['df_label'] = $this->_('<<');
$requiredRows[$this->dateRange]['df_link'] = $this->dateFactor + $this->dateRange * 2;
$requiredRows[$this->dateRange]['df_label'] = $this->_('>>');
if ($this->dateRange > 1) {
$i = intval($this->dateRange / 2);
$requiredRows[-$i]['df_link'] = $this->dateFactor - 1;
$requiredRows[-$i]['df_label'] = $this->_('<');
$requiredRows[$i]['df_link'] = $this->dateFactor + 1;
$requiredRows[$i]['df_label'] = $this->_('>');
}
$requiredRows[0]['df_link'] = $this->dateFactor ? '0' : null;
$requiredRows[0]['df_label'] = $this->_('Now!');
}
if ($this->dateFactor) {
$today = new \MUtil_Date();
$this->dateFactorChanges['D'] = $this->dateCurrentStart->diffDays($today);
$this->dateFactorChanges['W'] = $this->dateCurrentStart->diffWeeks($today);
$this->dateFactorChanges['M'] = $this->dateCurrentStart->diffMonths($today);
$this->dateFactorChanges['Y'] = $this->dateCurrentStart->diffYears($today);
} else {
$this->dateFactorChanges = array_fill_keys(array('D', 'W', 'M', 'Y'), 0);
}
// \MUtil_Echo::track($requiredRows);
// \MUtil_Echo::rs($start, $end, $where);
$select = new \Zend_Db_Select($this->db);
$select->from($this->dataTableName, $groupby + $this->getDbFields());
$select->where($where);
$select->group($groupby);
$this->processSelect($select);
// \MUtil_Echo::r((string) $select);
$model = new \MUtil_Model_SelectModel($select, $this->dataTableName);
// Display by column cannot use formatFunction as it is a simple repeater
// $model->set('duration_avg', 'formatFunction', $this->util->getLocalized()->formatNumber);
$transformer = new \MUtil_Model_Transform_RequiredRowsTransformer();
$transformer->setDefaultRow($this->getDefaultRow());
$transformer->setRequiredRows($requiredRows);
$transformer->setKeyItemCount($keyCount);
$model->addTransformer($transformer);
return $model;
}
/**
* Creates a model for getModel(). Called only for each new $action.
*
* The parameters allow you to easily adapt the model to the current action. The $detailed
* parameter was added, because the most common use of action is a split between detailed
* and summarized actions.
*
* @param boolean $detailed True when the current action is not in $summarizedActions.
* @param string $action The current action.
* @return \MUtil_Model_ModelAbstract
*/
protected function createModel($detailed, $action)
{
// Export all
if ('excel' === $action) {
$detailed = true;
}
if ($detailed) {
$year = $this->_('Year');
$month = $this->_('Month');
$fields[$year] = new \Zend_Db_Expr("YEAR(gr2o_created)");
$fields[$month] = new \Zend_Db_Expr("MONTH(gr2o_created)");
}
$consents = $this->util->getDbLookup()->getUserConsents();
$deleteds = array('' => '') + $this->util->getReceptionCodeLibrary()->getRespondentDeletionCodes();
$sql = "SUM(CASE WHEN grc_success = 1 AND gr2o_consent = '%s' THEN 1 ELSE 0 END)";
foreach ($consents as $consent => $translated) {
$fields[$translated] = new \Zend_Db_Expr(sprintf($sql, $consent));
}
$fields[$this->_('Total OK')] = new \Zend_Db_Expr("SUM(CASE WHEN grc_success = 1 THEN 1 ELSE 0 END)");
$sql = "SUM(CASE WHEN gr2o_reception_code = '%s' THEN 1 ELSE 0 END)";
foreach ($deleteds as $code => $translated) {
$fields[$translated] = new \Zend_Db_Expr(sprintf($sql, $code));
}
$fields[$this->_('Dropped')] = new \Zend_Db_Expr("SUM(CASE WHEN grc_success = 0 THEN 1 ELSE 0 END)");
$fields[$this->_('Total')] = new \Zend_Db_Expr("COUNT(*)");
$select = $this->db->select();
$select->from('gems__respondent2org', $fields)->joinInner('gems__reception_codes', 'gr2o_reception_code = grc_id_reception_code', array())->joinInner('gems__organizations', 'gr2o_id_organization = gor_id_organization', array('gor_name', 'gor_id_organization'));
$select->group(array('gor_name', 'gor_id_organization'));
if ($detailed) {
$select->group(array($fields[$year], $fields[$month]));
}
$model = new \MUtil_Model_SelectModel($select, 'consent-plan');
$model->setKeys(array('gor_id_organization'));
$model->resetOrder();
$model->set('gor_name', 'label', $this->_('Organization'));
foreach ($fields as $field => $expr) {
$model->set($field, 'label', $field, 'tdClass', 'rightAlign', 'thClass', 'rightAlign');
}
foreach ($deleteds as $code => $translated) {
$model->set($translated, 'tdClass', 'rightAlign smallTime', 'thClass', 'rightAlign smallTime');
}
foreach (array($this->_('Total OK'), $this->_('Dropped'), $this->_('Total')) as $name) {
$model->set($name, 'itemDisplay', \MUtil_Html::create('strong'), 'tableHeaderDisplay', \MUtil_Html::create('em'), 'tdClass', 'rightAlign selectedColumn', 'thClass', 'rightAlign selectedColumn');
}
if ($detailed) {
$model->set($month, 'formatFunction', $this->util->getLocalized()->getMonthName);
}
// Only show organisations the user is allowed to see
$allowed = $this->currentUser->getAllowedOrganizations();
$model->setFilter(array('gr2o_id_organization' => array_keys($allowed)));
// \MUtil_Model::$verbose = true;
return $model;
}
