Итак, есть кучка тестов в системе moodle, студенты проходят тесты, после нужно сделать выгрузку в excel с помощью скрипта
Код: Выделить всё
<?php
require_once ('csu_exl.php');
require_once('../config.php');
require_once($CFG->libdir.'/adminlib.php');
require_once($CFG->libdir.'/tablelib.php');
require_login();
$quest[0] = "Какова ваша оценка курса в целом?";
$quest[1] = "Оцените качество образовательного контента";
$quest[2] = "Оцените уровень преподавания";
$quest[3] = "Оцените ваши успехи в изучении курса";
# 1. Формируем HTTP заголовок
# EXL_header_xml();
EXL_header();
# 2. создает структуру Excel документа в DOM
$dom = EXL_CreateDOMExcelStruct();
#################################################################################################################################
# 5. Стили
$Styles = EXL_GetStyles($dom);
# стиль шапки - строка 1 SH1
$Style = EXL_create_element($dom,$Styles,'Style','','ss:ID','SH1');
$Interior = EXL_create_element($dom,$Style,'Interior','','ss:Color','#A1F5FF','ss:Pattern','Solid');
$Borders = EXL_create_element($dom,$Style,'Borders','');
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Bottom",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Left",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Right",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Top",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Alignment = EXL_create_element($dom,$Style,'Alignment','','ss:Horizontal','Center','ss:Vertical','Center','ss:WrapText','1');
$Font = EXL_create_element($dom,$Style,'Font','','ss:FontName','Arial Cyr','x:CharSet','204','ss:Bold','1');
$Style = EXL_create_element($dom,$Styles,'Style','','ss:ID','SFIO');
$Interior = EXL_create_element($dom,$Style,'Interior','','ss:Color','#A1F5FF','ss:Pattern','Solid');
$Borders = EXL_create_element($dom,$Style,'Borders','');
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Bottom",'ss:LineStyle',"Continuous",'ss:Weight',"2",'ss:Color','#9090FF');
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Left",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Right",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Top",'ss:LineStyle',"Continuous",'ss:Weight',"2",'ss:Color','#9090FF');
$Alignment = EXL_create_element($dom,$Style,'Alignment','','ss:Horizontal','Left','ss:Vertical','Center','ss:WrapText','1');
$Font = EXL_create_element($dom,$Style,'Font','','ss:FontName','Arial Cyr','x:CharSet','204','ss:Bold','1');
$Style = EXL_create_element($dom,$Styles,'Style','','ss:ID','SD0OK');
$Interior = EXL_create_element($dom,$Style,'Interior','','ss:Color','#FFFFFF','ss:Pattern','Solid');
$Borders = EXL_create_element($dom,$Style,'Borders','');
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Bottom",'ss:LineStyle',"Continuous",'ss:Weight',"2",'ss:Color','#9090FF');
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Left",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Right",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Top",'ss:LineStyle',"Continuous",'ss:Weight',"2",'ss:Color','#9090FF');
$Alignment = EXL_create_element($dom,$Style,'Alignment','','ss:Horizontal','Left','ss:Vertical','Center','ss:WrapText','1');
$Font = EXL_create_element($dom,$Style,'Font','','ss:FontName','Arial Cyr','x:CharSet','204','ss:Bold','0');
$Style = EXL_create_element($dom,$Styles,'Style','','ss:ID','SD1OK');
$Interior = EXL_create_element($dom,$Style,'Interior','','ss:Color','#FFFFFF','ss:Pattern','Solid');
$Borders = EXL_create_element($dom,$Style,'Borders','');
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Bottom",'ss:LineStyle',"Continuous",'ss:Weight',"2",'ss:Color','#9090FF');
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Left",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Right",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Top",'ss:LineStyle',"Continuous",'ss:Weight',"2",'ss:Color','#9090FF');
$Alignment = EXL_create_element($dom,$Style,'Alignment','','ss:Horizontal','Right','ss:Vertical','Center','ss:WrapText','1');
$Font = EXL_create_element($dom,$Style,'Font','','ss:FontName','Arial Cyr','x:CharSet','204','ss:Bold','0');
$Style = EXL_create_element($dom,$Styles,'Style','','ss:ID','SD0BAD');
$Interior = EXL_create_element($dom,$Style,'Interior','','ss:Color','#FFFFFF','ss:Pattern','Solid');
$Borders = EXL_create_element($dom,$Style,'Borders','');
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Bottom",'ss:LineStyle',"Continuous",'ss:Weight',"2",'ss:Color','#9090FF');
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Left",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Right",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Top",'ss:LineStyle',"Continuous",'ss:Weight',"2",'ss:Color','#9090FF');
$Alignment = EXL_create_element($dom,$Style,'Alignment','','ss:Horizontal','Right','ss:Vertical','Center','ss:WrapText','1');
$Font = EXL_create_element($dom,$Style,'Font','','ss:FontName','Arial Cyr','x:CharSet','204','ss:Color','#FF0000','ss:Bold','1');
$Style = EXL_create_element($dom,$Styles,'Style','','ss:ID','SD1BAD');
$Interior = EXL_create_element($dom,$Style,'Interior','','ss:Color','#B1F5FF','ss:Pattern','Solid');
$Borders = EXL_create_element($dom,$Style,'Borders','');
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Bottom",'ss:LineStyle',"Continuous",'ss:Weight',"2",'ss:Color','#9090FF');
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Left",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Right",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Top",'ss:LineStyle',"Continuous",'ss:Weight',"2",'ss:Color','#9090FF');
$Alignment = EXL_create_element($dom,$Style,'Alignment','','ss:Horizontal','Right','ss:Vertical','Center','ss:WrapText','1');
$Font = EXL_create_element($dom,$Style,'Font','','ss:FontName','Arial Cyr','x:CharSet','204','ss:Color','#FF0000','ss:Bold','1');
$Style = EXL_create_element($dom,$Styles,'Style','','ss:ID','SDolgOK');
$Interior = EXL_create_element($dom,$Style,'Interior','','ss:Color','#FFFFFF','ss:Pattern','Solid');
$Borders = EXL_create_element($dom,$Style,'Borders','');
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Bottom",'ss:LineStyle',"Continuous",'ss:Weight',"2",'ss:Color','#9090FF');
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Left",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Right",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Top",'ss:LineStyle',"Continuous",'ss:Weight',"2",'ss:Color','#9090FF');
$Alignment = EXL_create_element($dom,$Style,'Alignment','','ss:Horizontal','Right','ss:Vertical','Center','ss:WrapText','1');
$Font = EXL_create_element($dom,$Style,'Font','','ss:FontName','Arial Cyr','x:CharSet','204','ss:Color','#000000','ss:Bold','1');
$Style = EXL_create_element($dom,$Styles,'Style','','ss:ID','SDolgMedium');
$Interior = EXL_create_element($dom,$Style,'Interior','','ss:Color','#FFFFB0','ss:Pattern','Solid');
$Borders = EXL_create_element($dom,$Style,'Borders','');
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Bottom",'ss:LineStyle',"Continuous",'ss:Weight',"2",'ss:Color','#9090FF');
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Left",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Right",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Top",'ss:LineStyle',"Continuous",'ss:Weight',"2",'ss:Color','#9090FF');
$Alignment = EXL_create_element($dom,$Style,'Alignment','','ss:Horizontal','Right','ss:Vertical','Center','ss:WrapText','1');
$Font = EXL_create_element($dom,$Style,'Font','','ss:FontName','Arial Cyr','x:CharSet','204','ss:Color','#000000','ss:Bold','1');
$Style = EXL_create_element($dom,$Styles,'Style','','ss:ID','SDolgBAD');
$Interior = EXL_create_element($dom,$Style,'Interior','','ss:Color','#FFB0BC','ss:Pattern','Solid');
$Borders = EXL_create_element($dom,$Style,'Borders','');
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Bottom",'ss:LineStyle',"Continuous",'ss:Weight',"2",'ss:Color','#9090FF');
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Left",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Right",'ss:LineStyle',"Continuous",'ss:Weight',"1");
$Border = EXL_create_element($dom,$Borders,'Border','','ss:Position',"Top",'ss:LineStyle',"Continuous",'ss:Weight',"2",'ss:Color','#9090FF');
$Alignment = EXL_create_element($dom,$Style,'Alignment','','ss:Horizontal','Right','ss:Vertical','Center','ss:WrapText','1');
$Font = EXL_create_element($dom,$Style,'Font','','ss:FontName','Arial Cyr','x:CharSet','204','ss:Color','#000000','ss:Bold','1');
# 3. создаем лист в Workbook
$Worksheet = EXL_CreateWorksheet($dom,iconv('UTF-8','windows-1251', 'Отчет по обратной связи'));
# 4. Создаем таблицу и наполняем её данными
$Table = EXL_create_element($dom,$Worksheet,'Table','');
#################################################################################################################################
# сформируем шапку
# строка 1 - курсы - в excel шапка курса может содержать несколько подстолбцов оценок
# значит нам надо знать, сколько столбцов нам надо объединить (Имя курса, количество столбцов на курс)
# это я первый столбец нешироким, второй и третий столбец делаю побольше
$Column = EXL_create_element($dom,$Table,'Column','','ss:Width','100');
$Column = EXL_create_element($dom,$Table,'Column','','ss:Width','30');
$Column = EXL_create_element($dom,$Table,'Column','','ss:Width','120');
$Column = EXL_create_element($dom,$Table,'Column','','ss:Width','200');
$Row = EXL_create_element($dom,$Table,'Row','');
# столбец Группа
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:MergeDown','1','ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Группа','ss:Type','String');
# столбец кол-во студентов подписанных на курс
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:MergeDown','1','ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','кол-во студентов','ss:Type','String');
# столбец Преподаватель
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:MergeDown','1','ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Преподаватель','ss:Type','String');
# столбец Дисциплина
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:MergeDown','1','ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Дисциплина','ss:Type','String');
# столбец Вопрос 1
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:MergeAcross','2','ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data',$quest[0],'ss:Type','String');
# столбец Вопрос 2
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:MergeAcross','2','ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data',$quest[1],'ss:Type','String');
# столбец Вопрос 3
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:MergeAcross','2','ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data',$quest[2],'ss:Type','String');
# столбец Вопрос 4
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:MergeAcross','2','ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data',$quest[3],'ss:Type','String');
# столбец Средний балл для Вопрос 1
/* $Cell = EXL_create_element($dom,$Row,'Cell','','ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Средний балл','ss:Type','String');*/
# ВСТАВЛЯЮ пустую строчку, что бы эексель мог разграничить заголовок от данных, а иначе он не понимает и считает 2-ую строчку как данные
$Row = EXL_create_element($dom,$Table,'Row','');
$indexv1 = 5;
$indexv2 = 8;
$indexv3 = 11;
$indexv4 = 14;
# столбец Средний балл для Вопрос 1
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:Index',$indexv1,'ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Средний балл','ss:Type','String');
# столбец Кол-во ответивших на Вопрос 1
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:Index',$indexv1+1,'ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Отв на вопрос','ss:Type','String');
# столбец Кол-во ответивших на тест для дисциплины
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:Index',$indexv1+2,'ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Отв на анкету','ss:Type','String');
# столбец Средний балл для Вопрос 2
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:Index',$indexv2,'ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Средний балл','ss:Type','String');
# столбец Кол-во ответивших на Вопрос 2
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:Index',$indexv2+1,'ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Отв на вопрос','ss:Type','String');
# столбец Кол-во ответивших на тест для дисциплины
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:Index',$indexv2+2,'ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Отв на анкету','ss:Type','String');
# столбец Средний балл для Вопрос 3
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:Index',$indexv3,'ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Средний балл','ss:Type','String');
# столбец Кол-во ответивших на Вопрос 3
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:Index',$indexv3+1,'ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Отв на вопрос','ss:Type','String');
# столбец Кол-во ответивших на тест для дисциплины
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:Index',$indexv3+2,'ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Отв на анкету','ss:Type','String');
# столбец Средний балл для Вопрос 4
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:Index',$indexv4,'ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Средний балл','ss:Type','String');
# столбец Кол-во ответивших на Вопрос 4
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:Index',$indexv4+1,'ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Отв на вопрос','ss:Type','String');
# столбец Кол-во ответивших на тест для дисциплины
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:Index',$indexv4+2,'ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Отв на анкету','ss:Type','String');
/*
$Row = EXL_create_element($dom,$Table,'Row','');
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:StyleID','SD1OK');
$Data = EXL_create_element($dom,$Cell,'Data','Данные1','ss:Type','String');
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:StyleID','SD1OK');
$Data = EXL_create_element($dom,$Cell,'Data','Данные2','ss:Type','String'); */
# добавим фиксированную панель
$WorksheetOptions = EXL_create_element($dom,$Worksheet,'WorksheetOptions','','xmlns','urn:schemas-microsoft-com:office:excel');
EXL_create_element($dom,$WorksheetOptions,'FreezePanes','');
EXL_create_element($dom,$WorksheetOptions,'SplitHorizontal','2');
EXL_create_element($dom,$WorksheetOptions,'TopRowBottomPane','3');
EXL_create_element($dom,$WorksheetOptions,'ActivePane','2');
$categoryid = 13;
$sql = "select GROUP_CONCAT(id SEPARATOR ',') as str from moodle.mdl_course where category=$categoryid";
$courses = get_record_sql($sql);
$courses = '('.$courses->str.')';
# добавим фильтры
//$AutoFilter = EXL_create_element($dom,$Worksheet,'AutoFilter','','xmlns','urn:schemas-microsoft-com:office:excel','x:Range','R4C1:C'.($ssindex-1));
[color=#FF0000] $sql2= "select cqq.stid, gr.name,cont.count_student, /*concat(u.lastname,' ',u.firstname) as*/ t1.fiteacher, cn.course_name,f.test_feedbackid as test_feedbackid, cq.qname, cq.count_answed_sttudents, cqq.questname, cqq.sum_answ/cq.count_answed_sttudents as average_ball, cqq.count_student_answed_on_quest as count_student_answed_on_quest
from iitmgmt.feedback f
join iitmgmt.groups gr on gr.idgroup = f.groupid
join iitmgmt.coursename cn on cn.id = f.idcoursename
/*join iitmgmt.test_teacher tt on tt.testid = f.test_feedbackid
join moodle.mdl_user u on u.id = tt.teacherid*/
left join(
select GROUP_CONCAT(concat(u.lastname,' ',u.firstname,' (',roleteacher,') ') SEPARATOR ',') as fiteacher, t.* from
iitmgmt.test_teacher t
join moodle.mdl_user u on u.id = t.teacherid
group by t.testid
) as t1 on t1.testid = f.test_feedbackid[/color]
join (
/*получить кол-во студентов ответивших в определенном курсе на каждый тест*/
SELECT c.id as id2, c.fullname , q.course,q.id qid, q.name qname, count(qa.id) as count_answed_sttudents/*,q.timeopen, qa.userid, qa.attempt, u.username, u.firstname, u.lastname, r.shortname*/
FROM /*moodle.mdl_role_assignments ra join*/ moodle.mdl_user u /*on ra.userid=u.id*/
/*join moodle.mdl_context cxt on ra.contextid=cxt.id
join moodle.mdl_course c on cxt.instanceid=c.id
join moodle.mdl_role r on ra.roleid = r.id*/
join moodle.mdl_course c
left join moodle.mdl_quiz q on q.course = c.id
left join moodle.mdl_quiz_attempts qa on q.id = qa.quiz and qa.userid=u.id
WHERE /*cxt.contextlevel=50
AND r.shortname='student'
and c.id=906 and*/ c.id in $courses /*c.id<906 and c.id >896*/
and q.timeopen < UNIX_TIMESTAMP() and qa.timefinish<>0
and u.id not in (
SELECT u.id
FROM moodle.mdl_role_assignments ra join moodle.mdl_user u on ra.userid=u.id
join moodle.mdl_context cxt on ra.contextid=cxt.id
join moodle.mdl_course c2 on cxt.instanceid=c2.id
join moodle.mdl_role r on ra.roleid = r.id
WHERE cxt.contextlevel=50
AND r.shortname != 'student' and c2.id=c.id)
group by c.id, q.id
) as cq on f.test_feedbackid =cq.qid /* cq.id2=c.id*/
left join (
/*получить по каждому вопросу из теста из курса сумму ответов на вопрос, пока не могу понять откуда там может взяться большая сумма*/
SELECT st.id as stid,c.id as id3, c.fullname , q.course,q.id as qid2,q.name as qname, quest.name as questname,sum(abs(SUBSTRING_INDEX(st.answer,':',-1)-qans.id)+1) as sum_answ,count(st.answer) as count_student_answed_on_quest,SUBSTRING_INDEX(st.answer,':',-1),qans.id, count(qans.id), q.timeopen, qa.userid, qa.attempt, u.username, u.firstname, u.lastname/*, r.shortname*/
FROM /*moodle.mdl_role_assignments ra join*/ moodle.mdl_user u /*on ra.userid=u.id*/
/*join moodle.mdl_context cxt on ra.contextid=cxt.id
join moodle.mdl_course c on cxt.instanceid=c.id
join moodle.mdl_role r on ra.roleid = r.id*/
join moodle.mdl_course c
join moodle.mdl_quiz q on q.course = c.id
join moodle.mdl_quiz_attempts qa on q.id = qa.quiz and qa.userid=u.id
JOIN `moodle`.`mdl_question_sessions` sess on qa.uniqueid = sess.attemptid
join `moodle`.`mdl_question_states` st on st.question = sess.questionid AND st.attempt = sess.attemptid
JOIN `moodle`.`mdl_question` quest ON st.question = quest.id
JOIN `moodle`.`mdl_question_answers` qans ON st.question=qans.question and qans.answer='Очень плохо'
WHERE /*cxt.contextlevel=50
AND r.shortname='student'*/
/*and c.id=906*/ c.id in $courses
and q.timeopen < UNIX_TIMESTAMP() and qa.timefinish<>0
and SUBSTRING_INDEX(st.answer,':',-1) <> '' /*это означает пропущенный ответ*/
and st.id in (
/*пользователь может делать промежуточные сохранения рез-та тестирования, мы берем самый последний
отправленный результат*/
select max(st2.id) from `moodle`.`mdl_question_states` st2
where st.attempt = st2.attempt and st.question= st2.question
group by st.attempt, st.question,st.answer)
and u.id not in (
SELECT u.id
FROM moodle.mdl_role_assignments ra join moodle.mdl_user u on ra.userid=u.id
join moodle.mdl_context cxt on ra.contextid=cxt.id
join moodle.mdl_course c2 on cxt.instanceid=c2.id
join moodle.mdl_role r on ra.roleid = r.id
WHERE cxt.contextlevel=50
AND r.shortname != 'student' and c2.id=c.id)
group by c.id, q.id, quest.id/*, qans.id*/
) as cqq on cqq.qid2 = f.test_feedbackid /*cqq.id3 = c.id*/ and cqq.qid2 = cq.qid
join
(
/*получить кол-во студентов подписанных на курс*/
SELECT c2.id as id, c2.fullname , count(*) as count_student
FROM moodle.mdl_role_assignments ra join moodle.mdl_user u on ra.userid=u.id
join moodle.mdl_context cxt on ra.contextid=cxt.id
join moodle.mdl_course c2 on cxt.instanceid=c2.id
join moodle.mdl_role r on ra.roleid = r.id
WHERE cxt.contextlevel=50
AND r.shortname='student'
and c2.id in $courses
group by c2.id
) as cont on cq.id2 = cont.id" ;
$res = get_records_sql($sql2);
$results = createNeedleData($res);
//печатаем строки в отчет согласно столбцами шапки таблицы
foreach($results as $group=>$result)
{
foreach($result->disc as $d)
{
$Row = EXL_create_element($dom,$Table,'Row','');
# столбец Группа
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:StyleID','SD0OK');
$Data = EXL_create_element($dom,$Cell,'Data',$group,'ss:Type','String');
# столбец кол-во студентов подписанных на курс
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:StyleID','SD0OK');
$Data = EXL_create_element($dom,$Cell,'Data',$result->count_student,'ss:Type','String');
# столбец Преподаватель
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:StyleID','SD0OK');
$Data = EXL_create_element($dom,$Cell,'Data',$d->fiteacher,'ss:Type','String');
# столбец Дисциплина
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:StyleID','SD0OK');
$Data = EXL_create_element($dom,$Cell,'Data',$d->course_name,'ss:Type','String');
#пишем теперь про вопросы
For($i=0;$i<4; $i++)
{
/*echo "$i ";
print_r ($d->question);
echo '\n';*/
# столбец Средний балл для Вопрос 1
$Cell = EXL_create_element($dom,$Row,'Cell','',/*'ss:Index',$indexv1,*/'ss:StyleID','SD0OK');
$Data = EXL_create_element($dom,$Cell,'Data',round($d->question[$i]->average_ball,2),'ss:Type','String');
# столбец Кол-во ответивших на Вопрос 1
$Cell = EXL_create_element($dom,$Row,'Cell','',/*'ss:Index',$indexv1+1,*/'ss:StyleID','SD0OK');
$Data = EXL_create_element($dom,$Cell,'Data',$d->question[$i]->count_student_answed_on_quest,'ss:Type','String');
# столбец Кол-во ответивших на тест для дисциплины
$Cell = EXL_create_element($dom,$Row,'Cell','',/*'ss:Index',$indexv1+2,*/'ss:StyleID','SD0OK');
$Data = EXL_create_element($dom,$Cell,'Data',$d->count_answed_sttudents,'ss:Type','String');
}
}
}
$AutoFilter = EXL_create_element($dom,$Worksheet,'AutoFilter','','xmlns','urn:schemas-microsoft-com:office:excel','x:Range',"R2C1:C16"/*'R4C1:C'.($ssindex-1)*/);
//на другом листе делаем отчет по конструктивным комментариям
$sql = "SELECT st.id, gr.name,t1.fiteacher, cn.course_name as coursename, /*c.id, c.fullname , q.course,q.id,*//*q.name, quest.name,*//*sum(abs(SUBSTRING_INDEX(st.answer,':',-1)-qans.id)+1),*/SUBSTRING(st.answer,1,1000000000000000)/*SUBSTRING_INDEX(st.answer,':',-1)*/ as comment/*,qans.id, q.timeopen, qa.userid, qa.attempt, u.username, u.firstname, u.lastname, r.shortname*/
FROM moodle.mdl_role_assignments ra join moodle.mdl_user u on ra.userid=u.id
join moodle.mdl_context cxt on ra.contextid=cxt.id
join moodle.mdl_course c on cxt.instanceid=c.id
join moodle.mdl_role r on ra.roleid = r.id
join moodle.mdl_quiz q on q.course = c.id
join moodle.mdl_quiz_attempts qa on q.id = qa.quiz and qa.userid=u.id
JOIN `moodle`.`mdl_question_sessions` sess on qa.uniqueid = sess.attemptid
join `moodle`.`mdl_question_states` st on st.question = sess.questionid AND st.attempt = sess.attemptid
JOIN `moodle`.`mdl_question` quest ON st.question = quest.id and quest.name like 'Ваши замечания%'
join `iitmgmt`.`feedback` f on f.test_feedbackid = q.id
join iitmgmt.groups gr on gr.idgroup = f.groupid
join iitmgmt.coursename cn on cn.id = f.idcoursename
left join(
select GROUP_CONCAT(concat(u.lastname,' ',u.firstname,' (',roleteacher,') ') SEPARATOR ',') as fiteacher, t.* from
iitmgmt.test_teacher t
join moodle.mdl_user u on u.id = t.teacherid
group by t.testid
) as t1 on t1.testid = f.test_feedbackid
/*JOIN `moodle`.`mdl_question_answers` qans ON st.question=qans.question and qans.answer='Очень плохо'*/
WHERE cxt.contextlevel=50
AND r.shortname='student'
and c.id in $courses
/*and q.timeopen < UNIX_TIMESTAMP()*/ and qa.timefinish<>0
and trim(st.answer) <>''
and st.seq_number in (
/*пользователь может делать промежуточные сохранения рез-та тестирования, мы берем самый последний
отправленный результат*/
select max(st2.seq_number) from `moodle`.`mdl_question_states` st2
where st.attempt = st2.attempt and st.question= st2.question
group by st.attempt, st.question,st.answer)";
$results = get_records_sql($sql);
# 3. создаем лист в Workbook
$Worksheet2 = EXL_CreateWorksheet($dom,iconv('UTF-8','windows-1251', 'Конструктивные комментарии'));
# 4. Создаем таблицу и наполняем её данными
$Table2 = EXL_create_element($dom,$Worksheet2,'Table','');
#################################################################################################################################
# сформируем шапку
# строка 1 - курсы - в excel шапка курса может содержать несколько подстолбцов оценок
# значит нам надо знать, сколько столбцов нам надо объединить (Имя курса, количество столбцов на курс)
# это я первый столбец нешироким, второй и третий столбец делаю побольше
$Column = EXL_create_element($dom,$Table2,'Column','','ss:Width','100');
$Column = EXL_create_element($dom,$Table2,'Column','','ss:Width','250');
$Column = EXL_create_element($dom,$Table2,'Column','','ss:Width','150');
$Column = EXL_create_element($dom,$Table2,'Column','','ss:Width','600');
$Row = EXL_create_element($dom,$Table2,'Row','');
# столбец Группа
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:MergeDown','1','ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Группа','ss:Type','String');
# столбец дисциплина
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:MergeDown','1','ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Дисциплина','ss:Type','String');
# столбец Преподаватель
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:MergeDown','1','ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Преподаватель','ss:Type','String');
# столбец Комментарий
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:MergeDown','1','ss:StyleID','SH1');
$Data = EXL_create_element($dom,$Cell,'Data','Конструктивный комментарий','ss:Type','String');
$Row = EXL_create_element($dom,$Table2,'Row','');
foreach ($results as $result)
{
$Row = EXL_create_element($dom,$Table2,'Row','');
# столбец Группа
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:StyleID','SD0OK');
$Data = EXL_create_element($dom,$Cell,'Data',$result->name,'ss:Type','String');
# столбец дисциплина
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:StyleID','SD0OK');
$Data = EXL_create_element($dom,$Cell,'Data',$result->coursename,'ss:Type','String');
# столбец Преподаватель
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:StyleID','SD0OK');
$Data = EXL_create_element($dom,$Cell,'Data',$result->fiteacher,'ss:Type','String');
# столбец Комментарий
$Cell = EXL_create_element($dom,$Row,'Cell','','ss:StyleID','SD0OK');
$Data = EXL_create_element($dom,$Cell,'Data',$result->comment,'ss:Type','String');
}
$AutoFilter = EXL_create_element($dom,$Worksheet2,'AutoFilter','','xmlns','urn:schemas-microsoft-com:office:excel','x:Range',"R2C1:C4"/*'R4C1:C'.($ssindex-1)*/);
#добавим фиксированную панель
$WorksheetOptions = EXL_create_element($dom,$Worksheet2,'WorksheetOptions','','xmlns','urn:schemas-microsoft-com:office:excel');
EXL_create_element($dom,$WorksheetOptions,'FreezePanes','');
EXL_create_element($dom,$WorksheetOptions,'SplitHorizontal','2');
EXL_create_element($dom,$WorksheetOptions,'TopRowBottomPane','3');
EXL_create_element($dom,$WorksheetOptions,'ActivePane','2');
print $dom->saveXML();
function createNeedleData($objs)
{
global $quest;
$results = array();
foreach ($objs as $obj)
{
if (!isset($results[$obj->name]))
{
$result = new stdClass();
$result->count_student = $obj->count_student;
$disc = new stdClass();
$disc->fiteacher = $obj->fiteacher;
$disc->course_name = $obj->course_name;
$disc->count_answed_sttudents = $obj->count_answed_sttudents;
foreach ($quest as $key=>$q)
{
if (strcmp($q, $obj->questname) == 0)
{
$question = new stdClass();
$question->average_ball = $obj->average_ball;
$question->count_student_answed_on_quest = $obj->count_student_answed_on_quest;
$disc->question[$key] = $question;
}
}
$result->disc[$obj->test_feedbackid] = $disc;
$results[$obj->name] = $result;
}else# нужно вносить изменения уже в существующий массив
{
if (!isset($results[$obj->name]->disc[$obj->test_feedbackid]))
{//не было дисциплины, значит ее нужно добавить
$disc = new stdClass();
$disc->fiteacher = $obj->fiteacher;
$disc->course_name = $obj->course_name;
$disc->count_answed_sttudents = $obj->count_answed_sttudents;
foreach ($quest as $key=>$q)
{
if (strcmp($q, $obj->questname) == 0)
{
$question = new stdClass();
$question->average_ball = $obj->average_ball;
$question->count_student_answed_on_quest = $obj->count_student_answed_on_quest;
$disc->question[$key] = $question;
}
}
$results[$obj->name]->disc[$obj->test_feedbackid] = $disc;
}
else
{//нужно добавлять вопросы
foreach ($quest as $key=>$q)
{
if (strcmp($q, $obj->questname) == 0)
{
$question = new stdClass();
$question->average_ball = $obj->average_ball;
$question->count_student_answed_on_quest = $obj->count_student_answed_on_quest;
$results[$obj->name]->disc[$obj->test_feedbackid]->question[$key] = $question;
}
}
}
}
}
return $results;
}
?>
<?php
// admin_externalpage_print_footer();
?>
как мне сказали проблема у меня в
Код: Выделить всё
$sql2= "select cqq.stid, gr.name,cont.count_student, /*concat(u.lastname,' ',u.firstname) as*/ t1.fiteacher, cn.course_name,f.test_feedbackid as test_feedbackid, cq.qname, cq.count_answed_sttudents, cqq.questname, cqq.sum_answ/cq.count_answed_sttudents as average_ball, cqq.count_student_answed_on_quest as count_student_answed_on_quest
from iitmgmt.feedback f
join iitmgmt.groups gr on gr.idgroup = f.groupid
join iitmgmt.coursename cn on cn.id = f.idcoursename
/*join iitmgmt.test_teacher tt on tt.testid = f.test_feedbackid
join moodle.mdl_user u on u.id = tt.teacherid*/
left join(
select GROUP_CONCAT(concat(u.lastname,' ',u.firstname,' (',roleteacher,') ') SEPARATOR ',') as fiteacher, t.* from
iitmgmt.test_teacher t
join moodle.mdl_user u on u.id = t.teacherid
group by t.testid
) as t1 on t1.testid = f.test_feedbackid
как мне правильно переписать эту часть кода?