<?php require_once __DIR__.'/../wp-config.php';

if(!defined('TBL_MGR_STUDENTS')){
    define('TBL_MGR_STUDENTS','mgr_students');
}
class MgrStudents{
  static function encode_pwd($pwd_plaintext){
    return md5('@Students'.$pwd_plaintext.'AIA');
  }
  static function insert($school_no, $pwd_plaintext, $email, $name){
    global$wpdb;
    $pwd = self::encode_pwd($pwd_plaintext);
    $a=array('school_no'=>$school_no, 'pwd'=>$pwd,'email'=>$email,'name'=>$name);
    return $wpdb->insert(TBL_MGR_STUDENTS, $a);
  }
  static function login_result($school_no,$pwd){
    global$wpdb;
    $pwd_encode = self::encode_pwd($pwd);
    $school_no=strtolower($school_no);
    $sql='SELECT * FROM '.TBL_MGR_STUDENTS.' WHERE school_no="'.$school_no.'" AND `pwd`="'.$pwd_encode.'"';
    return $wpdb->get_row($sql);
  }
  static function all(){
    global$wpdb;
    return $wpdb->get_results('SELECT * FROM '.TBL_MGR_STUDENTS);
  }
  static function get_all_school_no_prefix(){
    global$wpdb;
    return $wpdb->get_results('SELECT SUBSTRING(school_no,1,5) school_no_prefix FROM '.TBL_MGR_STUDENTS.' GROUP BY SUBSTRING(school_no,1,5)');
  }
  static function find_by_school_no_prefix($school_no_prefix){
    global$wpdb;
    return$wpdb->get_results('SELECT * FROM '.TBL_MGR_STUDENTS.' WHERE school_no LIKE "'.$school_no_prefix.'%"');
  }
  static function get($school_no){
    global$wpdb;
    return $wpdb->get_row('SELECT * FROM '.TBL_MGR_STUDENTS.' WHERE school_no="'.$school_no.'"');
  }
  static function find_by_name($name){
    global$wpdb;
    return $wpdb->get_results('SELECT * FROM '.TBL_MGR_STUDENTS.' WHERE `name`="'.$name.'"');
  }
  static function get_by_name_and_email($name,$email){
    global$wpdb;
    return $wpdb->get_row('SELECT * FROM '.TBL_MGR_STUDENTS.' WHERE `name`="'.$name.'" AND `email`="'.$email.'"');
  }
  static function delete($school_no){
    global$wpdb;
    $wpdb->delete(TBL_MGR_STUDENTS, array('school_no'=>$school_no));
  }
  static function reset_pwd($school_no, $new_pwd){
    global$wpdb;
    self::update_pwd($school_no, $new_pwd);
    $wpdb->update(TBL_MGR_STUDENTS,array('reset_pwd_hashkey'=>NULL, 'hashkey_datetime'=>NULL),array('school_no'=>$school_no));
  }
  static function update_pwd($school_no, $new_pwd){
    global$wpdb;
    $wpdb->update(TBL_MGR_STUDENTS, array('pwd'=>self::encode_pwd($new_pwd)), array('school_no'=>$school_no));
  }
  static function update_reset_pwd_hashkey($school_no, $hashkey){
    global$wpdb;
    $school_no = strtolower($school_no);
    $wpdb->update(TBL_MGR_STUDENTS, array(
                    'reset_pwd_hashkey'=>$hashkey,
                    'hashkey_datetime'=>now()
                  ), array('school_no'=>$school_no));
  }
}