page_data=globalfunc::getMessage($parent,$parent->lang,'crtraining'); if(count($parent->params)==0) { $this->functraining($parent); }else { $qry_params=explode ("?",$parent->params[0]); $parameter_val=strtolower($qry_params[0]); switch($parameter_val){ case "baselevel": case 'getassessmentnames': case "trainingcalenderlist": case "updatetraining": case "cancelreschedulelist": case "declinetrainingreasonlist": case "canceltraining": case "rescheduletraining": case "acceptdeclinetraining": case "updatetrainingpresentabsent": case "traininglevelscore": case "updatetrainingscore": case "amacknowledgement": case "trainingattendancenotification": case "trainingattendancenotificationupdate": case "trainingqrcodegeneration": case "trainingdseqrcode": case "traininggroupimage": case "trainingsummery": case "dsefeedbacklist": case "dsescoreresultlist": case "trainingfeedbackquestion": case "trainingsfeedbacklist": case "trainingalluserfeedbackquestion": case "trainingcolors": $this->$parameter_val($parent ); break; default: $this->functraining($parent); break; } } $parent->response(array('response'=>'failed', 'message' => 'PAGE NOT FOUND'), 404); //print_r($parent); } // // GET BASE LEVEL private function baselevel ($parent) { globalfunc::checkMethodType($parent,array('GET')); globalfunc::checkAuthrozation($parent,array('type'=>'user')); globalfunc::checkParamCount($parent,1); $training_str = "SELECT `lvl_id`, `lvl_name`, `training_days`, `training_gap_days`, `assessment_values` FROM ".TABLE_TRAINING_LEVEL." ORDER BY lvl_id "; $trainer_arr = $parent->getQueryValue($training_str); $arr = (count($trainer_arr)>0)?$trainer_arr:array(); $parent->response(array('response'=>'success', 'data'=>$arr,'message'=>$this->page_data['success1']),200); } // Training Colors private function trainingcolors ($parent) { globalfunc::checkMethodType($parent,array('GET')); globalfunc::checkAuthrozation($parent,array('type'=>'user')); globalfunc::checkParamCount($parent,1); $training_colors = array(); $training_colors[0]['id'] = 1; $training_colors[0]['name'] = 'Training Scheduled not Published'; $training_colors[0]['color_code'] = '#9900CC'; $training_colors[1]['id'] = 2; $training_colors[1]['name'] = 'Training Scheduled'; $training_colors[1]['color_code'] = '#3F51B5'; $training_colors[2]['id'] = 3; $training_colors[2]['name'] = 'Training Confirmed'; $training_colors[2]['color_code'] = '#6D6D6D'; $training_colors[3]['id'] = 4; $training_colors[3]['name'] = 'Training In Progress'; $training_colors[3]['color_code'] = '#F67280'; $training_colors[4]['id'] = 5; $training_colors[4]['name'] = 'Training Completed'; $training_colors[4]['color_code'] = '#933A16'; $training_colors[5]['id'] = 6; $training_colors[5]['name'] = 'Training Closed'; $training_colors[5]['color_code'] = '#348E00'; $training_colors[6]['id'] = 7; $training_colors[6]['name'] = 'Training Reschedule'; $training_colors[6]['color_code'] = '#F9B316'; $training_colors[7]['id'] = 8; $training_colors[7]['name'] = 'Training Cancelled'; $training_colors[7]['color_code'] = '#F90303'; $parent->response(array('response'=>'success', 'data'=>$training_colors,'message'=>$this->page_data['success1']),200); } // Get Assessment Names private function getassessmentnames ($parent) { globalfunc::checkMethodType($parent,array('GET')); globalfunc::checkAuthrozation($parent,array('type'=>'user')); globalfunc::checkParamCount($parent,3); $title = escape_str($parent->params[1]); $segment = escape_str($parent->params[2]); if ($segment == 1) { $segment = 'Parents/Guardian'; } else if ($segment == 2) { $segment = 'Instructor'; } else if ($segment == 3) { $segment = 'Student Learner'; } $training_str = "SELECT `assessment_values`, lvl_name FROM ".TABLE_TRAINING_LEVEL." WHERE lvl_id = '" . $title . "' "; $trainer_arr = $parent->getQueryValue($training_str); $data = array(); if(count($trainer_arr)>0) { $assessment_values_array = explode(',', $trainer_arr[0]->assessment_values); foreach ($assessment_values_array as $key => $value) { if ($value == 1) { $data['pre'] = 'PRE-' . strtoupper($trainer_arr[0]->lvl_name) . '-' . $segment . '-ASSESSMENT'; } else if ($value == 2) { $data['post'] = 'POST-' . strtoupper($trainer_arr[0]->lvl_name) . '-' . $segment . '-ASSESSMENT'; } } } $parent->response(array('response'=>'success', 'data'=>$data,'message'=>$this->page_data['success1']),200); } // FUNCTIONS private function functraining ($parent) { switch($parent->get_request_method()) { case "GET" : $this->trainingdetails($parent); break; case "POST" : $this->newtraining($parent); break; case "DELETE": $this->canceltraining($parent);break; default: $parent->response(array('response'=>'failed'),404); break; } } private function trainingdetails ($parent) { globalfunc::checkMethodType($parent,array('GET' )); globalfunc::checkAuthrozation($parent,array('type'=>'user')); $current_role = $parent->current_user_data->role; //globalfunc::checkParamCount($parent,1); $schedule_id = escape_str($parent->params[0]); $input_date = escape_str($parent->params[1]); $schedule_str = "SELECT s.*, tl.lvl_name as title, CONCAT(u.fname, ' ', u.lname) as organizer_name FROM ".TABLE_TRAINING_SCHEDULE." s JOIN " . TABLE_TRAINING_LEVEL . " tl ON s.lvl_id = tl.lvl_id JOIN ".TABLE_USER." u ON s.trainer_id = u.userid WHERE s.schedule_id='".$schedule_id."' LIMIT 1 "; $schedule_arr = $parent->getQueryValue($schedule_str); if(count($schedule_arr) > 0) { $todaydate_time = date('Y-m-d H:i:s'); // Business Segment Name if ($schedule_arr[0]->business_segment == 2) { $schedule_arr[0]->segment_name = 'Parents/Guardian'; } else if ($schedule_arr[0]->business_segment == 1) { $schedule_arr[0]->segment_name = 'Instructor'; } else if ($schedule_arr[0]->business_segment == 3) { $schedule_arr[0]->segment_name = 'Student Learner'; } // Get Assessment Ids $assessment_str = "SELECT assessmentid, assessmenttype FROM " . TABLE_ASSESSMENT . " WHERE schedule_id = '" . $schedule_arr[0]->schedule_id . "' "; if ($current_role == 10) { $schedule_arr[0]->userid = $parent->current_user_id; } $schedule_arr[0]->startdate = date_format(date_create($schedule_arr[0]->start_date), "Y-m-d"); //$schedule_arr[0]->starttime = date_format(date_create($schedule_arr[0]->start_date), "H:i:s"); $schedule_arr[0]->enddate = date_format(date_create($schedule_arr[0]->end_date), "Y-m-d"); //$schedule_arr[0]->endtime = date_format(date_create($schedule_arr[0]->end_date), "H:i:s"); if($schedule_arr[0]->training_group_image != '') { //CONNECT S3 $s3 = awsfileserver::connectS3(); $schedule_arr[0]->training_group_image = awsfileserver::getS3FileUrl($s3,S3_BUCKET,S3_TRAINING_URL.$schedule_arr[0]->training_group_image); }else { $schedule_arr[0]->training_group_image = USER_NOIMAGE_URL; } $datediff = strtotime($schedule_arr[0]->enddate) - strtotime($schedule_arr[0]->startdate); $schedule_arr[0]->training_days = round($datediff / (60 * 60 * 24)) + 1; $starttime_array = json_decode(stripslashes($schedule_arr[0]->starttime), true); $endtime_array = json_decode(stripslashes($schedule_arr[0]->endtime), true); $todaydate = date('Y-m-d'); $schedule_arr[0]->submit_enable = 0; if ( (strtotime($schedule_arr[0]->startdate) <= strtotime($todaydate)) && (strtotime($schedule_arr[0]->enddate) >= strtotime($todaydate)) ) { $datediff = strtotime($todaydate) - strtotime($schedule_arr[0]->startdate); $schedule_arr[0]->current_day = round($datediff / (60 * 60 * 24)) + 1; $currentday = round($datediff / (60 * 60 * 24)); // Attendance Update Status before and after 30 mins $current_starttime = $starttime_array[$currentday]; $current_endtime = $endtime_array[$currentday]; $session_starttime = date("H:i", strtotime('-60 minutes', strtotime($current_starttime))); // $session_endtime = date("H:i", strtotime('+30 minutes', strtotime($current_endtime))); $today_time = date("H:i"); //if ( (strtotime($session_starttime) <= strtotime($today_time)) && (strtotime($session_endtime) >= strtotime($today_time)) ) { if ( (strtotime($session_starttime) <= strtotime($today_time)) ) { $schedule_arr[0]->attendance_update_status = 1; } //$schedule_arr[0]->attendance_update_status = 1; // Attendance and Score Submit Button Enable if (strtotime($schedule_arr[0]->enddate) == strtotime($todaydate)) { $schedule_arr[0]->submit_enable = 1; } $attendance_submit_array = json_decode($schedule_arr[0]->attendance_submit, true); $am_acknowledgement_array = json_decode($schedule_arr[0]->am_acknowledgement, true); //print $attendance_submit_array[$currentday]; $schedule_arr[0]->current_attendance_submit_status = $schedule_arr[0]->current_am_acknowledgement_status = 0; if (!empty($attendance_submit_array[$currentday])) { $schedule_arr[0]->current_attendance_submit_status = $attendance_submit_array[$currentday]; } if (!empty($am_acknowledgement_array[$currentday])) { $schedule_arr[0]->current_am_acknowledgement_status = $am_acknowledgement_array[$currentday]; } //print_r($attendance_submit_array); //print($am_acknowledgement_array); } $schedule_arr[0]->notes = ''; if ($schedule_arr[0]->current_day == 1) { $schedule_arr[0]->notes = 'Note: On clicking Save button, Pre-Assesment will be triggered to all participants who are marked present.'; } if (strtotime($schedule_arr[0]->enddate) == strtotime($todaydate)) { if (!empty($schedule_arr[0]->notes)) { $schedule_arr[0]->notes .= '
'; } $schedule_arr[0]->notes .= 'Note: On clicking Submit button, Post-Assesment will be triggered to all participants who are marked present.'; } $schedule_arr[0]->training_completed = false; if ( (strtotime($schedule_arr[0]->enddate) < strtotime($todaydate)) ) { $schedule_arr[0]->training_completed = true; } // Checking 6 hours checking for accept $schedule_arr[0]->is_active_hours = 1; $training_start_date = date("Y-m-d", strtotime($schedule_arr[0]->start_date)); $date_time_diff_str = strtotime($training_start_date . ' ' . $starttime_array[0]) - strtotime($todaydate_time); $date_time_diff_str = round($date_time_diff_str / (60 * 60)); // if ($date_time_diff_str < 1) { // $schedule_arr[0]->is_active_hours = 0; // } // Training Update or Reaschedule before 1 hours $schedule_arr[0]->training_update_status = true; if ($date_time_diff_str < 1) { $schedule_arr[0]->training_update_status = false; } //PARTICIPANT switch ($current_role) { case "7": $filter_cond = " AND d.am_email = '".$parent->current_user_data->emailid."'"; break; case "6": // Reginal Manager $filter_cond = " AND d.rm_email = '".$parent->current_user_data->emailid."'"; break; case "7": // Area Manager $filter_cond = " AND d.am_email = '".$parent->current_user_data->emailid."'"; break; case "10": // User $filter_cond = " AND p.userid='".$parent->current_user_id."'"; break; case "14": // Dealer $filter_cond = " AND u.dealer_code='".$parent->current_user_data->dealer_code."'"; break; default : $filter_cond = " "; break; } $participant_str = "SELECT p.`userid`, p.`is_attendance`,p.remarks, p.`roleplay_or_demo`, p.`assignment`, p.`performance`, p.`certification`, p.overall, p.absence_reason, p.manual_attendance_reason, p.qr_code, u.smpin, p.status, p.acknowledge_by, p.decline_reason, drl.name as decline_reason_name, p.decline_reason_desc, CONCAT(fname, ' ', lname)as name, u.profile_photo, d.dealer_name, d.dealer_code, d.am_name, d.am_email, p.creatorid, p.am_absence_reason, am_drl.name as am_absence_reason_name, p.am_absence_reason_desc FROM ".TABLE_TRAINING_PARTICIPANT." p LEFT JOIN ".TABLE_TRAINING_DECLINE_REASON_LIST." drl ON p.decline_reason = drl.id LEFT JOIN ".TABLE_TRAINING_DECLINE_REASON_LIST." am_drl ON p.am_absence_reason = am_drl.id JOIN ".TABLE_USER." u ON p.userid=u.userid JOIN ".DMS_DEALER." d ON d.dealer_code = u.dealer_code WHERE p.schedule_id='".$schedule_arr[0]->schedule_id."' " . $filter_cond . " "; $participant_arr = $parent->getQueryValue($participant_str); // Getting Percentage of Training Level $score_percentage_str = "SELECT id, name, percentage FROM ".TABLE_TRAINING_LEVEL_SCORE." WHERE level = '" . $schedule_arr[0]->lvl_id. "'"; $score_percentage_arr = $parent->getQueryValue($score_percentage_str); foreach ($score_percentage_arr as $percentage_key => $percentages) { if (strtolower($percentages->name) == '2') { $pre_test_percentage = $percentages->percentage; } elseif (strtolower($percentages->name) == '3') { $post_test_percentage = $percentages->percentage; } } if(count($participant_arr) > 0) { foreach($participant_arr as $skey => $res_part) { // Getting Pre and Post Assessment Scores $res_part->pre_test = $res_part->post_test = $res_part->pre_test_percentage = $res_part->post_test_percentage = 0; $assessment_str = "SELECT a.`assessmenttype`, au.`highscore` FROM ".TABLE_ASSESSMENT_USER." au JOIN ".TABLE_ASSESSMENT." a ON a.assessmentid = au.assessmentid WHERE a.schedule_id = '".$schedule_arr[0]->schedule_id."' AND au.userid = '" . $res_part->userid . "' AND au.highscorearid != 0 "; $assessment_arr = $parent->getQueryValue($assessment_str); if(count($assessment_arr) > 0) { foreach ($assessment_arr as $assessment_key => $assessments) { if ($assessments->assessmenttype == 1) { $res_part->pre_test = $assessments->highscore; $res_part->pre_test_percentage = ($res_part->pre_test * $pre_test_percentage) / 100; } elseif ($assessments->assessmenttype == 2) { $res_part->post_test = $assessments->highscore; $res_part->post_test_percentage = ($res_part->post_test * $post_test_percentage) / 100; } } } // if ($res_part->roleplay_or_demo == 0) { // $res_part->roleplay_or_demo = ''; // } if($res_part->profile_photo != '') { //CONNECT S3 $s3 = awsfileserver::connectS3(); $res_part->profile_photo = awsfileserver::getS3FileUrl($s3,S3_BUCKET,S3_PROFILE_IMAGE_URL.$res_part->profile_photo); }else { $res_part->profile_photo = USER_NOIMAGE_URL; } // attendance array if (!empty($input_date)) { $datediff = strtotime($input_date) - strtotime($schedule_arr[0]->startdate); $input_date_count = round($datediff / (60 * 60 * 24)) + 1; $day_cond = ' AND day = ' . $input_date_count; } //$res_part->attendance = ''; $participant_atten_str = "SELECT `day`, `ispresent`, `image`, `manual_attendance_reason`, `absence_reason` FROM ".TABLE_TRAINING_PARTICIPANT_ATTENDANCE." WHERE schedule_id = '".$schedule_arr[0]->schedule_id."' AND userid = '" . $res_part->userid . "' " . $day_cond; $participant_atten_arr = $parent->getQueryValue($participant_atten_str); foreach ($participant_atten_arr as $atten_key => $atten_details) { if($atten_details->image != '') { //CONNECT S3 $s3 = awsfileserver::connectS3(); //$atten_details->image = awsfileserver::getS3FileUrl($s3,S3_BUCKET,S3_PROFILE_IMAGE_URL.$atten_details->image); $atten_details->image = awsfileserver::getS3FileUrl($s3,S3_BUCKET,S3_TRAINING_URL.$atten_details->image); }else { $atten_details->image = USER_NOIMAGE_URL; } } if (!empty($input_date) ) { $res_part->attendance = $participant_atten_arr[0]; } else { $res_part->attendance = $participant_atten_arr; } } $assessment_str = "SELECT assessmentid, assessmentname, lvl_id, if(assessmenttype=1, 'pre', 'Post') as assessmenttype, total_question, assessmenttime FROM ".TABLE_ASSESSMENT." WHERE schedule_id='".$schedule_arr[0]->schedule_id."'"; $assessment_arr = $parent->getQueryValue($assessment_str); $schedule_arr[0]->participant = $participant_arr; $schedule_arr[0]->assessment = $assessment_arr; } } $parent->response(array('response'=>'success', 'data'=> $schedule_arr, 'message'=>$this->page_data['success1']),200); } private function trainingcalenderlist ($parent) { globalfunc::checkMethodType($parent,array('POST' )); globalfunc::checkAuthrozation($parent,array('type'=>'user')); globalfunc::checkParamCount($parent,1); $month = escape_str($parent->_request["month"]); $date_array = explode('-', $month); if (strlen($date_array[1]) == 1) { $month = $date_array[0] . '-0'.$date_array[1]; } $current_role = $parent->current_user_data->role; switch ($current_role) { case "3": $filter_cond = ""; break; case "4": // Zone Manager $dealer_cond = " JOIN " . DMS_DEALER . " d ON u.dealer_code = d.dealer_code "; $filter_cond = " AND d.zm_email = '".$parent->current_user_data->emailid."'"; break; case "5": $filter_cond = " AND s.trainer_id='".$parent->current_user_id."'"; break; case "6": // Reginal Manager $dealer_cond = " JOIN " . DMS_DEALER . " d ON u.dealer_code = d.dealer_code "; $filter_cond = " AND d.rm_email = '".$parent->current_user_data->emailid."'"; break; case "7": // Area Manager $dealer_cond = " JOIN " . DMS_DEALER . " d ON u.dealer_code = d.dealer_code "; $filter_cond = " AND d.am_email = '".$parent->current_user_data->emailid."'"; break; case "10": // User $filter_cond = " AND p.userid='".$parent->current_user_id."'"; break; case "14": // Dealer $filter_cond = " AND u.dealer_code='".$parent->current_user_data->dealer_code."'"; // $dealer_cond = " ON u.emailid=d.am_email "; // $usercond = " AND u.role=7 AND d.zone='".$trainer_arr[0]->zone."'"; break; default :$parent->response(array('response'=>'failed', 'message'=>$this->page_data['error2']), 406); break; } if ($current_role != 5) { $save_training_cond = " AND s.status > 0 "; } //$month = strtotime($month); for($i = 1; $i <= 31; $i++) { if (strlen($i) == 1 ) { $i = '0' . $i; } $training_date = $month . '-' . $i; $training_date_starttime = $month . '-' . $i . " 00:00:00"; $training_date_endtime = $month . '-' . $i . " 23:59:59"; //print $schedule_str = "SELECT s.*, tl.lvl_name as title FROM ".TABLE_TRAINING_SCHEDULE." s INNER JOIN " . TABLE_TRAINING_LEVEL . " tl ON s.lvl_id = tl.lvl_id INNER JOIN " . TABLE_TRAINING_PARTICIPANT . " p ON s.schedule_id = p.schedule_id WHERE s.start_date <= '".$training_date ."' AND s.end_date >= '".$training_date ."' " . $filter_cond . " "; //print $schedule_str = "SELECT s.*, tl.lvl_name as title FROM ".TABLE_TRAINING_SCHEDULE." s INNER JOIN " . TABLE_TRAINING_LEVEL . " tl ON s.lvl_id = tl.lvl_id WHERE (s.start_date <= '".$training_date ."' OR s.end_date >= '".$training_date ."') " . $filter_cond . " "; $schedule_str = "SELECT DISTINCT (s.`schedule_id`), s.*, tl.lvl_name as title FROM ".TABLE_TRAINING_SCHEDULE." s INNER JOIN " . TABLE_TRAINING_LEVEL . " tl ON s.lvl_id = tl.lvl_id INNER JOIN " . TABLE_TRAINING_PARTICIPANT . " p ON s.schedule_id = p.schedule_id JOIN ".TABLE_USER." u ON p.userid = u.userid " . $dealer_cond . " WHERE (('".$training_date ."' between s.start_date AND s.end_date ) OR s.start_date LIKE '" . $training_date . "%' )" . $save_training_cond . $filter_cond . " ORDER BY s.start_date "; $schedule_arr = $parent->getQueryValue($schedule_str); if(count($schedule_arr) > 0) { $today_date = date('Y-m-d H:i:s'); foreach ($schedule_arr as $schedule_key => $schedule_details) { // Dates $schedule_details->startdate = date_format(date_create($schedule_details->start_date), "Y-m-d"); //$schedule_details->starttime = date_format(date_create($schedule_details->start_date), "H:i:s"); $schedule_details->enddate = date_format(date_create($schedule_details->end_date), "Y-m-d"); // $schedule_details->endtime = date_format(date_create($schedule_details->end_date), "H:i:s"); $datediff = strtotime($todaydate) - strtotime($schedule_details->startdate); $schedule_arr[$schedule_key]->current_day = round($datediff / (60 * 60 * 24)) + 1; $currentday = round($datediff / (60 * 60 * 24)); // Start and end times $starttime_array = json_decode(stripslashes($schedule_details->starttime), true); $endtime_array = json_decode($schedule_details->endtime, true); $training_start_datetime = $schedule_details->startdate . ' ' . $starttime_array[0]; $current_starttime = $starttime_array[$currentday]; $current_endtime = $endtime_array[$currentday]; $session_starttime = date("H:i", strtotime('-60 minutes', strtotime($current_starttime))); // $session_endtime = date("H:i", strtotime('+30 minutes', strtotime($current_endtime))); $today_time = date("H:i"); // if ( (strtotime($session_starttime) <= strtotime($today_time)) && (strtotime($session_endtime) >= strtotime($today_time)) ) { if ( (strtotime($session_starttime) <= strtotime($today_time)) ) { $schedule_arr[$schedule_key]->attendance_update_status = 1; } // Days Calculation $datediff = strtotime($schedule_details->enddate) - strtotime($schedule_details->startdate); $schedule_details->training_days = round($datediff / (60 * 60 * 24)) + 1; $todaydate = date('Y-m-d'); if ( (strtotime($schedule_details->startdate) <= strtotime($todaydate)) && (strtotime($schedule_details->enddate) >= strtotime($todaydate)) ) { $datediff = strtotime($todaydate) - strtotime($schedule_details->startdate); $schedule_details->current_day = round($datediff / (60 * 60 * 24)) + 1; $schedule_arr[0]->attendance_update_status = 1; } // Training Update or Reaschedule before 6 hours $date_time_diff_str = strtotime($schedule_details->startdate . ' ' . $starttime_array[0]) - strtotime($today_date); $date_time_diff_str = round($date_time_diff_str / (60 * 60)); $schedule_details->training_update_status = true; if ($date_time_diff_str < 1) { $schedule_details->training_update_status = false; } if ($schedule_details->is_deleted == 1) { // Training Cancelled $schedule_details->training_status = '#F90303'; } else { if ($schedule_details->status == 1) { //print $training_start_datetime . '--' . $todaydate . '--' . $schedule_details->enddate . "
"; // 6 hours before training $six_hours_before_time = date("Y-m-d H:i:s", strtotime('-1 hours', strtotime($training_start_datetime))); if ( (strtotime($six_hours_before_time) <= strtotime($today_date)) && (strtotime($training_start_datetime) >= strtotime($today_date)) ) { $schedule_details->training_status = '#6D6D6D'; } // Reschedule Training //else if ( ($schedule_details->reschedule_status == 1) AND ($schedule_details->end_date < $today_date) ) { else if ( ($schedule_details->reschedule_status == 1) AND (strtotime($training_start_datetime) > strtotime($today_date)) ) { $schedule_details->training_status = '#F9B316'; } // Training Scheduled else if ( strtotime($six_hours_before_time) >= strtotime($today_date) ) { $schedule_details->training_status = '#3F51B5'; } // In Progress elseif ( (strtotime($training_start_datetime) <= strtotime($today_date)) && (strtotime($schedule_details->enddate) >= strtotime($todaydate)) ) { $schedule_details->training_status = '#F67280'; } // Training completed based on time //elseif ($schedule_details->end_date < $todaydate) { elseif ($schedule_details->end_date < $todaydate && ($schedule_details->score_submit == 0 || $schedule_details->score_submit == 1) ) { $schedule_details->training_status = '#933A16'; } elseif ($schedule_details->score_submit == 2) { // Completed $schedule_details->training_status = '#348E00'; } // elseif ($schedule_details->end_date > $todaydate) { // // Not Started // $schedule_details->training_status = '#6D6D6D'; // } } else if ($schedule_details->status == 0) { // Save Training $schedule_details->training_status = '#9900CC'; } } //PARTICIPANT /*$participant_str = "SELECT p.`userid`, p.`is_attendance`,p.remarks, p.`score`, p.`assessment_score`,u.smpin,CONCAT(fname,lname)as name, u.profile_photo ,d.dealer_name,p.creatorid FROM ".TABLE_TRAINING_PARTICIPANT." p JOIN ".TABLE_USER." u ON p.userid=u.userid JOIN ".DMS_DEALER." d ON d.dealer_code=u.dealer_code WHERE p.schedule_id='".$schedule_arr[$schedule_key]->schedule_id."' "; $participant_arr = $parent->getQueryValue($participant_str); if(count($participant_arr) > 0) { foreach($participant_arr as $skey => $res_part) { if($res_part->profile_photo != '') { //CONNECT S3 $s3 = awsfileserver::connectS3(); $res_part->profile_photo = awsfileserver::getS3FileUrl($s3,S3_BUCKET,S3_PROFILE_IMAGE_URL.$res_part->profile_photo); }else { $res_part->profile_photo = USER_NOIMAGE_URL; } } $assessment_str = "SELECT assessmentid, assessmentname, lvl_id, if(assessmenttype=1, 'pre', 'Post') as assessmenttype, total_question, assessmenttime FROM ".TABLE_ASSESSMENT." WHERE schedule_id='".$schedule_arr[$schedule_key]->schedule_id."'"; $assessment_arr = $parent->getQueryValue($assessment_str); if ($current_role != 10) { $schedule_arr[$schedule_key]->participant = $participant_arr; } $schedule_arr[$schedule_key]->assessment = $assessment_arr; }*/ } $arr[$i] = $schedule_arr; } } $parent->response(array('response'=>'success', 'data'=> $arr, 'message'=>$this->page_data['success1']),200); } // Cancel or Reschedule list private function cancelreschedulelist ($parent) { globalfunc::checkMethodType($parent,array('GET' )); globalfunc::checkAuthrozation($parent,array('type'=>'user')); globalfunc::checkParamCount($parent,1); $reschedule_str = "SELECT id, name FROM ".TABLE_TRAINING_RESCHEDULE_LIST." WHERE status = 1 "; $reschedule_arr = $parent->getQueryValue($reschedule_str); if(count($reschedule_arr) > 0) { $parent->response(array('response'=>'success', 'data'=> $reschedule_arr, 'message'=>$this->page_data['success1']),200); } } private function newtraining($parent) { globalfunc::checkMethodType($parent,array('POST' )); globalfunc::checkAuthrozation($parent,array('type'=>'user')); //globalfunc::checkParamCount($parent,1); //$REQUEST_KEYS=array('title'=>'number','description'=>'string','startdate'=>'date|Y-m-d','enddate'=>'date|Y-m-d'); $REQUEST_KEYS=array('title'=>'number','startdate'=>'date|Y-m-d','enddate'=>'date|Y-m-d'); globalfunc::mandatoryKey($parent,$REQUEST_KEYS); $lvl_id=escape_str($parent->_request["title"]); $training_start_date = $startdate = escape_str($parent->_request["startdate"]); $enddate=escape_str($parent->_request["enddate"]); $starttime=escape_str($parent->_request["starttime"]); $endtime=escape_str($parent->_request["endtime"]); $training_total_hours = escape_str($parent->_request["training_total_hours"]); // if($starttime!='' && $endtime!='') { // $startdate.=" ".$starttime; // $enddate.=" ".$endtime; // }else { // $startdate.=" 00:00:00"; // $enddate.=" 23:59:59"; // } $adhoc_training=escape_str($parent->_request["adhoc_training"]); $business_segment=escape_str($parent->_request["business_segment"]); $training_other_title=escape_str($parent->_request["training_other_title"]); $date_restriction=escape_str($parent->_request["date_restriction"]); $status=escape_str($parent->_request["status"]); $filter_zone = escape_str($parent->_request["filter_zone"]); $trainer_id=$parent->current_user_id; $description = escape_str($parent->_request['description']); $is_feedback = escape_str($parent->_request["is_feedback"]); $venue_type = escape_str($parent->_request["venue_type"]); $venue_am = escape_str($parent->_request["venue_am"]); $venue_dealer = escape_str($parent->_request["venue_dealer"]); $venue_address = escape_str($parent->_request["venue_address"]); $training_id = ''; // Getting Training Level Nice Name $training_level_str = "SELECT nick_name FROM ".TABLE_TRAINING_LEVEL." WHERE lvl_id = '".$lvl_id."' "; $training_level_arr = $parent->getQueryValue($training_level_str); if(count($training_level_arr) > 0) { $training_id .= $training_level_arr[0]->nick_name . '-'; } if (!empty($parent->current_user_data->fname)) { $trainer_fname = $parent->current_user_data->fname; $trainer_lname = $parent->current_user_data->lname; $fwords = explode(' ', $trainer_fname); $lwords = explode(' ', $trainer_lname); $training_id .= $fwords[0][0] . $fwords[1][0] . $lwords[0][0] . '-'; } $training_id .= date("dmy", strtotime($startdate)) . '-' . date("dmy", strtotime($enddate)); // Check Condition Training already available or not // $training_check_ques_str = "SELECT schedule_id FROM ".TABLE_TRAINING_SCHEDULE." WHERE `is_deleted` != '1' AND trainer_id='".$parent->current_user_id."' AND ('".$startdate ."' between start_date AND end_date )"; // $training_check_ques_arr=$parent->getQueryValue($training_check_ques_str); // if(count($training_check_ques_arr)>0) { // $parent->response(array('response'=>'failed','message'=>'Training already available in this time period.'),406); // } if(strtotime($startdate)response(array('response'=>'failed','message'=>$this->page_data["error4"]),406); if(strtotime($enddate)response(array('response'=>'failed','message'=>$this->page_data["error4"]),406); if(strtotime($startdate)>strtotime($enddate)) $parent->response(array('response'=>'failed','message'=>'from date should be greater than to date'),406); $todaydate_time = date('Y-m-d H:i:s'); $starttime_array = json_decode(stripslashes($starttime), true); $endtime_array = json_decode($endtime, true); // Check Condition about 6 hours $date_time_diff_str = strtotime($startdate . ' ' . $starttime_array[0]) - strtotime($todaydate_time); $date_time_diff_str = round($date_time_diff_str / (60 * 60)); // if ($date_time_diff_str < 1) { // $parent->response(array('response'=>'failed','message'=>'You can able to create training before 1 hour '),406); // } $dse = $parent->_request['dse']; $participant_arr = json_decode($dse); //sending as string if(!(is_array($participant_arr) && count($participant_arr)>0)) $parent->response(array('response'=>'failed','message'=>$this->page_data["error2"]),406); //$insert_qry = "INSERT INTO ".TABLE_TRAINING_SCHEDULE ." ( `lvl_id`, `schedule_name`, `trainer_id`, `start_date`, `end_date`, `venue_type`, `venue_am`, `venue_dealer`, `venue_address`, is_feedback, `is_deleted`, `status`, `created_on`, `modified_on`) VALUES ( '".$lvl_id."', '".$description."', '".$trainer_id."','".$startdate."', '".$enddate."', '".$venue_type."', '".$venue_am."', '".$venue_dealer."','".$venue_address."','".$is_feedback."', '0', '1','".NOW."', '".NOW."')"; $insert_qry = "INSERT INTO ".TABLE_TRAINING_SCHEDULE ." ( `training_id`, `lvl_id`, `schedule_name`, `trainer_id`, `start_date`, `end_date`, `venue_type`, `venue_am`, `venue_dealer`, `venue_address`, is_feedback, `starttime`, `endtime`, `training_total_hours`, `date_restriction`, `filter_zone`, `adhoc_training`, `business_segment`, `training_other_title`, `is_deleted`, `status`, `created_on`, `modified_on`) VALUES ( '".$training_id."', '".$lvl_id."', '".$description."', '".$trainer_id."','".$startdate."', '".$enddate."', '".$venue_type."', '".$venue_am."', '".$venue_dealer."','".$venue_address."','".$is_feedback."', '".$starttime."', '".$endtime."', '".$training_total_hours."', '".$date_restriction."', '".$filter_zone."', '".$adhoc_training."', '".$business_segment."', '".$training_other_title."', '0', '".$status."', '".NOW."', '".NOW."')"; $parent->executeQuery($insert_qry); $schedule_id=$parent->lastInsertId(); if(count($participant_arr)>0) { $participant_arr= array_unique($participant_arr); $ins_participant_arr=array(); $training_userids = ''; foreach($participant_arr as $userid) { // Check Condition Training already available or not $training_check_ques_str = "SELECT s.schedule_id FROM ".TABLE_TRAINING_SCHEDULE." s JOIN ".TABLE_TRAINING_PARTICIPANT ." p ON s.schedule_id = p.schedule_id WHERE ('".$training_start_date ."' between DATE_FORMAT(s.start_date,'%Y-%m-%d') AND DATE_FORMAT(s.end_date, '%Y-%m-%d') ) AND p.userid = '".$userid."' "; $training_check_ques_arr = $parent->getQueryValue($training_check_ques_str); if(count($training_check_ques_arr) > 0) { $training_userids .= $userid . ','; } $ins_participant_arr[] =" ( '".$schedule_id."', '".$userid."', '0', '0', '0', 'qwertyuiopasdfghjklzxcvbnm', '".$trainer_id."','".NOW."', '".NOW."')"; } $training_userids = trim($training_userids, ','); if(!empty($training_userids)) { // Get DSE $user_dse_str = "SELECT userid, smpin FROM ".TABLE_USER." WHERE userid in (".$training_userids.") "; $user_dse_arr = $parent->getQueryValue($user_dse_str); $dse_smpin = ''; foreach ($user_dse_arr as $user_key => $user_dse) { $dse_smpin .= $user_dse->smpin . ', '; } $dse_smpin = trim($dse_smpin, ', '); if (!empty($dse_smpin)) { $parent->response(array('response'=>'failed','message'=>'DSE (' . $dse_smpin . ') have already training in this time period.'),406); } } //print_r($ins_participant_arr); $insert_qry = "INSERT INTO ".TABLE_TRAINING_PARTICIPANT ." ( `schedule_id`, `userid`, `is_attendance`, `score`, `assessment_score`, `qr_code`, `creatorid`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_participant_arr); $parent->executeQuery($insert_qry); // Training Notification if ($status == 1) { $mobj = new stdClass(); $mobj->schedule_id = $schedule_id; $mobj->type = 'new'; $this->trainingnotifictaion($parent, $mobj); } } $assessments = $parent->_request['assessments']; $assessmenttype_array=json_decode($assessments, true); if(count($assessmenttype_array) > 0) { //ASSESSMENT QUESTION ADD $vehicle_type = 0; if ($business_segment == 1) { $vehicle_type = 1; } else if ($business_segment == 2) { $vehicle_type = 2; } foreach ($assessmenttype_array as $assessmentkey => $assessmenttypes) { if(!empty($assessmenttypes['name'])) { if ($assessmenttypes['assessment'] == 'pre') { $assessmenttype = '1'; } else if ($assessmenttypes['assessment'] == 'post') { $assessmenttype = '2'; } $assessmentname = $assessmenttypes['name']; $ques_count = $assessmenttypes["ques_count"]; $assessmenttime = $assessmenttypes["duration"]; //GET ASSESSMENT START AND END DATES $buffer_day = 1; if ($assessmenttype == 1) { $assessment_startdate=date('Y-m-d H:i:s',strtotime($startdate)); $assessment_enddate=date('Y-m-d H:i:s',strtotime('+'.$buffer_day.' day',strtotime($startdate))); } else { $assessment_startdate=date('Y-m-d H:i:s',strtotime($enddate)); $assessment_enddate=date('Y-m-d H:i:s',strtotime('+'.$buffer_day.' day',strtotime($enddate))); } //INSERT ASSESSMENT $insert_qry = "INSERT INTO ".TABLE_ASSESSMENT ." ( `atype`, `lvl_id`, `assessmentname`, `description`, `assessmentimage`, `assessmenttype`, `assettypeid`, `assessmenttimetype`, `assessmenttime`, `attemptslimit`, `publisheddate`, `enddate`, `randomcond`, `filter_role`, `filter_zone`, `filter_region`, `filter_dealer`, `assessmentnotification`, `total_question`,`schedule_id`, `status`, `creatorid`, `created_on`, `modified_on`) VALUES ('1','".$lvl_id."','".$assessmentname."','".$assessmentname."','','".$assessmenttype."','1','1','".$assessmenttime."','1','".$assessment_startdate."','".$assessment_enddate."','".$randomcond."','','','','','','".$ques_count."','".$schedule_id."','1','".$trainer_id."','".NOW."','".NOW."')"; $parent->executeQuery($insert_qry); $assessmentid = $parent->lastInsertId(); /*if(count($participant_arr)>0) { $ins_assess_arr=array(); foreach($participant_arr as $userid) { $ins_assess_arr[] =" ( '".$assessmentid."', '".$schedule_id."', '".$userid."', '0')"; } } //ASSESSMENT PARTICPANT ADD $insert_qry = "INSERT INTO ".TABLE_TRAINING_ASSESSMENT ." ( `assessmentid`, `schedule_id`, `userid`, `is_attended`) VALUES ".@implode(',',$ins_assess_arr); $parent->executeQuery($insert_qry);*/ $rcond = ($randomcond)?"ORDER BY RAND()":"ORDER BY id ASC"; $ques_str = "SELECT * FROM ".TABLE_QUESTION." WHERE vehicle_type = '" . $vehicle_type . "' AND assessment_type = '" . $assessmenttype . "' AND status='1' " . $rcond . " LIMIT " . $ques_count; //$ques_str = "SELECT * FROM ".TABLE_QUESTION." WHERE status='1' AND cateid='" . $lvl_id . "' " . $rcond . " LIMIT " . $ques_count; $ques_arr=$parent->getQueryValue($ques_str); if(count($ques_arr)>0) { $ins_ques_arr=array(); foreach($ques_arr as $res_ques) { $ins_ques_arr[] =" ( '".$assessmentid."','".$res_ques->id."', '1', '".$trainer_id."','".NOW."', '".NOW."')"; } //ASSESSMENT QUESTION $insert_qry = "INSERT INTO ".TABLE_ASSESSMENT_QUESTION ." (`assessmentid`, `qid`, `is_mandatory`, `creatorid`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_ques_arr); $parent->executeQuery($insert_qry); } } } } $parent->response(array('response'=>'success', 'message'=>$this->page_data['success1']),200); } // Training Schedule, Reschedule and Cancel Notification private function trainingnotifictaion ($parent, $obj) { if(!(isset($obj) && isset($obj->schedule_id))) $parent->response(array('response'=>'failed','message'=>'PHP ERROR Value not passed'),500); $schedule_id = $obj->schedule_id; $query_str = "SELECT s.schedule_name, s.start_date, s.end_date, tl.lvl_name as title FROM ".TABLE_TRAINING_SCHEDULE." s JOIN " . TABLE_TRAINING_LEVEL . " tl ON s.lvl_id = tl.lvl_id WHERE s.is_deleted='0' AND s.schedule_id='".$schedule_id."' LIMIT 1"; $query_arr=$parent->getQueryValue($query_str); // Set condition for Feedback Notofications $training_notification_type = $obj->type; if ($training_notification_type == 'feedback') { //$user_condition = " AND status='1' and is_attendance = 1 "; $user_condition = " AND status in (0,1) "; } if ($training_notification_type == 'pre' or $training_notification_type == 'post' or $training_notification_type == 'userscore_certified' or $training_notification_type == 'userscore_fail') { $user_condition .= " AND userid in (" . $obj->users . ") "; } $query_str = "SELECT userid FROM `".TABLE_TRAINING_PARTICIPANT."` WHERE schedule_id='".$schedule_id."' " . $user_condition . " "; $part_arr=$parent->getQueryValue($query_str); if(count($query_arr)>0 && count($part_arr) > 0) { if ($obj->type == 'new') { $schedule_type_name = 'New Training Scheduled '; } else if ($obj->type == 'update') { $schedule_type_name = 'Training Updated '; } else if ($obj->type == 'cancel') { $schedule_type_name = 'Training Cancelled '; } else if ($obj->type == 'reschedule') { $schedule_type_name = 'Training rescheduled '; } else if ($obj->type == 'feedback') { $schedule_type_name = 'Training Feedback '; } else if ($obj->type == 'pre') { $schedule_type_name = 'Training Pre Assessment '; } else if ($obj->type == 'post') { $schedule_type_name = 'Training Post Assessment '; } else if ($obj->type == 'score') { $schedule_type_name = 'Result of '; } // Notification Text if ($obj->type == 'feedback' OR $obj->type == 'pre' OR $obj->type == 'post') { if ($obj->type == 'pre') { $assessmenttype = 1; } else if ($obj->type == 'post') { $assessmenttype = 2; } $assess_query_str = "SELECT assessmentid, assessmenttime, assessmenttype FROM `".TABLE_ASSESSMENT."` WHERE schedule_id='".$schedule_id."' AND assessmenttype = '".$assessmenttype."' "; $assess_arr = $parent->getQueryValue($assess_query_str); $ntext = $schedule_type_name . ' - ' . $query_arr[0]->title . ' Duration : ' . $assess_arr[0]->assessmenttime; } else if ($obj->type == 'score') { $ntext = $schedule_type_name . $query_arr[0]->title . " date : " . date('d M Y',strtotime($query_arr[0]->start_date))." - ".date('d M Y',strtotime($query_arr[0]->end_date)) . " training has been published."; } else if ($obj->type == 'userscore_certified') { $ntext = 'Congratulations! You have cleared ' . $query_arr[0]->title . ' Certification'; } else if ($obj->type == 'userscore_fail') { $ntext = 'You have not cleared the ' . $query_arr[0]->title . ' Certification, You need to attend the training again.'; } else { $ntext = $schedule_type_name . ' - ' . $query_arr[0]->title . " from date : ".date('d M Y',strtotime($query_arr[0]->start_date))." - ".date('d M Y',strtotime($query_arr[0]->end_date)); } $notificationtitle = $query_arr[0]->title . ' - ' . $query_arr[0]->schedule_name; $notificationtext = $ntext; $notificationtype = 10; if ($training_notification_type == 'pre' OR $training_notification_type == 'post') { $notificationtype = 2; } else if ($training_notification_type == 'feedback') { $notificationtype = 11; } else if ($training_notification_type == 'score' OR $training_notification_type == 'userscore_certified' OR $training_notification_type == 'userscore_fail' ) { $notificationtype = 12; } $nobj = new stdClass(); $nobj->schedule_id = $schedule_id; if ($obj->type == 'feedback' OR $obj->type == 'pre' OR $obj->type == 'post') { $nobj->assessmentid = $assess_arr[0]->assessmentid; } $notificationdata = json_encode($nobj); //INSERT NOTIFICATION $parent->executeQuery("INSERT INTO `".TABLE_NOTIFICATION."` ( `notificationtitle`, `notificationtext`, `notificationdata`, `notificationtype`, `status`, `publisheddate`, `created_on`, `modified_on`) VALUES ('".escape_str($notificationtitle)."','".escape_str($notificationtext)."','".$notificationdata."','".$notificationtype."', '1','".NOW."','".NOW."','".NOW."')"); $notificationid = $parent->lastInsertId(); $data = array('notification_id'=>$notificationid, 'title'=>$notificationtitle, 'message'=>$notificationtext, 'notificationtype'=>$notificationtype, 'notificationdata'=>$notificationdata, 'created_date'=>NOW); if(count($part_arr)>0) { foreach ($part_arr as $res_user) { $user_arr[] = $res_user->userid; $dse_ids .= $res_user->userid . ','; } // Sending notification only to dealer, AM, RM, ZM if ($training_notification_type == 'score') { $user_arr = array(); } if ($training_notification_type == 'new' or $training_notification_type == 'cancel' or $training_notification_type == 'reschedule' or $training_notification_type == 'score' or $training_notification_type == 'update') { // Get Dealer code of DSE $dse_ids = trim($dse_ids, ','); $dealer_str = " SELECT dealer_code FROM `".TABLE_USER."` WHERE userid in (".$dse_ids.") GROUP BY dealer_code "; $dealer_arr = $parent->getQueryValue($dealer_str); if(count($dealer_arr) > 0) { foreach ($dealer_arr as $dealers) { $dealer_codes .= $dealers->dealer_code . ','; } $dealer_codes = trim($dealer_codes, ','); } // Get ZM ids if (!empty($dealer_codes)) { $zm_str = "SELECT u.userid FROM `".TABLE_USER."` u WHERE emailid in (SELECT zm_email FROM `".DMS_DEALER."` WHERE dealer_code in (".$dealer_codes.") GROUP BY zm_email) "; $zm_arr = $parent->getQueryValue($zm_str); if(count($zm_arr)>0) { foreach ($zm_arr as $zm_res_user) { $user_arr[] = $zm_res_user->userid; } } // Get RM ids $rm_str = "SELECT u.userid FROM `".TABLE_USER."` u WHERE emailid in (SELECT rm_email FROM `".DMS_DEALER."` WHERE dealer_code in (".$dealer_codes.") GROUP BY rm_email) "; $rm_arr = $parent->getQueryValue($rm_str); if(count($rm_arr)>0) { foreach ($rm_arr as $rm_res_user) { $user_arr[] = $rm_res_user->userid; } } // Get AM ids $am_str = "SELECT u.userid FROM `".TABLE_USER."` u WHERE emailid in (SELECT am_email FROM `".DMS_DEALER."` WHERE dealer_code in (".$dealer_codes.") GROUP BY am_email) "; $am_arr = $parent->getQueryValue($am_str); if(count($am_arr)>0) { foreach ($am_arr as $am_res_user) { $user_arr[] = $am_res_user->userid; } } // Get DEALER ids $dealer_str = "SELECT u.userid FROM `".TABLE_USER."` u WHERE dealer_code in (".$dealer_codes.") GROUP BY u.dealer_code "; $dealer_arr = $parent->getQueryValue($dealer_str); if(count($dealer_arr)>0) { foreach ($dealer_arr as $dealer_res_user) { $user_arr[] = $dealer_res_user->userid; } } } } //print_r($user_arr);exit(); $ins_arr = array(); foreach($user_arr as $res) { $ins_arr[] = " ('".$notificationid."', '".$res."', '0', '".NOW."')"; } $insert_chunk = array_chunk($ins_arr, 1000); foreach($insert_chunk as $chunk_ins) { //print "INSERT INTO ".TABLE_NOTIFICATION_USER."(`notificationid`, `userid`, `status`, `created_on`) VALUES ".@implode(',',$chunk_ins)." ON DUPLICATE KEY UPDATE `status` =0"; $parent->executeQuery("INSERT INTO ".TABLE_NOTIFICATION_USER."(`notificationid`, `userid`, `status`, `created_on`) VALUES ".@implode(',',$chunk_ins)." ON DUPLICATE KEY UPDATE `status` =0"); } $usertokenarr['ios']=array(); $usertokenarr['android']=array(); $user_device_str = "SELECT ud.devicetype,ud.token FROM `".TABLE_USER_DEVICES."` as ud JOIN (SELECT userid FROM ".TABLE_NOTIFICATION_USER." WHERE notificationid='".$notificationid."') as u ON ud.userid=u.userid WHERE ud.is_deleted='0' AND (devicetype='ios' OR devicetype='android') AND token!=''"; $user_device_arr = $parent->getQueryValue($user_device_str); if(count($user_device_arr) > 0) { foreach($user_device_arr as $res_dev) { //print_r($user_device_arr); if(strtolower($res_dev->devicetype) == 'ios') { $usertokenarr['ios'][] = $res_dev->token; } else if(strtolower($res_dev->devicetype) == 'android') { $usertokenarr['android'][] = $res_dev->token; } } // globalfunc::sendUserNotification($data,$usertokenarr); } } } } // Cancel Training private function canceltraining ($parent) { globalfunc::checkMethodType($parent,array('GET' )); globalfunc::checkAuthrozation($parent,array('type'=>'user')); globalfunc::checkParamCount($parent,3); $schedule_id = escape_str($parent->params[1]); $cancel_or_reschedule_type = escape_str($parent->params[2]); $update_qry = "UPDATE ".TABLE_TRAINING_SCHEDULE ." SET `cancel_or_reschedule_type`='".$cancel_or_reschedule_type."', `is_deleted`='1', `modified_on`='".NOW."' WHERE schedule_id='".$schedule_id."'"; $query = $parent->executeQuery($update_qry); if ($query) { // Training Notification $training_str = "SELECT status FROM ".TABLE_TRAINING_SCHEDULE." WHERE schedule_id='".$schedule_id."' LIMIT 1 "; $training_arr = $parent->getQueryValue($training_str); if ($training_arr[0]->status == 1) { $mobj = new stdClass(); $mobj->schedule_id = $schedule_id; $mobj->type = 'cancel'; $this->trainingnotifictaion($parent, $mobj); } } $parent->response(array('response'=>'success', 'message'=>$this->page_data['success1']),200); } // Reschedule Training private function rescheduletraining ($parent) { globalfunc::checkMethodType($parent,array('POST' )); globalfunc::checkAuthrozation($parent,array('type'=>'user')); globalfunc::checkParamCount($parent,3); $startdate=escape_str($parent->_request["startdate"]); $enddate=escape_str($parent->_request["enddate"]); $starttime=escape_str($parent->_request["starttime"]); $endtime=escape_str($parent->_request["endtime"]); $training_total_hours = escape_str($parent->_request["training_total_hours"]); // if($starttime!='' && $endtime!='') { // $startdate.=" ".$starttime; // $enddate.=" ".$endtime; // }else { // $startdate.=" 00:00:00"; // $enddate.=" 23:59:59"; // } $todaydate_time = date('Y-m-d H:i:s'); $starttime = stripslashes($starttime); $starttime_array = json_decode($starttime, true); $endtime_array = json_decode($endtime, true); // Check Condition about 6 hours $date_time_diff_str = strtotime($startdate . ' ' . $starttime_array[0]) - strtotime($todaydate_time); $date_time_diff_str = round($date_time_diff_str / (60 * 60)); if ($date_time_diff_str < 1) { $parent->response(array('response'=>'failed','message'=>'You can able to reschedule training before 1 hours '),406); } $schedule_id = escape_str($parent->params[1]); $cancel_or_reschedule_type = escape_str($parent->params[2]); $update_qry = "UPDATE ".TABLE_TRAINING_SCHEDULE ." SET `start_date`='".$startdate."', `end_date`='".$enddate."', `starttime`='".$starttime."', `endtime`='".$endtime."', `training_total_hours`='".$training_total_hours."', `cancel_or_reschedule_type`='".$cancel_or_reschedule_type."', `reschedule_status`='1', `modified_on`='".NOW."' WHERE schedule_id='".$schedule_id."'"; $query = $parent->executeQuery($update_qry); if ($query) { // Update Assessment for($assessmenttype=1; $assessmenttype<=2; $assessmenttype++) { //GET ASSESSMENT START AND END DATES $buffer_day = 1; if ($assessmenttype == 1) { $assessment_startdate=date('Y-m-d H:i:s',strtotime($startdate)); $assessment_enddate=date('Y-m-d H:i:s',strtotime('+'.$buffer_day.' day',strtotime($startdate))); } else { $assessment_startdate=date('Y-m-d H:i:s',strtotime($enddate)); $assessment_enddate=date('Y-m-d H:i:s',strtotime('+'.$buffer_day.' day',strtotime($enddate))); } $train_assess_str = "SELECT assessmentid FROM ".TABLE_ASSESSMENT." WHERE schedule_id='".$schedule_id."' AND assessmenttype = '" . $assessmenttype . "' LIMIT 1 "; $train_assess_arr = $parent->getQueryValue($train_assess_str); if(count($train_assess_arr) > 0) { $old_assessmentid = $train_assess_arr[0]->assessmentid; } $update_qry = "UPDATE ".TABLE_ASSESSMENT ." SET `publisheddate`='".$assessment_startdate."', `enddate`='".$assessment_enddate."' WHERE assessmentid='".$old_assessmentid."'"; $parent->executeQuery($update_qry); } // Training Notification $training_str = "SELECT status FROM ".TABLE_TRAINING_SCHEDULE." WHERE schedule_id='".$schedule_id."' LIMIT 1 "; $training_arr = $parent->getQueryValue($training_str); if ($training_arr[0]->status == 1) { $mobj = new stdClass(); $mobj->schedule_id = $schedule_id; $mobj->type = 'reschedule'; $this->trainingnotifictaion($parent, $mobj); } } $parent->response(array('response'=>'success', 'message'=>$this->page_data['success1']),200); } // Decline Treaining Reaosn list private function declinetrainingreasonlist ($parent) { globalfunc::checkMethodType($parent,array('GET' )); globalfunc::checkAuthrozation($parent,array('type'=>'user')); globalfunc::checkParamCount($parent,1); $reschedule_str = "SELECT id, name FROM ".TABLE_TRAINING_DECLINE_REASON_LIST." WHERE status = 1 "; $reschedule_arr = $parent->getQueryValue($reschedule_str); if(count($reschedule_arr) > 0) { $parent->response(array('response'=>'success', 'data'=> $reschedule_arr, 'message'=>$this->page_data['success1']),200); } } // Cancel Training private function acceptdeclinetraining ($parent) { globalfunc::checkMethodType($parent,array('POST' )); globalfunc::checkAuthrozation($parent,array('type'=>'user')); //globalfunc::checkParamCount($parent,4); $schedule_id = escape_str($parent->_request["schedule_id"]); $userid = escape_str($parent->_request["userid"]); $status = escape_str($parent->_request["status"]); $acknowledge_by = escape_str($parent->_request["acknowledge_by"]); $decline_reason = escape_str($parent->_request["decline_reason"]); $decline_reason_desc = escape_str($parent->_request["decline_reason_desc"]); $update_qry = "UPDATE ".TABLE_TRAINING_PARTICIPANT ." SET `status`='".$status."', acknowledge_by = '".$acknowledge_by."',acknowledge_by = '".$acknowledge_by."',`decline_reason`='".$decline_reason."', `decline_reason_desc`='".$decline_reason_desc."', `modified_on`='".NOW."' WHERE schedule_id='".$schedule_id."' AND userid='".$userid."'"; $parent->executeQuery($update_qry); $parent->response(array('response'=>'success', 'message'=>$this->page_data['success1']),200); } // Upload Training Group Image private function traininggroupimage ($parent) { globalfunc::checkParamCount($parent,1); $user_details=globalfunc::checkAuthrozation($parent,array('type'=>'user')); $REQUEST_KEYS=array('schedule_id'=>'number'); globalfunc::mandatoryKey($parent,$REQUEST_KEYS); $schedule_id = escape_str($parent->_request['schedule_id']); //CHECK FILE if(isset($_FILES['file']) && $_FILES['file']['name']) { //CONNECT S3 $s3=awsfileserver::connectS3(); //IMAGE UPLOAD $obj= new stdClass(); $obj->filekey='file'; $obj->size='10'; //IN MB $obj->type=array('jpg','jpeg','png'); globalfunc::validateFile($parent,$obj); $obj->path=$this->training_path; $obj->filename= 'profile_'.$parent->current_user_id.'_'.date('Ymd').uniqid(); if(isset($parent->_request['width']))$obj->width=$parent->_request['width']; if(isset($parent->_request['height']))$obj->height=$parent->_request['height']; $obj->cord=(isset($parent->_request['cord']))?$parent->_request['cord']:NULL; $fileName=globalfunc::uploadFile($parent,$obj); awsfileserver::checkupload($parent,$s3,S3_BUCKET,S3_TRAINING_URL.$fileName,dirname(__FILE__)."/../../../".S3_TRAINING_URL.$fileName) ; //UNLINK @unlink(dirname(__FILE__).'/../'.$this->training_path.$fileName); $econd=" ,training_group_img='".$fileName."'"; $parent->executeQuery(" UPDATE ".TABLE_TRAINING_SCHEDULE." SET training_group_image='".$fileName."', `modified_on` = '".NOW."' WHERE schedule_id = '".$schedule_id."' "); } //LOG $logstr='Training Group Photo UPDATE'; globalfunc::insertUserLog($parent,$logstr); $parent->response(array('response'=>'success', 'message'=>$this->page_data['success1']),200); } private function updatetraining($parent) { globalfunc::checkMethodType($parent,array('POST' )); globalfunc::checkAuthrozation($parent,array('type'=>'user')); $REQUEST_KEYS=array('schedule_id'=>'number', 'title'=>'number'); globalfunc::mandatoryKey($parent,$REQUEST_KEYS); $schedule_id=escape_str($parent->_request["schedule_id"]); $lvl_id=escape_str($parent->_request["title"]); $trainer_id=$parent->current_user_id; $adhoc_training=escape_str($parent->_request["adhoc_training"]); $business_segment=escape_str($parent->_request["business_segment"]); $training_other_title=escape_str($parent->_request["training_other_title"]); //$date_restriction=escape_str($parent->_request["date_restriction"]); $status=escape_str($parent->_request["status"]); $filter_zone = escape_str($parent->_request["filter_zone"]); $description = escape_str($parent->_request['description']); $is_feedback = escape_str($parent->_request["is_feedback"]); $venue_type = escape_str($parent->_request["venue_type"]); $venue_am = escape_str($parent->_request["venue_am"]); $venue_dealer = escape_str($parent->_request["venue_dealer"]); $venue_address = escape_str($parent->_request["venue_address"]); // if(strtotime($startdate)response(array('response'=>'failed','message'=>$this->page_data["error4"]),406); // if(strtotime($enddate)response(array('response'=>'failed','message'=>$this->page_data["error4"]),406); //if(strtotime($startdate)>strtotime($enddate)) $parent->response(array('response'=>'failed','message'=>'from date should be greater than to date'),406); $dse = $parent->_request['dse']; $participant_arr = json_decode($dse); //sending as string if(!(is_array($participant_arr) && count($participant_arr)>0)) $parent->response(array('response'=>'failed','message'=>$this->page_data["error2"]),406); //$update_qry = "UPDATE ".TABLE_TRAINING_SCHEDULE ." SET `lvl_id`='".$lvl_id."', `schedule_name`='".$description."', `trainer_id`='".$trainer_id."', `schedule_name`='".$description."', `start_date`='".$startdate."', `end_date`='".$enddate."', `starttime`='".$starttime."', `endtime`='".$endtime."', `training_total_hours`='".$training_total_hours."', `date_restriction`='".$date_restriction."', `venue_type`='".$venue_type."', `venue_am`='".$venue_am."', `venue_dealer`='".$venue_dealer."', `venue_address`='".$venue_address."', `filter_zone`='".$filter_zone."', `adhoc_training`='".$adhoc_training."', `business_segment`='".$business_segment."', `training_other_title`='".$training_other_title."', `status`='".$status."', `is_feedback`='".$is_feedback."', modified_on='".NOW."' WHERE schedule_id='".$schedule_id."'"; $update_qry = "UPDATE ".TABLE_TRAINING_SCHEDULE ." SET `lvl_id`='".$lvl_id."', `schedule_name`='".$description."', `trainer_id`='".$trainer_id."', `schedule_name`='".$description."', `venue_type`='".$venue_type."', `venue_am`='".$venue_am."', `venue_dealer`='".$venue_dealer."', `venue_address`='".$venue_address."', `filter_zone`='".$filter_zone."', `adhoc_training`='".$adhoc_training."', `business_segment`='".$business_segment."', `training_other_title`='".$training_other_title."', `status`='".$status."', `is_feedback`='".$is_feedback."', modified_on='".NOW."' WHERE schedule_id='".$schedule_id."'"; $parent->executeQuery($update_qry); if(count($participant_arr)>0) { // Delete Old One $delete_qry = $parent->executeQuery("DELETE FROM " . TABLE_TRAINING_PARTICIPANT . " WHERE schedule_id = '" . $schedule_id . "' "); $participant_arr= array_unique($participant_arr); $ins_participant_arr=array(); foreach($participant_arr as $userid) { $ins_participant_arr[] =" ( '".$schedule_id."', '".$userid."', '0', '0', '0', '".$trainer_id."','".NOW."', '".NOW."')"; } //print_r($ins_participant_arr); $insert_qry = "INSERT INTO ".TABLE_TRAINING_PARTICIPANT ." ( `schedule_id`, `userid`, `is_attendance`, `score`, `assessment_score`, `creatorid`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_participant_arr); $parent->executeQuery($insert_qry); $assessments = $parent->_request['assessments']; $assessmenttype_array=json_decode($assessments, true); if(count($assessmenttype_array) > 0) { //print_r($assessmenttype_array); // $delete_qry = $parent->executeQuery("DELETE FROM " . TABLE_ASSESSMENT . " WHERE schedule_id = '" . $schedule_id . "' "); // $delete_qry = $parent->executeQuery("DELETE FROM " . TABLE_TRAINING_ASSESSMENT . " WHERE schedule_id = '" . $schedule_id . "' "); //ASSESSMENT QUESTION ADD $vehicle_type = 0; if ($business_segment == 1) { $vehicle_type = 1; } else if ($business_segment == 2) { $vehicle_type = 2; } foreach ($assessmenttype_array as $assessmentkey => $assessmenttypes) { if(!empty($assessmenttypes['name'])) { if ($assessmenttypes['assessment'] == 'pre') { $assessmenttype = '1'; } else if ($assessmenttypes['assessment'] == 'post') { $assessmenttype = '2'; } $assessmentname = $assessmenttypes['name']; $ques_count = $assessmenttypes["ques_count"]; $assessmenttime = $assessmenttypes["duration"]; $train_assess_str = "SELECT assessmentid FROM ".TABLE_ASSESSMENT." WHERE schedule_id='".$schedule_id."' AND assessmenttype = '" . $assessmenttype . "' LIMIT 1 "; $train_assess_arr = $parent->getQueryValue($train_assess_str); if(count($train_assess_arr) > 0) { $old_assessmentid = $train_assess_arr[0]->assessmentid; } $delete_qry = $parent->executeQuery("DELETE FROM " . TABLE_ASSESSMENT_QUESTION . " WHERE assessmentid = '" . $old_assessmentid . "' "); //INSERT ASSESSMENT // $insert_qry = "INSERT INTO ".TABLE_ASSESSMENT ." ( `atype`, `lvl_id`, `assessmentname`, `description`, `assessmentimage`, `assessmenttype`, `assettypeid`, `assessmenttimetype`, `assessmenttime`, `attemptslimit`, `publisheddate`, `enddate`, `randomcond`, `filter_role`, `filter_zone`, `filter_region`, `filter_dealer`, `assessmentnotification`, `total_question`,`schedule_id`, `status`, `creatorid`, `created_on`, `modified_on`) VALUES ('1','".$lvl_id."','".$assessmentname."','".$assessmentname."','','".$assessmenttype."','1','1','".$assessmenttime."','1','".$assessment_startdate."','".$assessment_enddate."','".$randomcond."','','','','','','".$ques_count."','".$schedule_id."','1','".$trainer_id."','".NOW."','".NOW."')"; // $parent->executeQuery($insert_qry); // $assessmentid = $parent->lastInsertId(); $update_qry = "UPDATE ".TABLE_ASSESSMENT ." SET `assessmentname`='".$assessmentname."', `total_question`='".$ques_count."', `assessmenttime`='".$assessmenttime."', modified_on='".NOW."' WHERE assessmentid='".$old_assessmentid."'"; $parent->executeQuery($update_qry); /* if(count($participant_arr)>0) { $ins_assess_arr=array(); foreach($participant_arr as $userid) { $ins_assess_arr[] =" ( '".$assessmentid."', '".$schedule_id."', '".$userid."', '0')"; } } //ASSESSMENT PARTICPANT ADD $insert_qry = "INSERT INTO ".TABLE_TRAINING_ASSESSMENT ." ( `assessmentid`, `schedule_id`, `userid`, `is_attended`) VALUES ".@implode(',',$ins_assess_arr); $parent->executeQuery($insert_qry);*/ //ASSESSMENT QUESTION ADD $rcond = "ORDER BY RAND()"; $ques_str = "SELECT * FROM ".TABLE_QUESTION." WHERE vehicle_type = '" . $vehicle_type . "' AND assessment_type = '" . $assessmenttype . "' AND status='1' " . $rcond . " LIMIT " . $ques_count; $ques_arr=$parent->getQueryValue($ques_str); if(count($ques_arr)>0) { $ins_ques_arr=array(); foreach($ques_arr as $res_ques) { $ins_ques_arr[] =" ( '".$old_assessmentid."','".$res_ques->id."', '1', '".$trainer_id."','".NOW."', '".NOW."')"; } //ASSESSMENT QUESTION $insert_qry = "INSERT INTO ".TABLE_ASSESSMENT_QUESTION ." (`assessmentid`, `qid`, `is_mandatory`, `creatorid`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_ques_arr); $parent->executeQuery($insert_qry); } } } } } // Training Notification if ($status == 1) { $mobj = new stdClass(); $mobj->schedule_id = $schedule_id; $mobj->type = 'update'; $this->trainingnotifictaion($parent, $mobj); } $parent->response(array('response'=>'success', 'message'=>$this->page_data['success1']),200); } private function updatetrainingpresentabsent ($parent) { globalfunc::checkMethodType($parent,array('POST' )); globalfunc::checkAuthrozation($parent,array('type'=>'user')); globalfunc::checkParamCount($parent,1); $REQUEST_KEYS = array('schedule_id'=>'number', 'attendance'=>'string'); globalfunc::mandatoryKey($parent,$REQUEST_KEYS); $schedule_id = $parent->_request['schedule_id']; $attendance = $parent->_request['attendance']; $attendance_submit = $parent->_request['attendance_submit']; $attendance_array = json_decode($attendance, true); // Gertting Training Details $schedule_str = "SELECT start_date, end_date FROM ".TABLE_TRAINING_SCHEDULE." WHERE schedule_id='".$schedule_id."' LIMIT 1 "; $schedule_arr = $parent->getQueryValue($schedule_str); if(count($schedule_arr) > 0) { $datediff = strtotime($schedule_arr[0]->end_date) - strtotime($schedule_arr[0]->start_date); $training_days = round($datediff / (60 * 60 * 24)) + 1; } //print_r($attendance_array);exit(); if(count($attendance_array) > 0) { $todaydate = date('Y-m-d'); // Get Pre Assessment Id $pre_assess_str = "SELECT assessmentid FROM ".TABLE_ASSESSMENT." WHERE schedule_id='".$schedule_id."' AND assessmenttype='1' LIMIT 1 "; $pre_assess_arr = $parent->getQueryValue($pre_assess_str); // Get Post Assessment Id $post_assess_str = "SELECT assessmentid FROM ".TABLE_ASSESSMENT." WHERE schedule_id='".$schedule_id."' AND assessmenttype='2' LIMIT 1 "; $post_assess_arr = $parent->getQueryValue($post_assess_str); foreach ($attendance_array as $attendance_key => $atten) { $userid = $atten['userid']; $day = $atten['day']; $ispresent = $atten['ispresent']; if ($atten['ispresent'] == 2) { $absent_users .= $userid . ','; } $manual_attendance_reason = $atten['manual_attendance_reason']; $absence_reason = $atten['absence_reason']; //CHECK FILE //if(isset($_FILES['upload_photo']) && $_FILES['upload_photo']['name']) { $fileName = ''; if (isset($atten['upload_photo']) AND $atten['ispresent'] == 1) { $image_parts = explode(";base64,", $atten['upload_photo']); $image_type_aux = explode("image/", $image_parts[0]); $image_type = $image_type_aux[1]; if (!empty($image_type)) { $image_base64 = base64_decode($image_parts[1]); $fileName = 'atten_'.$atten['userid'].'_'.date('Ymd').uniqid() . '.' . $image_type; file_put_contents($this->training_path . $fileName, $image_base64); //CONNECT S3 $s3=awsfileserver::connectS3(); //IMAGE UPLOAD // $obj= new stdClass(); // $obj->filekey='upload_photo'; // $obj->size='10'; //IN MB // $obj->type=array('jpg','jpeg','png'); // globalfunc::validateFile($parent,$obj); // $obj->path=$this->training_path; // $obj->filename= 'profile_'.$parent->current_user_id.'_'.date('Ymd').uniqid(); // if(isset($parent->_request['width']))$obj->width=$parent->_request['width']; // if(isset($parent->_request['height']))$obj->height=$parent->_request['height']; // $obj->cord=(isset($parent->_request['cord']))?$parent->_request['cord']:NULL; // $fileName=globalfunc::uploadFile($parent,$obj); awsfileserver::checkupload($parent,$s3,S3_BUCKET,S3_TRAINING_URL.$fileName,dirname(__FILE__)."/../../../".S3_TRAINING_URL.$fileName) ; //UNLINK @unlink(dirname(__FILE__).'/../'.$this->training_path . $fileName); // Update Condition $file_condition = " ,`image`='" . $fileName . "'"; } } // Check already attendance available or not $atten_str = "SELECT id FROM ".TABLE_TRAINING_PARTICIPANT_ATTENDANCE." WHERE schedule_id = '" . $schedule_id . "' AND userid='" . $userid . "' AND day='" . $day . "' LIMIT 1"; //print $ispresent . '--'. $day . '--'. $pre_assess_arr[0]->assessmentid; $atten_arr = $parent->getQueryValue($atten_str); if(count($atten_arr) == 0) { // UPDATE TRAINING $insert_qry = "INSERT INTO ".TABLE_TRAINING_PARTICIPANT_ATTENDANCE ." (`schedule_id`, `userid`, `day`, `ispresent`, `image`, `manual_attendance_reason`, `absence_reason`, `status`, `created_on`, `modified_on`) VALUES ( '".$schedule_id."', '".$userid."', '".$day."', '".$ispresent."', '".$fileName."', '".$manual_attendance_reason."', '".$absence_reason."', '1', '".NOW."', '".NOW."') "; $parent->executeQuery($insert_qry); } else { $update_qry = "UPDATE ".TABLE_TRAINING_PARTICIPANT_ATTENDANCE ." SET `day`='".$day."', `ispresent`='" . $ispresent . "', `absence_reason`='" . $absence_reason . "', `manual_attendance_reason`='" . $manual_attendance_reason . "', `status`='" . $status . "', `modified_on` = '".NOW."' " . $file_condition . " WHERE schedule_id = '".$schedule_id."' AND userid = '" . $userid . "' AND day='" . $day . "' "; $parent->executeQuery($update_qry); } // PRE ASSESSMENT PARTICPANT ADD if ($ispresent == 1 AND $day == 1 AND !empty($pre_assess_arr[0]->assessmentid)) { // Get Pre Assessment Id $check_pre_assess_user_str = "SELECT assessmentid FROM ".TABLE_TRAINING_ASSESSMENT." WHERE schedule_id='".$schedule_id."' AND assessmentid='".$pre_assess_arr[0]->assessmentid."' AND userid='".$userid."' LIMIT 1 "; $check_pre_assess_user_arr = $parent->getQueryValue($check_pre_assess_user_str); if (count($check_pre_assess_user_arr) == 0) { $ins_pre_assess_arr[] =" ( '".$pre_assess_arr[0]->assessmentid."', '".$schedule_id."', '".$userid."', '0')"; $pre_assess_notification_users .= $userid . ','; } } // POST ASSESSMENT PARTICPANT ADD //print $ispresent . '--'. strtotime($schedule_arr[0]->end_date) . '--'. strtotime($todaydate) . '--'. $post_assess_arr[0]->assessmentid . '--'. $attendance_submit; if ($ispresent == 1 AND ( strtotime($schedule_arr[0]->end_date) == strtotime($todaydate) ) AND !empty($post_assess_arr[0]->assessmentid) AND (!empty($attendance_submit)) ) { // Get Pre Assessment Id $check_post_assess_user_str = "SELECT assessmentid FROM ".TABLE_TRAINING_ASSESSMENT." WHERE schedule_id='".$schedule_id."' AND assessmentid='".$post_assess_arr[0]->assessmentid."' AND userid='".$userid."' LIMIT 1 "; $check_post_assess_user_arr = $parent->getQueryValue($check_post_assess_user_str); if (count($check_post_assess_user_arr) == 0) { $ins_post_assess_arr[] =" ( '".$post_assess_arr[0]->assessmentid."', '".$schedule_id."', '".$userid."', '0')"; $post_assess_notification_users .= $userid . ','; } } // Giving Default adbsent for other days if 1st day is absent start if ( ($training_days > 1) AND ($day == 1 and $ispresent == 2) ) { for($i = 2; $i <= $training_days; $i++) { $atten_str = "SELECT id FROM ".TABLE_TRAINING_PARTICIPANT_ATTENDANCE." WHERE schedule_id = '" . $schedule_id . "' AND userid='" . $userid . "' AND day='" . $i . "' LIMIT 1"; $atten_arr = $parent->getQueryValue($atten_str); if(count($atten_arr) == 0) { //ASSESSMENT QUESTION $insert_qry = "INSERT INTO ".TABLE_TRAINING_PARTICIPANT_ATTENDANCE ." (`schedule_id`, `userid`, `day`, `ispresent`, `image`, `manual_attendance_reason`, `absence_reason`, `status`, `created_on`, `modified_on`) VALUES ( '".$schedule_id."', '".$userid."', '".$i."', '2', '".$fileName."', '".$manual_attendance_reason."', '".$absence_reason."', '1', '".NOW."', '".NOW."') "; $parent->executeQuery($insert_qry); } else { $update_qry = "UPDATE ".TABLE_TRAINING_PARTICIPANT_ATTENDANCE ." SET `day`='".$i."', `ispresent`='2', `absence_reason`='" . $absence_reason . "', `manual_attendance_reason`='" . $manual_attendance_reason . "', `status`='" . $status . "', `modified_on` = '".NOW."' WHERE schedule_id = '".$schedule_id."' AND userid = '" . $userid . "' AND day='" . $i . "' "; $parent->executeQuery($update_qry); } } } // Giving Default adbsent for other days if 1st day is absent end } // Update Submit Status in Training Schedule if (!empty($parent->_request['attendance_submit'])) { $update_qry = "UPDATE ".TABLE_TRAINING_SCHEDULE ." SET `attendance_submit`='".$attendance_submit."', `modified_on` = '".NOW."' WHERE schedule_id = '".$schedule_id."' "; $parent->executeQuery($update_qry); $absent_users = trim($absent_users, ','); //print $absent_users;exit(); if (!empty($absent_users) ) { // Get DSE id from Users $dse_str = " SELECT smpin FROM `".TABLE_USER."` WHERE userid in (".$absent_users.") "; $dse_arr = $parent->getQueryValue($dse_str); if(count($dse_arr) > 0) { foreach ($dse_arr as $dses) { $dse_codes .= $dses->smpin . ', '; } $dse_codes = trim($dse_codes, ', '); } // Training Notification $mobj = new stdClass(); $mobj->schedule_id = $schedule_id; $mobj->type = 'absent'; $mobj->absent_users = $absent_users; $mobj->dse_codes = $dse_codes; $this->trainingabsentnotifictaion($parent, $mobj); } } // Insert Pre Assessment Users if (!empty($ins_pre_assess_arr)) { $insert_qry = "INSERT INTO ".TABLE_TRAINING_ASSESSMENT ." ( `assessmentid`, `schedule_id`, `userid`, `is_attended`) VALUES ".@implode(',', $ins_pre_assess_arr); $parent->executeQuery($insert_qry); // Notifications $pre_assess_notification_users = trim($pre_assess_notification_users, ','); $mobj = new stdClass(); $mobj->schedule_id = $schedule_id; $mobj->type = 'pre'; $mobj->users = $pre_assess_notification_users; $this->trainingnotifictaion($parent, $mobj); } // Insert Post Assessment Users if (!empty($ins_post_assess_arr)) { $insert_qry = "INSERT INTO ".TABLE_TRAINING_ASSESSMENT ." ( `assessmentid`, `schedule_id`, `userid`, `is_attended`) VALUES ".@implode(',', $ins_post_assess_arr); $parent->executeQuery($insert_qry); // Notifications $post_assess_notification_users = trim($post_assess_notification_users, ','); $mobj = new stdClass(); $mobj->schedule_id = $schedule_id; $mobj->type = 'post'; $mobj->users = $post_assess_notification_users; $this->trainingnotifictaion($parent, $mobj); } } $parent->response(array('response'=>'success', 'message'=>$this->page_data['success1']),200); } // Training Absent Notification private function trainingabsentnotifictaion ($parent, $obj) { if(!(isset($obj) && isset($obj->schedule_id))) $parent->response(array('response'=>'failed','message'=>'PHP ERROR Value not passed'),500); $schedule_id = $obj->schedule_id; $query_str = "SELECT s.schedule_name, s.start_date, s.end_date, tl.lvl_name as title FROM ".TABLE_TRAINING_SCHEDULE." s JOIN " . TABLE_TRAINING_LEVEL . " tl ON s.lvl_id = tl.lvl_id WHERE s.is_deleted='0' AND s.schedule_id='".$schedule_id."' LIMIT 1"; $query_arr=$parent->getQueryValue($query_str); // $query_str = "SELECT userid FROM `".TABLE_TRAINING_PARTICIPANT."` WHERE schedule_id='".$schedule_id."' "; // $part_arr = $parent->getQueryValue($query_str); $absent_users = $obj->absent_users; $dse_codes = $obj->dse_codes; if(count($query_arr) > 0 && !empty($absent_users)) { if ($obj->type == 'absent') { $schedule_type_name = 'Red Alert - '; $ntext = $schedule_type_name . "You are marked absent in today's - " . $query_arr[0]->title . " training"; } $notificationtitle = $query_arr[0]->title . ' - ' . $query_arr[0]->schedule_name; $notificationtext = $ntext; $notificationtype = 10; $nobj = new stdClass(); $nobj->schedule_id = $schedule_id; $notificationdata = json_encode($nobj); //INSERT NOTIFICATION $parent->executeQuery("INSERT INTO `".TABLE_NOTIFICATION."` ( `notificationtitle`, `notificationtext`, `notificationdata`, `notificationtype`, `status`, `publisheddate`, `created_on`, `modified_on`) VALUES ('".escape_str($notificationtitle)."','".escape_str($notificationtext)."','".$notificationdata."','".$notificationtype."', '1','".NOW."','".NOW."','".NOW."')"); $notificationid = $parent->lastInsertId(); $data = array('notification_id'=>$notificationid, 'title'=>$notificationtitle, 'message'=>$notificationtext, 'notificationtype'=>$notificationtype, 'notificationdata'=>$notificationdata, 'created_date'=>NOW); if(!empty($absent_users)) { $part_arr = explode(',', $absent_users); foreach ($part_arr as $res_user) { $user_arr[] = $res_user; } // Absent Notification to User $ins_arr = array(); foreach($user_arr as $res) { $ins_arr[] = " ('".$notificationid."', '".$res."', '0', '".NOW."')"; } $insert_chunk = array_chunk($ins_arr, 1000); foreach($insert_chunk as $chunk_ins) { //print "INSERT INTO ".TABLE_NOTIFICATION_USER."(`notificationid`, `userid`, `status`, `created_on`) VALUES ".@implode(',',$chunk_ins)." ON DUPLICATE KEY UPDATE `status` =0"; $parent->executeQuery("INSERT INTO ".TABLE_NOTIFICATION_USER."(`notificationid`, `userid`, `status`, `created_on`) VALUES ".@implode(',',$chunk_ins)." ON DUPLICATE KEY UPDATE `status` =0"); } $usertokenarr['ios']=array(); $usertokenarr['android']=array(); $user_device_str = "SELECT ud.devicetype,ud.token FROM `".TABLE_USER_DEVICES."` as ud JOIN (SELECT userid FROM ".TABLE_NOTIFICATION_USER." WHERE notificationid='".$notificationid."') as u ON ud.userid=u.userid WHERE ud.is_deleted='0' AND (devicetype='ios' OR devicetype='android') AND token!=''"; $user_device_arr = $parent->getQueryValue($user_device_str); if(count($user_device_arr) > 0) { foreach($user_device_arr as $res_dev) { //print_r($user_device_arr); if(strtolower($res_dev->devicetype) == 'ios') { $usertokenarr['ios'][] = $res_dev->token; } else if(strtolower($res_dev->devicetype) == 'android') { $usertokenarr['android'][] = $res_dev->token; } } // globalfunc::sendUserNotification($data,$usertokenarr); } // Absent Notification to AM if (!empty($dse_codes)) { $notication_dse = '( ' . $dse_codes . ' )'; } $schedule_type_name = 'Red Alert - '; $ntext = $schedule_type_name . 'Your DSE ' . $notication_dse . ' s is/are absent for - ' . $query_arr[0]->title . ' training'; $notificationtitle = $query_arr[0]->title . ' - ' . $query_arr[0]->schedule_name; $notificationtext = $ntext; $notificationtype = 10; $nobj = new stdClass(); $nobj->schedule_id = $schedule_id; $notificationdata = json_encode($nobj); //INSERT NOTIFICATION $parent->executeQuery("INSERT INTO `".TABLE_NOTIFICATION."` ( `notificationtitle`, `notificationtext`, `notificationdata`, `notificationtype`, `status`, `publisheddate`, `created_on`, `modified_on`) VALUES ('".escape_str($notificationtitle)."','".escape_str($notificationtext)."','".$notificationdata."','".$notificationtype."', '1','".NOW."','".NOW."','".NOW."')"); $notificationid = $parent->lastInsertId(); $data = array('notification_id'=>$notificationid, 'title'=>$notificationtitle, 'message'=>$notificationtext, 'notificationtype'=>$notificationtype, 'notificationdata'=>$notificationdata, 'created_date'=>NOW); // Get Dealer code of DSE $dealer_str = " SELECT dealer_code FROM `".TABLE_USER."` WHERE userid in (".$absent_users.") GROUP BY dealer_code "; $dealer_arr = $parent->getQueryValue($dealer_str); if(count($dealer_arr) > 0) { foreach ($dealer_arr as $dealers) { $dealer_codes .= $dealers->dealer_code . ','; } $dealer_codes = trim($dealer_codes, ','); } // Get AM ids $user_arr = ''; $zm_str = "SELECT u.userid FROM `".TABLE_USER."` u WHERE emailid in (SELECT am_email FROM `".DMS_DEALER."` WHERE dealer_code in (".$dealer_codes.") GROUP BY am_email) "; $zm_arr = $parent->getQueryValue($zm_str); if(count($zm_arr)>0) { foreach ($zm_arr as $res_user) { $user_arr[] = $res_user->userid; } } // Get Dealer ids $user_arr = ''; $zm_str = "SELECT userid FROM `".TABLE_USER."` WHERE dealer_code in (".$dealer_codes.") GROUP BY dealer_code "; $zm_arr = $parent->getQueryValue($zm_str); if(count($zm_arr)>0) { foreach ($zm_arr as $res_user) { $user_arr[] = $res_user->userid; } } $ins_arr = array(); foreach($user_arr as $res) { $ins_arr[] = " ('".$notificationid."', '".$res."', '0', '".NOW."')"; } $insert_chunk = array_chunk($ins_arr, 1000); foreach($insert_chunk as $chunk_ins) { //print "INSERT INTO ".TABLE_NOTIFICATION_USER."(`notificationid`, `userid`, `status`, `created_on`) VALUES ".@implode(',',$chunk_ins)." ON DUPLICATE KEY UPDATE `status` =0"; $parent->executeQuery("INSERT INTO ".TABLE_NOTIFICATION_USER."(`notificationid`, `userid`, `status`, `created_on`) VALUES ".@implode(',',$chunk_ins)." ON DUPLICATE KEY UPDATE `status` =0"); } $usertokenarr['ios']=array(); $usertokenarr['android']=array(); $user_device_str = "SELECT ud.devicetype,ud.token FROM `".TABLE_USER_DEVICES."` as ud JOIN (SELECT userid FROM ".TABLE_NOTIFICATION_USER." WHERE notificationid='".$notificationid."') as u ON ud.userid=u.userid WHERE ud.is_deleted='0' AND (devicetype='ios' OR devicetype='android') AND token!=''"; $user_device_arr = $parent->getQueryValue($user_device_str); if(count($user_device_arr) > 0) { foreach($user_device_arr as $res_dev) { //print_r($user_device_arr); if(strtolower($res_dev->devicetype) == 'ios') { $usertokenarr['ios'][] = $res_dev->token; } else if(strtolower($res_dev->devicetype) == 'android') { $usertokenarr['android'][] = $res_dev->token; } } // globalfunc::sendUserNotification($data,$usertokenarr); } } } } // Training Level Score private function traininglevelscore ($parent) { globalfunc::checkMethodType($parent,array('GET' )); globalfunc::checkAuthrozation($parent,array('type'=>'user')); globalfunc::checkParamCount($parent,2); $level = escape_str($parent->params[1]); $reschedule_str = "SELECT l.name as id, ln.name as name, l.percentage FROM ".TABLE_TRAINING_LEVEL_SCORE." l JOIN ".TABLE_TRAINING_LEVEL_SCORE_NAMES." ln ON ln.id = l.name WHERE l.level = '".$level."' AND l.status = 1 "; $reschedule_arr = $parent->getQueryValue($reschedule_str); if(count($reschedule_arr) > 0) { $parent->response(array('response'=>'success', 'data'=> $reschedule_arr, 'message'=>$this->page_data['success1']),200); } } private function updatetrainingscore ($parent) { globalfunc::checkMethodType($parent,array('POST' )); globalfunc::checkAuthrozation($parent,array('type'=>'user')); globalfunc::checkParamCount($parent,1); $REQUEST_KEYS = array('schedule_id'=>'number'); globalfunc::mandatoryKey($parent,$REQUEST_KEYS); $schedule_id = $parent->_request['schedule_id']; $score = $parent->_request['score']; $score_submit = $parent->_request['score_submit']; $score_array = json_decode($score, true); if(count($score_array) > 0) { foreach ($score_array as $score_key => $atten) { $userid = $atten['userid']; $roleplay_or_demo = $atten['roleplay_or_demo']; $assignment = $atten['assignment']; $performance = $atten['performance']; $certification = $atten['certification']; $overall = $atten['overall']; $remarks = $atten['remarks']; $update_qry = "UPDATE ".TABLE_TRAINING_PARTICIPANT ." SET `roleplay_or_demo`='".$roleplay_or_demo."', `assignment`='".$assignment."', `performance`='".$performance."', `overall`='".$overall."', `remarks`='" . $remarks . "', `certification`='" . $certification . "', `modified_on` = '".NOW."' WHERE schedule_id = '".$schedule_id."' AND userid = '" . $userid . "' "; $parent->executeQuery($update_qry); } } // Update Submit Status in Training Schedule if ($parent->_request['score_submit'] == 1 or $parent->_request['score_submit'] == 2) { $update_qry = "UPDATE ".TABLE_TRAINING_SCHEDULE ." SET `score_submit`='".$score_submit."', `modified_on` = '".NOW."' WHERE schedule_id = '".$schedule_id."' "; $parent->executeQuery($update_qry); if ($parent->_request['score_submit'] == 1) { // feedback Questions $query_str = " SELECT schedule_id, schedule_name, is_feedback, lvl_id FROM ".TABLE_TRAINING_SCHEDULE." WHERE is_deleted='0' AND schedule_id='".$schedule_id."' LIMIT 1"; $query_arr = $parent->getQueryValue($query_str); // Publish Score of Training Notification to AM, RM, ZM $mobj = new stdClass(); $mobj->schedule_id = $schedule_id; $mobj->type = 'score'; $this->trainingnotifictaion($parent, $mobj); // NOtifications to DSE, AM & Dealer about Score certified or failed //$query_str = " SELECT p.userid, p.certification FROM ".TABLE_TRAINING_PARTICIPANT." p JOIN ".TABLE_TRAINING_PARTICIPANT_ATTENDANCE." pa ON p.schedule_id = pa.schedule_id AND p.userid = pa.userid WHERE pa.ispresent = '1' AND p.schedule_id='".$schedule_id."' GROUP BY pa.userid "; $part_query_str = " SELECT p.userid, p.certification FROM ".TABLE_TRAINING_PARTICIPANT." p WHERE p.status in (0, 1) AND p.schedule_id='".$schedule_id."' "; $part_query_arr = $parent->getQueryValue($part_query_str); $cerified_users = $failed_users = ''; foreach ($part_query_arr as $key => $values) { if ($values->certification == 1) { $cerified_users .= $values->userid . ','; } else if ($values->certification == 2) { $failed_users .= $values->userid . ','; } } $cerified_users = trim($cerified_users, ','); $failed_users = trim($failed_users, ','); // Ceritication Notification if (!empty($cerified_users)) { $mobj = new stdClass(); $mobj->schedule_id = $schedule_id; $mobj->users = $cerified_users; $mobj->type = 'userscore_certified'; $this->trainingnotifictaion($parent, $mobj); } // Failerd Notifcations if (!empty($failed_users)) { $mobj = new stdClass(); $mobj->schedule_id = $schedule_id; $mobj->users = $failed_users; $mobj->type = 'userscore_fail'; $this->trainingnotifictaion($parent, $mobj); } // Feedback if ($query_arr[0]->is_feedback == 1) { // Checking Assessmengt Avaible or not $check_asse_str = " SELECT assessmentid FROM ".TABLE_ASSESSMENT." WHERE assettypeid='2' AND schedule_id='".$schedule_id."' LIMIT 1"; $check_asse_arr = $parent->getQueryValue($check_asse_str); if(count($check_asse_arr) == 0) { //INSERT ASSESSMENT $assessmentname = $query_arr[0]->schedule_name . " feedback"; $ques_count = 5; $assessmenttime = 60; $buffer_day = 2; $assessment_startdate=date('Y-m-d H:i:s'); //GET ASSESSMENT END DATE $assessment_enddate=date('Y-m-d H:i:s',strtotime('+'.$buffer_day.' day')); $insert_qry = "INSERT INTO ".TABLE_ASSESSMENT ." ( `atype`, `lvl_id`, `assessmentname`, `description`, `assessmentimage`, `assessmenttype`, `assettypeid`, `assessmenttimetype`, `assessmenttime`, `attemptslimit`, `publisheddate`, `enddate`, `randomcond`, `filter_role`, `filter_zone`, `filter_region`, `filter_dealer`, `assessmentnotification`, `total_question`,`schedule_id`, `status`, `creatorid`, `created_on`, `modified_on`) VALUES ('1','".$lvl_id."','".$assessmentname."','".$assessmentname."','','".$assessmenttype."','2','2','".$assessmenttime."','1','".$assessment_startdate."','".$assessment_enddate."','".$randomcond."','','','','','','".$ques_count."','".$schedule_id."','1','".$trainer_id."','".NOW."','".NOW."')"; $parent->executeQuery($insert_qry); $assessmentid = $parent->lastInsertId(); // Getting PArtcipants Array $participant_str = " SELECT DISTINCT( p.userid ) FROM ".TABLE_TRAINING_PARTICIPANT." p JOIN ".TABLE_TRAINING_PARTICIPANT_ATTENDANCE." pa ON p.schedule_id = pa.schedule_id AND p.userid = pa.userid WHERE p.status in (0,1) and pa.ispresent = 1 AND p.schedule_id='".$schedule_id."'"; $participant_arr = $parent->getQueryValue($participant_str); if(count($participant_arr)>0) { $ins_assess_arr=array(); foreach($participant_arr as $userid => $participants) { $ins_assess_arr[] =" ( '".$assessmentid."', '".$schedule_id."', '".$participants->userid."', '0')"; } $insert_survey_qry = "INSERT INTO ".TABLE_TRAINING_SURVEY ." ( `assessmentid`, `schedule_id`, `userid`, `is_attended`) VALUES ".@implode(',',$ins_assess_arr); } //ASSESSMENT PARTICPANT ADD $parent->executeQuery($insert_survey_qry); // Notification //if ($query) { // Training Notification $mobj = new stdClass(); $mobj->schedule_id = $schedule_id; $mobj->type = 'feedback'; $this->trainingnotifictaion($parent, $mobj); //} //ASSESSMENT QUESTION ADD $rcond = ($randomcond)?"ORDER BY RAND()":"ORDER BY id ASC"; $ques_str = "SELECT * FROM ".TABLE_TRAINING_FEEDBACK_QUESTION." WHERE status='1' "; $ques_arr=$parent->getQueryValue($ques_str); if(count($ques_arr)>0) { $ins_ques_arr=array(); foreach($ques_arr as $res_ques) { $ins_ques_arr[] =" ( '".$assessmentid."','".$res_ques->id."', '1', '".$trainer_id."','".NOW."', '".NOW."')"; } //ASSESSMENT QUESTION $insert_ques_qry = "INSERT INTO ".TABLE_ASSESSMENT_QUESTION ." (`assessmentid`, `qid`, `is_mandatory`, `creatorid`, `created_on`, `modified_on`) VALUES ".@implode(',',$ins_ques_arr); $query = $parent->executeQuery($insert_ques_qry); } } } } } $parent->response(array('response'=>'success', 'message'=>$this->page_data['success1']),200); } // AM Acknowledge to Training private function amacknowledgement ($parent) { globalfunc::checkMethodType($parent,array('POST' )); globalfunc::checkAuthrozation($parent,array('type'=>'user')); globalfunc::checkParamCount($parent,1); $REQUEST_KEYS = array('schedule_id'=>'number', 'absent_reason'=>'string'); globalfunc::mandatoryKey($parent,$REQUEST_KEYS); $schedule_id = $parent->_request['schedule_id']; $absent_reason = $parent->_request['absent_reason']; $am_acknowledgement = $parent->_request['am_acknowledgement']; $participant_absent_reason_array = json_decode($absent_reason, true); if(count($participant_absent_reason_array) > 0) { foreach ($participant_absent_reason_array as $absent_key => $atten) { $userid = $atten['userid']; $am_absence_reason = $atten['am_absence_reason']; $am_absence_reason_desc = $atten['am_absence_reason_desc']; $update_qry = "UPDATE ".TABLE_TRAINING_PARTICIPANT ." SET `am_absence_reason`='".$am_absence_reason."', `am_absence_reason_desc`='".$am_absence_reason_desc."', `modified_on` = '".NOW."' WHERE schedule_id = '".$schedule_id."' AND userid = '" . $userid . "' "; $parent->executeQuery($update_qry); } // Update AM Aknowledgment in Training Schedule // Get Acknowledge Details $query_str = " SELECT p.userid FROM ".TABLE_TRAINING_PARTICIPANT." p LEFT JOIN ".TABLE_TRAINING_PARTICIPANT_ATTENDANCE." pa ON p.schedule_id = pa.schedule_id AND p.userid = pa.userid WHERE pa.ispresent = '2' AND p.schedule_id='".$schedule_id."' AND p.am_absence_reason = '' "; $query_arr = $parent->getQueryValue($query_str); if (!empty($parent->_request['am_acknowledgement']) and count($query_arr) == 0 ) { $update_qry = "UPDATE ".TABLE_TRAINING_SCHEDULE ." SET `am_acknowledgement`='".$am_acknowledgement."', `modified_on` = '".NOW."' WHERE schedule_id = '".$schedule_id."' "; $parent->executeQuery($update_qry); } $parent->response(array('response'=>'success', 'message'=>$this->page_data['success1']),200); } else { $parent->response(array('response'=>'failed', 'message'=>$this->page_data['error11']), 406); } } // Training Summery private function trainingsummery ($parent) { globalfunc::checkMethodType($parent,array('GET' )); globalfunc::checkAuthrozation($parent,array('type'=>'user')); $schedule_id = escape_str($parent->params[1]); $schedule_str = "SELECT s.*, tl.lvl_name as title, CONCAT(u.fname, ' ', u.lname) as organizer_name FROM ".TABLE_TRAINING_SCHEDULE." s JOIN " . TABLE_TRAINING_LEVEL . " tl ON s.lvl_id = tl.lvl_id JOIN ".TABLE_USER." u ON s.trainer_id = u.userid WHERE s.schedule_id = '".$schedule_id."' LIMIT 1 "; $schedule_arr = $parent->getQueryValue($schedule_str); if(count($schedule_arr) > 0) { // Total Particpants $participant_str = "SELECT count(participant_id) as total_planned_participant FROM ".TABLE_TRAINING_PARTICIPANT." WHERE schedule_id='".$schedule_id."' "; $participant_arr = $parent->getQueryValue($participant_str); $schedule_arr[0]->total_planned_participant = $participant_arr[0]->total_planned_participant; // Attendance $participant_atten_str = "SELECT `day`, count(id) as total_participant_appeared FROM ".TABLE_TRAINING_PARTICIPANT_ATTENDANCE." WHERE schedule_id = '".$schedule_id."' AND ispresent = '1' group by day "; $participant_atten_arr = $parent->getQueryValue($participant_atten_str); foreach ($participant_atten_arr as $atten_key => $atten_details) { $atten_details->total_planned_participant = $participant_arr[0]->total_planned_participant; $atten_details->no_of_absents = $atten_details->total_planned_participant - $atten_details->total_participant_appeared; } $schedule_arr[0]->attendance = $participant_atten_arr; // Total Particpants Appeared $participant_appr_str = "SELECT id FROM ".TABLE_TRAINING_PARTICIPANT_ATTENDANCE." WHERE schedule_id = '".$schedule_id."' AND ispresent = '1' group by userid "; $participant_appr_arr = $parent->getQueryValue($participant_appr_str); $schedule_arr[0]->total_participant_appeared = count($participant_appr_arr); // Total Particpants Cerified $participant_str = "SELECT count(participant_id) as no_of_dse_certified FROM ".TABLE_TRAINING_PARTICIPANT." WHERE schedule_id='".$schedule_id."' AND certification = 1 "; $participant_arr = $parent->getQueryValue($participant_str); $schedule_arr[0]->no_of_dse_certified = $participant_arr[0]->no_of_dse_certified; // Total Particpants Failed $schedule_arr[0]->no_of_dse_failed = $schedule_arr[0]->total_participant_appeared - $schedule_arr[0]->no_of_dse_certified; // Batch through $score_percentage_str = "SELECT percentage FROM ".TABLE_TRAINING_LEVEL_SCORE." WHERE level = '" . $schedule_arr[0]->lvl_id. "' AND name = 1"; $score_percentage_arr = $parent->getQueryValue($score_percentage_str); $schedule_arr[0]->batch_through = $score_percentage_arr[0]->percentage; // Average Batch score $participant_avg_str = "SELECT AVG(overall) AS average_batch_score FROM ".TABLE_TRAINING_PARTICIPANT." WHERE schedule_id='".$schedule_id."' "; $participant_avg_arr = $parent->getQueryValue($participant_avg_str); $schedule_arr[0]->average_batch_score = round($participant_avg_arr[0]->average_batch_score); } $parent->response(array('response'=>'success', 'data'=> $schedule_arr[0], 'message'=>$this->page_data['success1']),200); } private function trainingattendancenotification ($parent) { globalfunc::checkMethodType($parent,array('POST' )); globalfunc::checkAuthrozation($parent,array('type'=>'user')); globalfunc::checkParamCount($parent,1); $REQUEST_KEYS = array('userid'=>'number', 'schedule_id'=>'number'); globalfunc::mandatoryKey($parent,$REQUEST_KEYS); $schedule_id = $parent->_request['schedule_id']; $userid = $parent->_request['userid']; $query_str = "SELECT s.schedule_name, s.start_date, s.end_date, tl.lvl_name as title FROM ".TABLE_TRAINING_SCHEDULE." s JOIN " . TABLE_TRAINING_LEVEL . " tl ON s.lvl_id = tl.lvl_id WHERE s.is_deleted='0' AND s.schedule_id='".$schedule_id."' LIMIT 1"; $query_arr = $parent->getQueryValue($query_str); $otp_expiry = globalfunc::generateExpiryTime($parent, ENQUIRY_OTP_EXPIRY_MIN); $otp = globalfunc::generateOTP(); // Update OTP $update_qry = " UPDATE ".TABLE_TRAINING_PARTICIPANT ." SET `present_otp`='".$otp."', `modified_on` = '".NOW."' WHERE schedule_id = '".$schedule_id."' AND userid = '" . $userid . "' "; $parent->executeQuery($update_qry); $notificationtitle = $query_arr[0]->title . ' - ' . $query_arr[0]->schedule_name; $notificationtext = "OTP for marking your attendance is " . $otp ; $notificationtype = 10; $nobj = new stdClass(); $nobj->schedule_id = $schedule_id; $notificationdata = json_encode($nobj); //INSERT NOTIFICATION $parent->executeQuery("INSERT INTO `".TABLE_NOTIFICATION."` ( `notificationtitle`, `notificationtext`, `notificationdata`, `notificationtype`, `status`, `publisheddate`, `created_on`, `modified_on`) VALUES ('".escape_str($notificationtitle)."','".escape_str($notificationtext)."','".$notificationdata."','".$notificationtype."', '1','".NOW."','".NOW."','".NOW."')"); $notificationid = $parent->lastInsertId(); $data = array('notification_id' => $notificationid, 'title'=>$notificationtitle, 'message'=>$notificationtext, 'notificationtype'=>$notificationtype,' notificationdata'=>$notificationdata, 'created_date'=>NOW); $parent->executeQuery("INSERT INTO ".TABLE_NOTIFICATION_USER. "(`notificationid`, `userid`, `status`, `created_on`) VALUES ('".$notificationid."', '".$userid."', '0', '".NOW."')" ); $usertokenarr['ios']=array(); $usertokenarr['android']=array(); $user_device_str = "SELECT ud.devicetype, ud.token FROM `".TABLE_USER_DEVICES."` as ud JOIN (SELECT userid FROM ".TABLE_NOTIFICATION_USER." WHERE notificationid='".$notificationid."') as u ON ud.userid=u.userid WHERE ud.is_deleted='0' AND (devicetype='ios' OR devicetype='android') AND token!=''"; $user_device_arr = $parent->getQueryValue($user_device_str); if(strtolower($user_device_arr[0]->devicetype) == 'ios') { $usertokenarr['ios'][] = $user_device_arr[0]->token; } else if(strtolower($user_device_arr[0]->devicetype) == 'android') { $usertokenarr['android'][] = $user_device_arr[0]->token; } // globalfunc::sendUserNotification($data, $usertokenarr); $parent->response(array('response'=>'success', 'message'=>$this->page_data['success1']),200); } private function trainingattendancenotificationupdate ($parent) { globalfunc::checkMethodType($parent,array('POST' )); globalfunc::checkAuthrozation($parent,array('type'=>'user')); globalfunc::checkParamCount($parent,1); $REQUEST_KEYS = array('userid'=>'number', 'schedule_id'=>'number', 'otp'=>'number'); globalfunc::mandatoryKey($parent,$REQUEST_KEYS); $schedule_id = $parent->_request['schedule_id']; $userid = $parent->_request['userid']; $otp = $parent->_request['otp']; $query_str = " SELECT participant_id FROM ".TABLE_TRAINING_PARTICIPANT." WHERE schedule_id='".$schedule_id."' AND userid='".$userid."' AND present_otp='".$otp."' LIMIT 1"; $query_arr = $parent->getQueryValue($query_str); if (count($query_arr) > 0) { $parent->response(array('response'=>'success', 'message'=>$this->page_data['success1']),200); } else { $parent->response(array('response'=>'failed', 'message'=>$this->page_data['error11']), 406); } } // QR Code Generation private function trainingqrcodegeneration ($parent) { require "qrcode/qrlib.php"; header('Content-type: image/png'); $qr_data = "testdata"; chmod($foldername, 0777); $errorCorrectionLevel = 'L'; $matrixPointSize = 4; print $filename = $foldername . $qr_data . '.png'; try { // QRcode::png('barrda554'); // //echo 'You can provide data in GET parameter: like that

---

'; //QRcode::png('PHP QR Code :)', $filename, $errorCorrectionLevel, $matrixPointSize, 2); QRcode::png($qr_data, $filename, $errorCorrectionLevel, $matrixPointSize, 2); //QRcode::png($qr_data, $filename, QR_ECLEVEL_L, 4); } catch(PDOException $e) { echo $e->getMessage(); } } // QR Code Generation private function trainingdseqrcode ($parent) { globalfunc::checkMethodType($parent,array('GET' )); globalfunc::checkAuthrozation($parent,array('type'=>'user')); globalfunc::checkParamCount($parent,2); //print_r($parent); //$schedule_id = escape_str($parent->params[1]); $dse = escape_str($parent->params[1]); $todaydate_time = date('Y-m-d'); $training_check_ques_str = "SELECT s.schedule_id, tl.lvl_name as title, s.start_date, s.end_date, s.starttime, s.endtime FROM ".TABLE_TRAINING_SCHEDULE." s INNER JOIN " . TABLE_TRAINING_LEVEL . " tl ON s.lvl_id = tl.lvl_id JOIN ".TABLE_TRAINING_PARTICIPANT ." p ON s.schedule_id = p.schedule_id JOIN " . TABLE_USER . " u ON p.userid = u.userid WHERE ('".$todaydate_time ."' between DATE_FORMAT(s.start_date,'%Y-%m-%d') AND DATE_FORMAT(s.end_date, '%Y-%m-%d') ) AND u.smpin = '".$dse."' "; $training_check_ques_arr = $parent->getQueryValue($training_check_ques_str); if(count($training_check_ques_arr) > 0) { //$link = $SITE_URL . "hypermupload/training/crtraining.png"; $todaydate = date('Y-m-d'); $datediff = strtotime($todaydate) - strtotime($training_check_ques_arr[0]->start_date); $currentday = round($datediff / (60 * 60 * 24)); $starttime_array = json_decode(stripslashes($training_check_ques_arr[0]->starttime), true); $endtime_array = json_decode(stripslashes($training_check_ques_arr[0]->endtime), true); $current_starttime = $starttime_array[$currentday]; $current_endtime = $endtime_array[$currentday]; $time1 = strtotime($current_starttime); $time2 = strtotime($current_endtime); $training_check_ques_arr[0]->duration = round(abs($time2 - $time1) / 3600,2); $s3 = awsfileserver::connectS3(); $training_check_ques_arr[0]->link = awsfileserver::getS3FileUrl($s3,S3_BUCKET,S3_TRAINING_URL.'crtraining.png'); $training_check_ques_arr[0]->training_date = date('d/m/Y'); //$parent->response(array('response'=>'success', 'data'=> $link, 'message'=>$this->page_data['success1']),200); $parent->response(array('response'=>'success', 'data'=> $training_check_ques_arr[0], 'message'=>$this->page_data['success1']),200); } else { $parent->response(array('response'=>'success', 'message'=>$this->page_data['success1']),200); } } // DSE Score and Result private function dsescoreresultlist ($parent) { globalfunc::checkMethodType($parent,array('GET')); globalfunc::checkParamCount($parent,1); globalfunc::checkAuthrozation($parent,array('type'=>'user')); $schedule_str = "SELECT s.schedule_id, s.lvl_id, s.start_date, s.end_date, s.venue_type, s.venue_am, s.venue_dealer, s.venue_address, tl.lvl_name as title, s.starttime, s.endtime, s.training_id, p.`roleplay_or_demo`, p.`assignment`, p.`performance`, p.`certification`, p.overall FROM ".TABLE_TRAINING_SCHEDULE." s INNER JOIN " . TABLE_TRAINING_LEVEL . " tl ON s.lvl_id = tl.lvl_id INNER JOIN " . TABLE_TRAINING_PARTICIPANT . " p ON s.schedule_id = p.schedule_id WHERE p.userid = '".$parent->current_user_id."' "; $schedule_arr = $parent->getQueryValue($schedule_str); foreach ($schedule_arr as $key => $schedules) { $score_percentage_str = "SELECT id, name, percentage FROM ".TABLE_TRAINING_LEVEL_SCORE." WHERE level = '" . $schedules->lvl_id. "'"; $score_percentage_arr = $parent->getQueryValue($score_percentage_str); foreach ($score_percentage_arr as $percentage_key => $percentages) { if (strtolower($percentages->name) == '2') { $pre_test_percentage = $percentages->percentage; } elseif (strtolower($percentages->name) == '3') { $post_test_percentage = $percentages->percentage; } } // Getting Pre and Post Assessment Scores $schedule_arr[$key]->pre_test = $schedule_arr[$key]->post_test = $schedule_arr[$key]->pre_test_percentage = $schedule_arr[$key]->post_test_percentage = 0; $assessment_str = "SELECT a.`assessmenttype`, au.`highscore` FROM ".TABLE_ASSESSMENT_USER." au JOIN ".TABLE_ASSESSMENT." a ON a.assessmentid = au.assessmentid WHERE a.schedule_id = '".$schedules->schedule_id."' AND au.userid = '" . $parent->current_user_id . "' AND au.highscorearid != 0 "; $assessment_arr = $parent->getQueryValue($assessment_str); if(count($assessment_arr) > 0) { foreach ($assessment_arr as $assessment_key => $assessments) { if ($assessments->assessmenttype == 1) { $schedule_arr[$key]->pre_test = $assessments->highscore; $schedule_arr[$key]->pre_test_percentage = ($schedule_arr[$key]->pre_test * $pre_test_percentage) / 100; } elseif ($assessments->assessmenttype == 2) { $schedule_arr[$key]->post_test = $assessments->highscore; $schedule_arr[$key]->post_test_percentage = ($schedule_arr[$key]->post_test * $post_test_percentage) / 100; } } } } $parent->response(array('response'=>'success', 'data'=>$schedule_arr,'message'=>$this->page_data['success1']),200); } // DSE FeedBack List private function dsefeedbacklist ($parent) { globalfunc::checkMethodType($parent,array('GET')); globalfunc::checkParamCount($parent,1); globalfunc::checkAuthrozation($parent,array('type'=>'user')); //USER COND // $user_cond_arr=projectfunc::getUserAssessmentCond($parent); // $user_cond=" AND ".@implode(' AND ',$user_cond_arr); // $status_arr=array(); // //NEW ROLE CONDITION // $user_cond.=($parent->current_user_role>7 && $parent->current_user_role<14)?" AND a.user_type=0 ":" AND a.user_type=1 "; // if($parent->current_user_role>7 && $parent->current_user_role<14) { // $user_cond.=" AND (a.segment='0' OR a.segment='".$parent->current_user_data->segment."')"; // } //print $assessment_str = "SELECT a.* FROM ".TABLE_ASSESSMENT." as a WHERE a.status='1' AND a.assettypeid='2' AND schedule_id !='' AND a.publisheddate<='".NOW."' ".$user_cond; $assessment_str = "SELECT CONCAT(u.fname, ' ', u.lname) as organizer_name, a.assessmentid, a.filter_zone, a.filter_region, a.filter_dealer, a.status, a.schedule_id, s.training_group_image, s.business_segment, s.training_group_image, s.lvl_id, s.start_date, s.end_date, s.venue_type, s.venue_am, s.venue_dealer, s.venue_address, tl.lvl_name as title, s.starttime, s.endtime, s.training_id, d.dealer_code, d.am_name, d.am_email FROM ".TABLE_ASSESSMENT." as a JOIN ".TABLE_TRAINING_SCHEDULE." s ON a.schedule_id = s.schedule_id JOIN ".TABLE_USER." u ON s.trainer_id = u.userid LEFT JOIN ".DMS_DEALER." d ON d.dealer_code = u.dealer_code JOIN " . TABLE_TRAINING_LEVEL . " tl ON s.lvl_id = tl.lvl_id JOIN ". TABLE_TRAINING_SURVEY ." ts ON ts.assessmentid = a.assessmentid and ts.userid = '".$parent->current_user_id."' WHERE a.status='1' AND a.assettypeid='2' AND a.schedule_id !='' "; //$assessment_str = "SELECT s.*, a.assessmentid FROM ".TABLE_ASSESSMENT." as a JOIN ". TABLE_TRAINING_SCHEDULE ." s WHERE a.status='1' AND a.assettypeid='2' AND a.schedule_id !='' ".$user_cond; $assessment_arr=$parent->getQueryValue($assessment_str); if(count($assessment_arr)>0) { /* //GET ALL STATUS $status_str = "SELECT `statusid`, `status` FROM ".TABLE_STATUS; $status_res_arr=$parent->getQueryValue($status_str); if(count($status_res_arr)>0) { foreach($status_res_arr as $res) { $obj_key=$res->statusid; $status_arr[$obj_key]=$res->status; } } //S3 OBJECT $s3=awsfileserver::connectS3();*/ //ADD EXTRA FIELDS foreach($assessment_arr as $key=>$res_assessment) { // Business Segment Name if ($res_assessment->business_segment == 2) { $res_assessment->segment_name = 'Parents/Guardian'; } else if ($res_assessment->business_segment == 1) { $res_assessment->segment_name = 'Instructor'; } else if ($res_assessment->business_segment == 3) { $res_assessment->segment_name = 'Student Learner'; } if($res_assessment->training_group_image != '') { //CONNECT S3 $s3 = awsfileserver::connectS3(); $res_assessment->training_group_image = awsfileserver::getS3FileUrl($s3,S3_BUCKET,S3_TRAINING_URL.$res_assessment->training_group_image); }else { $res_assessment->training_group_image = USER_NOIMAGE_URL; } $datediff = strtotime($res_assessment->enddate) - strtotime($res_assessment->startdate); $assessment_arr[$key]->training_days = round($datediff / (60 * 60 * 24)) + 1; // Participants count $participant_str = "SELECT count(participant_id) as total_participants FROM ".TABLE_TRAINING_PARTICIPANT." WHERE schedule_id='".$res_assessment->schedule_id."' AND status in (0,1) "; $participant_arr = $parent->getQueryValue($participant_str); $assessment_arr[$key]->total_participants = $participant_arr[0]->total_participants; //SURVEY IMAGE /* $assessment_arr[$key]->assessmentimage_url=SURVEYIMAGE_URL; if($res_assessment->assessmentimage!='') { $assessment_arr[$key]->assessmentimage_url=awsfileserver::getS3FileUrl($s3,S3_BUCKET,S3_ASSESSMENT_URL.$res_assessment->assessmentimage); }*/ $assessment_arr[$key]->is_expired ='0'; $assessment_arr[$key]->is_completed ='0'; //CHECK ALREADY ATTENDTED $assessment_result_str = "SELECT remarks FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid='".$res_assessment->assessmentid."' AND userid='".$parent->current_user_id."' ORDER BY arid DESC"; $assessment_result_arr=$parent->getQueryValue($assessment_result_str); if(count($assessment_result_arr)>0) { $assessment_arr[$key]->is_completed ='1'; // 3-Completed , 4 - In Progress // $assessment_arr[$key]->status =($assessment_result_arr[0]->status=='1')?'3':'1'; // $res_assessment->status = $assessment_arr[$key]->status; } else { // $assessment_arr[$key]->status ='7'; // $res_assessment->status = $assessment_arr[$key]->status; } $res_assessment->remarks = $assessment_result_arr[0]->remarks; //EXPIRED if(strtotime($res_assessment->enddate)is_expired='1'; $assessment_arr[$key]->status = '8'; $res_assessment->status = '8'; } //STATUS STRING // $assessment_arr[$key]->statusname=(isset($status_arr[$res_assessment->status]))?$status_arr[$res_assessment->status]:""; // //ATTEMPT TIME // $attempt_time_str = "SELECT * FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid='".$res_assessment->assessmentid."' AND userid='".$parent->current_user_id."' ORDER by attempt_time DESC"; // $attempt_time_arr=$parent->getQueryValue($attempt_time_str); // $assessment_arr[$key]->attempt_time=(count($attempt_time_arr)>0)?$attempt_time_arr[0]->attempt_time:0; // //IS ATTENDED // $assessment_arr[$key]->is_attended='0'; // $assessment_arr[$key]->result_id=''; //HIGHSCORE // $assessment_arr[$key]->highscore='0'; // if(count($attempt_time_arr)>0) { // $user_assessment_score_arr=array_column($attempt_time_arr,'percentage_score'); // rsort( $user_assessment_score_arr); // $assessment_arr[$key]->highscore=(string)$user_assessment_score_arr[0]; // $assessment_arr[$key]->is_attended='1'; // //FOR SURVEY RESULT VIEW // $assessment_arr[$key]->result_id=$attempt_time_arr[0]->arid; // } $assessment_arr[$key]->training_feedback = $assessment_arr[$key]->trainer_feedback = $assessment_arr[$key]->overall_feedback = 0; // Survey Training feedback $survey_feedback_str =" SELECT count(userresultid) as question_count, SUM(r.choiseid) as question_sum FROM ".TABLE_ASSESSMENT_USERRESULT." r JOIN ". TABLE_TRAINING_FEEDBACK_QUESTION ." q ON q.id = r.qid WHERE r.assessmentid='".$res_assessment->assessmentid."' AND q.questiontype = 1 AND userid='".$parent->current_user_id."' "; $survey_feedback_arr=$parent->getQueryValue($survey_feedback_str); if (count($survey_feedback_arr) > 0 AND $survey_feedback_arr[0]->question_count > 0) { $training_feedback = round($survey_feedback_arr[0]->question_sum / $survey_feedback_arr[0]->question_count, 1); $assessment_arr[$key]->training_feedback = $training_feedback . '/' . '5'; } // Survey Trainer feedback $survey_feedback_str =" SELECT count(userresultid) as question_count, SUM(r.choiseid) as question_sum FROM ".TABLE_ASSESSMENT_USERRESULT." r JOIN ". TABLE_TRAINING_FEEDBACK_QUESTION ." q ON q.id = r.qid WHERE r.assessmentid='".$res_assessment->assessmentid."' AND q.questiontype = 2 AND userid='".$parent->current_user_id."' "; $survey_feedback_arr=$parent->getQueryValue($survey_feedback_str); if (count($survey_feedback_arr) > 0 AND $survey_feedback_arr[0]->question_count > 0) { $trainer_feedback = round($survey_feedback_arr[0]->question_sum / $survey_feedback_arr[0]->question_count, 1); $assessment_arr[$key]->trainer_feedback = $trainer_feedback . '/' . '5'; } // Overrall Feedback if (!empty($training_feedback) and !empty($trainer_feedback)) { $overall_feedback = round( ($training_feedback + $trainer_feedback) / 2); $assessment_arr[$key]->overall_feedback = $overall_feedback . '/' . '5'; } } } //LOG $logstr='SURVEY LIST'; globalfunc::insertUserLog($parent,$logstr); $parent->response(array('response'=>'success', 'data'=>$assessment_arr,'message'=>$this->page_data['success1']),200); } // Feedback Question private function trainingfeedbackquestion ($parent) { globalfunc::checkMethodType($parent,array('GET')); globalfunc::checkParamCount($parent,2); globalfunc::checkAuthrozation($parent,array('type'=>'user')); $assessmentid = escape_str($parent->params['1']); //VALIDATE ASSESSMENT projectfunc::userSurveyValid($parent,$parent->current_user_id,$assessmentid); //ASSESSMENT VALIDATION /*$assessment_str = "SELECT * FROM ".TABLE_ASSESSMENT." WHERE assessmentid='".$assessmentid."' AND status='1' AND assettypeid='2' LIMIT 1"; $assessment_arr=$parent->getQueryValue($assessment_str); if(count($assessment_arr)!=1) $parent->response(array('response'=>'failed','message'=>$this->page_data["error2"]),406); //S3 OBJECT $s3=awsfileserver::connectS3(); if($assessment_arr[0]->randomcond=='1' ) { $econd=" ORDER BY RAND() "; }else { $econd=" ORDER BY aq.aqid ASC"; }*/ //GET RATINGS IS COMMON $survey_str = "SELECT id, score options FROM ".TABLE_SURVEY_RATING." ORDER BY id ASC"; $survey_arr=$parent->getQueryValue($survey_str); // Training feedback question type ids array //$limit_str=" LIMIT 0,".$assessment_arr[0]->total_question." "; $ques_str ="SELECT aq.aqid ,q.id as qid, q.`questiontype`, if(q.`questiontype`=1, 'training', 'trainer') as questiontype_name, q.`questiontext` FROM ".TABLE_TRAINING_FEEDBACK_QUESTION." as q JOIN (SELECT * FROM ".TABLE_ASSESSMENT_QUESTION." WHERE assessmentid='".$assessmentid."' )as aq ON q.id=aq.qid "; $ques_arr=$parent->getQueryValue($ques_str); if(count($ques_arr)>0) { //CONNECT S3 $s3=awsfileserver::connectS3(); $this->rating_rid=1; $data->training_feedback_score = $data->trainer_feedback_score = 0; $training_question_count = $trainer_question_count = 0; foreach($ques_arr as $key=>$res_ques) { $res_ques->rating_options=array(); // Question Answer Result $feedback_question_str =" SELECT choiseid FROM ".TABLE_ASSESSMENT_USERRESULT." WHERE assessmentid='".$assessmentid."' AND userid='".$parent->current_user_id."' AND qid = ".$res_ques->qid." "; $feedback_question_arr=$parent->getQueryValue($feedback_question_str); if (count($feedback_question_arr) > 0 AND $feedback_question_arr[0]->choiseid > 0 ) { $res_ques->question_ans = $feedback_question_arr[0]->choiseid; if ($res_ques->questiontype == 1) { $training_question_count++; $data->training_feedback_score += $feedback_question_arr[0]->choiseid; } else if ($res_ques->questiontype == 2) { $trainer_question_count++; $data->trainer_feedback_score += $feedback_question_arr[0]->choiseid; } } else { $res_ques->rating_options=$survey_arr; } $question_arr[]=$res_ques; } //print $data->training_feedback_score .' --' . $training_question_count; if($data->training_feedback_score > 0 and $training_question_count > 0) { $data->training_feedback_score_avg = round( ($data->training_feedback_score / $training_question_count ), 1); } if($data->trainer_feedback_score > 0 and $trainer_question_count > 0) { $data->trainer_feedback_score_avg = round( ($data->trainer_feedback_score / $trainer_question_count ), 1); } // Getting Remarks $data->remarks = ''; $get_userresult_str="SELECT remarks FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid='".$assessmentid."' AND userid='".$parent->current_user_id."' ORDER BY arid DESC"; $userresult_arr=$parent->getQueryValue($get_userresult_str); if ( count($userresult_arr) > 0) { $data->remarks = $userresult_arr[0]->remarks; } $data->questions = $question_arr; } //LOG $logstr='TRAINING FEEDBACK QUESTION'; globalfunc::insertUserLog($parent,$logstr,$assessmentid); $parent->response(array('response'=>'success', 'data'=>$data,'message'=>$this->page_data['success1']),200); } // Trainer Feedback List private function trainingsfeedbacklist ($parent) { globalfunc::checkMethodType($parent,array('GET')); globalfunc::checkParamCount($parent,1); globalfunc::checkAuthrozation($parent,array('type'=>'user')); $current_role = $parent->current_user_data->role; switch ($current_role) { case "3": $filter_cond = ""; break; case "4": // Zone Manager $dealer_cond = " JOIN " . DMS_DEALER . " d ON u.dealer_code = d.dealer_code "; $filter_cond = " AND d.zm_email = '".$parent->current_user_data->emailid."'"; break; case "5": $filter_cond = " AND s.trainer_id='".$parent->current_user_id."'"; break; case "6": // Reginal Manager $dealer_cond = " JOIN " . DMS_DEALER . " d ON u.dealer_code = d.dealer_code "; $filter_cond = " AND d.rm_email = '".$parent->current_user_data->emailid."'"; break; case "7": // Area Manager $dealer_cond = " JOIN " . DMS_DEALER . " d ON u.dealer_code = d.dealer_code "; $filter_cond = " AND d.am_email = '".$parent->current_user_data->emailid."'"; break; case "14": // Dealer $filter_cond = " AND u.dealer_code='".$parent->current_user_data->dealer_code."'"; break; default : $filter_cond = " "; break; } //print $assessment_str = "SELECT a.* FROM ".TABLE_ASSESSMENT." as a WHERE a.status='1' AND a.assettypeid='2' AND schedule_id !='' AND a.publisheddate<='".NOW."' ".$user_cond; $assessment_str = "SELECT CONCAT(u.fname, ' ', u.lname) as organizer_name, a.assessmentid, a.filter_zone, a.filter_region, a.filter_dealer, a.status, a.schedule_id, s.business_segment, s.training_group_image, s.lvl_id, s.start_date, s.end_date, s.venue_type, s.venue_am, s.venue_dealer, s.venue_address, tl.lvl_name as title, s.starttime, s.endtime, s.training_id FROM ".TABLE_ASSESSMENT." as a JOIN ".TABLE_TRAINING_SCHEDULE." s ON a.schedule_id = s.schedule_id JOIN ".TABLE_USER." u ON s.trainer_id = u.userid " . $dealer_cond . " JOIN " . TABLE_TRAINING_LEVEL . " tl ON s.lvl_id = tl.lvl_id WHERE a.status='1' AND a.assettypeid='2' AND a.schedule_id !='' " . $filter_cond . " "; $assessment_arr=$parent->getQueryValue($assessment_str); if(count($assessment_arr)>0) { //GET ALL STATUS $status_str = "SELECT `statusid`, `status` FROM ".TABLE_STATUS; $status_res_arr=$parent->getQueryValue($status_str); // ADD EXTRA FIELDS foreach($assessment_arr as $key=>$res_assessment) { // Business Segment Name if ($res_assessment->business_segment == 2) { $res_assessment->segment_name = 'Parents/Guardian'; } else if ($res_assessment->business_segment == 1) { $res_assessment->segment_name = 'Instructor'; } else if ($res_assessment->business_segment == 3) { $res_assessment->segment_name = 'Student Learner'; } if($res_assessment->training_group_image != '') { //CONNECT S3 $s3 = awsfileserver::connectS3(); $res_assessment->training_group_image = awsfileserver::getS3FileUrl($s3,S3_BUCKET,S3_TRAINING_URL.$res_assessment->training_group_image); }else { $res_assessment->training_group_image = USER_NOIMAGE_URL; } $datediff = strtotime($res_assessment->enddate) - strtotime($res_assessment->startdate); $assessment_arr[$key]->training_days = round($datediff / (60 * 60 * 24)) + 1; // Participants count $participant_str = "SELECT count(id) as total_participants FROM ".TABLE_TRAINING_SURVEY." WHERE schedule_id='".$res_assessment->schedule_id."' AND assessmentid ='".$res_assessment->assessmentid."' "; $participant_arr = $parent->getQueryValue($participant_str); $assessment_arr[$key]->total_participants = $participant_arr[0]->total_participants; $assessment_arr[$key]->training_feedback = $assessment_arr[$key]->trainer_feedback = $assessment_arr[$key]->overall_feedback = 0; // Survey Training feedback $survey_feedback_str =" SELECT avg(r.choiseid) as choiseid FROM ".TABLE_ASSESSMENT_USERRESULT." r JOIN ". TABLE_TRAINING_FEEDBACK_QUESTION ." q ON q.id = r.qid WHERE r.assessmentid='".$res_assessment->assessmentid."' AND q.questiontype = 1 "; $survey_feedback_arr=$parent->getQueryValue($survey_feedback_str); if (count($survey_feedback_arr) > 0 AND $survey_feedback_arr[0]->choiseid > 0) { $training_feedback = round($survey_feedback_arr[0]->choiseid, 1); $assessment_arr[$key]->training_feedback = $training_feedback . '/' . '5'; } // Survey Trainer feedback $survey_trainer_str =" SELECT avg(r.choiseid) as choiseid FROM ".TABLE_ASSESSMENT_USERRESULT." r JOIN ". TABLE_TRAINING_FEEDBACK_QUESTION ." q ON q.id = r.qid WHERE r.assessmentid='".$res_assessment->assessmentid."' AND q.questiontype = 2 "; $survey_trainer_arr=$parent->getQueryValue($survey_trainer_str); if (count($survey_trainer_arr) > 0 AND $survey_trainer_arr[0]->choiseid > 0) { $trainer_feedback = round($survey_trainer_arr[0]->choiseid, 1); $assessment_arr[$key]->trainer_feedback = $trainer_feedback . '/' . '5'; } /*$survey_feedback_str =" SELECT count(userresultid) as question_count, SUM(r.choiseid) as question_sum FROM ".TABLE_ASSESSMENT_USERRESULT." r JOIN ". TABLE_TRAINING_FEEDBACK_QUESTION ." q ON q.id = r.qid WHERE r.assessmentid='".$res_assessment->assessmentid."' AND q.questiontype = 1 AND userid in (SELECT userid FROM ".TABLE_TRAINING_SURVEY." WHERE schedule_id='".$res_assessment->schedule_id."' AND assessmentid ='".$res_assessment->assessmentid."') "; $survey_feedback_arr=$parent->getQueryValue($survey_feedback_str); if (count($survey_feedback_arr) > 0 AND $survey_feedback_arr[0]->question_count > 0) { $training_feedback = round( ($survey_feedback_arr[0]->question_sum / $survey_feedback_arr[0]->question_count) / $assessment_arr[$key]->total_participants , 1); $assessment_arr[$key]->training_feedback = $training_feedback . '/' . '5'; } //Survey Trainer feedback $survey_feedback_str =" SELECT count(userresultid) as question_count, SUM(r.choiseid) as question_sum FROM ".TABLE_ASSESSMENT_USERRESULT." r JOIN ". TABLE_TRAINING_FEEDBACK_QUESTION ." q ON q.id = r.qid WHERE r.assessmentid='".$res_assessment->assessmentid."' AND q.questiontype = 2 AND userid in (SELECT userid FROM ".TABLE_TRAINING_SURVEY." WHERE schedule_id='".$res_assessment->schedule_id."' AND assessmentid ='".$res_assessment->assessmentid."') "; $survey_feedback_arr=$parent->getQueryValue($survey_feedback_str); if (count($survey_feedback_arr) > 0 AND $survey_feedback_arr[0]->question_count > 0) { $trainer_feedback = round( ($survey_feedback_arr[0]->question_sum / $survey_feedback_arr[0]->question_count) / $assessment_arr[$key]->total_participants, 1); $assessment_arr[$key]->trainer_feedback = $trainer_feedback . '/' . '5'; }*/ // Overrall Feedback if (!empty($training_feedback) and !empty($trainer_feedback)) { $overall_feedback = round( ($training_feedback + $trainer_feedback) / 2); $assessment_arr[$key]->overall_feedback = $overall_feedback . '/' . '5'; } } } //LOG $logstr='TRAINER FEEDBACK LIST'; globalfunc::insertUserLog($parent,$logstr); $parent->response(array('response'=>'success', 'data'=>$assessment_arr,'message'=>$this->page_data['success1']),200); } // Feedback Question private function trainingalluserfeedbackquestion ($parent) { globalfunc::checkMethodType($parent,array('GET')); globalfunc::checkParamCount($parent,2); globalfunc::checkAuthrozation($parent,array('type'=>'user')); $assessmentid = escape_str($parent->params['1']); //GET RATINGS IS COMMON $survey_str = "SELECT id, score options FROM ".TABLE_SURVEY_RATING." ORDER BY id ASC"; $survey_arr=$parent->getQueryValue($survey_str); // Training feedback users // $get_userresult_str = "SELECT CONCAT(u.fname, ' ', u.lname) as dse_name, ar.assessmentid, ar.userid, ar.remarks FROM ".TABLE_ASSESSMENTRESULT." ar JOIN ".TABLE_USER." u ON ar.userid = u.userid WHERE ar.assessmentid='".$assessmentid."' "; // $userresult_arr=$parent->getQueryValue($get_userresult_str); // if ( count($userresult_arr) > 0) { $ques_str ="SELECT DISTINCT(aq.aqid) , q.id as qid, q.`questiontype`, if(q.`questiontype`=1, 'training', 'trainer') as questiontype_name, q.`questiontext` FROM ".TABLE_TRAINING_FEEDBACK_QUESTION." as q JOIN (SELECT * FROM ".TABLE_ASSESSMENT_QUESTION." WHERE assessmentid='".$assessmentid."' )as aq ON q.id=aq.qid "; $ques_arr=$parent->getQueryValue($ques_str); // foreach ($userresult_arr as $user_assess_key => $userresult) { $question_arr = ''; if(count($ques_arr)>0) { $userresult_arr->training_feedback_score = $userresult_arr->trainer_feedback_score = 0; $training_question_count = $trainer_question_count = 0; foreach($ques_arr as $key=>$res_ques) { // Question Answer Result //$feedback_question_str =" SELECT choiseid FROM ".TABLE_ASSESSMENT_USERRESULT." WHERE assessmentid='".$assessmentid."' AND userid='".$userresult->userid."' "; $feedback_question_str =" SELECT avg(choiseid) as choiseid FROM ".TABLE_ASSESSMENT_USERRESULT." WHERE assessmentid='".$assessmentid."' AND qid = '".$res_ques->qid."' "; $feedback_question_arr=$parent->getQueryValue($feedback_question_str); if (count($feedback_question_arr) > 0 AND $feedback_question_arr[0]->choiseid > 0 ) { $res_ques->question_ans = round($feedback_question_arr[0]->choiseid, 1); if ($res_ques->questiontype == 1) { $training_question_count++; $userresult_arr->training_feedback_score += $feedback_question_arr[0]->choiseid; } else if ($res_ques->questiontype == 2) { $trainer_question_count++; $userresult_arr->trainer_feedback_score += $feedback_question_arr[0]->choiseid; } } $question_arr[]=$res_ques; } $userresult_arr->training_feedback_score_avg = round( ($userresult_arr->training_feedback_score / $training_question_count ), 1); $userresult_arr->trainer_feedback_score_avg = round( ($userresult_arr->trainer_feedback_score / $trainer_question_count ), 1); $userresult_arr->questions = $question_arr; // Getting Remarks // $data->remarks = ''; // $get_userresult_str="SELECT remarks FROM ".TABLE_ASSESSMENTRESULT." WHERE assessmentid='".$assessmentid."' AND userid='".$parent->current_user_id."' ORDER BY arid DESC"; // $userresult_arr=$parent->getQueryValue($get_userresult_str); // if ( count($userresult_arr) > 0) { // $data->remarks = $userresult_arr[0]->remarks; // } } // } // } //LOG $logstr='TRAINING FEEDBACK QUESTION'; globalfunc::insertUserLog($parent,$logstr,$assessmentid); $parent->response(array('response'=>'success', 'data'=>$userresult_arr,'message'=>$this->page_data['success1']),200); } }