<?php
if (!defined('ABSPATH')) exit();
if (!class_exists('DbIndeedAbstract')) require_once ULP_PATH . 'classes/Abstracts/DbIndeedAbstract.class.php';
if (class_exists('Db_User_Certificates')) return;
class Db_User_Certificates extends DbIndeedAbstract{
/*
uid BIGINT(20) NOT NULL,
course_id BIGINT(20),
certificate_id BIGINT(20) NOT NULL,
grade VARCHAR(10),
details TEXT,
obtained_date TIMESTAMP NOT NULL DEFAULT 0
*/
  	/**
  	 * @var string
  	 */
  	protected $table = '';
  	/**
  	 * @param none
  	 * @return none
  	 */
  	public function __construct(){
  		global $wpdb;
  		$this->table = $wpdb->prefix . 'ulp_student_certificate';
  	}
    public function getAllCertificatesForUser($uid=0){
        global $wpdb;
        $uid = esc_sql($uid);
        $q = "
              SELECT  a.id,
                      a.uid,
                      a.course_id,
                      a.certificate_id,
                      a.grade,
                      a.details,
                      a.obtained_date,
                      b.post_title as certificate_title,
                      c.post_title as course_name
                  FROM
                  {$wpdb->prefix}ulp_student_certificate a
                  INNER JOIN {$wpdb->posts} b
                  ON a.certificate_id=b.ID
                  INNER JOIN {$wpdb->posts} c
                  ON a.course_id=c.ID
                  WHERE
                  a.uid=$uid;
        ";
        return $wpdb->get_results($q);
        //return $this->getResults('*', "uid=$uid");
    }
    public function userHasCertificateForCourse($uid=0, $course_id=0){
        $uid = esc_sql($uid);
        $course_id = esc_sql($course_id);
        $data = $this->getRow("`id`, `uid`, `course_id`, `certificate_id`, `grade`, `details`, `obtained_date`", "uid=$uid AND course_id=$course_id");
        if ($data==null){
          return false;
        }
        return $data;
    }
    public function addCertificateForUser($uid=0, $course_id=0, $certificate_id=0, $grade='', $details=''){
        $uid = esc_sql($uid);
        $course_id = esc_sql($course_id);
        $certificate_id = esc_sql($certificate_id);
        $grade = esc_sql($grade);
        $details = esc_sql($details);
        $already_exists = $this->userHasCertificateForCourse($uid, $course_id);
        if ($already_exists && $already_exists == $certificate_id){
            return false;
        }
        $obtained_date = date('Y-m-d H:i:s', time() );
        $this->insert("null, $uid, $course_id, $certificate_id, '$grade', '$details', '$obtained_date'");
        do_action('ulp_user_receive_certificate', $uid, $course_id, $certificate_id, $grade);
    }
    public function getCertificateById($id=0){
        global $wpdb;
        $id = esc_sql($id);
        $q = "SELECT course_id, certificate_id, uid, grade, details, obtained_date FROM {$wpdb->prefix}ulp_student_certificate WHERE id=$id";
        $data = $wpdb->get_row($q);
        return (array)$data;
    }

	public function delete($uid=0, $certificate_id=0){
        $uid = esc_sql($uid);
        $certificate_id = esc_sql($certificate_id);
        return parent::delete(" uid=$uid AND certificate_id=$certificate_id ");
    }
}
