<?php namespace App\Models;

use Illuminate\Auth\Authenticatable;
use Illuminate\Database\Eloquent\Model;

class permintaanpupuk extends Sximo  {
	
	protected $table = 't_permintaan_pupuk_herbisida';
	protected $primaryKey = 'id';

	public function __construct() {
		parent::__construct();
		
	}

	public static function querySelect(  ){
		
		return "  SELECT t_permintaan_pupuk_herbisida.* FROM t_permintaan_pupuk_herbisida  ";
	}	

	public static function queryWhere(  ){
		
		return "  WHERE t_permintaan_pupuk_herbisida.id IS NOT NULL ";
	}
	
	public static function queryGroup(){
		return "  ";
	}

	public static function cetak_pengantar($id){
		$r = \DB::select("
				select 
						x.nama_rayon,
						x.nama_area,
						x.kode_permintaan,  
						x.jenis_kendaraan,
						x.nomer_kendaraan,
						x.tanggal,
						x.tanggal_permintaan,
						x.satuan,
						x.nama,
						x.jenis,
						sum(x.jumlah) as jumlah
				from 
				(SELECT
								f.nama_rayon,
								d.nama_area,
								a.kode_permintaan,	
								e.jenis_kendaraan,
								e.nomer_kendaraan,
								DATE(e.tgl_pengantar) AS tanggal,
								DATE(a.tgl_permintaan) AS tanggal_permintaan,
								c.satuan,
								c.nama,
								c.jenis,
								a.jumlah as jumlah
							FROM
								t_permintaan_pupuk_herbisida a
								INNER JOIN  m_pupuk_herbisida c
								ON a.barang = c.id
								INNER JOIN m_area_kebun  d 
								ON d.kode_area = a.area
								LEFT JOIN t_detail_pengantar_pupuk_herbisida e 
								ON e.kode_permintaan = a.kode_permintaan
								inner join master_rayon f on CONVERT(f.kode_rayon USING utf8mb4) COLLATE utf8mb4_unicode_ci = a.unit
							WHERE a.status = 3 AND a.kode_permintaan = '".$id."'
							group by a.barang
							) as x
							 group by x.nama
			");
		return $r;
	}

	public static function saldo_pupuk($id,$unit){
		$r = \DB::select("SELECT
				a.nama,
				a.jenis,
				a.satuan,
				IFNULL(SUM(b.jumlah),0) AS saldo,
				(SELECT IFNULL(SUM(d.jumlah), 0) FROM t_permintaan_pupuk_herbisida d
				WHERE a.id = d.barang
				AND d.status < 4 AND d.unit like '".$unit."%' AND DATE(d.tgl_permintaan) = date(NOW())) AS keluar_hi,
				(SELECT IFNULL(SUM(c.jumlah), 0)  FROM t_permintaan_pupuk_herbisida c
				WHERE a.id = c.barang
				AND c.status < 4 AND c.unit like '".$unit."%')  AS keluar_sd
			FROM
				m_pupuk_herbisida a
				LEFT JOIN t_penerimaan_pupuk_herbisida b
				ON a.id = b.nama_barang AND b.unit like '".$unit."%' and b.status = 2
				WHERE a.id = ".$id."
			GROUP BY a.nama
			ORDER BY a.id");
		return $r;
	}
	

}