function __construct()
{
parent::__construct("gnlms");
$this->initAlerts();
$this->tableDefinition = array("course" => array("table" => "course", "columns" => array("id", "course_number", "last_update", "title", "description", "url", "image", "certificate", "credit", "record_status"), "listcolumns" => array("id", "title", "case record_status when 1 then 'Yes' else 'No' end as 'active'"), "defaults" => array("record_status" => 0, "last_update" => null)), "user_course_registration" => array("table" => "user_course_registration", "columns" => array("id", "course_id", "user_id", "registration_date", "registration_type", "ec_item_id", "expiration_date", "course_status", "course_completion_date", "score", "test_attempts", "scormdata", "record_status"), "defaults" => array("record_status" => 0, "expiration_date" => null, "course_completion_date" => null, "ec_item_id" => null)), "admin_user_list" => array("listcolumns" => array("u.id", "u.user_name as 'User Name'", "u.last_name as 'Last Name'", "u.first_name as 'First Name'", "u.email", "t1.event_date as 'Last Activity'"), "list_select_table" => "(" . $this->getUserSelect() . ") u left join #user_course_event# uce on u.id=uce.user_id left join (select user_id, max(event_date) as 'event_date' from #user_course_event# group by user_id) t1 on t1.user_id = u.id", "groupby" => "group by u.id"), "active_courses" => array("list_select_table" => "(select * from #course# where record_status=1) c", "listcolumns" => array("c.id", "c.title")), "admin_course_list" => array("table" => "course", "listcolumns" => array("id", "title", "description", "record_status as 'active'")), "user_current_courses" => array("list_select_table" => "#course# c inner join #user_course_registration# ucr on c.id=ucr.course_id and ucr.course_status != 'Completed' and ucr.course_status != 'Failed' and ucr.record_status=1", "listcolumns" => array("c.id", "c.title", "c.description", "c.course_number", "ucr.course_status", "c.url", "if(ucr.expiration_date < current_date(), 1, 0) as 'expired'"), "context_filters" => array("context_user_id" => "ucr.user_id=#current_user_id#"), "filter" => "c.record_status=1"), "user_completed_courses" => array("list_select_table" => "#user_course_registration# ucr left join #course# c on ucr.course_id = c.id", "listcolumns" => array("c.*", "ucr.*"), "context_filters" => array("context_user_id" => "ucr.user_id=#current_user_id#"), "filter" => "ucr.course_status='Completed'"), "user_available_courses" => array("list_select_table" => "#course# c left join #user_course_registration# ucr on c.id=ucr.course_id and ucr.user_id=%d", "listcolumns" => array("c.*, case when ucr.record_status=0 then 'Inactive' else ucr.course_status end as 'course_status', ucr.registration_date, ucr.course_completion_date, ucr.expiration_date, ucr.score"), "filter" => "c.record_status=1"), "available_courses" => array("list_select_table" => "#course# c", "listcolumns" => array("c.id", "c.title", "c.description", "c.image", "c.credit"), "filter" => "record_status=1"), "course_users" => array("list_select_table" => "(" . $this->getUserSelect() . ") u inner join #user_course_registration# ucr on u.id=ucr.user_id", "listcolumns" => array("u.id", "concat(u.last_name, ', ', u.first_name) as 'name'", "u.email", "ucr.course_status", "ucr.registration_date", "ucr.expiration_date"), "context_filters" => array("context_course_id" => "ucr.course_id=#current_id#")), "user_course_event" => array("table" => "user_course_event", "columns" => array("id", "user_id", "course_id", "event_date", "event_type")), "admin_user_current_courses" => array("list_select_table" => "#course# c inner join #user_course_registration# ucr on c.id=ucr.course_id and ucr.course_completion_date is null", "listcolumns" => array("ucr.id", "c.id as 'course_id'", "c.title", "c.course_number", "ucr.course_status", "ucr.registration_date", "ucr.expiration_date"), "context_filters" => array("context_user_id" => "ucr.user_id=#current_id#")), "admin_user_completed_courses" => array("list_select_table" => "#course# c inner join #user_course_registration# ucr on c.id=ucr.course_id and ucr.course_completion_date is not null", "listcolumns" => array("c.id", "ucr.id as 'ur_id'", "ucr.course_id", "c.title", "c.course_number", "ucr.score", "date_format(ucr.course_completion_date, '%M %e, %Y') as 'date_completed'"), "context_filters" => array("context_user_id" => "ucr.user_id=#current_id#")), "admin_recent_activity" => array("list_select_table" => "#user_course_event# uce inner join (" . $this->getUserSelect() . ") u on uce.user_id=u.id inner join #course# c on c.id=uce.course_id and uce.event_date >= date_add(curdate(), interval -2 week)", "listcolumns" => array("u.id as 'id', date_format(uce.event_date, '%Y-%m-%d') as 'Date', concat(u.last_name, ', ', u.first_name) as 'User', uce.event_type as 'Status', c.title as 'course'")), "evaluation_response" => array("table" => "evaluation_response", "columns" => array("id", "user_id", "course_id", "response_date", "q1", "q2", "q3", "q4", "q5", "q6", "q7", "q8", "q9", "q10", "q11", "q12", "q13", "q14", "q15", "q16", "q17", "q18", "q19", "q20")));
}
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 __construct()
{
parent::__construct("gnlms");
$this->initAlerts();
$this->tableDefinition = array("course" => array("table" => "course", "columns" => array("id", "course_number", "last_update", "title", "description", "url", "certificate", "credit", "record_status"), "listcolumns" => array("id", "title", "case record_status when 1 then 'Yes' else 'No' end as 'active'"), "defaults" => array("record_status" => 0, "last_update" => null)), "user" => array("table" => "user", "columns" => array("id", "organization_id", "subscription_code_id", "user_name", "email", "first_name", "last_name", "middle_initial", "middle_name", "address_1", "address_2", "city", "state", "zip", "country", "title", "role", "phone", "assigned_org", "import_subscription_code"), "validationFunction" => "validateUser"), "ecommerce" => array("table" => "ecommerce", "columns" => array("id", "user_id", "transaction_date", "transaction_id", "transaction_amount")), "ecommerce_item" => array("table" => "ecommerce_item", "columns" => array("id", "ecommerce_id", "course_id", "course_price")), "user_course_registration" => array("table" => "user_course_registration", "columns" => array("id", "course_id", "user_id", "registration_date", "registration_type", "ec_item_id", "expiration_date", "course_status", "course_completion_date", "score", "scormdata", "record_status"), "defaults" => array("record_status" => 0, "expiration_date" => null, "course_completion_date" => null, "ec_item_id" => null)), "organization" => array("table" => "organization", "columns" => array("id", "name", "record_status"), "listcolumns" => array("id", "name", "case record_status when 1 then 'Active' else 'Inactive' end as 'status'"), "defaults" => array("record_status" => 0)), "subscription_code" => array("table" => "subscription_code", "columns" => array('id', 'code', 'organization_id', 'expiration_date', 'user_limit', 'record_status'), "listcolumns" => array("id", "code", "expiration_date", "user_limit", "record_status", "'Edit' as 'edit'"), "context_filters" => array("context_organization_id" => "organization_id=#current_id#"), "defaults" => array("record_status" => 0)), "subscription_code_course" => array("table" => "subscription_code_course", "columns" => array("id", "subscription_code_id", "course_id", "subscription_period_number", "subscription_period_interval"), "defaults" => array("subscription_period_number" => null, "subscription_period_interval" => null)), "subscription_code_course_list" => array("list_select_table" => "#subscription_code# sc inner join #subscription_code_course# scc on sc.id=scc.subscription_code_id inner join #course# c on c.id=scc.course_id", "listcolumns" => array("scc.id", "sc.code as 'subscription_code'", "c.title as 'course'", "concat_ws(' ', scc.subscription_period_number, case when scc.subscription_period_number > 1 then concat(scc.subscription_period_interval, 's') else scc.subscription_period_interval end) as 'subscription_period'"), "context_filters" => array("context_subscription_code_id" => "subscription_code_id=#current_id#")), "organization_course_list" => array("list_select_table" => "#organization# o inner join #subscription_code# sc on o.id=sc.organization_id inner join #subscription_code_course# scc on scc.subscription_code_id=sc.id inner join #course# c on c.id=scc.course_id", "listcolumns" => array("c.title as 'Course'", "sc.code as 'Subscription Code'"), "context_filters" => array("context_organization_id" => "o.id=#current_id#")), "announcement" => array("table" => "announcement", "columns" => array("id", "create_date", "created_by", "title", "text"), "listcolumns" => array("id", "create_date", "title", "text")), "admin_user_list" => array("listcolumns" => array("u.id", "u.user_name as 'User Name'", "u.last_name as 'Last Name'", "u.first_name as 'First Name'", "u.email", "o.name as 'company'", "t1.event_date as 'Last Activity'"), "list_select_table" => "#user# u left join #user_course_event# uce on u.id=uce.user_id left join #organization# o on o.id=u.organization_id left join #subscription_code# sc on sc.id=u.subscription_code_id left join (select user_id, max(event_date) as 'event_date' from #user_course_event# group by user_id) t1 on t1.user_id = u.id", "groupby" => "group by u.id"), "active_courses" => array("list_select_table" => "(select * from #course# where record_status=1) c", "listcolumns" => array("c.id", "c.title")), "admin_course_list" => array("table" => "course", "listcolumns" => array("id", "title", "description", "record_status as 'active'")), "user_current_courses" => array("list_select_table" => "#course# c inner join #user_course_registration# ucr on c.id=ucr.course_id and ucr.course_status != 'Completed' and ucr.course_status != 'Failed' and ucr.record_status=1", "listcolumns" => array("c.id", "c.title", "c.description", "c.course_number", "ucr.course_status", "c.url", "if(ucr.expiration_date < current_date(), 1, 0) as 'expired'"), "context_filters" => array("context_user_id" => "ucr.user_id=#current_user_id#"), "filter" => "c.record_status=1"), "user_available_courses" => array("list_select_table" => "#course# c left join #user_course_registration# ucr on c.id=ucr.course_id and ucr.user_id=%d", "listcolumns" => array("c.*, case when ucr.record_status=0 then 'Inactive' else ucr.course_status end as 'course_status', ucr.registration_date, ucr.course_completion_date, ucr.expiration_date, ucr.score"), "filter" => "c.record_status=1"), "course_users" => array("list_select_table" => "#user# u inner join #user_course_registration# ucr on u.id=ucr.user_id left join #organization# o on o.id=u.organization_id", "listcolumns" => array("u.id", "concat(u.last_name, ', ', u.first_name) as 'name'", "u.email", "o.name as 'company'", "ucr.course_status", "ucr.registration_date", "ucr.expiration_date"), "context_filters" => array("context_course_id" => "ucr.course_id=#current_id#")), "user_course_event" => array("table" => "user_course_event", "columns" => array("id", "user_id", "course_id", "event_date", "event_type")), "admin_user_current_courses" => array("list_select_table" => "#course# c inner join #user_course_registration# ucr on c.id=ucr.course_id and ucr.course_completion_date is null", "listcolumns" => array("ucr.id", "c.id as 'course_id'", "c.title", "c.course_number", "ucr.course_status", "ucr.registration_date", "ucr.expiration_date"), "context_filters" => array("context_user_id" => "ucr.user_id=#current_id#")), "admin_user_completed_courses" => array("list_select_table" => "#course# c inner join #user_course_registration# ucr on c.id=ucr.course_id and ucr.course_completion_date is not null", "listcolumns" => array("c.id", "ucr.id as 'ur_id'", "c.title", "c.course_number", "ucr.score", "date_format(ucr.course_completion_date, '%M %e, %Y') as 'date_completed'"), "context_filters" => array("context_user_id" => "ucr.user_id=#current_id#")), "admin_recent_activity" => array("list_select_table" => "#user_course_event# uce inner join #user# u on uce.user_id=u.id inner join #course# c on c.id=uce.course_id and uce.event_date >= date_add(curdate(), interval -2 week)", "listcolumns" => array("u.id as 'id', date_format(uce.event_date, '%Y-%m-%d') as 'Date', concat(u.last_name, ', ', u.first_name) as 'User', uce.event_type as 'Status', c.title as 'course'")));
}
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"));
}