<?php
include_once '../../config/database.php';
include_once '../../config/properties.php';

$servername = HOST;
$username = USER;
$password = PASSWORD;
$dbname = DATABASE;
$learner_path = CLIENT_HTTP_PATH;
$app_path = $properties['jdbc.playstore_link'];

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

include_once "../../resources/EncryptDecrypt.php";

$ADMIN_PATH = ADMIN_HTTP_PATH;
$TEMPLATE = $properties['jdbc.cert_template'];
$COMPANY = $properties['jdbc.comp_image_folder'];
$SIGN_PATH = $properties['jdbc.signatory_image_folder'];
$COMPANY_NAME = $properties['jdbc.comp_name_image_folder'];

// 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 {
//All active users designation fetch
    $result0 = mysqli_query($conn, "SELECT ua.designation_id,ua.user_id,ua.email_id,ua.first_name,ua.mobile_no,ua.company_id FROM user_account as ua,company as c WHERE ua.status='Active' AND ua.designation_id is not null AND ua.company_id=c.company_id AND c.status='Active'");
    if (mysqli_num_rows($result0) > 0) {
        $file_name = "logs_learning.log";
        $fp = fopen($file_name, "a");
        fwrite($fp, "\n" . "Date:" . $date_time . "\n");
        while ($row0 = mysqli_fetch_array($result0)) {
            $user_id = $row0['user_id'];
            $designation_id = $row0['designation_id'];
            $email_id = $row0['email_id'];
            $name = $row0['first_name'];
            if ($row0['mobile_no'] != null) {
                $mobile_no = $row0['mobile_no'];
            } else {
                $mobile_no = "";
            }
            $company_id = $row0['company_id'];
            //Designation mapped learning path fetch 
            $result1 = mysqli_query($conn, "SELECT lpd.learning_path_id FROM learning_path_designation as lpd,designation_master as d,learning_path as l WHERE l.learning_path_id=lpd.learning_path_id AND d.designation_id=lpd.designation_id AND d.status='Active' AND d.designation_id=" . $designation_id . " AND l.status='Active'");
            if (mysqli_num_rows($result1) > 0) {
                while ($row1 = mysqli_fetch_array($result1)) {
                    $course_ids = array();
                    $assessment_ids = array();
                    $assessment_list = '';
                    $course_list = '';
                    $learning_path_id = $row1['learning_path_id'];
                    //Weather user alrady completed the learning_path
                    $result_complete = mysqli_query($conn, "SELECT user_id FROM learning_path_user_completed WHERE learning_path_id='" . $learning_path_id . "' AND user_id=" . $user_id . "");
                    //if not completed the learning path
                    if (mysqli_num_rows($result_complete) == 0) {
                        //fetch all courses and assessments
                        $result2 = mysqli_query($conn, "SELECT lpa.ca_id,lpa.assignment_type,lpa.dependency,lpa.seq_no FROM learning_path as l,learning_path_assignment as lpa WHERE lpa.learning_path_id=l.learning_path_id AND l.status='Active' AND l.learning_path_id='" . $learning_path_id . "' ORDER BY lpa.seq_no ASC;");
                        if (mysqli_num_rows($result2) > 0) {
                            while ($row2 = mysqli_fetch_array($result2)) {
                                $ca_id = $row2['ca_id'];
                                $ca_type = $row2['assignment_type'];
                                $seq_no = $row2['seq_no'];
                                //required to user complted insert
                                if ($ca_type == "c") {
                                    array_push($course_ids, $ca_id);
                                    $course_list = implode(', ', $course_ids);
                                } else {
                                    array_push($assessment_ids, $ca_id);
                                    $assessment_list = implode(', ', $assessment_ids);
                                }

                                //course
                                if ($ca_type == "c") {
                                    //check user is completed the any offering of the course
                                    $result_check_complete = mysqli_query($conn, "SELECT ca.offering_id,ca.attended_date FROM course_offering as co,course_assignment as ca WHERE ca.offering_id=co.offering_id AND co.course_id = " . $ca_id . " AND co.offering_type is null AND co.status='Active' AND ca.result='complete' AND ca.user_id=" . $user_id . " AND ca.status is null");
                                    if (mysqli_num_rows($result_check_complete) > 0) {
                                        //if Yes then put the entry to completed user table
                                        while ($row_check_complete = mysqli_fetch_array($result_check_complete)) {
                                            mysqli_query($conn, "INSERT IGNORE INTO learning_path_user(learning_path_id, user_id, ca_id, offering_id, assignment_type, completed_date, last_updated_ts) VALUES('" . $learning_path_id . "', '" . $user_id . "', '" . $ca_id . "', '" . $row_check_complete['offering_id'] . "', '" . $ca_type . "', '" . $row_check_complete['attended_date'] . "', '" . $date_time . "' ) ");
                                        }
                                    } else {
                                        //any offerings are active or awaited
                                        $result_check_active_awaited = mysqli_query($conn, "SELECT ca.offering_id,ca.attended_date FROM course_offering as co,course_assignment as ca WHERE ca.offering_id=co.offering_id AND co.course_id = " . $ca_id . " AND co.offering_type is null AND co.status='Active' AND (ca.result is null OR ca.status='awaited') AND ca.user_id=" . $user_id . " AND ca.status is null");
                                        if (mysqli_num_rows($result_check_active_awaited) > 0) {
                                            //already offering assigned                             
                                        } else {
                                            $result3 = mysqli_query($conn, "SELECT offering_id, course_name,offering_name,start_date,end_date FROM course_offering WHERE course_id = " . $ca_id . " AND offering_type is null AND status='Active' AND end_date >= '" . $date . "' ORDER BY created_ts DESC LIMIT 1");
                                            if (mysqli_num_rows($result3) > 0) {
                                                while ($row3 = mysqli_fetch_array($result3)) {
                                                    $type = "course";
                                                    $offering_id = $row3['offering_id'];
                                                    $offering_name = $row3['offering_name'];
                                                    $course_name = $row3['course_name'];
                                                    $start_date = $row3['start_date'];
                                                    $end_date = $row3['end_date'];
                                                    course_assignment_insert($conn, $type, $user_id, $name, $email_id, $mobile_no, $company_id, $course_name, $designation_id, $learning_path_id, $ca_id, $offering_id, $offering_name, $start_date, $end_date, $ca_type, $date, $date_time, $fp, $learner_path, $seq_no, $api_key, $app_path);
                                                }
                                            }
                                        }
                                    }
                                } else {
                                    //assessment
                                    //check user is completed the any offering of assessment
                                    $result_check_complete = mysqli_query($conn, "SELECT ca.offering_id,ca.attended_date from course_offering as co,course_assignment as ca WHERE ca.offering_id=co.offering_id AND co.final_assmt_id = " . $ca_id . " AND co.offering_type='A' AND co.status='Active' AND ca.result='complete' AND ca.user_id=" . $user_id . " AND ca.status is null");
                                    if (mysqli_num_rows($result_check_complete) > 0) {
                                        //if Yes then put the entry to completed user table
                                        while ($row_check_complete = mysqli_fetch_array($result_check_complete)) {
                                            mysqli_query($conn, "INSERT IGNORE INTO learning_path_user(learning_path_id, user_id, ca_id, offering_id, assignment_type, completed_date, last_updated_ts) VALUES('" . $learning_path_id . "', '" . $user_id . "', '" . $ca_id . "', '" . $row_check_complete['offering_id'] . "', '" . $ca_type . "', '" . $row_check_complete['attended_date'] . "', '" . $date_time . "' ) ");
                                        }
                                    } else {
                                        $check_offering_active_awaited = mysqli_query($conn, "SELECT ca.offering_id FROM course_assignment as ca,course_offering as co WHERE co.offering_id=ca.offering_id AND co.status='Active'  AND (ca.result is null OR ca.result='awaited') AND ca.user_id=" . $user_id . " AND ca.status is null AND co.offering_type='A' AND co.final_assmt_id=" . $ca_id . "");
                                        if (mysqli_num_rows($check_offering_active_awaited) > 0) {
                                            //already offering assigned which is in active                                           
                                        } else {
                                            $result4 = mysqli_query($conn, "SELECT co.offering_id, co.offering_name,a.assessment_name,co.start_date,co.end_date FROM course_offering as co,assessment as a WHERE a.assessment_id=co.final_assmt_id AND co.final_assmt_id = " . $ca_id . " AND co.offering_type='A' AND co.status='Active' AND co.end_date >= '" . $date . "' ORDER BY co.created_ts ASC LIMIT 1");
                                            if (mysqli_num_rows($result4) > 0) {
                                                while ($row4 = mysqli_fetch_array($result4)) {
                                                    $type = "assessment";
                                                    $offering_id = $row4['offering_id'];
                                                    $offering_name = $row4['offering_name'];
                                                    $course_name = $row4['assessment_name'];
                                                    $start_date = $row4['start_date'];
                                                    $end_date = $row4['end_date'];
                                                    course_assignment_insert($conn, $type, $user_id, $name, $email_id, $mobile_no, $company_id, $course_name, $designation_id, $learning_path_id, $ca_id, $offering_id, $offering_name, $start_date, $end_date, $ca_type, $date, $date_time, $fp, $learner_path, $seq_no, $api_key, $app_path);
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }

                    if ($course_list == "") {
                        $course_list = "'$course_list'";
                    }
                    if ($assessment_list == "") {
                        $assessment_list = "'$assessment_list'";
                    }
                    //completed user count

                    $result_count = $result_complete_count = mysqli_query($conn, "SELECT(SELECT count(distinct co.course_id) FROM course_offering as co,course_assignment as ca WHERE co.offering_id=ca.offering_id AND co.course_id in (" . $course_list . ") and co.status='Active' AND co.offering_type is null AND ca.result='complete' AND ca.user_id=" . $user_id . ") as course_count, "
                            . "(SELECT count(distinct co.final_assmt_id) FROM course_offering as co,course_assignment as ca WHERE co.offering_id=ca.offering_id AND co.final_assmt_id in (" . $assessment_list . ") and co.status='Active' AND co.offering_type='A' AND ca.result='complete' AND ca.user_id=" . $user_id . ") AS assessment_count, "
                            . "(SELECT count(ca_id) as total_count FROM learning_path_user WHERE learning_path_id=" . $learning_path_id . " AND user_id=" . $user_id . ") as total_count");
                    if (mysqli_num_rows($result_count) > 0) {
                        if ($row_result_main = mysqli_fetch_array($result_count)) {
                            $count1 = $row_result_main['course_count'];
                            $count2 = $row_result_main['assessment_count'];
                            $total_count = $row_result_main['total_count'];
                            $flag = true;
                            //check course id length with completed courses
                            if (count($course_ids) != 0) {
                                if (count($course_ids) != $count1) {
                                    $flag = false;
                                }
                            }
                            //check assessment id length with completed assessmets
                            if (count($assessment_ids) != 0) {
                                if (count($assessment_ids) != $count2) {
                                    $flag = false;
                                }
                            }
                            //check total matches 
                            if ($total_count != 0) {
                                $count3 = $count1 + $count2;
                                if ($total_count != $count3) {
                                    $flag = false;
                                }
                            }


                            if ($flag) {
                                //Fetch latest completed date
                                $result_fetch_cdate = mysqli_query($conn, "SELECT completed_date FROM learning_path_user WHERE user_id=" . $user_id . " AND learning_path_id=" . $learning_path_id . " ORDER BY completed_date DESC LIMIT 1");
                                if (mysqli_num_rows($result_fetch_cdate) > 0) {
                                    //if Yes then put the entry to completed user table
                                    while ($row_cdate = mysqli_fetch_array($result_fetch_cdate)) {
                                        mysqli_query($conn, "INSERT IGNORE INTO learning_path_user_completed(learning_path_id, user_id, status, completed_date, last_updated_ts) VALUES('" . $learning_path_id . "', '" . $user_id . "', 'complete', '" . $row_cdate['completed_date'] . "', '" . $date_time . "' )");
                                        if (mysqli_affected_rows($conn) == 1) {
                                            //check user has email id
                                            //check for the certificate
                                            $check_certificate = mysqli_query($conn, "SELECT learning_path_id FROM learning_path_certificate WHERE learning_path_id=" . $learning_path_id . "");
                                            if (mysqli_num_rows($check_certificate) > 0) {
                                                require_once('tcpdf_include.php');
                                                $course_name = "";
                                                $first_name = "";
                                                $last_name = "";
                                                $attended_date = "";
                                                $template_id = "";
                                                $cust_company_logo = "";
                                                $cust_company_name = "";
                                                $user_email_id = "";
                                                $path = "";
                                                $web_link = "";
                                                $query = mysqli_query($conn, "SELECT lpc.template_id,c.logo_path,c.name,c.website_link,ua.first_name,ua.last_name,ua.email_id,l.learning_path_name,lpu.completed_date "
                                                        . "FROM user_account as ua,learning_path as l,learning_path_user_completed as lpu,company as c,learning_path_certificate as lpc "
                                                        . "WHERE ua.user_id=lpu.user_id AND l.learning_path_id=lpu.learning_path_id AND ua.company_id=c.company_id AND lpc.learning_path_id=l.learning_path_id "
                                                        . "AND lpu.learning_path_id=" . $learning_path_id . " AND ua.user_id=" . $user_id . "");
                                                if (mysqli_num_rows($query) > 0) {
                                                    while ($row_r = mysqli_fetch_array($query)) {
                                                        $first_name = $row_r['first_name'];
                                                        $last_name = $row_r['last_name'];
                                                        $user_email_id = $row_r['email_id'];
                                                        $course_name = $row_r['learning_path_name'];
                                                        $attended_date = $row_r['completed_date'];
                                                        $cust_company_logo = $row_r['logo_path'];
                                                        $cust_company_name = $row_r['name'];
                                                        $template_id = $row_r['template_id'];
                                                        $path = ADMIN_HTTP_PATH . $COMPANY . "/" . $cust_company_logo;
                                                        $web_link = $row_r['website_link'];
                                                    }
                                                }
                                                $_attened_date = strtotime($attended_date);
                                                $date1 = date("F d, Y", $_attened_date);
                                                $query1 = mysqli_query($conn, "SELECT ct.template_file,ct.template_name,cf.comp_name,cf.text1,cf.text2,cf.text3,cf.text4,cf.text5,cf.text6,"
                                                        . "cf.text1_color,cf.text2_color,cf.text3_color,cf.text4_color,cf.text5_color,cf.text6_color,cf.comp_name_color,cf.name_color,cf.course_color,cf.grade_color,"
                                                        . "cf.text1_size,cf.text2_size,cf.text3_size,cf.text4_size,cf.text5_size,cf.text6_size,cf.comp_name_size,cf.name_size,cf.course_size,cf.grade_size,cf.comp_name_path,cf.sign_path "
                                                        . "FROM certificate_template as ct,certificate_format as cf "
                                                        . "where ct.template_id=" . $template_id . " AND ct.format_id=cf.format_id");
                                                if (mysqli_num_rows($query1) > 0) {
                                                    while ($row4 = mysqli_fetch_array($query1)) {
                                                        $template_name_1 = $row4['template_name'];
                                                        $template_file_1 = $row4['template_file'];
                                                        $text1_1 = $row4['text1'];
                                                        $text2_1 = $row4['text2'];
                                                        $text3_1 = $row4['text3'];
                                                        $text4_1 = $row4['text4'];
                                                        $text5_1 = $row4['text5'];
                                                        $text6_1 = $row4['text6'];

                                                        $text1_color_1 = $row4['text1_color'];
                                                        $record1_1 = explode(",", $text1_color_1);
                                                        $text2_color_1 = $row4['text2_color'];
                                                        $record2_1 = explode(",", $text2_color_1);
                                                        $text3_color_1 = $row4['text3_color'];
                                                        $record3_1 = explode(",", $text3_color_1);
                                                        $text4_color_1 = $row4['text4_color'];
                                                        $record4_1 = explode(",", $text4_color_1);
                                                        $text5_color_1 = $row4['text5_color'];
                                                        $record5_1 = explode(",", $text5_color_1);
                                                        $text6_color_1 = $row4['text6_color'];
                                                        $record9_1 = explode(",", $text6_color_1);
                                                        $comp_name_color_1 = $row4['comp_name_color'];
                                                        $record10_1 = explode(",", $comp_name_color_1);
                                                        $name_color_1 = $row4['name_color'];
                                                        $record6_1 = explode(",", $name_color_1);
                                                        $course_color_1 = $row4['course_color'];
                                                        $record7_1 = explode(",", $course_color_1);

                                                        $text1_size_1 = $row4['text1_size'];
                                                        $text2_size_1 = $row4['text2_size'];
                                                        $text3_size_1 = $row4['text3_size'];
                                                        $text4_size_1 = $row4['text4_size'];
                                                        $text5_size_1 = $row4['text5_size'];
                                                        $text6_size_1 = $row4['text6_size'];
                                                        $comp_name_size_1 = $row4['comp_name_size'];
                                                        $name_size_1 = $row4['name_size'];
                                                        $course_size_1 = $row4['course_size'];

                                                        $comp_name_path_1 = $row4['comp_name_path'];
                                                        $sign_path_1 = $row4['sign_path'];
                                                        $company_name_con = $row4['comp_name'];

                                                        //$GLOBALS['s_path_1'] = $mobitute_template . $template_name_1 . '/' . $template_file_1;
//                                                        define('TEMPLATE_PATH2', $ADMIN_PATH . $TEMPLATE . "/" . $template_id . '/' . rawurlencode($template_file_1));
                                                        if (!defined('TEMPLATE_PATH2')) {
                                                            define('TEMPLATE_PATH2', $ADMIN_PATH . $TEMPLATE . "/" . $template_id . '/' . rawurlencode($template_file_1));
                                                        }
                                                        // Extend the TCPDF class to create custom Header and Footer
                                                        if (!class_exists('MYPDF')) {

                                                            class MYPDF extends TCPDF {

                                                                //Page header
                                                                public function Header() {

                                                                    // get the current page break margin
                                                                    $bMargin = $this->getBreakMargin();
                                                                    // get current auto-page-break mode
                                                                    $auto_page_break = $this->AutoPageBreak;
                                                                    // disable auto-page-break
                                                                    $this->SetAutoPageBreak(false, 0);
                                                                    // set bacground image
                                                                    //$img_file = $GLOBALS['s_path_1'];
                                                                    $this->Image(TEMPLATE_PATH2, 0, 0, 297, 210, '', '', '', false, 300, '', false, false, 0);
                                                                    // restore auto-page-break status
                                                                    $this->SetAutoPageBreak($auto_page_break, $bMargin);
                                                                    // set the starting point for the page content
                                                                    $this->setPageMark();
                                                                }

                                                            }

                                                        }

// create new PDF document
                                                        $pdf = new MYPDF('L', PDF_UNIT, PDF_PAGE_FORMAT, true, 'UTF-8', false);

// set document information
                                                        $pdf->SetCreator(PDF_CREATOR);
                                                        $pdf->SetTitle('Certificate');

// set header and footer fonts
                                                        $pdf->setHeaderFont(Array(PDF_FONT_NAME_MAIN, '', PDF_FONT_SIZE_MAIN));

// set default monospaced font
                                                        $pdf->SetDefaultMonospacedFont(PDF_FONT_MONOSPACED);

// set margins
                                                        $pdf->SetMargins(0, 0, 0);
                                                        $pdf->SetHeaderMargin(0);
                                                        $pdf->SetFooterMargin(0);

// remove default footer
                                                        $pdf->setPrintFooter(false);

// set auto page breaks
                                                        $pdf->SetAutoPageBreak(TRUE, PDF_MARGIN_BOTTOM);

// set image scale factor
                                                        $pdf->setImageScale(PDF_IMAGE_SCALE_RATIO);

// set some language-dependent strings (optional)
                                                        if (@file_exists(dirname(__FILE__) . '/lang/eng.php')) {
                                                            require_once(dirname(__FILE__) . '/lang/eng.php');
                                                            $pdf->setLanguageArray($l);
                                                        }

// add a page
                                                        $pdf->AddPage();

// Print a text
                                                        $pdf->Ln(20);
                                                        if ($comp_name_path_1 != "") {
                                                            $pdf->Image($ADMIN_PATH . $COMPANY_NAME . "/" . $comp_name_path_1, '', '', '', '', '', '', '', true, 300, 'C');
                                                        } else if ($company_name_con != "") {
                                                            $pdf->SetFont('freesans', 'B', $comp_name_size_1);
                                                            $pdf->SetTextColor($record10_1[0], $record10_1[1], $record10_1[2]);
                                                            $pdf->MultiCell(0, 0, $cust_company_name, 0, 'C', 0, 0, '', '', true, 0);
                                                        }
                                                        $pdf->Ln(22);
                                                        $pdf->SetFont('freesans', 'B', $text1_size_1);
                                                        $pdf->SetTextColor($record1_1[0], $record1_1[1], $record1_1[2]);
                                                        $pdf->MultiCell(0, 0, $text1_1, 0, 'C', 0, 0, '', '', true, 0);
                                                        $pdf->Ln(22);
                                                        $pdf->SetFont('freesans', 'B', $text2_size_1);
                                                        $pdf->SetTextColor($record2_1[0], $record2_1[1], $record2_1[2]);
                                                        $pdf->MultiCell(0, 0, $text2_1, 0, 'C', 0, 0, '', '', true, 0);
                                                        $pdf->Ln(12);
                                                        $pdf->SetFont('freesans', 'B', $name_size_1);
                                                        $pdf->SetTextColor($record6_1[0], $record6_1[1], $record6_1[2]);
                                                        $pdf->MultiCell(0, 0, $first_name . ' ' . $last_name, 0, 'C', 0, 0, '', '', true, 0);
                                                        $pdf->Ln(20);
                                                        $pdf->SetFont('freesans', 'B', $text3_size_1);
                                                        $pdf->SetTextColor($record3_1[0], $record3_1[1], $record3_1[2]);
                                                        $pdf->MultiCell(0, 0, $text3_1, 0, 'C', 0, 0, '', '', true, 0);
                                                        $pdf->Ln(11);
                                                        $pdf->SetFont('freesans', 'B', 20);
                                                        $pdf->SetTextColor($record7_1[0], $record7_1[1], $record7_1[2]);
                                                        $pdf->MultiCell(0, 0, $course_name, 0, 'C', 0, 0, '', '', true, 0);
                                                        $pdf->Ln(14);

                                                        $pdf->Ln(25);

                                                        $pdf->setCellPaddings($left = '45', $top = '', $right = '46', $bottom = '');
                                                        if ($text5_1 != "" && $attended_date != NULL) {
                                                            $pdf->SetFont('freesans', 'B', $text5_size_1);
                                                            $pdf->SetTextColor($record5_1[0], $record5_1[1], $record5_1[2]);
                                                            $pdf->Cell(0, 0, $date1, 0, false, 'L', 0, '', 0, false, 'T', 0);
                                                        }
                                                        if ($cust_company_logo != "") {
                                                            $pdf->Image($ADMIN_PATH . $COMPANY . "/" . $cust_company_logo, '', '', '', '', '', '', '', true, 300, 'C');
                                                        }
                                                        if ($sign_path_1 != "") {
                                                            $pdf->Image($ADMIN_PATH . $SIGN_PATH . "/" . $sign_path_1, 220, 142, '', '', '', '', '', true, 300, FALSE);
                                                        }
                                                        if ($text5_1 != "" && $attended_date != NULL) {
                                                            $pdf->Line(30, 156, 100, 156);
                                                        }
                                                        if ($text6_1 != "") {
                                                            $pdf->Line(200, 156, 270, 156);
                                                        }
                                                        $pdf->Ln(1);
                                                        $pdf->setCellPaddings($left = '45', $top = '', $right = '30', $bottom = '');
                                                        if ($text5_1 != "" && $attended_date != NULL) {
                                                            $pdf->SetFont('freesans', 'B', $text5_size_1);
                                                            $pdf->SetTextColor($record5_1[0], $record5_1[1], $record5_1[2]);
                                                            $pdf->Cell(0, 30, $text5_1, 0, false, 'L', 0, '', 0, false, 'T', 'M');
                                                        }
                                                        if ($text6_1 != "") {
                                                            $pdf->SetFont('freesans', 'B', $text6_size_1);
                                                            $pdf->SetTextColor($record6_1[0], $record6_1[1], $record6_1[2]);
                                                            $pdf->Cell(0, 30, $text6_1, 0, false, 'R', 0, '', 0, false, 'T', 'M');
                                                        }
                                                        /*                                                         * ************************************************* */

// --- example with background set on page ---
// remove default header
                                                        $pdf->setPrintHeader(false);
                                                        $pdf->setPrintFooter(false);
                                                        $encoded_file = base64_encode($pdf->Output('certificate.pdf', 'S'));
                                                        //Sending Mail
                                                        if ($user_email_id != "") {
                                                            $mail = sendMailBlue($fp, $user_email_id, $course_name, $encoded_file, $path, $first_name . ' ' . $last_name, $api_key, $web_link);
                                                        }
                                                    }
                                                }
                                            }
                                        }
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
        fclose($fp);
    }
    $conn->commit();
} catch (Exception $e) {
    $conn->rollback();
}
$conn->close();

function course_assignment_insert($conn, $type, $user_id, $name, $email_id, $mobile_no, $company_id, $course_name, $designation_id, $learning_path_id, $ca_id, $offering_id, $offering_name, $start_date, $end_date, $ca_type, $date, $date_time, $fp, $learner_path, $seq_no, $api_key, $app_path) {
//check limit
    $course_count_user = 0;
    $course_count_liscense_eny = 0;
    $course_count_liscense = 0;
    $result_check_course = mysqli_query($conn, "SELECT count(ca.user_id) as total_course_count FROM course_assignment as ca WHERE  ca.status is null AND ca.user_id='" . $user_id . "'");
    if (mysqli_num_rows($result_check_course) > 0) {
        if ($row_check_course = mysqli_fetch_array($result_check_course)) {
            $course_count_user = $row_check_course['total_course_count'];
        }
    }
    $flag = "0";
    $result_check_license = mysqli_query($conn, "SELECT l.max_course_per_usr FROM license_key as l,company as c WHERE c.company_code=l.company_code AND c.company_id='" . $company_id . "'");
    if (mysqli_num_rows($result_check_license) > 0) {
        if ($row_check_license = mysqli_fetch_array($result_check_license)) {
            if ($row_check_license['max_course_per_usr'] != null) {
                $course_count_liscense_eny = $row_check_license['max_course_per_usr'];
                $mcrypt = new MCrypt();
                $course_count_liscense = $mcrypt->decrypt($course_count_liscense_eny);
            } else {
                $flag = "1";
            }
        }
    }

    if ($course_count_liscense != 0) {
        if ($course_count_user <= $course_count_liscense) {
            $flag = "1";
        }
    }
    if ($flag == "0") {
        fwrite($fp, "can not assign due to user crossed the limit:" . $user_id . "\n");
    }
    if ($flag == "1") {
        //insert to course assignment users not attempted
        //added extra check because group assignmnet got issue.
        $result_check_ca = mysqli_query($conn, "SELECT user_id FROM course_assignment WHERE user_id='" . $user_id . "' AND offering_id='" . $offering_id . "'");
        if (mysqli_num_rows($result_check_ca) == 0) {
            mysqli_query($conn, "INSERT IGNORE INTO course_assignment(offering_id, user_id, group_id, score, completion, content_completion, assignment_type, course_assigned_user, course_assigned_ts, last_updated_user, last_updated_ts) VALUES('" . $offering_id . "', '" . $user_id . "', '', '0', '0', '0', 'LRGPATH', '" . $user_id . "', '" . $date_time . "', '" . $user_id . "', '" . $date_time . "' ) ");
            mysqli_query($conn, "INSERT IGNORE INTO course_assignment_date(offering_id, user_id, start_date,end_date,created_user,created_ts,last_updated_user, last_updated_ts) VALUES('" . $offering_id . "', '" . $user_id . "','" . $start_date . "','" . $end_date . "', '" . $user_id . "', '" . $date_time . "', '" . $user_id . "', '" . $date_time . "' ) ");
            if (mysqli_affected_rows($conn) == 1) {
                fwrite($fp, "User Id:" . $user_id . "\n");
                fwrite($fp, "Designation Id:" . $designation_id . "\n");
                fwrite($fp, "Learning Path Id:" . $learning_path_id . "\n");
                fwrite($fp, "Course Id:" . $ca_id . "\n");
                fwrite($fp, "Offering Id:" . $offering_id . "\n");
                fwrite($fp, "Offering Name:" . $offering_name . "\n");
                fwrite($fp, "Course Name:" . $course_name . "\n");
                //sending mail and sms
                sendmail($fp, $conn, $type, $company_id, $email_id, "enroll", $user_id, $name, $course_name, $learner_path, $start_date, $end_date, $api_key);
                sendsms($fp, $conn, $type, $company_id, $mobile_no, "enroll", $name, $course_name, $app_path, $start_date, $end_date);
            }
        }
    }
}

function sendmail($fp, $conn, $type, $company_id, $email_id, $mode, $user_id, $name, $course_name, $learner_path, $start_date, $end_date, $api_key) {
    $start_date = date("d-m-Y", strtotime($start_date));
    $end_date = date("d-m-Y", strtotime($end_date));
    if ($email_id != "") {
        $result_email = mysqli_query($conn, "SELECT email FROM configuration WHERE company_id = " . $company_id . " AND email='ON'");
        if (mysqli_num_rows($result_email) > 0) {
            $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=" . $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"];
                }
            }
            $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 ' . $name . ',<br/><br/>You have been assigned to ' . $type . ': ' . $course_name . '. <br/>Starts on: ' . $start_date . '. Ends on: ' . $end_date . '. Please complete it as early as possible.<br/> Access the learner panel at ' . $learner_path . ' or access the App using https://bit.ly/3xTfzhq.<br/><br/>Regards,<br/>K-nest LMS Admin</div><div style="padding:30px;"><center><img src="' . $logo_path . '" /><br/>' . $website_link . '</center></div></div>';
            $credentials = SendinBlue\Client\Configuration::getDefaultConfiguration()->setApiKey('api-key', $api_key);
            $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
            ]);
            try {
                $apiInstance->sendTransacEmail($sendSmtpEmail);
                fwrite($fp, "Sent the mail-" . $mode . "" . $course_name . "\n");
            } catch (Exception $e) {
                fwrite($fp, $e->getMessage());
                fclose($fp);
            }
        } else {
            fwrite($fp, "Cannot send Email of due to it is switched OFF:" . $course_name . "\n");
        }
    }
}

function sendsms($fp, $conn, $type, $company_id, $mobile_no, $mode, $name, $course_name, $app_path, $start_date, $end_date) {
    $start_date = date("d-m-Y", strtotime($start_date));
    $end_date = date("d-m-Y", strtotime($end_date));

    if ($mobile_no != "") {

        $result_sms = mysqli_query($conn, "SELECT sms FROM configuration WHERE company_id = " . $company_id . " AND sms='ON'");
        if (mysqli_num_rows($result_sms) > 0) {
            $tempid = "";
            if ($type == 'course') {
                $tempid = "1607100000000287019";
            } else {
                $tempid = "1607100000000287017";
            }
            $msgbody = urlencode("Dear User, You have been assigned to " . $type . ": " . $course_name . ". Please complete in time.Access the learner panel at " . $app_path . " - 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=' . $tempid . '&response=Y');
            fwrite($fp, "Sent SMS-" . $mode . " :" . $course_name . "\n");
        } else {

            fwrite($fp, "Cannot send SMS of due to it is switched OFF:" . $course_name . "\n");
        }
    }
}

function SendMailBlue($fp, $email_id = "", $subject = "", $encoded_file = "", $path = "", $user_full_name = "", $api_key = "", $web_link = "") {
    $html = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
	<head>
		<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
		<title></title>
	</head>
	<body style="font-family: arial;" width="100%">
		<div style="background-color:#f3f5f9;width:100%;">
			<div style="Margin:0px auto;max-width:720px;padding-top:30px;">
				<img src="' . $path . '" /><br/>
			</div><br/>
			<div style="background:#ffffff;background-color:#ffffff;Margin:0px auto;max-width:620px;padding:30px;">
				Hello ' . $user_full_name . '<br/><br/>
				Congratulations! You have unlocked your certificate of completion for "' . $subject . '"<br/><br/>
			    Regards,<br/>
				K-nest LMS Admin<br/>
			</div>
			<div style="padding:30px;"><center><img src="' . $path . '" /><br/>"' . $web_link . '"</center></div>
		</div>
	</body>
</html>';

    $subject = ($subject ? 'Your completion certificate is ready for "' . $subject . '"' : 'Default Subject');

    $credentials = SendinBlue\Client\Configuration::getDefaultConfiguration()->setApiKey('api-key', $api_key);
    $apiInstance = new SendinBlue\Client\Api\TransactionalEmailsApi(new GuzzleHttp\Client(), $credentials);

    $sendSmtpEmail = new \SendinBlue\Client\Model\SendSmtpEmail([
        'subject' => $subject,
        'sender' => ['name' => 'Knest LMS', 'email' => 'donotreply@knestlms.com'],
        'to' => [['email' => '' . $email_id . '']],
        'htmlContent' => $html
    ]);
    if (!empty($encoded_file)) {
        $attachment_item = array(
            'name' => 'certificate.pdf',
            'content' => $encoded_file
        );
        $attachment_list = array($attachment_item);
        $sendSmtpEmail['attachment'] = $attachment_list;
    }
    try {
        $result = $apiInstance->sendTransacEmail($sendSmtpEmail);
        if (strpos($result, 'messageId') !== false) {
            fwrite($fp, "true");
            fwrite($fp, "\n" . "Sent Certificate mail successfully-" . $email_id . "\n");
        } else {
            fwrite($fp, "false");
        }
    } catch (Exception $e) {
        fwrite($fp, "\n" . 'Exception when calling TransactionalEmailsApi->sendTransacEmail: ' . $e->getMessage());
    }
}
