<?php

include "EncryptDecrypt.php";

$servername = "localhost";
$username = "devknestlms_dbuser";
$password = "Narahari1!";
$dbname = "devknestlms_multi";

//added for sendinblue
require_once('../vendor/autoload.php');

date_default_timezone_set('Asia/Kolkata');
$datetime = date("Y-m-d H:i:s");
$date = date("Y-m-d");

$batch_user_id = 0;

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
// Turn autocommit off
$conn->autocommit(FALSE);

try {
    // API url
    $url = 'https://dev.knestlms.com/ai/evaluate/';
    // API method
    $method = 'POST';
    // Log file
    $file_name = "api_logo.txt";

    $result = mysqli_query($conn, "SELECT ai.offering_id,ai.assessment_id,ai.user_id,ai.attempt_no,ai.question_id, qb.keywords,qb.layer,
			qb.weight,qba.avg_pct,qr.subjective_answer,aq.question_marks,
			GROUP_CONCAT(qo.option_desc) option_desc 
			FROM ai_batch as ai,question_bank as qb,question_bank_ai as qba,assessment_qp as aq,quiz_response as qr,question_options as qo 
			WHERE ai.status='pending' AND qb.question_id=ai.question_id AND qb.question_id=qba.question_id AND qb.question_id=aq.question_id 
			AND ai.offering_id=qr.offering_id AND ai.assessment_id=qr.assessment_id AND ai.user_id=qr.user_id AND ai.attempt_no=qr.attempt_no 
			AND ai.question_id=qr.question_id AND qb.question_id=qo.question_id 
			GROUP BY ai.offering_id,ai.assessment_id,ai.user_id,ai.attempt_no,ai.question_id, qb.keywords,qb.layer,qb.weight,qba.avg_pct,qr.subjective_answer,aq.question_marks");
    if (mysqli_num_rows($result) > 0) {
        $fp = fopen($file_name, "a");
        fwrite($fp, "Date:" . $datetime . "\n");
        $count = 0;

        while ($row = mysqli_fetch_array($result)) {

            $count = $count + 1;
            $keys = array();
            $splitted_keys = explode(",", $row['keywords']);
            foreach ($splitted_keys as $key) {
                $keys[] = $key;
            }
            // $samples = array();
            // $splitted_sample = explode(",", $row['option_desc']);
            // foreach ($splitted_sample as $sample) {
            // 	$samples[] = $sample;
            // }
            $answer = $row['subjective_answer'];
            // $layer = $row['layer'];
            $percentages = array();
            $splitted_percentage = explode(",", $row['weight']);
            foreach ($splitted_percentage as $percentage) {
                $percentage = round($percentage / 100, 1);
                $percentages[] = $percentage;
            }

            // Collection object
            $data = [
                'answer' => $answer,
                'keys' => $keys,
                'code' => $row['question_id'],
                'percentage' => $percentages
            ];

            // Initializes a new cURL session
            $curl = curl_init($url);
            // Set the CURLOPT_RETURNTRANSFER option to true
            curl_setopt($curl, CURLOPT_RETURNTRANSFER, true);
            // Set the CURLOPT_POST option to true for POST request
            curl_setopt($curl, CURLOPT_POST, true);
            // Set the request data as JSON using json_encode function
            curl_setopt($curl, CURLOPT_POSTFIELDS, json_encode($data));
            // Set custom headers for RapidAPI Auth and Content-Type header
            curl_setopt($curl, CURLOPT_HTTPHEADER, [
                'Content-Type: application/json'
            ]);
            // Execute cURL request with all previous settings
            $response = curl_exec($curl);
            // Close cURL session
            curl_close($curl);
            //echo $response . PHP_EOL;

            $question_marks = $row['question_marks'];
            $avg_pct = $row['avg_pct'];
            $ai_marks = $response;

            $actual_marks = ((float) $ai_marks / (float) $avg_pct) * $question_marks;
            $actual_marks = round($actual_marks, 2);

            if ($actual_marks > $question_marks) {
                $actual_marks = $question_marks;
            }

            $result1 = mysqli_query($conn, "INSERT INTO quiz_response_marks(user_id,offering_id,assessment_id,attempt_no,
					question_id,marks,created_user,created_ts,last_updated_user,last_updated_ts) 
					values(" . $row['user_id'] . "," . $row['offering_id'] . "," . $row['assessment_id'] . "," . $row['attempt_no'] . ",
					" . $row['question_id'] . "," . $actual_marks . "," . $batch_user_id . ",'" . $datetime . "'," . $batch_user_id . ",'" . $datetime . "') 
					ON DUPLICATE KEY UPDATE marks=" . $actual_marks . ",last_updated_user=" . $batch_user_id . ",last_updated_ts='" . $datetime . "'");
            if ($result1) {
                //check s_ad question type calculated or not
                $result0 = mysqli_query($conn, "SELECT qr.offering_id,qr.assessment_id,qr.user_id,qr.attempt_no,qr.question_id, null as shipped 
					FROM quiz_response qr 
					WHERE not exists(select 1 from quiz_response_marks qrm where qr.offering_id=qrm.offering_id AND qr.assessment_id=qrm.assessment_id 
					AND qr.user_id=qrm.user_id AND qr.attempt_no=qrm.attempt_no AND qr.question_id=qrm.question_id AND qrm.marks is not null) 
					AND qr.offering_id=" . $row['offering_id'] . " AND qr.assessment_id=" . $row['assessment_id'] . " 
					AND qr.user_id=" . $row['user_id'] . " AND qr.attempt_no=" . $row['attempt_no'] . "");
                if (mysqli_num_rows($result0) == 0) {

                    $overall_result = null;
                    $results = array();
                    $incomplete = 0;
                    $user_assessment_marks = 0;
                    $total_assessment_marks = 0;
                    $passing_marks = 0;
                    $user_total_assessment_marks = 0;
                    $tmp_score = 0;

                    //Use SUM function
                    $result2 = mysqli_query($conn, "SELECT aq.question_marks,qrm.marks 
					  FROM assessment_qp as aq,quiz_response_marks as qrm 
					  WHERE qrm.user_id=" . $row['user_id'] . " AND qrm.offering_id=" . $row['offering_id'] . " AND 
					  qrm.assessment_id=" . $row['assessment_id'] . " AND qrm.attempt_no=" . $row['attempt_no'] . " AND 
					  aq.question_id=qrm.question_id AND aq.assessment_id=qrm.assessment_id");
                    if (mysqli_num_rows($result2) > 0) {
                        while ($row2 = mysqli_fetch_array($result2)) {
                            $total_assessment_marks = $total_assessment_marks + $row2['question_marks'];
                            $user_assessment_marks = $user_assessment_marks + $row2['marks'];
                        }
                    }

                    $user_total_assessment_marks = ($user_assessment_marks * 100) / $total_assessment_marks;
                    $user_total_assessment_marks = round($user_total_assessment_marks);

                    //Update quiz_score
                    $result3 = mysqli_query($conn, "UPDATE quiz_score SET total_score=" . $total_assessment_marks . ",score=" . $user_total_assessment_marks . ",last_updated_ts='" . $datetime . "' 
					  WHERE offering_id=" . $row['offering_id'] . " AND user_id=" . $row['user_id'] . " AND 
					  attempt_no=" . $row['attempt_no'] . " AND assessment_id=" . $row['assessment_id'] . "");

                    //Code similar to cal_result.php
                    $passing_score = 0;
                    $max_attempts = 0;
                    $assessment_wtg = 0;
                    $end_date = "";
                    //First - Assessment
                    $result4 = mysqli_query($conn, "SELECT rc.weightage,rc.passing_score,co.max_attempts,co.end_date 
					FROM course_offering as co,quiz_score as rc 
					WHERE rc.offering_id=co.offering_id AND rc.offering_id=" . $row['offering_id'] . " AND rc.user_id=" . $row['user_id'] . " AND 
					rc.attempt_no=" . $row['attempt_no'] . " AND rc.assessment_id=" . $row['assessment_id'] . "");
                    if (mysqli_num_rows($result4) > 0) {
                        if ($row4 = mysqli_fetch_array($result4)) {
                            $passing_score = $row4['passing_score'];
                            $max_attempts = $row4['max_attempts'];
                            $assessment_wtg = $row4['weightage'];
                            $end_date = $row4['end_date'];
                        }
                    }
                    $tmp_score = $assessment_wtg * $user_total_assessment_marks;
                    if ($user_total_assessment_marks >= $passing_score) {
                        array_push($results, "passed");
                    } else {
                        array_push($results, "failed");
                        if ($row['attempt_no'] >= $max_attempts) {
                            $incomplete = 1;
                        }
                        if ($end_date < $date) {
                            $incomplete = 1;
                        }
                    }
                    //Second - Scorm
                    $result5 = mysqli_query($conn, "SELECT scorm_id,weightage FROM result_criteria 
					WHERE offering_id=" . $row['offering_id'] . " and scorm_id!=0");
                    if (mysqli_num_rows($result5) > 0) {
                        while ($row5 = mysqli_fetch_array($result5)) {

                            $result6 = mysqli_query($conn, "SELECT value FROM scorm_track WHERE scorm_id=" . $row5['scorm_id'] . " AND 
								offering_id=" . $row['offering_id'] . " AND user_id=" . $row['user_id'] . " AND model='cmi.core.lesson_status'");
                            if (mysqli_num_rows($result6) > 0) {
                                if ($row6 = mysqli_fetch_array($result6)) {
                                    array_push($results, $row6['value']);
                                }
                            } else {
                                array_push($results, "not attempted");
                            }

                            $result7 = mysqli_query($conn, "SELECT value FROM scorm_track WHERE scorm_id=" . $row5['scorm_id'] . " AND 
								offering_id=" . $row['offering_id'] . " AND user_id=" . $row['user_id'] . " AND model='cmi.core.score.raw'");
                            if (mysqli_num_rows($result7) > 0) {
                                if ($row7 = mysqli_fetch_array($result7)) {
                                    $tmp_score = $tmp_score + ($row5['weightage'] * $row7['value']);
                                }
                            }
                        }
                    }
			   //Third - xapi //new added
                    $result_xapi = mysqli_query($conn, "SELECT xapi_id,weightage FROM result_criteria 
					WHERE offering_id=" . $row['offering_id'] . " and xapi_id!=0");
                    if (mysqli_num_rows($result_xapi) > 0) {
                        while ($row_xapi = mysqli_fetch_array($result_xapi)) {
	                            $result6 = mysqli_query($conn, "SELECT status FROM xapi_track WHERE xapi_id=" . $row_xapi['xapi_id'] . " AND 
								offering_id=" . $row['offering_id'] . " AND user_id=" . $row['user_id'] . "");
                            if (mysqli_num_rows($result6) > 0) {
                                if ($row6 = mysqli_fetch_array($result6)) {
                                   if($row6['status'] == 'passed' || $row6['status'] == 'failed' || $row6['status'] == 'completed' || $row6['status'] == 'incomplete'){
                                        array_push($results, $row6['status']);
                                    }else{
                                        array_push($results, "not attempted");
                                    }                              
				  }
                            } else {
                                array_push($results, "not attempted");
                            }

                            $result7 = mysqli_query($conn, "SELECT actual_score FROM xapi_track WHERE xapi_id=" . $row_xapi['xapi_id'] . " AND 
								offering_id=" . $row['offering_id'] . " AND user_id=" . $row['user_id'] . "");
                            if (mysqli_num_rows($result7) > 0) {
                                if ($row7 = mysqli_fetch_array($result7)) {
				
                                    $tmp_score = $tmp_score + ($row_xapi['weightage'] * $row7['actual_score']);
	                                }
                            }
                        }
                    }
                        //LTI
                    $result_lti = mysqli_query($conn, "SELECT lti_id,weightage FROM result_criteria 
					WHERE offering_id=" . $row['offering_id'] . " and lti_id!=0");
                    if (mysqli_num_rows($result_lti) > 0) {
                        while ($row_lti = mysqli_fetch_array($result_lti)) {
	                            $result6 = mysqli_query($conn, "SELECT lug.raw_grade,g.grade_pass FROM lti_user_grades as lug,grade_items as g where lug.grade_items_id=g.grade_items_id AND 
								lug.offering_id=" . $row['offering_id'] . " AND lug.user_id=" . $row['user_id'] . " AND g.lti_id=".$row_lti['lti_id']."");
                            if (mysqli_num_rows($result6) > 0) {
                                if ($row6 = mysqli_fetch_array($result6)) {
                                   if($row6['raw_grade'] >= $row6['grade_pass'] ){
                                        array_push($results,  "completed");
                                    }else{
                                        array_push($results, "incomplete");
                                    }    
                                    $tmp_score = $tmp_score + ($row_lti['weightage'] * $row6['raw_grade']);
				  }
                            } else {
                                array_push($results, "not attempted");
                            }
                        }
                    }
                    //Third update result
                    $final_score = $tmp_score / 100;

                    if (in_array('not attempted', $results) || in_array('failed', $results) || in_array('incomplete', $results)) {
                        if ($incomplete == 1) {
                            $overall_result = "incomplete";
                            $result8 = mysqli_query($conn, "UPDATE course_assignment 
								SET attended_date='" . $datetime . "',
								result='" . $overall_result . "',
								score=" . $final_score . ",
								last_updated_user=" . $batch_user_id . ",
								last_updated_ts='" . $datetime . "' 
								WHERE offering_id=" . $row['offering_id'] . " AND user_id=" . $row['user_id'] . "");
                        } else {
                            $result8 = mysqli_query($conn, "UPDATE course_assignment 
								SET attended_date='" . $datetime . "',
								result=null,
								score=" . $final_score . ",
								last_updated_user=" . $batch_user_id . ",
								last_updated_ts='" . $datetime . "' 
								WHERE offering_id=" . $row['offering_id'] . " AND user_id=" . $row['user_id'] . "");
                        }
                    } else if (in_array('passed', $results) || in_array('completed', $results)) {
                        //Fourth - Calculate Completion Points
                        $completion_points = 'null';
                        $result9 = mysqli_query($conn, "SELECT Group_concat(completion_points) as points FROM certificate_assignment WHERE offering_id=" . $row['offering_id'] . "");
                        if (mysqli_num_rows($result9) > 0) {
                            $row9 = mysqli_fetch_array($result9);
                            if ($row9['points'] != null) {
                                $result10 = mysqli_query($conn, "SELECT completion_points FROM certificate_assignment 
									WHERE offering_id=" . $row['offering_id'] . " AND cut_off<=" . $final_score . " ORDER BY cut_off DESC LIMIT 1");
                                if (mysqli_num_rows($result10) > 0) {
                                    if ($row10 = mysqli_fetch_array($result10)) {
                                        $completion_points = $row10['completion_points'];
                                    }
                                } else {
                                    $result13 = mysqli_query($conn, "SELECT completion_points FROM certificate_assignment WHERE offering_id=" . $row['offering_id'] . " and cut_off is NULL");
                                    if (mysqli_num_rows($result13) > 0) {
                                        if ($row13 = mysqli_fetch_array($result13)) {
                                            $completion_points = $row13['completion_points'];
                                        }
                                    }
                                }
                            } else {
                                $result11 = mysqli_query($conn, "SELECT co.completion_points from course_offering as co where co.offering_id=" . $row['offering_id'] . "");
                                if (mysqli_num_rows($result11) > 0) {
                                    if ($row11 = mysqli_fetch_array($result11)) {
                                        $completion_points = $row11['completion_points'];
                                    }
                                }
                            }
                        } else {
                            $result11 = mysqli_query($conn, "SELECT co.completion_points from course_offering as co where co.offering_id=" . $row['offering_id'] . "");
                            if (mysqli_num_rows($result11) > 0) {
                                if ($row11 = mysqli_fetch_array($result11)) {
                                    $completion_points = $row11['completion_points'];
                                }
                            }
                        }
                        if ($completion_points == "") {
                            $completion_points = 'null';
                        }

                        $overall_result = "complete";
                        $result12 = mysqli_query($conn, "UPDATE course_assignment 
						  SET attended_date='" . $datetime . "',
							  result='" . $overall_result . "',
							  score=" . $final_score . ",
							  completion_points =" . $completion_points . ",
							  last_updated_user=" . $batch_user_id . ",
							  last_updated_ts='" . $datetime . "' 
						  WHERE offering_id=" . $row['offering_id'] . " AND user_id=" . $row['user_id'] . "");
                        if ($result12) {
                            $result14 = mysqli_query($conn, "SELECT SUM(completion_points) as count 
								FROM course_assignment WHERE result='complete' and user_id=" . $row['user_id'] . " AND status IS NULL");
                            if (mysqli_num_rows($result14) > 0) {
                                $row14 = mysqli_fetch_array($result14);
                                mysqli_query($conn, "UPDATE user_account SET total_completion_points=" . $row14['count'] . ",
									last_updated_user=" . $batch_user_id . ",last_updated_ts='" . $datetime . "'
									WHERE user_id=" . $row['user_id'] . "");
                            }
                        }
                    }

                    mysqli_query($conn, "UPDATE ai_batch SET status='completed',last_updated_user=" . $batch_user_id . ",
						last_updated_ts='" . $datetime . "' WHERE user_id=" . $row['user_id'] . " AND 
						offering_id=" . $row['offering_id'] . " AND  assessment_id=" . $row['assessment_id'] . " AND 
						attempt_no=" . $row['attempt_no'] . " AND question_id=" . $row['question_id'] . "");

                    //Send SMS Email
                    $mcrypt = new MCrypt();
                    $link_value = $row['user_id'] . '&' . $row['offering_id'] . '&' . $row['attempt_no'];
                    $encrypted_link_value = $mcrypt->encrypt($link_value);
                    $result15 = mysqli_query($conn, "SELECT email_id,mobile_no,first_name,last_name,user_id  
					FROM user_account WHERE user_id=" . $row['user_id'] . "");
                    if (mysqli_num_rows($result15) > 0) {
                        $row15 = mysqli_fetch_array($result15);
                        $mobile_no = $row15["mobile_no"];
                        $email_id = $row15["email_id"];
                        $first_name = $row15["first_name"];
                        if ($row15["mobile_no"] != null || $mobile_no != "") {
                            $msgbody = urlencode("Dear " . $first_name . ", Assessment result has been published. Please login to K-nest LMS to verify the details. - 4EdgeIT Solutions");
//                            file_get_contents('http://trans.kapsystem.com/api/v3/index.php?method=sms&api_key=A7cca971e58ac774dd98d2830c7aea569&to=' . $mobile_no . '&sender=EDGEIT&message=' . $msgbody);
                            file_get_contents('http://www.smsjust.com/blank/sms/user/urlsms.php?username=haniballal01&pass=user@123&senderid=EDGEIT&dest_mobileno=' . $mobile_no . '&message=' . $msgbody.'&dltentityid=1201160450888723221&dlttempid=1607100000000223933&response=Y');
                        }

                        /* $to = $email_id;
                          $subject = "K-nest LMS";
                          $message = "Dear " . $first_name . ",<br/><br/>
                          Assessment result has been published. Please click on below link to view the result.<br/>
                          https://learn.knestlms.com/report.php?user=".$encrypted_link_value." <br/><br/>
                          Regards,<br/>K-nest LMS";
                          $headers = "MIME-Version: 1.0" . "\r\n";
                          $headers .= "Content-type:text/html;charset=UTF-8" . "\r\n";
                          $headers .= 'From: K-nest LMS <donotreply@knestlms.com>' . "\r\n";
                          mail($to, $subject, $message, $headers); */
                        if ($email_id != "") {
                            $website_link = "";
                            $result16 = mysqli_query($conn, "SELECT c.website_link,c.logo_path  
					FROM user_account as ua,company as c WHERE c.company_id=ua.company_id AND ua.user_id=" . $row15['user_id'] . "");
                            if (mysqli_num_rows($result16) > 0) {
                                $row16 = mysqli_fetch_array($result16);
                                $logo_path = "https://learn.knestlms.com/admin/files_cust_comp" . "/" . $row16['logo_path'];
                                if ($row16["website_link"] != null) {
                                    $website_link = $row16["website_link"];
                                }
                            }
                            $credentials = SendinBlue\Client\Configuration::getDefaultConfiguration()->setApiKey('api-key', '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');
                            $apiInstance = new SendinBlue\Client\Api\TransactionalEmailsApi(new GuzzleHttp\Client(), $credentials);
                            $sendSmtpEmail = new \SendinBlue\Client\Model\SendSmtpEmail([
                                'subject' => 'K-nest LMS',
                                'sender' => ['name' => 'K-nest LMS', 'email' => 'donotreply@knestlms.com'],
                                'to' => [['email' => $email_id]],
                                'htmlContent' => '<html><div style="background-color:#f3f5f9;width:100%;padding:10px 50px 10px 10px;"><div style="background:#ffffff;background-color:#ffffff;Margin:0px auto;max-width:620px;padding:30px;">Dear {{params.name}},<br/><br/>Assessment result has been published. Please click on below link to view the result.<br/>
							https://learn.knestlms.com/report.php?user={{params.value}}<br/><br/>Regards,<br/>K-nest LMS Admin</div><div style="padding:30px;"><center><img src=' . $logo_path . ' /><br/>' . $website_link . '</center></div></div></html>',
                                'params' => ['name' => $first_name, 'value' => $encrypted_link_value]
                            ]);
                            try {
                                 $apiInstance->sendTransacEmail($sendSmtpEmail);
                                //print_r($result);
                            } catch (Exception $e) {
                                echo $e->getMessage(), PHP_EOL;
                            }
                        }
                    }
                } else {
                    mysqli_query($conn, "UPDATE ai_batch SET status='completed',last_updated_user=" . $batch_user_id . ",
						last_updated_ts='" . $datetime . "' WHERE user_id=" . $row['user_id'] . " AND 
						offering_id=" . $row['offering_id'] . " AND  assessment_id=" . $row['assessment_id'] . " AND 
						attempt_no=" . $row['attempt_no'] . " AND question_id=" . $row['question_id'] . "");
                }
            }
        }
        $conn->commit();

        fwrite($fp, $count . "\n");
        fclose($fp);
    }
} catch (Exception $e) {
    $conn->rollback();

    $fp = fopen($file_name, "a");
    fwrite($fp, "Date:" . $datetime . "\n");
    fwrite($fp, "Message: " . $e->getMessage() . "\n");
    fclose($fp);
}

$conn->close();
