<?php

// Membuat Fungsi menjalankan query database
    function run_query($query){
        global $conn;
        return mysqli_query($conn, $query);
    }
// Membuat funsgi escape untuk semua inputan string
    function escape($data){
        global $conn;
        return mysqli_real_escape_string($conn, $data);
    }
// Mendapatkan data user
    function data_users(){
        $query = "SELECT id_users, name, username,status,terjemah_tanggal(tgl_reg) as tgl_reg FROM tb_users";
        $result = run_query($query);
        return $result;
    }
// Mendapatkan data team
    function data_teams(){
        $query = "SELECT * FROM tb_teams, tb_bidang WHERE tb_bidang.id_bidang=tb_teams.id_bidangFK";
        $result = run_query($query);
        return $result;
    }
// Mendaftarkan user untuk administrator admin web
    function register_users($nama,$username,$password,$status,$tgl_reg){
        $query = "INSERT INTO tb_users (name,username,password,status,tgl_reg) VALUES ('$nama','$username','$password','$status','$tgl_reg')";
        if(run_query($query)) return true;
        else return false;
    }
// Mendaftarkan team tenaga kerja
    function register_teams($name_teams,$bidang,$alamat,$contact_teams,$pay){
        $query = "INSERT INTO tb_teams (name_teams,id_bidangFK,alamat,contact_teams,pay) VALUES ('$name_teams',$bidang,'$alamat','$contact_teams',$pay)";
        if(run_query($query)) return true;
        else return false;
    }
// Mengecek apakah username sudah dipakai
    function cek_username($username){
        $query = "SELECT * FROM tb_users WHERE username = '$username'";
        $result = run_query($query);
        if(mysqli_num_rows($result) != 0) return true;
        else return false;
    }
// Mengecek username pada kondisi update
    function cek_username_update($username){
        $query = "SELECT * FROM tb_users WHERE username = '$username'";
        $result = run_query($query);
        if(mysqli_num_rows($result) <= 1) return true;
        else return false;
    }
// Mengambil data user dengan metode get menggunakan parameter id_users, digunakan sebagai update data users
    function get_users($id_users){
        $query = "SELECT * FROM tb_users WHERE id_users=$id_users";
        $result = run_query($query);
        return mysqli_fetch_assoc($result);
    }
// Mengambil data teams dengan metode get menggunakan parameter id_teams, digunakan sebagai update data teams
    function get_teams($id_teams){
        $query = "SELECT * FROM tb_teams WHERE id_teams=$id_teams";
        $result = run_query($query);
        return mysqli_fetch_assoc($result);
    }
// Funsgi untuk update users
    function update_users($nama,$username,$password,$status,$tgl_reg,$id_users){
        $query = "UPDATE tb_users SET name='$nama',username='$username',password='$password',status='$status',tgl_reg='$tgl_reg' WHERE id_users=$id_users";
        if(run_query($query)) return true;
        else return false;
    }
// Funsgi untuk update users
    function update_teams($name_teams,$bidang,$alamat,$contact_teams,$id_teams,$pay){
        $query = "UPDATE tb_teams SET name_teams='$name_teams',id_bidangFK=$bidang,alamat='$alamat',contact_teams='$contact_teams', pay=$pay WHERE id_teams=$id_teams";
        if(run_query($query)) return true;
        else return false;
    }
// Fungsi untuk menghapus data user, dengan parameter id_user yang digunakan untuk kondisi pada query SQL (WHERE)
    function delete_users($id_users){
        $query = "DELETE FROM tb_users WHERE id_users=$id_users";
        if(run_query($query)) return true;
        else return false;
    }
// Fungsi untuk menghapus data team, dengan parameter id_team yang digunakan untuk kondisi pada query SQL (WHERE)
    function delete_teams($id_teams){
        $query = "DELETE FROM tb_teams WHERE id_teams=$id_teams";
        if(run_query($query)) return true;
        else return false;
    }
// Fungsi untuk menapilkan jumlah team, digunakan untuk menampilkan jumlah team pada page home
    function jumlah_teams(){
        $query = "SELECT * FROM tb_teams";
        $result = run_query($query);
        return mysqli_num_rows($result);
    }
// Fungsi untuk menapilkan jumlah booking, digunakan untuk menampilkan jumlah booking pada page home
    function jumlah_booking(){
        $query = "SELECT * FROM tb_booking WHERE status = 'belum selesai'";
        $result = run_query($query);
        return mysqli_num_rows($result);
    }
// Fungsi untuk menapilkan jumlah admin, digunakan untuk menampilkan jumlah admin pada page home
    function jumlah_admin(){
        $query = "SELECT * FROM tb_users";
        $result = run_query($query);
        return mysqli_num_rows($result);
    }
// Fungsi untuk login admin
    function login_users($username,$password){
        $username = escape($username);
        $password = escape($password);

        $query = "SELECT password FROM tb_users WHERE username = '$username'";
        $result = run_query($query);
        $hash = mysqli_fetch_assoc($result)['password'];
        if(password_verify($password,$hash)) return true;
        else return false;
    }
// Fungsi multi level user, membedakaan antara admin dan keuangan
    function cek_admin($username){
        $query = "SELECT status FROM tb_users WHERE username = '$username'";
        $result = run_query($query);
        $status = mysqli_fetch_assoc($result)['status'];
        if($status == 'Admin') return true;
        else return false;
    }
// Mengambil data user dari get session
    function get_from_session($session){
        $query = "SELECT * FROM tb_users WHERE username='$session'";
        $result = run_query($query);
        return mysqli_fetch_assoc($result);
    }
// Membuat kode booking acak otomatis, dan langsung tampil pada inputan
    function code_booking(){
        $query = "select max(code_booking) from tb_booking";
        $carikode = run_query($query);
        $datakode = mysqli_fetch_array($carikode);
        if ($datakode) {
            $nilaikode = substr($datakode[0], 1);
            $kode = (int) $nilaikode;
            $kode = $kode + 1;
            $kode_otomatis = "P".str_pad($kode, 4, "0", STR_PAD_LEFT);
        }else{
            $kode_otomatis = "P0001";
        }
        return $kode_otomatis;
    }
// Fungsi looping data team, digunakan pada select daftar team
    function loop_teams(){
        $query = "SELECT * FROM tb_teams";
        $result = run_query($query);
        return $result;
    }
// Funsgi daftar booking
    function register_booking($tgl_booking,$code_booking,$pembooking,$name_teams){
        $query = "INSERT INTO tb_booking (tgl_booking,code_booking,name_booking,id_teamsFK,status) VALUES ('$tgl_booking','$code_booking','$pembooking','$name_teams','belum selesai')";
        return run_query($query);
    }
// Mengambil data booking
    function data_booking(){
        $query = "SELECT terjemah_tanggal(tgl_booking) AS tgl_booking, code_booking, name_booking, name_teams, name_bidang, pay FROM tb_booking,tb_teams,tb_bidang WHERE tb_booking.id_teamsFK=tb_teams.id_teams AND tb_bidang.id_bidang=tb_teams.id_bidangFK AND status = 'belum selesai'";
        $result = run_query($query);
        return $result;
    }
// Fungsi untuk menghapus data booking, dengan parameter code booking yang digunakan untuk kondisi pada query SQL (WHERE)
    function delete_booking($code){
        $query = "DELETE FROM tb_booking WHERE code_booking='$code'";
        if(run_query($query)) return true;
        else return false;
    }
// Mengambil data bookingan dengan paramater code bookingnya
    function get_data_booking($code){
        $query = "SELECT terjemah_tanggal(tgl_booking) AS tgl_booking, name_bidang, name_booking, code_booking, name_teams, id_teamsFK, pay FROM tb_booking, tb_teams, tb_bidang WHERE tb_booking.id_teamsFK=tb_teams.id_teams AND tb_teams.id_bidangFK=tb_bidang.id_bidang AND code_booking='$code'";
        $result = run_query($query);
        return $result;
    }
// Fungsi untuk mengecek teams yang free pada tgl ttt, ketika user mendaftarkan bookingan
    function cek_free_teams($name_teams, $tgl_booking){
        $query = "SELECT id_teamsFK, tgl_booking FROM tb_booking WHERE id_teamsFK='$name_teams' AND tgl_booking='$tgl_booking' AND status='belum selesai' ";
        $result = run_query($query);
        if(mysqli_num_rows($result) == 0) return true;
        else return false;
    }
// Fungsi update status bookingan menjadi selesai, sehingga transaksi dinyatakan selesai dan data bookingan masuk ke history
    function payments_done($code){
        $query = "UPDATE tb_booking SET status='selesai' WHERE code_booking='$code'";
        if(run_query($query)) return true;
        else return false;
    }
// Menampilkan data bookingan yang sudah selesai / data history
    function data_booking_selesai(){
        $query = "SELECT terjemah_tanggal(tgl_booking) AS tgl_booking, code_booking, name_booking, name_teams, name_bidang, pay FROM tb_booking, tb_teams, tb_bidang WHERE tb_booking.id_teamsFK=tb_teams.id_teams AND tb_bidang.id_bidang=tb_teams.id_bidangFK AND status = 'selesai'";
        $result = run_query($query);
        return $result;
    }
// Fungsi untuk menapilkan jumlah transaksi yang sudah selesai, digunakan untuk menampilkan jumlahnya pada page home
    function jumlah_payments_done(){
        $query = "SELECT * FROM tb_booking, tb_teams, tb_bidang WHERE tb_booking.id_teamsFK=tb_teams.id_teams AND tb_teams.id_bidangFK=tb_bidang.id_bidang AND status = 'selesai'";
        $result = run_query($query);
        return mysqli_num_rows($result);
    }
// Mengambil data bidang
    function data_bidang(){
        $query = "SELECT * FROM tb_bidang";
        $result = run_query($query);
        return $result;
    }
// Update data booking
    function update_booking($tanggal,$nama_booking,$id_teams,$code_booking,$code){
        $query = "UPDATE tb_booking SET code_booking='$code_booking', id_teamsFK=$id_teams,name_booking='$nama_booking',tgl_booking='$tanggal' WHERE code_booking='$code'";
        if(run_query($query)) return true;
        else return false;
    }
 ?>