function __construct()
{
parent::__construct("gnlms");
$this->dataDefinitions = array("course-activity" => array("title" => "Course Activity", "columns" => "u.email, u.last_name as 'Last Name', u.first_name as 'First Name', o.name as 'Organization', c.title as 'Course', uce.event_date as 'Date', uce.event_type as 'Activity Type'", "tableExpr" => "#user_course_event# uce inner join #user# u on u.id=uce.user_id inner join #course# c on c.id=uce.course_id left join #organization# o on o.id=u.organization_id", "filters" => array("_between" => array("date(uce.event_date)", "start_date", "end_date"), "_contains" => array("u.email", "email"), "o.id" => "organization_id", "uce.event_type" => "event_type", "u.test" => "test"), "orderBy" => array("event_date" => array("uce.event_date desc", "Event Date (desc)"), "last_name" => array("u.last_name", "Last Name"), "org" => array("o.name", "Organization"), "course" => array("c.title", "Course"), "event_type" => array('uce.event_type', 'Activity Type'))), "assessment-responses" => array("title" => "Course Post-Test: User Responses", "columns" => "u.email as 'Email', u.last_name as 'Last Name', u.first_name as 'First Name', ucar.response_date as 'Date', ucar.score as 'Score', case ucar.result when 1 then 'P' else 'F' end as 'Pass/Fail'", "tableExpr" => "#user# u inner join #user_course_assessment_response# ucar on u.id=ucar.user_id inner join #course_assessment# ca on ca.course_id=ucar.course_id and ca.name=ucar.name", "filters" => array("_contains" => array("u.email", "email"), "_between" => array("ucar.response_date", "start_date", "end_date"), "ca.course_id" => "course_id", "u.test" => "test"), "orderBy" => array("response_date" => array("ucar.response_date desc", "Date (desc)"), "last_name" => array("u.last_name", "Last Name"), "score" => array('ucar.score', 'Score'), "result" => array("ucar.result", "Pass/Fail"))), "assessment-summary" => array("title" => "Course Post-Test: Course Summary", "columns" => "caq.sequence, caq.text, caq.correct_answer", "tableExpr" => "#course_assessment_question# caq inner join #course_assessment# ca on ca.id=caq.course_assessment_id", "filters" => array("ca.course_id" => "course_id"), "orderBy" => "caq.sequence"), "course-completion" => array("title" => "Course Completion", "columns" => "u.email as 'Email', u.last_name as 'Last Name', u.first_name as 'First Name', o.name as 'Organization', c.title as 'Course', ucr.course_completion_date as 'Date', case when ucr.course_status='Completed' then 'Pass' else 'Fail' end as 'Result'", "tableExpr" => "#user# u inner join #user_course_registration# ucr on u.id=ucr.user_id and (ucr.course_status='Completed' or ucr.course_status='Failed') inner join #course# c on c.id=ucr.course_id left join #organization# o on o.id=u.organization_id", "filters" => array("_between" => array("ucr.course_completion_date", "start_date", "end_date"), "_contains" => array("u.email", "email"), "o.id" => "organization_id", "ucr.course_id" => "course_id", "u.test" => "test"), "orderBy" => array("date" => array("ucr.course_completion_date desc", "Date (desc)"), "last_name" => array("u.last_name", "Last Name"), "org" => array("o.name", "Organization"), "course" => array("c.title", "Course"), "result" => array("case when ucr.score > 70 then 1 else 0 end", "Result"))), "evaluation-data" => array("title" => "Evaluation Data", "columns" => "u.first_name as 'First Name', u.last_name as 'Last Name', u.title as 'Title', c.title as 'course', er.response_date as 'Comment Date', er.q1 as 'Q1', er.q2 as 'Q2', er.q3 as 'Q3', er.q4 as 'Q4', er.q5 as 'Q5', er.q6 as 'Q6', er.q7 as 'Q7', er.q8 as 'Q8', er.q9 as 'Q9', er.q10 as 'Q10', er.q11 as 'Q11', er.q12 as 'Q12', er.q13 as 'Q13', er.q14 as 'Q14', er.q15 as 'Q15', er.q16 as 'Q16', er.q17 as 'Q17', er.q18 as 'Q18', er.q19 as 'Q19', er.q20 as 'Q20'", "tableExpr" => "#evaluation_response# er inner join #user# u on u.id=er.user_id inner join #course# c on c.id=er.course_id", "filters" => array("_between" => array("date(er.response_date)", "start_date", "end_date"), "_contains" => array("u.last_name", "last_name"), "c.id" => "course_id"), "orderBy" => "er.response_date desc"));
}
function quoteString($str)
{
if ($str === null) {
return 'null';
} else {
return parent::quoteString($str);
}
}
function __construct()
{
parent::__construct("gnlms");
$this->dataDefinitions = array("user-registration" => array("columns" => "gn.email, gn.last_name as 'Last Name', gn.first_name as 'First Name', o.name as 'Organization', gn.title, gn.role, sc.code as 'Registration Code', convert_tz(wp.user_registered, '" . date('e') . "', '" . get_option('timezone_string') . "') as 'Registration Date'", "tableExpr" => "#user# gn inner join wp_users wp on gn.id=wp.ID left join #subscription_code# sc on sc.id=gn.subscription_code_id left join #organization# o on o.id=gn.organization_id", "filters" => array("_between" => array("date(wp.user_registered)", "start_date", "end_date"), "o.id" => "organization_id", "sc.code" => "regcode"), "orderBy" => array("reg_date" => array("wp.user_registered desc", "Registration Date (desc)"), "last_name" => array("gn.last_name", "Last Name"), "org" => array("o.name", "Organization"), "reg_code" => array("sc.code", "Registration Code"))), "user-activity" => array("columns" => "u.email, u.last_name as 'Last Name', u.first_name as 'First Name', o.name as 'Organization', c.title as 'Course', uce.event_date as 'Date', uce.event_type as 'Activity Type'", "tableExpr" => "#user_course_event# uce inner join #user# u on u.id=uce.user_id inner join #course# c on c.id=uce.course_id left join #organization# o on o.id=u.organization_id", "filters" => array("_between" => array("date(uce.event_date)", "start_date", "end_date"), "_contains" => array("u.email", "email"), "o.id" => "organization_id", "uce.event_type" => "event_type"), "orderBy" => array("event_date" => array("uce.event_date desc", "Event Date (desc)"), "last_name" => array("u.last_name", "Last Name"), "org" => array("o.name", "Organization"), "course" => array("c.title", "Course"), "event_type" => array('uce.event_type', 'Activity Type'))), "assessment-responses" => array("columns" => "u.email as 'Email', u.last_name as 'Last Name', u.first_name as 'First Name', ucar.response_date as 'Date', ucar.score as 'Score', case ucar.result when 1 then 'P' else 'F' end as 'Pass/Fail'", "tableExpr" => "#user# u inner join #user_course_assessment_response# ucar on u.id=ucar.user_id inner join #course_assessment# ca on ca.course_id=ucar.course_id and ca.name=ucar.name", "filters" => array("_contains" => array("u.email", "email"), "_between" => array("ucar.response_date", "start_date", "end_date"), "ca.course_id" => "course_id"), "orderBy" => array("response_date" => array("ucar.response_date desc", "Date (desc)"), "last_name" => array("u.last_name", "Last Name"), "score" => array('ucar.score', 'Score'), "result" => array("ucar.result", "Pass/Fail"))), "assessment-summary" => array("columns" => "caq.sequence, caq.text, caq.correct_answer", "tableExpr" => "#course_assessment_question# caq inner join #course_assessment# ca on ca.id=caq.course_assessment_id", "filters" => array("ca.course_id" => "course_id"), "orderBy" => "caq.sequence"), "course-completion" => array("columns" => "u.email as 'Email', u.last_name as 'Last Name', u.first_name as 'First Name', o.name as 'Organization', c.title as 'Course', ucr.course_completion_date as 'Date', case when ucr.course_status='Completed' then 'Pass' else 'Fail' end as 'Result'", "tableExpr" => "#user# u inner join #user_course_registration# ucr on u.id=ucr.user_id and (ucr.course_status='Completed' or ucr.course_status='Failed') inner join #course# c on c.id=ucr.course_id left join #organization# o on o.id=u.organization_id", "filters" => array("_between" => array("ucr.course_completion_date", "start_date", "end_date"), "_contains" => array("u.email", "email"), "o.id" => "organization_id", "ucr.course_id" => "course_id"), "orderBy" => array("date" => array("ucr.course_completion_date desc", "Date (desc)"), "last_name" => array("u.last_name", "Last Name"), "org" => array("o.name", "Organization"), "course" => array("c.title", "Course"), "result" => array("case when ucr.score > 70 then 1 else 0 end", "Result"))), "ecommerce" => array("columns" => "u.email as 'Email', u.last_name as 'Last Name', u.first_name as 'First Name', o.name as 'Organization', ec.transaction_id as 'Transaction ID', concat('\$', format(ec.transaction_amount, 2)) as 'Amount', ec.transaction_date as 'Date', group_concat(c.title separator '; ') as 'Courses'", "tableExpr" => "#ecommerce# ec left join #user# u on ec.user_id=u.id left join #organization# o on o.id=u.organization_id left join #ecommerce_item# ei on ei.ecommerce_id=ec.id left join #course# c on c.id=ei.course_id", "filters" => array("_between" => array("date(ec.transaction_date)", "start_date", "end_date"), "_contains" => array("u.email", "email"), "o.id" => "organization_id"), "orderBy" => array("date" => array("ec.transaction_date desc", "Date (desc)"), "last_name" => array("u.last_name", "Last Name"), "org" => array("o.name", "Organization")), "groupBy" => "ec.id"));
}
