<?php

require('db.php');


$types = array(
    'articles' => 'article',
    'news' => 'news',
    'faq' => 'faq',
    'activities' => 'activity',
);

// Get all the data from the DB according to given content type, for example articles
function get_all($type, $translation_flag = false, $GUID){
    if(!$GUID && !check_guid($GUID)) {
        return false;
    }
    
    
    global $types;
    $table = $type;
    $translation = $table . '_translate';
    $type = $types[$type];
    $id = (int)$id;
    $link = conn();
    $id_column = $type . '_id';

    /* ---- Must add status condition and sorting by date  ---- */
    $sql    = "SELECT * FROM $table";
    $result = mysql_query($sql, $link);

    if (!$result) {
        echo "DB Error, could not query the database\n";
        echo 'MySQL Error: ' . mysql_error();
        exit;
    }

    $TempArray = array(); // make a new array to hold all your data
    $index = 0;
    while($row = mysql_fetch_assoc($result)){ // loop to store the data in an associative array.
        if($translation_flag){
            $sql    = "SELECT * FROM $translation WHERE $id_column = $row[$id_column]";
            $trans_reslt = mysql_query($sql, $link);
            $trans_reslt = mysql_fetch_assoc($trans_reslt);
            if($trans_reslt){
                $row['translation'] = $trans_reslt;
            }
        }
        $TempArray[$index] = $row;
        $index++;
    }
    return $TempArray;

    free($result);

}


function get_news($translation_flag = false, $GUID){

    if(!$GUID && !check_guid($GUID)) {
        return false;
    }
    
    global $types;
    $table = 'news';
    $translation = $table . '_translate';
    $type = 'news';
    $id = (int)$id;
    $link = conn();
    $id_column = $type . '_id';

    /* ---- Must add status condition and sorting by date  ---- */
    $sql    = "SELECT * FROM $table";
    $result = mysql_query($sql, $link);

    if (!$result) {
        echo "DB Error, could not query the database\n";
        echo 'MySQL Error: ' . mysql_error();
        exit;
    }

    $TempArray = array(); // make a new array to hold all your data
    $index = 0;
    while($row = mysql_fetch_assoc($result)){ // loop to store the data in an associative array.
        if($translation_flag){
            $sql    = "SELECT * FROM $translation WHERE $id_column = $row[$id_column]";
            $trans_reslt = mysql_query($sql, $link);
            $trans_reslt = mysql_fetch_assoc($trans_reslt);
            if($trans_reslt){
                $row['translation'] = $trans_reslt;
            }
        }
        $TempArray[$index] = $row;
        $index++;
    }
    return $TempArray;

    free($result);

}

// Get one record from the database based of the content type
function get_one($type, $id, $translation_flag = false, $GUID){
    if(!$GUID && !check_guid($GUID)) {
        return false;
    }
    
    global $types;
    $table = $type;
    $translation = $table . '_translate';
    $type = $types[$type];
    $id = (int)$id;
    $link = conn();
    $id_column = $type . '_id';


    $id = (int)$id;
    $link = conn();
    $id_column = $type . '_id';

    $sql    = "SELECT * FROM $table WHERE $id_column = $id";
    $result = mysql_query($sql, $link);

    if (!$result) {
        echo "DB Error, could not query the database\n";
        echo 'MySQL Error: ' . mysql_error();
        exit;
    }

    $TempArray = array(); // make a new array to hold all your data
    $index = 0;
    while($row = mysql_fetch_assoc($result)){ // loop to store the data in an associative array.
        if($translation_flag){
            $sql    = "SELECT * FROM $translation WHERE $id_column = $row[$id_column]";
            $trans_reslt = mysql_query($sql, $link);
            $trans_reslt = mysql_fetch_assoc($trans_reslt);
            if($trans_reslt){
                $row['translation'] = $trans_reslt;
            }
        }
        $TempArray[$index] = $row;
        $index++;
    }
    return $TempArray;

    free($result);

}

// Get all the payments of a student
function get_payments($student_id, $GUID){

    if(!$GUID && !check_guid($GUID)) {
        return false;
    }
    
    $student_id = (int)$student_id;
    $link = conn();

    $sql    = "SELECT * FROM students_payments WHERE student_id = $student_id";
    $result = mysql_query($sql, $link);

    if (!$result) {
        echo "DB Error, could not query the database\n";
        echo 'MySQL Error: ' . mysql_error();
        exit;
    }

    $TempArray = array(); // make a new array to hold all your data
    $index = 0;
    while($row = mysql_fetch_assoc($result)){ // loop to store the data in an associative array.
        $TempArray[$index] = $row;
        $index++;
    }
    return $TempArray;

    free($result);
}

// Student Auth
function student_auth($student_id, $password){
    $student_id = (int)$student_id;
    $link = conn();

    $sql    = "SELECT * FROM students WHERE user_id = $student_id AND user_password = $password AND status = 1";
    $result = mysql_query($sql, $link);

    if (!$result) {
        return false;
    }

    while($row = mysql_fetch_assoc($result)){
        $id = $row['student_id'];
        $name_en = $row['student_name'];
        $name_ar = $row['student_ar_name'];
        $class_id = $row['class_id'];
        $year_id = $row['year_id'];
    }

    if(is_numeric($student_id)){

        //$class = array();
        $sql   = "
    SELECT classes.class_title AS class_en,classes_translate.class_title AS class_ar,classes.class_id, classes.level_id, levels.level_title AS level_en, levels_translate.level_title AS level_ar
    FROM classes
    JOIN classes_translate
    ON classes.class_id = classes_translate.class_id
    JOIN levels
    ON classes.level_id = levels.level_id
    JOIN levels_translate
    ON classes.level_id = levels_translate.level_id
    WHERE classes.class_id = $class_id
    ";
        $result = mysql_query($sql, $link);
        if($result){
            $class = mysql_fetch_assoc($result);
            $access_token = get_guid($id);
            $student = array(
                'student_id' => $id,
                'access_token' => $access_token,
                'name_en' => $name_en,
                'name_ar' => $name_ar,
                'class' => $class,
            );
            return $student;
        }

    }
    return false;


}

// Get all activities per student ID, Class ID
function get_activities($student_id, $class_id, $GUID){
    if(!$student_id && !$class_id)
        die();

    if(!$GUID && !check_guid($GUID)) {
        return false;
    }
    
    
    global $types;
    $table = 'activities';
    $translation = $table . '_translate';
    $type = $types[$type];
    $student_id = (int)$student_id;
    $class_id = (int)$class_id;
    $link = conn();
    $id_column = $type . '_id';


    /* ---- Must add status condition and sorting by date  ---- */
    $sql    = "SELECT activities.* FROM activities
  JOIN activities_classes
  ON activities.activity_id = activities_classes.activity_id
  WHERE activities_classes.class_id = $class_id
  ";
    $result = mysql_query($sql, $link);

    if (!$result) {
        echo "DB Error, could not query the database\n";
        echo 'MySQL Error: ' . mysql_error();
        exit;
    }

    $TempArray = array(); // make a new array to hold all your data
    $index = 0;
    while($row = mysql_fetch_assoc($result)){ // loop to store the data in an associative array.

        // Get the translation
        $id = $row['activity_id'];
        $sql    = "SELECT * FROM activities_translate WHERE activity_id = $id";
        $trans_reslt = mysql_query($sql, $link);
        if($trans_reslt){
            $trans_reslt = mysql_fetch_assoc($trans_reslt);
            if($trans_reslt){
                $row['translation'] = $trans_reslt;
            }
        }

        // check if the student subscribed to this activity
        $sql    = "SELECT * FROM activities_subscribers WHERE student_id = $student_id AND activity_id = $id";
        $subscribe_reslt = mysql_query($sql, $link);
        if($subscribe_reslt){
            $subscribe_reslt = mysql_fetch_assoc($subscribe_reslt);
            if($subscribe_reslt){
                $row['subscribe'] = $subscribe_reslt;
            }
        }
        $TempArray[$index] = $row;
        $index++;
    }
    return $TempArray;

    free($result);

}

// Get specific activity per student ID, Class ID and activity_id
function get_activity($student_id, $class_id, $activity_id, $GUID){
    if(!$student_id && !$class_id && !$activity_id)
        die();
    if(!$GUID && !check_guid($GUID)) {
        return false;
    }
    
    global $types;
    $table = 'activities';
    $translation = $table . '_translate';
    $type = $types[$type];
    $student_id = (int)$student_id;
    $class_id = (int)$class_id;
    $link = conn();
    $id_column = $type . '_id';


    /* ---- Must add status condition and sorting by date  ---- */
    $sql    = "SELECT activities.*, activities_classes.*, activities_subscribers. *
  FROM activities
  JOIN activities_classes
  ON activities.activity_id = activities_classes.activity_id
  JOIN activities_subscribers
  ON activities.activity_id = activities_subscribers.activity_id
  WHERE activities.activity_id = $activity_id AND
  activities_classes.class_id = $class_id AND
  activities_subscribers.student_id = $student_id
  ";
    $result = mysql_query($sql, $link);

    if (!$result) {
        echo "DB Error, could not query the database\n";
        echo 'MySQL Error: ' . mysql_error();
        exit;
    }

    $TempArray = array(); // make a new array to hold all your data
    $index = 0;
    while($row = mysql_fetch_assoc($result)){ // loop to store the data in an associative array.

        // Get the translation
        $id = $row['activity_id'];
        $sql    = "SELECT * FROM activities_translate WHERE activity_id = $id";
        $trans_reslt = mysql_query($sql, $link);
        if($trans_reslt){
            $trans_reslt = mysql_fetch_assoc($trans_reslt);
            if($trans_reslt){
                $row['translation'] = $trans_reslt;
            }
        }

        $TempArray[$index] = $row;
        $index++;
    }
    return $TempArray;

    free($result);

}


// Get all the homeworks of a student class
function get_homeworks($student_id, $class_id, $course_id = 0, $GUID){
    if(!$GUID && !check_guid($GUID)) {
        return false;
    }
    
    
    $student_id = (int)$student_id;
    $class_id = (int)$class_id;
    $link = conn();

    $sql    = "SELECT * FROM homework WHERE class_id = $class_id AND course_id = $course_id";
    $result = mysql_query($sql, $link);

    if (!$result) {
        echo "DB Error, could not query the database\n";
        echo 'MySQL Error: ' . mysql_error();
        exit;
    }

    $TempArray = array(); // make a new array to hold all your data
    $index = 0;
    while($row = mysql_fetch_assoc($result)){ // loop to store the data in an associative array.
        $TempArray[$index] = $row;
        $index++;
    }
    return $TempArray;

    free($result);
}

// Get all the homework attachment
function get_homework_attach($homework_id){

    $homework_id = (int)$homework_id;

    $link = conn();

    $sql    = "SELECT * FROM homework_attach WHERE homework_id = $homework_id";
    $result = mysql_query($sql, $link);

    if (!$result) {
        echo "DB Error, could not query the database\n";
        echo 'MySQL Error: ' . mysql_error();
        exit;
    }

    $TempArray = array(); // make a new array to hold all your data
    $index = 0;
    while($row = mysql_fetch_assoc($result)){ // loop to store the data in an associative array.
        $TempArray[$index] = $row;
        $index++;
    }
    return $TempArray;

    free($result);
}


// Get all the quizzes of a student class
function get_quizzes($student_id, $class_id, $GUID){
    if(!$GUID && !check_guid($GUID)) {
        return false;
    }
    
    $student_id = (int)$student_id;
    $class_id = (int)$class_id;
    $link = conn();

    $sql    = "SELECT online_quiz.* FROM online_quiz
  JOIN online_quiz_class
  ON online_quiz_class.quiz_id = online_quiz.quiz_id
  WHERE online_quiz_class.class_id = $class_id";
    $result = mysql_query($sql, $link);

    if (!$result) {
        echo "DB Error, could not query the database\n";
        echo 'MySQL Error: ' . mysql_error();
        exit;
    }

    $TempArray = array(); // make a new array to hold all your data
    $index = 0;
    while($row = mysql_fetch_assoc($result)){ // loop to store the data in an associative array.
        $TempArray[$index] = $row;
        $index++;
    }
    return $TempArray;

    free($result);
}


// Get about content (page number 8 & 9)

function get_about(){
    $link = conn();

    $sql    = "SELECT * FROM webpage WHERE page_id in (8,9)";
    $result = mysql_query($sql, $link);

    if (!$result) {
        echo "DB Error, could not query the database\n";
        echo 'MySQL Error: ' . mysql_error();
        exit;
    }

    $TempArray = array(); // make a new array to hold all your data
    $index = 0;
    while($row = mysql_fetch_assoc($result)){ // loop to store the data in an associative array.
        $TempArray[$index] = $row;
        $index++;
    }
    return $TempArray;

    free($result);

}

// Get gallaries with the photos

function get_galleries($student_id, $class_id, $GUID){
    if(!$student_id && !$class_id)
        die();
    if(!$GUID && !check_guid($GUID)) {
        return false;
    }
    

    $student_id = (int)$student_id;
    $class_id = (int)$class_id;
    $link = conn();


    /* ---- Must add status condition and sorting by date  ---- */
    $sql    = "SELECT * FROM gallery WHERE status = 1";
    $result = mysql_query($sql, $link);

    if (!$result) {
        echo "DB Error, could not query the database\n";
        echo 'MySQL Error: ' . mysql_error();
        exit;
    }

    $TempArray = array(); // make a new array to hold all your data
    $index = 0;
    while($row = mysql_fetch_assoc($result)){ // loop to store the data in an associative array.

        // Get the translation
        $id = $row['gallery_id'];
        $sql    = "SELECT * FROM gallery_translate WHERE gallery_id = $id";
        $trans_reslt = mysql_query($sql, $link);
        if($trans_reslt){
            $trans_reslt = mysql_fetch_assoc($trans_reslt);
            if($trans_reslt){
                $row['translation'] = $trans_reslt;
            }
        }

        // get all gallery images
        $sql    = "SELECT * FROM gallery_photo WHERE gallery_id = $id AND status = 1";
        $photo_reslt = mysql_query($sql, $link);
        
        //$photo_reslt = mysql_fetch_assoc($photo_reslt);  
        if($photo_reslt){
            while ($photo = mysql_fetch_assoc($photo_reslt)) {
                $photo['photo_file'] = str_replace('gallery/', 'http://sindbadschool.com/files/images/gallery/',$photo['photo_file']);
                $row['photos'][] = $photo;
            }
        }

        $TempArray[$index] = $row;
        $index++;
    }
    return $TempArray;

    free($result);
}

// Save user device tokens generated by ionic push
function save_device_token($student_id, $device_token, $uuid, $platform, $version, $logged_in, $language){
    $link = conn();
    $sql  = "INSERT INTO student_device_token (`student_id` ,`token`, `uuid`, `platform`, `version`, `logged_in`, `language`) VALUES ('$student_id','$device_token', '$uuid', '$platform', '$version', '$logged_in', '$language')";
    $result = mysql_query($sql, $link);
    if (!$result) {
        return false;
    }
}


// Save user device tokens generated by ionic push
function update_user_login_status($student_id, $device_token, $uuid, $logged_in){
    $link = conn();
    $sql  = "UPDATE `student_device_token` SET `logged_in` = '$logged_in' WHERE `student_id`='$student_id' AND `token` = '$device_token' AND `uuid` = '$uuid'";
    $result = mysql_query($sql, $link);
    if (!$result) {
        return false;
    }
}


// Save user device tokens generated by ionic push
function update_user_preferred_language($student_id, $device_token, $uuid, $language){
    $link = conn();
    $sql  = "UPDATE `student_device_token` SET `language` = '$language' WHERE `student_id`='$student_id' AND `token` = '$device_token' AND `uuid` = '$uuid'";
    $result = mysql_query($sql, $link);
    if (!$result) {
        return false;
    }
}


// Save user device tokens generated by ionic push
function delete_device_token($student_id, $device_token){
    $link = conn();
    $sql  = "DELETE FROM `student_device_token` WHERE `student_id`='$student_id' AND `token` = '$device_token'";
    $result = mysql_query($sql, $link);
    if (!$result) {
        echo "DB Error, could not query the database\n";
        echo 'MySQL Error: ' . mysql_error();
        exit;
    }
}


// Get all device tokens
function get_device_tokens($language = NULL){
    $link = conn();
    if (is_null($language)) {
        $sql = "SELECT `token` FROM `student_device_token` WHERE `logged_in` = 1";
    } else {
        $sql = "SELECT `token` FROM `student_device_token` WHERE `logged_in` = 1 AND `language` = '$language'";
    }
    $res = mysql_query($sql, $link);
    $tokens = array();
    if($res){
        while ($row = mysql_fetch_assoc($res)) {
            $tokens[] = $row['token'];
        }
    }
    return $tokens;
    free($res);
}


// Get device tokens for a student

function get_student_device_tokens($id, $language = NULL){
    $link = conn();
    if (!is_null($language)) {
        $sql = "SELECT `token` FROM `student_device_token` WHERE `student_id` = '$id' AND `logged_in` = 1 AND `language` = '$language'";

    } else {
        $sql = "SELECT `token` FROM `student_device_token` WHERE `student_id` = '$id' AND `logged_in` = 1";
    }

    $res = mysql_query($sql, $link);
    $tokens = array();
    if($res){
        while ($row = mysql_fetch_assoc($res)) {
            $tokens[] = $row['token'];
        }
    }
    //print_r($tokens);
    return $tokens;
    //free($res);
}


function get_user_reports($id, $year){
  
  if(!$id || !$year){
    return false;
  }
  
  $link = conn();
  $sql = "SELECT `G`.*, `E`.*, `C`.*
FROM `students_grade` AS `G`
INNER JOIN `exams` AS `E`
          ON G.exam_id=E.exam_id
INNER JOIN `courses` AS `C`
          ON G.course_id=C.course_id
WHERE (G.student_id=$id AND G.year_id=$year)
GROUP BY `C`.`course_id`
ORDER BY `C`.`sort_order` ASC"; 
  $result = mysql_query($sql, $link);

  if (!$result) {
      echo "DB Error, could not query the database\n";
      echo 'MySQL Error: ' . mysql_error();
      exit;
  }
  $TempArray = array(); // make a new array to hold all your data
  $index = 0;
  while($row = mysql_fetch_assoc($result)){ // loop to store the data in an associative array.
      $TempArray[$index] = $row;
      $index++;
  }
  return $TempArray;

  free($result);  
}

// Generate the access token for auth
function getGUID(){
    if (function_exists('com_create_guid')){
        return com_create_guid();
    }else{
        mt_srand((double)microtime()*10000);
        $charid = strtoupper(md5(uniqid(rand(), true)));
        $uuid =
             substr($charid, 0, 8)
            .substr($charid, 8, 4)
            .substr($charid,12, 4)
            .substr($charid,16, 4)
            .substr($charid,20,12);
        return $uuid;
    }
}

// Create access token for a user to login
function get_guid($student_id){
  if($student_id && is_numeric($student_id)){
    $GUID = getGUID();
    $link = conn();
    
    // Delete all old token for this user    
    $sql = "DELETE FROM access_token WHERE student_id = $student_id";
    $res = mysql_query($sql, $link);
    
    //$time = time();
    //print $time   .'-';
    // Insert the access token record
    $sql = "INSERT INTO access_token (student_id, token) VALUES ('".$student_id."', '".$GUID."')";
    $res = mysql_query($sql, $link);    
    return $GUID;
  }
   
}


// Check user's access token
function check_guid($GUID){
  if(!$GUID){
    return false;
  }
  
  $link = conn();
  $sql = "SELECT id FROM access_token WHERE token = $GUID";
  $status = mysql_query($sql, $link);
  if(!$status){
    return false;
  }else {
    return true;
  }
}

// Delete an access token
function delete_guid($GUID){
  if(!$GUID){
    return false;
  }
  $link = conn();
  $sql = "DELETE FROM access_token WHERE token = $GUID";
  $res = mysql_query($sql, $link);
  return true;
}

// Logout 
function logout($GUID){
  return delete_guid($GUID);
  
}
?>