public function actionIndex()
{
Yii::app()->db->createCommand('SET SESSION group_concat_max_len=150000')->execute();
$reqFields = Yii::app()->request->getParam('fields', null);
$reqCond = Yii::app()->request->getParam('cond', null);
$reqAcondval = Yii::app()->request->getParam('acondval', null);
$reqCondval = Yii::app()->request->getParam('condval', null);
$reqExcel = Yii::app()->request->getParam('excel', null);
//var_dump($reqCondval);exit();
$fields = array();
$fields[0] = array('text' => 'ПІБ персони');
$fields[1] = array('text' => 'Дата народження');
$fields[2] = array('text' => 'Адреса КОАТУУ');
$fields[3] = array('text' => 'Країна громадянства');
$fields[4] = array('text' => 'Закінчено навчальний заклад');
$fields[5] = array('text' => 'Місце народження');
$fields[6] = array('text' => 'Іноземна мова');
$fields[7] = array('text' => 'Спеціальність');
$fields[8] = array('text' => 'Факультет');
$fields[9] = array('text' => 'На бюджет');
$fields[10] = array('text' => 'На контракт');
$fields[11] = array('text' => 'Потрібен гуртожиток');
$fields[12] = array('text' => 'Статус заявки');
$fields[13] = array('text' => 'Дата створення заявки');
$fields[14] = array('text' => 'ЗНО (інформація)');
$fields[15] = array('text' => 'Форма навчання');
$fields[16] = array('text' => 'Іспити (інформація)');
$fields[17] = array('text' => 'ОКР');
$fields[18] = array('text' => 'Документи');
$fields[19] = array('text' => 'Пільги');
$fields[20] = array('text' => 'Тип пільги');
$fields[21] = array('text' => 'Першочергово');
$fields[22] = array('text' => 'Поза конкурсом');
$fields[23] = array('text' => 'Напрям');
$fields[24] = array('text' => 'Тип документа');
$fields[25] = array('text' => 'Курси ДП');
$fields[26] = array('text' => 'Номер справи');
$fields[27] = array('text' => 'Копія');
$fields[28] = array('text' => 'Відзнака');
$fields[29] = array('text' => 'Номер заяви');
$fields[30] = array('text' => 'Іноземна мова заяви');
$fields[31] = array('text' => 'Базова спеціальність');
$fields[32] = array('text' => 'Додатковий балл');
$fields[33] = array('text' => 'Приорітет');
if (!is_string($reqFields)) {
throw new CHttpException(400, 'Помилка. Невірний запит.');
}
$field_nums = explode(',', $reqFields);
$field_num_indexes = array_flip($field_nums);
if (count($field_nums) == 0) {
throw new CHttpException(400, 'Помилка. Невірний запит.');
}
$criteria = new CDbCriteria();
$select = array('*');
$with_rel = array();
$widget_columns = array();
$keys = array('idPersonSpeciality');
$group = 'idPerson';
for ($i = 0; $i < count($reqCond); $i++) {
$condition_type = $reqCond[$i];
if (isset($reqCondval[$i])) {
$condition_value = $reqCondval[$i];
} else {
$condition_value = "none";
}
if (isset($reqAcondval[$i])) {
$alternative_condition_value = $reqAcondval[$i];
} else {
$alternative_condition_value = true;
}
$to_select = in_array($i, $field_nums);
if (!$to_select && !$condition_type) {
continue;
}
$header = false;
if (isset($fields[$i])) {
$header = $fields[$i]['text'];
}
switch ($i) {
case 0:
$rels = array(array('name' => 'person', 'select' => true));
$sel = array('to_select' => $to_select, 'sql_as' => 'NAME', 'sql_expr' => "concat_ws(' ',trim(person.LastName),trim(person.FirstName),person.MiddleName)");
$widget_column = array('name' => 'NAME', 'header' => $header, 'value' => function ($data) {
echo $data->NAME;
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextOnly($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
////////////////////////////////////////////
break;
case 1:
$rels = array(array('name' => 'person', 'select' => true));
$sel = array('to_select' => $to_select, 'db_field' => 'person.Birthday');
$widget_column = array('name' => 'person.Birthday', 'header' => $header, 'value' => function ($data) {
echo !empty($data->person) ? $data->person->Birthday : "";
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldDateOnly($with_rel, $rels, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $alternative_condition_value, $criteria);
////////////////////////////////////////////
break;
case 2:
//Спеціально для ..баного КОАТУУ
$this->addRelationTo('person', $with_rel);
$KOATUU3_sql = "(SELECT concat(KOATUULevel3FullName,' (тип: ',KOATUULevel3Type,')') FROM koatuulevel3 " . "WHERE person.KOATUUCodeID=idKOATUULevel3)";
$KOATUU2_sql = "(SELECT KOATUULevel2FullName FROM koatuulevel2 " . "WHERE person.KOATUUCodeID=idKOATUULevel2)";
$KOATUU1_sql = "(SELECT KOATUULevel1FullName FROM koatuulevel1 " . "WHERE person.KOATUUCodeID=idKOATUULevel1)";
$KOATUU_sql = "IF (ISNULL(" . $KOATUU3_sql . ")," . "IF (ISNULL(" . $KOATUU2_sql . ")," . "IF (ISNULL(" . $KOATUU1_sql . "),NULL," . $KOATUU1_sql . ")," . $KOATUU2_sql . ")," . $KOATUU3_sql . ")";
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
if ($to_select) {
array_push($select, new CDbExpression($KOATUU_sql . " AS KOATUU"));
$widget_columns[$field_num_index] = array('name' => 'KOATUU', 'header' => $header, 'value' => function ($data) {
echo $data->KOATUU;
});
}
if ($condition_type == 1 && $alternative_condition_value) {
$criteria->addCondition("person.KOATUUCodeID = '" . $alternative_condition_value . "'");
}
if (($condition_type == 2 || $condition_type == 3) && $condition_value) {
$koatuu_keys = explode('|', $condition_value);
$result_ids = array();
$denial = $condition_type == 3 ? "NOT" : "";
$kriteria1 = new CDbCriteria();
$kriteria2 = new CDbCriteria();
$kriteria3 = new CDbCriteria();
foreach ($koatuu_keys as $koatuu_key) {
$or_koatuu_keys = explode('&', $koatuu_key);
if (count($or_koatuu_keys) > 1) {
$koatuu1_like_disjunct = '1';
$koatuu2_like_disjunct = '1';
$koatuu3_like_disjunct = '1';
foreach ($or_koatuu_keys as $koatuu_k) {
$koatuu3_like_disjunct .= " AND (concat(KOATUULevel3FullName," . "' (тип: ',KOATUULevel3Type,')') LIKE '%" . $koatuu_k . "%')";
$koatuu2_like_disjunct .= ' AND (KOATUULevel2FullName LIKE "%' . $koatuu_k . '%")';
$koatuu1_like_disjunct .= ' AND (KOATUULevel1FullName LIKE "%' . $koatuu_k . '%")';
}
$kriteria3->addCondition($koatuu3_like_disjunct, 'OR');
$kriteria2->addCondition($koatuu2_like_disjunct, 'OR');
$kriteria1->addCondition($koatuu1_like_disjunct, 'OR');
} else {
$kriteria3->addCondition("concat(KOATUULevel3FullName," . "' (тип: ',KOATUULevel3Type,')') LIKE '%" . $koatuu_key . "%'", "OR");
$kriteria2->addCondition('KOATUULevel2FullName LIKE "%' . $koatuu_key . '%"', "OR");
$kriteria1->addCondition('KOATUULevel1FullName LIKE "%' . $koatuu_key . '%"', "OR");
}
}
$k3models = KoatuuLevel3::model()->findAll($kriteria3);
foreach ($k3models as $model) {
/* @var $model KoatuuLevel3 */
$result_ids[$model->idKOATUULevel3] = $model->idKOATUULevel3;
}
$k2models = KoatuuLevel2::model()->findAll($kriteria2);
foreach ($k2models as $model) {
/* @var $model KoatuuLevel2 */
$result_ids[$model->idKOATUULevel2] = $model->idKOATUULevel2;
}
$k1models = KoatuuLevel1::model()->findAll($kriteria1);
foreach ($k1models as $model) {
/* @var $model KoatuuLevel1 */
$result_ids[$model->idKOATUULevel1] = $model->idKOATUULevel1;
}
$koatuu_id_in = implode(',', $result_ids);
if (!$koatuu_id_in) {
$criteria->addCondition('person.KOATUUCodeID IS ' . $denial . ' NULL');
} else {
$criteria->addCondition('person.KOATUUCodeID ' . $denial . ' IN (' . $koatuu_id_in . ')');
}
}
if ($condition_type == 4) {
$criteria->addCondition("person.KOATUUCodeID = '' OR ISNULL(person.KOATUUCodeID)");
}
//KOATUU END ////////////////////////////////////////////////////
break;
case 3:
$rels = array(array('name' => 'person', 'select' => true), array('name' => 'person.country', 'select' => true));
$sel = array('to_select' => $to_select, 'db_field_id' => 'person.CountryID', 'db_field' => 'country.CountryName');
$widget_column = array('name' => 'country.CountryName', 'header' => $header, 'value' => function ($data) {
if (!empty($data->person->country)) {
echo $data->person->country->CountryName;
}
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextAlternative($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $alternative_condition_value, $criteria);
////////////////////////////////////////////
break;
case 4:
$rels = array(array('name' => 'person', 'select' => true), array('name' => 'person.school', 'select' => true));
$sel = array('to_select' => $to_select, 'db_field_id' => 'person.SchoolID', 'db_field' => 'school.SchoolName');
$widget_column = array('name' => 'school.SchoolName', 'header' => $header, 'value' => function ($data) {
if (!empty($data->person->school)) {
echo $data->person->school->SchoolName;
}
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextAlternative($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $alternative_condition_value, $criteria);
////////////////////////////////////////////
break;
case 5:
$rels = array(array('name' => 'person', 'select' => true));
$sel = array('to_select' => $to_select, 'db_field' => 'person.BirthPlace');
$widget_column = array('name' => 'person.BirthPlace', 'header' => $header, 'value' => function ($data) {
echo $data->person->BirthPlace;
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextOnly($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
///////////////////////////////////////
break;
case 6:
$rels = array(array('name' => 'person', 'select' => true), array('name' => 'person.language', 'select' => true));
$sel = array('to_select' => $to_select, 'db_field_id' => 'person.LanguageID', 'db_field' => 'language.LanguagesName');
$widget_column = array('name' => 'language.LanguagesName', 'header' => $header, 'value' => function ($data) {
if (!empty($data->person->language)) {
echo $data->person->language->LanguagesName;
}
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextAlternative($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $alternative_condition_value, $criteria);
////////////////////////////////////////////
break;
case 7:
// $group = 't.idPersonSpeciality';
$SPEC_sql = "concat_ws(' '," . "SpecialityClasifierCode," . "(case substr(SpecialityClasifierCode,1,1) when '6' then " . "SpecialityDirectionName else SpecialityName end)," . "(case SpecialitySpecializationName when '' then '' " . " else concat('(',SpecialitySpecializationName,')') end)" . ",',',concat('форма: ',eduform.PersonEducationFormName))";
$rels = array(array('name' => 'sepciality', 'select' => false), array('name' => 'sepciality.eduform', 'select' => false));
$sel = array('to_select' => $to_select, 'db_field_id' => 't.SepcialityID', 'sql_as' => 'SPEC', 'sql_expr' => $SPEC_sql, 'group_concat' => true);
$widget_column = array('name' => 'SPEC', 'header' => $header, 'value' => function ($data) {
$_arr = array();
$_nodes = explode('||', $data->SPEC);
foreach ($_nodes as $_node) {
$_subjs = explode(';;', $_node);
if (!$_node) {
continue;
}
$is_first = true;
foreach ($_subjs as $_subj) {
if (isset($_arr[$_subj]) || !$_subj) {
continue;
}
$_arr[$_subj] = true;
if (!$is_first) {
echo '<br/>';
} else {
$is_first = false;
}
echo $_subj;
}
}
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextAlternative($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $alternative_condition_value, $criteria);
////////////////////////////////////////////
break;
case 8:
$group = 't.idPersonSpeciality';
$rels = array(array('name' => 'sepciality', 'select' => true), array('name' => 'sepciality.facultet', 'select' => true));
$sel = array('to_select' => $to_select, 'db_field' => 'facultet.FacultetFullName');
$widget_column = array('name' => 'facultet.FacultetFullName', 'header' => $header, 'value' => function ($data) {
if (!empty($data->sepciality->facultet)) {
echo $data->sepciality->facultet->FacultetFullName;
}
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextOnly($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
///////////////////////////////////////
break;
case 9:
$group = 't.idPersonSpeciality';
$sel = array('to_select' => $to_select, 'db_field' => 'isBudget');
$widget_column = array('name' => 'isBudget', 'header' => $header, 'value' => function ($data) {
echo $data->isBudget ? "так" : "ні";
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldCheckboxOnly($with_rel, $rels, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
////////////////////////////////////////////
break;
case 10:
$group = 't.idPersonSpeciality';
$sel = array('to_select' => $to_select, 'db_field' => 'isContract');
$widget_column = array('name' => 'isContract', 'header' => $header, 'value' => function ($data) {
echo $data->isContract ? "так" : "ні";
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldCheckboxOnly($with_rel, $rels, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
////////////////////////////////////////////
break;
case 11:
$group = 't.idPersonSpeciality';
$sel = array('to_select' => $to_select, 'db_field' => 'isNeedHostel');
$widget_column = array('name' => 'isNeedHostel', 'header' => $header, 'value' => function ($data) {
echo $data->isNeedHostel ? "так" : "ні";
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldCheckboxOnly($with_rel, $rels, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
////////////////////////////////////////////
break;
case 12:
$group = 't.idPersonSpeciality';
$rels = array(array('name' => 'status', 'select' => true));
$sel = array('to_select' => $to_select, 'db_field_id' => 't.StatusID', 'db_field' => 'status.PersonRequestStatusTypeName');
$widget_column = array('name' => 'status.PersonRequestStatusTypeName', 'header' => $header, 'value' => function ($data) {
if (!empty($data->status)) {
echo $data->status->PersonRequestStatusTypeName;
}
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextAlternative($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $alternative_condition_value, $criteria);
////////////////////////////////////////////
break;
case 13:
$group = 't.idPersonSpeciality';
$sel = array('to_select' => $to_select, 'db_field' => 't.CreateDate');
$widget_column = array('name' => 't.CreateDate', 'header' => $header, 'value' => function ($data) {
echo date('d.m.Y H:i:s', strtotime($data->CreateDate));
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldDateOnly($with_rel, $rels, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $alternative_condition_value, $criteria);
////////////////////////////////////////////
break;
case 14:
$rels = array(array('name' => 'documentSubject1', 'select' => true), array('name' => 'documentSubject1.subject1', 'select' => true), array('name' => 'documentSubject1.document1', 'select' => true), array('name' => 'documentSubject2', 'select' => true), array('name' => 'documentSubject2.subject2', 'select' => true), array('name' => 'documentSubject2.document2', 'select' => true), array('name' => 'documentSubject3', 'select' => true), array('name' => 'documentSubject3.subject3', 'select' => true), array('name' => 'documentSubject3.document3', 'select' => true));
$sel = array('to_select' => $to_select, 'sql_as' => 'ZNO', 'sql_expr' => "group_concat(DISTINCT concat_ws(';;',\r\n IF(ISNULL(t.DocumentSubject1),'',\r\n concat(\r\n subject1.SubjectName,\r\n '; бал: ',IF(ISNULL(documentSubject1.SubjectValue),\r\n 'немає',documentSubject1.SubjectValue),\r\n '; номер: ',IF(ISNULL(document1.Numbers),\r\n 'немає',document1.Numbers),\r\n '; PIN: ',IF(ISNULL(document1.ZNOPin),\r\n 'немає',document1.ZNOPin)\r\n )\r\n ),\r\n IF(ISNULL(t.DocumentSubject2),'',\r\n concat(\r\n subject2.SubjectName,\r\n '; бал: ',IF(ISNULL(documentSubject2.SubjectValue),\r\n 'немає',documentSubject2.SubjectValue),\r\n '; номер: ',IF(ISNULL(document2.Numbers),\r\n 'немає',document2.Numbers),\r\n '; PIN: ',IF(ISNULL(document2.ZNOPin),\r\n 'немає',document2.ZNOPin)\r\n )\r\n ),\r\n IF(ISNULL(t.DocumentSubject3),'',\r\n concat(\r\n subject3.SubjectName,\r\n '; бал: ',IF(ISNULL(documentSubject3.SubjectValue),\r\n 'немає',documentSubject3.SubjectValue),\r\n '; номер: ',IF(ISNULL(document3.Numbers),\r\n 'немає',document3.Numbers),\r\n '; PIN: ',IF(ISNULL(document3.ZNOPin),\r\n 'немає',document3.ZNOPin)\r\n )\r\n )\r\n ) SEPARATOR '||')");
$widget_column = array('name' => 'ZNO', 'header' => $header, 'value' => function ($data) {
$_arr = array();
$_nodes = explode('||', $data->ZNO);
foreach ($_nodes as $_node) {
$_subjs = explode(';;', $_node);
if (!$_node) {
continue;
}
$is_first = true;
foreach ($_subjs as $_subj) {
if (isset($_arr[$_subj]) || !$_subj) {
continue;
}
$_arr[$_subj] = true;
if (!$is_first) {
echo '<br/>';
} else {
$is_first = false;
}
echo $_subj;
}
}
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextOnly($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
///////////////////////////////////////
break;
case 16:
//$group = 't.idPersonSpeciality';
$rels = array(array('name' => 'exam1', 'select' => true), array('name' => 'exam2', 'select' => true), array('name' => 'exam3', 'select' => true));
$sel = array('to_select' => $to_select, 'sql_as' => 'EXAM', 'sql_expr' => "concat_ws(';;',\r\n IF(ISNULL(t.Exam1ID),'',\r\n concat(exam1.SubjectName,'; бал: ',\r\n IF(ISNULL(t.Exam1Ball),'немає',t.Exam1Ball))),\r\n IF(ISNULL(t.Exam2ID),'',\r\n concat(exam2.SubjectName,'; бал: ',\r\n IF(ISNULL(t.Exam2Ball),'немає',t.Exam2Ball))),\r\n IF(ISNULL(t.Exam3ID),'',\r\n concat(exam3.SubjectName,'; бал: ',\r\n IF(ISNULL(t.Exam3Ball),'немає',t.Exam3Ball)))\r\n )", 'group_concat' => true);
$widget_column = array('name' => 'EXAM', 'header' => $header, 'value' => function ($data) {
$_arr = array();
$_nodes = explode('||', $data->EXAM);
foreach ($_nodes as $_node) {
$_subjs = explode(';;', $_node);
if (!$_node) {
continue;
}
$is_first = true;
foreach ($_subjs as $_subj) {
if (isset($_arr[$_subj]) || !$_subj) {
continue;
}
$_arr[$_subj] = true;
if (!$is_first) {
echo '<br/>';
} else {
$is_first = false;
}
echo $_subj;
}
}
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextOnly($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
///////////////////////////////////////
break;
case 18:
$rels = array(array('name' => 'person', 'select' => true), array('name' => 'person.docs', 'select' => false), array('name' => 'person.docs.type', 'select' => false));
$sel = array('to_select' => $to_select, 'sql_as' => 'DOCS', 'sql_expr' => "concat(\r\n type.PersonDocumentTypesName,\r\n ': ',\r\n IF(ISNULL(docs.Series),'',docs.Series),' ',\r\n IF(ISNULL(docs.Numbers),'',concat(docs.Numbers,' ')),\r\n IF((ISNULL(docs.Issued) OR docs.Issued=''),\r\n '',concat('видано: ',docs.Issued,', ')),\r\n IF((ISNULL(docs.DateGet) OR docs.DateGet LIKE '1970-01-01'),\r\n '',concat('дата видачі ',docs.DateGet,' ')),\r\n IF ((ISNULL(docs.AtestatValue) OR docs.AtestatValue = 0),'',\r\n concat('(значення: ',docs.AtestatValue, ')'))\r\n )", 'group_concat' => true);
$widget_column = array('name' => 'DOCS', 'header' => $header, 'value' => function ($data) {
$_arr = array();
$_nodes = explode('||', $data->DOCS);
foreach ($_nodes as $_node) {
$_subjs = explode(';;', $_node);
if (!$_node) {
continue;
}
$is_first = true;
foreach ($_subjs as $_subj) {
if (isset($_arr[$_subj]) || !$_subj) {
continue;
}
$_arr[$_subj] = true;
if (!$is_first) {
echo '<br/>';
} else {
$is_first = false;
}
echo $_subj;
}
}
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextOnly($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
///////////////////////////////////////
break;
case 19:
$rels = array(array('name' => 'pbenefits', 'select' => false), array('name' => 'pbenefits.psbenefit', 'select' => false), array('name' => 'pbenefits.psbenefit.benefit', 'select' => false));
$sel = array('to_select' => $to_select, 'sql_as' => 'BENEFITS', 'sql_expr' => "\r\n concat(\r\n benefit.BenefitName,\r\n ': ',\r\n IF(ISNULL(psbenefit.Series),'',concat('серія: ',psbenefit.Series)),' ',\r\n IF(ISNULL(psbenefit.Numbers),'',concat('номер: ',psbenefit.Numbers,' ')),\r\n IF((ISNULL(psbenefit.Issued) OR psbenefit.Issued=''),\r\n '',concat('видано: ',psbenefit.Issued,', ')),\r\n IF ((benefit.isPV = 1),' (першочергово)',''),\r\n IF ((benefit.isPZK = 1),' (поза конкурсом)','')\r\n )", 'group_concat' => true);
$widget_column = array('name' => 'BENEFITS', 'header' => $header, 'type' => 'raw', 'value' => function ($data) {
$_arr = array();
$_nodes = explode('||', $data->BENEFITS);
foreach ($_nodes as $_node) {
$_subjs = explode(';;', $_node);
if (!$_node) {
continue;
}
$is_first = true;
foreach ($_subjs as $_subj) {
if (isset($_arr[$_subj]) || !$_subj) {
continue;
}
$_arr[$_subj] = true;
if (!$is_first) {
echo '<br/>';
} else {
$is_first = false;
}
echo $_subj;
}
}
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextOnly($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
///////////////////////////////////////
break;
case 20:
$rels = array(array('name' => 'pbenefits', 'select' => false), array('name' => 'pbenefits.psbenefit', 'select' => false), array('name' => 'pbenefits.psbenefit.benefit', 'select' => false));
$sel = array('to_select' => $to_select, 'db_field_id' => 'psbenefit.BenefitID', 'sql_as' => 'BENTYPES', 'sql_expr' => 'benefit.BenefitName', 'group_concat' => true);
$widget_column = array('name' => 'BENTYPES', 'header' => $header, 'value' => function ($data) {
echo $data->BENTYPES;
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextAlternative($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $alternative_condition_value, $criteria);
///////////////////////////////////////
break;
case 21:
$rels = array(array('name' => 'pbenefits', 'select' => false), array('name' => 'pbenefits.psbenefit', 'select' => false), array('name' => 'pbenefits.psbenefit.benefit', 'select' => false));
$sel = array('to_select' => $to_select, 'db_field' => 'benefit.isPV');
$widget_column = array('name' => 'benefit.isPV', 'header' => $header, 'value' => function ($data) {
if (!empty($data->pbenefits)) {
$echo = 'ні';
foreach ($data->pbenefits as $pben) {
if ($pben->psbenefit->benefit->isPV) {
$echo = 'так';
}
}
echo $echo;
}
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldCheckboxOnly($with_rel, $rels, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
////////////////////////////////////////////
break;
case 22:
$rels = array(array('name' => 'pbenefits', 'select' => false), array('name' => 'pbenefits.psbenefit', 'select' => false), array('name' => 'pbenefits.psbenefit.benefit', 'select' => false));
$sel = array('to_select' => $to_select, 'db_field' => 'benefit.isPZK');
$widget_column = array('name' => 'benefit.isPZK', 'header' => $header, 'value' => function ($data) {
if (!empty($data->pbenefits)) {
$echo = 'ні';
foreach ($data->pbenefits as $pben) {
if ($pben->psbenefit->benefit->isPZK) {
$echo = 'так';
}
}
echo $echo;
}
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldCheckboxOnly($with_rel, $rels, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
////////////////////////////////////////////
break;
case 15:
$group = 't.idPersonSpeciality';
$rels = array(array('name' => 'sepciality', 'select' => true), array('name' => 'sepciality.eduform', 'select' => true));
$sel = array('to_select' => $to_select, 'db_field_id' => 'sepciality.PersonEducationFormID', 'db_field' => 'eduform.PersonEducationFormName');
$widget_column = array('name' => 'eduform.PersonEducationFormName', 'header' => $header, 'value' => function ($data) {
echo $data->sepciality->eduform->PersonEducationFormName;
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextAlternative($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $alternative_condition_value, $criteria);
////////////////////////////////////////////
break;
case 17:
$group = 't.idPersonSpeciality';
$rels = array(array('name' => 'qualification', 'select' => true));
$sel = array('to_select' => $to_select, 'db_field_id' => 't.QualificationID', 'db_field' => 'qualification.QualificationName');
$widget_column = array('name' => 'qualification.QualificationName', 'header' => $header, 'value' => function ($data) {
if (!empty($data->qualification)) {
echo $data->qualification->QualificationName;
}
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextAlternative($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $alternative_condition_value, $criteria);
////////////////////////////////////////////
break;
case 23:
$group = 't.idPersonSpeciality';
$rels = array(array('name' => 'sepciality', 'select' => true));
$DIRECTION_sql = "concat_ws(' '," . "SpecialityClasifierCode," . "(case substr(SpecialityClasifierCode,1,1) when '6' then " . "SpecialityDirectionName else SpecialityName end)," . "(case SpecialitySpecializationName when '' then '' " . " else concat('(',SpecialitySpecializationName,')') end)" . ")";
$sel = array('to_select' => $to_select, 'sql_as' => 'DIRECTION', 'sql_expr' => $DIRECTION_sql, 'group_concat' => false);
$widget_column = array('name' => 'DIRECTION', 'header' => $header, 'value' => function ($data) {
echo $data->DIRECTION;
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextOnly($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
////////////////////////////////////////////
break;
case 24:
$rels = array(array('name' => 'person', 'select' => true), array('name' => 'person.docs', 'select' => false), array('name' => 'person.docs.type', 'select' => false));
$sel = array('to_select' => $to_select, 'db_field_id' => 'docs.TypeID', 'sql_as' => 'DOCTYPES', 'sql_expr' => 'type.PersonDocumentTypesName', 'group_concat' => true);
$widget_column = array('name' => 'DOCTYPES', 'header' => $header, 'value' => function ($data) {
echo $data->DOCTYPES;
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextAlternative($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $alternative_condition_value, $criteria);
///////////////////////////////////////
break;
case 25:
$rels = array(array('name' => 'coursedp', 'select' => true));
$sel = array('to_select' => $to_select, 'db_field' => 'coursedp.CourseDPName');
$widget_column = array('name' => 'CourseDPName', 'header' => $header, 'value' => function ($data) {
echo !$data->coursedp ? "" : $data->coursedp->CourseDPName;
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextOnly($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
////////////////////////////////////////////
break;
case 26:
$rels = array();
$sel = array('to_select' => $to_select, 'sql_as' => 'PersonCase', 'sql_expr' => "CONCAT((CASE t.QualificationID \r\n WHEN 1 THEN 'Б' \r\n WHEN 2 THEN 'СМ' \r\n WHEN 3 THEN 'СМ' \r\n WHEN 4 THEN 'МС' END), t.CourseID, '-', LPAD(t.PersonRequestNumber,5,'0'))");
$widget_column = array('name' => 'PersonCase', 'header' => $header, 'value' => function ($data) {
echo $data->PersonCase;
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextOnly($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
////////////////////////////////////////////
break;
case 27:
$group = 't.idPersonSpeciality';
$sel = array('to_select' => $to_select, 'db_field' => 't.isCopyEntrantDoc');
$widget_column = array('name' => 't.isCopyEntrantDoc', 'header' => $header, 'value' => function ($data) {
echo $data->isCopyEntrantDoc ? "так" : "ні";
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldCheckboxOnly($with_rel, $rels, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
////////////////////////////////////////////
break;
case 28:
$rels = array(array('name' => 'entrantdoc', 'select' => true), array('name' => 'entrantdoc.docaward', 'select' => true));
$sel = array('to_select' => $to_select, 'db_field_id' => 'entrantdoc.PersonDocumentsAwardsTypesID', 'db_field' => 'docaward.PersonDocumentsAwardsTypesName');
$widget_column = array('name' => 'docaward.PersonDocumentsAwardsTypesName', 'header' => $header, 'value' => function ($data) {
if (!empty($data->entrantdoc)) {
if (!empty($data->entrantdoc->docaward)) {
echo $data->entrantdoc->docaward->PersonDocumentsAwardsTypesName;
}
}
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextAlternative($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $alternative_condition_value, $criteria);
////////////////////////////////////////////
break;
case 29:
$rels = array();
$sel = array('to_select' => $to_select, 'sql_as' => 'ReqCase', 'sql_expr' => "LPAD(t.RequestNumber,5,'0')");
$widget_column = array('name' => 'ReqCase', 'header' => $header, 'value' => function ($data) {
echo $data->ReqCase;
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextOnly($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
////////////////////////////////////////////
break;
case 30:
$rels = array(array('name' => 'xlang', 'select' => true));
$sel = array('to_select' => $to_select, 'db_field_id' => 'LanguageExID', 'db_field' => 'xlang.LanguageExName');
$widget_column = array('name' => 'xlang.LanguageExName', 'header' => $header, 'value' => function ($data) {
if (!empty($data->person->language)) {
echo $data->xlang->LanguageExName;
}
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextAlternative($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $alternative_condition_value, $criteria);
////////////////////////////////////////////
break;
case 31:
$group = 't.idPersonSpeciality';
$rels = array(array('name' => 'entrantdoc', 'select' => true), array('name' => 'entrantdoc.docbasespec', 'select' => true));
$sel = array('to_select' => $to_select, 'db_field_id' => 'entrantdoc.PersonBaseSpecealityID', 'db_field' => 'docbasespec.PersonBaseSpecialityName');
$widget_column = array('name' => 'docbasespec.PersonBaseSpecialityName', 'header' => $header, 'value' => function ($data) {
if (!empty($data->entrantdoc)) {
if (!empty($data->entrantdoc->docbasespec)) {
echo $data->entrantdoc->docbasespec->PersonBaseSpecialityClasifierCode . ' ' . $data->entrantdoc->docbasespec->PersonBaseSpecialityName;
}
}
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextAlternative($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $alternative_condition_value, $criteria);
////////////////////////////////////////////
break;
case 32:
$group = 't.idPersonSpeciality';
$rels = array();
$sel = array('to_select' => $to_select, 'db_field' => 't.CoursedpBall');
$widget_column = array('name' => 't.CoursedpBall', 'header' => $header, 'value' => function ($data) {
echo $data->CoursedpBall;
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextOnly($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
////////////////////////////////////////////
break;
case 33:
$group = 't.idPersonSpeciality';
$rels = array();
$sel = array('to_select' => $to_select, 'db_field' => 't.priority');
$widget_column = array('name' => 't.priority', 'header' => $header, 'value' => function ($data) {
echo $data->priority;
});
$field_num_index = $to_select ? $field_num_indexes[$i] : 0;
$this->ProcessFieldTextOnly($with_rel, $rels, $select, $sel, $widget_columns, $widget_column, $field_num_index, $condition_type, $condition_value, $criteria);
////////////////////////////////////////////
break;
}
}
$criteria->select = $select;
$criteria->with = $with_rel;
$criteria->group = $group;
$criteria->together = true;
$model = new Personspeciality();
//$criteria->addCondition('KOA=123');
$dataProvider = new CActiveDataProvider($model, array('criteria' => $criteria, 'sort' => array('defaultOrder' => array('idPersonSpeciality' => CSort::SORT_ASC), 'attributes' => array('NAME' => array('asc' => 'NAME', 'desc' => 'NAME DESC'), 'DIRECTION' => array('asc' => 'sepciality.SpecialityName ASC,sepciality.SpecialityDirectionName ASC,sepciality.SpecialityClasifierCode ASC', 'desc' => 'sepciality.SpecialityName DESC,sepciality.SpecialityDirectionName DESC,sepciality.SpecialityClasifierCode DESC'), 'KOATUU' => array('asc' => 'KOATUU', 'desc' => 'KOATUU DESC'), 'ZNO' => array('asc' => 'ZNO', 'desc' => 'ZNO DESC'), 'BENEFITS' => array('asc' => 'BENEFITS', 'desc' => 'BENEFITS DESC'), 'DOCS' => array('asc' => 'DOCS', 'desc' => 'DOCS DESC'), 'PersonCase' => array('asc' => 'PersonCase', 'desc' => 'PersonCase DESC'), 'status.PersonRequestStatusTypeName' => array('asc' => 'status.PersonRequestStatusTypeName', 'desc' => 'status.PersonRequestStatusTypeName DESC'), 'facultet.FacultetFullName' => array('asc' => 'facultet.FacultetFullName', 'desc' => 'facultet.FacultetFullName DESC'), 'language.LanguagesName' => array('asc' => 'language.LanguagesName', 'desc' => 'language.LanguagesName DESC'), 'person.BirthPlace' => array('asc' => 'person.BirthPlace', 'desc' => 'person.BirthPlace DESC'), 'person.Birthday' => array('asc' => 'person.Birthday', 'desc' => 'person.Birthday DESC'), 'school.SchoolName' => array('asc' => 'school.SchoolName', 'desc' => 'school.SchoolName DESC'), 't.CreateDate' => array('asc' => 't.CreateDate', 'desc' => 't.CreateDate DESC'), 't.priority' => array('asc' => 't.priority', 'desc' => 't.priority DESC'), 't.CoursedpBall' => array('asc' => 't.CoursedpBall', 'desc' => 't.CoursedpBall DESC'), '*')), 'pagination' => array('pageSize' => $reqExcel ? 15000 : 150)));
$dataProvider->setTotalItemCount(count($model->findAll($criteria)));
$this->layout = $reqExcel ? '//layouts/clear' : '//layouts/main_noblock';
$direct_widget_columns = array();
for ($i = 0; $i < count($field_nums); $i++) {
foreach ($widget_columns as $key => $value) {
if ($key == $i) {
$direct_widget_columns[] = $value;
}
}
}
$this->render($reqExcel ? '/statistic/rept_excel' : '/statistic/rept', array('data' => $dataProvider, 'columns' => $direct_widget_columns));
}
public function student_details_fees($id)
{
// Warning: Please modify the following code to remove attributes that
// should not be searched.
$criteria = new CDbCriteria();
/************************************Ravi B************************************************/
$student_trans = StudentTransaction::model()->findByPk($id);
$fees_master = FeesMaster::model()->findByAttributes(array('fees_academic_term_name_id' => $student_trans->student_academic_term_name_id, 'fees_quota_id' => $student_trans->student_transaction_quota_id, 'fees_branch_id' => $student_trans->student_transaction_branch_id));
$criteria->condition = 'student_fees_master_student_transaction_id = :student_id AND fees_master_table_id=:fees_master';
$criteria->params = array(':student_id' => $id, ':fees_master' => $fees_master->fees_master_id);
/******************************************************************************************/
$dp = new CActiveDataProvider($this, array('criteria' => $criteria));
$dp->setTotalItemCount(count($this->findAll($criteria)));
return $dp;
}
public function search()
{
$pageSize = Yii::app()->user->getState('pageSize', Yii::app()->params['defaultPageSize']);
$sort = new CSort();
$sort->attributes = array('survey_id' => array('asc' => 't.sid asc', 'desc' => 't.sid desc'), 'title' => array('asc' => 'correct_relation_defaultlanguage.surveyls_title asc', 'desc' => 'correct_relation_defaultlanguage.surveyls_title desc'), 'creation_date' => array('asc' => 't.datecreated asc', 'desc' => 't.datecreated desc'), 'owner' => array('asc' => 'owner.users_name asc', 'desc' => 'owner.users_name desc'), 'anonymized_responses' => array('asc' => 't.anonymized asc', 'desc' => 't.anonymized desc'), 'running' => array('asc' => 't.active asc, t.expires asc', 'desc' => 't.active desc, t.expires desc'));
$sort->defaultOrder = array('creation_date' => CSort::SORT_DESC);
$criteria = new LSDbCriteria();
$aWithRelations = array('correct_relation_defaultlanguage');
// Search filter
$sid_reference = Yii::app()->db->getDriverName() == 'pgsql' ? ' t.sid::varchar' : 't.sid';
$aWithRelations[] = 'owner';
$criteria->compare($sid_reference, $this->searched_value, true);
$criteria->compare('t.admin', $this->searched_value, true, 'OR');
$criteria->compare('owner.users_name', $this->searched_value, true, 'OR');
$criteria->compare('correct_relation_defaultlanguage.surveyls_title', $this->searched_value, true, 'OR');
// Active filter
if (isset($this->active)) {
if ($this->active == 'N' || $this->active == "Y") {
$criteria->compare("t.active", $this->active, false);
} else {
// Time adjust
$sNow = date("Y-m-d H:i:s", strtotime(Yii::app()->getConfig('timeadjust'), strtotime(date("Y-m-d H:i:s"))));
if ($this->active == "E") {
$criteria->compare("t.active", 'Y');
$criteria->addCondition("t.expires <'{$sNow}'");
}
if ($this->active == "S") {
$criteria->compare("t.active", 'Y');
$criteria->addCondition("t.startdate >'{$sNow}'");
}
if ($this->active == "R") {
$now = new CDbExpression("NOW()");
$criteria->compare("t.active", 'Y');
$subCriteria1 = new CDbCriteria();
$subCriteria2 = new CDbCriteria();
$subCriteria1->addCondition($now . ' > t.startdate', 'OR');
$subCriteria2->addCondition($now . ' < t.expires', 'OR');
$subCriteria1->addCondition('t.expires IS NULL', "OR");
$subCriteria2->addCondition('t.startdate IS NULL', "OR");
$criteria->mergeWith($subCriteria1);
$criteria->mergeWith($subCriteria2);
}
}
}
$criteria->with = $aWithRelations;
// Permission
// Note: reflect Permission::hasPermission
if (!Permission::model()->hasGlobalPermission("surveys", 'read')) {
$criteriaPerm = new CDbCriteria();
// Multiple ON conditions with string values such as 'survey'
$criteriaPerm->mergeWith(array('join' => "LEFT JOIN {{permissions}} AS permissions ON (permissions.entity_id = t.sid AND permissions.permission='survey' AND permissions.entity='survey' AND permissions.uid='" . Yii::app()->user->id . "') "));
$criteriaPerm->compare('t.owner_id', Yii::app()->user->id, false);
$criteriaPerm->compare('permissions.read_p', '1', false, 'OR');
$criteria->mergeWith($criteriaPerm, 'AND');
}
// $criteria->addCondition("t.blabla == 'blub'");
$dataProvider = new CActiveDataProvider('Survey', array('sort' => $sort, 'criteria' => $criteria, 'pagination' => array('pageSize' => $pageSize)));
$dataProvider->setTotalItemCount($this->count($criteria));
return $dataProvider;
}
