'connection.php';
class Query {
    private $conn = null;

    public function __construct() {
        if ($this->conn == null) {
            $connection = new Connection();
            $this->conn = $connection->connect();
        }
    }
    /*Punya Jason -------------------------------------*/
    public function tambahBarang($kodeb,$namab,$hargab,$harga){
      $values=array($kodeb,$namab,$harga,$hargab);
      try {<?php
include
          $query = $this->conn->prepare('INSERT INTO tblBarang values(?,?,?,?)');
          $query->execute($values);
          return 'Data Barang saved!';
      } catch (PDOException $e) {
          return 'Cannot save data!<br>'.$e->getMessage();
      }
    }
    public function tambahUser($username,$password,$level,$kodecabang){
      $values=array($username,$password,$level,$kodecabang);
      try {
          $query = $this->conn->prepare('INSERT INTO tblUser values(?,?,?,?)');
          $query->execute($values);
          return 'Data User saved!';
      } catch (PDOException $e) {
          return 'Cannot save data!<br>'.$e->getMessage();
      }
    }
    public function tambahCabang($kodecabang,$cabang){
      $values=array($kodecabang,$cabang);
      try {
          $query = $this->conn->prepare('INSERT INTO tblCabang values(?,?)');
          $query->execute($values);
          return 'Data Cabang saved!';
      } catch (PDOException $e) {
          return 'Cannot save data!<br>'.$e->getMessage();
      }
    }
    public function listbarang(){
      try {
          $query = $this->conn->prepare('select * from tblBarang');
          $query->execute();
          return $query->fetchAll();
      } catch (PDOException $e) {
          return 'Cannot save data!<br>'.$e->getMessage();
      }
    }
    public function listuser(){
      try {
          $query = $this->conn->prepare('SELECT * from tblUser');
          $query->execute();
          return $query->fetchAll();
      } catch (PDOException $e) {
          return 'Cannot save data!<br>'.$e->getMessage();
      }
    }
    public function listcabang(){
      try {
          $query = $this->conn->prepare('SELECT * from tblCabang');
          $query->execute();
          return $query->fetchAll();
      } catch (PDOException $e) {
          return 'Cannot save data!<br>'.$e->getMessage();
      }
    }
    public function updatebarang($kodeb,$namab,$hargab,$harga){
      $values=array($namab,$hargab,$harga,$kodeb);
      try {
          $query = $this->conn->prepare('UPDATE tblBarang set namabarang=?, hrgbeli=?, hrgjual=? where kodebarang=?');
          $query->execute($values);
          return 'Data Barang Updated!';
      } catch (PDOException $e) {
          return 'Cannot save data!<br>'.$e->getMessage();
      }
    }
    public function updateuser($username,$password,$level,$kodecabang){
      $values=array($password,$level,$kodecabang,$username);
      try {
          $query = $this->conn->prepare('UPDATE tblUser set password=?, level=?, kodecabang=? where username=?');
          $query->execute($values);
          return 'Data User Updated!!';
      } catch (PDOException $e) {
          return 'Cannot save data!<br>'.$e->getMessage();
      }
    }
    public function updatecabang($kodecabang,$cabang){
      $values=array($cabang,$kodecabang);
      try {
          $query = $this->conn->prepare('UPDATE tblCabang set namacabang=? where kodecabang=?');
          $query->execute($values);
          return 'Data Cabang Updated!';
      } catch (PDOException $e) {
          return 'Cannot save data!<br>'.$e->getMessage();
      }
    }

    public function deleteBarang($kodeb){
      try {
          $query = $this->conn->prepare('DELETE FROM tblBarang where kodebarang=?');
          $query->bindParam(1, $kodeb, PDO::PARAM_INT);
          $query->execute();
          return 'Tahapan data saved!';
      } catch (PDOException $e) {
          return 'Cannot save data!<br>'.$e->getMessage();
      }
    }

    public function deleteUser($username){
      try {
          $query = $this->conn->prepare('DELETE FROM tblUser where username=?');
          $query->bindParam(1, $username, PDO::PARAM_INT);
          $query->execute();
          return 'Data Deleted!';
      } catch (PDOException $e) {
          return 'Cannot save data!<br>'.$e->getMessage();
      }
    }

    public function deleteCabang($kodecabang){
      try {
          $query = $this->conn->prepare('DELETE FROM tblCabang where kodecabang=?');
          $query->bindParam(1, $kodecabang, PDO::PARAM_INT);
          $query->execute();
          return 'Data Deleted!';
      } catch (PDOException $e) {
          return 'Cannot save data!<br>'.$e->getMessage();
      }
    }

    public function cekLogin($username,$password){
      try {
        $query = $this->conn->prepare('SELECT * FROM tblUser WHERE username=? AND password=?');
        $query->bindParam(1, $username, PDO::PARAM_INT);
        $query->bindParam(2, $password, PDO::PARAM_INT);
        $query->execute();
        if ($query) {
          return $query->fetch();
        }
        else {
          return '0';
        }
      } catch (PDOException $e) {
        return 'Cannot save data!<br>'.$e->getMessage();
      }
    }
    public function listbarangjson(){
      try {
          $query = $this->conn->prepare('select namabarang from tblBarang');
          $query->execute();
          $simpan=$query->fetchAll();
          $array = Array();
            foreach ($simpan as $key) {
              $array[] = $key["namabarang"];
            }
          $json_array = json_encode($array);
          return $json_array;
      } catch (PDOException $e) {
          return 'Cannot save data!<br>'.$e->getMessage();
      }
    }
    public function ambilkodebarang($namab){
      try {
          $query = $this->conn->prepare('SELECT * FROM tblBarang WHERE namabarang=?');
          $query->bindParam(1, $namab, PDO::PARAM_INT);
          $query->execute();
          return $query->fetch();
      } catch (PDOException $e) {
          return 'Cannot save data!<br>'.$e->getMessage();
      }
    }
    public function insertjual($ses){
      try {
          $query = $this->conn->prepare('INSERT INTO tblJual values(null,?,NOW())');
          $query->bindParam(1, $ses, PDO::PARAM_INT);
          $query->execute();
          return  $this->conn->lastInsertId();
      } catch (PDOException $e) {
          return 'Cannot save data!<br>'.$e->getMessage();
      }
    }
    public function tambahtransaksi($nota,$kdbarang,$qty){
      $values = array($nota,$kdbarang,$qty);
      try {
          $query = $this->conn->prepare('INSERT INTO tblDetilJual values(null,?,?,?)');
          $query->execute($values);
          return  "DATA MASUK";
      } catch (PDOException $e) {
          return 'Cannot save data!<br>'.$e->getMessage();
      }
    }

    /*Punya Nana -----------------------------------------*/
    /*laporanpenjualan.php*/
    public function tampilCabang(){
         try {
            $query = $this->conn->prepare('SELECT * FROM tblCabang');
            $query->execute();
            return $query->fetchAll();
        } catch (PDOException $e) {
            return 'Cannot retrieve data!<br>'.$e->getMessage();
        }
    }
    public function tampilPenjualanFull() {
        try {
            $query = $this->conn->prepare('SELECT tblCabang.namacabang, tblBarang.namabarang, tblDetilJual.qty, tblBarang.hrgjual, tblBarang.hrgbeli,
                                          tblDetilJual.qty*tblBarang.hrgjual AS tothrgjual, tblDetilJual.qty*tblBarang.hrgbeli AS tothrgbeli, tblDetilJual.qty*(tblBarang.hrgjual-tblBarang.hrgbeli) AS totalhrg
                                          FROM tblBarang INNER JOIN tblDetilJual USING(kodebarang)
                                                          INNER JOIN tblJual USING(notajual)
                                                          INNER JOIN tblCabang USING(kodecabang)
                                                          INNER JOIN tblUser USING(kodecabang)
                                          WHERE DATE(tblJual.tgljual)=DATE(NOW()) ');
            $query->bindParam(':user', $user, PDO::PARAM_INT);
            $query->execute();
            return $query->fetchAll();
        } catch (PDOException $e) {
            return 'Cannot retrieve data!<br>'.$e->getMessage();
        }
    }
    public function tampilPenjualan($cabang) {
        try {
            $query = $this->conn->prepare('SELECT tblBarang.namabarang, tblDetilJual.qty, tblBarang.hrgjual, tblBarang.hrgbeli,
                                          tblDetilJual.qty*tblBarang.hrgjual AS tothrgjual, tblDetilJual.qty*tblBarang.hrgbeli AS tothrgbeli, tblDetilJual.qty*(tblBarang.hrgjual-tblBarang.hrgbeli) AS totalhrg
                                          FROM tblBarang INNER JOIN tblDetilJual USING(kodebarang)
                                                          INNER JOIN tblJual USING(notajual)
                                                          INNER JOIN tblCabang USING(kodecabang)
                                                          INNER JOIN tblUser USING(kodecabang)
                                          WHERE tblCabang.namacabang= :cabang AND DATE(tblJual.tgljual)=DATE(NOW())');
            $query->bindParam(':cabang', $cabang, PDO::PARAM_INT);
            $query->execute();
            return $query->fetchAll();
        } catch (PDOException $e) {
            return 'Cannot retrieve data!<br>'.$e->getMessage();
        }
    }

    public function tampilPenjualanCJW($cabang,$jw) {
        try {
          if ($jw=="Harian") {
            $query = $this->conn->prepare('SELECT DATE(tblJual.tgljual) AS tgljual ,tblBarang.namabarang, tblDetilJual.qty, tblBarang.hrgjual, tblBarang.hrgbeli,
              tblDetilJual.qty*tblBarang.hrgjual AS tothrgjual, tblDetilJual.qty*tblBarang.hrgbeli AS tothrgbeli, tblDetilJual.qty*(tblBarang.hrgjual-tblBarang.hrgbeli) AS totalhrg
              FROM tblBarang INNER JOIN tblDetilJual USING(kodebarang)
              INNER JOIN tblJual USING(notajual)
              INNER JOIN tblCabang USING(kodecabang)
              INNER JOIN tblUser USING(kodecabang)
              WHERE tblCabang.namacabang= :cabang AND DATE(tblJual.tgljual)=DATE(NOW())');
              $query->bindParam(':cabang', $cabang, PDO::PARAM_INT);
              $query->execute();
              return $query->fetchAll();
          }
          else if ($jw=="Mingguan") {
            $query = $this->conn->prepare('SELECT DATE(tblJual.tgljual) AS tgljual ,tblBarang.namabarang, tblDetilJual.qty, tblBarang.hrgjual, tblBarang.hrgbeli,
              tblDetilJual.qty*tblBarang.hrgjual AS tothrgjual, tblDetilJual.qty*tblBarang.hrgbeli AS tothrgbeli, tblDetilJual.qty*(tblBarang.hrgjual-tblBarang.hrgbeli) AS totalhrg
              FROM tblBarang INNER JOIN tblDetilJual USING(kodebarang)
              INNER JOIN tblJual USING(notajual)
              INNER JOIN tblCabang USING(kodecabang)
              INNER JOIN tblUser USING(kodecabang)
              WHERE tblCabang.namacabang= :cabang AND DATE(tblJual.tgljual) BETWEEN DATE_SUB(DATE(NOW()),INTERVAL 7 DAY) AND DATE(NOW()) ');
              $query->bindParam(':cabang', $cabang, PDO::PARAM_INT);
              $query->execute();
              return $query->fetchAll();
          }
          else if ($jw=="Bulanan") {
            $query = $this->conn->prepare('SELECT DATE(tblJual.tgljual) AS tgljual ,tblBarang.namabarang, tblDetilJual.qty, tblBarang.hrgjual, tblBarang.hrgbeli,
              tblDetilJual.qty*tblBarang.hrgjual AS tothrgjual, tblDetilJual.qty*tblBarang.hrgbeli AS tothrgbeli, tblDetilJual.qty*(tblBarang.hrgjual-tblBarang.hrgbeli) AS totalhrg
              FROM tblBarang INNER JOIN tblDetilJual USING(kodebarang)
              INNER JOIN tblJual USING(notajual)
              INNER JOIN tblCabang USING(kodecabang)
              INNER JOIN tblUser USING(kodecabang)
              WHERE tblCabang.namacabang= :cabang AND DATE(tblJual.tgljual) BETWEEN DATE_SUB(DATE(NOW()),INTERVAL 30 DAY) AND DATE(NOW()) ');
              $query->bindParam(':cabang', $cabang, PDO::PARAM_INT);
              $query->execute();
              return $query->fetchAll();
          }
        } catch (PDOException $e) {
            return 'Cannot retrieve data!<br>'.$e->getMessage();
        }
    }

    public function tampilPenjualanJW($jw) {
        try {
          if ($jw=="Harian") {
            $query = $this->conn->prepare('SELECT tblCabang.namacabang,DATE(tblJual.tgljual) AS tgljual ,tblBarang.namabarang, tblDetilJual.qty, tblBarang.hrgjual, tblBarang.hrgbeli,
              tblDetilJual.qty*tblBarang.hrgjual AS tothrgjual, tblDetilJual.qty*tblBarang.hrgbeli AS tothrgbeli, tblDetilJual.qty*(tblBarang.hrgjual-tblBarang.hrgbeli) AS totalhrg
              FROM tblBarang INNER JOIN tblDetilJual USING(kodebarang)
              INNER JOIN tblJual USING(notajual)
              INNER JOIN tblCabang USING(kodecabang)
              INNER JOIN tblUser USING(kodecabang)
              WHEREDATE(tblJual.tgljual)=DATE(NOW())');
              $query->execute();
              return $query->fetchAll();
          }
          else if ($jw=="Mingguan") {
            $query = $this->conn->prepare('SELECT tblCabang.namacabang,DATE(tblJual.tgljual) AS tgljual ,tblBarang.namabarang, tblDetilJual.qty, tblBarang.hrgjual, tblBarang.hrgbeli,
              tblDetilJual.qty*tblBarang.hrgjual AS tothrgjual, tblDetilJual.qty*tblBarang.hrgbeli AS tothrgbeli, tblDetilJual.qty*(tblBarang.hrgjual-tblBarang.hrgbeli) AS totalhrg
              FROM tblBarang INNER JOIN tblDetilJual USING(kodebarang)
              INNER JOIN tblJual USING(notajual)
              INNER JOIN tblCabang USING(kodecabang)
              INNER JOIN tblUser USING(kodecabang)
              WHERE DATE(tblJual.tgljual) BETWEEN DATE_SUB(DATE(NOW()),INTERVAL 7 DAY) AND DATE(NOW()) ');
              $query->execute();
              return $query->fetchAll();
          }
          else if ($jw=="Bulanan") {
            $query = $this->conn->prepare('SELECT tblCabang.namacabang,DATE(tblJual.tgljual) AS tgljual ,tblBarang.namabarang, tblDetilJual.qty, tblBarang.hrgjual, tblBarang.hrgbeli,
              tblDetilJual.qty*tblBarang.hrgjual AS tothrgjual, tblDetilJual.qty*tblBarang.hrgbeli AS tothrgbeli, tblDetilJual.qty*(tblBarang.hrgjual-tblBarang.hrgbeli) AS totalhrg
              FROM tblBarang INNER JOIN tblDetilJual USING(kodebarang)
              INNER JOIN tblJual USING(notajual)
              INNER JOIN tblCabang USING(kodecabang)
              INNER JOIN tblUser USING(kodecabang)
              WHERE DATE(tblJual.tgljual) BETWEEN DATE_SUB(DATE(NOW()),INTERVAL 30 DAY) AND DATE(NOW()) ');
              $query->execute();
              return $query->fetchAll();
          }
        } catch (PDOException $e) {
            return 'Cannot retrieve data!<br>'.$e->getMessage();
        }
    }
     /*managebarang.php*/
    public function tampilBarang(){
         try {
            $query = $this->conn->prepare('SELECT * FROM tblBarang');
            $query->execute();
            return $query->fetchAll();
        } catch (PDOException $e) {
            return 'Cannot retrieve data!<br>'.$e->getMessage();
        }
    }
    /*kasir.php*/
    public function cabangUsaha($idcabang) {
         try {
              $query = $this->conn->prepare('SELECT tblBarang.namabarang, tblBarang.hrgjual, tblDetilJual.qty,
                                                    tblDetilJual.qty*tblBarang.hrgjual AS tothrgjual
                                              FROM tblBarang INNER JOIN tblDetilJual USING(kodebarang)
                                                              INNER JOIN tblJual USING(notajual)
                                                              INNER JOIN tblCabang USING(kodecabang)
                                                              INNER JOIN tblUser USING(kodecabang)
                                              WHERE tblCabang.kodecabang=:idcabang AND DATE(tblJual.tgljual)=DATE(NOW())');
              $query->bindParam(':idcabang', $idcabang, PDO::PARAM_INT);
              $query->execute();
              return $query->fetchAll();
         } catch (PDOException $e) {
              return 'Cannot retrieve data!<br>'.$e->getMessage();
         }
    }

    /*Punya Denni -----------------------------------------------*/
    public function ambilNamaCabang($idcabang) {
         try {
              $query = $this->conn->prepare('SELECT *
                                              FROM tblCabang
                                              WHERE kodecabang=:idcabang');
              $query->bindParam(':idcabang', $idcabang, PDO::PARAM_INT);
              $query->execute();
              return $query->fetch();
         } catch (PDOException $e) {
              return 'Cannot retrieve data!<br>'.$e->getMessage();
         }
    }


}

?>